Sqlite Query Browser [UPDATE V1.03]

[CSAUER]

Hi,

I would like to announce my latest public development with PB:
A Sqlite query browser, where you can manage tables and build queries by clicking through table structure.
Actually I don't have an exact plan for program's future (maybe bump it to a sell-able version), but with the actual version, I would like to give a present to the PB community as a warm thank you for all your kind support in PB questions.

UPDATED VERSION V1.01 - it supports:
- assembling "UPDATE", "INSERT", "DELETE" querries
- editing of query result with a double click on the list-item
- more datatypes (beside the official one, requested by GG)
- default values for field setup
- multi-languages
- select language on INI-file
- ressources are stored on wsqb.db file, you can edit with the tool
- if language or ressource-text is not supported, it will be added automatically/instantly/once to the database, when the program comes to the point where it request a text ressource

UPDATED VERSION V1.02 - it supports:
- a recent database history
- a recent query history
- export of query result
- import of query
- export of database dump (optionally including data)
- view indexes on database tree (optionally)
- German/English and experimental French language support (via preferences file)

UPDATED VERSION V1.03 - it supports:
- it should support Unicode
- supports views
- "make view of actual query" function
- a few bug fixes
- it's based on WUI library (skinned surface is deactivated for community edition) - to be consistent with other PB tools

Here you can find the Windows/Mac download in one ZIP: http://www.xideas.de/purebasic/WSQB_PBC ... V10300.zip

It includes a Windows and an experimental Mac version. Please feel free to test/use it.
I am looking forward to your comment, bug reports and suggestions.

Thanks community und PB-team for your great work.
CSAUER

[srod]

Looks great. Just completed a quick test and it works fine.

Thanks for this; could be very useful indeed.

[rsts]

Yes. Looks very nice.

Should help me quite a bit.

Thanks for sharing

cheers

[CSAUER]

Thanks for your first response.

I am not sure, if you figured out:
If you left-double-click on a tree gadget element, you can add it to the query string according to your selected option:
- SELECT: Adds field to displayed field list (double click on table does a complete * select)
- FROM: Adds table to table list
- WHERE: Adds field to where clause
etc.

This function is only basic, actually. Maybe there is a lot optimization required.
It is a bit like "MySQL Query Browser". One feature is "better", because my program shows indexed fields directly on the tree view.

[srod]

I am not sure, if you figured out: ...

Certainly did!

As I say this could be very useful indeed. I have a similar utility which I've used on occasion, but it no longer runs with the latest version of SQLite. This is where your utility steps in to help!

Any chance you might add 'Insert into', 'Update' and 'Delete from' options to the query builder? I know we can type such commands directly into the SQL edit control, but they could be useful additions nevertheless. It's always useful to be able to add a few records this way in order to get the database started etc.

Thanks again.

[CSAUER]

Yes, srod. It's been on my todo list.
Aftert your request, I moved the topic to the top. So you can expect it on the next release.
Maybe you can give some feedback on behavior of this query composing feature. Is there anything to optimize?

Another topic is to edit/add query data. This will be a bigger deal, but I have already some ideas, how to do it platform compatible with standard PB commands.

Cheers
CSAUER

[gnozal]

I don't currently use SQLite, but I guess that I would appreciate some 'Generate code' button, wich would generate the PB source code necessary to create the current database structure.

[Fangbeast]

@CSAUER Tried your program out today, pretty good. Shouldn't the fields in the tree have names? If so, they all remain blank, just the icons are there.

@Gnozal, that sounds like a great idea, don't know why I didn't think of it 2 years ago when I made a similar program (different feature set and theology). Damnit, it would have saved me months of time!!!

[GG]

I have a problem with my SQLite DB (3.4.x).
I have a table named : Client, which has 8 fields. Only the first column is printed on the table editor.

The table DDL is :

CREATE TABLE [Client] (
[libcli] VARCHAR(50) NULL,
[nocli] INTEGER NULL,
[noos] INTEGER NOT NULL,
[nosgbd] INTEGER NOT NULL,
[entete] TEXT NULL,
[purge] INTEGER NULL,
[instexec] INTEGER NULL,
[minilib] VARCHAR(15) NULL
)

Same thing with this table (only 2 first columns printed) :

