<?php
/**
 * Copyright (C) 2009-2010 Ultimate Linux Solutions CC
 * $Id$
 *
 * DB Schema tool.
 *
 * This should replace checkdb.php, or rather, checkdb.php should be re-implemented making use of these
 * functions, resulting in a MUCH SHORTER checkdb.php script.  The requirements for this script is:
 *
 * Checklist (mark stuff that's done with an X on the left, same column as the = for high-level headings).
 *
 * = Schema Comparison Requirements:
 *   * table comparisons:
 *    - tables that doesn't exist should be created.
 *    - tables that's extra should be dropped.
 *    - tables that differ should be updated.
 *    - tables that got renamed should be renamed properly.
 * X * column comparisons:
 * X  - column types should be compared, and changed as required.
 * X  - column renames.
 * X  - column order should be taken into consideration.
 *   * primary keys:
 *    - primary keys either exists for a table or doesn't.
 *    - can't have multiple primary keys, thus a PK change is a "drop primary key, add primary key()" change.
 *   * key comparisons:
 *    - keys comes in two flavors: indexes + unique.
 *    - keys has names.
 *    - keys can't be renamed (the old one should be dropped and the new one created).
 *    - if an FK references this key then that FK needs to be dropped before we can drop this key.
 *    - keys that can potentially be used by a FK needs to be created before that FK.
 *   * foreign keys:
 *    - foreign keys depends on the referenced tables(columns) already existing.
 *    - foreign keys can't be changed, thus drop + add (as per PKs + keys).
 *   * triggers:
 *    - create missing triggers.
 *    - drop extraneous triggers.
 *    - update (drop + re-create) incorrect triggers.
 *   * stored routines:
 *    - create missing routines.
 *    - drop extraneous routines.
 *    - update (drop + re-create) incorrect routines.
 * = Support Requirements:
 *   * Ignore certain tables.
 *   * Analyze live schema.
 * X  - tables
 * X  - views
 *    - stored procedures
 *    - stored functions
 * X  - grants
 * X  - triggers
 *   * Progressively construct a schema.
 *   * Progressively construct a table definition (may be useful down the line if we want to vary tables based on actual modules installed).
 *   * Ability to add arbitrary SQL statements to the destination schema that depends on certain mods, specifically:
 *    - column add
 *    - column drop
 *    - a column rename is seen (for the purposes of these triggers) as a drop of the old name, and an add of the new one.
 * = Grants:
 * X * Find a list of users who has grants on a DB.
 *   * Compare grants and grant + revoke as required.
 *   * Re-grant any execute privileges on any upddated stored routines as these gets dropped together with a drop of a stored routine.
 * = Migration tasks:
 *   * Ignore certain tables from the migration.
 *   * Ignore certain columns from the migration.
 *   * Ignore certain users (grants) from the migration.
 *   * Ability to ignore grants altogether (we may not have the "with grant option" privilege on all privileges)
 *
 * Notes:
 * - column order is rather tricky because:
 *   * simply issueing an alter table with an "after" modification doesn't seem to do what I expect (with multiple columns involved).
 *   * you can't set an after to a column that's also being modified in the same statement (doing this as part of an add seems to be ok).
 *   * it seems that the ORDER of the 'after' statements is actually important, eg:
 *     WORKS: alter table t change column i1 i1 int first, change column i2 i2 int after i1
 *     FAILS: alter table t change column i2 i2 int after i1, change column i1 i1 int first
 *     even though these two statements are logically IDENTICAL.
 * - modifying grants requires grant privileges.
 * - this file isn't sourced normally by core.php as it can function independently - if you're going to source core, do so before
 *   you source this file.
 *
 * Restrictions:
 * ============
 *
 * Not everything is handled cleanly, so here is a list of restrictions:
 * - type inference rules is built around the MySQL engine.  Whilst this may
 *   work for other engines too I cannot vouch for it.
 * - In the case of character (non-numeric) default values you have to use '
 *   marks to delimit the default value, " will NOT work.  If you do use quote
 *   marks for numeric types the same rule applies.
 * - The 'year' type is NOT handled.  This is so close to a int(4) that there
 *   is no real distinction IMHO.  You can specify a length, as in year(5) but
 *   this is always overruled back to year(4) except in the case of year(2) which
 *   is kept as year(2).  There may be semantic differences I'm not aware of.
 * - If BOTH unsigned and zerofill is specified unsigned MUST be first.
 * - auto_increment is only allowed for int data types and NOT NULL is enforced
 *   in this case.
 * - user and host names in grants containing ' marks will break.
 * - Unless 'nogrants' is given as option to schema_from_db_mysql - it HAS to be
 *		connected as a user with permissions to select stuff from the mysql.user
 *		tables, as well as to issue show grants for those users.
 *
 * Prerequisite Requirements:
 * =========================
 *
 * You need to provide some basic functions before calling certain functions in
 * this script, in particular, before you can create a schema from a live database
 * you need to provide the following functions:
 *
 * - query_getarray($query) - needs to issue the passed query, then construct
 *   an array containing the first column of the result set.  Eg, a sample query
 *   on a database with two tables foo and bar of "show tables" should return
 *   array("foo", "bar").
 * - query_getrow($query) - needs to issue the passed query, then return an associative
 *   array containing the first row of the result set.  The queries passed will always
 *   only result in a single row in the result set.
 *
 * Usage:
 * =====
 *
 * The easiest way to get started is to basically call schema_from_db_mysql() which
 * will create a schema from whatever database is served by the query_getarray and query_getrow
 * functions.
 *
 * To build a new Schema from scratch:
 *
 * $schema = schema_init();
 *
 * To add a table based on the output from "SHOW CREATE TABLE" use this:
 *
 * schema_add_table_from_sql($schema, $sql)
 */

