can i dynamically add new column to sqlite table by calling a method from another class?

Question

I have a table in database, each record of this table needs to store multiple Strings, i dont know how many Strings because its decided at runtime.

I want to add image uri's in database table dynamically, user dynamically add images in my app as many as he want so i need to save uri of them, what is the right approach to do it?

I am trying something like this by follow this Insert new column into table in sqlite ?

String ColumnName=Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_PICTURES).toString() + "/" + "image1.jpg");

addNewColumn(ColumnName);

i used below method for this (not worked):-

First i am adding new column in table :-

public Cursor addColumn(String name){
    db=dbhelper.getWritableDatabase();
    return  db.rawQuery("alter table info add column " + name + " text", null);
}

Then insert uri into this

public Boolean setUri(String columnName,String uri) {
    ContentValues cv= new ContentValues();
    cv.put(columnName,uri);
    SQLiteDatabase db =dbhelper.getWritableDatabase();
    long id=db.insert("info",null,cv);
    if(id>-1)
        return true;
    else
        return false;
}

is the above approach correct?

also i searched and fine below code :-

private static final String ALTER = "ALTER TABLE user_table ADD user_street1 TEXT";

     @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
    {
        db.execSQL(ALTER);
    }

can i call onUpgrade() method dynamically and add new column or any other way to do it..?

Thanks in advance :)

score 1 · Accepted Answer · answered Aug 23 '15 at 18:58

1

It's very bad to have an arbitrary number of columns in a table. You should instead use a second table with a foreign key referencing the ID of a row in the first table.

table user
_id     username    ...
------------------------
1       abc
2       xyz

table photoInfo
userId      photoUri
-------------------------------
1           /path/to/image1.jpg
1           /path/to/image2.jpg
2           /path/to/image3.jpg
1           /path/to/image4.jpg

To show photos for a particular user, use a JOIN.

answered Aug 23 '15 at 18:58

Karakuri

38,365
12
84
104

1

Thanks @Karakuri for suggestion :) , can you add demo code in your answer to explain it properly ,it will be usefull for me :) – Aug 23 '15 at 19:03
@Nishusharma demo code for what? When you insert a new image, you insert it to `photoInfo` table. If you don't know the user id at that time, you need to first look it up in the users table. – Karakuri Aug 23 '15 at 19:07
1

actually this is a notebook app in which user add image or text or both, so what if user id also creating at run time along with image, dynamicaly creating new record and image table record , is it possible?? – Aug 23 '15 at 19:40
@Nishusharma Lots of things are possible. The point is, don't make a table with arbitrary number of columns. Beyond that, how you want to do things is up to you. – Karakuri Aug 23 '15 at 19:44
1

and please also look at this question http://stackoverflow.com/questions/32126784/restrict-imagespan-click-event-to-its-borders-and-should-not-get-clicked-beyond – Aug 23 '15 at 19:53
@Nishusharma You should only add columns during database upgrades, not dynamically as part of fitting additional items into a table. A database schema should remain as stable as possible. If you are adding **data**, then you should inserting a **row** in a table that already has the necessary columns to represent the data. If you are adding **metadata** (e.g. users now have a an `age` value), then you would add or change **columns**. Please do some reading on "normalization", that's basically what I did with your example. – Karakuri Aug 23 '15 at 20:02
1

Thanks alot dear , your forign key concept will definitely usefull for me, would you please look at my another problem, its the biggest problem i am facing in my app http://stackoverflow.com/questions/32126784/restrict-imagespan-click-event-to-its-borders-and-should-not-get-clicked-beyond – Aug 23 '15 at 20:08
1

Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/87717/discussion-between-nishu-sharma-and-karakuri). – Aug 23 '15 at 20:14

can i dynamically add new column to sqlite table by calling a method from another class?

1 Answers1