SchemaService.php 3.88 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
<?php
namespace UnicaenImport\Service;

use UnicaenImport\Exception\Exception;
use UnicaenImport\Entity\Schema\Column;

/**
 *
 *
 * @author Laurent LÉCLUSE <laurent.lecluse at unicaen.fr>
 */
class SchemaService extends AbstractService
{
    /**
     * Schéma
     *
     * @var array
     */
    protected $schema;





    /**
     * Retourne le schéma de la BDD
     *
     * @return array
     */
    public function getSchema( $tableName=null )
    {
        if (empty($this->schema)){
            $this->schema = $this->makeSchema();
        }
        if (empty($tableName)){
            return $this->schema;
        }elseif(array_key_exists($tableName, $this->schema)){
            return $this->schema[$tableName];
        }else{
            return null;
        }
    }



    /**
     * @return Column[][]
     */
    public function makeSchema()
    {
        $sql = 'SELECT * FROM V_IMPORT_TAB_COLS';
        $d = $this->query( $sql, [] );

        $sc = [];
        foreach( $d as $col ){
            $column = new Column;
57
58
            $column->table           = $col['TABLE_NAME'];
            $column->name            = $col['COLUMN_NAME'];
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
            $column->dataType        = $col['DATA_TYPE'];
            $column->length          = (null === $col['LENGTH']) ? null : (integer)$col['LENGTH'];
            $column->nullable        = $col['NULLABLE'] == '1';
            $column->hasDefault      = $col['HAS_DEFAULT'] == '1';
            $column->refTableName    = $col['C_TABLE_NAME'];
            $column->refColumnName   = $col['C_COLUMN_NAME'];
            $column->importActif     = $col['IMPORT_ACTIF'] == '1';
            $sc[$col['TABLE_NAME']][$col['COLUMN_NAME']] = $column;
        }
        return $sc;
    }



    /**
     * retourne la liste des tables supportées par l'import automatique
     *
     * @return array
     */
    public function getImportTables()
    {
        $sql = "SELECT SUBSTR(name,5) as TABLE_NAME FROM (
            SELECT mview_name AS name FROM USER_MVIEWS
            UNION SELECT view_name AS name FROM USER_VIEWS
            UNION SELECT TABLE_NAME AS name FROM USER_TABLES
        ) t JOIN user_tables ut ON (ut.table_name = SUBSTR(name,5))
        WHERE name LIKE 'SRC_%'";
        return $this->query( $sql, [], 'TABLE_NAME');
    }

    /**
     * Retourne la liste des tables ayant des vues matérialisées
     *
     * @return string[]
     */
    public function getImportMviews()
    {
        $sql = "SELECT mview_name FROM USER_MVIEWS WHERE mview_name LIKE 'MV_%'";
        $stmt = $this->getEntityManager()->getConnection()->query($sql);
        $mviews = [];
        while ($d = $stmt->fetch()){
            $mvn = substr( $d['MVIEW_NAME'], 3 );
            $mviews[] = $mvn;
        }
        return $mviews;
    }

    /**
107
     *
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
     * @param string $tableName
     * @param string $columnName
     */
    public function hasColumn( $tableName, $columnName )
    {
        $sql = "
        SELECT
          COUNT(*) result
        FROM
          USER_TAB_COLS utc
        WHERE
          utc.table_name = :tableName
          AND utc.column_name = :columnName
        ";
        $result = $this->query( $sql, compact('tableName', 'columnName'), 'RESULT');
        return $result[0] === '1';
    }

    /**
     * Retourne les colonnes concernées par l'import pour une table donnée
     */
    public function getImportCols( $tableName )
    {
        $sql = "
        SELECT
            utc.COLUMN_NAME
        FROM
          USER_TAB_COLS utc
          JOIN ALL_TAB_COLS atc ON (atc.table_name = 'SRC_' || utc.table_name AND atc.column_name = utc.column_name)
        WHERE
          utc.COLUMN_NAME NOT IN ('ID')
          AND utc.COLUMN_NAME NOT LIKE 'HISTO_%'
          AND utc.COLUMN_NAME NOT LIKE 'SOURCE_%'
          AND utc.table_name = :tableName
        ORDER BY
          utc.COLUMN_NAME";

        return $this->query( $sql, ['tableName' => $tableName], 'COLUMN_NAME');
    }

}