Approaches to SQL

#2

My thoughts in the OP were similar, but I did come to a slightly different solution, using a Tag Tiddler to hold table metadata, especially the list of fields denoting column names.

There is something of a mismatch between TW’s lists of tiddlers and SQL’s lists of records, and this is where it gets complicated. It’s straightforward to query to find a subset of tiddlers meeting some criteria. It’s harder to collect fields from different record types into a new data structure. In SQL, that’s simply how it works. In TW, unless we are to create some temporary tiddler—which seems really problematic—then we can’t do this. But we can display results similar to this by nesting assignment widgets (let, set, vars, etc.). Examples such as SQL Inner Join demonstrate this.

I’d only been thinking of this as custom filters for a particular set of tiddler types. Do you envision a more comprehensive mechanism?

I’d love to hear more about this. I have been looking at commonalities between different wikis with SQL-like relationships, but haven’t really been looking to build common tools for them. I think you’re interested in taking this further than that, but I’m not sure what such an approach might look like.

Because I’m focused on creating one tiddler per relationship, and giving each one a tag categorizing that relationship, I find it simpler just to assume that the tag tiddler for that relationship is just like any other Table tag tiddler, holding metadata including the field-names important to the relationship. So I haven’t found a need for a centralized table. What advantages do you find in this?

The other aspect of this I find extremely interesting is the ability to derive relationships from other ones. Anyone who shares both Parents with me is my Sibling, and anyone who shares exactly one Parent is a Half-Sibling. I don’t need to detail that relationship anywhere, just let it fall out of the data. That is intriguing to me. But I still think of it in a case-by-case basis. I don’t know if there is some general approach that would abstract such notions.

#3

I have a set of “code patterns” to do this, so it is not a complication in my mind. I have used it a number of times and also informs some ideas I have for forms in TiddlyWiki on top of field handling.

  • In its simplest form you just store a field-list

Most definitely, in fact I would not bother with SQL like queries until the data was more complex because that is when it comes into its own, such as with a join etc…

A lot of this is in my head, and I have tested various aspects of this in tiddlywiki however it takes time to craft it into something others would follow and champion. Basically, we have the technology, we just need to build a systematic, well named platform on top of TiddlyWiki.

Per type of relationship, or per relationship between multiple tiddlers?

Me too, One per relationship eg married. But only when I need to store additional info within an instance of the relationship eg married with start and end dates.

As far as I can see there are a number of cases where structuring data we just cant do without an intermediate index table, to relate one tiddler with another, although right now I can’t put the full argument I am confident this is true. Perhaps from my former Database design and administration memories.

Personally I am trying to avoid tags (alt tags are fine) as I think the structural components should be separate from the free user tagging solution.

This interests me as well but goes far further and that is to be tolerant of missing information, allowing us to report what is missing and gradually build a database as information becomes available. This approach it much more suitable for naïve users because it does not need the same level of forethought.

Consider this example; Starting with a person tiddler Alice

  • add a married field - this means she married but we don’t know to whom
  • Add Bob to the married field we know she married Bob but the Bob tiddler does not exist
  • Add the Bob Tiddler, we can find that he is married to Alice because he is named in the married field of Alice
  • We can imply that there could now be a married tiddler, that could list all marriages, if not record them (that is already done).
  • Now we discover Alice remarried to Chris, so we can add Chris to Alices married field
    • This exposes the fact we need to now store more information for Alices marriages so we can determine the sequence, or periods.

Adding information to each instance of a relationship

Rather than actually create a tiddler for each marriage of a tiddler, I am looking here at using a form of index tiddler which contains entries such as Alice/marriage/start/end/Bob and Alice/marriage/start/end/Chris even better yet make these into missing tiddler titles [[Alice/marriage/start/end/Bob]] and [[Alice/marriage/start/end/Chris]] so the start and end of marriages can be found as missing titles or in the marriage tiddler with backlinks + those inferred from the marriage field. By including “marriage” in the title we can include other relationship types.

  • Have photos of the wedding? then place them in or tag them with the title eg; [[Alice/marriage/start/end/Chris]]
  • Searching for titles with the prefix or suffix of Alice, containing /marriage/ can find all such relationships with additional info.

