· 7 years ago · Dec 21, 2018, 01:40 PM
1package com.example.michta.sportslitpm;
2import android.content.ContentValues;
3import android.content.Context;
4import android.database.Cursor;
5import android.database.sqlite.SQLiteDatabase;
6import android.database.sqlite.SQLiteOpenHelper;
7import android.util.Log;
8import java.util.ArrayList;
9
10
11public class activitiesDB {
12
13
14
15 public static final String DB_NAME = "activity.db";
16 public static final int DB_VERSION = 1;
17
18 public static final String ACTIVITY_TABLE = "activity";
19
20 public static final String ACTIVITY_ID = "_id";
21 public static final int ACTIVITY_ID_COL = 0;
22
23 public static final String ACTIVITY_NAME = "activity_name";
24 public static final int ACTIVITY_NAME_COL = 1;
25
26 public static final String ACTIVITY_DATE = "activity_date";
27 public static final int ACTIVITY_DATE_COL = 2;
28
29 public static final String ACTIVITY_TIME = "activity_time";
30 public static final int ACTIVITY_TIME_COL = 3;
31
32 public static final String ACTIVITY_LOCATION = "activity_location";
33 public static final int ACTIVITY_LOCATION_COL = 4;
34
35 public static final String ACTIVITY_CAMPUS = "activity_campus";
36 public static final int ACTIVITY_CAMPUS_COL = 5;
37
38 public static final String ACTIVITY_DETAILS = "activity_details";
39 public static final int ACTIVITY_DETAILS_COL = 6;
40
41 // setup SQL queries
42 // CREATE and DROP TABLE statements
43 public static final String CREATE_ACTIVITY_TABLE =
44 "CREATE TABLE " + ACTIVITY_TABLE + " (" +
45 ACTIVITY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
46 ACTIVITY_NAME + " TEXT NOT NULL UNIQUE, " +
47 ACTIVITY_DATE + " TEXT NOT NULL, " +
48 ACTIVITY_TIME + " TEXT NOT NULL, " +
49 ACTIVITY_LOCATION + " TEXT NOT NULL, " +
50 ACTIVITY_CAMPUS + " TEXT NOT NULL, " +
51 ACTIVITY_DETAILS + " TEXT NOT NULL);";
52
53 public static final String DROP_ACTIVITY_TABLE = "DROP TABLE IF EXISTS " + ACTIVITY_TABLE;
54
55 // create inner DBHelper class that is a subclass of the SQLiteHelper class that
56
57 private static class DBHelper extends SQLiteOpenHelper {
58
59 public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
60 super(context, name, factory, version);
61 }
62
63 @Override
64 public void onCreate(SQLiteDatabase db) {
65 // create tables
66 db.execSQL(CREATE_ACTIVITY_TABLE);
67
68 // insert default lists
69 db.execSQL("INSERT INTO activity VALUES (1, 'Men’s J2 & J3 Soccer Training', '13th March', '5pm', 'Astro', 'Moylish', '')");
70 db.execSQL("INSERT INTO activity VALUES (2, 'Men’s Rugby Training', '14th January', '5:30pm', 'Rugby Pitch', 'Moylish', '')");
71 db.execSQL("INSERT INTO activity VALUES (3, 'Yoga', '31 December', '2pm', 'Sportshub', 'Moylish', '€5 per person')");
72 db.execSQL("INSERT INTO activity VALUES (4, 'Men’s Basketball v IT Tralee', '14th May', '8:30pm', 'Astro', 'Moylish', '')");
73 db.execSQL("INSERT INTO activity VALUES (5, 'Calisthenics', '21st April', '8:30pm', 'Astro', 'Moylish', 'Be in Dressing room at 8pm')");
74
75
76 }
77
78 @Override
79 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
80 Log.d("Activity", "Upgrading db from version "
81 + oldVersion + " to " + newVersion);
82
83 db.execSQL(activitiesDB.DROP_ACTIVITY_TABLE);
84 onCreate(db);
85 }
86 }
87
88 // database and database helper objects
89 private SQLiteDatabase db;
90 private DBHelper dbHelper;
91
92 public activitiesDB(Context context) {
93 dbHelper = new DBHelper(context, DB_NAME, null, DB_VERSION);
94 }
95
96 // private methods
97 private void openReadableDB() {
98 db = dbHelper.getReadableDatabase();
99 }
100
101 private void openWriteableDB() {
102 db = dbHelper.getWritableDatabase();
103 }
104
105 private void closeDB() {
106 if (db != null)
107 db.close();
108 }
109
110 public ArrayList<Activity> getActivities() {
111 ArrayList<Activity> activities = new ArrayList<Activity>();
112 openReadableDB();
113 String[] args = new String[]{"activity_name"};
114 Cursor cursor = db.query(ACTIVITY_TABLE,
115 null, null, null, null, null, null);
116 while (cursor.moveToNext()) {
117 Activity act = new Activity();
118 act.setID(cursor.getInt(ACTIVITY_ID_COL));
119 act.setName(cursor.getString(ACTIVITY_NAME_COL));
120 act.setDate(cursor.getString(ACTIVITY_DATE_COL));
121 act.setTime(cursor.getString(ACTIVITY_TIME_COL));
122 act.setLocation(cursor.getString(ACTIVITY_LOCATION_COL));
123 act.setCampus(cursor.getString(ACTIVITY_CAMPUS_COL));
124 act.setDetails(cursor.getString(ACTIVITY_DETAILS_COL));
125 activities.add(act);
126 }
127 if (cursor != null)
128 cursor.close();
129 closeDB();
130
131 return activities;
132 }
133
134 public long addActivity(String name, String time, String date, String location, String campus, String details) {
135 openWriteableDB();
136
137 ContentValues values = new ContentValues();
138 values.put(ACTIVITY_NAME, name);
139 values.put(ACTIVITY_TIME, time);
140 values.put(ACTIVITY_DATE, date);
141 values.put(ACTIVITY_LOCATION, location);
142 values.put(ACTIVITY_CAMPUS, campus);
143 values.put(ACTIVITY_DETAILS, details);
144
145 // BELOW RETURNS ID IF NEEDED
146 long id = db.insert(ACTIVITY_TABLE, null, values);
147
148
149 System.out.println("DEEBUG: "+id+ " ");
150 closeDB();
151
152 return id;
153 }
154
155 public long editActivity(long id, String name, String time, String date, String location, String campus, String details) {
156 openWriteableDB();
157 String where = "_id = ?";
158 ContentValues values = new ContentValues();
159 String[] whereArgs = new String[] {String.valueOf(id)};
160
161 values.put(ACTIVITY_NAME, name);
162 values.put(ACTIVITY_TIME, time);
163 values.put(ACTIVITY_DATE, date);
164 values.put(ACTIVITY_LOCATION, location);
165 values.put(ACTIVITY_CAMPUS, campus);
166 values.put(ACTIVITY_DETAILS, details);
167
168 // BELOW RETURNS ID IF NEEDED
169 long num = db.update(ACTIVITY_TABLE, values, where, whereArgs);
170
171
172 System.out.println("DEEBUG: "+id+ " ");
173 closeDB();
174
175 return num;
176 }
177
178
179}