github.com/slspeek/camlistore_namedsearch@v0.0.0-20140519202248-ed6f70f7721a/pkg/sorted/postgres/dbschema.go (about)

     1  /*
     2  Copyright 2012 The Camlistore 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  const requiredSchemaVersion = 2
    20  
    21  func SchemaVersion() int {
    22  	return requiredSchemaVersion
    23  }
    24  
    25  func SQLCreateTables() []string {
    26  	return []string{
    27  		`CREATE TABLE IF NOT EXISTS rows (
    28   k VARCHAR(255) NOT NULL PRIMARY KEY,
    29   v VARCHAR(255))`,
    30  
    31  		`CREATE TABLE IF NOT EXISTS meta (
    32   metakey VARCHAR(255) NOT NULL PRIMARY KEY,
    33   value VARCHAR(255) NOT NULL)`,
    34  	}
    35  }
    36  
    37  func SQLDefineReplace() []string {
    38  	return []string{
    39  		// The first 3 statements here are a work around that allows us to issue
    40  		// the "CREATE LANGUAGE plpsql;" statement only if the language doesn't
    41  		// already exist.
    42  		`CREATE OR REPLACE FUNCTION create_language_plpgsql() RETURNS INTEGER AS
    43  $$
    44  CREATE LANGUAGE plpgsql;
    45  SELECT 1;
    46  $$
    47  LANGUAGE SQL;`,
    48  
    49  		`SELECT CASE WHEN NOT
    50  (
    51  	SELECT  TRUE AS exists
    52  	FROM    pg_language
    53  	WHERE   lanname = 'plpgsql'
    54  	UNION
    55  	SELECT  FALSE AS exists
    56  	ORDER BY exists DESC
    57  	LIMIT 1
    58  )
    59  THEN
    60      create_language_plpgsql()
    61  ELSE
    62  	0
    63  END AS plpgsql_created;`,
    64  
    65  		`DROP FUNCTION create_language_plpgsql();`,
    66  
    67  		`CREATE OR REPLACE FUNCTION replaceinto(key TEXT, value TEXT) RETURNS VOID AS
    68  $$
    69  BEGIN
    70      LOOP
    71          UPDATE rows SET v = value WHERE k = key;
    72          IF found THEN
    73              RETURN;
    74          END IF;
    75          BEGIN
    76              INSERT INTO rows(k,v) VALUES (key, value);
    77              RETURN;
    78          EXCEPTION WHEN unique_violation THEN
    79          END;
    80      END LOOP;
    81  END;
    82  $$
    83  LANGUAGE plpgsql;`,
    84  		`CREATE OR REPLACE FUNCTION replaceintometa(key TEXT, val TEXT) RETURNS VOID AS
    85  $$
    86  BEGIN
    87      LOOP
    88          UPDATE meta SET value = val WHERE metakey = key;
    89          IF found THEN
    90              RETURN;
    91          END IF;
    92          BEGIN
    93              INSERT INTO meta(metakey,value) VALUES (key, val);
    94              RETURN;
    95          EXCEPTION WHEN unique_violation THEN
    96          END;
    97      END LOOP;
    98  END;
    99  $$
   100  LANGUAGE plpgsql;`,
   101  	}
   102  }