Rapid Database development tools proposal

#10

My Kansas Railroad wiki has a kind of database structure. See Kansas Railroads (note that there are no images included because of space limitations of tiddlyhost.

I’m not a programmer nor am I a database expert so there is probably a lot done in this wiki that is not ideal. Maybe this could be used as an example either way?

Also, any comments are welcome.

1 Like
#11

Here is a DB example from TW classic:
https://baggr.tiddlyspot.com/
OR
https://twdb.tiddlyspot.com/

1 Like
#12
1 Like
#13

I would like to start this process by introducing another mode to complement view and edit. This modes working title would be update and it would start with allowing the user to select an update button just as they do edit, however the only field available initially to edit is the text field.

  • In someways this is a stream of thought reply

This update mode would be where we introduce editable forms also visible in the view template, but leaving the edit mode as is for the designer to modify the tiddler, and any field.

  • In some wikis the edit mode would not be available for users, only the update mode.
  • Is the update form a rework of the view template, with no draft tiddler or the edit mode with a draft tiddler and done button?
    • Arguable there are two possible update modes, one edit template which could use a customised edit, one based on the view template where each field provides its own view/edit mode.
  • Tagging tools, title renames and field creation/edits can be selectively be made available to the update modes, even view template.
#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.