If your application requires a database and it comes with built in data, what is the best way to ship that application? Should I:

1. Precreate the SQLite database and include it in the `.apk`?

2. Include the SQL commands with the application and have it create the database and insert the data on first use?

The drawbacks I see are:

1. Possible SQLite version mismatches might cause problems and I currently don&#39;t know where the database should go and how to access it.
 
2. It may take a really long time to create and populate the database on the device.

Any suggestions? Pointers to the documentation regarding any issues would be greatly appreciated.

Ship an application with a database

I am developing an app and I know my database `*.db` will appear in `data/data/com.****.***`

I can access this file from AVD in Eclipse with help of sqlite manager

But I can&#39;t access this file in my Android phone.  
I googled it and it says I need to root my phone to do it, but I don&#39;t want to do that.

How can I access my `data/data/.....` directory in my Android phone &quot;**without rooting it**&quot;? 

Can I change user permissions for the directory `data/data.....` without rooting it?

How to access data/data folder in Android device?

I have created my tables in my `SQLiteOpenHelper` `onCreate()` but receive

    SQLiteException: no such table

or

    SQLiteException: no such column

errors. Why?

&gt;**NOTE:**
&gt;
&gt;(This is the amalgamated summary of tens of similar questions every week. Attempting to provide a &quot;canonical&quot; community wiki question/answer here so that all those questions can be directed to a good reference.)

When does SQLiteOpenHelper onCreate() / onUpgrade() run?

I have been using **Android Studio** to develop my app since it&#39;s was released.

Everything works nice until recently, I have to debug together with checking the database file. Since I don&#39;t know how to see the database directly, When I debugged to generate the database file, I have to export the database file from my phone to the PC. 

In order to do this, I have to open `DDMS &gt; File Explorer`. Once I open the DDMS, I have to reconnect the USB, and I lose my debug thread. After checking the database file, I have to close the DDMS and reconnect the USB again to get back to the debug mode. 

It&#39;s just too complicated. Does anyone have a better way to do this in Android Studio (I know it&#39;s easier in Eclipse) ? 

View contents of database file in Android Studio

On a non-rooted android device, I can navigate to the data folder containing the database using the `run-as` command with my package name. Most files types I am content with just viewing, but with the database I would like to pull if from the android device.

Is there a `download` `copy` or `move` command from this part of adb shell? I would like to download the database file and view its content using a database browser.

One answer here involves turning entire application package into a compressed archive, but there is no further answer on how to extract that archive once this is done and moved to the machine, leaving me very sidetracked when there might be a more direct solution to begin with

Retrieve database or any other file from the Internal Storage using run-as

I am unable to find where Android stores the database version within the SQLite database file. Where exactly is the database version stored?

Where does Android store SQLite&#39;s database version?

Every record in my `SQLite` database contains a field which contains a `Date` stored as a `string` in the format `&#39;yyyy-MM-dd HH:mm:ss&#39;`. 

Is it possible to query the database to get the record which contains the most recent date please?

SQLite Order By Date1530019888000

I&#39;d like to use Room with a pre-populated database, but I can&#39;t understand how to tell Room where to find my database.

I&#39;ve now put it in `src/main/assets/databases` and when I create the instance for the Room database I create it this way:

    Room.databaseBuilder(
        getApplicationContext(),
        AppDatabase.class,
        &quot;justintrain.db&quot;
    )
    .allowMainThreadQueries()
    .build();

This way tho, I think it&#39;s creating a new database every time, or anyways, it&#39;s not using the pre-populated one.

How can I make it to find my database?

How to use Room Persistence Library with pre-populated database?

I have read various on site about this issue but I am not able to figure this out. I am using pre-build database for this app.  I am using jellybean for this app.

**AndroidManifest.xml**

    &lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;
        &lt;manifest xmlns:android=&quot;http://schemas.android.com/apk/res/android&quot;
            package=&quot;com.example.quotes&quot;
            android:versionCode=&quot;1&quot;
            android:versionName=&quot;1.0&quot; &gt;

            &lt;uses-sdk
                android:minSdkVersion=&quot;8&quot;
                android:targetSdkVersion=&quot;17&quot; /&gt;

            &lt;application
                android:allowBackup=&quot;true&quot;
                android:icon=&quot;@drawable/ic_launcher&quot;
                android:label=&quot;@string/app_name&quot;
                android:theme=&quot;@style/AppTheme&quot; &gt;
                &lt;activity
                    android:name=&quot;com.example.quotes.MainActivity&quot;
                    android:label=&quot;@string/app_name&quot; &gt;
                    &lt;intent-filter&gt;
                        &lt;action android:name=&quot;android.intent.action.MAIN&quot; /&gt;

                        &lt;category android:name=&quot;android.intent.category.LAUNCHER&quot; /&gt;
                    &lt;/intent-filter&gt;
                &lt;/activity&gt;
            &lt;/application&gt;

        &lt;/manifest&gt;

