Feature request: dynamic history of custom queries

[expired user #5843]'s profile image [expired user #5843] posted 13 years ago in Feature discussion Permalink
Hi,
in first words - excellent work with HeidiSQL. This is great tool and development speed is amazing.
What miss for me: when I work on tables I generate many custom queries, which I want to use in the future. Best solution will by in tab "Query" (above or below in list SQL Functions, SQL Keywords, Snippets, Query Profile) name it - history tree.
This tree should contain every used custom queries in actually selected database.
Nice idea: this history list sorted by frequency of using those queries.
What do you think about this?
ansgar's profile image ansgar posted 13 years ago Permalink
What about saving as snippets? These are already listed right besides the query tabs.
[expired user #5843]'s profile image [expired user #5843] posted 13 years ago Permalink
Snippets are great idea but for other needs. They are common for all connections/databases.

What I need is separated query history appended specificity to database.

(I use many database and make many complex queries, which sometimes I want to use after few months. Store it in snippets give me a mess and are not useful)
[expired user #5843]'s profile image [expired user #5843] posted 13 years ago Permalink
Snippets in self are usefully for typical stored queries. I think easy solutions for my needs (and I think for other users) will be "Local snippets" for every database.
ansgar's profile image ansgar posted 13 years ago Permalink
Ok, I still don't get if you ask for some complete history or for single snippets. The complete history is the output of the SQL log, which can be auto-written to some sql file.

The snippet feature is intentionally not specific to servers, for historical and simplicity reasons. But that's anyway not what you're asking for if I understand right.
[expired user #5843]'s profile image [expired user #5843] posted 13 years ago Permalink
Ok. From beginning.

What I need it`s a access to specified queries. Queries history appended to database give me this. Not like SQL Log - which is common for all databases. (It`s hard to find something related to specified database in multiple - system and own queries)

First solution is history tree. There will be stored all queries used in active database and related to selected database.

Like this: http://imageshack.us/photo/my-images/651/mysqlc.jpg/

Second solution is adding new snippets tree - connected to actual selected database, in which I can store selected query. This give chance to have order in snippets.

Of course best will be implement those two improvements.

Is that clear? (sorry for my English..., I still learning)
ansgar's profile image ansgar posted 13 years ago Permalink
Your english is perfect.

But I disagree with "Of course best will be implement those two improvements.". I won't add two redundant features which are already implemented in a similar way. I think it's enough to have a) snippets for important stuff and b) the sql log, optionally written to files.
[expired user #5843]'s profile image [expired user #5843] posted 13 years ago Permalink
Of course I can work with database with console or by phpmyadmin. But I use HeidiSQL because I want to make my work easier. And this is why I wrote this proposition.

This improvement - divide snippets for every database for my point of view are important and make my work easier. I think for other users - too. Especially when they use many database and make many operations on them. Time of finding specified query will be shorter in opposite actually state where everything are in one tree.

I know - I can make it with SQL log or put every important for me query to snippets, but if I do this - list of snippets will have about 80-100 items for all my database (it`s not make a order).

HeidiSQL it`s a great tool and I want to be a greater and more useful.
lemon_juice's profile image lemon_juice posted 13 years ago Permalink
The history of custom queries grouped by date looks like a great suggestion and much more useful than snippets, IMO. The problem with snippets is that I have to think about manually saving each query as a snippet while at the time of executing a query I might not be aware that I might need it in the future.

This would have another betefit: in case of a crash the executed query is saved in the history and won't be lost. Heidi is prone to crashes from time to time and having a mechanism preventing data loss would be very welcome.
[expired user #5843]'s profile image [expired user #5843] posted 12 years ago Permalink
It is something to do to get this topic feature in HeidiSQL? Because not only me asking for make life easier with HeidSQL by storing queries in order/date/by datbase .
[expired user #6181]'s profile image [expired user #6181] posted 12 years ago Permalink
I also miss this feature. It is present in another tool I use for Oracle (PL/SQL developer) and I use it A LOT...

You can open a pop window with all your previous executed queries, select one and run it again.

It can save a lot of time if your working on a case on separate days for example..
ansgar's profile image ansgar posted 12 years ago Permalink
As said above, there is already a history, and it's possible to write it into files. You can rightlick the history to open up the log folder. We have snippets. Everything's there what you need. The fact that it's working differently than in the Oracle tool does not mean that this is better or worse to use, it's just a different approach. Both are ok, in my eyes.
[expired user #5843]'s profile image [expired user #5843] posted 12 years ago Permalink
I wonder how many people should ask for this to change your mind anse - with all respect to your great work.
lemon_juice's profile image lemon_juice posted 12 years ago Permalink
The log file is inconvenient to use because all kinds of queries go there including those generated by heidi so after some time the log files become huge piles of mostly useless data among which it's difficult to find what I'm looking for. I know you can say I can change what gets logged into the file but if I log only user queries then I will also see only user queries in the history which is not good. I need all queries reported in the history panel and only user queries written somewhere to a log file permanently.

A big improvement would be at least to be able to choose what gets logged into the file independently of what gets logged in the history panel.
[expired user #5843]'s profile image [expired user #5843] posted 12 years ago Permalink
http://i.imgur.com/TEH53.jpg one picture = 1000 words ;-)
[expired user #6181]'s profile image [expired user #6181] posted 12 years ago Permalink
@Anse, again I really love your work and tried your solution but get the message in the log file: '...large SQL query (12,9 KB), snipped at 2.000 characters'.... So please understand that yours is not a good solution either..
[expired user #6181]'s profile image [expired user #6181] posted 12 years ago Permalink
Oops... never mind...the 2000 is a setting
ansgar's profile image ansgar posted 12 years ago Permalink
Ok, seems there are probably more than 1 user wanting such a SQL history... sigh. Well, I just had the idea to add a history to the SQL helpers box, above the "Query profile" node, probably as a sub tree with dates in it:
+ Columns
+ SQL functions
+ SQL keywords
+ Snippets
- History
- Today
select bla from blub
- Yesterday
update bla set name='123'
- 2012-01-01
insert bla...
update blub...
+ Query profile


But I will need to be careful about size, so executing queries will not get slow due to a huge history file: Maximum file size will be let's say 1mb per session. A query of 2mb will be cut at 1MB, while having 2 with 500k and adding a 3rd one will remove the oldest query completely from history.
lemon_juice's profile image lemon_juice posted 12 years ago Permalink
anse, this proposition looks very good! As to large query sizes: maybe add an option to prevent all queries from going to the history if larger than a specified size? The reason is that importing a large file with queries could effectively wipe out all earlier history, which might be important. I don't know what other people think but for me history is useful for small queries anyway, the ones I write manually, and I don't need large dumps to be sitting there. It would be OK for me if anything larger than 3KB or so would simply not be saved in history, or saved as /* large query, content not saved */ or something.
[expired user #5843]'s profile image [expired user #5843] posted 12 years ago Permalink
@anse great news! Thank you for take this topic to work.
I think, it`s very important to history will be connected / filtered by actually used database to avoid mess with show too many history elements.
[expired user #5843]'s profile image [expired user #5843] posted 12 years ago Permalink
or at least connected to connection...
ansgar's profile image ansgar posted 12 years ago Permalink
Yes, the displayed history should be bound to the session/server you have currently open. But not to the current database. As you can use the db.table syntax to fire queries in other than the active database it seems unhandy to me to have only the queries which were fired while having the same active database. I guess the day nodes should help finding the right query here, or? Or, probably I can grey out those queries which were fired within a different database.
ansgar's profile image ansgar posted 12 years ago Permalink
Ah, and I should only add succesfully executed queries, not those to which the server returned some error, right? I find myself often in several times executing a wrong SQL syntax and would not like to have these in a history.
[expired user #5843]'s profile image [expired user #5843] posted 12 years ago Permalink
From my point of view - add only correct query. And link/filter query history by used connection is fine.
ansgar's profile image ansgar posted 12 years ago Permalink
So, I'll go and use ZLib compression on these queries while storing them into registry, where Heidi already has well thought out structures and session based keys. Using ZLib will prevent huge strings from blowing up the registry.
[expired user #5843]'s profile image [expired user #5843] posted 12 years ago Permalink
I don`t know too much about Windows programming, but have you consider storing those questions in private HeidiSQL sqlite file? Easy store, retrieve and update data with no danger to store to much data in windows registry.
ansgar's profile image ansgar posted 12 years ago Permalink
No, surely I won't use again a separate database engine to store recent queries, that's just too much effort. I think registry is fine, and works already good for the portable version of Heidi.
Code modification/commit from ansgar.becker, 12 years ago, revision 7.0.0.4094
Implement query history in helpers tree. See http://www.heidisql.com/forum.php?t=8927 .
ansgar's profile image ansgar posted 12 years ago Permalink
Done, see "Query history" tree node in latest build.
lemon_juice's profile image lemon_juice posted 12 years ago Permalink
Thanks, works great!

Is there an option to clear the history?
ansgar's profile image ansgar posted 12 years ago Permalink
The history is automatically trimmed when needed. Oldest items get deleted as soon as the history reaches a size of 1MB per session.
lemon_juice's profile image lemon_juice posted 12 years ago Permalink
I think there should be an option to clear all history because sometimes the queries may contain sensitive or private data, for example when I'm searching for a user with a given password and I don't want the data to be visible like that on the computer without any protection. This would be less of a problem if the queries were stored in a file, then I could just delete the file, but now messing in the registry to clear the history may seem scary to many people.
ansgar's profile image ansgar posted 12 years ago Permalink
Hm, ok. Will be a new context menu item.
lemon_juice's profile image lemon_juice posted 12 years ago Permalink
Great!
kez's profile image kez posted 12 years ago Permalink
Thanks Anse! I've been after that for some time. Save me loosing queries that become useful again after a short while...
Code modification/commit from ansgar.becker, 12 years ago, revision 7.0.0.4098
Add context menu item for manually clearing query history. See http://www.heidisql.com/forum.php?t=8927#p10355
ansgar's profile image ansgar posted 12 years ago Permalink
"Clear query history" is now a context menu item, in r4098.
lemon_juice's profile image lemon_juice posted 12 years ago Permalink
Works very well, thanks!

Please login to leave a reply, or register at first.