Spatialite GUI

[Duncan]

Hi

I'm using SpatialiteGUI 2.0 and 1.8

I use it for spatial as well as non spatial databases, but if I want to create a non spatial database in SpatialiteGUI I create the spatial metadata, which I may not want, but if create an empty database with sqlite, I can not open it in SpatialiteGUI and get an error Not a valid SQLite DB file: invalid magic number. 

This is the same in 1.8 and 2.0. I thought I used to be able to do this, but don't have an older version handy.

However, if I create a table in the new db in SQLite then I can open it in SpatialiteGUI

Is this intended behaviour?

I also noted that in 2.0 I cannot DROP a table using the Maintenance Menu, although it reports success, but the SQL works fine. This is OK in 1.8

Duncan 

[Jukka Rahkonen]

Hi,

How did you create the new database with SQLite?  I had a try with command
 sqlite2 test.sqlite

File "test.sqlite" file gets created but it has zero length and I verified with Notepad++ that the file has no contents at all. It can't be a valid SQLite db at that state.

I can also drop tables through the menu with the most recent spatialite-gui Windows version that I have, labelled 2.0.0 devel and obviously installed 16 June 2015. Which version and platform you have?

-Jukka Rahkonen-



Duncan wrote::

[a.fu...@lqt.it]

Hi Duncan and Jukka,

On Wed, 12 Aug 2015 01:22:21 -0700 (PDT), Jukka Rahkonen wrote:
> Hi,
>
> How did you create the new database with SQLite? I had a try with
> command
> sqlite2 test.sqlite
>
> File "test.sqlite" file gets created but it has zero length and I
> verified with Notepad++ that the file has no contents at all. It
> can't
> be a valid SQLite db at that state.
>

this is the usual SQLite's behaviour: simply establishing a connection
to a new DB is not enough to physically create and initialize the
corresponding file.
this action will always be postponed until some SQL statement will
be executed really requiring to access the db-file.
just few practical examples to understand better:

sqlite3 test1.sqlite
.quit

sqlite3 test2.sqlite
SELECT * FROM sqlite_master;
.quit

sqlite3 test3.sqlite
vacuum;
.quit

ls -l *.sqlite
-rw-r--r-- 1 sandro Administrators 0 Aug 12 09:02 test2.sqlite
-rw-r--r-- 1 sandro Administrators 1024 Aug 12 09:03 test3.sqlite

remarks;
- test1.sqlite does not exists, because no SQL command at all
was executed on its behalf.
- test2.sqlite is completely empty (zero length) because no
SQL command requiring a WRITE op was executed.
- only test3.sqlite has been completely initialized.

> I can also drop tables through the menu with the most recent
> spatialite-gui Windows version that I have, labelled 2.0.0 devel and
> obviously installed 16 June 2015. Which version and platform you
> have?
>

old versions of the GUI were effectively affected by a bug
sometimes causing confusion when dropping tables using the
GUI widgets.
this is supposed to be definitively resolved by the latest
version (2.0.0-devel).

> -Jukka Rahkonen-
>
> Duncan wrote::
>
>> Hi
>>
>> I'm using SpatialiteGUI 2.0 and 1.8
>> I use it for spatial as well as non spatial databases, but if I want
>> to create a non spatial database in SpatialiteGUI I create the
>> spatial metadata, which I may not want, but if create an empty
>> database with sqlite, I can not open it in SpatialiteGUI and get an
>> error Not a valid SQLite DB file: invalid magic number.
>>

every valid SQLite's DB-file must internally declare a well known
byte string at fixed position (aka "magic number"): spatialite_gui
will always check such "magic signature", and in case of failure
will absolutely refuse to open the failing file.

just for the sake of curiosity, the hexadecimal bytes found at the
very beginning (offset ZERO) of any SQLite DB are:

53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00

that once translated into its ASCII equivalent corresponds to:

"SQLite format 3"

the most obvious failure cause is the one imagined by Jukka;
a completely empty file will obviously fail to match the
expected "magic number" and will consequently discarded.

>> This is the same in 1.8 and 2.0. I thought I used to be able to do
>> this, but don't have an older version handy.
>> However, if I create a table in the new db in SQLite then I can open
>> it in SpatialiteGUI
>>
>> Is this intended behaviour?
>>
>> I also noted that in 2.0 I cannot DROP a table using the Maintenance
>> Menu, although it reports success, but the SQL works fine. This is
>> OK in 1.8
>>

updating to the most recent version should resolve this issue

bye Sandro

[Duncan]

 

  

I'm using 2.0.0 devel on Windows 7 64 bit dated 1 July. I also noted that it is a zero size file, but SQLite Studio opens it with no problem.

Thanks for the explanation, I can understand it now. I can create sample table and delete it after importing my data.

I could use SQLiteStudio but I was trying a workaround to have a db without full spatial_ref_sys.

I was unable to use the trick suggested in an earlier post to delete the spare records because of foreign key constraints

I'm still puzzled about the DROP Table problem but I'll try downloading it again

Duncan 

[Jukka Rahkonen]

Duncan wrote:

> Thanks for the explanation, I can understand it now. I can create
> sample table and delete it after importing my data.

As Sandro wrote, running vacuum leads to write to the database and I
tested that after that the minimal 1024 byte sized db file opens with
spatialite-gui.

-Jukka Rahkonen-

[Duncan]

As Sandro wrote, running vacuum leads to write to the database and I
tested that after that the minimal 1024 byte sized db file opens with
spatialite-gui.

I missed that one, it seems to be a better option

thanks