Announcing: TiddlyWiki for SQL users

It’s a really nice idea to put this on the Tag Tiddler.

I think this implementation would be a little brittle, but how about if the Tag Tiddler has a _fields property containing a list of field names?

In tids2table, I have this godawful blacklist code:

  <$list filter="[subfilter<filter>first[]fields[]] -[[title]] -[[text]] -[[modified]] -[[modifier]] -[[created]] -[[creator]] -[[tags]] -[[type]] -[[list]] -[[caption]] -[[revision]] -[[bag]]" > ... </$list>

This would make that so much nicer, as well as less error-prone.

I don’t see it that way.

That is in my mind complexity that isn’t needed. But you have to do it if it fits your thinking.

Sorry, just jumping in to add that you may also be interested in the optional :include/:exclude suffixes available to the fields operator. E.g. for my own wikis, I’ve defined a global variable,

\define systemFields() draft.title draft.of title tags text type created creator modified modifier

which lets me use the filter step fields:exclude<systemFields> and cut down on a number of ugly filters like that.

I only recently noticed this feature myself, so I thought I’d pass along the tip!

3 Likes

Perhaps not. But I use the Tag Tiddlers a fair bit. I’d be afraid of limiting myself to only including fields in the Tag Tiddler that serve as record column names. While right now in that wiki, I’m using caption, which I could clearly skip the same way I am now. But what if I want a template that uses, say, a priority field on the Tag Tiddler? If I added that, then priority would automatically become a column name for displays. But I meant it to apply to my table not to the rows.

So it feels likely to be brittle in my own workflow. Perhaps it wouldn’t be in yours.

Thank you. I think I had seen that before and forgotten. This was one place I knew was going to need some refactoring.

This is definitely an improvement. For my Node-based wikis, I’d have to also include bag and revision. I’m still not thrilled with any blacklisting approach though, overall.

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:

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!

4 Likes

Hello Scott,

Great work!

Another suggestion for easier understanding:

  • maybe use the same variable name in the WikiText version and the SQL version,
  • Add inline html comment

Some example below:

For instance (from SQL Playground — five tables - do as you will) :

SQL

SELECT CompanyName, ProductName
  FROM Supplier S
  JOIN Product P ON S.Id = P.SupplierId

WikiText

<$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>

Proposed WikiText:

<!-- 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

1 Like

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.

2 Likes

21 posts were split to a new topic: Approaches to SQL

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.

1 Like

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

  • We could permit multiple values
  • Bob may or may not yet exist.

In this case when referring back to SQL the Married field would be a column containing one or more foreign keys.

  • Bob can find who his wife is by finding his name (title) named in the married field of Alice.

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 :slight_smile:

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>
2 Likes

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.

1 Like

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.

1 Like