How do I create an Sqlite3 database using luasql?

Question

I am trying to create a Sqlite3 database with luasql. After I require luasql.sqlite3, how do I create the database on a file?

Also, I can't seem to find the manual for luasql. Is it available anywhere?

Here is the documentation to do that, have a look. This documentation is basically based on the Kepler projects LuaSQL documentation. — NIlesh Sharma
– NIlesh Sharma, Commented Sep 12, 2012 at 19:37
Thanks for the reply. I am not able to find any command to create a database. Does connect create a database if it does not exist? How is it saved to a file? — Milind
– Milind, Commented Sep 12, 2012 at 19:58
From my reading of that doc above, it seems to be the default to create the database (if it doesn't exist), otherwise "NOCREATE" can be used as for options to disable the creation. It's up to you to make sure your tables are there after you "connect". It would be "odd" if it didn't work this way, this is normally how sqlite is used. The database file is created as soon (more or less) as you create the database. — ergosys
– ergosys, Commented Sep 13, 2012 at 3:11

Jane T · Accepted Answer · 2012-09-13 07:53:50Z

2

SQLLite will create the db automatically if it does not exist.

Here is a set of Sample functions for using it in Lua (ignore the fh functions they are just internal to the program I use).

    require 'luasql.sqlite3'
    function opendb(dbname)
    -- Check for Settings Database and create if needed
    local db = fhGetPluginDataFileName()
    local dbenv = assert (luasql.sqlite3())
    -- connect to data source, if the file does not exist it will be created
    dbcon = assert (dbenv:connect(db))
    -- check table for page list
    checkTable(dbcon,'pagelist',
    [[CREATE TABLE pagelist(filename varchar(500), md5hash varchar(32),UNIQUE (filename))
    ]])
    -- create table for settings
    checkTable(dbcon,'settings',
    [[CREATE TABLE settings(key varchar(20), directory varchar(500), 
               host varchar(500), folder varchar(50), userid varchar(50), password varchar(50), UNIQUE (key))
    ]])
    return dbenv,dbcon
end
function checkTable(dbcon,table,createString)
    local sql = string.format([[SELECT count(name) as count FROM sqlite_master WHERE type='table' AND name='%s']],table)
    local cur = assert(dbcon:execute(sql))
    local rowcount = cur:fetch (row, "a")
    cur:close()
    if tonumber(rowcount) == 0 then
        -- Table not found create it
        res,err = assert(dbcon:execute(createString))
    end
end
function closedb(dbenv,dbcon)
    dbcon:close()
    dbenv:close()
end
function loadSettings(dbcon)
    local sql = [[SELECT * FROM settings]]
    local cur,err = assert(dbcon:execute(sql))
    local row = cur:fetch({},'a')
    cur:close()
    if row then
        return row
    else
        -- return default values
        return {
            directory = fhGetContextInfo('CI_PROJECT_PUBLIC_FOLDER')..'\\FH Website',
            host = 'websitehost',
            folder = '/',
            userid = 'user',
            password = 'password',
            new = 'yes'
        }
    end
end
function saveSettings(dbcon,settings)
    -- Check for Settings
    if settings.new == 'yes' then
        -- Create
        sql = string.format([[insert into settings (directory, host, folder, userid, password) Values('%s','%s','%s','%s','%s')]],settings.directory,settings.host,settings.folder,settings.userid,settings.password)
    else
        -- Update
        sql = string.format([[update settings set directory = '%s', host = '%s',folder = '%s',userid = '%s', password = '%s']],settings.directory,settings.host,settings.folder,settings.userid,settings.password)
    end
    local res = assert(dbcon:execute(sql))
end

edited Sep 13, 2012 at 7:53

answered Sep 13, 2012 at 7:48

Jane T

2,1212 gold badges17 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Milind Over a year ago

Thanks, that answers my question. Although I got confused because in the LuaSQL examples they connect to a database luasql-test and then use it but I cannot find the database file anywhere. If it was created how was it deleted?

Jane T Over a year ago

Just change the database name to a full path so you know where it is. For example the fhGetPlugInDataName returns something like "c:\users\documents\plugins\file.dat", it's possible when you don't specific a path, depending on where lua runs it could be put somewhere you can't find with a normal windows search.

Collectives™ on Stack Overflow

How do I create an Sqlite3 database using luasql?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related