|
Warning: this is an htmlized version!
The original is here, and the conversion rules are here. |
-- This file:
-- http://anggtwu.net/LUA/SQLite1.lua.html
-- http://anggtwu.net/LUA/SQLite1.lua
-- (find-angg "LUA/SQLite1.lua")
-- Author: Eduardo Ochs <eduardoochs@gmail.com>
--
-- A library to use SQLite3 and lsqlite3.
-- Note that I dropped the "3" and the initial "L" in most places -
-- and the "1" refers to the version of this library.
--
-- (defun e () (interactive) (find-angg "LUA/SQLite1.lua"))
-- «.so» (to "so")
-- «.load_sqlite» (to "load_sqlite")
-- «.lsqlite3-tables» (to "lsqlite3-tables")
-- «.rowss» (to "rowss")
-- «.nrowss» (to "nrowss")
-- «.select1» (to "select1")
-- «.dates_between» (to "dates_between")
-- «.diasdasemana» (to "diasdasemana")
-- «.feriados» (to "feriados")
-- «so» (to ".so")
-- «load_sqlite» (to ".load_sqlite")
-- (find-angg "LUA/lua50init.lua" "load_sqlite")
Path.prependtocpath "~/.luarocks/lib/lua/5.1/?.so"
sqlite3 = require("lsqlite3")
dbmem = sqlite3.open_memory()
db = dbmem
-- _____ _ _
-- |_ _|_ _| |__ | | ___ ___
-- | |/ _` | '_ \| |/ _ \/ __|
-- | | (_| | |_) | | __/\__ \
-- |_|\__,_|_.__/|_|\___||___/
--
-- «lsqlite3-tables» (to ".lsqlite3-tables")
-- sqlite3: (find-lsqlite3wiki "sqlite3_functions" "SQLite3 functions")
-- (find-lsqlite3file "lsqlite3.c" "luaL_Reg sqlitelib[] =")
-- sqlite3_db: (find-lsqlite3wiki "database_methods" "Database methods")
-- (find-lsqlite3file "lsqlite3.c" "luaL_Reg dblib[] =")
-- sqlite3_prep: (find-lsqlite3wiki "methods_for_prepared_statements")
-- (find-lsqlite3file "lsqlite3.c" "luaL_Reg vmlib[] =")
do
local db = sqlite3.open_memory()
local prep = db:prepare("SELECT 2+3")
sqlite3_db_methods = getmetatable(db)
sqlite3_prep_methods = getmetatable(prep)
end
sqlite3_sortedkeys = function () return VTable(sortedkeys(sqlite3)) end
sqlite3_db_sortedkeys = function () return VTable(sortedkeys(sqlite3_db_methods)) end
sqlite3_prep_sortedkeys = function () return VTable(sortedkeys(sqlite3_prep_methods)) end
--[[
* (eepitch-lua51)
* (eepitch-kill)
* (eepitch-lua51)
dofile "SQLite1.lua"
= sqlite3_sortedkeys()
= sqlite3_db_sortedkeys()
= sqlite3_prep_sortedkeys()
--]]
--
-- _ __ _____ _____ ___ _ __ _ __ _____ _____ ___
-- | '__/ _ \ \ /\ / / __/ __| | '_ \| '__/ _ \ \ /\ / / __/ __|
-- | | | (_) \ V V /\__ \__ \_ | | | | | | (_) \ V V /\__ \__ \
-- |_| \___/ \_/\_/ |___/___( ) |_| |_|_| \___/ \_/\_/ |___/___/
-- |/
--
-- «rowss» (to ".rowss")
-- «nrowss» (to ".nrowss")
-- (find-es "sqlite" "lua-rows")
sqlite3_db_methods.rowss = function (db,sql)
local bigT = VTable {}
for T in db:rows(sql) do table.insert(bigT, HTable(T)) end
return bigT
end
sqlite3_db_methods.nrowss = function (db,sql)
local bigT = VTable {}
for T in db:nrows(sql) do table.insert(bigT, HTable(T)) end
return bigT
end
--[==[
* (eepitch-lua51)
* (eepitch-kill)
* (eepitch-lua51)
dofile "SQLite1.lua"
= sqlite3_db_methods
= sqlite3_db_sortedkeys()
= db:exec [=[
CREATE TABLE numbers(num1,num2);
INSERT INTO numbers VALUES(10,11);
INSERT INTO numbers VALUES(20,22);
]=]
for L in db:rows ('SELECT * FROM numbers') do PP(L) end
= db:rowss ('SELECT * FROM numbers')
= db:rowss ('SELECT * FROM numbers')[1]
for T in db:nrows ('SELECT * FROM numbers') do PP(T) end
= db:nrowss('SELECT * FROM numbers')
= db:nrowss('SELECT * FROM numbers')[1]
for a,b in db:urows ('SELECT * FROM numbers') do PP(a,b) end
= db:urow1 ('SELECT * FROM numbers')
= db:urow1('SELECT date("now")')
= db:urow1('SELECT date("now")')
a,b,c,d = db:urows ('SELECT * FROM numbers')
= a,b,c,d
= a(b)
--]==]
-- _ _ _
-- ___ ___| | ___ ___| |_/ |
-- / __|/ _ \ |/ _ \/ __| __| |
-- \__ \ __/ | __/ (__| |_| |
-- |___/\___|_|\___|\___|\__|_|
--
-- «select1» (to ".select1")
sqlite3_db_methods.urow1 = function (db,sql)
local f,prep = db:urows(sql)
return f(prep)
end
sqlite3_db_methods.select1 = function (db,subsql)
local sql = "SELECT "..subsql
if sqlite3_verbose then print(sql) end
return db:urow1(sql)
end
sqlite3_db_methods.selectf = function (db,fmt,...)
return db:select1(format(fmt,...))
end
sqlite3_db_methods.qcall = function (db,funname,...)
local qargs = mapconcat(mytostring, {...}, ", ")
return db:selectf("%s(%s)", funname, qargs)
end
-- (find-sqlite3doc "lang_datefunc")
sqlite3_db_methods.date = function (db,...) return db:qcall("date", ...) end
sqlite3_db_methods.time = function (db,...) return db:qcall("time", ...) end
sqlite3_db_methods.datetime = function (db,...) return db:qcall("datetime", ...) end
sqlite3_db_methods.julianday = function (db,...) return db:qcall("julianday", ...) end
sqlite3_db_methods.strftime = function (db,...) return db:qcall("strftime", ...) end
sqlite3_db_methods.weekday = function (db,...) return db:qcall("strftime", "%w", ...) end
--[==[
* (eepitch-lua51)
* (eepitch-kill)
* (eepitch-lua51)
dofile "SQLite1.lua"
sqlite3_verbose = nil
sqlite3_verbose = 1
= db:select1("date('now')")
= db:select1('julianday("2024-10-21")')
= db:select1('julianday("2024-11-21")')
= db:selectf("julianday(%q)", "2024-10-21")
= db:julianday("2024-09-23")
= db:date(db:julianday("2024-09-23"), "+2 days")
= db:strftime("%w", "now")
= db:strftime("%w", "now")
= db:weekday("now")
= db:weekday("now", "+5 days")
--]==]
-- _ _
-- __| | __ _| |_ ___ ___
-- / _` |/ _` | __/ _ \/ __|
-- | (_| | (_| | || __/\__ \
-- \__,_|\__,_|\__\___||___/
--
-- «dates_between» (to ".dates_between")
-- (find-sqlite3doc "lang_datefunc")
-- (find-es "sqlite" "date")
dates_between = function (d1,d2)
local j1 = db:julianday(d1)
local j2 = db:julianday(d2)
local A = VTable {}
for j=j1,j2 do table.insert(A, db:date(j)) end
return A
end
--[[
* (eepitch-lua51)
* (eepitch-kill)
* (eepitch-lua51)
dofile "SQLite1.lua"
= dates_between("2024-09-23", "2024-10-01")
= weekdays_from "0:dom 1:2a 2:3a 3:4a 4:5a 5:6a 6:sab"
for n,nome in ("0:dom 1:2a 2:3a"):gmatch("(%w+):(%w+)") do
PP(n,nome)
end
(find-einsert '((32 127)))
--]]
-- _ _ _
-- __| (_) __ _ ___ __| | __ _ ___ ___ _ __ ___ __ _ _ __ __ _
-- / _` | |/ _` / __| / _` |/ _` | / __|/ _ \ '_ ` _ \ / _` | '_ \ / _` |
-- | (_| | | (_| \__ \ | (_| | (_| | \__ \ __/ | | | | | (_| | | | | (_| |
-- \__,_|_|\__,_|___/ \__,_|\__,_| |___/\___|_| |_| |_|\__,_|_| |_|\__,_|
--
-- «diasdasemana» (to ".diasdasemana")
weekdays_from = function (bigstr)
local T = VTable {}
local pat = "(%w+):(%w+)"
for n,name in bigstr:gmatch(pat) do
T[n] = name
T[n+0] = name
T[name] = n+0
end
return T
end
diasdasemana = weekdays_from "0:dom 1:2a 2:3a 3:4a 4:5a 5:6a 6:sab"
--[[
* (eepitch-lua51)
* (eepitch-kill)
* (eepitch-lua51)
dofile "SQLite1.lua"
= diasdasemana
= diasdasemana[db:weekday("now")]
diasdasemana_C2 = Set.from {"2a", "3a", "4a"}
for _,date in ipairs(dates_between("2024-09-23", "2025-02-28")) do
local ds = diasdasemana[db:weekday(date)]
if diasdasemana_C2:has(ds) then
print(ds, date)
end
end
--]]
-- «feriados» (to ".feriados")
feriados_from = function (bigstr)
local T = VTable {}
local pat = "^%s*(%S+)%s+(.*)"
for _,li in ipairs(splitlines(bigstr)) do
local when,rest = li:match(pat)
if when:match":" then
local date1,date2 = when:match("^([^:]+):(.*)")
for _,date in ipairs(dates_between(date1,date2)) do
-- print(date)
table.insert(T, HTable {date,rest})
end
-- print(date1,date2)
else
table.insert(T, HTable {when,rest})
end
end
return T
end
--[==[
* (eepitch-lua51)
* (eepitch-kill)
* (eepitch-lua51)
dofile "SQLite1.lua"
= feriados_from(feriados_2024_2_bigstr)
2024-09-23::2025-02-06 Periodo letivo
--]==]
--[[
* (eepitch-lua51)
* (eepitch-kill)
* (eepitch-lua51)
dofile "SQLite1.lua"
= SQLite.dbmem
= SQLite.db_keys
= SQLite.sqlite3_keys
bigstr = ee_readfile "~/usrc/lsqlite3_v096/lsqlite3.wiki.html"
= bigstr
pat = '<a name="(.-)">(.-)</a>'
for a,b in bigstr:gmatch(pat) do print(a,b) end
for a,b in bigstr:gmatch(pat) do
print(format('(find-lsqlite3wiki "%s" "%s")', a,b))
end
-- (find-lsqlite3wiki "sqlite3_open_ptr")
<a name="db_create_aggregate">db:create_aggregate</a>
sqlite3_open_memory
--]]
-- Local Variables:
-- coding: utf-8-unix
-- End: