如何使用 luasql 创建 Sqlite3 数据库? [英] How do I create an Sqlite3 database using luasql?

问题描述

我正在尝试使用 luasql 创建一个 Sqlite3 数据库.在我require luasql.sqlite3 之后，如何在文件上创建数据库?

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

另外，我似乎找不到 luasql 的手册.可以在任何地方使用吗?

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

推荐答案

如果数据库不存在，SQLLite 会自动创建.

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

这是一组在 Lua 中使用它的示例函数(忽略它们只是我使用的程序内部的 fh 函数).

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

这篇关于如何使用 luasql 创建 Sqlite3 数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！