Menu
▾
▴
[Aplaws-commit] r107 - in users: . sskracic sskracic/bin
|
From: <ssk...@re...> - 2004-11-17 16:31:24
|
Author: sskracic
Date: 2004-11-17 17:23:56 +0100 (Wed, 17 Nov 2004)
New Revision: 107
Added:
users/sskracic/
users/sskracic/bin/
users/sskracic/bin/pgcompare.tcl
Log:
Tcl utility that compares the metadata of two Postgres databases.
Added: users/sskracic/bin/pgcompare.tcl
===================================================================
--- users/sskracic/bin/pgcompare.tcl 2004-11-17 10:56:55 UTC (rev 106)
+++ users/sskracic/bin/pgcompare.tcl 2004-11-17 16:23:56 UTC (rev 107)
@@ -0,0 +1,231 @@
+#! /usr/bin/tclsh
+
+proc has_option {opt} {
+ upvar options options
+ set yield [expr {[lsearch -exact $options $opt] > -1}]
+ return $yield
+}
+
+set usage "Usage: $argv0 \[options\] db1 db2
+ Options:
+
+ -t <type> limit comparison scope to objects of <type>
+ only. Possible values for <type>:
+ sequence, function, view, table,
+ constraint, trigger, index
+ -n report only object names, w/o definition
+ -1 suppress objects unique to db1
+ -2 suppress objects unique to db2
+ -12 suppress objects contained in both databases
+ -c append contraint definitions to tables
+ -i append index definitions to tables
+ -s shows identical objects only (implies -1 -2, ignores -12)
+ -d shows different objects only (implies -1 -2, ignores -12)
+ -h -? shows this help text
+ -- end of options
+
+ Multiple options can be specified, although this doesn't
+ make sense sometimes. The default is -1 -2.
+"
+
+
+set err [list]
+
+set filter "*"
+set options [list]
+for {set optionc 0} {$optionc < [llength $argv]} {incr optionc} {
+ set arg [lindex $argv $optionc]
+ if {[regexp -- {-(.*)} $arg all opt]} {
+ switch -exact -- $opt {
+ "-" { break }
+ "t" { set filter [lindex $argv [incr optionc]] ; lappend options $opt }
+ "n" -
+ "1" -
+ "2" -
+ "12" -
+ "c" -
+ "i" -
+ "s" -
+ "h" -
+ "?" -
+ "d" { lappend options $opt }
+ default { lappend err "Invalid option: -$opt" }
+ }
+ } else {
+ break
+ }
+}
+
+if {[has_option h] || [has_option ?]} {
+ puts $usage
+ exit 2
+}
+
+if {[expr $optionc + 2] != [llength $argv]} {
+ lappend err "Exactly 2 database names must be specified"
+}
+
+if {[llength $err]} {
+ puts [join $err "\n"]
+ puts $usage
+ exit 1
+}
+
+set db1 [lindex $argv $optionc]
+set db2 [lindex $argv [expr $optionc + 1]]
+
+if {[has_option d] || [has_option s]} {
+ lappend options 1 2
+ # remove 12
+ set options [lsearch -all -inline -not $options 12]
+}
+
+if {[lsearch $options {[12]}] == -1} {
+ lappend options 1 2
+}
+
+
+# Now the only thing left to do is some crazy regexp matching
+
+# For each object type we want to compare, we specify a regexp which
+# should hopefully grab the object definition from the dump file
+
+set types(view) {--\n\n(CREATE VIEW (\S+?) .*?;)()\n}
+set types(function) {--\n\n(CREATE FUNCTION (\S+?) .*?LANGUAGE (?:c|plpgsql);)()\n}
+set types(table) {--\n\n(CREATE TABLE ((\S+?)) .*?;)\n}
+set types(sequence) {--\n\n(CREATE SEQUENCE (\S+?)\n.*?;)()\n}
+set types(constraint) {--\n\n(ALTER TABLE ONLY (\S+?)\n *?ADD CONSTRAINT (\S+?) .*?;)\n}
+set types(trigger) {--\n\n(CREATE TRIGGER (\S+?)\n *?(?:AFTER|BEFORE) (?:INSERT|DELETE|UPDATE) ON (\S+?)\n.*?;)\n}
+set types(index) {--\n\n(CREATE (?:UNIQUE )??INDEX (\S+?) ON (\S+?) .*?;)\n}
+
+# For each object in the catalog, we will make an entry in objects array.
+# The key of the entry will look like $object_type:$table_name:$object_name,
+# and the value will be object definition. Note that $table_name only
+# has meaning for constraints, triggers, indices and (redundantly) tables.
+# For views, functions and sequences this part is empty.
+
+foreach dbindex {db1 db2} {
+
+ set dbname [set $dbindex]
+
+ puts "Obtaining schema for $dbname"
+ set dbschema [exec pg_dump -s $dbname]
+
+ foreach type [array names types -glob $filter] {
+ puts " Fetching definition for object type: $type"
+ set rgxp $types($type)
+ set matches [regexp -inline -all -- $rgxp $dbschema]
+ # We must not forget that each regexp match will produce 4 elements in the $matches list.
+ # First element is complete match, while second is the definition.
+ # The third one is object name, except for the constraints where the table name comes first.
+ for {set i 0} {$i < [llength $matches]} {incr i 4} {
+ set definition [lindex $matches [expr $i + 1]]
+ if {"$type" == "constraint"} {
+ set table_name [lindex $matches [expr $i + 2]]
+ set obj_name [lindex $matches [expr $i + 3]]
+ } else {
+ set obj_name [lindex $matches [expr $i + 2]]
+ set table_name [lindex $matches [expr $i + 3]]
+ }
+ set key "$type:$table_name:$obj_name"
+ set objects[set dbindex]($key) $definition
+ }
+ }
+
+ if {[has_option c]} {
+ # Now we will append constraint definitions to tables, sorted by constraint name
+ foreach constraint_key [lsort [array names objects$dbindex {constraint:*}]] {
+ set keylist [split $constraint_key :]
+ set tablename [lindex $keylist 1]
+ set table_key "table:$tablename:$tablename"
+ append objects[set dbindex]($table_key) "\n[set objects[set dbindex]($constraint_key)]"
+ # remove this constraint from object listing
+ array unset objects$dbindex $constraint_key
+ }
+ }
+
+ if {[has_option i]} {
+ # Now we will append index definitions to tables
+ foreach index_key [lsort [array names objects$dbindex {index:*}]] {
+ set keylist [split $index_key :]
+ set tablename [lindex $keylist 1]
+ set table_key "table:$tablename:$tablename"
+ append objects[set dbindex]($table_key) "\n[set objects[set dbindex]($index_key)]"
+ # remove this index from object listing
+ array unset objects$dbindex $index_key
+ }
+ }
+
+}
+
+set onlyindb1 [list]
+set onlyindb2 [list]
+set inboth [list]
+
+set indb1 [array names objectsdb1]
+set indb2 [array names objectsdb2]
+
+foreach entry [lsort -unique [concat $indb1 $indb2]] {
+ set in1 [lsearch $indb1 $entry]
+ set in2 [lsearch $indb2 $entry]
+ if {$in1 >= 0 && $in2 >= 0} {
+ lappend inboth $entry
+ } elseif {$in1 >= 0} {
+ lappend onlyindb1 $entry
+ } elseif {$in2 >= 0} {
+ lappend onlyindb2 $entry
+ }
+}
+
+puts "Number of objects appearing in both databases: [llength $inboth]"
+puts "Number of objects unique to $db1: [llength $onlyindb1]"
+puts "Number of objects unique to $db2: [llength $onlyindb2]"
+
+if {![has_option 1]} {
+ foreach entry $onlyindb1 {
+ puts "ONLY IN $db1: $entry"
+ if {![has_option n]} {
+ puts "$objectsdb1($entry)"
+ puts "---"
+ }
+ }
+}
+
+if {![has_option 2]} {
+ foreach entry $onlyindb2 {
+ puts "ONLY IN $db2: $entry"
+ if {![has_option n]} {
+ puts "$objectsdb2($entry)"
+ puts "---"
+ }
+ }
+}
+
+if {![has_option 12]} {
+ foreach entry $inboth {
+ # Now compare two
+ set definition1 [string map {" " ""} $objectsdb1($entry)]
+ set definition2 [string map {" " ""} $objectsdb2($entry)]
+ if {[string equal -nocase $definition1 $definition2]} {
+ if {![has_option d]} {
+ puts "IDENTICAL: $entry"
+ if {![has_option n]} {
+ puts "$objectsdb1($entry)"
+ puts "---"
+ }
+ }
+ } else {
+ if {![has_option s]} {
+ puts "DIFFERENCE: $entry"
+ if {![has_option n]} {
+ puts "$db1: $objectsdb1($entry)"
+ puts "$db2: $objectsdb2($entry)"
+ puts "---"
+ }
+ }
+ }
+ }
+}
+
+
+
Property changes on: users/sskracic/bin/pgcompare.tcl
___________________________________________________________________
Name: svn:executable
+ *
|
