xNightR00T File Manager

<?php

require_once dirname(__FILE__) . "/zs_component_upgrader.php";

global $ZSD_MySQL_62;
$ZSD_MySQL_62 =
	'CREATE TABLE IF NOT EXISTS ZSD_VHOSTS (
	`ID` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	`NAME` VARCHAR(256),
	`ADDRESS` VARCHAR(128),
	`PORT` INTEGER NOT NULL,
	`TEXT` TEXT,
	`CREATED_AT` integer not null,
	`LAST_UPDATED` integer not null,
	`DOCUMENT_ROOT` VARCHAR(256),
	`OWNER` integer not null,
	`CONFIG_FILE` varchar(256),
	`IS_SSL` integer not null,
	`TEMPLATE` TEXT,
	`IS_DEFAULT` integer not null default 0,
	`CRT_FILE` varchar(256) default "",
	`CRT_KEY_FILE` varchar(256) default "",
	`CRT_CHAIN_FILE` varchar(256) default "",
	`SSL_APP_NAME` varchar(256) default ""
	) ENGINE=MyISAM;
	CREATE TABLE IF NOT EXISTS ZSD_VHOSTS_NODES (
	`ID` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	`VHOST_ID` INTEGER NOT NULL,
	`NODE_ID` integer not null default 0,
	`STATUS` integer not null default 0,
	`STATUS_MESSAGE` TEXT
	)ENGINE=MyISAM;
	drop index ZSD_VHOSTS_IDX1 ON ZSD_VHOSTS;
	drop index ZSD_VHOSTS_IDX3 ON ZSD_VHOSTS;
	drop index ZSD_VHOSTS_NODES_IDX1 ON ZSD_VHOSTS_NODES;
	create index ZSD_VHOSTS_IDX1 on ZSD_VHOSTS(`NAME`,`PORT`);
	create unique index ZSD_VHOSTS_NODES_IDX1 on ZSD_VHOSTS_NODES(`NODE_ID`, `VHOST_ID`);
	';

// --------------------------------------------------------------------------------------
// SQLITE related
//---------------------------------------------------------------------------------------
global $ZSD_Sqlite_62;
$ZSD_Sqlite_62 =
	'CREATE TABLE IF NOT EXISTS ZSD_VHOSTS (
	`ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	`NAME` VARCHAR(256),
	`ADDRESS` VARCHAR(128),
	`PORT` INTEGER NOT NULL,
	`TEXT` TEXT,
	`CREATED_AT` integer not null,
	`LAST_UPDATED` integer not null,
	`DOCUMENT_ROOT` VARCHAR(256),
	`OWNER` integer not null,
	`CONFIG_FILE` varchar(256),
	`IS_SSL` integer not null,
	`TEMPLATE` TEXT,
	`IS_DEFAULT` integer not null default 0,
	`CRT_FILE` varchar(256) default "",
	`CRT_KEY_FILE` varchar(256) default "",
	`CRT_CHAIN_FILE` varchar(256) default "",
	`SSL_APP_NAME` varchar(256) default ""
	);
	CREATE TABLE IF NOT EXISTS ZSD_VHOSTS_NODES (
	`ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	`VHOST_ID` INTEGER NOT NULL,
	`NODE_ID` integer not null default 0,
	`STATUS` integer not null default 0,
	`STATUS_MESSAGE` TEXT
	);
	CREATE TABLE IF NOT EXISTS ZSD_TASKS (
	ZSD_TASKS_SEQUENCE INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	NODE_ID INTEGER NOT NULL,
	TASK_ID INTEGER,
	EXTRA_DATA TEXT,
	AUDIT_ID INTEGER,
	IN_PROGRESS INTEGER DEFAULT 0,
	OWNER_NODE_ID INTEGER DEFAULT 0
	);
	create index if not exists ZSD_VHOSTS_IDX1 on ZSD_VHOSTS(`NAME`,`PORT`);
	create unique index if not exists ZSD_VHOSTS_NODES_IDX1 on ZSD_VHOSTS_NODES(`NODE_ID`, `VHOST_ID`);
	CREATE INDEX        IF NOT EXISTS ZSD_TASKS_IDX1 ON ZSD_TASKS(NODE_ID);
	CREATE INDEX        IF NOT EXISTS ZSD_TASKS_IDX2 ON ZSD_TASKS(IN_PROGRESS);
	';

// --------------------------------------------------------------------------------------
// SQLITE related
//---------------------------------------------------------------------------------------
global $ZSD_Sqlite_63;
$ZSD_Sqlite_63 =
	'CREATE TABLE IF NOT EXISTS ZSD_EXTENSIONS (
	NAME VARCHAR(256) NOT NULL PRIMARY KEY UNIQUE,
	EXT_VERSION VARCHAR(512),
	IS_INSTALLED INTEGER,
	IS_LOADED INTEGER,
	INI_FILE VARCHAR(1024) NOT NULL DEFAULT "",
	IS_ZEND_COMPONENT INTEGER,
	IS_ZEND_EXTENSION INTEGER
	);

	CREATE INDEX IF NOT EXISTS ZSD_EXTENSIONS_IDX3 ON ZSD_EXTENSIONS(IS_ZEND_EXTENSION);';

class ZSDDbCreator extends ZSComponentUpgrader
{

	protected function getSqliteDbName($version = "6.0.0") {
		return "zsd.db";
	}

	protected function getMysqlSqlFilenames() {
		return array(
				   "zsd_mysql_create_schema.sql"
				   , "mysql_create_pagecache_rules_db.sql"
				   , "mysql_create_monitor_rules_db.sql"
				   , "create_monitor_rules_fixtures.sql"
				   , "mysql_create_message_center_db.sql"
			   );
	}

	protected function getSqliteSqlFilenames() {
		return array(
				   "zsd_sqlite_create_schema.sql"
				   , "create_pagecache_rules_db.sql"
				   , "create_monitor_rules_db.sql"
				   , "create_monitor_rules_fixtures.sql"
				   , "sqlite_create_message_center_db.sql"
			   );
	}


	public function getComponent() { return "zsd"; }

	public function getSchemaVersion() { return "2018.0.0"; }

	 /**
	 * @return the current database schema version of ZSD
	 * @param $dbh PDO
	 */
	public function getMysqlSchemaVersion(  ) {
		$dbh = $this->getMySqlDbh();
		try {
			$res = $dbh->query("SELECT property_value from schema_properties where property = 'ZSD_VERSION'");
			foreach ($res as $row) {
				return $row['property_value'];
			}
			return "9.0.0";

		} catch (PDOException $e) {
			error( $e->getMessage() );
			return  "9.0.0";
		}
	}

	/**
	 *
	 * @param PDO $dbh
	 * @param string $version
	 */
	public function setMysqlSchemaVersion($dbh, $version) {
		try {
			$dbh->exec("REPLACE INTO schema_properties VALUES ('ZSD_VERSION', '" . $version . "')");
		} catch (Exception $ex) {
			error("Cannot update schema version - " . $ex->getMessage());
			return false;
		}

		return true;
	}

	public function getSqliteSchemaVersion() {
		$dbh = $this->getSqliteDbh();
		$version = $this->getCurrentSchemaVersionFromDB($dbh);

		return $this->getParsedVersionString($version);
	}

	public function setSqliteSchemaVersion($dbh, $version) {
		$this->setCurrentDbSchemaVersion($dbh, $version);
	}


