Download the PHP package eftec/pdoone without Composer

On this page you can find all versions of the php package eftec/pdoone. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.

FAQ

After the download, you have to make one include require_once('vendor/autoload.php');. After that you have to import the classes with use statements.

Example:
If you use only one package a project is not needed. But if you use more then one package, without a project it is not possible to import the classes with use statements.

In general, it is recommended to use always a project to download your libraries. In an application normally there is more than one library needed.
Some PHP packages are not free to download and because of that hosted in private repositories. In this case some credentials are needed to access such packages. Please use the auth.json textarea to insert credentials, if a package is coming from a private repository. You can look here for more information.

  • Some hosting areas are not accessible by a terminal or SSH. Then it is not possible to use Composer.
  • To use Composer is sometimes complicated. Especially for beginners.
  • Composer needs much resources. Sometimes they are not available on a simple webspace.
  • If you are using private repositories you don't need to share your credentials. You can set up everything on our site and then you provide a simple download link to your team member.
  • Simplify your Composer build process. Use our own command line tool to download the vendor folder as binary. This makes your build process faster and you don't need to expose your credentials for private repositories.
Please rate this library. Is it a good library?

Informations about the package pdoone

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

into this

or using the ORM (using eftec/PdoOneORM library)

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.

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()

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.

But we could change it to returns an array

We could also pass parameters.

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

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.

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.

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

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:

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:

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.

min max avg sum count
1 205 103.0000 21115 205

columnTable($tablename)

Returns all columns of a table

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)

Example (sqlsrv)

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.

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

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

foreignKeyTable

It returns all the foreign keys of a table.

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:

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.

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

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()

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()

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()

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()

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()

distinct($distinct='distinct')

Generates a select command.

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

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

from($tables)

Generates a "from" sql command.

Generates the query: select * from table

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

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

Generates a where command.

The where could be expressed in different ways.

Where() without parameters.

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

Where() with parameters defined by an indexed array.

It also works

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)

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

Where() using an associative array and named arguments

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

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']

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

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

Note. where could be nested.

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

You could also use:

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

order($order)

Generates an order command.

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

group($group)

Generates a group command.

Generates the query: select * from table group by p1

having($having,[$arrayParameters])

Generates a having command.

Note: it uses the same parameters as where()

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.

toPdoStatement($pdoMode)

It returns a PdoStatement from the current query

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

Example:

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:

toMeta()

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

or

result:

toListSimple()

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

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).

toResult()

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

firstScalar($colName=null)

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

first()

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

last()

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

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

sqlGen()

It returns the sql command and string.

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.

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.

Schema and values using two associative arrays:

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

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

Generates an insert command.

Using nested chain (single array)

Using nested chain multiple set

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

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

Using nested chain declarative set

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

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

Generates an insert command.

or

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

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

Generates a delete command.

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

You could also delete via a DQL builder chain.

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

(2) Sets the cache service

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

Example using apcu

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

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).

Using the sequence

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:

Then you can encrypt and decrypt a value using

Example:

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.

Throwing errors

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

Getting the last Query

Generating a log file

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

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"

Save the configuration in a file

Load the configuration from a file

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

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:

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:

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:

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:

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:

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()

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();

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:

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);

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).

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

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.

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)


All versions of pdoone with dependencies

PHP Build Version
Package Version
Requires php Version >=7.4
ext-json Version *
eftec/clione Version ^1.33
ext-pdo Version *
ext-readline Version *
eftec/messagecontainer Version ^2.9
Composer command for our command line client (download client) This client runs in each environment. You don't need a specific PHP version etc. The first 20 API calls are free. Standard composer command

The package eftec/pdoone contains the following files

Loading the files please wait ....