Example #1
0
// 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)
}
Example #2
0
// 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)
}
Example #3
0
/*
 * 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)
}
Example #4
0
// 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)
}
Example #5
0
// 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)
}
Example #6
0
/*
 * 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)
}
Example #7
0
/*
 * 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)
}
Example #8
0
/*
 * 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)
}
Example #9
0
/*
 * 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)
}
Example #10
0
// 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)
}
Example #11
0
/*
 * 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)
}
Example #12
0
/*
 * 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)
}