All other details about the table, I’d keep in the text field for the “table”.
As JSON data (I.e. maintained with edit field using “index” instead of field.
So definition details about the table and the columns, the columns being the fields.
The values in those fields being default values.
Then the “table” tiddler would be the template for new “rows”. Whatever applies to the tiddler and not the rows, I’d alter accordingly for the new row.
Something like that.
Hmm, an interesting approach too. I’m going to have to think through this a bit.
Thanks for your insight!
I have a new release out, 0.2.1. It has two main changes:
With help from @saqimtiaz and @springer, I was able to add virtual tiddlers for countries. Now various other tiddlers link to missing tiddlers, which are handled by a simple template, and list the suppliers and customers addressed in the country.
Based on a discussion with @Charlie_Veniot and @etardiff, I have come to a better handling of column names, using the Tag Tiddler to store some minor metadata.
The new version overlays the old one, but the older ones are still posted. This one, for instance, can also be found in https://crosseye.github.io/TW5-SQL_Playground/0.2.1/
Thank you everyone for the help!
Hello Scott,
Great work!
Another suggestion for easier understanding:
Some example below:
For instance (from SQL Playground — five tables - do as you will) :
SELECT CompanyName, ProductName
FROM Supplier S
JOIN Product P ON S.Id = P.SupplierId
<$list filter="[tag[Supplier]]" variable="company">
<$let company-id={{{ [<company>get[id]] }}} >
<$list filter="[tag[Product]] :filter[get[supplier-id]match<company-id>]" variable="product" />
</$let>
</$list>
<!-- List all company name from Supplier records -->
<$list filter="[tag[Supplier]]" variable="company-name">
<!-- Get supplier company id from current company name -->
<$let company-id={{{ [<company-name>get[id]] }}} >
<!-- Get all product name having a supplier id matching current company id -->
<$list filter="[tag[Product]] :filter[get[supplier-id]match<company-id>]" variable="product-name" />
</$let>
</$list>
BR,
Eskha
Hi Scott,
Great content – Well worth experimenting with it and examine the underlaying code and templates.
I think it can help users, which know SQL code to easier understand the TW filter-syntax, since they have a reference they know already.
I was looking at the very same example for the same reason, and definitely want to improve it, but have been vacillating on using the SQL names or their aliases (S and P). I will definitely fix this in some manner!
All these examples will eventually have significant explanatory text. I’m pretty sure I would rather that such explanations end up there than in comments, but I’ll see once I start writing that content.
Thank you very much for your contributions!
That is the main goal. A secondary goal is to help me organize my own thinking and my own practices for a certain kind of wiki. And I suppose a tertiary goal is as always to learn more TW techniques.
Thank you for the encouragement!
TiddlyWiki can export CSV files. There are software tools available for querying CSV files using SQL.
This might be common knowledge. I’m just exhaustively listing possible solutions to the problem.
Beyond SQL, TiddlyWiki can also be converted into a graph database(That is, a triplet.). Strings are replaced with identifiers, and relationships between these identifiers are generated. This allows tiddler data to be added outside of tiddlers, and enables the programmatic generation of virtual fields.
Triplet can implement multiple values.
I am thinking if we use the terms from triplets;
A triple is:
(subject, predicate, object)
A tiddler would be the subject eg; Alice
A field would be the predicate eg; Married
A field value/title would be the object eg; Bob
In this case when referring back to SQL the Married field would be a column containing one or more foreign keys.
Then if we wanted to record additional information for the Alice/married/Bob relationship we can create a title (even if a missing title) in the married tiddler [[Alice/married/Bob]] or [[Alice/married/start/end/Bob]]
All relationships can be found searching for example titles with the [prefix[Alice/]] AND/OR [[suffix[/Alice]]
I Call this out as an SQL that can handle this form of relationship would be great.
SQL is used to query tables. There are many specialized languages for querying graphs.
That’s a great ressource! FYI, if you want to do the reverse (use SQL to get tiddlers instead of using wikitext filters), you can use php-wasm. That way, the examples would work both ways 
Here’s a demo:
Wikitext:
<style>
.playground-editor{
min-height:50vh;
border:solid 1px;
display: grid;
grid-template-columns: 1fr 1fr;
&>*{
border-radius: 0;
resize:none;
width:100%;
height:100%!important;
border:inherit;
}
}
</style>
Tiddlers to pass to the PHP playground: <$edit field="filter" placeholder="filtre"/>
<div class="playground-editor">
<$edit field="php" tag="textarea" class="php hljs language-php"/>
<$wikify name="json-tiddlers-output" text="""
{"tiddlers":{<$list filter={{!!filter}} join=",">"{{!!title}}":<$jsontiddler tiddler={{!!title}} exclude=`created creator modified modifier title`/></$list>}}
""" mode="block">
<iframe srcdoc=```
<style>${[<stylesheet>get[text]]}$</style>
<script async type = "text/javascript" src = "https://cdn.jsdelivr.net/npm/php-wasm/php-tags.mjs"></script>
<script type = "text/php" data-stdout = "#output" data-stderr = "#error">
<?
$base64_data = "${[<json-tiddlers-output>encodebase64[]]}$";
$json_data = base64_decode($base64_data);
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE wiki (title TEXT PRIMARY KEY, text TEXT, tags TEXT, type TEXT)");
$data = json_decode($json_data, true);
if ($data && isset($data['tiddlers'])) {
$stmt = $db->prepare("INSERT INTO wiki (title, text, tags, type) VALUES (:title, :text, :tags, :type)");
foreach ($data['tiddlers'] as $title => $fields) {
$stmt->bindValue(':title', $title);
$stmt->bindValue(':text', $fields['text'] ?? '');
$stmt->bindValue(':tags', $fields['tags'] ?? '');
$stmt->bindValue(':type', $fields['type'] ?? '');
$stmt->execute();
}
}
?>
${[{!!php}]}$
</script>
<div id = "output"></div>
<div id = "error"></div>
<style>
* {
background: #f4f4f4;
color: #666;
page-break-inside: avoid;
font-family: monospace;
font-size: 15px;
line-height: 1.6;
max-width: 100%;
overflow: auto;
word-wrap: break-word;
}
</style>
```/>
</$wikify>
</div>
Equivalent wikitext:
<div class="playground-editor">
<$edit field="wikitext" tag="textarea" />
<p><$transclude $field="wikitext" $mode="block"/></p>
</div>
This is absolutely fascinating. My PHP days are long ago, but I guess it’s still clear enough what’s going on. And clearly if we wanted to, we could do something similar to your list of inserts of all non-system tiddlers with some dynamic lists of fields from our data tiddlers. I don’t know how practical this all is, but it’s incredibly impressive!
Thanks for sharing.
I moved a number of posts from here to the new topic Approaches to SQL. If you’ve been following the slightly contentious debate between @TW_Tones and me, you can find follow-ups there.
FYI: The New CodeMirror 6 TiddlyWiki5 plugin includes new tiddler types application/x-sql and text/x-sql and syntax checking that could help with SQL in TiddlyWiki
Thanks. I’ll take a look.