**Logcat Output**

according to logcat it&#39;s caused by SQLiteCantOpenDatabaseException
    
    06-10 23:07:01.831: E/Trace(4419): error opening trace file: No such file or directory (2)
    06-10 23:07:03.611: E/SQLiteLog(4419): (14) cannot open file at line 30176 of [00bb9c9ce4]
    06-10 23:07:03.621: E/SQLiteLog(4419): (14) os_unix.c:30176: (2) open(/data/data/com.example.quotes/databasesQuotesdb) - 
    06-10 23:07:03.641: E/SQLiteDatabase(4419): Failed to open database &#39;/data/data/com.example.quotes/databasesQuotesdb&#39;.
    06-10 23:07:03.641: E/SQLiteDatabase(4419): android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:209)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:804)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:789)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:669)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at com.example.quotes.DataBaseHelper.checkDataBase(DataBaseHelper.java:94)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at com.example.quotes.DataBaseHelper.createDataBase(DataBaseHelper.java:58)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at com.example.quotes.MainActivity.onCreate(MainActivity.java:34)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.app.Activity.performCreate(Activity.java:5104)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2144)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.app.ActivityThread.access$600(ActivityThread.java:141)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1234)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.os.Handler.dispatchMessage(Handler.java:99)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.os.Looper.loop(Looper.java:137)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at android.app.ActivityThread.main(ActivityThread.java:5041)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at java.lang.reflect.Method.invokeNative(Native Method)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at java.lang.reflect.Method.invoke(Method.java:511)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
    06-10 23:07:03.641: E/SQLiteDatabase(4419):     at dalvik.system.NativeStart.main(Native Method)
    06-10 23:07:03.731: E/SQLiteLog(4419): (14) cannot open file at line 30176 of [00bb9c9ce4]
    06-10 23:07:03.731: E/SQLiteLog(4419): (14) os_unix.c:30176: (2) open(/data/data/com.example.quotes/databasesQuotesdb) - 
    06-10 23:07:03.781: E/SQLiteDatabase(4419): Failed to open database &#39;/data/data/com.example.quotes/databasesQuotesdb&#39;.
    06-10 23:07:03.781: E/SQLiteDatabase(4419): android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:209)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:804)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:789)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:669)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at com.example.quotes.DataBaseHelper.openDataBase(DataBaseHelper.java:145)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at com.example.quotes.MainActivity.onCreate(MainActivity.java:44)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.app.Activity.performCreate(Activity.java:5104)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2144)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.app.ActivityThread.access$600(ActivityThread.java:141)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1234)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.os.Handler.dispatchMessage(Handler.java:99)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.os.Looper.loop(Looper.java:137)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at android.app.ActivityThread.main(ActivityThread.java:5041)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at java.lang.reflect.Method.invokeNative(Native Method)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at java.lang.reflect.Method.invoke(Method.java:511)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
    06-10 23:07:03.781: E/SQLiteDatabase(4419):     at dalvik.system.NativeStart.main(Native Method)
    06-10 23:07:03.791: D/AndroidRuntime(4419): Shutting down VM
    06-10 23:07:03.791: W/dalvikvm(4419): threadid=1: thread exiting with uncaught exception (group=0x40a71930)
    06-10 23:07:03.831: E/AndroidRuntime(4419): FATAL EXCEPTION: main
    06-10 23:07:03.831: E/AndroidRuntime(4419): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.quotes/com.example.quotes.MainActivity}: android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2180)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.app.ActivityThread.access$600(ActivityThread.java:141)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1234)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.os.Handler.dispatchMessage(Handler.java:99)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.os.Looper.loop(Looper.java:137)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.app.ActivityThread.main(ActivityThread.java:5041)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at java.lang.reflect.Method.invokeNative(Native Method)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at java.lang.reflect.Method.invoke(Method.java:511)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at dalvik.system.NativeStart.main(Native Method)
    06-10 23:07:03.831: E/AndroidRuntime(4419): Caused by: android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:209)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:804)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:789)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:669)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at com.example.quotes.DataBaseHelper.openDataBase(DataBaseHelper.java:145)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at com.example.quotes.MainActivity.onCreate(MainActivity.java:44)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.app.Activity.performCreate(Activity.java:5104)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2144)
    06-10 23:07:03.831: E/AndroidRuntime(4419):     ... 11 more
    06-10 23:07:04.083: D/dalvikvm(4419): GC_CONCURRENT freed 203K, 11% free 2676K/3000K, paused 25ms+18ms, total 306ms
    06-10 23:07:07.811: I/Process(4419): Sending signal. PID: 4419 SIG: 9