CREATE TABLE [Sgbd] (
[nosgbd] INTEGER NULL,
[libsgbd] VARCHAR(30) NULL,
[caracEOL] VARCHAR(5) NULL,
[instexec] VARCHAR(15) NULL
)

...

OK, i found the problem while writing this post : if a field is defined by VARCHAR(xx) NULL, your program is not able to find next columns (example of SGBD table : it only prints nosgbd and libsgbd which is varchar(30) null. Next columns won't be printed).

If you run a request (in my example : select * from sgbd), results are correct and all columns are well printed.
1/ Any chance to correct that ?
2/ Another thing, when you have results of your SQL requests, would it be possible to size the column at least the size of its title if column is too narrow (for example : column title is 15 caracters and column value is 2 caracters long) ?
3/ Multilanguage ?
4/ Does your program print indexes ?
5/ Possible to sort results by double clicking on column names ? (PureLvSort... )
I don't know if my explanations are clear...

[CSAUER]

@GG: 1) I didn't know, that VARCHAR is a valid field format for sqlite (I know it from MySQL). I found following list of field types for V3.x versions on sqlite.org website:

* NULL. The value is a NULL value.
* INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
* REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
* TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
* BLOB. The value is a blob of data, stored exactly as it was input.

I will add it with the next version.
2) Actually the columns are sizing according to biggest value in column. Maybe I should find some balance between column-header and value.
3) Multilanguage is prepared but not implemented yet. Actually planned languages are English and German, others are welcome, if someone would translate. I will base language-codes on a sqlite-database, so that you can add additional languages with the tool with ease.
4) Indexes are shown on the icon and on the table properties (column index). A separate view to indexes is actually not implemented, but planned.
5) I am thinking on sorting table, but I want to have it cross-platform, so PureLVSort is actually no option (it uses API calls). Another solution could be my CSGrid, but actually I want to keep standard controls. Maybe I do it the way by improving the query expression, when selecting a column header.

@Fangbeast: Of course, it should show field names behind icons. I extract them from field "sql" of table "sqlite_master", as well as field types and other settings. This field is a "CREATE TABLE" command. Could you please pass a example database? (Use sqlite.exe and command ".dump > db.sql")

@Gnozal: I put it on my todo-list.

I will release a new version with a couple of additions this evening. It will not feature all your request, but it will bring f.ex. editing of result-data. Hopefully it compiles on Mac as well... (which is actually like lotto)

See you this evening back on this topic.
CSAUER

[GG]

1/ OK. But i think there are other one : BOOLEAN, TIME, DATE, NVARCHAR.
Example on my SQlite db :
CREATE TABLE [Ferie] (
[id_masterf] INTEGER NULL,
[datef] DATE NULL
)
3/ I'm ready for doing French translation.

[CSAUER]

Ok, here I am back again to announce a new version 1.01
It supports:
- assembling "UPDATE", "INSERT", "DELETE" querries
- editing of query result with a double click on the list-item
- more datatypes (beside the official one, requested by GG)
- default values for field setup
- multi-languages
- select language on INI-file
- ressources are stored on wsqb.db file, you can edit with the tool
- if language or ressource-text is not supported, it will be added automatically/instantly/once to the database, when the program comes to the point where it request a text ressource

@GG: It would be fine, if you would do French translation and share wsqb.db file

The download is updated on above mentioned link.

I am looking forward to your feedback.

CSAUER

[GG]

French translation will be done for tomorrow.

[GG]

With 1.01.00 version, I have the same problem.
Example, with the following DDL :

CREATE TABLE [Client] (
[libcli] VARCHAR(50) NULL,
[nocli] INTEGER NULL,
[noos] INTEGER NOT NULL,
[nosgbd] INTEGER NOT NULL,
[entete] TEXT NULL,
[purge] INTEGER NULL,
[instexec] INTEGER NULL,
[minilib] VARCHAR(15) NULL
)

On the left (window under the button add table, edit table, delete table), I have the list of tables in my database.
When I click on this table, only libcli is printed, with 2 small rectangles ont its left.

+ Client
----|| [libcli]

Any idea ?

[CSAUER]

Okay, I found out where it comes from:
It's because of the line-feeds and VARCHAR(). The program found ")" and thought the expression is finished. I changed this and your example runs well on the newly uploaded revised version 1.01.01.

Maybe this solved the problem from fangbeast as well?