[v2] SQLite Database Viewer

Post your working scripts, libraries and tools.
User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

[v2] SQLite Database Viewer

19 Mar 2019, 13:15

There was some recent activity in the thread for Class_SQLiteDB by just me. It inspired me to make my own database viewer GUI for SQLite using his class. I had to make a few adjustments to his class to work with AHK v2 and I also wanted some additional functionality.

The code isn't really documented at all. But it seems to work fairly well. Only handles SELECT and PRAGMA statements!

Created using ahk v2-a100 and not tested with any other versions.

Let me know if you have any issues with it. Source and a release has now been uploaded to GitHub - https://github.com/kczx3/SQLiteViewer

This site has a great sample database to play with -> http://www.sqlitetutorial.net/sqlite-sample-database/

Features
  • Database viewer treeview
  • Drag any item from the Database treeview and drop into the query editor
  • When the query editor is focused, you can run with Ctrl + Enter also
  • Results are displayed in a lower ListView. You can filter the results by typing into the corresponding column's filter Edit and typing
  • StatusBar shows the last SQL ran, the DB that it ran against, and the number of rows returned (also shows the number of rows displayed out of the total if you are filtering)
  • History tab keeps track of recently ran queries, grouped by the database file they were ran against. Select one to view the full query in the Edit to the right
  • Create snippets on the right panel. If you hover over a snippet, a Tooltip is used to display the snippet contents. Double clicking a snippet will overwrite the query editor with the contents of the snippet (may change this to just insert at the current cursor position)
For now, the query editor starts off with a single tab with "+" as the text because positioning is different if you add a Tab control without any tabs. The "+" tab has no purpose for now.

Updates
3/23/2019
  • New version that uses Scintilla for all Edit controls that display SQLite statements. Modified Scintilla class from AutoGUI to work with AHK v2 and eliminated global variables and functions.
3/25/2019
  • Initial implementation of CallTips when writing queries in the main editor. There are a few limitations: A calltip will display if you type a function name and open parenthesis inside a string literal. If you type a close parenthesis at any time while a CallTip is open, it will close. If you select more than 1 character on the line where a CallTip was displayed, it will be closed. The parameter highlighting also does not work properly if you use arrow keys to the left a previous parameter slot and then delete the text for that parameter.
3/28/2019
  • GUI is now resizable. Resizes are buffered to only resize/redraw controls every three pixels. So there may be instances where it doesn't quite look right. Just resize it a bit more or maximize and then restore.
  • Code editor improvements include better workflows for typing quotes and closing parenthesis when they have no contents. Typing two quotes for example will now not insert three quotes. Instead, your cursor will go to the far side of the second quote. Same for braces. If your cursor is in between braces or quotes and you hit backspace, the quotes or braces on both sides will be removed.
  • Autocompletion of function names and SQLite keywords.
  • Probably other stuff I can't think of
4/17/2019
  • Implemented the REGEXP operator to be used in SQLite queries
  • Cleaned up Tab handling
  • Better autocompletion - if you have a full match typed of an autocompletion item and hit Tab or Enter, the Tab or Enter is also inserted into the editor whereas before it only would complete the autocompletion
  • When dragging an item from the Database Treeview into the editor, holding down the Ctrl key while releasing the mouse will instead overwrite the hovered word with the dragged word. If you are hovering near a space, then a normal text insertion is made.
  • Added vertical splitter control that can be dragged to resize the editors and results tabs.
SQLiteViewer.png
SQLiteViewer.png (89.96 KiB) Viewed 7330 times
Last edited by kczx3 on 02 May 2019, 11:42, edited 9 times in total.
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: [v2] SQLite Database Viewer

19 Mar 2019, 17:50

@kczx3, thank you for this. I just tested it with v2-a099 and it works great. Very impressive.
Regards,
burque505
User avatar
jNizM
Posts: 3183
Joined: 30 Sep 2013, 01:33
Contact:

