So, under Sqlite2, if I want to update a database''s schema, I have to
dump the DB, modify the table''s structure, then go and modify EVERY
insert statement.
The insert statements look like (from memory):
INSERT INTO my_table VALUES (''some_id'',
''some_val'', '''', '''',
'''',
''some_other val'', '''', '''',
'''', '''')
Assuming a my_table def like:
CREATE TABLE my_table (
id,
something,
something_else,
another_something,
etc etc);
So, modifying those INSERT statements suck. vim helps a little bit
using record, but it''s still painful.
It would help a lot if I could get sqlite to dump its insert
statements in the following psuedoformat:
INSERT INTO my_table (id=''some_id'',
something=''some_val'', '''' =>
another_else'' .....)
Is that possible? Any other strategies on how to properly update the
schema of a sqlite db?
Thanks,
Joe
On 5/19/05, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> So, under Sqlite2, if I want to update a database''s schema, I have to > dump the DB, modify the table''s structure, then go and modify EVERY > insert statement.Maybe not the cleanest way in the world - but I''ve done the following 1) create the new table (table_new) 2) use INSERT INTO table_new SELECT .... FROM table_old 3) drop table_old 4) dump the database 5) replace table_new with table_old in the dump file (yes it''s a find and replace but that''s a lot simpler then modifying each insert statement) -- John W Higgins wishdev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
On 5/19/05, Michael Campbell <michael.campbell-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I''ve never used sqlite, but .. it has no "alter table" mechanism?AFAIK, that feature was introduced quite recently into Sqlite3. But I''ve heard that the ruby-sqlite3 bindings (or maybe just sqlite3) is still sorta buggy and not as fast as sqlite2.> > > On 5/19/05, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > So, under Sqlite2, if I want to update a database''s schema, I have to > > dump the DB, modify the table''s structure, then go and modify EVERY > > insert statement. > > > > The insert statements look like (from memory): > > INSERT INTO my_table VALUES (''some_id'', ''some_val'', '''', '''', '''', > > ''some_other val'', '''', '''', '''', '''') > > > > Assuming a my_table def like: > > CREATE TABLE my_table ( > > id, > > something, > > something_else, > > another_something, > > etc etc); > > > > So, modifying those INSERT statements suck. vim helps a little bit > > using record, but it''s still painful. > > > > It would help a lot if I could get sqlite to dump its insert > > statements in the following psuedoformat: > > INSERT INTO my_table (id=''some_id'', something=''some_val'', '''' => > > another_else'' .....) > > > > Is that possible? Any other strategies on how to properly update the > > schema of a sqlite db? > > > > Thanks, > > Joe > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > >
On 5/19/05, John Higgins <wishdev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 5/19/05, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > So, under Sqlite2, if I want to update a database''s schema, I have to > > dump the DB, modify the table''s structure, then go and modify EVERY > > insert statement. > > Maybe not the cleanest way in the world - but I''ve done the following > > 1) create the new table (table_new) > 2) use INSERT INTO table_new SELECT .... FROM table_oldI''ve not used this particular syntax before. So, if table_new had an additional field, data from table_old would insert into that?> 3) drop table_old > 4) dump the database > 5) replace table_new with table_old in the dump file (yes it''s a find > and replace but that''s a lot simpler then modifying each insert > statement)Sounds good and easily scriptable.
> > 2) use INSERT INTO table_new SELECT .... FROM table_old > > I''ve not used this particular syntax before. So, if table_new had an > additional field, data from table_old would insert into that? >It''s the same rules as INSERT - you either give it an explicit field list or the select statements can add "pseudo-columns" into the statement which would have default values for the new fields. Something like table_old col_a col _b col_c table_new col_a col_b col_d Either INSERT INTO table_new (col_a, col_b) SELECT col_a, col_b FROM table_old or.... INSERT INTO table_new SELECT col_a, col_b, "default value for col_d" FROM table_old -- John W Higgins wishdev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
On Thu, May 19, 2005 at 09:33:26AM -0700, Joe Van Dyk wrote:> So, under Sqlite2, if I want to update a database''s schema, I have to > dump the DB, modify the table''s structure, then go and modify EVERY > insert statement. > > The insert statements look like (from memory): > INSERT INTO my_table VALUES (''some_id'', ''some_val'', '''', '''', '''', > ''some_other val'', '''', '''', '''', '''') > > Assuming a my_table def like: > CREATE TABLE my_table ( > id, > something, > something_else, > another_something, > etc etc); > > So, modifying those INSERT statements suck. vim helps a little bit > using record, but it''s still painful. > > It would help a lot if I could get sqlite to dump its insert > statements in the following psuedoformat: > INSERT INTO my_table (id=''some_id'', something=''some_val'', '''' => > another_else'' .....) > > Is that possible? Any other strategies on how to properly update the > schema of a sqlite db?SQLite 2 does not support the ALTER TABLE SQL statement, but its fairly trivial to work around it. SQLite3 does support ALTER TABLE. (http://www.sqlite.org/lang_altertable.html) If you want to do it all within the database a table at a time, then something like this works, and it is pretty scriptable. -- create sample data sqlite test.db sqlite> create table my_table (id,d1,d2); sqlite> insert into my_table values (0,''a'',''a1''); sqlite> insert into my_table values (1,''b'',''b1''); sqlite> insert into my_table values (1,''c'',''c1''); -- backup the old table sqlite> create table my_table_backup as select id,d1,d2 from my_table; -- alter the original table sqlite> drop table my_table; sqlite> create table my_table (id,d1,d2,new_d); sqlite> insert into my_table select id,d1,d2,''default'' from my_table_backup; sqlite> drop table my_table_backup; enjoy, -jeremy -- ======================================================================= Jeremy Hinegardner jeremy-eg9WJcVQRd1EMKyauTCisw@public.gmane.org