	protected function importLicenseFromBlueprint() {

		if ($this->getUpgradeFromVersion() != "5.6.0") {
			message("Not an upgrade from 5.6.0 - no need to import license");
			return;
		}

		message("Importing license from 6.0.0 blueprint if exists");

		$dbh = $this->getUpgradedMySqlDbh();
		if (!$dbh) {
			$dbh = $this->getSqliteDbh("6.0.0", false);
			if ($dbh) {
				message("Importing from SQLITE DB");
			} else {
				message("6.0.0 blueprint not found");
				return false;
			}
		} else {
			message("Importing from MYSQL DB");
		}

		if ($dbh) {
			$serial = "";
			if ($this->isTableExists($dbh, "ZSD_DIRECTIVES")) {
				$res = $dbh->query("select * from ZSD_DIRECTIVES WHERE NAME IN ('zend.serial_number', 'zend.user_name')")->fetchAll();
				if ($res) {
					foreach ($res as $row) {

						if ($row['NAME'] == 'zend.serial_number') {
							$serial = $row['DISK_VALUE'];
						} else if ($row['NAME'] == 'zend.user_name') {
							$user = $row['DISK_VALUE'];
						}
					}
				}
			}

			if ($serial) {
				message("License found ($user)");
				$this->updateLicense($user, $serial);
				return $user;
			} else {
				message("Could not find license in blueprint");
			}
		}

		return false;
	}

	public function updateVhostNginxConfFiles() {
		/*
		message("Updating nginx conf files in case fastcgi configuration");
		if ( $this->isWindows() ) {
			// nothing to be done on Windows OS as Nginx is not installed there
			message( "updateVhostNginxConfFiles(): nothing to be done on Windows" );
			return;
		}

		$basedir = $_GET['zsDir'] . "/etc/sites.d";
		foreach (glob( "$basedir/vhost_*.conf" ) as $filename) {
			if ( $this->isNginxVhostFile( $filename ) ) {
				$fileContent = file_get_contents( $filename );
				// $filename contains an nginx vhost definition
				// search for the include fastcfgi.conf
				if ( !preg_match( "/include.*?fastcgi\.conf/", $fileContent) ) {
					// could not locate the "include fastcgi.conf line"
					// add one
					$replacement = "$1" . PHP_EOL . "    include fastcgi.conf;" . PHP_EOL;
					$find_what   = '/(server[ \t\n\r]*\{)/';
					$fileContent = preg_replace( $find_what, $replacement, $fileContent );
					file_put_contents( $filename, $fileContent );
					message( "Added include fastcgi.conf to file: $filename " );
				}
			}
		}
		*/
	}

	protected function upgradeSqlite() {

		$upgradeFromVersion = $this->copySqliteFromPreviousVersions();
		$clearBlueprint = file_exists($this->getSqliteDbPath());
		$createSchema = true;

		if (!$upgradeFromVersion || $this->getUpgradeFromVersion() == "5.6.0") {
			if (file_exists($this->getSqliteDbPath())) {
				message("Database at " . $this->getSqliteDbPath() . " already exists");
				$clearBlueprint = true;
				$createSchema = false;

			} else {
				$this->createCleanSqliteDatabase();
				$createSchema = false;
				$clearBlueprint = false;

				$this->importPageCacheRules($this->getSqliteDbh());
			}
		}

		if ($createSchema && !file_exists($this->getSqliteDbPath())) {
			$this->createCleanSqliteDatabase();
		}

		if ($clearBlueprint && file_exists($this->getSqliteDbPath())) {
			message("Database at " . $this->getSqliteDbPath() . " already exists - clearing blueprint");
			$this->clearBlueprint($this->getSqliteDbh());
		}


		// upgrade to 6.1
		$dbh = $this->getSqliteDbh();

		// First make sure we got the schema version table
		$this->addSQLiteZSDSchemaVersionTableIfNeeded($dbh);

		$currentSchemaVersion = $this->getCurrentSchemaVersionFromDB($dbh);
		message( "Current ZSD schema version is: " . $currentSchemaVersion );

		if (($currentSchemaVersion <= 9100) && ($currentSchemaVersion >= 9000)) {
			// upgrade to 9.0 (schema version 9000)
			$currentSchemaVersion = $this->getCurrentSchemaVersionFromDB($dbh);
			if ($currentSchemaVersion && ($currentSchemaVersion < 9000) ) {
				$this->upgradeTo90( $dbh, true  ); // since sqlite is typeless, we don't really need to alter the db
				$this->setCurrentDbSchemaVersion( $dbh, 9000 );
			}

			$currentSchemaVersion = $this->getCurrentSchemaVersionFromDB($dbh);
			if ($currentSchemaVersion && ($currentSchemaVersion < 9100) ) {
				$this->upgradeTo91( $dbh, true  ); // since sqlite is typeless, we don't really need to alter the db
			}
			message( "Converting zsd schema version to \"9.1.0\"");
			$this->setCurrentDbSchemaVersion( $dbh, "9.1.0" );
		}

		$currentSchemaVersion = $this->getCurrentSchemaVersionFromDB($dbh);
		message("Schema version from the database is: " . $currentSchemaVersion);
		if ($currentSchemaVersion && (version_compare($currentSchemaVersion, "2018.0.0", "<"))) {
			$this->upgradeTo92($dbh, true);
			$this->setCurrentDbSchemaVersion( $dbh, "2018.0.0" );
		}
		$this->removeObsoleteINIDirectivesFromDB($dbh);

		//$upgrade2019_0_0 = new ZSDDbCreator_2019_0_0();
	//	$upgrade2019_0_0->upgradeSqlite();
	}

	/**
	 * set the current database version
	 * @param $dbh PDO
	 * @param $versionNumber integer
	 */
	protected function setCurrentDbSchemaVersion( $dbh, $versionNumber ) {
		$stmnt = $dbh->prepare("REPLACE INTO ZSD_SCHEMA_PROPERTIES (NAME, VERSION_NUMBER) VALUES ( 'schema_version', :VERSION_NUMBER )");
		$stmnt->bindValue(":VERSION_NUMBER", $versionNumber);
		$stmnt->execute();
	}

	/**
	 * @return check if a zend_optimizerplus -> opcache upgrade is needed
	 */
	public function upgradeOPCache() {
		message( "Upgrading ZendOpitmizerPlus to opcache ");
		$optimizerplusIni = $this->getConfdDir() . DIRECTORY_SEPARATOR . "optimizerplus.ini";
		$contentToAdd = "";
		if ( file_exists( $optimizerplusIni ) && is_readable( $optimizerplusIni) ) {
			// Migrating to OPCache from ZendOptimizerPlus
			$directives = parse_ini_file( $optimizerplusIni, false );

			// Prepare the target
			$targetFile = "";
			if ( stripos(PHP_OS, "windows") === FALSE ) {
				message("Current OS is: " . PHP_OS );
				$targetFile = $this->getConfdDir() . DIRECTORY_SEPARATOR . "opcache.ini";

			} else {
				message("Current OS is: " . PHP_OS );
				$targetFile = $this->getEtcDir() . DIRECTORY_SEPARATOR . "php.ini";

			}

			foreach ( $directives as $key => $value ) {
				$new_key = str_replace("zend_optimizerplus.", "opcache.", $key);
				if ( get_cfg_var( $new_key ) === FALSE ) {
					// opcache.* does not exist, this means we want to add it...
					// TODO :: YGM says: "He who does not escape the value shall explode!"
					message( "Will keep ini directive $key => $new_key" );
					$contentToAdd .= "$new_key=$value" . PHP_EOL;
				}
			}

			if ( strlen($contentToAdd) ) {
				// append its content to the $targetFile
				file_put_contents( $targetFile, PHP_EOL . $contentToAdd, FILE_APPEND);
			}

			// Rename the old optimizerplus.ini to something meaningful (.old)
			message( "Backing up $optimizerplusIni to $optimizerplusIni.old" );
			rename( $optimizerplusIni, $optimizerplusIni . ".old" );

		} else {
			message( "Upgrading ZendOpitmizerPlus to opcache - is not needed ( could not locate file $optimizerplusIni)");
		}
	}

