Select Git revision
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
SchemaService.php 14.69 KiB
<?php
namespace UnicaenOracle\Service;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\DBALException;
use UnicaenApp\Exception\RuntimeException;
/**
*
*
* @author Unicaen
*/
class SchemaService
{
/**
* @param Connection $conn
* @return string
*/
public function extractSchemaNameFromConnection(Connection $conn)
{
$connParams = $conn->getParams();
$candidateKeys = ['CURRENT_SCHEMA', 'user'];
foreach ($candidateKeys as $candidateKey) {
if (isset($connParams[$candidateKey])) {
return strtoupper($connParams[$candidateKey]);
}
}
throw new RuntimeException(
"L'une au moins des clés suivantes doit exister dans les paramètres de la connexion: " .
implode(', ', $candidateKeys)
);
}
/**
* @param Connection $connection
* @param string $outputFilePath
*/
public function createSchemaClearingScriptFile(Connection $connection, $outputFilePath)
{
try {
$stmt = $connection->executeQuery($this->generateSQLForSchemaClearing($connection));
$stmt->execute();
} catch (DBALException $e) {
throw new RuntimeException("Erreur!", null, $e);
}
$result = $stmt->fetchAll(\PDO::FETCH_COLUMN);
// NB: si le résultat est vide, c'est que le schéma est lui-même vide,
// ou alors c'est un problème de droit du USER Oracle utilisé pour exécuter les commandes.
if (count($result) === 0) {
$result[] = "-- Noop! Schema is empty?";
}
$sql = implode('', $result);
file_put_contents($outputFilePath, $sql);
}
/**
* @param Connection $srcConn
* @param Connection $dstConn
* @param bool $refConstraintsIncluded
* @param string $outputFilePath
*/
public function createSchemaCreationScriptFile(Connection $srcConn, Connection $dstConn, $refConstraintsIncluded, $outputFilePath)
{
$srcSchemaName = $this->extractSchemaNameFromConnection($srcConn);
$dstSchemaName = $this->extractSchemaNameFromConnection($dstConn);
// config DBMS Metadata
try {
$sql = $this->generateSQLForDBMSMetadataPackageConfiguration();
$stmt = $srcConn->executeQuery($sql);
$stmt->execute();
} catch (DBALException $e) {
throw new RuntimeException("Erreur!", null, $e);
}
// génération DDL
try {
$sql = $this->generateSQLForSchemaCreation($srcConn, $refConstraintsIncluded);
$stmt = $srcConn->prepare($sql);
$stmt->execute();
} catch (DBALException $e) {
throw new RuntimeException("Erreur!", null, $e);
}
$result = $stmt->fetchAll(\PDO::FETCH_COLUMN);
// NB: si le résultat est vide, c'est sans doute un problème de droit du USER Oracle utilisé pour exécuter les commandes.
if (count($result) === 0) {
$result[] = "-- Noop!";
}
$sql = implode('', $result);
$sql = str_replace("\"$srcSchemaName\"", "\"$dstSchemaName\"", $sql);
$sql = 'SET SQLBLANKLINES ON;' . PHP_EOL .
PHP_EOL .
$sql;
file_put_contents($outputFilePath, $sql);
}
/**
* @param Connection $srcConn
* @param Connection $dstConn
* @param string $outputFilePath
*/
public function createRefConstraintsCreationScriptFile(Connection $srcConn, Connection $dstConn, $outputFilePath)
{
$srcSchemaName = $this->extractSchemaNameFromConnection($srcConn);
$dstSchemaName = $this->extractSchemaNameFromConnection($dstConn);
// config DBMS Metadata
try {
$sql = $this->generateSQLForDBMSMetadataPackageConfiguration();
$stmt = $srcConn->executeQuery($sql);
$stmt->execute();
} catch (DBALException $e) {
throw new RuntimeException("Erreur!", null, $e);
}
// génération des contraintes de référence
try {
$sql = $this->generateSQLForRefConstraintsCreation($srcConn);
$stmt = $srcConn->prepare($sql);
$stmt->execute();
} catch (DBALException $e) {
throw new RuntimeException("Erreur!", null, $e);
}
$result = $stmt->fetchAll(\PDO::FETCH_COLUMN);
// NB: si le résultat est vide, c'est sans doute un problème de droit du USER Oracle utilisé pour exécuter les commandes.
if (count($result) === 0) {
$result[] = "-- Noop!";
}
$sql = implode('', $result);
$sql = str_replace("\"$srcSchemaName\"", "\"$dstSchemaName\"", $sql);
file_put_contents($outputFilePath, $sql); }
/**
* @param Connection $connection
* @return string
*/
function generateSQLForSchemaClearing(Connection $connection)
{
$schemaName = $this->extractSchemaNameFromConnection($connection);
return <<<EOS
WITH TMP(CATEG, NAME, SQL) AS (
--
-- triggers
--
SELECT 'A_TRIGGER', trigger_NAME, 'DROP TRIGGER ' || owner || '.' || trigger_NAME || ';' || chr(10)
FROM ALL_TRIGGERS
WHERE owner = '$schemaName'
UNION ALL
--
-- views
--
SELECT 'B_VIEW', view_name, 'DROP VIEW ' || owner || '.' || view_name || ';' || chr(10)
FROM all_views
WHERE owner = '$schemaName'
UNION ALL
--
-- materialized views
--
SELECT 'C_MATERIALIZED VIEW', mview_name, 'DROP MATERIALIZED VIEW ' || owner || '.' || mview_name || ';' || chr(10)
FROM all_mviews
WHERE owner = '$schemaName'
UNION ALL
--
-- procedures and functions
--
SELECT 'D_PROCEDURE', OBJECT_NAME, 'DROP PROCEDURE ' || owner || '.' || OBJECT_NAME || ';' || chr(10)
FROM SYS.ALL_OBJECTS
WHERE owner = '$schemaName'
and UPPER(OBJECT_TYPE) = 'PROCEDURE'
UNION ALL
SELECT 'E_FUNCTION', OBJECT_NAME, 'DROP FUNCTION ' || owner || '.' || OBJECT_NAME || ';' || chr(10)
FROM SYS.ALL_OBJECTS
WHERE owner = '$schemaName'
and UPPER(OBJECT_TYPE) = 'FUNCTION'
UNION ALL
--
-- packages
--
SELECT 'F_PACKAGE', OBJECT_NAME, 'DROP PACKAGE ' || owner || '.' || OBJECT_NAME || ';' || chr(10)
FROM SYS.ALL_OBJECTS
WHERE owner = '$schemaName' and UPPER(OBJECT_TYPE) = 'PACKAGE'
UNION ALL
--
-- sequences
--
SELECT 'G_SEQUENCE', sequence_name, 'DROP SEQUENCE ' || sequence_owner || '.' || sequence_name || ';' || chr(10)
FROM all_sequences
where sequence_owner = '$schemaName'
UNION ALL
--
-- indexes : supprimés avec les constraints ci-dessous
--
-- SELECT 'H_INDEX', index_name, 'DROP INDEX ' || owner || '.' || index_name || ';' || chr(10)
-- FROM all_indexes
-- WHERE owner = '$schemaName'
--
-- UNION ALL
--
-- constraints
--
-- les FK en premier
select 'I_CONSTRAINT', constraint_name, 'ALTER TABLE ' || OWNER || '.' || table_name || ' drop CONSTRAINT ' || constraint_name || ';' || chr(10)
from all_constraints
where constraint_type = 'R'
and owner = '$schemaName'
union all
-- ensuite les PK
select 'J_CONSTRAINT', constraint_name, 'ALTER TABLE ' || OWNER || '.' || table_name || ' drop CONSTRAINT ' || constraint_name || ';' || chr(10)
from all_constraints
where constraint_type = 'P'
and owner = '$schemaName'
UNION ALL
--
-- tables
--
SELECT 'K_TABLE', table_name, 'DROP TABLE ' || owner || '.' || table_name || ' PURGE;' || chr(10)
FROM all_tables
WHERE owner = '$schemaName' and
table_name not in (select mview_name from all_mviews) -- exclusion des tables liées à des vues matérialisées
-- UNION ALL
-- select 'PURGE RECYCLEBIN;'
-- FROM all_tables
-- WHERE owner = '$schemaName'
-- and rownum = 1
)
SELECT SQL
FROM TMP
ORDER BY CATEG, NAME
EOS;
}
/**
* @return string
*/ function generateSQLForDBMSMetadataPackageConfiguration()
{
return <<<EOS
--
-- DBMS_METADATA config
--
begin
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true); -- formatting the output with indentation and line feeds ?
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true); -- appending a SQL terminator (; or /) to each DDL statement ?
--
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'EMIT_SCHEMA', false); -- remove schema (undocumented)
DBMS_METADATA.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', false); --
DBMS_METADATA.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', true); -- including non-referential table constraints in the CREATE TABLE statement ?
DBMS_METADATA.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS', false); -- including referential constraints (foreign keys) in the CREATE TABLE statement ?
DBMS_METADATA.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'FORCE', true); -- using the FORCE keyword in the CREATE VIEW statement ?
DBMS_METADATA.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', false); -- including tablespace clauses in the DDL ?
DBMS_METADATA.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_CREATION', false); -- remove segement creation (undocumented)
DBMS_METADATA.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false); -- including segment attributes clauses (physical attributes, storage attributes, tablespace, logging) in the DDL ?
DBMS_METADATA.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false); -- including storage clauses in the DDL ?
end;
EOS;
}
/**
* @param Connection $connection
* @param bool $refConstraintsIncluded
* @return string
*/
function generateSQLForSchemaCreation(Connection $connection, $refConstraintsIncluded = true)
{
$schemaName = $this->extractSchemaNameFromConnection($connection);
$refConstraintsSql = NULL;
if ($refConstraintsIncluded) {
$refConstraintsSql = $this->generateSQLForRefConstraintsCreation($connection, true);
$refConstraintsSql = <<<EOS
UNION ALL
$refConstraintsSql
EOS;
}
$sql = <<<EOS
SELECT sql from (
--
-- sequences
--
SELECT 'A_'||sequence_name as ord, DBMS_METADATA.get_ddl ('SEQUENCE', sequence_name, sequence_owner) as sql
FROM all_sequences
where sequence_owner = '$schemaName'
UNION ALL
--
-- tables
--
SELECT 'B_'||table_name as ord, DBMS_METADATA.get_ddl ('TABLE', table_name, owner) as sql
FROM all_tables
WHERE owner = '$schemaName' and
table_name not in (select mview_name from all_mviews) -- exclusion des tables liées à des vues matérialisées
UNION ALL
--
-- constraints : unique
--
SELECT 'C_'||constraint_name as ord, DBMS_METADATA.get_ddl ('CONSTRAINT', constraint_name, owner) as sql
from user_constraints
WHERE constraint_type in ('U') and -- unique type
index_name is null and -- écarte les contraintes liées automatiquement à un index car créées avec la table
table_name not in (select mview_name from all_mviews) -- exclusion des tables liées à des vues matérialisées
and owner = '$schemaName'
UNION ALL
--
-- indexes
--
SELECT 'D_'||index_name as ord, DBMS_METADATA.get_ddl ('INDEX', index_name, owner) as sql
FROM all_indexes i
WHERE not exists (select * from user_constraints where index_name = i.index_name) -- écarte les index créés automatiquement car liés à une contrainte
and owner = '$schemaName'
-- select * from all_indexes WHERE owner = '$schemaName';
UNION ALL
--
-- views
--
SELECT 'E_'||view_name as ord, DBMS_METADATA.get_ddl ('VIEW', view_name, owner) as sql
FROM all_views
WHERE owner = '$schemaName'
UNION ALL
--
-- packages specs
--
SELECT 'F_'||OBJECT_NAME as ord, DBMS_METADATA.get_ddl ('PACKAGE_SPEC', OBJECT_NAME, owner) as sql
FROM SYS.ALL_OBJECTS
WHERE owner = '$schemaName'
and UPPER(OBJECT_TYPE) = 'PACKAGE'
UNION ALL
--
-- procedures and functions
--
SELECT 'G_'||OBJECT_NAME as ord, DBMS_METADATA.get_ddl ('PROCEDURE', OBJECT_NAME, owner) as sql
FROM SYS.ALL_OBJECTS
WHERE owner = '$schemaName'
and UPPER(OBJECT_TYPE) = 'PROCEDURE'
UNION ALL
SELECT 'G_'||OBJECT_NAME as ord, DBMS_METADATA.get_ddl ('FUNCTION', OBJECT_NAME, owner) as sql
FROM SYS.ALL_OBJECTS
WHERE owner = '$schemaName'
and UPPER(OBJECT_TYPE) = 'FUNCTION'
UNION ALL
--
-- packages
--
SELECT 'H_'||OBJECT_NAME as ord, DBMS_METADATA.get_ddl ('PACKAGE_BODY', OBJECT_NAME, owner) as sql
FROM SYS.ALL_OBJECTS
WHERE owner = '$schemaName'
and UPPER(OBJECT_TYPE) = 'PACKAGE BODY'
UNION ALL
--
-- triggers
--
SELECT 'I_'||trigger_NAME as ord, DBMS_METADATA.get_ddl ('TRIGGER', trigger_NAME, owner) as sql
FROM ALL_TRIGGERS
WHERE owner = '$schemaName'
UNION ALL
--
-- materialized views
--
SELECT 'J_'||mview_name as ord, DBMS_METADATA.get_ddl ('MATERIALIZED_VIEW', mview_name, owner) as sql
FROM all_mviews
WHERE owner = '$schemaName'
$refConstraintsSql
)
ORDER BY ORD
EOS;
return $sql;
}
/**
* @param Connection $connection
* @param bool $innerSQLOnly
* @return string
*/
function generateSQLForRefConstraintsCreation(Connection $connection, $innerSQLOnly = false)
{
$schemaName = $this->extractSchemaNameFromConnection($connection);
$sql = <<<EOS
--
-- reference constraints
--
--select 'Z_'||constraint_name as ord, DBMS_METADATA.get_ddl ('CONSTRAINT', constraint_name, owner) as sql
--from all_constraints
--where owner = '$schemaName'
-- and constraint_type = 'P'
--union all
select 'Z_'||constraint_name as ord, DBMS_METADATA.get_ddl ('REF_CONSTRAINT', constraint_name, owner) as sql
from all_constraints
where owner = '$schemaName'
and constraint_type = 'R'
EOS;
if (! $innerSQLOnly) {
$sql = <<<EOS
SELECT sql from (
$sql
)
ORDER BY ORD
EOS;
}
return $sql;
}
}