mirror of
https://github.com/MeowLynxSea/Uptimeow.git
synced 2025-07-09 10:54:38 +00:00
66 lines
2.5 KiB
Plaintext
66 lines
2.5 KiB
Plaintext
local su=require"sqlutil" -- Load SQL utility library
|
||
|
||
local dbname=ba.openio"home":realpath"/data/database.sqlite.db"
|
||
|
||
login = {}
|
||
|
||
if not su.exist(dbname) then
|
||
-- Create a database environment object and open data/file.sqlite.db
|
||
local env,conn = su.open"database"
|
||
-- 创建一个数据库，其中包含用户的mail,name,password,created_at,salt,balance,is_admin
|
||
trace("Creating DB...")
|
||
conn:execute"CREATE TABLE IF NOT EXISTS users (token TEXT PRIMARY KEY,name TEXT,avatar_url TEXT,balance INTEGER);"
|
||
conn:execute"CREATE TABLE IF NOT EXISTS histroy (token TEXT PRIMARY KEY,action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,delta INTEGER,type TEXT,balance INTEGER,total_amount INTEGER,show_amount INTEGER,remark TEXT,custom_order_id TEXT,out_trade_no TEXT,user_id TEXT);"
|
||
conn:execute"CREATE INDEX IF NOT EXISTS idx_history_token ON histroy(token);"
|
||
conn:execute"CREATE INDEX IF NOT EXISTS idx_history_action_time ON histroy(action_time);"
|
||
trace("DB created")
|
||
su.close(env,conn)
|
||
else
|
||
trace("DB already exists")
|
||
end
|
||
|
||
local env = luasql.sqlite()
|
||
local conn = assert(env:connect(dbname)) -- DB connection used for write operations
|
||
assert(conn:setautocommit"EXCLUSIVE") -- EXCLUSIVE for first DB operation only
|
||
conn:setbusytimeout(2000)
|
||
function onunload() -- auto run when app terminates
|
||
trace"Closing DB"
|
||
conn:close()
|
||
env:close()
|
||
end
|
||
|
||
-- Function used for committing and preparing next transaction for EXCLUSIVE use.
|
||
-- The function is used exclusively by dbexec below.
|
||
local function commit()
|
||
while true do
|
||
local ok, err = conn:commit"IMMEDIATE" -- Commit and prepare for IMMEDIATE transaction type
|
||
if ok then break end
|
||
if err ~= "BUSY" then
|
||
trace("ERROR: commit failed on exclusive connection:",err)
|
||
break
|
||
end
|
||
trace"BUSY writing, but we will try again"
|
||
end
|
||
end
|
||
commit() -- the two conn:exec above (in EXCLUSIVE mode)
|
||
|
||
-- Create the thread and the function used for inserting callback
|
||
-- functions into the thread queue.
|
||
local dbthread=ba.thread.create()
|
||
function dbexec(doit) -- used by index.lsp
|
||
dbthread:run(doit) -- queue the doit function in index.lsp
|
||
dbthread:run(commit) -- queue commit
|
||
end
|
||
|
||
-- Opens/creates a new DB 'read' connection object -- i.e. should only
|
||
-- be used for read operations
|
||
function openconn()
|
||
local conn = assert(env:connect(dbname))
|
||
if conn then conn:setbusytimeout(2000) end
|
||
return conn,env
|
||
end
|
||
|
||
-- Returns our persistent DB write connection object
|
||
function getexconn()
|
||
return conn,env
|
||
end |