· 7 years ago · Oct 02, 2018, 10:40 AM
1package galimski.igor.com.do_ing.sampledata;
2
3import android.content.ContentValues;
4import android.content.Context;
5import android.database.Cursor;
6import android.database.sqlite.SQLiteDatabase;
7import android.database.sqlite.SQLiteOpenHelper;
8import android.util.Log;
9
10import java.sql.Date;
11import java.util.ArrayList;
12
13import galimski.igor.com.do_ing.Task;
14import galimski.igor.com.do_ing.TaskPriority;
15
16
17public class DatabaseHelper extends SQLiteOpenHelper {
18
19 private static final String TAG = "SQLite";
20
21 private static final int DATABASE_VERSION = 1;
22
23 private static final String DATABASE_NAME = "TaskDatabase";
24
25 private static final String TABLE_TASK = "Task";
26
27 private static final String COLUMN_TASK_ID ="Id";
28 private static final String COLUMN_TASK_SHORT ="ShortDescription";
29 private static final String COLUMN_TASK_FULL = "FullDescription";
30
31 private static final String COLUMN_TASK_CREATIONDATE = "CreatedDate";
32 private static final String COLUMN_TASK_COMPLETIONDATE = "CompletionDate";
33
34 private static final String COLUMN_TASK_PRIORITY = "Priority";
35
36 private static final String COLUMN_TASK_SHOWN= "Shown";
37
38 public DatabaseHelper(Context context) {
39 super(context, DATABASE_NAME, null, DATABASE_VERSION);
40 }
41
42 @Override
43 public void onCreate(SQLiteDatabase db) {
44 Log.i(TAG, "MyDatabaseHelper.onCreate ... ");
45
46 // String script = "CREATE TABLE " + TABLE_NOTE + "("
47 // + COLUMN_NOTE_ID + " INTEGER PRIMARY KEY," + COLUMN_NOTE_TITLE + " TEXT,"
48 // + COLUMN_NOTE_CONTENT + " TEXT" + ")";
49 //String script = "CREATE TABLE `Task` ( `Id` INTEGER NOT NULL, `ShortDescription` TEXT NOT NULL, `FullDescription` TEXT, `CreatedDate` TEXT NOT NULL, `CompletionDate` TEXT NOT NULL, `Priority` TEXT NOT NULL, PRIMARY KEY(`Id`) )";
50
51 String script = "CREATE TABLE " + TABLE_TASK + "("
52 + COLUMN_TASK_ID + " INTEGER PRIMARY KEY,"
53 + COLUMN_TASK_SHORT + " TEXT,"
54 + COLUMN_TASK_CREATIONDATE + " TEXT,"
55 + COLUMN_TASK_COMPLETIONDATE + " TEXT,"
56 + COLUMN_TASK_PRIORITY + " TEXT,"
57 + COLUMN_TASK_SHOWN + " TEXT" + ")";
58
59 db.execSQL(script);
60 }
61
62
63 @Override
64 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
65 Log.i(TAG, "MyDatabaseHelper.onUpgrade ... ");
66
67 if (oldVersion != newVersion) {
68 db.execSQL("DROP TABLE IF EXISTS " + TABLE_TASK);
69 onCreate(db);
70 }
71
72 }
73
74 // Called when the database connection is being configured.
75 // Configure database settings for things like foreign key support, write-ahead logging, etc.
76 @Override
77 public void onConfigure(SQLiteDatabase db) {
78 super.onConfigure(db);
79 db.setForeignKeyConstraintsEnabled(true);
80 }
81
82 public void AddTask(Task task) {
83 Log.i(TAG, "MyDatabaseHelper.addTask ... ");
84
85 SQLiteDatabase db = this.getWritableDatabase();
86
87 ContentValues values = new ContentValues();
88
89 values.put(COLUMN_TASK_SHORT, task.GetShortDescription());
90 values.put(COLUMN_TASK_FULL, task.GetFullDescription());
91
92 values.put(COLUMN_TASK_CREATIONDATE, task.GetCreatedDate().toString());
93 values.put(COLUMN_TASK_COMPLETIONDATE, task.GetCompletionDate().toString());
94
95 values.put(COLUMN_TASK_PRIORITY, task.GetCompletionDate().toString());
96
97 db.insert(TABLE_TASK, null, values);
98
99 db.close();
100 }
101
102
103 public Task GetTask(int id) {
104 Log.i(TAG, "MyDatabaseHelper.getTask ... " + id);
105
106 SQLiteDatabase db = this.getReadableDatabase();
107
108 Cursor cursor = db.query(TABLE_TASK,
109 new String[] { COLUMN_TASK_ID, COLUMN_TASK_SHORT, COLUMN_TASK_FULL }, COLUMN_TASK_ID + "=?",
110 new String[] { String.valueOf(id) }, null, null, null, null);
111 if (cursor != null)
112 cursor.moveToFirst();
113
114 Task task = new Task();
115 task.SetShortDescription(cursor.getString(1));
116 task.SetFullDescription(cursor.getString(2));
117
118 task.SetCreatedDate(Date.valueOf(cursor.getString(3)));
119 task.SetCompletionDate(Date.valueOf(cursor.getString(4)));
120
121 task.SetTaskPriority(TaskPriority.valueOf(cursor.getString(5)));
122
123 task.SetNotificationShown(Boolean.parseBoolean(cursor.getString(6)));
124
125 cursor.close();
126
127 return task;
128 }
129
130
131 public ArrayList<Task> GetAllTasks() {
132 Log.i(TAG, "MyDatabaseHelper.getAllTasks ... " );
133
134 ArrayList<Task> taskList = new ArrayList<Task>();
135
136 String selectQuery = "SELECT * FROM " + TABLE_TASK;
137
138 SQLiteDatabase db = this.getWritableDatabase();
139
140 Cursor cursor = db.rawQuery(selectQuery, null);
141 if (cursor.moveToFirst()) {
142 do {
143 Task task = new Task();
144 task.SetShortDescription(cursor.getString(1));
145 task.SetFullDescription(cursor.getString(2));
146
147 task.SetCreatedDate(Date.valueOf(cursor.getString(3)));
148 task.SetCompletionDate(Date.valueOf(cursor.getString(4)));
149
150 task.SetTaskPriority(TaskPriority.valueOf(cursor.getString(5)));
151
152 task.SetNotificationShown(Boolean.parseBoolean(cursor.getString(6)));
153
154 taskList.add(task);
155 } while (cursor.moveToNext());
156 }
157
158 cursor.close();
159
160 return taskList;
161 }
162
163 /*public int updateNote(Note note) {
164 Log.i(TAG, "MyDatabaseHelper.updateNote ... " + note.getNoteTitle());
165
166 SQLiteDatabase db = this.getWritableDatabase();
167
168 ContentValues values = new ContentValues();
169 values.put(COLUMN_NOTE_TITLE, note.getNoteTitle());
170 values.put(COLUMN_NOTE_CONTENT, note.getNoteContent());
171
172 // updating row
173 return db.update(TABLE_NOTE, values, COLUMN_NOTE_ID + " = ?",
174 new String[]{String.valueOf(note.getNoteId())});
175 }*/
176
177 public void DeleteTask(Task task) {
178 Log.i(TAG, "MyDatabaseHelper.deleteTask ... ");
179
180 SQLiteDatabase db = this.getWritableDatabase();
181 db.delete(TABLE_TASK, COLUMN_TASK_ID + " = ?", new String[] { String.valueOf(task.GetId()) });
182 db.close();
183 }
184}