Class: PgSlice::Table

Inherits:
Object
  • Object
show all
Defined in:
lib/pgslice/table.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(schema, name) ⇒ Table

Returns a new instance of Table.



5
6
7
8
 
# File 'lib/pgslice/table.rb', line 5

def initialize(schema, name)
  @schema = schema
  @name = name
end

Instance Attribute Details

#name ⇒ Object (readonly)

Returns the value of attribute name.



3
4
5
 
# File 'lib/pgslice/table.rb', line 3

def name
  @name
end

#schema ⇒ Object (readonly)

Returns the value of attribute schema.



3
4
5
 
# File 'lib/pgslice/table.rb', line 3

def schema
  @schema
end

Instance Method Details

#column_cast(column) ⇒ Object 



91
92
93
94
 
# File 'lib/pgslice/table.rb', line 91

def column_cast(column)
  data_type = execute("SELECT data_type FROM information_schema.columns WHERE table_schema = $1 AND table_name = $2 AND column_name = $3", [schema, name, column])[0]["data_type"]
  data_type == "timestamp with time zone" ? "timestamptz" : "date"
end

#columns ⇒ Object 



18
19
20
 
# File 'lib/pgslice/table.rb', line 18

def columns
  execute("SELECT column_name FROM information_schema.columns WHERE table_schema = $1 AND table_name = $2", [schema, name]).map{ |r| r["column_name"] }
end

#exists? ⇒ Boolean

Returns:

  • (Boolean) 


14
15
16
 
# File 'lib/pgslice/table.rb', line 14

def exists?
  execute("SELECT COUNT(*) FROM pg_catalog.pg_tables WHERE schemaname = $1 AND tablename = $2", [schema, name]).first["count"].to_i > 0
end

#fetch_comment ⇒ Object 



133
134
135
 
# File 'lib/pgslice/table.rb', line 133

def fetch_comment
  execute("SELECT obj_description(#{regclass}) AS comment")[0]
end

#fetch_settings(trigger_name) ⇒ Object

legacy



142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
 
# File 'lib/pgslice/table.rb', line 142

