Eden SQLite

Install
Introduction
Contributing

====

Install

composer install eden/sqlite

====

Introduction

Eden's SQLite objects are nothing short of spectacular! We take ORM in to a whole new level featuring search, collections and models, designed for novices and enthuisiasts alike. Before we go into those advanced topics, we should first go over the basic methods available. First off we need to add connection information to our database model shown in Figure 1.

Figure 1. Database Connection Information

$database = eden('sqlite', '[SQL_FILE_PATH]');  //instantiate

Simply put, we instantiate the class giving the file path of the database on the server. There are no other requirements and you can start using methods immediately.

Note Eden's SQLite object will only connect when the first query is made. This strategy is called lazy loading.

====

Basic Querying

On a very low level can call raw queries as in Figure 2.

Figure 2. Raw Query

$database->query('SELECT * FROM user');  // returns results of raw queries

Eden's SQLite object uses PDO to communcate with your database. It's recommended you bind incoming variables contributed by the end user. Still on a low level, this can be acheived as in Figure 3.

Figure 3. Raw Binding

$query  = 'SELECT * FROM user WHERE user_name LIKE :user_name AND user_active = :user_active';
$bind   = array(':user_name' => '%'.$userName.'%', ':user_active' => 1);
 
$database->query($query, $bind); // returns results of raw queries

The above figure sets $query to a string with binded place holders :user_name and :user_active. $bind has the actual values these placeholders and should be replaced with duing execution of the query. We encourage this method because binding values prevents database injections.

Note: Binded variables must start with a colon(:).

====

Data Manipulation

If you prefer the ORM way to save data Figure 4 provides several method examples on how to acheive this.

Figure 4. Data Manilpulation

$settings = array(
	'user_name'     => 'Chris'
	'user_email'    => 'myemail@mail.com');
	 
$filter[] = array('user_id=%s', 1);     

// inserts row into 'user' table
$database->insertRow('user', $settings);
// updates rows in 'user' table where user_id is 
$database->updateRows('user', $settings, $filter);
// delete rows in 'user' table where user_id is 1
$database->deleteRows('user', $filter);

//updates data if there is a user_email with the value of anemail@gmail.com otherwise will insert
$database->setRow('user', 'user_email', 'anemail@gmail.com', $settings);

Inserting data is pretty trivial. We included 2 ways to insert data. Like getRow(), there's no need to worry about binded data because Eden wil do this for you. Figure 4 shows the 2 kind of inserts mentioned.

Figure 4. Two ways to insert

$settings = array('user_name' => 'Chris', 'user_email' => 'myemail@mail.com');
$database->insertRow('user', $settings);         // inserts row into 'user' table
 
$settings = array();
$settings[] = array('user_name' => 'Chris', 'user_email' => 'myemail@mail.com');
$settings[] = array('user_name' => 'Dan', 'user_email' => 'myemail2@mail.com');
$settings[] = array('user_name' => 'Clark', 'user_email' => 'myemail3@mail.com');
$database->insertRows('user', $settings);            // inserts multiple rows into 'user' table

So obviously insertRow() should be used if you just want to insert one row. Inserting two or more rows at the same time, you should use insertRows(). This method expects an array of arrays, or an array table.

Note: A common error found amongst programmers using Eden, is simply using insertRows() instead of insertRow().

Note: Using models and collections, you don't really need to worry about this method because it's covered in the save() method in a collection or model object. We'll go over models and collections later in this section.

Updating is about as easy as inserting. There's only one method you need to know.

Figure 5. Updating

$settings = array('user_name' => 'Chris', 'user_email' => 'myemail@mail.com');
$filter[] = array('user_id=%s', 1);
$database->updateRows('user', $settings, $filter); // inserts row into 'user' table

A common scenario is when you need to insert if a column value is not found and update if it is. We added an extra method called setRow() to simply to save you some lines of redundancy.

Figure 6. Insert or update

$settings = array('user_name' => 'Chris2', 'user_email' => 'myemail@mail.com');
$database->setRow('user', 'user_email', 'myemail@mail.com', $settings);

Figure 6 basically says, in user table, if myemail@mail.com exists in the user_email column, then update that row. If not then insert. Removing data is simple enough as well in Eden.

