SQLite Forum

sqlite-gui application

sqlite-gui application

(1) By anonymous on 2020-09-16 00:23:23 [link] [source]

Hi all,

This might interest those who want a lightweight sqlite gui that's based on a Windows look and feel. Maybe a Windows 95 look?

Github link: https://github.com/little-brother/sqlite-gui

(2) By Keith Medcalf (kmedcalf) on 2020-09-16 01:02:45 in reply to 1 [link] [source]

Very nice and no dependencies and you can replace the sqlite3.dll

(3) By skywalk on 2020-09-16 13:31:12 in reply to 1 [link] [source]

Close but not ready for primetime. 
No blob is nogo.
And I crashed it with simple export to csv.
A smaller export inserted [CR]'s at end of every row?

(4.1) By Keith Medcalf (kmedcalf) on 2020-09-16 17:12:00 edited from 4.0 in reply to 1 [link] [source]

The schema for the prefs.sqlite can also be simplified. PRIMARY KEY is an alternate spelling for UNIQUE, so you only need one of them, not both.

Plus, something which is unique/primary key is already indexed so no need to create a duplicate index explicitly.

These just waste space but should no be harmful, at least not in up-to-date versions of SQLite3.

The functionality and interface is reminiscent of the old MS SQL Server Admin/Query Tool thingimy back before they made it useless (and slow as molasses running up hill in an Edmonton -40 degree Winter) by re-writing it in dot SNOT and calling it "SQL Manglement Studio".

I found a couple of bugs already and there are some UI annoyances, but it does seems to work, if you are into GUIs.

(5) By little-brother on 2020-09-16 17:51:13 in reply to 1 [link] [source]

Hi, everyone!

@anonymous, thanks for share the link :)
I'm a developer of this tools.

> I crashed it with simple export to csv.
@skywalk, could you describe what kind of data in the table or send it as .sqlite/csv or smth else to lb.im@ya.ru?

> A smaller export inserted [CR]'s at end of every row?
It's a Windows End-of-Line-style "\r\n". The Unix format is "\n". Perhaps I should set Unix-"\n" as default.

> I found a couple of bugs already and there are some UI annoyances, but it does seems to work, if you are into GUIs.
@Keith Medcalf, could you provide more information? I'll try to fix it as soon as finish "the next-one feature".

I didn't dedicate too much time to data definition wizards because I use to work with data, not ddl.

(6) By skywalk on 2020-09-16 18:06:34 in reply to 5 [link] [source]

I am fine with the gui and its speed and lack of bloat.

The [CR]'s were in addition to [CR+LF]'s. 
Thus, 1 empty line in the exported csv.

I will get you a db when I have more time.

You really should add support for blobs. 
All my databases have at least 1 blob column.

(7) By anonymous on 2020-09-16 18:15:19 in reply to 5 [link] [source]

Hi LB,

thanks for creating the tool.

A minor bug I've found is that if you right-click a table before selecting it, the highlight jumps up to the previous highlighted table.

  1. Open the tool with bookstore DB
  2. Click books table
  3. Right-click orders
  4. Notice the highlight on orders quickly changes to books. If you query orders, it'll actually query books.

It would be nice to rename the editor tabs.

(8.1) By little-brother on 2020-09-16 18:42:32 edited from 8.0 in reply to 6 [link] [source]

> its speed
Honestly, SQLiteStudio is much faster (about 4 times) on long time queries. I got to figure out how it can be possible when I'll be out of ideas/bug fixing.

I have never used blob prefering them to files. Blobs take a lot of work: HEX-editor, image viewer and "save as file"-option. So it will not appear shortly.

> Thus, 1 empty line in the exported csv.
Thanks. I added it to my bug list.

(9) By little-brother on 2020-09-16 18:41:50 in reply to 7 [link] [source]

Thanks for details. I'll fix it (or at least try :D) in a next release.

> It would be nice to rename the editor tabs.
It is planned. But there are many things have higher priority (one of them is multi threading).

(10) By anonymous on 2020-09-16 19:13:58 in reply to 5 [link] [source]

There doesn't seem to be a way to close an editor tab. Do you plan on adding that in?

Also, when I close a database, I would expect the editor window to close. Perhaps there can be a prompt to the user asking if they want to save their query.

(11.1) By little-brother on 2020-09-17 21:01:26 edited from 11.0 in reply to 10 [link] [source]

> way to close an editor tab.
One tab should be open. Two tabs have crosses to close. One tab doesn't.

