Sqlite table user function
I am trying to make an application with user login. I have created two tables user_reg and user_info. The first one is empty with student id, email, password columns. The second table has three columns username, student id, course each having 10 values inserted already by me. I am trying to write a function so that the values are inserted into first table only when the the entered student id matches with any one of the student ids from the second table. I used raw query in cursor for selecting student id from second table. And a second cursor for getting the user inserted student id. And then if loop to see if the result matches. I think its wrong. What's the correct way to do it?
This is my code for table creation and insert values. I know its wrong, i should probably use a loop for querying user info table. Any help in how to do it properly would be appreciated.
public static final String SQL_TABLE_USERS = " CREATE TABLE " + TABLE_USERS
+ " ( "
+ KEY_ID + " INTEGER PRIMARY KEY, "
+ KEY_EMAIL + " TEXT, "
+ KEY_SID + " INTEGER, "
+ KEY_PASSWORD + " TEXT"
+ " ) ";
//SQL for creating user info table
public static final String SQL_TABLE_USERINFO = " CREATE TABLE " + TABLE_USERINFO
+ " ( "
+ KEY_ID + "INTEGER PRIMARY KEY, "
+ KEY_SID + " INTEGER, "
+ KEY_NAME + "TEXT, "
+ KEY_COURSE + " TEXT "
+ " ) ";
public SqliteHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
//Create Table when oncreate gets called
sqLiteDatabase.execSQL(SQL_TABLE_USERS);
sqLiteDatabase.execSQL(SQL_TABLE_USERINFO);
sqLiteDatabase.execSQL("INSERT INTO TABLE_USERINFO VALUES('01','45207160010','Mary James','TYBSCIT')");
sqLiteDatabase.execSQL("INSERT INTO TABLE_USERINFO VALUES('02','45207160020','Amelia John','FYBCOM')"); ```
And this is my function for matching student id:-
public boolean isSIDmatches(String sid) {
SQLiteDatabase db = this.getReadableDatabase();
String sidmatch = "SELECT KEY_SID FROM TABLE_USERINFO";
Cursor cursor = db.rawQuery(sidmatch, new String[]{""});
Cursor cursor1 = db.query(TABLE_USERS,// Selecting Table
new String[]{KEY_ID, KEY_EMAIL, KEY_SID, KEY_PASSWORD},//Selecting columns want to query
KEY_SID + "=?",
new String[]{sid},//Where clause
null, null, null);
if (cursor != null && cursor.moveToFirst()&& cursor.getCount()>0 && cursor==cursor1) {
return true;
}
else {
return false;
}
}
One way could be to incorporate a foreign key (not that you will need to turn foreign key handling on, as it is off by default).
Basically a foreign key introduces a constraint that says that a child can only be added if the value being applied to the child exists in the parent.
Child and parent being a table/column (or columns) combination.
As you are saying the value in the user_info table must exist in the user_reg table then user_reg will be the parent and user_info will be the child in both the column name is studentid.
As such you could define the foreign key using :-
CREATE TABLE IF NOT EXISTS user_info (studentid INTEGER PRIMARY KEY REFERENCES user_reg(studentid), email TEXT, password TEXT);
- Note it is assumed that a student will only have a single studentid
However, as a foreign key has to point to a unique row in the parent, this would restrict the user_reg table to having only 1 entry per student, which is probably undesirable.
This highlights an underlying issue, you are basically looking at the schema/structure in the wrong way in that you may be concentrating on viewing the structure from the point of view of the course.
It is suggested that the singular aspects of a user, their name, their id, their email etc belongs in the user_info table, that having a single row per student and that the user_reg table (registration in a course) be consider as the course or courses that a student is registered in and as such that a single student may be registered many times (in separate courses) and thus that this should in fact be the child and that the user_info should be the parent.
As such perhaps you should be using :-
CREATE TABLE IF NOT EXISTS user_info (studentid INTEGER PRIMARY KEY, email TEXT, password TEXT, username TEXT);
CREATE TABLE IF NOT EXISTS user_reg (studentid INTEGER REFERENCES user_info, course TEXT);
You would then initially add a student to the user_info table and then add the student to the course/courses e.g. :-
INSERT INTO user_info VALUES
(1,'Fred@email.mail','fredismypassword','UserFred'),
(2,'Mary@email.mail','maryismypassword','UserMary'),
(3,'Sue@email.mail','sureismypassword','UserSue')
;
INSERT INTO user_reg VALUES
(1,'English'),(1,'Woodwork'),(1,'Chemistry'),
(2,'Geography'),
(3,'Chemistry')
;
- So Fred is registered to 3 courses, Mary and Sue to one each
As an example a query could be :-
SELECT * FROM user_reg JOIN user_info ON user_reg.studentid = user_info.studentid ORDER BY course;
This would result in :-
Attempting to add a non-existent student e.g. using :-
INSERT INTO user_reg VALUES (10,'English') -- Oooops no such student ID
Would result in
INSERT INTO user_reg VALUES (10,'English') -- Oooops no such student ID
> FOREIGN KEY constraint failed
Example Using Android
Putting all of the together (replicating the above on Android) then the code could be
StudentDBHelper.java (the Database Helper)
public class StudentDBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "studentdb";
public static final int DBVERSION = 1;
public static final String TBl_USERINFO = "user_info";
public static final String TBL_USERREG = "user_reg";
public static final String COL_USERINFO_STUDENTID = "student_id";
public static final String COL_USERINFO_USERNAME = "username";
public static final String COL_USERINFO_EMAIL = "email";
public static final String COL_USERINFO_PASSWORD = "password";
public static final String COL_USERREG_STUDENT_ID = "student_id";
public static final String COL_USERREG_COURSE = "course";
private final String crtUserInfo = "CREATE TABLE IF NOT EXISTS " + TBl_USERINFO + "(" +
COL_USERINFO_STUDENTID + " INTEGER PRIMARY KEY, " +
COL_USERINFO_USERNAME + " TEXT, " +
COL_USERINFO_EMAIL + " TEXT," +
COL_USERINFO_PASSWORD + " TEXT" +
")";
private final String drpUerInfo = "DROP TABLE IF EXISTS " + TBl_USERINFO;
private final String crtUserReg = "CREATE TABLE IF NOT EXISTS " + TBL_USERREG + "(" +
COL_USERREG_STUDENT_ID + " INTEGER REFERENCES " + TBl_USERINFO + "(" + COL_USERINFO_STUDENTID + ")," +
COL_USERREG_COURSE + " TEXT " +
")";
private final String drpUserReg = " DROP TABLE If EXISTS " + TBL_USERREG;
SQLiteDatabase mDB;
public StudentDBHelper(Context context) {
super(context, DBNAME, null, DBVERSION);
mDB = this.getWritableDatabase(); //<<<<<<<<<< will force create when constructing if DB doesn't exist
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(crtUserInfo);
db.execSQL(crtUserReg);
}
@Override
public void onConfigure(SQLiteDatabase db) {
super.onConfigure(db);
db.setForeignKeyConstraintsEnabled(true); //<<<<<<<<<< TURN ON FOREIGN KEY HANDLING
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(drpUserReg); //<<<<<<<<<< must be done before UserInfo
db.execSQL(drpUerInfo);
}
public long addUserInfo(String userName, String email, String password) {
ContentValues cv = new ContentValues();
cv.put(COL_USERINFO_USERNAME,userName);
cv.put(COL_USERINFO_EMAIL,email);
cv.put(COL_USERINFO_PASSWORD,password);
return mDB.insert(TBl_USERINFO,null,cv);
}
public long addUserReg(long studentId, String course) {
ContentValues cv = new ContentValues();
cv.put(COL_USERREG_STUDENT_ID,studentId);
cv.put(COL_USERREG_COURSE,course);
return mDB.insert(TBL_USERREG,null,cv);
}
public void logStudentsInCourses() {
String tbl = TBL_USERREG +
" JOIN " + TBl_USERINFO + " ON " +
TBl_USERINFO + "." + COL_USERINFO_STUDENTID +
" = " +
TBL_USERREG + "." + COL_USERREG_STUDENT_ID;
Cursor csr = mDB.query(tbl,null,null,null,null,null,COL_USERREG_COURSE);
while (csr.moveToNext()) {
Log.d(
"DBINFO",
"Row " + String.valueOf(csr.getPosition() + 1) +
"\n\t Student UserName = " + csr.getString(csr.getColumnIndex(COL_USERINFO_USERNAME)) +
"\n\tStudent Email = " + csr.getString(csr.getColumnIndex(COL_USERINFO_EMAIL)) +
"\n\tStudent password = " + csr.getString(csr.getColumnIndex(COL_USERINFO_PASSWORD)) +
"\n\tEnrolled in Course " + csr.getString(csr.getColumnIndex(COL_USERREG_COURSE))
);
}
}
}
##MainActivity.java (an invoking activity)
public class MainActivity extends AppCompatActivity {
StudentDBHelper mDBHlpr;
Context mContext;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mContext = this;
mDBHlpr = new StudentDBHelper(this);
mDBHlpr.addUserInfo("Fred","fred@email.com","fredpassword"); //<<<<<<<<<< will return 1 as this will be the generated value (first run only)
mDBHlpr.addUserInfo("Mary","mary@email.com","marypassword");
mDBHlpr.addUserInfo("Sue","sue@email.com","suepassword");
mDBHlpr.addUserReg(1,"English");
mDBHlpr.addUserReg(1,"Wooodwork");
mDBHlpr.addUserReg(1,"Chemistry");
mDBHlpr.addUserReg(2,"Geography");
mDBHlpr.addUserReg(3,"Chemistry");
mDBHlpr.addUserReg(10,"Chemistry"); //<<<<<<<<< Ooops won't insert return will be -1
mDBHlpr.logStudentsInCourses();
}
}
- Note the above is only designed to be run once
Result
The above, when run the first time will output the following to to log :-
2019-02-03 13:57:09.829 15640-15640/so.cdfa E/SQLiteDatabase: Error inserting student_id=10 course=Chemistry
android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)
at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:796)
at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1564)
at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1433)
at so.cdfa.StudentDBHelper.addUserReg(StudentDBHelper.java:80)
at so.cdfa.MainActivity.onCreate(MainActivity.java:37)
at android.app.Activity.performCreate(Activity.java:7136)
at android.app.Activity.performCreate(Activity.java:7127)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1271)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2893)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3048)
at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108)
at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1808)
at android.os.Handler.dispatchMessage(Handler.java:106)
at android.os.Looper.loop(Looper.java:193)
at android.app.ActivityThread.main(ActivityThread.java:6669)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
2019-02-03 13:57:09.831 15640-15640/so.cdfa D/DBINFO: Row 1
Student UserName = Fred
Student Email = fred@email.com
Student password = fredpassword
Enrolled in Course Chemistry
2019-02-03 13:57:09.831 15640-15640/so.cdfa D/DBINFO: Row 2
Student UserName = Sue
Student Email = sue@email.com
Student password = suepassword
Enrolled in Course Chemistry
2019-02-03 13:57:09.831 15640-15640/so.cdfa D/DBINFO: Row 3
Student UserName = Fred
Student Email = fred@email.com
Student password = fredpassword
Enrolled in Course English
2019-02-03 13:57:09.831 15640-15640/so.cdfa D/DBINFO: Row 4
Student UserName = Mary
Student Email = mary@email.com
Student password = marypassword
Enrolled in Course Geography
2019-02-03 13:57:09.831 15640-15640/so.cdfa D/DBINFO: Row 5
Student UserName = Fred
Student Email = fred@email.com
Student password = fredpassword
Enrolled in Course Wooodwork
- Note the exception is not a run time/fatal exception and does not stop processing, it is simply reporting the failure to insert due to the foreign key conflict.