github.com/cactusblossom/fabric-ca@v0.0.0-20200611062428-0082fc643826/scripts/fvt/dbmigration_test.sh (about) 1 #!/bin/bash 2 # 3 # Copyright IBM Corp. All Rights Reserved. 4 # 5 # SPDX-License-Identifier: Apache-2.0 6 # 7 8 TESTCASE="db_migration" 9 FABRIC_CA="$GOPATH/src/github.com/hyperledger/fabric-ca" 10 SCRIPTDIR="$FABRIC_CA/scripts/fvt" 11 . $SCRIPTDIR/fabric-ca_utils 12 RC=0 13 DBNAME="fabric_ca" 14 15 TESTDIR="/tmp/$TESTCASE" 16 export FABRIC_CA_CLIENT_HOME="/tmp/db_migration/admin" 17 export FABRIC_CA_SERVER_HOME="$TESTDIR" 18 export CA_CFG_PATH="$TESTDIR" 19 20 ###### SQLITE ##### 21 22 mkdir -p $FABRIC_CA_SERVER_HOME 23 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME 'CREATE TABLE IF NOT EXISTS users (id VARCHAR(64), token bytea, type VARCHAR(64), affiliation VARCHAR(64), attributes TEXT, state INTEGER, max_enrollments INTEGER);' 24 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME 'CREATE TABLE IF NOT EXISTS affiliations (name VARCHAR(64) NOT NULL UNIQUE, prekey VARCHAR(64));' 25 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME 'CREATE TABLE IF NOT EXISTS certificates (id VARCHAR(64), serial_number blob NOT NULL, authority_key_identifier blob NOT NULL, ca_label blob, status blob NOT NULL, reason int, expiry timestamp, revoked_at timestamp, pem blob NOT NULL, PRIMARY KEY(serial_number, authority_key_identifier));' 26 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME "INSERT INTO affiliations (name) VALUES ('org1');" 27 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME "INSERT INTO affiliations (name) VALUES ('org1.dep1');" 28 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME "INSERT INTO certificates (id, serial_number, authority_key_identifier) VALUES ('registrar', '1234', '12345');" 29 30 # Start up the server and the schema should get updated 31 $SCRIPTDIR/fabric-ca_setup.sh -I -S -X -D -d sqlite3 32 33 enroll 34 if test $? -ne 0; then 35 ErrorMsg "Failed to enroll $REGISTRAR" 36 fi 37 38 $SCRIPTDIR/fabric-ca_setup.sh -K # Kill the server 39 40 # Check that the new schema took affect 41 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME 'pragma table_info(users)' > $TESTDIR/output.txt 42 grep 'id|VARCHAR(255)' $TESTDIR/output.txt 43 if [ $? != 0 ]; then 44 ErrorMsg "Database column 'id' should have character limit of 255" 45 fi 46 grep 'type|VARCHAR(256)' $TESTDIR/output.txt 47 if [ $? != 0 ]; then 48 ErrorMsg "Database column 'type' should have character limit of 256" 49 fi 50 grep 'affiliation|VARCHAR(1024)' $TESTDIR/output.txt 51 if [ $? != 0 ]; then 52 ErrorMsg "Database column 'affiliation' should have character limit of 1024" 53 fi 54 grep 'attributes|TEXT' $TESTDIR/output.txt 55 if [ $? != 0 ]; then 56 ErrorMsg "Database column 'attributes' should be a TEXT field" 57 fi 58 grep 'level|INTEGER' $TESTDIR/output.txt 59 if [ $? != 0 ]; then 60 ErrorMsg "Database column 'level' should be a INTEGER field" 61 fi 62 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME 'SELECT value FROM properties WHERE (property = "identity.level")' | grep '2' 63 if [ $? != 0 ]; then 64 ErrorMsg "Incorrect level found for 'identity.level' in properties table" 65 fi 66 grep 'incorrect_password_attempts|INTEGER' $TESTDIR/output.txt 67 if [ $? != 0 ]; then 68 ErrorMsg "Database column 'incorrect_password_attempts' should be a INTEGER field" 69 fi 70 71 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME 'pragma table_info(affiliations)' > $TESTDIR/output.txt 72 grep 'name|VARCHAR(1024)' $TESTDIR/output.txt 73 if [ $? != 0 ]; then 74 ErrorMsg "Database column 'name' should have character limit of 1024" 75 fi 76 grep 'prekey|VARCHAR(1024)' $TESTDIR/output.txt 77 if [ $? != 0 ]; then 78 ErrorMsg "Database column 'prekey' should have character limit of 1024" 79 fi 80 grep 'level|INTEGER' $TESTDIR/output.txt 81 if [ $? != 0 ]; then 82 ErrorMsg "Database column 'level' should be a INTEGER field" 83 fi 84 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME 'SELECT value FROM properties WHERE (property = "affiliation.level")' | grep '1' 85 if [ $? != 0 ]; then 86 ErrorMsg "Incorrect level found for 'affiliation.level' in properties table" 87 fi 88 89 90 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME 'pragma table_info(certificates)' > $TESTDIR/output.txt 91 grep 'id|VARCHAR(255)' $TESTDIR/output.txt 92 if [ $? != 0 ]; then 93 ErrorMsg "Database column 'id' should have character limit of 255" 94 fi 95 grep 'level|INTEGER' $TESTDIR/output.txt 96 if [ $? != 0 ]; then 97 ErrorMsg "Database column 'level' should be a INTEGER field" 98 fi 99 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME 'SELECT value FROM properties WHERE (property = "certificate.level")' | grep '1' 100 if [ $? != 0 ]; then 101 ErrorMsg "Incorrect level found for 'certificate.level' in properties table" 102 fi 103 104 rm $FABRIC_CA_SERVER_HOME/$DBNAME 105 106 ###### MYSQL ###### 107 108 $SCRIPTDIR/fabric-ca_setup.sh -I -S -X -D -d mysql # Start up the server and the new schema should get created 109 $SCRIPTDIR/fabric-ca_setup.sh -K # Kill the server 110 $SCRIPTDIR/fabric-ca_setup.sh -S -X -D -d mysql # Start up the server again and it should try to update the schema again, should result in no errors 111 if test $? -ne 0; then 112 ErrorMsg "Failed to start up server that is using the latest database schema" 113 fi 114 $SCRIPTDIR/fabric-ca_setup.sh -K # Kill the server 115 116 # Create the database tables using the old schema 117 echo "Creating '$DBNAME' MySQL database and tables before starting up server" 118 mysql --host=localhost --user=root --password=mysql -e "drop database $DBNAME;" 119 mysql --host=localhost --user=root --password=mysql -e "create database $DBNAME;" 120 mysql --host=localhost --user=root --password=mysql --database=$DBNAME -e "CREATE TABLE users (id VARCHAR(64) NOT NULL, token blob, type VARCHAR(64), affiliation VARCHAR(64), attributes VARCHAR(256), state INTEGER, max_enrollments INTEGER, PRIMARY KEY (id)) DEFAULT CHARSET=utf8 COLLATE utf8_bin;" 121 mysql --host=localhost --user=root --password=mysql --database=$DBNAME -e "CREATE TABLE affiliations (name VARCHAR(64) NOT NULL UNIQUE, prekey VARCHAR(64));" 122 mysql --host=localhost --user=root --password=mysql --database=$DBNAME -e "CREATE TABLE certificates (id VARCHAR(64), serial_number varbinary(128) NOT NULL, authority_key_identifier varbinary(128) NOT NULL, ca_label varbinary(128), status varbinary(128) NOT NULL, reason int, expiry timestamp DEFAULT 0, revoked_at timestamp DEFAULT 0, pem varbinary(4096) NOT NULL, PRIMARY KEY(serial_number, authority_key_identifier)) DEFAULT CHARSET=utf8 COLLATE utf8_bin;" 123 mysql --host=localhost --user=root --password=mysql --database=$DBNAME -e "SELECT character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND COLUMN_NAME = 'id';" | grep "64" 124 if [ $? != 0 ]; then 125 ErrorMsg "Database column 'id' should have character limit of 64" 126 fi 127 128 # Start up the server and the schema should get updated 129 $SCRIPTDIR/fabric-ca_setup.sh -I -S -X -D -d mysql 130 131 enroll 132 if test $? -ne 0; then 133 ErrorMsg "Failed to enroll $REGISTRAR" 134 fi 135 136 # Register a user with a username of 128 character. This should pass with the updated schema 137 USERNAME=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 128 | head -n 1) 138 register "" $USERNAME 139 if test $? -ne 0; then 140 ErrorMsg "Failed to register $USERNAME" 141 fi 142 143 # Register a user with a username of 300 character. This should result in an error 144 USERNAME=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 300 | head -n 1) 145 register "" $USERNAME 146 if test $? -ne 1; then 147 ErrorMsg "Should have failed to register $USERNAME" 148 fi 149 150 $SCRIPTDIR/fabric-ca_setup.sh -K 151 152 # Check that the new schema took affect 153 mysql --host=localhost --user=root --password=mysql --database=$DBNAME -e "SELECT column_name, character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users';" > $TESTDIR/text.txt 154 grep 'id'$'\t''255' $TESTDIR/text.txt 155 if [ $? != 0 ]; then 156 ErrorMsg "Database column 'id' should have character limit of 255" 157 fi 158 grep 'type'$'\t''256' $TESTDIR/text.txt 159 if [ $? != 0 ]; then 160 ErrorMsg "Database column 'affiliation' should have character limit of 256" 161 fi 162 grep 'affiliation'$'\t''1024' $TESTDIR/text.txt 163 if [ $? != 0 ]; then 164 ErrorMsg "Database column 'affiliation' should have character limit of 1024" 165 fi 166 grep 'attributes'$'\t''65535' $TESTDIR/text.txt 167 if [ $? != 0 ]; then 168 ErrorMsg "Database column 'attributes' should have character limit of 65535" 169 fi 170 grep 'incorrect_password_attempts' $TESTDIR/text.txt 171 if [ $? != 0 ]; then 172 ErrorMsg "Failed to create column 'incorrect_password_attempts' in MySQL" 173 fi 174 175 mysql --host=localhost --user=root --password=mysql --database=$DBNAME -e "SELECT column_name, character_maximum_length, data_type, extra FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'affiliations';" > $TESTDIR/text.txt 176 grep 'id'$'\t''NULL'$'\t''int'$'\t''auto_increment' $TESTDIR/text.txt 177 if [ $? != 0 ]; then 178 ErrorMsg "Integer auto_increment column 'id' should be present in the affiliations table" 179 fi 180 grep 'name'$'\t''1024' $TESTDIR/text.txt 181 if [ $? != 0 ]; then 182 ErrorMsg "Database column 'name' should have character limit of 1024" 183 fi 184 grep 'prekey'$'\t''1024' $TESTDIR/text.txt 185 if [ $? != 0 ]; then 186 ErrorMsg "Database column 'prekey' should have character limit of 1024" 187 fi 188 mysql --host=localhost --user=root --password=mysql --database=$DBNAME -e "SELECT column_name, character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'certificates' AND COLUMN_NAME = 'id';" | grep "255" 189 if [ $? != 0 ]; then 190 ErrorMsg "Database column 'id' should have character limit of 255" 191 fi 192 193 ###### POSTGRES ###### 194 $SCRIPTDIR/fabric-ca_setup.sh -I -S -X -D -d postgres # Start up the server and the new schema should get created 195 $SCRIPTDIR/fabric-ca_setup.sh -K # Kill the server 196 $SCRIPTDIR/fabric-ca_setup.sh -S -X -D -d postgres # Start up the server again and it should try to update the schema again, should result in no errors 197 if test $? -ne 0; then 198 ErrorMsg "Failed to start up server that is using the latest database schema" 199 fi 200 $SCRIPTDIR/fabric-ca_setup.sh -K # Kill the server 201 202 203 # Create the database tables using the old schema 204 echo "Creating '$DBNAME' Postgres database and tables before starting up server" 205 psql -c "drop database $DBNAME" 206 psql -c "create database $DBNAME" 207 psql -d $DBNAME -c "CREATE TABLE users (id VARCHAR(64), token bytea, type VARCHAR(64), affiliation VARCHAR(64), attributes VARCHAR(256), state INTEGER, max_enrollments INTEGER)" 208 psql -d $DBNAME -c "CREATE TABLE affiliations (name VARCHAR(64) NOT NULL UNIQUE, prekey VARCHAR(64))" 209 psql -d $DBNAME -c "CREATE TABLE certificates (id VARCHAR(64), serial_number bytea NOT NULL, authority_key_identifier bytea NOT NULL, ca_label bytea, status bytea NOT NULL, reason int, expiry timestamp, revoked_at timestamp, pem bytea NOT NULL, PRIMARY KEY(serial_number, authority_key_identifier))" 210 psql -d $DBNAME -c "SELECT character_maximum_length FROM information_schema.columns where table_name = 'users' AND column_name = 'id';" | grep "64" 211 if [ $? != 0 ]; then 212 ErrorMsg "Database column 'id' should have character limit of 64" 213 fi 214 215 # Start up the server and the schema should get updated 216 $SCRIPTDIR/fabric-ca_setup.sh -I -S -X -D -d postgres 217 218 enroll 219 if test $? -ne 0; then 220 ErrorMsg "Failed to enroll $REGISTRAR" 221 fi 222 223 # Register a user with a username of 128 character. This should pass with the updated schema 224 USERNAME=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 128 | head -n 1) 225 register "" $USERNAME 226 if test $? -ne 0; then 227 ErrorMsg "Failed to register $USERNAME" 228 fi 229 230 # Register a user with a username of 300 character. This should result in an error 231 USERNAME=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 300 | head -n 1) 232 register "" $USERNAME 233 if test $? -ne 1; then 234 ErrorMsg "Should have failed to register $USERNAME" 235 fi 236 237 $SCRIPTDIR/fabric-ca_setup.sh -K 238 239 # Check that the new schema took affect 240 psql -d $DBNAME -c "SELECT column_name, character_maximum_length FROM information_schema.columns where table_name = 'users';" > $TESTDIR/text.txt 241 grep -E 'id|255' $TESTDIR/text.txt 242 if [ $? != 0 ]; then 243 ErrorMsg "Database column 'id' should have character limit of 255" 244 fi 245 grep -E 'type|256' $TESTDIR/text.txt 246 if [ $? != 0 ]; then 247 ErrorMsg "Database column 'affiliation' should have character limit of 256" 248 fi 249 grep -E 'affiliation|1024' $TESTDIR/text.txt 250 if [ $? != 0 ]; then 251 ErrorMsg "Database column 'affiliation' should have character limit of 1024" 252 fi 253 psql -d $DBNAME -c "SELECT data_type FROM information_schema.columns where table_name = 'users' AND column_name = 'attributes';" | grep "text" 254 if [ $? != 0 ]; then 255 ErrorMsg "Database column 'affiliation' should be type 'text'" 256 fi 257 grep 'incorrect_password_attempts' $TESTDIR/text.txt 258 if [ $? != 0 ]; then 259 ErrorMsg "Database column 'incorrect_passwords_attempts' failed to be created" 260 fi 261 262 psql -d $DBNAME -c "SELECT column_name, character_maximum_length FROM information_schema.columns where table_name = 'affiliations';" > $TESTDIR/text.txt 263 grep -E 'name|1024' $TESTDIR/text.txt 264 if [ $? != 0 ]; then 265 ErrorMsg "Database column 'name' should have character limit of 1024" 266 fi 267 grep -E 'prekey|1024' $TESTDIR/text.txt 268 if [ $? != 0 ]; then 269 ErrorMsg "Database column 'prekey' should have character limit of 1024" 270 fi 271 272 psql -d $DBNAME -c "SELECT column_name, character_maximum_length FROM information_schema.columns where table_name = 'certificates' AND column_name = 'id';" | grep "255" 273 if [ $? != 0 ]; then 274 ErrorMsg "Database column 'id' should have character limit of 255" 275 fi 276 277 CleanUp $RC 278 exit $RC