[GRASS-user] Change SQLite DB Column Width?

Jeshua_Lacock2 · August 3, 2017, 11:19pm

Greetings,

I am attempting to patch vectors together with v.patch -e (I need the attributes). But I am getting this error:

ERROR: Length of string columns differ

Upon inspecting the columns, I see that at least one vector has a NAME column with a width of 120 characters, while most of them have a width of 80:

db.describe table=TrailSegment_12

column:NAME
description:
type:CHARACTER
len:120
scale:0
precision:0
default:
nullok:yes
select:?
update:?

Is it possible to change the width of the columns? The DB is SQLite.

I tried altering the width using db.execute and this SQL:

ALTER TABLE TrailSegment_12
ALTER COLUMN NAME CHARACTER(128)

But I get the errors:

DBMI-SQLite driver error:
Error in sqlite3_prepare():
near "TrailSegment_Michigan": syntax error

ERROR: Error while executing: 'ALTER TABLE TrailSegment_12'

Seems like there must be a way. I found SQLite has a .width command, but I am not sure how to use it.

Thanks,

Jeshua Lacock
Founder/Engineer
<3DTOPO.com>
GlassPrinted.com

Vincent_Bain · August 4, 2017, 5:52am

Hello Jeshua,

to my knowledge, no way to alter a column in sqlite3 :
https://www.sqlite.org/lang_altertable.html

.width only affects the output width of columns within sqlite3 prompt.

I would suggest you to simply add a new column (with the right width),
then copy the content of the previous to the new one and trash the
former.

Hope this helps,
Vincent.

Le jeudi 03 août 2017 à 17:19 -0600, Jeshua Lacock a écrit :

Greetings,

I am attempting to patch vectors together with v.patch -e (I need the attributes). But I am getting this error:

ERROR: Length of string columns differ

Upon inspecting the columns, I see that at least one vector has a NAME column with a width of 120 characters, while most of them have a width of 80:

> db.describe table=TrailSegment_12

column:NAME
description:
type:CHARACTER
len:120
scale:0
precision:0
default:
nullok:yes
select:?
update:?

Is it possible to change the width of the columns? The DB is SQLite.

I tried altering the width using db.execute and this SQL:

ALTER TABLE TrailSegment_12
ALTER COLUMN NAME CHARACTER(128)

But I get the errors:

DBMI-SQLite driver error:
Error in sqlite3_prepare():
near "TrailSegment_Michigan": syntax error

DBMI-SQLite driver error:
Error in sqlite3_prepare():
near "TrailSegment_Michigan": syntax error

ERROR: Error while executing: 'ALTER TABLE TrailSegment_12'

Seems like there must be a way. I found SQLite has a .width command, but I am not sure how to use it.

Thanks,

Jeshua Lacock
Founder/Engineer
<3DTOPO.com>
GlassPrinted.com

_______________________________________________
grass-user mailing list
grass-user@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-user

Jeshua_Lacock2 · August 4, 2017, 7:32am

On Aug 3, 2017, at 11:52 PM, Vincent Bain <bain@toraval.fr> wrote:

to my knowledge, no way to alter a column in sqlite3 :
https://www.sqlite.org/lang_altertable.html

Hi Vincent,

Thanks for the verifying; I was afraid of that.

.width only affects the output width of columns within sqlite3 prompt.

I see, thank you.

I would suggest you to simply add a new column (with the right width),
then copy the content of the previous to the new one and trash the
former.

That is a great suggestion, thank you.

Is there a way to copy a column to a new column? It looks like db.copy copies a whole database, not just a column..

Will I have to use SQLite to do it? I’ve never actually used it directly before, so any examples would be greatly appreciated!

Best,

Jeshua Lacock
Founder/Engineer
<3DTOPO.com>
GlassPrinted.com

Vincent_Bain · August 4, 2017, 7:41am

Le vendredi 04 août 2017 à 01:32 -0600, Jeshua Lacock a écrit :

Will I have to use SQLite to do it? I’ve never actually used it directly before, so any examples would be greatly appreciated!

you can either operate from an sqlite3 session, or use the grass builtin
command db.execute. In your case, it would look like this:

        db.execute sql='update my_table set
        my_new_column=my_former_column'

V.

neteler · August 4, 2017, 7:53am

On Aug 4, 2017 9:41 AM, “Vincent Bain” <bain@toraval.fr> wrote:

Le vendredi 04 août 2017 à 01:32 -0600, Jeshua Lacock a écrit :

Will I have to use SQLite to do it? I’ve never actually used it directly before, so any examples would be greatly appreciated!

you can either operate from an sqlite3 session, or use the grass builtin
command db.execute. In your case, it would look like this:

db.execute sql=‘update my_table set
my_new_column=my_former_column’

In addition, this module should do the job as well:
https://grass.osgeo.org/grass72/manuals/v.db.update.html

Markus

rouault · August 4, 2017, 8:03am

On jeudi 3 août 2017 17:19:56 CEST Jeshua Lacock wrote:

Greetings,

I am attempting to patch vectors together with v.patch -e (I need the

attributes). But I am getting this error:

ERROR: Length of string columns differ

Upon inspecting the columns, I see that at least one vector has a NAME column with a width of 120 characters, while most of them have a width of 80:

db.describe table=TrailSegment_12

column:NAME

description:

type:CHARACTER

len:120

scale:0

precision:0

default:

nullok:yes

select:?

update:?

Is it possible to change the width of the columns? The DB is SQLite.

I tried altering the width using db.execute and this SQL:

