4

The Android app that I am currently working on dynamically adds columns to an SQLite database. The problem I have is that I cannot figure out a way to remove these columns from the database.

If I add column A, B, C, D, and E to the database, is it possible to later remove column C?

I have done a lot of looking around and the closest thing I could find was a solution that requires building a backup table and moving all the columns (except the one to be deleted) into that backup table.

I can't figure out how I would do this, though. I add all the columns dynamically so their names are not defined as variables in my Java code. There doesn't seem to be a way to retrieve a column name by using Android's SQLiteDatabase.

Funkytown
  • 520
  • 1
  • 7
  • 16
  • 6
    An app that dynamically adds columns to tables strikes me as a design smell and not particularly scalable... – Joe Jul 28 '11 at 21:12
  • user-generated tables, why not? there could be a purpose – james Jul 28 '11 at 21:16
  • 1
    @Joe: Based on a particular comment below I agree with you. Funkytown is trying to add columns to represent Event objects in an attendance tracking app. Events need to be in their own table. (See my answer for a more detailed explanation.) – Paul Sasik Jul 28 '11 at 21:26
  • @binnyb: I'm not saying it's definitely a problem, but I would say most of the time this kind of approach is indicative of a design-level problem or inefficiency that needs to be refactored. – Joe Jul 28 '11 at 23:47

6 Answers6

2

SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table.

If you want to make more complex changes in the structure of a table, you will have to recreate the table. You can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.

For example, suppose you have a table named "t1" with columns names "a", "b", and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;
Basbous
  • 3,927
  • 4
  • 34
  • 62
1

SQLite doesn't support a way to drop a column in its SQL syntax, so its unlikely to show up in a wrapper API. SQLite doesn't often support all features that traditional databases support.

The solutions you've identified make sense and are ways to do it. Ugly, but valid ways to do it.

You can also 'deprecate' the columns and not use them by convention in newer versions of your app. That way older versions of your app that depend on column C won't break.

J. Polfer
  • 12,251
  • 10
  • 54
  • 83
  • Thanks, you confirmed my suspicion that it couldn't be done. I'll have to find another way to do what I'm doing. The app is (basically) an attendance tracking spreadsheet. You can add a new "event" and then indicate the people that attended or didn't. The columns are the "events". I was only concerned that if you accidentally create an event, you might want to delete it. – Funkytown Jul 28 '11 at 21:16
1

Oh... just noticed this comment:

The app is (basically) an attendance tracking spreadsheet. You can add a new "event" and then indicate the people that attended or didn't. The columns are the "events".

Based on that comment you should just create another table for your events and link to it from your other table(s). You should never have to add columns to support new domain objects like that. Each logical domain object should be represented by its own table. E.g. user, location, event...

Was writing this initially. Will keep it if you're interested:

Instead of dynamically adding and removing columns you should consider using an EAV data model for that part of your database that needs to be dynamic.

EAV data models store values as name/value pairs and the db structure never needs to change.

Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
1

Based on your comment below about adding a column for each event, I'd strongly suggest creating a second table in which each row will represent an event, and then tracking attendance by storing the user row id and the id of the event row in the attendance table. Continually piling columns onto the attendance table is a definite anti-pattern.

0

With regards to how to find out about the table schema, you can query the sqlite_master table as described in this other SO question - Is there an SQLite equivalent to MySQL's DESCRIBE [table]?

Community
  • 1
  • 1
arunkumar
  • 32,803
  • 4
  • 32
  • 47
0

As per SQLite FAQ, there is only limited support to the ALTER TABLE SQL command. So, the only way you can do is that ou can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.

Also you can get the column name from the database using a query. Any query say "SELECT * FROM " gives you a cursor object. You can use the method 

String getColumnName(int columnIndex);

or

String[] getColumnNames();

to retrieve the names of the columns.

vivekprakash
  • 346
  • 2
  • 9