test/sqlapi/Csql/DDLStmt/pkeyfkeyonalldatatype.c

   1 /*
   2   Primary key(t1) and foreign key(t2) testing, for all datatypes.
   3   try to drop t1 . it should fail.
   4 */
   5
   6 #include"common.h"
   7
   8 int main()
   9 {
  10     DbRetVal rv = OK;
  11     AbsSqlConnection *con = createConnection();
  12     rv = con->connect("root","manager");
  13     if(rv !=OK) {
  14         delete con;
  15         return 1;
  16     }
  17     printf("Connection opened\n");
  18     AbsSqlStatement *stmt = createStatement();
  19     stmt->setConnection(con);
  20 //Creating Table
  21     char statement[400];
  22     strcpy(statement,"CREATE TABLE pkeyTINY(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f1));");
  23     int rows=0;
  24     rv = stmt->prepare(statement);
  25     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 2; }
  26     rv = stmt->execute(rows);
  27     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 3; }
  28     stmt->free();
  29
  30     strcpy(statement,"CREATE TABLE fkeyTINY(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f1) references pkeyTINY(f1));");
  31     rows=0;
  32     rv = stmt->prepare(statement);
  33     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 4; }
  34     rv = stmt->execute(rows);
  35     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 5; }
  36     stmt->free();
  37
  38     rv = stmt->prepare("DROP TABLE pkeyTINY;");
  39     rv = stmt->execute(rows);
  40     if(rv==OK) { delete stmt; con->disconnect(); delete con; return 6; }
  41     printf("Pkey table pkeyTINY can not be dropped withouting dropping Fkey Table fkeyTINY\n");
  42     stmt->free();
  43
  44     strcpy(statement,"CREATE TABLE pkeySMALL(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f2));");
  45     rows=0;
  46     rv = stmt->prepare(statement);
  47     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 7; }
  48     rv = stmt->execute(rows);
  49     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 8; }
  50     stmt->free();
  51
  52     strcpy(statement,"CREATE TABLE fkeySMALL(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f2) references pkeySMALL(f2));");
  53     rows=0;
  54     rv = stmt->prepare(statement);
  55     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 9; }
  56     rv = stmt->execute(rows);
  57     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 10; }
  58     stmt->free();
  59
  60     rv = stmt->prepare("DROP TABLE pkeySMALL;");
  61     rv = stmt->execute(rows);
  62     if(rv==OK) { delete stmt; con->disconnect(); delete con; return 11; }
  63     printf("Pkey table pkeySMALL can not be dropped withouting dropping Fkey Table fkeySMALL\n");
  64     stmt->free();
  65
  66     strcpy(statement,"CREATE TABLE pkeyINT(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f3));");
  67     rows=0;
  68     rv = stmt->prepare(statement);
  69     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 12; }
  70     rv = stmt->execute(rows);
  71     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 13; }
  72     stmt->free();
  73
  74     strcpy(statement,"CREATE TABLE fkeyINT(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f3) references pkeyINT(f3));");
  75     rows=0;
  76     rv = stmt->prepare(statement);
  77     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 14; }
  78     rv = stmt->execute(rows);
  79     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 15; }
  80     stmt->free();
  81
  82     rv = stmt->prepare("DROP TABLE pkeyINT;");
  83     rv = stmt->execute(rows);
  84     if(rv==OK) { delete stmt; con->disconnect(); delete con; return 16; }
  85     printf("Pkey table pkeyINT can not be dropped withouting dropping Fkey Table fkeyINT\n");
  86     stmt->free();
  87
  88     strcpy(statement,"CREATE TABLE pkeyBIG(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f4));");
  89     rows=0;
  90     rv = stmt->prepare(statement);
  91     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 17; }
  92     rv = stmt->execute(rows);
  93     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 18; }
  94     stmt->free();
  95
  96     strcpy(statement,"CREATE TABLE fkeyBIG(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f4) references pkeyBIG(f4));");
  97     rows=0;
  98     rv = stmt->prepare(statement);
  99     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 19; }
 100     rv = stmt->execute(rows);
 101     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 20; }
 102     stmt->free();
 103
 104     rv = stmt->prepare("DROP TABLE pkeyBIG;");
 105     rv = stmt->execute(rows);
 106     if(rv==OK) { delete stmt; con->disconnect(); delete con; return 21; }
 107     printf("Pkey table pkeyBIG can not be dropped withouting dropping Fkey Table fkeyBIG\n");
 108     stmt->free();
 109
 110     strcpy(statement,"CREATE TABLE pkeyCHAR(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f5));");
 111     rows=0;
 112     rv = stmt->prepare(statement);
 113     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 22; }
 114     rv = stmt->execute(rows);
 115     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 23; }
 116     stmt->free();
 117
 118     strcpy(statement,"CREATE TABLE fkeyCHAR(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f5) references pkeyCHAR(f5));");
 119     rows=0;
 120     rv = stmt->prepare(statement);
 121     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 24; }
 122     rv = stmt->execute(rows);
 123     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 25; }
 124     stmt->free();
 125
 126     rv = stmt->prepare("DROP TABLE pkeyCHAR;");
 127     rv = stmt->execute(rows);
 128     if(rv==OK) { delete stmt; con->disconnect(); delete con; return 26; }
 129     printf("Pkey table pkeyCHAR can not be dropped withouting dropping Fkey Table fkeyCHAR\n");
 130     stmt->free();
 131
 132     strcpy(statement,"CREATE TABLE pkeyVARCHAR(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f6));");
 133     rows=0;
 134     rv = stmt->prepare(statement);
 135     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 27; }
 136     rv = stmt->execute(rows);
 137     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 28; }
 138     stmt->free();
 139
 140     strcpy(statement,"CREATE TABLE fkeyVARCHAR(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f6) references pkeyVARCHAR(f6));");
 141     rows=0;
 142     rv = stmt->prepare(statement);
 143     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 29; }
 144     rv = stmt->execute(rows);
 145     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 30; }
 146     stmt->free();
 147
 148     rv = stmt->prepare("DROP TABLE pkeyVARCHAR;");
 149     rv = stmt->execute(rows);
 150     if(rv==OK) { delete stmt; con->disconnect(); delete con; return 31; }
 151     printf("Pkey table pkeyVARCHAR can not be dropped withouting dropping Fkey Table fkeyVARCHAR\n");
 152     stmt->free();
 153
 154     strcpy(statement,"CREATE TABLE pkeyDATE(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f9));");
 155     rows=0;
 156     rv = stmt->prepare(statement);
 157     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 32; }
 158     rv = stmt->execute(rows);
 159     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 33; }
 160     stmt->free();
 161
 162     strcpy(statement,"CREATE TABLE fkeyDATE(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f9) references pkeyDATE(f9));");
 163     rows=0;
 164     rv = stmt->prepare(statement);
 165     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 34; }
 166     rv = stmt->execute(rows);
 167     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 35; }
 168     stmt->free();
 169
 170     rv = stmt->prepare("DROP TABLE pkeyDATE;");
 171     rv = stmt->execute(rows);
 172     if(rv==OK) { delete stmt; con->disconnect(); delete con; return 36; }
 173     printf("Pkey table pkeyDATE can not be dropped withouting dropping Fkey Table fkeyDATE\n");
 174     stmt->free();
 175
 176     strcpy(statement,"CREATE TABLE pkeyTIME(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f10));");
 177     rows=0;
 178     rv = stmt->prepare(statement);
 179     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 37; }
 180     rv = stmt->execute(rows);
 181     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 38; }
 182     stmt->free();
 183
 184     strcpy(statement,"CREATE TABLE fkeyTIME(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f10) references pkeyTIME(f10));");
 185     rows=0;
 186     rv = stmt->prepare(statement);
 187     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 39; }
 188     rv = stmt->execute(rows);
 189     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 40; }
 190     stmt->free();
 191
 192     rv = stmt->prepare("DROP TABLE pkeyTIME;");
 193     rv = stmt->execute(rows);
 194     if(rv==OK) { delete stmt; con->disconnect(); delete con; return 41; }
 195     printf("Pkey table pkeyTIME can not be dropped withouting dropping Fkey Table fkeyTIME\n");
 196     stmt->free();
 197
 198 // Show all tables
 199     strcpy(statement,"GETALLTABLES;");
 200     rows=0;
 201     rv = stmt->prepare(statement);
 202     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 42; }
 203     stmt->execute(rows);
 204     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 43; }
 205     printf("\t TABLES\n");
 206     printf("\t--------\n");
 207     while(stmt->next() !=NULL) {
 208         printf("\t%s\n",stmt->getFieldValuePtr(2));  //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
 209     }
 210     stmt->free();
 211
 212 //Droping tables
 213     rv = stmt->prepare("DROP TABLE fkeyTINY;");
 214     rv = stmt->execute(rows);
 215     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 44; }
 216     printf("fkeyTINY Table Dropped\n");
 217     stmt->free();
 218     rv = stmt->prepare("DROP TABLE pkeyTINY;");
 219     rv = stmt->execute(rows);
 220     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 45; }
 221     printf("pkeyTINY Table Dropped\n");
 222     stmt->free();
 223     rv = stmt->prepare("DROP TABLE fkeySMALL;");
 224     rv = stmt->execute(rows);
 225     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 46; }
 226     printf("fkeySMALL Table Dropped\n");
 227     stmt->free();
 228     rv = stmt->prepare("DROP TABLE pkeySMALL;");
 229     rv = stmt->execute(rows);
 230     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 47; }
 231     printf("pkeySMALL Table Dropped\n");
 232     stmt->free();
 233     rv = stmt->prepare("DROP TABLE fkeyINT;");
 234     rv = stmt->execute(rows);
 235     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 48; }
 236     printf("fkeyINT Table Dropped\n");
 237     stmt->free();
 238     rv = stmt->prepare("DROP TABLE pkeyINT;");
 239     rv = stmt->execute(rows);
 240     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 49; }
 241     printf("pkeyINT Table Dropped\n");
 242     stmt->free();
 243     rv = stmt->prepare("DROP TABLE fkeyBIG;");
 244     rv = stmt->execute(rows);
 245     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 50; }
 246     printf("fkeyBIG Table Dropped\n");
 247     stmt->free();
 248     rv = stmt->prepare("DROP TABLE pkeyBIG;");
 249     rv = stmt->execute(rows);
 250     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 51; }
 251     printf("pkeyBIG Table Dropped\n");
 252     stmt->free();
 253     rv = stmt->prepare("DROP TABLE fkeyCHAR;");
 254     rv = stmt->execute(rows);
 255     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 52; }
 256     printf("fkeyCHAR Table Dropped\n");
 257     stmt->free();
 258     rv = stmt->prepare("DROP TABLE pkeyCHAR;");
 259     rv = stmt->execute(rows);
 260     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 53; }
 261     printf("pkeyCHAR Table Dropped\n");
 262     stmt->free();
 263     rv = stmt->prepare("DROP TABLE fkeyVARCHAR;");
 264     rv = stmt->execute(rows);
 265     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 54; }
 266     printf("fkeyVARCHAR Table Dropped\n");
 267     stmt->free();
 268     rv = stmt->prepare("DROP TABLE pkeyVARCHAR;");
 269     rv = stmt->execute(rows);
 270     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 55; }
 271     printf("pkeyVARCHAR Table Dropped\n");
 272     stmt->free();
 273     rv = stmt->prepare("DROP TABLE fkeyDATE;");
 274     rv = stmt->execute(rows);
 275     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 56; }
 276     printf("fkeyDATE Table Dropped\n");
 277     stmt->free();
 278     rv = stmt->prepare("DROP TABLE pkeyDATE;");
 279     rv = stmt->execute(rows);
 280     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 57; }
 281     printf("pkeyDATE Table Dropped\n");
 282     stmt->free();
 283     rv = stmt->prepare("DROP TABLE fkeyTIME;");
 284     rv = stmt->execute(rows);
 285     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 58; }
 286     printf("fkeyTIME Table Dropped\n");
 287     stmt->free();
 288     rv = stmt->prepare("DROP TABLE pkeyTIME;");
 289     rv = stmt->execute(rows);
 290     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 59; }
 291     printf("pkeyTIME Table Dropped\n");
 292     stmt->free();
 293
 294 // Show all tables
 295     strcpy(statement,"GETALLTABLES;");
 296     rows=0;
 297     rv = stmt->prepare(statement);
 298     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 60; }
 299     stmt->execute(rows);
 300     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 61; }
 301     while(stmt->next() !=NULL) {
 302         printf("Table Name is %s\n",stmt->getFieldValuePtr(2));  //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
 303     }
 304
 305     stmt->free();
 306     con->disconnect();
 307     printf("Connection Closed\n");
 308
 309     delete stmt; delete con;
 310     return 0;
 311 }
 312