IOPC2

Before we start with this topic, we need to get familiar with database objects - persistent objects without an OID, descendants of DatabaseObject. Database objects usually represent query results from read-only tables or from tables not generated by the IOPC 2 library. They can be also mapped to database tables which are not part of an IOPC 2 generated schema. As query results, they do not need to have a database identity, but if they are mapped into some table, they do. This identity is an instance of the KeyValues class. They contain a list of key-value pairs which represents any single-attribute or composite (multi-attribute) key.

To illustrate the concept of database objects, we will re-use the previously created table blacklist:

CREATE TABLE blacklist(id NUMBER PRIMARY KEY, name VARCHAR2(20));
INSERT INTO blacklist VALUES(1, 'Richard Doe');
INSERT INTO blacklist VALUES(2, 'Ola Nordmann');

Now we create a database object class BlackListEntry whose instances will represent rows from that table:

class BlacklistEntry : public iopc::DatabaseObject {
public:
	EInt externalId;
	EString name;

	static void iopcInit(iopc::Type& t) {
		t["db.table"].setStringValue("blacklist");
		t.getAttribute("externalId")["db.column"].setStringValue("id");
		t.getAttribute("externalId")["db.primaryKey"].setBoolValue(true);
		t.getAttribute("name")["db.type.length"].setIntValue(20);
		// not necessary, attribute name and column name are the same:
		t.getAttribute("name")["db.column"].setStringValue("name");
	}
};

Because the name of the class and the names of its attributes differ from the schema, we must specify class metadata which describe mapping between this class and the blacklist table. The metadata can be specified in a configuration file as well (see the section called “Class metadata”). Metadata used in this example are listed in the following table:

The simplest way to load an object is to pass its OID or key values to the DbPtr constructor. In the following example, we will load a blacklist entry with id 2:

KeyValues key;
key.add("externalId", 2);
DbPtr<BlacklistEntry> entry(conn, key);
cout << entry->externalId << ": " << entry->name << endl;

Output of the example is:

2: Ola Nordmann

Because the class has identity defined, we can modify the loaded object or even create a new one:

DbPtr<BlacklistEntry> newEntry;
newEntry->externalId = 3;
newEntry->name = "Mary Major";
newEntry.bePersistent(conn);

Now, to the objects wihtout identity. We create a class that will represent USER_TABLES view from the Oracle Data Dictionary, and we will use it to list all tables in the current schema.

class UserTable : public iopc::DatabaseObject {
public:
	EString tableName;
	EString tablespaceName;

	static void iopcInit(iopc::Type& t) {
		t["db.transient"].setBoolValue(true);
		t["db.table"].setStringValue("USER_TABLES");
		t.getAttribute("tableName")["db.column"].setStringValue("TABLE_NAME");
		t.getAttribute("tableName")["db.type.length"].setIntValue(30);
		t.getAttribute("tablespaceName")["db.column"].setStringValue("TABLESPACE_NAME");
		t.getAttribute("tablespaceName")["db.type.length"].setIntValue(30);
	}
};

The db.transient metadata tells the library that the class is transient and that it does not have an identity. If we did not specify this metadata, an exception would be thrown during the library initialisation routine saying that the class needs at least one primary key to be defined.

Basic query that just returns all rows from a table associated with the specified class is very simple. We do not need to construct any Query objects:

Result<UserTable> userTables(conn);
userTables.open();
for (Result<UserTable>::iterator it = userTables.begin(); 
  it != userTables.end(); it++) {
	cout << it->tableName << "   " << it->tablespaceName << endl;
}
userTables.close();

The example lists all tables from the current schema along with tablespaces containing the tables. Query is executed by calling the Result<T>::open() method, a database cursor is created. The results can be then iterated as shown in the example. To free the allocated database cursor, Result<T>::close() must be called when the result is not needed any more.

If we want to filter or to sort the query results, we must create a SimpleQuery instance and specify our needs. The following example lists the table names in ascending order and restricts the table results to tables from the USERS tablespace.

