|
Warning: this is an htmlized version!
The original is here, and the conversion rules are here. |
-- This file:
-- http://anggtwu.net/LUA/SqlPdfs1.lua.html
-- http://anggtwu.net/LUA/SqlPdfs1.lua
-- (find-angg "LUA/SqlPdfs1.lua")
-- Author: Eduardo Ochs <eduardoochs@gmail.com>
--
-- (defun e () (interactive) (find-angg "LUA/SqlPdfs1.lua"))
-- See: (find-angg "LUA/LSQLite1.lua")
-- «.new-methods» (to "new-methods")
-- «.new-methods-tests» (to "new-methods-tests")
-- «.TableTudos» (to "TableTudos")
-- «.TableTudos-tests» (to "TableTudos-tests")
-- «.TableSems» (to "TableSems")
-- «.TableSems-tests» (to "TableSems-tests")
-- «.TableMs» (to "TableMs")
-- «.TableMs-tests» (to "TableMs-tests")
-- «.createall» (to "createall")
-- «.createall-tests» (to "createall-tests")
Path.addLUAtopath()
require "Caepro5"
Path.prependtocpath "~/.luarocks/lib/lua/5.1/?.so"
sqlite3 = require("lsqlite3")
dbmem = sqlite3.open_memory()
dbkeys0 = getmetatable(dbmem)
dbkeys = VTable(sortedkeys(getmetatable(dbmem)))
sqkeys = VTable(sortedkeys(sqlite3))
db = dbmem
--[[
* (eepitch-lua51)
* (eepitch-kill)
* (eepitch-lua51)
dofile "SqlPdfs1.lua"
--]]
-- «new-methods» (to ".new-methods")
-- (find-lsqlite3doc "#sqlite3_open")
-- (find-lsqlite3doc "#sqlite3_open_memory")
-- (find-lsqlite3doc "#db_nrows")
-- (find-lsqlite3doc "#db_prepare")
-- (find-lsqlite3doc "#db_urows")
-- (find-lsqlite3doc "#methods_for_prepared_statements")
-- (find-lsqlite3doc "#stmt_nrows")
dbkeys0.my_nrows = function (db, ...)
local rows = VTable {}
local stmt = db:prepare(...)
for row in stmt:nrows() do table.insert(rows, HTable(row)) end
return rows
end
dbkeys0.my_urows = function (db, ...)
local rows = VTable {}
local stmt = db:prepare(...)
for a,b,c,d,e,f,g,h in stmt:urows() do
table.insert(rows, HTable{a,b,c,d,e,f,g,h})
end
return rows
end
dbkeys0.my_prepare2 = function (db,st)
local insert_stmt = db:prepare(st)
local insert = function (...)
insert_stmt:bind_values(...)
insert_stmt:step()
insert_stmt:reset()
end
return insert_stmt,insert
end
dbkeys0.my_prepare = function (db,st)
local insert_stmt = db:prepare(st)
local insert = function (...)
insert_stmt:bind_values(...)
insert_stmt:step()
insert_stmt:reset()
end
return insert
end
create_numbers = [=[
CREATE TABLE numbers(num1,num2);
INSERT INTO numbers VALUES(10,11);
INSERT INTO numbers VALUES(20,22);
INSERT INTO numbers VALUES(30,33);
]=]
-- «new-methods-tests» (to ".new-methods-tests")
--[[
* (eepitch-lua51)
* (eepitch-kill)
* (eepitch-lua51)
dofile "SqlPdfs1.lua"
-- = dbkeys
-- = sqkeys
= db:exec(create_numbers)
for T in db:nrows('SELECT * FROM numbers') do PP(T) end
for a,b in db:urows('SELECT * FROM numbers') do PP(a,b) end
= db:my_nrows("SELECT *,rowid FROM numbers")
= db:my_nrows("SELECT *,4,rowid FROM numbers")
= db:my_urows("SELECT *,4,rowid FROM numbers")
-- stmt = db:prepare("SELECT article1, price1, article2, price2 FROM invoice WHERE id = ?")
stmt = db:prepare("SELECT num1,num2 FROM numbers")
= stmt
= stmt:bind_values()
= stmt:step()
PP(stmt:get_named_values())
PP(stmt:get_named_values())
stmt = db:prepare("SELECT num1,num2 FROM numbers")
stmt:reset()
for a,b in stmt:urows() do PP(a,b) end
stmt = "SELECT num1,num2 FROM numbers"
for a,b in stmt:urows() do PP(a,b) end
for a,b in stmt:urows("SELECT num1,num2 FROM numbers") do PP(a,b) end
for my_stats in db:urows("SELECT num1,num2 FROM numbers") do print("my_stats:", my_stats) end
for my_stats in db:urows("SELECT my_stats(col1, col2) FROM test")
do print("my_stats:", my_stats) end
= sqlite3
= VTable(sortedkeys(sqlite3))
= db:exec "CREATE TABLE test (col1, col2)"
= db:exec "INSERT INTO test VALUES (1, 2)"
= db:exec "INSERT INTO test VALUES (3, 4)"
for a,b in db:urows("SELECT col1, col2 FROM test") do
print("a b: ", a, b)
end
assert( db:create_aggregate("my_stats", 2, step, final) )
for my_stats in db:urows("SELECT my_stats(col1, col2) FROM test") do
print("my_stats:", my_stats)
end
--]]
-- «TableTudos» (to ".TableTudos")
-- (find-LATEXsh "ls *.mytoc")
-- (find-LATEXsh "ls *tudo.mytoc")
-- (find-LATEXsh "cat *tudo.mytoc")
--
TableTudos = Class {
type = "TableTudos",
__index = {
create0 = "CREATE TABLE tudos(tudostem, n, substem, initpage);",
create = function (t) return db:exec(t.create0) end,
fnames = function (t) return getoutput "cd ~/LATEX/ && ls *tudo.mytoc" end,
stems = function (t)
local bigstr = t:fnames()
local stems = VTable {}
for stem in bigstr:gmatch("([!-~]+).mytoc") do
table.insert(stems, stem)
end
return stems
end,
contents = function (t,stem)
return ee_readfile(format("~/LATEX/%s.mytoc", stem))
end,
gen4 = function (t) return cow(function ()
for _,stem in ipairs(t:stems()) do
local bigstr = t:contents(stem)
local pat = "{([!-z]+)}{([!-z]+)}{([!-z]+)}"
for n,substem,initpage in bigstr:gmatch(pat) do
coy(stem,n,substem,initpage)
end
end
end)
end,
fill = function (t)
local insert = db:my_prepare("INSERT INTO tudos VALUES (?, ?, ?, ?)")
for a,b,c,d in t:gen4() do insert(a,b,c,d) end
end,
},
}
-- «TableTudos-tests» (to ".TableTudos-tests")
--[[
* (eepitch-lua51)
* (eepitch-kill)
* (eepitch-lua51)
dofile "SqlPdfs1.lua"
t = TableTudos {}
= t:create()
= t:fnames()
= t:stems()
= t:contents("2023-2-C2-tudo")
for a,b,c,d in t:gen4() do print(a,b,c,d) end
= t:fill()
= db:my_urows("SELECT * FROM tudos")
= db:my_nrows("SELECT * FROM tudos")
= db:my_nrows("SELECT * FROM tudos where substem='2024-1-C2-P2'")
--]]
-- «TableSems» (to ".TableSems")
-- (find-angg "LUA/Caepro5.lua" "anyofs" "sems =")
--
TableSems = Class {
type = "TableSems",
__index = {
create0 = "CREATE TABLE sems(sem, yyyys);",
create = function (t) return db:exec(t.create0) end,
gen2 = function (t) return cow(function ()
for sem,yyyydots in sems:kvs() do
local yyyys = yyyydots:gsub("%.", "-")
coy(sem, yyyys)
end
end)
end,
fill = function (t)
local insert = db:my_prepare("INSERT INTO Sems VALUES (?, ?)")
for sem,yyyys in t:gen2() do insert(sem,yyyys) end
end
},
}
-- «TableSems-tests» (to ".TableSems-tests")
--[[
* (eepitch-lua51)
* (eepitch-kill)
* (eepitch-lua51)
dofile "SqlPdfs1.lua"
t = TableSems {}
= t:create()
for sem,yyyys in t:gen2() do print(sem,yyyys) end
= t:fill()
= db:my_urows("SELECT * FROM sems")
= db:my_nrows("SELECT * FROM sems")
--]]
-- «TableMs» (to ".TableMs")
-- (find-angg "LUA/Caepro5.lua" "anyofs" "\nMs =")
--
TableMs = Class {
type = "TableMs",
__index = {
create0 = "CREATE TABLE Ms(M, MM);",
create = function (t) return db:exec(t.create0) end,
fill = function (t)
local _,insert = db:my_prepare2("INSERT INTO Ms VALUES (?, ?)")
for M,MM in Ms:kvs() do insert(M,MM) end
end
},
}
-- «TableMs-tests» (to ".TableMs-tests")
--[[
* (eepitch-lua51)
* (eepitch-kill)
* (eepitch-lua51)
dofile "SqlPdfs1.lua"
t = TableMs {}
= t:create()
= t:fill()
= db:my_urows("SELECT * FROM Ms")
= db:my_nrows("SELECT * FROM Ms")
--]]
-- «createall» (to ".createall")
createall = function ()
local t
t = TableSems {}; t:create(); t:fill()
t = TableTudos {}; t:create(); t:fill()
t = TableMs {}; t:create(); t:fill()
end
-- «createall-tests» (to ".createall-tests")
-- (find-angggrep "grep --color=auto -niH --null -e create LUA/SqlPdfs1.lua")
-- (find-angggrep "grep --color=auto -niH --null -e 'create table' LUA/SqlPdfs1.lua")
--[[
* (find-sh0 "rm -fv /tmp/tudos.db")
* (eepitch-lua51)
* (eepitch-kill)
* (eepitch-lua51)
dofile "SqlPdfs1.lua"
createall()
dbdisk = sqlite3.open("/tmp/tudos.db")
db = dbdisk
= db:exec "begin transaction;"
t = TableSems {}; t:create(); t:fill()
t = TableMs {}; t:create(); t:fill()
t = TableTudos {}; t:create(); t:fill() -- takes 1 minute
= db:exec "end transaction;"
= db:close()
-- (sqlite-mode-open-file "/tmp/tudos.db")
= db:exec "attach database 'tudos.db' as disk;"
= db:exec "create table disk.tudos(tudostem, n, substem, initpage);"
= db:exec "create table disk.sems(sem, yyyys);"
= db:exec "create table disk.Ms(M, MM);"
= db:exec "
t = TableSems {}
t:create()
t:fill()
--]]
-- Local Variables:
-- coding: utf-8-unix
-- End: