Dominic Xavier
Reputation: 1
How to use triggers to create table dynamically
I like to create a table dynamically when a user registers to create account, So I have tried it with triggers, I don't know weather I can create table using triggers dynamically. I am absolutely new to android. It shows syntax error.
I have generated a database for entering in my data, when multiple accounts logged in I like to create table separately for every user i.e seperate table which contains data alone not the username and password.So I have created a method "trigger_table" in which I have created trigger and I have called it in "register" method and I have called register method in the button b1 where b1 is the register button. When I click on this button a table should be automatically created
public class sql{
public Boolean register(String username, String Password) {
SQLiteDatabase s = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("username", username);
values.put("Password", Password);
long row = s.insert("log", null, values);
trigger_table(username);//trigger method is called
if (row == -1)
return false;
else
return true;
}
}
public Boolean trigger_table(String t) {
SQLiteDatabase db = this.getWritableDatabase();
db.execSQL("CREATE TRIGGER aft_insert AFTER INSERT ON log AS BEGIN
CREATE TABLE"+t+"(username text ,Date date ,description text ,Amount
int)");
return true;// I have created method trigger table
}
b1.setOnClickListener((v) -> {
String s1 = t1.getText().toString();
String s2 = t2.getText().toString();
String s3 = t3.getText().toString();
try {
sql s = new sql(this);//this is class name which
Boolean bo ;
if(s2.equals(s3))
{
bo= s.register(s1,s2);//called register method
if(bo == true) {
b.setMessage("Account created")
.setPositiveButton("Ok",null);
AlertDialog al = b.create();
al.show();
}
}
else{
b.setMessage("Account does not created")
.setPositiveButton("Ok",null);
AlertDialog al = b.create();
al.show();
}
}
catch(Exception e) {
b.setMessage(e.toString())
.setPositiveButton("Ok", null);
AlertDialog al = b.create();
al.show();
t1.setText("");
t2.setText("");
}
});
Upvotes: 0
Views: 131
Answers (1)
MikeT
Reputation: 57043
In short you cannot use CREATE TABLE within a TRIGGER. TRIGGERS are limited to performing UPDATE, INSERT, DELETE and SELECT operations within the BEGIN END section/clause (i.e. the actions that can be taken/done). SQL As Understood By SQLite - CREATE TRIGGER
Additional re comment :-
The main idea is when a user gives username and password on register form it gets stored on a table called "log" and when a data i.e username is inserted, a new table must be created for that user.Is there is any possible ways of doing it?
This couldn't be done directly using SQL, there are numerous ways it could be done programatically. However, it very much goes against typical/recommend use as by having numerous tables with the same schema has many disadvantages when a single column relating to the current user would suffice for a single table for other data.
Each table has a least a 4k overhead just for the pages specific to that table. There are a greater number of tables in the schema (the sqlite_master table) thus increased processing requirements when accessing any identifier (name) as some examples.
Example
The following is a simple example of how you could do the above :-
The Database Helper (DBHelper.java)
:-
public class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "myuserdb";
public static final int DBVERSION = 1;
public static final String TBL_LOG = "log";
public static final String COL_LOG_ID = BaseColumns._ID;
public static final String COL_LOG_USERNAME = "user_name";
public static final String COL_LOG_PASSWORD = "password";
public static final String COL_USER_ID = BaseColumns._ID;
public static final String COL_USER_DATA = "user_data";
SQLiteDatabase mDB;
public DBHelper(Context context) {
super(context, DBNAME, null, DBVERSION);
mDB = this.getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
String crt_log_table = "CREATE TABLE IF NOT EXISTS " + TBL_LOG + "(" +
COL_LOG_ID + " INTEGER PRIMARY KEY, " +
COL_LOG_USERNAME + " TEXT UNIQUE, " +
COL_LOG_PASSWORD + " TEXT" +
")";
db.execSQL(crt_log_table);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
public long addLog(String user, String password) {
ContentValues cv = new ContentValues();
cv.put(COL_LOG_USERNAME,user);
cv.put(COL_LOG_PASSWORD,password);
long userid = mDB.insert(TBL_LOG,null,cv);
if (userid > 0) {
createUserSpecificTable(user);
}
return userid;
}
public boolean login(String user, String password) {
boolean rv = false;
String whereclause = COL_LOG_USERNAME + "=? AND " + COL_LOG_PASSWORD + "=?";
String[] whereargs = new String[]{user,password};
Cursor csr = mDB.query(TBL_LOG,null,whereclause,whereargs,null,null,null);
if (csr.getCount() > 0) {
rv = true;
}
csr.close();
return rv;
}
private void createUserSpecificTable(String user) {
String crt_userSpecific_table = "CREATE TABLE IF NOT EXISTS " + user + "(" +
COL_USER_ID + " INTEGER PRIARY KEY," +
COL_USER_DATA + " TEXT " +
")";
mDB.execSQL(crt_userSpecific_table);
}
public void addUserData(String user, String data) {
ContentValues cv = new ContentValues();
cv.put(COL_USER_DATA,data);
// User specific
mDB.insert(user,null,cv);
}
public Cursor getUserData(String user) {
return mDB.query(user,null,null,null,null,null,null);
}
}
- method addLog registers a new user and build the user specific table.
- method login allows a user to login returning true if the login was successful, else false.
- method adduserData adds some user specific data based upon the user passed.
- method getUserData returns the user specific data for a user as a Cursor.
- Note the above requires that a username adheres to unenclosed (identifier) table naming rules.
Testing Activity
:-
The following creates a number of logins, mocks some user sessions, writing the user's data to the log and finally then writes the schema (contents of sqlite_master to the log, to show that the user specific tables have been created ) :-
public class MainActivity extends AppCompatActivity {
DBHelper mDBHlpr;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDBHlpr = new DBHelper(this);
mDBHlpr.addLog("Fred","1234");
mDBHlpr.addLog("Sue","5678");
mDBHlpr.addLog("Mary","1111");
mDBHlpr.addLog("Tom","0000");
doMockUserSession("MRNOBODY","HACKIT");
doMockUserSession("Fred","1234");
doMockUserSession("Sue","666");
doMockUserSession("Sue","5678");
doMockUserSession("Mary","1111");
doMockUserSession("Tom","0000");
//Get all Items from the schema and write to the log
DatabaseUtils.dumpCursor(mDBHlpr.getWritableDatabase().query("sqlite_master",null,null,null,null,null,null));
}
private void doMockUserSession(String user, String password) {
if (mDBHlpr.login(user,password)) {
mDBHlpr.addUserData(user,"MY DATA " + String.valueOf(System.currentTimeMillis()));
Cursor csr = mDBHlpr.getUserData(user);
DatabaseUtils.dumpCursor(csr);
csr.close();
} else {
Log.d("LOGINFAIL","Login failed for user " + user);
}
}
}
Resultant output
The above (after a few runs) results in :-
2019-02-13 10:07:06.170 D/LOGINFAIL: Login failed for user MRNOBODY
2019-02-13 10:07:06.173 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@5690395
2019-02-13 10:07:06.175 I/System.out: 0 {
2019-02-13 10:07:06.175 I/System.out: _id=null
2019-02-13 10:07:06.175 I/System.out: user_data=MY DATA 1550011948580
2019-02-13 10:07:06.175 I/System.out: }
2019-02-13 10:07:06.175 I/System.out: 1 {
2019-02-13 10:07:06.176 I/System.out: _id=null
2019-02-13 10:07:06.176 I/System.out: user_data=MY DATA 1550012073536
2019-02-13 10:07:06.176 I/System.out: }
2019-02-13 10:07:06.176 I/System.out: 2 {
2019-02-13 10:07:06.176 I/System.out: _id=null
2019-02-13 10:07:06.176 I/System.out: user_data=MY DATA 1550012826172
2019-02-13 10:07:06.176 I/System.out: }
2019-02-13 10:07:06.176 I/System.out: <<<<<
2019-02-13 10:07:06.178 D/LOGINFAIL: Login failed for user Sue
2019-02-13 10:07:06.179 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@70c1caa
2019-02-13 10:07:06.179 I/System.out: 0 {
2019-02-13 10:07:06.179 I/System.out: _id=null
2019-02-13 10:07:06.179 I/System.out: user_data=MY DATA 1550011948588
2019-02-13 10:07:06.179 I/System.out: }
2019-02-13 10:07:06.179 I/System.out: 1 {
2019-02-13 10:07:06.179 I/System.out: _id=null
2019-02-13 10:07:06.180 I/System.out: user_data=MY DATA 1550012073545
2019-02-13 10:07:06.180 I/System.out: }
2019-02-13 10:07:06.180 I/System.out: 2 {
2019-02-13 10:07:06.180 I/System.out: _id=null
2019-02-13 10:07:06.180 I/System.out: user_data=MY DATA 1550012826178
2019-02-13 10:07:06.181 I/System.out: }
2019-02-13 10:07:06.181 I/System.out: <<<<<
2019-02-13 10:07:06.182 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@660309b
2019-02-13 10:07:06.182 I/System.out: 0 {
2019-02-13 10:07:06.182 I/System.out: _id=null
2019-02-13 10:07:06.182 I/System.out: user_data=MY DATA 1550011948594
2019-02-13 10:07:06.182 I/System.out: }
2019-02-13 10:07:06.182 I/System.out: 1 {
2019-02-13 10:07:06.183 I/System.out: _id=null
2019-02-13 10:07:06.183 I/System.out: user_data=MY DATA 1550012073547
2019-02-13 10:07:06.183 I/System.out: }
2019-02-13 10:07:06.183 I/System.out: 2 {
2019-02-13 10:07:06.183 I/System.out: _id=null
2019-02-13 10:07:06.183 I/System.out: user_data=MY DATA 1550012826181
2019-02-13 10:07:06.183 I/System.out: }
2019-02-13 10:07:06.183 I/System.out: <<<<<
2019-02-13 10:07:06.186 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8a61c38
2019-02-13 10:07:06.186 I/System.out: 0 {
2019-02-13 10:07:06.186 I/System.out: _id=null
2019-02-13 10:07:06.186 I/System.out: user_data=MY DATA 1550011948596
2019-02-13 10:07:06.186 I/System.out: }
2019-02-13 10:07:06.186 I/System.out: 1 {
2019-02-13 10:07:06.186 I/System.out: _id=null
2019-02-13 10:07:06.186 I/System.out: user_data=MY DATA 1550012073550
2019-02-13 10:07:06.186 I/System.out: }
2019-02-13 10:07:06.187 I/System.out: 2 {
2019-02-13 10:07:06.187 I/System.out: _id=null
2019-02-13 10:07:06.187 I/System.out: user_data=MY DATA 1550012826185
2019-02-13 10:07:06.187 I/System.out: }
2019-02-13 10:07:06.187 I/System.out: <<<<<
2019-02-13 10:07:06.187 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@6a34411
2019-02-13 10:07:06.187 I/System.out: 0 {
2019-02-13 10:07:06.187 I/System.out: type=table
2019-02-13 10:07:06.187 I/System.out: name=android_metadata
2019-02-13 10:07:06.188 I/System.out: tbl_name=android_metadata
2019-02-13 10:07:06.188 I/System.out: rootpage=3
2019-02-13 10:07:06.188 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
2019-02-13 10:07:06.188 I/System.out: }
2019-02-13 10:07:06.188 I/System.out: 1 {
2019-02-13 10:07:06.188 I/System.out: type=table
2019-02-13 10:07:06.188 I/System.out: name=log
2019-02-13 10:07:06.188 I/System.out: tbl_name=log
2019-02-13 10:07:06.188 I/System.out: rootpage=4
2019-02-13 10:07:06.188 I/System.out: sql=CREATE TABLE log(_id INTEGER PRIMARY KEY, user_name TEXT UNIQUE, password TEXT)
2019-02-13 10:07:06.188 I/System.out: }
2019-02-13 10:07:06.188 I/System.out: 2 {
2019-02-13 10:07:06.188 I/System.out: type=index
2019-02-13 10:07:06.188 I/System.out: name=sqlite_autoindex_log_1
2019-02-13 10:07:06.188 I/System.out: tbl_name=log
2019-02-13 10:07:06.188 I/System.out: rootpage=5
2019-02-13 10:07:06.188 I/System.out: sql=null
2019-02-13 10:07:06.188 I/System.out: }
2019-02-13 10:07:06.189 I/System.out: 3 {
2019-02-13 10:07:06.189 I/System.out: type=table
2019-02-13 10:07:06.189 I/System.out: name=Fred
2019-02-13 10:07:06.189 I/System.out: tbl_name=Fred
2019-02-13 10:07:06.189 I/System.out: rootpage=6
2019-02-13 10:07:06.189 I/System.out: sql=CREATE TABLE Fred(_id INTEGER PRIARY KEY,user_data TEXT )
2019-02-13 10:07:06.189 I/System.out: }
2019-02-13 10:07:06.189 I/System.out: 4 {
2019-02-13 10:07:06.189 I/System.out: type=table
2019-02-13 10:07:06.189 I/System.out: name=Sue
2019-02-13 10:07:06.189 I/System.out: tbl_name=Sue
2019-02-13 10:07:06.189 I/System.out: rootpage=7
2019-02-13 10:07:06.189 I/System.out: sql=CREATE TABLE Sue(_id INTEGER PRIARY KEY,user_data TEXT )
2019-02-13 10:07:06.189 I/System.out: }
2019-02-13 10:07:06.189 I/System.out: 5 {
2019-02-13 10:07:06.189 I/System.out: type=table
2019-02-13 10:07:06.189 I/System.out: name=Mary
2019-02-13 10:07:06.189 I/System.out: tbl_name=Mary
2019-02-13 10:07:06.189 I/System.out: rootpage=8
2019-02-13 10:07:06.189 I/System.out: sql=CREATE TABLE Mary(_id INTEGER PRIARY KEY,user_data TEXT )
2019-02-13 10:07:06.189 I/System.out: }
2019-02-13 10:07:06.189 I/System.out: 6 {
2019-02-13 10:07:06.189 I/System.out: type=table
2019-02-13 10:07:06.190 I/System.out: name=Tom
2019-02-13 10:07:06.190 I/System.out: tbl_name=Tom
2019-02-13 10:07:06.190 I/System.out: rootpage=9
2019-02-13 10:07:06.190 I/System.out: sql=CREATE TABLE Tom(_id INTEGER PRIARY KEY,user_data TEXT )
2019-02-13 10:07:06.190 I/System.out: }
2019-02-13 10:07:06.190 I/System.out: <<<<<
Upvotes: 1
Related Questions
- How to use TRIGGER in Android SQLite
- Why does SQLite give Cannot create instead of trigger on table
- Sqlite: create trigger programmatically
- How Do I Create Tables Dynamically in SQLite in Android?
- Creating table dynamically (On Button Click) In Android
- Dynamically creating SQLite table with defined rows
- How do I dynamically add tables in android sqlite?
- Generate Create Table Query Dynamically For Android SQLite
- Adding tables dynamically to a database in Android
- Sqlite Dynamic trigger lifecycle