Download the PHP package thomas-institut/datatable without Composer
On this page you can find all versions of the php package thomas-institut/datatable. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
Download thomas-institut/datatable
More information about thomas-institut/datatable
Files in thomas-institut/datatable
Package datatable
Short Description A simple data table interface
License MIT
Informations about the package datatable
DataTable
This package defines an interface to abstract data access and manipulation of SQL-like tables made out of rows with a unique integer id as its key and provides in-memory and MySQL implementations. The purpose is to decouple basic data functions from actual database details and to allow for faster testing by using in-memory tables.
Installation
Install the latest version with
Usage
DataTable
The main component is the DataTable
interface, which captures the basic functionality
of an SQL table with unique integer ids. Implementations deal with the underlying storage,
which can be shared among different DataTable instances. Basic id generation mechanisms are
provided as alternatives to sequential ids.
DataTable
objects also implement the ArrayAccess
, IteratorAggregate
, LoggerAwareInterface
and ErrorReporter
interfaces.
The default id generation mechanism behaves exactly like auto-increment in databases, but it is
handled without database intervention. Alternative id generators can be devised and set with
setIdGenerator
.
The MySqlDataTable
implementation provided in this package can also defer id generation to MySql's
AUTO INCREMENT functionality, and this is preferred over DataTable's default implementation.
A random id generator is provided. This generator will try to generate random Ids between two given values to up to a maximum number of attempts, after which it will default to the maximum current id plus 1:
If proper values of $min
, $max
and $maxAttempts
are chosen, it can be practically
impossible for the random id generator to go to the default.
Error Handling and Reporting
Invalid argument errors are handled by custom exceptions (see each method's documentation for details) and all other problems normally result in a RunTimeException being thrown.
The latest error can be inspected by calling the getErrorCode
and getErrorMessage
methods.
These two methods are defined in the ErrorReporter
interface.
Per the LoggerAwareInteface
, a PSR Logger can be set as well and implementations should normally
report all errors here too.
Row Creation
If an id is present in the row used for creation it will be used as the new id and
a RowAlreadyExists
exception will be thrown if that id is in use.
The default name for the id field/column is id
, if the underlying table in actual storage
uses a different name, this can be set setIdColumnName, normally right after construction:
Setting the column name in a DataTable does not change anything in the underlying database. It only tells the DataTable what the actual database id name is.
Array access can also be used to create new rows:
Any number of fields/column and types can be used in the creation row as long as this makes sense with the implementation and actual storage. It's up to the implementation to ignore extra data or throw an error.
Read / Search Rows
To check whether a row exists:
To get a particular row:
To get all rows:
All methods that may return multiple rows return an DataTableIterator
object.
This is a normal PHP iterator that can be used in a foreach
statement extended with
a count()
method that returns the number of results.
The iterator also provides a getFirst()
convenience method that returns the first
result in the set. However, there is no guarantee that a foreach
statement on the iterator
will work after getFirst()
or that getFirst()
will work after a foreach
because
rewinding might not be possible in particular implementations.
The search
method performs a general search on the DataTable based on an
array of search conditions and a search type according to the following rules:
Most often, the simpler utility method findRows
can be used for simple matching of
columns and values.
If a row matches the value for every key in $rowToMatch
, it is returned as
part of the result set. This is equivalent to do an AND search with EQUAL_TO conditions
for every key in $rowToMatch
Update Rows
In updateRow
the given row must have an id field that corresponds to a row in the table or
else a RowDoesNotExist
exception will be thrown. In the array access version, the
given $rowId
will be used regardless of whether there is an id field in the given row.
Only the fields in $row
are updated. An incomplete row may produce errors if the
underlying database schema expects values for those columns.
Delete Rows
The result is the number of columns affected, which is 0 if the row did not exist in the first place.
Transactions
DataTables provide a basic interface to underlying database transaction capabilities, if they exist.
To check if transactions are supported:
If transactions are supported, they can be started with startTransaction()
and
ended with commit()
or rollBack()
There is also a convenience method to have the DataTable check if the underlying database is in a transaction:
Extra care should be taken when working with transactions, especially if a database connection is shared among different DataTables. DataTables will not start a transaction if the underlying database reports that a transaction is currently going on (which may or may not be reliable depending on the database). Also, commits can only be executed on the DataTable that initiated the transaction.
If a DataTable does not support transactions, startTransaction()
, commit()
and
rollBack()
will always return false
.
InMemoryDataTable
A DataTable
implementation using simple PHP arrays, no storage. This makes
it possible to perform tests on data tables without having to set up
a database.
MySqlDataTable
A DataTable
implementation using a MySQL table.
MySqlDataTable
assumes that there is a table setup with at least
an integer id
column with the given name ($idColumnName
, which defaults to 'id').
If $useAutoInc
is true, MySqlDataTable
assumes that the id
column has
the AUTO_INCREMENT
attribute and will create rows so that MySQL will take care
of generating IDs. Otherwise, MySqlDataTable
itself takes care of generating
incremental IDs.
For compatibility with previous versions of this library, $useAutoInc
defaults to false.
However, it is recommended that you use MySQL auto-increment functionality. In a
scenario where there are multiple calls to createRow
concurrently, DataTable
's
internal ID generator may fail to generate a unique id.
The table in MySQL can have any number of extra columns of any type. As long
as calls to createRow
and updateRow
agree with columns names and types, everything
should work fine. You can also have default values defined in MySQL and leave
those out when calling createRow
.
MySqlDataTableWithRandomIds
The same as MySqlDatable
but using the randomId generator.
MySqlUnitemporalDataTable
A MySQL table with time-tagged rows. Every row not only has a unique id, but
also a valid_from and a valid_until time. When using the normal DataTable
methods
MySQLUnitemporalDataTable
behaves exactly the same as MySqlDataTable, but
it does not delete any rows, it just makes them invalid.
There is a set of time methods to create, read, update and delete previous versions of the data. For example:
$timeString
is a string formatted as a valid MySQL datetime with microseconds,
e.g. '2018-01-01 12:00:00.123123'
Use the static methods in the TimeString
class to generate such strings from MySQL date and datetime strings, and from UNIX
timestamps with or without microseconds.
The underlying MySQL table must have two datetime fields: valid_from
and
valid_until
The user is responsible for setting the PDO connection with the timezone that is going to be used in all queries using time parameters.
All versions of datatable with dependencies
ext-pdo Version *
psr/log Version ^3
thomas-institut/timestring Version >=1.1