Sqlite and files

Posted by WillFa on Sat 27 Jun 2009 03:45 AM — 4 posts, 19,901 views.

WillFa USA Sat 27 Jun 2009 03:45 AM #0

I'm making another plugin that's going to use a sqlite database. 2 questions.

A) Now that sqlite's built in, the plugin doesn't need to be trusted, right?

B) I was thinking of checking a variable for the directory path to the db, and if the var is not there to pop open utils.directorypicker. Is there a way to test if a file exists? I thought about io.open, but then it'd definitely need to be trusted, right? Is there a way I can tell if the user moved/deleted the db, and re-pop open the directory picker?

Hmm... I suppose I could do something like


if not var.dbpath then
  var.dbpath = utils.directorypicker("Select where to store the database.")
  if var.dbpath then
    InitDB = true
  else
    EnablePlugin(GetPluginID(), false)
    print("Disabling for now. Plugin needs a db.")
  end
end


stmt = db:prepare("SELECT * from SQLITE_MASTER where name = 'Mytable'") 
if stmt:step() ~= 100 and InitDB == false then
  --reselect
end
stmt:finalize()

--.....blah blah

Is there an easier way?

Nick Gammon Australia Forum Administrator Sat 27 Jun 2009 04:49 AM #1

To see if a file exists, you can use utils.readdir with a single file (not a wildcard):

http://www.gammon.com.au/scripts/doc.php?lua=utils.readdir

eg.


if utils.readdir (var.dbpath) == nil then
  print ("file does not exist")
end -- if

Quote:

Is there an easier way?

Well, I think a simple select, similar to what you had, would work alright:


 db = assert (sqlite3.open(var.dbpath))

 local table_exists = false

 for a in db:nrows([[
SELECT * FROM sqlite_master WHERE name = 'Mytable' AND type = 'table'
]]) do 
   table_exists = true
 end

 if not table_exists then

  assert (db:exec [[
    CREATE TABLE Mytable (
    id INTEGER NOT NULL PRIMARY KEY autoincrement,
    name text NOT NULL,
    some_other_stuff INTEGER NOT NULL
     );
   CREATE INDEX xref_name ON Mytable (name);
  ]])

 end -- if

Quote:

Now that sqlite's built in, the plugin doesn't need to be trusted, right?

Correct.

WillFa USA Sat 27 Jun 2009 05:00 AM #2

Cool. Thanks Nick.

The select on sqlite_master would need an open a database though, which would create one. I don't want to leave empty db files behind.

Nick Gammon Australia Forum Administrator Sat 27 Jun 2009 05:38 AM #3

This method will test for the table existing, without making the empty database:


DatabaseOpen ("db", "C:\\test.db", 1)      -- flags: read-only

DatabasePrepare ("db", 
[[
SELECT * FROM sqlite_master WHERE name = 'Mytable' AND type = 'table'
]]
)  
status = DatabaseStep ("db")  
DatabaseFinalize ("db")  

print (status)  --> 100 means we got a row, 101 means we didn't

DatabaseClose ("db")  -- close it