Module: Skiima::Db::Helpers::Postgresql

Defined in:
lib/skiima/db/helpers/postgresql.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Instance Attribute Details

#local_tz ⇒ Object

Returns the value of attribute local_tz.



5
6
7
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 5

def local_tz
  @local_tz
end

#version ⇒ Object

Returns the value of attribute version.



6
7
8
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 6

def version
  @version
end

Instance Method Details

#check_psql_version ⇒ Object 



22
23
24
25
26
27
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 22

def check_psql_version
  @version = postgresql_version
  if @version < 80200
    raise "Your version of PostgreSQL (#{postgresql_version}) is too old, please upgrade!"
  end
end

#column_names(table_name) ⇒ Object

necessary 2 override? class PgColumn

attr_accessor :name, :deafult, :type, :null
def initialize(name, default = nil, type = nil, null = true)
  @name, @default, @type, @null = name, default, type, null
end

def to_s
  # to be implemented
end

end



173
174
175
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 173

def column_names(table_name)
  columns(table_name).map(&:name)
end

#database_exists?(name, opts = {}) ⇒ Boolean

Returns:

  • (Boolean) 


34
35
36
37
38
39
40
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 34

def database_exists?(name, opts = {})
  query(Skiima.interpolate_sql('&', <<-SQL, { :database => name }))[0][0].to_i > 0
  SELECT COUNT(*)
  FROM pg_databases pdb
  WHERE pdb.datname = '&database'
  SQL
end

#drop(type, name, opts = {}) ⇒ Object

queries



117
118
119
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 117

def drop(type, name, opts = {})
  send("drop_#{type}", name, opts) if supported_objects.include? type.to_sym
end

#drop_database(name, opts = {}) ⇒ Object 



121
122
123
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 121

def drop_database(name, opts = {})
  "DROP DATABASE IF EXISTS #{name}"
end

#drop_index(name, opts = {}) ⇒ Object 



144
145
146
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 144

def drop_index(name, opts = {})
  "DROP INDEX IF EXISTS #{name}"
end

#drop_rule(name, opts = {}) ⇒ Object 



137
138
139
140
141
142
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 137

def drop_rule(name, opts = {})
  target = opts[:attr].first if opts[:attr]
  raise "requires target object" unless target

  "DROP RULE IF EXISTS #{name} ON #{target}"
end

#drop_schema(name, opts = {}) ⇒ Object 



125
126
127
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 125

def drop_schema(name, opts = {})
  "DROP SCHEMA IF EXISTS #{name}"
end

#drop_table(name, opts = {}) ⇒ Object 



129
130
131
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 129

def drop_table(name, opts = {})
  "DROP TABLE IF EXISTS #{name}"
end

#drop_view(name, opts = {}) ⇒ Object 



133
134
135
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 133

def drop_view(name, opts = {})
  "DROP VIEW IF EXISTS #{name}"
end

#execute(sql, name = nil) ⇒ Object 



8
9
10
11
12
13
14
15
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 8

def execute(sql, name = nil)
  # relying on formatting inside the file is precisely what i wanted to avoid...
  results = sql.split(/^--={4,}/).map do |line|
    super(line)
  end

  results.first
end

#index_exists?(name, opts = {}) ⇒ Boolean

Returns:

  • (Boolean) 


99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 99

def index_exists?(name, opts = {})
  target = opts[:attr] ? opts[:attr][0] : nil
  raise "requires target object" unless target
  schema, index = ::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::Utils.extract_schema_and_table(name.to_s)
  vars = { :index => index,
           :target => target,
           :schema => ((schema && !schema.empty?) ? "'#{schema}'" : "ANY (current_schemas(false))") }

  query(Skiima.interpolate_sql('&', <<-SQL, vars))[0][0].to_i > 0
  SELECT COUNT(*)
  FROM pg_indexes pgr
  WHERE pgr.indexname = '&index'
  AND pgr.tablename = '&target'
  AND pgr.schemaname = &schema
  SQL
end

#object_exists?(type, name, opts = {}) ⇒ Boolean

schema matchers

Returns:

  • (Boolean) 


30
31
32
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 30

def object_exists?(type, name, opts = {})
  send("#{type}_exists?", name, opts) if supported_objects.include? type.to_sym
end

#rule_exists?(name, opts = {}) ⇒ Boolean

Returns:

  • (Boolean) 


82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 82

def rule_exists?(name, opts = {})
  target = opts[:attr] ? opts[:attr][0] : nil
  raise "requires target object" unless target
  schema, rule = ::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::Utils.extract_schema_and_table(name.to_s)
  vars = { :rule => rule,
           :target => target,
           :schema => ((schema && !schema.empty?) ? "'#{schema}'" : "ANY (current_schemas(false))") }

  query(Skiima.interpolate_sql('&', <<-SQL, vars))[0][0].to_i > 0
  SELECT COUNT(*)
  FROM pg_rules pgr
  WHERE pgr.rulename = '&rule'
  AND pgr.tablename = '&target'
  AND pgr.schemaname = &schema
  SQL
end

#schema_exists?(name, opts = {}) ⇒ Boolean

Returns:

  • (Boolean) 


42
43
44
45
46
47
48
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 42

def schema_exists?(name, opts = {})
  query(Skiima.interpolate_sql('&', <<-SQL, { :schema => name }))[0][0].to_i > 0
  SELECT COUNT(*)
  FROM pg_namespace
  WHERE nspname = '&schema'
  SQL
end

#supported_objects ⇒ Object

skiima



18
19
20
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 18

def supported_objects
  [:database, :schema, :table, :view, :rule, :index]
end

#table_exists?(name, opts = {}) ⇒ Boolean

Returns:

  • (Boolean) 


50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 50

def table_exists?(name, opts = {})
  schema, table = ::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::Utils.extract_schema_and_table(name.to_s)
  vars = { :table => table,
           :schema => ((schema && !schema.empty?) ? "'#{schema}'" : "ANY (current_schemas(false))") }

  vars.inspect

  query(Skiima.interpolate_sql('&', <<-SQL, vars))[0][0].to_i > 0
  SELECT COUNT(*)
  FROM pg_class c
  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind in ('r')
  AND c.relname = '&table'
  AND n.nspname = &schema
  SQL
end

#view_exists?(name, opts = {}) ⇒ Boolean

Returns:

  • (Boolean) 


67
68
69
70
71
72
73
74
75
76
77
78
79
80
 
# File 'lib/skiima/db/helpers/postgresql.rb', line 67

def view_exists?(name, opts = {})
  schema, view = ::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::Utils.extract_schema_and_table(name.to_s)
  vars = { :view => view,
           :schema => ((schema && !schema.empty?) ? "'#{schema}'" : "ANY (current_schemas(false))") }

  query(Skiima.interpolate_sql('&', <<-SQL, vars))[0][0].to_i > 0
  SELECT COUNT(*)
  FROM pg_class c
  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind in ('v')
  AND c.relname = '&view'
  AND n.nspname = &schema
  SQL
end