Treat CR2 the same as NEF.

[pr0n] / sql / pr0n.sql

diff --git a/sql/pr0n.sql b/sql/pr0n.sql
index 11af9cac9ea0d17711ade54e01e8a8bf08b57e85..5f89f6c91e03c8bf62e76684a3718612d8c53218 100644 (file)
--- a/sql/pr0n.sql
+++ b/sql/pr0n.sql
@@ -3,7 +3,6 @@ CREATE TABLE events (
     "date" character varying NOT NULL,
     name character varying NOT NULL,
     vhost character varying NOT NULL,
     "date" character varying NOT NULL,
     name character varying NOT NULL,
     vhost character varying NOT NULL,

-    last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

 
     PRIMARY KEY (vhost, event)
 );
 
     PRIMARY KEY (vhost, event)
 );


@@ -13,9 +12,10 @@ CREATE TABLE last_picture_cache (
    vhost varchar NOT NULL,
    event varchar NOT NULL,
    last_picture timestamp without time zone,
    vhost varchar NOT NULL,
    event varchar NOT NULL,
    last_picture timestamp without time zone,

+   last_update timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

 
    PRIMARY KEY (vhost,event),
 
    PRIMARY KEY (vhost,event),

-   FOREIGN KEY (vhost,event) REFERNECES events(vhost,event)
+   FOREIGN KEY (vhost,event) REFERENCES events(vhost,event)

 );
 
 CREATE TABLE images (
 );
 
 CREATE TABLE images (


@@ -23,12 +23,14 @@ CREATE TABLE images (
     vhost character varying NOT NULL,
     event character varying NOT NULL,
     filename character varying NOT NULL,
     vhost character varying NOT NULL,
     event character varying NOT NULL,
     filename character varying NOT NULL,

-    width integer DEFAULT -1 NOT NULL,
-    height integer DEFAULT -1 NOT NULL,
+    width integer,
+    height integer,

     uploadedby character varying NOT NULL,
     "date" timestamp without time zone,
     takenby character varying NOT NULL,
     selected boolean DEFAULT false,
     uploadedby character varying NOT NULL,
     "date" timestamp without time zone,
     takenby character varying NOT NULL,
     selected boolean DEFAULT false,

+    model character varying,
+    lens character varying,

 
     FOREIGN KEY (vhost,event) REFERENCES events (vhost,event)
 );
 
     FOREIGN KEY (vhost,event) REFERENCES events (vhost,event)
 );


@@ -39,12 +41,14 @@ CREATE TABLE deleted_images (
     vhost character varying,
     event character varying NOT NULL,
     filename character varying NOT NULL,
     vhost character varying,
     event character varying NOT NULL,
     filename character varying NOT NULL,

-    width integer DEFAULT -1 NOT NULL,
-    height integer DEFAULT -1 NOT NULL,
+    width integer,
+    height integer,

     uploadedby character varying,
     "date" timestamp without time zone,
     takenby character varying NOT NULL,
     uploadedby character varying,
     "date" timestamp without time zone,
     takenby character varying NOT NULL,

-    selected boolean
+    selected boolean,
+    model character varying,
+    lens character varying

 );
 
 CREATE TABLE fake_files (
 );
 
 CREATE TABLE fake_files (


@@ -71,15 +75,26 @@ CREATE TABLE users (
     vhost character varying NOT NULL
 );
 
     vhost character varying NOT NULL
 );
 

+-- Mainly used for manual queries -- usually too slow to be very useful
+-- for web views in the long run.

 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 );
+CREATE INDEX exif_info_key ON exif_info ( key );
+CLUSTER exif_info_key ON exif_info;
+
+CREATE TABLE tags (
+    image integer NOT NULL REFERENCES images (id) ON DELETE CASCADE,
+    tag varchar NOT NULL,
+
+    PRIMARY KEY ( image, tag )
+);
+CREATE INDEX tags_tag ON tags ( tag );

 
 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;


@@ -89,4 +104,5 @@ GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE images TO pr0n;
 GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE shadow_files TO pr0n;
 GRANT SELECT ON TABLE users TO pr0n;
 GRANT SELECT,INSERT,DELETE ON TABLE exif_info TO pr0n;
 GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE shadow_files TO pr0n;
 GRANT SELECT ON TABLE users TO pr0n;
 GRANT SELECT,INSERT,DELETE ON TABLE exif_info TO pr0n;

+GRANT SELECT,INSERT,DELETE ON TABLE tags TO pr0n;

 GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE last_picture_cache TO pr0n;
 GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE last_picture_cache TO pr0n;