testing sqlite

[judell]

re: #134 (please test)

I think I am close.

 algernon --sqlite=xmlui.db --server . :8080

-------------------------------------------------
Server directory:	.
Server address:		:8080
Database:		SQLite
Options:		[Server]
Cache mode:		On
Cache size:		1048576 bytes
TLS certificate:	cert.pem
TLS key:		key.pem
Request limit:		10/sec per visitor
Large file threshold:	44040192 bytes
Large file timeout:	10 sec
INFO[0000] Serving HTTP/2 on https://localhost:8080/    
ERRO[0000] open cert.pem: no such file or directory. Not serving HTTP/2. 
INFO[0000] Use the -t flag for serving regular HTTP.    
INFO[0000] Serving HTTP on http://localhost:8080/      

It created some empty tables:

sqlite3 xmlui.db 
SQLite version 3.43.2 2023-10-10 13:08:14
Enter ".help" for usage hints.
sqlite> .tables
test         unconfirmed  usernames    users   

That lack permission:

curl http://localhost:8080/data
Permission denied.

 /data and /repos have user permissions, /admin has admin permissions and / is public, by default. This is configurable.

How would I make /data public?

What I really want is a lua script that sets up a public end point that can receive and run arbitrary sql. Is there a way?

Thanks! This is a very cool project and I think what I want is likely possible, hope so!

[xyproto]

Thanks for the question and for giving Algernon a shot. 🙂 This should be possible, but I need more time to give a proper answer. The days ahead are pretty hectic. Using a serverconf.lua where you clear the default permissions should work, but I want to double check this. Cheers

[judell]

Will try.

Thanks, have a nice holiday!

[judell]

OK, I've looked around in the code. From what I can tell, there are multiple databases supported which are used exclusively for recording things like user permissions. I am looking for a way to gain access to the database for other purposes. Consider a simple lightweight standalone app builder that uses a lightweight web server for static files. It can access web services by way of rest APIs. So it could use such a service to persist data. But the theme here is lightweight, simple, standalone, minimal config, no dependencies. Something like Algernon, talking to a local sqlite file, seems like it would be the ideal solution. I think this would require materializing a database connection to the Lua layer, which could then materialize a route that would receive arbitrary SQL, send it to the database, and return results as json which is how the app builder expects to receive data from any API. For what it's worth, I think this would be a really powerful architecture for a lot of things besides what I am aiming to do.

There are a few different Algernon-like projects floating around, so I'll look for one that already does this. If nothing turns up I would be motivated to try to make this happen in Algernon. I have a decent amount of experience with go, but wouldn't know where to start, so any pointers appreciated. If, that is, we think it's even possible at all. It seems like it should be, but you never know.

Thanks!

[tooolbox]

This is what I did with the MSSQL() command, I believe. Probably just take that and tweak it for sqlite.