Tag Archives: sqlite

Removing columns from SQLite Databases

For a while I’d been annoyed that my SQLite database for Flagster contained a few dead-end columns. Since SQLite does not allow you to drop columns, I was resigned to having them gathering dust, and space.

However searching for unrelated help, came across a neat trick for removing columns. Although it takes three stages.

This was all tested/completed using Base, arguably the best SQLite editor on the Mac. Make sure you take a backup before starting though, just in case.

(1) duplicate table (without the unwanted columns) as a new table in your database

To create a new table from an old one, use a combination of CREATE and SELECT commands:

CREATE TABLE newTable AS
  SELECT col1, col2, col3, col4 FROM oldTable;

Just only list the columns you want in the SELECT statement, and list them in the order you want them in the table. For bonus points you can also sort the records as they’re written to the table.

CREATE TABLE newTable AS
  SELECT col1, col2, col3, col4 FROM oldTable
  SORT BY col2;

(2) drop the old table (with the unwanted columns)

Once confident your newTable has all the data you need, you can remove the old one. This is simple using the DROP command:

DROP TABLE oldTable;

(3) rename the new table with the old name

Finally, to tidy up and save updating any existing code, rename the new table to match the old table name:

ALTER TABLE newTable
  RENAME TO oldTable;

And that’s it. Three steps later you’ve cleaned up your database, sorted your records, and made everything right in the world.

Getting the iPhone simulator to recognise sqlite database changes done in Terminal

I’ve been tearing my hair out for an afternoon working with a simple DB application for the iPhone/iPad. Essentially it connects to a sqlite3 database as the source file. But each time I edited that database content in Terminal, nothing was changing in the application when run.

Finally a solution. Need to reset the simulator itself to remove the local copy taken/needed when running a DB application.

Option you need is Reset Contents and Settings… under the iPhone Simulator menu.

Once reset when the application is run it pulls the updated database across as its copy. Phew!