Rapid Database development tools proposal

#14

Different humans use different words for probably the same thing.

I guess that would be me. When I saw “database” in the title, I remembered TiddlyMap, which is built on vis.js, then I remembered https://fossil-scm.org which has a built-in wiki and uses sqlite as backend, then I remembered sql.js , then I wondered if this topic may mean that sonebody has the yolo idea to combine TiddlyWiki and sql.js into an awesome plugin.

#15

There is a new plugin in the making, which actually does combine TW server version with SQLite.

The MWS Plugin: Announcing the MultiWikiServer Plugin

1 Like
#16

As the author of this topic my interest in databases is specifically about leveraging the tiddler, it can act as a record in a database with the fields driving the additional columns but also comes with convenient default fields such as created and modified.

  • A simple tag or field value can indicate to which table a record belongs, and smart design can have one record appear in more than one table.
  • The advantage of such records in a database, is as written about by me in the past, tiddlywiki puts the primary key or title at eye level, so each row in a table can be considered an object within that table.
  • Tools such as relink and other design methods can easily link records, tables to build sophisticated structures, basically by providing a title (for one to one relationships), or filter (for one to many relationships), relink specifically allows us to retain referential integrity.
  • By focusing on the tiddler as a database record we automatically inherit all the features of a tiddler and can then make use of tiddlywikis UI in viewing, listing, modifying records. This includes but is not limited to plugins/shadow tiddlers as read-only tiddlers that can be edited is desired. Thus a plugin can be a repository of data tiddlers.
  • Anyone with database and software design experience can see how a number of well establish features will be needed to back this up including field definitions, field type definitions, forms, edit modes, object and report templates.

The motivation of this topic is to open access and building of common everyday databases around the tiddler as a record, and to do this providing a small subset of tools to allow such databases to be bootstrapped with minimal complexity. Provide an infrastructure to move from a tiddler, considering it a real world object (contact, task etc…) and helping automaticaly deliver the necessary tools to build and use forms as a UI into the resulting database.

  • One design effort can be generalised to help many future design efforts by capturing and publishing the core infrastructure layer to make better use of tiddlywiki as a database for everyday objects.

Futures;

Once we have a reliable and accessible way to build databases and manipulate the data therein it will become possible to build meta tools that a naive user can use to build their databases with design guidance, on how to do so effectively.

#17

As I read your replies and work offline, I have come to realise a rational approach may be to create two new view and edit modes that are adjacent to the existing view and edit template. The following are those and some working titles;

  • View template used to render the text field

    • Introduce a set of tools to present the content of a tiddler and additional fields as a view template
    • Extension to the view template;
      • Have a view template extension that allows the introduction of filed level editors within the view template form. Lets call this “view update mode”, which allows some selective updating of the details within a view template.

  • Edit template used to edit a tiddlers text field, add and edit other custom fields.

    • Introduce a set of tools to edit the tiddler that is able to take account of additional fields, their definitions and field types.
    • Extension to the Edit template;
      • Have an edit template form. Lets call this “Form edit mode”, which makes the tiddler editor able to leverage a form to change the layout of the edit mode to facilitate to better allow editing of tiddlers acting as objects and leverage forms, field definitions and field types.

  • View mode

    • View update mode
      • No draft tiddler, the field mechanism may optionally have a save and undo option or edit directly.

  • Edit mode

    • Form edit mode
      • Draft tiddler, all changes can be escaped, cancel or done to commit to store.

In the above two cases the new extended modes may be toggledon/off from within their parent mode eg View/Edit and may default under particular conditions. Thus without intervention, “View Update mode” and “Form edit mode” may default for any tiddler that has the form defined.

  • Thus toggling between these two new modes would occur as currently implemented by clicking the edit button, and return to the update mode by clicking cancel or done.
    • This toggling could be local or global
    • Within these new modes and depending on a user access configuration each of these may provide a way to toggle back to the traditional view or edit mode for a given tiddler.

