module Gadget
Constants
- VERSION
Public Class Methods
Return a collection enumerating the columns in a table or database.
Usage¶ ↑
columns = Gadget.columns(conn) columns_in_table = Gadget.columns(conn, "tablename")
Parameters¶ ↑
-
conn
- aPG::Connection
to the database -
+tablename - if given, return columns only for the named table
Returns¶ ↑
-
a Hash:
- table name
-
a Hash:
:columns
-
an
Array
of column names
# File lib/gadget.rb, line 82 def self.columns(conn, *args) options = args.extract_options! tablename = args.shift nspname = args.shift || "public" sql = <<-END_OF_SQL SELECT t.tablename, a.attnum, a.attname, ns.nspname FROM pg_catalog.pg_attribute a INNER JOIN pg_catalog.pg_class c ON a.attrelid = c.oid INNER JOIN pg_catalog.pg_tables t ON c.relname = t.tablename INNER JOIN pg_catalog.pg_namespace ns ON c.relnamespace = ns.oid WHERE a.attnum >= 0 AND t.schemaname = ns.nspname AND ns.nspname = $1 END_OF_SQL unless options[:include_dropped] sql += " AND a.attisdropped IS FALSE" end if tablename.nil? rs = conn.exec_params(sql, [ nspname ]) else sql += " AND t.tablename = $2" rs = conn.exec_params(sql, [ nspname, tablename ]) end tuples = rs.reduce({}) do | h, row | h[row["tablename"]] ||= { columns: {} } h[row["tablename"]][:columns][row["attnum"]] = row["attname"] h end rs.clear tuples end
Return a collection enumerating the constraints in a table or database.
Usage¶ ↑
constraints = Gadget.constraints(conn) constraints_in_table = Gadget.constraints(conn, "tablename")
Parameters¶ ↑
-
conn
- aPG::Connection
to the database -
+tablename - if given, return constraints only for the named table
Returns¶ ↑
-
a Hash:
- table name
-
a Hash:
:constraints
-
an
Array
of Hashes: :name
-
the name of the constraint
:kind
-
the kind of the constraint:
* +check+ * +exclusion+ * +foreign key+ * +primary key+ * +trigger+ * +unique+
# File lib/gadget.rb, line 196 def self.constraints(conn, *args) _ = args.extract_options! tablename = args.shift sql = <<-END_OF_SQL SELECT pg_constraint.conname AS name, pg_constraint.contype AS constrainttype, t.tablename AS tablename FROM pg_catalog.pg_constraint INNER JOIN pg_catalog.pg_class c ON pg_constraint.conrelid = c.oid INNER JOIN pg_catalog.pg_tables t ON c.relname = t.tablename WHERE t.schemaname = 'public' END_OF_SQL if tablename.nil? rs = conn.exec(sql) else sql += " AND t.tablename = $1" rs = conn.exec_params(sql, [ tablename ]) end tuples = rs.reduce({}) do | h, row | name = row["tablename"] h[name] ||= { constraints: [] } new_constraint = { name: row["name"], kind: case row["constrainttype"] when "c" "check" when "f" "foreign key" when "p" "primary key" when "t" "trigger" when "u" "unique" when "x" "exclusion" else %Q(*** unknown: "#{row["constrainttype"]}"") end, } h[name][:constraints] << new_constraint h end rs.clear tuples end
Return a collection enumerating the dependencies between tables in a database. A table a
is considered to be dependent on a table b
if a
has a foreign key constraint that refers to b
.
Usage¶ ↑
dependencies = Gadget.dependencies(conn)
Parameters¶ ↑
-
conn
- aPG::Connection
to the database
Returns¶ ↑
-
a Hash:
- table name
-
an
Array
of table names
# File lib/gadget.rb, line 257 def self.dependencies(conn, *args) _ = args.extract_options! tables = self.tables(conn) foreign_keys = self.foreign_keys(conn) tables.reduce({}) do | h, (tablename, _) | h[tablename] = [] refs = foreign_keys[tablename] unless refs.nil? refs[:refs].each { | ref | h[tablename] << ref[:ref_name] } end h end end
Print dot (Graphviz data format) describing the dependency graph for a database to stdout.
Usage¶ ↑
Gadget.dependency_graph(conn)
Parameters¶ ↑
-
conn
- aPG::Connection
to the database
# File lib/gadget.rb, line 296 def self.dependency_graph(conn, *args) _ = args.extract_options! puts "digraph dependencies {" self.dependencies(conn).each do | tablename, deps | if deps.empty? puts %Q<"#{tablename}"> else deps.each { | dep | puts %Q|"#{tablename}" -> "#{dep}"| } end end puts "}" end
Return a collection enumerating the foreign keys in a table or database.
Usage¶ ↑
fks = Gadget.foreign_keys(conn) fks_in_table = Gadget.foreign_keys(conn, "tablename")
Parameters¶ ↑
-
conn
- aPG::Connection
to the database -
+tablename - if given, return foreign keys only for the named table
Returns¶ ↑
-
a Hash:
- table name
-
a Hash:
:refs
-
an
Array
of Hashes: :name
-
the name of the foreign key
:cols
-
the columns in _this table_ that make up the foreign key
:ref_name
-
the name of the table referred to by this foreign key
:ref_cols
-
the columns in _the other table_ that are referred to by this foreign key
# File lib/gadget.rb, line 133 def self.foreign_keys(conn, *args) _ = args.extract_options! tablename = args.shift sql = <<-END_OF_SQL SELECT pg_constraint.conname AS name, t1.tablename AS tablename, pg_constraint.conkey AS cols, t2.tablename AS refname, pg_constraint.confkey AS refcols FROM pg_catalog.pg_constraint INNER JOIN pg_catalog.pg_class c1 ON pg_constraint.conrelid = c1.oid INNER JOIN pg_catalog.pg_tables t1 ON c1.relname = t1.tablename INNER JOIN pg_catalog.pg_class c2 ON pg_constraint.confrelid = c2.oid INNER JOIN pg_catalog.pg_tables t2 ON c2.relname = t2.tablename WHERE t1.schemaname = 'public' AND t2.schemaname = 'public' AND pg_constraint.contype = 'f' END_OF_SQL if tablename.nil? rs = conn.exec(sql) else sql += " AND t1.tablename = $1" rs = conn.exec_params(sql, [ tablename ]) end tuples = rs.reduce({}) do | h, row | name = row["tablename"] h[name] ||= { refs: [] } col_names = self.columns(conn, name, include_dropped: true)[name][:columns] refcol_names = self.columns(conn, row["refname"], include_dropped: true)[row["refname"]][:columns] new_ref = { name: row["name"], cols: row["cols"].sub(/\A\{|\}\z/, "").split(",").map { | attnum | col_names[attnum] }, ref_name: row["refname"], ref_cols: row["refcols"].sub(/\A\{|\}\z/, "").split(",").map { | attnum | refcol_names[attnum] }, } h[name][:refs] << new_ref h end rs.clear tuples end
Return a collection enumerating the functions in a database.
Usage¶ ↑
functions = Gadget.functions(conn)
Parameters¶ ↑
-
conn
- aPG::Connection
to the database
Returns¶ ↑
-
a Hash:
- function name
-
a Hash:
:oid
-
the function's OID
:arg_types
-
the type IDs for the arguments to the function
# File lib/gadget.rb, line 323 def self.functions(conn, *args) _ = args.extract_options! rs = conn.exec(<<-END_OF_SQL) SELECT p.oid, p.proname, p.proargtypes FROM pg_catalog.pg_proc p INNER JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname = 'public' END_OF_SQL tuples = rs.reduce({}) do | h, row | h[row["proname"]] = { oid: row["oid"].to_i, arg_types: row["proargtypes"].split(/\s+/).map(&:to_i), } h end rs.clear tuples end
Return a collection enumerating the sequences in a database.
Usage¶ ↑
sequences = Gadget.sequences(conn)
Parameters¶ ↑
-
conn
- aPG::Connection
to the database
Returns¶ ↑
-
a Hash:
- sequence name
-
a Hash:
:oid
-
the sequence's OID
# File lib/gadget.rb, line 356 def self.sequences(conn, *args) _ = args.extract_options! sql = <<-END_OF_SQL SELECT c.oid, c.relname FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n on c.relnamespace = n.oid WHERE c.relkind = 'S' AND n.nspname = 'public' END_OF_SQL rs = conn.exec(sql) tuples = rs.reduce({}) do | h, row | h[row["relname"]] = { oid: row["oid"].to_i, } h end rs.clear tuples end
Return a collection enumerating the tables in a database.
Usage¶ ↑
tables = Gadget.tables(conn)
Parameters¶ ↑
-
conn
- aPG::Connection
to the database
Returns¶ ↑
-
a Hash:
- table name
-
a Hash:
:oid
-
the table's OID
# File lib/gadget.rb, line 48 def self.tables(conn, *args) _ = args.extract_options! sql = <<-END_OF_SQL SELECT c.oid, t.tablename FROM pg_catalog.pg_tables t INNER JOIN pg_catalog.pg_class c ON c.relname=t.tablename WHERE t.schemaname='public' END_OF_SQL rs = conn.exec(sql) tuples = rs.reduce({}) do | h, row | h[row["tablename"]] = { oid: row["oid"].to_i, } h end rs.clear tuples end
Return a collection enumerating the tables in a database in dependency order. If a table a
is dependent on a table b
, then a
will appear after b
in the collection.
Usage¶ ↑
dependencies = Gadget.dependencies(conn)
Parameters¶ ↑
-
conn
- aPG::Connection
to the database
Returns¶ ↑
-
a Hash:
- table name
-
an
Array
of table names
# File lib/gadget.rb, line 284 def self.tables_in_dependency_order(conn, *args) _ = args.extract_options! self.dependencies(conn).reduce(TsortableHash.new) { | h, (k, v) | h[k] = v; h }.tsort end
Return a collection enumerating the triggers in a database.
Usage¶ ↑
triggers = Gadget.triggers(conn)
Parameters¶ ↑
-
conn
- aPG::Connection
to the database
Returns¶ ↑
-
a Hash:
- trigger name
-
a Hash:
:oid
-
the trigger's OID
:table_name
-
the table on which the trigger is defined
:function_name
-
the name of the trigger's function
# File lib/gadget.rb, line 391 def self.triggers(conn, *args) _ = args.extract_options! tablename = args.shift sql = <<-END_OF_SQL SELECT tg.oid, tg.tgname, t.tablename, p.proname FROM pg_catalog.pg_trigger tg INNER JOIN pg_catalog.pg_class c ON tg.tgrelid = c.oid INNER JOIN pg_catalog.pg_tables t ON c.relname = t.tablename INNER JOIN pg_catalog.pg_proc p ON tg.tgfoid = p.oid WHERE tg.tgconstrrelid = 0 END_OF_SQL if tablename.nil? rs = conn.exec(sql) else sql += " AND t.tablename = $1" rs = conn.exec_params(sql, [ tablename ]) end tuples = rs.reduce({}) do | h, row | h[row["tgname"]] = { oid: row["oid"].to_i, table_name: row["tablename"], function_name: row["proname"], } h end rs.clear tuples end
Return a collection enumerating the types in a database.
Usage¶ ↑
types = Gadget.types(conn)
Parameters¶ ↑
-
conn
- aPG::Connection
to the database
Returns¶ ↑
-
a Hash:
- type name
-
a Hash:
:oid
-
the type's OID
# File lib/gadget.rb, line 433 def self.types(conn, *args) _ = args.extract_options! rs = conn.exec(<<-END_OF_SQL) SELECT t.oid, t.typname FROM pg_catalog.pg_type t END_OF_SQL tuples = rs.reduce({}) do | h, row | h[row["typname"]] = { oid: row["oid"].to_i, } h end rs.clear tuples end