home *** CD-ROM | disk | FTP | other *** search
/ PC World 2008 February (DVD) / PCWorld_2008-02_DVD.iso / v cisle / PHP / PHP.exe / EasyPHP-2.0b1-setup.exe / {app} / sqlitemanager / include / SQLiteTriggerProperties.class.php < prev    next >
Encoding:
PHP Script  |  2006-04-18  |  12.1 KB  |  288 lines
  1. <?php
  2. /**
  3. * Web based SQLite management
  4. * Show and manage 'FUNCTION' properties
  5. * @package SQLiteManager
  6. * @author FrΘdΘric HENNINOT
  7. * @version $Id: SQLiteTriggerProperties.class.php,v 1.34 2006/04/16 18:56:57 freddy78 Exp $ $Revision: 1.34 $
  8. */
  9.  
  10. class SQLiteTriggerProperties {
  11.  
  12.     /**
  13.     * reference of the connection object
  14.     *
  15.     * @access public
  16.     * @var resource
  17.     */
  18.     var $connId;
  19.     
  20.     /**
  21.     * TRIGGER name
  22.     *
  23.     * @access private
  24.     * @var string
  25.     */
  26.     var $trigger;
  27.     
  28.     /**
  29.     * this TRIGGER exist?
  30.     *
  31.     * @access private
  32.     * @var bool
  33.     */
  34.     var $isExist;
  35.     
  36.     /**
  37.     * TRIGGER properties
  38.     * @access private
  39.     * @var array
  40.     */
  41.     var $triggerProperties;
  42.     
  43.     /**
  44.     * Class constructor
  45.     *
  46.     * @access public
  47.     * @param object $conn reference to the connection object
  48.     */
  49.     function SQLiteTriggerProperties($conn){
  50.         $this->connId = $conn;
  51.         if($GLOBALS['trigger'] && ($GLOBALS['action']!='add')) {
  52.             $this->trigger = $GLOBALS['trigger'];
  53.         } elseif($GLOBALS['TriggerName']){
  54.             $this->trigger = $GLOBALS['TriggerName'];
  55.         } else return false;        
  56.         $this->isExist = $this->triggerExist($this->trigger);
  57.         return $this->isExist;
  58.     }
  59.     
  60.     /**
  61.     * Verify if the TRIGGER exist
  62.     *
  63.     * @access public
  64.     * @param string $trigger
  65.     */
  66.     function triggerExist($trigger){
  67.         if(empty($trigger)) $trigger = $this->trigger;
  68.         $query = "SELECT sql FROM sqlite_master WHERE type='trigger' AND name=".quotes($trigger).";";
  69.         if($this->connId->getResId($query)){
  70.             $triggerSQL = $this->connId->connId->fetch_single();
  71.             if(!$triggerSQL) return false;
  72.             $this->triggerProperties = $triggerSQL;
  73.             return true;
  74.         } else return false;            
  75.     }
  76.     
  77.     /**
  78.     * save current TRIGGER properties
  79.     *
  80.     * @access public
  81.     */
  82.     function saveProp(){
  83.         if(($GLOBALS['action']=='delete') || ($this->isExist)){
  84.             $queryDisplay = $query[] = 'DROP TRIGGER '.brackets($this->trigger).';';
  85.         }
  86.         if($GLOBALS['action']!='delete'){
  87.             if($this->isExist) $triggername = $this->trigger;
  88.             else $triggername = $_POST['TriggerName'];
  89.             $queryCreate = 'CREATE TRIGGER '.brackets($triggername).' '.$_POST['TriggerMoment'];
  90.             if($_POST['TriggerMoment']!='') $queryCreate .= ' ';
  91.             $queryCreate .= $_POST['TriggerEvent'].' ';
  92.             if($_POST['TriggerEvent']=='UPDATE OF') $queryCreate .= $_POST['ColumnList'].' ';
  93.             $queryCreate .= 'ON '.brackets($_POST['TriggerOn']).' '.$_POST['TriggerAction'];
  94.             if($_POST['TriggerAction']!='') $queryCreate .= ' ';
  95.             if($_POST['TriggerCondition']=='WHEN') $queryCreate .= 'WHEN '.$_POST['ConditionList'].' ';
  96.             $queryCreate .= "\n".'BEGIN '."\n".stripslashes($_POST['TriggerStep'])."\n".' END;'."\n";
  97.             $queryDisplay = $query[] = $queryCreate;
  98.             $this->triggerProperties = $queryCreate;
  99.         }            
  100.         $errorMessage = '';
  101.         foreach($query as $req){
  102.             $this->connId->getResId("BEGIN;");
  103.             $res = $this->connId->getResId($req);
  104.             $this->connId->getResId("COMMIT;");
  105.             if(!$res){
  106.                 $errorMessage .= $GLOBALS['traduct']->get(9).' '.$errorCode.' : '.@$this->connId->connId->getError()."\n";
  107.             }
  108.         }
  109.         displayQuery($queryDisplay);
  110.         if(!empty($errorMessage)) displayError($errorMessage);
  111.         if($GLOBALS['action']!='delete') {
  112.             $this->propView();
  113.             echo "<script  type=\"text/javascript\">parent.left.location='left.php?dbsel=".$GLOBALS["dbsel"]."';</script>";
  114.         } else {
  115.             echo "<script  type=\"text/javascript\">parent.left.location='left.php?dbsel=".$GLOBALS["dbsel"]."'; parent.main.location='main.php?dbsel=".$GLOBALS["dbsel"]."';</script>";
  116.         }
  117.     }
  118.  
  119.     /**
  120.     * Display the current TRIGGER properties
  121.     *
  122.     * @access public
  123.     */
  124.     function propView(){
  125.         $tabTrigInfo = $this->extractProperties($this->triggerProperties);
  126.         $triggView = 'CREATE TRIGGER '.brackets($this->trigger).' '.$tabTrigInfo['TriggerMoment'];
  127.         if($tabTrigInfo['TriggerMoment']!='') $triggView .= ' ';
  128.         $triggView .= $tabTrigInfo['TriggerEvent'].$tabTrigInfo['ColumnList'].' ';
  129.         $triggView .= 'ON '.brackets($tabTrigInfo['TriggerOn']).' '.$tabTrigInfo['TriggerAction'];
  130.         if($tabTrigInfo['TriggerAction']!='') $triggView .= ' ';
  131.         if($tabTrigInfo['TriggerCondition']=='WHEN ') $triggView .= 'WHEN '.$tabTrigInfo['ConditionList'].' ';
  132.         $triggView .= "\n".'BEGIN '."\n".$tabTrigInfo["TriggerStep"]."\n".' END;'."\n";
  133.         echo '<br>';
  134.         echo '    <table cellpadding="2" cellspacing="0" width="90%" class="viewProp">
  135.                     <tr class="viewPropTitle"><td align="right" width="20%" class="viewPropTitle">'.$GLOBALS['traduct']->get(19)." : </td><td align='center' class='viewPropTitle'>".$this->trigger."</td></tr>
  136.                     <tr><td align='right' class='viewProp'>".$GLOBALS['traduct']->get(53)." : </td><td class='viewProp'>".nl2br($triggView)."</td></tr>";
  137.         echo '        </table>';
  138.         echo '<div align="center">';
  139.         if(!$GLOBALS['workDb']->isReadOnly() && displayCondition('properties')) echo "<a href=\"main.php?dbsel=".$GLOBALS["dbsel"]."&trigger=".$this->trigger."&action=modify\" class='base' target=\"main\">".$GLOBALS['traduct']->get(14).'</a>';
  140.         else echo '<span class="base"><i>'.$GLOBALS['traduct']->get(14).'</i></span>';
  141.         echo str_repeat(' ', 10);
  142.         if(!$GLOBALS['workDb']->isReadOnly() && displayCondition('del')) echo '<a href="main.php?dbsel='.$GLOBALS['dbsel'].'&trigger='.$this->trigger.'&action=delete" class="base" target="main">'.$GLOBALS['traduct']->get(15).'</a>';
  143.         else echo '<span class="base"><i>'.$GLOBALS['traduct']->get(15).'</i></span>';
  144.         echo '</div>';
  145. }
  146.     
  147.     /**
  148.     * Display the current TRIGGER form for add or modify
  149.     */
  150.     function triggerEditForm(){
  151.         if($this->isExist) $tabTrigInfo = $this->extractProperties($this->triggerProperties);
  152.         else $tabTrigInfo = array('TriggerMoment'=>'', 'TriggerEvent'=>'', 'TriggerOn'=>'', 'TriggerCondition'=>'', 'TriggerStep'=>'');
  153.         echo '<br><center>';
  154.         if($GLOBALS['action']=='add') echo '<h4>'.$GLOBALS['traduct']->get(54).'</h4>';
  155.         else echo '<h4>'.$GLOBALS['traduct']->get(55).' : '.$this->trigger.'</h4>';
  156.         echo "    <script  type=\"text/javascript\">
  157.                 function checkColumn(){
  158.                     base=document.forms['triggerprop'];
  159.                     if(base.TriggerEvent.selectedIndex==3) afficheCalque('column');
  160.                     else cacheCalque('column');
  161.                     return;
  162.                 }
  163.                 function checkCondition(){
  164.                     base=document.forms['triggerprop'];
  165.                     if(base.TriggerCondition.selectedIndex==1) afficheCalque('condition');
  166.                     else cacheCalque('condition');
  167.                     return;
  168.                 }
  169.                 </script>";
  170.         echo "<form name=\"triggerprop\" action=\"main.php?dbsel=".$GLOBALS["dbsel"]."\" method=\"POST\" target=\"main\">";
  171.         echo "    <table cellpadding=2 cellspacing=0>
  172.                     <tr><td align='right' class='viewPropTitle'>".$GLOBALS['traduct']->get(19)." :</td><td class='viewProp'><input type=\"text\" name=\"TriggerName\" value=\"".$this->trigger."\"></td>
  173.                     <tr><td align='right' class='viewPropTitle'>".$GLOBALS['traduct']->get(56)." :</td><td class='viewProp'>
  174.                         <select name='TriggerMoment'>
  175.                             <option value=\"\"></option>
  176.                             <option value='BEFORE'".(($tabTrigInfo["TriggerMoment"]=="BEFORE")? " selected" : "" ).">BEFORE</option>
  177.                             <option value='AFTER'".(($tabTrigInfo["TriggerMoment"]=="AFTER")? " selected" : "" ).">AFTER</option>
  178.                             <option value='INSTEAD OF'".(($tabTrigInfo["TriggerMoment"]=="INSTEAD OF")? " selected" : "" ).">INSTEAD OF</option>
  179.                         </select></td>
  180.                     <tr><td align='right' class='viewPropTitle'>".$GLOBALS['traduct']->get(57)." :</td><td class='viewProp'>
  181.                             <div>
  182.                                 <div style='float: left'>
  183.                                     <select name='TriggerEvent' onChange='checkColumn()'>
  184.                                         <option value='DELETE'".(($tabTrigInfo["TriggerEvent"]=="DELETE")? " selected" : "" ).">DELETE</option>
  185.                                         <option value='INSERT'".(($tabTrigInfo["TriggerEvent"]=="INSERT")? " selected" : "" ).">INSERT</option>
  186.                                         <option value='UPDATE'".(($tabTrigInfo["TriggerEvent"]=="UPDATE")? " selected" : "" ).">UPDATE</option>
  187.                                         <option value='UPDATE OF'".(($tabTrigInfo["TriggerEvent"]=="UPDATE OF")? " selected" : "" ).">UPDATE OF</option>
  188.                                     </select>
  189.                                 </div>
  190.                                 <div id='column' style='float: right'>
  191.                                     <input type=\"text\" name=\"ColumnList\" value=\"".(($tabTrigInfo["TriggerEvent"]=="UPDATE OF ")? $tabTrigInfo["ColumnList"] : "" )."\" size=40>
  192.                                 </div>
  193.                             </div>
  194.                         </td>
  195.                     <tr><td align='right' class='viewPropTitle'>".$GLOBALS['traduct']->get(58)." :</td><td class='viewProp'>
  196.                         <select name='TriggerOn'>".$this->getOn($tabTrigInfo["TriggerOn"])."</select></td>
  197.                     <tr><td align='right' class='viewPropTitle'>".$GLOBALS['traduct']->get(33)." :</td><td class='viewProp'>
  198.                         <select name='TriggerAction'><option value=''></option>
  199.                         <option value='FOR EACH ROW'>FOR EACH ROW</option>
  200.                         <option value='FOR EACH STATEMENT'>FOR EACH STATEMENT</option></select></td>
  201.                     <tr><td align='right' class='viewPropTitle'>".$GLOBALS['traduct']->get(59)." :</td><td class='viewProp'>
  202.                         <div>
  203.                         <div style='float: left'>
  204.                             <select name='TriggerCondition' onChange='checkCondition();'>
  205.                                 <option value=''></option>
  206.                                 <option value='WHEN'".(($tabTrigInfo["TriggerCondition"]=="WHEN ")? " selected" : "" ).">WHEN</option>
  207.                             </select>
  208.                         </div>
  209.                         <div id='condition' style='float: right'>
  210.                             <input type=\"text\" name=\"ConditionList\" value=\"".(($tabTrigInfo["TriggerCondition"]=="WHEN ")? $tabTrigInfo["ConditionList"] : "" )."\" size=40>
  211.                         </div>
  212.                         </div>
  213.                         </td>
  214.                     </tr>
  215.                     <tr><td align='right' class='viewPropTitle'>".$GLOBALS['traduct']->get(60)." :</td><td class='viewProp'><textarea name='TriggerStep' cols=60 rows=4>".htmlentities(trim($tabTrigInfo["TriggerStep"]), ENT_NOQUOTES, $GLOBALS['charset'])."</textarea></td>
  216.                 </table>";
  217.         echo "<input type=\"hidden\" name=\"trigger\" value=\"".$this->trigger."\">\n";
  218.         echo "<input type=\"hidden\" name=\"action\" value=\"save\">\n";
  219.         echo "<input class=\"button\" type=\"submit\" value=\"".$GLOBALS['traduct']->get(51)."\">";
  220.         echo "</form></center>";
  221.         echo "<script  type=\"text/javascript\">checkColumn();checkCondition();</script>";
  222.  
  223.     }
  224.     
  225.     /**
  226.     * Get all table and view for 'ON' TRIGGER properties
  227.     *
  228.     * @access private
  229.     * @param string $selected current use
  230.     */
  231.     function getOn($selected){
  232.         $query = "SELECT name, type FROM sqlite_master WHERE (type='table' OR type='view') ORDER BY name;";
  233.         $listOn = '';
  234.         if($this->connId->connId->query($query)){            
  235.             while($ligne = $this->connId->connId->fetch_array(null, SQLITE_ASSOC)){
  236.                 $listOn .= '<option value="'.$ligne['name'].'"'.(($ligne['name']==$selected)? ' selected="selected"' : '' ).'>'.$ligne['name'].'('.$ligne['type'].')</option>';
  237.             }
  238.         }
  239.         return $listOn;        
  240.     }
  241.     
  242.     /**
  243.     * Extract All properties in the SQL TRIGGER Properties
  244.     *
  245.     * @access private
  246.     * @param string $sql SQL TRIGGER properties
  247.     */
  248.     function extractProperties($sql){
  249.         $out = array('TriggerMoment'=>'', 'TriggerEvent'=>'', 'ColumnList'=>'', 'TriggerAction'=>'', 'TriggerCondition'=>'', 'TriggerOn'=>'', 'TriggerStep'=>'');
  250.         $sql = str_replace("\n", ' ', $sql);
  251.         preg_match('/CREATE[[:space:]](.*)[[:space:]]ON/i', $sql, $StartTrig);
  252.         if($StartTrig[1]) $firstSubString = $StartTrig[1];
  253.         if(eregi('BEFORE', $firstSubString))     $out['TriggerMoment'] = 'BEFORE';
  254.         if(eregi('AFTER', $firstSubString))     $out['TriggerMoment'] = 'AFTER';
  255.         if(eregi('DELETE', $firstSubString))     $out['TriggerEvent'] = 'DELETE';
  256.         elseif(eregi('INSERT', $firstSubString))     $out['TriggerEvent'] = 'INSERT';
  257.         elseif(eregi('UPDATE OF', $firstSubString))     {
  258.             preg_match('/OF[[:space:]](.*)[[:space:]]ON/i', $sql, $colList);
  259.             $out['TriggerEvent']     = 'UPDATE OF ';
  260.             if($colList[1]) $out['ColumnList']        = trim($colList[1]);
  261.         } elseif(eregi('UPDATE', $sql))     $out['TriggerEvent'] = 'UPDATE';
  262.         
  263.         if(eregi('FOR EACH ROW', $sql)) {
  264.             $out['TriggerAction'] = 'FOR EACH ROW';
  265.             $searchTable = 'FOR';
  266.         }
  267.         if(eregi('FOR EACH STATEMENT', $sql)){
  268.             $out['TriggerAction'] = 'FOR EACH STATEMENT';
  269.             $searchTable = 'FOR';
  270.         }            
  271.         if(eregi('WHEN', $sql))     {
  272.             $searchTable = 'WHEN';
  273.             preg_match('/WHEN[[:space:]](.*)[[:space:]]|BEGIN/i', $sql, $condList);
  274.             $out['TriggerCondition'] = 'WHEN ';
  275.             if($condList[1]) $out['ConditionList'] = trim($condList[1]);
  276.         }
  277.         if(!isset($searchTable)) $searchTable = 'BEGIN';
  278.         preg_match('/ON[[:space:]](.*)[[:space:]]'.$searchTable.'/i', $sql, $tabList);
  279.         if($tabList[1]) $out['TriggerOn'] = trim($tabList[1]);
  280.         $Begin = stristr($sql, 'BEGIN');
  281.         preg_match('/BEGIN(.*)END/i', $sql, $stepList);
  282.         if($stepList[1]) $out['TriggerStep'] = $stepList[1];
  283.         return $out;
  284.     }
  285. }
  286.  
  287. ?>
  288.