/***********************************
 * Structures.  These are in fact associative(hash)-arrays, so they are not well defined in the sense that a C++ class is,
 * but please do follow these in order to keep sanity.
 */
/**
 * Structure: table structure (does not define name, merely the structure).
 *
 * indexes:
 * - fields - contains an _ordered_ associative array mapping field names to types.
 * - pkey - contains the name of the primary key, which is either a plain name, or an array for composite keys.
 * - indexes - normal non-unique keys.  contains an associative array mapping key names to key fields (array for composite).
 * - unique - like indexes, but for unique keys.
 * - fkeys - associative array mapping fkey names to fkey definitions, each fkey definition in itself is an associative array
 * 		with the following keys:
 * 		 - fields - the fields used for performing the reference.
 * 		 - reftable - the table being referenced.
 * 		 - reffields - the field(s) being referenced (array for composite).
 * 		 - onupdate - one of "restrict" (default), "cascade", "no action" or "set null"
 * 		 - ondelete - one of "restrict" (default), "cascade", "no action" or "set null"
 * - engine - any legal MySQL engine table type (MyISAM, InnoDB, Memory, NDB ...), defaults to InnoDB (HIGHLY recommended)
 * - charset - any legal MySQL character set, defaults to "latin1" (text types in the field list will be modified to explicitly have this charset as well, for improved comparisons and ensuring minimal update statements).
 * - fldrenames - an associative array mapping new table names to old names (an array), eg "privilege" => "priviledge" indicates that priviledge has been renamed to privilege.
 */
/**
 * Structure: stored routine (doesn't define name, simply parameters, (optionally) return type, characteristics and body.
 *
 * params - an associative array mapping parameter names to types.
 * returntype - only used for stored functions, the return type (eg, "varchar(16)").
 * deterministic - a boolean indicating whether or not the routine is deterministic (pure function: the results depends purely on the parameters).
 * sqllevel - one of "contains sql" (default), "no sql", "reads sql data" or "modifies sql data", setting this is relatively important as more functions/procs will actually be reads or modifies.
 *		- contains sql - implies the routine contains some sql statements.
 *		- no sql - implies the routine doesn't contain any sql statements (I fail to see how this is useful).
 *		- reads sql data - implies there are statements that selects data from tables, but does not trigger updates (insert/update statements).
 *		- writes sql data - implies that there are statements which triggers updates (insert/update statements)
 * body - the actual routine body, excluding the leading BEGIN and trailing END which will be added automatically.
 */
/**
 * Structure: trigger (definition)
 *
 * timing - "before" or "after"
 * event - "insert", "update" or "delete"
 * tbl - name of the affected table
 * stmt - will be executed for each row, can be compound in which case it must be wrapped in begin ... end (no trailing semi-colons).
 */
/**
 * Structure: grant (what privileges on being granted and on what)
 *
 * privs - an array containing the privileges.  These are pretty much the command separated list between GRANT and ON.
 * object - table of stored proc name.  object is automatically prefixed with the db name.
/**
 * Structure: schema structure (doesn't define DB name, simply the tables, views and routines contained therein.
 *
 * indexes:
 * - tables - an associative array mapping table names to table structures (as defined above).
 * - views - an associative array mapping view names to select statements.
 * - procs - an associative array mapping stored proc names to a routine definition.
 * - funcs - an associative array mapping stored func names to a routine definition.
 * - triggers - an associative array mapping trigger names to trigger definitions.
 * - tblrenames - an associative array mapping new table names to old names (an array), eg "priviledge" => array("privilege").
 * - grants - an associative array mapping user@host names to a list of grants.
 */

/** simple do_die should we not have the ULS one available. */
if (!function_exists("do_die")) {
	function do_die($msg) /* duplicate */
	{
		$trace = debug_backtrace();
		do_die($trace[0]['file'].":".$trace[0]['line']." - ".$msg."\n");
	}
}

/** simple warning function which will simply output all warnings to STDERR */
if (!function_exists("do_warn")) {
	function do_warn($msg) {
		fprintf(STDERR, "WARNING: %s\n", $msg);
	}
}

function schema_check_array($val)
{
	return is_array($val) ? $val : array($val);
}

function schema_split_compound($s)
{
	return preg_split("/`, ?`/", $s);
}

/**
 * This function initializes a new structure which will be usable for other schema
 * functions.  Call this to initialize a schema structure for use by other functions.
 */
function schema_init()
{
	return array();
}

function schema_add_table_from_sql(&$schema, $tbl_def)
{
	schema_table_from_sql($tbl_def, $tblname, $def);
	$schema['tables'][$tblname] = $def;
}

function schema_add_view_from_sql(&$schema, $view_def)
{
	preg_match("/^([^ ]| [^V]| V[^I]| VI[^E]| VIE[^W]| VIEW[^ ])* VIEW `([^`]+)` ([^A]|A[^S]|AS[^ ])*AS (.*)$/", $view_def, $matches) or do_die("Regex error trying to analyze view: ".$view_def);
	$schema['views'][$matches[2]] = $matches[4];
}

