require "meta_db/version"
require "indented_io"
require 'pg'
require 'yaml'
require 'meta_db/connection.rb'
require 'meta_db/db_object.rb'
module MetaDb
# TODO
def self.load_yaml(file)
YAML.load_file(file)
end
def self.save_yaml(db, file, pretty: true)
File.open(file, 'w') do |f|
arg = pretty ? { :indentation => 3 } : {}
f.write(YAML.dump(db, arg))
end
end
def self.load_marshal(file)
File.open(file) { |f| Marshal.load(f) }
end
def self.save_marshal(db, file)
File.open(file, 'w') { |f| Marshal.dump(db, f) }
end
# The options argument is a hash as PG::Connection options. :host, :port,
# :dbname, :user, and :password are some of the most used
def self.load_pg_conn(options)
PostgresConnection.open(options) { |conn| load_conn(conn) }
end
private
# Connect to the given database and return a MetaDb::Db object. If host is
# nil, a socket is used to connect to the database
#
# The connection object should support #name and #exec
def self.load_conn(conn)
# TODO: Start transaction
# Build database
r = conn.select %(
select usename::varchar
from pg_database d
join pg_user u on u.usesysid = d.datdba
where d.datname = '#{conn.database}'
)
r.size == 1 or raise "Internal error"
db = MetaDb::Database.new(conn.database, r.value)
# Build schemas
conn.select(%(
select schema_name::text as name,
schema_owner::text as owner
from information_schema.schemata
where schema_name !~ '^pg_' AND schema_name <> 'information_schema'
)).each_hash { |row|
row[:database] = db
MetaDb::Schema.init(row)
}
# Build tables
conn.select(%(
select table_schema::text as schema,
table_name::text as name,
table_type::text as type,
is_insertable_into = 'YES' as "insertable?",
is_typed = 'YES' as "typed?"
from information_schema.tables
where table_schema !~ '^pg_' AND table_schema <> 'information_schema'
)).each_hash { |row|
row[:schema] = db.schemas[row[:schema]]
klass = (row[:type] == 'VIEW' ? MetaDb::View : MetaDb::Table)
klass.init(row)
}
# Build columns
conn.select(%(
select table_schema::text as schema,
table_name::text as table,
column_name::text as name,
ordinal_position as ordinal,
data_type::text as type,
column_default as default,
is_identity = 'YES' as "identity?",
is_generated = 'ALWAYS' as "generated?",
is_nullable = 'YES' as "nullable?",
is_updatable = 'YES' as "updatable?"
from information_schema.columns
where table_schema !~ '^pg_' AND table_schema <> 'information_schema'
)).each_hash { |row|
row[:table] = db.schemas[row[:schema]].tables[row[:table]]
MetaDb::Column.init(row)
}
# Build simple constraints
conn.select(%(
select c.table_schema::text as schema,
c.table_name::text as table,
c.constraint_type::text,
c.constraint_name::text as name,
cc.check_clause as expression,
(
select array_agg(column_name::text)
from information_schema.constraint_column_usage ccu
where ccu.table_schema = c.table_schema
and ccu.table_name = c.table_name
and ccu.constraint_schema = c.constraint_schema
and ccu.constraint_name = c.constraint_name
) as columns
from information_schema.table_constraints c
left join information_schema.check_constraints cc
on cc.constraint_schema = c.table_schema and
cc.constraint_name = c.constraint_name
where c.table_schema !~ '^pg_' AND c.table_schema <> 'information_schema'
and c.constraint_type in ('PRIMARY KEY', 'UNIQUE', 'CHECK')
)).each_hash { |row|
row[:table] = db.schemas[row[:schema]].tables[row[:table]]
row[:columns] = lookup_columns(row[:table], row[:columns] || [])
case row[:constraint_type]
when "PRIMARY KEY"; MetaDb::PrimaryKeyConstraint.init(row)
when "UNIQUE"; MetaDb::UniqueConstraint.init(row)
when "CHECK"; MetaDb::CheckConstraint.init(row)
else
raise "Oops"
end
}
# Build referential constraints
#
# Referential constraints has to be initialized after unique constraints
#
# The GROUP BY is necessary because we re-assign constraints from schema to
# table. This requires joining key_column_usage again to get the name of
# the referenced table and that yields a row for each column in the unique
# key (TODO: Can this be omitted?)
#
conn.select(%(
select rc.constraint_schema::text as schema,
rc.constraint_name::text as name,
cu_refing.table_schema::text as "referencing_schema",
cu_refing.table_name::text as "referencing_table",
(
select array_agg(column_name::text order by ordinal_position)
from information_schema.key_column_usage kcu
where kcu.constraint_name = rc.constraint_name
) as "referencing_columns",
cu_refed.table_schema::text as referenced_schema,
cu_refed.table_name::text as referenced_table,
cu_refed.constraint_name::text as referenced_constraint
from information_schema.referential_constraints rc
join information_schema.key_column_usage cu_refing
on cu_refing.constraint_schema = rc.constraint_schema
and cu_refing.constraint_name = rc.constraint_name
join information_schema.key_column_usage cu_refed
on cu_refed.constraint_schema = rc.unique_constraint_schema
and cu_refed.constraint_name = rc.unique_constraint_name
where cu_refing.table_schema !~ '^pg_' AND cu_refing.table_schema <> 'information_schema'
group by
rc.constraint_schema,
rc.constraint_name,
cu_refing.table_schema,
cu_refing.table_name,
cu_refed.table_schema,
cu_refed.table_name,
cu_refed.constraint_name
)).each_hash { |row|
row[:schema] = db.schemas[row[:schema]]
row[:referencing_table] = row[:schema].tables[row[:referencing_table]]
row[:referencing_columns] = lookup_columns(row[:referencing_table], row[:referencing_columns])
row[:referenced_constraint] =
db.schemas[row[:referenced_schema]] \
.tables[row[:referenced_table]] \
.constraints[row[:referenced_constraint]]
MetaDb::ReferentialConstraint.init(row)
}
# Build functions and procedures
conn.select(%(
select s.nspname::text as "schema",
pg_get_userbyid(p.proowner)::text as "owner",
format('%I(%s)', p.proname, oidvectortypes(p.proargtypes))
|| ': '
|| format_type(p.prorettype, null) as "name",
case format_type(p.prorettype, null)
when 'void' then 'procedure'
else 'function'
end as "kind",
case
when prosecdef then 'definer'
else 'invoker'
end as "security"
from pg_proc p
join pg_namespace s on (p.pronamespace = s.oid)
where s.nspname !~ '^pg_' AND s.nspname <> 'information_schema'
)).each_hash { |row|
row[:schema] = db.schemas[row[:schema]]
klass = (row[:kind] == 'function' ? MetaDb::Function : MetaDb::Procedure)
klass.init(row)
}
# Build user-defined triggers
conn.select(%(
select n.nspname::text as "schema",
c.relname::text as "table",
t.tgname::text as "name",
fn.nspname::text as "function_schema",
format('%I(%s)', p.proname::text, oidvectortypes(p.proargtypes))
|| ': '
|| format_type(p.prorettype, null) as "function_name",
case when (tgtype::int::bit(7) & b'0000001')::int = 0 then 'stmt' else 'row' end as "level",
coalesce(
case when (tgtype::int::bit(7) & b'0000010')::int = 0 then null else 'before' end,
case when (tgtype::int::bit(7) & b'0000010')::int = 0 then 'after' else null end,
case when (tgtype::int::bit(7) & b'1000000')::int = 0 then null else 'instead' end,
''
)::text as "timing",
(case when (tgtype::int::bit(7) & b'0000100')::int = 0 then '' else ' insert' end) ||
(case when (tgtype::int::bit(7) & b'0001000')::int = 0 then '' else ' delete' end) ||
(case when (tgtype::int::bit(7) & b'0010000')::int = 0 then '' else ' update' end) ||
(case when (tgtype::int::bit(7) & b'0100000')::int = 0 then '' else ' truncate' end)
as "events"
from pg_trigger t
join pg_proc p on t.tgfoid = p.oid
join pg_class c on c.oid = t.tgrelid
join pg_namespace n on n.oid = c.relnamespace
join pg_namespace fn on fn.oid = p.pronamespace
where not t.tgisinternal
)).each_hash { |row|
schema = db.schemas[row[:schema]]
row[:table] = schema.tables[row[:table]]
function_schema = db.schemas[row[:function_schema]]
row[:function] = function_schema.functions[row[:function_name]]
MetaDb::Trigger.init(row)
}
# Return database object
db
end
def self.lookup_columns(table, column_names)
column_names.map { |n| table.columns[n] }
end
end