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!(schema, exclude: []) ⇒ Object

Empty all tables in the given schema



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

def clean!(schema, exclude: [])
  conn.session.triggers(false) {
    self.list_tables(schema, exclude: exclude).each { |table|
      conn.exec "delete from #{schema}.#{table}"
    }
  }
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.exec 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.exec "drop schema if exists #{schema} cascade"
  else
    conn.exec "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)



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.exec "drop table if exists #{schema}.#{table} cascade"
  }
  self.list_views(schema, exclude: exclude).each { |view|
    conn.exec "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) 


85
86
87
 
# File 'lib/pg_conn/schema_methods.rb', line 85

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

#exist_function(schema, function, signature) ⇒ Object

Raises:

  • (NotImplementedError) 


119
120
121
 
# File 'lib/pg_conn/schema_methods.rb', line 119

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

#exist_relation?(schema, relation) ⇒ Boolean

Returns true if relation (table or view) exists

Returns:

  • (Boolean) 


70
71
72
 
# File 'lib/pg_conn/schema_methods.rb', line 70

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) 


75
76
77
 
# File 'lib/pg_conn/schema_methods.rb', line 75

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) 


80
81
82
 
# File 'lib/pg_conn/schema_methods.rb', line 80

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



134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
 
# File 'lib/pg_conn/schema_methods.rb', line 134

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



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

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



115
116
117
 
# File 'lib/pg_conn/schema_methods.rb', line 115

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”)



110
111
112
 
# File 'lib/pg_conn/schema_methods.rb', line 110

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

#list_functions(schema, function = nil) ⇒ Object

Raises:

  • (NotImplementedError) 


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

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

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

Return list of relations in the schema



93
94
95
 
# File 'lib/pg_conn/schema_methods.rb', line 93

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



98
99
100
 
# File 'lib/pg_conn/schema_methods.rb', line 98

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



103
104
105
 
# File 'lib/pg_conn/schema_methods.rb', line 103

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)



128
129
130
 
# File 'lib/pg_conn/schema_methods.rb', line 128

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

#set_serial(schema, table, value) ⇒ Object

Set the serial value for the table



156
157
158
159
160
161
162
163
164
 
# File 'lib/pg_conn/schema_methods.rb', line 156

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.exec "select setval('#{seq}', #{value})"
  else
    conn.exec "select setval('#{seq}', 1, false)"
  end
end