Announcing: TiddlyWiki for SQL users

#1

There have been several discussions recently that touched on the usage of TiddlyWiki as a small relational database. While I didn’t chime in on those, that is very much how I tend to use TW. I got to thinking that it would be useful to explain the techniques involved. So I started something. (Boy, do I know how to enjoy a long weekend, huh?!):

https://crosseye.github.io/TW5-SQL_Playground/

There is absolutely no explanatory text yet. That will have to come later… if I decide this is worth pursuing. I found a reasonable SQL Tutorial that comes with its own sample database, and converted that to a JSON collection of tidders, with additional TW-specific fields and CamelCase replaced by kebab-case. There are tags/tables for Customer, Supplier, Product, Order and OrderItem, with the obvious relationships. Then I created a number of templates to view the data and its interconnections. You can explore that by poking around the Tables section. (Note that it will take a few hundred milliseconds to expand the menu for Orders [830 records/tiddlers] , and a few seconds for OrderItem [2155 of them], but other than that, things should open and display quickly enough.) All those templates are in the $:/_/sql/templates namespace. All templates, except for Order tiddlers are quite simple; Order is meant to look a bit like an invoice. Poke around, have fun. Please tell me if any of it does not seem reasonable.

But the main reason for this is in Examples. The idea is to have a number of examples of how you would convert SQL statements to somewhat equivalent WikiText. These are either taken directly from the tutorial, or minimally altered from it. Eventually, with explanatory text, this would be a tutorial as well as a list of how-tos. But again, that’s later.

For instance, SQL Where, before any added explanations, looks like this:

SQL

SELECT *
  FROM Supplier
 WHERE Country = 'France'

Filter

[tag[Supplier]country[France]]

List Results

Tabular View

city company-name contact-name country fax id phone
Paris Aux joyeux ecclésiastiques Guylène Nodier France (1) 03.83.00.62 18 (1) 03.83.00.68
Montceau Escargots Nouveaux Marie Delamare France NULL 27 85.57.00.07
Annecy Gai pâturage Eliane Noz France 38.76.98.58 28 38.76.98.06

The list-versus-tabular views would eventually become quite important, as SQL users expect a list of records, but they would need to come to terms with a list of tiddlers instead. Here we show them both.

I have not gotten to any SQL inserts/updates; they will take a more nuanced approach.

There’s much to do beyond just expanding the contents. I have a lot of duplication to remove; I need to ask the experts for better ways to do some of my templates, and for the most pedagogically sound ways to write the Examples’ WikiText

I know I will have questions about various specific parts of this, but for now, I would really love to hear first impressions. If expanded out, would this be a useful tool for beginner/intermediate TW users? How understandable are the examples, especially the last few, which are more complex to do in TW? What could I do to improve it? In general, what do you think?

(If you’re GitHub savvy, feel free to give feedback in terms of issues or pull requests on the repository.)

I tend to keep a few copies around:

At the moment, these all have the same content, of course.

4 Likes
King James Bible Wiki
Rapid Database development tools proposal
Rapid Database development tools proposal
Is there a way to improve performance of many nested transclusions?
#2

I’m not a SQL user, but I took a look anyway out of simple curiosity and was able to reverse-engineer some SQL concepts by analogy to your filters… so I’d say this is an excellent start, Scott!

Your last couple of examples are indeed quite dense. My instinct would be to split some of those filters out into named functions purely for ease of parsing, even if it doesn’t save you any steps or characters. (I’m thinking of the :map lines in particular, which you might be able to handle with a join rather than several addprefix and addsuffixes.) I imagine you’re trying to limit the number of constructions used, but it might be worth it to reduce the cognitive load.

A quick note on your non-functional Example/041 - your filter’s not working because an extra period crept in.

[tag[Supplier]] :filter[[USA,Japan,Germany].split[,]match{!!country}]
                                           ^ delete me!

Thanks for highlighting that particular technique, by the way. It’s so elegant, and yet it would never have occurred to me!

1 Like
#3

Oh, that’s an interesting use I never considered!

Thanks. Great to hear it!

On the contrary, I am looking for the simplest-to-grok, most TW-idiomatic means of doing these things. What’s there now are the first things I got working. At some point, I will probably create a number of posts asking how to do various examples better. And that extends to a few of the templates I’ve used as well. But I need first to decide if the whole project seems worthwhile. And I’ll need a little time away from it to make that decision.

