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? :)