Tutorial
Open connection and create default memory database
xdb_res_t *pRes;
xdb_row_t *pRow;
xdb_conn_t *pConn = xdb_open (":memory:");
XDB_CHECK (NULL != pConn, printf ("failed to create DB\n"); return -1;);
Create Table
pRes = xdb_exec (pConn, "CREATE TABLE IF NOT EXISTS student (id INT PRIMARY KEY, name CHAR(16), age INT, class CHAR(16), score FLOAT, info CHAR(255))");
XDB_RESCHK(pRes, printf ("Can't create table student\n"); goto error;);
pRes = xdb_exec (pConn, "CREATE TABLE IF NOT EXISTS teacher (id INT PRIMARY KEY, name CHAR(16), age INT, info CHAR(255), INDEX (name))");
XDB_RESCHK(pRes, printf ("Can't create table teacher\n"); goto error;);
pRes = xdb_exec (pConn, "CREATE TABLE IF NOT EXISTS book (id INT PRIMARY KEY, name CHAR(64), author CHAR(32), count INT, INDEX (name))");
XDB_RESCHK(pRes, printf ("Can't create table book\n"); goto error;);
Insert Rows
pRes = xdb_exec (pConn, "INSERT INTO student (id,name,age,class,score) VALUES (1,'jack',10,'3-1',90),(2,'tom',11,'2-5',91),(3,'jack',11,'1-6',92),(4,'rose',10,'4-2',90),(5,'tim',10,'3-1',95)");
XDB_RESCHK(pRes, printf ("Can't insert table student\n"); goto error;);
pRes = xdb_pexec (pConn, "INSERT INTO student (id,name,age,class,score,info) VALUES (6,'Tony',10,'3-1',95,'%s')", "He is a boy.\nHe likes playing football.\nWe all like him!");
XDB_RESCHK(pRes, printf ("Can't insert table student\n"); goto error;);
pRes = xdb_pexec (pConn, "INSERT INTO student (id,name,age,class,score,info) VALUES (7,'Wendy',10,'3-1',95,'%s')", "She is a girl.\nShe likes cooking.\nWe all love her!");
XDB_RESCHK(pRes, printf ("Can't insert table student\n"); goto error;);
pRes = xdb_exec (pConn, "INSERT INTO teacher (id,name,age) VALUES (1,'Tomas',40),(2,'Steven',50),(3,'Bill',31),(4,'Lucy',29)");
XDB_RESCHK(pRes, printf ("Can't insert table teacher\n"); goto error;);
pRes = xdb_exec (pConn, "INSERT INTO book (id,name,author,count) VALUES (1,'Romeo and Juliet','Shakespeare',10),(2,'Pride and Prejudice','Austen',5),(3,'Great Expectations','Dickens',8),(4,'Sorrows of Young Werther','Von Goethe',4)");
XDB_RESCHK(pRes, printf ("Can't insert table book\n"); goto error;);
Select Rows
pRes = xdb_exec (pConn, "SELECT * from student");
printf ("=== Select all %d rows\n", (int)pRes->row_count);
while (NULL != (pRow = xdb_fetch_row (pRes))) {
xdb_print_row (pRes->col_meta, pRow, 0);
printf ("\n");
}
xdb_free_result (pRes);
Update Rows
printf ("\n=== Update age = 9 for id = 2\n");
pRes = xdb_exec (pConn, "UPDATE student set age=9 WHERE id = 2");
XDB_RESCHK(pRes, printf ("Can't update id=%d\n",2); goto error;);
pRes = xdb_exec (pConn, "SELECT id,name,age,class,score from student WHERE id = 2");
printf (" select %d rows\n ", (int)pRes->row_count);
while (NULL != (pRow = xdb_fetch_row (pRes))) {
xdb_print_row (pRes->col_meta, pRow, 0);
printf ("\n");
}
xdb_free_result (pRes);
Delete Rows
printf ("\n=== Delete id = 3\n");
pRes = xdb_exec (pConn, "DELETE FROM student WHERE id = 3");
XDB_RESCHK(pRes, printf ("Can't delete id=%d\n",3); goto error;);
pRes = xdb_exec (pConn, "SELECT * from student WHERE id = 3");
printf (" select %d rows\n", (int)pRes->row_count);
while (NULL != (pRow = xdb_fetch_row (pRes))) {
xdb_print_row (pRes->col_meta, pRow, 0);
printf ("\n");
}
xdb_free_result (pRes);
Aggregation function
printf ("\n=== AGG COUNT,MIN,MAX,SUM,AVG\n");
pRes = xdb_exec (pConn, "SELECT COUNT(*),MIN(score),MAX(score),SUM(score),AVG(score) FROM student");
printf (" --- select %d rows\n ", (int)pRes->row_count);
if (NULL != (pRow = xdb_fetch_row (pRes))) {
xdb_print_row (pRes->col_meta, pRow, 0);
printf ("\n");
}
xdb_free_result (pRes);
Transaction Rollback
printf ("\n=== Rollback\n");
xdb_begin (pConn);
printf (" update age=15 for id = 2\n");
pRes = xdb_exec (pConn, "UPDATE student set age=15 WHERE id = 2");
pRes = xdb_exec (pConn, "SELECT id,name,age from student WHERE id = 2");
printf (" select %d rows: ", (int)pRes->row_count);
if (NULL != (pRow = xdb_fetch_row (pRes))) {
xdb_print_row (pRes->col_meta, pRow, 0);
printf ("\n");
}
xdb_free_result (pRes);
printf (" -- rollback\n");
xdb_rollback (pConn);
pRes = xdb_exec (pConn, "SELECT id,name,age from student WHERE id = 2");
printf (" select %d rows: ", (int)pRes->row_count);
if (NULL != (pRow = xdb_fetch_row (pRes))) {
xdb_print_row (pRes->col_meta, pRow, 0);
printf ("\n");
}
xdb_free_result (pRes);
Transaction Commit
printf ("\n=== Commit\n");
xdb_begin (pConn);
printf (" update age=15 for id = 2\n");
pRes = xdb_exec (pConn, "UPDATE student set age=15 WHERE id = 2");
pRes = xdb_exec (pConn, "SELECT * from student WHERE id = 2");
printf (" select %d rows: ", (int)pRes->row_count);
if (NULL != (pRow = xdb_fetch_row (pRes))) {
xdb_print_row (pRes->col_meta, pRow, 0);
printf ("\n");
}
xdb_free_result (pRes);
printf (" -- commit\n");
xdb_commit (pConn);
pRes = xdb_exec (pConn, "SELECT * from student WHERE id = 2");
printf (" select %d rows: ", (int)pRes->row_count);
if (NULL != (pRow = xdb_fetch_row (pRes))) {
xdb_print_row (pRes->col_meta, pRow, 0);
printf ("\n");
}
xdb_free_result (pRes);
Multi-Statements
printf ("\n=== Muti-Statements\n");
pRes = xdb_exec (pConn, "SELECT COUNT(*) FROM student; SELECT id,name FROM student WHERE id=2");
printf (" -- 1st result: ");
// count(*)
if (NULL != (pRow = xdb_fetch_row (pRes))) {
xdb_print_row (pRes->col_meta, pRow, 0);
printf ("\n");
}
xdb_free_result (pRes);
// select
printf (" -- 2nd result: ");
pRes = xdb_next_result (pConn);
if (NULL != pRes) {
if (NULL != (pRow = xdb_fetch_row (pRes))) {
xdb_print_row (pRes->col_meta, pRow, 0);
printf ("\n");
}
xdb_free_result (pRes);
}
Enter Embedded Shell
printf ("\n=== Enter interactive embedded shell\n");
xdb_exec (pConn, "SHELL");
Example Output
crossdb$ make example
=== Select all 7 rows
id=1 name='jack' age=10 class='3-1' score=90.000000 info=''
id=2 name='tom' age=11 class='2-5' score=91.000000 info=''
id=3 name='jack' age=11 class='1-6' score=92.000000 info=''
id=4 name='rose' age=10 class='4-2' score=90.000000 info=''
id=5 name='tim' age=10 class='3-1' score=95.000000 info=''
id=6 name='Tony' age=10 class='3-1' score=95.000000 info='He is a boy.
He likes playing football.
We all like him!'
id=7 name='Wendy' age=10 class='3-1' score=95.000000 info='She is a girl.
She likes cooking.
We all love her!'
=== Update age = 9 for id = 2
select 1 rows
id=2 name='tom' age=9 class='2-5' score=91.000000
id=2 name='tom' age=9 class='2-5' score=91.000000
id=2 name='tom' age=9 class='2-5' score=91.000000
=== Delete id = 3
select 0 rows
=== AGG COUNT,MIN,MAX,SUM,AVG
--- select 1 rows
COUNT(*)=6 MIN(score)=90.000000 MAX(score)=95.000000 SUM(score)=556.000000 AVG(score)=92.666667
COUNT(*)=6 MIN(score)=90.000000 MAX(score)=95.000000 SUM(score)=556.000000 AVG(score)=92.666667
COUNT(*)=6 MIN(score)=90.000000 MAX(score)=95.000000 SUM(score)=556.000000 AVG(score)=92.666667
=== Rollback
update age=15 for id = 2
select 1 rows: id=2 name='tom' age=15
-- rollback
select 1 rows: id=2 name='tom' age=9
=== Commit
update age=15 for id = 2
select 1 rows: id=2 name='tom' age=15 class='2-5' score=91.000000 info=''
-- commit
select 1 rows: id=2 name='tom' age=15 class='2-5' score=91.000000 info=''
=== Muti-Statements
-- 1st result: COUNT(*)=6
-- 2nd result: id=2 name='tom'
=== Enter interactive embedded shell
_____ _____ ____ _
/ ____| | __ \| _ \ _| |_
| | _ __ ___ ___ ___| | | | |_) | |_ _|
| | | '__/ _ \/ __/ __| | | | _ < |_|
| |____| | | (_) \__ \__ \ |__| | |_) | 0.8.0
\_____|_| \___/|___/___/_____/|____/ crossdb.org
============ Welcome to CrossDB Shell ============
<help>: Help Info <F1>: Shortcuts
<exit>: Exit shell <TAB>: Auto completion
XDB> exit