When the time came to add more functionality, I began to wonder if I should implement it by adding more tables to existing databases or open a completely new database to add functionality and add tables there.
Are there any fundamental rules regarding when to open a new database? Are there any tests available on this?
So, this is a very complex question and depends on more factors like the nature of the application, the required performance, the nature of the tables, the relationships between them, etc.
A lot of people, a lot of opinions, so everyone can tell you something else.
But my opinion is that you should have only one database (also when it will have quite a lot of eq 10-20 tables). Then try to think about using Singleton .
It is always better to have all tables in one database, especially if these tables have something in common (sometimes you need to join two or more tables, and if you put tables (tables) in another database, this will become impossible ), Also you do not know (right now) if you update the table (s), change the relationship between them, etc. In the future (due to application updates, for example). On the other hand, the structure of the application is cleaner and more readable, as if you had, for example, five *.db files on internal or external storage (I can say that it depends on the size of each of them, because it hurts to place 100 MB .db file for internal storage).
And to the implementation question: I recommend that you (as I mentioned above) create only one DatabaseHelper, which will wrap all the necessary db logic. Then create classes (one class for one table) called <?>Tools , for example UsersTools, which will wrap CRUD operations and specific methods for a specific table. I use this approach and have never experienced a problem.
Implementation Example:
Here is an example DatabaseHelper:
package com.sajmon.examples.db; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class DataSource extends SQLiteOpenHelper { private static DataSource instance = null; public static final String DB_NAME = "<dbname>"; public static final int DB_STARTING_VERSION = 1; public DataSource(Context cntx) { super(cntx, DB_NAME, null, DB_STARTING_VERSION); } /** * * @param mContext as Context of Activity * @return <li>new instance of }DataSource object.</li> */ public static DataSource getInstance(Context mContext) { if (instance == null) { instance = new DataSource(mContext); } return instance; } @Override public void onCreate(SQLiteDatabase db) { String query = "create table " + SQLConstants.TEST_TABLE_NAME + " (" + SQLConstants.KEY_ID + " integer not null, " + SQLConstants.KEY_TYPE + " text null, " + SQLConstants.KEY_DATE + " text null" + ")"; db.execSQL(query); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { String query = "drop table if exists " + SQLConstants.TEST_TABLE_NAME; db.execSQL(query); onCreate(db); } }
SQLConstants:
package com.sajmon.examples.db; public class SQLConstants { public static final String TEST_TABLE_NAME = "Test"; public static final String KEY_ID = "id"; public static final String KEY_TYPE = "type"; public static final String KEY_DATE = "date"; }
TestTools example:
package com.sajmon.examples.db; import java.util.Date; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteStatement; public class TestTools { private Context mContext; private SQLiteDatabase db; public TestTools(Context mContext) { this.mContext = mContext; } public boolean insert(Test t) throws SQLException { try { db = openWrite(DataSource.getInstance(mContext)); ContentValues values = new ContentValues(); if (db != null) { db.beginTransaction(); values.put(SQLConstants.KEY_ID, t.getId()); values.put(SQLConstants.KEY_TYPE, t.getType()); values.put(SQLConstants.KEY_DATE, t.getDate()); db.insertOrThrow(SQLConstants.TEST_TABLE_NAME, SQLConstants.KEY_TYPE, values); values.clear(); db.setTransactionSuccessful(); return true; } return false; } finally { if (db != null) { db.endTransaction(); } close(db); } } public boolean update(Test t) { try { db = openWrite(DataSource.getInstance(mContext)); ContentValues values = new ContentValues(); int count = -1; if (db != null) { db.beginTransaction(); values.put(SQLConstants.KEY_TYPE, t.getType()); values.put(SQLConstants.KEY_DATE, t.getDate()); count = db.update(SQLConstants.TEST_TABLE_NAME, values, SQLConstants.KEY_ID + "= ?", new String[] {t.getId()}); db.setTransactionSuccessful(); } return count > 0; } finally { if (db != null) { db.endTransaction(); close(db); } } } public boolean delete(Test t) { int count = -1; try { db = openWrite(DataSource.getInstance(mContext)); if (db != null) { db.beginTransaction(); count = db.delete(SQLConstants.TEST_TABLE_NAME, SQLConstants.KEY_ID + "= ?", new String[] {t.getId()}); db.setTransactionSuccessful(); } return count > 0; } finally { if (db != null) db.endTransaction(); close(db); } } } public List<Test> getAll() { Cursor c = null; List<Test> tests = new ArrayList<Test>(); Test test = null; try { db = openRead(DataSource.getInstance(mContext)); if (db != null) { String[] columns = {SQLConstants.KEY_ID, SQLConstants.KEY_TYPE, SQLConstants.KEY_DATE}; c = db.query(SQLConstants.TEST_TABLE_NAME, columns, null, null, null, null, null); if (c.moveToFirst()) { do { test = new Test(); test.setId(c.getInt(c.getColumnIndex(SQLConstants.KEY_ID))); test.setType(c.getString(c.getColumnIndex(SQLConstants.KEY_TYPE))); test.setDate(c.getString(c.getColumnIndex(SQLConstants.KEY_DATE))); tests.add(test); } while(c.moveToNext()); } } return tests; } finally { if (c != null) { c.close(); } if (db != null) { close(db); } } } private final synchronized SQLiteDatabase openWrite(SQLiteOpenHelper handler) { return handler.getWritableDatabase(); } private final synchronized SQLiteDatabase openRead(SQLiteOpenHelper handler) { return handler.getReadableDatabase(); } private final synchronized void close(SQLiteDatabase db) { if (db != null && db.isOpen()) { db.close(); } } }
Such a basic implementation example that can be changed / improved when it is based on individual requirements. And if you want to make the implementation "on a more abstract level," so the DAOFactory design template should be the best choice.
Note. It is very useful to get the experience of other Android developers (developers), and then start to think about it and decide the appropriate approach.