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_if_not_exists(schema_name) ⇒ void

Note:

Supports PostgreSQL 9.3+

This method returns an undefined value.

Create a schema if it does not exist yet.



21
22
23
24
25
26
 
# 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) ⇒ Object

Creates PostgreSQL view

Parameters:

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


53
54
55
56
 
# 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) ⇒ void

This method returns an undefined value.

Ensure schema does not exists.



31
32
33
 
# 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) ⇒ Object

Drops PostgreSQL view

Parameters:

  • view_name (String, Symbol) 


60
61
62
63
 
# 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) ⇒ 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



44
45
46
47
48
 
# 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 ⇒ Object

Returns an array of existing schemas.



36
37
38
39
 
# 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) ⇒ 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



84
85
86
87
88
 
# 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 ⇒ Object

Returns an array of existing, non system views.



66
67
68
69
70
71
72
73
 
# 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