SpatiaLite Ver. 2.4.0. GUI User Experience

[Gary_Gabriel]

Hi,

Thanks for the updates they succeeded well. I am familiarizing myself
with the GUI and changes.
I created temporary objects including a temporary view and table.
- 1) Is there a view or tree where the temporary objects are visible and
can be manipulated or viewed after creation?
- 2) Does the GUI offer creation of temporary objects? This is only a
question for clarification. Using SQL created the objects without any
problems.
- Can transactions be stored without storing each in a separate SQL
script file?

Version Input:
- I tried various functions of the query generator and they worked
satisfactorily.
- Using SQL to create the temporary objects generated the Message: "SQL
Query returned an empty ResultSet.
This is not an error.". It says that it is not an error message but I
interpreted it incorrectly to mean that the temporary objects did not
create. "SQL Query returned an empty ResultSet." is irritating and led
me to look for an error in generating the temporary objects. As 1)
relates I found no visual display and finally "SELECT * from
sqlite_temp_master". Brought the objects to light.

It is not a major issue- but in my opinion offering 1) and changing the
message by removing "SQL Query returned an empty ResultSet." would avoid
confusion and make the GUI more effective in handling temporary objects.
Objects successfully created would remove doubt.

SpatiaLite fulfills the requirements, functions reliably and offers good
functionality (Virtual Tables).

Thanks- Gary Gabriel

[a.furieri]

Hi Gary,

1) temp tables and views

yes, you are perfectly right: spatialite-gui ignored
at all any temporary object :-(
and this was because only 'sqlite_master' was queried
in order to populate the table tree view.
fix: the latest RC-1 now queries 'sqlite_temp_master'
as well, so temp objects are now correctly listed
in the tree view [same icons, but showing very faint
colors for temp objects]


2) transactions and SQL scripts

yes, you can freely store as much transactions
in the same SQL script-file as you need.
please note: nested transactions aren't supported
anyway in SQLite


3) message: "SQL Query returned an empty ResultSet.

This is not an error"

yes, I agree with you that this may be a little bit
confusing at first glance: but it's fully consistent
with similar SQL GUI tool implementations:

MySQLQueryBrowser
=======================================
after CREATE TABLE: "Query returned no resultset"
and after INSERT INTO: "1 row affected by the last
command, no resultset returned"

pgAdmin III [postgreSQL]
=======================================
after CREATE TABLE: "Query returned successfully
with no result in 176 ms"
and after INSERT INTO: "Query returned successfully:
1 rows affected, 187 ms execution time"

bye,
Sandro

[Gmail]

Hi Sandro,


Your reponse was good

> fix: the latest RC-1 now queries 'sqlite_temp_master'
> as well, so temp objects are now correctly listed
> in the tree view [same icons, but showing very faint
> colors for temp objects]
>

I downloaded: spatialite-gui-win-x86-1.3.0.zip to take a look at it. But
it was the same. Maybe I should have taken another one- but which one?

> 2) transactions and SQL scripts
>
> yes, you can freely store as much transactions
> in the same SQL script-file as you need.

This was not formulated clearly. To clarify: the commands are stored for
the session but there is no opportunity for persistent storage beyond
the session (that I found). Can the (individual) commands be
persistently stored for use during a later session? (For further
clarification on the point of storage of individual commands as opposed
to sql scripts in a file.) My experience relates that storing individual
commands in a file would not appeal as an expeditious action for the
user. Taking commands out of persistent storage in the GUI would appeal
to the user.

This may not be a design goal.

Another alternative would be for the user to copy the sql command into a
table for later and repeated use. However I in spite of referring to the
documentation I found no complete (typical operating system) key
commands for editing in the grid.

Simply to document Windows use:
1) A double click would open the row for editing.
2) In Windows a right click after highlighting the phase offers "Undo,
Cut, Copy, Paste, Paste without formatting, Delete ...". This works in
the SQL Execution window. But the right click does not function this way
in the grid. It offers row insertion and deletion. Extending the
functionality makes the GUI expedient for data entry and edit.

