Tables & Schemas

Table Names and Schemas Implementation in CacheRDD

Though the term "SCHEMA" in SQL world bears a much wider scope but I will focus on its usability to the xbase developer in relation to flat file system which all dialects of xbase language have supported so far.

At the simpest it can be preceived as logical partitions in a database to arrange tables similar like folders on the disk to contain .dbfs, .cdxs, etc. Though on the disk we can have nested folders, but in a database, SCHEMAs can be only one level deep, i.e., root->database->schema. And this is more than enough to arrange your tables into a database.

CachéRDD supports schemas alongwith table names to manage if such a scenario exists. When a connection is opened either with CachéAddConnection() or CachéAddConnectionEx() functions, its default schema is set to "SQLUSER". This can be subsequently set to another name by CachéSetSchema( cSchema, hConnection ). CachéRDD provides yet another method to place a table in another schema as:
 
cSchemaName||cTableName = "VOUCH||MYTABLE.DBF"
 
If scheme name is supplied like this as an argumnt to DbCreate() or DbUseArea() functions, the table will be created/opened in/from supplied SCHEMA. Otherwise default schema name set for the connection in scope will be used. Schema names and table names are converted to uppercase for normalization purposes.

Only the filename part of the xbase table without extension is sent to the database. For example :
 
Local cTable := 'C:\MyData\MyTable.dbf'
Local cSchema := 'Vouch'
Local aStruct := {}
 
aadd( aStruct, { 'Name', 'C', 30, 0 } )
aadd( aStruct, { 'Age', 'N', 5, 1 } )
 
DbCreate( cSchema + '||' + cTable, aStruct, 'CACHERDD' )
USE ( cSchema + '||' + cTable ) SHARED NEW
...
...

Schema name is normalized depending on the return value of CachéSetSchemaAsIs(). If the function returns true, schema name is passed as is received, otherwise it is uppercased. By default CachéSetSchemaAsIs() returns false denoting that convert the schema name to uppercase. This has an important bearing on opening the existing SQL created tables.

With reference to above example, the table will be created in schema VOUCH and table name as MYTABLE. Only filename part will be pulled from the filename-with-path without the extension, uppercased and along with schema will be sent to the database. CachéRDD does not support auto creation of table name based on the path.
 
If you do not use same names of tables in different folders then you need not to worry about anything. Alternatively you can parse paths to the tables, convert then to a unique schema name and rearrange it meeting above specs and supply to those functions. You are to look deep into those parts of your application which open/create/delete tables and indexes.

Why a schema is imporatnt in a real database environment is evident from the fact that a lot many settings can be employed to a schema to control the database behavior cummulatively applied to all those tables associated with that schema. From an xbase developers point of view it can be viewed as logical placement of tables in different folders.

Caché database sets "SQLUSER" as default schema name if one is not supplied with the table name. This has an important bearing on the SQL implementation. Every SQL command uses "SQLUSER" as default schema if a table name is supplied without a valid schema part. Thus "SELECT * FROM MyTable" will return an error if MyTable is created in "VOUCH" schema in above example. In that case the only way to retrieve data from MyTable will be "SELECT * FROM VOUCH.MyTable".

If you do not use schemas in your application all tables will be created in "SQLUSER" schema and thus will be available to any external application with only the table name. However CachéRDD does not restrict to this paradigm only.

CachéRDD has been designed to keep extendibility aspect in mind and supports record insertions, updations, deletions outside of the RDD apecific applications. This means you can design your applications in combination with RDD specific (x)Harbour compilations, XbScript, any ODBC specific web pages, or even Caché specific CSPs. The tables are pure tables like dbfs. The only exception is deleted records which are deleted permanently all the time. This was necessary to keep CachéRDD current with other applications accessing the same data. I believe, and you will also agree to, that this deficiency in RDD is worth the price as other much-wider options are there at your doorstep to enhance your productivity. You can be sure of that RLock()s and FLocks()s are visible to the external processes as well.