SQL Yoga Change Log

1.1.1 build 1
* dbconn_getVendor was not processing db key/conn name properly which would break things if not using default database and connection.
* A number of fixes that involved some operations not using the proper connection object.
* Added to the list of supported join types.
* Added dbconn_escapeString.
* When calling dbobject_delete the dbconn objects were not being deleted properly.
* Added support for the "options" parameter for SQLite (see dbconn_setVendor).

1.1.0 build 3
* commit transaction code now ensures that counter doesn't go lower than 0.
* The cursor assigned to a Query object was not being reset to 0 after it was closed.
* You can now pass an empty value to sqlquery_update and it won't complain. All records in the table will be updated.
* You can now pass a string of conditions to sqlquery_update and the string will be used as the "conditions". Previously if you didn't pass an array it was assumed the value you passed was the primary key value.
* Migrating to a schema version would update the schema version even if the last version that could be installed was lower than the currently installed version. This was incorrect behavior because an old version of the software could update the schema version number even if the schema was newer.
* All SQL Server queries use upper case in order to support databases that are case-sensitive.

1.1.0 build 2
* _StringToJoins did not set wholematches to true which would return false positives for WordOffset.

1.1.0 build 1
* SQL Yoga no longer complains if you are converting a cursor to a hierarchal structure and a table that was joined doesn't have it's primary key in the select clause as long as no fields from that table are in the select clause.
* Updated version code for PostgreSQL in order to support new format returned by version 9.
* SQL Server now uses proper type for binary columns when generating create SQL.
* Boolean logic now wraps 1s and 0s in db quotes.
* Added dbconn_getBooleanValue function which returns the proper boolean value representation based on the connection the database type.
* dbconn_lastInsertID now throws an error if there is no subclass for the database driver.
* Added support for dbschema_dropIndex.
* dbschema_migrateToVersion now catches any errors generated in InstallSchemaVersion code and returns it. This allows the transaction to be rolled back.
* sqlquery_move* commands were not working properly.
* If _CursorRowToArray fetched the fields (not passed in) then the list of fields was not being converted from comma delimited to return delimited.
* When syncing the schema with the database the target database was not being passed in. This could wipe out the internal schema of the wrong database object when using multiple databases.

1.0.2 build 8
* sqlquery_retrieveAsRecords was not embedding the connection and database information in the returned records.
* dbschema_migrateToVersion was not passing in active connection parameter in the call to check the schema version.

1.0.2 build 7
* Fixed a table lookup issue when using multiple many-to-many relationships in a join.
* SQL Yoga only escapes single quotes (') for PostGreSQL now. C escape sequences reuquire an E prefix as of 8.1.
* SQL Yoga will now turn INSERT_IDENTITY ON/OFF if you are creating a record that specifies a value for the Identify column.
* sqlyoga_setDefaultDatabase now accepts "default" as a paramater even if a database object named "default" doesn't exist.
* _SplitRecordsIntoTablesIndex was not always passing in the proper database object when getting table fields.

1.0.2 build 6
* SQL Query objects were not parsing "related table joins" properly if no Relationship objects had been defined.
* Added "schema migrations" property.
* MySQL now supported with schema migrations.
* _UpdateRecordInTable was doing an OR comparison when looking for "@where" key rather than an AND comparison.
* sqlquery_update now works with empty pConditions param if no primary key has been defined for the target table.
* Table aliases were not being resolved properly in the "where clause" and when using sqlquery_delete.
* When using sqlrecord_getRelated with an alias table an error would be thrown if a table object had not been defined for the table the alias pointed to.
* When retrieving related records for a Record object the check for column types that require quoting was not using the resolved table name for aliases.
* When retrieving related records using an alias table the alias conditions were being added to the WHERE clause twice.
* sqlquery_create now supports table aliases. The alias conditions will automatically be inserted into the table along with any other column values passed in.
* sqlquery_update now supports table aliases. The alias conditions will automatically be used in the WHERE clause when performing the update.
* Added TINYBLOB, MEDIUMBLOB, LONGBLOB, LongVarBinary and LongVarChar to list of binary/text data types.
* Setting the 'adaptor' property of a connection now sets the type to the same value if a) the type is empty or b) the adaptor is not odbc.
* When working with scopes it was possible to have duplicate columns in the ORDER BY clause.
* Empty values are now inserted as NULL for text, binary and decimal meta types when using ODBC adaptor. It does not like empty values.
* When updating a record in a table boolean fields were not being recognized and thus boolean string to 1/0 was not being performed in the WHERE condition.
* Added nchar, nvarchar, ntext and citext as "text" types.
* SQL Yoga now assigns the connection "type" when connecting if the "type" is empty.
* A query select clause was not honoring the database associated with the query when checking table aliases.