Re: [v2] SQLite Database Viewer

20 Mar 2019, 04:46

Nice.. Good Job... Like the "DB Browser for SQLite"

Do you plan to host it on GitHub? Would be nice.
And add a compiled version (SQLiteViewer.exe) so users who still on ahk 1.1 can use it easily too.
[AHK] v2.0.5 | [WIN] 11 Pro (Version 22H2) | [GitHub] Profile
User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [v2] SQLite Database Viewer

22 Mar 2019, 23:17

jNizM wrote:
20 Mar 2019, 04:46
Nice.. Good Job... Like the "DB Browser for SQLite"

Do you plan to host it on GitHub? Would be nice.
And add a compiled version (SQLiteViewer.exe) so users who still on ahk 1.1 can use it easily too.
Thanks!
I do plan to get it out there soon-ish. I'll include a compiled version as well.

New version in OP. Uses Scintilla now.
freakkk
Posts: 25
Joined: 21 Sep 2014, 20:14

Re: [v2] SQLite Database Viewer

24 Mar 2019, 14:57

This is fantastic!! :D

I've been switching several things over to using a json type model for my data storage lately, but this alone has me reconsidering using sqlite instead. Thank you for sharing!
User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [v2] SQLite Database Viewer

25 Mar 2019, 12:32

freakkk wrote:
24 Mar 2019, 14:57
This is fantastic!! :D

I've been switching several things over to using a json type model for my data storage lately, but this alone has me reconsidering using sqlite instead. Thank you for sharing!
Glad you like it!
User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [v2] SQLite Database Viewer

25 Mar 2019, 13:18

Update:

New version in OP that implements CallTips for SQLite functions. This was my first (rough) attempt at doing this.
User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [v2] SQLite Database Viewer

28 Mar 2019, 11:09

Update:

Improved code editing experience in the Query Editor. Added in autocompletion. GUI is now resizable. Probably some other things I can't remember.

Release is avaiable on GitHub. See OP.
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: [v2] SQLite Database Viewer

28 Mar 2019, 12:19

Hi, just tested the update, got this error. Edit: Using v2.0-a100 works great!

Code: Select all

	Line#
	085: this.controlCount--
	088: if (this.controlCount = 0)
	088: {
	089: DllCall("FreeLibrary", "Ptr", this._handle)
	090: }
	091: }
	101: {
--->	103: Return DllCall(this.df,"UInt" , this.dp,"UInt" , msg,"Int"  , wParam,"Int"  , lParam)
	108: }
	117: {
	133: this.IdFrom          := NumGet(lParam + A_Ptrsize * 1)
	134: this.SCNCode         := NumGet(lParam + A_Ptrsize * 2)
	136: this.Position        := NumGet(lParam + OffPosition)
	137: this.Ch              := NumGet(lParam + OffCh)
	138: this.Modifiers       := NumGet(lParam + OffModifiers)
Granted, I'm running 2.0-a99, as above, and I suppose that _might_ make a difference. But I tried to see if

Code: Select all

        this.df := SendMessage(this.SCI_GETDIRECTFUNCTION, 0, 0, this.hWnd)
retrieved a value, and it did not. I'll try a newer v2, and also try running as admin.
Thanks!
User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [v2] SQLite Database Viewer

28 Mar 2019, 12:30

Interesting that a99 throws an error but a100 doesn't. I'm not immediately sure what would cause that.

Glad that switching to a100 worked for you.

I'd be interested in getting your feedback regarding my implementation of buffering control moves in the gui resize event handler (mainResize). Do you feel that it makes the experience better? By not doing that, the flickering is rather annoying. I thought buffering would help but it obviously leads to a couple other nuances.
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: [v2] SQLite Database Viewer

28 Mar 2019, 12:36

@kczx3, it is smooth as silk for me :) I don't recall how it was before, but this is great.
User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [v2] SQLite Database Viewer