> Perhaps there can be a prompt to the user asking if they want to save their query.
You could turn off autosave-and-restore 
Database > Settings > "Restore editor's text on start up"
Ask each time on exit is annoying.

(12) By little-brother on 2020-09-23 12:15:58 in reply to 1 [link] [source]

1.2.2 released
+ Basic BLOB support
+ Editor tabs renaming
+ Startup script
* Improve tree navigation
* Improve code completition
* Fix CSV export bug
* Fix bugs

I put aside "the next big-one feature" because I decided to make it as standalone app. So I gathered the topic remarks and implemented them.

Download link - https://github.com/little-brother/sqlite-gui/releases/download/1.2.2/sqlite-gui.1.2.2.zip

(13) By skywalk on 2020-09-23 13:54:45 in reply to 12 [link] [source]

Ok, Latest exe crashes on [Right-Mouse-Clk]-Export to csv of recent query results.

(14) By little-brother on 2020-09-23 14:06:19 in reply to 13 [link] [source]

Could you provide more details e.g. data (or at least table structure) and the query text to reproduce error? Perhaps it's a BLOB trouble.

(15) By little-brother on 2020-09-26 14:29:57 in reply to 1 [link] [source]

1.2.3 released
+ Add delete and refresh buttons in Edit data dialog
* (Critical) Fix delete bug in Edit data dialog
* Fix csv export bugs

Download link - https://github.com/little-brother/sqlite-gui/releases/download/1.2.3/sqlite-gui.1.2.3.zip

