[GRASS-user] Change SQLite DB Column Width?

[Even Rouault]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/grass-user/attachments/20170804/8ad4625f/attachment-0001.html>