· 7 years ago · Feb 12, 2019, 11:22 PM
1public class sql{
2 public Boolean register(String username, String Password) {
3 SQLiteDatabase s = this.getWritableDatabase();
4 ContentValues values = new ContentValues();
5 values.put("username", username);
6 values.put("Password", Password);
7
8 long row = s.insert("log", null, values);
9 trigger_table(username);//trigger method is called
10
11 if (row == -1)
12 return false;
13 else
14 return true;
15 }
16}
17
18 public Boolean trigger_table(String t) {
19 SQLiteDatabase db = this.getWritableDatabase();
20 db.execSQL("CREATE TRIGGER aft_insert AFTER INSERT ON log AS BEGIN
21 CREATE TABLE"+t+"(username text ,Date date ,description text ,Amount
22 int)");
23 return true;// I have created method trigger table
24 }
25
26
27 b1.setOnClickListener((v) -> {
28 String s1 = t1.getText().toString();
29 String s2 = t2.getText().toString();
30 String s3 = t3.getText().toString();
31
32 try {
33 sql s = new sql(this);//this is class name which
34 Boolean bo ;
35 if(s2.equals(s3))
36 {
37 bo= s.register(s1,s2);//called register method
38 if(bo == true) {
39 b.setMessage("Account created")
40 .setPositiveButton("Ok",null);
41 AlertDialog al = b.create();
42 al.show();
43 }
44 }
45 else{
46 b.setMessage("Account does not created")
47 .setPositiveButton("Ok",null);
48 AlertDialog al = b.create();
49 al.show();
50 }
51 }
52 catch(Exception e) {
53 b.setMessage(e.toString())
54 .setPositiveButton("Ok", null);
55 AlertDialog al = b.create();
56 al.show();
57 t1.setText("");
58 t2.setText("");
59 }
60 });
61
62public class DBHelper extends SQLiteOpenHelper {
63
64 public static final String DBNAME = "myuserdb";
65 public static final int DBVERSION = 1;
66 public static final String TBL_LOG = "log";
67 public static final String COL_LOG_ID = BaseColumns._ID;
68 public static final String COL_LOG_USERNAME = "user_name";
69 public static final String COL_LOG_PASSWORD = "password";
70
71 public static final String COL_USER_ID = BaseColumns._ID;
72 public static final String COL_USER_DATA = "user_data";
73
74
75 SQLiteDatabase mDB;
76 public DBHelper(Context context) {
77 super(context, DBNAME, null, DBVERSION);
78 mDB = this.getWritableDatabase();
79 }
80
81 @Override
82 public void onCreate(SQLiteDatabase db) {
83 String crt_log_table = "CREATE TABLE IF NOT EXISTS " + TBL_LOG + "(" +
84 COL_LOG_ID + " INTEGER PRIMARY KEY, " +
85 COL_LOG_USERNAME + " TEXT UNIQUE, " +
86 COL_LOG_PASSWORD + " TEXT" +
87 ")";
88 db.execSQL(crt_log_table);
89 }
90
91 @Override
92 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
93
94 }
95
96 public long addLog(String user, String password) {
97 ContentValues cv = new ContentValues();
98 cv.put(COL_LOG_USERNAME,user);
99 cv.put(COL_LOG_PASSWORD,password);
100 long userid = mDB.insert(TBL_LOG,null,cv);
101 if (userid > 0) {
102 createUserSpecificTable(user);
103 }
104 return userid;
105 }
106
107
108 public boolean login(String user, String password) {
109 boolean rv = false;
110 String whereclause = COL_LOG_USERNAME + "=? AND " + COL_LOG_PASSWORD + "=?";
111 String[] whereargs = new String[]{user,password};
112 Cursor csr = mDB.query(TBL_LOG,null,whereclause,whereargs,null,null,null);
113 if (csr.getCount() > 0) {
114 rv = true;
115 }
116 csr.close();
117 return rv;
118 }
119
120 private void createUserSpecificTable(String user) {
121 String crt_userSpecific_table = "CREATE TABLE IF NOT EXISTS " + user + "(" +
122 COL_USER_ID + " INTEGER PRIARY KEY," +
123 COL_USER_DATA + " TEXT " +
124 ")";
125 mDB.execSQL(crt_userSpecific_table);
126 }
127
128 public void addUserData(String user, String data) {
129 ContentValues cv = new ContentValues();
130 cv.put(COL_USER_DATA,data);
131 // User specific
132 mDB.insert(user,null,cv);
133 }
134
135 public Cursor getUserData(String user) {
136 return mDB.query(user,null,null,null,null,null,null);
137 }
138}
139
140public class MainActivity extends AppCompatActivity {
141
142 DBHelper mDBHlpr;
143
144 @Override
145 protected void onCreate(Bundle savedInstanceState) {
146 super.onCreate(savedInstanceState);
147 setContentView(R.layout.activity_main);
148 mDBHlpr = new DBHelper(this);
149
150 mDBHlpr.addLog("Fred","1234");
151 mDBHlpr.addLog("Sue","5678");
152 mDBHlpr.addLog("Mary","1111");
153 mDBHlpr.addLog("Tom","0000");
154
155 doMockUserSession("MRNOBODY","HACKIT");
156 doMockUserSession("Fred","1234");
157 doMockUserSession("Sue","666");
158 doMockUserSession("Sue","5678");
159 doMockUserSession("Mary","1111");
160 doMockUserSession("Tom","0000");
161
162 //Get all Items from the schema and write to the log
163 DatabaseUtils.dumpCursor(mDBHlpr.getWritableDatabase().query("sqlite_master",null,null,null,null,null,null));
164 }
165
166
167 private void doMockUserSession(String user, String password) {
168
169 if (mDBHlpr.login(user,password)) {
170 mDBHlpr.addUserData(user,"MY DATA " + String.valueOf(System.currentTimeMillis()));
171 Cursor csr = mDBHlpr.getUserData(user);
172 DatabaseUtils.dumpCursor(csr);
173 csr.close();
174 } else {
175 Log.d("LOGINFAIL","Login failed for user " + user);
176 }
177 }
178}
179
1802019-02-13 10:07:06.170 D/LOGINFAIL: Login failed for user MRNOBODY
1812019-02-13 10:07:06.173 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@5690395
1822019-02-13 10:07:06.175 I/System.out: 0 {
1832019-02-13 10:07:06.175 I/System.out: _id=null
1842019-02-13 10:07:06.175 I/System.out: user_data=MY DATA 1550011948580
1852019-02-13 10:07:06.175 I/System.out: }
1862019-02-13 10:07:06.175 I/System.out: 1 {
1872019-02-13 10:07:06.176 I/System.out: _id=null
1882019-02-13 10:07:06.176 I/System.out: user_data=MY DATA 1550012073536
1892019-02-13 10:07:06.176 I/System.out: }
1902019-02-13 10:07:06.176 I/System.out: 2 {
1912019-02-13 10:07:06.176 I/System.out: _id=null
1922019-02-13 10:07:06.176 I/System.out: user_data=MY DATA 1550012826172
1932019-02-13 10:07:06.176 I/System.out: }
1942019-02-13 10:07:06.176 I/System.out: <<<<<
1952019-02-13 10:07:06.178 D/LOGINFAIL: Login failed for user Sue
1962019-02-13 10:07:06.179 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@70c1caa
1972019-02-13 10:07:06.179 I/System.out: 0 {
1982019-02-13 10:07:06.179 I/System.out: _id=null
1992019-02-13 10:07:06.179 I/System.out: user_data=MY DATA 1550011948588
2002019-02-13 10:07:06.179 I/System.out: }
2012019-02-13 10:07:06.179 I/System.out: 1 {
2022019-02-13 10:07:06.179 I/System.out: _id=null
2032019-02-13 10:07:06.180 I/System.out: user_data=MY DATA 1550012073545
2042019-02-13 10:07:06.180 I/System.out: }
2052019-02-13 10:07:06.180 I/System.out: 2 {
2062019-02-13 10:07:06.180 I/System.out: _id=null
2072019-02-13 10:07:06.180 I/System.out: user_data=MY DATA 1550012826178
2082019-02-13 10:07:06.181 I/System.out: }
2092019-02-13 10:07:06.181 I/System.out: <<<<<
2102019-02-13 10:07:06.182 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@660309b
2112019-02-13 10:07:06.182 I/System.out: 0 {
2122019-02-13 10:07:06.182 I/System.out: _id=null
2132019-02-13 10:07:06.182 I/System.out: user_data=MY DATA 1550011948594
2142019-02-13 10:07:06.182 I/System.out: }
2152019-02-13 10:07:06.182 I/System.out: 1 {
2162019-02-13 10:07:06.183 I/System.out: _id=null
2172019-02-13 10:07:06.183 I/System.out: user_data=MY DATA 1550012073547
2182019-02-13 10:07:06.183 I/System.out: }
2192019-02-13 10:07:06.183 I/System.out: 2 {
2202019-02-13 10:07:06.183 I/System.out: _id=null
2212019-02-13 10:07:06.183 I/System.out: user_data=MY DATA 1550012826181
2222019-02-13 10:07:06.183 I/System.out: }
2232019-02-13 10:07:06.183 I/System.out: <<<<<
2242019-02-13 10:07:06.186 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8a61c38
2252019-02-13 10:07:06.186 I/System.out: 0 {
2262019-02-13 10:07:06.186 I/System.out: _id=null
2272019-02-13 10:07:06.186 I/System.out: user_data=MY DATA 1550011948596
2282019-02-13 10:07:06.186 I/System.out: }
2292019-02-13 10:07:06.186 I/System.out: 1 {
2302019-02-13 10:07:06.186 I/System.out: _id=null
2312019-02-13 10:07:06.186 I/System.out: user_data=MY DATA 1550012073550
2322019-02-13 10:07:06.186 I/System.out: }
2332019-02-13 10:07:06.187 I/System.out: 2 {
2342019-02-13 10:07:06.187 I/System.out: _id=null
2352019-02-13 10:07:06.187 I/System.out: user_data=MY DATA 1550012826185
2362019-02-13 10:07:06.187 I/System.out: }
2372019-02-13 10:07:06.187 I/System.out: <<<<<
2382019-02-13 10:07:06.187 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@6a34411
2392019-02-13 10:07:06.187 I/System.out: 0 {
2402019-02-13 10:07:06.187 I/System.out: type=table
2412019-02-13 10:07:06.187 I/System.out: name=android_metadata
2422019-02-13 10:07:06.188 I/System.out: tbl_name=android_metadata
2432019-02-13 10:07:06.188 I/System.out: rootpage=3
2442019-02-13 10:07:06.188 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
2452019-02-13 10:07:06.188 I/System.out: }
2462019-02-13 10:07:06.188 I/System.out: 1 {
2472019-02-13 10:07:06.188 I/System.out: type=table
2482019-02-13 10:07:06.188 I/System.out: name=log
2492019-02-13 10:07:06.188 I/System.out: tbl_name=log
2502019-02-13 10:07:06.188 I/System.out: rootpage=4
2512019-02-13 10:07:06.188 I/System.out: sql=CREATE TABLE log(_id INTEGER PRIMARY KEY, user_name TEXT UNIQUE, password TEXT)
2522019-02-13 10:07:06.188 I/System.out: }
2532019-02-13 10:07:06.188 I/System.out: 2 {
2542019-02-13 10:07:06.188 I/System.out: type=index
2552019-02-13 10:07:06.188 I/System.out: name=sqlite_autoindex_log_1
2562019-02-13 10:07:06.188 I/System.out: tbl_name=log
2572019-02-13 10:07:06.188 I/System.out: rootpage=5
2582019-02-13 10:07:06.188 I/System.out: sql=null
2592019-02-13 10:07:06.188 I/System.out: }
2602019-02-13 10:07:06.189 I/System.out: 3 {
2612019-02-13 10:07:06.189 I/System.out: type=table
2622019-02-13 10:07:06.189 I/System.out: name=Fred
2632019-02-13 10:07:06.189 I/System.out: tbl_name=Fred
2642019-02-13 10:07:06.189 I/System.out: rootpage=6
2652019-02-13 10:07:06.189 I/System.out: sql=CREATE TABLE Fred(_id INTEGER PRIARY KEY,user_data TEXT )
2662019-02-13 10:07:06.189 I/System.out: }
2672019-02-13 10:07:06.189 I/System.out: 4 {
2682019-02-13 10:07:06.189 I/System.out: type=table
2692019-02-13 10:07:06.189 I/System.out: name=Sue
2702019-02-13 10:07:06.189 I/System.out: tbl_name=Sue
2712019-02-13 10:07:06.189 I/System.out: rootpage=7
2722019-02-13 10:07:06.189 I/System.out: sql=CREATE TABLE Sue(_id INTEGER PRIARY KEY,user_data TEXT )
2732019-02-13 10:07:06.189 I/System.out: }
2742019-02-13 10:07:06.189 I/System.out: 5 {
2752019-02-13 10:07:06.189 I/System.out: type=table
2762019-02-13 10:07:06.189 I/System.out: name=Mary
2772019-02-13 10:07:06.189 I/System.out: tbl_name=Mary
2782019-02-13 10:07:06.189 I/System.out: rootpage=8
2792019-02-13 10:07:06.189 I/System.out: sql=CREATE TABLE Mary(_id INTEGER PRIARY KEY,user_data TEXT )
2802019-02-13 10:07:06.189 I/System.out: }
2812019-02-13 10:07:06.189 I/System.out: 6 {
2822019-02-13 10:07:06.189 I/System.out: type=table
2832019-02-13 10:07:06.190 I/System.out: name=Tom
2842019-02-13 10:07:06.190 I/System.out: tbl_name=Tom
2852019-02-13 10:07:06.190 I/System.out: rootpage=9
2862019-02-13 10:07:06.190 I/System.out: sql=CREATE TABLE Tom(_id INTEGER PRIARY KEY,user_data TEXT )
2872019-02-13 10:07:06.190 I/System.out: }
2882019-02-13 10:07:06.190 I/System.out: <<<<<