/**
* Create the table "phone_number", which lists all known telephone numbers.
*/
int PhoneBook::create_table_phone_number()
{
int rc;
char buffer[256];
Query q;
//-------------------------------------------------------------------
// Create the phone_number table
// uint64 id
// nvarchar name(MAX_CONTACT_NAME)
// uint64 ring_id
// varchar picture_name(FILENAME_MAX)
// blob picture
//-------------------------------------------------------------------
sprintf( buffer,
"create table phone_number ("
" contact_id uint64 not null,"
" number ansistr(%d) not null,"
" type uint64 not null,"
" speed_dial sint64,"
" constraint contact_ref foreign key (contact_id) references contact(id)"
")",
MAX_PHONE_NUMBER);
if (DB_SUCCESS(rc = q.exec_direct(db, buffer))) {
//---------------------------------------------------------------
// Create contact_id index on PHONE_NUMBER table
//---------------------------------------------------------------
rc = q.exec_direct(db,
"create index by_contact_id on phone_number(contact_id)" );
}
return print_error(rc, q);
}
/**
* Briefly list all contacts in the database.
*/
void PhoneBook::list_contacts_brief()
{
Query q;
const char *cmd;
cmd = "select id, name "
" from contact "
" order by name ";
if (DB_SUCCESS(print_error(q.exec_direct(db, cmd), q))) {
//---------------------------------------------------------------
// Bind local data fields to the data retrieved by the SQL call.
// The field number is determined by the order of the fields
// in the select statement.
//
// Field bindings can be created before or after the query is executed.
//---------------------------------------------------------------
IntegerField id(q, "id");
WStringField name(q, "name");
for (q.seek_first(); !q.is_eof(); q.seek_next()) {
char name_mbs[50];
wcstombs(name_mbs, WString(name).c_str(), sizeof name_mbs/sizeof name_mbs[0]);
cout << (long) id << '\t';
cout << name_mbs << endl;
}
}
return;
}
/**
* Create sequences. Sequences are used to generate unique identifiers.
*
* Demonstrates:
* - defining sequences
*/
int PhoneBook::create_sequences()
{
Query q;
//-------------------------------------------------------------------
// Create CONTACT_ID sequence
//-------------------------------------------------------------------
return print_error(q.exec_direct(db, "create sequence contact_id start with 1"), q);
}
/**
* Create the table "contact", which lists contacts in the phone book.
*
* Demonstrates:
* - defining table schema: fields and indexes
* - unique and non-unique indexes
* - add_datatype() functions return FieldDesc so they can be chained together
* - add_index() functions return IndexDesc so they can be chained together
*/
int PhoneBook::create_table_contact(bool with_picture)
{
int rc;
char buffer[256];
Query q;
//-------------------------------------------------------------------
// Create the CONTACT table
// uint64 id
// nvarchar name(MAX_CONTACT_NAME)
// uint64 ring_id
// varchar picture_name(FILENAME_MAX)
// blob picture
//-------------------------------------------------------------------
if (with_picture)
sprintf(buffer,
"create table contact ("
" id uint64 not null,"
" name utf16str(%d) not null,"
" ring_id uint64,"
" picture_name varchar(%d),"
" picture blob,"
" constraint by_id primary key (id)"
")",
MAX_CONTACT_NAME, MAX_FILE_NAME);
else
sprintf(buffer,
"create table contact ("
" id uint64 not null,"
" name utf16str(%d) not null,"
" ring_id uint64,"
" picture_name varchar(%d),"
" constraint by_id primary key (id)"
")",
MAX_CONTACT_NAME, MAX_FILE_NAME);
if (DB_SUCCESS(rc = q.exec_direct(db, buffer))) {
//-----------------------------------------------------------
// Create name index on CONTACT table
//-----------------------------------------------------------
rc = q.exec_direct(db, "create index by_name on contact(name)");
}
return print_error(rc, q);
}
/**
* Commit transaction
*/
void PhoneBook::tx_commit()
{
Query q;
// Equivalent to: db.tx_commit();
print_error(q.exec_direct(db, "commit"), q);
}
/**
* Start transaction
*/
void PhoneBook::tx_start()
{
Query q;
// Equivalent to: db.tx_begin();
print_error(q.exec_direct(db, "start transaction"), q);
}
/**
* List all contacts in the database with full phone numbers
*
* Demonstrates:
* - parent/child relationships
*/
void PhoneBook::list_contacts(int sort)
{
Query q;
const char *cmd;
uint64_t prev_id = 0;
const char* query_by_name =
"select A.id, A.name, A.ring_id, A.picture_name, B.number, B.type, B.speed_dial"
" from contact A, phone_number B"
" where A.id = B.contact_id"
" order by A.name, B.type";
const char* query_by_id =
"select A.id, A.name, A.ring_id, A.picture_name, B.number, B.type, B.speed_dial"
" from contact A, phone_number B"
" where A.id = B.contact_id"
" order by A.id, B.type";
const char* query_by_ring_id_name =
"select A.id, A.name, A.ring_id, A.picture_name, B.number, B.type, B.speed_dial"
" from contact A, phone_number B"
" where A.id = B.contact_id"
" order by A.ring_id, A.name, B.type";
/* Choose the query for the selected sort order. */
switch (sort) {
case 0:
cmd = query_by_id;
break;
case 1:
cmd = query_by_name;
break;
case 2:
cmd = query_by_ring_id_name;
break;
default:
return;
}
if (DB_SUCCESS(print_error(q.exec_direct(db, cmd), q))) {
//---------------------------------------------------------------
// Bind local data fields to the data retrieved by the SQL call.
// The field number is determined by the order of the fields
// in the select statement.
//
// Field bindings can be created before or after the query is executed.
//---------------------------------------------------------------
IntegerField id (q, "id");
WStringField name (q, "name");
IntegerField ring_id (q, "ring_id");
StringField picture_name(q, "picture_name");
StringField number (q, "number");
IntegerField type (q, "type");
IntegerField speed_dial (q, "speed_dial");
for (q.seek_first(); !q.is_eof(); q.seek_next()) {
//-----------------------------------------------------------
// Otherwise, if brief is NOT set we want to display the
// entire row from the selected data.
//
// For contacts with numerous phone numbers, only display
// the ID, NAME, RING_TONE, and PICTURE_NAME once.
//-----------------------------------------------------------
if ( (uint64_t)id != prev_id ) {
char name_mbs[50];
wcstombs(name_mbs, WString(name).c_str(), sizeof name_mbs/sizeof name_mbs[0]);
prev_id = id;
cout << "Id: " << (long) id << endl;
cout << "Name: " << name_mbs << endl;
if (!ring_id.is_null())
cout << "Ring tone id: " << (int) ring_id << endl;
if (!picture_name.is_null())
cout << "Picture name: " << String(picture_name).c_str() << endl;
}
cout << "Phone number: " << String(number).c_str() << " (";
switch ((long)type) {
case HOME: cout << "Home"; break;
case MOBILE: cout << "Mobile"; break;
case WORK: cout << "Work"; break;
case FAX: cout << "Fax"; break;
case PAGER: cout << "Pager"; break;
}
if (((long)speed_dial) >= 0)
cout << ", speed dial " << (long) speed_dial;
cout << ")" << endl;
}
}
return;
}