CLI Database Discussions

Discuss anything related to command line tools here.
Message
Author
vevy
Posts: 572
Joined: Tue Sep 10, 2019 11:17 am

Re: CLI Database Discussions

#271 Post by vevy » Wed Sep 23, 2020 8:36 pm

I think I can work with that.
Child Field Type Edit State If enabled+empty If enabled+filled In display
URL H Must be disabled Use parent's Ignore and use parent's. Display parent's or disable. Non-filterable.
Size H+I Enabled Use parent's Use child's View. Filterable.
License H+I Enabled Use parent's Use child's View. Filterable.
Runs On H+I Enabled Use parent's Use child's View. Filterable.
Release date H Must be disabled Use parent's Ignore and use parent's. View. Filterable.


So, in all five, if empty>use parent's value.
But if filled:
  • in the case of URL and release date: Since these should be the same as the parent if the tools is really a child tool,
    • ignore entered data as a mistake (or drop on upload).
    • automatically disable/hide the edit field once there is a value in the parent ID.
  • with size, license and runs on, use the entered data. So don't hide if there is a value in parent ID.



Andrew Lee wrote:
Wed Sep 23, 2020 6:08 pm
2) No extra "Use parent" checkbox. If the field is blank, it will take on parent's value automatically. If there is really a need next time to force child field value to be blank (despite parent's value being populated), we can always use some indictor, like a single dash to indicate that.
What would be a case where we'd need a blank heritable field? I can think of:
  1. empty child > always use parent. Example, URL. (and just hide the field upon filling parent ID as described.)
  2. empty child > never use parent. Can't think of a use case?
  3. filled child > always use parent. Example, URL (First case if the field was not hidden).
  4. filled child > never use parent. Example, size, license, runs on.
The checkbox/radio button was to solve case 4, where both fields (parent ID, heritable field) had data. Case 4 can be handled by the UI for example.



------------------------------

There could be a relatively lightweight solution to make the query less convoluted:
entry_id entry_site entry_release_date entry_size entry_size_ref entry_license entry_license_ref entry_runs_on entry_runs_on_ref parent_id parent_id_exposed
11 site11 2020 1111 11 free 11 10 11 11
12 mistake 1222 12 11 11 11 11
13 2019mistake 11 foss 13 11 11 11
14 site14 2018 1444 14 public domain 14 xp 14 14

QUERY

Code: Select all

SELECT a.entry_id,
       b.entry_site,
       b.entry_release_date,                      --notice also b
       c.entry_size,
       d.entry_license,
       e.entry_runs_on
  FROM mytable a
       LEFT JOIN
       mytable b ON a.parent_id = b.entry_id
       LEFT JOIN
       mytable c ON a.entry_size_ref = c.entry_id
       LEFT JOIN
       mytable d ON a.entry_license_ref = d.entry_id
       LEFT JOIN
       mytable e ON a.entry_runs_on_ref = e.entry_id;

RESULT
entry_id entry_site entry_release_date entry_size entry_license entry_runs_on
11 site11 2020 1111 free 10
12 site11 2020 1222 free 10
13 site11 2020 1111 foss 10
14 site14 2018 1444 public domain xp


  • Have all entry with two "parent ID" fields: the regular called; e.g, (parent_id_exposed) and the other, not directly exposed in the UI; see later.
  • Add new second reference fields for all heritable/independent (H+I) fields (currently 3: size, license, runs on)
  • For the always heritable fields like URL, release date, no change is made.
  • For a parent/regular entry, the regular Parent ID field will naturally be empty. In this case, during upload or adding to the SQL database, automatically assign its own entry id (e.g. 11) to the three reference fields from the previous point and the non-exposed entry_id i.e. it references itself. The exposed one can be empty since it is not entered in the first place. The point of hiding the other parent id is not to confuse the user.
  • For a child entry, if a field (e.g. license) is left empty, automatically enter the parent id (e.g. 11) into the license_ref field on upload.
  • If data is entered in that field, enter the child's own id into that license_ref field (e.g. 12).
TLDR; this way all the problematic fields are always referenced for every entry, which greatly simplifies user queries.

This also allows us to keep the data entered in the always H fields since we can easily ignore them. No real need for dropping or hiding except cosmetically.
I do NOT have other accounts.

User avatar
Andrew Lee
Posts: 2468
Joined: Sat Feb 04, 2006 9:19 am
Contact:

