Module: PgPower::Tools

Extended by:
Tools
Included in:
Tools
Defined in:
lib/pg_power/tools.rb

Overview

Provides utility methods to work with PostgreSQL databases. Usage:

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

Instance Method Summary collapse

Instance Method Details

#create_schema(schema_name) ⇒ Object

Creates PostgreSQL schema



16
17
18
19
 
# File 'lib/pg_power/tools.rb', line 16

def create_schema(schema_name)
  sql = %{CREATE SCHEMA "#{schema_name}"}
  connection.execute sql
end

#create_view(view_name, view_definition) ⇒ Object

Creates PostgreSQL view

Parameters:

  • view_name (String, Symbol)
  • view_definition (String) 


45
46
47
48
 
# File 'lib/pg_power/tools.rb', line 45

def create_view(view_name, view_definition)
  sql = "CREATE VIEW #{view_name} AS #{view_definition}"
  connection.execute sql
end

#drop_schema(schema_name) ⇒ Object

Drops PostgreSQL schema



22
23
24
25
 
# File 'lib/pg_power/tools.rb', line 22

def drop_schema(schema_name)
  sql = %{DROP SCHEMA "#{schema_name}"}
  connection.execute sql
end

#drop_view(view_name) ⇒ Object

Drops PostgreSQL view

Parameters:

  • view_name (String, Symbol) 


52
53
54
55
 
# File 'lib/pg_power/tools.rb', line 52

def drop_view(view_name)
  sql = "DROP VIEW #{view_name}"
  connection.execute sql
end

#move_table_to_schema(table, new_schema) ⇒ Object

Move table to another schema without loosing data, indexes or constraints.

Parameters:

  • table (String) —

    table name (schema prefix is allowed)

  • new_schema (String) —

    schema where table should be moved to



36
37
38
39
40
 
# File 'lib/pg_power/tools.rb', line 36

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 ⇒ Object

Returns an array of existing schemas.



28
29
30
31
 
# File 'lib/pg_power/tools.rb', line 28

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) ⇒ Array[String, String]

Extract schema name and table name from qualified table name

Parameters:

  • table_name (String, Symbol) —

    table name

Returns:

  • (Array[String, String]) —

    schema and table



76
77
78
79
80
 
# File 'lib/pg_power/tools.rb', line 76

def to_schema_and_table(table_name)
  table, schema = table_name.to_s.split(".", 2).reverse
  schema ||= "public"
  [schema, table]
end

#views ⇒ Object

Returns an array of existing, non system views.



58
59
60
61
62
63
64
65
 
# File 'lib/pg_power/tools.rb', line 58

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