It seems to me to make sense to use the existing cascade mechanism or possibly even the development of new cascades to support this.

  • For example a “view form cascade” and “edit form cascade”, the absence of which uses the current view and edit templates.
  • This will allow a cascade of forms such as simple, verbose and custom for each tiddler type or “tiddler object”.
#18

Lots of great stuff here. I wish I had more time to digest it. I want to respond to two points:

I have one tool, written for a specific project but applicable fairly generically . You can see it in action on any of the data records (in the various tables, found under All Tables in the main menu) by looking at the footer, and expanding the fields details.

(The ViewTemplate/procedure breakdown needs to be fixed. The whole summary/details structure should be part of the template to make the procedure more reusable. And that procedure should probably be split in two for the two tables shown.)

There is one point about this that I think is important. The white-list of actual data fields to be shown is carried as a field on the TagTiddler. I think that’s a useful design. It gives us a single place to capture table-level information.

I want to caution about being too optimistic here. TW is never going to replace your RDMS. It’s a tool for creating and displaying content. But that content can, in whole or in part, be relational data. And if that data doesn’t need to be used much outside this GUI, then the relational capabilities of TW are great. I love the idea of expanding them. Just so long as people aren’t thinking that this will ever replace their Oracle, MySQL, or Mongo installations.

#19

Thanks @Scott_Sauyet for you feedback, and great example.

I agree in principal, On the above item I am keen to not use tags to drive the object type or mode the tiddler is in but a kind of field cascade, an example may be a field-list that contains the list of fields and their order in the form, possibly including virtual fields for other form elements like a “ruler line”. But of course if the current tiddler does not have a field-list then go to the form or object definition to find the form definition.

  • 1.0 You could ask then what is the minimum required to define a form, is it a list of elements (Fields and form elements) that can be stored in a field, idealy this would be better than requiring a whole tiddler to define a form, why because you have to define a form tiddler.
    • 1.1 Not withstanding the above being able to set a form template is important to cover all other cases.
  • 2.0 In your example you default to a table as the form for the resulting fields, this is a useful option but is not necessarily desirable in all cases.

I agree except there a whole suit of small applications where an RDMS is not needed, yet tiddlywiki can offer most of the features of a sophisticated DB.

  • Arguably if designed carefully we could build an export method for import to an RDMS when one outgrows the tiddlywiki native DB

