install/setup_postgres.sh

   1 #!/bin/bash
   2 # The contents of this file are subject to the Common Public Attribution
   3 # License Version 1.0. (the "License"); you may not use this file except in
   4 # compliance with the License. You may obtain a copy of the License at
   5 # http://code.reddit.com/LICENSE. The License is based on the Mozilla Public
   6 # License Version 1.1, but Sections 14 and 15 have been added to cover use of
   7 # software over a computer network and provide for limited attribution for the
   8 # Original Developer. In addition, Exhibit A has been modified to be consistent
   9 # with Exhibit B.
  10 #
  11 # Software distributed under the License is distributed on an "AS IS" basis,
  12 # WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
  13 # the specific language governing rights and limitations under the License.
  14 #
  15 # The Original Code is reddit.
  16 #
  17 # The Original Developer is the Initial Developer.  The Initial Developer of
  18 # the Original Code is reddit Inc.
  19 #
  20 # All portions of the code written by reddit are Copyright (c) 2006-2015 reddit
  21 # Inc. All Rights Reserved.
  22 ###############################################################################
  23
  24 # load configuration
  25 RUNDIR=$(dirname $0)
  26 source $RUNDIR/install.cfg
  27
  28 ###############################################################################
  29 # Configure PostgreSQL
  30 ###############################################################################
  31 SQL="SELECT COUNT(1) FROM pg_catalog.pg_database WHERE datname = 'reddit';"
  32 IS_DATABASE_CREATED=$(sudo -u postgres psql -t -c "$SQL")
  33
  34 if [ $IS_DATABASE_CREATED -ne 1 ]; then
  35     cat <<PGSCRIPT | sudo -u postgres psql
  36 CREATE DATABASE reddit WITH ENCODING = 'utf8' TEMPLATE template0 LC_COLLATE='en_US.utf8' LC_CTYPE='en_US.utf8';
  37 CREATE USER reddit WITH PASSWORD 'password';
  38 PGSCRIPT
  39 fi
  40
  41 sudo -u postgres psql reddit <<FUNCTIONSQL
  42 create or replace function hot(ups integer, downs integer, date timestamp with time zone) returns numeric as \$\$
  43     select round(cast(log(greatest(abs(\$1 - \$2), 1)) * sign(\$1 - \$2) + (date_part('epoch', \$3) - 1134028003) / 45000.0 as numeric), 7)
  44 \$\$ language sql immutable;
  45
  46 create or replace function score(ups integer, downs integer) returns integer as \$\$
  47     select \$1 - \$2
  48 \$\$ language sql immutable;
  49
  50 create or replace function controversy(ups integer, downs integer) returns float as \$\$
  51     select CASE WHEN \$1 <= 0 or \$2 <= 0 THEN 0
  52                 WHEN \$1 > \$2 THEN power(\$1 + \$2, cast(\$2 as float) / \$1)
  53                 ELSE power(\$1 + \$2, cast(\$1 as float) / \$2)
  54            END;
  55 \$\$ language sql immutable;
  56
  57 create or replace function ip_network(ip text) returns text as \$\$
  58     select substring(\$1 from E'[\\d]+\.[\\d]+\.[\\d]+')
  59 \$\$ language sql immutable;
  60
  61 create or replace function base_url(url text) returns text as \$\$
  62     select substring(\$1 from E'(?i)(?:.+?://)?(?:www[\\d]*\\.)?([^#]*[^#/])/?')
  63 \$\$ language sql immutable;
  64
  65 create or replace function domain(url text) returns text as \$\$
  66     select substring(\$1 from E'(?i)(?:.+?://)?(?:www[\\d]*\\.)?([^#/]*)/?')
  67 \$\$ language sql immutable;
  68 FUNCTIONSQL