Changeset 128499


Ignore:
Timestamp:
Nov 22, 2014, 1:27:38 PM (5 years ago)
Author:
cal@…
Message:

base: Clean up registry database, see https://lists.macosforge.org/pipermail/macports-dev/2014-November/028782.html

The following changes are included in this change:

  • Drop the `url' column from the ports table. It was unused and just wasting space and time (because there was a unique index using it). Since SQLite doesn't support ALTER TABLE DROP COLUMN, copy all data into a temporary table, re-create the original one with the new schema and copy the data back. Since this also drops all indices, re-create those as well.
  • Drop the `url' column from the registry2.0/entryobj.c file, i.e. the Tcl bindings for the registry.
  • Drop the index using the `url' column.
  • Drop the mtime', md5sum' and `editable' columns from the files table. Those were originally added to support configuration file handling, which has been implemented in GSoC 2010, but is currently not in a state that could be merged into trunk. Let's drop these fields now, knowing that we can easily re-add them later using the database upgrade code (which wasn't there when the branch was developed), rather than keeping them around and unused for a couple more years.
  • Modify the Tcl API registry2.0/fileobj.c to no longer support the removed fields.
  • Change registry2.0/registry.tcl fileinfo_for_file to always return a dummy md5 rather than querying the database for it. Essentially, this should mean no change for clients of the API, because the database fields never contained anything useful.
  • Modify the C registry API cregistry/entry.c to no longer set the `mtime' to the dummy value "0" on file creation.
  • Add an index on dependencies(id) to help speed up JOINs involving dependencies.
  • Add indices portgroups(id) and portgroups(id, name, version, size, sha256) to speed up portgroup management (the ID index) and opening portgroups from registry.
  • Update database version to 1.202
  • Reformat database creation SQL statements to allow for easier diffing, copying and changing by listing one field per line.
Location:
trunk/base/src
Files:
5 edited

Legend:

Unmodified
Added
Removed
  • trunk/base/src/cregistry/entry.c

    r128276 r128499  
    784784    int result = 1;
    785785    sqlite3_stmt* stmt = NULL;
    786     char* insert = "INSERT INTO registry.files (id, path, mtime, active) "
    787         "VALUES (?, ?, 0, 0)";
     786    char* insert = "INSERT INTO registry.files (id, path, active) "
     787        "VALUES (?, ?, 0)";
    788788    if ((sqlite3_prepare_v2(reg->db, insert, -1, &stmt, NULL) == SQLITE_OK)
    789789            && (sqlite3_bind_int64(stmt, 1, entry->id) == SQLITE_OK)) {
  • trunk/base/src/cregistry/sql.c

    r128089 r128499  
    130130        /* metadata table */
    131131        "CREATE TABLE registry.metadata (key UNIQUE, value)",
    132         "INSERT INTO registry.metadata (key, value) VALUES ('version', '1.201')",
     132        "INSERT INTO registry.metadata (key, value) VALUES ('version', '1.202')",
    133133        "INSERT INTO registry.metadata (key, value) VALUES ('created', strftime('%s', 'now'))",
    134134
    135135        /* ports table */
    136136        "CREATE TABLE registry.ports ("
    137             "id INTEGER PRIMARY KEY, "
    138             "name TEXT COLLATE NOCASE, portfile TEXT, 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)"
     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)"
    145154            ")",
    146         "CREATE INDEX registry.port_name ON ports "
     155        "CREATE INDEX registry.port_name ON ports"
    147156            "(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)",
     157        "CREATE INDEX registry.port_state ON ports(state)",
    151158
    152159        /* 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)",
     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)",
    157168        "CREATE INDEX registry.file_path ON files(path)",
    158169        "CREATE INDEX registry.file_actual ON files(actual_path)",
    159170
    160171        /* dependency map */
    161         "CREATE TABLE registry.dependencies (id INTEGER, name TEXT, variants TEXT, "
    162         "FOREIGN KEY(id) REFERENCES ports(id))",
    163         "CREATE INDEX registry.dep_name ON dependencies (name)",
     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)",
    164179
    165180        /* portgroups table */
    166         "CREATE TABLE registry.portgroups (id INTEGER, "
    167             "name TEXT, version TEXT COLLATE VERSION, size INTEGER, sha256 TEXT, "
    168             "FOREIGN KEY(id) REFERENCES ports(id))",
     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)",
    169190
    170191        "COMMIT",
     
    336357            static char* version_1_2_queries[] = {
    337358                /* portgroups table */
    338                 "CREATE TABLE registry.portgroups (id INTEGER, "
    339                     "name TEXT, version TEXT COLLATE VERSION, size INTEGER, sha256 TEXT, "
    340                     "FOREIGN KEY(id) REFERENCES ports(id))",
     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))",
    341366
    342367                "UPDATE registry.metadata SET value = '1.200' WHERE key = 'version'",
     
    373398            stmt = NULL;
    374399            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)) {
    375573                rollback_db(db);
    376574                return 0;
  • trunk/base/src/registry2.0/entryobj.c

    r117407 r128499  
    4343    "name",
    4444    "portfile",
    45     "url",
    4645    "location",
    4746    "epoch",
     
    420419    { "name", entry_obj_prop },
    421420    { "portfile", entry_obj_prop },
    422     { "url", entry_obj_prop },
    423421    { "location", entry_obj_prop },
    424422    { "epoch", entry_obj_prop },
  • trunk/base/src/registry2.0/fileobj.c

    r88376 r128499  
    4545    "actual_path",
    4646    "active",
    47     "mtime",
    48     "md5sum",
    49     "editable",
    5047    "binary",
    5148    NULL
     
    111108    { "actual_path", file_obj_prop },
    112109    { "active", file_obj_prop },
    113     { "mtime", file_obj_prop },
    114     { "md5sum", file_obj_prop },
    115     { "editable", file_obj_prop },
    116110    { "binary", file_obj_prop },
    117111    { NULL, NULL }
  • trunk/base/src/registry2.0/registry.tcl

    r116514 r128499  
    312312# 3: mode
    313313# 4: size
    314 # 5: md5 checksum information
     314# 5: md5 checksum information (deprecated, will always be "MD5 ($filename) NONE")
    315315#
    316316# fname         a path to a given file.
     
    321321    # and $statvar(mode) tells us that links are links).
    322322    if {![catch {file lstat $fname statvar}]} {
    323         if {[::file isfile $fname] && [::file type $fname] ne "link"} {
    324             if {[catch {md5 file $fname} md5sum] == 0} {
    325                 # Create a line that matches md5(1)'s output
    326                 # for backwards compatibility
    327                 set line "MD5 ($fname) = $md5sum"
    328                 return [list $fname $statvar(uid) $statvar(gid) $statvar(mode) $statvar(size) $line]
    329             }
    330         } else {
    331             return  [list $fname $statvar(uid) $statvar(gid) $statvar(mode) $statvar(size) "MD5 ($fname) NONE"]
    332         }
     323        return [list $fname $statvar(uid) $statvar(gid) $statvar(mode) $statvar(size) "MD5 ($fname) NONE"]
    333324    }
    334325    return {}
Note: See TracChangeset for help on using the changeset viewer.