just that.
undef, $id)
or die "Couldn't delete old EXIF information in SQL: $!";
- my $q = $dbh->prepare('INSERT INTO exif_info (image,tag,value) VALUES (?,?,?)')
+ my $q = $dbh->prepare('INSERT INTO exif_info (image,key,value) VALUES (?,?,?)')
or die "Couldn't prepare inserting EXIF information: $!";
for my $key (keys %$info) {
if ($model eq '') {
# no defined model
- $where .= " AND id NOT IN ( SELECT image FROM exif_info WHERE tag='Model' AND TRIM(value)<>'' )";
+ $where .= " AND id NOT IN ( SELECT image FROM exif_info WHERE key='Model' AND TRIM(value)<>'' )";
} else {
- $where .= " AND id IN ( SELECT image FROM exif_info WHERE tag='Model' AND TRIM(value)=$mq )";
+ $where .= " AND id IN ( SELECT image FROM exif_info WHERE key='Model' AND TRIM(value)=$mq )";
}
# This doesn't match 1:1 if there's both lens and lensspec, but it should be OK in practice
if ($lens eq '') {
# no defined lens
- $where .= " AND id NOT IN ( SELECT image FROM exif_info WHERE (tag='Lens' OR tag='LensSpec') AND TRIM(value)<>'' )";
+ $where .= " AND id NOT IN ( SELECT image FROM exif_info WHERE (key='Lens' OR key='LensSpec') AND TRIM(value)<>'' )";
} else {
- $where .= " AND id IN ( SELECT image FROM exif_info WHERE (tag='Lens' OR tag='LensSpec') AND TRIM(value)=$lq )";
+ $where .= " AND id IN ( SELECT image FROM exif_info WHERE (key='Lens' OR key='LensSpec') AND TRIM(value)=$lq )";
}
}
COUNT(*) AS num
FROM ( SELECT * FROM images WHERE vhost=? $where ) i
LEFT JOIN exif_info model ON i.id=model.image
- LEFT JOIN ( SELECT * FROM exif_info WHERE tag='Lens' ) lens ON i.id=lens.image
- LEFT JOIN ( SELECT * FROM exif_info WHERE tag='LensSpec') lens_spec ON i.id=lens_spec.image
- WHERE model.tag='Model'
+ LEFT JOIN ( SELECT * FROM exif_info WHERE key='Lens' ) lens ON i.id=lens.image
+ LEFT JOIN ( SELECT * FROM exif_info WHERE key='LensSpec') lens_spec ON i.id=lens_spec.image
+ WHERE model.key='Model'
GROUP BY 1,2
ORDER BY 1,2")
or die "Couldn't prepare to find equipment: $!";
CREATE TABLE exif_info (
image integer NOT NULL REFERENCES images (id) ON DELETE CASCADE,
- tag varchar NOT NULL,
+ key varchar NOT NULL,
value varchar NOT NULL,
- PRIMARY KEY ( image, tag )
+ PRIMARY KEY ( image, key )
);
-CREATE INDEX exif_info_tag ON exif_info ( tag );
-CLUSTER exif_info_tag ON exif_info;
+CREATE INDEX exif_info_key ON exif_info ( key );
+CLUSTER exif_info_key ON exif_info;
GRANT INSERT ON TABLE deleted_images TO pr0n;
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE events TO pr0n;
--- /dev/null
+--
+-- Upgrades pre-v2.50 databases to 2.50 format -- not many changes, though.
+--
+ALTER TABLE exif_info RENAME COLUMN tag TO key;
+CREATE INDEX exif_info_key ON exif_info ( key );
+CLUSTER exif_info_key ON exif_info;
+