source: trunk/base/src/cregistry/sql.c @ 141004

Last change on this file since 141004 was 141004, checked in by raimue@…, 5 years ago

cregistry: abort if registry version is newer than expected

  • Property svn:eol-style set to native
  • Property svn:keywords set to Id
File size: 23.7 KB
Line 
1/*
2 * sql.c
3 * $Id: sql.c 141004 2015-10-07 19:48:25Z raimue@macports.org $
4 *
5 * Copyright (c) 2007 Chris Pickel <sfiera@macports.org>
6 * Copyright (c) 2012, 2014 The MacPorts Project
7 * All rights reserved.
8 *
9 * Redistribution and use in source and binary forms, with or without
10 * modification, are permitted provided that the following conditions
11 * are met:
12 * 1. Redistributions of source code must retain the above copyright
13 *    notice, this list of conditions and the following disclaimer.
14 * 2. Redistributions in binary form must reproduce the above copyright
15 *    notice, this list of conditions and the following disclaimer in the
16 *    documentation and/or other materials provided with the distribution.
17 *
18 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR
19 * IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
20 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
21 * IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT,
22 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
23 * NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
24 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
25 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
26 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
27 * THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
28 */
29
30#if HAVE_CONFIG_H
31#include <config.h>
32#endif
33
34#include "registry.h"
35#include "sql.h"
36#include "vercomp.h"
37
38#include <sqlite3.h>
39#include <string.h>
40#include <tcl.h>
41#include <time.h>
42
43/**
44 * Executes a null-terminated list of queries. Pass it a list of queries, it'll
45 * execute them. This is mainly intended for initialization, when you have a
46 * number of standard queries to execute.
47 *
48 * @param [in] db      database to execute queries on
49 * @param [in] queries NULL-terminated list of queries
50 * @param [out] errPtr on error, a description of the error that occurred
51 * @return             true if success; false if failure
52 */
53int do_queries(sqlite3* db, char** queries, reg_error* errPtr) {
54    char** query;
55    sqlite3_stmt* stmt = NULL;
56    int r = SQLITE_OK;
57
58    for (query = queries; *query != NULL; query++) {
59        if ((r = sqlite3_prepare_v2(db, *query, -1, &stmt, NULL)) != SQLITE_OK) {
60            sqlite3_finalize(stmt);
61            break;
62        }
63
64        do {
65            r = sqlite3_step(stmt);
66        } while (r == SQLITE_BUSY);
67
68        sqlite3_finalize(stmt);
69
70        /* Either execution succeeded and r == SQLITE_DONE | SQLITE_ROW, or there was an error */
71        if (r != SQLITE_DONE && r != SQLITE_ROW) {
72            /* stop executing statements in case of errors */
73            break;
74        }
75    }
76
77    switch (r) {
78        case SQLITE_OK:
79        case SQLITE_DONE:
80        case SQLITE_ROW:
81            return 1;
82        default:
83            /* handle errors */
84            reg_sqlite_error(db, errPtr, *query);
85            return 0;
86    }
87}
88
89/**
90 * REGEXP function for sqlite3. Takes two arguments; the first is the value and
91 * the second the pattern. If the pattern is invalid, errors out. Otherwise,
92 * returns true if the value matches the pattern and false otherwise.
93 *
94 * This function is made available in sqlite3 as the REGEXP operator.
95 *
96 * @param [in] context sqlite3-defined structure
97 * @param [in] argc    number of arguments - always 2 and hence unused
98 * @param [in] argv    0: value to match; 1: pattern to match against
99 */
100static void sql_regexp(sqlite3_context* context, int argc UNUSED,
101        sqlite3_value** argv) {
102    const char* value = (const char*)sqlite3_value_text(argv[0]);
103    const char* pattern = (const char*)sqlite3_value_text(argv[1]);
104    switch (Tcl_RegExpMatch(NULL, value, pattern)) {
105        case 0:
106            sqlite3_result_int(context, 0);
107            break;
108        case 1:
109            sqlite3_result_int(context, 1);
110            break;
111        case -1:
112            sqlite3_result_error(context, "invalid pattern", -1);
113            break;
114    }
115}
116
117/**
118 * Creates tables in the registry. This function is called upon an uninitialized
119 * database to create the tables needed to record state between invocations of
120 * `port`.
121 *
122 * @param [in] db      database with an attached registry db
123 * @param [out] errPtr on error, a description of the error that occurred
124 * @return             true if success; false if failure
125 */
126int create_tables(sqlite3* db, reg_error* errPtr) {
127    static char* queries[] = {
128        "BEGIN",
129
130        /* metadata table */
131        "CREATE TABLE registry.metadata (key UNIQUE, value)",
132        "INSERT INTO registry.metadata (key, value) VALUES ('version', '1.202')",
133        "INSERT INTO registry.metadata (key, value) VALUES ('created', strftime('%s', 'now'))",
134
135        /* ports table */
136        "CREATE TABLE registry.ports ("
137              "id INTEGER PRIMARY KEY"
138            ", name TEXT COLLATE NOCASE"
139            ", portfile TEXT"
140            ", location TEXT"
141            ", epoch INTEGER"
142            ", version TEXT COLLATE VERSION"
143            ", revision INTEGER"
144            ", variants TEXT"
145            ", negated_variants TEXT"
146            ", state TEXT"
147            ", date DATETIME"
148            ", installtype TEXT"
149            ", archs TEXT"
150            ", requested INTEGER"
151            ", os_platform TEXT"
152            ", os_major INTEGER"
153            ", UNIQUE (name, epoch, version, revision, variants)"
154            ")",
155        "CREATE INDEX registry.port_name ON ports"
156            "(name, epoch, version, revision, variants)",
157        "CREATE INDEX registry.port_state ON ports(state)",
158
159        /* file map */
160        "CREATE TABLE registry.files ("
161              "id INTEGER"
162            ", path TEXT"
163            ", actual_path TEXT"
164            ", active INTEGER"
165            ", binary BOOL"
166            ", FOREIGN KEY(id) REFERENCES ports(id))",
167        "CREATE INDEX registry.file_port ON files(id)",
168        "CREATE INDEX registry.file_path ON files(path)",
169        "CREATE INDEX registry.file_actual ON files(actual_path)",
170
171        /* dependency map */
172        "CREATE TABLE registry.dependencies ("
173              "id INTEGER"
174            ", name TEXT"
175            ", variants TEXT"
176            ", FOREIGN KEY(id) REFERENCES ports(id))",
177        "CREATE INDEX registry.dep_id ON dependencies(id)",
178        "CREATE INDEX registry.dep_name ON dependencies(name)",
179
180        /* portgroups table */
181        "CREATE TABLE registry.portgroups ("
182              "id INTEGER"
183            ", name TEXT"
184            ", version TEXT COLLATE VERSION"
185            ", size INTEGER"
186            ", sha256 TEXT"
187            ", FOREIGN KEY(id) REFERENCES ports(id))",
188        "CREATE INDEX registry.portgroup_id ON portgroups(id)",
189        "CREATE INDEX registry.portgroup_open ON portgroups(id, name, version, size, sha256)",
190
191        "COMMIT",
192        NULL
193    };
194    return do_queries(db, queries, errPtr);
195}
196
197/**
198 * Tries to ROLLBACK a currently running transaction on the SQLite database.
199 * Errors are silently ignored to preserve errors that have been set before and
200 * are probably the root cause of why we did the rollback in the first place.
201 *
202 * @param [in] db    database to rollback
203 * @return           true if success, false on failure
204 */
205static int rollback_db(sqlite3* db) {
206    char* rollback = "ROLLBACK";
207    sqlite3_stmt* stmt = NULL;
208
209    /*puts("Attempting to ROLLBACK...");*/
210
211    if (sqlite3_prepare_v2(db, rollback, -1, &stmt, NULL) != SQLITE_OK) {
212        /*printf("failed prepare: %d: %s\n", sqlite3_errcode(db), sqlite3_errmsg(db));*/
213        return 0;
214    }
215
216    if (sqlite3_step(stmt) != SQLITE_DONE) {
217        /*printf("failed step: %d: %s\n", sqlite3_errcode(db), sqlite3_errmsg(db));*/
218        return 0;
219    }
220
221    /*puts("success.");*/
222
223    return 1;
224}
225
226/**
227 * Updates the database if necessary. This function queries the current database version
228 * from the metadata table and executes SQL to update the schema to newer versions if needed.
229 * After that, this function updates the database version number
230 *
231 * @param [in] db      database to update
232 * @param [out] errPtr on error, a description of the error that occurred
233 * @return             true if success; false if failure
234 */
235int update_db(sqlite3* db, reg_error* errPtr) {
236    const char* version;
237    int r;
238    int did_update = 0; /* true, if an update was done and the loop should be run again */
239    char* q_begin = "BEGIN";
240    char* q_version = "SELECT value FROM registry.metadata WHERE key = 'version'";
241    char* query = q_begin;
242    sqlite3_stmt* stmt = NULL;
243
244    do {
245        did_update = 0;
246
247        /* open a transaction to prevent a check-and-change race condition between
248         * multiple port(1) instances */
249        if ((r = sqlite3_prepare_v2(db, query, -1, &stmt, NULL)) != SQLITE_OK) {
250            break;
251        }
252
253        if ((r = sqlite3_step(stmt)) != SQLITE_DONE) {
254            break;
255        }
256
257        sqlite3_finalize(stmt);
258        stmt = NULL;
259
260        /* query current version number */
261        query = q_version;
262        if ((r = sqlite3_prepare_v2(db, query, -1, &stmt, NULL)) != SQLITE_OK) {
263            break;
264        }
265
266        r = sqlite3_step(stmt);
267        if (r == SQLITE_DONE) {
268            /* the version number was not found */
269            reg_throw(errPtr, REG_INVALID, "Version number in metadata table not found.");
270            sqlite3_finalize(stmt);
271            rollback_db(db);
272            return 0;
273        }
274        if (r != SQLITE_ROW) {
275            /* an error occured querying */
276            break;
277        }
278        if (NULL == (version = (const char *)sqlite3_column_text(stmt, 0))) {
279            reg_throw(errPtr, REG_INVALID, "Version number in metadata table is NULL.");
280            sqlite3_finalize(stmt);
281            rollback_db(db);
282            return 0;
283        }
284
285        /* we can't call vercmp directly because it's static, but we have
286         * sql_version, which is basically an alias */
287        /* There was a bug where the registry version was set as a float
288         * instead of a string on fresh installs, so some 1.100 registries
289         * will say 1.1. Fortunately, there were no other versions between
290         * 1.000 and 1.100. */
291        if (sql_version(NULL, -1, version, -1, "1.1") < 0) {
292            /* we need to update to 1.1, add binary field and index to files
293             * table */
294            static char* version_1_1_queries[] = {
295#if SQLITE_VERSION_NUMBER >= 3002000
296                "ALTER TABLE registry.files ADD COLUMN binary BOOL",
297#else
298                /*
299                 * SQLite < 3.2.0 doesn't support ALTER TABLE ADD COLUMN
300                 * Unfortunately, Tiger ships with SQLite < 3.2.0 (#34463)
301                 * This is taken from http://www.sqlite.org/faq.html#q11
302                 */
303
304                /* Create a temporary table */
305                "CREATE TEMPORARY TABLE mp_files_backup (id INTEGER, path TEXT, "
306                    "actual_path TEXT, active INT, mtime DATETIME, md5sum TEXT, editable INT, "
307                    "FOREIGN KEY(id) REFERENCES ports(id))",
308
309                /* Copy all data into the temporary table */
310                "INSERT INTO mp_files_backup SELECT id, path, actual_path, active, mtime, "
311                    "md5sum, editable FROM registry.files",
312
313                /* Drop the original table and re-create it with the new structure */
314                "DROP TABLE registry.files",
315                "CREATE TABLE registry.files (id INTEGER, path TEXT, actual_path TEXT, "
316                    "active INT, mtime DATETIME, md5sum TEXT, editable INT, binary BOOL, "
317                    "FOREIGN KEY(id) REFERENCES ports(id))",
318                "CREATE INDEX registry.file_port ON files(id)",
319                "CREATE INDEX registry.file_path ON files(path)",
320                "CREATE INDEX registry.file_actual ON files(actual_path)",
321
322                /* Copy all data back from temporary table */
323                "INSERT INTO registry.files (id, path, actual_path, active, mtime, md5sum, "
324                    "editable) SELECT id, path, actual_path, active, mtime, md5sum, "
325                    "editable FROM mp_files_backup",
326
327                /* Remove temporary table */
328                "DROP TABLE mp_files_backup",
329#endif
330                "CREATE INDEX registry.file_binary ON files(binary)",
331
332                "UPDATE registry.metadata SET value = '1.100' WHERE key = 'version'",
333
334                "COMMIT",
335                NULL
336            };
337
338            /* don't forget to finalize the version query here, or it might
339             * cause "cannot commit transaction - SQL statements in progress",
340             * see #32686 */
341            sqlite3_finalize(stmt);
342            stmt = NULL;
343
344            if (!do_queries(db, version_1_1_queries, errPtr)) {
345                rollback_db(db);
346                return 0;
347            }
348
349            did_update = 1;
350            continue;
351        }
352
353        if (sql_version(NULL, -1, version, -1, "1.200") < 0) {
354            /* We need to add the portgroup table and move the portfiles out
355               of the db and into the filesystem. The latter is way easier to do
356               from Tcl, so here we'll just flag that it needs to be done. */
357            static char* version_1_2_queries[] = {
358                /* portgroups table */
359                "CREATE TABLE registry.portgroups ("
360                      "id INTEGER"
361                    ", name TEXT"
362                    ", version TEXT COLLATE VERSION"
363                    ", size INTEGER"
364                    ", sha256 TEXT"
365                    ", FOREIGN KEY(id) REFERENCES ports(id))",
366
367                "UPDATE registry.metadata SET value = '1.200' WHERE key = 'version'",
368
369                "INSERT INTO registry.metadata (key, value) VALUES ('portfiles_update_needed', 1)",
370
371                "COMMIT",
372                NULL
373            };
374
375            sqlite3_finalize(stmt);
376            stmt = NULL;
377
378            if (!do_queries(db, version_1_2_queries, errPtr)) {
379                rollback_db(db);
380                return 0;
381            }
382
383            did_update = 1;
384            continue;
385        }
386
387        if (sql_version(NULL, -1, version, -1, "1.201") < 0) {
388            /* Delete the file_binary index, since it's a low-quality index
389             * according to https://www.sqlite.org/queryplanner-ng.html#howtofix */
390            static char* version_1_201_queries[] = {
391                "DROP INDEX IF EXISTS registry.file_binary",
392                "UPDATE registry.metadata SET value = '1.201' WHERE key = 'version'",
393                "COMMIT",
394                NULL
395            };
396
397            sqlite3_finalize(stmt);
398            stmt = NULL;
399            if (!do_queries(db, version_1_201_queries, errPtr)) {
400                rollback_db(db);
401                return 0;
402            }
403
404            did_update = 1;
405            continue;
406        }
407
408        if (sql_version(NULL, -1, version, -1, "1.202") < 0) {
409            static char* version_1_202_queries[] = {
410                "CREATE INDEX registry.portgroup_id ON portgroups(id)",
411                "CREATE INDEX registry.portgroup_open ON portgroups(id, name, version, size, sha256)",
412                "CREATE INDEX registry.dep_id ON dependencies(id)",
413
414                /*
415                 * SQLite doesn't support ALTER TABLE DROP CONSTRAINT or ALTER
416                 * TABLE DROP COLUMN, so we're doing the manual way to remove
417                 * UNIQUE(url, epoch, version, revision, variants) and the url
418                 * column.
419                 */
420
421                /* Create a temporary table */
422                "CREATE TEMPORARY TABLE mp_ports_backup ("
423                      "id INTEGER PRIMARY KEY"
424                    ", name TEXT COLLATE NOCASE"
425                    ", portfile CLOB"
426                    ", location TEXT"
427                    ", epoch INTEGER"
428                    ", version TEXT COLLATE VERSION"
429                    ", revision INTEGER"
430                    ", variants TEXT"
431                    ", negated_variants TEXT"
432                    ", state TEXT"
433                    ", date DATETIME"
434                    ", installtype TEXT"
435                    ", archs TEXT"
436                    ", requested INT"
437                    ", os_platform TEXT"
438                    ", os_major INTEGER"
439                    ", UNIQUE(name, epoch, version, revision, variants))",
440
441                /* Copy all data into the temporary table */
442                "INSERT INTO mp_ports_backup "
443                    "SELECT"
444                        "  id"
445                        ", name"
446                        ", portfile"
447                        ", location"
448                        ", epoch"
449                        ", version"
450                        ", revision"
451                        ", variants"
452                        ", negated_variants"
453                        ", state"
454                        ", date"
455                        ", installtype"
456                        ", archs"
457                        ", requested"
458                        ", os_platform"
459                        ", os_major"
460                    " FROM registry.ports",
461
462                /* Drop the original table and re-create it with the new structure */
463                "DROP TABLE registry.ports",
464                "CREATE TABLE registry.ports ("
465                      "id INTEGER PRIMARY KEY"
466                    ", name TEXT COLLATE NOCASE"
467                    ", portfile CLOB"
468                    ", location TEXT"
469                    ", epoch INTEGER"
470                    ", version TEXT COLLATE VERSION"
471                    ", revision INTEGER"
472                    ", variants TEXT"
473                    ", negated_variants TEXT"
474                    ", state TEXT"
475                    ", date DATETIME"
476                    ", installtype TEXT"
477                    ", archs TEXT"
478                    ", requested INT"
479                    ", os_platform TEXT"
480                    ", os_major INTEGER"
481                    ", UNIQUE(name, epoch, version, revision, variants))",
482
483                /* Copy all data back from temporary table */
484                "INSERT INTO registry.ports "
485                    "SELECT"
486                        "  id"
487                        ", name"
488                        ", portfile"
489                        ", location"
490                        ", epoch"
491                        ", version"
492                        ", revision"
493                        ", variants"
494                        ", negated_variants"
495                        ", state"
496                        ", date"
497                        ", installtype"
498                        ", archs"
499                        ", requested"
500                        ", os_platform"
501                        ", os_major"
502                    " FROM mp_ports_backup",
503
504                /* Re-create indices that have been dropped with the table */
505                "CREATE INDEX registry.port_name ON ports(name, epoch, version, revision, variants)",
506                "CREATE INDEX registry.port_state ON ports(state)",
507
508                /* Remove temporary table */
509                "DROP TABLE mp_ports_backup",
510
511                /*
512                 * SQLite doesn't support ALTER TABLE DROP COLUMN, so we're
513                 * doing the manual way to remove files.md5sum, files.mtime,
514                 * files.editable.
515                 */
516
517                /* Create a temporary table */
518                "CREATE TEMPORARY TABLE mp_files_backup ("
519                      "id INTEGER"
520                    ", path TEXT"
521                    ", actual_path TEXT"
522                    ", active INTEGER"
523                    ", binary BOOL"
524                    ")",
525
526                /* Copy all data into the temporary table */
527                "INSERT INTO mp_files_backup "
528                    "SELECT"
529                        "  id"
530                        ", path"
531                        ", actual_path"
532                        ", active"
533                        ", binary"
534                    " FROM registry.files",
535
536                /* Drop the original table and re-create it with the new structure */
537                "DROP TABLE registry.files",
538                "CREATE TABLE registry.files ("
539                      "id INTEGER"
540                    ", path TEXT"
541                    ", actual_path TEXT"
542                    ", active INTEGER"
543                    ", binary BOOL"
544                    ", FOREIGN KEY(id) REFERENCES ports(id))",
545
546                /* Copy all data back from temporary table */
547                "INSERT INTO registry.files "
548                    "SELECT"
549                        "  id"
550                        ", path"
551                        ", actual_path"
552                        ", active"
553                        ", binary"
554                    " FROM mp_files_backup",
555
556                /* Re-create indices that have been dropped with the table */
557                "CREATE INDEX registry.file_port ON files(id)",
558                "CREATE INDEX registry.file_path ON files(path)",
559                "CREATE INDEX registry.file_actual ON files(actual_path)",
560
561                /* Remove temporary table */
562                "DROP TABLE mp_files_backup",
563
564                /* Update version and commit */
565                "UPDATE registry.metadata SET value = '1.202' WHERE key = 'version'",
566                "COMMIT",
567                NULL
568            };
569
570            sqlite3_finalize(stmt);
571            stmt = NULL;
572            if (!do_queries(db, version_1_202_queries, errPtr)) {
573                rollback_db(db);
574                return 0;
575            }
576
577            did_update = 1;
578            continue;
579        }
580
581        /* add new versions here, but remember to:
582         *  - finalize the version query statement and set stmt to NULL
583         *  - do _not_ use "BEGIN" in your query list, since a transaction has
584         *    already been started for you
585         *  - end your query list with "COMMIT", NULL
586         *  - set did_update = 1 and continue;
587         *  - update the current version number below
588         */
589
590        if (sql_version(NULL, -1, version, -1, "1.202") > 0) {
591            /* the registry was already upgraded to a newer version and cannot be used anymore */
592            reg_throw(errPtr, REG_INVALID, "Version number in metadata table is newer than expected.");
593            sqlite3_finalize(stmt);
594            rollback_db(db);
595            return 0;
596        }
597
598        /* if we arrive here, no update was done and we should end the
599         * transaction. Using ROLLBACK here causes problems when rolling back
600         * other transactions later in the program. */
601        sqlite3_finalize(stmt);
602        stmt = NULL;
603        r = sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
604    } while (did_update);
605
606    sqlite3_finalize(stmt);
607    switch (r) {
608        case SQLITE_OK:
609        case SQLITE_DONE:
610        case SQLITE_ROW:
611            return 1;
612        default:
613            reg_sqlite_error(db, errPtr, query);
614            return 0;
615    }
616}
617
618/**
619 * Initializes database connection. This function creates all the temporary
620 * tables used by the registry. It also registers the user functions and
621 * collations declared here, making them available.
622 *
623 * @param [in] db      database to initialize
624 * @param [out] errPtr on error, a description of the error that occurred
625 * @return             true if success; false if failure
626 */
627int init_db(sqlite3* db, reg_error* errPtr) {
628    /* no code that uses these tables is being built at this time */
629    static char* queries[] = {
630        /*"BEGIN",*/
631
632        /* items cache */
633        /*"CREATE TEMPORARY TABLE items (refcount, proc UNIQUE, name, url, path, "
634            "worker, options, variants)",*/
635
636        /* indexes list */
637        /*"CREATE TEMPORARY TABLE indexes (file, name, attached)",
638
639        "COMMIT",*/
640        NULL
641    };
642
643    /* I'm not error-checking these. I don't think I need to. */
644    sqlite3_create_function(db, "REGEXP", 2, SQLITE_UTF8, NULL, sql_regexp,
645            NULL, NULL);
646
647    sqlite3_create_collation(db, "VERSION", SQLITE_UTF8, NULL, sql_version);
648
649    return do_queries(db, queries, errPtr);
650}
651
Note: See TracBrowser for help on using the repository browser.