Project

General

Profile

1
<?php
2

    
3
/**
4
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
5
 *
6
 * This program is free software: you can redistribute it and/or modify
7
 * it under the terms of the GNU General Public License as published by
8
 * the Free Software Foundation, either version 3 of the License, or
9
 * (at your option) any later version.
10
 *
11
 * This program is distributed in the hope that it will be useful,
12
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
14
 * GNU General Public License for more details.
15
 *
16
 * You should have received a copy of the GNU General Public License
17
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
18
 */
19

    
20
/**
21
 * SqlImport.php
22
 *
23
 * @category     Core
24
 * @package      Core_Database
25
 * @copyright    Manuela v.d.Decken <manuela@isteam.de>
26
 * @author       Manuela v.d.Decken <manuela@isteam.de>
27
 * @license      http://www.gnu.org/licenses/gpl.html   GPL License
28
 * @version      0.0.1
29
 * @revision     $Revision: 2070 $
30
 * @lastmodified $Date: 2014-01-03 02:21:42 +0100 (Fr, 03 Jan 2014) $
31
 * @since        File available since 30.11.2014
32
 * @description  can handle different actions with SQL-Import files
33
 */
34
class SqlImport {
35

    
36
/** WbDatabase object */
37
    protected $oDb          = null;
38
/** valide structure file to use for import */
39
    protected $sStructFile  = '';
40
/** valide TablePrefix to use for import */
41
    protected $sTablePrefix = '';
42
/** default Collation to use for import of chars/text */
43
    protected $sCollation   = 'utf8_unicode_ci';
44
/** default engine for tables */
45
    protected $sEngine      = 'MyISAM';
46
/** collected error messages */
47
    protected $aErrMsg      = array();
48
/** possible actions for doImport() */
49
    protected $aActions     = array(
50
        'uninstall',
51
        'install',
52
        'upgrade'
53
    );
54
/** collected additional replacements pairs */
55
    protected $aReplacements = array('key'=>array(), 'value'=>array());
56
/** possible engines to use */
57
    protected $aEngineTypesAvail = array(
58
        'memory'     => 'MEMORY',
59
        'myisam'     => 'MyISAM',
60
        'innodb'     => 'InnoDB',
61
        'archive'    => 'ARCHIVE'
62
    );
63
/** SQL objects witch can be handled */
64
    protected $aAvailSqlObjects = array(
65
        'TABLE',
66
        'VIEW',
67
        'INDEX',
68
        'PROCEDURE',
69
        'FUNCTION',
70
        'TRIGGER',
71
        'EVENT'
72
    );
73
/**
74
 * Constructor
75
 * @param WbDatabase $oDb
76
 * @param string $sStructFile
77
 */
78
    public function __construct(WbDatabase $oDb, $sStructFile)
79
    {
80
        $this->oDb = $oDb;
81
        $this->sTablePrefix = $oDb->TablePrefix;
82
        if (file_exists($sStructFile)) {
83
            $this->sStructFile = str_replace('\\', '/', $sStructFile);
84
        } else {
85
            $this->sStructFile = '';
86
            $this->aErrMsg[] = 'file with structure not available! ['.$sStructFile.']';
87
        }
88
    }
89
/**
90
 * Add Key-Value pairs for additional placeholders
91
 * @param string $sKey
92
 * @param mixed $sValue
93
 */
94
    public function addReplacement($sKey, $sValue = '')
95
    {
96
        $sKey = strtoupper(preg_replace('/([a-z0-9])([A-Z])/', '\1_\2', ltrim($sKey, 'a..z')));
97
        $this->aReplacements['key'][]  = '/\{'.$sKey.'\}/';
98
        $this->aReplacements['value'][] = $sValue;
99
    }
100
/**
101
 * define another collation then default utf8_unicode_ci
102
 * @param string $sCollation
103
 * @return boolean
104
 */
105
    public function setDefaultCollation($sCollation = 'utf8_unicode_ci')
106
    {
107
        $bRetval = false;
108
        // test if selected collation is available on current server.
109
        $sql = 'SHOW COLLATION LIKE \''.$this->oDb->escapeString($sCollation).'\'';
110
        if (($oAvailCollations = $this->oDb->doQuery($sql))) {
111
            if (($oAvailCollations->numRows())) {
112
                //use new collation
113
                $this->sCollation = $sCollation;
114
                $bRetval = true;
115
            }
116
        }
117
        return $bRetval;
118
    }
119
/**
120
 * define another prefix then 'MyISAM'
121
 * @param string $sEngine
122
 * @return boolean
123
 */
124
    public function setDefaultEngine($sEngine ='MyISAM')
125
    {
126
        $bRetval = false;
127
        if (isset($this->aEngineTypesAvail[strtolower($sEngine)])) {
128
            // use new engine
129
            $this->sEngine = $this->aEngineTypesAvail[strtolower($sEngine)];
130
            $bRetval = true;
131
        }
132
        return $bRetval;
133
    }
134
/**
135
 * define another prefix then default in Wbdatabase
136
 * @param string $sTablePrefix
137
 * @return boolean
138
 */
139
    public function setTablePrefix($sTablePrefix = '')
140
    {
141
        $bRetval = false;
142
        if (
143
        // Prefix must be empty or matching allowed table names
144
            $sTablePrefix == '' ||
145
            preg_match('/^[a-z][a-z0-9_]*$/i', $sTablePrefix)
146
        ) {
147
            // use new TablePrefix
148
            $this->sTablePrefix = $sTablePrefix;
149
            $bRetval = true;
150
        }
151
        return $bRetval;
152
    }
153
/**
154
 * Start and execute the import
155
 * @param string $sAction
156
 * @return boolean
157
 */
158
    public function doImport($sAction)
159
    {
160
        if (! $this->sStructFile) {
161
        // no file found so ignore import method
162
            return true;
163
        }
164
        if (! is_readable($this->sStructFile)) {
165
        // file fond but it's not readable
166
            $this->aErrMsg[] = 'unable to read file ['.$this->sStructFile.']';
167
            return false;
168
        }
169
        $sAction = strtolower(
170
            preg_replace(
171
                '/^.*?('.implode('|', $this->aActions).')(\.php)?$/i',
172
                '$1',
173
                $sAction
174
            )
175
        );
176
        $sAction = ($sAction == '' ? 'install' : $sAction);
177
        if ($this->importSql($sAction)) {
178
            return true;
179
        } else {
180
            $this->aErrMsg[] = $this->oDb->getError();
181
            return false;
182
        }
183
    }
184
/**
185
 * return errorstrings, concatet by LF
186
 * @return string
187
 */
188
    public function getError()
189
    {
190
        return implode(PHP_EOL, $this->aErrMsg);
191
    }
192
/**
193
 * Import an SQl-Dumpfile witch can include unlimited additional placeholders for values
194
 * @param string $sAction 'install' || 'uninstall' || 'upgrade'
195
 * @return bool  false on error
196
 */
197
    protected function importSql($sAction = 'install')
198
    {
199
        $retval = true;
200
        $this->error = '';
201
        // sanitize arguments
202
        $aTmp = preg_split('/_/', $this->sCollation, null, PREG_SPLIT_NO_EMPTY);
203
        $sCharset = $aTmp[0];
204
        // get from addReplacements
205
        $aSearch  = $this->aReplacements['key'];
206
        /* ***  ATTENTION:: Do Not Change The Order Of Search-Replace Statements !! *** */
207
        // define basic array of searches
208
        $aSearch[] = '/\{TABLE_PREFIX\}/';                                        /* step 0 */
209
        $aSearch[] = '/\{FIELD_CHARSET\}/';                                       /* step 1 */
210
        $aSearch[] = '/\{FIELD_COLLATION\}/';                                     /* step 2 */
211
        $aSearch[] = '/\{TABLE_ENGINE\}/';                                        /* step 3 */
212
        $aSearch[] = '/\{TABLE_ENGINE=([a-zA-Z_0-9]*)\}/';                        /* step 4 */
213
        $aSearch[] = '/\{CHARSET\}/';                                             /* step 5 */
214
        $aSearch[] = '/\{COLLATION\}/';                                           /* step 6 */
215
        // get from addReplacements
216
        $aReplace = $this->aReplacements['value'];
217
        // define basic array of replacements
218
        $aReplace[] = $this->sTablePrefix;                                        /* step 0 */
219
        $aReplace[] = ' CHARACTER SET {CHARSET}';                                 /* step 1 */
220
        $aReplace[] = ' COLLATE {COLLATION}';                                     /* step 2 */
221
        $aReplace[] = ' {TABLE_ENGINE='.$this->sEngine.'}';                       /* step 3 */
222
        $aReplace[] = ' ENGINE=$1 DEFAULT CHARSET={CHARSET} COLLATE={COLLATION}'; /* step 4 */
223
        $aReplace[] = $sCharset;                                                  /* step 5 */
224
        $aReplace[] = $this->sCollation;                                          /* step 6 */
225

    
226
        $sql = ''; // buffer for statements
227
        $aSql = file($this->sStructFile, FILE_SKIP_EMPTY_LINES|FILE_IGNORE_NEW_LINES);
228
        //  remove possible ByteOrderMark
229
        $aSql[0] = preg_replace('/^[\xAA-\xFF]{3}/', '', $aSql[0]);
230
    // iterate file line by line
231
        while (sizeof($aSql) > 0) {
232
            // remove trailing and leading whitespaces
233
            $sSqlLine = trim(array_shift($aSql));
234
            // skip line if it's a comment
235
            if (preg_match('/^--/', $sSqlLine)) { continue; }
236
            // attach line to buffer
237
            $sql = $sql.' '.$sSqlLine;
238
            // detect end of statement
239
            if ((substr($sql,-1,1) == ';')) {
240
                // replace placeholders in statement
241
                $sql = trim(preg_replace($aSearch, $aReplace, $sql));
242
                $sAvailSqlObjects = implode('|', $this->aAvailSqlObjects);
243
                switch ($sAction) {
244
                    case 'uninstall': // execute DROP - skip CREATE
245
                        if (preg_match('/^\s*CREATE ('.$sAvailSqlObjects.') /si', $sql)) {
246
                            $sql = ''; // clear buffer
247
                            continue 2; // read next statement
248
                        }
249
                        break;
250
                    case 'upgrade': // skip DROP; execute CREATE
251
                        if (preg_match('/^\s*DROP ('.$sAvailSqlObjects.') /si', $sql)) {
252
                            $sql = ''; // clear buffer
253
                            continue 2; // read next statement
254
                        }
255
                        break;
256
                    default: // install:  execute DROP; execute CREATE
257
                        break;
258
                }
259
                if (!$this->oDb->doQuery($sql)) {
260
                    $retval = false;
261
                    $this->aErrMsg[] = $this->oDb->getError();
262
                    unset($aSql);
263
                    break;
264
                }
265
                $sql = ''; // clear buffer
266
            }
267
        }
268
        return $retval;
269
    } // end of function importSql()
270

    
271
}
(10-10/37)