Correct work with the database in Android

Greetings to all droids in these difficult times for us.
Honestly, this hype has hesitated about patents, wars, etc., but this article is not about this.
I was not going to write an article on this topic, because everywhere there is a whole lot about working with the database in Android and everything seems to be simple, but I am very tired of receiving reports about errors, errors specific and related to the database.
Therefore, I will look at a couple of points that I encountered in practice, to warn people who just have to deal with it, and then I wait for your comments on solving these problems and then make changes to the post and we will make an excellent tutorial that will work With SQLite in Android, not only for beginners, but also for those who are already familiar with the basics and have written simple applications.

Ways to work with the database

There are three ways to work with data in the database, which immediately rush to the mind:
1) You create an empty database structure. The user works with the application (creates notes, deletes them) and the database is full. An example would be the NotePad application in demo examples developer.android.com or on your droid device.
2) You already have a ready-made database filled with data that needs to be distributed with the application, or parse the data from the file into assets.
3) Receive data from the network, as needed.
If there is any other one or two ways, then I’m happy to add this list with your help.
All the main tutorials are designed just for the first time. You write a request to create a database structure and execute this query in the onCreate () method of the SQLiteOpenHelper class, for example:

class MyDBHelper extends SQLiteOpenHelper { final String CREATE_TABLE = "CREATE TABLE myTable(...)"; final String DB_NAME = "mySuperDB.db"; Context mContext; public MyDBHelper(Context context, int dbVer){ super(context, DB_NAME, null, dbVer); mContext = context; } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_TABLE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { //       db.execSQL("DROP TABLE IF EXISTS tableName"); onCreate(db); } ...-  } 

Like that. A more complete version of the class and other components can be viewed at the link at the bottom of the article.
Additionally, you can override the methods onOpen (), getReadableDatabase () / getWritableDatbase (), but usually there is enough of the above and data retrieval methods.
Further, we create an instance of this class in our application when it is launched and execute queries, that is, the problem part is passed. Why is she a problem? Because when a user downloads applications from the market, he does not think about your database and anything can happen. Let's say the network has disappeared, or the process has started another one, or you have written a code that is vulnerable to errors.

By the way, there is one more thing worth paying attention to. An instance variable of our class can be created and stored in the Application object and accessed as necessary, but you need to remember to call the close () method, since a permanent connection to the database is a heavy resource. In addition, there may be collisions when working with a database of several threads.
But there is another way, for example, to create our object as needed to access the database. I think this is a matter of preference, but which also needs to be discussed.
')
And now the most important thing. What if we needed to use an already existing database with data in the application?
A little googling, you immediately stumble upon such a "wonderful article" - www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications in which, as it seems, there is a necessary panacea. But it was not there. It also has several errors.

Here they are:
1) In the createDataBase () method, the line:
SQLiteDatabase dbRead = getReadableDatabase ();
and then the code ... contains the crash of the application on the NTS Desire, because we get the database to read (it is created), but does not close.
Add the line below dbRead.close () and the fix is ​​ready, but the point is controversial.
Here is what the dock says on the getReadableDatabase () method:
Create and / or open a database. This will be the same object that has received some information, such as a full disk read. In that case, the database object will be returned. If the problem is a problem, it will be possible to make it.
Like this method, you can take it a long time to return, so that you can get it.
So. This method should not be called in the main application thread. Everything else is clear.
2) Error: No such table android_metadata. The author of the post got out by creating this table in advance in the database. I do not know how much this is the right way, but this table is created in each sqlite-database by the system and contains the current locale.
3) Error: Unable to open database file. There are many opinions, different opinions, which you can read on the links below.

stackoverflow.com/questions/3563728/random-exception-android-database-sqlite-sqliteexception-unable-to-open-database
groups.google.com/group/android-developers/browse_thread/thread/a0959c4059359d6f
code.google.com/p/android/issues/detail?id=949
stackoverflow.com/questions/4937934/unable-to-open-database-file-on-device-htc-desire
androidblogger.blogspot.com/2011/02/instable-android-and-unable-to-open.html

It is possible that the problems are related to the fact that one thread blocks the database and the second one cannot access it, perhaps the problem is access rights to the application (it was noted that the problems with the database often appear on the NTS phones on those models that can not be ruined , although not only on them, for example, on Acer tablets), however, these problems exist.
I tend to the option that the problem is in the threads, not for nothing that we are not recommended to call the methods for creating the base in the main thread.

