Getting Started

Minimal Steps

Installation:

composer require acseo/sql-server-bundle:dev-master

Add the bundle to AppKernel.php:

new ACSEO\Bundle\SQLServerBundle\ACSEOSQLServerBundle(),

Configure Doctrine: Update parameters.yml:

driver_class: \ACSEO\Bundle\SQLServerBundle\Driver\SQLServerDriver

Override Doctrine types in config.yml:

doctrine:
    dbal:
        types:
            string: ACSEO\Bundle\SQLServerBundle\Type\StringType
            datetime: ACSEO\Bundle\SQLServerBundle\Type\DateTimeType
            text: ACSEO\Bundle\SQLServerBundle\Type\TextType

Post-Install Script: Add to composer.json:

"post-install-cmd": [
    "ACSEO\\Bundle\\SQLServerBundle\\Composer\\ScriptHandler::updateDoctrineDriverManager"
]

First Use Case: Define an entity with SQL Server-specific fields (e.g., NVARCHAR(MAX)) and let the bundle handle conversions automatically.

Implementation Patterns

Usage Patterns

Entity Mapping: Use SQL Server-specific data types in your Doctrine entities (e.g., NVARCHAR, DATETIME2). The bundle ensures seamless conversion between PHP and SQL Server types.
```
/**
 * @ORM\Column(type="string", length=255, nullable=true)
 */
private $description;
```

Query Building: Leverage SQL Server functions (e.g., GETDATE(), CONVERT) via DQL or native queries. The bundle optimizes these for SQL Server.

$query = $entityManager->createQuery('SELECT e FROM App\Entity\Post e WHERE e.createdAt > :date')
    ->setParameter('date', new \DateTime('2023-01-01'));

Migrations: Use the bundle’s type mappings when generating migrations for SQL Server-specific columns:
```
php bin/console doctrine:migrations:diff
```
Custom Types: Extend the bundle’s type classes (e.g., StringType, DateTimeType) for project-specific SQL Server data types.

Workflows

Development: Use the bundle’s type overrides to avoid manual type casting in queries or entities. Focus on writing portable Doctrine code while letting the bundle handle SQL Server quirks.
Deployment: Ensure the pdo_dblib extension is enabled in php.ini (handled by the post-install-cmd script). Test migrations and queries on SQL Server early to catch type mismatches.
Debugging: Enable Doctrine’s SQL logging to verify type conversions:
```
doctrine:
    dbal:
        logging: true
```

Integration Tips

Symfony Flex: If using Symfony Flex, manually register the bundle in config/bundles.php:

return [
    // ...
    ACSEO\Bundle\SQLServerBundle\ACSEOSQLServerBundle::class => ['all' => true],
];

Doctrine Extensions: Combine with other bundles (e.g., Stof\DoctrineExtensionsBundle) for advanced SQL Server features like JSON or GEOGRAPHY types.
CI/CD: Add a pre-deploy check to verify pdo_dblib is installed:
```
php -m | grep pdo_dblib || exit 1
```

Gotchas and Tips

Pitfalls

Missing pdo_dblib:
- Symptom: Queries fail with PDOException: could not find driver.
- Fix: Install the extension (pecl install pdo_dblib) and ensure it’s enabled in php.ini. The post-install-cmd script should handle this, but verify manually in CI/CD.

Type Mismatches:

Symptom: SQLSTATE[22007] errors for unsupported types (e.g., TIMESTAMP).

Fix: Explicitly map custom types in config.yml or extend the bundle’s type classes. Example:

// src/Doctrine/DBAL/Types/CustomDateTimeType.php
class CustomDateTimeType extends DateTimeType {
    public function convertToDatabaseValue($value, AbstractPlatform $platform) {
        return $platform->getDateTimeFormatString() === 'Y-m-d H:i:s.u' ?
            $value->format('Y-m-d H:i:s.u') :
            parent::convertToDatabaseValue($value, $platform);
    }
}

Case Sensitivity:
- SQL Server’s NVARCHAR is case-sensitive by default. Use COLLATE in migrations or queries if case-insensitive behavior is needed:
```
/**
 * @ORM\Column(type="string", options={"collation"="SQL_Latin1_General_CP1_CI_AS"})
 */
private $name;
```
Legacy Code:
- Avoid hardcoding SQL Server functions (e.g., GETDATE()) in portable code. Use Doctrine’s CURRENT_TIMESTAMP or NOW() instead.

Debugging

Enable SQL Logging:
```
doctrine:
    dbal:
        logging: true
        logging_format: '%%sql%%'
```
Check var/log/dev.log for raw SQL to verify type conversions.

Doctrine Events: Listen to onFlush to inspect entity state before queries:

$eventManager->addEventListener(ORM\Events::onFlush, function (ORM\Event\OnFlushEventArgs $args) {
    foreach ($args->getEntityManager()->getUnitOfWork()->getScheduledEntityInsertions() as $entity) {
        var_dump(get_class($entity), $entity->getDescription());
    }
});

Platform-Specific Queries: Use AbstractPlatform to check the database platform:

if ($entityManager->getConnection()->getDatabasePlatform() instanceof SQLServerPlatform) {
    // SQL Server-specific logic
}

Extension Points

Custom Type Classes: Extend ACSEO\Bundle\SQLServerBundle\Type\AbstractType to support project-specific SQL Server types. Example for UNIQUEIDENTIFIER:

class UuidType extends AbstractType {
    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform) {
        return 'UNIQUEIDENTIFIER';
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform) {
        return $value instanceof \Ramsey\Uuid\Uuid ? $value->toString() : null;
    }
}

Driver Overrides: Extend SQLServerDriver to add custom SQL Server functions or query optimizations:

class CustomSQLServerDriver extends SQLServerDriver {
    public function getListTableColumnNamesSQL($tableName) {
        return 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?';
    }
}

Configuration Overrides: Use environment variables or parameter bags to dynamically configure type mappings:

# config/packages/dev/doctrine.yml
doctrine:
    dbal:
        types:
            string: "%env(DOCTRINE_STRING_TYPE)%"

Tips

Testing: Mock SQLServerPlatform in PHPUnit to test type conversions:

$platform = $this->createMock(AbstractPlatform::class);
$platform->method('getName')->willReturn('sqlserver');
$type = new StringType();
$this->assertEquals('NVARCHAR(255)', $type->getSQLDeclaration(['length' => 255], $platform));

Performance: Prefer NVARCHAR over VARCHAR for Unicode support in SQL Server. The bundle handles conversions automatically.

Documentation: Add comments to entities to clarify SQL Server-specific behaviors:

/**
 * @ORM\Column(type="datetime")
 * @var \DateTimeInterface|null SQL Server DATETIME2 precision
 */
private $createdAt;

Community: Contribute missing type mappings or fixes to the bundle’s GitHub repo. The project is lightweight but can be extended for broader use.

Sql Server Bundle Laravel Package