Data
Accessing data in Tequila is pretty straightforward and allows easy database switching by not allowing any native object in the system, using dao's or the functions provided to get data you will normally be working with Value Objects or Anonymous Value Objects
How can I access database data?
The preferred method to retrieve data from the database is using the DAO's, the data access objects provide a single access point to any table in the system, allowing you to have extra control on every method without caring about the sql.
Methods available to access data are:
DAO
getData
getRow
getValue
Using DAO
Perhaps the easiest way to understand the DAO is trough examples
Please notice this is sample code only! A real application will just return the data to be processed by the view
A DAO example:
Retrieving a customer
1 $myDao = new customers_DAO(); 2 $myCustomer = $myDao->get(1); 3 print_r($myCustomer); // Show object customer_VO 4 echo $myCustomer->I_CUSTOMERNAME; // This will print the customer name 5 $myCustomer->I_PHONE = "555-5555-5555"; // Assign a new telephone number
Retrieving a list of customer
1 $myDao = new customers_DAO(); 2 $myCustomerList = $myDao->find("I_CUSTOMERNAME = '%TEQUILA%'); 3 print_r($myCustomerList); // Show results, an array of customer_VO objects matching tequila 4 foreach($myCustomerList as $customer) 5 { 6 echo $myCustomer->I_CUSTOMERNAME; // This will print the customer name 7 }
VO's and DAO's provide a very efficient way to pass data around the application, including filling them from the post or rendering to the client. Check the DAO page for more information and the Controller documentation for specific methods
Accessing the database without DAO's
As practical as they can be DAO's sometimes are not the answer to your needs Tequila offers you many methods to access
getData
Tequila prefered method when not using DAO, a 1 liner to get any data in any format you need from the database.
function getData($mSql, $voType = 'ECMA', $indexBy = null)
Parameters
$mSql
The SQL statement to retrieve the data - SELECT only
$voType
A VO type that you created, example: customer_VO
Empty to return a generic object
1 return numeric array
2 return associative array
3 return both
$indexBy
A property of the object that is used to index the resulting array of VO's or generic objects. i.e.
1 $myResults = getData("SELECT Country, Icon_Drink from beverage", "ECMA", "Icon_Drink") 2 // To show a sample record 3 print_r($myResults ["Tequila"]); 4 // (Country="Mexico", Icon_Drink="Tequila")
The results are indexed with the value of icon_drink
Example:
1 $mSql = "SELECT * FROM CUSTOMERS INNER JOIN ADDRESS ON ......."; 2 $myCust = getData($mSql, 'Customer_VO'); 3 foreach ($myCust as $aCustomer) 4 { 5 echo $aCustomer->address; 6 }
Getting a single Value
When you need to access a single value from the database you can use the function:
1 $mSql = "SELECT NAME FROM COUNTRIES WHERE SHORTCODE = "FR"; 2 $countryname = getValue($mSql);
Getting a row
You can call the function:
1 $mSql = "SELECT * FROM COUNTRIES WHERE SHORTCODE = "FR"; 2 $country = getRow($mSql)
How data is passed around?
In Tequila the preferred way of passing data is through objects, they can be specific objects VO's you create, or generic anonymous objects, javascript style.
Which SQL style I have to use?
This libraries were thought with mySql in mind which is evident by the use of the LIMIT clause, the good news is that the libraries do modify this clause to make this work on most databases!
The DAO use standard SQL that is supported by all databases. If you are coding your own functions we recommend:
- Place the functions in the specific DAO (So all SQL is easy to find if needed)
- Try using the most general SQL for easy migration but
- The framework do nothing to your SQL, you can pass your Oracle style sql and call the framework methods without problems!
What about BLOB?
Blob is supported in all libraries, we have tested extensively with Oracle, MySQL and MSSQL using the AdoDB library. To handle BLOB fields in Oracle databases you need some extra instructions apart of your SQL all this is handled by DAO_BLOB
* Warning: We are planning to modify DAO_BLOB behavior on next release, currently it will load the data into the VO. We plan to stop this behavior and provide a function to access it, this is to stop users loading too much data when they don't need to use it.
If you need to use this method before we release the new version please contact us
Supported databases
MySQL
MSSQL
Oracle
sqlite
Adodb - Can use any db that ado can use
Msaccess
Postgres7
Tequila uses the data layer developer by the phpBB group and also used by phpNuke and who knows who else, we liked this library because it provide very close access to native php functions without so many wrappers.
Libraries status
We have extended and modify some of this libraries, and are currently replacing most functionality for a main function that returns objects straight from the data layer avoiding double parsing into array and then VO's the current status is:
MySQL fully tested and supported with Object support and mapping support
ADO DB. Tested and supported with object support and mapping support. We don't advice this database as there is no way to limit amount of rows retrieved efficiently (No LIMIT clause or cursor, so unless you modify your own SQL's there's a lot of data wasted)
Oracle. Tested and supported, No object support yet. No mapping support yetThis library was extensively modified to supported names with cases, still we recommend you code your applications using only uppercase names for database fields.
sqLite. Tested and supported. no object support.
* Notice that when object support is not available all functions will still work. Object support is just an improvement to the libraries
My Database is not in there, what can i do?
Creating your own is really easy, just copy and modify the mysql4 library, function getRows provide most needed functionality. Don't forget to send us to share with other people that can use and improve your library. You can also contact us if you need assistance.