**DataBaseHelper.java**

    public class DataBaseHelper extends SQLiteOpenHelper{

        //The Android&#39;s default system path of your application database.
        private static String DB_PATH = &quot;/data/data/com.example.quotes/databases&quot;;
    
        private static String DB_NAME = &quot;Quotesdb&quot;;
    
        // Table Names of Data Base.
        static final String TABLE_Name = &quot;Quotes&quot;;
    
        // Contacts Table Columns names
        //private static final String _Id = &quot;_Id&quot;;
        //private static final String quotes = &quot;quotes&quot;;
        //private static final String author = &quot;author&quot;;
    
        private SQLiteDatabase myDataBase; 
    
        private final Context myContext;
    
        /**
         * Constructor
         * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
         * @param context
         */
        public DataBaseHelper(Context context) {
            super(context, DB_NAME, null, 1);
            this.myContext = context;
        }
    
        /**
         * Creates a empty database on the system and rewrites it with your own database.
         * */
        public void createDataBase() throws IOException{
    
            boolean dbExist = checkDataBase();
            SQLiteDatabase db_Read = null;
    
            if(dbExist){
                //do nothing - database already exist
            }else{
                //By calling this method and empty database will be created into the default system path
                //of your application so we are gonna be able to overwrite that database with our database.
                db_Read = this.getReadableDatabase(); 
                db_Read.close();

                try {
                    copyDataBase();
                } catch (IOException e) {
                    //throw new Error(&quot;Error copying database&quot;);
                    e.toString();
                }
            }
        }
    
        /**
         * Check if the database already exist to avoid re-copying the file each time you open the application.
         * @return true if it exists, false if it doesn&#39;t
         */
        private boolean checkDataBase(){
    
            SQLiteDatabase checkDB = null;
    
            try{
                String myPath = DB_PATH + DB_NAME;
                checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
            }catch(SQLiteException e){
                //database does&#39;t exist yet.
            }
    
            if(checkDB != null){
                checkDB.close();
            }
    
            return checkDB != null ? true : false;
        }
    
        /**
         * Copies your database from your local assets-folder to the just created empty database in the
         * system folder, from where it can be accessed and handled.
         * This is done by transfering bytestream.
         * */
        private void copyDataBase() throws IOException{
    
            //Open your local db as the input stream
            InputStream myInput = myContext.getAssets().open(DB_NAME);
    
            // Path to the just created empty db
            String outFileName = DB_PATH + DB_NAME;
    
            //Open the empty db as the output stream
            OutputStream myOutput = new FileOutputStream(outFileName);
    
            //transfer bytes from the inputfile to the outputfile
            byte[] buffer = new byte[1024];
            int length;
            while ((length = myInput.read(buffer))&gt;0){
                myOutput.write(buffer, 0, length);
            }
    
            //Close the streams
            myOutput.flush();
            myOutput.close();
            myInput.close();
        }
    
        public void openDataBase() throws SQLException{
            //Open the database
            String myPath = DB_PATH + DB_NAME;
            myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    
        }
    
        @Override
        public synchronized void close() {
            if(myDataBase != null)
                myDataBase.close();
    
            super.close();
        }
    
        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
        }
    
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub
        }
    
        /*// Getting single contact
        public List&lt;Quotes&gt; getQuote(int id) {
            ArrayList&lt;Quotes&gt;();
            getReadableDatabase();
    
            Cursor cursor = db.query(TABLE_Name, new String[] { _Id,
                    qotes,author }, _Id + &quot;=?&quot;, new String[] { String.valueOf(id) },
                    null, null, null, null);
    
            //String query = &quot;SELECT _Id, qotes, author From &quot;+TABLE_Name;
            //String query = &quot;SELECT * From Quotes&quot;;
    
            //Cursor cursor = db.rawQuery(query, null);
    
            if (cursor != null)
                cursor.moveToFirst();
            Quotes quotes = new Quotes(Integer.parseInt(cursor.getString(0)),
            cursor.getString(1),cursor.getString(2));
            QuoteList.add(quotes);
            return QuoteList;
        }
        */
    }