Re: CLI Database Discussions

#272 Post by Andrew Lee » Sat Sep 26, 2020 11:51 pm

The implementation of inheritance using shadow table is done.

Please report any bugs.

vevy
Posts: 572
Joined: Tue Sep 10, 2019 11:17 am

Re: CLI Database Discussions

#273 Post by vevy » Sun Sep 27, 2020 2:16 pm

Neat! Thanks!

What solution did you use?

Bug report:
Inherited "Release date" shows in preview but not in view.
I do NOT have other accounts.

User avatar
Andrew Lee
Posts: 2468
Joined: Sat Feb 04, 2006 9:19 am
Contact:

Re: CLI Database Discussions

#274 Post by Andrew Lee » Mon Sep 28, 2020 2:11 am

vevy wrote:
Sun Sep 27, 2020 2:16 pm
Neat! Thanks!

What solution did you use?
What you call "caching", but as I mentioned earlier, I think a better terminology would be "shadow" table.
vevy wrote:
Sun Sep 27, 2020 2:16 pm
Bug report:
Inherited "Release date" shows in preview but not in view.
Thanks. Fixed!

vevy
Posts: 572
Joined: Tue Sep 10, 2019 11:17 am

Re: CLI Database Discussions

#275 Post by vevy » Mon Sep 28, 2020 2:13 pm

Andrew Lee wrote:
Mon Sep 28, 2020 2:11 am
vevy wrote:
Sun Sep 27, 2020 2:16 pm
Neat! Thanks!

What solution did you use?
What you call "caching", but as I mentioned earlier, I think a better terminology would be "shadow" table.
I meant the SQL relationships.
Thanks. Fixed!
Can confirm.

----------------
Maybe #54 next? Or the mini-requests? :mrgreen:
I do NOT have other accounts.

User avatar
Andrew Lee
Posts: 2468
Joined: Sat Feb 04, 2006 9:19 am
Contact:

Re: CLI Database Discussions

#276 Post by Andrew Lee » Mon Sep 28, 2020 2:52 pm

vevy wrote:
Mon Sep 28, 2020 2:13 pm
I meant the SQL relationships.
Not sure what you mean by "relationship". Each entry has a parent id. Fields are set to NULL if inheritance is required. A set of SQL statements populate a shadow table with all the inherited field values resolved. This is done only after an entry has been edited.
vevy wrote:
Mon Sep 28, 2020 2:13 pm
Maybe #54 next? Or the mini-requests? :mrgreen:
No problem. I am always going through my todo list.

vevy
Posts: 572
Joined: Tue Sep 10, 2019 11:17 am

Re: CLI Database Discussions

#277 Post by vevy » Mon Sep 28, 2020 3:01 pm

Andrew Lee wrote:
Mon Sep 28, 2020 2:52 pm
vevy wrote:
Mon Sep 28, 2020 2:13 pm
I meant the SQL relationships.
Not sure what you mean by "relationship". Each entry has a parent id. Fields are set to NULL if inheritance is required. A set of SQL statements populate a shadow table with all the inherited field values resolved. This is done only after an entry has been edited.
Weren't we discussing how to make the SQL referencing and query reasonable? Did you use one of these? A third one? Just curious to see which was more feasible in practice, made more sense to you, etc.
No problem. I am always going through my todo list.
👍
I do NOT have other accounts.

User avatar
Andrew Lee
Posts: 2468
Joined: Sat Feb 04, 2006 9:19 am
Contact:

Re: CLI Database Discussions

#278 Post by Andrew Lee » Tue Sep 29, 2020 2:30 am

vevy wrote:
Mon Sep 28, 2020 3:01 pm
Weren't we discussing how to make the SQL referencing and query reasonable? Did you use one of these? A third one? Just curious to see which was more feasible in practice, made more sense to you, etc.
I ended up using a single left join to retrieve both child and parent columns, then go through the rows and construct a single insert/update to refresh the shadow table. Much simpler that way.

User avatar
Andrew Lee
Posts: 2468
Joined: Sat Feb 04, 2006 9:19 am
Contact:

Re: CLI Database Discussions

#279 Post by Andrew Lee » Tue Sep 29, 2020 2:58 am

I have implemented #53 and #54 as macro links below the "How to extract" field.

While I understand the benefits of tags, I think it changes the conversational nature of contributing to the database and makes the entire experience too constrained/restrictive.