I think if we indicate an “object-type” for a given tiddler, then a default “form definition” will be found and used (this default could be a form similar to your table example if none has being designed. However which form definition is the default could be altered globally, eg the simple and verbose forms, or overridden locally using a “form” custom-field, or a field-list

  • This approach raises the need for a “form editor” however forms are implemented. Ideally using drag and drop reorder, which is why I like the single field form definition above #1
2 Likes
#20

@Scott_Sauyet et al, am I crazy thinking that one tiddler could have more than one object-type and example may be in a book having an object-type=chapter but also making it a todo because it needs further content or editing.

  • I think it makes sense starting with an object-type but perhaps later including object-types
  • Or perhaps todos are handled differently not so much as an object-type but as an action-type or with action-types ie any object can have an action type of todo, date etc…
#21

Well that’s what tagging is about, so of course a tiddler can have multiple roles.

But in any common DB schema, a record belongs to only one table. We don’t have LineItem/Customer hybrids! So no record should have both of those tags.

Still, a Customer tiddler can also have a TODO tag, and maybe some associated fields:

title: Customer/1001
tags: Customer TODO
id: 1001
first-name: Maria
last-name: Anders
city: Berlin
country: Germany
phone: 030-0074321
todo-text: Review for fraudulent transactions
todo-deadline: 20240916120000000

That tag and those fields might appear in templates or various filters. Those would generally be orthogonal to the concerns of our DB-schema.

#22

Of cource you can add multiple SuperTag on a tiddler, so it supports multiple sets of pre-defined fields.

#23

Yes, perhaps, but as I said I don’t want to use tags, as a rule. However I expect there is some design ideas in your solution I can use. An example would be including the superTag output as the content of the form tiddler. And similarly use Smart form plugin, forms using JSON… etc…

  • Keep in mind when avoiding classical tags,
    • It is also possible to use an alt-tag field or list field to enable multiple values in a single field.
    • Tags can indicate what kind of object it is but I would prefer to let the form decide how to indicate its object-type

Note also: I want the automatic forms to make use of the default field definitions and field types which will present different editors eg color/date/tiddler picker etc…

#24

Nope, not crazy.

I have tiddlers tagged as “employee”. Those tiddlers would then have “employee” related widgets for setting values for “employee” related fields.

Some of those employee tiddlers are also tagged as “manager”. Those tiddlers would then also have “manager” related widgets for setting values for “manager” related fields.

The list of managers would show up as one of the employee fields to set the employee’s manager.

In a db, employee would normally be a db table, and manager would ideally be a distinct db table (assuming a bunch of columns specific to managers) with a foreign key column linked to employee.

One tiddler handles both of these tables Cool.

If one really needs to maintain history of employees and managers, then those would be handled by tags like “was employee” and “was manager”.

Something like that…

#25

Thanks for reminding me, each object type can have one or more sub forms, depending on values on the tiddler. In this case the various forms may come from the fact that the object is a contact, an employee and a Manager.

Good catch, as this has architectural implications.

A given object-type may then have a forms-list with the sub forms testing for their own display criteria, eg if a manager

Perhaps there is a primary object-type such as contact but then related object-types or sub-type such as manager ?

  • A given object-type can then permit the addition of relevant sub-types including todo
  • A project object could have reference and project-task sub-types
#26

I know I appear to be talking to myself but I feel with the discussion here I can develop the scope of this project and all your ideas and references.

One feature I wanted to consider was for a given object-type to automaticaly scan all such objects to detect additional fields then add these to the objects definition.

  • This would allow you to add a field to an existing object and it is now available on all of the same objects
    • This is needed to live up to the topics title "
      Rapid Database development tools proposal"
  • If we were using sub-types the fields for the subtype may be included in the current object type. It looks like a conscious intervention may be needed before adding such new fields back to the object-type.

What other tricks can we use to Minimise the build effort ?

  • For example by providing some information or extracting information from the current wiki.
    • Like above, changes to an instance of an object type being considered for use in all with that object-type
    • Provide the name of intended object-types forms subtypes fields and field-types and then automate the pick lists when wanting to make or use them. This allows you to brainstorm/dump design details without having to complete the process for each, and allowing you to continue with the details later.
  • Build the database using a database builder.
#27

Sure, and the tool I mentioned could be modified fairly easily to handle this sort of hierarchical relationships, and even more complex ones where an Employee might have more than one Manager, that latter by doing exactly what we do in other relational databases, introducing an additional table to list the many-to-many relationships. In fact, OrderItems is precisely that sort of table.

I know it may be in some of the posts above, but would you be willing to explain why? My work is assuming that every table has a dedicated tag, and that its Tag Tiddler itself is tagged Table. This locks up the tag namespace only for those table names; and nothing prevents you from using any other tags you like.

This does make the assumption that tiddlers with these tags are mostly just data tiddlers, but it doesn’t prevent you from adding descriptive text fields, additional unrelated fields, or templates more specific to such tiddlers.

It feels flexible and clean, and very much in the spirit of TW to do it this way. What don’t you like about it?

1 Like
#28

It is perhaps an opinion but I like the infrastructure to be hidden, and not apparent and personally I leave tags for ad hoc tagging, used to organise selections or lists of tiddlers.

  • The kind of solution I am discussing could have hundreds of tiddlers AND tags.
  • Since I will provide the tools to finds and list objects I can use fields even field lists (alternative tags) to handle reading the records.

Further considerations object Instance status.

In any database or for any object type one may create there will be new, old, canceled, completed and archive actions which deserve handling and so there is a known set of “status handling” that may be used for all instances of object types.

  • Recently I discovered an idea of keeping a tiddler that is no longer active by renaming it with a system prefix eg $/archived/tiddlername and the tiddler otherwise remains the same.
    • What do we do here with the aforementioned manager or sub-type etc…
    • [Edited] I have my own answer here, the manager will now have some contacts with the $:/archive prefix which are not listed unless looking for archived items. Even if the manager is archived the original relationship is retained.
  • However perhaps a done or other status’s do not involve a rename.
    • [Edited] Objects can have a status but not when taken somewhat “offline” through prefixing. Prefixing in someways removes the item from the table but retains all its last status info, thus one could see it was archived when it was undone, done, or canceled.
    • What is the exhaustive list of appropriate object status’s?
#29

I recently saw mention of @pmario’s KeyValues — advanced data-tiddler functions (wikilabs.github.io) and it leads me to think what if we had a set of operators similar to this, to query the tables implied by the above tiddler based objects?

I think this could be constructed from custom operators that include a table name which is the same as object-type but possibly excludes inactive records/tiddlers. This will list titles that can then be further filtered according to key/title and another to return column/fieldname and values.

#30

Of course it’s an opinion, but that’s a good thing. Differences in opinion is how we ever make progress!

Ahh, and that’s a major difference. I prefer infrastructure to be discreet and unobtrusive, but always visible – well any infrastructure the user might care about. This probably means that my slowly proceeding SQL project will not be complementary to this effort. I wasn’t sure whether or not we’d be able to work them together, but this probably means that we can’t, as I’m very happy with my tag-per-table design. That’s too bad, but not a tragedy. Perhaps if at some point we both come to resolutions on our ideas, we can try again for a meeting of the minds.

That wiki currently has 3183 db-row equvalent tiddlers, but only 5 tables, plus 10 other (non-system) tags. I can easily see useful systems with a dozen or two tables, but if your tables are numbered in the hundreds, I would not expect TiddlyWiki to be a good fit. Do you have any specific schemas you would like to port to TW that have more than say, 25 tables?

Right, it’s not that much harder to use fields for this. But it is harder. While your tools might make that easier, I’m still not seeing a reason—for myself—to move away from tags.

I’ve done the same thing for my own wiki development, but I’ve never thought of it applying to data tiddlers. It makes a great deal of sense to accomplish soft deletes. I might also consider replacing tags (in your case field list values, I guess) with a prefixed name:

title: Customer/123
caption: Jane Doe
tags: Customer NeedsReview

→ 

title: $:/archived/Customer/123
caption: Jane Doe
tags: $:/archived/Customer $:/archived/NeedsReview

Then most of your work can proceed without worrying about the archived ones, but you can easily find them. That sounds like a great approach.

This still has the problem that many DB designs have of showing only the recent change, without a strong sense of history. I’ve been thinking about that problem for my work, but don’t have anything more yet than disconnected thoughts.

#31

Thanks for your feedback @Scott_Sauyet

Keep in mind I said not use tags, not more than that. There still remains available the use of alternative tag fields, and list fields. Personally I envisage that in time a particular object type will have icons, CSS or forms that make it very obvious what they are and I want to leave it up to the designer what they choose to be visible and not-visible. As a rule tags are on the view and edit templates, so are visible.

  • To put it simply we can address your desired visibility without “polluting the tag space”.

Interesting that you ask this, I do not specifically have cases with lots of tables.

  • Although it is quite easy to Imagin one, just take a subject area which may itself have a half dozen objects eg astronomy, then superimpose a task management system for managing the database and you my be approaching a dozen tables.
  • However interestingly, as I have built the infrastructure for similar solutions a few times now, it becomes clear that this can be a “reiterative bootstrapping process”, with a clean and easy way to build “object/tables” it actually is possibly to build the tables to control the infrastructure elements, which are themselves there for building tables.
    • For example you create a contact object, now you can have a table of objects, a table for field definitions, a table of field types, a table of forms, a table of reports… so I am sure this could quickly build the number of tables. Many of which may belong in the “base solution”, as they are essential for the long term viability of such a solution. Why get every one to reinvent the wheel?

It is in my view only because we have not developed the use of fields more, that a resort to tags seem easier. After all we have tag tiddlers, but not field tiddlers. Changing this is in part, what I hope to do because fields/columns are important to a database.

  • Its quite meta but this project is about doing exactly this and extending easy of use of other elements beyond tags.
  • Also from a database logic perspective, tags as we use them are a very loose and flexible and don’t lend themself to forms, validation, order etc…
  • Although system tags have their place as a serving specific purposes non-system tags can quickly wander all over the place and will quickly mix systematic and content tags.
#32

On further reflection it think TiddlyWiki may be a very good fit, because tiddlywiki can have lots of tiddlers and tables are just a logical layer above tiddlers.

  • For example just by adding a field we could define a virtual table of all tiddlers containing that field.

I agree this needs to be part of the future of any such solution. If designed logically I think it could prove trivial to insert in the design, steps to capture each change, even a transaction or one could rollback or reapply.

  • This is why I in part favor a move away from the edit mode (with draft tiddlers) to an update mode where one can selectively edit fields rather than the whole tiddlers, arguably making it easier to track field edits.
#33

We may simply have very different ideas of what sort of databases would be a good fit. I’ve worked on a few projects that have motivated the approach I’m trying to document:

  1. I created a site for the Democratic Town Committee for my small town. While it has a number of plain content tiddlers, the bulk of it is data-driven, with the table/tags Party, Person, Organization, Voter, Lawn Sign, as well as Position and Candidacy – the latter two being many-to-many tables bringing together Person and Organization for similar but distinct reasons.

  2. I have previously mentioned my Chess Club site. This (out-of-date and slightly anonymized) wiki is used by the administrators of a company Chess Club, again has some plain content tiddlers, but is mostly based on our Member, Meeting, and Puzzle tables/tags.

  3. As I join projects or teams at work (including this week, when I shifted departments) I create a wiki to track all the documentation I can find on the project. These are more of a mix of custom content and data tiddlers, but there are table/tags for Document Link, Author, Location (Sharepoint, file share, Confluence, GitHub, GitLab, etc.) Document Type (Video, Web Page, MS Word, Powerpoint, etc.) and Questions.

  4. I’m still in the dreaming stage of this one, but I’m envisioning a wiki to help organize my wife’s horse stable (a full-time business with ~20 horses, ~30 weekly lessons, ~10 weekly training rides.) Here I would expect table/tags such as Horse, Stall, Paddock, Feed, Rider and Boarder (both extending Person), Blacksmith Visit, Veterinarian Visit, and maybe a few more.

What I would like to point out is that these are meant to be—and so far are quite successful at being—full-fledged tools. But none of them has even a dozen tables. While I can certainly imagine larger ones, I feel as though it wouldn’t be long before complexity overtook the simplicity I strive for.

I use tags everywhere. I find that the ability to categorize information in multiple overlapping ways makes for much more resilient systems. TiddlyWiki’s tags simply fit with how I think. And because tags can themselves be tagged, I can mimic hierarchies with them when necessary.

So I never think of “resorting to” tags as a failing. It’s just how I like to work.

I’d be very surprised if it ends up as trivial. The only way I’ve seen to successfully track history in a consistent matter is to go the route of Datomic Database, in which the equivalent of SQL rows are sequences of changes. Nothing is ever deleted, just appended. That’s a very powerful, very useful model (and there are source-code management tools that have the same idea), but these require significantly more storage than SQL databases. That would likely to be a problem for TW.

The building blocks are clearly there, as all the editing widgets allow you to bind to a tiddler/field, and actions can give you much more flexibilitiy.

But I think there is still a storage problem to be solved, and a significant rendering one, if you want to use a delta-model for the data.

I’m not following. Could you expand this idea?

Yes, and that can sometimes become a serious concern. But because I am clearly not envisioning nearly as many table/tags as you are, for me the Table tags are not a significant source of that problem. But I don’t see how enhanced fields solve this problem. I think that tagging my table tags Table gives me all the flexibility I need.