slork/sqlite.php

<?php

require "settings.php";

$pdo = null;
try {
	$pdo = new PDO("sqlite:" . "${db_path}/slork.sqlite");
} catch (PDOException $e) {
	// handle the exception here
}
if ( !$pdo ) {
	exit( "Cannot connect to database" );
}

// Create maintainer database
$pdo->exec( "DROP TABLE IF EXISTS maintainer;" );
$query = <<<QUERY
CREATE TABLE IF NOT EXISTS maintainer (
		id INTEGER PRIMARY KEY, maintainer INTEGER, api TEXT,
		UNIQUE( maintainer ));
QUERY;
$stmt = $pdo->exec( $query );
//$stmt->execute();

// Create user/player database
$pdo->exec( "DROP TABLE IF EXISTS player;" );
$query = <<<QUERY
CREATE TABLE IF NOT EXISTS player (
		id INTEGER PRIMARY KEY, maintainer INTEGER,
		player INTEGER, property TEXT, value TEXT,
		UNIQUE(maintainer, player),
		FOREIGN KEY (maintainer) REFERENCES maintainer(maintainer));
QUERY;
$stmt = $pdo->exec( $query );
//$stmt->execute();

// Create player preferences table (properties)
$pdo->exec( "DROP TABLE IF EXISTS player_properties;" );
$query = <<<QUERY
	CREATE TABLE player_properties (
	    id INTEGER PRIMARY KEY, 
	    maintainer INTEGER, 
	    player INTEGER, 
	    property TEXT DEFAULT '["name"]',
	    UNIQUE( maintainer, player));
QUERY;
$pdo->exec( $query );

$pdo->exec( "DROP TABLE IF EXISTS discord" );
$query = <<<QUERY
	CREATE TABLE discord (
	    id INTEGER PRIMARY KEY, 
		maintainer INTEGER, 
		discord TEXT, 
		UNIQUE(maintainer));
QUERY;
$pdo->exec( $query );
$query = <<<QUERY
	INSERT OR REPLACE INTO discord (maintainer, discord)
		VALUES (?, ?)
QUERY;
$stmt = $pdo->prepare( $query );
foreach ($discords as $discord ) {
	$stmt->execute( $discord );
}

// properties.php contains queries for a subsequent
// table properties containing default property names.
require_once "properties.php";