vevy
Posts: 572
Joined: Tue Sep 10, 2019 11:17 am

Re: CLI Database Discussions

#280 Post by vevy » Wed Sep 30, 2020 5:08 pm

Andrew Lee wrote:
Tue Sep 29, 2020 2:30 am
I ended up using a single left join to retrieve both child and parent columns, then go through the rows and construct a single insert/update to refresh the shadow table. Much simpler that way.
I see (I think!).

You mean #52 and #53? #54 is the unexpanded tag.😉

I kinda get what you mean, but I am worried about having to perform the same change many times or leaving things inconsistent. 🤔

How about adding it as an option? A contributor can choose. Like here:
unexpanded.png

I think the icon should be inherited too.
I tried to see if the Open source/Public domain status (checkbox) is inherited, but when I searched, checking the Open source box (in search) doesn't cause an effect. It should filter out any tool that is not FOSS/PD.

I'll update the first post with these (and other) requests. Update: Added #55 to #67

(Andrew: Strikethrough implemented. Color is discouraged.)
Why is color discouraged?
I do NOT have other accounts.

User avatar
Andrew Lee
Posts: 2468
Joined: Sat Feb 04, 2006 9:19 am
Contact:

Re: CLI Database Discussions

#281 Post by Andrew Lee » Wed Sep 30, 2020 6:14 pm

vevy wrote:
Wed Sep 30, 2020 5:08 pm
How about adding it as an option? A contributor can choose. Like here:
I understand your fixation on consistency and uniformity, but I am personally against the idea of having specialized content tags like these.
vevy wrote:
Wed Sep 30, 2020 5:08 pm
I think the icon should be inherited too.
I tried to see if the Open source/Public domain status (checkbox) is inherited, but when I searched, checking the Open source box (in search) doesn't cause an effect. It should filter out any tool that is not FOSS/PD.
OK, noted.
vevy wrote:
Wed Sep 30, 2020 5:08 pm
Why is color discouraged?
Ever been color-blind? :D
Plus I don't want things to get too fancy.

vevy
Posts: 572
Joined: Tue Sep 10, 2019 11:17 am

Re: CLI Database Discussions

#282 Post by vevy » Wed Sep 30, 2020 6:19 pm

Andrew Lee wrote:
Wed Sep 30, 2020 6:14 pm
I understand your fixation on consistency and uniformity, but I am personally against the idea of having specialized content tags like these.
Would you also be opposed to entering the reference text as such button (or even manually)?

Code: Select all

[url=https://www.portablefreeware.com/forums/viewtopic.php?t=25028&p=97047#p97047][size=2][color=grey][u]Standard extraction[/u][/color][/size][/url]
vevy wrote:
Wed Sep 30, 2020 5:08 pm
Why is color discouraged?
Ever been color-blind? :D
Plus I don't want things to get too fancy.
I see! I mainly use it for grey, and such.
I do NOT have other accounts.

User avatar
Andrew Lee
Posts: 2468
Joined: Sat Feb 04, 2006 9:19 am
Contact:

Re: CLI Database Discussions

#283 Post by Andrew Lee » Wed Sep 30, 2020 8:18 pm

vevy wrote:
Wed Sep 30, 2020 6:19 pm
Would you also be opposed to entering the reference text as such button (or even manually)?

Code: Select all

[url=https://www.portablefreeware.com/forums/viewtopic.php?t=25028&p=97047#p97047][size=2][color=grey][u]Standard extraction[/u][/color][/size][/url]
I prefer to have the full text of the procedure, rather than a link to the procedure, as what I have done for the macro link itself.

Actually, if the extraction procedure field is not that applicable for CLI tools, we can get rid of it altogether, and insert exceptions in the description.

I am always of the belief that "less is more".

vevy
Posts: 572
Joined: Tue Sep 10, 2019 11:17 am

Re: CLI Database Discussions

#284 Post by vevy » Wed Sep 30, 2020 8:25 pm

Hmm. Interesting. How about making the extraction field optional, so as not to crowd the description more than it already is?
I do NOT have other accounts.

User avatar
Andrew Lee
Posts: 2468
Joined: Sat Feb 04, 2006 9:19 am
Contact:

Re: CLI Database Discussions

#285 Post by Andrew Lee » Fri Oct 02, 2020 6:23 pm

Extraction and settings fields are now optional.

Post Reply