CLI Database Discussions

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

Re: CLI Database Discussions

#241 Post by vevy » Tue Sep 15, 2020 11:14 am

Thanks for the fix.

OK. We are discussing two related but distinct questions:
Is it relevant for CLI?
Is it parentable?
Here is what I think.


Is it Relevant?
Obviously relevant:
Title, Categories, URL, Size, License, Description, Parent ID, Version, Download URL, System Requirements, Additional Statuses, Dependencies, Forum ID, Suggested By, Date.

Field Relevant? Reason Example
Writes settings to Yes? CLI tools can use settings. Some may write settings files by default with usage:
alternative shells, shell extensions, text-mode browsers, etc.
Maybe make the field optional?
Lynx, Bash, Clink.
How to extract Yes Most tools are just plain .exes or in archives, but some tools need special extraction from installers Lynx, Gow.
Icon Nice to have Most don't have icons. Some do. Could be useful. FFmpeg, busybox-w32.
Screenshot Yes? Most don't have "looks". Some do (TUIs, Shells). Also, provides useful usage info. Far Manager, Lynx, Yori shell.
Additional features ? Can be redundant. Leave until accepted replacement is in place.
Path portability ? Don't know enough to have an opinion.
Unicode support ? Don't know enough to have an opinion.
Stealth Yes Many tools leave logs, etc. Clink
Keywords ? Made as a substitute solution. Leave until not needed.
Similar/alternative apps ? Could be useful. Could be redundant.
Use cases Yes 8)




Is it heritable?

Keep a few examples in mind:
  • A busybox applet (touch).
  • A Swiss File Knife internal command (touch).
  • A Yori tool (touch.exe).