Incidentally the titles in the above pattern effectively form an index table, which we may use instead of going the title route I suggested above. Although I like this leveraging of titles in TiddlyWiki as the filters are good at handling them, even when missing.

  • With start/end in the relationship “title” Alice could even remarry Bob a second time and we can store that second remarriage, because the start/end make it unique.

Additional note

The marriage relationship Alice/Bob allows us to infer Bob/Alice but there are other circumstances where this may not be true. We want to be able to indicate if this is the case or not. For example Alice may have a children field containing Dan, such that Dan is her Child, But Alice is thus inferred his parent.

  • The relationship could be “birthed” and only names the children that Alice Birthed, unlike marriages where Bob infers he is married to Alice.

In closing on SQL

It this relationship model was present you can see how a set of custom filter operators or an SQL like language that can handle the different levels of information, make inferences and generate lists/reports or new tables from such data would be a useful additional layer to TiddlyWikis data handling model. Allowing the same queries to be used regardless of the underlying information structure.

  • It must also be tolerant of missing information, in fact could generate lists of missing information to go looking for such as the date of a marriage.
#4

It’s not quite clear to me if we’re talking about the same thing. What we do best in TW filters is transforming lists of tiddler titles into different lists of tiddler titles: concatenating them, intersecting them, filtering them, transforming them, etc. In between these steps we have different lists of tiddlers.

In SQL, we do a similar thing with grids of rows and columns. We transform grids of rows and columns into different grids: combining them, intersecting them, filtering them, projecting them, transforming them, etc. In between these steps we have different grids of rows and columns.

But grids of rows and columns are a different data structure than lists of tiddlers. There is some conceptual overlap but also there are substantial differences. This simple common style of SQL statement:

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

doesn’t have a ready analog in TW.

In SQL, this creates a new temporary grid of rows containing columns CompanyName and ProductName. I can’t see any equivalent of that in TiddlyWiki.

We can easily have a collection of tiddlers tagged Supplier, each containing an Id field and another collection tagged Product, each containing a supplierId field. And we can display nested ListWidgets that format this information in various ways. But I don’t see us being able to collect this information into a data structure that we could further process as though it were a simple list of tiddlers.

If you do have a way to do that, I would really appreciate it if you can share. I haven’t been able to come up with anything.

For the wiki as described in the OP, that’s not particularly relevant. It’s not trying to do SQL-like queries at all. It’s simply pointing out what equivalents to such queries you might develop in TW. If you’re trying to develop a useful toolkit for such scenarios, then this would be very relevant.

I’m thinking something like this (very much off-the-cuff):

title: Marriage
tags: Table
required-fields: people start-date
optional-fields: end-date? ended-in?

title: m1234
tags: Marriage
people: [[Alice]] [[Bob]]
start-date: 1986-09-06
end-date: 1993-05-19
ended-in: divorce

title: m2345
tags: Marriage
people: [[Alice]] [[Chris]]
start-date: 1997-06-21

(I’d likely go with person-1 and person-2 rather than people, but people would also capture polygamous relationships too, if that were a consideration.)

You might be interested in a post from @tomzheng and the entire thread. He’s developing a related tool.

For most of my use-cases, missing information is not a major factor, but the very related later addition of supplemental information can be critical. It sounds like you approach handles them much the same way, which sounds right.

To me that’s much the same as derived relationships discussed above. But again, I think of these for individual wikis with their own data structures. You could certainly capture some of this sort of information by noting if a relationship is (using the mathematical terms) symmetric (like married-to) and/or transitive (like descended-from), You might also deal with others like antisymmetric or reflexive. But derived relationships are broader than can be captured by such categories. is-cousin-of, for instance, is derivable from a collection of parent-child relationships (by having a shared pair of grandparents but not the same parents.) It’s easy to see how to do this for the relationships in a given wiki, but much harder to see how to generalize it.

I hope none of this comes across as discouraging. You’re treading into waters much deeper than I’ve tried. I don’t know if there’s a path across the river the way you’re going, but I do hope so. For me, at the moment, I’m content to play in the shallows, though.