Perhaps the way out of this is the next solution (option number 2 is being considered) Using the first version of working with the database, fill it with data after creation, for example:

  @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_TABLE); fillData(db); } private void fillData(SQLiteDatabase db) { //  data.xml    assets //     //     sql-        db.execSQL()   } 

This approach still needs to be tested in practice, but since this post is aimed at developing the right collective decision on this topic, comments and samples on this topic are welcome.
The moral of the story is this: if you have found some good piece of code for your solution, then check it out, do not be lazy before copying and paste into your project.

Conclusion

In general, this post shows (regarding method number 2) how to do it is not necessary, but also contains a couple of curious thoughts.
The getReadableDatabase () method can be overridden for example:

  @Override public synchronized SQLiteDatabase getReadableDatabase() { //Log.d(Constants.DEBUG_TAG, "getReadableDatabase() called"); SQLiteDatabase db; try { db = super.getReadableDatabase(); } catch (SQLiteException e) { Log.d(Constants.DEBUG_TAG, e.getMessage()); File dbFile = myContext.getDatabasePath(DB_NAME); Log.d(Constants.DEBUG_TAG,"db path="+dbFile.getAbsolutePath()); //db = SQLiteDatabase.openDatabase(/*DB_PATH + DB_NAME*/ dbFile.getAbsolutePath(), null, SQLiteDatabase.NO_LOCALIZED_COLLATORS); db = SQLiteDatabase.openOrCreateDatabase(dbFile.getAbsolutePath(), null); } return db; } 

An excellent tutorial on this topic here is www.vogella.de/articles/AndroidSQLite/article.html

By the way: following the practice of the platform itself, the primary key field should be called "_id".

Write in your comments used practices. We will make this post better for everyone, and maybe the world will become a bit kinder.

UPD Just checked your approach. Everything works in the emulator, but be careful.

 public class DBHelper extends SQLiteOpenHelper { final static int DB_VER = 1; final static String DB_NAME = "todo.db"; final String TABLE_NAME = "todo"; final String CREATE_TABLE = "CREATE TABLE "+TABLE_NAME+ "( _id INTEGER PRIMARY KEY , "+ " todo TEXT)"; final String DROP_TABLE = "DROP TABLE IF EXISTS "+TABLE_NAME; final String DATA_FILE_NAME = "data.txt"; Context mContext; public DBHelper(Context context) { super(context, DB_NAME, null, DB_VER); Log.d(Constants.DEBUG_TAG,"constructor called"); mContext = context; } @Override public void onCreate(SQLiteDatabase db) { Log.d(Constants.DEBUG_TAG,"onCreate() called"); db.execSQL(CREATE_TABLE); fillData(db); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL(DROP_TABLE); onCreate(db); } private ArrayList<String> getData() { InputStream stream = null; ArrayList<String> list = new ArrayList<String>(); try { stream = mContext.getAssets().open(DATA_FILE_NAME); } catch (IOException e) { Log.d(Constants.DEBUG_TAG,e.getMessage()); } DataInputStream dataStream = new DataInputStream(stream); String data = ""; try { while( (data=dataStream.readLine()) != null ) { list.add(data); } } catch (IOException e) { e.printStackTrace(); } return list; } private void fillData(SQLiteDatabase db){ ArrayList<String> data = getData(); for(String dt:data) Log.d(Constants.DEBUG_TAG,"item="+dt); if( db != null ){ ContentValues values; for(String dat:data){ values = new ContentValues(); values.put("todo", dat); db.insert(TABLE_NAME, null, values); } } else { Log.d(Constants.DEBUG_TAG,"db null"); } } } 

File data.txt is in assets like this:
Zametka # 1
Zametka # 2
Zametka # 3
Zametka # 4

And application class:

 public class TODOApplication extends Application { private DBHelper mDbHelper; @Override public void onCreate(){ super.onCreate(); mDbHelper = new DBHelper(getApplicationContext()); mDbHelper.getWritableDatabase(); } @Override public void onLowMemory() { super.onLowMemory(); mDbHelper.close(); } @Override public void onTerminate(){ super.onTerminate(); mDbHelper.close(); } } 

I note that this class is used only to demonstrate and check what happens when you call the getReadableDatabase () / getWritableDatabase () methods and create the base. In real projects, the code needs to be adapted.
In addition, the android_metadata sign appeared in the database (without my participation), so the error indicated above has been resolved.
I hope someone will come in handy.

Curious additions # 1 (from Kalobok habrauser )

So far I have completely abandoned SQLiteOpenHelper - it turned out that it is impossible to create a base on the SD card in it. Theoretically, what it returns should be used as the path to the database. In practice, SQLiteOpenHelper sometimes uses it, and sometimes bypasses it - it depends on whether we open the database for reading or writing, if it already exists, etc. SQLiteOpenHelper.getWritableDatabase calls Context.openOrCreateDatabase, which in turn uses Context.validateFilePath to get the full path to the file. It uses the private method Context.getDatabasesDir, which cannot be overridden - arrived. The database will be created in the standard directory.

But if we called SQLiteOpenHelper.getReadableDatabase, first it will try to call all the same getWritableDatabase. But if it does not work out, then it will bypass the Context.openOrCreateDatabase - it will call Context.getDatabasePath (we can correct it) and open the necessary database itself. This method would suit us if it was always used. But alas. :(
In general, the idea with this helper was good, and the implementation - with the left foot with a hangover.