Field Independent (I) from parent
Dependent on parent=Heritable (H)
Should be heritable (at least now)? Notes
Title I+H No Possible use for H: Automatically include parent name between brackets touch [Yori].
Categories Mostly I No H is more trouble than benefit.
URL H Yes
Size I+H Yes Search "touch" + filter "<10MB/small" should include all three examples above.
License Mostly H, rarely I Yes
Description I No If part of description is heritable, maybe it needs its own field anyway.
Write settings to I+H No For the H, leave empty or refer to parent. If filtering is needed, it can be complemented with "IsFullyPortable" Boolean.
How to extract Unneeded No Leave empty (ignore #53, make field optional) or refer to parent.
Version Mostly H. Occasionally I No Leave empty. Even if made heritable, provides little value.
Download URL H No Refer to parent.
System Requirements H+I Yes Filter: tools that work on XP.
Icon Probably H+I No Not enough data.
Screenshot I No If it warrants an entry, it can't really be covered by the parent screenshot in 99% of the cases.
Additional Features = Categories
Additional Status Some H, some I No Could be confusing in practice.
Path portability Seems filterable.
Unicode support Seems filterable.
Dependencies H+I No Although potentially filterable (with a Boolean), doesn't seem that useful to filter by (unless separated by multiple Booleans (e.g. is/not .NET dependent etc)
Stealth H+I (Mostly H?) ? Filterable, but too much work to fill in the data :mrgreen:
Keywords I No If it warrants an entry, its keywords can't be exactly as parent.
Forum ID H or I No Little value.
Alternative/similar apps I No If it warrants an entry, ...
Suggested by I or H No
Release date H Yes
Use cases = keywords
Developer/Author field (Required) H Yes Filter/search by developer/distributor/project.
Documentation Link (Optional) H+I No If H, refer to parent.
Documentation Text (Optional) I No If it warrants an entry, ...
IsWarningNet (optional) H+I No Better done manually.
IsWarningSecurity (optional) = IsWarningNet
IsWarningDecompression (optional) = IsWarningNet
IsNotArchiver (optional) = IsWarningNet
IsNotCompressor (optional) = IsWarningNet
Blurb I No
IsInIndex I No
Available in: I No
Notes I No
GUI Front-ends H No Refer to parent
I do NOT have other accounts.

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

Re: CLI Database Discussions

#242 Post by vevy » Tue Sep 15, 2020 11:25 am

I added 3 mini-requests/bug reports to the first post.

To keep things organized, here are the most pressing things for me (to be able to continue):
  • The parent ID preview bug.
  • The additional statuses #43 to #49. Forgot to add #39 ("Win Package", same style as Package or Collection, just the "Win Package" title).
  • The heritable fields.
And a small quality-of-life job: #36 :D

Thanks

PS Just so you know, I have been contemplating suggesting revamping the entry edit page based on related fields/view presentation and better fit things together instead of every little field in a separate line. But first, I'd like to hear from you and other members, especially billon.
For starters, I'd move the release date next to the version field, since both are usually changed together.
I do NOT have other accounts.

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

Re: CLI Database Discussions

#243 Post by Andrew Lee » Wed Sep 16, 2020 4:51 am

The parent preview bug has been fixed.

The following fields will be made inheritable:

- Website URL
- Download URL
- Size
- License
- SysReq

However, that will have to wait for tomorrow as I have run out of steam now :D

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

Re: CLI Database Discussions

#244 Post by vevy » Wed Sep 16, 2020 11:07 am

Andrew Lee wrote:
Wed Sep 16, 2020 4:51 am
The parent preview bug has been fixed.
It still happens for me. Notice that it happens with new entries, not while editing existing ones.
Steps to reproduce:
  • Click add entry.
  • Fill the required fields.
  • Put a number in parent ID
  • Click preview.

By the way, for #36 I meant in the entry view. :mrgreen: See attached image.
However, that will have to wait for tomorrow as I have run out of steam now :D
hug.gif
hug.gif (2.13 KiB) Viewed 102 times
Last edited by vevy on Fri Sep 18, 2020 1:23 pm, edited 1 time in total.
I do NOT have other accounts.

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

Re: CLI Database Discussions

#245 Post by Andrew Lee » Wed Sep 16, 2020 9:42 pm

vevy wrote:
Wed Sep 16, 2020 11:07 am
It still happens for me. Notice that it happens with new entries, not while editing existing ones.
Fixed for new entries.
vevy wrote:
Wed Sep 16, 2020 11:07 am
By the way, for #36 I meant in the entry view. :mrgreen: See attached image.
Updated.

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

Re: CLI Database Discussions

#246 Post by vevy » Wed Sep 16, 2020 9:47 pm

Confirmed. Thanks!


If you don't mind, I'd like to put these two lines at the end of span.status (line 2201); so that the statuses are better aligned with the "Private/Updated/New/Adware" style tag.

Code: Select all

	position: relative;
	top: 2px;
I do NOT have other accounts.

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

Re: CLI Database Discussions

#247 Post by Andrew Lee » Thu Sep 17, 2020 9:48 pm

vevy wrote:
Wed Sep 16, 2020 9:47 pm
If you don't mind, I'd like to put these two lines at the end of span.status (line 2201); so that the statuses are better aligned with the "Private/Updated/New/Adware" style tag.

Code: Select all

	position: relative;
	top: 2px;
Done.

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

Re: CLI Database Discussions

#248 Post by Andrew Lee » Thu Sep 17, 2020 11:05 pm

Andrew Lee wrote:
Wed Sep 16, 2020 4:51 am
The following fields will be made inheritable:

- Website URL
- Download URL
- Size
- License
- SysReq
I have implemented this.

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

Re: CLI Database Discussions

#249 Post by vevy » Thu Sep 17, 2020 11:13 pm

Thanks a lot👍👍

One small, but important thing though. While I think that hiding heritable elements is a neat trick, they shouldn't because sometimes, the child can differ from the parent e.g. license, size, etc. See the table above for the fields that could be independent (I). :D

Field Independent (I) from parent
Dependent on parent=Heritable (H)
Should be heritable (at least now)? Notes
URL H Yes
Size I+H Yes Search "touch" + filter "<10MB/small" should include all three examples above.
License Mostly H, rarely I Yes
System Requirements H+I Yes Filter: tools that work on XP.
Release date H Yes
Developer/Author field (Required) H Yes Filter/search by developer/distributor/project.

So, the following fields should be excluded from hiding (and allowed to have independent data):
Size: for example: Yori is ~2.5MB, but child tool yhash.exe (which inherit license and release date, etc) is 35KB and can be used standalone.
License: Sometimes, child tools can have different license. e.g. util-linux
System Requirements: a specific tool may have particular needs that only exist in certain Windows versions.
Last edited by vevy on Thu Sep 17, 2020 11:32 pm, edited 1 time in total.
I do NOT have other accounts.

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

Re: CLI Database Discussions

#250 Post by Andrew Lee » Thu Sep 17, 2020 11:32 pm

vevy wrote:
Thu Sep 17, 2020 11:13 pm
Thanks a lot👍👍

One small, but important thing though. While I think that hiding heritable elements is a neat trick, they shouldn't because sometimes, the child can differ from the parent e.g. license, size, etc. See the table above for the fields that could be independent (I). :D
Currently the respective values from parent always override child's. That's because the left join always retrieve those inheritable values from the parent.

Is there any way to retrieve values from the parent only if the corresponding child value is NULL?

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

Re: CLI Database Discussions

#251 Post by vevy » Thu Sep 17, 2020 11:35 pm

I edited the post above at the same time.

using the same example from here.
entry_id entry_date parent_id (FK>entry_id)
123 2020 123
124 NULL 123
125 2019 123

Code: Select all

SELECT a.entry_id,
       b.entry_date
  FROM mytable a
       LEFT JOIN
       mytable b ON a.parent_id = b.entry_id
 WHERE a.entry_date IS NULL
UNION ALL
SELECT entry_id,
       entry_date
  FROM mytable
 WHERE entry_date IS NOT NULL
 ORDER BY entry_id;
RESULT:
entry_id entry_date
123 2020
124 2020
125 2019
The last one ignored the parent ID.
I do NOT have other accounts.

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

Re: CLI Database Discussions

#252 Post by Andrew Lee » Fri Sep 18, 2020 3:09 am

Your answer does not seem to be extendable to the case where there are "entry_date_1", "entry_date_2", ..., "entry_date_6", one of more of which could be NULL or not.

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

Re: CLI Database Discussions

#253 Post by vevy » Fri Sep 18, 2020 3:30 am

Andrew Lee wrote:
Fri Sep 18, 2020 3:09 am
Your answer does not seem to be extendable to the case where there are "entry_date_1", "entry_date_2", ..., "entry_date_6", one of more of which could be NULL or not.
Why would there be 6 dates for one entry? :?
I do NOT have other accounts.

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

Re: CLI Database Discussions

#254 Post by Andrew Lee » Fri Sep 18, 2020 2:36 pm

vevy wrote:
Fri Sep 18, 2020 3:30 am
Why would there be 6 dates for one entry? :?
Multiple inheritable fields:

1) website_url
2) download_url
3) download64_url
4) license
5) release_date
6) size

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

