Is is possible to view computed fields?

I'm not sure if I have seen a discussion about this so I'm asking here: is it possible to view computed fields in Heidi? For example, I have a BLOB field which contains compressed XML so it's not directly readable. Then in the data grid I'd like Heidi to use UNCOMPRESS(field_name) so that I can read the data. Now I sometimes use a workaround with creating views but that clutters the db.

What's wrong with the query tab result with a

SELECT UNCOMPRESS(field) FROM ...

?

The wrong thing is it's not automatic and requires a lot of typing if there are many fields and you want to view all of them. Plus, the standard data grid has many benefits that the result tab has not: filter, column selection, etc.

Well the alternative for you is not to use compressed contents. It's impossible to support such use cases in the data tab where you have everything auto-generated. That's where the query tabs help you.

Well the alternative for you is not to use compressed contents.

Well, if I use compressed contents I usually have a reason to do so and in this case it's to save space. Anyway, compressing was just an example, I may want other data manipulation like converting 0 and 1 to 'off' and 'on', converting an integer timestamp to a date format, cconverting units (cents to euros, microseconds to seconds, etc.), converting 4-bit integer IP numbers to readable format and many others.

It's impossible to support such use cases in the data tab where you have everything auto-generated. That's where the query tabs help you.

Why impossible? Actually it's very simple and would require only being able to set up my own SQL string as a substitute for a column name. For example Heidi issues this SQL:

SELECT `order_id`, `name`, LEFT(`contents`, 256) FROM `orders` ORDER BY `order_id` DESC LIMIT 0, 200;

Then I instruct Heidi that instead of `contents` my custom string gets injected into the query, for example UNCOMPRESS(`contents`). Then when I go to the data grid, Heidi does this:

SELECT `order_id`, `name`, LEFT(UNCOMPRESS(`contents`), 256) FROM `orders` ORDER BY `order_id` DESC LIMIT 0, 200;

The modified field could then be read-only in the grid, that's no problem.

This is only the most basic feature but would be very convenient sometimes. The custom query data grid does not support automatic LIMIT, choosing columns to display, mysql sorting and filters - things which are very convenient in the main data grid.

Further, this could be developed more to being able to add new computed columns (for example a sum of other two values) and attaching custom JOINs - this would be very powerful - for example, if I have an integer user_id FK column I could program Heidi to do always do a JOIN with the main users table and display the username instead of (or apart from) the integer in every child table.

Sure, this is not anything that can't be done with the custom query box but that would require me to do a lot of manual typing. I can use snippets for that but then I don't have all the convenient features like auto LIMIT, quick sorting, quick filter and quick column choice. Another way to achieve this would be to allow the last four features in the custom query data grid - that would require from Heidi some manipulation on the entered queries but for simple SELECTs that would work well. Then I could use snippets to remember my queries with computed fields. Either way would be fine.

This is not something that there is no workaround for - but that would allow people do these things faster - that would be just one click on the data tab instead of trying to compose custom queries, load snippets and manually change LIMIT, ORDER BY, etc.

Also, now there is a problem partly related to what I wrote above - if I type this query:

SELECT UNCOMPRESS(contents) FROM orders;

Then I am not really able to view all the data unless it's very short because it is truncated in the grid. In the main data grid I can double-click the value and view it in the separate edit text box. When I type my custom query Heidi says: Grid editing error: Column #x has an undefined origin: UNCOMPRESS(contents).

I disagree - typing a query is from my point of view not "a lot of manual typing". Doing this in the data tab would even require quite a few additionially clicks.

I don't think you understood me correctly. I said that the main point of this feature would be one-click solution. I set up my functions on columns once, Heidi remembers them and then it is one click every time. No typing, no selecting snippets. Do you think it's a useless feature?

I would not say that would be useless but I guess that was a rhetoric question. I think that adding further function logic in the data tab/grid tends to be impossible. When you browse tables with some TEXT column you already see a LEFT(col) in the SQL log, which is done internally to enhance network speed. Now, please look at the code in HeidiSQL which handles loading the remaining stuff of these incomplete fetched columns - ugly, with complex code paths, hackish. The main problem is that if you edit a function result of a cell you have to somehow convert it back to the expected structure when saving changes per INSERT or UPDATE. The MySQL driver does not return the db+table+column name when applying such functions - which is the reason for some of the above mentioned hacks. This is also the reason for your noticed "has an undefined origin" error. All in all I think some solution to your requested function stuff would be largely hackish and requires much code, while use cases are rare.

The main problem is that if you edit a function result of a cell you have to somehow convert it back to the expected structure when saving changes per INSERT or UPDATE.

I understand editing could be a problem so, as I said before, those cells could be read-only and ingnored on any updates, inserts can be disabled too.

If even that is too hackish - would it be possible to add auto-LIMIT and native sql sorting to the query result grid? So that buttons 'Next', 'Show all' and 'Sorting' are available? Heidi would have to automatically manipulate the entered query (add LIMIT and ORDER BY) but it should be possible - PMA has been doing it for years and it works pretty well for SELECTs. This could be optional because some people might not want Heidi to change anything in their queries.