28 Mar 2019, 12:41

Sweet! :thumbup:
micasa
Posts: 24
Joined: 29 Dec 2018, 04:21

Re: [v2] SQLite Database Viewer

31 Mar 2019, 07:04

@kczx3 I just tested it out on a database with over 8 million rows and it was very smooth! Response time is great all around. Thank you!

When I try to use your ported version of Class_SQLiteDB in a standalone script of mine, I get a dll error on line 534.

Parameter #1 invalid
---> 534: RC := DllCall("SQlite3.dll\sqlite3_exec", "Ptr", This._Handle, "Ptr", &UTF8, "Int", CBPtr, "Ptr", Object(This)
, "PtrP", Err, "Cdecl Int")

I am basically using the same script (just ported to AHK_V2) and folder structure as the one I had for AHK_V1 Class_SQLiteDB. The dll seems to be loading okay. Version of AHK_V2 is AutoHotkey_2.0-a100-52515e2.
User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [v2] SQLite Database Viewer

31 Mar 2019, 07:52

Certainly possible. I just fixed syntax issues but haven’t really used the exec method yet. I’ll look into it
User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [v2] SQLite Database Viewer

31 Mar 2019, 11:18

AHK v1 gave you two ways to retrieve the address of an object. Either &object or Object(object).

AHK v2 doesn't support the latter method. So in the exec method of the SQLiteDB class, you need to modify it like so:

Code: Select all

   ; ===================================================================================================================
   ; METHOD Exec           Execute SQL statement
   ; Parameters:           SQL         - Valid SQL statement
   ;                       Callback    - Name of a callback function to invoke for each result row coming out
   ;                                     of the evaluated SQL statements.
   ;                                     The function must accept 4 parameters:
   ;                                     1: SQLiteDB object
   ;                                     2: Number of columns
   ;                                     3: Pointer to an array of pointers to columns text
   ;                                     4: Pointer to an array of pointers to column names
   ;                                     The address of the current SQL string is passed in A_EventInfo.
   ;                                     If the callback function returns non-zero, DB.Exec() returns SQLITE_ABORT
   ;                                     without invoking the callback again and without running any subsequent
   ;                                     SQL statements.  
   ; Return values:        On success  - True, the number of changed rows is given in property Changes
   ;                       On failure  - False, ErrorMsg / ErrorCode contain additional information
   ; ===================================================================================================================
   Exec(SQL, Callback := "") {
      This.ErrorMsg := ""
      This.ErrorCode := 0
      This.SQL := SQL
      If !(This._Handle) {
         This.ErrorMsg := "Invalid dadabase handle!"
         Return False
      }
      CBPtr := 0
      Err := 0
      If (FO := Func(Callback)) && (FO.MinParams = 4)
         CBPtr := CallbackCreate(Callback, "FC", 4)
      This._StrToUTF8(SQL, UTF8)
      ObjAddRef(address := &this)
      RC := DllCall("SQlite3.dll\sqlite3_exec", "Ptr", This._Handle, "Ptr", &UTF8, "Ptr", CBPtr, "Ptr", address, "PtrP", Err, "Cdecl Int")
      ObjRelease(address)
      CallError := ErrorLevel
      If (CBPtr)
         CallbackFree(CBPtr)
      If (CallError) {
         This.ErrorMsg := "DLLCall sqlite3_exec failed!"
         This.ErrorCode := CallError
         Return False
      }
      If (RC) {
         This.ErrorMsg := Err ? StrGet(Err, "UTF-8") : ""
         This.ErrorCode := RC
         DllCall("SQLite3.dll\sqlite3_free", "Ptr", Err, "Cdecl")
         Return False
      }
      This.Changes := This._Changes()
      Return True
   }
micasa
Posts: 24
Joined: 29 Dec 2018, 04:21

Re: [v2] SQLite Database Viewer

