Damaged database?

[Adam Dershowitz]

On Feb 24, 2013, at 3:11 AM, Rainer Müller wrote:

> On 2013-02-23 20:23, Bradley Giesbrecht wrote:
>>> What happens if you query the sqlite registry db directly?
>>> 
>>> $ sqlite3 /opt/local/var/macports/registry/registry.db .schema
>>> $ sqlite3 /opt/local/var/macports/registry/registry.db "select load_extension('/opt/local/share/macports/sqlite/macports.sqlext');select t1.id, t1.name from ports t1 where t1.name = 'gst-plugins-base'"
>>> $ sqlite3 /opt/local/var/macports/registry/registry.db "select load_extension('/opt/local/share/macports/sqlite/macports.sqlext');select t1.id, t1.name, group_concat(distinct t2.name) as "deps", group_concat(distinct t3.path) as "files" from ports t1 join dependencies t2 on t1.id = t2.id join files t3 on t1.id = t3.id where t1.name = 'gst-plugins-base';"
> 
> The macports.sqlext is not installed on a normal end-user installation.
> However, it can be built easily from the source code:
> 
>  $ svn co
> https://svn.macports.org/repository/macports/tags/release_2_1_3/base/src/cregistry/
>  $ cd cregistry
>  $ make macports.sqlext
> 
> Rainer

Thanks.  Turns out, that I had to first be in /opt/local/var/db/dports/sources/rsync.rsync.darwinports.org_dpupdate1/base/src/ or the make failed (it tries to include ../../Mk/macports.autoconf.mk).  And then I needed sudo for each.  But, it then built fine, and it seems that the extension then runs fine from the location where I built it.

Then, here is what the above commands report:
dersh$sqlite3 /opt/local/var/macports/registry/registry.db .schema
CREATE TABLE dependencies (id INTEGER, name TEXT, variants TEXT, FOREIGN KEY(id) REFERENCES ports(id));
CREATE TABLE 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 TABLE metadata (key UNIQUE, value);
CREATE TABLE ports (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT COLLATE NOCASE, portfile CLOB, 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));
CREATE INDEX dep_name ON dependencies (name);
CREATE INDEX file_actual ON files(actual_path);
CREATE INDEX file_binary ON files(binary);
CREATE INDEX file_path ON files(path);
CREATE INDEX file_port ON files (id);
CREATE INDEX port_name ON ports (name, epoch, version, revision, variants);
CREATE INDEX port_state ON ports (state);
CREATE INDEX port_url ON ports (url, epoch, version, revision, variants);

dersh$ sqlite3 /opt/local/var/macports/registry/registry.db "select load_extension('/opt/local/var/db/dports/sources/rsync.rsync.darwinports.org_dpupdate1/base/src/cregistry/macports.sqlext');select t1.id, t1.name from ports t1 where t1.name = 'gst-plugins-base'"

Error: no such collation sequence: VERSION

dersh$ sqlite3 /opt/local/var/macports/registry/registry.db "select load_extension('/opt/local/var/db/dports/sources/rsync.rsync.darwinports.org_dpupdate1/base/src/cregistry/macports.sqlext');select t1.id, t1.name, group_concat(distinct t2.name) as "deps", group_concat(distinct t3.path) as "files" from ports t1 join dependencies t2 on t1.id = t2.id join files t3 on t1.id = t3.id where t1.name = 'gst-plugins-base';"

Error: database disk image is malformed

Brad, as to your question about the version of sqlite3, we are probably using the same one, because it is the one installed by macports.

I tried the above both the the restore from about a month ago, and with my most recent one, and got the same results with each one.  And, I have done a bunch of upgrades in that period, so most of the database is OK.  

Do the above look correct?  Any thoughts about how it might be possible to repair this?


Thanks,

--Adam