bug with auto increment primary key

[pcr...@yahoo.com]

Hi,

I'm not sure where to report this, and figured this as good a place as any...

I create a table with an autoincrementing primary key. This seems to work fine, and is supposed to be an alias for the Sqlite3 rowid value.

However, if connected with spatialite I type .schema <table> I just get an error message, "Error: no such column: rowid"

The same command used in sqlite3 gives me the correct  output.

So spatialite is not recognising the link to the underlying rowid for some reason.

Thanks,

Brent Wood

[Brad Hards]

On Thursday, 15 December 2022 11:42:12 AM AEDT 'pcr...@yahoo.com' via

SpatiaLite Users wrote:
> Hi,
>
> I'm not sure where to report this, and figured this as good a place as
> any...
>
> I create a table with an autoincrementing primary key. This seems to work
> fine, and is supposed to be an alias for the Sqlite3 rowid value.

Can you show the exact command(s) you used, and what version of spatialite
(and other tools, if applicable) you are using?

Brad

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

On Wed, 14 Dec 2022 16:42:12 -0800 (PST), 'pcr...@yahoo.com' via
SpatiaLite Users wrote:
> Hi,
>
> I'm not sure where to report this, and figured this as good a place
> as
> any...
>
> I create a table with an autoincrementing primary key. This seems to
> work fine, and is supposed to be an alias for the Sqlite3 rowid
> value.
>

> However, if connected with spatialite I type .schema I just get an

> error message, "Error: no such column: rowid"
>
> The same command used in sqlite3 gives me the correct output.
>
> So spatialite is not recognising the link to the underlying rowid for
> some reason.
>

Hi Brent,

you are obviously speaking about the Command Line backend (spatialite
CLI)
and its related dot macros (.read, .help, .schema, .nullvalue and
alike).

note well: all them are not supported by any underlying library but are
directly implemented by the CLI backend itself.

please see:
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=dot+macros

a bit of history: about 10 years ago trying to load SpatiaLite as a
dynamic extension to SQLite used to be a painful experience,
particularly on Windows.
so at the time forking a specialized spatialite CLI backend that
automatically initialized the extension seemed to be a good idea.
it was instead a blatant error, because the CLI backend of SQLite
continued to evolve in the meanwhile while updating the SpatiaLite's
own CLI backend quicly proved to be an impossible task.

conclusion: that the dot macros implemented by the spatialite CLI
backend today often suck compared to those of SQLite is not at all
a surprise, it is absolutely obvious.

-------------------------

how to solve the problem in the simplest way.

A) nowadays loading SpatiaLite as a dynamic extension has become
a reasonably simple process even on Windows.

B) almost all the dot macros specifically supported by SpatiaLite
itself are now availabla as SQL functions. please see:

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.2.1+functions

so the most elegant and effective way to use SpatiaLite in a CLI
backend environment is to directly call the SQLite own CLI backend
and then loading SpatiaLite as a dynamic extension.
this way you'll get all the Spatial SQL support and keep all the
usefull SQLite "vanilla" dot macros unchanged.

there is no real reason to still continue to use the SpatiaLite's
own CLI backend; it still continues to be supported just for
historical compatibility, but nowadays it's become obsolete.

further reading:
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite

bye Sandro