class Postgres

Public Class Methods

new(dbconfig) click to toggle source

Initialize Postgres instance

@param [Hash] dbconfig Database config

# File lib/pgcp/postgres.rb, line 9
def initialize(dbconfig)
  @dbconfig = dbconfig
end

Public Instance Methods

column_definitions(schema_name, table_name) click to toggle source
# File lib/pgcp/postgres.rb, line 137
  def column_definitions(schema_name, table_name)
    with_connection do |conn|
      sql = <<-SQL.strip_heredoc
        SELECT 
          c.relname, a.attname AS column_name,
          pg_catalog.format_type(a.atttypid, a.atttypmod) as type,
          case 
            when a.attnotnull
          then 'NOT NULL' 
          else 'NULL' 
          END as not_null 
        FROM pg_class c,
         pg_attribute a,
         pg_type t,
         pg_namespace n
         WHERE c.relname = '#{table_name}'
         AND n.nspname = '#{schema_name}'
         AND a.attnum > 0
         AND a.attrelid = c.oid
         AND a.atttypid = t.oid
         AND c.relnamespace = n.oid
       ORDER BY a.attnum
      SQL

      rs = conn.exec sql

      rs.values.map do |col|
        {name: col[1], type: col[2], null: col[3]}
      end
    end
  end
copy_from_file(schema_name, table_name, csv_file, options={}) click to toggle source
# File lib/pgcp/postgres.rb, line 122
def copy_from_file(schema_name, table_name, csv_file, options={})
  with_connection do |conn|
    schema_name = conn.escape_string(schema_name)
    table_name = conn.escape_string(table_name)

    conn.copy_data "COPY #{schema_name}.#{table_name} FROM STDIN CSV #{options[:header]?'HEADER':''}" do
      buf = ''
      while csv_file.read(256, buf)
        conn.put_copy_data(buf)
      end
    end

  end
end
create_indexes(schema_name, table_name, indexes) click to toggle source
# File lib/pgcp/postgres.rb, line 243
  def create_indexes(schema_name, table_name, indexes)
    with_connection do |conn|
      indexes.each do |index|
        if index['primary']
          sql = <<-SQL.strip_heredoc
            ALTER TABLE #{schema_name}.#{table_name} ADD PRIMARY KEY (#{index['columns'][0]})
          SQL
        else
          sql = <<-SQL.strip_heredoc
          CREATE #{index['unique'] ? 'UNIQUE': ''} INDEX #{index['name']}
          ON #{schema_name}.#{table_name} (#{index['columns'].join(', ')})
          #{index['where'] ? 'WHERE ' + index['where'] : ''}
          SQL
        end

        conn.exec(sql)
      end
    end
  end
create_table(schema_name, table_name, columns, options={}) click to toggle source
# File lib/pgcp/postgres.rb, line 52
def create_table(schema_name, table_name, columns, options={})
  with_connection do |conn|
    if options[:temporary]
      table_name = conn.escape_string(table_name)
      create_sql = create_table_statement(conn, columns,
                                          table_name,
                                          options)
      conn.transaction do
        conn.exec create_sql
        conn.exec "DROP TABLE IF EXISTS #{table_name}"
      end

      true
    else
      unless _table_exists?(conn, schema_name, table_name)
        create_sql = create_table_statement(conn, columns,
                                            "#{schema_name}.#{table_name}",
                                            options)
        conn.exec create_sql

        true
      end

      false
    end
  end
end
create_table_from_query(query, schema_name, table_name, columns, options={}) click to toggle source
# File lib/pgcp/postgres.rb, line 80
def create_table_from_query(query, schema_name, table_name, columns, options={})
  with_connection do |conn|
    create_sql = create_table_statement(conn, columns,
                                        "#{schema_name}.#{table_name}",
                                        options)
    conn.transaction do
      conn.exec "DROP TABLE IF EXISTS #{schema_name}.#{table_name}"
      conn.exec create_sql
      conn.exec "INSERT INTO #{schema_name}.#{table_name}\n#{query}"
    end
  end