/** This function will print a php array structure given a table definition.  By default it outputs to stdout but can be sent to any file. */
function schema_print_table($def, $lead = "", $fp = STDOUT)
{
	fprintf($fp, "array(\n");

	// output the fields (note the @ sign in case the index isn't set)
	fprintf($fp, "%s\t'fields' => array(\n", $lead);
	foreach ($def['fields'] as $fn => $fd) {
		fprintf($fp, "%s\t\t'%s' => \"%s\",\n", $lead, $fn, str_replace("\\", "\\\\", $fd));
	}
	fprintf($fp, "%s\t),\n", $lead);

	// primary key
	if (isset($def['pkey']))
		fprintf($fp, "%s\t'pkey' => array('%s'),\n", $lead, implode("', '", schema_check_array($def['pkey'])));

	// indexes
	foreach (array("unique", "indexes") as $itype) {
		if (isset($def[$itype])) {
			fprintf($fp, "%s\t'%s' => array(\n", $lead, $itype);
			foreach ($def[$itype] as $kn => $kd) {
				fprintf($fp, "%s\t\t'%s' => array('%s'),\n", $lead, $kn, implode("', '", schema_check_array($kd)));
			}
			fprintf($fp, "%s\t),\n", $lead);
		}
	}

	// foreign keys
	if (isset($def['fkeys'])) {
		fprintf($fp, "%s\t'fkeys' => array(\n", $lead);
		foreach ($def['fkeys'] as $fkn => $fkd) {
			fprintf($fp, "%s\t\t'%s' => array(\n", $lead, $fkn);
			fprintf($fp, "%s\t\t\t'fields' => array('%s'),\n", $lead, implode("', '", schema_check_array($fkd['fields'])));
			fprintf($fp, "%s\t\t\t'reftable' => '%s',\n", $lead, @$fkd['reftable']);
			fprintf($fp, "%s\t\t\t'reffields' => array('%s'),\n", $lead, implode("', '", schema_check_array($fkd['reffields'])));
			if (isset($fkd['onupdate']))
				fprintf($fp, "%s\t\t\t'onupdate' => '%s',\n", $lead, $fkd['onupdate']);
			if (isset($fkd['ondelete']))
				fprintf($fp, "%s\t\t\t'ondelete' => '%s',\n", $lead, $fkd['ondelete']);
			fprintf($fp, "%s\t\t),\n", $lead);
		}
		fprintf($fp, "%s\t),\n", $lead);
	}

	// table engine
	if (isset($def['engine']))
		fprintf($fp, "%s\t'engine' => '%s',\n", $lead, $def['engine']);

	// table character set
	if (isset($def['charset']))
		fprintf($fp, "%s\t'charset' => '%s',\n", $lead, $def['charset']);

	// field renames
	if (isset($def['fldrenames'])) {
		fprintf($fp, "%s\t'fldrenames' => array(\n", $lead);
		foreach ($def['fldrenames'] as $dst => $src)
			fprintf($fp, "%s\t\t'%s' => array('%s'),\n", $lead, $dst, implode("', '", schema_check_array($src)));
		fprintf($fp, "%s\t),\n", $lead);
	}
	fprintf($fp, "%s)", $lead);
}

/** Prints out an entire schema. */
function schema_print($schema, $lead = "", $fp = NULL)
{
	if (!$fp) {
		if (defined('STDOUT'))
			$fp = STDOUT;
		else
			do_die("Error determining output FD");
	}
	fprintf($fp, "array(\n");

	fprintf($fp, "%s\t'tables' => array(\n", $lead);
	if (isset($schema['tables'])) foreach ($schema['tables'] as $tbl => $def) {
		fprintf($fp, "%s\t\t'%s' => ", $lead, $tbl);
		schema_print_table($def, $lead."\t\t", $fp);
		fprintf($fp, ",\n");
	}
	fprintf($fp, "%s\t),\n", $lead);

	fprintf($fp, "%s\t'views' => array(\n", $lead);
	if (isset($schema['views'])) foreach ($schema['views'] as $view => $def) {
		fprintf($fp, "%s\t\t'%s' => '%s',\n", $lead, $view, preg_replace('/([\'\\\\])/', '\\\\${1}', $def));
	}
	fprintf($fp, "%s\t),\n", $lead);

	fprintf($fp, "%s)", $lead);
}