	/**
	 * @param $dbh PDO
	 */
	protected function upgradeTo63( $dbh, $isSqlite ) {
		message( "Upgrading ZSD database to 6.3" );


		// In ZS6.3 we removed PDO_OCI
		// Make sure its not in the database...
		try {
			if ( $isSqlite ) {
				$dbh->exec("DROP TABLE ZSD_EXTENSIONS");
				global $ZSD_Sqlite_63;
				message("Upgrading ZSD_EXTENSIONS table ... (for SQLite)");
				execQueries($dbh, $ZSD_Sqlite_63);

			} else {
				message("Upgrading ZSD_EXTENSIONS table ... (for Mysql)");
				$dbh->exec("ALTER TABLE ZSD_EXTENSIONS ADD COLUMN IS_ZEND_EXTENSION INTEGER DEFAULT 0");
			}

			message( "Remove PDO_OCI entry from ZSD_EXTENIONS" );
			$dbh->exec( "delete from ZSD_EXTENSIONS where NAME = 'PDO_OCI'" );

		} catch ( PDOException $e ) { }
	}
	/**
	 * @param $dbh PDO
	 */
	protected function upgradeTo90( $dbh, $isSqlite ) {
		message( "Upgrading ZSD database to 9.0" );
		if($isSqlite) {
			message( "Upgrade to 9.0 is not required for SQLite" );
			return;
		}

		try {
			// MYSQL
			$this->execQueriesDiscardDuplicates($dbh, "alter table ZSD_MESSAGES modify COLUMN DETAILS TEXT");
		} catch ( PDOException $e ) { }
	}

	/**
	 * go over the schema tables and change the datatype of the auto-increment field from INT to BIGINT
	 * (MySQL only)
	 * @param \PDO $dbh
	 * @param bool $isSqlite
	 */
	protected function alterColumnType($dbh, $isSqlite) {
		message("alterColumnType called");
		if($isSqlite) {
			message("SQLITE mode, nothing to be done");
			return;
		}

		try {
			$schema = $this->getDbSettings()->getDbName();
			$sql = "SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
					WHERE DATA_TYPE = 'int'
					AND EXTRA like '%auto_increment%'
					AND TABLE_SCHEMA = '" . $schema . "'";
			$res = $dbh->query($sql);
			if($res == false) return;
			foreach($res as $line) {
				// For each table, alter the datatype
				$tableName = $line[0];
				$colName = $line[1];
				$sql = "ALTER TABLE $tableName MODIFY $colName BIGINT AUTO_INCREMENT NOT NULL";
				message($sql);
				$dbh->exec($sql);
			}

		} catch(PDOException $e) {
		}
	}

	/**
	 * @param \PDO $db
	 */
	protected function upgradeTo92($dbh, $isSqlite) {
		try {

			if(!$isSqlite) {
				message( "Upgrading ZSD database to 2019.0.0 (MySQL)" );
			} else {
				message("Upgrading ZSD database to 2019.0.0 (SQLite)");
			}

			$dbh->exec("ALTER TABLE ZSD_DIRECTIVES ADD REQUIRES_RESTART INTEGER DEFAULT 1;");

			$dbh->exec("ALTER TABLE ZSD_PAGECACHE_RULES ADD ENABLE INTEGER DEFAULT 1;");

			$dbh->exec("ALTER TABLE ZSD_PAGECACHE_RULES ADD PRIORITY INTEGER NOT NULL DEFAULT -1;");
			if ($isSqlite) {
				$dbh->exec("UPDATE ZSD_PAGECACHE_RULES set PRIORITY = (select count(*) from ZSD_PAGECACHE_RULES as cnt_t  where cnt_t.NAME >= ZSD_PAGECACHE_RULES.NAME and cnt_t.APP_ID = ZSD_PAGECACHE_RULES.APP_ID)");
			} else {
				$dbh->exec("UPDATE ZSD_PAGECACHE_RULES AS main
							LEFT JOIN (
							SELECT main.*, COUNT(cnt_t.rule_id) AS counter
							FROM ZSD_PAGECACHE_RULES AS main
							LEFT JOIN ZSD_PAGECACHE_RULES AS cnt_t ON cnt_t.NAME >= main.NAME AND cnt_t.APP_ID = main.APP_ID
							GROUP BY main.rule_id
							) AS t ON t.NAME >= main.NAME AND t.APP_ID = main.APP_ID SET main.priority = t.counter");
			}
			$dbh->exec("CREATE UNIQUE INDEX ZSD_PAGECACHE_RULES_APP_ID_IDX ON ZSD_PAGECACHE_RULES (APP_ID,PRIORITY)");

			$dbh->exec("ALTER TABLE ZSD_VHOSTS ADD APPLICATION_POOL VARCHAR(256)");

			// Add UNIQUE_NAME column with default values
			$dbh->exec("ALTER TABLE ZSD_VHOSTS ADD UNIQUE_NAME VARCHAR(256)");
			$dbh->exec("ALTER TABLE ZSD_VHOSTS ADD VHOST_TYPE integer not null default 0");
			$dbh->exec("ALTER TABLE ZSD_VHOSTS ADD REAL_DOCUMENT_ROOT VARCHAR(256)");
			$dbh->exec("ALTER TABLE ZSD_VHOSTS ADD ORIG_DOCUMENT_ROOT VARCHAR(256)");
			$dbh->exec("update ZSD_VHOSTS Set REAL_DOCUMENT_ROOT=(select DOCUMENT_ROOT);");
			$dbh->exec("update ZSD_VHOSTS Set ORIG_DOCUMENT_ROOT=(select DOCUMENT_ROOT);");
			if($isSqlite) {
				$dbh->exec("update ZSD_VHOSTS Set UNIQUE_NAME=(select printf('%s:%d', NAME, PORT));");
			} else {
				$dbh->exec("update ZSD_VHOSTS Set UNIQUE_NAME=(select concat(NAME, ':', PORT));");
			}
			$dbh->exec("CREATE UNIQUE INDEX ZSD_VHOSTS_UNIQUE_NAME_IDX ON ZSD_VHOSTS (UNIQUE_NAME)");

			if (!$isSqlite) {
				$dbh->exec("CREATE TABLE IF NOT EXISTS ZSD_VHOSTS_BINDINGS (
				`ID` BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
				`VHOST_ID` INTEGER NOT NULL,
				`IS_SSL` integer not null,
				`HOSTNAME` VARCHAR(128),
				`PORT` integer not null,
				`ADDRESS` VARCHAR(128)
				) Engine=Innodb DEFAULT CHARSET=latin1;
				CREATE UNIQUE INDEX ZSD_VHOSTS_BINDINGS_IDX1 ON ZSD_VHOSTS_BINDINGS(VHOST_ID, PORT, ADDRESS);");
			} else {
				$dbh->exec("CREATE TABLE IF NOT EXISTS ZSD_VHOSTS_BINDINGS (
				`ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
				`VHOST_ID` INTEGER NOT NULL,
				`IS_SSL` integer not null,
				`HOSTNAME` VARCHAR(128),
				`PORT` integer not null,
				`ADDRESS` VARCHAR(128)
				);
				CREATE UNIQUE INDEX IF NOT EXISTS ZSD_VHOSTS_BINDINGS_IDX1 ON ZSD_VHOSTS_BINDINGS(VHOST_ID, PORT, ADDRESS);");
			}

			$dbh->query("INSERT INTO ZSD_VHOSTS_BINDINGS SELECT NULL, ID, IS_SSL, NAME, PORT, ADDRESS FROM ZSD_VHOSTS");
			$dbh->query("UPDATE ZSD_VHOSTS_BINDINGS SET HOSTNAME = '' WHERE HOSTNAME = '*'");

			if ($isSqlite) {
				$dbh->exec("	drop index if exists ZSD_VHOSTS_IDX1;
								drop index if exists ZSD_VHOSTS_IDX2;
								drop index if exists ZSD_VHOSTS_IDX3;
							");
				$this->removeSqliteColumns($dbh, "ZSD_VHOSTS", array("ADDRESS", "PORT"));
			} else {
				$dbh->exec("	drop index ZSD_VHOSTS_IDX1 ON ZSD_VHOSTS;
								drop index ZSD_VHOSTS_IDX2 ON ZSD_VHOSTS;
								drop index ZSD_VHOSTS_IDX3 ON ZSD_VHOSTS;
							");
				$this->removeMysqlColumns($dbh, "ZSD_VHOSTS", array("ADDRESS", "PORT"));
			}

			$res = $dbh->query("SELECT INI_FILE FROM ZSD_DIRECTIVES WHERE NAME='zend_statistics_extension.log_verbosity_level'");
			if ($res) {
				foreach ($res as $row) {
					$statsExtPath = $row['INI_FILE'];
				}
				if (isset($statsExtPath)) {
					$statsPath = str_replace("statistics_ext.ini", "statistics.ini", $statsExtPath);
					message("Replacing statistics_ext.ini entries with statistics.ini");
					execQuery($dbh, "UPDATE ZSD_DIRECTIVES SET INI_FILE='{$statsPath}' WHERE INI_FILE LIKE '%statistics_ext%'");

					message("Inserting statistics_ext contents to statistics.ini");
					$statsExtContents = file_get_contents($statsExtPath);
					file_put_contents($statsPath, $statsExtContents, FILE_APPEND);
				}
			}

		} catch ( PDOException $e ) {
			error($e->getMessage());
			return;
		}
	}


	/**
	 * @param \PDO $dbh
	 */
	protected function upgradeTo91($dbh, $isSqlite) {
		try {
			if(!$isSqlite) {
				// MySQL/MariaDB only
				// Create the new table ZSD_METADATA_TABLE and add triggers
				message( "Upgrading ZSD database to 9.1 (MySQL)" );

				// MYSQL
				$zsdMySqlFilesTableSql =
				'CREATE TABLE IF NOT EXISTS `ZSD_FILES` (
					`ID` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
					`NAME` VARCHAR(128),
					`LINE_NUMBER` INTEGER DEFAULT 0,
					`CONTENT` TEXT
				) Engine=Innodb DEFAULT CHARSET=latin1;
				';

				$zsdMySqlFilesTableSqlIndex1 =
				'CREATE UNIQUE INDEX ZSD_FILES_IDX1 ON ZSD_FILES(`NAME`, `LINE_NUMBER`);';

				$zsdMetadataSql =
				'CREATE TABLE IF NOT EXISTS `ZSD_METADATA_TABLE` (
					`TABLE_NAME` VARCHAR(128) NOT NULL PRIMARY KEY UNIQUE,
					`LAST_UPDATED` INTEGER DEFAULT 0
				) ENGINE=Innodb;
				';

				execQuery($dbh, $zsdMetadataSql);
				execQuery($dbh, $zsdMySqlFilesTableSql);

				// Add the index if not exists
				execQueriesDiscardDuplicates($dbh, $zsdMySqlFilesTableSqlIndex1);

				// Execute the fixtures
				$this->alterColumnType($dbh, false);

				// Change ZSD_MESSAGES::MSG_KEY to VARCHAR(128) from VARCHAR(64)
				execQuery($dbh, "alter table ZSD_MESSAGES modify COLUMN MSG_KEY VARCHAR(128)");

			} else {
				message("Upgrading ZSD database to 9.1 (SQLite)");

				// SQLITE
				$zsdSqliteFilesTableSql =
				'CREATE TABLE IF NOT EXISTS ZSD_FILES (
					ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
					NAME VARCHAR(128) DEFAULT "",
					LINE_NUMBER INTEGER DEFAULT 0,
					CONTENT TEXT DEFAULT ""
				);
				CREATE UNIQUE INDEX IF NOT EXISTS ZSD_FILES_IDX1 ON ZSD_FILES(NAME, LINE_NUMBER);
				';

				// Use the plural version
				execQueries($dbh, $zsdSqliteFilesTableSql);

				// Change ZSD_MESSAGES::MSG_KEY to VARCHAR(128) from VARCHAR(64)
				// In SQLite we need to drop and recreate the table
				$zsdSqliteAlterMessagesTable =
				'drop table if exists ZSD_MESSAGES;
				CREATE TABLE IF NOT EXISTS ZSD_MESSAGES (
					MSG_ID       INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
					NODE_ID      INTEGER NOT NULL,
					CONTEXT      INTEGER,
					MSG_KEY      VARCHAR(128),
					MSG_SEVERITY INTEGER NOT NULL,
					DETAILS      TEXT,
					TYPE         INTEGER NOT NULL
				);
				';
				execQueries($dbh, $zsdSqliteAlterMessagesTable);
			}

			// common to both databases
			execQuery($dbh, "REPLACE INTO ZSD_NOTIFICATIONS_ACTIONS (TYPE, NAME) VALUES (55,  'routeCanBeDisabled')");
			execQuery($dbh, "REPLACE INTO ZSD_NOTIFICATIONS_ACTIONS (TYPE, NAME) VALUES (56,  'routeCanBeEnabled')");
			execQuery($dbh, "REPLACE INTO ZSD_NOTIFICATIONS_ACTIONS (TYPE, NAME) VALUES (57,  'CodeTracingWriteFailed')");
			execQuery($dbh, "REPLACE INTO ZSD_NOTIFICATIONS_ACTIONS (TYPE, NAME) VALUES (58,  'statsdOffline')");

		} catch ( PDOException $e ) { }
	}


	/**
	 * remove directive from ZSD_DIRECTIVES table
	 * @param <unknown> \PDO
	 * @return
	 */
	protected function removeDirectiveFromDB($db, $directiveName) {
		try {
			message("Deleting directive $directiveName from the database");
			execQuery($db, "delete from ZSD_DIRECTIVES where NAME='$directiveName'");
		} catch(PDOException $e) {}
	}

	/**
	 * @param $dbh PDO
	 */
	protected function upgradeTo62( $dbh, $isSqlite ) {
		message( "Upgrading ZSD database to 6.2" );

		if ( $isSqlite ) {
			// We changed the type of the column EXTRA_DATA to TEXT
			// since sqlite does not support modify column, we need to drop the table
			// and recreate it
			message("Dropping table ZSD_TASKS...");
			$dbh->exec("drop table ZSD_TASKS");
			message("Dropping table ZSD_TASKS... done");

			global $ZSD_Sqlite_62;
			message("Creating ZSD_VHOSTS, ZSD_VHOSTS_NODES and ZSD_TASKS tables and indexes... (for SQLite)");
			execQueries($dbh, $ZSD_Sqlite_62);
			message("Creating ZSD_VHOSTS, ZSD_VHOSTS_NODES and ZSD_TASKS tables and indexes... (for SQLite)..done");

		} else {
			global $ZSD_MySQL_62;
			message("Creating VHOSTS tables and indexes... (for MySQL)");
			execQueries($dbh, $ZSD_MySQL_62);
			message("Creating VHOSTS tables and indexes...done");

		}

		// Execute the "alter table" with the execQueriesDiscardDuplicates which ignores duplicate entries
		$this->execQueriesDiscardDuplicates( $dbh, "ALTER TABLE ZSD_NODES_PROFILE ADD ZENDSERVER_VERSION VARCHAR(128) NOT NULL DEFAULT ''" );

		// Add new notifications to the table ZSD_NOTIFICATIONS_ACTIONS
		$dbh->exec( "REPLACE INTO ZSD_NOTIFICATIONS_ACTIONS (TYPE, NAME) VALUES (38, 'noSupport')");
		$dbh->exec( "REPLACE INTO ZSD_NOTIFICATIONS_ACTIONS (TYPE, NAME) VALUES (39, 'librarySetDefaultError')");
		$dbh->exec( "REPLACE INTO ZSD_NOTIFICATIONS_ACTIONS (TYPE, NAME) VALUES (40, 'libraryDeployError')");
		$dbh->exec( "REPLACE INTO ZSD_NOTIFICATIONS_ACTIONS (TYPE, NAME) VALUES (41, 'libraryRemoveError')");
		$dbh->exec( "REPLACE INTO ZSD_NOTIFICATIONS_ACTIONS (TYPE, NAME) VALUES (42, 'vhostMissing')");
		$dbh->exec( "REPLACE INTO ZSD_NOTIFICATIONS_ACTIONS (TYPE, NAME) VALUES (43, 'vhostModified')");
		$dbh->exec( "REPLACE INTO ZSD_NOTIFICATIONS_ACTIONS (TYPE, NAME) VALUES (44, 'webserverConfigTestFailed')");

		// Need to modify the column type
		if ( !$isSqlite ) {
			$this->execQueriesDiscardDuplicates($dbh, "alter table ZSD_TASKS modify COLUMN EXTRA_DATA TEXT");
		}

		// Add column SERVER_FLAGS to ZSD_NODES table
		$this->execQueriesDiscardDuplicates($dbh, "ALTER TABLE ZSD_NODES ADD SERVER_FLAGS INTEGER DEFAULT 0");
	}

	/**
	 * Upgrade ZSD database from 6.0.1/0 to 6.1
	 * @param PDO $dbh
	 */
	protected function upgradeTo61( $dbh ) {
		message( "Upgrading ZSD database to 6.1" );

		// In ZS 6.1 the blueprint should no longer contain
		// Extension Manager 'dir' directives
		message( "Deleting zend_extension_manager.dir.* directives from ZSD_DIRECTIVES table..." );
		$dbh->exec("DELETE FROM ZSD_DIRECTIVES WHERE NAME LIKE 'zend_extension_manager.dir.%'");

		// Fix apc entry in the database
		$apcLoaded    = $this->isAPCLoaded( $dbh );
		$apcInstalled = $this->isAPCInstalled($dbh);

		message( "Current APC status: installed: " . ($apcInstalled ? "Yes" : "No") . ", loaded: " . ($apcLoaded ? "Yes" : "No") );
		message( "Upgrading APC entries..." );

		if ( $apcInstalled ) {
			// Don't touch the entry in the database, only add new one
			// with the string 'APC Compatibility Layer'
			$this->addAPCExntesionEntry( $dbh, 'APC Compatibility Layer', false, 'apc_compatibility_layer.ini' );

		} else if ( $apcLoaded && !$apcInstalled ) {
			// APC is loaded but not installed
			// -> the apc entry is actually representing the fake APC
			// update this entry to 'not loaded' and add new 'APC Compatibility Layer' entry
			$this->addAPCExntesionEntry($dbh, 'APC Compatibility Layer', true, 'apc_compatibility_layer.ini');
			$this->addAPCExntesionEntry($dbh, 'apc', false, 'apc.ini');

		} else {
			// not loaded and not installed.
			// Just add the 'APC Compatibility Layer entry
			$this->addAPCExntesionEntry($dbh, 'APC Compatibility Layer', false, 'apc_compatibility_layer.ini');
		}
	}
	/**
	 * @param PDO $dbh
	 */
	protected function addSQLiteZSDSchemaVersionTableIfNeeded( $dbh ) {
		// if the table exists, do nothing
		if ( $this->isTableExists($dbh, "ZSD_SCHEMA_PROPERTIES") ) {
			message( "ZSD_SCHEMA_PROPERTIES table already exists - will not add it" );
			return;
		}

		message( "ZSD_SCHEMA_PROPERTIES table does not exist - will add it" );
		// Create the table
		$stmnt = $dbh->prepare("CREATE TABLE IF NOT EXISTS ZSD_SCHEMA_PROPERTIES (NAME VARCHAR(256) NOT NULL PRIMARY KEY UNIQUE, VERSION_NUMBER INTEGER)");
		$stmnt->execute();

		message( "Updating ZSD schema version to 6001" );

		$stmnt2 = $dbh->prepare("INSERT OR IGNORE INTO ZSD_SCHEMA_PROPERTIES (NAME, VERSION_NUMBER) VALUES ( 'schema_version', 6001 )");
		$stmnt2->execute();
	}

	/**
	 * return the current database schema from the database
	 * @param PDO $dbh
	 */
	protected function getCurrentSchemaVersionFromDB( $dbh ) {
		$res = $dbh->query("SELECT VERSION_NUMBER from ZSD_SCHEMA_PROPERTIES where NAME = 'schema_version'");
		foreach ($res as $row) {
			return $row['VERSION_NUMBER'];
		}
		/// default value 6.0.1
		return "6.0.1";
	}

	/**
	 * @brief insert or replace entry into the ZSD_EXTENSIONS table
	 * @param PDO $dbh
	 * @param bool $loaded
	 * @param string $iniFile
	 */
	protected function addAPCExntesionEntry( $dbh, $name, $loaded, $iniFile ) {
		$stmt = $dbh->prepare("REPLACE INTO ZSD_EXTENSIONS (NAME, IS_INSTALLED, IS_LOADED, INI_FILE, IS_ZEND_COMPONENT) VALUES (:NAME, :IS_INSTALLED, :IS_LOADED, :INI_FILE, :IS_ZEND_COMPONENT)");
		$stmt->bindValue(":NAME",              $name);
		$stmt->bindValue(":IS_INSTALLED",      0);
		$stmt->bindValue(":IS_LOADED",         ($loaded ? 1 : 0));
		$stmt->bindValue(":INI_FILE",          (string) $iniFile);
		$stmt->bindValue(":IS_ZEND_COMPONENT", 0);
		$stmt->execute();
	}

	/**
	 * @brief return true if apc is installed in the database
	 * @param PDO $dbh
	 * @return true on success, false otherwise
	 */
	protected function isAPCInstalled( $dbh ) {
		$res = $dbh->query("SELECT IS_INSTALLED,IS_LOADED from ZSD_EXTENSIONS where NAME = 'apc'");
		foreach ($res as $row) {
			if ( $row['IS_INSTALLED'] ) {
				// if IS_INSTALLED is 1 it means that ZSD was able to locate
				// the apc.so on the file system
				return true;
			}
		}
		return false;
	}

	/**
	 * @brief return true if apc is loaded
	 * @param PDO $dbh
	 * @return true on success, false otherwise
	 */
	protected function isAPCLoaded( $dbh ) {
		$res = $dbh->query("SELECT IS_LOADED from ZSD_EXTENSIONS where NAME = 'apc'");
		foreach ($res as $row) {
			if ( $row['IS_LOADED'] ) {
				return true;
			}
		}
		return false;
	}

	protected function clearMysqlBlueprintIfNeeded() {

		$clear = false;

		$dbh = $this->getMySqlDbh();
		if (!$dbh) {
			message("Cannot clear blueprint");
			return;
		}

		$profilesTableExists = $this->isTableExists($dbh, "ZSD_NODES_PROFILE");
		$versions = array();
		if ($profilesTableExists) {
			$res = $dbh->query("SELECT PHPVERSION FROM ZSD_NODES_PROFILE group by PHPVERSION")->fetchAll();
			foreach ($res as $row) {
				$versions[] = $row['PHPVERSION'];
			}
		}
		message("PHP versions found in profiles - " . implode(",", $versions));
		if (!in_array(phpversion(), $versions)) {
			message("Installation version " . phpversion() . "not in profiles");
			$clear = true;
		}
		if ($this->getUpgradeFromVersion() == "6.0.0") {
			$zsVersion = $this->getCurrentSchemaVersionFromMySQLDB($dbh);
			message("ZS version in schema properties - " . $zsVersion);
			if ($zsVersion == "6.0.0") {
				$clear = true;
			}
		}

		if ($clear) {
			message("Clearing blueprint..");
			$this->clearBlueprint($dbh);
		}
	}


	protected function upgradeMysql() {

		$this->createCleanMysqlDatabase();

		if ($this->getUpgradeFromVersion() == "5.6.0") {
			$dbh56 = $this->getMySqlDbh("5.6.0");
			if ($dbh56)	{
				$dbh56->query("use ZendServer;");
				$this->importPageCacheRules($dbh56);
			}
		}

		$dbh = $this->getMySqlDbh();
		if (!$dbh) {
			return;
		}

		$schemaVersion = $this->getMysqlSchemaVersion($dbh);
		message("Current schema version is [" . $schemaVersion . "]" );

		$this->clearMysqlBlueprintIfNeeded();

		if (!$schemaVersion || version_compare('6.0.1', $schemaVersion, ">")) {

			if ( !$schemaVersion ) {
				message( "Could not locate schema version in the database. Ensuring that we are at least at version 6.0.1" );
			}

			message("Updating schema version to 6.0.1");
			$this->setMysqlSchemaVersion($dbh, '6.0.1');
			$schemaVersion = '6.0.1';
		}

		message( "Current ZSD schema version is: " . $schemaVersion );
		$dbh = $this->getMysqlDbh();

		if ( $schemaVersion == '6.0.1' ) {
			$this->upgradeTo61( $dbh  );
			$this->setMysqlSchemaVersion( $dbh, '6.1.0' );
		}

		$schemaVersion = $this->getMysqlSchemaVersion($dbh);
		if ( $schemaVersion == '6.1' || $schemaVersion == '6.1.0') {
			$this->upgradeTo62($dbh, false);
			$this->setMysqlSchemaVersion( $dbh, '6.2.0' );
		}

		$schemaVersion = $this->getMysqlSchemaVersion($dbh);
		if ( $schemaVersion == '6.2' || $schemaVersion == '6.2.0') {
			$this->updateVhostNginxConfFiles();
			$this->upgradeTo63($dbh, false);
			$this->setMysqlSchemaVersion( $dbh, '6.3.0' );
		}

		$schemaVersion = $this->getMysqlSchemaVersion($dbh);
		if ($schemaVersion == '8.5.0') {
			$this->upgradeTo90($dbh, false);
			$this->setMysqlSchemaVersion( $dbh, '9.0.0' );
		}

		$schemaVersion = $this->getMysqlSchemaVersion($dbh);
		message("Schema version from the database is: " . $schemaVersion);
		if ($schemaVersion == '9.0.0') {
			$this->upgradeTo91($dbh, false);
			$this->setMysqlSchemaVersion( $dbh, '9.1.0' );
		}

		$schemaVersion = $this->getMysqlSchemaVersion($dbh);
		message("Schema version from the database is: " . $schemaVersion);
		if ($schemaVersion == '9.1.0') {
			$this->upgradeTo92($dbh, false);
		}

		// We always run the change from INT(11) to BIGINT (even on clean installs)
		$this->alterColumnType($dbh, false);

		// Remove the obsolete directives
		$this->removeObsoleteINIDirectivesFromDB($dbh);

	//	$upgrade2019_0_0 = new ZSDDbCreator_2019_0_0();
	//	$upgrade2019_0_0->upgradeMysql();
	}

	/**
	 *
	 * @param PDO $dbh
	 */
	protected function clearBlueprint($dbh) {
		try {

			$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

			$dbh->beginTransaction();

			$dbh->exec("DELETE FROM ZSD_DIRECTIVES;");
			$dbh->exec("DELETE FROM ZSD_EXTENSIONS;");

			$dbh->commit();

		} catch (Exception $ex) {
			$dbh->rollBack();
			message($ex->getMessage());
		}
	}


	private function disableZRayInProduction() {
		$upgradeFromVersion = $this->getUpgradeFromVersion();
		if ($upgradeFromVersion && version_compare($upgradeFromVersion, "7.0.0", "<")) {
			message("Setting ZRay configuration for upgrade");
			$serverProfile = $this->getServerProfile();
			message("Server profile is $serverProfile");
			if (!$serverProfile || strtolower($serverProfile) != "development") {
				// disable ZRay in non development upgrade
				$zrayIniPath = $_GET['zsDir'] . "/etc/" . get_cfg_var("zend.ini_scandir") . "/zray.ini";
				$ini = file($zrayIniPath);
				foreach ($ini as $line => $str) {
					if (preg_match('@zray.enable[\s]*=@', $str)) {
						message("found zray directive in line {$line}");
						$ini[$line] = "zray.enable=0" . PHP_EOL;
						break;
					}
				}
				file_put_contents($zrayIniPath, implode("", $ini));
				message("Disabled ZRay for non development upgrade");
			}
		}
	}
	protected function upgradeZrayINI()
	{
		// Remove redundant INI entries from zray.ini
		$iniFile = $this->getConfdDir() . DIRECTORY_SEPARATOR . "zray.ini";
		$this->removeDirectiveFromINI("zray.enable_attribute_masking", $iniFile);
		$this->removeDirectiveFromINI("zray.attribute_masking_list", $iniFile);
	}

	protected function removeObsoleteINIDirectivesFromDB($db) {
		$this->removeDirectiveFromDB($db, "zray.enable_attribute_masking");
		$this->removeDirectiveFromDB($db, "zray.attribute_masking_list");
		$this->removeDirectiveFromDB($db, "zend_monitor.security_black_list");
		$this->removeDirectiveFromDB($db, "zend_monitor.super_globals_to_secure");
		$this->removeDirectiveFromDB($db, "zend_datacache.apc_compatibility");
		$this->removeDirectiveFromDB($db, "zray.ports_blacklist");
		$this->removeDirectiveFromDB($db, "zend_monitor.event_generate_trace_file");
	}

	public function upgrade() {
		$this->upgradeSqlite();

		$licenseName = "";
		$upgradeFromVersion = $this->getUpgradeFromVersion();
		if ( $upgradeFromVersion == "6.0.0") {
			$licenseName = $this->importLicenseFromBlueprint();
		} else if ($upgradeFromVersion == "5.6.0") {
			if (!$this->importLicenseFromBlueprint()) {
				$this->updateLicense("", "");
			}
		}

		if ($this->isClusterUpgrade()) {
			$this->upgradeMysql();
		}

		$this->disableZRayInProduction();
		$this->upgradeZrayINI();
	}

	private function updateLicense($user, $serial) {

		message("Update license ($user)");

		$monIni = parse_ini_file($_GET['zsDir'] . "/etc/monitor_node.ini");
		$globalsIniPath = $monIni['zend_monitor.global_directives_ini_file'];
		$ini = file($globalsIniPath);
		foreach ($ini as $line => $str) {
			if (strstr($str, "zend.user_name")) {
				$ini[$line] = "zend.user_name=" . $user . PHP_EOL;
			}
			if (strstr($str, "zend.serial_number")) {
				$ini[$line] = "zend.serial_number=" . $serial . PHP_EOL;
			}
		}

		message("Updating license in $globalsIniPath");
		file_put_contents($globalsIniPath, implode("", $ini));
	}

	public function updateNodeId($id) {

		message("Updating node id to $id");

		$monIni = parse_ini_file($_GET['zsDir'] . "/etc/monitor_node.ini");
		$globalsIniPath = $monIni['zend_monitor.global_directives_ini_file'];
		$ini = file($globalsIniPath);
		foreach ($ini as $line => $str) {
			if (strstr($str, "zend.node_id")) {
				$ini[$line] = "zend.node_id=$id" . PHP_EOL;
			}
		}

		file_put_contents($globalsIniPath, implode("", $ini));
	}

	public function updateClusterNodeId($id, $mysqlIp) {

		$id = "$id@$mysqlIp@ZendServer@" . time();
		message("Updating cluster node id - $id ");

		$zsdIni = $_GET['zsDir'] . "/etc/zsd.ini";
		$ini = file($zsdIni);
		foreach ($ini as $line => $str) {
			if (strstr($str, "zend_server_daemon.cluster_node_id")) {
				$ini[$line] = "zend_server_daemon.cluster_node_id=$id" . PHP_EOL;
				break;
			}
		}

		file_put_contents($zsdIni, implode("", $ini));
	}

	private function importPageCacheRules($dbh) {

		message("Importing Page Cache rules");

		$res = $dbh->query("SELECT count(*) as count from ZSD_PAGECACHE_RULES");
		foreach ($res as $row) {
			if ($row['count'] > 0) {
				message("DB already contains page cache rules");
				return;
			}
		}

		$rules = simplexml_load_file($this->sqlFile = $_GET['zsDir'] . "/etc/pagecache_rules.xml");

		try {
			$dbh->beginTransaction();
			foreach ($rules->cache->url as $rule) { /* @var $rule SimpleXMLElement */
				$path = "http://" . $rule['host'] . "/" . $rule['path'];
				message("Found page cache rule " . $rule['id'] . ": $path");

				$stmt = $dbh->prepare("INSERT INTO ZSD_PAGECACHE_RULES (RULE_ID, APP_ID, URL, NAME, META_XML) VALUES (:RULE_ID, :APP_ID, :URL, :NAME, :META_XML)");
				$stmt->bindValue(":RULE_ID", $rule['id']);
				$stmt->bindValue(":APP_ID", -1);
				$stmt->bindValue(":URL", $path);
				$stmt->bindValue(":NAME", (string) $rule->name);
				$stmt->bindValue(":META_XML", strstr($rule->asXML(), "<url"));
				$stmt->execute();
			}
			$dbh->commit();
		} catch (Exception $ex) {
			$dbh->rollBack();
			error($ex->getMessage());
		}
	}

	/**
	 * Execute ZSD fixtures
	 * @param \PDO $dbh
	 * @param string $filename
	 */
	private function runMySQLFixtures($dbh, $filename) {
		$sqlFileFullpath = $this->getSqlsDir() . "/" .$filename;
		$sql = file_get_contents($sqlFileFullpath);
		message("Executing MySQL fixtures file: " . $sqlFileFullpath . "\n");
		try {
			$dbh->exec($sql);
		} catch (\Exception $ex) {
			if (!strstr($ex->getMessage(), "already exists") && !stristr($ex->getMessage(), "duplicate")) {
				throw $ex;
			}
		}
	}
}

class ZSDDbCreator_2019_0_0 extends ZSDDbCreator
{
	const SCHEMA_VERSION = "2019.0.0";

	public function getSchemaVersion() { return self::SCHEMA_VERSION; }

	public function upgrade() {
		$this->upgradeSqlite();

		if ($this->isClusterUpgrade()) {
			$this->upgradeMysql();
		}
	}

	private function createPageCacheResponseTables($dbh, $isSqlite) {

		message("Creating Page Cache response rules tables");

		if ($isSqlite) {
			$autoIncrement = "AUTOINCREMENT";
		} else {
			$autoIncrement = "AUTO_INCREMENT";
		}

		$dbh->exec("
		CREATE TABLE ZSD_PAGECACHE_RESPONSE_HEADERS (
			RULE_ID	INTEGER NOT NULL PRIMARY KEY {$autoIncrement},
			APP_ID INTEGER NOT NULL DEFAULT -1,
			NAME	TEXT,
			OPERATION	TEXT,
			VALUE	TEXT,
			INHERIT_GLOBALS INTEGER NOT NULL DEFAULT 1
		);

		CREATE TABLE ZSD_PAGECACHE_RESPONSE_CODES (
			RULE_ID	INTEGER NOT NULL PRIMARY KEY {$autoIncrement},
			APP_ID INTEGER NOT NULL DEFAULT -1,
			CODES TEXT);

		CREATE INDEX ZSD_PAGECACHE_RESPONSE_HEADERS_IDX1 ON ZSD_PAGECACHE_RESPONSE_HEADERS(APP_ID);
		CREATE INDEX ZSD_PAGECACHE_RESPONSE_CODES_IDX1 ON ZSD_PAGECACHE_RESPONSE_CODES(APP_ID);
		"
		);
	}

	private function createMultiPHPTables($dbh, $isSqlite) {

		message("Creating Multi PHP tables");

		if ($isSqlite) {
			$autoIncrement = "AUTOINCREMENT";
			$tableSuffix = "";
			$keyLength = "";
		} else {
			$autoIncrement = "AUTO_INCREMENT";
			$tableSuffix = " Engine=Innodb DEFAULT CHARSET=latin1";
			$keyLength = "(32)";
		}

		$dbh->exec("

			CREATE TABLE IF NOT EXISTS ZSD_PHP_EXTENSIONS_DIRECTIVES (
			NAME VARCHAR(256),
			MEMORY_VALUE VARCHAR(512),
			DISK_VALUE TEXT,
			EXTENSION VARCHAR(256),
			INI_FILE VARCHAR(256) NOT NULL DEFAULT '',
			INI_FILE_HASH VARCHAR(32) NOT NULL,
			MEMORY_ONLY INTEGER,
			INI_SECTION VARCHAR(128) DEFAULT '',
			REQUIRES_RESTART INTEGER DEFAULT 1,
			PHP_VERSION VARCHAR(64) NOT NULL
		){$tableSuffix};

		CREATE TABLE IF NOT EXISTS ZSD_ZEND_EXTENSIONS_DIRECTIVES (
			NAME VARCHAR(256),
			MEMORY_VALUE VARCHAR(512),
			DISK_VALUE TEXT,
			EXTENSION VARCHAR(256),
			INI_FILE VARCHAR(256) NOT NULL DEFAULT '',
			INI_FILE_HASH VARCHAR(32) NOT NULL,
			MEMORY_ONLY INTEGER,
			INI_SECTION VARCHAR(128) DEFAULT '',
			REQUIRES_RESTART INTEGER DEFAULT 1
		){$tableSuffix};

		CREATE TABLE IF NOT EXISTS ZSD_ZEND_DAEMONS_DIRECTIVES (
			NAME VARCHAR(256),
			MEMORY_VALUE VARCHAR(512),
			DISK_VALUE TEXT,
			DAEMON VARCHAR(64),
			INI_FILE VARCHAR(256) NOT NULL DEFAULT '',
			INI_FILE_HASH VARCHAR(32) NOT NULL,
			MEMORY_ONLY INTEGER,
			INI_SECTION VARCHAR(128) DEFAULT '',
			REQUIRES_RESTART INTEGER DEFAULT 1
		){$tableSuffix};

		CREATE TABLE IF NOT EXISTS ZSD_ZEND_COMMON_DIRECTIVES (
			NAME VARCHAR(256),
			MEMORY_VALUE VARCHAR(512),
			DISK_VALUE TEXT,
			INI_FILE VARCHAR(256) NOT NULL DEFAULT '',
			INI_FILE_HASH VARCHAR(32) NOT NULL,
			MEMORY_ONLY INTEGER,
			INI_SECTION VARCHAR(128) DEFAULT '',
			REQUIRES_RESTART INTEGER DEFAULT 1
		){$tableSuffix};

		CREATE TABLE IF NOT EXISTS ZSD_PHP_EXTENSIONS (
			ID INTEGER NOT NULL PRIMARY KEY {$autoIncrement},
			NAME VARCHAR(256) NOT NULL,
			EXT_VERSION VARCHAR(512),
			IS_INSTALLED INTEGER,
			IS_LOADED INTEGER,
			INI_FILE VARCHAR(256) NOT NULL DEFAULT '',
			PHP_VERSION VARCHAR(64) NOT NULL
		){$tableSuffix};

		CREATE TABLE IF NOT EXISTS ZSD_ZEND_EXTENSIONS (
			ID INTEGER NOT NULL PRIMARY KEY {$autoIncrement},
			NAME VARCHAR(256) NOT NULL,
			EXT_VERSION VARCHAR(512),
			IS_INSTALLED INTEGER,
			IS_LOADED INTEGER,
			INI_FILE VARCHAR(256) NOT NULL DEFAULT '',
			PHP_VERSION VARCHAR(64) NOT NULL
		){$tableSuffix};


		CREATE TABLE IF NOT EXISTS ZSD_PHP_VERSIONS (
			ID INTEGER NOT NULL PRIMARY KEY {$autoIncrement},
			PHP_VERSION_SHORT VARCHAR(64) NOT NULL,
			PHP_VERSION_LONG VARCHAR(64) NOT NULL,
			ACTIVE INTEGER
		){$tableSuffix};

		CREATE UNIQUE INDEX ZSD_PHP_VERSIONS_UNIQUE1 ON ZSD_PHP_VERSIONS(PHP_VERSION_SHORT);

		CREATE UNIQUE INDEX ZSD_PHP_DIRECTIVES_IDX1 ON ZSD_PHP_EXTENSIONS_DIRECTIVES(NAME, INI_FILE_HASH{$keyLength});
		CREATE        INDEX ZSD_PHP_DIRECTIVES_IDX2 ON ZSD_PHP_EXTENSIONS_DIRECTIVES(INI_FILE);
		CREATE        INDEX ZSD_PHP_DIRECTIVES_IDX3 ON ZSD_PHP_EXTENSIONS_DIRECTIVES(MEMORY_ONLY);

		CREATE UNIQUE INDEX ZSD_ZEND_EXT_DIRECTIVES_IDX1 ON ZSD_ZEND_EXTENSIONS_DIRECTIVES(NAME, INI_FILE_HASH{$keyLength});
		CREATE        INDEX ZSD_ZEND_EXT_DIRECTIVES_IDX2 ON ZSD_ZEND_EXTENSIONS_DIRECTIVES(INI_FILE);
		CREATE        INDEX ZSD_ZEND_EXT_DIRECTIVES_IDX3 ON ZSD_ZEND_EXTENSIONS_DIRECTIVES(MEMORY_ONLY);

		CREATE UNIQUE INDEX ZSD_ZEND_DAEMONS_DIRECTIVES_IDX1 ON ZSD_ZEND_DAEMONS_DIRECTIVES(NAME, INI_FILE_HASH{$keyLength});
		CREATE        INDEX ZSD_ZEND_DAEMONS_DIRECTIVES_IDX2 ON ZSD_ZEND_DAEMONS_DIRECTIVES(INI_FILE);
		CREATE        INDEX ZSD_ZEND_DAEMONS_DIRECTIVES_IDX3 ON ZSD_ZEND_DAEMONS_DIRECTIVES(MEMORY_ONLY);

		CREATE UNIQUE INDEX ZSD_ZEND_COMMON_DIRECTIVES_IDX1 ON ZSD_ZEND_COMMON_DIRECTIVES(NAME, INI_FILE_HASH{$keyLength});
		CREATE        INDEX ZSD_ZEND_COMMON_DIRECTIVES_IDX2 ON ZSD_ZEND_COMMON_DIRECTIVES(INI_FILE);
		CREATE        INDEX ZSD_ZEND_COMMON_DIRECTIVES_IDX3 ON ZSD_ZEND_COMMON_DIRECTIVES(MEMORY_ONLY);

		CREATE UNIQUE INDEX ZSD_PHP_EXTENSIONS_IDX1 ON ZSD_PHP_EXTENSIONS(NAME, PHP_VERSION);
		CREATE UNIQUE INDEX ZSD_ZEND_EXTENSIONS_IDX1 ON ZSD_ZEND_EXTENSIONS(NAME, PHP_VERSION);

		");
	}

	public function upgradeSqlite() {
		message("Upgrading ZSD SQLITE schema to " . self::SCHEMA_VERSION);
		if ($this->getSqliteSchemaVersion() != self::SCHEMA_VERSION ) {
			$dbh = $this->getSqliteDbh();
			$this->createMultiPHPTables($dbh, true);
			$this->createPageCacheResponseTables($dbh, true);
			$this->setSqliteSchemaVersion( $dbh, self::SCHEMA_VERSION );
		}
	}

	public function upgradeMysql() {
		message("Upgrading ZSD MYSQL schema to " . self::SCHEMA_VERSION);
		if ($this->getMysqlSchemaVersion() != self::SCHEMA_VERSION ) {
			$dbh = $this->getMySqlDbh();
			$this->createMultiPHPTables($dbh, false);
			$this->createPageCacheResponseTables($dbh, false);
			$this->setMysqlSchemaVersion($dbh, self::SCHEMA_VERSION);
		}
	}
}

class ZSDDbCreator_2019_0_1 extends ZSDDbCreator {
	const SCHEMA_VERSION = "2019.0.1";

	public function getSchemaVersion() { return self::SCHEMA_VERSION; }

	public function upgrade() {
		$this->upgradeSqlite();

		if ($this->isClusterUpgrade()) {    // is MYSQL upgrade, not Cluster
			$this->upgradeMysql();
		}
	}

	public function upgradeSqlite() {
		message("Clearing ZSD SQLite PHP extensions list");
		$dbh = $this->getSqliteDbh();
		$dbh->exec("DELETE FROM ZSD_PHP_EXTENSIONS;");
		$dbh->exec("DELETE FROM ZSD_PHP_EXTENSIONS_DIRECTIVES;");
		$dbh->exec("DELETE FROM ZSD_ZEND_EXTENSIONS;");
	}

	public function upgradeMysql() {
		message("Clearing ZSD MySql PHP extensions list");
		$dbh = $this->getMySqlDbh();
		$dbh->exec("DELETE FROM ZSD_PHP_EXTENSIONS;");
		$dbh->exec("DELETE FROM ZSD_PHP_EXTENSIONS_DIRECTIVES;");
		$dbh->exec("DELETE FROM ZSD_ZEND_EXTENSIONS;");
	}
}