1.0.2 build 5
* sqlrecord_create was not updating primary key values properly when passing in an array of Record objects.
* The database schema was always being imported when connecting using a database object other than "default".

1.0.2 build 4
* IS EMPTY is now translated to = '' in search conditions.
* "SHOW TABLES" now used for MySQL to work around #8620 (http://quality.runrev.com/qacenter/show_bug.cgi?id=8620).

1.0.2 build 3
* SQL Yoga now makes 1 attempt to reconnect to a database when RevDB returns an error meaning the connection id is no longer valid. Previously 2 attempts were made.

1.0.2 build 2
* _CursorRowToArray now honors multiple columns with the same name. An integer is added to the end of any duplicate column names. For example, given a query with the fields: 'id, id, id, title, title' the resulting array would have the keys: 'id, id2, id3, title, title2'.
* sqlquery_currentRowToArray was not working with Valentina.

1.0.2 build 1
* English syntax parser no longer replaces "is" at the beginning of table/field names with "=" in certain conditions.
* sqlrecord_update, sqlrecord_delete, and sqlrecord_retrieve now throw an error if an array isn't passed in (catches typos).
* tableobjects_tableExists() now throws an error if the table does not exist.
* Fixed issue in _ConvertQueryJoinToFlatHierarchy regarding array index that wasn't having one of the keys deleted properly in the repeat loop.

1.0.1 build 1
* Added sqlyoga_getDatabases().
* A Database object's "connections" property now returns a return delimited list of the names of Connection objects.
* dbobject_createObject now throws an error if you try to create a Database object with no name or a name that already exists.
* tableobj_createObject now throws an error if pName is empty.
* Fixed a bug in dbconn_exists().
* dbconn_createObject now throws an error if name is already taken or if name is empty.
* When calling tableobjects_reload the default Database object is set to the Database tableobjects_reload is acting on. This way any table, relationship and scope objects are created in the proper Database object if it is no the default one.
* tableobjects_reload now targets the behavior button directly when messages are dispatched.
* sqlquery_retrieveAsArray now behaves as documented in that it aggregates results in the array you pass in.
* When setting the "select clause" of a SQL Query object you can using TABLE.* to include all columns in a table. All columns of the table will be added to the query and respect the "column names length" property. When generating hierarchal results of Records column names length = "long" is required and this syntax provides a nice shortcut for times when you need to add some SQL functions to the query.
* Fixed an issue with converting multiple many-to-many relationships in a cursor to hierarchal arrays of Records.
* IS NULL was being translated to = NULL when setting the "condition" property of a SQL Query or Scope object.
* sqlrecord_find now accepts any non-array variable when performing the find.
* Updating a record now throws an error if no primary key is defined for the table and no where clause is supplied.
* Added sqlquery_create and sqlquery_update. These commands allow you to create/update specific fields in database tables without creating Record objects.
* Added 'sql conditions' property to Scopes so you can see the SQL that will be generated.
* Importing a database schema for a Database object that was not the default would replace the default Database object.
* Added tableobjects_tableExists().
* Added "column names length" (short, full, default). Allows you to explicitly select just column name (short), the table and column name (full) or the default behavior. Supersedes "full column names".
* When throwing the "sqlyoga_executesql_err" and "sqlyoga_connection_err" errors SQL Yoga now includes the context information so you can track down who generated the bad SQL call.
* Trying to add a non-existent scope to a SQL Query object now throws an error.
* dbconn_createObject now throws an error if you pass in the name of a Database object that doesn't exist.

