Cell limited preloaded values - better handling

[pawelsalawa]

Details

Cells with preloaded data limited should have better handling. A lot of people are confused by truncated value to 100 characters. It should either read full value if column is expanded, or perhaps show some kind of notification about limited values. Maybe even something else?

[protegesolutions]

It could simply be a setting with a limit (say, 500 chars), and maybe a warning about settings it too high. Having no control and no info about this is quite confusing, I think.

[fitdev]

Is there no way at all to get the full value, if it exceeds 100 chars? I thought it applies to Grid views only, but the same thing happens with Form view. I second the opinion of @protegesolutions that there should be a setting for that. But in addition, it would be really nice if there was a way to also get the full value (not necessarily as a Grid View), but maybe directly to text or something similar. At least that way full data can be accessible from within the program.

[pawelsalawa]

1. It does apply to grid view only. Form view always loads full data. Please check again. I just verified it to be sure and it does load full data in form view.
2. In Grid View it will load full data if you enter Editing value, because you do need full previous value in order to update it properly, so editing it is a way to get full value.

This limitation is introduced to protect memory usage (imagine table with 5 columns, 1000 rows, each cell having 1MB od data = 5 * 1000 MB = 5GB of RAM). I implemented it, because some users actually reported this case to happen.

Regardless of above, as this issue is stating, I'm planning on making it better.

[fitdev]

Thank you for your explanation and for all your hard work. This is the best SQLite Manager! Didn't know about editing the value trick.

The limitation makes sense of course. Perhaps the way to handle it would be to silently load full data only for selected row(s) (controllable via an option). At least that would take care of the common copy-paste usage case, so that a selected value can be copied to clipboard in full, even if only a fraction of the text is actually visible inside the grid view cell.

As for the Form View, unfortunately this is not my case with 3.2.1:

1. I go to Open SQL Editor
2. I type in my SELECT query
3. I run the query and get results in grid view by default, where TEXT values are truncated
4. I switch to Form View tab, and the values are still truncated!
5. In the form view, I navigate to the next/previous record, refresh data - the values still stay truncated, even though they are being displayed in Text Editor control, not a grid view cell.

[pawelsalawa]

Can you show me the exact SELECT query you're using for this?

[fitdev]

select count(Messages.id) as count, UserID, Url from Messages
inner join Profiles on Messages.UserID=Profiles.UserID
 where Messages.Kind='Test' group by Messages.UserID order by count desc
 limit 10

In this case Url gets truncated to 100 chars even in Form view.

[pawelsalawa]

I see. The use of "GROUP BY" clause prevents SQLiteStudio from complementary loading of full value of particular cell (as ROWID is ambigous in GROUP BY).

Now... what could be done in this scenario? The problem here is that on one side we want to limit size of preloaded values due to memory limitations in some cases (like I described 4 posts above). Then on the other side this optimization fails to load full value in case of DISTINCT, or GROUP BY, or expression columns, because in these cases it is no certain what the ROWID is to load data for.

Any ideas on how to load full value for such cell?

I suppose repeating entire original query and wrapping it up with another level of outer SELECT would work, because we could refer to the cell's row by its index (using OFFSET and LIMIT), but I'm not sure how performant would that be. Still it could be the best effort solution available. Unless someone can think of better way?

[fitdev]

Thank you for this explanation. I did not think that having GROUP BY would change things significantly.

In my view the best, and probably easiest to implement, solution would be to give user full control over whether or not and to what degree to truncate cell values - done via configurable option (either on a program level, or on a per-query/view level - via toggle / dropdown: "Truncate to: 100, 1000, 10000, OFF").

Perhaps the only time truncation should be forced is when dealing with really large values, say well in excess of 1M.

After all user normally knows (or at least can guess) how much data will be involved and whether or not there is sufficient RAM. Besides, there is already a setting to limit the number of rows displayed in the Grid, so cautious users can set it to be pretty low (like 50 for example) to really make sure they won't run into memory issues when loading unknown data with cell value truncation turned off.

This approach (giving control over truncation to the user) would significantly increase program's usefulness, as 100 chars limit is just way too low: Urls, tweets, file paths, short descriptions can all be easily a few 100 chars long, so 1000 char limit, in my view would be the smallest viable alternative. Also, offering 64-bit version would also ease memory requirements.

[pawelsalawa]

I agree. Having it configurable (and default value at 1000 or even more) makes perfect sense. That's both simple and efficient way and that's how it's going to be implemented.

