· 7 years ago · Mar 02, 2019, 04:42 PM
1package DatabaseManager;
2
3/*
4 * Created by Marco-Edoardo Palma.
5 */
6import Commons.*;
7
8import java.text.NumberFormat;
9import java.util.Locale;
10
11/*
12 * This class is a container for all of the queries that can be made to the database.
13 */
14public class QueryComposer
15{
16
17 private static String CREATE_TABLE_USERS
18 = "CREATE TABLE IF NOT EXISTS USERS(\n"
19 + "id VARCHAR(255) PRIMARY KEY NOT NULL,\n"
20 + "gender VARCHAR(55),\n"
21 + "age VARCHAR(55),\n"
22 + "income VARCHAR(55)\n"
23 + ");";
24
25 private static String CREATE_TABLE_IMPRESSION_LOGS
26 = "CREATE TABLE IF NOT EXISTS IMPRESSION_LOGS(\n"
27 + "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n"
28 + "userId VARCHAR(255),\n"
29 + "date DATE,\n"
30 + "context VARCHAR(255),\n"
31 + "impressionCost NUMERIC,\n"
32 + "FOREIGN KEY (userId) REFERENCES USERS(id)\n"
33 + ");";
34
35 private static String CREATE_TABLE_CLICK_LOGS
36 = "CREATE TABLE IF NOT EXISTS CLICK_LOGS(\n"
37 + "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
38 + "userId VARCHAR(255),\n"
39 + "date DATE,\n"
40 + "clickCost NUMERIC,\n"
41 + "FOREIGN KEY (userId) REFERENCES USERS(id)\n"
42 + ");";
43
44 private static String CREATE_TABLE_SERVER_LOGS
45 = "CREATE TABLE IF NOT EXISTS SERVER_LOGS(\n"
46 + "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n"
47 + "userId VARCHAR(255),\n"
48 + "entryDate DATE,\n"
49 + "exitDate DATE,\n"
50 + "pagesViewed INTEGER,\n"
51 + "conversion VARCHAR(55),\n"
52 + "FOREIGN KEY (userId) REFERENCES USERS(id)\n"
53 + ");";
54
55 private static String CREATE_TABLE_SETTINGS
56 = "CREATE TABLE IF NOT EXISTS SETTINGS (\n"
57 + "name VARCHAR(255) NOT NULL,\n"
58 + "value TEXT,\n"
59 + "PRIMARY KEY (name)\n"
60 + ");";
61
62 public static String[] CREATE_TABLES =
63 {
64 "PRAGMA foreign_keys = ON;",
65 CREATE_TABLE_USERS,
66 CREATE_TABLE_IMPRESSION_LOGS,
67 CREATE_TABLE_CLICK_LOGS,
68 CREATE_TABLE_SERVER_LOGS,
69 CREATE_TABLE_SETTINGS
70 };
71
72 public static String[] CREATE_INDEXES =
73 {
74 "CREATE INDEX IF NOT EXISTS IMPRESSION_LOGS_DATE_INDEX ON IMPRESSION_LOGS (date);",
75 "CREATE INDEX IF NOT EXISTS CLICK_LOGS_DATE_INDEX ON CLICK_LOGS (date);",
76 "CREATE INDEX IF NOT EXISTS SERVER_LOGS_ENTRY_DATE_INDEX ON SERVER_LOGS (entryDate);",
77 "CREATE INDEX IF NOT EXISTS SERVER_LOGS_EXIT_DATE_INDEX ON SERVER_LOGS (exitDate);"
78 };
79
80 public static String GETLASTID = "SELECT last_insert_rowid() as id";
81
82 /*
83 INSERT STATEMENTS
84 */
85 public static String insertUserStmt(UserEntry user)
86 {
87 return "INSERT INTO USERS VALUES (" + user.stringify()+ ");";
88 }
89
90 public static String insertImpressionStmt(ImpressionEntry ie)
91 {
92 return "INSERT INTO IMPRESSION_LOGS VALUES (" + ie.stringify() + ");";
93 }
94
95 public static String insertClickStmt(ClickEntry ce)
96 {
97 return "INSERT INTO CLICK_LOGS VALUES (" + ce.stringify() + ");";
98 }
99
100 public static String insertServerStmt(ServerEntry se)
101 {
102 return "INSERT INTO SERVER_LOGS VALUES (" + se.stringify() + ");";
103 }
104
105 public static String insertSettingStmt(String name, String value)//TODO check... looks shitty!
106 {
107 return "INSERT INTO SETTINGS VALUES ('" + name + "', '" + value + "');";
108 }
109
110 /*
111 UPDATE STATEMENTS
112 */
113 // if you need them call me up (Marco)
114 /*
115 DELETE STATEMENTS
116 */
117 // if you need them call me up (Marco)
118 /*
119 DROP ALL STATEMENTS
120 */
121 public static String dropAllFrom_USERS = "DELETE FROM USERS;";
122 public static String dropAllFrom_IMPRESSION_LOGS = "DELETE FROM IMPRESSION_LOGS;";
123 public static String dropAllFrom_CLICK_LOGS = "DELETE FROM CLICK_LOGS;";
124 public static String dropAllFrom_SERVER_LOGS = "DELETE FROM SERVER_LOGS;";
125 public static String dropAllFrom_SETTINGS = "DELETE FROM SETTINGS;";
126 public static String[] dropAll_noSettings =
127 {
128 dropAllFrom_USERS,
129 dropAllFrom_IMPRESSION_LOGS,
130 dropAllFrom_CLICK_LOGS,
131 dropAllFrom_SERVER_LOGS
132 };
133
134 /*
135 COUNT STATEMENTS
136 */
137 public static String countAllFrom_USERS = "SELECT COUNT(*) as c FROM USERS;";
138 public static String countAllFrom_IMPRESSION_LOGS = "SELECT COUNT(*) as c FROM IMPRESSION_LOGS;";
139 public static String countAllFrom_CLICK_LOGS = "SELECT COUNT(*) as c FROM CLICK_LOGS;";
140 public static String countAllFrom_SERVER_LOGS = "SELECT COUNT(*) as c FROM SERVER_LOGS;";
141 public static String countAllFrom_SETTINGS = "SELECT COUNT(*) as c FROM SETTINGS;";
142
143 /*
144 SELECT ALL STATEMENTS
145 */
146 public static String selectAllFrom_USERS = "SELECT * FROM USERS;";
147 public static String selectAllFrom_IMPRESSION_LOGS = "SELECT * FROM IMPRESSION_LOGS;";
148 public static String selectAllFrom_CLICK_LOGS = "SELECT * FROM CLICK_LOGS;";
149 public static String selectAllFrom_SERVER_LOGS = "SELECT * FROM SERVER_LOGS;";
150 public static String selectAllFrom_SETTINGS = "SELECT * FROM SETTINGS;";
151
152 /*
153 SELECT BY ID
154 */
155 public static String selectByIdFrom_USERS(String id)
156 {
157 return "SELECT * FROM USERS WHERE USERS.id='" + id + "' LIMIT 1;";
158 }
159
160 public static String selectByIdFrom_IMPRESSION_LOGS(int id)
161 {
162 return "SELECT * FROM IMPRESSION_LOGS WHERE IMPRESSION_LOGS.id=" + id + " LIMIT 1;";
163 }
164
165 public static String selectByIdFrom_CLICK_LOGS(int id)
166 {
167 return "SELECT * FROM CLICK_LOGS WHERE CLICK_LOGS.id=" + id + " LIMIT 1;";
168 }
169
170 public static String selectByIdFrom_SERVER_LOGS(int id)
171 {
172 return "SELECT * FROM SERVER_LOGS WHERE SERVER_LOGS.id=" + id + " LIMIT 1;";
173 }
174
175 /*
176 SELECT BY userId
177 */
178 public static String selectByUserIdFrom_IMPRESSION_LOGS(String userId)
179 {
180 return "SELECT * FROM IMPRESSION_LOGS WHERE IMPRESSION_LOGS.userId='" + userId + "';";
181 }
182
183 public static String selectByUserIdFrom_CLICK_LOGS(String userId)
184 {
185 return "SELECT * FROM CLICK_LOGS WHERE CLICK_LOGS.userId='" + userId + "';";
186 }
187
188 public static String selectByUserIdFrom_SERVER_LOGS(String userId)
189 {
190 return "SELECT * FROM SERVER_LOGS WHERE SERVER_LOGS.userId='" + userId + "';";
191 }
192
193 public static String selectByNameFrom_SETTINGS(String name)
194 {
195 return "SELECT * FROM SETTINGS WHERE SETTINGS.name='" + name + "' LIMIT 1;";
196 }
197 /*
198 Number of impressions by week query.
199 */
200
201 public static String getNumberOfImpressionsPerWeek = "select Date as d,count(impressionCost) as c from impression_logs group by strftime('%W', Date) order by Date;";
202 public static String getNumberOfImpressionsPerHour="select Date as d,count(impressionCost) as c from impression_logs group by strftime('%H:%d', Date) order by Date;";
203 public static String getNumberOfImpressionsPerDay="select Date as d,count(impressionCost) as c from impression_logs group by strftime('%d', Date) order by Date;";
204
205 /*
206 Number of clicks
207 */
208 public static String getNumberOfClicksPerWeek="select Date as d,count(ClickCost) as c from click_logs group by strftime('%W', Date) order by Date;";
209 public static String getNumberOfClicksPerHour="select Date as d,count(ClickCost) as c from click_logs group by strftime('%H:%d', Date) order by Date;";
210 public static String getNumberOfClicksPerDay="select Date as d,count(ClickCost) as c from click_logs group by strftime('%d', Date) order by Date;";
211
212 /*
213 *Number of uniques
214 */
215 public static String getNumberOfUniques="select count (distinct ID) as GroupedValues from click_logs;";
216 public static String getGetNumberOfUniquesPerDay="select Date as d,count( id) as c from click_logs group by strftime('%d', Date) order by Date;";
217 public static String getGetNumberOfUniquesPerWeek="select Date as d,count(distinct id) as c from click_logs group by strftime('%W', Date) order by Date;";
218 public static String getGetNumberOfUniquesPerHours="select Date as d,count( id) as c from click_logs group by strftime('%H:%d', Date) order by Date;";
219
220
221 /*
222 Number of Bounces
223 */
224 public static String getNumberOfBounces="select count(strftime('%M', ExitDate)-strftime('%M', EntryDate)) as GroupedValues from server_logs where strftime('%M', ExitDate)-strftime('%M', EntryDate)=0 AND PagesViewed=1;";
225 public static String getNumberOfBouncesPerDay="select EntryDate as Entry,count(strftime('%M', ExitDate)-strftime('%M', EntryDate)) as c from server_logs where strftime('%M', ExitDate)-strftime('%M', EntryDate)=0 AND PagesViewed=1 group by strftime('%d',EntryDate) order by EntryDate;";
226 public static String getNumberOfBouncesPerHour="select EntryDate as d,count(strftime('%M', ExitDate)-strftime('%M', EntryDate)) as c from server_logs where strftime('%M', ExitDate)-strftime('%M', EntryDate)=0 AND PagesViewed=1 group by strftime('%H:%d',EntryDate) order by EntryDate;";
227 public static String getNumberOfBouncesPerWeek="select EntryDate as d,count(strftime('%M', ExitDate)-strftime('%M', EntryDate)) as c from server_logs where strftime('%M', ExitDate)-strftime('%M', EntryDate)=0 AND PagesViewed=1 group by strftime('%W',EntryDate);";
228
229
230 /*
231 Number of Conversions
232 */
233 public static String getNumberOfConversions="select count(Conversion) as GroupedValues from server_logs where Conversion='No';";
234 public static String getNumberOfConversionsPerHour="select EntryDate as d, count(Conversion) as c from server_logs where Conversion='No' group by strftime('%H:%d',EntryDate) order by EntryDate;";
235 public static String getNumberOfConversionsPerDay="select EntryDate as d, count(Conversion) as c from server_logs where Conversion='No' group by strftime('%d',EntryDate) order by EntryDate;";
236 public static String getNumberOfConversionsPerWeek="select EntryDate as d, count(Conversion) as c from server_logs where Conversion='No' group by strftime('%W',EntryDate) order by EntryDate;";
237
238 /*
239 Total Cost
240 */
241 public static String getTotalCost="select (select sum(impressionCost) from impression_logs) +(select sum(ClickCost) from click_logs) as GroupedValues;";
242 public static String getTotalCostPerHour="select d,sum(total) as c from (select date as d,ClickCost as total from click_logs union all select date as d,impressionCost as total from impression_logs) as u group by strftime('%H:%d',d) order by d;";
243 public static String getTotalCostPerWeek="select d,sum(total) as c from (select date as d,ClickCost as total from click_logs union all select date as d,impressionCost as total from impression_logs) as u group by strftime('%W',d) order by d;";
244 public static String getTotalCostPerDay="select d,sum(total) as c from (select date as d,ClickCost as total from click_logs union all select date as d,impressionCost as total from impression_logs) as u group by strftime('%d',d) order by d";
245 /*
246 Gets per hour--24 hours
247 For time granularity!!!
248 */
249 public static String getTotalNumberOfImpressionsPerHour="select count(impressionCost) as GroupedValues from impression_logs group by strftime('%H',Date);";
250
251 /*
252 CTR per week the closest i've got ok=>>>>wrong result tho
253 */
254 public static String getCTRPerWeek="SELECT date as d ,cast(count(date) AS FLOAT)/cast((SELECT count(date) FROM impression_logs group by strftime('%W',date) order by date) AS FLOAT) as c FROM click_logs group by strftime('%W',date) order by date;";
255 public static String getCTRPerDay="SELECT date as d ,cast(count(date) AS FLOAT)/cast((SELECT count(date) FROM impression_logs group by strftime('%d',date) order by date) AS FLOAT) as c FROM click_logs group by strftime('%d',date) order by date;";
256 public static String getCTRPerHour="SELECT date as d ,cast(count(date) AS FLOAT)/cast((SELECT count(date) FROM impression_logs group by strftime('%H:%d',date) order by date) AS FLOAT) as c FROM click_logs group by strftime('%H:%d',date) order by date;";
257
258
259}