I found a blunder when user deletes a row in Edit data dialog :(
Also I tried to fix csv export. I didn't receive any information from @skywalk, so I couldn't check it.

(16) By anonymous on 2020-09-26 15:28:23 in reply to 15 [link] [source]

csv export is no longer double spaced for Windows, but why the semicolon to separate the records? Is that more common to use than a comma?

1;Steelheart;Brandon Sanderson;978-0385743563;5.0;3
2;All Our Yesterdays;Cristin Terrill;978-1423176374;7.3;10
3;Life After Life;Jill McCorkle;978-1616203221;2.82;4
4;Life After Life;Kate Atkinson;978-0316176491;8.1;5
5;Foundation;Isaac Asimov;978-0553293357;12.7;8
6;Dracula;Bram Stoker;978-0486411095;6.6;0

I like when data is queried with a right-click on a table, it opens in a new results tab, but find it not intuitive the query is placed in the same editor tab. Would you consider having the new query open a new editor tab with the new query?

(17) By little-brother on 2020-09-26 16:32:40 in reply to 16 [link] [source]

> but why the semicolon to separate the records?
App uses the delimeter applying on last CSV export. 
By default it's ';'. I'll change it to comma.

> the new query open a new editor tab with the new query
Ok. Coming in the next update as option.

(18) By anonymous on 2020-09-26 21:51:09 in reply to 17 [link] [source]

App uses the delimeter applying on last CSV export.

Ah, I do see the options to use other column delimiters, but I agree that the default of a comma is a better idea.

Couple small things...

I don't know if it would ever be done, but you can't create an empty table. There's an error message when creating an empty table without any records:

near ")": syntax error

Is there a way to save database changes, or does that happen automatically? When creating a new table in your bookstore example DB, it looks like it automatically saved it, without any kind of user ack. I suppose this is operating like the sqlite cli...there's no prompt to save before exiting. For a gui, though, a save db changes message might be expected.

The "save" icon (for save query) looks permanently grayed out, but it actually saves queries. Is that by default?

(19) By anonymous on 2020-09-27 01:36:00 in reply to 18 [link] [source]

Is that by default?

I meant to ask if that's by DESIGN. As is, is it by design to have the icon look grayed out/disabled.

(20) By little-brother on 2020-09-27 12:38:31 in reply to 18 [link] [source]

> you can't create an empty table
Ok, I'll add check to prevent it.

> Is there a way to save database changes, or does that happen automatically?
It does automatically on each value changing. You could start transaction explicitly by execute "begin transaction" before data changing. And after commit or rollback all changes.
> The "save" icon (for save query) looks permanently grayed out, but it actually saves queries.
I'll recolor icons to avoid this misunderstanding. 

Thanks for remarks!

(21.1) By little-brother on 2020-11-17 11:58:28 edited from 21.0 in reply to 1 [link] [source]

1.3.1 released

  • Show referenced data by Alt + Click
  • Workflow manager (beta)
  • Query the table data in a new tab
  • F5 to refresh tree
  • REDO in Editor by Ctrl + Shift + Z
  • Decode and concat functions
  • Back up prefs.sqlite
  • Fix sorting in data grids
  • Fix csv import
  • Recompile sqlite3.dll
  • Change icu extension to third party
  • Minor changes

This release introduced a new ETL tool to execute sequences of parameterized queries and import/export data via ODBC to SQLite database.
It's a simplifier version of SSIS and Apache NiFi :D Check Wiki to learn more.

Download 1.3.1

(22.1) By little-brother on 2020-11-17 11:58:42 edited from 22.0 in reply to 1 [source]

1.3.1 released

  • ODBC import
  • Database comparison
  • Database search
  • Database statistics
  • Fix bugs

Download 1.3.2

(23) By little-brother on 2020-12-27 08:32:12 in reply to 1 [link] [source]

Since 1.3.2 I have added two features

  1. Enable/disable indexes/triggers
  2. Special an editor tab with terminal mode (picture).

Download 1.3.6

P.S. I don't feel as I have right to publish releases here. So I stop it :)

(24) By anonymous on 2020-12-27 14:27:31 in reply to 23 [link] [source]

This download is blocked by Chrome.

(25.1) By little-brother on 2020-12-27 17:18:38 edited from 25.0 in reply to 24 [link] [source]

Thanks a lot! I don't know about this issue because I primarily use Firefox.

This is due to the fact that some antiviruses do not like small files :/

But the fact that this is the reason for the blockage is nonsense.

I recompiled all dll-s with -O option (optimize for speed) and updated releases files to reduce a Chrome paranoia.

(26) By anonymous on 2020-12-29 08:46:29 in reply to 25.1 [link] [source]

See here for details

Expand Technical Information for ways to circumvent.

(27) By little-brother on 2020-12-30 07:28:37 in reply to 26 [link] [source]

None of them cause triggering the virus detection. I don't use any of them.
I've been told that a reason is a empty entry point like this. I'm a not skilled C-programmer, so it take a time to grok it.

(28) By anonymous on 2020-12-30 08:11:25 in reply to 27 [link] [source]

If it helps, this is the reason logged for detecting a threat:

Detected: PUA:Win32/InstallCore
Status: Failed
This threat or app might not be completely remediated.
This program has potentially unwanted behaviour.

On Windows 10 using Chrome or Edge to download the zip from the link given.

(29) By little-brother on 2020-12-30 11:03:52 in reply to 28 [link] [source]

Thanks for your report. I'll try to fix it.
P.S. I added it as a new issue.

(30) By Trudge on 2020-12-30 17:42:00 in reply to 18 [link] [source]

As a long-time Perl programmer having to deal with various 'source' files for DB creation, I have discovered that using a pipe symbol ' ' as a field separator is less head-ache. It is vary rarely used in common text, thus not likely to be interpreted as anything else. It's easy to split on. So if you have control over the source file, consider using the pipe symbol as a field separator. My 2ยข.

(31) By Warren Young (wyoung) on 2020-12-31 00:30:52 in reply to 30 [link] [source]

Ironically, it's your pipe character that's causing a problem with your post's formatting: click Edit, then click the "Markup Style" link to learn more about Markdown formatting, which will explain why it's being formatted thus.

Solution: put the pipe in backticks.

(32) By Trudge on 2020-12-31 16:19:50 in reply to 31 [link] [source]

Heh, what a newbie. Thanks eh! What should have appeared is the pipe symbol |.

(33) By little-brother on 2021-01-03 17:29:18 in reply to 28 [link] [source]

Looks like I solved the problem by a compiler update. I recompiled dlls and now it is safe from a virustotal's point of view.

(34) By MBL (UserMBL) on 2022-04-24 17:43:24 in reply to 5 [link] [source]

I tested a little version 1.7.2 (came from forum post about Drill-Down) and my first test results are quiet good.

Some hints for improvement:

The splitters of the screen areas can be moved and the areas re-sized ... but the mouse does not show at which points on screen. It is usual in such GUIs to change the mouse cursor into a splitter symbol while it hoovers over the sensible area.

(35) By little-brother on 2022-04-25 22:26:06 in reply to 34 [link] [source]

Thanks. I always forget about this improvement. I should it put on the top in To-Do list.

P.S. The best way to suggest smth is use Issues or email me at lb-im@ya.ru