Choosing a Style of Coding

Valentina has rich and powerful API which allows to use different coding styles. You can use one of the following styles:

• SQL Style
  • Pure SQL Style
  • SQL-API Style
• API Style
  • API Style (Dynamic Structure)
  • Class Style (Static Structure)

SQL Style means that you practically perform all database operations via SQL commands. This is a very common way of work with a RDBMS. Most IDEs support this coding style, because it is popular and available for usage with the majority of RDBMS available on the market today.

How does this style look? It uses very simple set of API classes and methods. The developer usually works only with:

• a Database class/object, and
• the result of an SQL command.

Sometimes a Database object has only one command such as shown in the following example Query() calls:

Listing 1. 

result = db.Query( "INSERT INTO Table T DEFAULT VALUES" )
result = db.Query( "SELECT * FROM T" )

If you have the only one API method which performs a SQL command, different possible variations of the returned result of that method will appear. The result can be as follows:

• some kind of recordset of the SELECT command;
• an integer value, such as the affected record number, for other SQL commands.
• something else …

Sometimes a Database object has two methods: the first one in order to execute SELECT commands, and the second one for executing additional SQL commands:

Listing 2.

int AffectedRows = db.SqlExecute( "INSERT INTO Table T DEFAULT VALUES" )
Cursor curs      = db.SqlSelect( "SELECT * FROM T" )

Thus the developer can cleanly obtain an explicit result for each method.

The various implementations offer different API methods to work with the result of a SELECT query.

For example, the Postgre SQL API gives you a result, and a set of methods, that allow you to extract information from the result, such as:

Listing 3.
 
int PQntuples( const PGresult* result );
int PQnfields( const PGresult* result );
int PQfname( const PGresult* result, int index );
int PQfsize( const PGresult* result, int index );

Both mySQL and many other databases offer a similar API. The Developer must explicitly allocate buffers of the required sizes, read values into those buffers, free the buffers, etc.

In contrast, Valentina provides the VCursor class, which works in tandem with the VField class. Valentina prepares this hierarchy of objects itself, so that you get a cleaner API that is easier to use. You do not need to explicitly manage the internal buffers used for the data.