I’m eager to see what you come up with!

1 Like
#5

I wonder if this will help present my view here?

To me a tiddler title is the key to a record who’s columns are the tiddlers fields.

By what ever means a collection of tiddlers with the same or similar fields, bundled together by any means, such as a title prefix, tag or value in one of the fields makes a table.

Now if any one of the fields on the tiddlers and thus in the table have the key or keys (titles) to other tiddler(s), then they are referring to an item in another table (possibly also an item in the current table). One could say that tiddlers can exist in one or more tables simultaneously, you just need to know how to determine the members of each table. It is the same model as lists and sets.

If we say to be married is either or both having a marriage field/column naming another person OR be listed in another persons marriage we can access a “table” (virtual or otherwise) of all listed marriages.

So once we have multiple columns, in rows, in tables and multiple tables we have a database, and a database can be queried with filters and/or something like SQL.

This multi-column list can be the output of a list widget if you wanted, but more importantly it could be used “as if the table existed” for making queries against the data.

1 Like
#6

Yes, this very much accords with my own view. I’ve focused on a common tag, but recognize that you could use fields or title prefixes or other mechanisms for this. So yes, a group of tiddlers with certain common fields and some sort of membership mechanism (tag, field, prefix) form a table, and a collection of related tables can form a database.

I know how to display such results. This is one of my examples in the wiki from the OP. But how would you use this as a data structure we can further manipulate? 5.4.0 multi-valued variables should make this easier, but I don’t see how to do something like this in current TW1. Do you have any examples?

I don’t see how to do that. Do you have further details?

The only real possibility I see for these scenarios is to create temporary tiddlers with this data. While that should work, it’s extremely heavy-weight, and not really appropriate for on-the-fly querying. Do you have another approach?



1 I exaggerate a bit here. We might be able to format each row like a title list and then concatenate the rows into another list, something like:

<$let x="[[a b]] [[c  d]]" y="[[e f]] [[g  h]]" z="[[i j]] [[k l]]">

----------
<$list filter="[<x>] [<y>] [<z>]" variable="record">

Column1: {{{ [enlist<record>nth[1]] }}}

Column2: {{{ [enlist<record>nth[2]] }}}

----------
</$list>
</$let>

But this is extremely unergonomic, and it’s not clear if its extensible to longer sets of records.

#7

Just poking my big nose in to say…

You guys need a common algebra (TW-filters <–> SQL).

Being aware of that or not, slowly, you might just be hammering one out.

3 Likes
#8

I don’t think we’ll ever get fully there. But in a real sense, that’s what the wiki in the OP is designed for, to at least make an approach.

A large subset of TW’s filter operators work as an algebra over lists of strings.

And SQL famously is an algebra over tables.

I think there are enough differences that finding true morphisms between the two algebras is going to be difficult, though. That won’t stop me from trying to provide practical advice, though, especially for those used to SQL trying to work with TW.

Sorry for the tehcnobabble here. I did grad work in math, and my very least favorite subject was Category Theory, so I’m not likely to follow up much further, unless someone can really pique my interest.

#9

Hence…

However, withholding of breath is ill-advised :wink:

which is the antidote of acetaminophen.

1 Like
#10

not handy sorry, still awfully conceptual because I want to build the framework so it reduces the cognitive load doing more and more sophisticated operations on data.

Yes, For a moment consider a filter or filters and or a nested list which iterates a table and joins it with another. Typically we would expect this to return a displayed list, we could provide an option to save it to a new table but that would capture the state “as at a moment in time”. I would prefer to see this implemented as filter functions as they “get evaluated on the fly” and can be used as parameters (unlike a $list without wikify).

Ideally this would be somewhat generic so we could name the two tables and have them joined automatically. However there would be parameters to such a set of functions, by setting one of these the output may only be the key and one of the columns values according to a custom filter.

  • Thus although we have a structure that understands two tables and how to join two (or more) tables our actual query may only need extract a title=value pair.
  • The logical table exists in TiddlyWiki more as a virtual construction against which we can make queries, and although it can be iterated as a whole table it more often would facilitate queries against it and only return the result.

