I made the changes discussed in the last post (excluding @Mohammad’s TOC advice, which still needs some thinking.) The SQL Where examples now match (thanks @Eskha!) And I added a line number to the Order Template as a link to the LineItem records, inspired by comments from @stefan_from_germany. Now an Order might look something like this:
1 Like
Hi @Scott_Sauyet that’s great, I really enjoyed looking through it.
The correspondence between SQL and TW filters is of interest to me from another direction: my goal in MWS is to compile TiddlyWiki filters into SQL queries that can be executed directly on the database.
1 Like
Hi @Scott_Sauyet,
I investigated a little bit your implementation.
How I can SELECT specific fields as
SELECT city, company-name
FROM Suppliers
And is your plan to develop the SQL statements also as procedure to have really something like
<<SQL-SELECT FIELD="city company-name" FROM="Suppliers">>
instead of
<<tids2table "[tag[Supplier]]">>
As it is now it is a huge step forward to use tiddlywiki as Know-How-Transfer tool.
Stefan
Glad to hear it.
Oh, that’s fascinating! Two rich query languages, with a great deal of overlap but very different underlying structures (rows and columns versus lists of objects.) I’ve been punting a bit on that difference, and will respond soon to another recent post explaining how much (how little?) we can comfortably do to handle SELECT foo bar baz FROM qux. I would expect the results of your filter conversions to look more like SELECT * from qux, which seems doable for the simple things; I’m very interested to see how you go about the more complex filters.
You can’t.
Or at least you can’t in any simple, idiomatic manner. Here’s where we run into the fundamental mismatch between the underlying ideas in SQL and TW. The basic data structure that SQL knows is a table containing rows and columns. It can trivially create temporary ones to answer your query. The basic data structure of TW is a list of tiddlers (technically, a list of tiddler titles.) That these tiddlers might sometimes have a consistent set of fields that can be treated as columns is useful, but far from the core of TW.
But that doesn’t stop you from displaying whatever subset of fields you choose. Just don’t expect the fundamental data structures to include this sort of data.
I’m expecting the explanatory text to dive into this in much greater detail. But the goal here is not to explain how to convert arbitrary SQL into Wikitext, but to explain how to use the available relational nature of tiddlers to perform various tasks. SQL is for many a familiar model to compare and contrast with TW; that’s all. Central to TW is that it’s a user interface that also holds the necessary data to present this UI. SQL is a language to optimize data access in a consistent fashion. There is overlap—perhaps a great deal of overlap—but we can’t try to treat them as too similar.
Maybe.
While I have considered extending tids2table to allow me to select column/field names, this will always be a behind-the-scenes feature. I’m trying to create documentation for users, not SQL-like tools for TW. The problem with SQL-SELECT as a name is that it implies that I’m returning a collection of rows containing columns, and that is simply not how TW works. While I suppose we might do something with large collections of temporary tiddlers, that’s not how I ever work, and not something I mean to promote.
Instead, an example like SQL Inner Join has code like this:
<table><tr><th>Company Name</th><th>Product Name</th></tr>
<$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">
<tr><td><$text text={{{ [<company>get[caption]] }}} /></td><td><$text text={{{ [<product>get[caption]] }}} /></td></tr>
</$list>
</$let>
</$list>
</table>
which shows how to build one sort of UI around nested lists. It does not try to create a new data structure (and certainly not a collection of temporary tiddlers!) to hold the data.
Company Name Product Name Exotic Liquids Chai Exotic Liquids Chang Exotic Liquids Aniseed Syrup Refrescos Americanas LTDA Guaraná Fantástica Heli Süßwaren GmbH & Co. KG NuNuCa Nuß-Nougat-Creme Heli Süßwaren GmbH & Co. KG Gumbär Gummibärchen Heli Süßwaren GmbH & Co. KG Schoggi Schokolade … …
But another example, using the same nesting of lists, generates this:
And so I’m a little afraid that selecting the columns inside such a procedure would give people the wrong impression of what’s going on. But I’ll keep thinking about it.
Thanks for the feedback!
The direction I was taking: the db tables are virtual. The rows of a table are all of the tiddlers that have a tag, so the tag is the table. The columns of the table are the domain of fields that exist across the tiddlers that have the tag.
1 Like
Yes, that’s how I’m looking at it as well. More complex way to choose the tables might get a minor mention, but I expect to focus on places where every table has a single distinct tag.
This is also clear. There is little—besides discipline—to ensure that all the tiddlers for a given tag have the appropriate collection of fields, but I don’t think that’s a big deal.
The big problem is the notion of combining record sets. In SQL, it’s a fundamental part of the design:
SELECT DISTINCT CompanyName, ProductName
FROM Supplier S
JOIN Product P ON S.Id = P.SupplierId
JOIN OrderItem I ON P.Id = I.ProductId
ORDER BY CompanyName
In TW, unless we create some temporary tiddlers, we won’t have that. But if we’re trying to format a visual table of rows and columns combining this information, we can often do so with nested lists.
To formalize what fields relate to a tag, create a tiddler with the name = tag. Whatever fields are defined for that tiddler are the fields related to that tag.
The collection of tiddlers for the tags, they are the data dictionary.
Cool.
2 Likes
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:
-
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!
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