SQLite driver doesn't complain enough with stupid mistakes

[hamish]

Using the SQLite driver, the following v.db.addcol command runs without error:

G64:spearf> g.copy vect=archsites,tmp_arch
G64:spearf> v.db.addcol tmp_arch column='x double y double'

(typo, no comma between column defs)

but when you try and do something with it you get an error:

G64:spearf> v.info -c tmp_arch
Displaying column types/names for database connection of layer 1:
WARNING: SQLite driver: unable to parse decltype: double y double
WARNING: SQLite driver: unable to parse decltype: double y double
WARNING: SQLite driver: column 'x', SQLite type 2 is not supported
INTEGER|cat
CHARACTER|str1

a fatal error from v.db.addcol or from the driver would be nice.

Hamish

[neteler]

If desired in the SQLite driver, only SQL parsing might solve the problem (complicated).

For now, in r34809 I have added a check to v.db.addcol to reject odd column definitions comparing number of tokens against number of desired columns to be created.

Someone needs to forward-port to trunk/Python this test if desired.

Markus

[glynn]

Replying to neteler:

If desired in the SQLite driver, only SQL parsing might solve the problem (complicated). For now, in r34809 I have added a check to v.db.addcol to reject odd column definitions comparing number of tokens against number of desired columns to be created.

So presumably it won't accept column types which contain spaces ("double precision", "character varying", "varchar (50)", etc)?

Someone needs to forward-port to trunk/Python this test if desired.

Not desired.

[glynn]

Replying to neteler:

If desired in the SQLite driver, only SQL parsing might solve the problem (complicated).

The main problem is that, although DBMI specifies an "add column" method, none of the drivers implement it. So v.db.addcol just executes an SQL "ALTER TABLE ..." statement.

So the only way to trap this in the driver would be to parse every SQL statement which is passed to it, which I don't expect to happen.

BTW, even if all of the drivers were updated to implement the add_column method, and a corresponding db.addcol module was created, you could still get exactly the same problem from people/code adding columns or creating tables manually via e.g. db.execute or the sqlite3 command.

[neteler]

Replying to glynn:

Replying to neteler:

If desired in the SQLite driver, only SQL parsing might solve the problem (complicated). For now, in r34809 I have added a check to v.db.addcol to reject odd column definitions comparing number of tokens against number of desired columns to be created.

So presumably it won't accept column types which contain spaces ("double precision", "character varying", "varchar (50)", etc)?

Right. r34809 reverted.

Markus

[neteler]

The intentionally broken command (missing comma) from the original ticket is no longer accepted:

GRASS 6.4.3svn (spearfish60):~ > v.db.addcol tmp_arch column='x double y double'
DBMI-SQLite driver error:
Error in sqlite3_prepare():
duplicate column name: x

ERROR: Error while executing: 'ALTER TABLE tmp_arch ADD COLUMN x double y
       double
       '
ERROR: Cannot continue (problem adding column).

Hence the ticket can be closed.