Re-ordering columns in an sqlite database
I know it isn't really necessary as it doesn't actually affect anything (except my sensibilities) but I'd like the ability to re-order columns in some sqlite databases. Well, it *does* have some advantages, it would mean I can do things like "SELECT * FROM database" and get the columns displayed in the order I want rather than the order resulting from database creation followed by adding extra columns as I found I needed them. So, is there any way to re-order columns other than by creating a new table with the required column order, copying all the data across, deleting the old table and renaming the new one? I can't find any neat utilities out there to do this for me. -- Chris Green
Chris G wrote:
I know it isn't really necessary as it doesn't actually affect anything (except my sensibilities) but I'd like the ability to re-order columns in some sqlite databases.
Well, it *does* have some advantages, it would mean I can do things like "SELECT * FROM database" and get the columns displayed in the order I want rather than the order resulting from database creation followed by adding extra columns as I found I needed them.
That would be crazy as every time you wanted a different order you would have to re-order the tables and then every time you insert a row, guess what....you would have to re-order the tables again. The correct way to do this surely is to order results on your queries not on the data itself SELECT * FROM table ORDER BY column of course you can also specify multiple columns so if say you wanted to surname and then date of birth then two identical surnames would be ordered with the oldest one first. it's naturally also possible to reverse the order etc
On 25 Mar 2010, at 23:14, Wayne Stallwood <ALUGlist@digimatic.co.uk> wrote:
Chris G wrote:
I know it isn't really necessary as it doesn't actually affect anything (except my sensibilities) but I'd like the ability to re-order columns in some sqlite databases.
Well, it *does* have some advantages, it would mean I can do things like "SELECT * FROM database" and get the columns displayed in the order I want rather than the order resulting from database creation followed by adding extra columns as I found I needed them.
That would be crazy as every time you wanted a different order you would have to re-order the tables and then every time you insert a row, guess what....you would have to re-order the tables again.
The correct way to do this surely is to order results on your queries not on the data itself
SELECT * FROM table ORDER BY column
of course you can also specify multiple columns so if say you wanted to surname and then date of birth then two identical surnames would be ordered with the oldest one first. it's naturally also possible to reverse the order etc
Wayne, I think Chris was talking about reordering the *columns* not the *rows* ;) Chris, You could dump the data, recreate the table with the columns in the order you want and then reimport the data, or you could just use SELECT with the fields in the order you want ;) -- David Reynolds david@reynoldsfamily.org.uk
On Thu, Mar 25, 2010 at 11:41:30PM +0000, David Reynolds wrote:
On 25 Mar 2010, at 23:14, Wayne Stallwood <ALUGlist@digimatic.co.uk> wrote:
Chris G wrote:
I know it isn't really necessary as it doesn't actually affect anything (except my sensibilities) but I'd like the ability to re-order columns in some sqlite databases.
Well, it *does* have some advantages, it would mean I can do things like "SELECT * FROM database" and get the columns displayed in the order I want rather than the order resulting from database creation followed by adding extra columns as I found I needed them.
That would be crazy as every time you wanted a different order you would have to re-order the tables and then every time you insert a row, guess what....you would have to re-order the tables again.
The correct way to do this surely is to order results on your queries not on the data itself
SELECT * FROM table ORDER BY column
of course you can also specify multiple columns so if say you wanted to surname and then date of birth then two identical surnames would be ordered with the oldest one first. it's naturally also possible to reverse the order etc
Wayne,
I think Chris was talking about reordering the *columns* not the *rows* ;)
Chris,
You could dump the data, recreate the table with the columns in the order you want and then reimport the data, or you could just use SELECT with the fields in the order you want ;)
Yes, I think that's the only way to do it. I can do a *little* re-ordering with sqlitebrowser because if I use its ability to add a column to a table the new column is added as the first column whereas if I add a column using ALTER TABLE x ADD COLUMN then the column is added as the last column. Thus with a little work columns can be moved around and, in particular, 'important' columns can be moved to the front. I'd love to know how sqlitebrowser's internal column editing manages to add columns at the front. -- Chris Green
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Chris G wrote:
I can do a *little* re-ordering with sqlitebrowser because if I use its ability to add a column to a table the new column is added as the first column whereas if I add a column using ALTER TABLE x ADD COLUMN then the column is added as the last column. Thus with a little work columns can be moved around and, in particular, 'important' columns can be moved to the front.
I'd love to know how sqlitebrowser's internal column editing manages to add columns at the front.
If it's anything like MySQL, then you can specify a FIRST or AFTER modifyer, e.g. given Field1 Field2 Field3 Field4 then alter table mytable add Field2andahalf varchar(20) after Field2; would stick the new field between Field2 and Field3. Simon - -- - --------------------------------------------------------------------- Simon Ransome http://nosher.net Photography RSS feed - http://nosher.net/images/images.rss -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mandriva - http://enigmail.mozdev.org/ iD8DBQFLrI/S8tdcY+OcZZwRAjpqAJ9oeGuTwJx5tY9IqP+kZe9v9g7mhQCgvrO7 BTvGUYXxgF3pGfTZxy3AyNE= =FLNZ -----END PGP SIGNATURE-----
On 26 Mar 10:43, simon ransome wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Chris G wrote:
I can do a *little* re-ordering with sqlitebrowser because if I use its ability to add a column to a table the new column is added as the first column whereas if I add a column using ALTER TABLE x ADD COLUMN then the column is added as the last column. Thus with a little work columns can be moved around and, in particular, 'important' columns can be moved to the front.
I'd love to know how sqlitebrowser's internal column editing manages to add columns at the front.
If it's anything like MySQL, then you can specify a FIRST or AFTER modifyer, e.g. given
Field1 Field2 Field3 Field4
then
alter table mytable add Field2andahalf varchar(20) after Field2;
would stick the new field between Field2 and Field3.
Doesn't work in SQLite, which would just make the field type... "varchar(20) after Field2" Yes. Really. In case one wasn't aware, all fields in sqlite are text. Nothing else. It doesn't do type checking. -- Brett Parker
On Fri, Mar 26, 2010 at 10:55:59AM +0000, Brett Parker wrote:
On 26 Mar 10:43, simon ransome wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Chris G wrote:
I can do a *little* re-ordering with sqlitebrowser because if I use its ability to add a column to a table the new column is added as the first column whereas if I add a column using ALTER TABLE x ADD COLUMN then the column is added as the last column. Thus with a little work columns can be moved around and, in particular, 'important' columns can be moved to the front.
I'd love to know how sqlitebrowser's internal column editing manages to add columns at the front.
If it's anything like MySQL, then you can specify a FIRST or AFTER modifyer, e.g. given
Field1 Field2 Field3 Field4
then
alter table mytable add Field2andahalf varchar(20) after Field2;
would stick the new field between Field2 and Field3.
Doesn't work in SQLite, which would just make the field type...
"varchar(20) after Field2"
Yes. Really.
Well when I tried it I got an error message:- sqlite> alter table companies add newColumn varchar(20) after Place; SQL error: near "after": syntax error sqlite>
In case one wasn't aware, all fields in sqlite are text. Nothing else. It doesn't do type checking.
Are you sure you're not talking about sqlite version 2? I've just tried putting text into an integer column on a sqlite 3 database and it gives an error:- sqlite> pragma table_info(Companies); 0|Email|TEXT|0||0 1|Address|TEXT|0||0 2|Place|TEXT|0||0 3|Contact|TEXT|0||0 4|Tel|TEXT|0||0 5|Web|TEXT|0||0 6|Comment|TEXT|0||0 7|Category|TEXT|0||0 8|Name|TEXT|0||0 9|id|INTEGER|0||1 sqlite> select id, name from Companies where Name = 'Landrau'; 60|Landrau sqlite> sqlite> update Companies set id = 'ABCDE' where name = 'Landrau'; SQL error: datatype mismatch sqlite> -- Chris Green
On 26 Mar 12:37, Chris G wrote:
On Fri, Mar 26, 2010 at 10:55:59AM +0000, Brett Parker wrote:
On 26 Mar 10:43, simon ransome wrote:
Chris G wrote:
I can do a *little* re-ordering with sqlitebrowser because if I use its ability to add a column to a table the new column is added as the first column whereas if I add a column using ALTER TABLE x ADD COLUMN then the column is added as the last column. Thus with a little work columns can be moved around and, in particular, 'important' columns can be moved to the front.
I'd love to know how sqlitebrowser's internal column editing manages to add columns at the front.
If it's anything like MySQL, then you can specify a FIRST or AFTER modifyer, e.g. given
Field1 Field2 Field3 Field4
then
alter table mytable add Field2andahalf varchar(20) after Field2;
would stick the new field between Field2 and Field3.
Doesn't work in SQLite, which would just make the field type...
"varchar(20) after Field2"
Yes. Really.
Well when I tried it I got an error message:-
sqlite> alter table companies add newColumn varchar(20) after Place; SQL error: near "after": syntax error sqlite>
In case one wasn't aware, all fields in sqlite are text. Nothing else. It doesn't do type checking.
Are you sure you're not talking about sqlite version 2? I've just tried putting text into an integer column on a sqlite 3 database and it gives an error:-
brettp@miranda:~$ sqlite3 test.db SQLite version 3.5.9 Enter ".help" for instructions sqlite> create table test ( ...> id int, ...> test text); sqlite> insert into test(id,test) values('54', 3); sqlite> select * from test; 54|3 sqlite> alter table test add column splat varchar(100) after id; SQL error: near "after": syntax error sqlite> alter table test add column after id splat varchar(100); sqlite> .schema test CREATE TABLE test ( id int, test text, after id splat varchar(100)); sqlite> insert into test(id,test) values('spank', 3); sqlite> .schema test CREATE TABLE test ( id int, test text, after id splat varchar(100)); sqlite> select * from test; 54|3| spank|3| sqlite> (OK, so getting the syntax the wrong way round gets you broken things...) But, see, type checking... where? :) -- Brett Parker
On Fri, Mar 26, 2010 at 10:43:33AM +0000, simon ransome wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Chris G wrote:
I can do a *little* re-ordering with sqlitebrowser because if I use its ability to add a column to a table the new column is added as the first column whereas if I add a column using ALTER TABLE x ADD COLUMN then the column is added as the last column. Thus with a little work columns can be moved around and, in particular, 'important' columns can be moved to the front.
I'd love to know how sqlitebrowser's internal column editing manages to add columns at the front.
If it's anything like MySQL, then you can specify a FIRST or AFTER modifyer, e.g. given
Field1 Field2 Field3 Field4
then
alter table mytable add Field2andahalf varchar(20) after Field2;
would stick the new field between Field2 and Field3.
No, sqlite3 won't have that, it just says 'near "after": syntax error'. -- Chris Green
On 26/03/2010 12:26, Chris G wrote:
On Fri, Mar 26, 2010 at 10:43:33AM +0000, simon ransome wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Chris G wrote:
I can do a *little* re-ordering with sqlitebrowser because if I use its ability to add a column to a table the new column is added as the first column whereas if I add a column using ALTER TABLE x ADD COLUMN then the column is added as the last column. Thus with a little work columns can be moved around and, in particular, 'important' columns can be moved to the front.
I'd love to know how sqlitebrowser's internal column editing manages to add columns at the front.
If it's anything like MySQL, then you can specify a FIRST or AFTER modifyer, e.g. given
Field1 Field2 Field3 Field4
then
alter table mytable add Field2andahalf varchar(20) after Field2;
would stick the new field between Field2 and Field3.
No, sqlite3 won't have that, it just says 'near "after": syntax error'.
Hi Chris, You could create a view with the columns ordered as you want, then do your selects on the view instead. eg CREATE VIEW viewname AS SELECT column1,column2,column3 FROM tablename WHERE.... then SELECT * FROM viewname will give you the columns in the order you want. See here http://www.sqlite.org/lang_createview.html HTH Chris
On Fri, Mar 26, 2010 at 12:31:09PM +0000, Chris Glover wrote:
On 26/03/2010 12:26, Chris G wrote:
On Fri, Mar 26, 2010 at 10:43:33AM +0000, simon ransome wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Chris G wrote:
I can do a *little* re-ordering with sqlitebrowser because if I use its ability to add a column to a table the new column is added as the first column whereas if I add a column using ALTER TABLE x ADD COLUMN then the column is added as the last column. Thus with a little work columns can be moved around and, in particular, 'important' columns can be moved to the front.
I'd love to know how sqlitebrowser's internal column editing manages to add columns at the front.
If it's anything like MySQL, then you can specify a FIRST or AFTER modifyer, e.g. given
Field1 Field2 Field3 Field4
then
alter table mytable add Field2andahalf varchar(20) after Field2;
would stick the new field between Field2 and Field3.
No, sqlite3 won't have that, it just says 'near "after": syntax error'.
Hi Chris,
You could create a view with the columns ordered as you want, then do your selects on the view instead.
eg
CREATE VIEW viewname AS SELECT column1,column2,column3 FROM tablename WHERE....
then SELECT * FROM viewname will give you the columns in the order you want.
That's a point, I'd not thought of using views, thank you. -- Chris Green
On 25-Mar-10 23:14:53, Wayne Stallwood wrote:
Chris G wrote:
I know it isn't really necessary as it doesn't actually affect anything (except my sensibilities) but I'd like the ability to re-order columns in some sqlite databases.
Well, it *does* have some advantages, it would mean I can do things like "SELECT * FROM database" and get the columns displayed in the order I want rather than the order resulting from database creation followed by adding extra columns as I found I needed them.
That would be crazy as every time you wanted a different order you would have to re-order the tables and then every time you insert a row, guess what....you would have to re-order the tables again.
The correct way to do this surely is to order results on your queries not on the data itself
SELECT * FROM table ORDER BY column
of course you can also specify multiple columns so if say you wanted to surname and then date of birth then two identical surnames would be ordered with the oldest one first. it's naturally also possible to reverse the order etc
Your "ORDER BY column" specifies that the rows will be ordered according to a sort on the contents of column "column". If I understand Chris aright, he has created a database table with columns firstname lastname DOB Street Salary Country Phone Town HouseNo ... as a result of adding extra columns higgledy-piggledy after initially creating a 3-column table with "firstname" "lastname" "DOB" in the desired order, and he wanted the left-to-right order of the columns to be different, i.e. as really desired, not as created. I don't know of any way to make "SELECT *" output the columns in anything other than the order in which they occur in the table. One can of course do it the hard way: SELECT firstname lastname DOB \ HouseNo Street Town Country Phone Salary FROM ... But I'm wondering (though I haven't been able to locate the syntax) if it's possible to define a permanent variable, say Cols, somewhere which is a list of the columns in the desired order, and then SELECT Cols from ... That way, when the next unmatched sock is stuffed into the drawer (i.e. a new column is added), all that is needed is to update the variable Cols. Ted. -------------------------------------------------------------------- E-Mail: (Ted Harding) <Ted.Harding@manchester.ac.uk> Fax-to-email: +44 (0)870 094 0861 Date: 25-Mar-10 Time: 23:48:23 ------------------------------ XFMail ------------------------------
On 25 Mar 20:00, Chris G wrote:
I know it isn't really necessary as it doesn't actually affect anything (except my sensibilities) but I'd like the ability to re-order columns in some sqlite databases.
Well, it *does* have some advantages, it would mean I can do things like "SELECT * FROM database" and get the columns displayed in the order I want rather than the order resulting from database creation followed by adding extra columns as I found I needed them.
So, is there any way to re-order columns other than by creating a new table with the required column order, copying all the data across, deleting the old table and renaming the new one?
I can't find any neat utilities out there to do this for me.
When are you using a sqlite database and interfacing to it with something that isn't a programming language? Most languages with a database api will allow you to collect results from the database in a 'hashmap' or 'dictionary' with the column name as the key. Also, if you're adding lots of columns, you probably want to be more selective which are displayed if you are accessing it from the command line sqlite program, i.e. using: SELECT field1,field2 from mytable where <some condition>; Rather than the * approach. Thanks, -- Brett Parker
On Fri, Mar 26, 2010 at 07:33:31AM +0000, Brett Parker wrote:
On 25 Mar 20:00, Chris G wrote:
I know it isn't really necessary as it doesn't actually affect anything (except my sensibilities) but I'd like the ability to re-order columns in some sqlite databases.
Well, it *does* have some advantages, it would mean I can do things like "SELECT * FROM database" and get the columns displayed in the order I want rather than the order resulting from database creation followed by adding extra columns as I found I needed them.
So, is there any way to re-order columns other than by creating a new table with the required column order, copying all the data across, deleting the old table and renaming the new one?
I can't find any neat utilities out there to do this for me.
When are you using a sqlite database and interfacing to it with something that isn't a programming language? Most languages with a database api will allow you to collect results from the database in a 'hashmap' or 'dictionary' with the column name as the key.
I'm interfacing to it from a Wiki so that, while I have *some* programming ability it's pretty limited.
Also, if you're adding lots of columns, you probably want to be more selective which are displayed if you are accessing it from the command line sqlite program, i.e. using: SELECT field1,field2 from mytable where <some condition>; Rather than the * approach.
What I'm doing is using the database to provide data storage for information in the Wiki, the ordering of columns as displayed in the Wiki is, of course, under the control of the Wiki. However when I browse the data in the database using tools like sqlitebrowser and sqliteman it would be really nice to see the database columns in a sensible order with the most important column(s) first so they're immediately visible. I can create a database browsing page in the Wiki which shows all columns in the order I want and allows me to change the data but it doesn't provide the facilities of sqlitebrowser and sqliteman to add/delete/change columns. -- Chris Green
participants (7)
-
Brett Parker -
Chris G -
Chris Glover -
David Reynolds -
simon ransome -
Ted.Harding＠manchester.ac.uk -
Wayne Stallwood