Rename "tag" to "key" in exif_info table, and make a small SQL script to do

just that.

diff --git a/perl/Sesse/pr0n/Common.pm b/perl/Sesse/pr0n/Common.pm
index 073996f187bc06783d1dc8ac8c27019e6974d691..d73df38a381788ff31b090f708d43f5b20f9d566 100644 (file)
--- a/perl/Sesse/pr0n/Common.pm
+++ b/perl/Sesse/pr0n/Common.pm
@@ -216,7 +216,7 @@ sub update_image_info {
                        undef, $id)
                        or die "Couldn't delete old EXIF information in SQL: $!";
 
                        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) {
                        or die "Couldn't prepare inserting EXIF information: $!";
 
                for my $key (keys %$info) {

diff --git a/perl/Sesse/pr0n/Index.pm b/perl/Sesse/pr0n/Index.pm
index 6c7bc8079c7ef0bdc622f1f2c73ae37f63e3a31d..39c5da43f97000fef1a96b401ebd3239efece9e5 100644 (file)
--- a/perl/Sesse/pr0n/Index.pm
+++ b/perl/Sesse/pr0n/Index.pm
@@ -115,17 +115,17 @@ sub handler {
 
                if ($model eq '') {
                        # no defined model
 
                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 {
                } 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
                }
        
                # 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 {
                } 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 )";

                }
        }
 
                }
        }
 


@@ -220,9 +220,9 @@ sub handler {
                                        COUNT(*) AS num
                                FROM ( SELECT * FROM images WHERE vhost=? $where ) i
                                        LEFT JOIN exif_info model ON i.id=model.image
                                        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: $!";
                                GROUP BY 1,2
                                ORDER BY 1,2")
                                or die "Couldn't prepare to find equipment: $!";

diff --git a/sql/pr0n.sql b/sql/pr0n.sql
index 5eacc40c23253ea8cf26030540ac00841e2a03a3..c65d1caa57d55064eddcc7d5e1771b78f3e20734 100644 (file)
--- a/sql/pr0n.sql
+++ b/sql/pr0n.sql
@@ -73,14 +73,14 @@ CREATE TABLE users (
 
 CREATE TABLE exif_info (
     image integer NOT NULL REFERENCES images (id) ON DELETE CASCADE,
 
 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,
 
     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;
 
 GRANT INSERT ON TABLE deleted_images TO pr0n;
 GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE events TO pr0n;

diff --git a/sql/upgrade-v2.50.sql b/sql/upgrade-v2.50.sql
new file mode 100644 (file)
index 0000000..6f740fe
--- /dev/null
+++ b/sql/upgrade-v2.50.sql
@@ -0,0 +1,7 @@
+--
+-- 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;
+