[sqlite] nested tree table porting from mysql -> sqlite

[ciz]

I have trouble to move up node on sqlite
on mysql i write:
UPDATE SET rgt = rgt + IF(rgt<%1,%2,-%3)
on sqlite i must rewrite query to
UPDATE SET rgt = (SELECT CASE WHEN (rgt < %1) THEN rgt + %2 ELSE rgt + -%3
END)
is here a way to create a trigger or similar on sqlite to support
IF(expression,int or string,int or string)
and use the same query?


detail code...

/* nested tree table */
/*
   create table catememo (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   root_id INTEGER,
   name varchar(110),
   lft INTEGER,
   rgt INTEGER,
   oldid INTEGER,
   attribute BLOB)");

   online demo http://www.klempert.de/nested_sets/demo/
   show tree
   SELECT n.*, round((n.rgt-n.lft-1)/2,0) AS childs,
   count(*)+(n.lft>1) AS level,
   ((min(p.rgt)-n.rgt-(n.lft>1))/2) > 0 AS lower,
   (( (n.lft-max(p.lft)>1) )) AS upper FROM catememo n,
   catememo p WHERE n.lft BETWEEN p.lft
   AND p.rgt AND
   (p.root_id = n.root_id) AND (p.id != n.id OR n.lft = 1)
   GROUP BY n.root_id,n.id ORDER BY n.root_id,n.lft"
*/

/* move node up */
/* mysql UPDATE SET rgt = rgt + IF(rgt<%1,%2,-%3)
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF()
returns expr2; otherwise it returns expr3. IF()
returns a numeric or string value, depending on the
context in which it is used.
mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'yes','no');
        -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
        -> 'no'
*/

/* sqlite translate */
QString handleleft = QString("SET lft= (SELECT CASE WHEN (lft < %1) "
                             "THEN lft + %2 ELSE lft + -%3 END), ")
                             .arg(nn1left)
                             .arg(inc)
                             .arg(desc);

QString handleright = QString("rgt= (SELECT CASE WHEN (rgt < %1) "
                              "THEN rgt + %2 ELSE rgt + -%3 END)")
                              .arg(nn1left)
                              .arg(inc)
                              .arg(desc);

QString sqlup = QString("UPDATE %1 ").arg(table);
sqlup.append(handleleft + handleright);
sqlup.append(QString(" WHERE    root_id= %1 AND lft>=%2
").arg(r2.value("root_id").toInt()).arg(nn2left));
sqlup.append(QString("AND rgt<=%1  ").arg(nn1right));
qDebug() << " sqlup ->  " << sqlup;
if (query.exec(sqlup)) {
  /* reload table nested tree */
}


_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users