source: branches/gsoc11-statistics/base/src/cregistry/sql.c @ 140222

Last change on this file since 140222 was 105085, checked in by snc@…, 7 years ago

merge from trunk

  • Property svn:eol-style set to native
  • Property svn:keywords set to Id
File size: 14.0 KB
Line 
1/*
2 * sql.c
3 * $Id: sql.c 105085 2013-04-09 18:46:31Z snc@macports.org $
4 *
5 * Copyright (c) 2007 Chris Pickel <sfiera@macports.org>
6 * Copyright (c) 2012 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.100)",
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 AUTOINCREMENT, "
138            "name TEXT COLLATE NOCASE, portfile CLOB, url TEXT, "
139            "location TEXT, epoch INTEGER, version TEXT COLLATE VERSION, "
140            "revision INTEGER, variants TEXT, negated_variants TEXT, "
141            "state TEXT, date DATETIME, installtype TEXT, archs TEXT, "
142            "requested INT, os_platform TEXT, os_major INTEGER, "
143            "UNIQUE (name, epoch, version, revision, variants), "
144            "UNIQUE (url, epoch, version, revision, variants)"
145            ")",
146        "CREATE INDEX registry.port_name ON ports "
147            "(name, epoch, version, revision, variants)",
148        "CREATE INDEX registry.port_url ON ports "
149            "(url, epoch, version, revision, variants)",
150        "CREATE INDEX registry.port_state ON ports (state)",
151
152        /* file map */
153        "CREATE TABLE registry.files (id INTEGER, path TEXT, actual_path TEXT, "
154            "active INT, mtime DATETIME, md5sum TEXT, editable INT, binary BOOL, "
155            "FOREIGN KEY(id) REFERENCES ports(id))",
156        "CREATE INDEX registry.file_port ON files (id)",
157        "CREATE INDEX registry.file_path ON files(path)",
158        "CREATE INDEX registry.file_actual ON files(actual_path)",
159        "CREATE INDEX registry.file_binary ON files(binary)",
160
161        /* dependency map */
162        "CREATE TABLE registry.dependencies (id INTEGER, name TEXT, variants TEXT, "
163        "FOREIGN KEY(id) REFERENCES ports(id))",
164        "CREATE INDEX registry.dep_name ON dependencies (name)",
165
166        "COMMIT",
167        NULL
168    };
169    return do_queries(db, queries, errPtr);
170}
171
172/**
173 * Tries to ROLLBACK a currently running transaction on the SQLite database.
174 * Errors are silently ignored to preserve errors that have been set before and
175 * are probably the root cause of why we did the rollback in the first place.
176 *
177 * @param [in] db    database to rollback
178 * @return           true if success, false on failure
179 */
180static int rollback_db(sqlite3* db) {
181    char* rollback = "ROLLBACK";
182    sqlite3_stmt* stmt = NULL;
183
184    /*puts("Attempting to ROLLBACK...");*/
185
186    if (sqlite3_prepare_v2(db, rollback, -1, &stmt, NULL) != SQLITE_OK) {
187        /*printf("failed prepare: %d: %s\n", sqlite3_errcode(db), sqlite3_errmsg(db));*/
188        return 0;
189    }
190
191    if (sqlite3_step(stmt) != SQLITE_DONE) {
192        /*printf("failed step: %d: %s\n", sqlite3_errcode(db), sqlite3_errmsg(db));*/
193        return 0;
194    }
195
196    /*puts("success.");*/
197
198    return 1;
199}
200
201/**
202 * Updates the database if necessary. This function queries the current database version
203 * from the metadata table and executes SQL to update the schema to newer versions if needed.
204 * After that, this function updates the database version number
205 *
206 * @param [in] db      database to update
207 * @param [out] errPtr on error, a description of the error that occurred
208 * @return             true if success; false if failure
209 */
210int update_db(sqlite3* db, reg_error* errPtr) {
211    const char* version;
212    int r;
213    int did_update = 0; /* true, if an update was done and the loop should be run again */
214    char* q_begin = "BEGIN";
215    char* q_version = "SELECT value FROM registry.metadata WHERE key = 'version'";
216    char* query = q_begin;
217    sqlite3_stmt* stmt = NULL;
218
219    do {
220        did_update = 0;
221
222        /* open a transaction to prevent a check-and-change race condition between
223         * multiple port(1) instances */
224        if ((r = sqlite3_prepare_v2(db, query, -1, &stmt, NULL)) != SQLITE_OK) {
225            break;
226        }
227
228        if ((r = sqlite3_step(stmt)) != SQLITE_DONE) {
229            break;
230        }
231
232        sqlite3_finalize(stmt);
233        stmt = NULL;
234
235        /* query current version number */
236        query = q_version;
237        if ((r = sqlite3_prepare_v2(db, query, -1, &stmt, NULL)) != SQLITE_OK) {
238            break;
239        }
240
241        r = sqlite3_step(stmt);
242        if (r == SQLITE_DONE) {
243            /* the version number was not found */
244            reg_throw(errPtr, REG_INVALID, "Version number in metadata table not found.");
245            sqlite3_finalize(stmt);
246            rollback_db(db);
247            return 0;
248        }
249        if (r != SQLITE_ROW) {
250            /* an error occured querying */
251            break;
252        }
253        if (NULL == (version = (const char *)sqlite3_column_text(stmt, 0))) {
254            reg_throw(errPtr, REG_INVALID, "Version number in metadata table is NULL.");
255            sqlite3_finalize(stmt);
256            rollback_db(db);
257            return 0;
258        }
259
260        /* we can't call vercmp directly because it's static, but we have
261         * sql_version, which is basically an alias */
262        if (sql_version(NULL, -1, version, -1, "1.1") < 0) {
263            /* we need to update to 1.1, add binary field and index to files
264             * table */
265            static char* version_1_1_queries[] = {
266#if SQLITE_VERSION_NUMBER >= 3002000
267                "ALTER TABLE registry.files ADD COLUMN binary BOOL",
268#else
269                /*
270                 * SQLite < 3.2.0 doesn't support ALTER TABLE ADD COLUMN
271                 * Unfortunately, Tiger ships with SQLite < 3.2.0 (#34463)
272                 * This is taken from http://www.sqlite.org/faq.html#q11
273                 */
274
275                /* Create a temporary table */
276                "CREATE TEMPORARY TABLE mp_files_backup (id INTEGER, path TEXT, "
277                    "actual_path TEXT, active INT, mtime DATETIME, md5sum TEXT, editable INT, "
278                    "FOREIGN KEY(id) REFERENCES ports(id))",
279
280                /* Copy all data into the temporary table */
281                "INSERT INTO mp_files_backup SELECT id, path, actual_path, active, mtime, "
282                    "md5sum, editable FROM registry.files",
283
284                /* Drop the original table and re-create it with the new structure */
285                "DROP TABLE registry.files",
286                "CREATE TABLE registry.files (id INTEGER, path TEXT, actual_path TEXT, "
287                    "active INT, mtime DATETIME, md5sum TEXT, editable INT, binary BOOL, "
288                    "FOREIGN KEY(id) REFERENCES ports(id))",
289                "CREATE INDEX registry.file_port ON files(id)",
290                "CREATE INDEX registry.file_path ON files(path)",
291                "CREATE INDEX registry.file_actual ON files(actual_path)",
292
293                /* Copy all data back from temporary table */
294                "INSERT INTO registry.files (id, path, actual_path, active, mtime, md5sum, "
295                    "editable) SELECT id, path, actual_path, active, mtime, md5sum, "
296                    "editable FROM mp_files_backup",
297
298                /* Remove temporary table */
299                "DROP TABLE mp_files_backup",
300#endif
301                "CREATE INDEX registry.file_binary ON files(binary)",
302
303                "UPDATE registry.metadata SET value = '1.100' WHERE key = 'version'",
304
305                "COMMIT",
306                NULL
307            };
308
309            /* don't forget to finalize the version query here, or it might
310             * cause "cannot commit transaction - SQL statements in progress",
311             * see #32686 */
312            sqlite3_finalize(stmt);
313            stmt = NULL;
314
315            if (!do_queries(db, version_1_1_queries, errPtr)) {
316                rollback_db(db);
317                return 0;
318            }
319
320            did_update = 1;
321            continue;
322        }
323
324        /* add new versions here, but remember to:
325         *  - finalize the version query statement and set stmt to NULL
326         *  - do _not_ use "BEGIN" in your query list, since a transaction has
327         *    already been started for you
328         *  - end your query list with "COMMIT", NULL
329         *  - set did_update = 1 and continue;
330         */
331
332        /* if we arrive here, no update was done and we should end the
333         * transaction. Using ROLLBACK here causes problems when rolling back
334         * other transactions later in the program. */
335        sqlite3_finalize(stmt);
336        stmt = NULL;
337        r = sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
338    } while (did_update);
339
340    sqlite3_finalize(stmt);
341    switch (r) {
342        case SQLITE_OK:
343        case SQLITE_DONE:
344        case SQLITE_ROW:
345            return 1;
346        default:
347            reg_sqlite_error(db, errPtr, query);
348            return 0;
349    }
350}
351
352/**
353 * Initializes database connection. This function creates all the temporary
354 * tables used by the registry. It also registers the user functions and
355 * collations declared here, making them available.
356 *
357 * @param [in] db      database to initialize
358 * @param [out] errPtr on error, a description of the error that occurred
359 * @return             true if success; false if failure
360 */
361int init_db(sqlite3* db, reg_error* errPtr) {
362    /* no code that uses these tables is being built at this time */
363    static char* queries[] = {
364        /*"BEGIN",*/
365
366        /* items cache */
367        /*"CREATE TEMPORARY TABLE items (refcount, proc UNIQUE, name, url, path, "
368            "worker, options, variants)",*/
369
370        /* indexes list */
371        /*"CREATE TEMPORARY TABLE indexes (file, name, attached)",
372
373        "COMMIT",*/
374        NULL
375    };
376
377    /* I'm not error-checking these. I don't think I need to. */
378    sqlite3_create_function(db, "REGEXP", 2, SQLITE_UTF8, NULL, sql_regexp,
379            NULL, NULL);
380
381    sqlite3_create_collation(db, "VERSION", SQLITE_UTF8, NULL, sql_version);
382
383    return do_queries(db, queries, errPtr);
384}
385
Note: See TracBrowser for help on using the repository browser.