source-function-F_execute_sql_queries

  1: <?php
  2: //============================================================+
  3: // File name   : tce_functions_install.php
  4: // Begin       : 2002-05-13
  5: // Last Update : 2013-10-23
  6: //
  7: // Description : Installation functions for TCExam.
  8: //
  9: // Author: Nicola Asuni
 10: //
 11: // (c) Copyright:
 12: //               Nicola Asuni
 13: //               Tecnick.com LTD
 14: //               www.tecnick.com
 15: //               info@tecnick.com
 16: //
 17: // License:
 18: //    Copyright (C) 2004-2013  Nicola Asuni - Tecnick.com LTD
 19: //    See LICENSE.TXT file for more information.
 20: //============================================================+
 21: 
 22: /**
 23:  * Install TCExam database.
 24:  * @param string $db_type database type (MySQL)
 25:  * @param string $db_host database host
 26:  * @param string $db_port database port number
 27:  * @param string $db_user database user
 28:  * @param string $db_password database password
 29:  * @param string $database_name database name
 30:  * @param string $table_prefix prefix for tables
 31:  * @param boolean $drop_database if true drop existing database
 32:  * @param boolean $create_new if true creates new database
 33:  * @param string $progress_log log file name
 34:  * @return boolean True in case of success, False otherwise.
 35:  */
 36: function F_install_database($db_type, $db_host, $db_port, $db_user, $db_password, $database_name, $table_prefix, $drop_database, $create_new, $progress_log) {
 37:     ini_set('memory_limit', '256M');
 38:     define ('K_DATABASE_TYPE', $db_type); // database type (for Database Abstraction Layer)
 39:     // Load the Database Abstraction Layer for selected DATABASE type
 40:     switch (K_DATABASE_TYPE) {
 41:         case 'ORACLE': {
 42:             require_once('../shared/code/tce_db_dal_oracle.php');
 43:             break;
 44:         }
 45:         case 'POSTGRESQL': {
 46:             require_once('../shared/code/tce_db_dal_postgresql.php');
 47:             break;
 48:         }
 49:         case 'MYSQL': {
 50:             require_once('../shared/code/tce_db_dal_mysqli.php');
 51:             break;
 52:         }
 53:         case 'MYSQLDEPRECATED': {
 54:             require_once('../shared/code/tce_db_dal_mysql.php');
 55:             break;
 56:         }
 57:         default: {
 58:             return false;
 59:         }
 60:     }
 61:     echo "\n".'<li>create or replace database and get connection........';
 62:     error_log('  create or replace database and get connection'."\n", 3, $progress_log); //log info
 63:     if ($db = F_create_database(K_DATABASE_TYPE, $db_host, $db_port, $db_user, $db_password, $database_name, $table_prefix, $drop_database, $create_new)) { //create database if not exist
 64:         echo '<span style="color:#008000">[OK]</span></li>';
 65:         echo "\n".'<li>create database tables..........';
 66:         error_log('  [START] create database tables'."\n", 3, $progress_log); //log info
 67:         // process structure sql file
 68:         if (F_execute_sql_queries($db, strtolower(K_DATABASE_TYPE).'_db_structure.sql', 'tce_', $table_prefix, $progress_log)) {
 69:             echo '<span style="color:#008000">[OK]</span></li>';
 70:             error_log('  [END:OK] create database tables'."\n", 3, $progress_log); //log info
 71:             echo "\n".'<li>fill tables with default data...';
 72:             error_log('  [START] fill tables with default data'."\n", 3, $progress_log); //log info
 73:             // process data sql file
 74:             if (F_execute_sql_queries($db, 'db_data.sql', 'tce_', $table_prefix, $progress_log)) {
 75:                 echo '<span style="color:#008000">[OK]</span></li>';
 76:                 error_log('  [END:OK] fill tables with default data'."\n", 3, $progress_log); //log info
 77:             } else {
 78:                 echo '<span style="color:#CC0000">[ERROR '.F_db_error($db).']</span></li>';
 79:                 error_log('  [END:ERROR] fill tables with default data: '.F_db_error($db)."\n", 3, $progress_log); //log info
 80:                 return false;
 81:             }
 82:         } else {
 83:             echo '<span style="color:#CC0000">[ERROR '.F_db_error($db).']</span></li>';
 84:             error_log('  [END:ERROR] create database tables: '.F_db_error($db)."\n", 3, $progress_log); //log info
 85:             return false;
 86:         }
 87:     } else {
 88:         echo '<span style="color:#CC0000">[ERROR: '.F_db_error($db).']</span></li>';
 89:         error_log('  [END:ERROR] could not connect to database: '.F_db_error($db)."\n", 3, $progress_log); //log info
 90:         return false;
 91:     }
 92:     flush();
 93:     return true;
 94: }
 95: 
 96: 
 97: /**
 98:  * Parses an SQL file and execute queries.
 99:  * @param string $db database connector
100:  * @param string $sql_file file to parse
101:  * @param string $search string to replace
102:  * @param string $replace replace string
103:  * @param string $progress_log log file name
104:  * @return boolean true in case of success, false otherwise.
105:  */
106: function F_execute_sql_queries($db, $sql_file, $search, $replace, $progress_log) {
107:     ini_set('memory_limit', -1); // remove memory limit
108: 
109:     $sql_data = @fread(@fopen($sql_file, 'r'), @filesize($sql_file)); //open and read file
110:     if ($search) {
111:         $sql_data = str_replace($search, $replace, $sql_data); // execute search and replace for the given parameters
112:     }
113:     $sql_data = str_replace("\r", '', $sql_data); // remove CR
114:     $sql_data = "\n".$sql_data; //prepare string for replacements
115:     $sql_data = preg_replace("/\/\*([^\*]*)\*\//si", ' ', $sql_data); // remove comments (/* ... */)
116:     $sql_data = preg_replace("/\n([\s]*)\#([^\n]*)/si", '', $sql_data); // remove comments (lines starting with '#' (MySQL))
117:     $sql_data = preg_replace("/\n([\s]*)\-\-([^\n]*)/si", '', $sql_data); // remove comments (lines starting with '--' (PostgreSQL))
118:     $sql_data = preg_replace("/;([\s]*)\n/si", ";\r", $sql_data); // mark valid new lines
119:     $sql_data = str_replace("\n", " ", $sql_data); // remove carriage returns
120:     $sql_data = preg_replace("/(;\r)$/si", '', $sql_data); // remove last ";\r"
121:     $sql_query = explode(";\r", trim($sql_data)); // split sql string into SQL statements
122:     //execute queries
123:     while(list($key, $sql) = each($sql_query)) { //for query on sql file
124:         error_log('    [SQL] '.$key."\n", 3, $progress_log); //create progress log file
125:         echo ' '; //print something to keep browser live
126:         if (($key % 300) == 0) { //force flush output every 300 processed queries
127:             echo '<!-- '.$key.' -->'."\n"; flush(); //force flush output to browser
128:         }
129:         if(!$r = F_db_query($sql, $db)) {
130:             return FALSE;
131:         }
132:     }
133:     return TRUE;
134: }
135: 
136: /**
137:  * Create new database. Existing database will be dropped.
138:  * Oracle databases must be created manually (create the tcexam user and set the database name equal to user name)
139:  * @param string $host Database server path. It can also include a port number. e.g. "hostname:port" or a path to a local socket e.g. ":/path/to/socket" for the localhost. Note: Whenever you specify "localhost" or "localhost:port" as server, the MySQL client library will override this and try to connect to a local socket (named pipe on Windows). If you want to use TCP/IP, use "127.0.0.1" instead of "localhost". If the MySQL client library tries to connect to the wrong local socket, you should set the correct path as mysql.default_host in your PHP configuration and leave the server field blank.
140:  * @param string $dbtype database type ('MYSQL' or 'POSTGREQL')
141:  * @param string $host database host
142:  * @param string $port database port
143:  * @param string $user Name of the user that owns the server process.
144:  * @param string $password Password of the user that owns the server process.
145:  * @param string $database Database name.
146:  * @param string $table_prefix prefix for tables
147:  * @param boolean $drop if true drop existing database
148:  * @param boolean $create if true creates new database
149:  * @return database link identifier on success, FALSE otherwise.
150:  */
151: function F_create_database($dbtype, $host, $port, $user, $password, $database, $table_prefix, $drop, $create) {
152:     // open default connection
153:     if ($drop OR $create) {
154:         if ($db = @F_db_connect($host, $port, $user, $password)) {
155:             if ($dbtype == 'ORACLE') {
156:                 if ($drop) {
157:                     $table_prefix = strtoupper($table_prefix);
158:                     // DROP sequences
159:                     $sql = 'select \'DROP SEQUENCE \'||sequence_name||\'\' from user_sequences where sequence_name like \''.$table_prefix.'%\'';
160:                     if($r = @F_db_query($sql, $db)) {
161:                         while($m = @F_db_fetch_array($r)) {
162:                             @F_db_query($m[0], $db);
163:                         }
164:                     }
165:                     // DROP triggers
166:                     $sql = 'select \'DROP TRIGGER \'||trigger_name||\'\' from user_triggers where trigger_name like \''.$table_prefix.'%\'';
167:                     if($r = @F_db_query($sql, $db)) {
168:                         while($m = @F_db_fetch_array($r)) {
169:                             @F_db_query($m[0], $db);
170:                         }
171:                     }
172:                     // DROP tables
173:                     $sql = 'select \'DROP TABLE \'||table_name||\' CASCADE CONSTRAINTS\' from user_tables where table_name like \''.$table_prefix.'%\'';
174:                     if($r = @F_db_query($sql, $db)) {
175:                         while($m = @F_db_fetch_array($r)) {
176:                             @F_db_query($m[0], $db);
177:                         }
178:                     }
179:                 } else {
180:                     echo '<span style="color:#000080">[SKIP DROP]</span> ';
181:                 }
182:                 // Note: Oracle Database automatically creates a schema when you create a user,
183:                 //       so you have to create a tcexam user before calling this.
184:             } else {
185:                 if ($drop) {
186:                     // DROP existing database (if exist)
187:                     @F_db_query('DROP DATABASE '.$database.'', $db);
188:                 } else {
189:                     echo '<span style="color:#000080">[SKIP DROP]</span> ';
190:                 }
191:                 if($create) {
192:                     // create database
193:                     $sql = 'CREATE DATABASE '.$database.'';
194:                     if ($dbtype == 'MYSQL') {
195:                         $sql .= ' CHARACTER SET utf8 COLLATE utf8_unicode_ci';
196:                     } elseif ($dbtype == 'POSTGRESQL') {
197:                         $sql .= ' ENCODING=\'UNICODE\'';
198:                     }
199:                     if(!$r = @F_db_query($sql, $db)) {
200:                         return FALSE;
201:                     }
202:                 } else {
203:                     echo '<span style="color:#000080">[SKIP CREATE]</span> ';
204:                 }
205:             }
206:             @F_db_close($db);
207:         } else {
208:             return FALSE;
209:         }
210:     } else {
211:         echo '<span style="color:#000080">[SKIP DROP AND CREATE]</span> ';
212:     }
213:     if ($db = @F_db_connect($host, $port, $user, $password, $database)) {
214:         return $db;
215:     } else {
216:         return FALSE;
217:     }
218: }
219: 
220: /**
221:  * Update some configuration files.
222:  * @param string $db_type database type (MySQL)
223:  * @param string $db_host database host
224:  * @param string $db_port database port number
225:  * @param string $db_user database user
226:  * @param string $db_password database password
227:  * @param string $database_name database name
228:  * @param string $table_prefix table prefix
229:  * @param string $path_host host URL
230:  * @param string $path_tcexam relative URL where this program is installed
231:  * @param string $path_main real full server path where this program is installed
232:  * @param string $standard_port standard http web port
233:  * @param string $progress_log log file name
234:  * @return boolean true in case of success, false otherwise
235:  */
236: function F_update_config_files($db_type, $db_host, $db_port, $db_user, $db_password, $database_name, $table_prefix, $path_host, $path_tcexam, $path_main, $standard_port, $progress_log) {
237: 
238:     if(!defined('PHP_VERSION_ID')) {
239:         $version = PHP_VERSION;
240:         define('PHP_VERSION_ID', (($version[0] * 10000) + ($version[2] * 100) + $version[4]));
241:     }
242:     if (PHP_VERSION_ID < 50300) {
243:         @set_magic_quotes_runtime(0);
244:     }
245: 
246:     // initialize configuration directories with default values
247:     
248:     rename('../shared/config.default', '../shared/config');
249:     rename('../admin/config.default', '../admin/config');
250:     rename('../public/config.default', '../public/config');
251: 
252:     $config_file = array(); // configuration files
253: 
254:     $config_file[0] = '../shared/config/tce_db_config.php';
255:     $config_file[1] = '../shared/config/tce_paths.php';
256: 
257:     // file parameters to change as regular expressions (0=>search, 1=>replace)
258:     $parameter = array();
259: 
260:     $parameter[0] = array(
261: 
262:         '0'  => array ('0' => "K_DATABASE_TYPE', '([^\']*)'", '1' => "K_DATABASE_TYPE', '".$db_type."'"),
263:         '1'  => array ('0' => "K_DATABASE_HOST', '([^\']*)'", '1' => "K_DATABASE_HOST', '".$db_host."'"),
264:         '2'  => array ('0' => "K_DATABASE_PORT', '([^\']*)'", '1' => "K_DATABASE_PORT', '".$db_port."'"),
265:         '3'  => array ('0' => "K_DATABASE_NAME', '([^\']*)'", '1' => "K_DATABASE_NAME', '".$database_name."'"),
266:         '4'  => array ('0' => "K_DATABASE_USER_NAME', '([^\']*)'", '1' => "K_DATABASE_USER_NAME', '".$db_user."'"),
267:         '5'  => array ('0' => "K_DATABASE_USER_PASSWORD', '([^\']*)'", '1' => "K_DATABASE_USER_PASSWORD', '".$db_password."'"),
268:         '6'  => array ('0' => "K_TABLE_PREFIX', '([^\']*)'", '1' => "K_TABLE_PREFIX', '".$table_prefix."'")
269:     );
270: 
271:     $parameter[1] = array(
272:         '0'  => array ('0' => "K_PATH_HOST', '([^\']*)'", '1' => "K_PATH_HOST', '".$path_host."'"),
273:         '1'  => array ('0' => "K_PATH_TCEXAM', '([^\']*)'", '1' => "K_PATH_TCEXAM', '".$path_tcexam."'"),
274:         '2'  => array ('0' => "K_PATH_MAIN', '([^\']*)'", '1' => "K_PATH_MAIN', '".$path_main."'"),
275:         '3'  => array ('0' => "K_STANDARD_PORT', ([^\)]*)", '1' => "K_STANDARD_PORT', ".$standard_port."")
276:     );
277: 
278:     while(list($key, $file_name) = each($config_file)) { //for each configuration file
279: 
280:         error_log('  [START] process file: '.basename($file_name)."\n", 3, $progress_log); //log info
281:         echo "\n".'<li>start process <i>'.basename($file_name).'</i> file:';
282:         echo "\n".'<ul>';
283:         //try to change file permissions (unix-like only)
284:         //chmod($file_name, 0777);
285: 
286:         echo "\n".'<li>open file.................';
287:         error_log('    open file', 3, $progress_log); //log info
288:         $fp = fopen($file_name, 'r+');
289:         if (!$fp) {
290:             echo '<span style="color:#CC0000">[ERROR]</span></li>';
291:             error_log(' [ERROR]'."\n", 3, $progress_log); //log info
292:         } else { // the file has been opened
293:             echo '<span style="color:#008000">[OK]</span></li>';
294:             error_log(' [OK]'."\n", 3, $progress_log); //log info
295: 
296:             //read the file
297:             echo "\n".'<li>read file.................';
298:             error_log('    read file', 3, $progress_log); //log info
299:             $file_data = fread($fp, filesize($file_name));
300:             if (!$file_data){
301:                 echo '<span style="color:#CC0000">[ERROR]</span></li>';
302:                 error_log(' [ERROR]'."\n", 3, $progress_log); //log info
303:             } else {
304:                 echo '<span style="color:#008000">[OK]</span></li>';
305:                 error_log(' [OK]'."\n", 3, $progress_log); //log info
306: 
307:                 //change cfg file values
308:                 while(list($pkey, $pval) = each($parameter[$key])) { //for each file parameter
309:                     echo "\n".'<li>update value '.$pkey.' ...........';
310:                     error_log('      update value '.$pkey.'', 3, $progress_log); //log info
311:                     $file_data = preg_replace('#'.$pval[0].'#', $pval[1], $file_data); //update cfg parameters
312:                     echo '<span style="color:#008000">[OK]</span></li>';
313:                     error_log(' [OK]'."\n", 3, $progress_log); //log info
314:                 }
315:             }
316: 
317:             //write the file
318:             echo "\n".'<li>write file................';
319:             error_log('    write file', 3, $progress_log); //log info
320:             rewind ($fp);
321:             if (!fwrite ($fp, $file_data)) {
322:                 echo '<span style="color:#CC0000">[ERROR]</span></li>';
323:                 error_log(' [ERROR]'."\n", 3, $progress_log); //log info
324:             } else {
325:                 echo '<span style="color:#008000">[OK]</span></li>';
326:                 error_log(' [OK]'."\n", 3, $progress_log); //log info
327:             }
328: 
329:             if (strlen($file_data) < filesize($file_name)) {
330:                 ftruncate ($fp, strlen($file_data)); //truncate file
331:             }
332: 
333:             echo "\n".'<li>close file................';
334:             error_log('    close file', 3, $progress_log); //log info
335:             if (fclose($fp)) {
336:                 echo '<span style="color:#008000">[OK]</span></li>';
337:                 error_log(' [OK]'."\n", 3, $progress_log); //log info
338:             } else {
339:                 echo '<span style="color:#CC0000">[ERROR]</span></li>';
340:                 error_log(' [ERROR]'."\n", 3, $progress_log); //log info
341:             }
342:         }
343: 
344:         //try to set file permissions to read only (unix-like only)
345:         //chmod($file_name, 0644);
346:         echo "\n".'</ul>';
347:         echo "\n".'</li>';
348:         echo "\n".'<li>end process <i>'.basename($file_name).'</i> file</li>';
349:         error_log('  [END] process file: '.basename($file_name)."\n", 3, $progress_log); //log info
350:     }
351:     if (PHP_VERSION_ID < 50300) {
352:         set_magic_quotes_runtime(get_magic_quotes_gpc()); //restore magic quotes settings
353:     }
354:     flush(); // force browser output
355:     return TRUE;
356: }
357: 
358: //============================================================+
359: // END OF FILE
360: //============================================================+
361: