From JFramework Wiki

Jump to: navigation, search

jFramework concerns performance and simplicity, So it does not use DBAL's like PDO or ADO, Though some of its drivers might use them internally. It instead uses an interface designed internally which is both very simple and fast. jFramework DBAL uses MySQLi for MySQL interactions which is about 10 times faster than PDO, but uses PDO for SQLite 3.0 interactions (the interface wraps PDO SQLite 3), Yet the SQLite jFramework interface which is based on PDO is way simpler than its PDO brother.

It's perfectly easy to add any driver to jFramework, It also supports multiple database connections via registry.

Setup

There is a default database setup for jFramework (which is also optional). You can set its parameters in your application.php file, And you can determine where your application is to set different parameters. Four basic parameters are Host (defaults to localhost), User, Name (of database) and password.

Note: If you set your database user to null or an empty string, jFramework will go on without any database connections and will ignore its own database functionality. In this mode, if you ever perform a database interaction you'll receive an application error. <pre> reg("app/db/default/name","jf"); reg("app/db/default/user","root"); #comment this for no-DB jFramework reg("app/db/default/pass",""); reg("app/db/default/host","localhost"); </pre> You can also set your database default adapter which defaults to 'mysqli' (in _japp/lib/dbal.php).

Note: If you're using SQLite as the database, The database name would be the file name for the database, And you should set a folder with Full Everybody access as your SQLite folder. You can also use a read-only SQLite database if its file exists, In this mode only queries that make a change in the database would cause an error. <pre>

reg("app/db/default/adapter","pdo_sqlite");

reg("app/db/default/sqlite/folder",new jpRoot ( ) . "/install/db/");

reg("app/db/default/sqlite/readonly",false); # only for SQLite when no write access is available

</pre>

Additional Databases

In order to connect to additional databases and use them, You can set other parameters. Keep in mind that jFramework would yet use the default database for its functionality, But you can use your own database connections in your application. To do so, You need to add this code to your application.php : <pre> $this->LoadSystemModule (new jpCustom2Module ( "lib.dbal.adapter.pdo_sqlite",".") ); //replace pdo_sqlite with your driver of choice $this->DB2=new DBAL_PDO_SQLite( $user,$pass,$name,$host ); //also replace DBAL_PDO_SQLite with your driver classname </pre>

Usage

This is the abstract interface for jFramework DBAL:

<pre> interface jFramework_DBAL_Abstract {

   function LastID ();
   function Escape ();
   function ResultCount ();
   function AffectedRows ();
   function AutoQuery ($QueryString);
   function Query ($QueryString);
   function NextResult ();
   function AllResult ();
   function Execute ();
   function Prepare ($QueryString);

} //Inside _japp/lib/dbal/interface.php </pre>

We're going to describe these functions one by one:

List of Methods

LastID()

This function returns the last insertion id of an insert query. It has no arguments.

Escape(...)

This function receives variable arguments, and escapes them all. It uses the drivers escaping facilities. Since there's a much better approach (prepared statements), Use of this function is not recommended unless necessary.

ResultCount()

Returns the number of rows returned on a select query. Can be used to check if any results are available.

AffectedRows()

Returns the number of affected rows by an update query.

Query($Query)

This performs a single query against the database, and returns no results.

NextResult()

Returns a row of data after a query in a one-dimensional associative array, if available. Otherwise returns false so can be used in a foreach statement.

AllResult()

Returns all rows of data in 2D associative array, even if there are only one rows of data. 1st dimension indexes are numerical, And returns false if no rows.

Prepare($Query)

Recieves a query and returns a prepared statement object (jFramework DBAL Prepared Statement) based on it. See prepared statement section for more info.

Execute($Query,...)

This is somehow identical to j::SQL function (the j::SQL is a litter more capable as it receives arrays as well). This one gets a $Query as the first argument, and gets a few more (variable count) arguments based on the query, creates a prepared statement from the query, binds the arguments to the statement and returns a 2D array of results on select queries, the last insertion id on insert queries and false on other queries.

You should keep in mind that you should strictly avoid concatenating any variables to the query to prevent SQL Injection attacks. In fact, Your query should be constant in most cases. In situations where you have to generate a dynamic query, make sure to escape all variables that are concatenated and white-list them before doing so. In any circumstances, You should never concatenate any data to your query. See the following examples: <pre> $ord=$_GET['sort']; if ($ord=="1")

  $ord="ASC";

else

  $ord="DESC"; //White list,  $ord can't be anything but "ASC" or "DESC"

$title=$_GET['title'];

$Result=$this->DB->Execute("SELECT * FROM myTable WHERE Title=? ORDER BY {$ord}", $title); </pre> In the above example, title could be anything and this SQL query is totally safe. There is no chance of SQL Injection in the example above since $title is sent to the database separated from the query itself as data, and $ord is safely whitelisted and concatenated to the query. Now consider the following example: <pre> $ID=j::SQL("INSERT INTO Users (Username,Password) VALUES (?,?)","admin","admin"); echo "User admin created with ID {$ID}".BR; </pre>

Prepared Statements

Both Execute() and Prepare() functions above used the jFramework DBAL Prepared Statement object, The former implicitly and the latter explicitly by returning it. Now we're going to discuss methods available to this object.

To make things simpler than ever, This object has the same interface as the base DBAL object, With one more functions available to it! So we're going to only discuss those functions that vary in this object from the DBAL object:

Bind(...)

This function receives variable arguments and binds them to the statement. You have to make sure that the number of arguments are exactly the same as the number of ?'s in the original prepared statement or you'll encounter a database error.

AutoQuery($Query,...)

This is identical to the DBAL's Execute() function as it receives a query, prepares it, binds arguments, runs it and returns the result.

Execute(...)

This one is slightly different from DBAL's Execute(), as it only runs the prepared statement against the database. If you provide this function with any arguments, It will try to bind them first and then do its usual task.

Drivers

To create additional drivers for jFramework DBAL, simply copy one of the drivers (available in _japp/lib/dbal/adapter), rename it to what you like and rewrite its functions. Afterwards change the _japp/lib/dbalphp and in the if-else statement, add your own database adapter. Now to use it, you simply have to change your application.php file's default database adapter to what you just created.

Don't forget to share your adapter with the jFramework if you added any!

Additional Modules

Since jFramework's RBAC uses a rather complex set of database functionality, These functions are encapsulated inside the DBAL and loaded with jFramework, So you can use them in your own application as well. There are currently Nested Set modules available at _japp/lib/dbal/nestedset which provide the user with a very robust and fast way of working with hierarchical data (as in RBAC), In two modules: 'BaseNestedSet' and 'FullNestedSet'. To see more on how to use them, Visit Nested Set.

Roadmap

We plan to add Large Data handling and Transactions to jFramework DBAL.

DBAL