Doctrine Database Abstraction Layer

Related tags

Database dbal
Overview
Comments
  • Testing of MSSQL on Windows with AppVeyor

    Testing of MSSQL on Windows with AppVeyor

    This is an initial draft [POC] for testing of MSSQL on windows with AppVeyor

    This PR creates the initial draft configuration files for AppVeyor CI testing, adjustments may be needed to meet project configuration needs.

    For more information see issue #2616

    New Feature CI SQL Server 
    opened by photodude 161
  • MariaDB 10.2 initial support

    MariaDB 10.2 initial support

    Initial support for MariaDB 10.2.7+.

    Doctrine currently does not support MariaDB 10.2.7+. Schema creation/update is triggering infinite schema diffs

    Updated: Title updated from 'MariaDB 10.2 support (will also fix version detection), version detection should be considered as new feature.

    Features

    • [x] 1. MariaDb version detection (strip '5.5.5-' prefix)

    To circumvent a limitation in the replication protocol, MariaDB 10+ use a prefixed version with '5.5.5-' (for instance 5.5.5-Mariadb-10.0.8-xenial). This cause issues with the current detection mechanism with both Mysqli and PDOMysql dbal implementations (always detect 5.5.5). While mariadb strips the prefix automatically in their libmariadb-client, most (if not all) of php installations relies on mysqlnd, so the removal of the prefix 5.5.5- must be made in userland (MysqliConnection.php and AbstractMySQLDriver.php). See https://jira.mariadb.org/browse/MDEV-4088 for reference.

    • [x] ~2. MariaDb 10.2 support BLOB/TEXT defaults values (since 10.2.1).~

    Update: Removed from this P/R... will be made available in a subsequent P/R (due to architecture decision to be made - current doctrine implementation needs to be reworked)

    Note that Mysql (as of 5.7.19) is not yet supporting defaults for BLOB and TEXT.
    See https://mariadb.com/kb/en/library/blob-and-text-data-types

    • [x] UPDATED ~3. MariaDb 10.2 support JSON type.~

    Removed from P/R by @lcobucci, probably to reflect the fact that MariaDB alias JSON to MEDIUMTEXT

    ~The JSON type have been introduced in 10.2.7 as an alias to LONGTEXT. See https://mariadb.com/kb/en/library/json-data-type/ To ensure portability between MySQL/Mariadb, the best is to prefer TEXT type over JSON when defining schema (till a real JSON type appears in Maria).~

    • [x] 4. MariaDb 10.2 supports CURRENT_TIME, CURRENT_DATE as default values

    Note that MySQL (as of 5.7.19) does not support default values CURRENT_TIME and CURRENT_DATE as default values for TIME and DATE columns (CURRENT_TIMESTAMP for DATETIME is supported).

    • [x] ~5. Default literal values must be escaped.~

    Update: Removed from this PR in favor of #2850... Current doctrine implementation does not handle escaping of defaults (backslashes...), adding it in this P/R should be done for all platforms at once and thus introduce more BC-risks (bugs...). Implementing separately looks safer and should help having this P/R sooner. Both P/R can be done independently.

    ~While table comments are automatically quoted (MySQLPlatform::quoteStringLiteral()) the default column values were not. Defaults values for string literals are now properly quoted.~

    • [x] 6. MariaDB 10.2.7 information changes schema for default values.

    In order to allow expressions as default values and distinguish them from literals, Mariadb now quotes default values in information_schema.column table. This changes brings a lot of incompatibilities between the (Oracle-)Mysql/MariaDB platforms.

    Instead of creating a specific SchemaManager for MariaDB, the solution taken in this P/R is to map the introduced changes in the current MySQLSchemaManager (method: getMariaDb1027ColumnDefault()).

    From MariaDB 10.2.7, information schema changes includes:

    • NULL is now quoted as 'NULL' (see exceptions in https://jira.mariadb.org/browse/MDEV-14053. Unquoted null is technically possible)
    • String defaults are quoted (To save the string 'NULL', set the default as ''NULL'').
    • CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME defaults are silently changed to 'current_timestamp()', 'currdate()', 'currtime()'. To prevent schema diff, they are mapped back to their original values.
    • (UPDATED) Partial support for default literal escaping have been done. Currently limited on schema introspection. #2850 will bring the support for backslashes in defaults later on but the code is ready.

    See https://mariadb.com/kb/en/library/information-schema-columns-table/ for reference and https://jira.mariadb.org/browse/MDEV-13132, https://jira.mariadb.org/browse/MDEV-13341 for history.

    Will fix:

    • https://github.com/doctrine/dbal/issues/2817 (invalid detection of column default values)
    • https://github.com/doctrine/doctrine2/issues/6565 (schema update will alter all tables with columns_default='null')

    • [x] 7. Support for MariaDB 10.2 reserved keywords.

    • [x] 8. Added MariaDB 10.2 in travis (both pdo_mysql and mysqli)

    Possible BC-breaks:

    None found. Since the extraction of defaults escaping in #2850, there should be no impact on other platforms.

    Portability concerns

    • Portability of CURRENT_TIME and CURRENT_DATE, mariadb 10.2 supports them, but no way to get back to mysql.

    Issues that will be fixed by this P/R:

    DBAL related:

    • https://github.com/doctrine/dbal/issues/2817 (invalid detection of column default values)

    ORM related:

    • https://github.com/doctrine/doctrine2/issues/6565 (schema update will alter all tables with columns_default='null')
    • https://github.com/doctrine/doctrine2/issues/6790 (Doctrine schema update command shows always few queries to be executed)

    Fix (or might fix) the following issues :

    • https://github.com/nextcloud/server/issues/6193
    • https://github.com/owncloud/core/issues/28695
    • https://github.com/doctrine/DoctrineBundle/issues/259
    • https://github.com/snipe/snipe-it/issues/3947
    • belgattitude/openstore-schema-core#1
    • https://github.com/flarum/core/issues/1211
    • https://github.com/doctrine/DoctrineBundle/issues/702
    • https://github.com/laravel/framework/issues/22050

    Other related open P/R

    • https://github.com/doctrine/dbal/pull/2850 (Escaping of default values)
    New Feature MariaDB Platforms Platform Detection Compatibility Reserved Keywords 
    opened by belgattitude 108
  • Introduce a Transaction object

    Introduce a Transaction object

    This is a proposal for a Transaction object, as suggested by @guilhermeblanco and @beberlei in doctrine/doctrine2#949; this is a foundation for the proposed ORM feature. This supersedes #571.

    This PR makes the following changes to Connection:

    • Adds the following method:
      • getTransactionManager() : returns the TransactionManager
    • Deprecates the following methods, in favour of their counterparts on Transaction:
      • commit()
      • rollBack()
      • setRollbackOnly()
      • isRollbackOnly()
    • Deprecates the following methods, in favour of their counterparts on TransactionManager:
      • beginTransaction()
      • isTransactionActive()
      • getTransactionNestingLevel()
      • setNestTransactionsWithSavepoints()
      • getNestTransactionsWithSavepoints()

    The new way of dealing with transactions is now:

    $transactionManager = $connection->getTransactionManager();
$transaction = $transactionManager->beginTransaction();
// ...
$transaction->commit();

    A fluent API is available to configure a transaction:

    $transaction = $transactionManager->createTransaction()
    ->withIsolationLevel(Connection::TRANSACTION_SERIALIZABLE)
    ->begin();

    Calls to commit() and rollback() are automatically propagated to nested transactions:

    $transaction1 = $transactionManager->beginTransaction();
$transaction2 = $transactionManager->beginTransaction();
$transaction1->commit(); // will commit $transaction2 then $transaction1

    Overall, it's not a complicated change, does not introduce any BC break, and passes all existing tests.

    New Feature 
    opened by BenMorel 99
  • Support for backslashes in default values

    Support for backslashes in default values

    It is now possible to use \, or other escaped characters, in default values for a column.

    Previously this lead to a confusion when diffing actual and expected schema leading to perpetual out of sync schema.

    Bug MySQL Schema Management 
    opened by PowerKiKi 54
  • Add native JSON type support for MySQL >=5.7.8

    Add native JSON type support for MySQL >=5.7.8

    As of MySQL 5.7.8, MySQL supports a native JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column:

    http://dev.mysql.com/doc/refman/5.7/en/json.html

    Improvement MySQL Platforms Type Mapping Columns 
    opened by ismailbaskin 54
  • [DBAL-831] Fix explicit case sensitive identifiers in Oracle

    [DBAL-831] Fix explicit case sensitive identifiers in Oracle

    This is a patch for https://github.com/doctrine/dbal/pull/540.

    It includes the following bugfixes:

    • Makes normalization of internally stored assets names consistent in Doctrine\DBAL\Schema\Table
    • Fixes SQL generation for autoincrement columns with explicitly quoted table/column names in Oracle.
    • Oracle identifiers that have been stored with explicit quotation are now correctly reverse-engineered by explicitly quoting them again in the schema manager (to preserve case). This only applies to identifiers that are not all uppercase (which indicated that they have been stored with a non-standard case).
    opened by deeky666 50
  • Migrate jobs away from Travis to Github Actions

    Migrate jobs away from Travis to Github Actions

    Todo:

    • [x] mssql
    • [x] sqlite
    • [x] ibm_db2 (this one looks really nasty)
    • [x] setup dependency on sqlite job
    • [x] decide what to do about PostgreSQL 9.2 (I can't get it to work)
    • [x] add some 7.3 jobs for MariaDB / PG ?
    • [x] remove scripts from tests/travis?
    • [x] find replacement for cron that runs with dev dependencies
    • [x] make relevant jobs required
    • [x] make sure code coverage hasn't decreased
    • [x] test only the latest version of a platform when not using the latest version of PHP
    CI 
    opened by greg0ire 49
  • Fix identifier escaping when used as string literal for T-SQL

    Fix identifier escaping when used as string literal for T-SQL

    | Q | A |------------- | ----------- | Type | bug | BC Break | no | Fixed issues | #4283

    Summary

    Alternative to closes https://github.com/doctrine/dbal/pull/4284 to fix it correctly and with proper functional tests.

    The issue was that MSSQL expects string literal (quoted by ') for EXEC sp_addextendedproperty instead of identifier name (quoted by []).

    SQL Server Quoting 
    opened by mvorisek 44
  • Identify retryable transaction errors and cause them to raise specific exception types

    Identify retryable transaction errors and cause them to raise specific exception types

    So this PR now only contains the explicit exceptions so they can be catched RDMBS agnostic.

    Below description can be provided externally.

    It is best practice to implement retry logic for transactions that are aborted because of deadlocks or timeouts. This makes such method available inside the DBAL and also adds detection for errors where retrying makes sense in the different database drivers.

    Deadlocks and timeouts are caused by lock contention and you often can design your application to reduce the likeliness that such an error occurs. But it's impossible to guarantee that such error conditions will never occur. This is why implementing retrying logic for such errors is actually a must when you have to ensure the application does not fail in edge cases or high load. Some references where something similar has already been discussed and implemented:

    I chose the name retryable because it is consistent with transactional. I think the implementation is quite straight forward and fits very well with the DBAL design.

    In our case we had seldomly errors like

    • Doctrine\\DBAL\\Exception\\DriverException: An exception occurred while executing 'UPDATE product SET modified = ? WHERE id = ?' with params [\"2014-10-15 16:28:55\", \"460315800000\"]:\n\nSQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
    • Doctrine\\DBAL\\Exception\\DriverException: An exception occurred while executing 'INSERT INTO ... VALUES (...)' with params [\"...\", \"...\"]:\n\nSQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

    As you can see even the exception message suggests to retry the transaction. This is now easily possible with

    $retryWrapper = new RetryWrapper(function () use ($con) {
    return $con->update('tablename', array('field' => 'data'), array('id' => 'myid'));
});

$affectedRows = $retryWrapper();
    New Feature Drivers Error Handling Locking Transactions 
    opened by Tobion 43
  • Fix that MysqliStatement cannot handle streams

    Fix that MysqliStatement cannot handle streams

    | Q | A |------------- | ----------- | Type | bug | BC Break | no | Fixed issues |

    The binary and blob types map BLOB/BINARY/VARBINARY columns to PHP streams. Internally, they use the ParameterType::LARGE_OBJECT (i. e. \PDO::PARAM_LOB) binding type, which suggests that efficient handling of PHP stream resources was intended.

    However, at least when using the mysqli driver, stream resources passed into insert() or update() are simply cast to strings. As a result, a literal string like "Resource id #126" will end up in the database.

    This PR fixes the issue by correctly processing streams in the MysqliStatement when they are passed with the ParameterType::LARGE_OBJECT binding type. It uses the mysqli::send_long_data() method to pass stream data in chunks to the MySQL server, thus keeping the memory footprint low. This method does not (despite claims to the contrary) allow to bypass the max_allowed_package size.

    Update: The pdo_mysql driver was already capable of handling streams this way. Now this is covered by tests.

    Helpful documentation

    • http://php.net/manual/en/mysqli-stmt.send-long-data.php
    • http://php.net/manual/en/mysqli-stmt.bind-param.php - see first "Note"
    • http://php.net/manual/en/pdo.lobs.php
    • https://blogs.oracle.com/oswald/phps-mysqli-extension:-storing-and-retrieving-blobs
    Improvement MySQL mysqli QueryBuilder Prepared Statements 
    opened by mpdude 41
  • Php Inspections (EA Ultimate): minor code tweaks

    Php Inspections (EA Ultimate): minor code tweaks

    | Q | A |------------- | ----------- | Type | improvement | BC Break | no | Fixed issues | n/a

    Minor code tweaks by Php Inspections (EA Ultimate)

    Improvement 
    opened by kalessil 41
  • Use narrower return types for convertTo*Value methods

    Use narrower return types for convertTo*Value methods

    | Q | A |------------- | ----------- | Type | improvement | Fixed issues |

    Summary

    Narrows down the mixed return type from Type in extending classes.

    opened by MidnightDesign 1
  • More meticulous analysis in PDO and PDOStatement

    More meticulous analysis in PDO and PDOStatement

    | Q | A |------------- | ----------- | Type | improvement

    Summary

    I submitted phpstan/phpstan-src#2036 to phpstan-src to make PDO, PDOException and PDOStatement return more detailed results. phpstan does integration tests for some repositories, including doctrin/dbal, and this PR is to fix the errors with dbal in the integration tests.

    Already passed testing in local ✅

    Static Analysis 
    opened by kang8 1
  • DBAL always generates ALTER TABLE for MySQL TEXT columns

    DBAL always generates ALTER TABLE for MySQL TEXT columns

    Bug Report

    | Q | A |------------ | ------ | Version | 3.5.1

    Summary

    DBAL always generates ALTER TABLE for MySQL TEXT columns, even if there's no change. This behavior has been introduced with 3.4.0, before no statement was generated.

    Current behaviour

    ALTER TABLE mshop_media CHANGE preview preview TEXT CHARACTER SET utf8mb4 NOT NULL COLLATE `utf8mb4_general_ci`

    How to reproduce

    Create migration that creates a table with TEXT column like this:

    CREATE TABLE `mshop_media` (
  `preview` text COLLATE utf8mb4_general_ci NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

    Then, run migration again and an ALTER TABLE statement wiil be created.

    Expected behaviour

    No ALTER TABLE statement generated if the current text column is already up to date.

    Bug Requires Feedback 
    opened by aimeos 1
  • Wrong statement generated to change from TEXT to JSON column in MySQL

    Wrong statement generated to change from TEXT to JSON column in MySQL

    Bug Report

    | Q | A |------------ | ------ | Version | 3.5.1

    Summary

    When migrating from a TEXT column with a serialized JSON content to a JSON column type, Doctrine DBAL generates an ALTER TABLE statement which contains CHARACTER SET and COLLATION not accepted by MySQL for JSON column types.

    Current behaviour

    Generated statement:

    ALTER TABLE mshop_media CHANGE preview preview JSON CHARACTER SET utf8mb4 NOT NULL COLLATE `utf8mb4_general_ci`

    How to reproduce

    Create a table with a TEXT column:

    CREATE TABLE `mshop_media` (
  `preview` text COLLATE utf8mb4_general_ci NOT NULL DEFAULT '{}',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

    Then, try to change preview column to a JSON column type using DBAL API.

    Expected behaviour

    Generated statement should be:

    ALTER TABLE mshop_media CHANGE preview preview JSON
    Bug Requires Feedback 
    opened by aimeos 1
  • [Feature request] Running parallel/async doctrine queries

    [Feature request] Running parallel/async doctrine queries

    Feature Request

    | Q | A |------------ | ------ | New Feature | yes | RFC | yes

    Summary

    First of all, thanks to all maintainers of Doctrine. You make the world better. 👏

    It would be neat if doctrine can run multiple queries without waiting for each to finish after executing the next one. Currently I have a use case for a search page, which queries the database several times and the total time of all queries is relatively big, but each query by itself takes no more than 300ms. Getting around this is no trivial task. Only if I could run these queries in parallel. It seems that PHP 8.1 supports something like this with Fibers.

    It there currently any limitation to implement parallelism in Doctrine? I'm sure that for most people this is must have feature and it would be very beneficial for the whole community.

    New Feature Requires Feedback 
    opened by nacholibre 1
Releases(3.5.2)
Owner
Doctrine
The Doctrine Project is the home to several PHP libraries primarily focused on database storage and object mapping.
Doctrine
GitHub https://www.doctrine-project.org/projects/dbal.html
:gem: Simple MySQLi Abstraction Layer + Doctrine/DBAL support

?? Simple MySQLi Class This is a simple MySQL Abstraction Layer compatible with PHP 7+ that provides a simple and secure interaction with your databas

Lars Moelleken 40 Sep 5, 2022
Database Abstraction Layer, Schema Introspection, Schema Generation, Query Builders

Cycle DBAL Secure, multiple SQL dialects (MySQL, PostgreSQL, SQLite, SQLServer), schema introspection, schema declaration, smart identifier wrappers,

Cycle ORM 30 Oct 18, 2022
Orm is a simple database abstraction layer that supports postgresql.

Orm What is it Orm is a simple database abstraction layer that supports postgresql. Welcome to join us or star us for encouragement. Requires php 8.1

null 2 Sep 28, 2022
ORM layer that creates models, config and database on the fly

RedBeanPHP 5 RedBeanPHP is an easy to use ORM tool for PHP. Automatically creates tables and columns as you go No configuration, just fire and forget

Gabor de Mooij 2.2k Jan 9, 2023
Laravel 5 - Repositories to abstract the database layer

Laravel 5 Repositories Laravel 5 Repositories is used to abstract the data layer, making our application more flexible to maintain. See versions: 1.0.

Anderson Andrade 4k Jan 6, 2023
A Redis based, fully automated and scalable database cache layer for Laravel

Lada Cache A Redis based, fully automated and scalable database cache layer for Laravel Contributors wanted! Have a look at the open issues and send m

Matt 501 Dec 30, 2022
Doctrine Object Relational Mapper (ORM)

3.0.x 2.9.x 2.8.x Doctrine 2 is an object-relational mapper (ORM) for PHP 7.1+ that provides transparent persistence for PHP objects. It sits on top o

Doctrine 9.5k Jan 2, 2023
A drop-in Doctrine ORM 2 implementation for Laravel 5+ and Lumen

Laravel Doctrine ORM A drop-in Doctrine ORM 2 implementation for Laravel 5+ $scientist = new Scientist( 'Albert', 'Einstein' ); $scientist->a

Laravel Doctrine 777 Dec 17, 2022
🔌 A Doctrine DBAL Driver implementation on top of Swoole Coroutine PostgreSQL extension

Swoole Coroutine PostgreSQL Doctrine DBAL Driver A Doctrine\DBAL\Driver implementation on top of Swoole\Coroutine\PostgreSQL. Getting started Install

Leo Cavalcante 19 Nov 25, 2022
Psalm Stubs for doctrine/mongodb-odm library

doctrine-mongodb-psalm-plugin A Doctrine plugin for Psalm (requires Psalm v4). Installation: $ composer require --dev runtothefather/doctrine-mongodb-

Evgeny 6 Jun 15, 2022
Provides integration for Doctrine with various Symfony components.

Doctrine Bridge The Doctrine bridge provides integration for Doctrine with various Symfony components. Resources Contributing Report issues and send P

Symfony 3k Dec 23, 2022
Doctrine PHP mapping driver

WORK IN PROGRESS! Doctrine PHP mapping driver Alternative mapping driver that allows to write mappings in PHP. Documentation Associations examples TOD

Andrey Klimenko 3 Aug 15, 2021
Doctrine extension to persist spatial data objects.

doctrine-Spatial Doctrine-spatial is a doctrine extension. It implements spatial types and functions. As exemple, this extension can help you to know

LongitudeOne 36 Jan 7, 2023
Monadic Doctrine repositories helper classes and services.

Doctrine Repository Monadic Helper Description This project provides the necessary classes and services to use Doctrine repositories in a more functio

(infinite) loophp 10 Aug 29, 2022
PostgreSQL enhancements for Doctrine

PostgreSQL enhancements for Doctrine. Provides support for advanced data types (json, jssnb, arrays), text search, array operators and jsonb specific functions.

Martin Georgiev 258 Dec 31, 2022
Pure PHP NoSQL database with no dependency. Flat file, JSON based document database.

Please give it a Star if you like the project ?? ❤️ SleekDB - A NoSQL Database made using PHP Full documentation: https://sleekdb.github.io/ SleekDB i

Kazi Mehedi Hasan 745 Jan 7, 2023
SleekwareDB is a NoSQL database storage service. A database storage service that can be used for various platforms and is easy to integrate.

SleekwareDB is a NoSQL database storage service. A database storage service that can be used for various platforms and is easy to integrate. NoSQL API

SleekwareDB 12 Dec 11, 2022
The lightweight PHP database framework to accelerate development

The lightweight PHP database framework to accelerate development Features Lightweight - Less than 100 KB, portable with only one file Easy - Extremely

Angel Lai 4.6k Dec 28, 2022
[READ ONLY] Subtree split of the Illuminate Database component (see laravel/framework)

Illuminate Database The Illuminate Database component is a full database toolkit for PHP, providing an expressive query builder, ActiveRecord style OR

The Laravel Components 2.5k Dec 27, 2022