Class: PgConn::SchemaMethods

Inherits:
Object
  • Object
show all
Defined in:
lib/pg_conn/schema_methods.rb

Overview

Schema methods

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(conn) ⇒ SchemaMethods

Returns a new instance of SchemaMethods.



7
8
9
 
# File 'lib/pg_conn/schema_methods.rb', line 7

def initialize(conn)
  @conn = conn
end

Instance Attribute Details

#conn ⇒ Object (readonly)

Returns the value of attribute conn.



5
6
7
 
# File 'lib/pg_conn/schema_methods.rb', line 5

def conn
  @conn
end

Instance Method Details

#clean!(*schemas, exclude: []) ⇒ Object

Empty all tables in the given schema(s)



50
51
52
53
54
55
56
57
58
 
# File 'lib/pg_conn/schema_methods.rb', line 50

def clean!(*schemas, exclude: [])
  conn.session.triggers(false) {
    schemas.flatten.each { |schema|
      self.list_tables(schema, exclude: exclude).each { |table|
        conn.execute "delete from #{schema}.#{table}"
      }
    }
  }
end

#column_type(schema, relation = nil, column) ⇒ Object

Return type of the given column or nil if not found



122
123
124
 
# File 'lib/pg_conn/schema_methods.rb', line 122

def column_type(schema, relation = nil, column)
  conn.tuples(column_list_type_query(schema, relation, column)).first&.last
end

#create(schema, authorization: nil) ⇒ Object

Create a new schema. The authorization option can be used to set the owner of the schema



22
23
24
25
26
 
# File 'lib/pg_conn/schema_methods.rb', line 22

def create(schema, authorization: nil)
  authorization_clause = authorization ? "authorization #{authorization}" : nil
  stmt = ["create schema", schema, authorization_clause].compact.join(" ")
  conn.execute stmt
end

#drop(schema, cascade: false) ⇒ Object

Drop schema



29
30
31
32
33
34
35
36
 
# File 'lib/pg_conn/schema_methods.rb', line 29

def drop(schema, cascade: false)
  if cascade
    conn.execute "drop schema if exists #{schema} cascade"
  else
    conn.execute "drop schema if exists #{schema}"
  end
  true
end

#empty!(schema, exclude: []) ⇒ Object

Hollow out a schema by dropping all tables and views (but still not functions and procedures TODO). TODO: Swap names with #clean!



40
41
42
43
44
45
46
47
 
# File 'lib/pg_conn/schema_methods.rb', line 40

def empty!(schema, exclude: [])
  self.list_tables(schema, exclude: exclude).each { |table|
    conn.execute "drop table if exists #{schema}.#{table} cascade"
  }
  self.list_views(schema, exclude: exclude).each { |view|
    conn.execute "drop view if exists #{schema}.#{view} cascade"
  }
end

#exist?(schema) ⇒ Boolean

Return true if schema exists

Returns:

  • (Boolean) 


12
13
14
15
16
17
18
 
# File 'lib/pg_conn/schema_methods.rb', line 12

def exist?(schema)
  conn.exist? %(
      select 1
      from information_schema.schemata
      where schema_name = '#{schema}'
  )
end

#exist_column?(schema, relation, column) ⇒ Boolean

Return true if the column exists

Returns:

  • (Boolean) 


87
88
89
 
# File 'lib/pg_conn/schema_methods.rb', line 87

def exist_column?(schema, relation, column)
  conn.exist? column_exist_query(schema, relation, column)
end

#exist_function(schema, function, signature) ⇒ Object

Raises:

  • (NotImplementedError) 


126
127
128
 
# File 'lib/pg_conn/schema_methods.rb', line 126

def exist_function(schema, function, signature)
  raise NotImplementedError
end

#exist_relation?(schema, relation) ⇒ Boolean

Returns true if relation (table or view) exists

Returns:

  • (Boolean) 


72
73
74
 
# File 'lib/pg_conn/schema_methods.rb', line 72

def exist_relation?(schema, relation)
  conn.exist? relation_exist_query(schema, relation)
end

#exist_table?(schema, table) ⇒ Boolean

Return true if table exists

Returns:

  • (Boolean) 


77
78
79
 
# File 'lib/pg_conn/schema_methods.rb', line 77

def exist_table?(schema, table)
  conn.exist? relation_exist_query(schema, table, kind: %w(r f))
end

#exist_view?(schema, view) ⇒ Boolean

Return true if view exists