D’Oh! I stared at that for so long, and simply couldn’t see it. Thank you.

All thanks for that should go to @saqimtiaz, who showed it to me in response to a previous question.

(In any case, that issue is fixed, but anyone who wants to see what we were talking about can find it in the old version: https://crosseye.github.io/TW5-SQL_Playground/0.1.0/#Example%2F041.)

1 Like
#4

Hi Scott,
Great Wiki! I like the templates you have used for displaying different pieces of information. The order forms are impressive.
The examples are simple to follow.

Just a small comments, for Contents use

<<toc-selective-expandable "TableOfContents" sort:"sortan[]">>

This way the contents will appear in the right order :wink:

#5

Really interesting project.

Just a small comment, in your SQL where topic there is a mismatch of countries between the SQL version (UK) and the TiddlyWiki version (France). Fixing it will make it easier for a newcomer to follow.

BR

Eskha

#6

Hi Scott,

I like your idea. My use case is know how transfer cross different departments. Like from Development to Purchasing and Quality.
Here I have often the request to generate tables for overview and yours is a very good possibility to get a concept the people can work with.
My idea a few days ago was something like this Idea of multi field data tiddler like a data base - Discussion - Talk TW (tiddlywiki.org).
But If I look to you examples I think it would be an excellent extension to the existing filter expression.

I will follow your concepts and the implementation :+1:

Stefan

#7

I’ve switched that back and forth. When I do that (usually on “caption”) it changes other things that I want to keep static. I want the main contents to be in the order presented (Overview, Examples, Orders [maybe], All Tables, and About). But this orders them by title/caption. The trouble is that I want different sorting for different sections. The main content is as above, Examples I’m sorting by title, but displaying the caption; however maintaining the titles in the order I want to present them is a bit annoying. All Tables I would like to keep in a static order: Customer, Supplier, Product, Order, OrderItem. Customers, I’d like to sort by last name/first name, although by caption (first-last) would be acceptable. Suppliers and Products, I’d like to sort by name (duplicated in caption). Order and OrderItem, I’d like to sort numerically on id (equivalently, alphanumeric by caption.)

I’ve seen @pmario’s enhanced tabs, and I will probably look at incorporating them at some point. Other suggestions would be quite welcome. My hierarchy will be fixed and not dynamic, so I suppose I could do this manually without the tabs macros, but I would rather not.

Thank you for the suggestion!

Oops! I’ll make sure that’s fixed in the next release. Thank you!

While there were several other threads also involved, yours was the one that most directly motivated this idea. I’m afraid it was in the negative though, with the idea that what you are looking for goes against the Philosophy of Tiddlers. I wantd to present an example of how we might work if every tiddler contains “the smallest semantically meaningful units” and that we use built-in capabilities to explore the “rich modelling of relationships between them.”

In this example, on each Order page, at the top is a link to the Customer tiddler, and there are links to every Product being ordered. Each Customer page includes a list of Orders. Each Product page links to its Supplier and all the Orders including it. Each Supplier has a list of Products. There is one missing connection – there should be a link from Order to each OrderItem. It will be easy to add, and I’ll do it in the next release.

These are the simplest, most obvious relations between tables, and the original idea was to show how to build more complex relations out of these… an idea which quickly morphed into a more generic, “How to mimic SQL in TW” one. But that original idea could be demonstrated with filters like this, to find the list of customers who’ve ordered Product/18 (Carnarvon Tigers (16 kg pkg.): $62.50):

[tag[OrderItem]product-id[18]get[order-id]addprefix[Order/]get[customer-id]addprefix[Customer/]unique[]get[caption]sort[]]

And of course we could extend that idea to find all the Customers whose orders included products from a given supplier. And so on.

The major point is that TiddlyWiki already gives you a great number of tools to work with structured data.

A real-world example of these ideas is in http://scott.sauyet.com/Tiddlywiki/Demo/ClubDemo/v4/, a somewhat-anonymized version of the wiki we use to manage our company’s Chess Club. If you look at the major data, Members, Meetings, and Puzzles, you will see that most everything is derived from fields in tiddlers. Only a small part of the site is custom text.

#8

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:

image
image1765×1072 71.5 KB

1 Like
#9

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
#10

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

#11

:blush: 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.

#12

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!

#13

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.

#14

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.

#15

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
#16

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.

#17

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.

#18

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!

2 Likes
#19

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.

#20

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.