github.com/silveraid/fabric-ca@v1.1.0-preview.0.20180127000700-71974f53ab08/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 '1' 63 if [ $? != 0 ]; then 64 ErrorMsg "Incorrect level found for 'identity.level' in properties table" 65 fi 66 67 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME 'pragma table_info(affiliations)' > $TESTDIR/output.txt 68 grep 'name|VARCHAR(1024)' $TESTDIR/output.txt 69 if [ $? != 0 ]; then 70 ErrorMsg "Database column 'name' should have character limit of 1024" 71 fi 72 grep 'prekey|VARCHAR(1024)' $TESTDIR/output.txt 73 if [ $? != 0 ]; then 74 ErrorMsg "Database column 'prekey' should have character limit of 1024" 75 fi 76 grep 'level|INTEGER' $TESTDIR/output.txt 77 if [ $? != 0 ]; then 78 ErrorMsg "Database column 'level' should be a INTEGER field" 79 fi 80 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME 'SELECT value FROM properties WHERE (property = "affiliation.level")' | grep '1' 81 if [ $? != 0 ]; then 82 ErrorMsg "Incorrect level found for 'affiliation.level' in properties table" 83 fi 84 85 86 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME 'pragma table_info(certificates)' > $TESTDIR/output.txt 87 grep 'id|VARCHAR(255)' $TESTDIR/output.txt 88 if [ $? != 0 ]; then 89 ErrorMsg "Database column 'id' should have character limit of 255" 90 fi 91 grep 'level|INTEGER' $TESTDIR/output.txt 92 if [ $? != 0 ]; then 93 ErrorMsg "Database column 'level' should be a INTEGER field" 94 fi 95 sqlite3 $FABRIC_CA_SERVER_HOME/$DBNAME 'SELECT value FROM properties WHERE (property = "certificate.level")' | grep '1' 96 if [ $? != 0 ]; then 97 ErrorMsg "Incorrect level found for 'certificate.level' in properties table" 98 fi 99 100 rm $FABRIC_CA_SERVER_HOME/$DBNAME 101 102 ###### MYSQL ###### 103 104 $SCRIPTDIR/fabric-ca_setup.sh -I -S -X -D -d mysql # Start up the server and the new schema should get created 105 $SCRIPTDIR/fabric-ca_setup.sh -K # Kill the server 106 $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 107 if test $? -ne 0; then 108 ErrorMsg "Failed to start up server that is using the latest database schema" 109 fi 110 $SCRIPTDIR/fabric-ca_setup.sh -K # Kill the server 111 112 # Create the database tables using the old schema 113 echo "Creating '$DBNAME' MySQL database and tables before starting up server" 114 mysql --host=localhost --user=root --password=mysql -e "drop database $DBNAME;" 115 mysql --host=localhost --user=root --password=mysql -e "create database $DBNAME;" 116 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;" 117 mysql --host=localhost --user=root --password=mysql --database=$DBNAME -e "CREATE TABLE affiliations (name VARCHAR(64) NOT NULL UNIQUE, prekey VARCHAR(64));" 118 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;" 119 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" 120 if [ $? != 0 ]; then 121 ErrorMsg "Database column 'id' should have character limit of 64" 122 fi 123 124 # Start up the server and the schema should get updated 125 $SCRIPTDIR/fabric-ca_setup.sh -I -S -X -D -d mysql 126 127 enroll 128 if test $? -ne 0; then 129 ErrorMsg "Failed to enroll $REGISTRAR" 130 fi 131 132 # Register a user with a username of 128 character. This should pass with the updated schema 133 USERNAME=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 128 | head -n 1) 134 register "" $USERNAME 135 if test $? -ne 0; then 136 ErrorMsg "Failed to register $USERNAME" 137 fi 138 139 # Register a user with a username of 300 character. This should result in an error 140 USERNAME=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 300 | head -n 1) 141 register "" $USERNAME 142 if test $? -ne 1; then 143 ErrorMsg "Should have failed to register $USERNAME" 144 fi 145 146 $SCRIPTDIR/fabric-ca_setup.sh -K 147 148 # Check that the new schema took affect 149 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 150 grep 'id'$'\t''255' $TESTDIR/text.txt 151 if [ $? != 0 ]; then 152 ErrorMsg "Database column 'id' should have character limit of 255" 153 fi 154 grep 'type'$'\t''256' $TESTDIR/text.txt 155 if [ $? != 0 ]; then 156 ErrorMsg "Database column 'affiliation' should have character limit of 256" 157 fi 158 grep 'affiliation'$'\t''1024' $TESTDIR/text.txt 159 if [ $? != 0 ]; then 160 ErrorMsg "Database column 'affiliation' should have character limit of 1024" 161 fi 162 grep 'attributes'$'\t''65535' $TESTDIR/text.txt 163 if [ $? != 0 ]; then 164 ErrorMsg "Database column 'attributes' should have character limit of 65535" 165 fi 166 167 mysql --host=localhost --user=root --password=mysql --database=$DBNAME -e "SELECT column_name, character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'affiliations';" > $TESTDIR/text.txt 168 grep 'name'$'\t''1024' $TESTDIR/text.txt 169 if [ $? != 0 ]; then 170 ErrorMsg "Database column 'name' should have character limit of 1024" 171 fi 172 grep 'prekey'$'\t''1024' $TESTDIR/text.txt 173 if [ $? != 0 ]; then 174 ErrorMsg "Database column 'prekey' should have character limit of 1024" 175 fi 176 177 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" 178 if [ $? != 0 ]; then 179 ErrorMsg "Database column 'id' should have character limit of 255" 180 fi 181 182 ###### POSTGRES ###### 183 $SCRIPTDIR/fabric-ca_setup.sh -I -S -X -D -d postgres # Start up the server and the new schema should get created 184 $SCRIPTDIR/fabric-ca_setup.sh -K # Kill the server 185 $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 186 if test $? -ne 0; then 187 ErrorMsg "Failed to start up server that is using the latest database schema" 188 fi 189 $SCRIPTDIR/fabric-ca_setup.sh -K # Kill the server 190 191 192 # Create the database tables using the old schema 193 echo "Creating '$DBNAME' Postgres database and tables before starting up server" 194 psql -c "drop database $DBNAME" 195 psql -c "create database $DBNAME" 196 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)" 197 psql -d $DBNAME -c "CREATE TABLE affiliations (name VARCHAR(64) NOT NULL UNIQUE, prekey VARCHAR(64))" 198 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))" 199 psql -d $DBNAME -c "SELECT character_maximum_length FROM information_schema.columns where table_name = 'users' AND column_name = 'id';" | grep "64" 200 if [ $? != 0 ]; then 201 ErrorMsg "Database column 'id' should have character limit of 64" 202 fi 203 204 # Start up the server and the schema should get updated 205 $SCRIPTDIR/fabric-ca_setup.sh -I -S -X -D -d postgres 206 207 enroll 208 if test $? -ne 0; then 209 ErrorMsg "Failed to enroll $REGISTRAR" 210 fi 211 212 # Register a user with a username of 128 character. This should pass with the updated schema 213 USERNAME=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 128 | head -n 1) 214 register "" $USERNAME 215 if test $? -ne 0; then 216 ErrorMsg "Failed to register $USERNAME" 217 fi 218 219 # Register a user with a username of 300 character. This should result in an error 220 USERNAME=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 300 | head -n 1) 221 register "" $USERNAME 222 if test $? -ne 1; then 223 ErrorMsg "Should have failed to register $USERNAME" 224 fi 225 226 $SCRIPTDIR/fabric-ca_setup.sh -K 227 228 # Check that the new schema took affect 229 psql -d $DBNAME -c "SELECT column_name, character_maximum_length FROM information_schema.columns where table_name = 'users';" > $TESTDIR/text.txt 230 grep 'id | 255' $TESTDIR/text.txt 231 if [ $? != 0 ]; then 232 ErrorMsg "Database column 'id' should have character limit of 255" 233 fi 234 grep 'type | 256' $TESTDIR/text.txt 235 if [ $? != 0 ]; then 236 ErrorMsg "Database column 'affiliation' should have character limit of 256" 237 fi 238 grep 'affiliation | 1024' $TESTDIR/text.txt 239 if [ $? != 0 ]; then 240 ErrorMsg "Database column 'affiliation' should have character limit of 1024" 241 fi 242 psql -d $DBNAME -c "SELECT data_type FROM information_schema.columns where table_name = 'users' AND column_name = 'attributes';" | grep "text" 243 if [ $? != 0 ]; then 244 ErrorMsg "Database column 'affiliation' should be type 'text'" 245 fi 246 247 psql -d $DBNAME -c "SELECT column_name, character_maximum_length FROM information_schema.columns where table_name = 'affiliations';" > $TESTDIR/text.txt 248 grep 'name | 1024' $TESTDIR/text.txt 249 if [ $? != 0 ]; then 250 ErrorMsg "Database column 'name' should have character limit of 1024" 251 fi 252 grep 'prekey | 1024' $TESTDIR/text.txt 253 if [ $? != 0 ]; then 254 ErrorMsg "Database column 'prekey' should have character limit of 1024" 255 fi 256 257 psql -d $DBNAME -c "SELECT column_name, character_maximum_length FROM information_schema.columns where table_name = 'certificates' AND column_name = 'id';" | grep "255" 258 if [ $? != 0 ]; then 259 ErrorMsg "Database column 'id' should have character limit of 255" 260 fi 261 262 CleanUp $RC 263 exit $RC