[LotCooler]

An idea of a temporary workaround (or even as a permanent solution that would be fine with me), you could set the display to show something like "..." or maybe "<...>" at the end of any column that has the data truncated...so at least you know to open in form view if you want to see the whole thing. I think as long as we know when it is occurring, we can deal with it.

[fitdev]

Or perhaps data can be dynamically loaded on demand. For instance, clearly just for display purposes a few hundred characters in nearly always enough. However when you navigate to the cell to: edit data, copy data to clipboard, or resize cell to view more of its contents - that's when the entire data is needed.

So perhaps, the DataGrid control can be modified such that when full data is needed (cell enters edit mode, or Ctrl-C is pressed on the cell / row / rows), it would fetch the entire data under the covers.

My main concern is how to always be able to easily get to underlying data in Grid mode (Form mode is not always available).

[pawelsalawa]

Actually it is currently implemented in this very way - it loads full contents if it's necessary (for example when editing its value, or copying contents), whereas only first 100 characters are preloaded for initial display.

Problem is that it's not "fully loaded" when user resizes (enlarges) the cell view and I was thinking about addressing it, but it's not easy to determin when the data should be loaded, because font is not regular (thus different characters can result in different width), sometimes data contains newlines, sometimes binary (not printable) data. Yet another problem is that if you resize 1 column and you have 1000 rows loaded, you have to track 1000 cells at the same time, where resizing action performed by user produces lots of events in the application. Multiply it by 1000 and it becomes clumsy.

I'm thinking about adding sort of "..." at the end as @fitdev mentioned, but I would also make it as a clickable button or a link, so user can explicitly request loading full contents if they are pending.

I'm open for more ideas. Maybe someone has even better ones.

[fitdev]

@pawelsalawa
I was not aware that it already works this way, since copying the selected cell's value never returned a full value in my case, so I had to check the actual database contained the data in other ways (programmatically). Such is the case with v. 3.2.1 at least for me.

I don't think the approach of accurately calculating the number of characters to load based on column width is feasible. It's too much work and complicated. I do like your idea of adding a clickable button or link at the end in all cells with truncated values. Perhaps that with 2 menu items - Copy and Edit would be enough. Whereby Copy would copy the entire contents to clipboard, while Edit could open it up in a new window / tab for editing.

However I think the ability to select rows and be able to copy them in full is still useful. So perhaps it, too, can be addressed but at some later point.

[DingBEN]

I have been having the same battle...
Well, Mr. pawelsalawa is very restricted on time, yet these discussions for every little detail back and forth on a weak implementation will eat more time than to make a plan to code a new implementation.

I have columns that store books of crap... and I am small potatoes.
...in other issues I have mentioned a few hints as to how to handle some of this - globally - uniformly - and intuitively.

The FORMview though is better than none, is really cumbersome, having to constantly resize the cells is murder, trying to resize a CELL when towards the bottom of the screen is a down-right-mess.
Hardly a form will fit the screen real-estate anyhow, so we are reduced to a vertical layout that spreads the columns(CELLS) in a conundrum of thicknesses with yet more buttons repeated at each unit!

The current implementation needs to be scrapped, period! But not until a better solution is implemented.
Ween yourself of the pigeon-hole MS model, and start thinking browser like implementations.

There are basically 2 choices

1. keep the FROMview, but allow the user to drag columns in the layout and allow sizing of each input box, and store the template, so can be recalled(dropdown)... make sure layout is scrollable (vertical power), avoid panning approaches at all**(none)**.

2. Get rid of FORMview altogether.

• Data always lands in dataGRID format.
• on the double-click already used to open cell for editing overlay a panel that fills the data area taking maximum real-estate with double-clicked-cell's data in it.
• make it so it can be dragged out of the way to see under-lying grid data for reference.
  ~ no flow-murdering pointing-dragging handles.
  ~ no having to go to a different view that needs to be scrolled, sized, battled with!
• double-click anywhere on the panel to close it
  done... works for all editing.

Bells and whistles

• quick 3 way toggle to set 3 pre-set sizes
• (vertical power) while panel is up allow changing of column/CELL by pointing to columns via the database tree.
• possibly make it so can be split vertically and/or horizontally so can use near row data if the case

[fitdev]

Perhaps Form View can be better utilized / used as a Details view in the Master-Details paradigm?

Thus, it could co-exist within the same window, or inside a new detachable window (in case of multiple screens) and be automatically updated for every selected row in the grid view.

