Opened 11 years ago

Closed 7 years ago

#391 closed defect (fixed)

SQLite driver doesn't complain enough with stupid mistakes

Reported by: hamish Owned by: grass-dev@…
Priority: major Milestone: 6.4.0
Component: Database Version: svn-develbranch6
Keywords: sqlite Cc:
CPU: Unspecified Platform: Linux

Description

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

Change History (5)

comment:1 Changed 11 years ago by 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

comment:2 in reply to:  1 ; Changed 11 years ago by 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.

comment:3 in reply to:  1 Changed 11 years ago by 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.

comment:4 in reply to:  2 Changed 11 years ago by 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

comment:5 Changed 7 years ago by neteler

Resolution: fixed
Status: newclosed

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.

Note: See TracTickets for help on using tickets.