Gammon Forum : MUSHclient : General : Sqlite and files

Entire forum ➜ MUSHclient ➜ General ➜ Sqlite and files

Sqlite and files

It is now over 60 days since the last post. This thread is closed. Refresh page

Posted by WillFa USA (525 posts) Bio

Date

Sat 27 Jun 2009 03:45 AM (UTC)

Amended on Sat 27 Jun 2009 03:47 AM (UTC) by WillFa

Message

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?

Top

Posted by Nick Gammon Australia (23,046 posts) Bio Forum Administrator

Date

Reply #1 on Sat 27 Jun 2009 04:49 AM (UTC)

Amended on Sat 27 Jun 2009 04:53 AM (UTC) by Nick Gammon

Message

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.

- Nick Gammon

www.gammon.com.au, www.mushclient.com

Top

Posted by

WillFa USA (525 posts) Bio

Date

Reply #2 on Sat 27 Jun 2009 05:00 AM (UTC)

Message

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.

Top

Posted by Nick Gammon Australia (23,046 posts) Bio Forum Administrator

Date Reply #3 on Sat 27 Jun 2009 05:38 AM (UTC)

Message

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

- Nick Gammon

www.gammon.com.au, www.mushclient.com

Top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.

13,974 views.

It is now over 60 days since the last post. This thread is closed. Refresh page

Go to topic: Search the forum

top