end
drop_table(schema_name, table_name) click to toggle source
# File lib/pgcp/postgres.rb, line 38
  def drop_table(schema_name, table_name)
    with_connection do |conn|
      if _table_exists?(conn, schema_name, table_name)
        fq_table_name = conn.escape_string("#{schema_name}.#{table_name}")

        sql = <<-SQL.strip_heredoc
          DROP TABLE #{fq_table_name}
        SQL

        conn.exec sql
      end
    end
  end
exec(sql, val=[]) click to toggle source
# File lib/pgcp/postgres.rb, line 13
def exec(sql, val=[])
  with_connection do |conn|
    conn.exec sql, val

  end
end
get_create_table_statement(src_schema_name, src_table_name, dest_schema_name=nil, dest_table_name=nil) click to toggle source
# File lib/pgcp/postgres.rb, line 263
def get_create_table_statement(src_schema_name, src_table_name, dest_schema_name=nil, dest_table_name=nil)
  dest_schema_name ||= src_schema_name
  dest_table_name ||= dest_schema_name

  columns = column_definitions(src_schema_name, src_table_name)

  statement = "CREATE TABLE #{dest_schema_name}.#{dest_table_name} (\n"
  columns.each_with_index do |col, index|
    statement << "  #{col[:name]}  #{col[:type]}  #{col[:null]}"
    statement << ',' if index != columns.size - 1
    statement << "\n"
  end
  statement << ");\n"

  statement
end
get_indexes(schema_name, table_name) click to toggle source
# File lib/pgcp/postgres.rb, line 192
def get_indexes(schema_name, table_name)
  idx_names = self.index_names(schema_name, table_name)

  with_connection do |conn|
    idx_names.map do |name|
      index_info = index_info(conn, name, schema_name)
      index_info['name'] = name
      index_info['columns'] = index_column_names conn, index_info['oid']

      index_info
    end
  end
end
hotswap_table(schema_name, src_table_name, dst_table_name) click to toggle source
# File lib/pgcp/postgres.rb, line 93
def hotswap_table(schema_name, src_table_name, dst_table_name)
  with_connection do |conn|
    conn.transaction do
      schema_name = conn.escape_string(schema_name)
      dst_table_name = conn.escape_string(dst_table_name)
      conn.exec "DROP TABLE #{schema_name}.#{dst_table_name}" if _table_exists?(conn, schema_name, dst_table_name)
      conn.exec "ALTER TABLE #{schema_name}.#{src_table_name} RENAME TO #{dst_table_name}"
      #conn.exec "TRUNCATE TABLE #{schema_name}.#{dst_table_name}" if _table_exists?(conn, schema_name, dst_table_name)
      #conn.exec "INSERT INTO #{schema_name}.#{dst_table_name}\n(SELECT * FROM #{schema_name}.#{src_table_name})"
      #conn.exec "DROP TABLE #{schema_name}.#{src_table_name}"
    end

  end
end
index_column_names(conn, oid) click to toggle source
# File lib/pgcp/postgres.rb, line 230
  def index_column_names conn, oid
    sql = <<-SQL.strip_heredoc
    SELECT
         pg_catalog.pg_get_indexdef(A.attrelid, A.attnum, TRUE) AS "column_name"
    FROM pg_catalog.pg_attribute A
    WHERE A.attrelid = $1
      AND A.attnum > 0
      AND NOT A.attisdropped
    ORDER BY A.attnum;
    SQL
    conn.exec(sql, [oid]).map { |row| row['column_name'] }
  end
index_info(conn, index_name, schema_name) click to toggle source
# File lib/pgcp/postgres.rb, line 206
  def index_info(conn, index_name, schema_name)
    sql = <<-SQL.strip_heredoc
    SELECT
        C.oid,
        I.indisunique AS "unique",
        I.indisprimary AS "primary",
        pg_get_expr(I.indpred, I.indrelid) AS "where"
    FROM pg_catalog.pg_class C,
         pg_catalog.pg_namespace N,
         pg_catalog.pg_index I
    WHERE C.relname = '#{index_name}'
      AND C.relnamespace = N.oid
      AND I.indexrelid = C.oid
      AND N.nspname = '#{schema_name}';
    SQL

    rs = conn.exec sql
    rs[0].tap do |info|
      info['unique'] = info['unique'] != 'f'
      info['primary'] = info['primary'] != 'f'
      info['where'] = info['where'][1..-2] if info['where'].present?
    end
  end
