+ * This class represents a collection of documents, stored on MySQL.
+ */
+class MySQLCollection extends SQLCollection {
+ ///
+ /// Singleton pattern to get the MySQLDatabase instance
+ ///
+
+ /**
+ * @var MySQLDatabase The mongo client to the database the collection is hosted
+ */
+ public static $client = null;
+
+
+ /**
+ * Gets the existing MySQLDatabase instance, or if not available, initializes one.
+ *
+ * @param Context $context
+ * @return MySQLDatabase The MySQLDatabase instance
+ */
+ public static function getCurrentSiteDatabaseClient () {
+ if (self::$client === null) {
+ $client = Database::load();
+ if ($candidateClient instanceof MySQLDatabase) {
+ self::$client = $client;
+ } else {
+ throw new InvalidArgumentException("The MySQLDatabase driver is intended to be used when your main database product is MySQL. We recommend whether you pick the same engine for collections and other db use, whether you use a key/store storage solution for collections, like MongoDB.");
+ }
+ }
+ return self::$client;
+ }
+
+ ///
+ /// Constructor
+ ///
+
+ /**
+ * Initializes a new instance of MongoCollection
+ *
+ * @param string $id the collection identifiant
+ */
+ public function __construct ($id, MySQLDatabase $client = null, $table = '') {
+ global $Config;
+
+ if ($client === null) {
+ self::getCurrentSiteDatabaseClient();
+ } else {
+ self::$client = $client;
+ }
+
+ if ($table == '') {
+ if (!array_key_exists('DocumentStorage', $Config) || !array_key_exists('Table', $Config['DocumentStorage'])) {
+ throw new Exception("Configuration parameter missing: \$Config['DocumentStorage']['Table']. Expected value for this parameter is the table to store the collections documents.");
+ * @return mixed If the query doesn't return any result, null. If the query return a row with one field, the scalar value. Otheriwse, an aossciative array, the fields as keys, the row as values.
+ */
+ public function query ($sql) {
+ if ($sql == "") {
+ return null;
+ }
+
+ $db = self::$client;
+ if (!$result = $db->query($sql, MYSQL_ASSOC)) {
+ throw new Exception("Can't execute collection query.");
+ }
+
+ if (!$row = $db->fetchRow($result)) {
+ return null;
+ }
+
+ if (count($row) == 1) {
+ return array_shift($row);
+ } else {
+ return $row;
+ }
+ }
+
+ /**
+ * Escapes the SQL string
+ *
+ * @param string $value The value to escape
+ * @return string The escaped value
+ */
+ public function escape ($value) {
+ return self::$client->escape($value);
+ }
+
+ /**
+ * Gets all the documents from the collection
+ *
+ * @return Iterator An iterator to the documents, each item an instance of CollectionDocument
+ */
+ public function getAll () {
+ $db = self::$client;
+
+ $collectionId = $this->escape($this->id);
+ $sql = "SELECT * FROM $this->table WHERE collection_id = '$collectionId'";
+ if (!$result = $db->query($sql, MYSQL_ASSOC)) {
+ throw new Exception("Can't get each collection documents.");
+ * Abstract class with SQL standard implementation of CRUD features for collections using a SQL database.
+ */
+abstract class SQLCollection extends Collection {
+ /**
+ * @var string The SQL collections table
+ */
+ public $table;
+
+ /**
+ * Executes a SQL query
+ *
+ * @param string $sql The SQL query
+ * @return mixed If the query doesn't return any null, nothing. If the query return a row with one field, the scalar value. Otheriwse, an aossciative array, the fields as keys, the row as values.
+ */
+ public abstract function query ($sql);
+
+ /**
+ * Escapes the SQL string
+ *
+ * @param string $value The value to escape
+ * @return string The escaped value
+ */
+ public abstract function escape ($value);
+
+ /**
+ * Adds a document to the collection
+ *
+ * @param CollectionDocument $document The document to add
+ * @return boolean true if the operation succeeded; otherwise, false.
+ */
+ public function add (CollectionDocument &$document) {
+ * @param string $documentId The identifiant of the document to delete
+ * @return boolean true if the operation succeeded; otherwise, false.
+ */
+ public function delete ($documentId) {
+ $collectionId = $this->escape($this->id);
+ $documentId = $this->escape($documentId);
+
+ $sql = "DELETE FROM $this->table WHERE collection_id = '$collectionId' AND document_id = '$documentId'";
+ $this->query($sql);
+ }
+
+ /**
+ * Determines if a document exists
+ *
+ * @param CollectionDocument $document The document to check
+ * @return boolean true if the document exists; otherwise, false.
+ */
+ public function exists (CollectionDocument $document) {
+ $collectionId = $this->escape($this->id);
+ $documentId = $this->escape($document->id);
+
+ $sql = "SELECT count(*) FROM $this->table WHERE collection_id = '$collectionId' AND document_id = '$documentId'";
+ return $this->query($sql) == 1;
+ }
+
+ /**
+ * Updates a document in the collection
+ *
+ * @param CollectionDocument $document The document to update
+ * @return boolean true if the operation succeeded; otherwise, false.
+ */
+ public function update (CollectionDocument &$document) {
+ $collectionId = $this->escape($this->id);
+ $documentId = $this->escape($document->id);
+ $value = $this->escape(json_encode($document));
+
+ $sql = "UPDATE $this->table SET document_value = '$value' WHERE collection_id = '$collectionId' AND document_id = '$documentId'";
+ $this->query($sql);
+ }
+
+ /**
+ * Gets a document from the collection
+ *
+ * @param string $documentId The identifiant of the document to get
+ * @param CollectionDocument $document The document
+ */
+ public function get ($documentId){
+ $type = $this->documentType;
+ if (!class_exists($type)) {
+ throw new Exception("Can't create an instance of $type. If the class exists, did you registered a SPL autoloader or updated includes/autoload.php?");
+ }
+
+ $collectionId = $this->escape($this->id);
+ $documentId = $this->escape($documentId);
+
+ $sql = "SELECT document_value FROM $this->table WHERE collection_id = '$collectionId' AND document_id = '$documentId'";
+ $data = $this->query($sql);
+ $data = json_decode($data);
+
+ return $type::loadFromObject($data);
+ }
+
+ /**
+ * Gets a count of the documents in the collection
+ *
+ * @return int The number of documents
+ */
+ public function count () {
+ $collectionId = $this->escape($this->id);
+ $sql = "SELECT count(*) FROM $this->table WHERE collection_id = '$collectionId'";
+ * This class represents a collection of documents, stored on MySQL.
+ */
+class SQLiteCollection extends SQLCollection {
+
+ ///
+ /// Singleton pattern to get or initialize the SQLite instance
+ ///
+
+ /**
+ * @var SQLite3 The SQLite client to the database the collection is hosted
+ */
+ public static $client = null;
+
+
+ /**
+ * Gets the existing SQLite3 instance, or if not available, initializes one.
+ *
+ * @return SQLite3 The SQLite3 instance
+ */
+ public static function getClient () {
+ if (self::$client === null) {
+ self::$client = self::initializeClient();
+ }
+ return self::$client;
+ }
+
+ /**
+ * Initializes a new SQLite3 instance
+ *
+ * @return SQLite3 the client
+ */
+ public static function initializeClient () {
+ global $Config;
+ if (!array_key_exists('DocumentStorage', $Config) || !array_key_exists('File', $Config['DocumentStorage'])) {
+ throw new Exception("Configuration parameter missing: \$Config['DocumentStorage']['File']. Expected value for this parameter is the path to the SQLite database file.");
+ }
+
+ return new SQLite3($Config['DocumentStorage']['File']);
+ }
+
+ ///
+ /// Constructor
+ ///
+
+ /**
+ * Initializes a new instance of MongoCollection
+ *
+ * @param string $id the collection identifiant
+ */
+ public function __construct ($id) {
+ $this->table = 'collections';
+ $this->id = $id;
+ $this->initializeCollectionsTable();
+ }
+
+ ///
+ /// Helper to create SQLite3 schema if required
+ ///
+
+ /**
+ * Initializaes collections table
+ */
+ protected function initializeCollectionsTable () {
+ if (defined('COLLECTIONS_SQLITE_DATABASE_READY') && COLLECTIONS_SQLITE_DATABASE_READY) {
+ * @return mixed If the query doesn't return any result, null. If the query return a row with one field, the scalar value. Otherwise, an associative array, the fields as keys, the row as values.
+ */
+ public function query ($sql) {
+ $client = static::getClient();
+
+ if (static::isStatement($sql)) {
+ if (!$client->exec($sql)) {
+ throw new Exception(
+ "Can't execute collection query. "
+ . $client->lastErrorMsg()
+ );
+ }
+ return null;
+ }
+
+ $result = $client->query($sql);
+ if ($result === true) {
+ return null;
+ }
+ if ($result === false) {
+ throw new Exception(
+ "Can't execute collection query. "
+ . $client->lastErrorMsg()
+ );
+ }
+
+ $row = $result->fetchArray(SQLITE3_ASSOC);
+ $scalar = ($result->numColumns() == 1);
+ $result->finalize();
+
+ if ($scalar) {
+ return array_shift($row);
+ } else {
+ return $row;
+ }
+ }
+
+ /**
+ * Escapes the SQL string
+ *
+ * @param string $value The value to escape
+ * @return string The escaped value
+ */
+ public function escape ($value) {
+ return SQLite3::escapeString($value);
+ }
+
+ /**
+ * Gets all the documents from the collection
+ *
+ * @return Iterator An iterator to the documents, each item an instance of CollectionDocument
+ */
+ public function getAll () {
+ $collectionId = $this->escape($this->id);
+ $sql = "SELECT document_value FROM $this->table WHERE collection_id = '$collectionId'";
+ $client = static::getClient();
+ $type = $this->documentType;
+
+ $result = $client->query($sql);
+ while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$this->assertEquals('Iain M. Banks', $newBook->author);
$this->assertEquals('redBook', $newBook->id);
//::set - an existing document as parameter
$previousId = $this->redBook->id;
$this->collection->set($this->redBook);
$this->assertEquals(
$previousId,
$this->redBook->id,
"The document ID has been modified during a set operation on an already added dcoument. It should stay the same. Old id: $previousId. New id: " . $this->redBook->id
+ * Initializes the resources needed for thist test.
+ */
+ public function setUp () {
+ $db = new MySQLDatabase(
+ UNITTESTING_MYSQL_HOST,
+ UNITTESTING_MYSQL_USERNAME,
+ UNITTESTING_MYSQL_PASSWORD,
+ UNITTESTING_MYSQL_DATABASE
+ );
+
+ $this->initializeDocuments();
+ $this->collection = new MySQLCollection('quux', $db, UNITTESTING_MYSQL_TABLE);
+ }
+
+ ///
+ /// Tests specific to MySQLCollection
+ ///
+
+ /**
+ * Tests the property table is correctly set
+ *
+ * @covers MySQLCollection::construct
+ */
+ public function testTable () {
+ $this->assertEquals(UNITTESTING_MYSQL_TABLE, $this->collection->table, "The collection constructor should have initialized the MySQLCollection::table property.");
+ }
+
+ /**
+ * Tests if the ready constant has been correctly defined
+ $this->fail("The query() specifications provides: 'If the query doesn't return any result, return null.'. This is also the expected behavior for empty queries. Instead we got an exception.");
+
+ }
+ $this->assertNull($resultNull, "The query() specifications provides: 'If the query doesn't return any result, return null.'. This is also the expected behavior for empty queries.");
+ $this->assertNull($resultNull, "The query() specifications provides: 'If the query doesn't return any result, return null.'. This is expected for $sqlNullResult.");
+ $this->assertEquals(2, $resultScalar, "The query() specifications provides: 'If the query return a row with one field, the scalar value.' This is expected for $sqlScalarResult.");
+ $this->assertEquals($expectedResultArray, $resultArray, "The query() specifications provides: '[If the query returns a non scalar result, return] an associative array, the fields as keys, the row as values.'. This is expected for $sqlArrayResult.");
+ $this->assertSame($client1, $client2, "The collection classes are expected to use a singleton pattern for the client: you should return the same object initialized before instead to create another one.");
+ "The query PRAGMA database_list hasn't returned what we expected: one database opened by the client, the file returned by the database matching our configuration file."
+ );
+
+ $this->assertTrue(
+ defined('COLLECTIONS_SQLITE_DATABASE_READY'),
+ "After the client has been initialized, we shall have a 'COLLECTIONS_SQLITE_DATABASE_READY' constant defined."
+ );
+
+ $this->assertSame(
+ COLLECTIONS_SQLITE_DATABASE_READY,
+ true,
+ "COLLECTIONS_SQLITE_DATABASE_READY constant value shall be the boolean true."
+ "The following string isn't properly escaped: '$toEscapeExpressions[$i]'"
+ );
+ }
+ }
+
+ /**
+ * @covers SQLiteCollection::isStatement
+ */
+ public function testStatements () {
+ $sqlQueries = [
+ "SELECT foo FROM bar",
+ "PRAGMA foo"
+ ];
+
+ $sqlStatements = [
+ "UPDATE bar SET foo = 'quux'",
+ "DELETE FROM bar WHERE foo = 'quux'",
+ "INSERT INTO bar (foo) VALUES ('quux')",
+ "REPLACE INTO bar (foo) VALUES ('quux')",
+ "INSERT INTO bar SELECT FROM baz"
+ ];
+
+ foreach ($sqlQueries as $sql) {
+ $this->assertFalse(
+ $this->collection->isStatement($sql),
+ "The query $sql should be considered as a query, not as a statement, to use SQLite3::query() and not SQLite3::exec()"
+ );
+ }
+
+ foreach ($sqlStatements as $sql) {
+ $this->assertTrue(
+ $this->collection->isStatement($sql),
+ "The query $sql should be considered as a statement, not as a query, as long as SQLite3 is concerned, to use SQLite3::exec() and not SQLite3::query()"