android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database

I am trying to use this query upon my Android database, but it does not return any data.  Am I missing something?

    SQLiteDatabase db = mDbHelper.getReadableDatabase();
    	String select = &quot;Select _id, title, title_raw from search Where(title_raw like &quot; + &quot;&#39;%Smith%&#39;&quot; +
		&quot;)&quot;;    	
    	Cursor cursor = db.query(TABLE_NAME, FROM, 
    			select, null, null, null, null);
    	startManagingCursor(cursor);
    	return cursor;

How to perform an SQLite query within an Android application?

Last year when I checked about SQLite on their web site, the recommended SQLite database size was 2 gigabytes. But now, I could not find that recommendation again.

So has anyone tried to work with an SQLite database that is bigger than 2 gigabytes using latest version of it? How well did SQLite perform?

P.S: I would like to make a mobile application that requires big database (for example storing Wikipedia articles) that works locally.
 

Will SQLite performance degrade if the database size is greater than 2 gigabytes?

as can be seen in the documentation the syntax to make insert or update is : `INSERT OR REPLACE INTO &lt;table&gt; (&lt;columns&gt;) VALUES (&lt;values&gt;)`, my question is there any function that merge the following ?

    public long insert (String table, String nullColumnHack, ContentValues values) 
    public int update (String table, ContentValues values, String whereClause, String[] whereArgs)


or it has to be done with a prepared SQL statement and rawQuery?

What&#39;s the best practices to do an insert or update in Android?

Android SQLite Insert or Update

I am creating task manager. I have tasklist and I want when I click on particular tasklist name if it empty then it goes on Add Task activity but if it has 2 or 3 tasks then it shows me those tasks into it in list form.

I am trying to get count in list. my database query is like:

    public Cursor getTaskCount(long tasklist_Id) {
 
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor= db.rawQuery(&quot;SELECT COUNT (*) FROM &quot; + TABLE_TODOTASK + &quot; WHERE &quot; + KEY_TASK_TASKLISTID + &quot;=?&quot;,
				 new String[] { String.valueOf(tasklist_Id) });
		if(cursor!=null &amp;&amp; cursor.getCount()!=0)
	          cursor.moveToNext();
		return cursor;
	}    

In My activity:

    list_tasklistname.setOnItemClickListener(new OnItemClickListener() {
		@Override
		public void onItemClick(AdapterView&lt;?&gt; arg0,
				android.view.View v, int position, long id) {
					db = new TodoTask_Database(getApplicationContext());
					Cursor c = db.getTaskCount(id);
					System.out.println(c.getCount());
					if(c.getCount()&gt;0) {	
					System.out.println(c);
					Intent taskListID = new Intent(getApplicationContext(), AddTask_List.class);
					task = adapter.getItem(position);
					int taskList_id = task.getTaskListId();
					taskListID.putExtra(&quot;TaskList_ID&quot;, taskList_id);
					startActivity(taskListID);
				}
				else {
					Intent addTask = new Intent(getApplicationContext(), Add_Task.class);
					startActivity(addTask);
				}
			}
		});
		db.close();
	}

but when I am clicking on tasklist name it is returning 1, bot number of tasks into it.
    



How to get row count in sqlite using Android?

I am trying to make my first Android app. I noticed that the `SQLiteOpenHelper.onCreate()` method is not called to create tables if the database not exists. However, the `onCreate()` method did not work even thought I tried to debug. 