/** Will attempt to ensure that a given type is valid, if it's not, it'll simply return NULL, otherwise the cleaned up (long) type */
/** TODO idea: actually move everything from NOT NULL onwards into a separate validation structure since this is near common for all types as far as I can tell.*/
function schema_validate_type($type, $defcharset = "latin1")
{
	if (preg_match('/^(tiny|small|medium|big)?int(eger)?(\([0-9]+\))?( unsigned)?( zerofill)?(( NOT)? NULL)?( default (\'[0-9]+\'|[0-9]+|NULL))?( auto_increment)?$/i', $type, $matches)) {
		$type = "int";
		$defsize = 11;
		if (!empty($matches[1])) {
			$type = strtolower($matches[1]).$type;
			switch (strtolower($matches[1])) {
			case "tiny": $defsize = 4; break;
			case "small": $defsize = 6; break;
			case "medium": $defsize = 9; break;
			case "big": $defsize = 20; break;
			default: do_die("Error deciding on default size for type '{$matches[1]}int'");
			}
		}

		if (!empty($matches[3]))
			$type .= $matches[3];
		else
			$type .= "(".$defsize.')';

		if (!empty($matches[4]))
			$type .= " unsigned";

		if (!empty($matches[5]))
			$type .= " zerofill";

		if (!empty($matches[7]) && !empty($matches[6]) || /* auto_increment columns may not be NULL */ !empty($matches[10]))
			$type .= " NOT NULL";
		elseif (empty($matches[8]))
			$type .= " default NULL"; // anything that is not NOT NULL should have default NULL unless a different default is set.

		if (!empty($matches[8])) {
			if ($matches[9][0] == "'" || strtoupper($matches[9]) == "NULL")
				$type .= " default ".$matches[9];
			else
				$type .= " default '".$matches[9]."'";
		}

		if (!empty($matches[10]))
			$type .= " auto_increment";

		return $type;
	} elseif (preg_match('/^(real|double|float|decimal|numeric)(\(([0-9]+)(,[0-9]+)\))?( unsigned)?( zerofill)?(( NOT)? NULL)?( default (\'[0-9]+([.][0-9]+)?\'|[0-9]+([.][0-9]+)?|NULL))?$/i', $type, $matches)) {
		switch (strtolower($matches[1])) {
		case 'real':
		case 'double':
			$type = "double";
			break;
		case 'float':
			$type = "float";
			break;
		case 'decimal':
		case 'numeric':
			$type = "decimal";
			if (empty($matches[2]))
				$type .= "(10,0)";
			break;
		default:
			do_die("Error handling type ".$matches[1]."\n");
		}

		if (!empty($matches[2]))
			$type .= "(".$matches[3].(empty($matches[4]) ? ",0" : $matches[4]).")";

		if (!empty($matches[5]))
			$type .= " unsigned";

		if (!empty($matches[6]))
			$type .= " zerofill";

		if (!empty($matches[8]) && !empty($matches[7]))
			$type .= " NOT NULL";
		elseif (empty($matches[9]))
			$type .= " default NULL"; // anything that is not NOT NULL should have default NULL unless a different default is set.

		if (!empty($matches[9])) {
			if ($matches[10][0] == "'" || strtoupper($matches[10]) == "NULL")
				$type .= " default ".$matches[10];
			else
				$type .= " default '".$matches[10]."'";
		}

		return $type;
	} elseif (preg_match('/^(date|time|timestamp|datetime)(( NOT)? NULL)?( default (\'(([0-9]{1,4})-([0-9]{1,2})-([0-9]{1,2})\b)? ?(\b([01]?[0-9]|2[0-3]):([0-5]?[0-9]):([0-5]?[0-9]))?\'|NULL|CURRENT_TIMESTAMP))?( on update CURRENT_TIMESTAMP)?$/i', $type, $matches)) {
		/* note that 'year' is explicitly not handled (it has a (size) option I don't know how to deal with yet). */
		$type = strtolower($matches[1]);

		if (strtolower($matches[1]) == "timestamp") {
			// there always has to be ONE bloody type for which the rules differ radically.
			if (empty($matches[2]) || !empty($matches[3]))
				$type .= " NOT";
			$type .= " NULL";
		} elseif (!empty($matches[2]) && !empty($matches[3]))
			$type .= " NOT NULL";
		elseif (empty($matches[4]))
			$type .= " default NULL";

		if (strtoupper($matches[4]) == "CURRENT_TIMESTAMP") {
			if (strtolower($matches[1]) != "timestamp")
				return NULL;
			$type .= " default CURRENT_TIMESTAMP";
		} elseif (strtoupper($matches[4]) == "NULL") {
			$type .= " default NULL";
		} elseif (!empty($matches[4])) {
			if (strtoupper($matches[5]) == "NULL") {
				$type .= " default NULL";
			} elseif (strtolower($matches[1]) == "timestamp" && strtoupper($matches[5]) == "CURRENT_TIMESTAMP") {
				$type .= " default CURRENT_TIMESTAMP";
			} else {
				$hasdate = in_array($matches[1], array("date", "timestamp", "datetime"));
				$hastime = in_array($matches[1], array("time", "timestamp", "datetime"));

				$type .= " default '";
				if ($hasdate)
					$type .= sprintf("%04d-%02d-%02d", $matches[7], $matches[8], $matches[9]);
				if ($hasdate && $hastime)
					$type .= " ";
				if ($hastime)
					$type .= sprintf("%02d:%02d:%02d", $matches[11], $matches[12], $matches[13]);
				$type .= "'";
			}
		}

		if (!empty($matches[14])) {
			if (strtolower($matches[1]) != "timestamp")
				return NULL;
			$type .= " on update CURRENT_TIMESTAMP";
		}

		if (strtolower($matches[1]) == "timestamp" && empty($matches[4]) && empty($matches[14]))
			$type .= " default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
		return $type;
	} elseif (preg_match('/^(tiny|medium|long)?(blob|text)( character set ([^\s]+))?( collate ([^\s]+))?(( NOT)? NULL)?( default (\'([^\\\\\']|\\\\.)+\'|NULL))?$/i', $type, $matches)) {
		$type = strtolower($matches[2]);
		$istext = ($type == "text");

		if (!empty($matches[1])) {
			$type = strtolower($matches[1]).$type;
		}

		if ($istext) {
			$type .= " character set ".(empty($matches[4]) ? $defcharset : $matches[4]);

			if (!empty($matches[5]))
				$type .= " collate ".$matches[6];
		}

		if (!empty($matches[7]) && !empty($matches[8]))
			$type .= " NOT NULL";
		elseif (empty($matches[9]))
			$type .= " default NULL"; // anything that is not NOT NULL should have default NULL unless a different default is set.

		if (!empty($matches[9])) {
			$type .= " default ";
			if (strtoupper($matches[10]) == "NULL")
				$type .= "NULL";
			else
				$type .= $matches[10];
		}

		return $type;
	} elseif (preg_match('/^(var)?(binary|char)(\([0-9]+\))?( character set ([^\s]+))?( collate ([^\s]+))?(( NOT)? NULL)?( default (\'([^\\\\\']|\\\\.)+\'|NULL))?$/i', $type, $matches)) {
		$type = strtolower($matches[2]);
		$ischar = ($type == "char");
		if (!empty($matches[1]))
			$type = "var".$type;

		$type .= empty($matches[3]) ? "(1)" : $matches[3];

		if ($ischar) {
			$type .= " character set ".(empty($matches[4]) ? $defcharset : $matches[5]);

			if (!empty($matches[6]))
				$type .= " collate ".$matches[7];
		} /* Note that this has the effect of stripping out CHARACTER SET and COLLATE options for binary types should they be (incorrectly so) set. */

		if (!empty($matches[8]) && !empty($matches[9]))
			$type .= " NOT NULL";
		elseif (empty($matches[10]))
			$type .= " default NULL"; // anything that is not NOT NULL should have default NULL unless a different default is set.

		if (!empty($matches[10])) {
			$type .= " default ";
			if (strtoupper($matches[11]) == "NULL")
				$type .= "NULL";
			else
				$type .= $matches[11];
		}

		return $type;
	} elseif (preg_match('/^(enum|set)\s*(\(\'([^\\\\\']|\\\\.)+\'(\s*,\s*\'([^\\\\\']|\\\\.)+\')*\))( character set ([^\s]+))?( collate ([^\s]+))?(( NOT)? NULL)?( default (\'([^\\\\\']|\\\\.)+\'|NULL))?$/i', $type, $matches)) {
		$type = strtolower($matches[1]);

		$type .= preg_replace("/([^\\\\])'\s*,\s*'/", "\\1','", $matches[2]);

		$type .= " character set ".(empty($matches[7]) ? $defcharset : $matches[7]);

		if (!empty($matches[9]))
			$type .= " collate ".$matches[9];

		if (!empty($matches[10]) && !empty($matches[11]))
			$type .= " NOT NULL";
		elseif (empty($matches[12]))
			$type .= " default NULL"; // anything that is not NOT NULL should have default NULL unless a different default is set.

		if (!empty($matches[12])) {
			$type .= " default ";
			if (strtoupper($matches[13]) == "NULL")
				$type .= "NULL";
			else
				$type .= $matches[13];
		}

		return $type;
	} else
		return NULL;
}

