/**
* @return Database
*/
protected function getDB()
{
$db = new Database($GLOBALS['test_dbname'], $this->getDriverClassName(), $this->getDriverParameters());
$db->getDriver()->executeUpdate('DROP TABLE IF EXISTS Books');
$db->getDriver()->executeUpdate('DROP TABLE IF EXISTS Authors');
$db->getDriver()->executeUpdate(<<<SQL
CREATE TABLE Authors (
idAuthor INTEGER PRIMARY KEY NOT NULL,
name VARCHAR(100)
)
SQL
);
$db->getDriver()->executeUpdate(<<<SQL
CREATE TABLE Books (
idBook INTEGER PRIMARY KEY NOT NULL,
title VARCHAR(200) NOT NULL,
idAuthor INTEGER NOT NULL,
isbn VARCHAR(50) NOT NULL,
description TEXT,
dateCreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(idAuthor) REFERENCES Authors(idAuthor)
)
SQL
);
$db->getDriver()->executeUpdate(<<<SQL
CREATE TABLE IF NOT EXISTS testprefix_Test (
test INTEGER PRIMARY KEY DEFAULT NULL
)
SQL
);
$db->getDriver()->executeUpdate("INSERT INTO Authors (idAuthor, name) VALUES (1, 'Author 1')");
$db->getDriver()->executeUpdate("INSERT INTO Authors (idAuthor, name) VALUES (2, 'Author 2')");
$db->getDriver()->executeUpdate("INSERT INTO Books (idBook, title, idAuthor, isbn, description) VALUES (1, 'My First Book', 1, '12345-6789', 'It wasn''t very good')");
$db->getDriver()->executeUpdate("INSERT INTO Books (idBook, title, idAuthor, isbn, description) VALUES (2, 'My Second Book', 1, '12345-6790', 'It wasn''t very good either')");
$db->getDriver()->executeUpdate("INSERT INTO Books (idBook, title, idAuthor, isbn, description) VALUES (3, 'My First Book', 2, '12345-6790', 'It was OK')");
return $db;
}
/**
* @return Database
*/
protected function getDB()
{
$parameters = $this->getDriverParameters();
$pdo = new \PDO("sqlsrv:Server={$parameters['host']}", $parameters['username'], $parameters['password']);
$stmt = $pdo->prepare("IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = ?) CREATE DATABASE [{$GLOBALS['test_dbname']}]");
$stmt->bindParam(1, $GLOBALS['test_dbname']);
$stmt->execute();
$pdo = null;
$db = new Database($GLOBALS['test_dbname'], $this->getDriverClassName(), $parameters);
$db->getDriver()->executeUpdate("IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Books') DROP TABLE Books");
$db->getDriver()->executeUpdate("IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Authors') DROP TABLE Authors");
$db->getDriver()->executeUpdate("IF EXISTS (SELECT * FROM sys.objects WHERE name = 'testprefix_Test') DROP TABLE testprefix_Test");
$db->getDriver()->executeUpdate(<<<SQL
CREATE TABLE Authors (
idAuthor INTEGER PRIMARY KEY NOT NULL IDENTITY(1,1),
name NVARCHAR(100)
)
SQL
);
$db->getDriver()->executeUpdate(<<<SQL
CREATE TABLE Books (
idBook INTEGER PRIMARY KEY NOT NULL IDENTITY(1,1),
title NVARCHAR(200) NOT NULL,
idAuthor INTEGER NOT NULL,
isbn NVARCHAR(50) NOT NULL,
description NVARCHAR(MAX),
dateCreated DATETIME NOT NULL DEFAULT GETDATE(),
FOREIGN KEY(idAuthor) REFERENCES Authors(idAuthor)
)
SQL
);
$db->getDriver()->executeUpdate(<<<SQL
CREATE TABLE testprefix_Test (
test int DEFAULT NULL
)
SQL
);
$db->getDriver()->executeUpdate("DELETE FROM Books");
$db->getDriver()->executeUpdate("DELETE FROM Authors");
$db->getDriver()->executeUpdate("INSERT INTO Authors (name) VALUES ('Author 1')");
$db->getDriver()->executeUpdate("INSERT INTO Authors (name) VALUES ('Author 2')");
$db->getDriver()->executeUpdate("INSERT INTO Books (title, idAuthor, isbn, description) VALUES ('My First Book', 1, '12345-6789', 'It wasn''t very good')");
$db->getDriver()->executeUpdate("INSERT INTO Books (title, idAuthor, isbn, description) VALUES ('My Second Book', 1, '12345-6790', 'It wasn''t very good either')");
$db->getDriver()->executeUpdate("INSERT INTO Books (title, idAuthor, isbn, description) VALUES ('My First Book', 2, '12345-6790', 'It was OK')");
return $db;
}