Creating temporary, even permanent tiddlers containing this data has its place, for example capturing the state as at a point in time. And perhaps could be used if one was doing a year end analysis because you want to query the same joined table many times.

However my other approach is to encode the logical table or means by which to represent it, then through setting parameters like the primary key and table names effectively query the logical table while only returning the results of the specific query. The joined table never exists unless you choose to display it in full.

  • There would be many such virtual tables, joins etc… that we can generate even within a simple database. One could batch discover which of these contain, or could contain foreign keys to another table and thus draw a line to that table as a possible join.
  • Given this “entity relationship” diagram we could permit a once off click to save an appropriate “virtual” table “function” that can be used for such queries in future.

Sure I describe a utopia, but if there is any value in any kind of utopia, it is as a destination to approach, knowing each step closer brings value.

#11

We discussed recently how different our approaches to tool-generation are. This demonstrates it clearly. I was looking for examples, ones that I might be able to put into tiddlywiki.com or into mys SQL Playground. I work from examples out to generic tools. You prefer to focus on the generic ideas before spending time on examples.

Sometimes this can be frustrating for me. I imagine it can for you too.

When I ask for examples or elucidation, I hear more abstract ideas, without any sense if they can actually be achieved in current TW. I have a lot of thoughts like, “Is this man allergic to details?” I imagine you have ones like, “Don’t bother me with trivia when I’m thinking deep thoughts!”

I know how to create nested lists for such data. The example I mentioned earlier does this. But I don’t know how to store them as a data structure that we can manipulate in further steps (except for the ugly possibility of storing every “row” as a temporary tiddler.) I just can use them as variables in a display report. That’s valuable, but I’m really hoping for more.

I can’t tell from your post if you have an answer for this, or if all this is still at a conceptual stage for you.

#12
  • Neither of these is true for me, regardless of what you think.

I suppose I have strengths in conceptual thinking and a lot of people need more concrete examples.

Personally I hope to recruit people to help develop such a solution, so I would not want to be too black and white so soon, and should someone else have ideas I have not explored. I did not want to lock in the details, and since the solution aims to be simple but sophisticated the final solutions still need to evolve. So I don’t have final details to share.

However for me, I feel I have laid out sufficient argument for me to move on to the details now.

If I knew what details would help you, I would give you at least preliminary code to help you visualise. But in truth your “not visualising” what I am thinking remains for me a “concept” only, because you cant give me the details of your “objection” either.

I will have a go in my next reply

#13

Maybe that’s part of it. But it feels to me that you’re making extended plans without demonstrating that the goals are even possible. That’s why I tend to work from specifics to a general strategy. After doing something a few times, I feel as though I have a better sense of what can even be done.

In any case,

I feel as though we’re still taking past one another on some of this. But perhaps the following would help.

The SQL Tutorial I used to motivate my examples in the sample wiki were derived from a tutorial at dofactory. There is an online widget there to fiddle with queries against the data. You can enter and run the following query in this widget::

SELECT TOP 10 CompanyName, SUM(LineTotal) as Total
  FROM (
       SELECT CompanyName, OI.UnitPrice * OI.Quantity AS LineTotal FROM OrderItem OI
         JOIN [Order] O ON OI.OrderId = O.id
         JOIN Product P ON OI.ProductId = P.Id
         JOIN Supplier S ON P.SupplierId = S.Id
        WHERE O.OrderDate >= '07/01/2024'
          AND O.OrderDate < '10/01/2024'
  ) Totals
GROUP BY CompanyName
ORDER BY Total DESC

to get the names and total sales of the top ten Suppliers (by total sales) for the third quarter of 2024:

CompanyName Total
Aux joyeux ecclésiastiques 32561.20
Plutzer Lebensmittelgroßmärkte AG 19597.60
Gai pâturage 14128.80
Pavlova, Ltd. 10649.80
Norske Meierier 8080.00
Forêts d’érables 7932.80
Pasta Buttini s.r.l. 7350.40
Heli Süßwaren GmbH & Co. KG 6850.50
G’day, Mate 6373.20
Specialty Biscuits, Ltd. 5583.70