/**
 * This function will attempt to validate a table definition, it will also
 * cleanup certain type defs, eg int -> int(11) default NULL, or varchar(20)
 * -> varchar(20) charset latin1 default NULL */
/**
 *  return values:
 *  0 - no errors,
 *  1 - warnings issued (alterations made),
 *  2 - errors (invalid) */
/**
 * If a full schema is also provided then we can perform better FK checks */
function schema_validate_table($tblname, &$def, &$schema = NULL)
{
	$errlevel = 0;

	if (!isset($def['charset'])) {
		$def['charset'] = "latin1";
	}

	if (!isset($def['engine'])) {
		$def['engine'] = "InnoDB";
	} else {
		$found = false;
		foreach (array("MyISAM", "MEMORY", "InnoDB", "BerkeleyDB", "BLACKHOLE"/*, "EXAMPLE"*/, "ARCHIVE", "CSV", "ndbcluster", "FEDERATED", "MRG_MYISAM"/*, "ISAM"*/) as $tt) {
			if (strcasecmp($tt, $def['engine']) == 0) {
				$found = true;
				$def['engine'] = $tt;
				break;
			}
		}
		if (!$found) {
			do_warn("Invalid table engine '".$def['engine']."' requested, defaulting to InnoDB.");
			$def = "InnoDB";
		}
	}

	if (!isset($def['fields']) || !is_array($def['fields'])) {
		do_warn("table ".$tblname." contains no fields.");
		$def['fields'] = array();
	}

	foreach ($def['fields'] as $fn => $fd) {
		$nfd = schema_validate_type($fd, $def['charset']);
		if (!$nfd) {
			do_warn("table $tblname contains an invalid type of \"$fd\" for field $fn.");
			$errlevel = max($errlevel, 2);
		} elseif ($nfd != $fd) {
			do_warn("field $tblname.$fn updated to $nfd (from $fd).");
			$errlevel = max($errlevel, 1);
			$def['fields'][$fn] = $nfd;
		}
	}

	if (isset($def['pkey'])) {
		foreach (schema_check_array($def['pkey']) as $pkf) {
			if (!in_array($pkf, array_keys($def['fields']))) {
				do_warn("field '$pkf' as used in the PK definition of $tblname doesn't exist in the table definition.");
				$errlevel = max($errlevel, 2);
			} else {
				$fd = $def['fields'][$pkf];
				if (!preg_match("/ NOT NULL\b/i", $fd)) {
					do_warn("field '$pkf' which is part of the PK for $tblname should be NOT NULL.");
					$errlevel = max($errlevel, 1);
					$def['fields'][$pkf] = preg_replace("/( default\b.*)?$/i", " NOT NULL\\1", $fd, 1);
				}
			}
		}
	}

	$keynames = array();
	foreach (array("unique", "indexes") as $ktype) {
		if (!isset($def[$ktype]))
			continue;

		foreach ($def[$ktype] as $kn => $kd) {
			if (in_array($kn, $keynames)) {
				do_warn("Duplicate key name ".$kn." in table ".$tblname);
				$errlevel = max($errlevel, 2);
			} else
				$keynames[] = $kn;
			$def[$ktype][$kn] = $kd = schema_check_array($kd);
			foreach ($kd as $kf) {
				if (!isset($def['fields'][$kf])) {
					do_warn((($ktype == "unique") ? "Unique key" : "Index")." ".$kn." in table ".$tblname." references non-existing field ".$kf);
					$errlevel = max($errlevel, 2);
				}
			}
		}
	}

	if (isset($def['fkeys'])) {
		if ($def['engine'] != "InnoDB") {
			do_warn("Use of foreign keys requires InnoDB storage, updating storage engine for table ".$tblname);
			$errlevel = max($errlevel, 1);
			$def['engine'] = "InnoDB";
		}

		foreach ($def['fkeys'] as $fkn => $fkd) {
			if (in_array($fkn, $keynames)) {
				do_warn("Duplicate key name ".$fkn." in table ".$tblname);
				$errlevel = max($errlevel, 2);
			} else
				$keynames[] = $fkn;

			foreach (schema_check_array($fkd['fields']) as $kf) {
				if (!isset($def['fields'][$kf])) {
					do_warn("Foreign key ".$kn." in table ".$tblname." references non-existing field ".$kf);
					$errlevel = max($errlevel, 2);
				}
			}

			if (!isset($fkd['reftable'])) {
				do_warn("Foreign key ".$fkn." in table ".$tblname." doesn't reference another table");
				$errlevel = max($errlevel, 2);
			} elseif (!isset($fkd['reffields'])) {
				do_warn("Foreign key ".$fkn." in table ".$tblname." doesn't specify which fields in ".$fkd['reftable']." is being referenced.");
				$errlevel = max($errlevel, 2);
			} elseif (sizeof(schema_check_array($fkd['fields'])) != sizeof(schema_check_array($fkd['reffields']))) {
				do_warn("Field count for foreign key ".$fkn." is inconsistent (the number of referencing fields differ from the number of referenced fields.");
				$errlevel = max($errlevel, 2);
			} elseif ($schema) { /* We have a schema, we can perform more sophistecated checks */
				do_warn("No Schema checks are being done yet for table ".$tblname." (".__FILE__.":".__LINE__.")");
				// check that referenced table exists
				// check that referenced table has engine innodb
				// check that referenced fields exist
				// check that referencing and referenced fields has the same type (bar NOT NULL which we should strip out from the types before comparing - also, the referenced table may not yet have been validated, so we should validate each referenced type before doing the comparison).
			}
		}
	}

	if (isset($def['fldrenames'])) {
		$fromnames = array();
		foreach ($def['fldrenames'] as $nn => $ons) {
			$ons = schema_check_array($ons);

			if (!isset($def['fields'][$nn])) {
				do_warn("Attempt to rename to non-existing field ".$nn." in table ".$tblname." (assuming this is a typo and not stopping progress)");
			}

			foreach ($ons as $on) {
				if (isset($def['fields'][$on])) {
					do_warn("Ignoring attempt to rename from existing field ".$on." to ".$nn." in table ".$tblname);
				} elseif (in_array($on, $fromnames)) {
					do_warn("Multiple attempts to rename from field ".$on." in table ".$tblname." (new names include at least ".$nn." and ".$fromnames[$on].")");
					$errlevel = max($errlevel, 1);
				} else
					$fromnames[$on] = $nn;
			}
		}
	}

	return $errlevel;
}


