Ideas for future from Jaanus

Here I try to express my ideas for the future of Fabrik in hope that it would be helpful for the further development.

Packages

Today

We have 2 ways to create packages

• we choose lists & forms and export them for other sites
• for own site we can create package only by installing an exported package

Suggestion

Packages are created by default just as db tables (no zip): when creating list & form we create also a new package or choose an existing package under which we create them. So they work also like categories.

We can export each package as zip, including or excluding optionally visualizations. In case when we need (do we?) to choose lists/forms to be included/excluded, we do it for both lists and corresponding forms (not separately).

We can import packages. In case when a package with this name already exists we can choose another name.

Purpose

• better exchangeabilty of solutions created with Fabrik
• easier management of Fabrik list/forms which are categorized

Joins: autocreated table and element names

Today

List joins - when creating a group and then making it as repeated, we get a new table named [your_parent_table_name]_[group_id#]_repeat with elements that we already created for this group plus autocreated field parent_id

Repeatelements - when creating dbjoin/cdd as checkboxes or multiselect, fileupload in ajax mode etc, we get a bridge table named [your_parent_table_name]_repeat_[your_element_name] containing fields parent_id and [your_element_name]

When I want that a repeatelement would be a real bridge between data in two tables

• under one list [table_1] I create element [table_2_id] that creates table [table_1]_repeat_[table_2_id] containing fields parent_id and [table_2_id]
• under second list [table_2] I create element [table_1_id] that creates table [table_2]_repeat_[table_1_id] containing fields parent_id and [table_1_id]
• now I create a third table [table_1_id]_repeat_[table_2_id] with fields id, [table_1_id], [table_2_id], params
• then I replace the table [table_1]_repeat_[table_2_id] with a MySQL view with the same name as SELECT id, [table_1_id] as parent_id, [table_2_id], params FROM [table_1_id]_repeat_[table_2_id]
• and I do respectively the same with [table_2]_repeat_[table_1_id]
• note - as both mysql views are simply querying a single table they are updateable so I can edit and submit new data

Suggestions

Autocreated table name

for group: [your_parent_table_name]_repeat_[user_defined_name]

when [user_defined_name] could be a custom one or the name of an existing db table

for repeatelement: [your_parent_table_pk]_repeat_[your_element_name]

for both: parent id field name = [your_parent_table_name]_[your_parent_table_pk], e.g cities_id or counties_MKOOD , depending on the real name of the existing pk

What if you try to create

• new repeat group under list with main table [your_parent_table_name], you choose your [user_defined_name]. You are about to create a table named [your_parent_table_name]_repeat_[user_defined_name] - and there is already one table named [user_defined_name]_repeat_[your_parent_table_name] (table name elements in reversed order!)
• new repeatelement [your_element_name] under list with main table [your_parent_table_name] or in joined group that belongs to [your_parent_table_name]. You are about to create [your_parent_table_pk]_repeat_[your_element_name] and there is already a table [your_element_name]_repeat_[your_parent_table_pk] (see again the order)

A dialog window asks:

• group: there is already a bridge table between [your_parent_table_name] and [user_defined_name] called [user_defined_name]_repeat_[your_parent_table_name]. Would you still like to create a new one called [your_parent_table_name]_repeat_[user_defined_name]? Options "No, use an existing one", "Yes, create new", "cancel".
• repeatelement: there is already a bridge table between db fields [your_parent_table_pk] and [your_element_name] called [your_element_name]_repeat_[your_parent_table_pk]. Would you still like to create a new one called [your_parent_table_pk]_repeat_[your_element_name]? Options "No, use an existing one", "Yes, create new", "cancel".

Purpose

I think you already guessed the purpose of the proposed changes - reusability of data. It's not impossible today as I explained in an example in section "Today". But with my proposed changes we just can omit most of steps listed there, including creation of MySQL views that (as I read somewhere) some hosts may be restricted to update.

Joins: multiparent list joins

Today

We can already create multiparent list joins so that

• from t1 to t2 from pk1 to fk1
• from t1 to t2 from el2 to fk2 [NOTE - hugh - this won't work, as we assume that one end of any join is a PK]
• from t3 to t2 from pk3 to fk3

and then the dataset in child (t2) looks like

• id fk1 fk2 fk3
• 1 pk1 el2 pk3
• 2 pk1 el2 pk3

that means select * from t1 left join t3 on t3.pk1=t1.pk1 left join t2 on (t2.fk1=t1.pk1 AND t2.fk2=t1.el2 AND t2.fk3=t3.pk3) . Let's call it "method AND"

We can't create with the same method the joins that would work rather as left join t2 on (t2.fk1=t1.pk1 OR t2.fk2=t1.el2 OR t2.fk3=t3.pk3). Let's call it "method OR". But in many cases we would need such data set:

• id fk1 fk2 fk3
• 1 pk1 NULL NULL
• 2 NULL NULL pk3
• 3 NULL el2 NULL

If we need such approach we should use more clever ways and turn again towards MySQL views.

Example (that works in reality):

An online registry contains musical and literatural works (incl song lyrics, sacred texts used in music ect). All main entries of works are in table work_main (where work_type_id equals respectively 1 or 2). Version data for musical works are built in work_music, literature data in work_lit, author data in work_authors. Additionally, we use work_music_title_lang as a bridge between data for musical and literatural works (work_main_id and text_main_id are both pointing to work_main.id, work_lit_id points to work_lit.id). There are more tables involved but for this example it's enough

Musical works main data:

select * from work_main LEFT JOIN work_music_title_lang ON work_music_title_lang.work_main_id = work_main.id LEFT JOIN work_music on work_music.work_main_id = work_main.id LEFT JOIN work_authors on work_authors.work_main_id = work_main.id WHERE work_main.work_type_id = 1 (here a default role is composer)

Musical works version data: select * from work_music LEFT join work_authors on work_authors.work_music_id = work_music.id (here a default role is arranger)

Literatural works main data

select * from work_main LEFT JOIN work_lit on work_lit.work_main_id = work_main.id LEFT JOIN work_authors on work_authors.work_main_id = work_main.id [LEFT JOIN work_music_title_lang ON work_music_title_lang.work_main_id = text_main.id ] WHERE work_main.work_type_id = 2 (here a default role is text author)

Literatural works version data

select * from work_lit LEFT JOIN work_authors on work_authors.work_lit_id = work_lit_id (default role - translator)

When an user submits new musical work he can choose corresponding literatural work in joined group (work_music_title_lang). But what if the needed literatural work is not available there? We should then submit one. We can do it via frontend add of dbjoin element.

But here is a problem - sometimes the popup form produces a js error when submiting repeated joined data there. Anyway - it would be more convenient to submit also text author data via the musical work form. Unfortunately, as said, we can't do it using work_authors again as child table. Now is the time to create 2 MySQL views - work_authors_lit_main and work_authors_lit, both as select * from work_authors . And 2 new list joins:

• from work_music_title_lang to work_authors_lit_main from text_main_id to work_main_id
• from work_music_title_lang to work_authors_lit from work_lit_id to work_lit_id (use of words "from" and "to" is correspondending to the list admin where "to" means foreign key).

We make them repeated and in group admin we set minimum repeats to 0.

Now - when we submit new musical work and

• there already exist a needed literatural work then we need only to select it in the dbjoin dropdown and do nothing else. In authors sections we just submit a composer and don't touch the literatural author groups that are closed.
• When needed text doesn't exist (let's say even no main data) then we open a frontent add popup, inside the popup another frontend add popup for parent data... when this all is submitted we fill the current form AND we add also text author (and if needed, translator) in corresponding groups.

after this new musical work is submitted -

• whoever is an administrator of literatural data, he/she sees that a new work is submitted and could verify whether the author data is full and correct
• the text authors are displayed also with the musical work data

Suggestion

We obviously need an option to choose whether the list join multiple parent -> single child is "method AND" or "method OR".

• When we choose the "method OR" then we would also need to choose the element that says us which fk element should be active for this joined data row.

Purpose

Makes possible the use of more complex data manipulation without leaving the current form. Eliminates the need for MySQL views in form submission.

(will be completed/continued)