Database errors

In this category I will comment about database design, SQL errors and Data validation errors which are very common ones.


It's also important to notice that not all errors are bugs or failures but sometimes incorrect use of data and/or resources.


What makes use of data 'correct'?

* Minimal exchange of information
o Delimited rows
o Delimited columns
o Don't retrieve BLOB/TEXT until required
* Standard SQL queries
* Non specific db-brand queries
* Proper indexed and accessed information
* Proper keys definition
* Reuse of objects and connection objects


In Tequila by using DAO classes and generated pages these errors are somehow minimized as the page provide simple JS validation and the SQL is formed by the DAO class, unfortunately DAO cannot determine when to eliminate BLOB's/TEXT fields from the query, nor workaround poor DB libraries.


As with any code introduction new places for bugs/issues are created, let's examine some common ones.
DAO errors

There are some common misuses of DAO classes:

* Using DAO instead of DAO_unique for tables with no autonumeric field (auto increment)
* Using $key instead of $keys for DAO_unique
* Not setting $nullable array for tables that doesn't allow nulls and use ADODB library


Abuse of data
Some errors are very hard to notice as they don't create errors but just affect performance

* Not filtering results
* Loading Text and BLOB fields when they are not required, specially on multiple records
* Loading a full set of data to display just a section (pagination, see LIMIT clause for more info)


* To avoid these problems in DAO classes you should create a special method in the DAO child that return just the fields you need.

SQL Errors
A common source of failures are incorrect SQL statements; at development time all SQL queries must be tested out of the system first (and then in); some SQL statements might work correctly with a simple test query, but will fail when using real data, some common mistakes are:

* Filter criteria is incorrectly joined
* One side of a JOIN can include NULL values, You need to use a left/right join (this often becomes evident, when the system uses real data)
* Null values in the data

Database design

Database design is an speciality in itself, please consult with your manager or project leader. When creating tables don't forget to use the prefixes T_ for tables and I_ for fields.


Out of Tequila I still recommend this or a similar notation, specially if you consider a database migration can take place in the future.
Issues with Naming, Advanced functions & DB versions

Please be aware that some common functions might not be supported by specific databases, or will fail even if supported,


Here's a list of some of issues we have discovered in different databases:


* MySql before 4.1
o Lack of support for subselects (Use joins instead)
* MySql
o Regex support is faulty. We have found mysql crashed several times due to use of REGEX to find tags (others worked well)
* MSSQL
o SQL PHP library is really deficient, therefore Tequila uses the ADODB COM object (You can monitor status of new library here)
* MSSQL using ADODB
o Support is still incredibly bad, with lack of paging support and other critical stuff.
o Until the release of the new Microsoft library this issues are unsolvable and Tequila/PHP uses workarounds for them.
* ORACLE
o Long names. Oracle doesn't support very long field names
o Oracle use UPPER CASE


CASE sensitivity

A common cause for bugs on system migration is case sensitivity, be aware of the following:



* MySQL doesn't respect Case in table names in windows systems
* MySQL respect case in *nix systems
* PHP is case sensitive (always)
* Oracle forces UPPERCASE*


*Tequila has a modified Oracle library that allow mixed case to be used in queries (to allow db migration) however this comes with an overhead.
Implications breakdown
Coming down to code this means that when you change the OS or even the web server your application might completely crash. Possible reasons are:

* The Script to create the DB was exported from the window system
* The target database uses different case


Example:
We assume system was developed on win and SQL is dumped from this system

// Table is exported from Win system
$mSql = "SELECT UserName from Users WHERE idUser = 3";
...
$username = $rst['UserName'];
// Will succeed in windows
// Will fail in *nix as Users table doesn't exist; Users != users
// Will fail in Oracle as field it's called $rst['USERNAME']
Recommended practices

* In your code use always UPPER CASE for table names and fields
* Define your database using UPPER CASE for tables
* Verify your export database script 'case' is correct before recreating.

Database mobility
Coding for proper database mobility is a challenge in itself. In Tequila/BB we have standardized a set of practices to favor mobility:

* Prefer use DAO over SQL to have a single control point
* Have single entry points for DB querys (functions execSQL and insertSQL)
* Have a single function for data retrieval (getData)
* Use only standard SQL
* Avoid / Minimize use of stored procedures
* Avoid / Minimize use of temp tables
* Prefix all tables with T_
* Prefix all field names with I_


* These practices are critical, not following them might result in your code being rejected, some critical cases like the use of 'name' field will result in database reserved word conflicts

Find php framework and more useful information about RAD workflow open source.