英文:
Using multiple SQLite Databases in my android app project
问题
I see you're working on an Android alarm clock app and have concerns about handling database tables and IDs when deleting items from a queue. It's a complex topic, and there are various strategies to manage this.
One approach is to implement a soft delete system, where instead of physically removing rows from the database, you mark them as deleted. You can add a "deleted" flag in your table and set it to true when an item is removed. This way, the IDs remain stable, and you can filter out deleted items when displaying the queue.
Another option is to use a unique identifier for each item in your queue that doesn't change, regardless of deletions. This identifier can be a combination of the table's primary key and a queue-specific ID.
Both of these methods require some changes to your existing database structure and query logic. The choice depends on your specific requirements and how you want to handle deleted items.
If you have further questions or need specific code modifications, feel free to ask.
英文:
so i am currently programming an alarm clock app for android, which i am still learning and i was wondering if i should make a DBHelper class for every database that i create, or how i can handle multiple tables pointing to each other by ids. It's problematic for me, because i populate a RecyclerView with data from said tables. Now problems start to arise, as soon as i try to delete data at runtime, the table ids are all primary key autoincrement, so if i delete any element, this will cause the id to not represent the row number of the database table, sadly autoincrement doesnt handle ids to decrease if an item in a row before them is deleted.
At first i tried communicating the data for the alarms across multiple activities via sharedPreference, i wanted the data to persist if the app crashes for instance, so i switched to sqlite, i do have my DBHelper class, carrying all of the tables that i use:
package com.example.alarm;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;
import java.util.Objects;
public class DBHelper extends SQLiteOpenHelper{
Context context;
public DBHelper(Context context, String db) {
super(context, db, null, 1);
this.context = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create Table Alarmdatabase (id INTEGER primary key autoincrement, label TEXT, method_queue_id INTEGER, sound_path_id INTEGER, privilege_rights INTEGER, snoozable INTEGER, time_wake_up_hours INTEGER, time_wake_up_minutes INTEGER, days_schedule_id INTEGER, weeks_schedule_id INTEGER, check_awake INTEGER, alarm_level_table_id INTEGER)");
//every int representing a bool is -1 for false this is the id of the table and the item in it, for the corresponding method
db.execSQL("create Table Methoddatabase (id INTEGER primary key autoincrement, queue_id INTEGER, method_type_id INTEGER, method_id INTEGER, difficulty_id INTEGER, label TEXT, method_database_specific_id INTEGER)"); //multiple rows with same queue_id are part of the same queue
db.execSQL("create Table Methodtype (id INTEGER primary key autoincrement, method_type TEXT)");
db.execSQL("create Table Method (id INTEGER primary key autoincrement, method TEXT)");
db.execSQL("create Table Difficulty (id INTEGER primary key autoincrement, difficulty TEXT)");
db.execSQL("create Table Alarmlevel (id INTEGER primary key autoincrement, level_id INTEGER, method_databse_queue_id INTEGER)"); //multiple rows with the same level_id make up the different Alarmlevels, might add more attribs later
db.execSQL("create Table QRBarcodedatabase (label TEXT primary key, decoded TEXT)");
db.execSQL("create Table Mathdatabase (id INTEGER primary key autoincrement, method TEXT, difficulty TEXT)");
db.execSQL("create Table Locationdatabase (id INTEGER primary key autoincrement, latitude_int INTEGER, zero_point_latitude INTEGER, longitude_int INTEGER, zero_point_longitude INTEGER, radius_int INTEGER, zero_point_radius INTEGER, street TEXT, radius_mode TEXT)");
setupTablesForPreset(db);
}
private void setupTablesForPreset(SQLiteDatabase db) {
ContentValues cv = new ContentValues();
long res;
for(String type : new String[]{"tap_off","math","qr_barcode","location","sudoku","memory","passphrase"}) {
cv.put("method_type", type);
res = db.insert("Methodtype", null, cv);
if(res == -1){
Toast.makeText(context, "Error setting up static Database \"Methodtype\"", Toast.LENGTH_SHORT).show();
}
}
cv = new ContentValues();
for(String type : new String[]{"null","add","sub","mult","div","fac","root","value_fx","extrema_fx","multiple_choice","reach_radius","leave_radius"}) {
cv.put("method", type);
res = db.insert("Method", null, cv);
if(res == -1){
Toast.makeText(context, "Error setting up static Database \"Method\"", Toast.LENGTH_SHORT).show();
}
}
cv = new ContentValues();
for(String type : new String[]{"ex_easy","easy","middle","hard","ex_hard"}) {
cv.put("difficulty", type);
res = db.insert("Difficulty", null, cv);
if(res == -1){
Toast.makeText(context, "Error setting up static Database \"Difficulty\"", Toast.LENGTH_SHORT).show();
}
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int verOld, int verNew) {
db.execSQL("drop Table if exists Alarmdatabase");
db.execSQL("drop Table if exists Methoddatabase");
db.execSQL("drop Table if exists Methodtype");
db.execSQL("drop Table if exists Method");
db.execSQL("drop Table if exists Difficulty");
db.execSQL("drop Table if exists Alarmlevel");
db.execSQL("drop Table if exists QRBarcodedatabase");
db.execSQL("drop Table if exists Mathdatabase");
db.execSQL("drop Table if exists Locationdatabase");
onCreate(db);
}
//TODO: I don't need to handle SQLInjection, atleast for now, because sharing of settings is not supported as of now, so this would only harm the person itself, or be a nice niche feature lol
//(this might change in the future, so i will keep this todo in until publishing, so if i add that feature at any point, i'll know, that i have to take care of that.)
public void deleteRow(String table, int row_id){
SQLiteDatabase db = this.getWritableDatabase();
long res = db.delete(table, "id=?", new String[]{String.valueOf(row_id+2)});
if(res ==0){
Toast.makeText(context, "Failed deleting item: " + res, Toast.LENGTH_SHORT).show();
}else {
Toast.makeText(context, "Success deleting item: " +res, Toast.LENGTH_SHORT).show();
}
}
public void deleteRow(String table, String label){
SQLiteDatabase db = this.getWritableDatabase();
long res = db.delete(table, "label=?", new String[]{label});
if(res ==-1){
Toast.makeText(context, "Failed deleting item", Toast.LENGTH_SHORT).show();
}else {
Toast.makeText(context, "Success deleting item", Toast.LENGTH_SHORT).show();
}
}
public void addAlarm(String label, int methodQueueId, int soundPathId, boolean privilegeRights, boolean snoozable, int wakeUpTimeHours, int wakeUpTimeMinutes, int daysScheduleId, int weeksScheduleId, boolean checkAwake, int alarmLevelTableId){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("label", label);
cv.put("method_queue_id",methodQueueId);
cv.put("sound_path_id",soundPathId);
if(privilegeRights){
cv.put("privilege_rights",1);}
else{
cv.put("privilege_rights", 0);}
if(snoozable){
cv.put("snoozable",1);}
else{
cv.put("snoozable",0);}
cv.put("time_wake_up_hours", wakeUpTimeHours);
cv.put("time_wake_up_minutes", wakeUpTimeMinutes);
cv.put("days_schedule_id",daysScheduleId);
cv.put("weeks_schedule_id",weeksScheduleId);
if(checkAwake){
cv.put("check_awake", 1);}else{
cv.put("check_awake", 0);}
cv.put("alarm_level_table_id",alarmLevelTableId);
long res = db.insert("Alarmdatabase", null, cv);
if(res == -1){
Toast.makeText(context, "Inserting into Alarmdatabase failed", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Successfully added into Alarmdatabase", Toast.LENGTH_SHORT).show();
}
}
public void addMethod(int queueID, int methodTypeId, int methodId, int difficultyId, String label, int methodDatabaseSpecificID){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("queue_id",queueID);
cv.put("method_type_id",methodTypeId);
if(methodId != -1) cv.put("method_id",methodId);
if(difficultyId != -1) cv.put("difficulty_id",difficultyId);
if(!Objects.equals(label, "-1")) cv.put("label",label);
cv.put("method_database_specific_id", methodDatabaseSpecificID); //This is the id, that (if necessary) points to the specific data of the method database defined by method_type_id
//In easy words: if you need to look up the decoded string from qrcode method, this points to the id in the specific database, that has this info
long res = db.insert("Methoddatabase", null, cv);
if(res == -1){
Toast.makeText(context, "Inserting into Methoddatabase failed", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Successfully added into Methoddatabase", Toast.LENGTH_SHORT).show();
}
}
public String getMethodById(int id){
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery("SELECT * FROM Method WHERE ?", new String[]{"id =" + id});
if(c.getCount() == 0){
return "non existent";
}else{
c.moveToNext();
return c.getString(1);
}
}
public String getMethodTypeById(int id){
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery("SELECT * FROM MethodType WHERE ?", new String[]{"id =" + id});
if(c.getCount() > 0){
c.moveToFirst();
return c.getString(1);
}else{
return "non existent";
}
}
public String getDifficulty(int id){
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery("SELECT * FROM Difficulty WHERE ?", new String[]{"id =" + id});
if(c.getCount() == 0){
return "non existent";
}else{
c.moveToNext();
return c.getString(1);
}
}
public int findIdByMethodType(String methodType){
String[] methArr = new String[]{"tap_off","math","qr_barcode","location","sudoku","memory","passphrase"};
for(int i = 0; i < methArr.length; i++) {
if(methArr[i].equals(methodType)){
return i+1;
}
}
return -1;
}
public int findIdByMethod(String method){
String[] meArr = new String[]{"null","Addition","Subtraction","Multiplication","Division","Faculty (x!)","Root","Value for f(x)","Determine extrema of f(x)","Multiple choice questions","reach_radius","leave_radius"};
for(int i = 0; i < meArr.length; i++){
if(meArr[i].equals(method)){
return i+1;
}
}
return -1;
}
public int findIdByDifficulty(String difficulty){
switch (difficulty) {
case "Extremely easy":
return 1;
case "Easy":
return 2;
case "Middle":
return 3;
case "Hard":
return 4;
case "Extremely hard":
return 5;
default:
return -1;
}
}
public void addMath(String method, String difficulty){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("method",method);
cv.put("difficulty",difficulty);
long res = db.insert("Mathdatabase", null, cv);
if(res == -1){
Toast.makeText(context, "Inserting into Mathdatabase failed", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Successfully added into Mathdatabase", Toast.LENGTH_SHORT).show();
}
}
public void addQRBar(String label, String decoded){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("label", label);
cv.put("decoded", decoded);
long res = db.insert("QRBarcodedatabase", null, cv);
if(res == -1){
Toast.makeText(context, "Inserting into QRBarcodedatabase failed", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Successfully added into QRBarcodedatabase", Toast.LENGTH_SHORT).show();
}
}
// Locationdatabase latitude_int INTEGER, zero_point_latitude INTEGER, longitude_int INTEGER, zero_point_longitude INTEGER, radius_int INTEGER, zero_point_radius INTEGER, street TEXT)");
public long addLocation(int latitudeInt, int zeroPointLatitude, int longitudeInt, int zeroPointLongitude, int radiusInt, int zeroPointRadius, String street, String radiusMode){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("latitude_int",latitudeInt);
cv.put("zero_point_latitude",zeroPointLatitude);
cv.put("longitude_int",longitudeInt);
cv.put("zero_point_longitude",zeroPointLongitude);
cv.put("radius_int",radiusInt);
cv.put("zero_point_radius",zeroPointRadius);
cv.put("street",street);
cv.put("radius_mode", radiusMode);
long res = db.insert("Locationdatabase", null, cv);
if(res == -1){
Toast.makeText(context, "Inserting into Locationdatabase failed", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Successfully added into Locationdatabase", Toast.LENGTH_SHORT).show();
}
return res;
}
public void editMethoddatabase(int queue_id, int method_type_id, int method_id, int difficulty_id, String label, int method_database_spec_id, int row_id){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
if(queue_id != -1) cv.put("queue_id", queue_id);
if(method_type_id != -1) cv.put("method_type_id", method_type_id);
if(method_id != -1) cv.put("method_id", method_id);
if(difficulty_id != -1) cv.put("difficulty_id", difficulty_id);
if(!Objects.equals(label,"-1")) cv.put("label", label);
if(method_database_spec_id != -1) cv.put("method_database_specific_id", method_database_spec_id);
if (method_id == 1){
editMathdatabase(getMethodById(method_id), getDifficulty(difficulty_id), method_database_spec_id);
}
long result = db.update("Methoddatabase", cv, "id=?", new String[]{String.valueOf(row_id)});
if(result == -1){
Toast.makeText(context, "Failed to update Methoddatabase", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Success updating Methoddatabase", Toast.LENGTH_SHORT).show();
}
}
public void editMathdatabase(String method, String difficulty, int row_id){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues c = new ContentValues();
if (!Objects.equals(method, "")) c.put("method", method);
if (!Objects.equals(difficulty, "")) c.put("difficulty", difficulty);
long r = db.update("Mathdatabase",c, "id=?", new String[]{String.valueOf(row_id)});
if(r == -1){
Toast.makeText(context, "Failed to update Mathdatabase", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Success updating Mathdatabase", Toast.LENGTH_SHORT).show();
}
}
public void editLocation(int lat, int zerolat, int lon, int zerolon, int radius, int zeroradius, String street, String enter_leave, int row_id){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
int currlat, currzerolat, currlon, currzerolon, currradius, currzeroradius;
String currstreet, currenterleave;
Cursor c = getData("Locationdatabase");
if(c.getCount() > 0) {
while(c.moveToNext()){
if(c.getInt(0) == row_id){
currlat = c.getInt(1);
currzerolat = c.getInt(2);
currlon = c.getInt(3);
currzerolon = c.getInt(4);
currradius = c.getInt(5);
currzeroradius = c.getInt(6);
currstreet = c.getString(7);
currenterleave = c.getString(8);
if (lat != currlat) cv.put("latitude_int", lat);
if (zerolat != currzerolat) cv.put("zero_point_latitude", zerolat);
if (lon != currlon) cv.put("longitude_int", lon);
if (zerolon != currzerolon) cv.put("zero_point_longitude", zerolon);
if (radius != currradius) cv.put("radius_int", radius);
if (zeroradius != currzeroradius) cv.put("zero_point_radius", zeroradius);
if (!Objects.equals(street, currstreet)) cv.put("street", street);
if (!Objects.equals(enter_leave,currenterleave)) cv.put("radius_mode", enter_leave);
}
}
long res = db.update("Locationdatabase", cv, "id=?", new String[]{String.valueOf(row_id)});
if (res == -1) {
Toast.makeText(context, "Failed to update Locationdatabase", Toast.LENGTH_SHORT).show();
} else {
Toast.makeText(context, "Success updating Locationdatabase", Toast.LENGTH_SHORT).show();
}
}else{
Toast.makeText(context, "how did you manage to even get here wtf", Toast.LENGTH_SHORT).show();
}
}
public Cursor getData(String database ){
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = null;
if (db != null){
c = db.rawQuery("Select * from "+database, null);
}
return c;
}
public Cursor execQuery(String sql, String[] selectionArgs){
SQLiteDatabase db = this.getWritableDatabase();
return db.rawQuery(sql, selectionArgs);
}
}
To not confuse you: the alarm has a queue of methods to solve when turning off the alarm, methods as of now are settable for location(enter/leave radius), math tasks, scanning a qr/barcode, tapping off. I will implement a sudoku, memory and a typing your typephrase method. The Alarm should also be able to consist of different levels, so if you solve the first queue of methods, you can set x more method queues to come after that. Levels are not yet implemented, but will also be complicated when the ids are changing.
Does someone know, how i could change my code, so it wont get confused by deleting a method from the queue?
答案1
得分: 1
这没问题。ID 不应与表行关联。
你熟悉外键吗？这是用于在多个表之间建立关系的方法。
英文:
so if i delete any element, this will cause the id to not represent the row number of the database table
This is fine. IDs shouldn't be linked to table rows.
Are you familiar with foreign keys? That is how you are supposed to build a relationship between multiple tables.
答案2
得分: 0
以下是代码的翻译部分：
Ok, so i resorted to updating the database now. I have method_specific_id not as a foreign key, even though it is one, but it still works and a foreign key can also be set from more than 1 database.
Update of the Code:
package com.example.alarm;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;
import java.util.Objects;
public class DBHelper extends SQLiteOpenHelper{
Context context;
public DBHelper(Context context, String db) {
super(context, db, null, 1);
this.context = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create Table Alarmdatabase (id INTEGER primary key autoincrement, label TEXT, method_queue_id INTEGER, privilege_rights INTEGER," +
" time_wake_up_hours INTEGER, time_wake_up_minutes INTEGER, days_schedule_id INTEGER, weeks_schedule_id INTEGER, check_awake INTEGER," +
" alarm_level_id INTEGER, foreign key (alarm_level_id) references Alarmlevel(id))");
//every int representing a bool is -1 for false //represents how many methods are in the queue
//alarm_level_id points to the Alarmlevel Table which has the level specific labels, snooze settings, sounds, etc
//if alarm levels not used, just set everything as alarm level 1
db.execSQL("create Table Methoddatabase (id INTEGER primary key autoincrement, queue_id INTEGER, method_type_id INTEGER, method_id INTEGER, difficulty_id INTEGER," +
" label TEXT, method_database_specific_id INTEGER, foreign KEY(method_type_id) references Methodtype(id), foreign key (method_id) references Method(id)," +
" foreign key (difficulty_id) references Difficulty(id), foreign key(label) references QRBarcodedatabase(label)," +
" foreign KEY(queue_id) references Alarmlevel(id))");
db.execSQL("create Table Methodtype (id INTEGER primary key autoincrement, method_type TEXT)");
db.execSQL("create Table Method (id INTEGER primary key autoincrement, method TEXT)");
db.execSQL("create Table Difficulty (id INTEGER primary key autoincrement, difficulty TEXT)");
db.execSQL("create Table Alarmlevel (id INTEGER primary key autoincrement, label TEXT, snooze_count INTEGER, snooze_time INTEGER, sound_path TEXT)"); //multiple rows with the same level_id make up the different Alarmlevels, might add more attribs later
db.execSQL("create Table QRBarcodedatabase (label TEXT primary key, decoded TEXT)");
db.execSQL("create Table Mathdatabase (id INTEGER primary key autoincrement, method TEXT, difficulty TEXT)");
db.execSQL("create Table Locationdatabase (id INTEGER primary key autoincrement, latitude_int INTEGER, zero_point_latitude INTEGER, longitude_int INTEGER," +
" zero_point_longitude INTEGER, radius_int INTEGER, zero_point_radius INTEGER, street TEXT, radius_mode TEXT)");
setupTablesForPreset(db);
}
@Override
public void onUpgrade(SQLiteDatabase db, int verOld, int verNew) {
db.execSQL("drop Table if exists Alarmdatabase");
db.execSQL("drop Table if exists Methoddatabase");
db.execSQL("drop Table if exists Methodtype");
db.execSQL("drop Table if exists Method");
db.execSQL("drop Table if exists Difficulty");
db.execSQL("drop Table if exists Alarmlevel");
db.execSQL("drop Table if exists QRBarcodedatabase");
db.execSQL("drop Table if exists Mathdatabase");
db.execSQL("drop Table if exists Locationdatabase");
onCreate(db);
}
private void setupTablesForPreset(SQLiteDatabase db) {
ContentValues cv = new ContentValues();
long res;
for(String type : new String[]{"tap_off","math","qr_barcode","location","sudoku","memory","passphrase"}) {
cv.put("method_type", type);
res = db.insert("Methodtype", null, cv);
if(res == -1){
Toast.makeText(context, "Error setting up static Database 'Methodtype'", Toast.LENGTH_SHORT).show();
}
}
cv = new ContentValues();
for(String type : new String[]{"null","add","sub","mult","div","fac","root","value_fx","extrema_fx","multiple_choice","reach_radius","leave_radius"}) {
cv.put("method", type);
res = db.insert("Method", null, cv);
if(res == -1){
Toast.makeText(context, "Error setting up static Database 'Method'", Toast.LENGTH_SHORT).show();
}
}
cv = new ContentValues();
for(String type : new String[]{"ex_easy","easy","middle","hard","ex_hard"}) {
cv.put("difficulty", type);
res = db.insert("Difficulty", null, cv);
if(res == -1){
Toast.makeText(context, "Error setting up static Database 'Difficulty'", Toast.LENGTH_SHORT).show();
}
}
}
/**
* @param database: the string of the table in database.db
* @return the max id in the table, if db empty it returns 0
*/
public int getMaxTableId(String database){
SQLiteDatabase db = this.getReadableDatabase();
Cursor c1 = db.rawQuery("SELECT max(id) FROM " + database, null);
if(c1.getCount() >0){
int id = 0;
c1.moveToFirst();
id = c1.getInt(0);
c1.close();
return id;
}
return 0;
}
//TODO: I don't need to handle SQLInjection, atleast for now, because sharing of settings is not supported as of now, so this would only harm the person itself, or be a nice niche feature lol
//(this might change in the future, so i will keep this todo in until publishing, so if i add that feature at any point, i'll know, that i have to take care of that.)
public void deleteRow(String table, int row_id){
SQLiteDatabase db = this.getWritableDatabase();
long res = db.delete(table, "id=?", new String[]{String.valueOf(row_id)});
if(res ==0){
Toast.makeText(context, "Failed deleting item: " + res, Toast.LENGTH_SHORT).show();
}else {
Toast.makeText(context, "Success deleting item: " +res, Toast.LENGTH_SHORT).show();
}
}
public void addAlarm(String label, int methodQueueId, int soundPathId, boolean privilegeRights, boolean snoozable, int wakeUpTimeHours, int wakeUpTimeMinutes, int daysScheduleId, int weeksScheduleId, boolean checkAwake, int alarmLevelTableId){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("label", label);
cv.put("method_queue_id",methodQueueId);
cv.put("sound_path_id",soundPathId);
if(privilegeRights){
cv.put("privilege_rights",1);
}else{
cv.put("privilege_rights", 0);
}
if(snoozable){
cv.put("snoozable",1);
}else{
cv.put("snoozable",0);
}
cv.put("time_wake_up_hours", wake
<details>
<summary>英文:</summary>
Ok, so i resorted to updating the database now. I have method_specific_id not as a foreign key, even though it is one, but it still works and a foreign key can also be set from more than 1 database.
Update of the Code:
package com.example.alarm;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;
import java.util.Objects;
public class DBHelper extends SQLiteOpenHelper{
Context context;
public DBHelper(Context context, String db) {
super(context, db, null, 1);
this.context = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create Table Alarmdatabase (id INTEGER primary key autoincrement, label TEXT, method_queue_id INTEGER, privilege_rights INTEGER," +
" time_wake_up_hours INTEGER, time_wake_up_minutes INTEGER, days_schedule_id INTEGER, weeks_schedule_id INTEGER, check_awake INTEGER," +
" alarm_level_id INTEGER, foreign key (alarm_level_id) references Alarmlevel(id))");
//every int representing a bool is -1 for false //represents how many methods are in the queue
//alarm_level_id points to the Alarmlevel Table which has the level specific labels, snooze settings, sounds, etc
//if alarm levels not used, just set everything as alarm level 1
db.execSQL("create Table Methoddatabase (id INTEGER primary key autoincrement, queue_id INTEGER, method_type_id INTEGER, method_id INTEGER, difficulty_id INTEGER," +
" label TEXT, method_database_specific_id INTEGER, foreign KEY(method_type_id) references Methodtype(id), foreign key (method_id) references Method(id)," +
" foreign key (difficulty_id) references Difficulty(id), foreign key(label) references QRBarcodedatabase(label)," +
" foreign KEY(queue_id) references Alarmlevel(id))");
db.execSQL("create Table Methodtype (id INTEGER primary key autoincrement, method_type TEXT)");
db.execSQL("create Table Method (id INTEGER primary key autoincrement, method TEXT)");
db.execSQL("create Table Difficulty (id INTEGER primary key autoincrement, difficulty TEXT)");
db.execSQL("create Table Alarmlevel (id INTEGER primary key autoincrement, label TEXT, snooze_count INTEGER, snooze_time INTEGER, sound_path TEXT)"); //multiple rows with the same level_id make up the different Alarmlevels, might add more attribs later
db.execSQL("create Table QRBarcodedatabase (label TEXT primary key, decoded TEXT)");
db.execSQL("create Table Mathdatabase (id INTEGER primary key autoincrement, method TEXT, difficulty TEXT)");
db.execSQL("create Table Locationdatabase (id INTEGER primary key autoincrement, latitude_int INTEGER, zero_point_latitude INTEGER, longitude_int INTEGER," +
" zero_point_longitude INTEGER, radius_int INTEGER, zero_point_radius INTEGER, street TEXT, radius_mode TEXT)");
setupTablesForPreset(db);
}
@Override
public void onUpgrade(SQLiteDatabase db, int verOld, int verNew) {
db.execSQL("drop Table if exists Alarmdatabase");
db.execSQL("drop Table if exists Methoddatabase");
db.execSQL("drop Table if exists Methodtype");
db.execSQL("drop Table if exists Method");
db.execSQL("drop Table if exists Difficulty");
db.execSQL("drop Table if exists Alarmlevel");
db.execSQL("drop Table if exists QRBarcodedatabase");
db.execSQL("drop Table if exists Mathdatabase");
db.execSQL("drop Table if exists Locationdatabase");
onCreate(db);
}
private void setupTablesForPreset(SQLiteDatabase db) {
ContentValues cv = new ContentValues();
long res;
for(String type : new String[]{"tap_off","math","qr_barcode","location","sudoku","memory","passphrase"}) {
cv.put("method_type", type);
res = db.insert("Methodtype", null, cv);
if(res == -1){
Toast.makeText(context, "Error setting up static Database \"Methodtype\"", Toast.LENGTH_SHORT).show();
}
}
cv = new ContentValues();
for(String type : new String[]{"null","add","sub","mult","div","fac","root","value_fx","extrema_fx","multiple_choice","reach_radius","leave_radius"}) {
cv.put("method", type);
res = db.insert("Method", null, cv);
if(res == -1){
Toast.makeText(context, "Error setting up static Database \"Method\"", Toast.LENGTH_SHORT).show();
}
}
cv = new ContentValues();
for(String type : new String[]{"ex_easy","easy","middle","hard","ex_hard"}) {
cv.put("difficulty", type);
res = db.insert("Difficulty", null, cv);
if(res == -1){
Toast.makeText(context, "Error setting up static Database \"Difficulty\"", Toast.LENGTH_SHORT).show();
}
}
}
/**
* @param database: the string of the table in database.db
* @return the max id in the table, if db empty it returns 0
*/
public int getMaxTableId(String database){
SQLiteDatabase db = this.getReadableDatabase();
Cursor c1 = db.rawQuery("SELECT max(id) FROM " + database, null);
if(c1.getCount() >0){
int id = 0;
c1.moveToFirst();
id = c1.getInt(0);
c1.close();
return id;
}
return 0;
}
//TODO: I don't need to handle SQLInjection, atleast for now, because sharing of settings is not supported as of now, so this would only harm the person itself, or be a nice niche feature lol
//(this might change in the future, so i will keep this todo in until publishing, so if i add that feature at any point, i'll know, that i have to take care of that.)
public void deleteRow(String table, int row_id){
SQLiteDatabase db = this.getWritableDatabase();
long res = db.delete(table, "id=?", new String[]{String.valueOf(row_id)});
if(res ==0){
Toast.makeText(context, "Failed deleting item: " + res, Toast.LENGTH_SHORT).show();
}else {
Toast.makeText(context, "Success deleting item: " +res, Toast.LENGTH_SHORT).show();
}
}
public void addAlarm(String label, int methodQueueId, int soundPathId, boolean privilegeRights, boolean snoozable, int wakeUpTimeHours, int wakeUpTimeMinutes, int daysScheduleId, int weeksScheduleId, boolean checkAwake, int alarmLevelTableId){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("label", label);
cv.put("method_queue_id",methodQueueId);
cv.put("sound_path_id",soundPathId);
if(privilegeRights){
cv.put("privilege_rights",1);}
else{
cv.put("privilege_rights", 0);}
if(snoozable){
cv.put("snoozable",1);}
else{
cv.put("snoozable",0);}
cv.put("time_wake_up_hours", wakeUpTimeHours);
cv.put("time_wake_up_minutes", wakeUpTimeMinutes);
cv.put("days_schedule_id",daysScheduleId);
cv.put("weeks_schedule_id",weeksScheduleId);
if(checkAwake){
cv.put("check_awake", 1);}else{
cv.put("check_awake", 0);}
cv.put("alarm_level_table_id",alarmLevelTableId);
long res = db.insert("Alarmdatabase", null, cv);
if(res == -1){
Toast.makeText(context, "Inserting into Alarmdatabase failed", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Successfully added into Alarmdatabase", Toast.LENGTH_SHORT).show();
}
}
public void addMethod(int id, int queueID, int methodTypeId, int methodId, int difficultyId, String label, int methodDatabaseSpecificID){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("queue_id",queueID);
cv.put("method_type_id",methodTypeId);
if(methodId != -1) cv.put("method_id",methodId);
if(difficultyId != -1) cv.put("difficulty_id",difficultyId);
if(!Objects.equals(label, "-1")) cv.put("label",label);
cv.put("method_database_specific_id", methodDatabaseSpecificID); //This is the id, that (if necessary) points to the specific data of the method database defined by method_type_id
//In easy words: if you need to look up the decoded string from qrcode method, this points to the id in the specific database, that has this info
long res = db.insert("Methoddatabase", null, cv);
if(res == -1){
Toast.makeText(context, "Inserting into Methoddatabase failed", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Successfully added into Methoddatabase", Toast.LENGTH_SHORT).show();
}
}
public String getMethodById(int id){
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery("SELECT * FROM Method WHERE ?", new String[]{"id =" + id});
if(c.getCount() == 0){
return "non existent";
}else{
c.moveToNext();
return c.getString(1);
}
}
public String getMethodTypeById(int id){
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery("SELECT * FROM MethodType WHERE ?", new String[]{"id =" + id});
if(c.getCount() > 0){
c.moveToFirst();
return c.getString(1);
}else{
return "non existent";
}
}
public String getDifficulty(int id){
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery("SELECT * FROM Difficulty WHERE ?", new String[]{"id =" + id});
if(c.getCount() == 0){
return "non existent";
}else{
c.moveToNext();
return c.getString(1);
}
}
public int findIdByMethodType(String methodType){
String[] methArr = new String[]{"tap_off","math","qr_barcode","location","sudoku","memory","passphrase"};
for(int i = 0; i < methArr.length; i++) {
if(methArr[i].equals(methodType)){
return i+1;
}
}
return -1;
}
public int findIdByMethod(String method){
String[] meArr = new String[]{"null","Addition","Subtraction","Multiplication","Division","Faculty (x!)","Root","Value for f(x)","Determine extrema of f(x)","Multiple choice questions","reach_radius","leave_radius"};
for(int i = 0; i < meArr.length; i++){
if(meArr[i].equals(method)){
return i+1;
}
}
return -1;
}
public int findIdByDifficulty(String difficulty){
switch (difficulty) {
case "Extremely easy":
return 1;
case "Easy":
return 2;
case "Middle":
return 3;
case "Hard":
return 4;
case "Extremely hard":
return 5;
default:
return -1;
}
}
public void addMath(String method, String difficulty){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("method",method);
cv.put("difficulty",difficulty);
long res = db.insert("Mathdatabase", null, cv);
if(res == -1){
Toast.makeText(context, "Inserting into Mathdatabase failed", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Successfully added into Mathdatabase", Toast.LENGTH_SHORT).show();
}
}
public void addQRBar(String label, String decoded){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("label", label);
cv.put("decoded", decoded);
long res = db.insert("QRBarcodedatabase", null, cv);
if(res == -1){
Toast.makeText(context, "Inserting into QRBarcodedatabase failed", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Successfully added into QRBarcodedatabase", Toast.LENGTH_SHORT).show();
}
}
// Locationdatabase latitude_int INTEGER, zero_point_latitude INTEGER, longitude_int INTEGER, zero_point_longitude INTEGER, radius_int INTEGER, zero_point_radius INTEGER, street TEXT)");
public long addLocation(int latitudeInt, int zeroPointLatitude, int longitudeInt, int zeroPointLongitude, int radiusInt, int zeroPointRadius, String street, String radiusMode){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("latitude_int",latitudeInt);
cv.put("zero_point_latitude",zeroPointLatitude);
cv.put("longitude_int",longitudeInt);
cv.put("zero_point_longitude",zeroPointLongitude);
cv.put("radius_int",radiusInt);
cv.put("zero_point_radius",zeroPointRadius);
cv.put("street",street);
cv.put("radius_mode", radiusMode);
long res = db.insert("Locationdatabase", null, cv);
if(res == -1){
Toast.makeText(context, "Inserting into Locationdatabase failed", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Successfully added into Locationdatabase", Toast.LENGTH_SHORT).show();
}
return res;
}
public long addLevel(int id, String label, int snooze_count, int snooze_time, String sound_path){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
if(snooze_count != -2) cv.put("snooze_count", snooze_count);
if(snooze_time != -1) cv.put("snooze_time", snooze_time);
if(label != null) cv.put("label", label);
if(!Objects.equals(sound_path, "-1")) cv.put("sound_path", sound_path);
long result = db.insert("Alarmlevel", null, cv);
if(result == -1){
Toast.makeText(context, "Inserting into Alarmlevel failed", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Successfully added into Alarmlevel", Toast.LENGTH_SHORT).show();
}
return result;
}
public void editLevel(int id, String label, int snooze_count, int snooze_time, String sound_path){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
if(snooze_count != -2) cv.put("snooze_count", snooze_count);
if(snooze_time != -1) cv.put("snooze_time", snooze_time);
if(!Objects.equals(label, "-1")) cv.put("label", label);
if(!Objects.equals(sound_path, "-1")) cv.put("sound_path", sound_path);
long result = db.update("Alarmlevel", cv, "id=?", new String[]{String.valueOf(id)});
if(result == -1){
Toast.makeText(context, "Failed to update alarm level", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Success updating alarm level", Toast.LENGTH_SHORT).show();
}
}
public void editMethoddatabase(int queue_id, int method_type_id, int method_id, int difficulty_id, String label, int method_database_spec_id, int row_id){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
if(queue_id != -1) cv.put("queue_id", queue_id);
if(method_type_id != -1) cv.put("method_type_id", method_type_id);
if(method_id != -1) cv.put("method_id", method_id);
if(difficulty_id != -1) cv.put("difficulty_id", difficulty_id);
if(!Objects.equals(label,"-1")) cv.put("label", label);
if(method_database_spec_id != -1) cv.put("method_database_specific_id", method_database_spec_id);
if (method_id == 1){
editMathdatabase(getMethodById(method_id), getDifficulty(difficulty_id), method_database_spec_id);
}
long result = db.update("Methoddatabase", cv, "id=?", new String[]{String.valueOf(row_id)});
if(result == -1){
Toast.makeText(context, "Failed to update Methoddatabase", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Success updating Methoddatabase", Toast.LENGTH_SHORT).show();
}
}
public void editMathdatabase(String method, String difficulty, int row_id){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues c = new ContentValues();
if (!Objects.equals(method, "")) c.put("method", method);
if (!Objects.equals(difficulty, "")) c.put("difficulty", difficulty);
long r = db.update("Mathdatabase",c, "id=?", new String[]{String.valueOf(row_id)});
if(r == -1){
Toast.makeText(context, "Failed to update Mathdatabase", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(context, "Success updating Mathdatabase", Toast.LENGTH_SHORT).show();
}
}
public void editLocation(int lat, int zerolat, int lon, int zerolon, int radius, int zeroradius, String street, String enter_leave, int row_id){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
int currlat, currzerolat, currlon, currzerolon, currradius, currzeroradius;
String currstreet, currenterleave;
Cursor c = getData("Locationdatabase");
if(c.getCount() > 0) {
while(c.moveToNext()){
if(c.getInt(0) == row_id){
currlat = c.getInt(1);
currzerolat = c.getInt(2);
currlon = c.getInt(3);
currzerolon = c.getInt(4);
currradius = c.getInt(5);
currzeroradius = c.getInt(6);
currstreet = c.getString(7);
currenterleave = c.getString(8);
if (lat != currlat) cv.put("latitude_int", lat);
if (zerolat != currzerolat) cv.put("zero_point_latitude", zerolat);
if (lon != currlon) cv.put("longitude_int", lon);
if (zerolon != currzerolon) cv.put("zero_point_longitude", zerolon);
if (radius != currradius) cv.put("radius_int", radius);
if (zeroradius != currzeroradius) cv.put("zero_point_radius", zeroradius);
if (!Objects.equals(street, currstreet)) cv.put("street", street);
if (!Objects.equals(enter_leave,currenterleave)) cv.put("radius_mode", enter_leave);
}
}
long res = db.update("Locationdatabase", cv, "id=?", new String[]{String.valueOf(row_id)});
if (res == -1) {
Toast.makeText(context, "Failed to update Locationdatabase", Toast.LENGTH_SHORT).show();
} else {
Toast.makeText(context, "Success updating Locationdatabase", Toast.LENGTH_SHORT).show();
}
}else{
Toast.makeText(context, "how did you manage to even get here wtf", Toast.LENGTH_SHORT).show();
}
}
public Cursor getData(String database ){
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = null;
if (db != null){
c = db.rawQuery("Select * from "+database, null);
}
return c;
}
public Cursor execQuery(String sql, String[] selectionArgs){
SQLiteDatabase db = this.getWritableDatabase();
return db.rawQuery(sql, selectionArgs);
}
}
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论