The current workflow as I experience it:
In the tree Right Click for "Edit Table Rows". But then the user cannot
select or order the rows to find the row to edit. Using the SQL
Execution with "order by" to order the column to find the row to edit
(or store) takes the user out of editing. A Right Click in a Grid Cell
offers "Delete Row" or "Insert new Row"; "Edit Row" is missing after
ordering the rows assuming that I have found the available user commands.

> 3) message: "SQL Query returned an empty ResultSet.
> This is not an error"
>
>

Your explanation makes it clear. The conventions should be respected.

Thanks for the active response- Gary

[Alessandro Furieri]

Hi Gabriel,
I've personally checked the fix just now:

1) I've downloaded
http://www.gaia-gis.it/spatialite-2.4.0/spatialite-gui-win-x86-1.3.0.zip

2) I've created a new DB

3) then I've launched the following SQL statements:
CREATE TEMPORARY TABLE tmptbl (
id INTEGER PRIMARY KEY NOT NULL,
name TEXT NOT NULL)

CREATE TEMPORARY VIEW tmpview AS
SELECT * FROM tmptbl

4) and immediately after performing a *refresh*
command on the tree list I'm able to see both
the temp-table and temp-view

so I cannot confirm: anything seems to work fine
as expected

===================

ok, I understand your point of view: and I agree
with you. Yes, storing complex SQL queries in a
persistent way within the DB itself may well be
a really useful option.

Obviously this will introduce some further complexity:
- we must set up some code (or name) to individually
identify each one "stored query"
- and define a short explanation/comment for each
stored query [think about trying to re-use a complex
and quite intricate query you've written several
months ago ...]
- and finally we need some smart dialog to search
withing the "stored queries" table in order to
retrieve the one we actually are looking for

All right, may well be this will be a milestone for
the next SpatiaLite release: your suggestion seems
quite useful to be implemented in the near future.

bye,
Sandro

[Gmail]

Hi Sandro,

> I've personally checked the fix just now:
>

Nicely done- I overlooked the obvious- to refresh. It fills the bill well.

> ==================
>
> ok, I understand your point of view: and I agree
> with you. Yes, storing complex SQL queries in a
> persistent way within the DB itself may well be
> a really useful option.
>
> Obviously this will introduce some further complexity:
> - we must set up some code (or name) to individually
> identify each one "stored query"
> - and define a short explanation/comment for each
> stored query [think about trying to re-use a complex
> and quite intricate query you've written several
> months ago ...]
>

Yeah- I know what you mean- I think I spend half of my life searching.
Maybe the following will provide some ideas and help along the way when
it comes to implementation.

CREATE TABLE DbStructure("id" INTEGER,"type" TEXT,"value" TEXT, "name"
TEXT, "tbl_name" TEXT, "rootpage" INTEGER, "db_name" TEXT, "logic" TEXT,
"sql_descrip" TEXT)

Field explanation:
id integer,
type- SQL type,
value SQL command ,
name- query name,
tbl_name- table relationship,
db_name- useful for any and all databaseses,
logic- quick explanation of the logic to understand how to use, change
and optimize,
sql_descrip- more details when needed

> - and finally we need some smart dialog to search
> withing the "stored queries" table in order to
> retrieve the one we actually are looking for
>

Using type- SQL type organized similarly to SQLite helps to find what
the user is looking for. SELECT, Table, UPDATE, INSERT, DELETE, View,
ATTACH, Procedure tags for the statements help to identify and find
quickly what is needed. When it comes time to designing and
implementation, and I can help- let me know. I'll be glad to do what I can.

You are moving the Release Candidates along well. Thanks for the great
job and all the effort. I enjoy using SpatiaLite as an important tool.

Take care- Gary