1/* sql.vala 2 * 3 * Copyright © 2011-2012 Collabora Ltd. 4 * By Siegfried-Angel Gevatter Pujals <siegfried@gevatter.com> 5 * By Seif Lotfy <seif@lotfy.com> 6 * Copyright © 2011 Manish Sinha <manishsinha@ubuntu.com> 7 * Copyright © 2012 Canonical Ltd. 8 * By Siegfried-A. Gevatter <siegfried.gevatter@collabora.co.uk> 9 * 10 * This program is free software: you can redistribute it and/or modify 11 * it under the terms of the GNU Lesser General Public License as published by 12 * the Free Software Foundation, either version 2.1 of the License, or 13 * (at your option) any later version. 14 * 15 * This program is distributed in the hope that it will be useful, 16 * but WITHOUT ANY WARRANTY; without even the implied warranty of 17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 18 * GNU General Public License for more details. 19 * 20 * You should have received a copy of the GNU Lesser General Public License 21 * along with this program. If not, see <http://www.gnu.org/licenses/>. 22 * 23 */ 24 25using Zeitgeist; 26 27namespace Zeitgeist.SQLite 28{ 29 30 public enum EventViewRows 31 { 32 ID, 33 TIMESTAMP, 34 INTERPRETATION, 35 MANIFESTATION, 36 ACTOR, 37 PAYLOAD, 38 SUBJECT_URI, 39 SUBJECT_ID, 40 SUBJECT_INTERPRETATION, 41 SUBJECT_MANIFESTATION, 42 SUBJECT_ORIGIN, 43 SUBJECT_ORIGIN_URI, 44 SUBJECT_MIMETYPE, 45 SUBJECT_TEXT, 46 SUBJECT_STORAGE, 47 SUBJECT_STORAGE_STATE, 48 EVENT_ORIGIN, 49 EVENT_ORIGIN_URI, 50 SUBJECT_CURRENT_URI, 51 SUBJECT_ID_CURRENT, 52 SUBJECT_TEXT_ID, 53 SUBJECT_STORAGE_ID, 54 ACTOR_URI, 55 SUBJECT_CURRENT_ORIGIN, 56 SUBJECT_CURRENT_ORIGIN_URI 57 } 58 59 public delegate void DeletionCallback (string table, int64 rowid); 60 61 public class Database : Object 62 { 63 private const int DEFAULT_OPEN_FLAGS = 64 Sqlite.OPEN_READWRITE | Sqlite.OPEN_CREATE; 65 66 public Sqlite.Statement event_insertion_stmt; 67 public Sqlite.Statement id_retrieval_stmt; 68 public Sqlite.Statement move_handling_stmt; 69 public Sqlite.Statement payload_insertion_stmt; 70 71 // The DB should be accessible from engine for statement preperations 72 // as well as allowing extensions to add tables to it. 73 public Sqlite.Database database; 74 75 private DeletionCallback? deletion_callback = null; 76 private bool is_read_only = false; 77 78 public Database () throws EngineError 79 { 80 open_database (true); 81 82 prepare_read_queries (); 83 prepare_modification_queries (); 84 85 // Register a data change notification callback to look for 86 // deletions, so we can keep the TableLookups up to date. 87 database.update_hook (update_callback); 88 } 89 90 public Database.read_only () throws EngineError 91 { 92 is_read_only = true; 93 open_database (false); 94 95 prepare_read_queries (); 96 // not initializing the modification queries will let us find 97 // issues more easily 98 99 // Register a data change notification callback to look for 100 // deletions, so we can keep the TableLookups up to date. 101 database.update_hook (update_callback); 102 } 103 104 private void open_database (bool retry) 105 throws EngineError 106 { 107 int flags = is_read_only ? Sqlite.OPEN_READONLY : DEFAULT_OPEN_FLAGS; 108 int rc = Sqlite.Database.open_v2 ( 109 Utils.get_database_file_path (), 110 out database, flags); 111 112 if (rc == Sqlite.OK) 113 { 114 try 115 { 116 // Errors (like a malformed database) may not be exposed 117 // until we try to operate on the database. 118 if (is_read_only) 119 { 120 int ver = DatabaseSchema.get_schema_version (database); 121 if (ver < DatabaseSchema.CORE_SCHEMA_VERSION) 122 { 123 throw new EngineError.DATABASE_CANTOPEN ( 124 "Unable to open database: old schema version"); 125 } 126 } 127 else 128 { 129 DatabaseSchema.ensure_schema (database); 130 } 131 } 132 catch (EngineError err) 133 { 134 if (err is EngineError.DATABASE_CORRUPT && retry) 135 rc = Sqlite.CORRUPT; 136 else if (err is EngineError.DATABASE_CANTOPEN) 137 rc = Sqlite.CANTOPEN; 138 else if (err is EngineError.DATABASE_BUSY) 139 rc = Sqlite.BUSY; 140 else 141 throw err; 142 } 143 } 144 145 if (rc != Sqlite.OK) 146 { 147 if (rc == Sqlite.CORRUPT && retry) 148 { 149 // The database disk image is malformed 150 warning ("It looks like your database is corrupt. " + 151 "It will be renamed and a new one will be created."); 152 retire_database (); 153 open_database (false); 154 } 155 else if (rc == Sqlite.PERM || rc == Sqlite.CANTOPEN) 156 { 157 // Access permission denied / Unable to open database file 158 throw new EngineError.DATABASE_CANTOPEN ( 159 database.errmsg ()); 160 } 161 else if (rc == Sqlite.BUSY) 162 { 163 // The database file is locked 164 throw new EngineError.DATABASE_BUSY (database.errmsg ()); 165 } 166 else 167 { 168 string message = "Can't open database: %d, %s".printf (rc, 169 database.errmsg ()); 170 throw new EngineError.DATABASE_ERROR (message); 171 } 172 } 173 } 174 175 private static void retire_database () throws EngineError 176 { 177 try 178 { 179 File dbfile = File.new_for_path ( 180 Utils.get_database_file_path ()); 181 dbfile.set_display_name ( 182 Utils.get_database_file_retire_name ()); 183 } 184 catch (Error err) 185 { 186 string message = "Could not rename database: %s".printf ( 187 err.message); 188 throw new EngineError.DATABASE_RETIRE_FAILED (message); 189 } 190 } 191 192 public uint32 get_last_id () throws EngineError 193 { 194 int last_id = -1; 195 int rc = database.exec ("SELECT MAX(id) FROM event", 196 (n_columns, values, column_names) => 197 { 198 if (values[0] == null) 199 last_id = 0; 200 else 201 last_id = int.parse (values[0]); 202 return 0; 203 }, null); 204 assert_query_success (rc, "Can't query database"); 205 assert (last_id != -1); 206 return last_id; 207 } 208 209 public void set_deletion_callback (owned DeletionCallback? callback) 210 { 211 deletion_callback = (owned) callback; 212 } 213 214 /** 215 * Join all given event_ids into a comma-separated string suitable 216 * for use in a SQL query like "WHERE id IN (...)". 217 */ 218 public string get_sql_string_from_event_ids (uint32[] event_ids) 219 requires (event_ids.length > 0) 220 { 221 var sql_condition = new StringBuilder (); 222 sql_condition.append_printf ("%u", event_ids[0]); 223 for (int i = 1; i < event_ids.length; ++i) { 224 sql_condition.append_printf (", %u", event_ids[i]); 225 } 226 return sql_condition.str; 227 } 228 229 public TimeRange? get_time_range_for_event_ids (uint32[] event_ids) 230 throws EngineError 231 { 232 if (event_ids.length == 0) 233 return null; 234 235 string sql = """ 236 SELECT MIN(timestamp), MAX(timestamp) 237 FROM event 238 WHERE id IN (%s) 239 """.printf (get_sql_string_from_event_ids (event_ids)); 240 241 TimeRange? time_range = null; 242 int rc = database.exec (sql, 243 (n_columns, values, column_names) => 244 { 245 if (values[0] != null) 246 { 247 int64 start = int64.parse (values[0]); 248 int64 end = int64.parse (values[1]); 249 time_range = new TimeRange (start, end); 250 } 251 return 0; 252 }, null); 253 assert_query_success (rc, "SQL Error"); 254 255 return time_range; 256 } 257 258 public void insert_or_ignore_into_table (string table_name, 259 GenericArray<string> values) throws EngineError 260 { 261 if (values.length == 0) 262 return; 263 264 int rc; 265 266 var sql = new StringBuilder (); 267 sql.append ("INSERT OR IGNORE INTO "); 268 sql.append (table_name); 269 sql.append (" (value) SELECT ?"); 270 for (int i = 1; i < values.length; ++i) 271 sql.append (" UNION SELECT ?"); 272 273 Sqlite.Statement stmt; 274 rc = database.prepare_v2 (sql.str, -1, out stmt); 275 assert_query_success (rc, "SQL error"); 276 277 for (int i = 0; i < values.length; ++i) 278 stmt.bind_text (i+1, values[i]); 279 280 rc = stmt.step (); 281 assert_query_success (rc, "SQL error", Sqlite.DONE); 282 } 283 284 public void begin_transaction () throws EngineError 285 { 286 int rc = database.exec ("BEGIN"); 287 assert_query_success (rc, "Can't start transaction"); 288 } 289 290 public void end_transaction () throws EngineError 291 { 292 int rc = database.exec ("COMMIT"); 293 assert_query_success (rc, "Can't commit transaction"); 294 } 295 296 public void abort_transaction () throws EngineError 297 { 298 int rc = database.exec ("ROLLBACK"); 299 assert_query_success (rc, "Can't rollback transaction"); 300 } 301 302 public void close () 303 { 304 // SQLite connection is implicitly closed upon destruction 305 database = null; 306 } 307 308#if EXPLAIN_QUERIES 309 public void explain_query (Sqlite.Statement prepared_stmt) 310 throws EngineError 311 { 312 int rc; 313 Sqlite.Statement stmt; 314 315 var explain_sql = "EXPLAIN QUERY PLAN %s".printf (prepared_stmt.sql ()); 316 317 rc = prepared_stmt.db_handle ().prepare_v2 (explain_sql, -1, out stmt); 318 assert_query_success(rc, "SQL error"); 319 320 print ("%s\n", explain_sql); 321 322 while ((rc = stmt.step()) == Sqlite.ROW) 323 { 324 int select_id = stmt.column_int (0); 325 int order = stmt.column_int (1); 326 int from = stmt.column_int (2); 327 unowned string detail = stmt.column_text (3); 328 329 print ("%d %d %d %s\n", select_id, order, from, detail); 330 } 331 } 332#endif 333 334 /** 335 * Ensure `rc' is SQLITE_OK. If it isn't, print an error message 336 * and throw an error. 337 * 338 * @param rc error code returned by a SQLite call 339 * @param msg message to print if `rc' indicates an error 340 * @throws EngineError err 341 */ 342 [Diagnostics] 343 public void assert_query_success (int rc, string msg, 344 int success_code=Sqlite.OK) throws EngineError 345 { 346 if (unlikely (rc != success_code)) 347 { 348 string error_message = "%s: %d, %s".printf ( 349 msg, rc, database.errmsg ()); 350 warning ("%s\n", error_message); 351 assert_not_corrupt (rc); 352 throw new EngineError.DATABASE_ERROR (error_message); 353 } 354 } 355 356 /** 357 * Ensure `rc' isn't SQLITE_CORRUPT. If it is, schedule a database 358 * retire and Zeitgeist restart so a new database can be created, 359 * unless in read-only mode, in which case EngineError.DATABASE_ERROR 360 * will be thrown. 361 * 362 * This function should be called whenever assert_query_success isn't 363 * used. 364 * 365 * @param rc error code returned by a SQLite call 366 */ 367 public void assert_not_corrupt (int rc) 368 throws EngineError 369 { 370 if (unlikely (rc == Sqlite.CORRUPT)) 371 { 372 warning ("It looks like your database is corrupt: %s".printf ( 373 database.errmsg ())); 374 if (!is_read_only) 375 { 376 // Sets a flag in the database indicating that it is 377 // corrupt. This will trigger a database retire and 378 // re-creation on the next startup. 379 DatabaseSchema.set_corruption_flag (database); 380 } 381 throw new EngineError.DATABASE_CORRUPT (database.errmsg ()); 382 } 383 } 384 385 private void prepare_read_queries () throws EngineError 386 { 387 int rc; 388 string sql; 389 390 // Event ID retrieval statement 391 sql = """ 392 SELECT id FROM event 393 WHERE timestamp=? AND interpretation=? AND 394 manifestation=? AND actor=? 395 """; 396 rc = database.prepare_v2 (sql, -1, out id_retrieval_stmt); 397 assert_query_success (rc, "Event ID retrieval query error"); 398 } 399 400 private void prepare_modification_queries () throws EngineError 401 { 402 int rc; 403 string sql; 404 405 // Event insertion statement 406 sql = """ 407 INSERT INTO event ( 408 id, timestamp, interpretation, manifestation, actor, 409 origin, payload, subj_id, subj_id_current, 410 subj_interpretation, subj_manifestation, subj_origin, 411 subj_origin_current, subj_mimetype, subj_text, subj_storage 412 ) VALUES ( 413 ?, ?, ?, ?, ?, 414 (SELECT id FROM uri WHERE value=?), 415 ?, 416 (SELECT id FROM uri WHERE value=?), 417 (SELECT id FROM uri WHERE value=?), 418 ?, ?, 419 (SELECT id FROM uri WHERE value=?), 420 (SELECT id FROM uri WHERE value=?), 421 ?, 422 (SELECT id FROM text WHERE value=?), 423 (SELECT id FROM storage WHERE value=?) 424 )"""; 425 426 rc = database.prepare_v2 (sql, -1, out event_insertion_stmt); 427 assert_query_success (rc, "Insertion query error"); 428 429 // Move handling statment 430 sql = """ 431 UPDATE event 432 SET subj_id_current=(SELECT id FROM uri WHERE value=?) 433 , subj_origin_current=(SELECT id FROM uri WHERE value=?) 434 WHERE subj_id_current=(SELECT id FROM uri WHERE value=?) 435 AND interpretation!=? AND timestamp<? 436 """; 437 rc = database.prepare_v2 (sql, -1, out move_handling_stmt); 438 assert_query_success (rc, "Move handling error"); 439 440 // Payload insertion statment 441 sql = """ 442 INSERT INTO payload (value) VALUES (?) 443 """; 444 rc = database.prepare_v2 (sql, -1, out payload_insertion_stmt); 445 assert_query_success (rc, "Payload insertion query error"); 446 } 447 448 public bool analyze() throws EngineError 449 { 450 int rc = database.exec("ANALYZE"); 451 assert_query_success (rc, "Event ID retrieval query error"); 452 return false; 453 } 454 455 public void set_cache_size (int size) { 456 DatabaseSchema.exec_query (database, 457 "PRAGMA cache_size = %i".printf (size)); 458 } 459 460 protected void update_callback (Sqlite.Action action, 461 string dbname, string table, int64 rowid) 462 { 463 if (action != Sqlite.Action.DELETE) 464 return; 465 if (deletion_callback != null) 466 deletion_callback (table, rowid); 467 //interpretations_table 468 // manifestations_ 469 //mimetypes_table - mimetype table 470 // actors_ . actor table 471 // FIXME! 472 /* 473 stdout.printf ("%s", dbname); // = main 474 stdout.printf ("%s", table); 475 stdout.printf ("%li", (long) rowid); 476 */ 477 } 478 479 } 480 481} 482 483// vim:expandtab:ts=4:sw=4 484