Edit: I want your opinion onchanged my approach of an SQLiteOpenHelper class. I have removed methods that are not important for this question, for better overview.
My desired behavior works, but I wonder if it is efficient. Question objects have a category as one of their arguments (in form of a string). When I add a new Question object, either over the addQuestion(Question question) or addQuestions(List<Question> questionList) method, I wantam trying to query my database and check, which categories in my questions don't have an entry in the Categories table yet. If the category doesn't appear there yet, I add it. So the categories table dynamically grows by adding Question objects to the database that have a new category. The addNewCategories method takes care of this checkmy existing SQLite quiz. I turn the List<String>, which contains all the categories of the question listThis is my approach, into a LinkedHashSet to let the system remove duplicates.
Iswhat do you think about it necessary to implement some foreign key logic here?
And do Do you think I should just skip the Categories table completelyforeign key restriction is necessary? Because if I later need to retrieve allam a total SQLite noob and it's the categories,first time I can do this withuse a DISTINCT query right?foreign key.
public class QuizDbHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "MyAwesomeQuiz.db";
private static final int DATABASE_VERSION = 1;
private SQLiteDatabase db;
public QuizDbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
this.db = db;
final String SQL_CREATE_CATEGORIES_TABLE = "CREATE TABLE " +
CategoriesTable.TABLE_NAME + "( " +
CategoriesTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
CategoriesTable.COLUMN_NAME + " TEXT " +
")";
final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
QuestionsTable.TABLE_NAME + " ( " +
QuestionsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
QuestionsTable.COLUMN_QUESTION + " TEXT, " +
QuestionsTable.COLUMN_OPTION1 + " TEXT, " +
QuestionsTable.COLUMN_OPTION2 + " TEXT, " +
QuestionsTable.COLUMN_OPTION3 + " TEXT, " +
QuestionsTable.COLUMN_ANSWER_NR + " INTEGER, " +
QuestionsTable.COLUMN_DIFFICULTY + " TEXT, " +
QuestionsTable.COLUMN_CATEGORY + " TEXTINTEGER, " +
"FOREIGN KEY(" + QuestionsTable.COLUMN_CATEGORY + ") REFERENCES " +
CategoriesTable.TABLE_NAME + "(" + QuestionsTable._ID + ")" +
")";
db.execSQL(SQL_CREATE_CATEGORIES_TABLE);
db.execSQL(SQL_CREATE_QUESTIONS_TABLE);
fillCategoriesTable();
fillQuestionsTable();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + QuestionsTable.TABLE_NAME);
onCreate(db);
}
private@Override
public void fillQuestionsTableonConfigure(SQLiteDatabase db) {
ArrayList<Question>super.onConfigure(db);
questionList = new ArrayList<>db.setForeignKeyConstraintsEnabled(true);
}
private void fillQuestionsTable() {
Question q1 = new Question("Easy: A is correct",
"A", "B", "C", 1, Question.DIFFICULTY_EASY, "Cat 2"1);
questionList.addaddQuestion(q1);
Question q2 = new Question("Medium: B is correct",
"A", "B", "C", 2, Question.DIFFICULTY_MEDIUM, "Cat 2"2);
questionList.addaddQuestion(q2);
Question q3 = new Question("Medium: C is correct",
"A", "B", "C", 3, Question.DIFFICULTY_MEDIUM, "Cat 1"3);
questionList.addaddQuestion(q3);
Question q4 = new Question("Hard: A is correct",
"A", "B", "C", 1, Question.DIFFICULTY_HARD, "Cat 4"4);
questionList.addaddQuestion(q4);
Question q5 = new Question("Hard: B is correct",
"A", "B", "C", 2, Question.DIFFICULTY_HARD, "Cat 4"5);
questionList.addaddQuestion(q5);
Question q6 = new Question("Hard: C is correct",
"A", "B", "C", 3, Question.DIFFICULTY_HARD, "Cat 5"6);
questionList.addaddQuestion(q6);
addQuestions(questionList);
}
private void addQuestion(Question question) {
List<Question> questionList = new ArrayList<>();
questionList.add(question);
addNewCategories(questionList);
ContentValues cv = new ContentValues();
cv.put(QuestionsTable.COLUMN_QUESTION, question.getQuestion());
cv.put(QuestionsTable.COLUMN_OPTION1, question.getOption1());
cv.put(QuestionsTable.COLUMN_OPTION2, question.getOption2());
cv.put(QuestionsTable.COLUMN_OPTION3, question.getOption3());
cv.put(QuestionsTable.COLUMN_ANSWER_NR, question.getAnswerNr());
cv.put(QuestionsTable.COLUMN_DIFFICULTY, question.getDifficulty());
cv.put(QuestionsTable.COLUMN_CATEGORY, question.getCategory());
db.insert(QuestionsTable.TABLE_NAME, null, cv);
}
private void addQuestions(List<Question> questionList) {
addNewCategories(questionList);
private forvoid fillCategoriesTable(Question question : questionList) {
ContentValuesCategory cvc1 = new ContentValues();
cv.put(QuestionsTable.COLUMN_QUESTION, question.getQuestion());
cv.put(QuestionsTable.COLUMN_OPTION1, question.getOption1());
cv.putCategory(QuestionsTable.COLUMN_OPTION2,"Cat question.getOption2()1");
cv.put(QuestionsTable.COLUMN_OPTION3, question.getOption3addCategory()c1);
Category c2 = new cv.putCategory(QuestionsTable.COLUMN_ANSWER_NR,"Cat question.getAnswerNr()2");
cv.put(QuestionsTable.COLUMN_DIFFICULTY, question.getDifficultyaddCategory()c2);
Category c3 = new cv.putCategory(QuestionsTable.COLUMN_CATEGORY,"Cat question.getCategory()3");
db.insertaddCategory(QuestionsTable.TABLE_NAME, null, cvc3);
}
}
private void addNewCategories(List<Question> questionList) {
List<String> allCategories = getAllCategories();
List<String> allQuestionListCategories = new ArrayList<>();
for (Question question : questionList) {
allQuestionListCategories.add(question.getCategory());
}
Set<String> uniqueQuestionListCategories = new LinkedHashSet<>(allQuestionListCategories);
for (String category : uniqueQuestionListCategories) {
boolean newCategoryNecessary = true;
if (allCategories.contains(category))
newCategoryNecessary = false;
private ifvoid addCategory(newCategoryNecessaryCategory category) {
ContentValues cv = new ContentValues();
cv.put(CategoriesTable.COLUMN_NAME, category.getName());
db.insert(CategoriesTable.TABLE_NAME, null, cv);
}
}
}
public List<String> getAllCategories() {
//hier vermutlich DB öffnen falls null (wenn außerhalb aufrufen)
ArrayList<String> allCategories = new ArrayList<>();
public List<Category> getAllCategories() {
Cursor c = db.rawQuery("SELECT DISTINCTArrayList<Category> "allCategories += CategoriesTable.COLUMN_NAMEnew +ArrayList<>();
db = getReadableDatabase();
Cursor c "= db.rawQuery("SELECT * FROM " + CategoriesTable.TABLE_NAME, null);
if (c.moveToFirst()) {
do {
StringCategory category = new Category();
category.setId(c.getInt((c.getColumnIndex(CategoriesTable._ID))));
category.setName(c.getString(c.getColumnIndex(CategoriesTable.COLUMN_NAME)));
allCategories.add(category);
} while (c.moveToNext());
}
c.close();
return allCategories;
}
public ArrayList<Question> getAllQuestions() {
ArrayList<Question> questionList = new ArrayList<>();
db = getReadableDatabase();
Cursor c = db.rawQuery("SELECT * FROM " + QuestionsTable.TABLE_NAME, null);
if (c.moveToFirst()) {
do {
Question question = new Question();
question.setQuestion(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_QUESTION)));
question.setOption1(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION1)));
question.setOption2(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION2)));
question.setOption3(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION3)));
question.setAnswerNr(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER_NR)));
question.setDifficulty(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_DIFFICULTY)));
questionList.add(question);
} while (c.moveToNext());
}
c.close();
return questionList;
}
public ArrayList<Question> getQuestions(String difficulty) {
ArrayList<Question> questionList = new ArrayList<>();
db = getReadableDatabase();
String[] selectionArgs = new String[]{difficulty};
Cursor c = db.rawQuery("SELECT * FROM " + QuestionsTable.TABLE_NAME +
" WHERE " + QuestionsTable.COLUMN_DIFFICULTY + " = ?", selectionArgs);
if (c.moveToFirst()) {
do {
Question question = new Question();
question.setQuestion(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_QUESTION)));
question.setOption1(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION1)));
question.setOption2(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION2)));
question.setOption3(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION3)));
question.setAnswerNr(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER_NR)));
question.setDifficulty(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_DIFFICULTY)));
questionList.add(question);
} while (c.moveToNext());
}
c.close();
return questionList;
}
}
