lib/meta_db.rb in meta_db-0.1.1 vs lib/meta_db.rb in meta_db-0.2.0
- old
+ new
@@ -49,78 +49,80 @@
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.each_array.first)
+ db = MetaDb::Database.new(conn.database, r.value)
# Build schemas
conn.select(%(
- select schema_name::varchar as name,
- schema_owner::varchar as owner
+ 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::varchar as schema,
- table_name::varchar as name,
- table_type::varchar as type,
+ 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.dot row[:schema]
+ row[:schema] = db.schemas[row[:schema]]
klass = (row[:type] == 'VIEW' ? MetaDb::View : MetaDb::Table)
klass.init(row)
}
# Build columns
conn.select(%(
- select table_schema::varchar || '.' || table_name as table,
+ select table_schema::text as schema,
+ table_name::text as table,
+ column_name::text as name,
ordinal_position as ordinal,
- column_name::varchar as name,
- data_type::varchar as type,
+ data_type::text as type,
column_default as default,
is_identity = 'YES' as "identity?",
- is_generated = 'YES' as "generated?",
+ 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.dot row[:table]
+ row[:table] = db.schemas[row[:schema]].tables[row[:table]]
MetaDb::Column.init(row)
}
# Build simple constraints
conn.select(%(
- select c.constraint_type::varchar,
- c.table_schema || '.' || c.table_name as table,
- c.constraint_name::varchar as name,
+ 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::varchar)
+ 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
+ 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.dot row[:table]
+ 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)
@@ -137,26 +139,28 @@
# 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::varchar as schema,
- rc.constraint_name::varchar as name,
- cu_refing.table_schema || '.' || cu_refing.table_name as "referencing_table",
+ 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::varchar order by ordinal_position)
+ 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 || '.' || cu_refed.table_name || '.' || cu_refed.constraint_name
- as "referenced_constraint"
+ 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
+ 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
+ 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,
@@ -164,19 +168,83 @@
cu_refing.table_name,
cu_refed.table_schema,
cu_refed.table_name,
cu_refed.constraint_name
)).each_hash { |row|
- row[:schema] = db.dot row[:schema]
- row[:referencing_table] = db.dot row[:referencing_table]
+ 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.dot row[:referenced_constraint]
+ 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.children[n] }
+ column_names.map { |n| table.columns[n] }
end
end