androidsqliteandroid-sqlitesqliteopenhelper
I want to create a SQLite database like in the images how do I do it? I just want to know how to I create the link column so as to link databases?
I want to know as to how to link one database to other one. For Example The Apps column in the days table should open one instance of the Apps table with the following date.
Solution
You can open one of the databases and then ATTACH the other database.
It would be simpler to have all the tables in a single database. Having a single database with all the tables would then allow you to enforce referential integrity by utilising foreign key constraints (i.e. that the links do actually link).
Example of Attaching utilising SQLiteOpenHelper subclass
The following is an example of attaching a second database, both databases based upon your schemas, via a Single Database Helper (subclass of SQliteOpenHelper).
- As would be done with SQLiteOpenHelper for the main database, if the second database didn't exist it will be created along with the tables.
DatabaseHelper
class DatabaseHelper extends SQLiteOpenHelper {
public static final String DBNAME = "database1";
public static final int DBVERSION = 1;
public static final String OTHER_DBNAME = "database2";
public static final String ATTACH_SCHEMA = "a1";
public static final String TB_DAYTABLE = "daytable";
public static final String COL_DATE_DAYTABLE = "date";
public static final String COL_TOTALTIME_DAYTABLE = "totaltime";
public static final String COl_UNLOCKCOUNTS_DAYTABLE = "unlockcounts";
public static final String COL_APPS_DAYTABLE = "apps";
public static final String TB_APPS = "apps";
public static final String COl_NAME_APPS = "name";
public static final String COl_TOTALTIME_APPS = "totaltime";
public static final String COl_COUNTS_APPS = "counts";
public static final String COL_TIMELINE_APPS = "timeline";
public static final String TB_TIMELINE = "timeline";
public static final String COL_STARTTIME_TIMELINE = "starttime";
public static final String COL_ENDTIME_TIMELINE = "endtime";
public static final String COL_TOTALTIME_TIMELINE = "totaltime";
private static volatile DatabaseHelper instance;
private SQLiteDatabase db;
private DatabaseHelper(@Nullable Context context) {
super(context, DBNAME, null, DBVERSION);
db = this.getWritableDatabase();
db.execSQL("ATTACH DATABASE '" + context.getDatabasePath(OTHER_DBNAME) + "' AS " + ATTACH_SCHEMA);
Cursor csr = db.query(ATTACH_SCHEMA + ".sqlite_master",new String[]{"name"},"name=?",new String[]{TB_TIMELINE},null,null,null);
if (!csr.moveToFirst()) {
createAttachedTables(db);
}
}
public static DatabaseHelper getInstance(Context context) {
if (instance == null) {
instance = new DatabaseHelper(context);
}
return instance;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS " + TB_DAYTABLE + " (" +
COL_DATE_DAYTABLE + " TEXT " +
"," + COL_TOTALTIME_DAYTABLE + " INTEGER " +
"," + COl_UNLOCKCOUNTS_DAYTABLE + " INTEGER " +
"," + COL_APPS_DAYTABLE + " INTEGER " +
")" );
db.execSQL("CREATE TABLE IF NOT EXISTS " + TB_APPS + " (" +
COl_NAME_APPS + " TEXT " +
"," + COl_TOTALTIME_APPS + " INTEGER " +
"," + COl_COUNTS_APPS + " INTEGER " +
"," + COL_TIMELINE_APPS + " TEXT " +
")");
}
private void createAttachedTables(SQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS " + ATTACH_SCHEMA + "." + TB_TIMELINE + "(" +
COL_STARTTIME_TIMELINE + " TEXT " +
"," + COL_ENDTIME_TIMELINE + " TEXT " +
"," + COL_TOTALTIME_TIMELINE + " INTEGER " +
")");
db.execSQL("CREATE TABLE IF NOT EXISTS " + ATTACH_SCHEMA + "." + TB_APPS + "(" +
COl_NAME_APPS + " TEXT " +
"," + COl_TOTALTIME_APPS + " INTEGER " +
"," + COl_COUNTS_APPS + " INTEGER " +
"," + COL_TIMELINE_APPS + " TEXT " +
")");
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}
The Helper has been tested (attaching the 2nd database and creating the tables) using the following in an Activity (MainActivity):-
public class MainActivity extends AppCompatActivity {
private DatabaseHelper db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
db = DatabaseHelper.getInstance(this);
}
}
Result
Opening the Database in Android Studio's Database Inspector and extract data from both schemas (i.e. the respective sqlite_master table) then the following can be seen :-
i.e. the 2 database are attached and all 4 tables exist.
using the database_list PRAGMA shows :-
i.e. again the two databases
Additional
Considering what you problem appears to be, that you want to store app usage data. I believe that two tables would suffice.
That is the APPS table appears to be duplicated so isn't required. Secondly the stats unlock counts, total times etc could be calculated from just storing the start and end times linked to the respective App.
So a schema along the lines of the following could be used to ascertain values:-
- where the red line shows the link from the the Usage to the respective App.
Now assuming that the app table contains:-
And the usage table contains :-
Then a query such as :-
SELECT
date(usage.startdatetime) AS date,
app.appname, count(*) AS unlocks,
time(sum(strftime('%s',usage.enddatetime) - strftime('%s',usage.startdatetime) ),'unixepoch') AS elapsedtime
FROM app JOIN usage ON app._id = usage.applink
GROUP BY date(usage.startdatetime),app._id
ORDER BY date(usage.startdatetime) ASC,appname ASC
Will return the following result:-
- So as you can see the total time, and unlock counts have been calculated on the fly (i.e. there is no need to store the values).
Example 2 - The 2 table solution for Android :-
The Database Helper AltDBHelper :-
class AltDBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "appusage.db";
public static final int DBVERSION = 1;
private static SQLiteDatabase db;
private static volatile AltDBHelper instance;
private AltDBHelper(@Nullable Context context) {
super(context, DBNAME, null, DBVERSION);
db = this.getWritableDatabase();
}
public static AltDBHelper getInstance(Context context) {
if (instance == null) {
instance = new AltDBHelper(context);
}
return instance;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(AppTable.getCreateSQL());
db.execSQL(UsageTable.getCreateSQL());
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
}
/*
Insert an App into the database
*/
public long insert(String appName) {
ContentValues cv = new ContentValues();
cv.put(AppTable.APPNAME,appName);
return AltDBHelper.db.insert(AppTable.TBNAME,null,cv);
}
/*
Query to return the Date, App, Number of unlocks and Sum of the Elapsed times (total time)
by each day by each App
* Based upon the query :-
* SELECT
* app.appname,
* date(usage.startdatetime),
* time(sum(strftime('%s',enddatetime) - strftime('%s',startdatetime)),'unixepoch')
* FROM usage JOIN app ON app._id = usage.applink
* GROUP BY date(startdatetime),app._id
* ORDER BY date(startdatetime),app.appName
*
*/
public static final String DATE = "date";
private static final String DATE_COL_EXPRESSION = "date(" + UsageTable.STARTDATETIME_QUALIFIED + ") AS " + DATE;
public static final String APPUNLOCKS = "unlocks";
private static final String APPUNLOCKS_COL_EXPRESSION = "count(*) AS " + APPUNLOCKS;
public static final String ELAPSEDTIME = "elapsedtime";
private static final String ELAPSEDTIME_EXPRESSION =
"time(" +
"sum(" +
"strftime('%s'," + UsageTable.ENDDATETIME_QUALIFIED + ") " +
" - " +
" strftime('%s'," + UsageTable.STARTDATETIME_QUALIFIED + ") " +
")" +
",'unixepoch'" +
")" +
" AS " + ELAPSEDTIME;
private static final String APPSTATS_TABLE =
AppTable.TBNAME + " JOIN " + UsageTable.TBNAME +
" ON " +
AppTable.APPID_QUALIFIED + " = " +
UsageTable.APPLINK_QUALIFIED;
private static final String DATEPART_GROUPBY = "date(" + UsageTable.STARTDATETIME_QUALIFIED + ")";
private static final String APPPART_GROUPBY = AppTable.APPID_QUALIFIED;
private static final String GROUPBY = DATEPART_GROUPBY + "," + APPPART_GROUPBY;
public Cursor getAppDailyStatsAsCursor(Long id) {
String whereClause = null;
String[] whereArgs = null;
if (id != null) {
whereClause = AppTable.APPID_QUALIFIED + "=?";
whereArgs = new String[]{String.valueOf(id)};
}
return AltDBHelper.db.query(
APPSTATS_TABLE,
new String[]{
DATE_COL_EXPRESSION,
AppTable.APPNAME_QUALIFIED,
APPUNLOCKS_COL_EXPRESSION,
ELAPSEDTIME_EXPRESSION
},
whereClause,
whereArgs,
GROUPBY,
null,
"date(" + UsageTable.STARTDATETIME_QUALIFIED + ") " + " ASC" + "," + AppTable.APPNAME + " ASC"
);
}
/* The App Table name and column definitions */
public static final class AppTable {
public static final String TBNAME = "app";
public static final String APPID = BaseColumns._ID;
public static final String APPID_QUALIFIED = TBNAME + "." + APPID;
public static final String APPID_UNIQUE = TBNAME+APPID;
public static final String APPNAME = "appname";
public static final String APPNAME_QUALIFIED = TBNAME + "." + APPNAME;
public static String getCreateSQL() {
return "CREATE TABLE IF NOT EXISTS " + TBNAME + "(" +
APPID + " INTEGER PRIMARY KEY " +
"," + APPNAME + " TEXT UNIQUE " +
")";
}
}
/* The Usage table name and column definitions also method for inserting rows (alternative) */
public static final class UsageTable {
public static final String TBNAME = "usage";
public static final String USAGEID = BaseColumns._ID;
public static final String USAGEID_QUALIFIED = TBNAME + "." + USAGEID;
public static final String USAGEID_UNIQUE = TBNAME + USAGEID;
public static final String APPLINK = "applink";
public static final String APPLINK_QUALIFIED = TBNAME + "." + APPLINK;
public static final String STARTDATETIME = "startdatetime";
public static final String STARTDATETIME_QUALIFIED = TBNAME + "." + STARTDATETIME;
public static final String ENDDATETIME = "enddatetime";
public static final String ENDDATETIME_QUALIFIED = TBNAME + "." + ENDDATETIME;
public static String getCreateSQL() {
return "CREATE TABLE IF NOT EXISTS " + TBNAME + "(" +
USAGEID + " INTEGER PRIMARY KEY" +
"," + APPLINK + " INTEGER REFERENCES " + AppTable.TBNAME + "(" + AppTable.APPID + ") ON DELETE CASCADE ON UPDATE CASCADE" +
"," + STARTDATETIME + " TEXT " +
"," + ENDDATETIME + " TEXT " +
")";
}
public static long insert(long appId, String startDateTime, String endDateTime) {
ContentValues cv = new ContentValues();
cv.put(APPLINK,appId);
cv.put(STARTDATETIME,startDateTime);
cv.put(ENDDATETIME,endDateTime);
return AltDBHelper.db.insert(TBNAME,null,cv);
}
}
}
using the above in an activit, MainActivity to produce the same result as per the additional section explained above :-
public class MainActivity extends AppCompatActivity {
private DatabaseHelper db;
private AltDBHelper altdb;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
db = DatabaseHelper.getInstance(this); /* The original answer attaching a second database */
/* using alternative with just 2 tables */
altdb = AltDBHelper.getInstance(this); /* get an Instance of the DBHelper */
/* Add data for App1 along with some usage data */
long a1 = altdb.insert("App1");
AltDBHelper.UsageTable.insert(a1,"2021-01-01 15:30:00","2021-01-01 16:00:00");
AltDBHelper.UsageTable.insert(a1,"2021-01-01 17:21:34","2021-01-01 19:33:12");
AltDBHelper.UsageTable.insert(a1,"2021-01-02 09:11:27","2021-01-02 09:15:28");
AltDBHelper.UsageTable.insert(a1,"2021-01-03 00:00:00","2021-01-03 05:00:59");
/* Likewise for App2 */
long a2 = altdb.insert("App2");
AltDBHelper.UsageTable.insert(a2,"2021-01-01 13:04:27","2021-01-01 13:06:07");
AltDBHelper.UsageTable.insert(a2,"2021-01-03 12:51:51","2021-01-03 12:55:55");
AltDBHelper.UsageTable.insert(a2,"2021-01-02 03:33:30","2021-01-02 03:33:35");
AltDBHelper.UsageTable.insert(a2,"2021-01-02 01:01:01","2021-01-02 01:03:04");
AltDBHelper.UsageTable.insert(a2,"2021-01-01 14:25:36","2021-01-01 15:26:37");
/* Add just App3 with no usage data */
long a3 = altdb.insert("App3");
/* Get the Daily App Usage */
Cursor csr = altdb.getAppDailyStatsAsCursor(null);
DatabaseUtils.dumpCursor(csr); /* Dump the Cursor (just to show what the Cursor contains) */
csr.close(); /* done with the Cursor so close it */
}
}
- As can be seen, the original code showing how to attach a database has been left in.
When the above is run for the first time (not designed to be rerun as it just a demo), the dump of the Cursor is as follows :-
2021-05-30 18:29:23.739 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@9ba4107
2021-05-30 18:29:23.742 I/System.out: 0 {
2021-05-30 18:29:23.742 I/System.out: date=2021-01-01
2021-05-30 18:29:23.743 I/System.out: appname=App1
2021-05-30 18:29:23.743 I/System.out: unlocks=2
2021-05-30 18:29:23.743 I/System.out: elapsedtime=02:41:38
2021-05-30 18:29:23.744 I/System.out: }
2021-05-30 18:29:23.744 I/System.out: 1 {
2021-05-30 18:29:23.744 I/System.out: date=2021-01-01
2021-05-30 18:29:23.744 I/System.out: appname=App2
2021-05-30 18:29:23.745 I/System.out: unlocks=2
2021-05-30 18:29:23.745 I/System.out: elapsedtime=01:02:41
2021-05-30 18:29:23.745 I/System.out: }
2021-05-30 18:29:23.746 I/System.out: 2 {
2021-05-30 18:29:23.746 I/System.out: date=2021-01-02
2021-05-30 18:29:23.746 I/System.out: appname=App1
2021-05-30 18:29:23.747 I/System.out: unlocks=1
2021-05-30 18:29:23.747 I/System.out: elapsedtime=00:04:01
2021-05-30 18:29:23.747 I/System.out: }
2021-05-30 18:29:23.747 I/System.out: 3 {
2021-05-30 18:29:23.748 I/System.out: date=2021-01-02
2021-05-30 18:29:23.748 I/System.out: appname=App2
2021-05-30 18:29:23.749 I/System.out: unlocks=2
2021-05-30 18:29:23.749 I/System.out: elapsedtime=00:02:08
2021-05-30 18:29:23.749 I/System.out: }
2021-05-30 18:29:23.749 I/System.out: 4 {
2021-05-30 18:29:23.750 I/System.out: date=2021-01-03
2021-05-30 18:29:23.750 I/System.out: appname=App1
2021-05-30 18:29:23.750 I/System.out: unlocks=1
2021-05-30 18:29:23.750 I/System.out: elapsedtime=05:00:59
2021-05-30 18:29:23.751 I/System.out: }
2021-05-30 18:29:23.751 I/System.out: 5 {
2021-05-30 18:29:23.751 I/System.out: date=2021-01-03
2021-05-30 18:29:23.751 I/System.out: appname=App2
2021-05-30 18:29:23.751 I/System.out: unlocks=1
2021-05-30 18:29:23.752 I/System.out: elapsedtime=00:04:04
2021-05-30 18:29:23.752 I/System.out: }
2021-05-30 18:29:23.752 I/System.out: <<<<<
- Your app currently targets API level 30 and must target at least API level 31 to ensure it is built on the latest APIs
- How can I reduce the size of a library folder?
- Stream Audio from Client to Server to Multiple Clients Java
- Talkback does not specify index out of list count in RecyclerView
- Panorama 360 viewer with phonegap
- opening an external "file explorer" app: how to get absolute path from a uri pointing to a folder
- Which ParseException may be thrown on signup/login attempt
- What I should add as a key to LaunchedEffect to be changeable?
- Variable not updated in a Coroutine
- How To Stop Git-Bash command on windows
- Getting Error while Dexing. after upgrading flutter to 3.19.0
- Can't find the option to change privacy policy for Android app already on play store
- Button not responding to Click Event after Translation animation
- Using libcurl in C++ Android application using CMake
- Why compose adds extra text to Accessibility?
- packages have newer versions incompatible with dependency constraints
- How can I scroll to the beginning of a paged list after the start of the list is no longer presented
- why flutter_fortune_wheel always run instantly after page open?
- How to create Viewpager Transformation in Jetpack Compose?
- Remove underline from TextInputEditText
- String replace isn't working for some cyrillic characters
- How to create push notification with long text and picture in one style
- Android ListPopupWindow's method isShowing() does not work
- How to remove top and bottom padding exo player in android jetpack compose?
- flutter doctor --android-licenses does not work
- Eliminating Blinking Effect in Rotational Animation of List Elements
- Flutter Firebase and Android issue - unable to initialise. Cannot find google-services.json with latest (sept 2020) migration instructions executed
- Magento REST API for Android Application
- input type=number not possible to insert negative numbers on mobile phone
- Why doen't Compose trigger a recomposition on State update?