Of course whether or not such a Form view would support editing will depend on how the grid view was generated. If it was part of a select query (as opposed to being a direct table edit), then it should be read-only. Otherwise, it could act as a true form for data entry.

With this approach full data should be loaded for the currently selected row only.

[Boogier]

Hi all

I would like to vote for adjustable string length for the grid view. This value can be put into options (configuration dialog) to give a user ability to adjust the view according to his needs. Not everybody works with 10MB strings, but it would be good to see more than 100 characters in the grid in many cases.

[LaraSQP]

Please address this issue.

And, btw, thank you for taking the time to develop SQLiteStudio and for making it available for free. It's a must-have. Best of its kind, imo.

[pawelsalawa]

Note - once fixing this, it should be checked whether the FK editor (ComboBox) in grid view inherits fixed behavior (it should, but better verify this).

[tedstriker2]

"Load full values" isn't working for me in version 3.3.0. Here's my query:

SELECT
    dtc.tbl_name,
    'DELETE FROM '
    ||dtc.tbl_name
    ||' WHERE EXISTS (SELECT 1 FROM dmz_ent de WHERE de.'
    ||dtc.col_name
    ||' = '
    ||dtc.tbl_name
    ||'.'
    ||dtc.col_name
    ||' AND de.'
    ||dtc2.col_name
    ||' = '
    ||dtc.tbl_name
    ||'.'
    ||dtc2.col_name
    ||' AND de.obsolete = 1)'
    ||';' del_sql,
    1 severity
FROM dmz_tab_cols dtc
JOIN dmz_tab_cols dtc2
ON
    dtc2.tbl_name = dtc.tbl_name
    AND dtc2.col_name = 'orig_source'
WHERE
    dtc.tbl_type = 'table'
    AND dtc.col_name = 'orig_ent_id'
    AND EXISTS (
        SELECT
            1
        FROM dmz_ent de
        WHERE
            de.obsolete = 1
    )
ORDER BY
    dtc.tbl_name = 'dmz_ent',
    dtc.tbl_name
;

[pawelsalawa]

What do you mean it does not work?

1. What exactly are you doing?
2. What is expected result?
3. What is actual result?

[tedstriker2]

In your previous comment you wrote:

there is an indicator (button) showing up when there is more to load and user can press it to load full data for the cell

1. I had a query which produced some strings longer than 100 characters
2. I expected that after clicking on the column header and choosing "Load full values", the query would be refreshed with that column not being truncated at 100 characters.
3. The actual result was, nothing happened - nothing I did would cause the column to show the whole string.

[pawelsalawa]

After you loaded values, did you resize column (to be wider) to see remaining part?

[pawelsalawa]

I don't have your database, so I just created something on the fly to be at least similar to what you have. So, here's a result of a query executed initially:

Please notice that first 2 values have "..." in the end, indicating that there is more to see in this column. Third column has
icon, indicating that value was limited and you can load its full value by clicking it.

Then I resize this column (no loading/reloading is done yet at this point):

Now we can see that actually all 3 values are limited and can be loaded. Let's do it as you did - I will right-click on header and choose to load full values:

More contents is visible now:

So I enlarge the column more:

On the last picture we can see that first value is visible entirely (no "..." at the end), while other two still have even more contents that can be shown in the column. I can either resize it further, or I can just switch to the "Form View" tab to work on full values in more suitable editors for such long values.

[LaraSQP]

Works on queries as expected.

Does not work on Data tab of a table (but it would be great if it did).

[pawelsalawa]

@LaraSQP Works for me. Are you following the same steps as I described above on screenshots?

[LaraSQP]

Thanks for the quick reply.

Like I said, it works on queries as expected (able to load full values, etc). So much appreciated.

It would be nice if it also worked on the Data tab of a table.

[pawelsalawa]

@LaraSQP Yes, I understand. Like I said, it works for me, as far as I checked, therefor I'm trying to figure out what are you doing differently, that it doesn't work for you.

• Have you resized (enlarged) your column with big values?
• Did you see the the icons for pending full value loading?
  

[LaraSQP]

What a weird circular conversation.

When I run a query, I see those blue arrows and can "load full values", etc.

When I go to the Data tab of a table, I do not see those arrows and the columns remain truncated, as they always have.

[LaraSQP]

Hold on.

I just found some blue arrows on some entries in the Data tab of a table.