Please look at the code below and give me any suggestions. Any help will be appreciated.


    public class NameToPinyinActivity extends Activity {
    
    	DatabaseOpenHelper helper = null;
    
    	@Override
    	public void onCreate(Bundle savedInstanceState) {
    		super.onCreate(savedInstanceState);
    		setContentView(R.layout.nametopinyin);
    
    		Button searchButton = (Button) findViewById(R.id.search);
    		searchButton.setOnClickListener(new ButtonClickListener());
    		
    		helper = new DatabaseOpenHelper(NameToPinyinActivity.this);
    	}
    
    public class DatabaseOpenHelper extends SQLiteOpenHelper {
    
    	/** DB Name */
    	private static final String DB_NAME = &quot;pinyin&quot;;
    
    	/** CREATE TABLE SQL */
    	private static final String CREATE_TABLE_SQL = &quot;CREATE TABLE UNICODE_PINYIN&quot;
    			+ &quot;(ID INTEGER PRIMARY KEY AUTOINCREMENT, &quot;
    			+ &quot;UNICODE TEXT NOT NULL, PINYIN TEXT NOT NULL)&quot;;
    
    	public DatabaseOpenHelper(Context context) {
    		super(context, DB_NAME, null, 1);
    	}
    
    	@Override
    	public void onCreate(SQLiteDatabase db) {
    		db.beginTransaction();
    		try {
    			db.execSQL(CREATE_TABLE_SQL);
    			db.setTransactionSuccessful();
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally {
    			db.endTransaction();
    		}
    	}
    


Android SQLiteOpenHelper: Why onCreate() method is not called?

I have two major questions. 

 1. Database won&#39;t delete when uninstall app.
 2. Downloaded files won&#39;t delete while unstable the app.
 
There is a database in my android application. I create it by java

    class as follows.

    public DataBaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public DataBaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler) {
        super(context, name, factory, version, errorHandler);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // creating required tables
        db.execSQL(CREATE_TABLE_QUOTES);
        db.execSQL(CREATE_TABLE_FILTERS);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // on upgrade drop older tables
        db.execSQL(&quot;DROP TABLE IF EXISTS &quot; + TABLE_QUOTES);
        db.execSQL(&quot;DROP TABLE IF EXISTS &quot; + TABLE_QUOTES);
        // create new tables
        onCreate(db);
    }

There is no specific path defined  at the code for database.

This is the code how I download files. And there is specific path, But it is not allowed to create folder in Android&gt;data&gt;com.myapp as well. 

    public String downloadImage(String img_url, int i) {
            File sdCard = Environment.getExternalStorageDirectory();
            File dir = new File (sdCard.getAbsolutePath() + &quot;/fog/images/filters&quot;);
            // Make sure the Pictures directory exists.
            dir.mkdirs();
            File destinationFile = new File(dir, &quot;filter&quot;+i);
            String filepath = null;
            try{
                URL url = new URL(&quot;http://fog.wrapper.io/uploads/category/&quot;+img_url+&quot;.png&quot;);

                HttpURLConnection conection = (HttpURLConnection)url.openConnection();
                conection.setRequestMethod(&quot;GET&quot;);
                conection.setRequestProperty(&quot;Content-length&quot;, &quot;0&quot;);
                conection.setUseCaches(false);
                conection.setAllowUserInteraction(false);
                conection.connect();

                int status = conection.getResponseCode();

                switch (status) {
                    case 200:
                    case 201:
                        FileOutputStream fileOutput = new FileOutputStream(destinationFile);
                        InputStream inputStream = conection.getInputStream();
                        int totalSize = conection.getContentLength();
                        int downloadedSize = 0;
                        byte[] buffer = new byte[1024];
                        int bufferLength = 0;
                        while ( (bufferLength = inputStream.read(buffer)) &gt; 0 )
                        {
                            fileOutput.write(buffer, 0, bufferLength);
                            downloadedSize += bufferLength;                            Log.i(&quot;Progress:&quot;,&quot;downloadedSize:&quot;+downloadedSize+&quot;totalSize:&quot;+ totalSize) ;
                        }
                        fileOutput.close();
                        if(downloadedSize==totalSize) filepath = destinationFile.getPath();
                       Log.i(&quot;filepath:&quot;,&quot; &quot;+filepath) ;
                        return filepath;
                }

            } catch (IOException e) {
                Log.d(&quot;ImageManager&quot;, &quot;Error: &quot; + e);
            }
            return null;
        }
    } // Get filters

Please help me. Sorry for bad English.

Database won&#39;t remove when uninstall the Android Application

I&#39;m using Android&#39;s Room Persistence Library from the Android Architecture Components recently announced at Google I/O. Things seem to be working, but I&#39;m getting the following error:

&gt; Warning:tagId column references a foreign key but it is not part of an
&gt; index. This may trigger full table scans whenever parent table is
&gt; modified so you are highly advised to create an index that covers this
&gt; column.

My database has 3 tables: `Note`, `Tag`, and `JoinNotesTags`. Notes to Tags is a many-to-many relationship, hence the JoinNotesTags table to handle the mapping. The tables are straightforward:

 - `Note.id` and `Tag.id` are both primary keys
 - `JoinNotesTags.noteId` references `Note.id`
 - `JoinNotesTags.tagId` references `Tag.id`

The foreign key constraints are defined on the `JoinNotesTags` table. For reference, here is the `CREATE TABLE` statement for the `JoinNotesTags` table:

    &quot;CREATE TABLE IF NOT EXISTS `JoinNotesTags` (
        `id` INTEGER PRIMARY KEY AUTOINCREMENT, 
        `noteId` INTEGER, 
        `tagId` INTEGER, 
        FOREIGN KEY(`noteId`) REFERENCES `Note`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE , 
        FOREIGN KEY(`tagId`) REFERENCES `Tag`(`id`) ON UPDATE NO ACTION ON DELETE NO ACTION 
    )&quot;

And here is the corresponding `@Entity` annotation for that class:

    @Entity(
            indices = arrayOf(Index(value = *arrayOf(&quot;noteId&quot;, &quot;tagId&quot;), unique = true)),
            foreignKeys = arrayOf(
                    ForeignKey(
                            entity = Note::class,
                            parentColumns = arrayOf(&quot;id&quot;),
                            childColumns = arrayOf(&quot;noteId&quot;),
                            onDelete = ForeignKey.CASCADE),
                    ForeignKey(
                            entity = Tag::class,
                            parentColumns = arrayOf(&quot;id&quot;),
                            childColumns = arrayOf(&quot;tagId&quot;))
            )
    )

As you can see from the `@Entity` annotation, `tagId` *is* included in a composite unique index along with `noteId`. I&#39;ve confirmed that this index is correctly defined in the auto-generated json schema file as well:

    &quot;CREATE UNIQUE INDEX `index_JoinNotesTags_noteId_tagId` 
        ON `JoinNotesTags` (`noteId`, `tagId`)&quot;

So, my question: Is this warning just a bug in the (still-alpha-release) Room Library -- i.e. the compile-time analysis is missing the fact that `tagId` is part of this composite index? Or do I really have an indexing problem that I need to resolve in order to avoid full table scans?

Android Room compile-time warning about column in foreign key not part of an index. What does it mean?

How do I use the `String[] selectionArgs` in `SQLiteDatabase.query()`? I wish I could just set it to `null`, as I have no use for it. I am just trying to load an entire unsorted table from a database into a `Cursor`. Any suggestions on how to achieve this?

Using String[] selectionArgs in SQLiteDatabase.query()

I recently saw the new feature announced on Google I/O Room Persistence Library to work with Sqlite databases on Android. I have been looking to the [official documentation][1] and I don&#39;t find which dependencies I should import to my gradle file on my Android project. Someone can give me a hand?


  [1]: https://developer.android.com/topic/libraries/architecture/room.html

How to import Room Persistence Library to an Android project

I&#39;m creating a table with foreign key references. I&#39;m wondering about the required syntax. Mostly I&#39;ve seen the following (from http://www.sqlite.org/foreignkeys.html#fk_basics):

    CREATE TABLE artist(  
      artistid    INTEGER PRIMARY KEY,  
      artistname  TEXT  
    );  
    CREATE TABLE track(  
      trackid     INTEGER,   
      trackname   TEXT,  
      trackartist INTEGER,  
      FOREIGN KEY(trackartist) REFERENCES artist(artistid)  
    );

However, from the same site (http://www.sqlite.org/foreignkeys.html#fk_actions) I see this:

    CREATE TABLE artist(  
      artistid    INTEGER PRIMARY KEY,  
      artistname  TEXT  
    );  
    CREATE TABLE track(  
      trackid     INTEGER,  
      trackname   TEXT,   
      trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE  
    );

The latter syntax is a little more concise, but I want to know if the result is somehow different (aside from the `ON UPDATE CASCADE`, which of course has an effect; I only included it because I copied the code exactly from the referenced site, and because I don&#39;t know that the above syntax doesn&#39;t apply only when making such a specification). I am working in Android, in case that matters.


All Android Sqlite Solutions on Gang of Coders

Total of 19 Android Sqlite Solutions