module PgSaurus::Tools

Provides utility methods to work with PostgreSQL databases. Usage:

PgSaurus::Tools.create_schema "services"  # => create new PG schema "services"
PgSaurus::Tools.create_schema "nets"
PgSaurus::Tools.drop_schema "services"    # => remove the schema
PgSaurus::Tools.schemas                   # => ["public", "information_schema", "nets"]
PgSaurus::Tools.move_table_to_schema :computers, :nets
PgSaurus::Tools.create_view view_name, view_definition # => creates new DB view
PgSaurus::Tools.drop_view view_name       # => removes the view
PgSaurus::Tools.views                     # => ["x_view", "y_view", "z_view"]

Public Instance Methods

create_schema_if_not_exists(schema_name) click to toggle source

Create a schema if it does not exist yet.

@note

Supports PostgreSQL 9.3+

@return [void]

# File lib/pg_saurus/tools.rb, line 21
def create_schema_if_not_exists(schema_name)
  unless schemas.include?(schema_name.to_s)
    sql = %{CREATE SCHEMA "#{schema_name}"}
    connection.execute sql
  end
end
create_view(view_name, view_definition) click to toggle source

Creates PostgreSQL view @param [String, Symbol] view_name @param [String] view_definition

# File lib/pg_saurus/tools.rb, line 53
def create_view(view_name, view_definition)
  sql = "CREATE VIEW #{view_name} AS #{view_definition}"
  connection.execute sql
end
drop_schema_if_exists(schema_name) click to toggle source

Ensure schema does not exists.

@return [void]

# File lib/pg_saurus/tools.rb, line 31
def drop_schema_if_exists(schema_name)
  connection.drop_schema(schema_name, if_exists: true)
end
drop_view(view_name) click to toggle source

Drops PostgreSQL view @param [String, Symbol] view_name

# File lib/pg_saurus/tools.rb, line 60
def drop_view(view_name)
  sql = "DROP VIEW #{view_name}"
  connection.execute sql
end
move_table_to_schema(table, new_schema) click to toggle source

Move table to another schema without loosing data, indexes or constraints. @param [String] table table name (schema prefix is allowed) @param [String] new_schema schema where table should be moved to

# File lib/pg_saurus/tools.rb, line 44
def move_table_to_schema(table, new_schema)
  schema, table = to_schema_and_table(table)
  sql = %{ALTER TABLE "#{schema}"."#{table}" SET SCHEMA "#{new_schema}"}
  connection.execute sql
end
schemas() click to toggle source

Returns an array of existing schemas.

# File lib/pg_saurus/tools.rb, line 36
def schemas
  sql = "SELECT nspname FROM pg_namespace WHERE nspname !~ '^pg_.*' order by nspname"
  connection.query(sql).flatten
end
to_schema_and_table(table_name) click to toggle source

Extract schema name and table name from qualified table name @param [String, Symbol] table_name table name @return [Array[String, String]] schema and table

# File lib/pg_saurus/tools.rb, line 84
def to_schema_and_table(table_name)
  table, schema = table_name.to_s.split(".", 2).reverse
  schema ||= "public"
  [schema, table]
end
views() click to toggle source

Returns an array of existing, non system views.

# File lib/pg_saurus/tools.rb, line 66
    def views
      sql = <<-SQL
      SELECT table_schema, table_name, view_definition
      FROM INFORMATION_SCHEMA.views
      WHERE table_schema NOT IN ('pg_catalog','information_schema')
      SQL
      connection.execute sql
    end

Private Instance Methods

connection() click to toggle source

Return database connections

# File lib/pg_saurus/tools.rb, line 76
def connection
  ActiveRecord::Base.connection
end