As always, the question of “how would I do that/this in TiddlyWiki” just grabs me by the jugular (i.e. it gets all of my attention.)
Proof of concept prototype pending …
select * from Examples
Results (preceded by sanity-checks of steps towards result set) in attached PDF.
(Attachment select _ from Examples.pdf is missing)
And because Talk TiddlyWiki does not allow attachments, you’ll have to find it in the Google Groups thread for these posts: https://groups.google.com/g/tiddlywiki/c/GWxH8tPzDn4
This is some interesting stuff!
I notice that in the PDF output, if a tiddler listed in “The record set” table doesn’t have some fields (or the field value is blank, or two fields have exactly the same value), that row of the table omits the corresponding cell, rather than showing a blank placeholder cell (or a repeated value if two fields have matching values). The result is that the cells for that tiddler don’t align with the corresponding field name headings.
Example 1:
“Interactive Git Documentation by Devin Weaver” has a blank “type” field.
The result is that the “url” field value is displayed under the “type” column heading
Example 2:
“Simple Zork-like Game by Jed Carty” has the same timestamp for “created” and “modified” (20141118143636664).
The result is that the “modified” field value is omitted from the table, and the “text” field value is displayed under the “tags” column heading.
Since I can’t look at your code to see what you are doing, I can only guess at some possible workarounds for these issues.
I assume you are using some filter syntax to iterate over the fields of a tiddler and using something like “get<fieldname>” to retrieve the values that are shown in the table cells. If this is the case, you might try using something like “get<fieldname>else[]”, which would ensure that empty/missing fields are converted to blank text rather than being omitted. For the duplicate value problem, you might try preceding the relevant filter run syntax with “=” to preserve duplicate values in the results.
Hope this helps,
-e
Arg! You are absolutely right. I skipped handling empty fields.
I had imposed on myself a hard 10:30 PM cutoff time on that first crack at it, and had not gotten around to validating the result set.
First thing to fix when I get back at it tomorrow.
Thanks !
Okay, “nulls” handled, for this sample query, anyhoo.
Must test with different “tables” (in what I’m doing, I’m using tags as the way to define virtual tables; a bit different, yet oh-so-cool, from dbms’: the tables are dynamic in regards to columns, as the columns are whatever existing fields in tiddlers that have the tag.)
New version of PDF attached.
(Attachment select _ from Examples.pdf is missing)
Oops, still have a bug in there. On it.
It would be some nice if a search of “duplicates” in tiddlywiki.com’s documentation lead to the “de-duplication” line in the “Filter Expressions” tiddler.
Better if it lead to a tiddler that discussed the broader topic of allowing/disallowing duplicates, with info about the de-duplication filter run prefix and the unique operator. All very good and related stuff.
Anyway, that last bug was related to uniqueness being automagically applied, and having to add a “=” prefix to eliminate de-duplication.
(Attachment select _ from Examples.pdf is missing)
The “code” in the macro is not pretty to look at. I don’t do pretty code until I’ve got proof of concept working.
Proof of concept works (I think, proof in the pudding), so now I can consider making pretty code.
If you want to play with the working “select * from tag” (a tag is a virtual, distributed, and dynamic table), download the attached and drag into whatever TiddlyWiki instance (TiddlyWiki.com !)
Two tiddlers:
- SQL Macros
- select * from Examples
TW-SQL.json (2.32 KB)
1 Like
BTW, currently only works with tags that have no spaces in them.
Next on deck:
<<sql """
SELECT *
FROM Examples
ORDER BY modified """>>
1 Like