envms · cbornhoft · Oct 1, 2018 · Oct 1, 2018 · Oct 1, 2018 · Oct 1, 2018
diff --git a/.travis.yml b/.travis.yml
@@ -3,6 +3,9 @@ php:
   - 7.1
   - 7.2
 
+services:
+  - mysql
+
 env:
   - DB_USER=root
 
@@ -17,4 +20,4 @@ notifications:
 
 os: linux
 group: stable
-dist: trusty
+dist: xenial
diff --git a/src/Queries/Base.php b/src/Queries/Base.php
@@ -230,7 +230,7 @@ private function debugger()
                 $debug .= $query;
 
                 foreach (debug_backtrace() as $backtrace) {
-                    if (isset($backtrace['file']) && !$this->regex->localFile($backtrace['file'])) {
+                    if (isset($backtrace['file']) && !$this->regex->compareLocation($backtrace['file'])) {
                         // stop at the first file outside the FluentPDO source
                         break;
                     }

diff --git a/src/Queries/Json.php b/src/Queries/Json.php
@@ -0,0 +1,56 @@
+<?php
+namespace Envms\FluentPDO\Queries;
+
+use Envms\FluentPDO\{Query, Utilities};
+
+/**
+ * Class Json
+ *
+ * @package Envms\FluentPDO\Queries
+ */
+class Json extends Common
+{
+
+    /** @var mixed */
+    protected $fromTable;
+    /** @var mixed */
+    protected $fromAlias;
+    /** @var boolean */
+    protected $convertTypes = false;
+
+    /**
+     * Json constructor
+     *
+     * @param Query  $fluent
+     * @param string $table
+     */
+    public function __construct(Query $fluent, string $table)
+    {
+        $clauses = [
+            'SELECT'   => ', ',
+            'JOIN'     => [$this, 'getClauseJoin'],
+            'WHERE'    => [$this, 'getClauseWhere'],
+            'GROUP BY' => ',',
+            'HAVING'   => ' AND ',
+            'ORDER BY' => ', ',
+            'LIMIT'    => null,
+            'OFFSET'   => null,
+            "\n--"     => "\n--",
+        ];
+
+        parent::__construct($fluent, $clauses);
+
+        // initialize statements
+        $tableParts = explode(' ', $table);
+        $this->fromTable = reset($tableParts);
+        $this->fromAlias = end($tableParts);
+
+        $this->statements['SELECT'][] = '';
+        $this->joins[] = $this->fromAlias;
+
+        if (isset($fluent->convertTypes) && $fluent->convertTypes) {
+            $this->convertTypes = true;
+        }
+    }
+
+}
diff --git a/tests/Queries/CommonTest.php b/tests/Queries/CommonTest.php
@@ -40,7 +40,7 @@ public function testFullJoin()
 
         self::assertEquals('SELECT article.*, user.name FROM article LEFT JOIN user ON user.id = article.user_id ORDER BY article.title',
             $query->getQuery(false));
-        self::assertEquals('Marek - article 1 Robert - article 2 Marek - article 3 ', $returnValue);
+        self::assertEquals('Marek - article 1 Robert - article 2 Marek - article 3 Kevin - artïcle 4 Chris - article 5 Chris - სარედაქციო 6 ', $returnValue);
     }
 
     public function testShortJoin()
@@ -146,7 +146,7 @@ public function testJoinInGroupBy()
 
         self::assertEquals('SELECT user.type, count(article.id) AS article_count FROM article LEFT JOIN user ON user.id = article.user_id GROUP BY user.type',
             $query->getQuery(false));
-        self::assertEquals(['0' => ['type' => 'admin', 'article_count' => '2'], '1' => ['type' => 'author', 'article_count' => '1']],
+        self::assertEquals(['0' => ['type' => 'admin', 'article_count' => '4'], '1' => ['type' => 'author', 'article_count' => '2']],
             $query->fetchAll());
     }
 

diff --git a/tests/Queries/InsertTest.php b/tests/Queries/InsertTest.php
@@ -41,6 +41,7 @@ public function testInsertUpdate()
     {
         $query = $this->fluent->insertInto('article', ['id' => 1])
             ->onDuplicateKeyUpdate([
+                'published_at' => '2011-12-10 12:10:00',
                 'title'   => 'article 1b',
                 'content' => new Envms\FluentPDO\Literal('abs(-1)') // let's update with a literal and a parameter value
             ]);
@@ -49,14 +50,15 @@ public function testInsertUpdate()
 
         $query2 = $this->fluent->insertInto('article', ['id' => 1])
             ->onDuplicateKeyUpdate([
+                'published_at' => '2011-12-10 12:10:00',
                 'title'   => 'article 1',
                 'content' => 'content 1',
             ]);
 
         $q2 = $this->fluent->from('article', 1);
 
-        self::assertEquals('INSERT INTO article (id) VALUES (?) ON DUPLICATE KEY UPDATE title = ?, content = abs(-1)', $query->getQuery(false));
-        self::assertEquals(['0' => '1', '1' => 'article 1b'], $query->getParameters());
+        self::assertEquals('INSERT INTO article (id) VALUES (?) ON DUPLICATE KEY UPDATE published_at = ?, title = ?, content = abs(-1)', $query->getQuery(false));
+        self::assertEquals([0 => '1', 1 => '2011-12-10 12:10:00', 2 => 'article 1b'], $query->getParameters());
         self::assertEquals('last_inserted_id = 1', 'last_inserted_id = ' . $query->execute());
         self::assertEquals(['id' => '1', 'user_id' => '1', 'published_at' => '2011-12-10 12:10:00', 'title' => 'article 1b', 'content' => '1'],
             $q->fetch());

diff --git a/tests/Queries/SelectTest.php b/tests/Queries/SelectTest.php
@@ -132,16 +132,16 @@ public function testWhereColumnName()
     {
         $query = $this->fluent->from('user')
             ->where('type = :type', [':type' => 'author'])
-            ->where('id > :id AND name <> :name', [':id' => 1, ':name' => 'Marek']);
+            ->where('id > :id AND name <> :name', [':id' => 3, ':name' => 'Marek']);
 
         $returnValue = '';
         foreach ($query as $row) {
             $returnValue = $row['name'];
         }
 
         self::assertEquals('SELECT user.* FROM user WHERE type = :type AND id > :id AND name <> :name', $query->getQuery(false));
-        self::assertEquals([':type' => 'author', ':id' => 1, ':name' => 'Marek'], $query->getParameters());
-        self::assertEquals('Robert', $returnValue);
+        self::assertEquals([':type' => 'author', ':id' => 3, ':name' => 'Marek'], $query->getParameters());
+        self::assertEquals('Kevin', $returnValue);
     }
 
     public function testWhereOr()
@@ -198,12 +198,13 @@ public function testCountable()
             ->from('article')
             ->select(null)
             ->select('title')
-            ->where('id > 1');
+            ->where('id > 1')
+            ->where('id < 4');
 
         $count = count($articles);
 
         self::assertEquals(2, $count);
-        self::assertEquals(['0' => ['title' => 'article 2'], '1' => ['title' => 'article 3']], $articles->fetchAll());
+        self::assertEquals([0 => ['title' => 'article 2'], 1 => ['title' => 'article 3']], $articles->fetchAll());
     }
 
     public function testWhereNotArray()
@@ -217,16 +218,16 @@ public function testWhereColNameEscaped()
     {
         $query = $this->fluent->from('user')
             ->where('`type` = :type', [':type' => 'author'])
-            ->where('`id` > :id AND `name` <> :name', [':id' => 1, ':name' => 'Marek']);
+            ->where('`id` > :id AND `name` <> :name', [':id' => 3, ':name' => 'Marek']);
 
         $rowDisplay = '';
         foreach ($query as $row) {
             $rowDisplay = $row['name'];
         }
 
         self::assertEquals('SELECT user.* FROM user WHERE `type` = :type AND `id` > :id AND `name` <> :name', $query->getQuery(false));
-        self::assertEquals([':type' => 'author', ':id' => '1', ':name' => 'Marek'], $query->getParameters());
-        self::assertEquals('Robert', $rowDisplay);
+        self::assertEquals([':type' => 'author', ':id' => '3', ':name' => 'Marek'], $query->getParameters());
+        self::assertEquals('Kevin', $rowDisplay);
     }
 
     public function testAliasesForClausesGroupbyOrderBy()
@@ -240,8 +241,8 @@ public function testFetch()
     {
         $queryPrint = $this->fluent->from('user', 1)->fetch('name');
         $queryPrint2 = $this->fluent->from('user', 1)->fetch();
-        $statement = $this->fluent->from('user', 3)->fetch();
-        $statement2 = $this->fluent->from('user', 3)->fetch('name');
+        $statement = $this->fluent->from('user', 5)->fetch();
+        $statement2 = $this->fluent->from('user', 5)->fetch('name');
 
         self::assertEquals('Marek', $queryPrint);
         self::assertEquals(['id' => '1', 'country_id' => '1', 'type' => 'admin', 'name' => 'Marek'], $queryPrint2);
@@ -254,30 +255,33 @@ public function testFetchPairsFetchAll()
         $result = $this->fluent->from('user')->fetchPairs('id', 'name');
         $result2 = $this->fluent->from('user')->fetchAll();
 
-        self::assertEquals(['1' => 'Marek', '2' => 'Robert'], $result);
+        self::assertEquals(['1' => 'Marek', '2' => 'Robert', '3' => 'Chris', '4' => 'Kevin'], $result);
         self::assertEquals([
-            '0' => ['id' => '1', 'country_id' => '1', 'type' => 'admin', 'name' => 'Marek'],
-            '1' => ['id' => '2', 'country_id' => '1', 'type' => 'author', 'name' => 'Robert']
+            0 => ['id' => '1', 'country_id' => '1', 'type' => 'admin', 'name' => 'Marek'],
+            1 => ['id' => '2', 'country_id' => '1', 'type' => 'author', 'name' => 'Robert'],
+            2 => ['id' => '3', 'country_id' => '2', 'type' => 'admin', 'name' => 'Chris'],
+            3 => ['id' => '4', 'country_id' => '2', 'type' => 'author', 'name' => 'Kevin']
         ], $result2);
     }
 
     public function testFetchAllWithParams()
     {
         $result = $this->fluent->from('user')->fetchAll('id', 'type, name');
 
-        self::assertEquals(['1' => ['id' => '1', 'type' => 'admin', 'name' => 'Marek'], '2' => ['id' => '2', 'type' => 'author', 'name' => 'Robert']],
        self::assertEquals([1 => ['id' => '1', 'type' => 'admin', 'name' => 'Marek'], 2 => ['id' => '2', 'type' => 'author', 'name' => 'Robert'],
+                            3 => ['id' => '3', 'type' => 'admin', 'name' => 'Chris'], 4 => ['id' => '4', 'type' => 'author', 'name' => 'Kevin']],
             $result);
     }
 
     public function testFetchColumn()
     {
-        $printColumn = $this->fluent->from('user', 1)->fetchColumn();
-        $printColumn2 = $this->fluent->from('user', 1)->fetchColumn(3);
-        $statement = $this->fluent->from('user', 3)->fetchColumn();
-        $statement2 = $this->fluent->from('user', 3)->fetchColumn(3);
+        $printColumn = $this->fluent->from('user', 3)->fetchColumn();
+        $printColumn2 = $this->fluent->from('user', 3)->fetchColumn(3);
+        $statement = $this->fluent->from('user', 5)->fetchColumn();
+        $statement2 = $this->fluent->from('user', 5)->fetchColumn(3);
 
-        self::assertEquals(1, $printColumn);
-        self::assertEquals('Marek', $printColumn2);
+        self::assertEquals(3, $printColumn);
+        self::assertEquals('Chris', $printColumn2);
         self::assertEquals(false, $statement);
         self::assertEquals(false, $statement2);
     }

diff --git a/tests/Queries/UpdateTest.php b/tests/Queries/UpdateTest.php
@@ -34,12 +34,12 @@ public function testUpdate()
 
         self::assertEquals('UPDATE country SET name = ? WHERE id = ?', $query->getQuery(false));
         self::assertEquals(['0' => 'aikavolS', '1' => '1'], $query->getParameters());
-        self::assertEquals(['id' => '1', 'name' => 'aikavolS'], $query2->fetch());
+        self::assertEquals(['id' => '1', 'name' => 'aikavolS', 'details' => '{"gdp": 90.75, "pop": 5456300, "name": "Slovensko"}'], $query2->fetch());
 
         $this->fluent->update('country')->set('name', 'Slovakia')->where('id', 1)->execute();
         $query3 = $this->fluent->from('country')->where('id', 1);
 
-        self::assertEquals(['id' => '1', 'name' => 'Slovakia'], $query3->fetch());
+        self::assertEquals(['id' => '1', 'name' => 'Slovakia', 'details' => '{"gdp": 90.75, "pop": 5456300, "name": "Slovensko"}'], $query3->fetch());
     }
 
     public function testUpdateLiteral()

diff --git a/tests/RegexTest.php b/tests/RegexTest.php
@@ -87,7 +87,7 @@ public function testTableJoin()
         $join = $this->regex->tableJoin("user");
         self::assertEquals(1, $join);
 
-        $join = $this->regex->tableJoin("user.");
+        $join = $this->regex->tableJoin("`user`.");
         self::assertEquals(1, $join);
 
         $join = $this->regex->tableJoin("'''");
@@ -102,7 +102,7 @@ public function testTableJoinFull()
         $join = $this->regex->tableJoinFull("user.");
         self::assertEquals(1, $join);
 
-        $join = $this->regex->tableJoinFull("user.column");
+        $join = $this->regex->tableJoinFull("`user`.`column`");
         self::assertEquals(1, $join);
 
         $join = $this->regex->tableJoinFull("user .column");

diff --git a/tests/_resources/fluentdb.sql b/tests/_resources/fluentdb.sql
@@ -8,62 +8,72 @@ SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
 
 DROP TABLE IF EXISTS `article`;
 CREATE TABLE `article` (
-  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-  `user_id` int(11) unsigned NOT NULL DEFAULT 0,
-  `published_at` datetime NOT NULL DEFAULT 0,
-  `title` varchar(100) NOT NULL DEFAULT '',
-  `content` text NOT NULL DEFAULT '',
+  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+  `user_id` INT(10) UNSIGNED NOT NULL DEFAULT 0,
+  `published_at` DATETIME NOT NULL DEFAULT 0,
+  `title` VARCHAR(100) NOT NULL DEFAULT '',
+  `content` TEXT NOT NULL DEFAULT '',
   PRIMARY KEY (`id`),
   KEY `user_id` (`user_id`),
-  CONSTRAINT `article_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
+  CONSTRAINT `fk_article_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 INSERT INTO `article` (`id`, `user_id`, `published_at`, `title`, `content`) VALUES
 (1, 1, '2011-12-10 12:10:00', 'article 1', 'content 1'),
 (2, 2, '2011-12-20 16:20:00', 'article 2', 'content 2'),
-(3, 1, '2012-01-04 22:00:00', 'article 3', 'content 3');
+(3, 1, '2012-01-04 22:00:00', 'article 3', 'content 3'),
+(4, 4, '2018-07-07 15:15:07', 'artïcle 4', 'content 4'),
+(5, 3, '2018-10-01 01:10:01', 'article 5', 'content 5'),
+(6, 3, '2019-01-21 07:00:00', 'სარედაქციო 6', '함유량 6');
 
 DROP TABLE IF EXISTS `comment`;
 CREATE TABLE `comment` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `article_id` int(11) unsigned NOT NULL,
-  `user_id` int(11) unsigned NOT NULL,
-  `content` varchar(100) NOT NULL,
+  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+  `article_id` INT(10) UNSIGNED NOT NULL,
+  `user_id` INT(10) UNSIGNED NOT NULL,
+  `content` VARCHAR(100) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `article_id` (`article_id`),
   KEY `user_id` (`user_id`),
-  CONSTRAINT `comment_ibfk_1` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`),
-  CONSTRAINT `comment_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
+  CONSTRAINT `fk_comment_article_id` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`),
+  CONSTRAINT `fk_comment_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 INSERT INTO `comment` (`id`, `article_id`, `user_id`, `content`) VALUES
 (1, 1, 1, 'comment 1.1'),
 (2, 1, 2, 'comment 1.2'),
-(3, 2, 1, 'comment 2.1');
+(3, 2, 1, 'comment 2.1'),
+(4, 5, 4, 'cömment 5.4'),
+(5, 6, 2, 'ਟਿੱਪਣੀ 6.2');
 
 DROP TABLE IF EXISTS `country`;
 CREATE TABLE `country` (
-  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-  `name` varchar(20) NOT NULL,
+  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+  `name` VARCHAR(20) NOT NULL,
+  `details` JSON NOT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-INSERT INTO `country` (`id`, `name`) VALUES
-(1, 'Slovakia');
+INSERT INTO `country` (`id`, `name`, `details`) VALUES
+(1, 'Slovakia', '{"name": "Slovensko", "pop": 5456300, "gdp": 90.75}'),
+(2, 'Canada', '{"name": "Canada", "pop": 37198400, "gdp": 1592.37}'),
+(3, 'Germany', '{"name": "Deutschland", "pop": 82385700, "gdp": 3486.12}');
 
 DROP TABLE IF EXISTS `user`;
 CREATE TABLE `user` (
-  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-  `country_id` int(11) unsigned NOT NULL,
-  `type` enum('admin','author') NOT NULL,
-  `name` varchar(20) NOT NULL,
+  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+  `country_id` INT(10) UNSIGNED NOT NULL,
+  `type` ENUM('admin','author') NOT NULL,
+  `name` VARCHAR(20) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `country_id` (`country_id`),
-  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`)
+  CONSTRAINT `fk_user_country_id` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 INSERT INTO `user` (`id`, `country_id`, `type`, `name`) VALUES
 (1, 1, 'admin', 'Marek'),
-(2, 1, 'author', 'Robert');
+(2, 1, 'author', 'Robert'),
+(3, 2, 'admin', 'Chris'),
+(4, 2, 'author', 'Kevin');
 
--- 2018-08-24 14:38:17
+-- 2018-10-01 07:42:17
diff --git a/tests/_resources/init.php b/tests/_resources/init.php
@@ -2,10 +2,10 @@
 
 // determine the test environment first
 if (getenv('TRAVIS')) {
-    $pdo = new PDO("mysql:dbname=fluentdb;host=localhost", "root");
+    $pdo = new PDO("mysql:dbname=fluentdb;host=localhost;charset=utf8", "root");
 }
 else {
-    $pdo = new PDO("mysql:dbname=fluentdb;host=localhost", "vagrant", "vagrant");
+    $pdo = new PDO("mysql:dbname=fluentdb;host=localhost;charset=utf8", "vagrant", "vagrant");
 }
 
 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);