/** Attempt to generate a table definition (structure) from an actual CREATE TABLE statement */
function schema_table_from_sql($sql, &$tblname, &$def)
{
	$def = array();

	$lines = explode("\n", $sql);

	$tbl_line = array_shift($lines);
	$tail_line = array_pop($lines);

	preg_match("/^CREATE TABLE `(.*)` \($/", $tbl_line, $parts) or do_die("Regex error (create table line) for: ".$sql);

	$tblname = $parts[1];

	preg_match("/^\) ENGINE=([^ ]*)( .*)? DEFAULT CHARSET=(.*);?$/", $tail_line, $parts) or do_die("Regex error");

	$def['engine'] = $parts[1];
	$def['charset'] = $parts[3];

	array_push($lines, array_pop($lines).",");

	$prim_key = array();
	$foreign_keys = array();
	$uniq_keys = array();
	$general_keys = array();

	foreach($lines as $l) {
		$l = trim($l);
		if ($l[0] == "`") {
			preg_match("/^`(.*)` (.*),$/", $l, $parts) or do_die("Regex error");
			$type = $parts[2];
			if (preg_match("/^((varchar|char|enum|set)\([^)]+\)( binary)?|(tiny|medium)?(text)) (.*)$/", $type, $typep) && !strpos($type, " character set "))
				$type = $typep[1]." character set ".$def['charset']." ".$typep[6];
			$def['fields'][$parts[1]] = $type;
		} else {
			$words = explode(' ', $l);
			switch($words[0]) {
			case 'PRIMARY':
				preg_match("/^.*\(`(.*)`\).*$/", $l, $parts) or do_die("Regex error");
				$def['pkey'] = schema_split_compound($parts[1]);
				break;
			case 'UNIQUE':
				preg_match("/^.*`(.*)` \(`(.*)`\).*$/", $l, $parts) or do_die("Regex error");
				$uniq_name = $parts[1];
				$uniq_parts = schema_split_compound($parts[2]);
				$def['unique'][$uniq_name] = $uniq_parts;
				break;
			case 'KEY':
				preg_match("/^.*`(.*)` \(`(.*)`\).*$/", $l, $parts) or do_die("Regex error");
				$key_name = $parts[1];
				$key_parts = schema_split_compound($parts[2]);
				$def['indexes'][$key_name] = $key_parts;
				break;
			case 'CONSTRAINT':
				preg_match("/^CONSTRAINT `(.*)` FOREIGN KEY \(`(.*)`\) REFERENCES `(.*)` \(`(.*)`\)( ON DELETE (CASCADE|SET NULL|NO ACTION|RESTRICT))?( ON UPDATE (CASCADE|SET NULL|NO ACTION|RESTRICT))?,$/", $l, $parts) or do_die("Regex error");
				$def['fkeys'][$parts[1]] = array(
					"fields" => schema_split_compound($parts[2]),
					"reftable" => $parts[3],
					"reffields" => schema_split_compound($parts[4]),
					"ondelete" => !empty($parts[6]) ? $parts[6] : "RESTRICT",
					"onupdate" => !empty($parts[8]) ? $parts[8] : "RESTRICT",
				);
				break;
			default:
				do_die("Error parsing line".$l);
			}
		}
	}

	return 0;
}

