// compareTables outputs SQL to make the table names match between DBs
func compareTables(conn1 *sql.DB, conn2 *sql.DB) {
sql := `
SELECT table_name
, table_type
, is_insertable_into
FROM information_schema.tables
WHERE table_schema = 'public'
AND (table_type = 'BASE TABLE' --OR table_type = 'VIEW'
)
ORDER BY table_name COLLATE "C" ASC;`
rowChan1, _ := pgutil.QueryStrings(conn1, sql)
rowChan2, _ := pgutil.QueryStrings(conn2, sql)
rows1 := make(TableRows, 0)
for row := range rowChan1 {
rows1 = append(rows1, row)
}
sort.Sort(rows1)
rows2 := make(TableRows, 0)
for row := range rowChan2 {
rows2 = append(rows2, row)
}
sort.Sort(rows2)
// We have to explicitly type this as Schema here for some unknown reason
var schema1 Schema = &TableSchema{rows: rows1, rowNum: -1}
var schema2 Schema = &TableSchema{rows: rows2, rowNum: -1}
// Compare the tables
doDiff(schema1, schema2)
}
// compareSequences outputs SQL to make the sequences match between DBs
func compareSequences(conn1 *sql.DB, conn2 *sql.DB) {
sql := `
SELECT sequence_name, data_type, start_value
, minimum_value, maximum_value
, increment, cycle_option
FROM information_schema.sequences
WHERE sequence_schema = 'public'
ORDER BY sequence_name;`
rowChan1, _ := pgutil.QueryStrings(conn1, sql)
rowChan2, _ := pgutil.QueryStrings(conn2, sql)
rows1 := make(SequenceRows, 0)
for row := range rowChan1 {
rows1 = append(rows1, row)
}
sort.Sort(rows1)
rows2 := make(SequenceRows, 0)
for row := range rowChan2 {
rows2 = append(rows2, row)
}
sort.Sort(rows2)
// We have to explicitly type this as Schema here for some unknown reason
var schema1 Schema = &SequenceSchema{rows: rows1, rowNum: -1}
var schema2 Schema = &SequenceSchema{rows: rows2, rowNum: -1}
// Compare the tables
doDiff(schema1, schema2)
}
/*
* Compare the foreign keys in the two databases. We do not recreate foreign keys if just the name is different.
*/
func compareForeignKeys(conn1 *sql.DB, conn2 *sql.DB) {
sql := `
SELECT c.conname AS fk_name
, cl.relname AS table_name
, pg_catalog.pg_get_constraintdef(c.oid, true) as constraint_def
FROM pg_catalog.pg_constraint c
INNER JOIN pg_class AS cl ON (c.conrelid = cl.oid)
WHERE c.contype = 'f';
`
rowChan1, _ := pgutil.QueryStrings(conn1, sql)
rowChan2, _ := pgutil.QueryStrings(conn2, sql)
rows1 := make(ForeignKeyRows, 0)
for row := range rowChan1 {
rows1 = append(rows1, row)
}
sort.Sort(rows1)
rows2 := make(ForeignKeyRows, 0)
for row := range rowChan2 {
rows2 = append(rows2, row)
}
sort.Sort(rows2)
// We have to explicitly type this as Schema here for some unknown reason
var schema1 Schema = &ForeignKeySchema{rows: rows1, rowNum: -1}
var schema2 Schema = &ForeignKeySchema{rows: rows2, rowNum: -1}
// Compare the columns
doDiff(schema1, schema2)
}
// compareFunctions outputs SQL to make the functions match between DBs
func compareFunctions(conn1 *sql.DB, conn2 *sql.DB) {
sql := `
SELECT p.oid::regprocedure AS function_name
, t.typname AS return_type
, pg_get_functiondef(p.oid) AS definition
FROM pg_proc AS p
JOIN pg_type t ON (p.prorettype = t.oid)
JOIN pg_namespace n ON (n.oid = p.pronamespace)
JOIN pg_language l ON (p.prolang = l.oid AND l.lanname IN ('c','plpgsql', 'sql'))
WHERE n.nspname = 'public';
`
rowChan1, _ := pgutil.QueryStrings(conn1, sql)
rowChan2, _ := pgutil.QueryStrings(conn2, sql)
rows1 := make(FunctionRows, 0)
for row := range rowChan1 {
rows1 = append(rows1, row)
}
sort.Sort(rows1)
rows2 := make(FunctionRows, 0)
for row := range rowChan2 {
rows2 = append(rows2, row)
}
sort.Sort(rows2)
// We must explicitly type this as Schema here
var schema1 Schema = &FunctionSchema{rows: rows1, rowNum: -1}
var schema2 Schema = &FunctionSchema{rows: rows2, rowNum: -1}
// Compare the functions
doDiff(schema1, schema2)
}
// compareTables outputs SQL to make the table names match between DBs
func compareTables(conn1 *sql.DB, conn2 *sql.DB) {
sql := `
SELECT table_name
, CASE table_type WHEN 'BASE TABLE' THEN 'TABLE' ELSE table_type END AS table_type
, is_insertable_into
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_type = 'BASE TABLE'
ORDER BY table_name;`
rowChan1, _ := pgutil.QueryStrings(conn1, sql)
rowChan2, _ := pgutil.QueryStrings(conn2, sql)
rows1 := make(TableRows, 0)
for row := range rowChan1 {
rows1 = append(rows1, row)
}
sort.Sort(rows1)
rows2 := make(TableRows, 0)
for row := range rowChan2 {
rows2 = append(rows2, row)
}
sort.Sort(rows2)
// We have to explicitly type this as Schema here
var schema1 Schema = &TableSchema{rows: rows1, rowNum: -1}
var schema2 Schema = &TableSchema{rows: rows2, rowNum: -1}
// Compare the tables
doDiff(schema1, schema2)
}
/*
* Compare the roles in the two databases
*/
func compareRoles(conn1 *sql.DB, conn2 *sql.DB) {
sql := `
SELECT r.rolname
, r.rolsuper
, r.rolinherit
, r.rolcreaterole
, r.rolcreatedb
, r.rolcanlogin
, r.rolconnlimit
, r.rolvaliduntil
, r.rolreplication
FROM pg_catalog.pg_roles AS r
ORDER BY r.rolname;
`
rowChan1, _ := pgutil.QueryStrings(conn1, sql)
rowChan2, _ := pgutil.QueryStrings(conn2, sql)
rows1 := make(RoleRows, 0)
for row := range rowChan1 {
rows1 = append(rows1, row)
}
sort.Sort(rows1)
rows2 := make(RoleRows, 0)
for row := range rowChan2 {
rows2 = append(rows2, row)
}
sort.Sort(rows2)
// We have to explicitly type this as Schema here for some unknown reason
var schema1 Schema = &RoleSchema{rows: rows1, rowNum: -1}
var schema2 Schema = &RoleSchema{rows: rows2, rowNum: -1}
doDiff(schema1, schema2)
}
/*
* Compare the columns in the two databases
*/
func compareGrantAttributes(conn1 *sql.DB, conn2 *sql.DB) {
sql := `
-- Attribute/Column ACL only
SELECT
n.nspname AS schema
, CASE c.relkind
WHEN 'r' THEN 'TABLE'
WHEN 'v' THEN 'VIEW'
WHEN 'f' THEN 'FOREIGN TABLE'
END as type
, c.relname AS relationship_name
, a.attname AS attribute_name
, a.attacl AS attribute_acl
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
INNER JOIN (SELECT attname, unnest(attacl) AS attacl, attrelid
FROM pg_catalog.pg_attribute
WHERE NOT attisdropped AND attacl IS NOT NULL)
AS a ON (a.attrelid = c.oid)
WHERE c.relkind IN ('r', 'v', 'f')
AND n.nspname !~ '^pg_'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY n.nspname, c.relname, a.attname;
`
rowChan1, _ := pgutil.QueryStrings(conn1, sql)
rowChan2, _ := pgutil.QueryStrings(conn2, sql)
rows1 := make(GrantAttributeRows, 0)
for row := range rowChan1 {
rows1 = append(rows1, row)
}
sort.Sort(rows1)
rows2 := make(GrantAttributeRows, 0)
for row := range rowChan2 {
rows2 = append(rows2, row)
}
sort.Sort(rows2)
// We have to explicitly type this as Schema here for some unknown reason
var schema1 Schema = &GrantAttributeSchema{rows: rows1, rowNum: -1}
var schema2 Schema = &GrantAttributeSchema{rows: rows2, rowNum: -1}
doDiff(schema1, schema2)
}
/*
* Compare the columns in the two databases
*/
func compareIndexes(conn1 *sql.DB, conn2 *sql.DB) {
// This SQL was generated with psql -E -c "\d t_org"
// The "magic" is in pg_get_indexdef and pg_get_constraint
sql := `
SELECT c.relname AS table_name
, c2.relname AS index_name
, i.indisprimary AS pk
, i.indisunique AS uq
, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) AS index_def
, pg_catalog.pg_get_constraintdef(con.oid, true) AS constraint_def
, con.contype AS typ
FROM pg_catalog.pg_index AS i
JOIN pg_catalog.pg_class AS c ON (c.oid = i.indrelid)
JOIN pg_catalog.pg_class AS c2 ON (c2.oid = i.indexrelid)
LEFT JOIN pg_catalog.pg_constraint con
ON (con.conrelid = i.indrelid AND con.conindid = i.indexrelid AND con.contype IN ('p','u','x'))
WHERE c.relname NOT LIKE 'pg_%'
--AND c.relname = 't_org'
--ORDER BY c.relname, c2.relname;
`
rowChan1, _ := pgutil.QueryStrings(conn1, sql)
rowChan2, _ := pgutil.QueryStrings(conn2, sql)
rows1 := make(IndexRows, 0)
for row := range rowChan1 {
rows1 = append(rows1, row)
}
sort.Sort(rows1)
rows2 := make(IndexRows, 0)
for row := range rowChan2 {
rows2 = append(rows2, row)
}
sort.Sort(rows2)
// We have to explicitly type this as Schema here for some unknown reason
var schema1 Schema = &IndexSchema{rows: rows1, rowNum: -1}
var schema2 Schema = &IndexSchema{rows: rows2, rowNum: -1}
// Compare the columns
doDiff(schema1, schema2)
}
/*
* Compare the columns in the two databases
*/
func compareGrantRelationships(conn1 *sql.DB, conn2 *sql.DB) {
sql := `
SELECT
n.nspname AS schema
, CASE c.relkind
WHEN 'r' THEN 'TABLE'
WHEN 'v' THEN 'VIEW'
WHEN 'S' THEN 'SEQUENCE'
WHEN 'f' THEN 'FOREIGN TABLE'
END as type
, c.relname AS relationship_name
, unnest(c.relacl) AS relationship_acl
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.relkind IN ('r', 'v', 'S', 'f')
AND n.nspname !~ '^pg_'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY n.nspname, c.relname;
`
rowChan1, _ := pgutil.QueryStrings(conn1, sql)
rowChan2, _ := pgutil.QueryStrings(conn2, sql)
rows1 := make(GrantRelationshipRows, 0)
for row := range rowChan1 {
rows1 = append(rows1, row)
}
sort.Sort(rows1)
rows2 := make(GrantRelationshipRows, 0)
for row := range rowChan2 {
rows2 = append(rows2, row)
}
sort.Sort(rows2)
// We have to explicitly type this as Schema here for some unknown reason
var schema1 Schema = &GrantRelationshipSchema{rows: rows1, rowNum: -1}
var schema2 Schema = &GrantRelationshipSchema{rows: rows2, rowNum: -1}
doDiff(schema1, schema2)
}
// compareTriggers outputs SQL to make the triggers match between DBs
func compareTriggers(conn1 *sql.DB, conn2 *sql.DB) {
sql := `
SELECT tbl.relname AS table_name
, t.tgname AS trigger_name
, pg_catalog.pg_get_triggerdef(t.oid, true) AS definition
, t.tgenabled AS enabled
FROM pg_catalog.pg_trigger t
INNER JOIN (
SELECT c.oid, c.relname
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace AND n.nspname = 'public')
WHERE pg_catalog.pg_table_is_visible(c.oid)) AS tbl
ON (tbl.oid = t.tgrelid)
AND NOT t.tgisinternal
ORDER BY 1;
`
rowChan1, _ := pgutil.QueryStrings(conn1, sql)
rowChan2, _ := pgutil.QueryStrings(conn2, sql)
rows1 := make(TriggerRows, 0)
for row := range rowChan1 {
rows1 = append(rows1, row)
}
sort.Sort(rows1)
rows2 := make(TriggerRows, 0)
for row := range rowChan2 {
rows2 = append(rows2, row)
}
sort.Sort(rows2)
// We must explicitly type this as Schema here
var schema1 Schema = &TriggerSchema{rows: rows1, rowNum: -1}
var schema2 Schema = &TriggerSchema{rows: rows2, rowNum: -1}
// Compare the triggers
doDiff(schema1, schema2)
}
/*
* Compare the columns in the two databases
*/
func compareColumns(conn1 *sql.DB, conn2 *sql.DB) {
sql := `
SELECT table_name
, column_name
, data_type
, is_nullable
, column_default
, character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'public'
AND is_updatable = 'YES'
-- We do not depend on this sorting correctly
ORDER BY table_name, column_name COLLATE "C" ASC;`
rowChan1, _ := pgutil.QueryStrings(conn1, sql)
rowChan2, _ := pgutil.QueryStrings(conn2, sql)
//rows1 := make([]map[string]string, 500)
rows1 := make(ColumnRows, 0)
for row := range rowChan1 {
rows1 = append(rows1, row)
}
sort.Sort(rows1)
//rows2 := make([]map[string]string, 500)
rows2 := make(ColumnRows, 0)
for row := range rowChan2 {
rows2 = append(rows2, row)
}
sort.Sort(&rows2)
// We have to explicitly type this as Schema here for some unknown reason
var schema1 Schema = &ColumnSchema{rows: rows1, rowNum: -1}
var schema2 Schema = &ColumnSchema{rows: rows2, rowNum: -1}
// Compare the columns
doDiff(schema1, schema2)
}
/*
* Compare the ownership of tables, sequences, and views in the two databases
*/
func compareOwners(conn1 *sql.DB, conn2 *sql.DB) {
sql := `
SELECT n.nspname AS schema
, c.relname AS relationship_name
, a.rolname AS owner
, CASE WHEN c.relkind = 'r' THEN 'TABLE'
WHEN c.relkind = 'S' THEN 'SEQUENCE'
WHEN c.relkind = 'v' THEN 'VIEW'
ELSE c.relkind::varchar END AS type
FROM pg_class AS c
INNER JOIN pg_authid AS a ON (a.oid = c.relowner)
INNER JOIN pg_namespace AS n ON (n.oid = c.relnamespace)
WHERE n.nspname = 'public'
AND c.relkind IN ('r', 'S', 'v');
`
rowChan1, _ := pgutil.QueryStrings(conn1, sql)
rowChan2, _ := pgutil.QueryStrings(conn2, sql)
rows1 := make(OwnerRows, 0)
for row := range rowChan1 {
rows1 = append(rows1, row)
}
sort.Sort(rows1)
rows2 := make(OwnerRows, 0)
for row := range rowChan2 {
rows2 = append(rows2, row)
}
sort.Sort(rows2)
// We have to explicitly type this as Schema here for some unknown reason
var schema1 Schema = &OwnerSchema{rows: rows1, rowNum: -1}
var schema2 Schema = &OwnerSchema{rows: rows2, rowNum: -1}
doDiff(schema1, schema2)
}