DL6ER commented 6 days ago

What does this implement/fix?

This PR adds lsqlite3, a thin wrapper around the SQLite3 engine into FTL's LUA engine. It can be used on the CLI and in scripts. It isn't limited to batch scripts, but also available on LUA server pages (webserver) as well as dnsmasq LUA helper scripts.

Example usage:

local sqlite3 = require("lsqlite3")
-- This line is actually optional, you could also
-- directly use, e.g. "lsqlite3.open_memory()" below 

local db = sqlite3.open_memory()

db:exec[[
  CREATE TABLE test (id INTEGER PRIMARY KEY, content TEXT);

  INSERT INTO test VALUES (NULL, 'Hello World');
  INSERT INTO test VALUES (NULL, 'Hello Lua');
  INSERT INTO test VALUES (NULL, 'Hello Sqlite3')
]]

for row in db:nrows("SELECT * FROM test") do
  print(row.id, row.content)
end

resulting in

1       Hello World
2       Hello Lua
3       Hello Sqlite3

If you have cloned the FTL soures, you could also run

pihole-FTL lua test/lsqlite3/simple.lua

We include four of their unit tests to the FTL CI test runs to ensure the module is working as expected. We cannot include all examples as the library is implementing deprecated functions which are omitted from the SQLite3 engine baked into the FTL binary. The following functions mentioned in the lsqlite3 documentation is, hence, not available in our implementation:

load_extension
trace
aggregate_count

All examples work with in-memory databases, but you can obviously work with database files as well, e.g.,

db = lsqlite3.open("/etc/pihole/pihole-FTL.db")
print(db:db_filename("main"))

for row in db:rows("SELECT COUNT(*) FROM queries") do -- rows without names
  print(row[1]) -- LUA starts counting at 1
end 

for row in db:nrows("SELECT COUNT(*) AS count FROM queries") do -- nrows = named rows
  print(row.count)
end

print('sqlite3 version:', lsqlite3.version())
print('lsqlite3 version:', lsqlite3.lversion())

returning

/etc/pihole/pihole-FTL.db
2465066
2465066
sqlite3 version:        3.46.0
lsqlite3 version:       0.9.6

Related issue or feature (if applicable): N/A

Pull request in docs with documentation (if applicable): N/A

By submitting this pull request, I confirm the following:

I have read and understood the contributors guide, as well as this entire template. I understand which branch to base my commits and Pull Requests against.
I have commented my proposed changes within the code.
I am willing to help maintain this change if there are issues with it later.
It is compatible with the EUPL 1.2 license
I have squashed any insignificant commits. (git rebase)

Checklist:

[x] The code change is tested and works locally.
[x] I based my code and PRs against the repositories developmental branch.
[x] I signed off all commits. Pi-hole enforces the DCO for all contributions
[x] I signed all my commits. Pi-hole requires signatures to verify authorship
[x] I have read the above and my PR is ready for review.

yubiuser commented 5 days ago

What's the reason to add this other than just "because we can"? Was there really a real user request or use case?

DL6ER commented 5 days ago

Example from https://github.com/civetweb/civetweb/blob/master/test/page.lp slightly fixed because it contains SQLite3 errors (trying to add columns that are already there -> SQL failure and using lsqlite3 instead of sqlite3):

HTTP/1.1 200 OK
Content-Type: text/html

<html><body>

<p>This is another example of a Lua server page, served by
<a href="https://github.com/civetweb/civetweb/">CivetWeb web server</a>.
</p><p>
The following features are available:
<ul>
<?
  mg.write("<li>" .. _VERSION .. " server pages</li>")
  if lsqlite3 then
    mg.write("<li>sqlite3 binding</li>")
  end
  if lfs then
    mg.write("<li>lua file system</li>")
  end
?>
</ul></p>
<p> Today is <? mg.write(os.date("%A")) ?></p>
<p> URI is <? mg.write(mg.request_info.uri) ?></p>
<p> URI is <?=mg.request_info.uri?></p>

<p>Database example:
<pre>
<?
  -- Open database
  local db = lsqlite3.open("/etc/pihole/civetweb_test.db")
  -- Note that the data base is located in the current working directory
  -- of the process if no other path is given here.

  -- Setup a trace callback, to show SQL statements we'll be executing.
  -- db:trace(function(data, sql) mg.write('Executing: ', sql: '\n') end, nil)

  -- Create a table if it is not created already
  db:exec([[
    CREATE TABLE IF NOT EXISTS requests (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      method NOT NULL,
      uri NOT NULL,
      addr,
      civetwebversion,
      luaversion,
      aux
    );
  ]])

  -- Add entry about this request
  local stmt = db:prepare(
    'INSERT INTO requests VALUES(NULL, datetime(\'now\'), ?, ?, ?, ?, ?, ?);');
  stmt:bind_values(mg.request_info.request_method,
                   mg.request_info.uri,
                   mg.request_info.remote_port,
                   mg.version,
                   _VERSION,
                   ""
                   )
  stmt:step()
  stmt:reset()
  stmt:finalize()

  -- Show all previous records
  mg.write('Requests:\n')
  mg.write('ID\tTimestamp\t\tMethod\tURI\t\tPort\tCivet Version\tLua Version\n')
  for v in db:rows('SELECT * FROM requests ORDER BY id DESC;') do
  mg.write(v[1] .. '\t' .. v[2] .. '\t' .. v[3] .. '\t'
        .. v[4] .. '\t' .. v[5] .. '\t' .. v[6] .. '\t\t'
        .. v[7] .. '\n')
  end

  -- Close database
  db:close()
?>
</pre></p>
</body></html>

Another example showing how to interact with the in-memory database (debug.database must be true for access to the in-memory database)

HTTP/1.1 200 OK
Content-Type: text/html

<html><body>

<p>Accessing the in-memory database:
<pre>
<?
  local ptr = pihole.get_memdb()
  if ptr == nil then
    mg.write('Memory database is not available\n')
  else
    local mdb = lsqlite3.open_ptr(ptr)

    for v in mdb:rows('SELECT COUNT(*) FROM query_storage;') do
      mg.write('Number of queries in the in-memory database: ' .. v[1] .. '\n')
    end

    for v in mdb:rows('SELECT COUNT(*) FROM domain_by_id;') do
      mg.write('Number of domains in the in-memory database: ' .. v[1] .. '\n')
    end

    for v in mdb:rows('SELECT COUNT(*) FROM client_by_id;') do
      mg.write('Number of clients in the in-memory database: ' .. v[1] .. '\n')
    end
  end
?>
</pre></p>
</body></html>

You could save these files, e.g., as /var/www/html/sqlite3.lp and access them via https://pi.hole/sqlite3

github-actions[bot] commented 1 day ago

This pull request has conflicts, please resolve those before we can evaluate the pull request.

pi-hole / FTL

Add LuaSQLite3 LUA module #2002

What does this implement/fix?

Checklist: