Download the PHP package eftec/pdoone without Composer

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.

[]() []() []() []() []()

Turn this

into this

or using the ORM (using eftec/PdoOneORM library)

Table of contents

• Database Access Object wrapper for PHP and PDO in a single class
• Table of contents
  • Examples
  • Installation
  • Install (using composer)
  • Install (manually)
  • How to create a Connection?
  • OCI
  • How to run a SQL command?
  • 1. Running a raw query
  • 2. Running a native PDO statement
  • 3. Running using the query builder
  • 4. Running using an ORM
  • How to work with Date values?
  • How to run a transaction?
  • Custom Queries
  • tableExist($tableName)
  • statValue($tableName,$columnName)
  • columnTable($tablename)
  • foreignKeyTable($tableName)
  • createTable($tableName,$definition,$primaryKey=null,$extra='',$extraOutside='')
  • tableSorted($maxLoop = 5, $returnProblems = false, $debugTrace = false)
  • validateDefTable($pdoInstance,$tablename,$defTable,$defTableKey)
  • foreignKeyTable
  • Query Builder (DQL)
  • select($columns)
  • count($sql,$arg='*')
  • min($sql,$arg='*')
  • max($sql,$arg='*')
  • sum($sql,$arg='*')
  • avg($sql,$arg='*')
  • distinct($distinct='distinct')
  • from($tables)
  • where($where,[$arrayParameters=array()])
    • Where() without parameters.
    • Where() with parameters defined by an indexed array.
    • Where() using an associative array
    • Where() using an associative array and named arguments
    • Examples of where()
  • order($order)
  • group($group)
  • having($having,[$arrayParameters])
  • End of the chain
    • runGen($returnArray=true)
    • toList($pdoMode)
• toPdoStatement($pdoMode)
• fetchLoop($callable,$pdoMode)
  • toMeta()
  • toListSimple()
  • toListKeyValue()
  • toResult()
  • firstScalar($colName=null)
  • first()
  • last()
  • sqlGen()
    • Query Builder (DML)
    • insert($table,$schema,[$values])
    • insertObject($table,[$declarativeArray],$excludeColumn=[])
    • update($$table,$schema,$values,[$schemaWhere],[$valuesWhere])
    • delete([$table],[$schemaWhere],[$valuesWhere])
    • Cache
    • How to configure it?
    • Example using apcu
    • Sequence
    • Creating a sequence
    • Creating a sequence without a table.
    • Using the sequence
    • Fields
    • Encryption
    • How to debug and trace errors in the database?
    • Setting the log level
    • Throwing errors
    • Getting the last Query
    • Generating a log file
    • CLI
    • Run as cli
    • Run as CLI interative
  • Examples
    • Run CLI to generate repository classes.
    • cli-classcode
    • cli-selectcode
    • cli-arraycode
    • cli-json
    • cli-csv
    • UI
    • How to run the UI?
    • DDL Database Design Language
    • Nested Operators
    • DQL Database Query Language
    • DML Database Model Language
    • Validate the model
    • Recursive
  • recursive()
  • getRecursive()
  • hasRecursive()
    • Benchmark (mysql, estimated)
    • migration from 3 to 4
    • Error FAQs
    • Uncaught Error: Undefined constant eftec_BasePdoOneRepo::COMPILEDVERSION
    • Changelist

Examples

ExampleTicketPHP	Example cupcakes	Example Search	Example Different Method

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

• "mysql" is the MySQL database. It also allows sqlsrv (for sql server) or "oci"
• 127.0.0.1 is the server where is the database.
• root is the user
• abc.123 is the password of the user root.
• sakila is the database used.
• "" (optional) it could be a log file, such as c:\temp\log.txt

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)

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

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

This library also 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.

How to work with Date values?

PdoOne allows 5 types of dates.

• SQL Format It is the format how the date is stored into the database. It depends on the type of the database. For example MySQL could use the format Y-m-d.

• Human Format It is the format how the end user looks our date.

• ISO Date Format. It is the format how the value could be transported and serialized.

• Timestamp: It counts the number of seconds after 1-1-1970

• Class / PHP Class: It is an DateTime object.

How to run a transaction?

There are 3 methods to runs 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.

columnTable($tablename)

Returns all columns of a table

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.

• $definition The definition is an associative array with the name of the column as key and the definition as value.
• primaryKey It could be a string or associative array.
  • if it is a string then it is the name of the primary key, example "user_id";
  • if it is an associative array, then it could be used to define primary key, unique, key and foreign keys:
    • 'key_name'=>'PRIMARY KEY'
    • 'key_name'=>'KEY'
    • 'key_name'=>'UNIQUE KEY'
    • 'key_name'=>'FOREIGN KEY REFERENCES TABLEREF(COLREF) ...'
• $extra It defines an extra definition inside the definition of the table.
• extraOutside It defines an extra definition after the definition of the table.

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.

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.

• $where is an array or a string. If it's a string, then it's evaluated by using the parameters. if any

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.

• Cache works with the next methods.
  • toList()
  • toListSimple()
  • first()
  • firstScalar()
  • last()

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.

• sequence: It uses the functionality of the database to create and use a sequence. MySql doesn't have sequences but they are emulated. The main problem of the sequence is it returns a consecutive number, example: 1,2,3,4... This number is predictable. For example, if you are the user number 20, then you can guess another user = 19, 21, etc.
• snowflakes: It uses a table to generate a unique ID. The sequence used is based on Twitter's Snowflake, and it is generated based on time (with microseconds), nodeId and a unique sequence. This generates a LONG (int 64) value that it's unique. Example: 10765432100123456789. This number is partially predictable .

Creating a sequence

• $dao->nodeId set the node value (default is 1). If we want unique values amongst different clusters, then we could set the value of the node as unique. The limit is up to 1024 nodes.
• $dao->tableSequence it sets the table (and function), the default value is snowflake.

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([unpredictable=false]) Returns a sequence without using a table. This sequence is more efficient than $dao->getSequence, but it uses a random value to deals with collisions.

• If upredictable is true then it returns an unpredictable number (it flips some digits)

Using the sequence

• $dao->getSequence([unpredictable=false]) returns the last sequence. If the sequence fails to generate, then it returns -1. The function could fail if the function is called more than 4096 times every 1/1000th second.

Fields

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.

• 0=no debug for production (all message of error are generic)
  
• 1=it shows an error message
  
• 2=it shows the error messages and the last query
• 3=it shows the error message, the last query and the last parameters (if any). It could be unsafe (it could show passwords)

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)

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:

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 ❌

DML Database Model Language

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

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)

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

migration from 3 to 4

• If you are not using ORM features, then you don't need to do any change.
• If you are using ORM features then:
  • Add the new library eftec/PdoOneORM
  • Use $pdo=new PdoOneORM(); instead of $pdo=new PdoOne();
  • The filename configuration of the CLI must be rebuilt again.

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)

• 4.6.2 2024-03-02
  • [fix]PdoOne::$cacheService is now mixed.
• 4.6.1 2024-03-02
  • [fix]PdoOne::$instance is null by default (instead of undefined)
  • updated CliOne dependency.
• 4.6 2024-03-02
  • Updating dependency to PHP 7.4. The extended support of PHP 7.2 ended 3 years ago.
  • Added more type hinting in the code.
• 4.4 2023-12-12
  • updated PodOneQuery to be compatible with PdoOneORM
  • update phpdoc to be compatible with Visual Studio Code.
• 4.3.3 2023-09-05
  • change the PHPDOC comments, now it uses markdown instead of "pre" tag.
• 4.3.2 2023-09-05
  • A small fix with the error messages, now on level 1 it shows the cause of the error.
• 4.3.1 2023-09-02
  • [PdoOneCli] Update to 2.5
  • Fixed a problem when the cli ends.
  • The class is able to return an instance of it.
• 4.3 2023-07-01
  • [PdoOneQuery] Update to 4.1
  • _toList() added argument $returnArray
  • toPdoStatement() new method
  • fetchLoop() new method.
• 4.2 2023-04-07
  • [PdoOne] the constructor allows to set the key-value table.
  • [PdoOne] new methods getTableKV() getDefaultTableKV(),existKVTable()
  • [PdoOneCli] Update to 2.3.1. Now you can enter more values. Also, load and save works in PHP format (it is more flexible4)
• 4.1.2 2023-03-21
  • str_start_with() is not defined in PHP older than PHP 8.0, so I replaced.
• 4.1.1 2023-03-21
  • [PdoOneCli] Update to 2.3.1.
  • [composer.json] Update dependency to eftec/clione 1.26 or higher.
• 4.1 2023-03-20
  • [PdoOneCli] Updated to 2.3. Now menu is generated by library CliOne.
• 4.0.1 2023-03-11
  • [PdoOneCli] Fix a small bug with option save. It failed to ask the filename.

• 4.00 2023-11-03

  • [PdoOne] 4.0 Now ORM features are separated in a different library called eftec/PdoOneORM.
  • If you want to use ORM, then install the library eftec/PdoOneORM and use it instead.
  • See migration from 3 to 4 for further information.
  • [PdoOne] new method factoryFromArray()
  • [PdoOne] Removed all old GUI features that are not available anymore.
  • [CLI] The cli was rebuilt. Only a few features are present. Now, it allows to save the configuration, load and save it as a PHP file.
  • [_BasePdoOne] Now it is separated in eftec/pdoonerepo
• 3.16 2023-12-02
  • [PdoOneQuery] 3.11
    • exists(),insert(),update(),delete(),deleteById() fixed a bug that caused a recursivity
    • insertObjects() avoids to add a numeric index. Now it adds the correct index
  • [PdoOne] 3.16
    • dateConvertInput() does not crash if the date is incorrect, and it tries to determine the current time.
    • runRawQuery() returns false if the inmediate query fails
    • Added the method generateCodeArrayRecursive() used to generate code.
    • rollback() allows to show the cause why it failed
    • The templates (used to generate the code) now validates some input and output values.
  • [BasePdoOneRepo] 6.7
    • validateModel() now works with date. If the date is incorrect then it returns false. It also cleans the last error. if the cause is correct.
    • recursive insert or update now validates if the record exists using the right class.
    • called to rollback() now stores the cause of why it failed.
• 3.15 2023-02-03
  • [PdoOneQuery] 3.10 Fixed a problem with insert(),update(),delete(), it will not reset the stack correctly.
  • [_BasePdoOneRepo] reset() method is now public.
• 3.14 2023-01-30
  • [PdoOneQuery] 3.9 Fixed a problem with first() where the primary key is not numeric.
  • [Pdo] /[PdoOneQuery] New method now() to obtain the date and time of the database.
  • [_BasePdoOneRepo] 12 Updated to binary version 12. You must rebuild the ORM files.
• 3.13 2023-01-26
  • [PdoOneQuery] Fixed a problem with single() value.
  • [PdoOne] Fixed if MessageContainer is not loaded (now, it is ignored and not used)
• 3.12.2 2022-09-03
  • [_BasePdoOneRepo] Added some missing argument hinting
  • [_BasePdoOneRepo] Fixed a problem with Insert()
  • [PdoOne] fixed some problem when some indexes are missing
  • [PdoOneEncryption] Added some missing argument hinting
• 3.12.1 2022-08-26
  • [PdoOneQuery] fixed a problem with page() in an ORM.
• 3.12 2022-08-14
  • [PdoOne] Added field $prefixTable to prefix every table in the database.
    • It works with select,insert,update,delete,from,count and many others operations.
• 3.11.1 2022-07-30
  • [CLI] update CLI to 1.6.1 Added the column "key" to "tablefull".
• 3.11 2022-07-30
  • [CLI] update CLI to 1.6. Added "relation2" and "tablefull" to definitions.
• 3.10 2022-07-30
  • [CLI] update CLI to 1.5.
• 3.9 2022-07-23
  • [ORM] [CLI] Now, you can savely edit some part of the code generated under the comment blocks marked as "EDIT".
• 3.8.1 2022-07-23
  • [ORM] fixed another problem with where() when the filter use a named parameter.
  • [CLI] Exit option exists instantly. And create does not exit.
• 3.8 2022-07-22
  • [ORM] fixed a problem when where is used and more than two column (different tables) use the same name of column.
• 3.7 2022-07-16
  • [ORM] fixed a problem with first
  • [ORM] fixed a problem with insert and conversion of columns
  • _BasePdoOne update to binary version 11. It will require regeneration of repository classes.
• 3.6 2022-07-07
  • [CLI] update CLI to 1.1
• 3.5 2022-07-06
  • [ORM] fixed recursive when "query", "insert","update" and "delete". Now recursive work with aliases.
  • _BasePdoOne update to binary version BINARYVERSION. It will require regeneration of repository classes.
• 3.3 2022-06-27
  • [CLI] updated CLI to 1.0
• 3.2 2022-06-27
  • [CLI] updated CLI to 0.14
• 3.1.6 2022-06-24
  • [CLI] solved a problem with the conversion per type of column
  • [_BasePdoOneRepo] Fixed a problem with the conversion of the end results.
• 3.1.5 2022-06-23
  • [PdoOneQuery] Solved a bug when the cache is invalidated when insert(),update(), etc.
• 3.1.4 2022-06-21
  • [CACHE] fixed a problem with the cache and type hinting.
• 3.1.3 2022-06-18
  • [ORM] fixed a bug with the template of the abstract file and limit()
• 3.1.2 2022-06-18
  • [ORM] fixed a bug with the template of the abstract file and limit()
• 3.1.1 2022-06-17
  • fixed a bug with limit() and page()
• 3.1 2022-06-11
  • fixed a problem with mysql in Linux in the method getdeftablefk()
• 3.0 2022-06-1
  • _BasePdoOneRepo rebuild from scratch.
  • [ORM] the use of dependencies are changed.
• 2.32 2022-03-20
  • createTable() allows to specify the definition of the columns universally using a pseudo php syntax.
• 2.31 2022-03-04
  • PdoOneCli updated to version 0.10
• 2.30 2022-02-28
  • Update PdoOneCli to version 0.9
    • The CLI is almost functional with the new engine however it requires some cleanups.

• 2.29 2022-02-20

  • Added as a binary file (vendor/bin/pdoonecli)

• 2.27 2022-02-19

  • [core] lots of cleanups.
  • [_BasePdoOneRepo] update to binary version 8. You must rebuild the repository classes to rebuild the base class.

• 2.26 2022-02-19

  • [core] [new] added more type hiting for the arguments for safety and stability of the tool.
  • [cli] now the CLI is located a different file called pdoonecli Also the CLI has more features than before, including the generation of the OOP classes.

• 2.25 2022-02-01

  • [core] [new] Key-Value functionalities:setKvDefaultTable(),kv(),createTableKV(),dropTableKV(),getKV() ,setKV(),garbageCollectorKV(),delKV(),flushKV(),existKV()
  • [core] [new] createIndex()

• 2.24.1 2022-02-06

  • [core] [fix] Now, most generation of classes are defined in templates instead of the code. It will keep the code clean while it will also save a few bits of memories (old: 6446 lines, current: 5963 lines).

• 2.24 2022-02-06

  • [repo] Now the library allows multiple connections using different repository class bases.
  • [_BasePdoOne] Updated to version 7. You must rebuild the repository classes to rebuild the base class.
  • [core] [new] Now MessageContainer is injected automatically
  • [core] [new] You can obtain an instance of PdoOne using the static method PdoOne::instance()
  • [core] [fix] Method lastError() always returns a string (empty if not error) instead of a NULL.
  • [core] [change] Method getMessages() rename to getMessageContainer()
  • [core] [new] Method getMessages() returns all the messages.
  • [core] [new] Method getErrors(),getFirstError(),getLastError(),hasError() return error messages.
  • [core] [new] Method getInfos(),getFirstInfo(),getLastInfo() return info messages.

• 2.23 2022-02-04

  • [PdoOneQuery] [PdoOne] [fix] Fixed compatibility with PHP 8.1. PHP 8.1 deprecates a lot of functionalities.
  • [new] update dependency to php>=7.2.5 to stay in sync with Composer. If you have trouble, then you can use an old version of the library

• 2.22.2 2022-02-01

  • [PdoOneQuery] [fix] when the argument of a method is empty or zero. Now, it throws an exception.

• 2.22.1 2022-01-03

  • [core] [edit] generateAllClasses() now returns errors and warnings.

• 2.22 2022-01-30

  • [core] [edit] A new static value called $pageSize
  • [PdoOneQuery] [edit] the method page() allows to specify the size of the page.
  • [_BasePdoOne] [edit] the method page() allows to specify the size of the page.

• 2.21 2022-01-28

  • [core] [fix] method singularTable() is now more exact to convert plural names to singular.
  • However, it is far from perfect.
  • [_BasePdoOne] [fixed] now several methods store the last error.

• 2.20 2022-01-04

  • [new] update dependency to php>=7.1.3. PHP 5.6 was discontinued 3 years ago.

• 2.19

  • [new] [core] callProcedure() could return a value other than true or false (SQL server only)
  • [new] [sqlsrv] implemented callProcedure() and createProcedure()

• 2.18

  • [new] [oci] added oci (oracle) as a new driver.
  • [fix] [core] dbTypeToPHP() and datesql2Text()
  • [new] [core] clearError(),removeDoubleQuotes() and a new argument for connect()

• 2.16

  • [fix] [sqlsrv] fixed the format of the date-time of sql.
  • [fix] [sqlsrv] columnTable() returns distinct values.

• 2.15 2021-07-24

• 2.14.3 2021-06-15

  • [fix] [orm] setCache()::first() and setCache()::count() didn't work correctly. fixed.
  • Query now supports factoryNull()

• 2.14.2 2021-06-13

  • [fix] [orm] useCache() and setRelation() do not chain correctly, fixed.
  • [fix] [orm] useCache() doubled the cache. fixed.

• 2.14.1 2021-06-09

  • [fix] custom_exception_handler when the error returned does not have an argument, or the argument is not an array.

• 2.14 2021-06-04

• • _BasePdoOneRepo now works more closely with the class PdoOneQuery, so each query is a different instance.

  • [fix] PdoOne dateConvertInput() does not crash when the value is not a date.

  • [fix] PdoOne throwError() does not stack errors but still triggers the last error (if any).

  • [changes] ❗ PdoOne aggregate functions (sum,min,max,avg) now returns a value instead of generating a query.

• [fix] PdoOne generateCodeArray() used for the generation of classes, returns the correct name when it is set.

• [changes] _BasePdoOneRepo: reset(),getQuery(),dropTable(),useCache(),limit(),newQuery(),order(),innerjoin(),left() ,right()

• [changes] PdoOneQuery: Multiples changes.

• 2.13.1 2021-05-22

  • [fix] [orm] the method where() and limit() generated a new query every time, so the command ClaseRepo::recursive()::where() failed generated two queries instead of one.

• 2.13 2021-04-17

  • [debug] More changes to how it shows error messages.
  • /_BasePdoOneRepo updated to version 5.0 (binary 5).

• 2.12 2021-04-17

  • [debug] Change how it shows the errors. Now it uses a custom error handle (it could be disabled with setting $this->customError=false)

• 2.11.1 2021-04-17

  • [fix] Mysql extension now knows the type int24 (plus other types of variables).
  • [fix] Regresion, some removed { } caused a bug when they are followed by []
  • [code]

• 2.11 2021-04-17

  • [code] Lots of cleanups. Removed unneeding { }. Merged common code in branches.

• 2.10.3 2021-04-14

  • [fix] BasePdoOne fixed method getRecursive(), it generated a new query, and now it reuses a query (if any).
  • It also returns the query

• 2.10.2 2021-04-06

  • Fixed

• 2.10.1 2021-04-05

  • Fixed the generation of the ORM in the use of where(), limit() and dependencies()

• 2.10 2021-04-04

  • Some CLI cleanups.

• 2.9.4 2021-03-22

  • ORM: _BasePdoOneRepo is updated to BINARYVERSION=4. If you are using the ORM, then you should rebuild all ORM classes.
  • CORE: Query chains creates a new instance of PdoOneQuery instead of an instance of PdoOne, so it is possible to define multiples chains without interfering each others.
  • ORM: Fixed a problem with the base class. Now the Base class contains the constant COMPILEDVERSION
  • ORM: Fixed a problem with the model and the method toArray()

• 2.9.3 2021-02-22

  • fix a bug with useCache() when we use with where()

• 2.9.2 2021-02-18

  • fixed a bug with createSequence()

• 2.9.1 2021-02-16

  • Fixed debugFile() when the file is unable to open.

• 2.9 2021-02-16

  • BasePdoOneRepo::getPdoOne() validates if the static class uses the right version. It is only done once (when it creates the instance of pdoOne), so it must not affect the performance.
  • You should re-generate all ORM classes generated (if you are generated one)

• 2.8 2021-02-13

  • Updated _BasePdoOneRepo to 4.13. You should re-generate all ORM classes generated (if you are generated one)

• 2.7.1 2021-01-21

  • Oracle is still a WIP (OCI)

• 2.7 2021-01-10

  • Many changes, compatibility with PHP 8.0
  • Fixed a bug with cache where it keeps the cache of previous runs.
  • Note: $PdoOne->useCache considers = false (no cache) and everything else as use cache. Nulls are not allowed.
  • Note: Previous generated code must be rebuilt it again.

• 2.6.3 2020-10-16

  • Internal, Changed beginTry() and endTry(). Now, if an operation that uses beginTry() and endTry() fails, then the error is throw in endTry().

• 2.6.2 2020-10-09

  • The default argument of _first (code generated) is PdoOne::NULL instead of null because null is a valid value

• 2.6.1 2020-09-24

  • update _BasePdoOneRepo to 4.12.1
  • Preliminary support to Oracle OCI (only raw operations)
  • _BasePdoOneRepo() insert now allows using nulls
  • _BasePdoOneRepo() first() the "where" uses the name of the table.

• 2.6 2020-09-17

  • the methods insert(),update(),delete() and insertObject() flushes the cache if it is called with useCache() , example: $this->usecache('','customers')->insert(); // delete the group cache customers.

• 2.5 2020-09-13

  • update _BasePdoOneRepo to 4.12
  • new method setCache()
  • updated method useCache(). Now it allows '*' to use the same tables assigned by from() and joins()

• 2.4.1 2020-09-13

  • The code generated now the method first() returns false if it doesn't found any value, instead of an empty array.

• 2.4 2020-09-06

  • update _BasePdoOneRepo to 4.11
  • The code generated now store the column of the identity SELF::IDENTITY;
  • _BasePdoOneRepo now converts objects to array recursively (array)$obj only converts the first level.

• 2.3 2020-09-06

  • new method getDefTableExtended() that returns extended information about the table (motor,description,collation and schema)
  • method truncate() has a new argument $forced. Now, it is possible to force truncate (for example, when the table has FK)
  • new method resetIdentity() that reset the identity/autonumeric of a table (if any)
  • Updated _BasePdoOneRepo
  • The code generated now allows setting values using the factory()
  • The code generated now reset the recursivity when we use the method factory()

• 2.2.6 2020-09-03

  • Updated _BasePdoOneRepo
  • Method validateDefTable() works with table

• 2.2.5 2020-08-30

  • Fixed a bug with the method dateConvert(). The conversion from date -> any other format misses the time.

• 2.2.3 2020-08-23

  • _BasePdoOneRepo update to 4.8.2. Solved a fix returning a ONETOMANY field.

• 2.2.2 2020-08-17

  • _BasePdoOneRepo update to 4.8.2. It solved a problem with insert,update,delete and merge when the input is an object.

• 2.2.1 2020-08-16

  • Fixed a bug when the system is unable to convert the date. Now, it returns false.
  • BasePdoOneRepo update to 4.8.1, if the transaction is open, then it doesn't nest a new transaction.

• 2.2 2020-08-14

  • New method setUseInternalCache() and flushInternalCache() where we could set an internal cache. The internal cache stores results, and they are keep into memory. For example

• The internal cache is tested with runRawQuery (if returns an array), toList(), meta() and first()

• 2.0.1 2020-08-12
  • Fixed a bug with the generated code with linked relation manytoone and onetonone.
• 2.0 2020-08-11
  • Core: The arguments are rebuild from scratch. Now, arguments are more natural, and we don't need to specify the type. It also allows many other different kind of combinations.
  • Before: $this->runRawQuery($sql,['i',20,'s','hello]);
  • Now: $this->runRawQuery($sql,[20,'hello']);
  • Also, (named): $this->runRawQuery($sql,['col1'=>20,'col2'=>'hello']);
  • Since it is a core change, then former code that uses the version 1.x could not be compatible without changing all references to methods that use arguments specifying the types.
    • runRawQuery()
    • set()
    • select()
    • where()
    • insert()
    • update()
    • delete()
    • toMeta()
• 1.55.1 2020-08-05
  • In the generation of the code, changed is_array() by isset()
• 1.55 2020-8-05
  • Updated generation of code. Now, the relations manytoony and onetoone returns linked values. Linked values are values that are linked (one of them is a pointer to the other value).
  • Fixed: the relation onetoone, the field refcol now uses the right column.
• 1.54 2020-8-02
  • connect does not set attribute directly. Mysql and sql server sets different arguments.
  • generateCodeClass has an additional argument and it generares an extra field.
  • generateAllClasses has an additional field (allows adds new fields)
  • generateAbstractCodeclass has an additional field (allows adds new fields)
  • BasePdoOneRepo updated to 4.8. Now it uses new functionalities.
    • It allows custom input and output conversions.
    • Input and output conversions code is generated at compile time instead to evaluates at runtime.
    • It allows toList() and first() to returns extra columns. Those columns are also added to the model-class
• 1.53 2020-7-27
  • Method connect() sets PDO::ATTR_EMULATE_PREPARES to false
• 1.52 2020-7-19
  • Updated method generateCodeArray()
  • Updated method generateCodeClass()
  • Updated method generateCodeClassRepo()
  • Updated method generateModelClass()
  • new method generateAbstractModelClass()
• 1.51 2020-7-18
  • class BasePdoOneRepo updated to 4.7. Now it supports arrays and objects
  • Updated method generateCodeArray()
  • Updated method generateCodeClass()
  • Updated method generateCodeClassRepo()
  • new method generateModelClass() It generates model from tables
• 1.50 2020-7-04
  • Updated method generateCodeArray()
  • Updated method generateCodeClass()
  • Updated method generateCodeClassRepo()
• 1.49 2020-6-19
  • New method generateAllClasses()
• 1.48 2020-6-15
  • dateTextNow() now returns a human-readable format
  • New method dateNow() that returns a date in a normal format.
  • BasePdoOne::count() now works with setRecursive()
• 1.47 2020-6-14
  • The flow ->where()->update() now works when "where()" uses named arguments.
  • Fixed a bug with the next query ->where('a1=:Argument',[])
  • Added the method PdoOneEncryption::hash() and added the method PdoOne::hash() to wrap the first method.
  • Added the field PdoOneEncryption:$hashType And PdoOne uses as the default hash algorythm.
  • The method RunRawQuery() now accept named arguments.
• 1.46 2020-6-13
  • _BasePdoOneRepo updated to 4.6
  • Now generation of code has a Base class, so it is possible to name each independiently of the table.
• 1.45.1 2020-6-11
  • Method generateCodeClass now creates ONETOMANY relation if the case of the table does not match.
• 1.45 2020-6-7
  • Added method generateCodeClassRepo()
  • Modified method generateCodeClass()
• 1.44.2 2020-6-3
  • Solved a problem with BasePdoOneRepo
• 1.44.1 2020-6-2
  • Solved a small bug on BasePdoOneRepo
• 1.44 2020-5-31
  • Updated BasePdoOneRepo to 4.4. Now it catches errors or returns false.
• 1.43 2020-5-31
  • Updated BasePdoOneRepo to 4.3. Now it works with conversions. It doesn't convert fields from where , but it converts results, insert and update.
  • BasePdoOneRepo 4.3 also restricts the columns to insert and update.
  • BasePdoOneRepo 4.3 also uses a long definition of columns.
• 1.42 2020-5-29
  • Updated BasePdoOneRepo to 4.2. Now it works with cache
  • New method buildUniqueID()
• 1.41.2 2020-5-29
  • Updated BasePdoOneRepo to 4.1.1 In the method _first(), if argument is null then it doesn't query the value.
• 1.41.1 2020-5-28
  • Added more phpdoc for generateCodeClass()
• 1.41 2020-5-28
  • Updated BasePdoOneRepo to 4.0.1. It allows to specify a prefix in classes.
  • generateCodeClass() allows to specificy a prefix different from 'Repo'.
• 1.40.1 2020-5-27
  • Updated BasePdoOneRepo to 4.0.1 It allows long namespaces. It also adds an alias to the first table.
• 1.40 2020-05-21
  • Updated BasePdoOneRepo 4.0
• 1.39 2020-05-12
  • Updated _BasePdoOneRepo to version 4.0
  • Solved a problem with getDefTableFK (mysql and sqlsrv)
  • new field (database_identityName)
  • Solved a problem with getPK (sqlsrv)
• 1.38 2020-05-10
  • updated _BasePdoOneRepo to version 4.0
• 1.37 2020-05-03
  • added method setNoReset()
• 1.36 2020-05-03
  • added method hasRecursive()
• 1.35.1 2020-04-30
  • autoload.php delete (it's a trash file)
• 1.35 2020-04-28
  • _BasePdoOneRepo 2.3 added relation ONETOMANY
  • generateCodeClass() supports for _BasePdoOneRepo 2.3

• 1.34.2 2020-04-27

  • Updated other components.
  • left(), right() and innerjoin() don't replace where() anymore

• 1.34.1 2020-04-27

  • _BasePdoOneRepo 2.2.1 fixed.
• 1.34 2020-04-27
  • _BasePdoOneRepo 2.2 now it allows load relation many by one.
  • sniffer is removed. Using Php Inspections ​(EA Extended)​
• 1.33 2020-04-15
  • _BasePdoOneRepo version 2.1
  • new method hasWhere()
  • generateCodeArray() has a new argument (recursive)
  • toList(), first(), firstScalar(), toMeta(), toListSimple() and all related method throws error more close to the method.
  • Travis has been removed (tests are done locally in my machine. Why? It's because travis is not fully compatible with sqlsrv)
• 1.32.1 BasePdoOneRepo added version 2.0
• 1.32 2020-04-12
  • The class generated now extends _BasePdoOneRepo
    • It implements the default commands: insert,select,update,delete,create table,truncate.
  • Security: user/password is deleted from memory after connection.
  • Mysql:createSequence() functions are now marked as "MODIFIES SQL DATA" and "NOT DETERMINISTIC"
  • Added more test, including test for SQLSRV.
• 1.31.1 2020-04-11
  • CLI: Primary key is not required anymore.
  • new method createFK();
  • Foreign keys are not created separately of other keys.
  • new method camelize()
  • new method isQuery()
  • new method filterKey()
  • new method getDefTableFK()
• 1.31 2020-04-11
  • new method tableSorted()
• 1.30 2020-04-10
  • Class separated in 3 new service class
  • lib/ext/PdoOne_Mysql.php = Service class for Mysql (mysql)
  • lib/ext/PdoOne_Sqlsrv.php = Service class for Sql Server (sqlsrv)
  • lib/ext/PdoOne_TestMockup.php = Service class for mockup test (test)
  • lib/ext/PdoOne_IExt.php = Interface for all services.
  • the /examples folder is not delivered by default. It allows to reduce the number of size. Use composer --prefer-source to get the examples
• 1.29 2020-04-10
  • createTable() now allows more features
  • new method validateDefTable()
  • a new UI render()
• 1.28.1 2020-04-06
  • cli now supports sqlsrv
• 1.28 2020-04-06
  • method toMeta() now allows arguments.
  • The library now has a cli interface and a generation of code
  • new method isCli()
  • new method cliEngine()
  • new method getParameterCli() protected
  • new method removeTrailSlash() protected
  • new method fixCsv() protected
  • new method generateCodeSelect() protected
  • new method generateCodeArray() protected
  • new method generateCodeClass() protected
• 1.24 2020-03-26
  • builderReset() is now public
• 1.23.1 2020-03-10
  • Fixed a problem with the cache
• 1.23 2020-03-10
  • method toMeta()
• 1.22 2020-02-08
  • method invalidateCache()
  • changed the interface IPdoOneCache
• 1.21 2020-02-07
  • method setCacheService() and getCacheService()
  • method useCache()
• 1.20 2020-jan-25
  • Many cleanups.
  • update() and delete() now allows to set the query.
  • new method addDelimiter() to add delimiters to the query (i.e. 'table' for mysql and [table] for sql server)
• 1.19 2020-jan-15
  • getSequence() now has a new argument (name of the sequence, optional)
  • createSequence() has a new argument (type of sequence) and it allows to create a sequential sequence.
  • objectexist() now is public, and it allows to works with functions
  • Bug fixed: objectExist() now works correctly (used by tableExist())
  • new DDL methods drop(), dropTable() and truncate()
• 1.16 2020-jan-14
  • new method toListKeyValue()
• 1.15 2019-dec-29
  • Fix small bug if the argument of isAssoc() is not an array.
• 1.14 2019-dec-26
  • method where() works with associative array
• 1.13 2019-dec-26
  • new method count()
  • new method sum()
  • new method min()
  • new method max()
  • new method avg()
  • method select now allows null definition.
  • obtainSqlFields() discontinued
• 1.12 2019-oct-20 Added argument (optional) ->toList($pdomodel) Added method ->toListSimple()
• 1.11 2019-oct-01 1.11 It is still compatible with php 5.6.Added to composer.json
• 1.10 2019-oct-01 1.10 Added method dateConvert(). Added trace to the throw.
• 1.9 2019-aug-10 1.8 republished
• 1.8 2019-aug-10 Added a date format. Methods dateSql2Text() and dateText2Sql()
• 1.7 2019-jun-23 Added some benchmark. It also solves a problem with the tags. Now: table.field=? is converted to table.field=?
• 1.6 2019-jun-22 affected_rows() returns a correct value.
• 1.5 2019-may-31 some cleanups. columnTable() returns if the column is nullable or not.
• 1.4 2019-may-30 insertobject()
• 1.3 2019-may-23 New changes
• 1.2 2019-may-22 New fixed.
• 1.1 2019-may-21 Some maintenance
• 1.0 2019-may-21 First version