I keep toying with the idea (and simplicity) of using TW5 for a small database of three tables and a couple of thousand records. Using the ideas from Announcing: TiddlyWiki for SQL users it all works well. However the issue is if I made this available directly on the web the whole dataset would be exposed, which is not ideal!
Can anyone think of a method of querying the data (SQL) without exposing the full TW file?
Thanks.
I think there may be a misunderstanding. The idea of “TiddlyWiki for SQL users” is a description how SQL commands would look like and then defines a TW filter expression that does the same thing.
A single file TW has no possibility to access its data using SQL commands. The advantage of TW is, that all the data is always available to do full text search without the need of internet connection.
if you don’t need dynamic elements in the reports you want to publish, I suggest you render those reports as static pages from your tiddlywiki, and publish only that.
https://tiddlywiki.com/static/RenderCommand.html and https://tiddlywiki.com/static/Generating%20Static%20Sites%20with%20TiddlyWiki.html might be good starting points.
@pmario has it right about that demo. It was designed to show people used to SQL how to do something similar with TW. But here records are tiddlers in the wiki. If you want something different, other techniques would be necessary. I think TW will scale reasonably to thousands or even tens of thousands of records, but probably not hundreds of thousands or millions.
If you want your data kept elsewhere, only viewing small results through TW, you will probably need to investigate how to expose your data through an HTTP interface (possibly REST with JSON) and then use the tm-http-request to fetch it.
If there’s only a very small subset of your data you want users seeing, then I would extract that subset and port it to the wiki; either that or follow @jerojasro’s suggestion.
If they need to see arbitrary parts of it, but only a little at a time, then I would suggest that you do try converting it to tiddlers and use the techniques of the SQL Playground. TiddlyWiki scales to many more records than you might imagine.