[Home] [Downloads] [Search] [Help/forum]


Register forum user name Search FAQ

Gammon Forum

[Folder]  Entire forum
-> [Folder]  MUSHclient
. -> [Folder]  General
. . -> [Subject]  Sqlite and files

Sqlite and files

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


Posted by WillFa   USA  (525 posts)  [Biography] 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?
[Go to top] top

Posted by Nick Gammon   Australia  (22,973 posts)  [Biography] 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
[Go to top] top

Posted by WillFa   USA  (525 posts)  [Biography] 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.
[Go to top] top

Posted by Nick Gammon   Australia  (22,973 posts)  [Biography] 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
[Go to top] 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,066 views.

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

Go to topic:           Search the forum


[Go to top] top

Quick links: MUSHclient. MUSHclient help. Forum shortcuts. Posting templates. Lua modules. Lua documentation.

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.

[Home]


Written by Nick Gammon - 5K   profile for Nick Gammon on Stack Exchange, a network of free, community-driven Q&A sites   Marriage equality

Comments to: Gammon Software support
[RH click to get RSS URL] Forum RSS feed ( https://gammon.com.au/rss/forum.xml )

[Best viewed with any browser - 2K]    [Hosted at HostDash]