Re: [GRASS-user] Change SQLite DB Column Width?
On vendredi 4 août 2017 16:22:57 CEST Jeshua Lacock wrote: > > On Aug 4, 2017, at 2:03 AM, Even Rouault > > 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 ___ grass-user mailing list grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Change SQLite DB Column Width?
> On Aug 4, 2017, at 2:03 AM, Even Rouault 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 ___ grass-user mailing list grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Change SQLite DB Column Width?
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 ___ grass-user mailing list grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Change SQLite DB Column Width?
On Aug 4, 2017 9:41 AM, "Vincent Bain" 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 ___ grass-user mailing list grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Change SQLite DB Column Width?
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. ___ grass-user mailing list grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Change SQLite DB Column Width?
> On Aug 3, 2017, at 11:52 PM, Vincent Bain 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 ___ grass-user mailing list grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
Re: [GRASS-user] Change SQLite DB Column Width?
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 ___ grass-user mailing list grass-user@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/grass-user
