Announcing: TiddlyWiki for SQL users

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

#21

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.

#22

Hmm, an interesting approach too. I’m going to have to think through this a bit.

Thanks for your insight!

#23

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
How can I make this ViewTemplateBodyFilter query work appropriately
#24

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

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.

#26

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!