add mapquest layer back

[syj.git] / scripts / schema.postgres.sql

diff --git a/scripts/schema.postgres.sql b/scripts/schema.postgres.sql
index 5877f8c21f705f51db5c82ec0801d8cd913b4ee2..bcbfef3e07c364f34854338fc2718d7113c8f189 100644 (file)
--- a/scripts/schema.postgres.sql
+++ b/scripts/schema.postgres.sql
@@ -10,8 +10,7 @@ CREATE TABLE users (
     pseudo VARCHAR(20) UNIQUE NOT NULL CHECK (pseudo ~ '^[a-zA-Z0-9_.]+$'),
     email VARCHAR(320) UNIQUE NOT NULL,
     lang VARCHAR(3),
-    creation_time TIMESTAMP NOT NULL DEFAULT NOW(),
-    creation_addr INET NOT NULL
+    creation_time TIMESTAMP NOT NULL DEFAULT NOW()
 );
 
 ---
@@ -101,27 +100,11 @@ DROP TABLE IF EXISTS paths CASCADE;
 CREATE TABLE paths (
     id INTEGER PRIMARY KEY DEFAULT nextval('paths_id_seq'),
     geom GEOGRAPHY (LINESTRING, 4326) NOT NULL,
-    owner INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
-    title VARCHAR(40),
+    creator INTEGER REFERENCES users ON DELETE SET NULL,
+    creator_ip INET NOT NULL,
+    title VARCHAR(160),
+    last_update TIMESTAMP NOT NULL DEFAULT NOW(),
     urlcomp VARCHAR(20) UNIQUE CHECK (urlcomp ~ '^[a-z][a-zA-Z0-9_]*$') -- ~: matches regular expression; case sensitive
 );
 
--- unique (geom, owner) constraint will not work: first because of postgis
--- #541; then because equality is checked by comparing bbox instead of real
--- geometries. So, we implement that constraint in a trigger
-CREATE OR REPLACE FUNCTION tg_paths_bu() RETURNS TRIGGER AS $$
-DECLARE res INTEGER;
-BEGIN
-    SELECT INTO res COUNT(*) FROM paths WHERE ST_AsBinary(geom) = ST_AsBinary(NEW.geom) AND owner = NEW.owner AND id != NEW.id;
-    IF res >= 1 THEN
-        RAISE 'duplicate key paths_geom_key' using ERRCODE = 'unique_violation';
-    ELSE
-        RETURN NEW;
-    END IF;
-END;
-$$ LANGUAGE PLPGSQL;
-
-DROP TRIGGER IF EXISTS tg_paths_bu ON paths;
-CREATE TRIGGER tg_paths_bu BEFORE INSERT OR UPDATE ON paths FOR EACH ROW EXECUTE PROCEDURE tg_paths_bu();
-
 COMMIT;