The main advantage of this approach is that you interface with the DBMS using SQL. It`s VERY IMPORTANT if your program communicates with a remote server. So if you are going to develop a client-server application operating across the Internet you should choose the SQL Style of coding.

The main annoyance of this approach is that you must do a lot of string manipulation such as concatenation and format conversions in order to prepare the SQL commands.

To add a record you have to build a SQL INSERT command.
To update a record you have to build a SQL UPDATE command.
...

The Valentina VCursor class allows the developer not to only read in the field values of found records, but also to add, delete and update records as well.

Compare the following 2 examples. The first example shows the usage of pure SQL style; the second one shows the usage of VCursor methods.

Listing 4.
 
result = db.Query( "SELECT * FROM T WHERE id = 15" );
 
// Do something, now we want to update that record: 
string smd = "UPDATE T(f1) VALUES (" + toStr(value) + ")";
result2 = db.Query( cmd );
 
// Now we want to re-read that record with updated values, so
// we need to re-execute the query:
result = db.Query( "SELECT * FROM T WHERE id = 15" );

Listing 5.
 
VCursor curs = db.SqlSelect( "SELECT * FROM T WHERE id = 15" );
 
// Do something, now we want to update that record: 
curs.Field("f1").value = value;
curs.UpdateRecord();

It is easy to see that the developer benefits from having less code to write and that the overall performance is improved, because there is

• no need to build strings for a second and third query;
• no need to execute the second and third queries;
• no need for the DBMS to spend time parsing the second and third queries; and
• less information being sent to the DBMS (only a new value and command);

Valentina provides a set of API (classes and methods) that are rarely found in any other DBMS. Actually this API is so rich that you can completely manage a database without writing any SQL at all!

NOTE: Keep in mind that Valentina supports an Object-Relational model. This model offers several points of view:

• a Relational model (view);
• an Object model (view); and
• an Extended Navigational model (view);

You should also note that this API Style that allows management of a database without SQL does not however support pure relational operations such as “JOIN” and “GROUP BY”. All the other operations that can be accomplished with SQL can also be performed using API calls. Instead of joins you can use links and directly code navigation among the records.

Since this style uses only capabilities of the API interface calls, it is referred to as “Pure API Style”.

The major advantages of Pure API style in comparison with SQL Style are:

• superior speed,
• the best possible efficiency,
• a navigational model, and
• the support of an object-oriented coding style.

Superior Speed

The code written using the API style is very fast, because it allows the developer to make direct calls to the innermost, low-level implementation procedures of the database engine. When you do VField.Find() you make a direct call to engine. There is no overhead of parsing, optimizer, or of any other intermediate layers. This is why you get the best possible effectiveness.

Navigational Model

Today many RDBMS developers think that the relational model has already won the database market war, and none of the alternative models can offer any merit worth using.

However, there are many reasons why the Navigational Model allows the development of software which is far more efficient, by a factor of at least 4 to 5 times.

Furthermore, Valentina offers developers the usage of an Extended Navigational Model. The main improvement of this model is that developers can work with specific Sets of records, as described in more detail in the ValentinaKernel.pdf document.

The main disadvantage of the Pure API style compared with the SQL Style of coding arises when working with a remote server. While using the SQL Style the programs send small string-based commands to server and the remote DBMS can do a lot of work to provide the requested result. With the API Style the developer has to obtain and operate on potentially large intermediate results in the client computer. This can result in the network becoming a significant bottleneck.

On the other hand, given a fast high-bandwidth network connection the API Style still can be used quite effectively; especially since fast modern networks can often be faster than accessing data from a slower local hard disk drive.

The so-called “Class Style” of coding is a special case of API Style coding. This style can be used for any of the object-oriented languages supported by Valentina. This currently includes C++, Java, REALbasic, Objective-C, .NET (C#, Managed C++, VB7, etc.).

When writing code using the API Style, the main object that maintains a connection with the database is a Database object. The developer retains a named pointer to this Database object. The reference can be maintained in the global namespace of the Application, or in the property of a Window (so each window manages its own database). The developer may alternatively maintain an array of references to Database objects.

Regardless, the Database object provides the ROOT interface for accessing information stored in the database. The following listing shows an example of how this may look when accessing tables and fields:

Listing 6.
// This is pseudo-code with a syntax similar to Java or C# 
 
VDatabase gDatabase; // This is a global pointer to a Database object.
 
window1_func_N() 
    VTable tblPerson 
    VVarChar fldFirstName
 
    tblPerson = gDatabase.Table( "Person" )
    fldFirstName = tblPerson.VarCharField( "FirstName" )
 
    VSet res = fldFirstName.Find( "Peter" );
    ...
end 
 
window2_func_N()
    tblPerson as VTable 
    VVarChar fldFirstName
 
    tblPerson = gDatabase.Table( "Person" )
    fldFirstName = tblPerson.VarCharField( "FirstName" )
 
    VSet res = fldFirstName.FindLike( pGivenStr );
    ...
end 

A reference must first be obtained to any table or field that is to be accessed. The VDatabase.Table() and VTable.Field() methods are used for that purpose.

Object-oriented developers are likely to think: “Hey! I can make my own classes that initialize references upon object creation for subsequent use.” Let's try to write some example classes to accomplish this (draft of idea).

Listing 7.
// This is pseudo-code with a syntax similar to Java or C# 
 
class TblPerson : VTable
{
    properties:
         VVarChar        mFirstName;  
         VVarChar        mLastName;  
 
    methods:
         Constructor(){
              mFirstName = new VVarChar();
              mLastName = new VVarChar();
         }
};
 
class MyDatabase : VDatabase
{
    properties:
         TblPerson        mTblPerson;  
 
    methods:
         Constructor(){
              mTblPerson = new TblPerson();
         }
};

Please remember that MyDatabase is a subclass of VDatabase, so all the methods of VDatabase can be used with MyDatabase. The same is true for the TblPerson class, which is a subclass of VTable.

We have made a big step forward! Now we can write code such as the following one:

Listing 8.
// This is pseudo-code with a syntax similar to Java or C# 
 
// This is a global reference to a Database object.
MyDatabase gDatabase = new MyDatabase();  
 
window1_func_N() 
    VSet res = gDatabase.mTblPerson.mFirstName.Find( "Peter" );
    ...
end 
 
window2_func_N()
    VSet res = gDatabase.mTblPerson.mFirstName.FindLike( pGivenStr );
    ...
end 

Comparing Listing 8 with Listing 6 you can see that we now need to write much less redundant, mechanical code; which is really great!

Even better, the code now is more efficient, because it uses direct references to table Person and field objects, so there is no need to use dynamic name-based lookups via “VDatabase.Table( tblName )”. Instead the reference provide direct, immediate access; which is also great!

You should also note, that the style of using classes can only be employed if the developer knows exactly what tables and fields are required for the database structure at the development time. However, indeed the developer does already know this for most applications. The code in Listing 6 also uses the exact names of tables and fields; right?

So what is the cost of using this approach? The cost is that it is a bit more difficult to modify the structure of an existing application and its database structures on the user side, because the code to be modified gets distributed across more separate methods.

You may note that we previously considered

• the creation of the hierarchy objects via “new MyDatabase()”, and
• the usage of the resulting hierarchy.

But we still need to consider the question of HOW and WHEN those objects created in the environment of REALbasic, Java, C#, etc. are linked to the lower-level Valentina database objects.

First the developer describes the desired database structure in terms of classes. This organization includes one MyDatabase class and several MyTables classes.

How is a new database created using these classes? An ideal way would be like that:

main()
{
      MyDatabase db = new MyDatabase();
      db.Create(); 
} 

How is an existing database opened using those classes? An ideal way would look like:

main()
{
      MyDatabase db = new MyDatabase();
      db.Open( dbpath ); 
} 

Note, that the single line with “new MyDatabase()” does a lot of work. It calls the database class constructor which, in turn, calls constructors for the table classes, that then create fields. After this single line is executed, the entire hierarchy of objects is constructed: Database → Tables → Fields.

The intent of the “ideal” code in the above examples is that:

• “db.Create()” will somehow create the Database including all of its tables and fields; and
• “db.Open()” will somehow open an existing database, and establish appropriate links between objects and the underlying database structures.

Valentina implements that ideal! Valentina has a mechanism which allows that ideal code to work properly.