Re: CLI Database Discussions

#255 Post by vevy » Fri Sep 18, 2020 4:20 pm

entry_id entry_site entry_size entry_license entry_os parent_id
123 site1 2500 foss 7,8,10 123
124 NULL 100 NULL NULL 123
125 NULL NULL free 10 123
126 site4 500 lite vista NULL
127 mistake 450 free xp 123

Code: Select all

WITH tableSize AS (
    SELECT a.entry_id,
           b.entry_size
      FROM mytable a
           LEFT JOIN
           mytable b ON a.parent_id = b.entry_id
     WHERE a.entry_size IS NULL
    UNION ALL
    SELECT entry_id,
           entry_size
      FROM mytable
     WHERE entry_size IS NOT NULL
),
tableLicense AS (
    SELECT a.entry_id,
           b.entry_license
      FROM mytable a
           LEFT JOIN
           mytable b ON a.parent_id = b.entry_id
     WHERE a.entry_license IS NULL
    UNION ALL
    SELECT entry_id,
           entry_license
      FROM mytable
     WHERE entry_license IS NOT NULL
),
tableOS AS (
    SELECT a.entry_id,
           b.entry_os
      FROM mytable a
           LEFT JOIN
           mytable b ON a.parent_id = b.entry_id
     WHERE a.entry_os IS NULL
    UNION ALL
    SELECT entry_id,
           entry_os
      FROM mytable
     WHERE entry_os IS NOT NULL
),
tableSite AS (
    SELECT a.entry_id,
           b.entry_site
      FROM mytable a
           LEFT JOIN
           mytable b ON a.parent_id = b.entry_id
     WHERE a.parent_id IS NOT NULL
    UNION ALL
    SELECT entry_id,
           entry_site
      FROM mytable
     WHERE parent_id IS NULL
)
SELECT tableSize.entry_id,
       tableSite.entry_site,
       tableSize.entry_size,
       tableLicense.entry_license,
       tableOS.entry_os
  FROM tableSize
       LEFT JOIN
       tableLicense ON tableLicense.entry_id = tableSize.entry_id
       LEFT JOIN
       tableOS ON tableOS.entry_id = tableSize.entry_id
       LEFT JOIN
       tableSite ON tableSite.entry_id = tableSize.entry_id
 ORDER BY tableSize.entry_id;

RESULT:
entry_id entry_site entry_size entry_license entry_os
123 site1 2500 foss 7,8,10
124 site1 100 foss 7,8,10
125 site1 2500 free 10
126 site4 500 lite vista
127 site1 450 free xp
A few notes,
  • This uses CTE or Common Table Expressions. Basically a virtual table based on given conditions.
  • The general formula I used is:
    • Create tables of two columns each: id and each of the needed fields.
    • Do each of these mini tables like the previous idea (select entries where fields is null (inherit) and select where is its not null (don't inherit) and union both).
    • I did this for, entry_size, entry_license, entry_os; since any of these could be independent from parent or inherited.
    • I created one last table (tableSite) where it can only b inherited:
      • If parent_id is not null (i.e. the entry is a child), I took the data from parent and ignored the actual column, even if it had data (This simulates your field-hiding feature).
      • If the parent is null (not a child), I took the data from the actual site column.
      • You can repeat that for the other H-only (must be inherited) fields (Site, Release date, Developer).
    • I then left joined all these two-column mini tables based on the entry_id.
    • So basically, "With A as (such), B as (such), C as (such),... join A,B,C,..."
  • I skipped download URL fields because I think it shouldn't not be inheritable because a lot of download notes/dependencies/warnings are in the main package.

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


If this solution works out, there would be only a couple of minor things from here that maybe you can implement too so that I can resume adding tools:
#39 ("Win Package", same style as Package or Collection, just the "Win Package" title).
Maybe take a look at the posts here and the first post to see if there is something you think is worth doing now?

Thanks
I do NOT have other accounts.

Post Reply