1.0.0 build 15
* tableobjects_getRelationship now throws error if no relationship exists.
* tblrelation_createObject now throws an error if you try to create a relationships with no name or a name that already exists.
* tblscope_createObject now throws an error if you pass in a name of an existing scope (for that table).
* A Table object's "relationships" property now returns a return delimited list of the names of relationships associated with the table.
* A Table object's "table aliases" property now returns a return delimited list of tables that are aliases to the table.
* tableobj_createObject throws an error if you pass in the name of a table that already exists.
* All SQL Yoga code that queries the database now tries to reconnect if SQL Yoga detects a lost connection.

1.0.0 build 14
* If an error occurred while connecting to the database it was not reported properly. SQL Yoga would still try to query the database for it's version number which would throw an error thus losing the original error.

1.0.0 build 13
* Added dbconn_exists() to check if connection exists.
* sqlquerytmpl_get was not using the proper connection when fetching properties.

1.0.0 build 12
* Fixed issues surrounding the creation and use of multiple Database objects.
* Improved error reporting in relation to non-existent database and connection objects.

1.0.0 build 11
* You can now set the default Database object name to use in API calls. See sqlyoga_setDefaultDatabase and sqlyoga_getDefaultDatabase().
* Adjusted when default settings for connections are set. Fixed issues with Valentina.
* Fixed incorrect SQL syntax associated with "begins with" and "ends with".

1.0.0 build 10
* sqlquery_get "cursor id" now returns 0 if the property is empty.
* Reworked throwing of errors from within SQL Yoga library. When SQL Yoga throws an error the object that called the SQL Yoga call is properly reported.
* Fixed an issue when trying to reconnect when the 'MySQL server has gone away' error is returned while executing a query.

1.0.0 build 9
* IMPORTANT! As of this build you need to set Connection object properties such as file, host, username and password each time you launch your application. These properties were being stored as part of the Database object previously but one does not want a username/password being stored that way.
* Addition of Valentina support. Requires V4REV 4.3b14 or later.
* Added "flush each log" property for Valentina databases.
* sqlrecord_find no longer requires that the table you pass in be defined as a table object.
* host, database name, port, username, password, log file, log field, last executed sql, and version are now stored for the current session only. They are no longer stored when calling dbobject_save.
* sqlrecord_getRelated now throws an error if an invalid Record object is passed in.
* All vendor properties for Connections are stored for the session. They are no longer saved with dbobject_save.

1.0.0 build 8
* Connections have a new property called "quote identifiers". If true then all table and field names are quoted so that reserved words won't cause conflicts. Default is true.
* SQL Query Templates now generate a SQL Query when getting properties. This gits rid of duplicate code and fixes some bugs.
* sqlquery_delete was not working properly and had been left out of the unit tests. Both issues addressed.

1.0.0 build 7
* Creating, updating and deleting routines for SQL Records now convert true/false to 1/0 in generated queries.
* SQL Records now convert boolean columns to true/false when retrieving.
* SQL Queries now have 'group by', 'having', 'limit' and 'offset' properties.
* sqlrecord_delete now uses all included fields in the SQL Record object if a) the table has no primary key defined or b) not all primary key fields are included.
* dbobject.createTables.[DBKEY] (message triggered when calling tableobjects_reload) has been replaced with dbobject.createTables. The [DBKEY] suffix was unnecessary. dbobject.createTables.[DBKEY] will be sent if dbobject.createTables is "unhandled" so that existing code doesn't break.

1.0.0 build 6
* When passing an integer as the 2nd param to sqlrecord_find the primary key field or the table is now used if it is one field. Otherwise 'id' is used. Previously 'id' was always used.
* Smarter quoting of field values. '01' will report that it is an integer but can be in a field with a text type. It must be quoted in this case. The type of the field as defined in the database is now checked when quoting/escaping values.

1.0.0 build 4
* SQL Server is now supported through RevDB ODBC driver.

1.0.0 build 3
* An error is now thrown when calling sqlrecord_get/set with a property that is not among the keys of pRecordA and has not been defined as a custom property in your 'table objects behavior' script.

1.0.0 build 2
* Updated callbacks so that they are prefixed with 'tableobj.' rather than 'dbtable.'.

