<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head><meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>[128499] trunk/base/src</title>
</head>
<body>
<style type="text/css"><!--
#msg dl.meta { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; }
#msg dl.meta dt { float: left; width: 6em; font-weight: bold; }
#msg dt:after { content:':';}
#msg dl, #msg dt, #msg ul, #msg li, #header, #footer, #logmsg { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; }
#msg dl a { font-weight: bold}
#msg dl a:link { color:#fc3; }
#msg dl a:active { color:#ff0; }
#msg dl a:visited { color:#cc6; }
h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; }
#msg pre { overflow: auto; background: #ffc; border: 1px #fa0 solid; padding: 6px; }
#logmsg { background: #ffc; border: 1px #fa0 solid; padding: 1em 1em 0 1em; }
#logmsg p, #logmsg pre, #logmsg blockquote { margin: 0 0 1em 0; }
#logmsg p, #logmsg li, #logmsg dt, #logmsg dd { line-height: 14pt; }
#logmsg h1, #logmsg h2, #logmsg h3, #logmsg h4, #logmsg h5, #logmsg h6 { margin: .5em 0; }
#logmsg h1:first-child, #logmsg h2:first-child, #logmsg h3:first-child, #logmsg h4:first-child, #logmsg h5:first-child, #logmsg h6:first-child { margin-top: 0; }
#logmsg ul, #logmsg ol { padding: 0; list-style-position: inside; margin: 0 0 0 1em; }
#logmsg ul { text-indent: -1em; padding-left: 1em; }#logmsg ol { text-indent: -1.5em; padding-left: 1.5em; }
#logmsg > ul, #logmsg > ol { margin: 0 0 1em 0; }
#logmsg pre { background: #eee; padding: 1em; }
#logmsg blockquote { border: 1px solid #fa0; border-left-width: 10px; padding: 1em 1em 0 1em; background: white;}
#logmsg dl { margin: 0; }
#logmsg dt { font-weight: bold; }
#logmsg dd { margin: 0; padding: 0 0 0.5em 0; }
#logmsg dd:before { content:'\00bb';}
#logmsg table { border-spacing: 0px; border-collapse: collapse; border-top: 4px solid #fa0; border-bottom: 1px solid #fa0; background: #fff; }
#logmsg table th { text-align: left; font-weight: normal; padding: 0.2em 0.5em; border-top: 1px dotted #fa0; }
#logmsg table td { text-align: right; border-top: 1px dotted #fa0; padding: 0.2em 0.5em; }
#logmsg table thead th { text-align: center; border-bottom: 1px solid #fa0; }
#logmsg table th.Corner { text-align: left; }
#logmsg hr { border: none 0; border-top: 2px dashed #fa0; height: 1px; }
#header, #footer { color: #fff; background: #636; border: 1px #300 solid; padding: 6px; }
#patch { width: 100%; }
#patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;}
#patch .propset h4, #patch .binary h4 {margin:0;}
#patch pre {padding:0;line-height:1.2em;margin:0;}
#patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;}
#patch .propset .diff, #patch .binary .diff {padding:10px 0;}
#patch span {display:block;padding:0 10px;}
#patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;}
#patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;}
#patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;}
#patch .lines, .info {color:#888;background:#fff;}
--></style>
<div id="msg">
<dl class="meta">
<dt>Revision</dt> <dd><a href="https://trac.macports.org/changeset/128499">128499</a></dd>
<dt>Author</dt> <dd>cal@macports.org</dd>
<dt>Date</dt> <dd>2014-11-22 05:27:38 -0800 (Sat, 22 Nov 2014)</dd>
</dl>
<h3>Log Message</h3>
<pre>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.</pre>
<h3>Modified Paths</h3>
<ul>
<li><a href="#trunkbasesrccregistryentryc">trunk/base/src/cregistry/entry.c</a></li>
<li><a href="#trunkbasesrccregistrysqlc">trunk/base/src/cregistry/sql.c</a></li>
<li><a href="#trunkbasesrcregistry20entryobjc">trunk/base/src/registry2.0/entryobj.c</a></li>
<li><a href="#trunkbasesrcregistry20fileobjc">trunk/base/src/registry2.0/fileobj.c</a></li>
<li><a href="#trunkbasesrcregistry20registrytcl">trunk/base/src/registry2.0/registry.tcl</a></li>
</ul>
</div>
<div id="patch">
<h3>Diff</h3>
<a id="trunkbasesrccregistryentryc"></a>
<div class="modfile"><h4>Modified: trunk/base/src/cregistry/entry.c (128498 => 128499)</h4>
<pre class="diff"><span>
<span class="info">--- trunk/base/src/cregistry/entry.c 2014-11-22 13:08:38 UTC (rev 128498)
+++ trunk/base/src/cregistry/entry.c 2014-11-22 13:27:38 UTC (rev 128499)
</span><span class="lines">@@ -783,8 +783,8 @@
</span><span class="cx"> reg_registry* reg = entry->reg;
</span><span class="cx"> int result = 1;
</span><span class="cx"> sqlite3_stmt* stmt = NULL;
</span><del>- char* insert = "INSERT INTO registry.files (id, path, mtime, active) "
- "VALUES (?, ?, 0, 0)";
</del><ins>+ char* insert = "INSERT INTO registry.files (id, path, active) "
+ "VALUES (?, ?, 0)";
</ins><span class="cx"> if ((sqlite3_prepare_v2(reg->db, insert, -1, &stmt, NULL) == SQLITE_OK)
</span><span class="cx"> && (sqlite3_bind_int64(stmt, 1, entry->id) == SQLITE_OK)) {
</span><span class="cx"> int i;
</span></span></pre></div>
<a id="trunkbasesrccregistrysqlc"></a>
<div class="modfile"><h4>Modified: trunk/base/src/cregistry/sql.c (128498 => 128499)</h4>
<pre class="diff"><span>
<span class="info">--- trunk/base/src/cregistry/sql.c 2014-11-22 13:08:38 UTC (rev 128498)
+++ trunk/base/src/cregistry/sql.c 2014-11-22 13:27:38 UTC (rev 128499)
</span><span class="lines">@@ -129,43 +129,64 @@
</span><span class="cx">
</span><span class="cx"> /* metadata table */
</span><span class="cx"> "CREATE TABLE registry.metadata (key UNIQUE, value)",
</span><del>- "INSERT INTO registry.metadata (key, value) VALUES ('version', '1.201')",
</del><ins>+ "INSERT INTO registry.metadata (key, value) VALUES ('version', '1.202')",
</ins><span class="cx"> "INSERT INTO registry.metadata (key, value) VALUES ('created', strftime('%s', 'now'))",
</span><span class="cx">
</span><span class="cx"> /* ports table */
</span><span class="cx"> "CREATE TABLE registry.ports ("
</span><del>- "id INTEGER PRIMARY KEY, "
- "name TEXT COLLATE NOCASE, portfile TEXT, url TEXT, "
- "location TEXT, epoch INTEGER, version TEXT COLLATE VERSION, "
- "revision INTEGER, variants TEXT, negated_variants TEXT, "
- "state TEXT, date DATETIME, installtype TEXT, archs TEXT, "
- "requested INT, os_platform TEXT, os_major INTEGER, "
- "UNIQUE (name, epoch, version, revision, variants), "
- "UNIQUE (url, epoch, version, revision, variants)"
</del><ins>+ "id INTEGER PRIMARY KEY"
+ ", name TEXT COLLATE NOCASE"
+ ", portfile TEXT"
+ ", location TEXT"
+ ", epoch INTEGER"
+ ", version TEXT COLLATE VERSION"
+ ", revision INTEGER"
+ ", variants TEXT"
+ ", negated_variants TEXT"
+ ", state TEXT"
+ ", date DATETIME"
+ ", installtype TEXT"
+ ", archs TEXT"
+ ", requested INTEGER"
+ ", os_platform TEXT"
+ ", os_major INTEGER"
+ ", UNIQUE (name, epoch, version, revision, variants)"
</ins><span class="cx"> ")",
</span><del>- "CREATE INDEX registry.port_name ON ports "
</del><ins>+ "CREATE INDEX registry.port_name ON ports"
</ins><span class="cx"> "(name, epoch, version, revision, variants)",
</span><del>- "CREATE INDEX registry.port_url ON ports "
- "(url, epoch, version, revision, variants)",
- "CREATE INDEX registry.port_state ON ports (state)",
</del><ins>+ "CREATE INDEX registry.port_state ON ports(state)",
</ins><span class="cx">
</span><span class="cx"> /* file map */
</span><del>- "CREATE TABLE registry.files (id INTEGER, path TEXT, actual_path TEXT, "
- "active INT, mtime DATETIME, md5sum TEXT, editable INT, binary BOOL, "
- "FOREIGN KEY(id) REFERENCES ports(id))",
- "CREATE INDEX registry.file_port ON files (id)",
</del><ins>+ "CREATE TABLE registry.files ("
+ "id INTEGER"
+ ", path TEXT"
+ ", actual_path TEXT"
+ ", active INTEGER"
+ ", binary BOOL"
+ ", FOREIGN KEY(id) REFERENCES ports(id))",
+ "CREATE INDEX registry.file_port ON files(id)",
</ins><span class="cx"> "CREATE INDEX registry.file_path ON files(path)",
</span><span class="cx"> "CREATE INDEX registry.file_actual ON files(actual_path)",
</span><span class="cx">
</span><span class="cx"> /* dependency map */
</span><del>- "CREATE TABLE registry.dependencies (id INTEGER, name TEXT, variants TEXT, "
- "FOREIGN KEY(id) REFERENCES ports(id))",
- "CREATE INDEX registry.dep_name ON dependencies (name)",
</del><ins>+ "CREATE TABLE registry.dependencies ("
+ "id INTEGER"
+ ", name TEXT"
+ ", variants TEXT"
+ ", FOREIGN KEY(id) REFERENCES ports(id))",
+ "CREATE INDEX registry.dep_id ON dependencies(id)",
+ "CREATE INDEX registry.dep_name ON dependencies(name)",
</ins><span class="cx">
</span><span class="cx"> /* portgroups table */
</span><del>- "CREATE TABLE registry.portgroups (id INTEGER, "
- "name TEXT, version TEXT COLLATE VERSION, size INTEGER, sha256 TEXT, "
- "FOREIGN KEY(id) REFERENCES ports(id))",
</del><ins>+ "CREATE TABLE registry.portgroups ("
+ "id INTEGER"
+ ", name TEXT"
+ ", version TEXT COLLATE VERSION"
+ ", size INTEGER"
+ ", sha256 TEXT"
+ ", FOREIGN KEY(id) REFERENCES ports(id))",
+ "CREATE INDEX registry.portgroup_id ON portgroups(id)",
+ "CREATE INDEX registry.portgroup_open ON portgroups(id, name, version, size, sha256)",
</ins><span class="cx">
</span><span class="cx"> "COMMIT",
</span><span class="cx"> NULL
</span><span class="lines">@@ -335,9 +356,13 @@
</span><span class="cx"> from Tcl, so here we'll just flag that it needs to be done. */
</span><span class="cx"> static char* version_1_2_queries[] = {
</span><span class="cx"> /* portgroups table */
</span><del>- "CREATE TABLE registry.portgroups (id INTEGER, "
- "name TEXT, version TEXT COLLATE VERSION, size INTEGER, sha256 TEXT, "
- "FOREIGN KEY(id) REFERENCES ports(id))",
</del><ins>+ "CREATE TABLE registry.portgroups ("
+ "id INTEGER"
+ ", name TEXT"
+ ", version TEXT COLLATE VERSION"
+ ", size INTEGER"
+ ", sha256 TEXT"
+ ", FOREIGN KEY(id) REFERENCES ports(id))",
</ins><span class="cx">
</span><span class="cx"> "UPDATE registry.metadata SET value = '1.200' WHERE key = 'version'",
</span><span class="cx">
</span><span class="lines">@@ -380,6 +405,179 @@
</span><span class="cx"> continue;
</span><span class="cx"> }
</span><span class="cx">
</span><ins>+ if (sql_version(NULL, -1, version, -1, "1.202") < 0) {
+ static char* version_1_202_queries[] = {
+ "CREATE INDEX registry.portgroup_id ON portgroups(id)",
+ "CREATE INDEX registry.portgroup_open ON portgroups(id, name, version, size, sha256)",
+ "CREATE INDEX registry.dep_id ON dependencies(id)",
+
+ /*
+ * SQLite doesn't support ALTER TABLE DROP CONSTRAINT or ALTER
+ * TABLE DROP COLUMN, so we're doing the manual way to remove
+ * UNIQUE(url, epoch, version, revision, variants) and the url
+ * column.
+ */
+
+ /* Create a temporary table */
+ "CREATE TEMPORARY TABLE mp_ports_backup ("
+ "id INTEGER PRIMARY KEY"
+ ", name TEXT COLLATE NOCASE"
+ ", portfile CLOB"
+ ", location TEXT"
+ ", epoch INTEGER"
+ ", version TEXT COLLATE VERSION"
+ ", revision INTEGER"
+ ", variants TEXT"
+ ", negated_variants TEXT"
+ ", state TEXT"
+ ", date DATETIME"
+ ", installtype TEXT"
+ ", archs TEXT"
+ ", requested INT"
+ ", os_platform TEXT"
+ ", os_major INTEGER"
+ ", UNIQUE(name, epoch, version, revision, variants))",
+
+ /* Copy all data into the temporary table */
+ "INSERT INTO mp_ports_backup "
+ "SELECT"
+ " id"
+ ", name"
+ ", portfile"
+ ", location"
+ ", epoch"
+ ", version"
+ ", revision"
+ ", variants"
+ ", negated_variants"
+ ", state"
+ ", date"
+ ", installtype"
+ ", archs"
+ ", requested"
+ ", os_platform"
+ ", os_major"
+ " FROM registry.ports",
+
+ /* Drop the original table and re-create it with the new structure */
+ "DROP TABLE registry.ports",
+ "CREATE TABLE registry.ports ("
+ "id INTEGER PRIMARY KEY"
+ ", name TEXT COLLATE NOCASE"
+ ", portfile CLOB"
+ ", location TEXT"
+ ", epoch INTEGER"
+ ", version TEXT COLLATE VERSION"
+ ", revision INTEGER"
+ ", variants TEXT"
+ ", negated_variants TEXT"
+ ", state TEXT"
+ ", date DATETIME"
+ ", installtype TEXT"
+ ", archs TEXT"
+ ", requested INT"
+ ", os_platform TEXT"
+ ", os_major INTEGER"
+ ", UNIQUE(name, epoch, version, revision, variants))",
+
+ /* Copy all data back from temporary table */
+ "INSERT INTO registry.ports "
+ "SELECT"
+ " id"
+ ", name"
+ ", portfile"
+ ", location"
+ ", epoch"
+ ", version"
+ ", revision"
+ ", variants"
+ ", negated_variants"
+ ", state"
+ ", date"
+ ", installtype"
+ ", archs"
+ ", requested"
+ ", os_platform"
+ ", os_major"
+ " FROM mp_ports_backup",
+
+ /* Re-create indices that have been dropped with the table */
+ "CREATE INDEX registry.port_name ON ports(name, epoch, version, revision, variants)",
+ "CREATE INDEX registry.port_state ON ports(state)",
+
+ /* Remove temporary table */
+ "DROP TABLE mp_ports_backup",
+
+ /*
+ * SQLite doesn't support ALTER TABLE DROP COLUMN, so we're
+ * doing the manual way to remove files.md5sum, files.mtime,
+ * files.editable.
+ */
+
+ /* Create a temporary table */
+ "CREATE TEMPORARY TABLE mp_files_backup ("
+ "id INTEGER"
+ ", path TEXT"
+ ", actual_path TEXT"
+ ", active INTEGER"
+ ", binary BOOL"
+ ")",
+
+ /* Copy all data into the temporary table */
+ "INSERT INTO mp_files_backup "
+ "SELECT"
+ " id"
+ ", path"
+ ", actual_path"
+ ", active"
+ ", binary"
+ " FROM registry.files",
+
+ /* Drop the original table and re-create it with the new structure */
+ "DROP TABLE registry.files",
+ "CREATE TABLE registry.files ("
+ "id INTEGER"
+ ", path TEXT"
+ ", actual_path TEXT"
+ ", active INTEGER"
+ ", binary BOOL"
+ ", FOREIGN KEY(id) REFERENCES ports(id))",
+
+ /* Copy all data back from temporary table */
+ "INSERT INTO registry.files "
+ "SELECT"
+ " id"
+ ", path"
+ ", actual_path"
+ ", active"
+ ", binary"
+ " FROM mp_files_backup",
+
+ /* Re-create indices that have been dropped with the table */
+ "CREATE INDEX registry.file_port ON files(id)",
+ "CREATE INDEX registry.file_path ON files(path)",
+ "CREATE INDEX registry.file_actual ON files(actual_path)",
+
+ /* Remove temporary table */
+ "DROP TABLE mp_files_backup",
+
+ /* Update version and commit */
+ "UPDATE registry.metadata SET value = '1.202' WHERE key = 'version'",
+ "COMMIT",
+ NULL
+ };
+
+ sqlite3_finalize(stmt);
+ stmt = NULL;
+ if (!do_queries(db, version_1_202_queries, errPtr)) {
+ rollback_db(db);
+ return 0;
+ }
+
+ did_update = 1;
+ continue;
+ }
+
</ins><span class="cx"> /* add new versions here, but remember to:
</span><span class="cx"> * - finalize the version query statement and set stmt to NULL
</span><span class="cx"> * - do _not_ use "BEGIN" in your query list, since a transaction has
</span></span></pre></div>
<a id="trunkbasesrcregistry20entryobjc"></a>
<div class="modfile"><h4>Modified: trunk/base/src/registry2.0/entryobj.c (128498 => 128499)</h4>
<pre class="diff"><span>
<span class="info">--- trunk/base/src/registry2.0/entryobj.c 2014-11-22 13:08:38 UTC (rev 128498)
+++ trunk/base/src/registry2.0/entryobj.c 2014-11-22 13:27:38 UTC (rev 128499)
</span><span class="lines">@@ -42,7 +42,6 @@
</span><span class="cx"> const char* entry_props[] = {
</span><span class="cx"> "name",
</span><span class="cx"> "portfile",
</span><del>- "url",
</del><span class="cx"> "location",
</span><span class="cx"> "epoch",
</span><span class="cx"> "version",
</span><span class="lines">@@ -419,7 +418,6 @@
</span><span class="cx"> /* keys */
</span><span class="cx"> { "name", entry_obj_prop },
</span><span class="cx"> { "portfile", entry_obj_prop },
</span><del>- { "url", entry_obj_prop },
</del><span class="cx"> { "location", entry_obj_prop },
</span><span class="cx"> { "epoch", entry_obj_prop },
</span><span class="cx"> { "version", entry_obj_prop },
</span></span></pre></div>
<a id="trunkbasesrcregistry20fileobjc"></a>
<div class="modfile"><h4>Modified: trunk/base/src/registry2.0/fileobj.c (128498 => 128499)</h4>
<pre class="diff"><span>
<span class="info">--- trunk/base/src/registry2.0/fileobj.c 2014-11-22 13:08:38 UTC (rev 128498)
+++ trunk/base/src/registry2.0/fileobj.c 2014-11-22 13:27:38 UTC (rev 128499)
</span><span class="lines">@@ -44,9 +44,6 @@
</span><span class="cx"> "path",
</span><span class="cx"> "actual_path",
</span><span class="cx"> "active",
</span><del>- "mtime",
- "md5sum",
- "editable",
</del><span class="cx"> "binary",
</span><span class="cx"> NULL
</span><span class="cx"> };
</span><span class="lines">@@ -110,9 +107,6 @@
</span><span class="cx"> { "path", file_obj_prop },
</span><span class="cx"> { "actual_path", file_obj_prop },
</span><span class="cx"> { "active", file_obj_prop },
</span><del>- { "mtime", file_obj_prop },
- { "md5sum", file_obj_prop },
- { "editable", file_obj_prop },
</del><span class="cx"> { "binary", file_obj_prop },
</span><span class="cx"> { NULL, NULL }
</span><span class="cx"> };
</span></span></pre></div>
<a id="trunkbasesrcregistry20registrytcl"></a>
<div class="modfile"><h4>Modified: trunk/base/src/registry2.0/registry.tcl (128498 => 128499)</h4>
<pre class="diff"><span>
<span class="info">--- trunk/base/src/registry2.0/registry.tcl 2014-11-22 13:08:38 UTC (rev 128498)
+++ trunk/base/src/registry2.0/registry.tcl 2014-11-22 13:27:38 UTC (rev 128499)
</span><span class="lines">@@ -311,7 +311,7 @@
</span><span class="cx"> # 2: gid
</span><span class="cx"> # 3: mode
</span><span class="cx"> # 4: size
</span><del>-# 5: md5 checksum information
</del><ins>+# 5: md5 checksum information (deprecated, will always be "MD5 ($filename) NONE")
</ins><span class="cx"> #
</span><span class="cx"> # fname a path to a given file.
</span><span class="cx"> # return a 6-tuple about this file.
</span><span class="lines">@@ -320,16 +320,7 @@
</span><span class="cx"> # (we won't store the md5 of the target of links since it's meaningless
</span><span class="cx"> # and $statvar(mode) tells us that links are links).
</span><span class="cx"> if {![catch {file lstat $fname statvar}]} {
</span><del>- if {[::file isfile $fname] && [::file type $fname] ne "link"} {
- if {[catch {md5 file $fname} md5sum] == 0} {
- # Create a line that matches md5(1)'s output
- # for backwards compatibility
- set line "MD5 ($fname) = $md5sum"
- return [list $fname $statvar(uid) $statvar(gid) $statvar(mode) $statvar(size) $line]
- }
- } else {
- return [list $fname $statvar(uid) $statvar(gid) $statvar(mode) $statvar(size) "MD5 ($fname) NONE"]
- }
</del><ins>+ return [list $fname $statvar(uid) $statvar(gid) $statvar(mode) $statvar(size) "MD5 ($fname) NONE"]
</ins><span class="cx"> }
</span><span class="cx"> return {}
</span><span class="cx"> }
</span></span></pre>
</div>
</div>
</body>
</html>