CLI Database Discussions

Discuss anything related to command line tools here.
Message
Author
User avatar
Andrew Lee
Posts: 2468
Joined: Sat Feb 04, 2006 9:19 am
Contact:

Re: CLI Database Discussions

#256 Post by Andrew Lee » Fri Sep 18, 2020 7:55 pm

I think this solution is too heavy and not scalable. When doing search results or category listings, a whole bunch of rows are retrieved, not just one. Imagine doing the above, then tag on a couple of additional criteria to retrieve a list of matching entries. Then scale this up to multiple searches/filtering. I am not sure my tiny VPS is going to be able to handle it!

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

Re: CLI Database Discussions

#257 Post by vevy » Fri Sep 18, 2020 8:05 pm

Andrew Lee wrote:
Fri Sep 18, 2020 7:55 pm
I think this solution is too heavy and not scalable. When doing search results or category listings, a whole bunch of rows are retrieved, not just one. Imagine doing the above, then tag on a couple of additional criteria to retrieve a list of matching entries. Then scale this up to multiple searches/filtering. I am not sure my tiny VPS is going to be able to handle it!
How about the caching idea?
Get an export table/database and feed the site forms/queries from it. Only update the query results when entries are added/updated.
Like, the second (result) table above could be be the database/table that is actually getting queries by the users.
I do NOT have other accounts.

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

Re: CLI Database Discussions

#258 Post by vevy » Fri Sep 18, 2020 8:10 pm

I think these are called virtual tables.
https://www.quora.com/What-is-a-virtual-table-in-SQL

--------------------
However, you can create a unique clustered index on the view – referred to as an indexed view – to persist the data on disk. This index can then be used for reads, reducing the amount of I/O.
http://www.brentozar.com/archive/2013/1 ... xed-views/

Emphasis mine.
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

#259 Post by Andrew Lee » Fri Sep 18, 2020 8:23 pm

vevy wrote:
Fri Sep 18, 2020 8:05 pm
How about the caching idea?
Get an export table/database and feed the site forms/queries from it. Only update the query results when entries are added/updated.
Like, the second (result) table above could be be the database/table that is actually getting queries by the users.
That is one possible solution. But that's going to mess with other subsystems eg. changelog.

I am also mulling over exposing the entire left join to the front end (which will have to do something about it).

Whatever the case, it is going to take a lot more work than I expected because neither the existing backend or frontend were structured for this, so we are now tacking on a hack job.

The decision to make now is which hack job is least painful and more sustainable going forward. :roll:

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

Re: CLI Database Discussions

#260 Post by vevy » Fri Sep 18, 2020 8:27 pm

Andrew Lee wrote:
Fri Sep 18, 2020 8:23 pm
I am also mulling over exposing the entire left join to the front end (which will have to do something about it).
I don't get that part.
The decision to make now is which hack job is least painful and more sustainable going forward. :roll:
I think maybe a checkbox per independent/heritable (the three ones that could be either way: size, license, runs on) field that says "Use parent".
For the must be heritable (date, website, dev), use the parent id.
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

#261 Post by Andrew Lee » Fri Sep 18, 2020 8:35 pm

vevy wrote:
Fri Sep 18, 2020 8:27 pm
Andrew Lee wrote:
Fri Sep 18, 2020 8:23 pm
I am also mulling over exposing the entire left join to the front end (which will have to do something about it).
I don't get that part.
I mean, get both the parent and child field eg. parent.release_date and child.release_date. Then frontend has to decide what to do with the information.

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

Re: CLI Database Discussions

#262 Post by vevy » Fri Sep 18, 2020 8:42 pm

I wrote this before I saw your post:

I am going to put some ideas out there and we can sieve them:
- put the limit in UI: a radio button instead of a checkbox.
- two fields for each: one a reference and one for actual data.
- special text like app=xx in the fields
- put the referenced values (of the parent) as static strings. Only when the parent is updated do you run a script to update children (based on which entries have fields with their "use parent" checked.

Is something here is what you were referring to?



Also, maybe run tests. Maybe the query isn't so heavy?
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

#263 Post by Andrew Lee » Fri Sep 18, 2020 8:55 pm

Thanks for all the suggestions. I will do some tests and try to figure out which is the best path to go.

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

Re: CLI Database Discussions

#264 Post by vevy » Fri Sep 18, 2020 8:57 pm

Andrew Lee wrote:
Fri Sep 18, 2020 8:55 pm
Thanks for all the suggestions. I will do some tests and try to figure out which is the best path to go.
My pleasure. Good luck and thanks for the openness and help.

Quick mockup for a possibility:
radio.png
I do NOT have other accounts.

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

Re: CLI Database Discussions

#265 Post by vevy » Mon Sep 21, 2020 5:24 pm

A quick thing: the entry changelog (i) button seems to have disappeared with the latest UI tweaks.
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

#266 Post by Andrew Lee » Tue Sep 22, 2020 1:23 am

vevy wrote:
Mon Sep 21, 2020 5:24 pm
A quick thing: the entry changelog (i) button seems to have disappeared with the latest UI tweaks.
Fixed.

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

Re: CLI Database Discussions

#267 Post by vevy » Tue Sep 22, 2020 2:27 am

👍

Any news?
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

#268 Post by Andrew Lee » Wed Sep 23, 2020 12:51 am

I am assuming you are asking about progress on the parent-id feature.

Time is a little tight this week, and I am slowing chipping at it. Hoping to finish it when I get more hours during the weekends.

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

Re: CLI Database Discussions

#269 Post by vevy » Wed Sep 23, 2020 4:08 pm

Hoping for the best.

Meanwhile, can you give me anything that works so that I can continue adding while you work things out?

I mean something that doesn't have to look good or anything, but we can easily move the data from once you are finished.

No pressure, just asking if it is possible. :D
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

#270 Post by Andrew Lee » Wed Sep 23, 2020 6:08 pm

You can already use the parent ID feature as is now, just that all inherited fields will automatically take on the values of the parent during display. The actual value in the child is still preserved.

Once everything is done, the child value (if populated) will _not_ take on parent's value during display.

So I would say, go ahead. Nothing will be broken even with the new code in place.

There are 2 things I have pretty much decided on after last night:

1) Will use the caching approach. Actually, I think a better terminology might be a "shadow" table.

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.

Post Reply