X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=admin%2Fdb%2Fmaintenance_db.inc;h=ce8e550619a22ee44fb388c06a54a1b04eac3d22;hb=e9782c04faf09dd040dbc0c908cdb6e6d4f0dcfc;hp=11914e02c519e98b69d8de9d46ff6b7a00797545;hpb=cef4b4de401511a4195b7b8ad04b47bec1895b20;p=fa-stable.git

diff --git a/admin/db/maintenance_db.inc b/admin/db/maintenance_db.inc
index 11914e02..ce8e5506 100644
--- a/admin/db/maintenance_db.inc
+++ b/admin/db/maintenance_db.inc
@@ -18,6 +18,8 @@
  * @param $strSortType String containing either asc or desc [default to asc]
  * @desc Naturally sorts an array using by the column $strSortBy
  */
+define('EXPORT_MAX_INSERT', 50000);
+
 function array_natsort($aryData, $strIndex, $strSortBy, $strSortType=false)
 {
    //    if the parameters are invalid
@@ -72,13 +74,14 @@ function write_config_db($new = false)
 	$n = count($db_connections);
 	$msg = "<?php\n\n";
 	$msg .= "/*Connection Information for the database\n";
-	$msg .= "- \$def_coy is the default company that is pre-selected on login\n\n";
-	$msg .= "- host is the computer ip address or name where the database is the default is localhost assuming that the web server is also the sql server\n\n";
-	$msg .= "- user is the user name under which the database should be accessed - need to change to the mysql (or other DB) user set up for purpose\n";
-	$msg .= "  NB it is not secure to use root as the user with no password - a user with appropriate privileges must be set up\n\n";
-	$msg .= "- password is the password the user of the database requires to be sent to authorise the above database user\n\n";
-	$msg .= "- DatabaseName is the name of the database as defined in the RDMS being used. Typically RDMS allow many databases to be maintained under the same server.\n";
-	$msg .= "  The scripts for MySQL provided use the name logicworks */\n\n\n";
+	$msg .= "\$def_coy - the default company that is pre-selected on login\n\n";
+	$msg .= "'host' - the computer ip address or name where the database is. The default is 'localhost' assuming that the web server is also the sql server.\n\n";
+	$msg .= "'dbuser' - the user name under which the company database should be accessed.\n";
+	$msg .= "  NB it is not secure to use root as the dbuser with no password - a user with appropriate privileges must be set up.\n\n";
+	$msg .= "'dbpassword' - the password required for the dbuser to authorise the above database user.\n\n";
+	$msg .= "'dbname' - the name of the database as defined in the RDMS being used. Typically RDMS allow many databases to be maintained under the same server.\n";
+	$msg .= "'tbpref' - prefix on table names, or '' if not used. Always use non-empty prefixes if multiply company use the same database.\n";
+	$msg .= "*/\n\n\n";
 
 	$msg .= "\$def_coy = " . $def_coy . ";\n\n";
 	$msg .= "\$tb_pref_counter = " . $tb_pref_counter . ";\n\n";
@@ -121,26 +124,14 @@ function write_extensions($extensions=null, $company = -1)
 	if (!isset($next_extension_id)) {
 		$next_extension_id = 1;
 	}
-//	$exts = array_natsort($extensions, 'name', 'name');
-//	$extensions = $exts;
 
 	$msg = "<?php\n\n";
 	if ($company == -1)
 		$msg .=
-"/* List of installed additional modules and plugins. If adding extensions manually 
-	to the list make sure they have unique, so far not used extension_ids as a keys,
-	and \$next_extension_id is also updated.
-	
-	'name' - name for identification purposes;
-	'type' - type of extension: 'module' or 'plugin'
-	'path' - FA root based installation path
-	'filename' - name of module menu file, or plugin filename; related to path.
-	'tab' - index of the module tab (new for module, or one of standard module names for plugin);
-	'title' - is the menu text (for plugin) or new tab name
-	'active' - current status of extension
-	'acc_file' - (optional) file name with \$security_areas/\$security_sections extensions; 
-		related to 'path'
-	'access' - security area code in string form
+"/* List of installed additional extensions. If extensions are added to the list manually
+	make sure they have unique and so far never used extension_ids as a keys,
+	and \$next_extension_id is also updated. More about format of this file yo will find in 
+	FA extension system documentation.
 */
 \n\$next_extension_id = $next_extension_id; // unique id for next installed extension\n\n";
 	else 
@@ -189,7 +180,7 @@ function update_extensions($extensions) {
 	}
 
 	// update per company files
-	$cnt = count($db_connections);
+ 	$cnt = max(1, count($db_connections));
 	for($i = 0; $i < $cnt; $i++) 
 	{
 		$newexts = $extensions;
@@ -219,10 +210,13 @@ function write_lang()
 	$n = count($installed_languages);
 	$msg = "<?php\n\n";
 
-	$msg .= "/* How to make new entries here\n\n";
-	$msg .= "-- 'code' should match the name of the directory for the language under \\lang\n";
+	$msg .= "/* How to make new entries here for non-packaged languages:\n\n";
+	$msg .= "-- 'code' should match the name of the directory for the language under \\lang\n.";
 	$msg .= "-- 'name' is the name that will be displayed in the language selection list (in Users and Display Setup)\n";
-	$msg .= "-- 'rtl' only needs to be set for right-to-left languages like Arabic and Hebrew\n\n";
+	$msg .= "-- 'rtl' only needs to be set for right-to-left languages like Arabic and Hebrew\n";
+	$msg .= "-- 'encoding' used in translation file\n";
+	$msg .= "-- 'version' always set to '' for manually installed languages.\n";
+	$msg .= "-- 'path' installation path related to FA root (e.g. 'lang/en_US').\n";
 	$msg .= "*/\n\n\n";
 
 	$msg .= "\$installed_languages = " . var_export($installed_languages, true);
@@ -259,55 +253,22 @@ function write_lang()
 	return true;
 }
 
-function db_create_db($connection)
-{
-	$db = mysql_connect($connection["host"] ,
-		$connection["dbuser"], $connection["dbpassword"]);
-	if (!mysql_select_db($connection["dbname"], $db))
-	{
-		$sql = "CREATE DATABASE " . $connection["dbname"] . "";
-		if (!mysql_query($sql))
-			return 0;
-		mysql_select_db($connection["dbname"], $db);
-	}
-	return $db;
-}
-
-function db_drop_db($connection)
-{
-
-	if ($connection["tbpref"] == "")
-	{
-		$sql = "DROP DATABASE " . $connection["dbname"] . "";
-		return mysql_query($sql);
-	}
-	else
-	{
-    	$res = db_query("show table status");
-    	$all_tables = array();
-    	while($row = db_fetch($res))
-    		$all_tables[] = $row;
-        // get table structures
-		foreach ($all_tables as $table)
-		{
-			if (strpos($table['Name'], $connection["tbpref"]) === 0)
-				db_query("DROP TABLE `".$table['Name'] . "`");
-		}
-		//deleting the tables, how??
-		return true;
-	}
-}
-
 function db_import($filename, $connection, $force=true)
 {
-	global $db, $go_debug;
+	global $db, $go_debug, $sql_trail;
+
+	$sql_trail = false;
 
 	$allowed_commands = array(
 		"create"  => 'table_queries', 
+		"delimiter" => 'table_queries',
 		"alter table" => 'table_queries', 
 		"insert" => 'data_queries', 
 		"update" => 'data_queries', 
-		"drop table if exists" => 'drop_queries');
+		"drop table if exists" => 'drop_queries',
+		"drop function if exists" => 'drop_queries',
+		"drop trigger if exists" => 'drop_queries',
+		);
 	$ignored_mysql_errors = array( //errors ignored in normal (non forced) mode
 		'1022',	// duplicate key
 		'1050', // Table %s already exists
@@ -333,6 +294,7 @@ function db_import($filename, $connection, $force=true)
 
 	// parse input file
 	$query_table = '';
+	$delimiter = ';';
 	foreach($lines as $line_no => $line)
 	{
 		$line = trim($line);
@@ -345,6 +307,10 @@ function db_import($filename, $connection, $force=true)
 			{
 				if (strtolower(substr($line, 0, strlen($cmd))) == $cmd) 
 				{
+					if ($cmd == 'delimiter') {
+						$delimiter = trim(substr($line, 10));
+						continue 2;
+					}
 					$query_table = $table;
 					${$query_table}[] = array('', $line_no+1);
 					break;
@@ -354,9 +320,10 @@ function db_import($filename, $connection, $force=true)
 		 if($query_table != '')  // inside allowed query
 		 {
 		 	$table = $query_table;
-			if (substr($line, -1) == ';') // end of query found
+
+			if (substr($line, -strlen($delimiter)) == $delimiter) // end of query found
 			{
-				$line = substr($line, 0, strlen($line) - 1); // strip ';'
+				$line = substr($line, 0, strlen($line) - strlen($delimiter)); // strip delimiter
 				$query_table = '';
 			}
 			${$table}[count(${$table}) - 1][0] .= $line . "\n";
@@ -414,6 +381,7 @@ function db_import($filename, $connection, $force=true)
 	}
 	
 	db_query("SET foreign_key_checks=1");
+	if ($delimiter != ';') db_query("delimiter ;"); // just for any case
 
 	if (count($sql_errors)) {
 		// display first failure message; the rest are probably derivative 
@@ -423,8 +391,6 @@ function db_import($filename, $connection, $force=true)
 		return false;
 	} else
 		return true;
-	//$shell_command = C_MYSQL_PATH . " -h $host -u $user -p{$password} $dbname < $filename";
-	//shell_exec($shell_command);
 }
 
 // returns the content of the gziped $path backup file. use of $mode see below
@@ -480,23 +446,27 @@ function db_unzip($mode, $path)
     	return explode("\n", $file_data);
 }
 
-function db_backup($conn, $ext='no', $comm='', $tbpref = TB_PREF)
+function db_backup($conn, $ext='no', $comm='', $path=null)
 {
 	if ($conn['tbpref'] != "")
 		$filename = $conn['dbname'] . "_" . $conn['tbpref'] . date("Ymd_Hi") . ".sql";
 	else
 		$filename = $conn['dbname'] . "_" . date("Ymd_Hi") . ".sql";
 
-	return db_export($conn, $filename, $ext, $comm, $tbpref);
+	if (!isset($path))
+		$path = BACKUP_PATH;
+
+	return db_export($conn, $path . clean_file_name($filename), $ext, $comm);
 }
 
 // generates a dump of $db database
 // $drop and $zip tell if to include the drop table statement or dry to pack
-function db_export($conn, $filename, $zip='no', $comment='', $tbpref = TB_PREF)
+function db_export($conn, $filename, $zip='no', $comment='', $no_default=false)
 {
 
 	global $app_title, $version, $power_url, $path_to_root;
 
+
     $error = false;
     // set max string size before writing to file
     $max_size = 1048576 * 2; // 2 MB
@@ -511,7 +481,7 @@ function db_export($conn, $filename, $zip='no', $comment='', $tbpref = TB_PREF)
     	$backupfile = $filename . ".zip";
     else
     	$backupfile = $filename;
-    $company = get_company_pref('coy_name', $tbpref);
+    $company = $conn['name']; // get_company_pref('coy_name');
 
     //create comment
     $out="# MySQL dump of database '".$conn["dbname"]."' on host '".$conn["host"]."'\n";
@@ -539,7 +509,6 @@ function db_export($conn, $filename, $zip='no', $comment='', $tbpref = TB_PREF)
     $all_tables = array();
     while($row = db_fetch($res))
     {
-		//if ($conn["tbpref"] == "" || strpos($row['Name'], $conn["tbpref"]) !== false) replaced
 		if (($conn["tbpref"] == "" && !preg_match('/[0-9]+_/', $row['Name'])) ||
 			($conn["tbpref"] != "" && strpos($row['Name'], $conn["tbpref"]) === 0))
     		$all_tables[] = $row;
@@ -574,7 +543,6 @@ function db_export($conn, $filename, $zip='no', $comment='', $tbpref = TB_PREF)
 	// as long as no error occurred
 	if (!$error)
 	{
-		//while($row=@mysql_fetch_array($res))
 		foreach ($all_tables as $row)
 		{
 			$tablename = $row['Name'];
@@ -585,18 +553,20 @@ function db_export($conn, $filename, $zip='no', $comment='', $tbpref = TB_PREF)
 			$out.="### Structure of table `".$tablename."` ###\n\n";
 
 			$out.="DROP TABLE IF EXISTS `".$tablename."`;\n\n";
+			if ($no_default && ($def_pos = strpos($table_sql[$tablename], "DEFAULT CHARSET")) > 0)
+				$table_sql[$tablename] = substr($table_sql[$tablename], 0, $def_pos);
 			$out.=$table_sql[$tablename];
 
 			// add auto_increment value
 //			if ($auto_incr[$tablename])
 //				$out.=" AUTO_INCREMENT=".$auto_incr[$tablename];
 			$out.=" ;";
-			$out.="\n\n\n";
+			$out.="\n\n";
 
 			// export data
 			if (!$error)
 			{
-				$out.="### Data of table `".$tablename."` ###\n\n";
+				$out.="### Data of table `".$tablename."` ###\n";
 
 				// check if field types are NULL or NOT NULL
 				$res3 = db_query("SHOW COLUMNS FROM `" . $tablename . "`");
@@ -609,18 +579,40 @@ function db_export($conn, $filename, $zip='no', $comment='', $tbpref = TB_PREF)
 				}
 
 				$res2 = db_query("SELECT * FROM `" . $tablename . "`");
+				$maxinsert = 0;
+				$insert = '';
 				for ($j = 0; $j < db_num_rows($res2); $j++)
 				{
-					$out .= "INSERT INTO `" . $tablename . "` VALUES (";
 					$row2 = db_fetch_row($res2);
-					// run through each field
+					$values = '(';
 					for ($k = 0; $k < $nf = db_num_fields($res2); $k++)
 					{
-						$out .= db_escape($row2[$k], $field_null[$k]);
+						$values .= db_escape($row2[$k], $field_null[$k]);
 						if ($k < ($nf - 1))
-							$out .= ", ";
+							$values .= ', ';
+					}
+					$values .= ')';
+					$len = strlen($values);
+					if ($maxinsert < $len+1)
+					{
+						$maxinsert = EXPORT_MAX_INSERT;
+						if ($insert)
+						{
+							$out .= $insert .';'; // flush insert query
+							$insert = '';
+						}
+					}
+
+					if ($insert == '')
+					{
+						$insert = "\nINSERT INTO `" . $tablename . "` VALUES\n";
+						$maxinsert -= strlen($insert);
+					} else {
+						$insert .= ",\n";
 					}
-					$out .= ");\n";
+
+					$maxinsert -= $len;
+					$insert .= $values;
 
 					// if saving is successful, then empty $out, else set error flag
 					if (strlen($out) > $max_size && $zip != "zip")
@@ -631,12 +623,13 @@ function db_export($conn, $filename, $zip='no', $comment='', $tbpref = TB_PREF)
 							$error = true;
 					}
 				}
-
+				if ($insert)
+					$out .= $insert. ';';
 			// an error occurred! Try to delete file and return error status
 			}
 			elseif ($error)
 			{
-				@unlink(BACKUP_PATH . $backupfile);
+				@unlink($backupfile);
 				return false;
 			}
 
@@ -654,13 +647,10 @@ function db_export($conn, $filename, $zip='no', $comment='', $tbpref = TB_PREF)
 	}
 	else
 	{
-		@unlink(BACKUP_PATH . $backupfile);
+		@unlink($backupfile);
 		return false;
 	}
 
-	// if (mysql_error()) return "DB_ERROR";
-	//@mysql_close($con);
-
 	//if ($zip == "zip")
 	//	$zip = $time;
 	if (save_to_file($backupfile, $zip, $out))
@@ -669,7 +659,7 @@ function db_export($conn, $filename, $zip='no', $comment='', $tbpref = TB_PREF)
 	}
 	else
 	{
-		@unlink(BACKUP_PATH . $backupfile);
+		@unlink($backupfile);
 		return false;
 	}
     return $backupfile;
@@ -726,13 +716,15 @@ function order_sql_tables($tables, $fks)
 
 // saves the string in $fileData to the file $backupfile as gz file or not ($zip)
 // returns backup file name if name has changed (zip), else TRUE. If saving failed, return value is FALSE
-function save_to_file($backupfile, $zip, $fileData)
+function save_to_file($path, $zip, $fileData)
 {
 	global $path_to_root;
 
+	$backupfile = basename($path);
+
     if ($zip == "gzip")
     {
-        if ($zp = @gzopen(BACKUP_PATH . $backupfile, "a9"))
+        if ($zp = @gzopen($path, "a9"))
         {
 			@gzwrite($zp, $fileData);
 			@gzclose($zp);
@@ -772,7 +764,7 @@ function save_to_file($backupfile, $zip, $fileData)
         $c_len = strlen($zdata);
 
         // dos time
-        $timearray = getdate($zip);
+        $timearray = getdate();
         $dostime = (($timearray['year'] - 1980) << 25) | ($timearray['mon'] << 21) | ($timearray['mday'] << 16) |
             ($timearray['hours'] << 11) | ($timearray['minutes'] << 5) | ($timearray['seconds'] >> 1);
         $dtime = dechex($dostime);
@@ -809,7 +801,7 @@ function save_to_file($backupfile, $zip, $fileData)
         // total # of entries "on this disk", total # of entries overall, size of central dir, offset to start of central dir, .zip file comment length
         $fileData .= pack('v', 1) . pack('v', 1) . pack('V', strlen($cdrec)) . pack('V', strlen($fr)) . "\x00\x00";
 
-        if ($zp = @fopen(BACKUP_PATH . $backupfile, "a"))
+        if ($zp = @fopen($path, "a"))
         {
 			@fwrite($zp, $fileData);
 			@fclose($zp);
@@ -824,7 +816,7 @@ function save_to_file($backupfile, $zip, $fileData)
     }
     else
     {
-        if ($zp = @fopen(BACKUP_PATH . $backupfile, "a"))
+        if ($zp = @fopen($path, "a"))
         {
 			@fwrite($zp, $fileData);
 			@fclose($zp);
@@ -854,4 +846,39 @@ function create_comp_dirs($comp_path, $comp_subdirs)
 			@fclose($f);
 	    }
 }
+
+//
+//	Checks $field existence in $table with given field $properties
+//	$table - table name without prefix
+//  $field -  optional field name
+//  $properties - optional properties of field defined by MySQL:
+//		'Type', 'Null', 'Key', 'Default', 'Extra'
+//
+function check_table($pref, $table, $field=null, $properties=null)
+{
+	$tables = @db_query("SHOW TABLES LIKE '".$pref.$table."'");
+	if (!db_num_rows($tables))
+		return 1;		// no such table or error
+
+	$fields = @db_query("SHOW COLUMNS FROM ".$pref.$table);
+	if (!isset($field)) 
+		return 0;		// table exists
+
+	while( $row = db_fetch_assoc($fields)) 
+	{
+		if ($row['Field'] == $field) 
+		{
+			if (!isset($properties)) 
+				return 0;
+			foreach($properties as $property => $value) 
+			{
+				if ($row[$property] != $value) 
+					return 3;	// failed type/length check
+			}
+			return 0; // property check ok.
+		}
+	}
+	return 2; // field not found
+}
+
 ?>
\ No newline at end of file