Differences

This shows you the differences between two versions of the page.

--- dictionary:metacasing [2015/11/13 00:51] – [Overview] mnewnham
+++ dictionary:metacasing [2015/12/19 17:21] (current) – removed mnewnham
@@ Line 1: / Line 1: @@
-====== MetaCasing ======
-===== Description =====
-As of ADOdb version 6.0, **MetaCasing** provides a complete, separate method of controlling key/value casing alternative to the effects of [[reference:ADODB_ASSOC_CASE]] when used in metaFunctions. It provides a superior functionality:
-  * Consistent casing of keys in ''meta'' functions.
-  * Can be changed dynamically inside programs
-  * Is consistently supported by all database drivers.
-  * Improves performance when schema information is read multiple time, or used in the course of an update. For instance, when [[reference:autoexecute|autoExecute()]] is used on the same table more than once, the second (and subsequent) record insertion/update occurs in 1/2 the time as under ADOdb version 5.
-  * Separates the 'business process' of ADOdb (management of schemas, update of records using helper routines such as [[reference:autoexecute|autoExecute()]],[[dictionary:createtablesql|createTableSql()]] from the 'presentation layer' [[dictionary:metatables|metaTables()]],[[dictionary:metacolumns|metaColumns()]], etc.
-<WRAP info>
-MetaCasing variables have no effect on the keys of retrieved recordsets. These are still controlled by [[reference:ADODB_ASSOC_CASE]] or [[reference:setfetchmode|setFetchMode]]
-</WRAP>
-===== Overview =====
-Historically, ADOdb has provided a confusing set of functions, constants and variables that interact with each other to attempt to provide consistency of presentation layer, whilst allowing database updates with helper routines. The following non-exhaustive list shows some of these items
-  * ADODB_ASSOC_CASE
-  * ADODB_FORCE_QUOTES
-  * The $nameQuote option on autoExecute()
-The end result has been that schema information must be sometimes re-read multiple times in order to obtain the necessary consistent presentation layer values, whilst configuring the tables and columns differently for table updates.
-MetaCasing attempts to eliminate most of the confusing conflicts, as well as providing performance enhancements by storing and re-using database schema information instead of reading the database multiple times.
-==== The MetaCache ====
-Each time an element of the schema - such as tables, columns and indexes - is read, the retrieved data is stored in the metaCache, so that any subsequent requests for that information, even through different methods, will be retrieved through the cache. For example, a request for column name information via the [[dictionary:metacolumnnames|metaColumnNames()]] method will cache information about the schema that can be used by the [[reference:autoexecute|autoExecute()]] method.
-The data in the metaCache is stored in 'native mode', that is, it follows casing conventions provided by the database management system. For that reason, the casing of keys in the cache should not be relied on to be of any specific type. The metaCasing system provides helper routines to provide API consistency for data returned. These helper routines post-process the retrieved information and allow the schema information to be presented without re-reading the database.
-===== Basic Usage =====
-Both metaCasing and MetaCaching is enabled by default in ADOdb versions 6.0 and higher. The configurations of either can be modified at any time after creation of the ADOdb class. Configuration is not predicated on having established a connection to the database **[what do you mean ?]**. In addition, configuration can be changed multiple times within a single procedure.
-  * To disable metaCasing, call the method ''$db->setMetacasing($db::METACASE_LEGACY)''.
-  * To disable metaCaching, call the method ''$db->disableMetaCache()''
-==== Configuring metaCasing ===
-MetaCasing is controlled by a single method [[dictionary:metacasing#setmetacasing|setMetaCasing()]], taking a single argument which controls how the data is presented to the end user. **It has no effect on how the schema information is retrieved from the database, the casing of data keys of retrieved recordsets when [[reference:setfetchmode|setFetchMode()]] is in associative mode, or the values of data when read and written into the database**. The available values to control presentation of data is shown below
-^ Constant         ^ Value  ^ Description                                                                                                                                                                                                                    ^
-| METACASE_LOWER   | 0      | Certain returned keys and values are transposed to lower case                                                                                                                                                                                          |
-| METACASE_UPPER   | 1      | Certain returned keys and values are transposed to upper case                                                                                                                                                                                          |
-| METACASE_NATIVE  | 2      | Keys are returned as provided from the database. There is no established standard for the key case returned. In databases that support case specific table names, setting this value may also change how other functions work. |
-| METACASE_LEGACY  | -1     | The keys are returned in the same manner as previous versions of ADOdb. This is the default option. This option is provided for transitioning of old code, which may be dependent on key inconsistencies                                                                                                                              |
-==== Usage ====
-<code php>
-$db->setMetaCasing($db::METACASE_UPPER);
-print_r($db->metaColumns('ACT');
-/*
-* Prints
-array(
-=>ACTNO,
-=>ACTDESC,
-=ACTKWD
-    )
-*/
-$db->setMetaCasing($db::METACASE_LOWER);
-print_r($db->metaColumns('ACT');
-/*
-* Prints
-array(
-=>actno,
-=>actdesc,
-=actkwd
-    )
-*/
-</code>
-Note in the above example, the table name requested was provided in upper case, but the resulting column names were in lower case. One of the benefits of the metaCase system is that it is no longer necessary to know the correct casing of table or column names, they will be automatically converted to the correct values.
-===== Available Public Methods =====
------------------------------
-==== setMetaCasing ====
-<WRAP right box 350px>
-== Syntax ==
-  void setMetacasing(
-     int $casing
-  )
-</WRAP>
-The method ''setMetaCasing()'' sets the default casing method for the next (and subsequent, unless changed) call to Meta function. The valid options are described above.
------------------------
-==== getMetaCasing ====
-<WRAP right box 350px>
-== Syntax ==
-  int getMetacasing()
-</WRAP>
-The method ''getMetaCasing()'' returns the currently set casing method for the next (and subsequent, unless changed) call to a Meta function.
-------------------------
-==== clearMetaCache ====
-<WRAP right box 350px>
-== Syntax ==
-  void clearMetaCache(
-      optional string $tableName=false
-  )
-</WRAP>
-The method ''clearMetaCache()'' flushes the metaCache for the specified table, or for all tables if no table name is supplied. Subsequent calls to metaFunctions will begin rebuilding the cache
-------------------------
-==== disableMetaCache ====
-<WRAP right box 350px>
-== Syntax ==
-  void disableMetaCache()
-</WRAP>
-The method ''disableMetaCache()'' disables metaCaching. Any cached metadata will be flushed. Subsequent calls to metaFunctions will always query the database directly. There may **[will ?]** be overhead in data retrieval.
-----------------------------
-==== enableMetaCache ====
-<WRAP right box 350px>
-== Syntax ==
-  void enableMetaCache()
-</WRAP>
-The method ''enableMetaCache()'' re-enables a previously disabled metaCache. Subsequent calls to metaFunctions will rebuild the metaCache.
-----------------------------
-==== buildMetaCache ====
-<WRAP right box 350px>
-== Syntax ==
-  void buildMetaCache()
-</WRAP>
-The method ''buildMetaCache()'' builds a metaCache array for all of the available tables, columns, indexes, keys and procedures for the currently selected database. For programs that make substantial use of metaFunctions, or helper functions like ''autoExecute()'' across multiple tables, there may be substantial performance benefits in using this function to build the metaCache before accessing specific table information
------------------------------------
-===== How metaCasing affects specific functions =====
-Metacasing rules are applied to all [[dictionary:data_dictionary|Meta functions]]. Noting in particular the change to [[dictionary:metacolumns|metaColumns()]] described below  as well as the following functions that depend on Meta functions:
-==== autoExecute() ====
-All of the above casing rules apply as above, plus another important change: If the metaCasing is set to METACASE_NATIVE, then this supersedes all of the [[reference:adodb_quote_fieldnames|$ADODB_QUOTE_FIELDNAMES]] options. Any setting currently in place when ''autoExecute()'' is called will be ignored. In addition, all table and columns will be quoted with the value defined in the ''$nameQuote'' class variable (not necessarily backticks). If compatibility is needed with old code, metacasing can be turned off prior to calling the autoExecute() function.
-==== getInsertSql and getUpdateSql ====
-Metacasing will now produce a more formal SQL statement in preparation for insertion of a record.
-  * All tables and column names will be quoted, irrespective of their containing special characters
-  * Table and column names will be correctly cased, based on how they are held in the database
-=== Example ===
-<code php>
-/*
-* Connection assumed
-*/
-/*
-* Use legacy metacasing
-*/
-$db->setMetaCasing($db::METACASE_LEGACY);
-$table = 'ACT';
-$fields = array('act-no'=>10,
-                'DESCRIPTION'=>'A record to insert',
-                'Balance'=>1024);
-$sql = getInsertSql($table,$fields);
-print_r($sql);
-/*
-* Keys are always upper cased
-* Prints INSERT INTO ACT (`ACT-NO`,DESCRIPTION,BALANCE) VALUES (10,'A record to insert',1024);
-*/
-/*
-* Set metacasing to native
-*/
-$db->setMetaCasing($db::METACASE_NATIVE);
-$sql = getInsertSql($table,$fields);
-print_r($sql);
-/*
-* Keys are used based on how they are held in the database
-* Prints INSERT INTO `act` (`act-no`,`description`,balance`) VALUES (10,'A record to insert',1024);
-*/
-</code>
-===== Schema Management Functions =====
-Any meta function to manages tables, columns or indexes now has the object name in question managed by metaCasing. The same rules that are applied to getInsertSql() and getUpdateSql() are applied to these items.