/**
 * This function will use the query_* functions as specified in the
 * Requirements section in order to construct a schema structure from the
 * live mysql database
 */
function schema_from_db_mysql($_flags = array())
{
	$schema = schema_init();
	$flags = array(
		'nogrants' => false,
	);
	foreach ($_flags as $f => $v)
		if (isset($flags[$f]))
			$flags[$f] = $v;

	list($dbname) = query_getrow("SELECT database() `0`");

	$tbls = query_getarray("SHOW TABLES");

	foreach ($tbls as $tbl) {
		$create_stm = query_getrow("SHOW CREATE TABLE ".$tbl);

		if (isset($create_stm['Create Table'])) {
			schema_add_table_from_sql($schema, $create_stm['Create Table']);
		} elseif (isset($create_stm['Create View'])) {
			schema_add_view_from_sql($schema, $create_stm['Create View']);
		} else {
			print_r($create_stm);
			do_die("Error handling unknown table-like structure.");
		}
	}

	$procs = query_getset("SHOW PROCEDURE STATUS WHERE Db=DATABASE()");
	foreach ($procs as $p) {
		do_warn("Need to analyze stored proc ".$p['Name']);
	}

	$funcs = query_getset("SHOW FUNCTION STATUS WHERE Db=DATABASE()");
	foreach ($funcs as $f) {
		do_warn("Need to analyze stored func ".$f['Name']);
	}

	$triggers = query_getset("SHOW TRIGGERS");
	foreach ($triggers as $t) {
		$schema['triggers'][$t['Trigger']] = array(
			"event" => strtolower($t['Event']),
			"timing" => strtolower($t['Timing']),
			"tbl" => $t['Table'],
			"stmt" => $t['Statement'],
		);
	}

	/* Get the grants that's SPECIFICALLY FOR the active DB */
	if (!$flags['nogrants']) {
		$users = query_getset("SELECT DISTINCT User, Host FROM mysql.user");
		foreach ($users as $u) {
			$grants = query_getarray("SHOW GRANTS FOR `".escape_sql($u['User'])."`@`".escape_sql($u['Host'])."`");

			foreach ($grants as $g) {
				preg_match("/^GRANT (.*) ON (['`](.*)['`]|\\*)[.@](['`](.*)[`']|\\*) TO '([^']*)'@'([^']*)'( IDENTIFIED BY PASSWORD '.*'| WITH GRANT OPTION)*$/", $g, $p) or do_die("Regex error for ".$g);

				/* Only for explicit grants on the current DB */
				if ($p[3] == $dbname) {
					$privs = explode(", ", $p[1]);
					$obj = ($p[4] == '*') ? $p[4] : $p[5];

					/* The split() above splits things like INSERT (foo, bar) which is one priv. */
					unset($cpriv);
					$rprivs = array();
					foreach ($privs as $pr) {
						if (isset($cpriv)) {
							$cpriv .= ", ".$pr;
							if (strpos($cpriv, ")") !== FALSE) {
								$rprivs[] = $cpriv;
								unset ($cpriv);
							}
						} else {
							if (strpos($pr, "(") !== FALSE) {
								$cpriv = $pr;
							} else {
								$rprivs[] = $pr;
							}
						}
					}

					$schema['grants'][$p[6].'@'.$p[7]][] = array(
						"privs" => $rprivs,
						"object" => $obj,
					);
				}
			}
		}
	}

	return $schema;
}