Not all, but some.

It appears the blue arrows are not showing up consistently.

I will test further and see if I can report a reproducible bug.

[pawelsalawa]

So... as I said earlier - go through my explanation with screenshots above. See where I point out "...". If you see this, it means that you need to resize your column to bigger, because there is more text to show. If you resize it enough that you hit "the limit", then the blue arrow will show up.

Everything works, your columns are just to narrow and the remaining part of values are hidden.

[tedstriker2]

After you loaded values, did you resize column (to be wider) to see remaining part?

Yes and no. On further review, getting the program to load the full values depends on the column width being "wide enough" in a way I do not understand. I would expect that if I right click the column header and select "Load full values", that the program would do so immediately, without checking the current column width. That dependency is very confusing.

Also, it can be difficult to make a column "wide enough" when it is the last column because I have no room (to the right) to resize the column. Double-clicking the column border (to auto-fit to the widest value) can also fail in this situation. The "widest value" calculation may not be based on the "full value" of a cell that I requested "Load full value" on?

[pawelsalawa]

As far as I try it, it does not depend on it.
I said you should resize column only so you can notice it, but if you "load full values" - they will indeed be loaded. From that point of time it's up to you how much of the value you want to see and you control the size of column.

If you want to see entire value without resizing column, you should either switch to Form View (where always full value is loaded to each editor field) or you should right-click on a cell and pick "Edit in value editor" (or use Alt+Enter shortcut).

[tedstriker2]

I said you should resize column only so you can notice it, but if you "load full values" - they will indeed be loaded.

I just tested again, and now it works! Copying the values is also suddenly working (copying the full values), even though I did not use the "Load full value" feature. Yesterday, if I copied a value it would be truncated.

One setting I changed today was "Number of memorized table populating configurations". I changed it to zero, then back to the default (100). I changed the setting [for a different reason] and do not know if it solved my problem, or if it is unrelated.

Since I can not reproduce the issue now, I will not be able to help any further. Sorry!

[pawelsalawa]

I think that more likely was that you were running older version of SQLiteStudio for some time yesterday. Wouldn't it be possible?

[tedstriker2]

I think that more likely was that you were running older version of SQLiteStudio for some time yesterday. Wouldn't it be possible?

Impossible, for two reasons:

1. I was using version 3.3.0 all day yesterday.
2. The last version of SQLiteStudio I used (3.0.6) did not do column truncating (that I ever noticed).

[pawelsalawa]

Copying values to clipboard has already been loading full values transparently (even in version 3.2.1). There is no need to explicitly click "load full values".

I have no clue why it wasn't working for you yesterday. Since I cannot reproduce it (and now neither can you), I have no way to fix it.

[LaraSQP]

So... as I said earlier - go through my explanation with screenshots above. See where I point out "...". If you see this, it means that you need to resize your column to bigger, because there is more text to show. If you resize it enough that you hit "the limit", then the blue arrow will show up.

Everything works, your columns are just to narrow and the remaining part of values are hidden.

Not exactly.

The text in some columns is being truncated without an ellipsis, just chopped off mid-word.

Could it be that the horizontal size of the text is not calculated correctly when custom fonts are used?

I'm using DejaVu Sans Mono 12.

[pawelsalawa]

I'm not sure. So you're saying there is no ellipsis. Okay. If you resize such column, the contents remain chopped off at the same point of text (no more test shows up as you keep enlarging it) and no "load full value" appears?

If the answer is "yes", then what happens when you select this cell and switch to "Form View" - does it have full value loaded there?

[LaraSQP]

The most reliable way to reproduce it is to open a table, go to the "Data" tab, and double-click on the column separator in the header (to auto-adjust the column). As you scroll down, you can see the text will be chopped in entries that are too long. A subsequent double-click on the column separator in the header will expand the column further, now showing the blue arrows (right-aligned), but the text remains chopped where it was (no repaint, I guess).

Editing the content in place or viewing it in the "Form view" tab shows the entire text. So no problems in that regard.

It is only the missing ellipsis, blue arrows, and chopped text. That is, the visual candy.

Thus, it is a minor issue and I am happy with the situation as is.

[pawelsalawa]

It works as expected. It chops off at 100 characters, as that's the limit. If you try to show more (by resizing) an arrow appears to indicate to you "Do you want to see more of this value? Click me to load!".
Until you click the button, value will remain at 100 characters length and this is expected behavior.

[LaraSQP]

Alright then.