-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathobjectCounter.sh
executable file
·192 lines (182 loc) · 6.81 KB
/
objectCounter.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
#!/bin/sh
#
# objectCounter.sh
###########################################################################
#
# Purpose: This script compares a list of objects in the database with
# the ones defined in the pgsnsnpschema product to make sure
# they are in sync. It prints the counts, along with any cases
# where the lists are different.
#
# Usage:
#
# objectCounter.sh
#
# Env Vars:
#
# - PG_DBSERVER, PG_DBNAME and PG_DBUSER are used to connect to the
# database. These should be set in the instance of mgiconfig that
# is sourced via the Configuration file.
#
# Inputs: None
#
# Outputs: None
#
# Exit Codes:
#
# 0: Successful completion
# 1: Fatal error occurred
#
# Assumes: Nothing
#
# Implementation:
#
# Notes: None
#
###########################################################################
cd `dirname $0`; . ./Configuration
TOP=`pwd`
#
# Create temp files that will be removed when the script terminates.
#
TMP_SCHEMA=/tmp/tmp_schema_$$
TMP_DB=/tmp/tmp_db_$$
TMP_SORT=/tmp/tmp_sort_$$
TMP_DIFF=/tmp/tmp_diff_$$
trap "rm -f ${TMP_SCHEMA} ${TMP_DB} ${TMP_SORT} ${TMP_DIFF}" 0 1 2 15
#
# Print the schema product and database that are being compared.
#
echo ""
echo "Schema product: ${TOP}"
echo "Database: ${PG_DBSERVER}.${PG_DBNAME}.snp"
#
# This function compares the contents of 2 temp files and prints the counts
# and object differences. One file contains a list of objects defined in the
# schema product and the other contains a list of objects from the database.
# This function is called for each type of object that needs to be compared.
#
do_diff ()
{
echo ""
echo ""
echo $1
echo "----------------------------------------"
S_COUNT=`cat ${TMP_SCHEMA} | wc -l`
D_COUNT=`cat ${TMP_DB} | wc -l`
echo "Schema count: ${S_COUNT} Database count: ${D_COUNT}"
rm -f ${TMP_DIFF}
diff -i ${TMP_SCHEMA} ${TMP_DB} > ${TMP_DIFF}
if [ `cat ${TMP_DIFF} | wc -l` -eq 0 ]
then
echo ""
echo "No difference"
fi
if [ `cat ${TMP_DIFF} | grep "^< " | wc -l` -gt 0 ]
then
if [ `cat ${TMP_DIFF} | grep "crs_typings__cross_key_fkey" | wc -l` -gt 0 ]
then
echo ""
echo "No difference"
else
echo ""
echo "Missing from the database:"
cat ${TMP_DIFF} | grep "^< " | cut -c3-
fi
fi
if [ `cat ${TMP_DIFF} | grep "^> " | wc -l` -gt 0 ]
then
echo ""
echo "Missing from the schema product:"
cat ${TMP_DIFF} | grep "^> " | cut -c3-
fi
}
#
# Tables
#
# 1) Get a list of table names defined in the schema product.
# - to derive the table name that should be in the database:
# - use the table name in field 3
# - remove the "snp." prefix from the table name
# - remove any "(" at the end of the table name
# 2) Get a list of table names defined in the database.
# 3) Call the diff function.
#
cd ${TOP}/table
rm -f ${TMP_SCHEMA} ${TMP_DB}
grep -i "^CREATE TABLE " *_create.object | cut -d':' -f2 | cut -d' ' -f3 | sed 's/snp\.//' | sed 's/(//' | sed 's/./\L&/g' | sort > ${TMP_SCHEMA}
psql -h ${PG_DBSERVER} -d ${PG_DBNAME} -U ${PG_DBUSER} -t --command "select tablename from pg_catalog.pg_tables where schemaname = 'snp'" | sed "s/ //g" | grep -v "^$" | sort > ${TMP_DB}
do_diff Tables
#
# Indexes
#
# 1) Get a list of index names defined in the schema product.
# - regular indexes and unique indexes are parsed separately
# - ignore comment lines that start with "--"
# - regular indexes are in field 3
# - unique indexes are in field 4
# 2) Get a list of index names defined in the database.
# 3) Call the diff function.
#
cd ${TOP}/index
rm -f ${TMP_SCHEMA} ${TMP_DB} ${TMP_SORT}
grep -i "^CREATE INDEX " *_create.object | cut -d':' -f2 | grep -v "^\-\-" | cut -d' ' -f3 | sed 's/./\L&/g' > ${TMP_SORT}
grep -i "^CREATE UNIQUE INDEX " *_create.object | cut -d':' -f2 | grep -v "^\-\-" | cut -d' ' -f4 | sed 's/./\L&/g' >> ${TMP_SORT}
sort ${TMP_SORT} > ${TMP_SCHEMA}
psql -h ${PG_DBSERVER} -d ${PG_DBNAME} -U ${PG_DBUSER} -t --command "select indexrelname from pg_stat_user_indexes where schemaname = 'snp' and indexrelname not like '%_pkey'" | sed "s/ //g" | grep -v "^$" | sort > ${TMP_DB}
do_diff Indexes
#
# Primary keys
#
# 1) Get a list of primary key names defined in the schema product.
# - ignore comment lines that start with "--"
# - to derive the primary key name that should be in the database:
# - use the table name in field 3
# - remove the "snp." prefix from the table name
# - append "_pkey" to the end off the table name
# 2) Get a list of primary key names defined in the database.
# 3) Call the diff function.
#
cd ${TOP}/key
rm -f ${TMP_SCHEMA} ${TMP_DB}
grep -i "ADD PRIMARY KEY" *_create.object | cut -d':' -f2 | grep -v "^\-\-" | cut -d' ' -f3 | sed 's/snp\.//' | sed 's/$/_pkey/' | sed 's/./\L&/g' | sort > ${TMP_SCHEMA}
psql -h ${PG_DBSERVER} -d ${PG_DBNAME} -U ${PG_DBUSER} -t --command "select indexrelname from pg_stat_user_indexes where schemaname = 'snp' and indexrelname like '%_pkey'" | sed "s/ //g" | grep -v "^$" | sort > ${TMP_DB}
do_diff "Primary Keys"
#
# Foreign keys
#
# 1) Get a list of foreign key names defined in the schema product.
# - ignore comment lines that start with "--"
# - to derive the foreign key name that should be in the database:
# - use the table name in field 3 and the key column in field 7
# - remove the "snp." prefix from the table name
# - convert the "(" before the key column to an "_"
# - convert the ")" or "," after the key column to "_fkey"
# 2) Get a list of foreign key names defined in the database.
# 3) Call the diff function.
#
cd ${TOP}/key
rm -f ${TMP_SCHEMA} ${TMP_DB}
grep -i "ADD FOREIGN KEY" *_create.object | cut -d':' -f2 | grep -v "^\-\-" | awk '{print $3 $7}' | sed 's/snp\.//' | sed 's/(/_/' | sed 's/[),]/_fkey/' | sed 's/./\L&/g' | sort > ${TMP_SCHEMA}
psql -h ${PG_DBSERVER} -d ${PG_DBNAME} -U ${PG_DBUSER} -t --command "select conname from pg_constraint c join pg_namespace n on n.oid = c.connamespace where c.contype in ('f') and n.nspname = 'snp' and conname != 'crs_typings__cross_key_rownumber_fkey'" | sed "s/ //g" | grep -v "^$" | sort > ${TMP_DB}
do_diff "Foreign Keys"
#
# Duplicate keys
#
# Print any duplicate primary/foreign keys in the database.
#
cd ${TOP}
rm -f ${TMP_DB}
psql -h ${PG_DBSERVER} -d ${PG_DBNAME} -U ${PG_DBUSER} -t --command "select c.conname as duplidateKeys from pg_constraint c join pg_namespace n on n.oid = c.connamespace where c.contype in ('f') and n.nspname = 'snp' and c.conname like '%fkey1%'" | grep -v "^$" | sort > ${TMP_DB}
echo ""
echo ""
echo "Duplicate Primary/Foreign Keys"
echo "----------------------------------------"
if [ `cat ${TMP_DB} | wc -l` -eq 0 ]
then
echo ""
echo "None"
else
cat ${TMP_DB}
fi
exit 0