SimpleQuery q(
  "$::UserTable::tablespaceName$ = 'USERS'", "$::UserTable::tableName$ ASC");
Result<UserTable> userTables(conn, q);
userTables.open();
for (Result<UserTable>::iterator it = userTables.begin(); 
  it != userTables.end(); it++) {
	cout << it->tableName << " " << it->tablespaceName << endl;
}
userTables.close();

First parameter of the SimpleQuery constructor filters the query result, it is inserted into the WHERE part of the resulting SQL query. Second parameter represents the ORDER BY part. For the query format description please refer to the section called “Querying”.

Finally, database object classes even need not to be bound to one table, they can represent results of any query. In the following example, we add a columnCount attribute to the UserTable class to represent number of columns of each table. If we join the USER_TABLES and USER_TAB_COLS tables, we may calculate the column count by grouping the results by TABLE_NAME (and TABLESPACE_NAME) and by adding a COUNT(*) column. The modified UserTable class would look like this:

class UserTable : public iopc::DatabaseObject {
public:
	EString tableName;
	EString tablespaceName;
	EInt columnCount;

	static void iopcInit(iopc::Type& t) {
		t["db.transient"].setBoolValue(true);
		t.getAttribute("tableName")["db.column"].setStringValue("TABLE_NAME");
		t.getAttribute("tableName")["db.type.length"].setIntValue(30);
		t.getAttribute("tablespaceName")["db.column"].setStringValue("TABLESPACE_NAME");
		t.getAttribute("tablespaceName")["db.type.length"].setIntValue(30);
		t.getAttribute("columnCount")["db.column.sql"].setStringValue("COUNT(*)");
		// Not necessary:
		t.getAttribute("columnCount")["db.column"].setStringValue("COLUMN_COUNT");
	}
};

The db.table metadata was removed. The db.column.sql metadata specifies the column expression used to select value for this attribute. It is rendered as COUNT(*) AS column_name.

Instead of SimpleQuery we now use a FreeQuery instance because it allows us to specify everything after the FROM keyword in the SQL SELECT statement:

FreeQuery q("USER_TABLES JOIN USER_TAB_COLS USING (TABLE_NAME) GROUP BY TABLESPACE_NAME, TABLE_NAME");
Result<UserTable> userTables(conn, q);
userTables.open();
for (Result<UserTable>::iterator it = userTables.begin(); 
  it != userTables.end(); it++) {
	cout << it->tableName << " " << it->tablespaceName << " " 
    << it->columnCount << endl;
}
userTables.close();

The resulting SELECT statement will be:

SELECT 
  COUNT(*) AS COLUMN_COUNT, 
  TABLE_NAME, 
  TABLESPACE_NAME 
FROM 
  USER_TABLES JOIN USER_TAB_COLS USING (TABLE_NAME) 
GROUP BY 
  TABLESPACE_NAME, 
  TABLE_NAME

OID objects are queried in a similar way. To illustrate it, we list all persons in our database who are older than 30:

SimpleQuery q("$::Person::age$ >= 30");
Result<Person> r(conn, q);
r.open();
for (Result<Person>::iterator it = r.begin(); it != r.end(); it++) {
	cout << "name: " << it->name << " age: " << it->age << endl;
}
r.close();

Output of the example is:

name: Mary Major age: 60
name: Ola Nordmann age: 45

As you see, the query selects from the polymorphic views, so even the Person descendants are returned. To select only the Person instances we must change the query to FreeQuery and specify the corresponding simple view name from which to select:

FreeQuery q2("$::Person[db.view.sv]$ WHERE $::Person[db.view.sv]::age$ >= 30");

// SQL executed:
SELECT OID FROM Person_SV WHERE Person_SV.Person_age >= 30

Only OID column is selected for OID objects because selected objects may be present in the cache. Full object is then either obtained from the cache or loaded from database when accessed^[36].