src/libpakfire/db.c

   1 /*#############################################################################
   2 #                                                                             #
   3 # Pakfire - The IPFire package management system                              #
   4 # Copyright (C) 2021 Pakfire development team                                 #
   5 #                                                                             #
   6 # This program is free software: you can redistribute it and/or modify        #
   7 # it under the terms of the GNU General Public License as published by        #
   8 # the Free Software Foundation, either version 3 of the License, or           #
   9 # (at your option) any later version.                                         #
  10 #                                                                             #
  11 # This program is distributed in the hope that it will be useful,             #
  12 # but WITHOUT ANY WARRANTY; without even the implied warranty of              #
  13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the               #
  14 # GNU General Public License for more details.                                #
  15 #                                                                             #
  16 # You should have received a copy of the GNU General Public License           #
  17 # along with this program.  If not, see <http://www.gnu.org/licenses/>.       #
  18 #                                                                             #
  19 #############################################################################*/
  20
  21 #include <errno.h>
  22 #include <linux/limits.h>
  23 #include <stdlib.h>
  24 #include <time.h>
  25
  26 #include <solv/solver.h>
  27 #include <sqlite3.h>
  28
  29 #include <pakfire/archive.h>
  30 #include <pakfire/db.h>
  31 #include <pakfire/file.h>
  32 #include <pakfire/filelist.h>
  33 #include <pakfire/logging.h>
  34 #include <pakfire/package.h>
  35 #include <pakfire/pakfire.h>
  36 #include <pakfire/repo.h>
  37 #include <pakfire/util.h>
  38
  39 #define DATABASE_PATH PAKFIRE_PRIVATE_DIR "/packages.db"
  40
  41 #define CURRENT_SCHEMA 8
  42 #define SCHEMA_MIN_SUP 7
  43
  44 struct pakfire_db {
  45         struct pakfire* pakfire;
  46         int nrefs;
  47
  48         char path[PATH_MAX];
  49         int mode;
  50
  51         sqlite3* handle;
  52         int schema;
  53         time_t last_modified_at;
  54 };
  55
  56 static void logging_callback(void* data, int r, const char* msg) {
  57         struct pakfire* pakfire = (struct pakfire*)data;
  58
  59         ERROR(pakfire, "Database Error: %s: %s\n",
  60                 sqlite3_errstr(r), msg);
  61 }
  62
  63 static sqlite3_value* pakfire_db_get(struct pakfire_db* db, const char* key) {
  64         sqlite3_stmt* stmt = NULL;
  65         sqlite3_value* val = NULL;
  66         int r;
  67
  68         const char* sql = "SELECT val FROM settings WHERE key = ?";
  69
  70         // Prepare the statement
  71         r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
  72         if (r != SQLITE_OK) {
  73                 //ERROR(db->pakfire, "Could not prepare SQL statement: %s: %s\n",
  74                 //      sql, sqlite3_errmsg(db->handle));
  75                 return NULL;
  76         }
  77
  78         // Bind key
  79         r = sqlite3_bind_text(stmt, 1, key, strlen(key), NULL);
  80         if (r != SQLITE_OK) {
  81                 ERROR(db->pakfire, "Could not bind key: %s\n", sqlite3_errmsg(db->handle));
  82                 goto ERROR;
  83         }
  84
  85         // Execute the statement
  86         do {
  87                 r = sqlite3_step(stmt);
  88         } while (r == SQLITE_BUSY);
  89
  90         // We should have read a row
  91         if (r != SQLITE_ROW)
  92                 goto ERROR;
  93
  94         // Read value
  95         val = sqlite3_column_value(stmt, 0);
  96         if (!val) {
  97                 ERROR(db->pakfire, "Could not read value\n");
  98                 goto ERROR;
  99         }
 100
 101         // Copy value onto the heap
 102         val = sqlite3_value_dup(val);
 103
 104 ERROR:
 105         if (stmt)
 106                 sqlite3_finalize(stmt);
 107
 108         return val;
 109 }
 110
 111 static char* pakfire_db_get_string(struct pakfire_db* db, const char* key) {
 112         char* s = NULL;
 113
 114         // Fetch the value from the database
 115         sqlite3_value* value = pakfire_db_get(db, key);
 116         if (!value)
 117                 return NULL;
 118
 119         // Extract the value as string
 120         const char* p = (const char*)sqlite3_value_text(value);
 121         if (!p)
 122                 goto ERROR;
 123
 124         // Copy string to heap
 125         s = strdup(p);
 126
 127 ERROR:
 128         if (value)
 129                 sqlite3_value_free(value);
 130
 131         return s;
 132 }
 133
 134 static int pakfire_db_set_string(struct pakfire_db* db, const char* key, const char* val) {
 135         sqlite3_stmt* stmt = NULL;
 136         int r;
 137
 138         DEBUG(db->pakfire, "Setting %s to '%s'\n", key, val);
 139
 140         const char* sql = "INSERT INTO settings(key, val) VALUES(?, ?) \
 141                 ON CONFLICT (key) DO UPDATE SET val = excluded.val";
 142
 143         // Prepare statement
 144         r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
 145         if (r != SQLITE_OK) {
 146                 ERROR(db->pakfire, "Could not prepare SQL statement: %s: %s\n",
 147                         sql, sqlite3_errmsg(db->handle));
 148                 return 1;
 149         }
 150
 151         // Bind key
 152         r = sqlite3_bind_text(stmt, 1, key, strlen(key), NULL);
 153         if (r != SQLITE_OK) {
 154                 ERROR(db->pakfire, "Could not bind key: %s\n", sqlite3_errmsg(db->handle));
 155                 goto ERROR;
 156         }
 157
 158         // Bind val
 159         r = sqlite3_bind_text(stmt, 2, val, strlen(val), NULL);
 160         if (r != SQLITE_OK) {
 161                 ERROR(db->pakfire, "Could not bind val: %s\n", sqlite3_errmsg(db->handle));
 162                 goto ERROR;
 163         }
 164
 165         // Execute the statement
 166         do {
 167                 r = sqlite3_step(stmt);
 168         } while (r == SQLITE_BUSY);
 169
 170         // Set return code
 171         r = (r == SQLITE_OK);
 172
 173 ERROR:
 174         if (stmt)
 175                 sqlite3_finalize(stmt);
 176
 177         return r;
 178 }
 179
 180 static int pakfire_db_set_int(struct pakfire_db* db, const char* key, int val) {
 181         sqlite3_stmt* stmt = NULL;
 182         int r;
 183
 184         DEBUG(db->pakfire, "Setting %s to '%d'\n", key, val);
 185
 186         const char* sql = "INSERT INTO settings(key, val) VALUES(?, ?) \
 187                 ON CONFLICT (key) DO UPDATE SET val = excluded.val";
 188
 189         // Prepare statement
 190         r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
 191         if (r != SQLITE_OK) {
 192                 ERROR(db->pakfire, "Could not prepare SQL statement: %s: %s\n",
 193                         sql, sqlite3_errmsg(db->handle));
 194                 return 1;
 195         }
 196
 197         // Bind key
 198         r = sqlite3_bind_text(stmt, 1, key, strlen(key), NULL);
 199         if (r != SQLITE_OK) {
 200                 ERROR(db->pakfire, "Could not bind key: %s\n", sqlite3_errmsg(db->handle));
 201                 goto ERROR;
 202         }
 203
 204         // Bind val
 205         r = sqlite3_bind_int64(stmt, 2, val);
 206         if (r != SQLITE_OK) {
 207                 ERROR(db->pakfire, "Could not bind val: %s\n", sqlite3_errmsg(db->handle));
 208                 goto ERROR;
 209         }
 210
 211         // Execute the statement
 212         do {
 213                 r = sqlite3_step(stmt);
 214         } while (r == SQLITE_BUSY);
 215
 216         // Set return code
 217         r = (r == SQLITE_OK);
 218
 219 ERROR:
 220         if (stmt)
 221                 sqlite3_finalize(stmt);
 222
 223         return r;
 224 }
 225
 226 static time_t pakfire_read_modification_time(struct pakfire_db* db) {
 227         time_t t = 0;
 228
 229         // Fetch the value from the database
 230         sqlite3_value* value = pakfire_db_get(db, "last_modified_at");
 231         if (value) {
 232                 t = sqlite3_value_int64(value);
 233                 sqlite3_value_free(value);
 234         } else {
 235                 DEBUG(db->pakfire, "Could not find last modification timestamp\n");
 236         }
 237
 238         return t;
 239 }
 240
 241 static int pakfire_update_modification_time(struct pakfire_db* db) {
 242         // Get the current time in UTC
 243         time_t t = time(NULL);
 244
 245         // Store it in the database
 246         int r = pakfire_db_set_int(db, "last_modified_at", t);
 247         if (r)
 248                 return r;
 249
 250         // Update the last modification timestamp
 251         db->last_modified_at = t;
 252
 253         return r;
 254 }
 255
 256 static int pakfire_db_execute(struct pakfire_db* db, const char* stmt) {
 257         int r;
 258
 259         DEBUG(db->pakfire, "Executing database query: %s\n", stmt);
 260
 261         do {
 262                 r = sqlite3_exec(db->handle, stmt, NULL, NULL, NULL);
 263         } while (r == SQLITE_BUSY);
 264
 265         // Log any errors
 266         if (r) {
 267                 ERROR(db->pakfire, "Database query failed: %s\n", sqlite3_errmsg(db->handle));
 268         }
 269
 270         return r;
 271 }
 272
 273 static int pakfire_db_begin_transaction(struct pakfire_db* db) {
 274         return pakfire_db_execute(db, "BEGIN TRANSACTION");
 275 }
 276
 277 static int pakfire_db_commit(struct pakfire_db* db) {
 278         /*
 279                 If the database was opened in read-write mode, we will store the
 280                 timestamp of the latest modification to compare whether the database
 281                 has been changed mid-transaction.
 282         */
 283         if (db->mode == PAKFIRE_DB_READWRITE) {
 284                 int r = pakfire_update_modification_time(db);
 285                 if (r)
 286                         return r;
 287         }
 288
 289         return pakfire_db_execute(db, "COMMIT");
 290 }
 291
 292 static int pakfire_db_rollback(struct pakfire_db* db) {
 293         return pakfire_db_execute(db, "ROLLBACK");
 294 }
 295
 296 /*
 297         This function performs any fast optimization and tries to truncate the WAL log file
 298         to keep the database as compact as possible on disk.
 299 */
 300 static void pakfire_db_optimize(struct pakfire_db* db) {
 301         pakfire_db_execute(db, "PRAGMA optimize");
 302         pakfire_db_execute(db, "PRAGMA wal_checkpoint = TRUNCATE");
 303 }
 304
 305 static void pakfire_db_free(struct pakfire_db* db) {
 306         if (db->handle) {
 307                 // Optimize the database before it is being closed
 308                 pakfire_db_optimize(db);
 309
 310                 // Close database handle
 311                 int r = sqlite3_close(db->handle);
 312                 if (r != SQLITE_OK) {
 313                         ERROR(db->pakfire, "Could not close database handle: %s\n",
 314                                 sqlite3_errmsg(db->handle));
 315                 }
 316         }
 317
 318         pakfire_unref(db->pakfire);
 319
 320         free(db);
 321 }
 322
 323 static int pakfire_db_get_schema(struct pakfire_db* db) {
 324         sqlite3_value* value = pakfire_db_get(db, "schema");
 325         if (!value)
 326                 return -1;
 327
 328         int schema = sqlite3_value_int64(value);
 329         sqlite3_value_free(value);
 330
 331         DEBUG(db->pakfire, "Database has schema version %d\n", schema);
 332
 333         return schema;
 334 }
 335
 336 static int pakfire_db_create_schema(struct pakfire_db* db) {
 337         int r;
 338
 339         // Create settings table
 340         r = pakfire_db_execute(db, "CREATE TABLE IF NOT EXISTS settings(key TEXT, val TEXT)");
 341         if (r)
 342                 return 1;
 343
 344         // settings: Add a unique index on key
 345         r = pakfire_db_execute(db, "CREATE UNIQUE INDEX IF NOT EXISTS settings_key ON settings(key)");
 346         if (r)
 347                 return 1;
 348
 349         // Create packages table
 350         r = pakfire_db_execute(db,
 351                 "CREATE TABLE IF NOT EXISTS packages("
 352                         "id             INTEGER PRIMARY KEY, "
 353                         "name           TEXT, "
 354                         "evr            TEXT, "
 355                         "arch           TEXT, "
 356                         "groups         TEXT, "
 357                         "filename       TEXT, "
 358                         "size           INTEGER, "
 359                         "inst_size      INTEGER, "
 360                         "digest         TEXT, "
 361                         "license        TEXT, "
 362                         "summary        TEXT, "
 363                         "description    TEXT, "
 364                         "uuid           TEXT, "
 365                         "vendor         TEXT, "
 366                         "build_host     TEXT, "
 367                         "build_time     INTEGER, "
 368                         "installed      INTEGER, "
 369                         "userinstalled  INTEGER, "
 370                         "repository     TEXT, "
 371                         "source_name    TEXT, "
 372                         "source_evr     TEXT, "
 373                         "source_arch    TEXT"
 374                 ")");
 375         if (r)
 376                 return 1;
 377
 378         // packages: Create index to find package by name
 379         r = pakfire_db_execute(db, "CREATE INDEX IF NOT EXISTS packages_name ON packages(name)");
 380         if (r)
 381                 return 1;
 382
 383         // packages: Create unique index over UUID
 384         r = pakfire_db_execute(db, "CREATE UNIQUE INDEX IF NOT EXISTS packages_uuid ON packages(uuid)");
 385         if (r)
 386                 return 1;
 387
 388         // Create dependencies table
 389         r = pakfire_db_execute(db,
 390                 "CREATE TABLE IF NOT EXISTS dependencies("
 391                         "pkg            INTEGER, "
 392                         "type           TEXT, "
 393                         "dependency     TEXT, "
 394                         "FOREIGN KEY (pkg) REFERENCES packages(id) ON DELETE CASCADE"
 395                 ")");
 396         if (r)
 397                 return r;
 398
 399         // dependencies: Add index over packages
 400         r = pakfire_db_execute(db, "CREATE INDEX IF NOT EXISTS dependencies_pkg_index ON dependencies(pkg)");
 401         if (r)
 402                 return r;
 403
 404         // Create files table
 405         r = pakfire_db_execute(db,
 406                 "CREATE TABLE IF NOT EXISTS files("
 407                         "id             INTEGER PRIMARY KEY, "
 408                         "path           TEXT, "
 409                         "pkg            INTEGER, "
 410                         "size           INTEGER, "
 411                         "config         INTEGER, "
 412                         "datafile       INTEGER, "
 413                         "mode           INTEGER, "
 414                         "user           TEXT, "
 415                         "'group'        TEXT, "
 416                         "digest         TEXT, "
 417                         "ctime          INTEGER, "
 418                         "mtime          INTEGER, "
 419                         "capabilities   TEXT, "
 420                         "FOREIGN KEY (pkg) REFERENCES packages(id) ON DELETE CASCADE"
 421                 ")");
 422         if (r)
 423                 return 1;
 424
 425         // files: Add index over packages
 426         r = pakfire_db_execute(db, "CREATE INDEX IF NOT EXISTS files_pkg_index ON files(pkg)");
 427         if (r)
 428                 return 1;
 429
 430         // files: Add index over path
 431         r = pakfire_db_execute(db, "CREATE INDEX IF NOT EXISTS files_path_index ON files(path)");
 432         if (r)
 433                 return 1;
 434
 435         // Create scriptlets table
 436         r = pakfire_db_execute(db,
 437                 "CREATE TABLE IF NOT EXISTS scriptlets("
 438                         "id             INTEGER PRIMARY KEY, "
 439                         "pkg            INTEGER, "
 440                         "type           TEXT, "
 441                         "scriptlet      TEXT, "
 442                         "FOREIGN KEY (pkg) REFERENCES packages(id) ON DELETE CASCADE"
 443                 ")");
 444         if (r)
 445                 return 1;
 446
 447         // scriptlets: Add index over packages
 448         r = pakfire_db_execute(db, "CREATE INDEX IF NOT EXISTS scriptlets_pkg_index ON scriptlets(pkg)");
 449         if (r)
 450                 return 1;
 451
 452         const char* arch = pakfire_get_arch(db->pakfire);
 453
 454         // Set architecture
 455         r = pakfire_db_set_string(db, "arch", arch);
 456         if (r) {
 457                 ERROR(db->pakfire, "Could not set architecture\n");
 458                 return r;
 459         }
 460
 461         return 0;
 462 }
 463
 464 static int pakfire_db_migrate_to_schema_8(struct pakfire_db* db) {
 465         // packages: Drop build_id column
 466
 467         // Add foreign keys
 468         // TODO sqlite doesn't support adding foreign keys to existing tables and so we would
 469         // need to recreate the whole table and rename it afterwards. Annoying.
 470
 471         return 0;
 472 }
 473
 474 static int pakfire_db_migrate_schema(struct pakfire_db* db) {
 475         int r;
 476
 477         while (db->schema < CURRENT_SCHEMA) {
 478                 // Begin a new transaction
 479                 r = pakfire_db_begin_transaction(db);
 480                 if (r)
 481                         goto ROLLBACK;
 482
 483                 switch (db->schema) {
 484                         // No schema exists
 485                         case -1:
 486                                 r = pakfire_db_create_schema(db);
 487                                 if (r)
 488                                         goto ROLLBACK;
 489
 490                                 db->schema = CURRENT_SCHEMA;
 491                                 break;
 492
 493                         case 7:
 494                                 r = pakfire_db_migrate_to_schema_8(db);
 495                                 if (r)
 496                                         goto ROLLBACK;
 497
 498                                 db->schema++;
 499                                 break;
 500
 501                         default:
 502                                 ERROR(db->pakfire, "Cannot migrate database from schema %d\n", db->schema);
 503                                 goto ROLLBACK;
 504                 }
 505
 506                 // Update the schema version
 507                 r = pakfire_db_set_int(db, "schema", CURRENT_SCHEMA);
 508                 if (r)
 509                         goto ROLLBACK;
 510
 511                 // All done, commit!
 512                 r = pakfire_db_commit(db);
 513                 if (r)
 514                         goto ROLLBACK;
 515         }
 516
 517         return 0;
 518
 519 ROLLBACK:
 520         pakfire_db_rollback(db);
 521
 522         return 1;
 523 }
 524
 525 static int pakfire_db_check_arch(struct pakfire_db* db) {
 526         int r = 1;
 527
 528         // Fetch database architecture
 529         char* db_arch = pakfire_db_get_string(db, "arch");
 530         if (!db_arch) {
 531                 ERROR(db->pakfire, "Database is of an unknown architecture\n");
 532                 goto ERROR;
 533         }
 534
 535         // Fetch the running architecture
 536         const char* arch = pakfire_get_arch(db->pakfire);
 537         if (!arch)
 538                 goto ERROR;
 539
 540         // They should match
 541         if (strcmp(db_arch, arch) == 0)
 542                 r = 0;
 543
 544 ERROR:
 545         if (db_arch)
 546                 free(db_arch);
 547
 548         return r;
 549 }
 550
 551 static int pakfire_db_setup(struct pakfire_db* db) {
 552         int r;
 553
 554         // Setup logging
 555         sqlite3_config(SQLITE_CONFIG_LOG, logging_callback, db->pakfire);
 556
 557         // Enable foreign keys
 558         pakfire_db_execute(db, "PRAGMA foreign_keys = ON");
 559
 560         // Make LIKE case-sensitive
 561         pakfire_db_execute(db, "PRAGMA case_sensitive_like = ON");
 562
 563         // Fetch the current schema
 564         db->schema = pakfire_db_get_schema(db);
 565
 566         // Check if the schema is recent enough
 567         if (db->schema > 0 && db->schema < SCHEMA_MIN_SUP) {
 568                 ERROR(db->pakfire, "Database schema %d is not supported by this version of Pakfire\n",
 569                         db->schema);
 570                 return 1;
 571         }
 572
 573         // Read modification timestamp
 574         db->last_modified_at = pakfire_read_modification_time(db);
 575
 576         DEBUG(db->pakfire, "The database was last modified at %ld\n", db->last_modified_at);
 577
 578         // Done when not in read-write mode
 579         if (db->mode != PAKFIRE_DB_READWRITE)
 580                 return 0;
 581
 582         // Disable secure delete
 583         pakfire_db_execute(db, "PRAGMA secure_delete = OFF");
 584
 585         // Set database journal to WAL
 586         r = pakfire_db_execute(db, "PRAGMA journal_mode = WAL");
 587         if (r != SQLITE_OK) {
 588                 ERROR(db->pakfire, "Could not set journal mode to WAL: %s\n",
 589                         sqlite3_errmsg(db->handle));
 590                 return 1;
 591         }
 592
 593         // Disable autocheckpoint
 594         r = sqlite3_wal_autocheckpoint(db->handle, 0);
 595         if (r != SQLITE_OK) {
 596                 ERROR(db->pakfire, "Could not disable autocheckpoint: %s\n",
 597                         sqlite3_errmsg(db->handle));
 598                 return 1;
 599         }
 600
 601         // Create or migrate schema
 602         r = pakfire_db_migrate_schema(db);
 603         if (r)
 604                 return r;
 605
 606         return 0;
 607 }
 608
 609 int pakfire_db_open(struct pakfire_db** db, struct pakfire* pakfire, int flags) {
 610         int r = 1;
 611
 612         struct pakfire_db* o = calloc(1, sizeof(*o));
 613         if (!o)
 614                 return -ENOMEM;
 615
 616         o->pakfire = pakfire_ref(pakfire);
 617         o->nrefs = 1;
 618
 619         int sqlite3_flags = 0;
 620
 621         // Store mode & forward it to sqlite3
 622         if (flags & PAKFIRE_DB_READWRITE) {
 623                 o->mode = PAKFIRE_DB_READWRITE;
 624                 sqlite3_flags |= SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
 625         } else {
 626                 o->mode = PAKFIRE_DB_READONLY;
 627                 sqlite3_flags |= SQLITE_OPEN_READONLY;
 628         }
 629
 630         // Make the filename
 631         r = pakfire_make_path(o->pakfire, o->path, DATABASE_PATH);
 632         if (r < 0)
 633                 goto END;
 634
 635         // Try to open the sqlite3 database file
 636         r = sqlite3_open_v2(o->path, &o->handle, sqlite3_flags, NULL);
 637         if (r != SQLITE_OK) {
 638                 ERROR(pakfire, "Could not open database %s: %s\n",
 639                         o->path, sqlite3_errmsg(o->handle));
 640
 641                 r = 1;
 642                 goto END;
 643         }
 644
 645         // Setup the database
 646         r = pakfire_db_setup(o);
 647         if (r)
 648                 goto END;
 649
 650         // Check for compatible architecture
 651         r = pakfire_db_check_arch(o);
 652         if (r)
 653                 goto END;
 654
 655         *db = o;
 656         r = 0;
 657
 658 END:
 659         if (r)
 660                 pakfire_db_free(o);
 661
 662         return r;
 663 }
 664
 665 struct pakfire_db* pakfire_db_ref(struct pakfire_db* db) {
 666         db->nrefs++;
 667
 668         return db;
 669 }
 670
 671 struct pakfire_db* pakfire_db_unref(struct pakfire_db* db) {
 672         if (--db->nrefs > 0)
 673                 return db;
 674
 675         pakfire_db_free(db);
 676
 677         return NULL;
 678 }
 679
 680 static unsigned long pakfire_db_integrity_check(struct pakfire_db* db) {
 681         sqlite3_stmt* stmt = NULL;
 682         int r;
 683
 684         r = sqlite3_prepare_v2(db->handle, "PRAGMA integrity_check", -1, &stmt, NULL);
 685         if (r) {
 686                 ERROR(db->pakfire, "Could not prepare integrity check: %s\n",
 687                         sqlite3_errmsg(db->handle));
 688                 return 1;
 689         }
 690
 691         // Count any errors
 692         unsigned long errors = 0;
 693
 694         while (1) {
 695                 do {
 696                         r = sqlite3_step(stmt);
 697                 } while (r == SQLITE_BUSY);
 698
 699                 if (r == SQLITE_ROW) {
 700                         const char* error = (const char*)sqlite3_column_text(stmt, 0);
 701
 702                         // If the message is "ok", the database has passed the check
 703                         if (strcmp(error, "ok") == 0)
 704                                 continue;
 705
 706                         // Increment error counter
 707                         errors++;
 708
 709                         // Log the message
 710                         ERROR(db->pakfire, "%s\n", error);
 711
 712                 // Break on anything else
 713                 } else
 714                         break;
 715         }
 716
 717         sqlite3_finalize(stmt);
 718
 719         if (errors)
 720                 ERROR(db->pakfire, "Database integrity check failed\n");
 721         else
 722                 INFO(db->pakfire, "Database integrity check passed\n");
 723
 724         return errors;
 725 }
 726
 727 static unsigned long pakfire_db_foreign_key_check(struct pakfire_db* db) {
 728         sqlite3_stmt* stmt = NULL;
 729         int r;
 730
 731         r = sqlite3_prepare_v2(db->handle, "PRAGMA foreign_key_check", -1, &stmt, NULL);
 732         if (r) {
 733                 ERROR(db->pakfire, "Could not prepare foreign key check: %s\n",
 734                         sqlite3_errmsg(db->handle));
 735                 return 1;
 736         }
 737
 738         // Count any errors
 739         unsigned long errors = 0;
 740
 741         while (1) {
 742                 do {
 743                         r = sqlite3_step(stmt);
 744                 } while (r == SQLITE_BUSY);
 745
 746                 if (r == SQLITE_ROW) {
 747                         const unsigned char* table = sqlite3_column_text(stmt, 0);
 748                         unsigned long rowid = sqlite3_column_int64(stmt, 1);
 749                         const unsigned char* foreign_table = sqlite3_column_text(stmt, 2);
 750                         unsigned long foreign_rowid = sqlite3_column_int64(stmt, 3);
 751
 752                         // Increment error counter
 753                         errors++;
 754
 755                         // Log the message
 756                         ERROR(db->pakfire, "Foreign key violation found in %s, row %lu: "
 757                                 "%lu does not exist in table %s\n", table, rowid, foreign_rowid, foreign_table);
 758
 759                 // Break on anything else
 760                 } else
 761                         break;
 762         }
 763
 764         sqlite3_finalize(stmt);
 765
 766         if (errors)
 767                 ERROR(db->pakfire, "Foreign key check failed\n");
 768         else
 769                 INFO(db->pakfire, "Foreign key check passed\n");
 770
 771         return errors;
 772 }
 773
 774 /*
 775         This function performs an integrity check of the database
 776 */
 777 int pakfire_db_check(struct pakfire_db* db) {
 778         int r;
 779
 780         // Perform integrity check
 781         r = pakfire_db_integrity_check(db);
 782         if (r)
 783                 return 1;
 784
 785         // Perform foreign key check
 786         r = pakfire_db_foreign_key_check(db);
 787         if (r)
 788                 return 1;
 789
 790         return 0;
 791 }
 792
 793 // Returns the number of packages installed
 794 ssize_t pakfire_db_packages(struct pakfire_db* db) {
 795         sqlite3_stmt* stmt = NULL;
 796         ssize_t packages = -1;
 797
 798         int r = sqlite3_prepare_v2(db->handle, "SELECT COUNT(*) FROM packages", -1, &stmt, NULL);
 799         if (r) {
 800                 ERROR(db->pakfire, "Could not prepare SQL statement: %s\n",
 801                         sqlite3_errmsg(db->handle));
 802                 return -1;
 803         }
 804
 805         // Execute query
 806         do {
 807                 r = sqlite3_step(stmt);
 808         } while (r == SQLITE_BUSY);
 809
 810         if (r == SQLITE_ROW) {
 811                 packages = sqlite3_column_int64(stmt, 0);
 812         }
 813
 814         sqlite3_finalize(stmt);
 815
 816         return packages;
 817 }
 818
 819 static void pakfire_db_add_userinstalled(struct pakfire* pakfire, const char* name) {
 820         Pool* pool = pakfire_get_solv_pool(pakfire);
 821
 822         // Convert name to ID
 823         Id id = pool_str2id(pool, name, 1);
 824
 825         // Append it to pooljobs
 826         queue_push2(&pool->pooljobs, SOLVER_USERINSTALLED|SOLVER_SOLVABLE_NAME, id);
 827 }
 828
 829 static const struct pakfire_digest {
 830         enum pakfire_digests type;
 831         const char* prefix;
 832 } pakfire_digests[] = {
 833         { PAKFIRE_DIGEST_SHA512, "sha512:" },
 834         { PAKFIRE_DIGEST_SHA256, "sha256:" },
 835         { PAKFIRE_DIGEST_SHA1, "sha1:" },
 836         { PAKFIRE_DIGEST_NONE, NULL },
 837 };
 838
 839 static char* pakfire_db_pack_digest(enum pakfire_digests type, const char* hexdigest) {
 840         char* s = NULL;
 841         int r;
 842
 843         for (const struct pakfire_digest* digest = pakfire_digests; digest->type; digest++) {
 844                 if (digest->type == type) {
 845                         r = asprintf(&s, "%s%s", digest->prefix, hexdigest);
 846                         if (r < 0)
 847                                 return NULL;
 848
 849                         return s;
 850                 }
 851         }
 852
 853         return NULL;
 854 }
 855
 856 static const char* pakfire_db_unpack_digest(const char* hexdigest, enum pakfire_digests* type) {
 857         *type = PAKFIRE_DIGEST_NONE;
 858
 859         // Don't do anything for empty input
 860         if (!hexdigest || !*hexdigest)
 861                 return NULL;
 862
 863         for (const struct pakfire_digest* digest = pakfire_digests; digest->type; digest++) {
 864                 if (pakfire_string_startswith(hexdigest, digest->prefix)) {
 865                         *type = digest->type;
 866
 867                         // Return the beginning of the hexdigest
 868                         return hexdigest + strlen(digest->prefix);
 869                 }
 870         }
 871
 872         // No match
 873         return NULL;
 874 }
 875
 876 static int pakfire_db_add_dependencies(struct pakfire_db* db, unsigned long id, struct pakfire_package* pkg) {
 877         sqlite3_stmt* stmt = NULL;
 878         int r = 1;
 879
 880         const char* sql = "INSERT INTO dependencies(pkg, type, dependency) VALUES(?, ?, ?)";
 881
 882         // Prepare the statement
 883         r = sqlite3_prepare_v2(db->handle, sql, -1, &stmt, NULL);
 884         if (r) {
 885                 ERROR(db->pakfire, "Could not prepare SQL statement: %s: %s\n",
 886                         sql, sqlite3_errmsg(db->handle));
 887                 goto END;
 888         }
 889
 890         const struct __relation {
 891                 const char* type;
 892                 char** (*func)(struct pakfire_package*);
 893         } relations[] = {
 894                 { "provides", pakfire_package_get_provides },
 895                 { "prerequires", pakfire_package_get_prerequires },
 896                 { "requires", pakfire_package_get_requires },
 897                 { "conflicts", pakfire_package_get_conflicts },
 898                 { "obsoletes", pakfire_package_get_obsoletes },
 899                 { "recommends", pakfire_package_get_recommends },
 900                 { "suggests", pakfire_package_get_suggests },
 901                 { "supplements", pakfire_package_get_supplements },
 902                 { "enhances", pakfire_package_get_enhances },
 903                 { NULL, NULL },
 904         };
 905
 906         for (const struct __relation* relation = relations; relation->type; relation++) {
 907                 char** list = relation->func(pkg);
 908                 if (!list)
 909                         continue;
 910
 911                 for (char** dep = list; *dep; dep++) {
 912                         // Bind package ID
 913                         r = sqlite3_bind_int64(stmt, 1, id);
 914                         if (r) {
 915                                 ERROR(db->pakfire, "Could not bind id: %s\n",
 916                                         sqlite3_errmsg(db->handle));
 917                                 goto END;
 918                         }
 919
 920                         // Bind type
 921                         r = sqlite3_bind_text(stmt, 2, relation->type, -1, NULL);
 922                         if (r) {
 923                                 ERROR(db->pakfire, "Could not bind type: %s\n",
 924                                         sqlite3_errmsg(db->handle));
 925                                 goto END;
 926                         }
 927
 928                         // Bind dependency
 929                         r = sqlite3_bind_text(stmt, 3, *dep, -1, NULL);
 930                         if (r) {
 931                                 ERROR(db->pakfire, "Could not bind dependency: %s\n",
 932                                         sqlite3_errmsg(db->handle));
 933                                 goto END;
 934                         }
 935
 936                         // Execute query
 937                         do {
 938                                 r = sqlite3_step(stmt);
 939                         } while (r == SQLITE_BUSY);
 940
 941                         free(*dep);
 942
 943                         // Reset bound values
 944                         sqlite3_reset(stmt);
 945                 }
 946
 947                 free(list);
 948         }
 949
 950         // All okay
 951         r = 0;
 952
 953 END:
 954         if (stmt)
 955                 sqlite3_finalize(stmt);
 956
 957         return r;
 958 }
 959
 960 static int pakfire_db_add_files(struct pakfire_db* db, unsigned long id, struct pakfire_archive* archive) {
 961         sqlite3_stmt* stmt = NULL;
 962         char* digest = NULL;
 963         int r = 1;
 964
 965         // Get the filelist from the archive
 966         struct pakfire_filelist* filelist = pakfire_archive_get_filelist(archive);
 967         if (!filelist) {
 968                 ERROR(db->pakfire, "Could not fetch filelist from archive\n");
 969                 return 1;
 970         }
 971
 972         // Nothing to do if the list is empty
 973         if (pakfire_filelist_is_empty(filelist)) {
 974                 r = 0;
 975                 goto END;
 976         }
 977
 978         const char* sql = "INSERT INTO files(pkg, path, size, config, datafile, mode, "
 979                 "user, 'group', digest, ctime, mtime, capabilities) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 980
 981         // Prepare the statement
 982         r = sqlite3_prepare_v2(db->handle, sql, -1, &stmt, NULL);
 983         if (r) {
 984                 ERROR(db->pakfire, "Could not prepare SQL statement: %s: %s\n",
 985                         sql, sqlite3_errmsg(db->handle));
 986                 goto END;
 987         }
 988
 989         for (unsigned int i = 0; i < pakfire_filelist_size(filelist); i++) {
 990                 struct pakfire_file* file = pakfire_filelist_get(filelist, i);
 991
 992                 // Bind package ID
 993                 r = sqlite3_bind_int64(stmt, 1, id);
 994                 if (r) {
 995                         ERROR(db->pakfire, "Could not bind id: %s\n", sqlite3_errmsg(db->handle));
 996                         pakfire_file_unref(file);
 997                         goto END;
 998                 }
 999
1000                 // Bind name
1001                 const char* path = pakfire_file_get_path(file);
1002
1003                 r = sqlite3_bind_text(stmt, 2, path, -1, NULL);
1004                 if (r) {
1005                         ERROR(db->pakfire, "Could not bind path: %s\n", sqlite3_errmsg(db->handle));
1006                         pakfire_file_unref(file);
1007                         goto END;
1008                 }
1009
1010                 // Bind size
1011                 size_t size = pakfire_file_get_size(file);
1012
1013                 r = sqlite3_bind_int64(stmt, 3, size);
1014                 if (r) {
1015                         ERROR(db->pakfire, "Could not bind size: %s\n", sqlite3_errmsg(db->handle));
1016                         pakfire_file_unref(file);
1017                         goto END;
1018                 }
1019
1020                 // Bind config - XXX TODO
1021                 r = sqlite3_bind_null(stmt, 4);
1022                 if (r) {
1023                         ERROR(db->pakfire, "Could not bind config: %s\n", sqlite3_errmsg(db->handle));
1024                         pakfire_file_unref(file);
1025                         goto END;
1026                 }
1027
1028                 // Bind datafile - XXX TODO
1029                 r = sqlite3_bind_null(stmt, 5);
1030                 if (r) {
1031                         ERROR(db->pakfire, "Could not bind datafile: %s\n", sqlite3_errmsg(db->handle));
1032                         pakfire_file_unref(file);
1033                         goto END;
1034                 }
1035
1036                 // Bind mode
1037                 mode_t mode = pakfire_file_get_mode(file);
1038
1039                 r = sqlite3_bind_int64(stmt, 6, mode);
1040                 if (r) {
1041                         ERROR(db->pakfire, "Could not bind mode: %s\n", sqlite3_errmsg(db->handle));
1042                         pakfire_file_unref(file);
1043                         goto END;
1044                 }
1045
1046                 // Bind user
1047                 const char* user = pakfire_file_get_user(file);
1048
1049                 r = sqlite3_bind_text(stmt, 7, user, -1, NULL);
1050                 if (r) {
1051                         ERROR(db->pakfire, "Could not bind user: %s\n", sqlite3_errmsg(db->handle));
1052                         pakfire_file_unref(file);
1053                         goto END;
1054                 }
1055
1056                 // Bind group
1057                 const char* group = pakfire_file_get_group(file);
1058
1059                 r = sqlite3_bind_text(stmt, 8, group, -1, NULL);
1060                 if (r) {
1061                         ERROR(db->pakfire, "Could not bind group: %s\n", sqlite3_errmsg(db->handle));
1062                         pakfire_file_unref(file);
1063                         goto END;
1064                 }
1065
1066                 enum pakfire_digests digest_type = PAKFIRE_DIGEST_NONE;
1067
1068                 // Bind digest
1069                 const char* hexdigest = pakfire_file_get_hexdigest(file, &digest_type);
1070                 if (hexdigest) {
1071                         digest = pakfire_db_pack_digest(digest_type, hexdigest);
1072                         if (!digest) {
1073                                 ERROR(db->pakfire, "Could not pack digest: %m\n");
1074                                 pakfire_file_unref(file);
1075                                 goto END;
1076                         }
1077
1078                         r = sqlite3_bind_text(stmt, 9, digest, -1, NULL);
1079                         if (r) {
1080                                 ERROR(db->pakfire, "Could not bind digest: %s\n", sqlite3_errmsg(db->handle));
1081                                 pakfire_file_unref(file);
1082                                 goto END;
1083                         }
1084                 } else {
1085                         r = sqlite3_bind_null(stmt, 9);
1086                         if (r) {
1087                                 ERROR(db->pakfire, "Could not bind digest: %s\n", sqlite3_errmsg(db->handle));
1088                                 pakfire_file_unref(file);
1089                                 goto END;
1090                         }
1091                 }
1092
1093                 // Bind ctime
1094                 time_t ctime = pakfire_file_get_ctime(file);
1095
1096                 r = sqlite3_bind_int64(stmt, 10, ctime);
1097                 if (r) {
1098                         ERROR(db->pakfire, "Could not bind ctime: %s\n", sqlite3_errmsg(db->handle));
1099                         pakfire_file_unref(file);
1100                         goto END;
1101                 }
1102
1103                 // Bind mtime
1104                 time_t mtime = pakfire_file_get_mtime(file);
1105
1106                 r = sqlite3_bind_int64(stmt, 11, mtime);
1107                 if (r) {
1108                         ERROR(db->pakfire, "Could not bind mtime: %s\n", sqlite3_errmsg(db->handle));
1109                         pakfire_file_unref(file);
1110                         goto END;
1111                 }
1112
1113                 // Bind capabilities - XXX TODO
1114                 r = sqlite3_bind_null(stmt, 12);
1115                 if (r) {
1116                         ERROR(db->pakfire, "Could not bind capabilities: %s\n", sqlite3_errmsg(db->handle));
1117                         pakfire_file_unref(file);
1118                         goto END;
1119                 }
1120
1121                 // Execute query
1122                 do {
1123                         r = sqlite3_step(stmt);
1124                 } while (r == SQLITE_BUSY);
1125
1126                 // Move on to next file
1127                 pakfire_file_unref(file);
1128
1129                 // Check for errors
1130                 if (r != SQLITE_DONE) {
1131                         ERROR(db->pakfire, "Could not add file to database: %s\n",
1132                                 sqlite3_errmsg(db->handle));
1133                         goto END;
1134                 }
1135
1136                 // Reset bound values
1137                 sqlite3_reset(stmt);
1138
1139                 // Free digest
1140                 if (digest) {
1141                         free(digest);
1142                         digest = NULL;
1143                 }
1144         }
1145
1146         // All okay
1147         r = 0;
1148
1149 END:
1150         if (stmt)
1151                 sqlite3_finalize(stmt);
1152         if (digest)
1153                 free(digest);
1154
1155         pakfire_filelist_unref(filelist);
1156
1157         return r;
1158 }
1159
1160 static int pakfire_db_add_scriptlets(struct pakfire_db* db, unsigned long id, struct pakfire_archive* archive) {
1161         sqlite3_stmt* stmt = NULL;
1162         size_t size;
1163         int r = 1;
1164
1165         const char* sql = "INSERT INTO scriptlets(pkg, type, scriptlet) VALUES(?, ?, ?)";
1166
1167         // Prepare the statement
1168         r = sqlite3_prepare_v2(db->handle, sql, -1, &stmt, NULL);
1169         if (r) {
1170                 ERROR(db->pakfire, "Could not prepare SQL statement: %s: %s\n",
1171                         sql, sqlite3_errmsg(db->handle));
1172                 goto END;
1173         }
1174
1175         for (const char** type = pakfire_scriptlet_types; *type; type++) {
1176                 // Fetch the scriptlet
1177                 struct pakfire_scriptlet* scriptlet = pakfire_archive_get_scriptlet(archive, *type);
1178                 if (!scriptlet)
1179                         continue;
1180
1181                 // Bind package ID
1182                 r = sqlite3_bind_int64(stmt, 1, id);
1183                 if (r) {
1184                         ERROR(db->pakfire, "Could not bind id: %s\n", sqlite3_errmsg(db->handle));
1185                         pakfire_scriptlet_unref(scriptlet);
1186                         goto END;
1187                 }
1188
1189                 // Bind handle
1190                 r = sqlite3_bind_text(stmt, 2, *type, -1, NULL);
1191                 if (r) {
1192                         ERROR(db->pakfire, "Could not bind type: %s\n", sqlite3_errmsg(db->handle));
1193                         pakfire_scriptlet_unref(scriptlet);
1194                         goto END;
1195                 }
1196
1197                 const char* data = pakfire_scriptlet_get_data(scriptlet, &size);
1198
1199                 // Bind scriptlet
1200                 r = sqlite3_bind_text(stmt, 3, data, size, NULL);
1201                 if (r) {
1202                         ERROR(db->pakfire, "Could not bind scriptlet: %s\n", sqlite3_errmsg(db->handle));
1203                         pakfire_scriptlet_unref(scriptlet);
1204                         goto END;
1205                 }
1206
1207                 // Execute query
1208                 do {
1209                         r = sqlite3_step(stmt);
1210                 } while (r == SQLITE_BUSY);
1211
1212                 // Check for errors
1213                 if (r != SQLITE_DONE) {
1214                         ERROR(db->pakfire, "Could not add scriptlet to database: %s\n",
1215                                 sqlite3_errmsg(db->handle));
1216                         pakfire_scriptlet_unref(scriptlet);
1217                         goto END;
1218                 }
1219
1220                 pakfire_scriptlet_unref(scriptlet);
1221
1222                 // Reset bound values
1223                 sqlite3_reset(stmt);
1224         }
1225
1226         // All okay
1227         r = 0;
1228
1229 END:
1230         if (stmt)
1231                 sqlite3_finalize(stmt);
1232
1233         return r;
1234 }
1235
1236 int pakfire_db_add_package(struct pakfire_db* db,
1237                 struct pakfire_package* pkg, struct pakfire_archive* archive, int userinstalled) {
1238         sqlite3_stmt* stmt = NULL;
1239         char* digest = NULL;
1240         int r;
1241
1242         // Begin a new transaction
1243         r = pakfire_db_begin_transaction(db);
1244         if (r)
1245                 goto ERROR;
1246
1247         const char* sql = "INSERT INTO "
1248                 "packages("
1249                         "name, "
1250                         "evr, "
1251                         "arch, "
1252                         "groups, "
1253                         "filename, "
1254                         "size, "
1255                         "inst_size, "
1256                         "digest, "
1257                         "license, "
1258                         "summary, "
1259                         "description, "
1260                         "uuid, "
1261                         "vendor, "
1262                         "build_host, "
1263                         "build_time, "
1264                         "installed, "
1265                         "repository, "
1266                         "userinstalled, "
1267                         "source_name, "
1268                         "source_evr, "
1269                         "source_arch"
1270                 ") VALUES("
1271                         "?, "
1272                         "?, "
1273                         "?, "
1274                         "?, "
1275                         "?, "
1276                         "?, "
1277                         "?, "
1278                         "?, "
1279                         "?, "
1280                         "?, "
1281                         "?, "
1282                         "?, "
1283                         "?, "
1284                         "?, "
1285                         "?, "
1286                         "CURRENT_TIMESTAMP, "
1287                         "?, "
1288                         "?, "
1289                         "?, "
1290                         "?, "
1291                         "?"
1292                 ")";
1293
1294         // Prepare the statement
1295         r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
1296         if (r != SQLITE_OK) {
1297                 ERROR(db->pakfire, "Could not prepare SQL statement: %s: %s\n",
1298                         sql, sqlite3_errmsg(db->handle));
1299                 goto ERROR;
1300         }
1301
1302         // Bind name
1303         const char* name = pakfire_package_get_name(pkg);
1304
1305         r = sqlite3_bind_text(stmt, 1, name, -1, NULL);
1306         if (r) {
1307                 ERROR(db->pakfire, "Could not bind name: %s\n", sqlite3_errmsg(db->handle));
1308                 goto ERROR;
1309         }
1310
1311         // Bind evr
1312         const char* evr = pakfire_package_get_evr(pkg);
1313
1314         r = sqlite3_bind_text(stmt, 2, evr, -1, NULL);
1315         if (r) {
1316                 ERROR(db->pakfire, "Could not bind evr: %s\n", sqlite3_errmsg(db->handle));
1317                 goto ERROR;
1318         }
1319
1320         // Bind arch
1321         const char* arch = pakfire_package_get_arch(pkg);
1322
1323         r = sqlite3_bind_text(stmt, 3, arch, -1, NULL);
1324         if (r) {
1325                 ERROR(db->pakfire, "Could not bind arch: %s\n", sqlite3_errmsg(db->handle));
1326                 goto ERROR;
1327         }
1328
1329         // Bind groups
1330         char* groups = pakfire_package_get_groups(pkg);
1331         if (groups) {
1332                 r = sqlite3_bind_text(stmt, 4, groups, -1, NULL);
1333                 if (r) {
1334                         ERROR(db->pakfire, "Could not bind groups: %s\n", sqlite3_errmsg(db->handle));
1335                         free(groups);
1336                         goto ERROR;
1337                 }
1338
1339                 free(groups);
1340
1341         // No groups
1342         } else {
1343                 r = sqlite3_bind_null(stmt, 4);
1344                 if (r)
1345                         goto ERROR;
1346         }
1347
1348         // Bind filename
1349         const char* filename = pakfire_package_get_filename(pkg);
1350
1351         r = sqlite3_bind_text(stmt, 5, filename, -1, NULL);
1352         if (r) {
1353                 ERROR(db->pakfire, "Could not bind filename: %s\n", sqlite3_errmsg(db->handle));
1354                 goto ERROR;
1355         }
1356
1357         // Bind size
1358         unsigned long long size = pakfire_package_get_downloadsize(pkg);
1359
1360         r = sqlite3_bind_int64(stmt, 6, size);
1361         if (r) {
1362                 ERROR(db->pakfire, "Could not bind size: %s\n", sqlite3_errmsg(db->handle));
1363                 goto ERROR;
1364         }
1365
1366         // Bind installed size
1367         unsigned long long inst_size = pakfire_package_get_installsize(pkg);
1368
1369         r = sqlite3_bind_int64(stmt, 7, inst_size);
1370         if (r) {
1371                 ERROR(db->pakfire, "Could not bind inst_size: %s\n", sqlite3_errmsg(db->handle));
1372                 goto ERROR;
1373         }
1374
1375         enum pakfire_digests digest_type = PAKFIRE_DIGEST_NONE;
1376
1377         const char* hexdigest = pakfire_package_get_hexdigest(pkg, &digest_type);
1378         if (hexdigest) {
1379                 digest = pakfire_db_pack_digest(digest_type, hexdigest);
1380                 if (!digest)
1381                         goto ERROR;
1382
1383                 r = sqlite3_bind_text(stmt, 8, digest, -1, NULL);
1384                 if (r) {
1385                         ERROR(db->pakfire, "Could not bind digest: %s\n", sqlite3_errmsg(db->handle));
1386                         goto ERROR;
1387                 }
1388         }
1389
1390         // Bind license
1391         const char* license = pakfire_package_get_license(pkg);
1392
1393         r = sqlite3_bind_text(stmt, 9, license, -1, NULL);
1394         if (r) {
1395                 ERROR(db->pakfire, "Could not bind license: %s\n", sqlite3_errmsg(db->handle));
1396                 goto ERROR;
1397         }
1398
1399         // Bind summary
1400         const char* summary = pakfire_package_get_summary(pkg);
1401
1402         r = sqlite3_bind_text(stmt, 10, summary, -1, NULL);
1403         if (r) {
1404                 ERROR(db->pakfire, "Could not bind summary: %s\n", sqlite3_errmsg(db->handle));
1405                 goto ERROR;
1406         }
1407
1408         // Bind description
1409         const char* description = pakfire_package_get_description(pkg);
1410
1411         r = sqlite3_bind_text(stmt, 11, description, -1, NULL);
1412         if (r) {
1413                 ERROR(db->pakfire, "Could not bind description: %s\n", sqlite3_errmsg(db->handle));
1414                 goto ERROR;
1415         }
1416
1417         // Bind uuid
1418         const char* uuid = pakfire_package_get_uuid(pkg);
1419
1420         r = sqlite3_bind_text(stmt, 12, uuid, -1, NULL);
1421         if (r) {
1422                 ERROR(db->pakfire, "Could not bind uuid: %s\n", sqlite3_errmsg(db->handle));
1423                 goto ERROR;
1424         }
1425
1426         // Bind vendor
1427         const char* vendor = pakfire_package_get_vendor(pkg);
1428
1429         r = sqlite3_bind_text(stmt, 13, vendor, -1, NULL);
1430         if (r) {
1431                 ERROR(db->pakfire, "Could not bind vendor: %s\n", sqlite3_errmsg(db->handle));
1432                 goto ERROR;
1433         }
1434
1435         // Bind build_host
1436         const char* build_host = pakfire_package_get_build_host(pkg);
1437
1438         r = sqlite3_bind_text(stmt, 14, build_host, -1, NULL);
1439         if (r) {
1440                 ERROR(db->pakfire, "Could not bind build_host: %s\n", sqlite3_errmsg(db->handle));
1441                 goto ERROR;
1442         }
1443
1444         // Bind build_time
1445         time_t build_time = pakfire_package_get_build_time(pkg);
1446
1447         r = sqlite3_bind_int64(stmt, 15, build_time);
1448         if (r) {
1449                 ERROR(db->pakfire, "Could not bind build_time: %s\n", sqlite3_errmsg(db->handle));
1450                 goto ERROR;
1451         }
1452
1453         // Bind repository name
1454         struct pakfire_repo* repo = pakfire_package_get_repo(pkg);
1455         if (repo) {
1456                 const char* repo_name = pakfire_repo_get_name(repo);
1457                 pakfire_repo_unref(repo);
1458
1459                 r = sqlite3_bind_text(stmt, 16, repo_name, -1, NULL);
1460                 if (r)
1461                         goto ERROR;
1462
1463         // No repository?
1464         } else {
1465                 r = sqlite3_bind_null(stmt, 16);
1466                 if (r)
1467                         goto ERROR;
1468         }
1469
1470         // installed by the user?
1471         r = sqlite3_bind_int(stmt, 17, userinstalled);
1472         if (r) {
1473                 ERROR(db->pakfire, "Could not bind userinstalled: %s\n", sqlite3_errmsg(db->handle));
1474                 goto ERROR;
1475         }
1476
1477         // Source package name
1478         const char* source_name = pakfire_package_get_source_name(pkg);
1479         if (source_name) {
1480                 r = sqlite3_bind_text(stmt, 18, source_name, -1, NULL);
1481                 if (r)
1482                         goto ERROR;
1483         } else {
1484                 r = sqlite3_bind_null(stmt, 18);
1485                 if (r)
1486                         goto ERROR;
1487         }
1488
1489         // Source EVR
1490         const char* source_evr = pakfire_package_get_source_evr(pkg);
1491         if (source_evr) {
1492                 r = sqlite3_bind_text(stmt, 19, source_evr, -1, NULL);
1493                 if (r)
1494                         goto ERROR;
1495         } else {
1496                 r = sqlite3_bind_null(stmt, 19);
1497                 if (r)
1498                         goto ERROR;
1499         }
1500
1501         // Source arch
1502         const char* source_arch = pakfire_package_get_source_arch(pkg);
1503         if (source_arch) {
1504                 r = sqlite3_bind_text(stmt, 20, source_arch, -1, NULL);
1505                 if (r)
1506                         goto ERROR;
1507         } else {
1508                 r = sqlite3_bind_null(stmt, 20);
1509                 if (r)
1510                         goto ERROR;
1511         }
1512
1513         // Run query
1514         do {
1515                 r = sqlite3_step(stmt);
1516         } while (r == SQLITE_BUSY);
1517
1518         if (r != SQLITE_DONE) {
1519                 ERROR(db->pakfire, "Could not add package to database: %s\n",
1520                         sqlite3_errmsg(db->handle));
1521                 r = 1;
1522                 goto ERROR;
1523         }
1524
1525         // Save package ID
1526         unsigned long packages_id = sqlite3_last_insert_rowid(db->handle);
1527
1528         // Add dependencies
1529         r = pakfire_db_add_dependencies(db, packages_id, pkg);
1530         if (r)
1531                 goto ERROR;
1532
1533         // Add files
1534         r = pakfire_db_add_files(db, packages_id, archive);
1535         if (r)
1536                 goto ERROR;
1537
1538         // Add scriptlets
1539         r = pakfire_db_add_scriptlets(db, packages_id, archive);
1540         if (r)
1541                 goto ERROR;
1542
1543 ERROR:
1544         if (stmt)
1545                 sqlite3_finalize(stmt);
1546         if (digest)
1547                 free(digest);
1548
1549         // Commit or rollback
1550         if (r)
1551                 pakfire_db_rollback(db);
1552         else
1553                 r = pakfire_db_commit(db);
1554
1555         return r;
1556 }
1557
1558 int pakfire_db_remove_package(struct pakfire_db* db, struct pakfire_package* pkg) {
1559         sqlite3_stmt* stmt = NULL;
1560         int r = 1;
1561
1562         // Begin a new transaction
1563         r = pakfire_db_begin_transaction(db);
1564         if (r)
1565                 goto ERROR;
1566
1567         // Fetch the package's UUID
1568         const char* uuid = pakfire_package_get_uuid(pkg);
1569         if (!uuid) {
1570                 ERROR(db->pakfire, "Package has no UUID\n");
1571                 r = 1;
1572                 goto ERROR;
1573         }
1574
1575         r = sqlite3_prepare_v2(db->handle,
1576                 "DELETE FROM packages WHERE uuid = ?", -1, &stmt, NULL);
1577         if (r) {
1578                 ERROR(db->pakfire, "Could not prepare SQL statement: %s\n",
1579                         sqlite3_errmsg(db->handle));
1580                 goto ERROR;
1581         }
1582
1583         // Bind UUID
1584         r = sqlite3_bind_text(stmt, 1, uuid, -1, NULL);
1585         if (r) {
1586                 ERROR(db->pakfire, "Could not bind uuid: %s\n", sqlite3_errmsg(db->handle));
1587                 goto ERROR;
1588         }
1589
1590         // Execute query
1591         do {
1592                 r = sqlite3_step(stmt);
1593         } while (r == SQLITE_BUSY);
1594
1595         // Check if we have been successful
1596         if (r != SQLITE_DONE) {
1597                 ERROR(db->pakfire, "Could not delete package %s\n", uuid);
1598                 r = 1;
1599                 goto ERROR;
1600         }
1601
1602         // All done
1603         r = 0;
1604
1605 ERROR:
1606         if (stmt)
1607                 sqlite3_finalize(stmt);
1608
1609         // Commit or rollback
1610         if (r)
1611                 pakfire_db_rollback(db);
1612         else
1613                 r = pakfire_db_commit(db);
1614
1615         return r;
1616 }
1617
1618 struct pakfire_scriptlet* pakfire_db_get_scriptlet(struct pakfire_db* db,
1619                 struct pakfire_package* pkg, const char* type) {
1620         struct pakfire_scriptlet* scriptlet = NULL;
1621         sqlite3_stmt* stmt = NULL;
1622         int r = 1;
1623
1624         // Fetch the package's UUID
1625         const char* uuid = pakfire_package_get_uuid(pkg);
1626         if (!uuid) {
1627                 ERROR(db->pakfire, "Package has no UUID\n");
1628                 goto ERROR;
1629         }
1630
1631         const char* sql = "SELECT scriptlets.scriptlet FROM packages \
1632                 JOIN scriptlets ON packages.id = scriptlets.pkg \
1633                 WHERE packages.uuid = ? AND scriptlets.type = ?";
1634
1635         r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
1636         if (r) {
1637                 ERROR(db->pakfire, "Could not prepare SQL statement: %s %s\n",
1638                         sql, sqlite3_errmsg(db->handle));
1639                 goto ERROR;
1640         }
1641
1642         // Bind UUID
1643         r = sqlite3_bind_text(stmt, 1, uuid, -1, NULL);
1644         if (r) {
1645                 ERROR(db->pakfire, "Could not bind uuid: %s\n", sqlite3_errmsg(db->handle));
1646                 goto ERROR;
1647         }
1648
1649         r = sqlite3_bind_text(stmt, 2, type, -1, NULL);
1650         if (r) {
1651                 ERROR(db->pakfire, "Could not bind type: %s\n", sqlite3_errmsg(db->handle));
1652                 goto ERROR;
1653         }
1654
1655         DEBUG(db->pakfire, "Searching for scriptlet for package %s of type %s\n", uuid, type);
1656
1657         // Execute query
1658         do {
1659                 r = sqlite3_step(stmt);
1660         } while (r == SQLITE_BUSY);
1661
1662         // We have some payload
1663         if (r == SQLITE_ROW) {
1664                 const void* data = sqlite3_column_blob(stmt, 1);
1665                 ssize_t size = sqlite3_column_bytes(stmt, 1);
1666
1667                 // Create a scriptlet object
1668                 r = pakfire_scriptlet_create(&scriptlet, db->pakfire, type, data, size);
1669                 if (r)
1670                         goto ERROR;
1671         }
1672
1673 ERROR:
1674         if (stmt)
1675                 sqlite3_finalize(stmt);
1676
1677         return scriptlet;
1678 }
1679
1680 static int pakfire_db_load_package(struct pakfire_db* db, struct pakfire_repo* repo, sqlite3_stmt* stmt) {
1681         struct pakfire_package* pkg = NULL;
1682         int r = 1;
1683
1684         // Name
1685         const char* name = (const char*)sqlite3_column_text(stmt, 0);
1686         if (!name) {
1687                 ERROR(db->pakfire, "Could not read name: %s\n", sqlite3_errmsg(db->handle));
1688                 goto ERROR;
1689         }
1690
1691         // EVR
1692         const char* evr = (const char*)sqlite3_column_text(stmt, 1);
1693         if (!evr) {
1694                 ERROR(db->pakfire, "Could not read evr: %s\n", sqlite3_errmsg(db->handle));
1695                 goto ERROR;
1696         }
1697
1698         // Arch
1699         const char* arch = (const char*)sqlite3_column_text(stmt, 2);
1700         if (!arch) {
1701                 ERROR(db->pakfire, "Could not read arch: %s\n", sqlite3_errmsg(db->handle));
1702                 goto ERROR;
1703         }
1704
1705         // Create package
1706         pkg = pakfire_package_create(db->pakfire, repo, name, evr, arch);
1707         if (!pkg) {
1708                 ERROR(db->pakfire, "Could not create package\n");
1709                 goto ERROR;
1710         }
1711
1712         // ID
1713         uint64_t id = sqlite3_column_int64(stmt, 3);
1714         if (id)
1715                 pakfire_package_set_dbid(pkg, id);
1716
1717         // Groups
1718         const char* groups = (const char*)sqlite3_column_text(stmt, 4);
1719         if (groups) {
1720                 pakfire_package_set_groups(pkg, groups);
1721         }
1722
1723         // Filename
1724         const char* filename = (const char*)sqlite3_column_text(stmt, 5);
1725         if (filename) {
1726                 pakfire_package_set_filename(pkg, filename);
1727         }
1728
1729         // Size
1730         size_t size = sqlite3_column_int64(stmt, 6);
1731         if (size) {
1732                 pakfire_package_set_downloadsize(pkg, size);
1733         }
1734
1735         // Installed size
1736         size = sqlite3_column_int64(stmt, 7);
1737         if (size) {
1738                 pakfire_package_set_installsize(pkg, size);
1739         }
1740
1741         // Digest
1742         const char* digest = (const char*)sqlite3_column_text(stmt, 8);
1743         if (digest) {
1744                 enum pakfire_digests digest_type = PAKFIRE_DIGEST_NONE;
1745
1746                 // Unpack digest
1747                 const char* hexdigest = pakfire_db_unpack_digest(digest, &digest_type);
1748                 if (hexdigest)
1749                         pakfire_package_set_hexdigest(pkg, digest_type, hexdigest);
1750         }
1751
1752         // License
1753         const char* license = (const char*)sqlite3_column_text(stmt, 9);
1754         if (license) {
1755                 pakfire_package_set_license(pkg, license);
1756         }
1757
1758         // Summary
1759         const char* summary = (const char*)sqlite3_column_text(stmt, 10);
1760         if (summary) {
1761                 pakfire_package_set_summary(pkg, summary);
1762         }
1763
1764         // Description
1765         const char* description = (const char*)sqlite3_column_text(stmt, 11);
1766         if (description) {
1767                 pakfire_package_set_description(pkg, description);
1768         }
1769
1770         // UUID
1771         const char* uuid = (const char*)sqlite3_column_text(stmt, 12);
1772         if (uuid) {
1773                 pakfire_package_set_uuid(pkg, uuid);
1774         }
1775
1776         // Vendor
1777         const char* vendor = (const char*)sqlite3_column_text(stmt, 13);
1778         if (vendor) {
1779                 pakfire_package_set_vendor(pkg, vendor);
1780         }
1781
1782         // Build Host
1783         const char* build_host = (const char*)sqlite3_column_text(stmt, 14);
1784         if (build_host) {
1785                 pakfire_package_set_build_host(pkg, build_host);
1786         }
1787
1788         // Build Time
1789         time_t build_time = sqlite3_column_int64(stmt, 15);
1790         if (build_time) {
1791                 pakfire_package_set_build_time(pkg, build_time);
1792         }
1793
1794         // Install Time
1795         time_t install_time = sqlite3_column_int64(stmt, 16);
1796         if (install_time) {
1797                 pakfire_package_set_install_time(pkg, install_time);
1798         }
1799
1800         // installed by user?
1801         int userinstalled = sqlite3_column_int(stmt, 17);
1802         if (userinstalled)
1803                 pakfire_db_add_userinstalled(db->pakfire, name);
1804
1805         // Files
1806         const char* files = (const char*)sqlite3_column_text(stmt, 18);
1807         if (files) {
1808                 r = pakfire_package_set_filelist_from_string(pkg, files);
1809                 if (r)
1810                         goto ERROR;
1811         }
1812
1813         // Dependencies
1814
1815         const struct dependency {
1816                 unsigned int field;
1817                 void (*func)(struct pakfire_package* pkg, const char* dep);
1818         } dependencies[] = {
1819                 { 19, pakfire_package_add_provides },
1820                 { 20, pakfire_package_add_prerequires },
1821                 { 21, pakfire_package_add_requires },
1822                 { 22, pakfire_package_add_conflicts },
1823                 { 23, pakfire_package_add_obsoletes },
1824                 { 24, pakfire_package_add_recommends },
1825                 { 25, pakfire_package_add_suggests },
1826                 { 26, pakfire_package_add_supplements },
1827                 { 27, pakfire_package_add_enhances },
1828                 { 0, NULL },
1829         };
1830
1831         for (const struct dependency* deps = dependencies; deps->field; deps++) {
1832                 const char* relations = (const char*)sqlite3_column_text(stmt, deps->field);
1833                 if (relations) {
1834                         pakfire_str2deps(db->pakfire, pkg, deps->func, relations);
1835                 }
1836         }
1837
1838         // Source package
1839         const char* source_name = (const char*)sqlite3_column_text(stmt, 28);
1840         if (source_name)
1841                 pakfire_package_set_source_name(pkg, source_name);
1842
1843         // Source EVR
1844         const char* source_evr = (const char*)sqlite3_column_text(stmt, 29);
1845         if (source_evr)
1846                 pakfire_package_set_source_evr(pkg, source_evr);
1847
1848         // Source arch
1849         const char* source_arch = (const char*)sqlite3_column_text(stmt, 30);
1850         if (source_arch)
1851                 pakfire_package_set_source_arch(pkg, source_arch);
1852
1853         // Success
1854         r = 0;
1855
1856 ERROR:
1857         if (pkg)
1858                 pakfire_package_unref(pkg);
1859
1860         return r;
1861 }
1862
1863 int pakfire_db_load(struct pakfire_db* db, struct pakfire_repo* repo) {
1864         sqlite3_stmt* stmt = NULL;
1865         int r = 1;
1866
1867         DEBUG(db->pakfire, "Loading package database...\n");
1868
1869         // Drop contents of the repository
1870         pakfire_repo_clear(repo);
1871
1872         // Save starting time
1873         clock_t t_start = clock();
1874         clock_t t_end;
1875
1876         const char* sql =
1877                 "SELECT "
1878                         "name, "
1879                         "evr, "
1880                         "arch, "
1881                         "id, "
1882                         "groups, "
1883                         "filename, "
1884                         "size, "
1885                         "inst_size, "
1886                         "digest, "
1887                         "license, "
1888                         "summary, "
1889                         "description, "
1890                         "uuid, "
1891                         "vendor, "
1892                         "build_host, "
1893                         "build_time, "
1894                         "strftime('%s', installed) AS installed, "
1895                         "userinstalled, "
1896                         "("
1897                                 "SELECT group_concat(path, '\n') FROM files WHERE files.pkg = packages.id"
1898                         ") AS files, "
1899                         "("
1900                                 "SELECT group_concat(dependency, '\n') FROM dependencies d "
1901                                         "WHERE d.pkg = packages.id AND d.type = 'provides'"
1902                         ") AS provides, "
1903                         "("
1904                                 "SELECT group_concat(dependency, '\n') FROM dependencies d "
1905                                         "WHERE d.pkg = packages.id AND d.type = 'prerequires'"
1906                         ") AS prerequires, "
1907                         "("
1908                                 "SELECT group_concat(dependency, '\n') FROM dependencies d "
1909                                         "WHERE d.pkg = packages.id AND d.type = 'requires'"
1910                         ") AS requires, "
1911                         "("
1912                                 "SELECT group_concat(dependency, '\n') FROM dependencies d "
1913                                         "WHERE d.pkg = packages.id AND d.type = 'conflicts'"
1914                         ") AS conflicts, "
1915                         "("
1916                                 "SELECT group_concat(dependency, '\n') FROM dependencies d "
1917                                         "WHERE d.pkg = packages.id AND d.type = 'obsoletes'"
1918                         ") AS obsoletes, "
1919                         "("
1920                                 "SELECT group_concat(dependency, '\n') FROM dependencies d "
1921                                         "WHERE d.pkg = packages.id AND d.type = 'recommends'"
1922                         ") AS recommends, "
1923                         "("
1924                                 "SELECT group_concat(dependency, '\n') FROM dependencies d "
1925                                         "WHERE d.pkg = packages.id AND d.type = 'suggests'"
1926                         ") AS suggests, "
1927                         "("
1928                                 "SELECT group_concat(dependency, '\n') FROM dependencies d "
1929                                         "WHERE d.pkg = packages.id AND d.type = 'supplements'"
1930                         ") AS supplements, "
1931                         "("
1932                                 "SELECT group_concat(dependency, '\n') FROM dependencies d "
1933                                         "WHERE d.pkg = packages.id AND d.type = 'enhances'"
1934                         ") AS enhances, "
1935                         "source_name, "
1936                         "source_evr, "
1937                         "source_arch "
1938                 "FROM "
1939                         "packages"
1940                 ";";
1941
1942         // Prepare the statement
1943         r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
1944         if (r) {
1945                 ERROR(db->pakfire, "Could not prepare SQL statement: %s %s\n",
1946                         sql, sqlite3_errmsg(db->handle));
1947                 goto ERROR;
1948         }
1949
1950         for (;;) {
1951                 // Execute query
1952                 r = sqlite3_step(stmt);
1953
1954                 switch (r) {
1955                         // Retry if the database was busy
1956                         case SQLITE_BUSY:
1957                                 continue;
1958
1959                         // Read a row
1960                         case SQLITE_ROW:
1961                                 r = pakfire_db_load_package(db, repo, stmt);
1962                                 if (r)
1963                                         goto ERROR;
1964                                 break;
1965
1966                         // All rows have been processed
1967                         case SQLITE_DONE:
1968                                 goto END;
1969
1970                         // Go to error in any other cases
1971                         default:
1972                                 goto ERROR;
1973                 }
1974         }
1975
1976 END:
1977         // Save time when we finished
1978         t_end = clock();
1979
1980         DEBUG(db->pakfire, "Loading package database completed in %.4fms\n",
1981                 (double)(t_end - t_start) * 1000 / CLOCKS_PER_SEC);
1982
1983         // Mark repository as changed
1984         pakfire_repo_has_changed(repo);
1985
1986         // All done
1987         r = 0;
1988
1989 ERROR:
1990         if (r) {
1991                 ERROR(db->pakfire, "Failed reading package database: %d\n", r);
1992                 pakfire_repo_clear(repo);
1993         }
1994
1995         if (stmt)
1996                 sqlite3_finalize(stmt);
1997
1998         return r;
1999 }
2000
2001 static int pakfire_db_load_file(struct pakfire_db* db, struct pakfire_filelist* filelist,
2002                 sqlite3_stmt* stmt) {
2003         struct pakfire_file* file = NULL;
2004         char abspath[PATH_MAX];
2005         int r;
2006
2007         // Create a new file object
2008         r = pakfire_file_create(&file, db->pakfire);
2009         if (r)
2010                 goto ERROR;
2011
2012         // Path
2013         const char* path = (const char*)sqlite3_column_text(stmt, 0);
2014         if (path)
2015                 pakfire_file_set_path(file, path);
2016
2017         // Abspath
2018         r = pakfire_make_path(db->pakfire, abspath, path);
2019         if (r < 0)
2020                 goto ERROR;
2021
2022         pakfire_file_set_abspath(file, abspath);
2023
2024         // Size
2025         size_t size = sqlite3_column_int64(stmt, 1);
2026         if (size)
2027                 pakfire_file_set_size(file, size);
2028
2029         // Mode
2030         mode_t mode = sqlite3_column_int(stmt, 2);
2031         if (mode)
2032                 pakfire_file_set_mode(file, mode);
2033
2034         // User
2035         const char* user = (const char*)sqlite3_column_text(stmt, 3);
2036         if (user)
2037                 pakfire_file_set_user(file, user);
2038
2039         // Group
2040         const char* group = (const char*)sqlite3_column_text(stmt, 4);
2041         if (group)
2042                 pakfire_file_set_group(file, group);
2043
2044         // ctime
2045         time_t ctime = sqlite3_column_int64(stmt, 5);
2046         if (ctime)
2047                 pakfire_file_set_ctime(file, ctime);
2048
2049         // mtime
2050         time_t mtime = sqlite3_column_int64(stmt, 6);
2051         if (mtime)
2052                 pakfire_file_set_mtime(file, mtime);
2053
2054         // Append the file to the filelist
2055         r = pakfire_filelist_append(filelist, file);
2056
2057 ERROR:
2058         if (file)
2059                 pakfire_file_unref(file);
2060
2061         return r;
2062 }
2063
2064 int pakfire_db_package_filelist(struct pakfire_db* db, struct pakfire_filelist** filelist,
2065                 struct pakfire_package* pkg) {
2066         struct pakfire_filelist* fl = NULL;
2067         sqlite3_stmt* stmt = NULL;
2068         int r = 1;
2069
2070         // Fetch the package ID
2071         uint64_t id = pakfire_package_get_dbid(pkg);
2072         if (!id) {
2073                 ERROR(db->pakfire, "Package did not have an ID\n");
2074                 return 1;
2075         }
2076
2077         // Create a new filelist
2078         r = pakfire_filelist_create(&fl, db->pakfire);
2079         if (r) {
2080                 ERROR(db->pakfire, "Could not create filelist: %m\n");
2081                 goto ERROR;
2082         }
2083
2084         const char* sql =
2085                 "SELECT "
2086                         "path, "
2087                         "size, "
2088                         "mode, "
2089                         "user, "
2090                         "'group', "
2091                         "ctime, "
2092                         "mtime "
2093                 "FROM files "
2094
2095                 // Select all files that belong to this package
2096                 "WHERE "
2097                         "pkg = ? "
2098
2099                 // Filter out any files that are also in a different package (i.e. an update
2100                 // that has already been installed and this is the cleanup of the obsolete pkg)
2101                 "AND "
2102                         "NOT EXISTS ("
2103                                 "SELECT "
2104                                         "1 "
2105                                 "FROM files AS duplicates "
2106                                 "WHERE "
2107                                         "files.path = duplicates.path "
2108                                 "AND "
2109                                         "files.pkg != duplicates.pkg"
2110                         ") "
2111
2112                 // Return the longest paths first
2113                 "ORDER BY "
2114                         "length(path) DESC"
2115                 ";";
2116
2117         // Prepare the statement
2118         r = sqlite3_prepare_v2(db->handle, sql, strlen(sql), &stmt, NULL);
2119         if (r) {
2120                 ERROR(db->pakfire, "Could not prepare SQL statement: %s %s\n",
2121                         sql, sqlite3_errmsg(db->handle));
2122                 goto ERROR;
2123         }
2124
2125         // Bind package ID
2126         r = sqlite3_bind_int64(stmt, 1, id);
2127         if (r) {
2128                 ERROR(db->pakfire, "Could not bind package ID: %s\n", sqlite3_errmsg(db->handle));
2129                 goto ERROR;
2130         }
2131
2132         for (;;) {
2133                 // Execute query
2134                 r = sqlite3_step(stmt);
2135
2136                 switch (r) {
2137                         // Retry if the database was busy
2138                         case SQLITE_BUSY:
2139                                 continue;
2140
2141                         // Read a row
2142                         case SQLITE_ROW:
2143                                 r = pakfire_db_load_file(db, fl, stmt);
2144                                 if (r)
2145                                         goto ERROR;
2146                                 break;
2147
2148                         // All rows have been processed
2149                         case SQLITE_DONE:
2150                                 goto END;
2151
2152                         // Go to error in any other cases
2153                         default:
2154                                 goto ERROR;
2155                 }
2156         }
2157
2158 END:
2159         *filelist = pakfire_filelist_ref(fl);
2160         r = 0;
2161
2162 ERROR:
2163         if (stmt)
2164                 sqlite3_finalize(stmt);
2165         if (fl)
2166                 pakfire_filelist_unref(fl);
2167
2168         return r;
2169 }