How to make dynamic column using Android SQLite Database

Question

Im writing application on Android and im using SQlite database. I want to be able to add columns to my table by the user choice. so the user can add any column that he wants the to table. For example the user have "animal" table and he want to add column for "dog", "cat" and "fish".

I have read about some solutions and i didnt saw one that can help me. I read that the simple way to add column is using:

        @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        // If you need to add a column
        if (newVersion > oldVersion) {
            db.execSQL("ALTER TABLE " + TableName + " ADD COLUMN " + ColumnName);
        }
}

But my problem with this that i cant choose what is the name of the column that will be added to the table by the user choise, there is no parameter for string. So i tried using something like this, and to call it directly.

        @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion, String newColumnName) {

        // If you need to add a column
        if (newVersion > oldVersion) {
            db.execSQL("ALTER TABLE " + TableName + " ADD COLUMN " + newColumnName);
        }
}

But i got error for this method.

I have another question about the database version. The onUpgrade method get automaticly called when onCreate get called. In onUpgrade there is oldVersion and newVersion parameters for the database version. when do i set the oldVersion and newVersion parameters? How i set my newVersion parameter to 2,3,4...?

sounds like poor database design. Are You sure You can't use relations to achieve same thing? — Gustek
– Gustek, Commented May 1, 2013 at 18:30
@Gustek thank you for you answer. what do you mean relations? to open more tables? But then i have the same problem. because my table number is limited to what i have set in the first place. And if my user want to add "Bird" table i dont have method that can do it. — tomer
– tomer, Commented May 2, 2013 at 9:18
Why it have to be columns, not rows in another table and many to many relationship? What is a data structure You try to store? — Gustek
– Gustek, Commented May 2, 2013 at 13:33
Im trying to do shopping list log. So the user can add any item that he want, and that item will be added as new column in the table. If ill put in new table, how can i can do that when the user press "add item" or something like this, a new table will be created. — tomer
– tomer, Commented May 2, 2013 at 15:00

Doug Currie · Accepted Answer · 2013-05-01 18:33:16Z

1

You can create an auxiliary table to hold the extra column data. Queries to your primary table can be converted into queries on a new view.

create table if not exists animal (pk integer primary key, name);

create table if not exists animal_aux (animal_pk, col_name, col_val);

create view if not exists animal_view
    as select animal.name as name,
              ct.col_val as cat,
              dt.col_val as dog
        from animal, animal_aux as ct, animal_aux as dt
        where animal.pk = ct.animal_pk
          and animal.pk = dt.animal_pk
          and ct.col_name = 'cat'
          and dt.col_name = 'dog'
;

This schema should be enhanced to make animal_pk, col_name a primary key, or at least unique in animal_aux. You may also need triggers to add or remove entries in the aux table when you insert or delete entries in the animal table.

Example:

sqlite> select * from animal_view;
sqlite> insert into animal values (NULL, 'fred');
sqlite> select * from animal_view;
sqlite> select * from animal;
1|fred
sqlite> insert into animal_aux values (1, "dog", "beagle");
sqlite> insert into animal_aux values (1, "cat", "siamese");
sqlite> select * from animal_view;
fred|siamese|beagle
sqlite>

Each time you add a virtual column, you'll need to

drop view animal_view;

and then re-create it with the appropriate extra columns and where clauses.

edited May 1, 2013 at 18:33

answered May 1, 2013 at 18:26

Doug Currie

41.4k1 gold badge104 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tomer Over a year ago

Thank you for your answer Doug Currie. But im not sure i understood your answer. is this code in Java? The first code you wrote, under what method is it? on upgrade? what is the parameters of this method?

Doug Currie Over a year ago

The code is in SQL, and tested with the SQLite shell. In your application you must construct these statements and execute them with execSQL.

Saud Khan · Accepted Answer · 2016-08-10 21:11:16Z

0

final static String Database_name="empDb.db";

public DatabaseHelper(Context context) {
    super(context, Database_name, null, 1);
}
@Override    public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table emp_tbl (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,salary TEXT)");
}

@Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS tbl_emp");
}

blog: https://itmulc.blogspot.com/2016/08/android-sqlite-database-with-complete.html Get more info about it. https://www.youtube.com/watch?v=W8-Z85oPNmQ

answered Aug 10, 2016 at 21:11

Saud Khan

113 bronze badges

Collectives™ on Stack Overflow

How to make dynamic column using Android SQLite Database

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related