/
DatabaseHelper.java
197 lines (160 loc) · 5.94 KB
/
DatabaseHelper.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
package com.lsiegert;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHelper extends SQLiteOpenHelper{
//The Android's default system path of your application database.
private static String DB_PATH = "/data/data/com.lsiegert/databases/";
private static String DB_NAME = "NUHockey.sqlite";
public SQLiteDatabase myDb;
private final Context myContext;
/**
* Constructor
* Takes and keeps a reference of the passed context in order to access to the application assets and resources.
* @param context
*/
public DatabaseHelper(Context context) {
super(context, DB_NAME, null, R.string.databaseVersion);
this.myContext = context;
}
/**
* Creates a empty database on the system and rewrites it with your own database.
* */
public void createDatabase() throws IOException{
boolean dbExists = checkDatabase();
SQLiteDatabase db_Read = null;
if(false){
//do nothing - database already exists
}else{
db_Read = this.getReadableDatabase();
db_Read.close();
try {
copyDatabase();
} catch (IOException e) {
throw new Error("Error copying database");
}
}
}
/**
* Check if the database already exists to avoid re-copying the file each time you open the application.
* @return true if it exists, false if it doesn't
*/
private boolean checkDatabase(){
SQLiteDatabase checkDB = null;
try{
String myPath = DB_PATH + DB_NAME;
checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
} catch(SQLiteException e){
//database does't exist yet.
}
if(checkDB != null){
checkDB.close();
}
return checkDB != null ? true : false;
}
/**
* Copies your database from your local assets-folder to the just created empty database in the
* system folder, from where it can be accessed and handled.
* */
private void copyDatabase() throws IOException{
InputStream myInput = myContext.getAssets().open(DB_NAME);
String outFileName = DB_PATH + DB_NAME;
OutputStream myOutput = new FileOutputStream(outFileName);
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer))>0){
myOutput.write(buffer, 0, length);
}
myOutput.flush();
myOutput.close();
myInput.close();
}
public void openDatabase() throws SQLException{
String myPath = DB_PATH + DB_NAME;
myDb = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
}
@Override
public synchronized void close() {
if(myDb != null)
myDb.close();
super.close();
}
@Override
public void onCreate(SQLiteDatabase db) {
checkDatabase();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
onCreate(db);
}
public Cursor getAllPlayers() {
return myDb.query("Players", null, null, null, null, null, "number");
}
public Cursor getAllGames() {
return myDb.query("Games", null, null, null, null, null, "date");
}
public Cursor getAllSeasons() {
String query = "select distinct season from Games order by season desc";
return myDb.rawQuery(query, null);
}
// location should be either home, away, or neutral
public Cursor getGamesByLocation(String location) {
return myDb.query("Games", null, "location = ?", new String[]{location}, null, null, "date");
}
// Get all games for the given season
public Cursor getGamesBySeason(String season) {
return myDb.query("Games", null, "season = ?", new String[]{season}, null, null, "date");
}
// W-L-T record across all games the user has attended
public String getRecord() {
String wins = "select _id from Games where nuscore > oppscore and attended=1";
String losses = "select _id from Games where nuscore < oppscore and attended=1";
String ties = "select _id from Games where nuscore = oppscore and attended=1";
int w = myDb.rawQuery(wins, null).getCount();
int l = myDb.rawQuery(losses, null).getCount();
int t = myDb.rawQuery(ties, null).getCount();
return w + "-" + l + "-" + t;
}
// W-L-T record across games the user has attended, by location
public String getRecordByLocation(String location){
String wins = "select _id from Games where nuscore > oppscore and attended=1 and location=?";
String losses = "select _id from Games where nuscore < oppscore and attended=1 and location=?";
String ties = "select _id from Games where nuscore = oppscore and attended=1 and location=?";
String[] args = new String[]{location};
int w = myDb.rawQuery(wins, args).getCount();
int l = myDb.rawQuery(losses, args).getCount();
int t = myDb.rawQuery(ties, args).getCount();
return w + "-" + l + "-" + t;
}
public int getNumOfTeams() {
String query = "select distinct opponent from Games where attended=1";
return myDb.rawQuery(query, null).getCount();
}
public int getNumOfGoals() {
String query = "select nuscore from Games where attended=1";
Cursor allGoals = myDb.rawQuery(query, null);
allGoals.moveToFirst();
int totalGoals = 0;
while (!allGoals.isAfterLast()) {
totalGoals = totalGoals + allGoals.getInt(0);
allGoals.moveToNext();
}
return totalGoals;
}
public int getNumOfShutouts() {
String query = "select _id from Games where attended=1 and oppscore=0";
return myDb.rawQuery(query, null).getCount();
}
public int getNumOfWinsAtAgganis() {
String query ="select _id from Games where attended=1 and " +
"nuscore>oppscore and opponent='boston university' and location='away'";
return myDb.rawQuery(query, null).getCount();
}
}