index_names(schema_name, table_name) click to toggle source
# File lib/pgcp/postgres.rb, line 169
  def index_names(schema_name, table_name)
    with_connection do |conn|
      sql = <<-SQL.strip_heredoc
        SELECT
            C.relname AS "index_name"
        FROM pg_catalog.pg_class C,
             pg_catalog.pg_namespace N,
             pg_catalog.pg_index I,
             pg_catalog.pg_class C2
        WHERE C.relkind IN ( 'i', '' )
          AND N.oid = C.relnamespace
          AND N.nspname = '#{schema_name}'
          AND I.indexrelid = C.oid
          AND C2.oid = I.indrelid
          AND C2.relname = '#{table_name}';
      SQL

      rs = conn.exec sql

      rs.values.map(&:first)
    end
  end
list_tables(schema_name) click to toggle source
# File lib/pgcp/postgres.rb, line 20
  def list_tables(schema_name)
    with_connection do |conn|
      sql = <<-SQL.strip_heredoc
        SELECT table_name
        FROM information_schema.tables
        WHERE table_type = 'BASE TABLE'
          AND table_schema NOT IN ('pg_catalog', 'information_schema')
          AND table_schema = '#{schema_name}'
        ORDER BY 1
      SQL

      rs = conn.exec sql

      rs.values.map(&:first)
    end

  end
schema_names() click to toggle source
# File lib/pgcp/postgres.rb, line 108
  def schema_names
    with_connection do |conn|
      sql = <<-SQL.strip_heredoc
      SELECT schema_name
      FROM information_schema.schemata
      WHERE schema_name <> 'information_schema'
      AND schema_name NOT LIKE 'pg_%'
      SQL

      rs = conn.exec sql
      rs.values.map(&:first)
    end
  end
table_exist?(schema_name, table_name) click to toggle source
# File lib/pgcp/postgres.rb, line 280
def table_exist?(schema_name, table_name)
  with_connection do |conn|
    _table_exists?(conn, schema_name, table_name)
  end
end

Private Instance Methods

_table_exists?(connection, schema_name, table_name) click to toggle source
# File lib/pgcp/postgres.rb, line 327
  def _table_exists?(connection, schema_name, table_name)
    sql = <<-SQL.strip_heredoc
        SELECT
          count(table_name)
        FROM
          information_schema.tables
        WHERE
          table_schema <> 'pg_catalog'
          AND table_schema <> 'information_schema'
          AND table_schema !~ '^pg_toast'
          AND table_schema = '#{connection.escape_string(schema_name)}'
          AND table_name = '#{connection.escape_string(table_name)}'
        GROUP BY
          table_schema,table_name;
    SQL

    res = connection.exec(sql)

    res.values.size > 0
  end
column_line(column) click to toggle source
# File lib/pgcp/postgres.rb, line 300
def column_line(column)
  name, data_type, nullable = column.symbolize_keys.values_at(:column_name, :data_type, :is_nullable)

  # default type to varchar
  data_type ||= "VARCHAR(1000)"

  line_tokens = ["\"#{name}\""]
  line_tokens << data_type
  line_tokens << (nullable ? '' : 'NOT NULL')

  line_tokens
    .select { |token| token != '' }
    .join " "
end
create_table_statement(connection, columns, table_name, options={}) click to toggle source
# File lib/pgcp/postgres.rb, line 315
def create_table_statement(connection, columns, table_name, options={})
  statement = "CREATE #{options[:temporary] ? 'TEMPORARY' : ''} TABLE #{connection.escape_string(table_name)} (\n"
  statement << columns
    .map { |column| column_line(column) }
    .map(&:strip)
    .map { |column| connection.escape_string(column) }
    .join(",\n")
  statement << "\n);"

  statement
end
get_connection() click to toggle source
# File lib/pgcp/postgres.rb, line 296
def get_connection
  PG::Connection.connect(@dbconfig)
end
with_connection(&block) click to toggle source
# File lib/pgcp/postgres.rb, line 288
def with_connection(&block)
  conn = get_connection

  block.call(conn)
ensure
  conn.close if not conn.nil?
end