module PgPower::Tools
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"]
Public Instance Methods
create_schema(schema_name)
click to toggle source
Creates PostgreSQL schema
# 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)
click to toggle source
Creates PostgreSQL view @param [String, Symbol] view_name @param [String] view_definition
# 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)
click to toggle source
Drops PostgreSQL schema
# 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)
click to toggle source
Drops PostgreSQL view @param [String, Symbol] view_name
# 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)
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_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()
click to toggle source
Returns an array of existing schemas.
# 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)
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_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()
click to toggle source
Returns an array of existing, non system views.
# 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
Private Instance Methods
connection()
click to toggle source
Return database connections
# File lib/pg_power/tools.rb, line 68 def connection ActiveRecord::Base.connection end