From 8ee8780ebf38e20cf18c8cf97d7a5b86255920f3 Mon Sep 17 00:00:00 2001
From: Dan Poltawski <dan.poltawski@lancaster.ac.uk>
Date: Tue, 27 Oct 2009 23:25:07 +0000
Subject: [PATCH] lib/dml - 'complicated sql' unit test MDL-19645

I have just added one specific example SQL syntax which was breaking on
earlier versions of sqlite. We need to add more variations used throughout
moodle so different engines can be properly tested for compatibility.
---
 lib/dml/simpletest/testdml.php | 35 ++++++++++++++++++++++++++++++++++
 1 file changed, 35 insertions(+)

diff --git a/lib/dml/simpletest/testdml.php b/lib/dml/simpletest/testdml.php
index 0ab3aae6ba..cf931ed96a 100755
--- a/lib/dml/simpletest/testdml.php
+++ b/lib/dml/simpletest/testdml.php
@@ -2332,6 +2332,41 @@ class dml_test extends UnitTestCase {
             $this->assertTrue(true, 'DB Transactions not supported. Test skipped');
         }
     }
+
+    /**
+     * Test some more complex SQL syntax which moodle uses and depends on to work
+     * useful to determine if new database libraries can be supported.
+     */
+    public function test_get_records_sql_complicated() {
+        global $CFG;
+        $DB = $this->tdb;
+        $dbman = $DB->get_manager();
+
+        $table = $this->get_test_table();
+        $tablename = $table->getName();
+
+        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
+        $table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
+        $dbman->create_table($table);
+        $this->tables[$tablename] = $table;
+
+        $DB->insert_record($tablename, array('course' => 3));
+        $DB->insert_record($tablename, array('course' => 3));
+        $DB->insert_record($tablename, array('course' => 5));
+        $DB->insert_record($tablename, array('course' => 2));
+
+        // we have sql like this in moodle, this syntax breaks on older versions of sqlite for example..
+        $sql = 'SELECT a.id AS id, a.course AS course
+                FROM {'.$tablename.'} a
+                JOIN (SELECT * FROM {'.$tablename.'}) b
+                ON a.id = b.id
+                WHERE a.course = ?';
+
+        $this->assertTrue($records = $DB->get_records_sql($sql, array(3)));
+        $this->assertEqual(2, count($records));
+        $this->assertEqual(1, reset($records)->id);
+        $this->assertEqual(2, next($records)->id);
+    }
 }
 
 /**
-- 
2.39.5