31 Mar 2019, 22:25

kczx3 wrote:
31 Mar 2019, 11:18
AHK v1 gave you two ways to retrieve the address of an object. Either &object or Object(object).

AHK v2 doesn't support the latter method. So in the exec method of the SQLiteDB class, you need to modify it like so:
Thank you! It basically works, but still throws errors while cleaning up after a query.

Unknown method.
---> 264: This._DB._Queries.Remove(This.Handle)

An exception was thrown.
For Each, Query in This._Queries
--->466: DllCall("SQlite3.dll\sqlite3_finalize", "Ptr", Query, "Cdecl Int")

Since my current needs aren't very complicated, I can just comment out these lines and still get the results I want. However, for people with lots of prepared statements, simply commenting out these lines is obviously not desirable.

Sorry, I now know it wasn't your intention to port Class SQLiteDB in its entirety, but simply use it in your SQLite Database Viewer application.

Actually, I was just about to post an example on how to use sqlite3.exe in AHK_V2 for non-ASCII data sets. But when I found your application, I naturally wanted to use the SQLiteDB class since it makes for cleaner and more efficient code. I'll probably still post my sqlite3.exe example, but would like to get this class to work so that I can provide AHK_V2 examples for both.

I hope my comments don't trouble you much. Thank you for your time and work.
User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [v2] SQLite Database Viewer

01 Apr 2019, 07:23

Not at all. I just don't have the time to fully go through and test everything so typically its a "fix when I find the break" strategy. You're just helping me find the breaks faster!

Replace the Free() method of the _Recordset class with this:

Code: Select all

; ----------------------------------------------------------------------------------------------------------------
; METHOD Free        Free query result
; Parameters:        None
; Return values:     On success  - True
;                    On failure  - False, ErrorMsg / ErrorCode contain additional information
; Remarks:           After the call of this method further access on the query result is impossible.
; ----------------------------------------------------------------------------------------------------------------
Free() {
    This.ErrorMsg := ""
    This.ErrorCode := 0
    If !(This._Handle)
        Return True
    RC := DllCall("SQlite3.dll\sqlite3_finalize", "Ptr", This._Handle, "Cdecl Int")
    If (ErrorLevel) {
        This.ErrorMsg := "DLLCall sqlite3_finalize failed!"
        This.ErrorCode := ErrorLevel
        Return False
    }
    If (RC) {
        This.ErrorMsg := This._DB._ErrMsg()
        This.ErrorCode := RC
        Return False
    }
    
    This._DB._Queries.Delete(This._Handle)
    This._Handle := 0
    Return True
}
micasa
Posts: 24
Joined: 29 Dec 2018, 04:21

Re: [v2] SQLite Database Viewer

01 Apr 2019, 10:02

kczx3 wrote:
01 Apr 2019, 07:23
Not at all. I just don't have the time to fully go through and test everything so typically its a "fix when I find the break" strategy. You're just helping me find the breaks faster!

Replace the Free() method of the _Recordset class with this:
Thank you! That fixed it! Much faster than my sqlite3.exe implementation. I think I am still going to post my sqlite3.exe example, as well as an example using this AHK_V2 version of Class_SQLiteDB.ahk.

If you would like, I could create an "unlisted" paste on pastebin.com so that you can get the updated version of Class_SQLiteDB.ahk. Otherwise, you can just make the changes yourself and then reflect it on your github page. Just let me know if you want me to paste it. Thanks again!

P.S. When I get the time over the next few weeks or so, I will try to test it out some more.
User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [v2] SQLite Database Viewer

01 Apr 2019, 10:30

Nah, I've already incorporated the fixes into mine.

Thanks for your testing!
User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [v2] SQLite Database Viewer

17 Apr 2019, 10:15

Another smaller update. See most recent Release on GitHub.

Return to “Scripts and Functions (v2)”

Who is online

Users browsing this forum: bonobo and 12 guests