Returns:

  • (Boolean) 


82
83
84
 
# File 'lib/pg_conn/schema_methods.rb', line 82

def exist_view?(schema, view)
  conn.exist? relation_exist_query(schema, view, kind: %w(v m))
end

#get_serial(schema, table, next: false) ⇒ Object

Get the current serial value for the table. Returns nil if the serial has not been used. If :next is true, the next value will be returned



141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
 
# File 'lib/pg_conn/schema_methods.rb', line 141

def get_serial(schema, table, next: false)
  uid = "#{schema}.#{table}"
  next_option = binding.local_variable_get(:next) # because 'next' is a keyword

  seq = sequence(schema, table) or raise ArgumentError, "Table #{uid} does not have a sequence"
  value = conn.value %(
    select
      case is_called
        when true then last_value
        else null
      end as "value"
    from
      #{seq}
  )
  if next_option
    value&.+(1) || 1
  else
    value
  end
end

#list(all: false, exclude: []) ⇒ Object

List schemas. Built-in schemas are not listed unless the :all option is true. The :exclude option can be used to exclude named schemas



62
63
64
65
66
67
68
69
 
# File 'lib/pg_conn/schema_methods.rb', line 62

def list(all: false, exclude: [])
  conn.values(%(
      select schema_name
      from information_schema.schemata
  )).select { |schema|
    !exclude.include?(schema) && (all ? true : schema !~ /^pg_/ && schema != "information_schema")
  }
end

#list_column_types(schema, relation = nil) ⇒ Object

Like #list_columns but returns a tuple of column UID and column type



117
118
119
 
# File 'lib/pg_conn/schema_methods.rb', line 117

def list_column_types(schema, relation = nil)
  conn.tuples column_list_type_query(schema, relation)
end

#list_columns(schema, relation = nil) ⇒ Object

Return a list of columns. If relation is defined, only columns from that relation are listed. Columns are returned as fully qualified names (eg. "schema.relation.column")



112
113
114
 
# File 'lib/pg_conn/schema_methods.rb', line 112

def list_columns(schema, relation = nil)
  conn.values column_list_query(schema, relation)
end

#list_functions(schema, function = nil) ⇒ Object

Raises:

  • (NotImplementedError) 


130
131
132
 
# File 'lib/pg_conn/schema_methods.rb', line 130

def list_functions(schema, function = nil)
  raise NotImplementedError
end

#list_relations(schema, exclude: []) ⇒ Object

Return list of relations in the schema



95
96
97
 
# File 'lib/pg_conn/schema_methods.rb', line 95

def list_relations(schema, exclude: [])
  conn.values relation_list_query(schema, exclude: exclude)
end

#list_tables(schema, exclude: []) ⇒ Object

Return list of tables in the schema



100
101
102
 
# File 'lib/pg_conn/schema_methods.rb', line 100

def list_tables(schema, exclude: [])
  conn.values relation_list_query(schema, exclude: exclude, kind: %w(r f))
end

#list_views(schema, exclude: []) ⇒ Object

Return list of view in the schema



105
106
107
 
# File 'lib/pg_conn/schema_methods.rb', line 105

def list_views(schema, exclude: [])
  conn.values relation_list_query(schema, exclude: exclude, kind: %w(v m))
end

#sequence(schema, table) ⇒ Object

Return name of the table's sequence (if any)



135
136
137
 
# File 'lib/pg_conn/schema_methods.rb', line 135

def sequence(schema, table)
  conn.value "select pg_get_serial_sequence('#{schema}.#{table}', 'id')"
end

#set_serial(schema, table, value) ⇒ Object

Set current serial value for the table. The sequence is marked unused if value is nil



163
164
165
166
167
168
169
170
171
 
# File 'lib/pg_conn/schema_methods.rb', line 163

def set_serial(schema, table, value)
  uid = "#{schema}.#{table}"
  seq = sequence(schema, table) or raise ArgumentError, "Table #{uid} does not have a sequence"
  if value
    conn.execute "select setval('#{seq}', #{value})"
  else
    conn.execute "select setval('#{seq}', 1, false)"
  end
end

#update_serial(schema, table) ⇒ Object 



173
174
175
176
177
 
# File 'lib/pg_conn/schema_methods.rb', line 173

def update_serial(schema, table)
  uid = "#{schema}.#{table}"
  max_id = conn.value "select max(id) from #{uid}"
  set_serial(schema, table, max_id)
end