ALTER TABLE TrailSegment_12

ALTER COLUMN NAME CHARACTER(128)

But I get the errors:

DBMI-SQLite driver error:

Error in sqlite3_prepare():

near “TrailSegment_Michigan”: syntax error

DBMI-SQLite driver error:

Error in sqlite3_prepare():

near “TrailSegment_Michigan”: syntax error

ERROR: Error while executing: ‘ALTER TABLE TrailSegment_12’

Seems like there must be a way. I found SQLite has a .width command, but I

am not sure how to use it.

As column width is just a hint in SQLite and has no influence on the database structure (you can insert fields that are longer than the declared size), you can just edit the sqlite_master table (which is generaly a dangerous game, and must be done only when you know what you are doing)

With the sqlite3 shell :

PRAGMA writable_schema = 1;

UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE TrailSegment_12(column_definitions_before_name, NAME CHARACTER(128), column_definitions_after_name) ’ WHERE NAME = ‘TrailSegment_12’;

PRAGMA writable_schema = 0;

so basically get the existing SQL definitions of the table with “SELECT SQL FROM SQLITE_MASTER WHERE NAME = ‘TrailSegment_12’”, edit it to change the column width, and put it in the above UPDATE

Even

–

Spatialys - Geospatial professional services

http://www.spatialys.com

Jeshua_Lacock2 · August 4, 2017, 10:22pm

On Aug 4, 2017, at 2:03 AM, Even Rouault <even.rouault@spatialys.com> wrote:

As column width is just a hint in SQLite and has no influence on the database structure (you can insert fields that are longer than the declared size), you can just edit the sqlite_master table (which is generaly a dangerous game, and must be done only when you know what you are doing)

With the sqlite3 shell :

PRAGMA writable_schema = 1;
UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE TrailSegment_12(column_definitions_before_name, NAME CHARACTER(128), column_definitions_after_name) ' WHERE NAME = 'TrailSegment_12';
PRAGMA writable_schema = 0;

so basically get the existing SQL definitions of the table with "SELECT SQL FROM SQLITE_MASTER WHERE NAME = 'TrailSegment_12'", edit it to change the column width, and put it in the above UPDAT

Hi Even,

Thanks for your help.

If I could figure out exactly what you mean, I think this might be the most elegant solution.

But when I run:

sqlite> SELECT sql FROM sqlite_master WHERE type = 'TrailSegment_12’;
sqlite>

It doesn’t display anything and just returns to the sqlite prompt.

Best,

Jeshua Lacock
Founder/Engineer
<3DTOPO.com>
GlassPrinted.com

rouault · August 5, 2017, 4:11pm

On vendredi 4 août 2017 16:22:57 CEST Jeshua Lacock wrote:

On Aug 4, 2017, at 2:03 AM, Even Rouault even.rouault@spatialys.com

wrote:

As column width is just a hint in SQLite and has no influence on the

database structure (you can insert fields that are longer than the

declared size), you can just edit the sqlite_master table (which is

generaly a dangerous game, and must be done only when you know what you

are doing)

With the sqlite3 shell :

PRAGMA writable_schema = 1;

UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE

TrailSegment_12(column_definitions_before_name, NAME CHARACTER(128),

column_definitions_after_name) ’ WHERE NAME = ‘TrailSegment_12’; PRAGMA

writable_schema = 0;

so basically get the existing SQL definitions of the table with "SELECT

SQL FROM SQLITE_MASTER WHERE NAME = ‘TrailSegment_12’", edit it to change

the column width, and put it in the above UPDAT

Hi Even,

Thanks for your help.

If I could figure out exactly what you mean, I think this might be the most

elegant solution.

But when I run:

sqlite> SELECT sql FROM sqlite_master WHERE type = 'TrailSegment_12’;

The condition is name = ‘TrailSegment_12’ not type =

sqlite>

It doesn’t display anything and just returns to the sqlite prompt.

Best,

Jeshua Lacock

Founder/Engineer

<3DTOPO.com>

GlassPrinted.com

–

Spatialys - Geospatial professional services

http://www.spatialys.com

Jeshua_Lacock2 · August 6, 2017, 9:56pm

On Aug 5, 2017, at 10:11 AM, Even Rouault <even.rouault@spatialys.com> wrote:

The condition is name = ‘TrailSegment_12’ not type =

Doh! Thanks - I had copied in your SQL but when it didn’t work I resorted to finding more examples on the web.

The reason why your suggested SQL wasn’t working was because I wasn’t setting the database connection with :

.open /Volumes/bigy/National/transportation/PERMANENT/sqlite/sqlite.db

I was able to reconfigure the schema as you suggested, and when I re-ran the SELECT SQL command, it showed the new size:

sqlite> SELECT SQL FROM SQLITE_MASTER WHERE NAME = 'TrailSegment_Wyoming’;

But v.patch -e still wouldn’t work. I looked at the table in the GUI and it still showed the old width of the column:

So as far as I can tell this solution won’t work, which is a bummer. I ended up getting around the issue by copying a target table, dropping all rows then inserting rows from the mismatched table (essentially as Vincent suggested).

I think it would be REALLY nice if we could supply a schema when importing with v.in.ogr. Apparently it allows you to specify the columns (but not specify their data type and width?). For instance I wish something like this worked:

v.in.ogr columns=“GNIS_ID varchar(80), NAME varchar(128), etc.”

That way, if as long as I used the same schema for different imports, they would all be combinable without any issue.

Best,

Jeshua Lacock
Founder/Engineer
<3DTOPO.com>
GlassPrinted.com