function create_table($options)
{
/*
* This function will generate the SQL for the following:
* drop, create table with email unique, grant permissions
*
*/
$username = $options['u'];
$password = $options['p'];
$hostname = $options['h'];
$dbname = "catalyst";
$tablename = "users";
// **** Define the SQL statements we want
$sql_create = "create table users (\n firstname VARCHAR(30) NOT NULL,\n surname VARCHAR(30) NOT NULL,\n email VARCHAR(100) PRIMARY KEY\n )";
$sql_grant = "grant ALL on {$dbname}.* TO '{$username}'@'{$hostname}'";
$sql_drop = "drop table users";
$sql_table_exist = "SELECT COUNT(*) AS count\n FROM information_schema.tables\n WHERE table_schema = '{$dbname}'\n AND table_name = '{$tablename}'";
try {
//create our SQL connection
//$conn = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
// set PDO error mode to exception
//$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn = open_db_connection($options);
/*
* So let's test if the table exists first
*/
try {
//$result = $conn->query($sql_table_exist)->fetchColumn();
$result = check_table_exists($conn);
} catch (PDOException $e) {
echo $result . $e->getMessage();
}
if ($result) {
try {
$conn->exec($sql_drop);
} catch (PDOException $e) {
echo $sql_drop . $e->getMessage();
}
try {
$conn->exec($sql_create);
} catch (PDOException $e) {
echo $sql_create . $e->getMessage();
}
try {
$conn->exec($sql_grant);
} catch (PDOException $e) {
echo $sql_grant . $e->getMessage();
}
} else {
if (!$result) {
$conn->exec($sql_create);
$conn->exec($sql_grant);
}
}
} catch (PDOException $e) {
echo 'Connection failed' . " " . $e->getMessage() . "\n";
}
$conn = null;
}
function create_table($options)
{
/*
* This function will create the users table if it doesn't
* exist and grant permissions on the table to the creating
* user.
* If the table does exist, it will drop the table before
* recreating it
*/
$username = $options['u'];
$password = $options['p'];
$hostname = $options['h'];
//$dbname = "catalyst";
$dbname = $options['dbname'];
$tablename = "users";
// Define the SQL statements necessary for whatever we want to do
$sql_create = "create table users (\n\t\tE_Id int NOT NULL AUTO_INCREMENT,\n name VARCHAR(30) NOT NULL,\n surname VARCHAR(30) NOT NULL,\n email VARCHAR(100) NOT NULL,\n\t\tUNIQUE(E_Id)\n )";
$sql_grant = "grant ALL on {$dbname}.* TO '{$username}'@'{$hostname}'";
$sql_drop = "drop table users";
$sql_table_exist = "SELECT COUNT(*) AS count\n FROM information_schema.tables\n WHERE table_schema = '{$dbname}'\n AND table_name = '{$tablename}'";
try {
$conn = open_db_connection($options);
// open a connection
/*
* So let's test if the table exists first
*/
try {
$result = check_table_exists($conn, $options);
} catch (PDOException $e) {
echo $result . $e->getMessage();
}
if ($result) {
try {
$conn->exec($sql_drop);
} catch (PDOException $e) {
echo $sql_drop . $e->getMessage();
}
try {
$conn->exec($sql_create);
} catch (PDOException $e) {
echo $sql_create . $e->getMessage();
}
try {
$conn->exec($sql_grant);
} catch (PDOException $e) {
echo $sql_grant . $e->getMessage();
}
} else {
if (!$result) {
echo "\nTable does not exist...creating!\n";
$conn->exec($sql_create);
$conn->exec($sql_grant);
}
}
} catch (PDOException $e) {
echo 'Connection failed' . " " . $e->getMessage() . "\n";
}
$conn = null;
}
function csv_reader($dry_run, $options)
{
/*
* This function does all the work of removing unwanted characters,
* spaces, exclamation marks and such from the names in the users.csv
* file.
*/
$filename = $options['file'];
$conn = open_db_connection($options);
$table_exists = check_table_exists($conn, $options);
if (!$table_exists) {
echo "\nWarning! Table does not exist\n";
}
if (($handle = fopen($filename, "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);
//Lower case then capitalise first
$data[0] = ucfirst(strtolower($data[0]));
//Lower case then capitalise first
$data[1] = ucfirst(strtolower($data[1]));
//Lower case the email
$data[2] = strtolower($data[2]);
//$data[2] = preg_replace('/\s+/', "", $data[2]);
// Remove trailing whitespaces
$data[0] = rtrim($data[0]);
// Remove trailing whitespaces
$data[1] = rtrim($data[1]);
// Remove trailing whitespaces
$data[2] = rtrim($data[2]);
// Remove non alphas from firstname
$data[0] = preg_replace('/[^a-z]+\\Z/i', "", $data[0]);
// Remove non alphas (except apostrophes) from surname
$data[1] = preg_replace('/[^a-z\']+\\Z/i', "", $data[1]);
// Add backslashes to escape the apostrophes
$data[0] = addslashes($data[0]);
$data[1] = addslashes($data[1]);
$data[2] = addslashes($data[2]);
echo " email is " . $data[2] . "\n";
if (validEmail($data[2])) {
if ($dry_run) {
echo $data[0] . " " . $data[1] . " " . $data[2] . " would be written to database\n";
} else {
if (!$dry_run) {
update_table($data, $conn);
}
}
} else {
echo $data[0] . " " . $data[1] . " " . $data[2] . " will NOT be written to database as email is invalid\n";
}
}
fclose($handle);
$conn = null;
}
}
function csv_reader($dry_run, $options)
{
/*
* Somewhere in this function there will be an if/else statement
* to test valid emails, if not valid, write to STDOUT
* else, call update table
*/
$filename = $options['file'];
/* if (dry_run)
open connection
check if table exists
parse file
don't write to table
write to STDOUT
else if (!dry_run)
open connection
check if table exists
parse file
write to table */
$conn = open_db_connection($options);
check_table_exists($conn);
$row = 1;
if (($handle = fopen($filename, "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);
$data[0] = ucfirst(strtolower($data[0]));
$data[1] = ucfirst(strtolower($data[1]));
$data[2] = strtolower($data[2]);
if (validEmail($data[2])) {
if ($dry_run) {
echo "\ndry_run is set\n";
echo $data[0] . " " . $data[1] . " " . $data[2] . " would be written to database";
} else {
if (!$dry_run) {
echo "\ndry_run is not set\n";
update_table($data, $conn);
}
}
} else {
//echo "\nThis email is not valid\n";
echo "\n" . $data[0] . " " . $data[1] . " " . $data[2] . " will not be written to database as email is invalid\n";
}
// echo " $num fields in line $row: \n";
$row++;
// for ($c=0; $c < $num; $c++) {
// echo $data[$c] . "\n";
// }
}
fclose($handle);
}
}
}
if (!$archive_table_exists) {
$ss->query($archive_query);
}
foreach ($options_alter_queries as $options_alter_query) {
$ss->query($options_alter_query);
}
foreach ($visits_alter_queries as $visits_alter_query) {
$ss->query($visits_alter_query);
}
foreach ($archive_alter_queries as $archive_alter_query) {
$ss->query($archive_alter_query);
}
$options_table_exists = check_table_exists($ss->tables['options']);
$visits_table_exists = check_table_exists($ss->tables['visits']);
$archive_table_exists = check_table_exists($ss->tables['archive']);
$options_table_missing = $options_table_exists ? check_table_fields_exist($ss->tables['options'], $options_tbl_fields) : array();
$visits_table_missing = $visits_table_exists ? check_table_fields_exist($ss->tables['visits'], $visits_tbl_fields) : array();
$archive_table_missing = $archive_table_exists ? check_table_fields_exist($ss->tables['archive'], $archive_tbl_fields) : array();
if ($options_table_exists && $visits_table_exists && $archive_table_exists && empty($options_table_missing) && empty($visits_table_missing) && empty($archive_table_missing)) {
echo '<p>All required ' . $creating . ' have been created. Click the button below to continue to the next step.</p>' . "\n";
} else {
echo '<p>Simple Stats was unable to create the ' . $creating . '. This is most likely because the MySQL user does not have permission to create ' . $creating . '.<p>You will need to create the ' . $creating . ' yourself, by executing the following queries.';
if (!$options_table_exists) {
echo '<p>To create the options table:</p><pre>' . htmlspecialchars($options_query) . ';</pre>';
}
if (!$visits_table_exists) {
echo '<p>To create the visits table:</p><pre>' . htmlspecialchars($visits_query) . ';</pre>';
}
if (!$archive_table_exists) {
echo '<p>To create the archive table:</p><pre>' . htmlspecialchars($archive_query) . ';</pre>';
