search

EFTEC / PdoOne

It is a simple library for PHP that simplify the use of the PDO extension.
https://www.eftec.cl
MIT License
108 stars 19 forks source link
mysql-php pdo-php pdo-wrapper php php-library query-builder sql-server

readme

Database Access Object wrapper for PHP and PDO in a single class

PdoOne. It's a simple wrapper for PHP's PDO library compatible with SQL Server (2008 R2 or higher), MySQL (5.7 or higher) and Oracle (12.1 or higher).

This library tries to work as fast as possible. Most of the operations are simple string/array managements and work in the bare metal of the PDO library, but it also allows to create an ORM using the extension eftec/PdoOneORM.

Packagist Total Downloads [Maintenance]() [composer]() [php]() [php]() [CocoaPods]()

Turn this

$stmt = $pdo->prepare("SELECT * FROM myTable WHERE name = ?");
$stmt->bindParam(1,$_POST['name'],PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->get_result();
$products=[];
while($row = $result->fetch_assoc()) {
  $product[]=$row; 
}
$stmt->close();

into this

$products=$pdoOne
    ->select("*")
    ->from("myTable")
    ->where("name = ?",[$_POST['name']]) 
    ->toList();

or using the ORM (using eftec/PdoOneORM library)

ProductRepo // this class was generated with echo $pdoOne()->generateCodeClass(['Product']); or using the cli.
    ::where("name = ?",[$_POST['name']])
    ::toList();

Table of contents

Examples

ExampleTicketPHP Example cupcakes Example Search Example Different Method
example php bladeone example php bladeone cupcakes example php bladeone search

More examples:

Example Mysql PHP and PDO using PDOOne

Installation

This library requires PHP 7.1 and higher, and it requires the extension PDO and the extension PDO-MYSQL (Mysql), PDO-SQLSRV (sql server) or PDO-OCI (Oracle)

Install (using composer)

Edit composer.json the next requirement, then update composer.

  {
      "require": {
        "eftec/PdoOne": "^4.0.1"
      }
  }

or install it via cli using

composer require eftec/PdoOne

Install (manually)

Just download the folder lib from the library and put in your folder project. Then you must include all the files included on it.

How to create a Connection?

Create an instance of the class PdoOne as follows. Then, you can open the connection using the method connect() or open()

use eftec\PdoOne;
// mysql
$dao=new PdoOne("mysql","127.0.0.1","root","abc.123","sakila","");
$conn->logLevel=3; // it is for debug purpose and it works to find problems.
$dao->connect();

// sql server 10.0.0.1\instance or (local)\instance or machinename\instance or machine (default instance)
$dao=new PdoOne("sqlsrv","(local)\sqlexpress","sa","abc.123","sakila","");
$conn->logLevel=3; // it is for debug purpose and it works to find problems.
$dao->connect();

// test (mockup)
$dao=new PdoOne("test","anyy","any","any","any","");
$dao->connect();

// oci (oracle) ez-connect. Remember that you must have installed Oracle Instant client and added to the path.

$cs='(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = instancia1)))';
$dao=new PdoOne("oci",$cs,"sa","abc.123"); // oracle uses the user as the schema
$conn->logLevel=3; // it is for debug purpose and it works to find problems.
$dao->connect();

// oci (oracle) tsnnames (the environment variables TNS_ADMIN and PATH must be correctly configured), also tnsnames.ora must exists.
$cs='instancia1';
$dao=new PdoOne("oci",$cs,"sa","abc.123"); // oracle uses the user as the schema
$conn->logLevel=3; // it is for debug purpose and it works to find problems.
$dao->connect();

where

$dao=new PdoOne("mysql","127.0.0.1","root","abc.123","sakila","");

OCI

Oracle is tricky to install. In Windows, from the Oracle home's bin folder, you must copy all the dll to the PHP folder and Apache Folder.

How to run a SQL command?

1. Running a raw query

With the method RunRawQuery(), we could execute a command directly to PDO with or without parameters. And it could return a PdoStatement or an array. It is useful when we want speed.

RunRawQuery($rawSql,$param,$returnArray,$fetchMode,$fetchArgument)

string $rawSql The query to execute array|null $param [type1,value1,type2,value2] or [name1=>value,name2=value2] bool $returnArray if true (default) then it returns an array. If false then it returns a PDOStatement int $fetchMode Indicates the mode to fetch. Example: PDO::FETCH_ASSOC null $fetchArgument The argument of the fetchMode.

$sql='select * from table where id=1';
$pdoStatement=$pdoOne->runRawQuery($sql,[],false);  // [] are the parameters

But we could change it to returns an array

$sql='select * from table where id=1';
$values=$pdoOne->runRawQuery($sql);  // [] are the parameters

We could also pass parameters.

$values=$con->runRawQuery('select * from table where id=?',[20]); // with parameter
$values=$con->runRawQuery('select * from table where id=:name',['name'=>20]); // with named parameter
$values=$con->runRawQuery('select * from table',[]); // without parameter.

Note, this library uses prepared statements, so it is free of SQL injection (if you use parameters)

$name="O'hara";
$values=$con->runRawQuery("select * from table where name=:name",['name'=> $name]); // it works.✅
$values=$con->runRawQuery("select * from table where name=?",[$name]); // it works ok.✅
$values=$con->runRawQuery("select * from table where name='$name'"); // it will crash.❌

2. Running a native PDO statement

With the method runQuery() we could execute a prepared statement in PDO. It is useful when we want to pass arguments to it. runQuery() requires a PDO PreparedStatement.

This method is not recommended unless you are already working with PDO statements, and you don't want to adapt all your code.

$sql="insert into `product`(name) values(?)";
$stmt=$pdoOne->prepare($sql);
$productName="Cocacola";
$stmt->bind_param("s",$productName); // s stand for a string. Also i =integer, d = double and b=blob
$rows=$pdoOne->runQuery($stmt);
$allRows=$rows->fetch_all(PDO::FETCH_ASSOC);

3. Running using the query builder

You can use the query builder to build your command. You could check the chapter about Query Builder (DQL) for more information.

// query builder
$pdoOne->set(['name'=>'cocacola'])
    ->from('product')
    ->insert();

4. Running using an ORM

The library eftec\PdoOneORM allows to create an [orm](#orm) of your tables. If you are generated an ORM, then you can use the next code

ProductRepo::toList(['category'=>'drink']);

Where ProductRepo is a service class generated by using the ORM.

5. Run a query with a different mode

By default, PdoOne executes the queries in the mode PDO::FETCH_ASSOC You can change by running the queries as:

$pdo->setFechMode(PDO::FETCH_CLASS,'stdClass')->runRawQuery($query);
// or you can run as
$pdo->runRawQuery($query,null,true,false,null,PDO::FETCH_CLASS,'stdClass')

How to work with Date values?

PdoOne allows 5 types of dates.

How to run a transaction?

There are 3 methods to runs a transaction:

Method Description
startTransaction() It starts a transaction. Depending on the type database, it could be stacked or not.
commit() Commit (and closes) a transaction
rollback() Rollback (and closes) a transaction

Example:

try {
    $sql="insert into `product`(name) values(?)";
    $pdoOne->startTransaction();
    $result=$pdoOne->runRawQuery($sql,['Fanta'=>$productName],false);
    $pdoOne->commit(); // transaction ok
} catch (Exception $e) {
    $pdoOne->rollback(false); // error, transaction cancelled, the false means that it doesn't throw an exception if we want rollback.
}

Custom Queries

tableExist($tableName)

Returns true if the table exists (current database/schema)

statValue($tableName,$columnName)

Returns the statistics (as an array) of a column of a table.

$stats=$pdoOne->statValue('actor','actor_id');
min max avg sum count
1 205 103.0000 21115 205

columnTable($tablename)

Returns all columns of a table

$result=$pdoOne->columnTable('actor');
colname coltype colsize colpres colscale iskey isidentity
actor_id smallint 5 0 1 1
first_name varchar 45 0 0
last_name varchar 45 0 0
last_update timestamp 0 0

foreignKeyTable($tableName)

Returns all foreign keys of a table (source table)

createTable($tableName,$definition,$primaryKey=null,$extra='',$extraOutside='')

Creates a table using a definition and primary key.

Note: You could generate a code to create a table using an existing table by executing cli (output classcode)
php pdoone.php -database mysql -server 127.0.0.1 -user root -pwd abc.123 -db sakila -input film -output classcode

Example: (mysql)

$pdo->createTable('film',                                                                                                
    [                                                                                                                    
        "film_id" => "smallint unsigned not null auto_increment",                                                        
        "title" => "varchar(255) not null",                                                                              
        "description" => "text",                                                                                         
        "release_year" => "year",                                                                                        
        "language_id" => "tinyint unsigned not null",                                                                    
        "original_language_id" => "tinyint unsigned",                                                                    
        "rental_duration" => "tinyint unsigned not null default '3'",                                                    
        "rental_rate" => "decimal(4,2) not null default '4.99'",                                                         
        "length" => "smallint unsigned",                                                                                 
        "replacement_cost" => "decimal(5,2) not null default '19.99'",                                                   
        "rating" => "enum('G','PG','PG-13','R','NC-17') default 'G'",                                                    
        "special_features" => "set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes')",                     
        "last_update" => "timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP"                      
    ],[                                                                                                                  
        "film_id" => "PRIMARY KEY",                                                                                      
        "title" => "KEY",                                                                                                
        "language_id" => "FOREIGN KEY REFERENCES`language`(`language_id`) ON UPDATE CASCADE",                            
        "original_language_id" => "FOREIGN KEY REFERENCES`language`(`language_id`) ON UPDATE CASCADE"                    
    ]);                                                                                                                  
$pdo->createTable('film',                                                                                                
    [                                                                                                                    
        "film_id" => "smallint unsigned not null auto_increment",                                                        
        "title" => "varchar(255) not null",                                                                              
        "description" => "text",                                                                                         
        "release_year" => "year",                                                                                        
        "language_id" => "tinyint unsigned not null",                                                                    
        "original_language_id" => "tinyint unsigned",                                                                    
        "rental_duration" => "tinyint unsigned not null default '3'",                                                    
        "rental_rate" => "decimal(4,2) not null default '4.99'",                                                         
        "length" => "smallint unsigned",                                                                                 
        "replacement_cost" => "decimal(5,2) not null default '19.99'",                                                   
        "rating" => "enum('G','PG','PG-13','R','NC-17') default 'G'",                                                    
        "special_features" => "set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes')",                     
        "last_update" => "timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP"                      
    ],'film_id');

Example (sqlsrv)

$pdo->createTable('film',
   [
       "film_id" => "int NOT NULL IDENTITY(1,1)",
       "title" => "varchar(255) NOT NULL",
       "description" => "text(2147483647) DEFAULT (NULL)",
       "release_year" => "varchar(4)",
       "language_id" => "tinyint NOT NULL",
       "original_language_id" => "tinyint DEFAULT (NULL)",
       "rental_duration" => "tinyint NOT NULL DEFAULT ((3))",
       "rental_rate" => "decimal(4,2) NOT NULL DEFAULT ((4.99))",
       "length" => "smallint DEFAULT (NULL)",
       "replacement_cost" => "decimal(5,2) NOT NULL DEFAULT ((19.99))",
       "rating" => "varchar(10) DEFAULT ('G')",
       "special_features" => "varchar(255) DEFAULT (NULL)",
       "last_update" => "datetime NOT NULL DEFAULT (getdate())"
   ],[
       "language_id" => "FOREIGN KEY REFERENCES language(language_id)",
       "original_language_id" => "FOREIGN KEY REFERENCES language(language_id)",
       "film_id" => "PRIMARY KEY"
   ]);

tableSorted($maxLoop = 5, $returnProblems = false, $debugTrace = false)

It returns a list of tables ordered by dependency (from no dependent to more dependent)

Note: This operation is not foolproof because the tables could have circular references.

$dao = new PdoOne('sqlsrv', "(local)\sqlexpress", "sa", "abc.123", "sakila");
$dao->open();
echo "<pre>";
var_dump($dao->tableSorted(3, false, true)); // it returns the tables sortered
var_dump($dao->tableSorted(3, true, true)); // it returns all the tables that can't be sortered
echo "</pre>";

validateDefTable($pdoInstance,$tablename,$defTable,$defTableKey)

It validates a table if the table matches the definition asigned by values.

$def=[
       "film_id" => "int NOT NULL IDENTITY(1,1)",
       "title" => "varchar(255) NOT NULL",
       "description" => "text(2147483647) DEFAULT (NULL)",
       "release_year" => "varchar(4)",
       "language_id" => "tinyint NOT NULL",
       "original_language_id" => "tinyint DEFAULT (NULL)",
       "rental_duration" => "tinyint NOT NULL DEFAULT ((3))",
       "rental_rate" => "decimal(4,2) NOT NULL DEFAULT ((4.99))",
       "length" => "smallint DEFAULT (NULL)",
       "replacement_cost" => "decimal(5,2) NOT NULL DEFAULT ((19.99))",
       "rating" => "varchar(10) DEFAULT ('G')",
       "special_features" => "varchar(255) DEFAULT (NULL)",
       "last_update" => "datetime NOT NULL DEFAULT (getdate())"
   ];
$keys=[
       "language_id" => "FOREIGN KEY REFERENCES language(language_id)",
       "original_language_id" => "FOREIGN KEY REFERENCES language(language_id)",
       "film_id" => "PRIMARY KEY"
   ]; 

var_dump(PdoOne::validateDefTable(self::getPdoOne(),self::TABLE,$def,$keys));

foreignKeyTable

It returns all the foreign keys of a table.


$result=$pdoOne->foreignKeyTable('actor');
collocal tablerem colrem
customer_id customer customer_id
rental_id rental rental_id
staff_id staff staff_id

Query Builder (DQL)

You could also build a procedural query.

Example:

$results = $pdoOne->select("*")->from("producttype")
    ->where('name=?', [ 'Cocacola'])
    ->where('idproducttype=?', [ 1])
    ->toList();

select($columns)

Indicates the columns to return. The argument is a SQL command, so it allows any operation that the database support, including functions, constants, operators, alias and such.

$results = $pdoOne->select("col1,col2"); //...

Generates the query: select col1,col2 ....

$results = $pdoOne->select("select * from table"); //->...

Generates the query: *select from table** ....

count($sql,$arg='*')

Generates a query that returns a count of values. It is a macro of the method select()

$result = $pdoOne->count('from table where condition=1'); // select count(*) from table where c..
$result = $pdoOne->count()->from('table')->where('condition=?',[1]); // select count(*) from table where c..
$result = $pdoOne->count('from table','col1'); // select count(col1) from table
$result = $pdoOne->count()->from('table'); // select count(*) from table

min($sql,$arg='*')

Generates a query that returns the minimum value of a column. If $arg is empty then it uses $sql for the name of the column It is a macro of the method select()

$result = $pdoOne->min('from table where condition=1','col'); // select min(col) from table where c..
$result = $pdoOne->min('from table','col1'); // select min(col1) from table
$result = $pdoOne->min('','col1')->from('table'); // select min(col1) from table
$result = $pdoOne->min('col1')->from('table'); // select min(col1) from table

max($sql,$arg='*')

Generates a query that returns the maximum value of a column. If $arg is empty then it uses $sql for the name of the column It is a macro of the method select()

$result = $pdoOne->max('from table where condition=1','col'); // select max(col) from table where c..
$result = $pdoOne->max('from table','col1'); // select max(col1) from table

sum($sql,$arg='*')

Generates a query that returns the sum value of a column. If $arg is empty then it uses $sql for the name of the column It is a macro of the method select()

$result = $pdoOne->sum('from table where condition=1','col'); // select sum(col) from table where c..
$result = $pdoOne->sum('from table','col1'); // select sum(col1) from table

avg($sql,$arg='*')

Generates a query that returns the average value of a column. If $arg is empty then it uses $sql for the name of the column It is a macro of the method select()

$result = $pdoOne->avg('from table where condition=1','col'); // select avg(col) from table where c..
$result = $pdoOne->avg('from table','col1'); // select avg(col1) from table

distinct($distinct='distinct')

Generates a select command.

$results = $pdoOne->select("col1,col2")->distinct(); //...

Generates the query: select distinct col1,col2 ....

Note: ->distinct('unique') returns select unique ..

from($tables)

Generates a "from" sql command.

$results = $pdoOne->select("*")->from('table'); //...

Generates the query: select * from table

$tables could be a single table or a sql construction. For examp, the next command is valid:

$results = $pdoOne->select("*")->from('table t1 inner join t2 on t1.c1=t2.c2'); //...

where($where,[$arrayParameters=array()])

Generates a where command.

$results = $pdoOne->select("*")
->from('table')
->where('p1=1'); //...

The where could be expressed in different ways.

Where() without parameters.

It is possible to write the where without parameters as follows:

$results = $pdoOne->select("*")->from('table')->where("p1=1 and p2>2.5 or p3 like '%aa%'");

Where() with parameters defined by an indexed array.

$aa='aa';
$results = $pdoOne->select("*")->from('table')->where("p1=? and p2>? or p3 like ?",[1
                                                                                    ,2.5
                                                                                    ,"%$aa%"]);

It also works

// (if there is only a single argument without a type)
$results = $pdoOne->select("*")->from('table')->where("p1=?",[1]);  // = where("p1=?",[1]);
// (if we don't define to where to put the value)
$results = $pdoOne->select("*")->from('table')->where("p1",[1]); // = where("p1=?",[1]);

Where() using an associative array

It is a shorthand definition of a query using an associative array, where the key is the name of the column and the value is the value to compare

It only works with equality (=) and the logic operator 'and' (the type is defined automatically)

// select * from table where p1='1' and p2='2.5' and p3='aa'
$results = $pdoOne->select("*")->from('table')->where(['p1'=>1
                                                       ,'p2'=>2.5
                                                       ,'p3'=>'aa']);

Also, it is possible to specify the type of parameter.

// select * from table where p1=1 and p2='2.5' and p3='aa'
$results = $pdoOne->select("*")->from('table')->where(['p1'=>[1]
                                                       ,'p2'=>[2.5]
                                                       ,'p3'=>['aa']]);

Where() using an associative array and named arguments

You could also use an associative array as argument and named parameters in the query

$results = $pdoOne->select("*")->from("table")
    ->where('condition=:p1 and condition2=:p2',['p1'=>'Coca-Cola','p2'=>1])
    ->toList();

Generates the query: select * from table where condition=?(Coca-Cola) and condition2=?(1)

Examples of where()

Generates the query: select * from table where p1=1

Note: ArrayParameters is an array as follows: type,value.
Where type is i=integer, d=double, s=string or b=blob. In case of doubt, use "s" (see table bellow)
Example of arrayParameters:
[1 ,'hello' ,20.3 ,'world']

$results = $pdoOne->select("*")
->from('table')
->where('p1=?',[1]); //...

Generates the query: select * from table where p1=?(1)

$results = $pdoOne->select("*")
->from('table')
->where('p1=? and p2=?',[1,'hello']); //...

Generates the query: select * from table where p1=?(1) and p2=?('hello')

Note. where could be nested.

$results = $pdoOne->select("*")
->from('table')
->where('p1=?',[1])
->where('p2=?',['hello']); //...

Generates the query: select * from table where p1=?(1) and p2=?('hello')

You could also use:

$results = $pdoOne->select("*")->from("table")
    ->where(['p1'=>'Coca-Cola','p2'=>1])
    ->toList();

Generates the query: select * from table where p1=?(Coca-Cola) and p2=?(1)

order($order)

Generates an order command.

$results = $pdoOne->select("*")
->from('table')
->order('p1 desc'); //...

Generates the query: select * from table order by p1 desc

group($group)

Generates a group command.

$results = $pdoOne->select("*")
->from('table')
->group('p1'); //...

Generates the query: select * from table group by p1

having($having,[$arrayParameters])

Generates a having command.

Note: it uses the same parameters as where()

$results = $pdoOne->select("*")
->from('table')
->group('p1')
->having('p1>?',array(1)); //...

Generates the query: select * from table group by p1 having p1>?(1)

Note: Having could be nested having()->having()
Note: Having could be without parameters having('col>10')

End of the chain

runGen($returnArray=true)

Run the query generate.

Note if returnArray is true then it returns an associative array. if returnArray is false then it returns a mysqli_result
Note: It resets the current parameters (such as current select, from, where, etc.)

toList($pdoMode)

It's a macro of runGen(). It returns an associative array or false if the operation fails.

$results = $pdoOne->select("*")
->from('table')
->toList();

toPdoStatement($pdoMode)

It returns a PdoStatement from the current query

Note: if you want to loop the statement, then you can use fetchLoop()

Example:

$stmt = $pdoOne->select("*")
  ->from('table')
  ->toPdoStatement(); 
while ($row = $stmt->fetch()) {
  // do something
}

fetchLoop($callable,$pdoMode)

It fetches a query for every row.
This method could be used when we don't want to read all the information at once, so you can read and process each line separately
Example: 

$this->select('select id,name from table')
      ->fetchLoop(static function($row) {return($row);},\PDO::FETCH_ASSOC)

toMeta()

It returns a metacode (definitions) of each column of a query.

$results = $pdoOne->select("*")
->from('table')
->toMeta();

or

$results = $pdoOne->toMeta('select * from table');

result:

array(3) {
  [0]=>
  array(7) {
    ["native_type"]=>
    string(4) "LONG"
    ["pdo_type"]=>
    int(2)
    ["flags"]=>
    array(2) {
      [0]=>
      string(8) "not_null"
      [1]=>
      string(11) "primary_key"
    }
    ["table"]=>
    string(11) "producttype"
    ["name"]=>
    string(13) "idproducttype"
    ["len"]=>
    int(11)
    ["precision"]=>
    int(0)
  }
  [1]=>
  array(7) {
    ["native_type"]=>
    string(10) "VAR_STRING"
    ["pdo_type"]=>
    int(2)
    ["flags"]=>
    array(0) {
    }
    ["table"]=>
    string(11) "producttype"
    ["name"]=>
    string(4) "name"
    ["len"]=>
    int(135)
    ["precision"]=>
    int(0)
  }
}

toListSimple()

It's a macro of runGen. It returns an indexed array from the first column

$results = $pdoOne->select("*")
->from('table')
->toListSimple(); // ['1','2','3','4']

toListKeyValue()

It returns an associative array where the first value is the key and the second is the value.
If the second value does not exist then it uses the index as value (first value).

$results = $pdoOne->select("cod,name")
->from('table')
->toListKeyValue(); // ['cod1'=>'name1','cod2'=>'name2']

toResult()

It's a macro of runGen. It returns a mysqli_result or null.

$results = $pdoOne->select("*")
->from('table')
->toResult(); //

firstScalar($colName=null)

It returns the first scalar (one value) of a query. If $colName is null then it uses the first column.

$count=$this->count('from product_category')->firstScalar();

first()

It's a macro of runGen. It returns the first row if any, if not then it returns false, as an associative array.

$results = $pdoOne->select("*")
->from('table')
->first();

last()

It's a macro of runGen. It returns the last row (if any, if not, it returns false) as an associative array.

$results = $pdoOne->select("*")
->from('table')
->last();

Sometimes is more efficient to run order() and first() because last() reads all values.

sqlGen()

It returns the sql command and string.

$sql = $pdoOne->select("*")
->from('table')
->sqlGen();
echo $sql; // returns select * from table
$results=$pdoOne->toList(); // executes the query

Note: it doesn't reset the query.

Query Builder (DML)

There are four ways to execute each command.

Let's say that we want to add an integer in the column col1 with the value 20

Schema and values using a list of values: Where the first value is the column, the second is the type of value ( i=integer,d=double,s=string,b=blob) and second array contains the values.

$pdoOne->insert("table"
    ,['col1']
    ,[20]);

Schema and values in the same list: Where the first value is the column, the second is the type of value ( i=integer,d=double,s=string,b=blob) and the third is the value.

$pdoOne->insert("table"
    ,['col1',20]);

Schema and values using two associative arrays:

$pdoOne->insert("table"
    ,['col1']
    ,['col1'=>20]);

Schema and values using a single associative array: The type is calculated automatically.

$pdoOne->insert("table"
    ,['col1'=>20]);

insert($table,$schema,[$values])

Generates an insert command.

$pdoOne->insert("producttype"
    ,['idproducttype','name','type']
    ,[1,'cocacola',1]);

Using nested chain (single array)

    $pdoOne->from("producttype")
        ->set(['idproducttype',0 ,'name','Pepsi' ,'type',1])
        ->insert();

Using nested chain multiple set

    $pdoOne->from("producttype")
        ->set("idproducttype=?",[101])
        ->set('name=?',['Pepsi'])
        ->set('type=?',[1])
        ->insert();

or (the type is defined, in the possible, automatically by MySql)

    $pdoOne->from("producttype")
        ->set("idproducttype=?",[101])
        ->set('name=?','Pepsi')
        ->set('type=?',1)
        ->insert();

insertObject($table,[$declarativeArray],$excludeColumn=[])

    $pdoOne->insertObject('table',['Id'=>1,'Name'=>'CocaCola']);

Using nested chain declarative set

    $pdoOne->from("producttype")
        ->set('(idproducttype,name,type) values (?,?,?)',[100,'Pepsi',1])
        ->insert();

Generates the query: insert into productype(idproducttype,name,type) values(?,?,?) ....

update($$table,$schema,$values,[$schemaWhere],[$valuesWhere])

Generates an insert command.

$pdoOne->update("producttype"
    ,['name','type'] //set
    ,[6,'Captain-Crunch',2] //set
    ,['idproducttype'] // where
    ,[6]); // where
$pdoOne->update("producttype"
    ,['name'=>'Captain-Crunch','type'=>2] // set
    ,['idproducttype'=>6]); // where
$pdoOne->from("producttype")
    ->set("name=?",['Captain-Crunch']) //set
    ->set("type=?",[6]) //set
    ->where('idproducttype=?',[6]) // where
    ->update(); // update

or

$pdoOne->from("producttype")
    ->set("name=?",'Captain-Crunch') //set
    ->set("type=?",6) //set
    ->where('idproducttype=?',[6]) // where
    ->update(); // update

Generates the query: update producttype set name=?,type=? where idproducttype=? ....

delete([$table],[$schemaWhere],[$valuesWhere])

Generates a delete command.

$pdoOne->delete("producttype"
    ,['idproducttype'] // where
    ,[7]); // where
$pdoOne->delete("producttype"
    ,['idproducttype'=>7]); // where

Generates the query: delete from producttype where idproducttype=? ....

You could also delete via a DQL builder chain.

$pdoOne->from("producttype")
    ->where('idproducttype=?',[7]) // where
    ->delete(); 
$pdoOne->from("producttype")
    ->where(['idproducttype'=>7]) // where
    ->delete();

Generates the query: delete from producttype where idproducttype=? ....

Cache

It is possible to optionally cache the result of the queries. The duration of the query is also defined in the query. If the result of the query is not cached, then it is calculated normally (executing the query in the database). For identify a query as unique, the system generates a unique id (uid) based in sha256 created with the query, parameters, methods and the type of operation.

The library does not do any cache operation directly, instead it allows to cache the results using an external library.

How to configure it?

  1. We need to define a class that implements the interface \eftec\IPdoOneCache
class CacheService implements \eftec\IPdoOneCache {
    public $cacheData=[];
    public $cacheCounter=0; // for debug
    public  function getCache($uid,$family='') {
        if(isset($this->cacheData[$uid])) {
            $this->cacheCounter++;
            echo "using cache\n";
            return $this->cacheData[$uid];
        }
        return false;
    }
    public function setCache($uid,$family='',$data=null,$ttl=null) {

        $this->cacheData[$uid]=$data;
    }
    public function invalidateCache($uid = '', $family = '') {
        unset($this->cacheData[$uid]);
    }
}
$cache=new CacheService();

(2) Sets the cache service

    $pdoOne=new PdoOne("mysql","127.0.0.1","travis","","travisdb");
    $cache=new CacheService();
    $$pdoOne->setCacheService($cache);

(3) Use the cache as follows, we must add the method useCache() in any part of the query.

    $pdoOne->select('select * from table')
        ->useCache()->toList(); // cache that never expires
    $pdoOne->select('select * from table')
        ->useCache(1000)->toList(); // cache that lasts 1000ms.

Example using apcu

class CacheService implements \eftec\IPdoOneCache {
    public  function getCache($uid,$family='') {
        return apcu_fetch($uid);
    }
    public function setCache($uid,$family='',$data=null,$ttl=null) {
        apcu_store($uid,$data,$ttl);
    }
    public function invalidateCache($uid = '', $family = '') {
        // invalidate cache
        apcu_delete($uid);
    }
}
$cache=new CacheService();

Sequence

Sequence is an alternative to AUTO_NUMERIC (identity) field. It has two methods to create a sequence: snowflake and sequence. It is an alternative to create a GUID mainly because it returns a number (a GUID usually is a string that it is more expensive to index and to store)

The goal of the sequence is to create a unique number that it is never repeated.

Creating a sequence

$dao->nodeId=1; // optional
$dao->tableSequence='snowflake'; // optional
$dao->createSequence(); // it creates a table (and it could create a store procedure) called snowflake and a function called next_snowflake(). You could create it only once.

Creating a sequence without a table.

It is possible to create a new sequence without any table. It is fast, but it could have problems of collisions.

It ensures a collision free number only if we don't do more than one operation per 0.0001 second However, it also adds a pseudo random number (0-4095 based in time) so the chances of collision is 1/4095 (per two operations done every 0.0001 second). It is based on Twitter's Snowflake number. i.e.. you are safe of collisions if you are doing less than 1 million of operations per second (technically: 45 millions).

$pdo->getSequencePHP() // string(19) "3639032938181434317" 
$dao->getSequencePHP(true) // string(19) "1739032938181434311"

Using the sequence

$pdo->getSequence() // string(19) "3639032938181434317" 
$pdo->getSequencePHP() // string(19) "3639032938181434317" 
$pdo->getSequence(true) // returns a sequence by flipping some values.
$pdo->getSequencePHP(true) // string(19) "1739032938181434311"

Fields

Field Description Example
$prefixBase If we need to add a prefix to every table $this->prefixBase='example_';
$internalCacheCounter The counter of hits of the internal cache. $this->internalCacheCounter=;
$nodeId Used by sequence (snowflake). nodeId It is the identifier of the node. It must be between 0..1023 $this->nodeId=3;
$tableSequence The name of the table sequence (snowflake) $this->tableSequence="tableseq1";
$masks0 If we want to generate an unpredictable number (used by sequence) $this->masks0=[0,1,2,3,4];
$masks1 If we want to generate an unpredictable number (used by sequence) $this->masks1=[4,3,2,1,0];
$databaseType The current type of database. It is set via el constructor echo $this->databaseType;
$server The current server machine echo $this->server;
$user The current user echo $this->user;
$pwd The current password echo $this->pwd;
$db The current database or schema (oracle ignores this value) echo $this->db;
$charset To set the default charset. It must be set via constructor echo $this->charset;
$isOpen It is true if the database is connected otherwise,it's false if($this->isOpen) { …};
$throwOnError If true (default), then it throws an error if happens an error. If false, then the execution continues $this->throwOnError=false;
$conn1 The instance of PDO. You can set it or use it directly. $this->conn1->pdoStatement(..);
$transactionOpen True if the transaction is open if($this->transactionOpen) { …};
$readonly if the database is in READ ONLY mode or not. If true then we must avoid to write in the database $this->readonly=true;
$logFile full filename of the log file. If it's empty then it doesn't store a log file. The log file is limited to 1mb $this->logFile="/folder/file.log";
$errorText It stores the last error. runGet and beginTry resets it echo $this->errorText;
$isThrow todo $this->isThrow=;
$logLevel It indicates the current level of log. 0 = no log (for production), 3= full log $this->logLevel=3;
$lastQuery Last query executed echo $this->lastQuery;
$lastParam The last parameters. It is an associative array echo $this->lastParam;

Encryption

This library permits encryption/decryption of the information.

To set the encryption you could use the next command:

$this->setEncryption(12345678, '', 'INTEGER'); // the type of encryption is integer and it only works with integers. It doesn't use a salt value
$this->setEncryption('password', 'some-salt', 'AES-256-CTR'); // the password, the salt and the type of encryption (aes-256-ctr), you can use other methods
$this->setEncryption('passwrd', '', 'SIMPLE'); // the type of encryption is simple and it only works with primitive values. It doesn't use a salt.

Then you can encrypt and decrypt a value using

$encrypted=$this->encrypt($original); // encrypt $original
$original=$this->decrypt($encrypted); // decrypt $encrypted

Example:

$this->setEncryption('12345', 'salt-1234'); // it will use AES-256-CTR, the password and the salt must be secret.
// create user
$this->set(['username' => 1, 'password' => $this->encrypt($password)])
     ->from('user')
     ->insert();
// validate user
$user=$this->select(['username','password'])
    ->from('user')
    ->where(['username','password'],[1,$this->encrypt($password)])
             ->first();
// $user= if false or null then the user does not exist or the password is incorrect.

How to debug and trace errors in the database?

Setting the log level

You can set the log level to 3. The log level works when the operation fails, the higher the log level, then it shows most information.

$pdoOne->logLevel=3; // the highest for debug.

Throwing errors

By default, PdoOne throws PHP errors, but we could avoid it by setting the field $throwOnError to false.

$pdoOne->throwOnError=false; // it could be used in production.

Getting the last Query

var_dump($pdoOne->lastQuery); // it shows the last query
var_dump($pdoOne->lastParam); // and it shows the last parameters.

Generating a log file

If empty then it will not generate a log file (using the php log file)

$pdoOne->logFile=true;

CLI

PdoOne has some features available only in CLI.

Run as cli

Execute the next line (in the lib folder)

php pdoonecli.php

(or pointing to the right folder)

php /var/web/vendor/eftec/lib/pdoonecli

Run as CLI interative

You could use the flag "-i" to enter in interactive mode.

You could use the TAB key to autocomplete values (if any).

Note: You could also save and load the configuration.

Examples

Connect to mysql and generate a csv from the table "actor"

## via arguments
php pdoonecli --databasetype mysql --server 127.0.0.1 -u root -p abc.123 --database sakila -in actor -out csv
## via user input (interactive)
php pdoonecli -i -in actor -out csv

Save the configuration in a file

php pdoonecli --databasetype mysql --server 127.0.0.1 -u root -p abc.123 --database sakila --saveconfig myconfig

Load the configuration from a file

php pdoonecli --loadconfig myconfig -in actor -out csv

Run CLI to generate repository classes.

You could use the flag "-cli" to generate the repository classes

The CLI is interactive, and it allows to load and save the configuration.

cli-classcode

The functionality will generate a ready-to-use repository class.

Let's say the next example

mysql:
php pdoone.php --database mysql --server 127.0.0.1:3306 --user root -p abc.123 -db sakila --input "Actor" --output classcode
sqlsrv:
php pdoone.php --database sqlsrv --server PCJC\SQLEXPRESS --user sa -p abc.123 -db sakila --input "Actor" --output classcode

It will connect to the database mysql, ip: 127.0.0.1 and database sakila, and it will read the "actor" table.

It will return the next result

/**
 * Generated by PdoOne Version 1.28
 * Class ActorRepo
 */
class ActorRepo
{
    const TABLE = 'Actor';
    const PK = 'actor_id';
    /** @var PdoOne */
    public static $pdoOne = null;

    /**
     * It creates a new table<br>
     * If the table exists then the operation is ignored (and it returns false)
     *
     * @param array $definition
     * @param null  $extra
     *
     * @return array|bool|PDOStatement
     * @throws Exception
     */
    public static function createTable($definition, $extra = null) {
        if (!self::getPdoOne()->tableExist(self::TABLE)) {
            return self::getPdoOne()->createTable(self::TABLE, $definition, self::PK, $extra);
        }
        return false; // table already exist
    }
    // .....
}

This functionality will generate a new Repository class with the most common operations: insert, list, update, delete, get, count, create table, drop table and truncate table

Why we need to generate a class? (instead of inherit one) This Crud class is only a starting point. The developer could modify the code, add new methods, modify previous method and so on.

For to use the class, we could write the next code:

// 1) option 1, inject an instance of $pdo
ActorRepo::setPdoOne($pdoOne); // it inject the current connect to the database

// 2) option 2.
// If the global variable $pdoOne exists, then it is injected. (unless it is defined by using setPdoOne()
$pdoOne=new PdoOne("mysql","127.0.0.1","root","abc.123","sakila","");
$pdoOne->connect();

// 3) option 3
// If the global function pdoOne() exists, then it is used for obtain the instance.
function pdoOne() {
    global $pdo;
    if ($pdo===null) {
        $pdo=new PdoOne('mysql','127.0.0.1','root','abc.123','sakila');
    }
    return $pdo;
}

$actorActorRepo::get(2); // it will read the actor with the pk=2 and it will return as an array.
$actors=$actorArray=ActorRepo::select(); // it returns all the rows.

Alternatively, you could generate the php file automatically as follows:

php pdoone.php -database mysql -server 127.0.0.1:3306 -user root -pwd abc.123 -db sakila -input "Actor" -output classcode >ActorRepo.php

Note: the code lacks of php-tags, namespace and use but everything else is here.

cli-selectcode

It will take a query and will return a php code with the query formatted.

Example:

php pdoone.php -database mysql -server 127.0.0.1:3306 -user root -pwd abc.123 -db sakila -input "select * from actor" -output selectcode

It will generate the next code:

 /** @var array $result=array(["actor_id"=>0,"first_name"=>'',"last_name"=>'',"last_update"=>'']) */
 $result=$pdo
         ->select("*")
         ->from("actor")
         ->toList();

cli-arraycode

It will generate an associative array (with default values) based in the query or table selected.

php pdoone.php -database mysql -server 127.0.0.1:3306 -user root -pwd abc.123 -db sakila -input "select * from actor" -output arraycode

It will return:

// ["actor_id"=>0,"first_name"=>'',"last_name"=>'',"last_update"=>'']

cli-json

It will return the result of the query as a json

php pdoone.php -database mysql -server 127.0.0.1:3306 -user root -pwd abc.123 -db sakila -input "select * from actor" -output json

It will return:

[{"actor_id":"1","first_name":"PENELOPE","last_name":"GUINESS","last_update":"2006-02-15 01:34:33"}
,{"actor_id":"2","first_name":"NICK","last_name":"WAHLBERG","last_update":"2006-02-15 01:34:33"}
,{"actor_id":"3","first_name":"ED","last_name":"CHASE","last_update":"2006-02-15 01:34:33"}
,{"actor_id":"4","first_name":"JENNIFER","last_name":"DAVIS","last_update"}]

cli-csv

It will return the result of the query as a json

php pdoone.php -database mysql -server 127.0.0.1:3306 -user root -pwd abc.123 -db sakila -input "select * from actor" -output csv

It will return:

actor_id,first_name,last_name,last_update
1,"PENELOPE","GUINESS","2006-02-15 01:34:33"
2,"NICK","WAHLBERG","2006-02-15 01:34:33"
3,"ED","CHASE","2006-02-15 01:34:33"
4,"JENNIFER","DAVIS","2006-02-15 01:34:33"

UI

Alternatively to the CLI, the library has an interface visual. It does all the operation of the CLI.

How to run the UI?

Simply call the method render()

<?php

use eftec\PdoOne;
use mapache_commons\Collection;

include "../vendor/autoload.php";

$dao=new PdoOne("test","127.0.0.1","dummy","dummy","dummy"); // we need any connection.
$dao->logLevel=3;

$dao->render();

There is an example in the folder examples/testui.php

DDL Database Design Language

The next commands usually are executed alone (not in a chain of methods)

Method Description Example
createTable() Creates the table and indexes using the definition inside the Repo TablaParentRepo::createTable();
createForeignKeys() Create all foreign keys of the table TablaParentRepo::createForeignKeys();
dropTable() Drop the table TablaParentRepo::dropTable();
truncate() Truncate the table TablaParentRepo::truncate();
validTable() Validate if the table hasn't changed $ok=TablaParentRepo::validTable(); 
TablaParentRepo::createTable();
TablaParentRepo::createForeignKeys();
TablaParentRepo::dropTable();
TablaParentRepo::truncate();
// We don't have a method to alter a table.
$ok=TablaParentRepo::validTable(); // it returns true if the table matches with the definition stored into the clas

Nested Operators

The nested operators are methods that should be in between of our chain of methods.

ClassRepo::op()::where()::finalop() is ✅

ClassRepo::op()::op()::where() will leave the chain open ❌

For example:

// select * 
//        from table 
//        inner join table2 on t1=t2 
//        where col=:arg
//        and col2=:arg2
//    group by col
//        having col3=:arg3
//    order by col
//    limit 20,30
$results=$pdo->select('*')
    ->from('table')
    ->innerjoin('table2 on t1=t2')
    ->where('col=:arg and col2:=arg2',[20,30]) 
    // it also works with ->where('col=:arg',20)->where('col2'=>30)
    // it also works with ->where('col=?',20)->where('col2=?'=>30)
    ->group('col')
    ->having('col3=:arg3',400)
    ->order('col')
    ->limit('20,30')
    ->toList(); // end of the chain
Method Description Example
where() It adds a where to the chain TablaParentRepo::where()
order() It adds a order by to the chain TablaParentRepo::order()
group() it adds a group by to the chain TablaParentRepo::group()
limit() It limits the results TablaParentRepo::limit()
page() Its similar to limit but it uses page TablaParentRepo::page()
innerjoin() It adds a inner join to the query TablaParentRepo::innerjoin()
left() It adds a left join to the query TablaParentRepo::left()
right() It adds a right join to the query TablaParentRepo::right()

DQL Database Query Language

We have different methods to generate a DQL (query) command in our database.

If the operation fails, they return a FALSE, and they could trigger an exception.

The next methods should be at the end of the chain. Examples:

ClassRepo::op()::op()::toList() is ✅

ClassRepo::op()::toList()::op() will trigger an exception ❌

Command Description Example
toList() Returns an array of elements $data=TableNameRepo::toList(); // select from tablerepo
$data=TableNameRepo::where('a1=?',[$value])::toList(); // select from tablerepo where a1=$value
first() Returns a simple row $data=TableNameRepo::first($pk); // select * from tablerepo where pk=$pk (it always returns 1 or zero values)
$data=TableNameRepo::where('a1=?',[$value])::first(); // it returns the first value (or false if not found)
exist() Returns true if a primary key exists $data=TableNameRepo::exist($pk); // returns true if the object exists.
count() Returns the number of rows in a query $data=TableNameRepo::count($conditions);
$data=TableNameRepo::where('a1=?',[$value])::count();

DML Database Model Language

The next methods allow inserting,update or delete values in the database.

Method Description Example
insert It inserts a value into the database. It could return an identity $identity=TablaParentRepo::insert($obj);
update It updates a value into the database. TablaParentRepo::update($obj);
delete It deletes a value from the database. TablaParentRepo::delete($obj);
deletebyId It deletes a value (using the primary key as condition) from the database. TablaParentRepo::deleteById($pk); 
// where obj is an associative array or an object, where the keys are the name of the columns (case sensitive)
$identity=TablaParentRepo::insert($obj); 
TablaParentRepo::update($obj);
TablaParentRepo::delete($obj);
TablaParentRepo::deleteById(id);

Validate the model

It is possible to validate the model. The model is validated using the information of the database, using the type of the column, the length, if the value allows null and if it is identity (auto numeric).

$obj=['IdUser'=>1,'Name'='John Doe']; 
UserRepo::validateModel($obj,false,['_messages']); // returns true if $obj is a valid User.

Recursive

A recursive array is an array of strings with values that it could be read or obtained or compared. For example, to join a table conditionally. PdoOne does not use it directly but _BasePdoOneRepo uses it (_BasePdoOneRepo is a class used when we generate a repository service class automatically).

Example

$this->select('*')->from('table')->recursive(['table1','table1.table2']);
// some operations that involves recursive
if($this->hasRecursive('table1')) {
    $this->innerJoin('table1 on table.c=table1.c');
}
if($this->hasRecursive('table1.table2')) {
    $this->innerJoin('table1 on table1.c=table2.c');
}
$r=$this->toList(); // recursive is resetted.

recursive()

It sets a recursive array.

This value is resets each time a chain methods ends.

getRecursive()

It gets the recursive array.

hasRecursive()

It returns true if recursive has some needle.

If $this->recursive is ['*'] then it always returns true.

$this->select('*')->from('table')->recursive(['*']);
$this->hasRecursive('anything'); // it always returns true.

Benchmark (mysql, estimated)

Library Insert findPk hydrate with time
PDO 671 60 278 887 3,74
PdoOne 774 63 292 903 4,73
LessQL 1413 133 539 825 5,984
YiiM 2260 127 446 1516 8,415
YiiMWithCache 1925 122 421 1547 7,854
Yii2M 4344 208 632 1165 11,968
Yii2MArrayHydrate 4114 213 531 1073 11,22
Yii2MScalarHydrate 4150 198 421 516 9,537
Propel20 2507 123 1373 1960 11,781
Propel20WithCache 1519 68 1045 1454 8,228
Propel20FormatOnDemand 1501 72 994 1423 8,228
DoctrineM 2119 250 1592 1258 18,139
DoctrineMWithCache 2084 243 1634 1155 17,952
DoctrineMArrayHydrate 2137 240 1230 877 16,83
DoctrineMScalarHydrate 2084 392 1542 939 18,887
DoctrineMWithoutProxies 2119 252 1432 1960 19,822
Eloquent 3691 228 708 1413 12,155

PdoOne adds a bit of ovehead over PDO, however it is simple a wrapper to pdo.

migration from 3 to 4

Error FAQs

Uncaught Error: Undefined constant eftec_BasePdoOneRepo::COMPILEDVERSION

It means that you are updated PdoOne, and you are using one class generated by the ORM. This class must be re-generated.

Changelist

In a nutshell:

Every major version means that it could break old code. I.e. 1.0 -> 2.0

Every minor version means that it adds a new functionality i.e. 1.5 -> 1.6 (new methods)

Every decimal version means that it patches/fixes/refactoring a previous functionality i.e. 1.5.0 -> 1.5.1 (fix)

$this->setUseInternalCache(true);
$rows=$this->select('*')->from('table')->where(['i'=>1])->toList(); // read from the database
// ...
$rows2=$this->select('*')->from('table')->where(['i'=>1])->toList(); // read from memory
// ...
$rows3=$this->select('*')->from('table')->where(['i'=>2])->toList(); // read from the database because the query is in 
                                                                     // memory but the parameters are different 

echo $this->internalCacheCounter;