Figure 7. Remove

$filter[] = array('user_id=%s', 1);
$database->deleteRows('user', $filter); // delete rows in 'user' table where user_id is 1

====

Searching

A better way to build complex queries is with using Eden's search object. An overview example can be found in Figure 8.

Figure 8. SQLite Search

$database
	->search('user')
	->setColumns('*')
	->innerJoinOn('group', 'group_owner=user_id')
	->leftJoinUsing('friends', 'user_id')
	->filterByUserName('Chris')
	->addFilter("user_last LIKE '%s%%'", 'Brown')
	->sortByUserId('ASC')
	->addSort('user_last', 'DESC')
	->setRange(25)
	->setStart(75)
	->getRows();

In the figure above there's a few methods being powered with magic, but we'll just start going down the line. First off, to instantiate the search object you simply need to call search() passing the name of the table as the argument. Secondly we call setColumns(). This call is optional, but if used, can either accept an array of columns or an argument separated list of columns, ie. setColumns('user_id', 'user_name'). Next, innerJoinOn() is the new way we accept joins. There are eight methods dedicated to different kinds of joins.

Kinds of Join methods

innerJoinOn()
innerJoinUsing()
leftJoinOn()
leftJoinUsing()
rightJoinOn()
rightJoinUsing()
outerJoinOn()
outerJoinUsing()

No matter what methods you choose from above there are two arguments you need to add. The first argument is the name of the table you would like to join and the second one is the how they relate to each other.

The first magic powered method is called filterByUserName(). There is no actual method called filterByUserName() in the SQLite class. Instead when this function is called it will parse out the name of the method and recognize that UserName is the name of a column and convert that into addFilter('user_name=%s', 'Chris') as in Figure 8.

addFilter() generally accepts two arguments. The first argument is the filter clause. If you notice in our filter example in Figure 8 we use %s to delimit a binded value. You can have as many binded values per filter as you like. The following arguments need to include the binded values in order of when they occur in the filter clause.

The second magic powered mehod is called sortByUserId('ASC').There is no actual method called sortByUserId('ASC') in the SQLite class. Instead when this function is called it will parse out the name of the method and recognize that UserId is the name of a column and convert that into addSort('user_id', 'ASC') as in Figure 8.

There are three kinds of pagination methods also available

Pagination Methods

setRange(75)
setStart(25)
setPage(1)

It's important if you are going to use setPage(1) to call setRange(75) first because the underlying function simply calculates the start index based on the range. Two other methods that are not covered by Figure 8 are the ability to group and to set the table to something else.

Figure 9. Other Useful methods

->setTable('user')
->setGroup('user_active')

Getting Results

When your happy with your query you can retrieve the results in 3 ways as described in Figure 0.

Figure 10. Retrieving Results

->getTotal()
->getRows()
->getCollection()

Figure 10 shows three ways to get the results, the first way getTotal(), will retrieve the total number and does not consider pagination elements. getRows() will simply return a raw array. getCollection() will return you an object with the results for further manipulation.

====

Collections

Collections do exactly the same thing as models except it manipulates multiple models instead. Collections can be iterable and access as arrays as well. Collections only hold model objects so if you wanted to use your own extended model, you would need to call setModel('Your_Model').

Figure 11. SQLite Collections

//set user name for all rows
$collection->setUserName('Chris');

// set or get any abstract key for all rows
$collection->setAnyThing()

//collections are iterable
foreach($collection as $model) {        
	echo $model->getUserName().' ';
	echo $model['user_email'];
}
 
//access as array
echo $collection[0]['user_name'];
//set as array
$collection[0]['user_email'] = 'my@email.com'; 
 
$collection->save('user', $database);    //save to 'user' table in database
										//only relavent columns will be saved
										//for all rows

Some other utility methods not covered by th above examples are date formating and copying from one column to another. Figure 12, show how we would go about doing these things.

Figure 12. Utility methods

//formats a date column
$collection->formatTime('post_

Sqlite

Install / Use

README

Eden SQLite

Install

Introduction

Basic Querying

Data Manipulation

Searching

Getting Results

Collections