DatabaseServer;
$database = (string) $applicationSettings->DatabaseName;
$user = (string) $applicationSettings->DatabaseUser;
$password = (string) $applicationSettings->DatabasePassword;
try {
$connection = new PDO(
"mysql:host={$host};dbname={$database};charset=utf8mb4",
$user,
$password,
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
$connection->exec("SET FOREIGN_KEY_CHECKS = 0");
} catch (PDOException | Exception $e) {
echoLog("Unable to connect to the database.", "error");
exit();
}
// ----------------------------
// Get Table Definition Files
// ----------------------------
$tableFiles = glob("*.php");
$excludedFiles = [
"_createSystemTable.php",
"_createTable.php",
basename(__FILE__),
];
$tableFiles = array_values(array_filter($tableFiles, function ($file) use ($excludedFiles) {
return !in_array($file, $excludedFiles, true);
}));
sort($tableFiles);
// ----------------------------
// Process Table Files
// ----------------------------
foreach ($tableFiles as $tableFile) {
$tableName = basename($tableFile, ".php");
echoLog("Processing {$tableName}...", "warning");
try {
$connection->exec("DROP VIEW IF EXISTS `view_{$tableName}`");
echoLog("Dropped view_{$tableName}, if it existed.", "success");
} catch (PDOException $e) {
echoLog("Could not drop view_{$tableName}.", "error");
continue;
}
try {
$connection->exec("DROP TABLE IF EXISTS `{$tableName}`");
echoLog("Dropped {$tableName}, if it existed.", "success");
} catch (PDOException $e) {
echoLog("Could not drop table {$tableName}.", "error");
continue;
}
$SQL = null;
if (!include $tableFile) {
echoLog("Failed to include {$tableFile}. Skipping.", "error");
continue;
}
if (empty($SQL)) {
echoLog("No SQL found in {$tableFile}. Skipping.", "error");
continue;
}
runSQL($SQL, $connection, "create {$tableName}");
switch ($tableFile) {
case "popovers.php":
seedPopovers($connection);
break;
case "users.php":
seedInitialAdminUser($connection);
break;
}
}
try {
$connection->exec("SET FOREIGN_KEY_CHECKS = 1");
} catch (PDOException $e) {
echoLog("Warning: Could not re-enable foreign key checks.", "warning");
}
echoLog("System tables created successfully.", "success");
// -------------------------
// Helper Functions
// -------------------------
function runSQL($SQL, PDO $connection, $context = "query") {
try {
$connection->exec($SQL);
echoLog("Completed {$context}.", "success");
} catch (PDOException $e) {
echoLog("Error running {$context}.", "error");
exit();
}
}
function seedPopovers(PDO $connection) {
$popovers = [
[
"find_user",
"Find User",
"Find User...",
"Find by Name, Email, or Role
"
],
[
"find_report",
"Find Report",
"Find Report...",
"Find by Name or Description
"
],
[
"select_report",
"Select Report",
"Find Report...",
"Find by Name, Email, or Role
"
],
[
"find_popover",
"Find Popover",
"Find Popover...",
"Find by Name, Title or Placeholder
"
],
[
"find_journal",
"Find Journal",
"Find Journal...",
"Find by Name, Email, or Role
"
],
[
"find_status",
"Find Status",
"Find Status...",
"Find by Name
"
],
[
"find_dropdowntype",
"Find Dropdown Types",
"Find Dropdown Types...",
"Search for dropdown types
"
],
];
$SQL = "
INSERT INTO popovers (
popover_name,
popover_title,
popover_placeholder,
popover_text,
popover_created,
popover_creator
) VALUES (
:popover_name,
:popover_title,
:popover_placeholder,
:popover_text,
CURDATE(),
:popover_creator
)
";
$statement = $connection->prepare($SQL);
foreach ($popovers as $popover) {
$statement->execute([
":popover_name" => $popover[0],
":popover_title" => $popover[1],
":popover_placeholder" => $popover[2],
":popover_text" => $popover[3],
":popover_creator" => INIT_SCRIPT_USER,
]);
}
echoLog("Seeded popovers.", "success");
}
function seedInitialAdminUser(PDO $connection) {
$password = password_hash("349Jamot@", PASSWORD_DEFAULT);
$SQL = "
INSERT INTO users (
user_id,
user_name,
user_email,
user_password,
user_role,
user_change_password,
user_active,
user_creator,
user_created
) VALUES (
:user_id,
:user_name,
:user_email,
:user_password,
:user_role,
:user_change_password,
:user_active,
:user_creator,
CURDATE()
)
";
$statement = $connection->prepare($SQL);
$statement->execute([
":user_id" => "DSYSADMIN",
":user_name" => "James Richie",
":user_email" => "james.richie@onesourceits.com",
":user_password" => $password,
":user_role" => "Administrator",
":user_change_password" => 1,
":user_active" => 1,
":user_creator" => INIT_SCRIPT_USER,
]);
echoLog("Seeded initial administrator user.", "success");
}
function echoLog($message, $type = "") {
$class = trim("log {$type}");
echo "" . htmlspecialchars($message, ENT_QUOTES, "UTF-8") . "
";
}
?>