Module: PgSaurus::Tools

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

Overview

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"]

Instance Method Summary collapse

Instance Method Details

#create_schema(schema_name) ⇒ Object

Creates PostgreSQL schema



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

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

#create_schema_if_not_exists(schema_name) ⇒ void

Note:

CREATE SCHEMA IF EXISTS appeared only in PostgreSQL 9.3 It is not used here for backward compatibility.

This method returns an undefined value.

Create a schema if it does not exist yet.



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

def create_schema_if_not_exists(schema_name)
  unless schemas.include?(schema_name.to_s)
    create_schema(schema_name)
  end
end

#create_view(view_name, view_definition) ⇒ Object

Creates PostgreSQL view

Parameters:

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


67
68
69
70
 
# File 'lib/pg_saurus/tools.rb', line 67

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



44
45
46
47
 
# File 'lib/pg_saurus/tools.rb', line 44

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

#drop_schema_if_exists(schema_name) ⇒ void

This method returns an undefined value.

Ensure schema does not exists.



37
38
39
40
41
 
# File 'lib/pg_saurus/tools.rb', line 37

def drop_schema_if_exists(schema_name)
  if schemas.include?(schema_name.to_s)
    drop_schema(schema_name)
  end
end

#drop_view(view_name) ⇒ Object

Drops PostgreSQL view

Parameters:

  • view_name (String, Symbol) 


74
75
76
77
 
# File 'lib/pg_saurus/tools.rb', line 74

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



58
59
60
61
62
 
# File 'lib/pg_saurus/tools.rb', line 58

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.



50
51
52
53
 
# File 'lib/pg_saurus/tools.rb', line 50

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



98
99
100
101
102
 
# File 'lib/pg_saurus/tools.rb', line 98

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.



80
81
82
83
84
85
86
87
 
# File 'lib/pg_saurus/tools.rb', line 80

def views
  sql = "  SELECT table_schema, table_name, view_definition\n  FROM INFORMATION_SCHEMA.views\n  WHERE table_schema NOT IN ('pg_catalog','information_schema')\n  SQL\n  connection.execute sql\nend\n"