/** Function to compare two table structures, and return a list of modifications required to go from tbl A to tbl B */
function schema_compare_tables($a, $b)
{
	$tblmods = array();

	// refactor the array to be "forwarding" of nature (the referse is more useful for logical reasons)
	$renames = array();
	if (isset($b['fldrenames'])) {
		foreach ($b['fldrenames'] as $n => $onames)
			foreach (schema_check_array($onames) as $o)
				$renames[$o] = $n;
	}

	// an associative array containing the table's fields after drops + renames.
	$fields = array();

	// check for column renames + drops.
	foreach ($a['fields'] as $fldname => $fldtype) {
		if (!isset($b['fields'][$fldname])) {
			if (isset($renames[$fldname])) {
				// note that the rename is reverse (ie, tblmods[a][rename] = b implies b gets renamed to a)
				$tblmods['fields'][$renames[$fldname]]['rename'] = $fldname;
				$fields[$renames[$fldname]] = $fldtype;
			} else {
				$tblmods['fields'][$fldname]['mod'] = "drop";
			}
		} else {
			$fields[$fldname] = $fldtype;
		}
	}

	// check for new columns, column order and column types.
	/* Note that we need to keep two pre-existing colls because we might add columns and stuff */
	/* Re-orderings also mess with things a bit... */
	$prealcol = NULL;
	$pexistcol = NULL;
	$p = 0; // priority - we must (for renames) order updates left to right.
	$orig_order = array_keys($fields);
	$auto_inc_col = NULL; // we need to track this one in case of PK changes.

	foreach ($b['fields'] as $fldname => $fldtype) {
		if (preg_match("/ auto_increment\d/", $fldtype))
			$auto_inc_col = $fldname;

		$i = array_search($fldname, $orig_order);
		if ($i > 0) {
			$cpcol = $orig_order[$i - 1];
		} else {
			$cpcol = NULL;
		}

		if (!isset($fields[$fldname])) {
			$tblmods["fields"][$fldname]['mod'] = "add";
			$tblmods["fields"][$fldname]['order'] = $prealcol;
		} elseif ($fields[$fldname] != $fldtype || $pexistcol != $cpcol) {
			$tblmods["fields"][$fldname]['mod'] = "change";
			if ($pexistcol != $cpcol)
				$tblmods["fields"][$fldname]["order"] = $prealcol;
		}

		if (isset($tblmods["fields"][$fldname])) {
			$tblmods["fields"][$fldname]['type'] = $fldtype;

			if (!isset($tblmods["fields"][$fldname]['mod']))
				$tblmods["fields"][$fldname]['mod'] = "change"; /* rename ? */
		}

		if (isset($tblmods["fields"][$fldname]))
			$tblmods["fields"][$fldname]['prio'] = ++$p;

		if (!isset($tblmods['fields'][$fldname]['mod']) || $tblmods["fields"][$fldname]['mod'] != "add")
			$pexistcol = $fldname;

		$prealcol = $fldname;
	}

	// check that the primary keys are equal, if they are not, then we need to (drop the PK and re-add it).
	// this is tricky due to auto_increment keys.  auto_increment keys MUST at all times be part of the PK.
	// for now, ignore this and deal with it later.
	if (!$b['pkey']) {
		if ($a['pkey'])
			$tblmods['pkey']['final'] = array();
	} elseif (!$a['pkey'] && $b['pkey'] || schema_check_array($a['pkey']) != schema_check_array($b['pkey'])) {
		$tblmods['pkey']['final'] = schema_check_array($b['pkey']);
	}
	if (isset($tblmods['pkey']) && $auto_inc_col)
		$tblmods['pkey']['auto_inc_col'] = $auto_inc_col;

	// keys (un)fortunately doesn't have order issues.  So let's deal with our keys.

	//print_r($a);
	//print_r($b);
	//print_r($tblmods);

	return $tblmods;
}

/**
 * Compare two schemas and return a structure of changes required.
 * This merely list the changes, it does NOT generate the actual SQL
 * to make the modifications, or perform any kind of ordering.
 *
 * It assumes both schemas are valid.  IE:  FKs, types etc are valid.
 */
function schema_compare($s, $d)
{
	$diffs = array();
	if (isset($s['tables'])) foreach ($s['tables'] as $stn => $std) {
		if (isset($d['tables'][$stn])) {
			$diff = schema_compare_tables($std, $d['tables'][$stn]);
			if ($diff)
				$diffs['tables']['mod'][$stn] = $diff;
		} else {
			$diffs['tables']['drop'][] = $stn;
		}
	}

	if (isset($d['tables'])) foreach ($d['tables'] as $stn => $std) {
		if (!isset($s['tables'][$stn]))
			$diffs['tables']['add'][$stn] = $std;
	}

	return $diffs;
}

/** Functions to retrieve various bits from a schema */
function schema_get_table($s, $tblname) { return isset($s['tables'][$tblname]) ? $s['tables'][$tblname] : NULL; }
?>