If I wanted to duplicate this functionality in TW—which I haven’t tried—I probably could manage it with some work. But this SQL is built on a subquery, using the results of the inner SELECT statement as a source of data for the outer one.

That inner query returns something like this:

CompanyName LineTotal
Tokyo Traders 744.00
Heli Süßwaren GmbH & Co. KG 398.40
Gai pâturage 217.60
Refrescos Americanas LTDA 36.00
Bigfoot Breweries 112.00
Svensk Sjöföda AB 304.00
Pavlova, Ltd. 778.40
Formaggi Fortini s.r.l. 700.00
Gai pâturage 2176.00
Plutzer Lebensmittelgroßmärkte AG 1092.00
Plutzer Lebensmittelgroßmärkte AG 3465.00
Aux joyeux ecclésiastiques 2108.00
 
(…234 more records…) …

That’s what I really would love to be able to do in TW, and what I don’t see how to do. (Note well that to do this, we’d have to change the query dates; since my extract of that data is from an older source that has transactions dated in the early 2010. Changing the years to 2013 should work.. Update: The data in the wiki has been updated to have dates in 2023 - 2025. Also notice that in this US-based tool, US-style dates are used: 12/17/2025).

To be clear, I’m not asking how to duplicate the whole query. But noting that SQL uses subqueries to create something like in-memory tables, which can then be queried just like any other table, I’m asking how would we do something equivalent in TW. Could we create a data structure from our filter syntax that holds derived information and that we could use in further filters? If so, how might you go about it?

Feel free to use a copy of my wiki for testing. The five tables are available from the right sidebar, and their TagTiddlers have a _fields_ field holding the names of the equivalent of the SQL columns. All the infrastructure is in the $:/_/sql namespace.

#14

Just popping in to say that I think this will be much easier with the advent of multi-valued variables and particularly the :let filter run prefix in 5.4.0. Otherwise, you may need to make heavy use of functions and join.

2 Likes
#15

Agreed. I meant to mention that in the last post. A multi-valued list of strings isn’t as nice to query as an object with named properties like a tiddler, but if we’re consistent in our usage, then it can be used for much the same purposes. And let might be a lifesaver.

#16

To be totally clear, perhaps it will help in future before I even bother sharing a strategy, I have usually done dozens of exploratory research and Proof of concepts for all the elements a believe are needed. They are often disembodied from the larger strategy so I would not bring them in as details when explaining the concept.

  • If you can contribute to the concept please do so, otherwise I look forward to your contribution as the details unfold. It is the diversity in the way we think that makes collaboration so powerful.

For example previous work to build dynamic tables from scratch with both row and column filters, my flags solution, and advanced nested lists all comprise the code we would need.

In your SQL Example above I can visualise this in nested lists, however with the extended filter runs and functions I can visualise moving the nested lists into functions. So we then can have the equivalent of SQL in Tiddlywiki filters. By keeping your example in mind I can visualise an “interpreter” to translate SQL into Filters, although I know there are gaps in my understanding there, I have seen plenty of equivalents including tiddlywiki’s scripts parsing into html, or graph plugins etc… to know it is possible.

I agree whole heartedly with this however part of playing with this 5.4.0 “multi-valued variables” feature for me has been seeing how it can be used in some of the “less tractable” cases, at least when it comes to moving multi-value and keyword value lists into filters.

  • In the next section you can see how I would like to generate filter output, a row, that may contain two or more titles which may include duplicates of one of the titles, that represents the keys in joined tables. I am not sure if multi-valued variables will help here, but I expect so.
    • for example the initial output of a join would be;
key1oftable1, key5oftable2
key2oftable1, key7oftable2
key2oftable1, key9oftable2

A “key” :nerd_face: learning here for me

Whilst trying to pencil out the concepts here I have come to discover at least one way to “substantially” simplify multiple table manipulation in tiddlywiki.

With the exception of list fields or fields with a title (key), or a field that is the basis of a selection filter (even then) the following is true;

All the other columns in all other tables can be ignored while doing a query, that is operations like select and join are driven by the keys/titles in the tables, not the values in other field/columns. Once you have these you can then return to each named tiddler to retrieve the various field/column values to generate the final “virtual table” or record.

Another simplification is;

If the title of the current tiddler is the key to its entry in another table, sometimes all we need to know is “this key may have an entry in another table (as a foreign key)”, thus we can know it can be joined. As a result there may be value in asking the designer (once off) to nominate which tables have a relationship with another and the fieldname in which it is stored, including if it is a list field.

  • This could be the point to “generate” a set of custom operator/functions which can then be used to do the joins and other queries. an example may be storing these in a tiddler table1.table2 which can automatically generate this joined table, in that tiddler (with/without a filtered selection). In the real world this may read client.invoices
  • It would also generate functions to filter the output, that is, a query against this virtual table (but only in memory. Perhaps we store these in table1/global.

Gven a list of joined rows

So far the output of a join has being described as totally in memory but we can use a method as documented here How to have your code and action it too - batch operations on multiple tiddlers, refactoring your wiki to add actions to store the output a temporary, or permanent (as at) record eg in a tiddler.

  • This still requires a trigger so I hope one day we have an action on navigate (once) to trigger this.
#17

Ok, although I would love it if you can share them when asked. It’s very difficult for me to sign onto a cause if I don’t see that its goals are even possible. “Trust me,” is not enough.

In any case, we have two very different focuses.

From the OP on, I’ve been discussing how to write a wiki that can demonstrate how various facets of SQL translate into TiddlyWiki. And I’m trying to create a wiki that itself can serve as an example of those principles. My goal is to help people who understand SQL learn how to apply similar concepts towards building a wiki. It’s all about examples.

As I understand it, your goal is abstract some of the work of maintaining SQL-adjacent TiddlyWikis into a tool or collection of tools that would automate many common patterns.

These goals are not at all in conflict. But they have little in common. Perhaps one day if you manage to develop your tool, in some places it could serve to replace the patterns I’m trying to enforce. And perhaps some of my example could serve as targets for your tools to try to hit. Other than that, I see little overlap.

#18

Actually it’s not a simple “Trust me”, I have put a conceptual argument, perhaps it is not because I am asking you trust me, but you do not trust conceptual models. Each to their own diverse thinking.

This is another case where we differ. The way I conceptualise tiddlers as rows, fields as columns, connected by a filter becomes a table, connected by foreign keys is what I would have thought essential to your endeavour. I gave the interpretation of how TiddlyWiki Translates into SQL.

But I do not need to persuade you, I was only trying to help, and put to words, what I can see to be the fundamental equivalency between our endeavours.

#19

It’s not that.

Conceptual models are great. They help us define terms consistently, delineate boundaries between subsystems, create useful definitions, and much more.

But this thread was about how to share information with beginner and intermediate TW users, specifically those ones who already understand SQL. (Or in @etardiff’s case, helping an advanced TW user learn more about SQL! :smile:) I have already articulated the notion of tiddlers with a specific tag serving the same role as table rows, with fields as columns and that tag standing in for the table itself. (I know you are also suggesting using non-tag fields or title prefixes to define table rows; I have already said that I will treat that as an advanced topic, if at all.) When you restate that, I can nod along, but it doesn’t add much as far as I’m concerned.

When you talk about all the things you’ve done to support your concept but never offer any of them for display, this all sounds like vapor-ware.

So if all you’re offering is the notion of tiddlers as rows and so on, sure, fine, but we’ve already been through that. You seemed to be suggesting generic tools that would let you work with many different schemas, common tables that would serve for many different types of relationships, clicking to save virtual tables for future querying, But I haven’t seen any evidence that these could actually be built or that they’d offer much of use, especially to my target audience of SQL users new to TW.

#20

Hmmm is all I can say

never offer

Is a bit extreme

So you have researched my body of work here in talk.tiddlywiki?

#21

I imagine you do know that I’ve read almost everything you’ve posted in the last few years (minus perhaps some AI material). I’ve learned a lot from you, and we’ve had many fruitful exchanges.

I was speaking only of this topic. But as this has veered pretty far from the OP, I think I will split out our recent exchange into its own topic.