Problem with TreeConnector and PDO / SQLITE

dactar · July 19, 2011, 10:40pm

Hello,

I’ve took the sample of dhtmlxConnector for tree 01_basic_connector.php and I adapt it for PDO / SQLite. I’ve loaded the dump.sql file for create the table tasks into a new sqlite database after adapt it for sqlite syntax.

CREATE TABLE IF NOT EXISTS `tasks` (
  `taskId` int(11) NOT NULL PRIMARY KEY,
  `parentId` int(11) DEFAULT '0',
  `taskName` varchar(250) DEFAULT NULL,
  `duration` float(9,1) DEFAULT '0.0',
  `work` float(9,1) DEFAULT '0.0',
  `start` date DEFAULT NULL,
  `finish` date DEFAULT NULL,
  `complete` int(11) DEFAULT '0',
  `predecessors` varchar(100) DEFAULT NULL,
  `resource` varchar(100) DEFAULT NULL
) ;

INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1000, 0, 'dhtmlXGrid', 22.0, 11.0, '2007-02-14', '2007-02-17', 50, '', 'BX');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1001, 1000, 'Version 1.1', 21.0, 1.0, '2007-02-07', '2007-02-24', 4, '', 'CW');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1002, 1001, 'Task 682400', 30.0, 1.0, '2007-02-07', '2007-02-17', 3, '', 'CW');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1003, 1001, 'Task 532497', 27.0, 13.0, '2007-02-09', '2007-02-22', 48, '', 'BX');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1004, 1001, 'Task 581814', 3.0, 1.0, '2007-02-06', '2007-02-25', 33, '', 'CW');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1005, 1001, 'Task 125488', 30.0, 12.0, '2007-02-08', '2007-02-25', 40, '', 'CW');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1006, 1001, 'Task 541992', 4.0, 2.0, '2007-02-11', '2007-02-19', 50, '', 'CW');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1007, 1001, 'Task 182020', 16.0, 8.0, '2007-02-11', '2007-02-21', 50, '', 'CW');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1008, 1001, 'Task 812934', 30.0, 16.0, '2007-02-13', '2007-02-20', 53, '', 'AZ');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1009, 1001, 'Task 729871', 6.0, 2.0, '2007-02-09', '2007-02-19', 33, '', 'CW');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1010, 1001, 'Task 311306', 3.0, 2.0, '2007-02-06', '2007-02-18', 66, '', 'BX');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1011, 1001, 'Task 463487', 4.0, 4.0, '2007-02-11', '2007-02-18', 100, '', 'BX');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1101, 1000, 'Version 1.2', 12.0, 5.0, '2007-02-10', '2007-02-18', 41, '', 'AZ');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1102, 1101, 'Task 228651', 3.0, 3.0, '2007-02-07', '2007-02-23', 100, '', 'CW');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1103, 1101, 'Task 319851', 7.0, 1.0, '2007-02-09', '2007-02-21', 14, '', 'BX');
INSERT INTO `tasks` (`taskId`, `parentId`, `taskName`, `duration`, `work`, `start`, `finish`, `complete`, `predecessors`, `resource`) VALUES (1104, 1101, 'Task 903672', 4.0, 4.0, '2007-02-13', '2007-02-25', 100, '', 'CW');
...
...
...

The adapted php file is now :

$db = new PDO("sqlite:db/demo");
require_once('php/connector/tree_connector.php');
require_once('php/connector/db_pdo.php');
$tree = new TreeConnector($db,"PDO");
$tree->enable_log("/tmp/log.txt");
$tree->render_table("tasks","taskId","taskName","","parentId");

There’s a big problem with PDO / SQLite, the xml generated contain only tree records :

<tree id="0">
  <item id="1000" text="dhtmlXGrid" child="-1">
    <item id="1001" text="Version 1.1" child="-1">
       <item id="1002" text="Task 682400" child="-1"/>
    </item>
  </item>
</tree>

On mysql (or PDO / mysql) there’s no problem, all records are here :

<tree id="0">
  <item id="1000" text="dhtmlXGrid" child="-1">
    <item id="1001" text="Version 1.1" child="-1">
    <item id="1002" text="Task 682400" child="-1"/>
    <item id="1003" text="Task 532497" child="-1"/>
    <item id="1004" text="Task 581814" child="-1"/>
    <item id="1005" text="Task 125488" child="-1"/>
    <item id="1006" text="Task 541992" child="-1"/>
    <item id="1007" text="Task 182020" child="-1"/>
    <item id="1008" text="Task 812934" child="-1"/>
    <item id="1009" text="Task 729871" child="-1"/>
    <item id="1010" text="Task 311306" child="-1"/>
    <item id="1011" text="Task 463487" child="-1"/>
  </item>
  <item id="1101" text="Version 1.2" child="-1">
    <item id="1102" text="Task 228651" child="-1"/>
    <item id="1103" text="Task 319851" child="-1"/>
...
...

So the problem is with PDO + SQLIte
As mentionned here docs.dhtmlx.com/doku.php?id=dhtm … ide_others , there’s a db_sqlite.php but this file is not in the dthmlxconnector archive. Si I can’t tell if the problem is related to SQLite or only PDO + SQLite
There’s NO problem if I use dynamic_loading

Thanks in advance for your help

@++
JC

Stanislav · July 20, 2011, 1:14pm

sql_lite wrapper attached

If issue still occurs - please provide the content of tmp/log.txt for the problematic scenario.
db_sqlite.zip (527 Bytes)

dactar · July 20, 2011, 5:16pm

Hello,

I’ve test the db_sqlite attached and it works BUT this connector is for sqlite v2 databases who are depreciated.

So, there’s no db_sqlite3 connector and the only solution (and the best) is the use of PDO who give access to sqlite v3 databases.

Here’s my /tmp/log.txt for PDO / SQLITE :

====================================
Log started, 20/07/2011 07:07:13
====================================

SELECT taskId,parentId,taskName FROM tasks WHERE parentId = '0'

SELECT taskId,parentId,taskName FROM tasks WHERE parentId = '1000'

SELECT taskId,parentId,taskName FROM tasks WHERE parentId = '1001'

SELECT taskId,parentId,taskName FROM tasks WHERE parentId = '1002'

Done in 0.0081031322479248s

Thanks for your help

@++
JC

Stanislav · July 21, 2011, 10:02am

The log records do not contain any visible errors, it may be some issue with PDO
Anyway - adapter for sqllite3 is attached

You can use it as

[code] $db = new SQLite3(‘mysqlitedb.db’);

require("../../codebase/grid_connector.php");
require("../../codebase/db_sqlite3.php");

$grid = new GridConnector($db,"SQLite3");
$grid->render_table("grid50000","item_id","item_nm,item_cd");[/code]

db_sqlite3.zip (501 Bytes)

dactar · July 21, 2011, 10:37pm

Hello,

I’ve test the db_sqlite3 attached and it works with sqlite v3 database I’ve now a workarround till PDO / SQLITE the problem is corrected

So the situation is with TreeConnector:

db_sqlite = OK with sqlite v2 database
db_sqlite3 = OK with sqlite v3 database
db_PDO = OK with mysql (and some others) database
db_PDO = Problem with sqlite v3 database (if dynamic_loading is true = OK)

Do you have reproduced the problem locally ?

Thanks

@++
JC

Stanislav · July 22, 2011, 11:00am

Yep, we was able to reconstruct the same issue locally, it seems that PDO lost the previous result set when new request to the sqllite db executed.

We will check what can be done, but most probably is caused by some issue in the pdo driver