On Tue, Dec 16, 2014 at 11:40:22PM +0000, Simon Slavin wrote: > If 'ALTER TABLE ... ADD COLUMN ...' fails it fails harmlessly, with
But it doesn't fail so harmlessly: $ sqlite3 db 'alter table toy add column foo text; select 5;' || echo fail SQL Error: duplicate column name: foo fail $ Note that: - the second statement was not executed - the noise on stderr - the non-zero exit status for the sqlite3 shell (which means that one of sqlite3_prepare_v2() or sqlite3_step() failed) Yes, I can work around this. Compare to a CREATE TABLE .. IF NOT EXISTS, which is silent and does not cause the shell to exit with a non-zero exit status (because it doesn't cause the sqlite3_exec() nor sqlite3_step() to return an error), and does not stop evaluation of remaining input to the shell. Ideally I could just have schema SQL in a file, doing DROP .. IF EXISTS for some schema elements, CREATE .. IF NOT EXISTS for all of them, and ALTER TABLE .. IF NOT EXISTS to upgrade schemas by just evaluating this one file. Executing a schema setup/upgrade file this via the shell is extremely convenient. (I do that all the time, but not with ALTER TABLE.) > its work already having been done. The ALTER command came along a > long time after original SQL. By that time software could handle > cases where a single SQL command failed without the software having to > crash at that point. Yes, there are workarounds, I'm well aware. > I would value far more the ability to do > > ALTER TABLE ... DROP COLUMN ... > > in SQLite, difficult though it would be to implement in SQLite3. I would like this too, yes. It wouldn't be that difficult: all that's needed is to arrange for the dropped column to remain on-disk but otherwise be ignored (hidden, but really well hidden), but still be added (with null value) for INSERTs and UPDATEs. For SQLite3 that would mean something like extending the sqlite_master table to list the on-disk columns, with dropped columns marked-up as such. One would have to vaccuum to have them truly disappear. (For extra credit fail if triggers/FKs retain dangling references to the dropped column, and even better, defer this check to commit time, since subsequent statements might remediate this.) Some things are easier than others. ALTER .. IF NOT EXISTS surely would be easier to add than DROP COLUMN. Whether that's enough to recommend it is a different story; I leave it to the SQLite3 team to decide that. Even better, I'd like a normalized form of the schema stored in sqlite_* tables, so that I could create/alter/drop schema elements with normal CREATE/UPDATE/DELETE statements with WHERE clauses (so that I could express conditional schema changes in SQL). It'd be better than any pragmas like table_info(table_name). Much of the schema manipulation statement logic could later be re-implemented by mapping those to DMLs and then executing them, with many constraints (e.g., new columns must allow NULL or otherwise have a default value, ...) implemented as triggers. Today I'm just asking for IF NOT EXISTS. If it's not adopted, no big deal. I think it has a couple of things to recommend it (utility, relative ease of implementation), but I'm not paying for it. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