def fetch_settings(trigger_name)
  needs_comment = false
  trigger_comment = fetch_trigger(trigger_name)
  comment = trigger_comment || fetch_comment
  if comment
    field, period, cast, version = comment["comment"].split(",").map { |v| v.split(":").last } rescue []
    version = version.to_i if version
  end

  unless period
    needs_comment = true
    function_def = execute("SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = $1", [trigger_name])[0]
    return [] unless function_def
    function_def = function_def["pg_get_functiondef"]
    sql_format = Helpers::SQL_FORMAT.find { |_, f| function_def.include?("'#{f}'") }
    return [] unless sql_format
    period = sql_format[0]
    field = /to_char\(NEW\.(\w+),/.match(function_def)[1]
  end

  # backwards compatibility with 0.2.3 and earlier (pre-timestamptz support)
  unless cast
    cast = "date"
    # update comment to explicitly define cast
    needs_comment = true
  end

  version ||= trigger_comment ? 1 : 2
  declarative = version > 1

  [period, field, cast, needs_comment, declarative, version]
end

#fetch_trigger(trigger_name) ⇒ Object 



137
138
139
 
# File 'lib/pgslice/table.rb', line 137

def fetch_trigger(trigger_name)
  execute("SELECT obj_description(oid, 'pg_trigger') AS comment FROM pg_trigger WHERE tgname = $1 AND tgrelid = #{regclass}", [trigger_name])[0]
end

#foreign_keys ⇒ Object 



43
44
45
 
# File 'lib/pgslice/table.rb', line 43

def foreign_keys
  execute("SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = #{regclass} AND contype ='f'").map { |r| r["pg_get_constraintdef"] }
end

#index_defs ⇒ Object 



71
72
73
 
# File 'lib/pgslice/table.rb', line 71

def index_defs
  execute("SELECT pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = #{regclass} AND indisprimary = 'f'").map { |r| r["pg_get_indexdef"] }
end

#intermediate_table ⇒ Object 



79
80
81
 
# File 'lib/pgslice/table.rb', line 79

def intermediate_table
  self.class.new(schema, "#{name}_intermediate")
end

#max_id(primary_key, below: nil, where: nil) ⇒ Object 



96
97
98
99
100
101
102
103
 
# File 'lib/pgslice/table.rb', line 96

def max_id(primary_key, below: nil, where: nil)
  query = "SELECT MAX(#{quote_ident(primary_key)}) FROM #{quote_table}"
  conditions = []
  conditions << "#{quote_ident(primary_key)} <= #{below}" if below
  conditions << where if where
  query << " WHERE #{conditions.join(" AND ")}" if conditions.any?
  execute(query)[0]["max"].to_i
end

#min_id(primary_key, column, cast, starting_time, where) ⇒ Object 



105
106
107
108
109
110
111
112
 
# File 'lib/pgslice/table.rb', line 105

def min_id(primary_key, column, cast, starting_time, where)
  query = "SELECT MIN(#{quote_ident(primary_key)}) FROM #{quote_table}"
  conditions = []
  conditions << "#{quote_ident(column)} >= #{sql_date(starting_time, cast)}" if starting_time
  conditions << where if where
  query << " WHERE #{conditions.join(" AND ")}" if conditions.any?
  (execute(query)[0]["min"] || 1).to_i
end

#partitions ⇒ Object

ensure this returns partitions in the correct order



115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
 
# File 'lib/pgslice/table.rb', line 115

def partitions
  query = <<-SQL
    SELECT
      nmsp_child.nspname  AS schema,
      child.relname       AS name
    FROM pg_inherits
      JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
      JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
      JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
      JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
    WHERE
      nmsp_parent.nspname = $1 AND
      parent.relname = $2
    ORDER BY child.relname ASC
  SQL
  execute(query, [schema, name]).map { |r| Table.new(r["schema"], r["name"]) }
end

#primary_key ⇒ Object

stackoverflow.com/a/20537829 TODO can simplify with array_position in Postgres 9.5+



49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
# File 'lib/pgslice/table.rb', line 49

def primary_key
  query = <<-SQL
    SELECT
      pg_attribute.attname,
      format_type(pg_attribute.atttypid, pg_attribute.atttypmod),
      pg_attribute.attnum,
      pg_index.indkey
    FROM
      pg_index, pg_class, pg_attribute, pg_namespace
    WHERE
      nspname = $1 AND
      relname = $2 AND
      indrelid = pg_class.oid AND
      pg_class.relnamespace = pg_namespace.oid AND
      pg_attribute.attrelid = pg_class.oid AND
      pg_attribute.attnum = any(pg_index.indkey) AND
      indisprimary
  SQL
  rows = execute(query, [schema, name])
  rows.sort_by { |r| r["indkey"].split(" ").index(r["attnum"]) }.map { |r| r["attname"] }
end

#quote_table ⇒ Object 



75
76
77
 
# File 'lib/pgslice/table.rb', line 75

def quote_table
  [quote_ident(schema), quote_ident(name)].join(".")
end

#retired_table ⇒ Object 



83
84
85
 
# File 'lib/pgslice/table.rb', line 83

def retired_table
  self.class.new(schema, "#{name}_retired")
end

#sequences ⇒ Object

www.dbforums.com/showthread.php?1667561-How-to-list-sequences-and-the-columns-by-SQL



23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
# File 'lib/pgslice/table.rb', line 23

def sequences
  query = <<-SQL
    SELECT
      a.attname AS related_column,
      n.nspname AS sequence_schema,
      s.relname AS sequence_name
    FROM pg_class s
      INNER JOIN pg_depend d ON d.objid = s.oid
      INNER JOIN pg_class t ON d.objid = s.oid AND d.refobjid = t.oid
      INNER JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
      INNER JOIN pg_namespace n ON n.oid = s.relnamespace
      INNER JOIN pg_namespace nt ON nt.oid = t.relnamespace
    WHERE s.relkind = 'S'
      AND nt.nspname = $1
      AND t.relname = $2
    ORDER BY s.relname ASC
  SQL
  execute(query, [schema, name])
end

#to_s ⇒ Object 



10
11
12
 
# File 'lib/pgslice/table.rb', line 10

def to_s
  [schema, name].join(".")
end

#trigger_name ⇒ Object 



87
88
89
 
# File 'lib/pgslice/table.rb', line 87

def trigger_name
  "#{name}_insert_trigger"
end