1.0.0 build 1
* Fixed a bug in the english syntax parser used when setting 'conditions' property.
* Added 'log file' property for database connections. Set to file path to log queries executed by SQL Yoga.
* Changed database object property 'tables behavior' property to 'table objects behavior'.
* sqlrecord_retrieve has been replaced with sqlrecord_  .
* Changed table objects API calls from dbtable[s]_ to tableobj_ and tableobjects_. Wanted to clarify that API wasn't creating actual tables in the database.
* Changed dbrelation_ API calls to tblrelation_ since relationships are between table objects.
* Changed dbscope_ API calls to tblscope_ since scopes are associated with table objects.
* Added support for trial license keys that expire after X amount of days.
* Changed dbquerytmpl_ API calls to sqlquerytmpl_.
* Renamed dbutil_ API calls to sqlyoga_.
* Rather than using [objecttype]_create/_delete, API will now use [objecttype]_createObject/_deleteObject. One might infer that a couple of objects would actually create/delete data in the database using _create/_delete. All object creation is being updated to _createObject/_deleteObject in order to standardize.
* sqlquery_create has been renamed sqlquery_createObject. sqlquery_create will continue to work for the next few revisions.
* sqlrecord_initWithTable has been renamed to sqlrecord_createObject. sqlrecord_initWithTable will continue to work for the next few revisions.
* sqlrecord_createObject now returns an array containing all fields in the table it represents. Default values for columns are included in the returned array.
* sqlrecord_create and sqlrecord_update no longer fetch data from database after CREATE/INSERT.
* Connection errors are now thrown. This allows the developer to handle the error in a central errorDialog message and disable their application or prompt to reconnect as necessary.
* SQL execution errors are now thrown.
* The arrays that are used to represent SQL Record objects are now similar to the arrays that represent SQL Query objects in that you should use the supplied get/set handlers to modify them. Always use sqlrecord_set/get when modifying SQL Record object properties. Even though the object is represented as an array do not fall into the tempation of 'put "some value" into theRecordA["database column"]'. This can break things.

0.9.3 build 5
* SQL Records have two new properties: @connection and @database. Get/set using sqlrecord_get and sqlrecord_set.
* Added 'last executed sql' and 'log field' properties to database connection objects. dbconn_get("last executed sql") returns the last SQL statement executed against the database connection object. dbconn_set "log field", pFieldRef will set the field that all executed SQL statements should be logged to. This is for debugging purposes.

0.9.3 build 4
* sqlrecord_retrieve now returns an error if no database record was found using the provided columns to search on.
* sqlrecord_create and sqlrecord_update now trigger validation callbacks. If you want to validate data entry for a particular table then in your "tables behavior" button script define a command that looks like this:

dbtable.validate.[TABLE_NAME] @pRecordA, pConnectionA

Example:
command dbtable.validate.lessons @pRecordA, pConnectionA
## verify the pRecordA keys (columns in the table) have
## valid values...
end dbtable.validate.lessons

* When calling sqlrecord_delete the SQL Record object (array) is now cleared of all data. Only the @table key remains since the record no longer exists in the database.
* When setting "related table joins" for a SQL Query or SQL Query Template object if the value you set it to contains " ON " then SQL Yoga will not automatically write join for you.

0.9.3 build 3
* Data is now fetched using 3rd parameter of revDatabaseColumnNumbered. This allows for binary data to make it through.
* Binary columns have their data inserted using special RevDB prefix so that binary data is inserted properly.
* SQL Query "select clause" was not being set properly.
* "odbc" was not accepted as a proper string for connection types.
* sqlrecord_createFromTable changed to sqlrecord_initWithTable. Too much potential for confusion with sqlrecord_create and sqlrecord_createFromTable. In addition, initializing is a better description of what is actually happening.
* sqlquery_create now throws an error if you pass in a name that is neither a table name nor a SQL Query Template object name. This can occur if you try to create a SQL Query before connecting the the database so that SQL Yoga can store the database schema.

0.9.3 build 2
* Added version and link to SQL Yoga web page. Seen if you open libSQLYoga.
* PostgreSQL now passes all unit tests.

0.9.3 build 1

* SQL Yoga is now a single stack file. No more substacks.
* Changed sqlrecord_get to a function. Now it only returns custom properties defined for a table or related records for a single Record object.
* Added sqlrecord_getRelated. This command retrieves related records for a Record object and adss them to a key in the object. Works with one record or mutiple records.