What if SQL statements could be used in TiddlyWiki?

50 views
Skip to first unread message

Charlie Veniot

unread,
Nov 24, 2022, 3:46:34 PM11/24/22
to TiddlyWiki
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 ...

Charlie Veniot

unread,
Nov 24, 2022, 7:39:39 PM11/24/22
to TiddlyWiki
Working on:  select * from Examples

Using TiddlyWiki.com

The tag "Examples" being the table.

Charlie Veniot

unread,
Nov 24, 2022, 9:29:38 PM11/24/22
to TiddlyWiki
select * from Examples

Results (preceded by sanity-checks of steps towards result set) in attached PDF.

select _ from Examples.pdf

Charlie Veniot

unread,
Nov 24, 2022, 9:31:40 PM11/24/22
to TiddlyWiki
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

Eric Shulman

unread,
Nov 24, 2022, 10:39:01 PM11/24/22
to TiddlyWiki
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

CJ Veniot

unread,
Nov 24, 2022, 11:44:42 PM11/24/22
to tiddl...@googlegroups.com
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 !

--
You received this message because you are subscribed to a topic in the Google Groups "TiddlyWiki" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/tiddlywiki/GWxH8tPzDn4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to tiddlywiki+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tiddlywiki/0c91473e-d7eb-46a5-b888-91a0e34b7d24n%40googlegroups.com.

Charlie Veniot

unread,
Nov 25, 2022, 9:39:46 AM11/25/22
to TiddlyWiki
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.

select _ from Examples.pdf

Charlie Veniot

unread,
Nov 25, 2022, 9:49:48 AM11/25/22
to TiddlyWiki
Oops, still have a bug in there.  On it.

Charlie Veniot

unread,
Nov 25, 2022, 10:09:55 AM11/25/22
to TiddlyWiki
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.
select _ from Examples.pdf

Charlie Veniot

unread,
Nov 25, 2022, 10:15:46 AM11/25/22
to TiddlyWiki
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

Charlie Veniot

unread,
Nov 25, 2022, 10:20:27 AM11/25/22
to TiddlyWiki
BTW, currently only works with tags that have no spaces in them.

Charlie Veniot

unread,
Nov 25, 2022, 11:19:39 AM11/25/22
to TiddlyWiki
Next on deck:

```
<<sql """
 SELECT *
   FROM Examples
  ORDER BY modified """>>
```

Reply all
Reply to author
Forward
0 new messages