MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
main.cpp
1 /* Copyright (C) 2008 Sun Microsystems Inc.
2 
3  This program is free software; you can redistribute it and/or modify
4  it under the terms of the GNU General Public License as published by
5  the Free Software Foundation; version 2 of the License.
6 
7  This program is distributed in the hope that it will be useful,
8  but WITHOUT ANY WARRANTY; without even the implied warranty of
9  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10  GNU General Public License for more details.
11 
12  You should have received a copy of the GNU General Public License
13  along with this program; if not, write to the Free Software
14  Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA */
15 
16 /**************************************************************
17  *
18  * NOTE THAT THIS TOOL CAN ONLY BE RUN AGAINST THE EMPLOYEES DATABASE
19  * TABLES WHICH IS A SEPERATE DOWNLOAD AVAILABLE AT WWW.MYSQL.COM.
20  **************************************************************/
21 
22 
23 // Used for cout
24 #include <iostream>
25 #include <stdio.h>
26 #include <time.h>
27 #include <assert.h>
28 
29 #include <mysql.h>
30 #include <mysqld_error.h>
31 #include <NdbApi.hpp>
32 
33 #include "NdbQueryBuilder.hpp"
34 #include "NdbQueryOperation.hpp"
35 
36 #define USE_RECATTR
37 
41 #define PRINT_ERROR(code,msg) \
42  std::cout << "Error in " << __FILE__ << ", line: " << __LINE__ \
43  << ", code: " << code \
44  << ", msg: " << msg << "." << std::endl
45 #define MYSQLERROR(mysql) { \
46  PRINT_ERROR(mysql_errno(&mysql),mysql_error(&mysql)); \
47  exit(-1); }
48 #define PRINT_APIERROR(error) { \
49  PRINT_ERROR((error).code,(error).message); }
50 #define APIERROR(error) { \
51  PRINT_APIERROR(error); \
52  exit(-1); }
53 
54 
55 /*****************************************************
56 ** Defines record structure for the rows in our tables
57 ******************************************************/
58 struct ManagerRow
59 {
60  char dept_no[4];
61  Uint32 emp_no;
62  Int32 from_date;
63  Int32 to_date;
64  Uint32 my_key;
65 };
66 
67 struct ManagerPKRow
68 {
69  Uint32 emp_no;
70  char dept_no[4];
71 };
72 
73 struct EmployeeRow
74 {
75  Uint32 emp_no;
76  Int32 birth_date; // sizeof(date)....?
77  char first_name[14+1];
78  char last_name[16+1];
79  char gender;
80  Int32 hire_date;
81 };
82 
83 struct SalaryRow
84 {
85  Uint32 emp_no;
86  Int32 from_date;
87  Uint32 salary;
88  Int32 to_date;
89 };
90 
91 
92 const char* employeeDef =
93 "CREATE TABLE employees ("
94 " emp_no INT NOT NULL,"
95 " dept_no CHAR(4) NOT NULL," // Temporary added OJA
96 " birth_date DATE NOT NULL,"
97 " first_name VARCHAR(14) NOT NULL,"
98 " last_name VARCHAR(16) NOT NULL,"
99 " gender ENUM ('M','F') NOT NULL, "
100 " hire_date DATE NOT NULL,"
101 " PRIMARY KEY (emp_no))"
102 " ENGINE=NDB";
103 
104 const char* departmentsDef =
105 "CREATE TABLE departments ("
106 " dept_no CHAR(4) NOT NULL,"
107 " dept_name VARCHAR(40) NOT NULL,"
108 " PRIMARY KEY (dept_no),"
109 " UNIQUE KEY (dept_name))"
110 " ENGINE=NDB";
111 
112 const char* dept_managerDef =
113 "CREATE TABLE dept_manager ("
114 " dept_no CHAR(4) NOT NULL,"
115 " emp_no INT NOT NULL,"
116 " from_date DATE NOT NULL,"
117 " to_date DATE NOT NULL,"
118 " my_key INT NOT NULL,"
119 " KEY (emp_no),"
120 " KEY (dept_no),"
121 //" FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,"
122 //" FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,"
123 " UNIQUE KEY MYINDEXNAME (my_key),"
124 " PRIMARY KEY (emp_no,dept_no))"
125 " ENGINE=NDB"
126 //" PARTITION BY KEY(dept_no)"
127 ;
128 
129 const char* dept_empDef =
130 "CREATE TABLE dept_emp ("
131 " emp_no INT NOT NULL,"
132 " dept_no CHAR(4) NOT NULL,"
133 " from_date DATE NOT NULL,"
134 " to_date DATE NOT NULL,"
135 " KEY (emp_no),"
136 " KEY (dept_no),"
137 " FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,"
138 " FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,"
139 " PRIMARY KEY (emp_no,dept_no))"
140 " ENGINE=NDB";
141 
142 const char* titlesDef =
143 "CREATE TABLE titles ("
144 " emp_no INT NOT NULL,"
145 " title VARCHAR(50) NOT NULL,"
146 " from_date DATE NOT NULL,"
147 " to_date DATE,"
148 " KEY (emp_no),"
149 " FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,"
150 " PRIMARY KEY (emp_no,title, from_date))"
151 " ENGINE=NDB";
152 
153 const char* salariesDef =
154 "CREATE TABLE salaries ("
155 " emp_no INT NOT NULL,"
156 " salary INT NOT NULL,"
157 " from_date DATE NOT NULL,"
158 " to_date DATE NOT NULL,"
159 " KEY (emp_no),"
160 " FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,"
161 " PRIMARY KEY (emp_no, from_date))"
162 " ENGINE=NDB";
163 
164 
165 int createEmployeeDb(MYSQL& mysql)
166 {
167  if (true)
168  {
169  mysql_query(&mysql, "DROP DATABASE employees");
170  printf("Dropped existing employees DB\n");
171  mysql_query(&mysql, "CREATE DATABASE employees");
172  mysql_commit(&mysql);
173  printf("Created new employees DB\n");
174 
175  if (mysql_query(&mysql, "USE employees") != 0) MYSQLERROR(mysql);
176  mysql_commit(&mysql);
177  printf("USE employees DB\n");
178 
179  if (mysql_query(&mysql, employeeDef) != 0) MYSQLERROR(mysql);
180  mysql_commit(&mysql);
181  printf("Created 'employee' table\n");
182 
183  if (mysql_query(&mysql, departmentsDef) != 0) MYSQLERROR(mysql);
184  mysql_commit(&mysql);
185  printf("Created 'departments' table\n");
186 
187  if (mysql_query(&mysql, dept_managerDef) != 0) MYSQLERROR(mysql);
188  mysql_commit(&mysql);
189  printf("Created 'dept_manager' table\n");
190 
191  if (mysql_query(&mysql, dept_empDef) != 0) MYSQLERROR(mysql);
192  mysql_commit(&mysql);
193  printf("Created 'dept_emp' table\n");
194 
195  if (mysql_query(&mysql, titlesDef) != 0) MYSQLERROR(mysql);
196  mysql_commit(&mysql);
197  printf("Created 'titles' table\n");
198 
199  if (mysql_query(&mysql, salariesDef) != 0) MYSQLERROR(mysql);
200  mysql_commit(&mysql);
201  printf("Created 'salaries' table\n");
202 
203 
204  printf("Insert simple test data\n");
205 
206  if (mysql_query(&mysql, "Insert into dept_manager(dept_no,emp_no,my_key) values ('d005',110567,110567)") != 0) MYSQLERROR(mysql);
207  mysql_commit(&mysql);
208  if (mysql_query(&mysql, "Insert into dept_manager(dept_no,emp_no,my_key) values ('c005',11057,11067)") != 0) MYSQLERROR(mysql);
209  mysql_commit(&mysql);
210  if (mysql_query(&mysql, "Insert into dept_manager(dept_no,emp_no,my_key) values ('e005',210567,210567)") != 0) MYSQLERROR(mysql);
211  mysql_commit(&mysql);
212 
213  if (mysql_query(&mysql, "Insert into dept_manager(dept_no,emp_no,my_key) values ('f005',210568,210568)") != 0) MYSQLERROR(mysql);
214  mysql_commit(&mysql);
215  if (mysql_query(&mysql, "Insert into dept_manager(dept_no,emp_no,my_key) values ('g005',210569,210569)") != 0) MYSQLERROR(mysql);
216  mysql_commit(&mysql);
217  if (mysql_query(&mysql, "Insert into dept_manager(dept_no,emp_no,my_key) values ('h005',210560,210560)") != 0) MYSQLERROR(mysql);
218  mysql_commit(&mysql);
219  if (mysql_query(&mysql, "Insert into dept_manager(dept_no,emp_no,my_key) values ('i005',210561,210561)") != 0) MYSQLERROR(mysql);
220  mysql_commit(&mysql);
221 
222  if (mysql_query(&mysql, "Insert into employees(emp_no,dept_no) values (110567,'d005')") != 0) MYSQLERROR(mysql);
223  mysql_commit(&mysql);
224 
225  }
226 
227  return 1;
228 }
229 
230 #if 0
231 /**************************************************************
232  * Initialise NdbRecord structures for table and index access *
233  **************************************************************/
234 static void init_ndbrecord_info(Ndb &myNdb)
235 {
236  NdbDictionary::Dictionary* myDict= myNdb.getDictionary();
237  manager = myDict->getTable("dept_manager");
238  employee= myDict->getTable("employees");
239  salary = myDict->getTable("salaries");
240 
241  if (!employee || !manager || !salary)
242  APIERROR(myDict->getNdbError());
243 
244  rowManagerRecord = manager->getDefaultRecord();
245  if (rowManagerRecord == NULL) APIERROR(myDict->getNdbError());
246 
247  rowEmployeeRecord = employee->getDefaultRecord();
248  if (rowEmployeeRecord == NULL) APIERROR(myDict->getNdbError());
249 
250  rowSalaryRecord = salary->getDefaultRecord();
251  if (rowSalaryRecord == NULL) APIERROR(myDict->getNdbError());
252 
253  // Lookup Primary key for salaries table
254  const NdbDictionary::Index *myPIndex= myDict->getIndex("PRIMARY", "salaries");
255  if (myPIndex == NULL)
256  APIERROR(myDict->getNdbError());
257 
258  indexSalaryRecord = myPIndex->getDefaultRecord();
259  if (indexSalaryRecord == NULL) APIERROR(myDict->getNdbError());
260 }
261 #endif
262 
263 
272 int testQueryBuilder(Ndb &myNdb)
273 {
274  const NdbDictionary::Table *manager, *employee, *salary;
275  int res;
276  NdbTransaction* myTransaction = NULL;
277  NdbQuery* myQuery = NULL;
278 
279  const char* dept_no = "d005";
280  Uint32 emp_no = 110567;
281 
282  ManagerRow managerRow;
283  EmployeeRow employeeRow;
284 
285  printf("\n -- Building query --\n");
286 
287  NdbDictionary::Dictionary* myDict= myNdb.getDictionary();
288  manager = myDict->getTable("dept_manager");
289  employee= myDict->getTable("employees");
290  salary = myDict->getTable("salaries");
291 
292  if (!employee || !manager || !salary)
293  APIERROR(myDict->getNdbError());
294 
296  // Prepare alternatine non-default NdbRecords for MANAGER table
298  NdbRecord *rowManagerRecord;
299 
300  {
301  const NdbDictionary::Column *manager_dept_no;
302  const NdbDictionary::Column *manager_emp_no;
303  const NdbDictionary::Column *manager_from_date;
304  const NdbDictionary::Column *manager_to_date;
305 
306  manager_dept_no = manager->getColumn("dept_no");
307  if (manager_dept_no == NULL) APIERROR(myDict->getNdbError());
308  manager_emp_no = manager->getColumn("emp_no");
309  if (manager_emp_no == NULL) APIERROR(myDict->getNdbError());
310  manager_from_date = manager->getColumn("from_date");
311  if (manager_from_date == NULL) APIERROR(myDict->getNdbError());
312  manager_to_date = manager->getColumn("to_date");
313  if (manager_to_date == NULL) APIERROR(myDict->getNdbError());
314 
315  const NdbDictionary::RecordSpecification mngSpec[] = {
316  {manager_emp_no, offsetof(ManagerRow, emp_no), 0,0},
317 // {manager_dept_no, offsetof(ManagerRow, dept_no), 0,0},
318 // {manager_from_date, offsetof(ManagerRow, from_date), 0,0},
319  {manager_to_date, offsetof(ManagerRow, to_date), 0,0}
320  };
321 
322  rowManagerRecord =
323  myDict->createRecord(manager, mngSpec, 2, sizeof(mngSpec[0]));
324  if (rowManagerRecord == NULL) APIERROR(myDict->getNdbError());
325  }
326 
327 
338  NdbQueryBuilder* const myBuilder = NdbQueryBuilder::create(myNdb);
339 
340 #if 0
341  printf("Compare with old API interface\n");
342 
343  {
344  myTransaction= myNdb.startTransaction();
345  if (myTransaction == NULL) APIERROR(myNdb.getNdbError());
346 
347  // Lookup Primary key for manager table
348  const NdbDictionary::Index *myPIndex= myDict->getIndex("PRIMARY", manager->getName());
349  if (myPIndex == NULL)
350  APIERROR(myDict->getNdbError());
351 
352  NdbIndexScanOperation* ixScan =
353  myTransaction->scanIndex(myPIndex->getDefaultRecord(),
354  manager->getDefaultRecord());
355 
356  if (ixScan == NULL)
357  APIERROR(myTransaction->getNdbError());
358 
359 
360  /* Add a bound
361  */
362  ManagerPKRow low={0,"d005"};
363  ManagerPKRow high={110567,"d005"};
364 
365  NdbIndexScanOperation::IndexBound bound;
366  bound.low_key=(char*)&low;
367  bound.low_key_count=2;
368  bound.low_inclusive=true;
369  bound.high_key=(char*)&high;
370  bound.high_key_count=2;
371  bound.high_inclusive=false;
372  bound.range_no=0;
373 
374  if (ixScan->setBound(myPIndex->getDefaultRecord(), bound))
375  APIERROR(myTransaction->getNdbError());
376  }
377 #endif
378 
379 #if 1
380  /* qt1 is 'const defined' */
381  printf("q1\n");
382  const NdbQueryDef* q1 = 0;
383  {
384  NdbQueryBuilder* qb = myBuilder; //myDict->getQueryBuilder();
385 
386  const NdbQueryOperand* managerKey[] = // Manager is indexed om {"dept_no", "emp_no"}
387  { qb->constValue("d005"), // dept_no = "d005"
388  qb->constValue(110567), // emp_no = 110567
389  0
390  };
391  const NdbQueryLookupOperationDef *readManager = qb->readTuple(manager, managerKey);
392  if (readManager == NULL) APIERROR(qb->getNdbError());
393 
394  q1 = qb->prepare();
395  if (q1 == NULL) APIERROR(qb->getNdbError());
396 
397  // Some operations are intentionally disallowed through private declaration
398 // delete readManager;
399 // NdbQueryLookupOperationDef illegalAssign = *readManager;
400 // NdbQueryLookupOperationDef *illegalCopy1 = new NdbQueryLookupOperationDef(*readManager);
401 // NdbQueryLookupOperationDef illegalCopy2(*readManager);
402  }
403 
404  printf("q2\n");
405  const NdbQueryDef* q2 = 0;
406  {
407  NdbQueryBuilder* qb = myBuilder; //myDict->getQueryBuilder();
408 
409  // Manager key defined as parameter
410  const NdbQueryOperand* managerKey[] = // Manager is indexed om {"dept_no", "emp_no"}
411  { qb->paramValue(), // dept_no parameter,
412  qb->paramValue("emp"), // emp_no parameter - param naming is optional
413  0
414  };
415  // Lookup on a single tuple with key define by 'managerKey' param. tuple
416  const NdbQueryLookupOperationDef* readManager = qb->readTuple(manager, managerKey);
417  if (readManager == NULL) APIERROR(qb->getNdbError());
418 
419  q2 = qb->prepare();
420  if (q2 == NULL) APIERROR(qb->getNdbError());
421  }
422 
423 /**** UNFINISHED...
424  printf("q3\n");
425  const NdbQueryDef* q3 = 0;
426  {
427  NdbQueryBuilder* qb = myBuilder; //myDict->getQueryBuilder();
428 
429  const NdbQueryIndexBound* managerBound = // Manager is indexed om {"dept_no", "emp_no"}
430  { ....
431  };
432  // Lookup on a single tuple with key define by 'managerKey' param. tuple
433  const NdbQueryScanNode *scanManager = qb->scanIndex(manager, managerKey);
434  if (scanManager == NULL) APIERROR(qb->getNdbError());
435 
436  q3 = qb->prepare();
437  if (q3 == NULL) APIERROR(qb->getNdbError());
438  }
439 *****/
440 #endif
441 
442 
443 #if 1
444 {
445  /* Composite operations building real *trees* aka. linked operations.
446  * (First part is identical to building 'qt2' above)
447  *
448  * The related SQL query which this simulates would be something like:
449  *
450  * select * from dept_manager join employees using(emp_no)
451  * where dept_no = 'd005' and emp_no = 110567;
452  */
453  printf("q4\n");
454  const NdbQueryDef* q4 = 0;
455  {
456  NdbQueryBuilder* qb = myBuilder; //myDict->getQueryBuilder();
457 
458  const NdbQueryOperand* constManagerKey[] = // Manager is indexed om {"dept_no", "emp_no"}
459  { qb->constValue("d005"), // dept_no = "d005"
460  qb->constValue(110567), // emp_no = 110567
461  0
462  };
463  const NdbQueryOperand* paramManagerKey[] = // Manager is indexed om {"dept_no", "emp_no"}
464  { qb->paramValue(), // dept_no parameter,
465  qb->paramValue("emp"), // emp_no parameter - param naming is optional
466  0
467  };
468  // Lookup a single tuple with key define by 'managerKey' param. tuple
469  const NdbQueryLookupOperationDef *readManager = qb->readTuple(manager, paramManagerKey);
470  //const NdbQueryLookupOperationDef *readManager = qb->readTuple(manager, constManagerKey);
471  if (readManager == NULL) APIERROR(qb->getNdbError());
472 
473  // THEN: employee table is joined:
474  // A linked value is used to let employee lookup refer values
475  // from the parent operation on manger.
476 
477  const NdbQueryOperand* joinEmployeeKey[] = // Employee is indexed om {"emp_no"}
478  { qb->linkedValue(readManager, "emp_no"), // where '= readManger.emp_no'
479  0
480  };
481  const NdbQueryLookupOperationDef* readEmployee = qb->readTuple(employee, joinEmployeeKey);
482  if (readEmployee == NULL) APIERROR(qb->getNdbError());
483 
484  q4 = qb->prepare();
485  if (q4 == NULL) APIERROR(qb->getNdbError());
486  }
487 
489  // q4 may later be executed as:
490  // (Possibly multiple ::execute() or multiple NdbQueryDef instances
491  // within the same NdbTransaction::execute(). )
493  NdbQueryParamValue paramList[] = {dept_no, emp_no};
494 
495  myTransaction= myNdb.startTransaction();
496  if (myTransaction == NULL) APIERROR(myNdb.getNdbError());
497 
498  myQuery = myTransaction->createQuery(q4,paramList);
499  if (myQuery == NULL)
500  APIERROR(myTransaction->getNdbError());
501 
502 #ifdef USE_RECATTR
503  const NdbRecAttr *key[2][2];
504 
505  for (Uint32 i=0; i<myQuery->getNoOfOperations(); ++i)
506  {
507  NdbQueryOperation* op = myQuery->getQueryOperation(i);
508  const NdbDictionary::Table* table = op->getQueryOperationDef().getTable();
509 
510  key[i][0] = op->getValue(table->getColumn(0));
511  key[i][1] = op->getValue(table->getColumn(1));
512  }
513 
514 #else
515 {
516  memset (&managerRow, 0, sizeof(managerRow));
517  memset (&employeeRow, 0, sizeof(employeeRow));
518  const NdbRecord* rowManagerRecord = manager->getDefaultRecord();
519  if (rowManagerRecord == NULL) APIERROR(myDict->getNdbError());
520 
521  const NdbRecord* rowEmployeeRecord = employee->getDefaultRecord();
522  if (rowEmployeeRecord == NULL) APIERROR(myDict->getNdbError());
523 
524  assert(myQuery->getNoOfOperations()==2);
525  NdbQueryOperation* op0 = myQuery->getQueryOperation(0U);
526  NdbQueryOperation* op1 = myQuery->getQueryOperation(1U);
527 
528  op0->setResultRowBuf(rowManagerRecord, (char*)&managerRow);
529  op1->setResultRowBuf(rowEmployeeRecord, (char*)&employeeRow);
530 }
531 #endif
532 
533  printf("Start execute\n");
534  if (myTransaction->execute(NdbTransaction::NoCommit) != 0 ||
535  myQuery->getNdbError().code)
536  {
537  APIERROR(myQuery->getNdbError());
538  }
539  printf("Done executed\n");
540 
541  // All NdbQuery operations are handled as scans with cursor placed 'before'
542  // first record: Fetch next to retrieve result:
543  res = myQuery->nextResult();
544  if (res == NdbQuery::NextResult_error)
545  APIERROR(myQuery->getNdbError());
546 
547 #ifdef USE_RECATTR
548  printf("manager emp_no: %d\n", key[0][1]->u_32_value());
549  printf("employee emp_no: %d\n", key[1][0]->u_32_value());
550  assert(!key[0][1]->isNULL() && key[0][1]->u_32_value()==emp_no);
551  assert(!key[1][0]->isNULL() && key[1][0]->u_32_value()==emp_no);
552 #else
553  // NOW: Result is available in 'managerRow' buffer
554  printf("manager emp_no: %d\n", managerRow.emp_no);
555  printf("employee emp_no: %d\n", employeeRow.emp_no);
556  assert(managerRow.emp_no==emp_no);
557  assert(employeeRow.emp_no==emp_no);
558 #endif
559 
560  myQuery->close();
561 
562  myNdb.closeTransaction(myTransaction);
563  myTransaction = 0;
564 }
565 #endif
566 
567 
568 #if 1
569 {
571  printf("q4_1\n");
572  const NdbQueryDef* q4_1 = 0;
573  {
574  NdbQueryBuilder* qb = myBuilder; //myDict->getQueryBuilder();
575 
576  const NdbQueryOperand* empKey[] = // Employee is indexed om {"emp_no"}
577  {
578  //qb->constValue(110567), // emp_no = 110567
579  qb->paramValue(),
580  0
581  };
582  const NdbQueryLookupOperationDef* readEmployee = qb->readTuple(employee, empKey);
583  if (readEmployee == NULL) APIERROR(qb->getNdbError());
584 
585  const NdbQueryOperand* joinManagerKey[] = // Manager is indexed om {"dept_no", "emp_no"}
586  {
587  qb->paramValue(),
588  //qb->constValue(1005), // dept_no = "d005"
589  //qb->linkedValue(readEmployee,"dept_no"),
590  qb->linkedValue(readEmployee,"emp_no"), // emp_no = 110567
591  //qb->constValue(110567),
592  //qb->paramValue(),
593  0
594  };
595 
596  // Join with a single tuple with key defined by linked employee fields
597  const NdbQueryLookupOperationDef *readManager = qb->readTuple(manager, joinManagerKey);
598  if (readManager == NULL) APIERROR(qb->getNdbError());
599 
600  q4_1 = qb->prepare();
601  if (q4_1 == NULL) APIERROR(qb->getNdbError());
602  }
603 
605  // q4 may later be executed as:
606  // (Possibly multiple ::execute() or multiple NdbQueryDef instances
607  // within the same NdbTransaction::execute(). )
609 
610 //NdbQueryParamValue paramList_q4[] = {emp_no};
611 //NdbQueryParamValue paramList_q4[] = {dept_no};
612  NdbQueryParamValue paramList_q4[] = {emp_no, dept_no};
613 
614  myTransaction= myNdb.startTransaction();
615  if (myTransaction == NULL) APIERROR(myNdb.getNdbError());
616 
617  myQuery = myTransaction->createQuery(q4_1,paramList_q4);
618  if (myQuery == NULL)
619  APIERROR(myTransaction->getNdbError());
620 
621 #ifdef USE_RECATTR
622  const NdbRecAttr *value_q4[2][2];
623 
624  for (Uint32 i=0; i<myQuery->getNoOfOperations(); ++i)
625  {
626  NdbQueryOperation* op = myQuery->getQueryOperation(i);
627  const NdbDictionary::Table* table = op->getQueryOperationDef().getTable();
628 
629  value_q4[i][0] = op->getValue(table->getColumn(0));
630  value_q4[i][1] = op->getValue(table->getColumn(1));
631  }
632 #else
633 {
634  memset (&managerRow, 0, sizeof(managerRow));
635  memset (&employeeRow, 0, sizeof(employeeRow));
636  const NdbRecord* rowEmployeeRecord = employee->getDefaultRecord();
637  if (rowEmployeeRecord == NULL) APIERROR(myDict->getNdbError());
638 
639  const NdbRecord* rowManagerRecord = manager->getDefaultRecord();
640  if (rowManagerRecord == NULL) APIERROR(myDict->getNdbError());
641 
642  assert(myQuery->getNoOfOperations()==2);
643  NdbQueryOperation* op0 = myQuery->getQueryOperation(0U);
644  NdbQueryOperation* op1 = myQuery->getQueryOperation(1U);
645 
646  op0->setResultRowBuf(rowEmployeeRecord, (char*)&employeeRow);
647  op1->setResultRowBuf(rowManagerRecord, (char*)&managerRow);
648 }
649 #endif
650 
651  printf("Start execute\n");
652  if (myTransaction->execute(NdbTransaction::NoCommit) != 0 ||
653  myQuery->getNdbError().code)
654  {
655  APIERROR(myQuery->getNdbError());
656  }
657  printf("Done executed\n");
658 
659  // All NdbQuery operations are handled as scans with cursor placed 'before'
660  // first record: Fetch next to retrieve result:
661  res = myQuery->nextResult();
662  if (res == NdbQuery::NextResult_error)
663  APIERROR(myQuery->getNdbError());
664 
665 #ifdef USE_RECATTR
666  printf("employee emp_no: %d\n", value_q4[0][0]->u_32_value());
667  printf("manager emp_no: %d\n", value_q4[1][1]->u_32_value());
668  assert(!value_q4[0][0]->isNULL() && value_q4[0][0]->u_32_value()==emp_no);
669  assert(!value_q4[1][1]->isNULL() && value_q4[1][1]->u_32_value()==emp_no);
670 
671 #else
672  printf("employee emp_no: %d\n", employeeRow.emp_no);
673  printf("manager emp_no: %d\n", managerRow.emp_no);
674  assert(managerRow.emp_no==emp_no);
675  assert(employeeRow.emp_no==emp_no);
676 #endif
677 
678  myQuery->close();
679 
680  myNdb.closeTransaction(myTransaction);
681  myTransaction = 0;
682 }
683 #endif
684 
686 
687 #if 1
688 {
689  // Example: ::readTuple() using Index for unique key lookup
690  printf("q5\n");
691 
692  const NdbQueryDef* q5 = 0;
693  {
694  NdbQueryBuilder* qb = myBuilder; //myDict->getQueryBuilder();
695 
696  // Lookup Primary key for manager table
697  const NdbDictionary::Index *myPIndex= myDict->getIndex("MYINDEXNAME$unique", manager->getName());
698  if (myPIndex == NULL)
699  APIERROR(myDict->getNdbError());
700 
701  // Manager index-key defined as parameter, NB: Reversed order compared to hash key
702  const NdbQueryOperand* managerKey[] = // Manager PK index is {"emp_no","dept_no", }
703  {
704  //qb->constValue(110567), // emp_no = 110567
705  qb->paramValue(),
706  0
707  };
708  // Lookup on a single tuple with key define by 'managerKey' param. tuple
709  const NdbQueryLookupOperationDef* readManager = qb->readTuple(myPIndex, manager, managerKey);
710  if (readManager == NULL) APIERROR(qb->getNdbError());
711 
712  q5 = qb->prepare();
713  if (q5 == NULL) APIERROR(qb->getNdbError());
714  }
715 
716  myTransaction= myNdb.startTransaction();
717  if (myTransaction == NULL) APIERROR(myNdb.getNdbError());
718 
719  NdbQueryParamValue paramList_q5[] = {emp_no};
720  myQuery = myTransaction->createQuery(q5,paramList_q5);
721  if (myQuery == NULL)
722  APIERROR(myTransaction->getNdbError());
723 
724 #ifdef USE_RECATTR
725  const NdbRecAttr *value_q5[2];
726 
727  NdbQueryOperation* op = myQuery->getQueryOperation(0U);
728  const NdbDictionary::Table* table = op->getQueryOperationDef().getTable();
729 
730  value_q5[0] = op->getValue(table->getColumn(0));
731  value_q5[1] = op->getValue(table->getColumn(1));
732 #else
733 {
734  memset (&managerRow, 0, sizeof(managerRow));
735  const NdbRecord* rowManagerRecord = manager->getDefaultRecord();
736  if (rowManagerRecord == NULL) APIERROR(myDict->getNdbError());
737 
738  // Specify result handling NdbRecord style - need the (single) NdbQueryOperation:
739  NdbQueryOperation* op = myQuery->getQueryOperation(0U);
740 
741  op->setResultRowBuf(rowManagerRecord, (char*)&managerRow);
742 }
743 #endif
744 
745  printf("Start execute\n");
746  if (myTransaction->execute(NdbTransaction::NoCommit) != 0 ||
747  myQuery->getNdbError().code)
748  {
749  APIERROR(myQuery->getNdbError());
750  }
751  printf("Done executed\n");
752 
753  // All NdbQuery operations are handled as scans with cursor placed 'before'
754  // first record: Fetch next to retrieve result:
755  res = myQuery->nextResult();
756  if (res == NdbQuery::NextResult_error)
757  APIERROR(myQuery->getNdbError());
758 
759 #ifdef USE_RECATTR
760  printf("employee emp_no: %d\n", value_q5[1]->u_32_value());
761  assert(!value_q5[1]->isNULL() && value_q5[1]->u_32_value()==emp_no);
762 #else
763  printf("employee emp_no: %d\n", managerRow.emp_no);
764  assert(managerRow.emp_no==emp_no);
765 #endif
766 
767  myQuery->close();
768 
769  myNdb.closeTransaction(myTransaction);
770  myTransaction = 0;
771 }
772 #endif
773 
774 #if 1
775 {
776  printf("q6: Table scan + linked lookup\n");
777 
778  const NdbQueryDef* q6 = 0;
779  {
780  NdbQueryBuilder* qb = myBuilder; //myDict->getQueryBuilder();
781 
782 /****
783  // Lookup Primary key for manager table
784  const NdbDictionary::Index *myPIndex= myDict->getIndex("PRIMARY", manager->getName());
785  if (myPIndex == NULL)
786  APIERROR(myDict->getNdbError());
787 
788  const NdbQueryOperand* low[] = // Manager PK index is {"emp_no","dept_no", }
789  { qb->constValue(110567), // emp_no = 110567
790  0
791  };
792  const NdbQueryOperand* high[] = // Manager PK index is {"emp_no","dept_no", }
793  { qb->constValue("illegal key"),
794  0
795  };
796  const NdbQueryIndexBound bound (low, NULL); // emp_no = [110567, oo]
797  const NdbQueryIndexBound bound_illegal(low, high); // 'high' is char type -> illegal
798  const NdbQueryIndexBound boundEq(low);
799 ****/
800  // Lookup on a single tuple with key define by 'managerKey' param. tuple
801 // const NdbQueryScanOperationDef* scanManager = qb->scanIndex(myPIndex, manager, &boundEq);
802  const NdbQueryScanOperationDef* scanManager = qb->scanTable(manager);
803  if (scanManager == NULL) APIERROR(qb->getNdbError());
804 
805  // THEN: employee table is joined:
806  // A linked value is used to let employee lookup refer values
807  // from the parent operation on manager.
808 
809  const NdbQueryOperand* empJoinKey[] = // Employee is indexed om {"emp_no"}
810  { qb->linkedValue(scanManager, "emp_no"), // where '= readManger.emp_no'
811  0
812  };
813  const NdbQueryLookupOperationDef* readEmployee = qb->readTuple(employee, empJoinKey);
814  if (readEmployee == NULL) APIERROR(qb->getNdbError());
815 
816  q6 = qb->prepare();
817  if (q6 == NULL) APIERROR(qb->getNdbError());
818  }
819 
820  myTransaction= myNdb.startTransaction();
821  if (myTransaction == NULL) APIERROR(myNdb.getNdbError());
822 
823  myQuery = myTransaction->createQuery(q6, (NdbQueryParamValue*)0);
824  if (myQuery == NULL)
825  APIERROR(myTransaction->getNdbError());
826 
827 #ifdef USE_RECATTR
828  const NdbRecAttr* value_q6[2][2];
829 
830  for (Uint32 i=0; i<myQuery->getNoOfOperations(); ++i)
831  {
832  NdbQueryOperation* op = myQuery->getQueryOperation(i);
833  const NdbDictionary::Table* table = op->getQueryOperationDef().getTable();
834 
835  value_q6[i][0] = op->getValue(table->getColumn(0));
836  value_q6[i][1] = op->getValue(table->getColumn(1));
837  }
838 #else
839 {
840  int err;
841  const NdbRecord* rowManagerRecord = manager->getDefaultRecord();
842  if (rowManagerRecord == NULL) APIERROR(myDict->getNdbError());
843 
844  assert(myQuery->getNoOfOperations()==2);
845  NdbQueryOperation* op0 = myQuery->getQueryOperation(0U);
846  err = op0->setResultRowBuf(rowManagerRecord, (char*)&managerRow);
847  assert (err==0);
848 //if (err == NULL) APIERROR(op0->getNdbError());
849 
850  const NdbRecord* rowEmployeeRecord = employee->getDefaultRecord();
851  if (rowEmployeeRecord == NULL) APIERROR(myDict->getNdbError());
852 
853  NdbQueryOperation* op1 = myQuery->getQueryOperation(1U);
854  err = op1->setResultRowBuf(rowEmployeeRecord, (char*)&employeeRow);
855  assert (err==0);
856 //if (err == NULL) APIERROR(op1->getNdbError());
857 }
858 #endif
859 
860  printf("Start execute\n");
861  if (myTransaction->execute(NdbTransaction::NoCommit) != 0 ||
862  myQuery->getNdbError().code)
863  {
864  APIERROR(myQuery->getNdbError());
865  }
866 
867  int cnt = 0;
868  while (true) {
869  memset (&managerRow, 0, sizeof(managerRow));
870  memset (&employeeRow, 0, sizeof(employeeRow));
871 
872  // All NdbQuery operations are handled as scans with cursor placed 'before'
873  // first record: Fetch next to retrieve result:
874  NdbQuery::NextResultOutcome res = myQuery->nextResult();
875 
876  if (res == NdbQuery::NextResult_error) {
877  PRINT_APIERROR(myQuery->getNdbError());
878  break;
879  } else if (res!=NdbQuery::NextResult_gotRow) {
880  break;
881  }
882 
883 #ifdef USE_RECATTR
884  printf("manager emp_no: %d, NULL:%d\n",
885  value_q6[0][1]->u_32_value(), myQuery->getQueryOperation(0U)->isRowNULL());
886  printf("employee emp_no: %d, NULL:%d\n",
887  value_q6[1][0]->u_32_value(), myQuery->getQueryOperation(1U)->isRowNULL());
888 #else
889  // NOW: Result is available in row buffers
890  printf("manager emp_no: %d, NULL:%d\n",
891  managerRow.emp_no, myQuery->getQueryOperation(0U)->isRowNULL());
892  printf("employee emp_no: %d, NULL:%d\n",
893  employeeRow.emp_no, myQuery->getQueryOperation(1U)->isRowNULL());
894 #endif
895  cnt++;
896  };
897  printf("EOF, %d rows\n", cnt);
898  myQuery->close();
899 
900  myNdb.closeTransaction(myTransaction);
901  myTransaction = 0;
902 }
903 #endif
904 
905 #if 1
906 {
907  printf("Ordered index scan + lookup\n");
908 
909  const NdbQueryDef* q6_1 = 0;
910  {
911  NdbQueryBuilder* qb = myBuilder; //myDict->getQueryBuilder();
912 
913  // Lookup Primary key for manager table
914  const NdbDictionary::Index *myPIndex= myDict->getIndex("PRIMARY", manager->getName());
915  if (myPIndex == NULL)
916  APIERROR(myDict->getNdbError());
917 
918  const NdbQueryOperand* low[] = // Manager PK index is {"emp_no","dept_no", }
919  {
920  qb->paramValue(),
921 // qb->constValue(110567), // emp_no = 110567
922  qb->constValue("d005"), // dept_no = "d005"
923  0
924  };
925  const NdbQueryOperand* high[] = // Manager PK index is {"emp_no","dept_no", }
926  {
927  qb->constValue(110567), // emp_no = 110567
928  qb->constValue("d005"), // dept_no = "d005"
929  0
930  };
931  const NdbQueryIndexBound bound (low, high); // emp_no = [110567, oo]
932  const NdbQueryIndexBound boundEq(low);
933 
934  // Lookup on a single tuple with key define by 'managerKey' param. tuple
935  const NdbQueryScanOperationDef* scanManager = qb->scanIndex(myPIndex, manager, &bound);
936  if (scanManager == NULL) APIERROR(qb->getNdbError());
937 
938  // THEN: employee table is joined:
939  // A linked value is used to let employee lookup refer values
940  // from the parent operation on manager.
941 
942  const NdbQueryOperand* empJoinKey[] = // Employee is indexed om {"emp_no"}
943  { qb->linkedValue(scanManager, "emp_no"), // where '= readManger.emp_no'
944  0
945  };
946  const NdbQueryLookupOperationDef* readEmployee = qb->readTuple(employee, empJoinKey);
947  if (readEmployee == NULL) APIERROR(qb->getNdbError());
948 
949  q6_1 = qb->prepare();
950  if (q6_1 == NULL) APIERROR(qb->getNdbError());
951  }
952 
953  myTransaction= myNdb.startTransaction();
954  if (myTransaction == NULL) APIERROR(myNdb.getNdbError());
955 
956  NdbQueryParamValue paramList_q6_1[] = {emp_no};
957  myQuery = myTransaction->createQuery(q6_1, paramList_q6_1);
958  if (myQuery == NULL)
959  APIERROR(myTransaction->getNdbError());
960 
961 #ifdef USE_RECATTR
962  const NdbRecAttr* value_q6_1[2][2];
963 
964  for (Uint32 i=0; i<myQuery->getNoOfOperations(); ++i)
965  {
966  NdbQueryOperation* op = myQuery->getQueryOperation(i);
967  const NdbDictionary::Table* table = op->getQueryOperationDef().getTable();
968 
969  value_q6_1[i][1] = op->getValue(table->getColumn(1));
970  value_q6_1[i][0] = op->getValue(table->getColumn(0));
971  }
972 #else
973 {
974  int err;
975 //int mask = 0x03;
976  const NdbRecord* rowManagerRecord = manager->getDefaultRecord();
977  if (rowManagerRecord == NULL) APIERROR(myDict->getNdbError());
978 
979  assert(myQuery->getNoOfOperations()==2);
980  NdbQueryOperation* op0 = myQuery->getQueryOperation(0U);
981  err = op0->setResultRowBuf(rowManagerRecord, (char*)&managerRow /*, (const unsigned char*)&mask*/);
982  assert (err==0);
983  if (err) APIERROR(myQuery->getNdbError());
984 
985  const NdbRecord* rowEmployeeRecord = employee->getDefaultRecord();
986  if (rowEmployeeRecord == NULL) APIERROR(myDict->getNdbError());
987 
988  NdbQueryOperation* op1 = myQuery->getQueryOperation(1U);
989  err = op1->setResultRowBuf(rowEmployeeRecord, (char*)&employeeRow /*, (const unsigned char*)&mask*/);
990  assert (err==0);
991  if (err) APIERROR(myQuery->getNdbError());
992 }
993 #endif
994 
995  printf("Start execute\n");
996  if (myTransaction->execute(NdbTransaction::NoCommit) != 0 ||
997  myQuery->getNdbError().code)
998  {
999  APIERROR(myQuery->getNdbError());
1000  }
1001  printf("Done executed\n");
1002 
1003  int cnt = 0;
1004  while (true) {
1005  memset (&managerRow, 0, sizeof(managerRow));
1006  memset (&employeeRow, 0, sizeof(employeeRow));
1007 
1008  // All NdbQuery operations are handled as scans with cursor placed 'before'
1009  // first record: Fetch next to retrieve result:
1010  NdbQuery::NextResultOutcome res = myQuery->nextResult();
1011 
1012  if (res == NdbQuery::NextResult_error) {
1013  PRINT_APIERROR(myQuery->getNdbError());
1014  break;
1015  } else if (res!=NdbQuery::NextResult_gotRow) {
1016  break;
1017  }
1018 
1019 #ifdef USE_RECATTR
1020  printf("manager emp_no: %d, NULL:%d\n",
1021  value_q6_1[0][1]->u_32_value(), myQuery->getQueryOperation(0U)->isRowNULL());
1022  printf("employee emp_no: %d, NULL:%d\n",
1023  value_q6_1[1][0]->u_32_value(), myQuery->getQueryOperation(1U)->isRowNULL());
1024 #else
1025  // NOW: Result is available in row buffers
1026  printf("manager emp_no: %d, NULL:%d\n",
1027  managerRow.emp_no, myQuery->getQueryOperation(0U)->isRowNULL());
1028  printf("employee emp_no: %d, NULL:%d\n",
1029  employeeRow.emp_no, myQuery->getQueryOperation(1)->isRowNULL());
1030 #endif
1031  cnt++;
1032  };
1033 
1034  printf("EOF, %d rows\n", cnt);
1035  myQuery->close();
1036 
1037  myNdb.closeTransaction(myTransaction);
1038  myTransaction = 0;
1039 }
1040 #endif
1041 
1042  myBuilder->destroy();
1043  return 0;
1044 }
1045 
1046 
1047 int
1048 main(int argc, const char** argv){
1049  if(argc!=4){
1050  std::cout << "Usage: " << argv[0]
1051  << " <mysql IP address> <mysql port> <cluster connect string>"
1052  << std::endl;
1053  exit(-1);
1054  }
1055  const char* const host=argv[1];
1056  const int port = atoi(argv[2]);
1057  const char* const connectString = argv[3];
1058 
1059  //extern const char *my_progname;
1060  //NDB_INIT(argv[0]);
1061  ndb_init();
1062  MYSQL mysql;
1063  if(!mysql_init(&mysql)){
1064  std::cout << "mysql_init() failed:" << std::endl;
1065  }
1066  if(!mysql_real_connect(&mysql, host, "root", "", "",
1067  port, NULL, 0)){
1068  std::cout << "mysql_real_connect() failed:" << std::endl;
1069  }
1070 
1071 
1072  if (!createEmployeeDb(mysql))
1073  { std::cout << "Create of employee DB failed" << std::endl;
1074  exit(-1);
1075  }
1076  mysql_close(&mysql);
1077 
1078  /**************************************************************
1079  * Connect to ndb cluster *
1080  **************************************************************/
1081  {
1082  Ndb_cluster_connection cluster_connection(connectString);
1083  if (cluster_connection.connect(4, 5, 1))
1084  {
1085  std::cout << "Unable to connect to cluster within 30 secs." << std::endl;
1086  exit(-1);
1087  }
1088  // Optionally connect and wait for the storage nodes (ndbd's)
1089  if (cluster_connection.wait_until_ready(30,0) < 0)
1090  {
1091  std::cout << "Cluster was not ready within 30 secs.\n";
1092  exit(-1);
1093  }
1094  Ndb myNdb(&cluster_connection,"employees");
1095  if (myNdb.init(1024) == -1) { // Set max 1024 parallel transactions
1096  APIERROR(myNdb.getNdbError());
1097  exit(-1);
1098  }
1099  std::cout << "Connected to Cluster\n";
1100 
1101  /*******************************************
1102  * Check table existence *
1103  *******************************************/
1104  if (true)
1105  {
1106  bool has_tables = true;
1107  const NdbDictionary::Dictionary* myDict= myNdb.getDictionary();
1108 
1109  if (myDict->getTable("departments") == 0)
1110  { std::cout << "Table 'departments' not found" << std::endl;
1111  has_tables = false;
1112  }
1113  if (myDict->getTable("employees") == 0)
1114  { std::cout << "Table 'employees' not found" << std::endl;
1115  has_tables = false;
1116  }
1117  if (myDict->getTable("dept_emp") == 0)
1118  { std::cout << "Table 'dept_emp' not found" << std::endl;
1119  has_tables = false;
1120  }
1121  if (myDict->getTable("dept_manager") == 0)
1122  { std::cout << "Table 'dept_manager' not found" << std::endl;
1123  has_tables = false;
1124  }
1125  if (myDict->getTable("salaries") == 0)
1126  { std::cout << "Table 'salaries' not found" << std::endl;
1127  has_tables = false;
1128  }
1129  if (myDict->getTable("titles") == 0)
1130  { std::cout << "Table 'titles' not found" << std::endl;
1131  has_tables = false;
1132  }
1133  if (!has_tables)
1134  { std::cout << "Table(s) was missing from the 'employees' DB" << std::endl;
1135  exit(-1);
1136  }
1137  std::cout << "All tables in 'employees' DB was found" << std::endl;
1138  }
1139 
1140  testQueryBuilder(myNdb);
1141 
1142  } // Must call ~Ndb_cluster_connection() before ndb_end().
1143  ndb_end(0);
1144  return 0;
1145 }
1146 
1147 
1148