view release on metacpan or  search on metacpan

config/sql/dbinit_Config.pm  view on Meta::CPAN

# *************************************************************************
# Copyright (c) 2014-2017, SUSE LLC
#
# All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are met:
#
# 1. Redistributions of source code must retain the above copyright notice,
# this list of conditions and the following disclaimer.
#
# 2. Redistributions in binary form must reproduce the above copyright
# notice, this list of conditions and the following disclaimer in the
# documentation and/or other materials provided with the distribution.
#
# 3. Neither the name of SUSE LLC nor the names of its contributors may be
# used to endorse or promote products derived from this software without
# specific prior written permission.
#
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
# ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
# LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
# CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
# SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
# INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
# CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
# ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
# POSSIBILITY OF SUCH DAMAGE.
# *************************************************************************
#
# sql/dbinit_Config.pm
#
# database initialization SQL

#
# DBINIT_CONNECT_SUPERUSER
# DBINIT_CONNECT_SUPERAUTH
#
# These should be overrided in Dochazka_SiteConfig.pm with real
# superuser credentials (but only for testing - do not put production
# credentials in any configuration file!!!!)
#
set( 'DBINIT_CONNECT_SUPERUSER', 'postgres' );
set( 'DBINIT_CONNECT_SUPERAUTH', 'bogus_password_to_be_overrided' );

#
# DBINIT_CREATE
# 
#  A list of SQL statements that are executed when the database is first
#  created, to set up the table structure, etc. -- see the create_tables
#  subroutine in REST.pm 
#
set( 'DBINIT_CREATE', [

    # miscellaneous settings

    q/SET client_min_messages=WARNING/,

    # generalized (utility) functions used in multiple datamodel classes

    q#CREATE OR REPLACE FUNCTION round_time(timestamptz)
      RETURNS TIMESTAMPTZ AS $$
          SELECT date_trunc('hour', $1) + INTERVAL '5 min' * ROUND(date_part('minute', $1) / 5.0)
      $$ LANGUAGE sql IMMUTABLE
    #,

    q#COMMENT ON FUNCTION round_time(timestamptz) IS 
      'Round a single timestamp value to the nearest 5 minutes'#,

    q#CREATE OR REPLACE FUNCTION parens(tstzrange)
      RETURNS RECORD AS $$
        DECLARE
            left_paren     text;
            right_paren    text;
        BEGIN
            IF lower_inc($1) THEN
                left_paren := '['::text;
            ELSE
                left_paren := '('::text;
            END IF;
            IF upper_inc($1) THEN
                right_paren := ']'::text;
            ELSE
                right_paren := ')'::text;
            END IF;
        RETURN (left_paren, right_paren);
        END;
      $$ LANGUAGE plpgsql#,

    q/CREATE OR REPLACE FUNCTION overlaps(tstzrange, tstzrange)
      RETURNS boolean AS $$
        BEGIN
            IF $1 && $2 THEN
                RETURN 't'::boolean;
            ELSE
                RETURN 'f'::boolean;
            END IF;
        END;
      $$ LANGUAGE plpgsql/,

    q/COMMENT ON FUNCTION overlaps(tstzrange, tstzrange) IS