1	/*
     2	Copyright 2012 The Perkeep Authors.
     3	
     4	Licensed under the Apache License, Version 2.0 (the "License");
     5	you may not use this file except in compliance with the License.
     6	You may obtain a copy of the License at
     7	
     8	     http://www.apache.org/licenses/LICENSE-2.0
     9	
    10	Unless required by applicable law or agreed to in writing, software
    11	distributed under the License is distributed on an "AS IS" BASIS,
    12	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    13	See the License for the specific language governing permissions and
    14	limitations under the License.
    15	*/
    16	
    17	package postgres
    18	
    19	import (
    20		"strconv"
    21	
    22		"perkeep.org/pkg/sorted"
    23	)
    24	
    25	const requiredSchemaVersion = 2
    26	
    27	func SchemaVersion() int {
    28		return requiredSchemaVersion
    29	}
    30	
    31	func SQLCreateTables() []string {
    32		return []string{
    33			`CREATE TABLE IF NOT EXISTS rows (
    34	 k VARCHAR(` + strconv.Itoa(sorted.MaxKeySize) + `) NOT NULL PRIMARY KEY,
    35	 v VARCHAR(` + strconv.Itoa(sorted.MaxValueSize) + `))`,
    36	
    37			`CREATE TABLE IF NOT EXISTS meta (
    38	 metakey VARCHAR(255) NOT NULL PRIMARY KEY,
    39	 value VARCHAR(255) NOT NULL)`,
    40		}
    41	}
    42	
    43	func SQLDefineReplace() []string {
    44		return []string{
    45			// The first 3 statements here are a work around that allows us to issue
    46			// the "CREATE LANGUAGE plpsql;" statement only if the language doesn't
    47			// already exist.
    48			`CREATE OR REPLACE FUNCTION create_language_plpgsql() RETURNS INTEGER AS
    49	$$
    50	CREATE LANGUAGE plpgsql;
    51	SELECT 1;
    52	$$
    53	LANGUAGE SQL;`,
    54	
    55			`SELECT CASE WHEN NOT
    56	(
    57		SELECT  TRUE AS exists
    58		FROM    pg_language
    59		WHERE   lanname = 'plpgsql'
    60		UNION
    61		SELECT  FALSE AS exists
    62		ORDER BY exists DESC
    63		LIMIT 1
    64	)
    65	THEN
    66	    create_language_plpgsql()
    67	ELSE
    68		0
    69	END AS plpgsql_created;`,
    70	
    71			`DROP FUNCTION create_language_plpgsql();`,
    72	
    73			`CREATE OR REPLACE FUNCTION replaceinto(key TEXT, value TEXT) RETURNS VOID AS
    74	$$
    75	BEGIN
    76	    LOOP
    77	        UPDATE rows SET v = value WHERE k = key;
    78	        IF found THEN
    79	            RETURN;
    80	        END IF;
    81	        BEGIN
    82	            INSERT INTO rows(k,v) VALUES (key, value);
    83	            RETURN;
    84	        EXCEPTION WHEN unique_violation THEN
    85	        END;
    86	    END LOOP;
    87	END;
    88	$$
    89	LANGUAGE plpgsql;`,
    90			`CREATE OR REPLACE FUNCTION replaceintometa(key TEXT, val TEXT) RETURNS VOID AS
    91	$$
    92	BEGIN
    93	    LOOP
    94	        UPDATE meta SET value = val WHERE metakey = key;
    95	        IF found THEN
    96	            RETURN;
    97	        END IF;
    98	        BEGIN
    99	            INSERT INTO meta(metakey,value) VALUES (key, val);
   100	            RETURN;
   101	        EXCEPTION WHEN unique_violation THEN
   102	        END;
   103	    END LOOP;
   104	END;
   105	$$
   106	LANGUAGE plpgsql;`,
   107		}
   108	}