Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id C4CDC9FB2CC for ; Thu, 9 Nov 2006 21:00:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.60]) (amavisd-new, port 10024) with ESMTP id 29979-05 for ; Thu, 9 Nov 2006 21:00:18 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.224]) by postgresql.org (Postfix) with ESMTP id 017B09FB2C4 for ; Thu, 9 Nov 2006 21:00:17 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id 50so258384wri for ; Thu, 09 Nov 2006 17:00:16 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=Oj1UFmVQZ1f28lStjRbdEi64InrcDFvRs90SWKIIgpKP1wt2eQCVoKKDSTOuq/aW8Q2+9EG0RonLRcOFRTh8MMuJ0TbWxSaNklCizvwV6kmpNzleQsLzAb4xiePbSQed6aBJuRwci3vPg7X7GIRYJEuYhju6R4fY32nxtJ5FWEg= Received: by 10.65.84.6 with SMTP id m6mr2234906qbl.1163120415814; Thu, 09 Nov 2006 17:00:15 -0800 (PST) Received: by 10.65.249.11 with HTTP; Thu, 9 Nov 2006 17:00:15 -0800 (PST) Message-ID: <7b872b430611091700r6412b1d0ld71c83e70494b3d1@mail.gmail.com> Date: Fri, 10 Nov 2006 04:30:15 +0330 From: "Servers24 Network" To: pgsql-php@postgresql.org Subject: Re: PgSQL and integration between 2 PHP programs and a Java program MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_23347_15434436.1163120415779" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/35 X-Sequence-Number: 3652 ------=_Part_23347_15434436.1163120415779 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi First tI should thank Brew and Juan for their kind help. Second it seems that the php applications doesn't use ADODB as here's a sample DB connection function of one of the PHP sctipts : [code] class DB { function DB(){ $this->values = array(); }//DB function connect() { $this->conn_id = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS,1); mysql_select_db(SQL_DB,$this->conn_id); $version = $this->version(); if($version >= 4.1){ $encoding = extract_encoding("mysql"); if($encoding){ $sql_query = "SET NAMES '".$encoding."'"; $this->execute($sql_query); }//if }//if }//connect function disconnect(){ if($this->conn_id) @mysql_close($this->conn_id); }//disconnect function version(){ $sql_query = "SELECT VERSION()"; $version = $this->single($sql_query); $version = ereg_replace("[^0-9|\.]","",$version); return $version; }//version function execute($sql_query) { if(!$this->conn_id){ $this->connect(); }//if preg_match_all("/\:[A-Za-z|0-9|_]{1,}\:/",$sql_query,$matches); $patterns = $matches[0]; while(list(,$val) = each($patterns)){ $key = substr($val,1,strlen($val)-2); $value = stripslashes($this->values[$key]); $sql_query = hs_ereg_replace($val,mysql_escape_string($value),$sql_query); }//while reset($this->values); $this->sql_result = mysql_query($sql_query,$this->conn_id); if(!$this->sql_result && !hs_ereg("^LOCK TABLES",$sql_query) && $sql_query!="UNLOCK TABLES"){ die($sql_query.":".mysql_error($this->conn_id)); }//if return $this->sql_result; }//execute function insert($sql_query){ if((!$this->sql_result)||($sql_query != $this->sql_query)){ $this->execute($sql_query); }//if $this->clean(); return mysql_insert_id($this->conn_id); }//insert function update($sql_query){ if((!$this->sql_result)||($sql_query != $this->sql_query)){ $this->execute($sql_query); }//if $this->clean(); return mysql_affected_rows($this->conn_id); }//update function fetch($sql_query){ if((!$this->sql_result)||($sql_query != $this->sql_query)){ $this->execute($sql_query); }//if $this->sql_query = $sql_query; $result = @mysql_fetch_object($this->sql_result); if(!$result) $this->clean(); return $result; }//fetch function row($sql_query){ if((!$this->sql_result)||($sql_query != $this->sql_query)){ $this->execute($sql_query); }//if $this->sql_query = $sql_query; $result = @mysql_fetch_assoc($this->sql_result); if(!$result) $this->clean(); return $result; }//row function result($sql_query){ $ret_array = array(); while($this->res = $this->row($sql_query)){ $ret_array[] = $this->res; }//while @mysql_free_result($this->res); $this->clean(); return $ret_array; }//result function single($sql_query){ $this->execute($sql_query); $this->clean(); return @mysql_result($this->sql_result,0); }//single function lock($table){ $sql_query="LOCK TABLES `".$table."` WRITE"; $this->execute($sql_query); }//lock function unlock(){ $sql_query="UNLOCK TABLES"; $this->execute($sql_query); }//unlock function clean(){ $this->sql_query = ""; }//clean }//DB class $DB = new DB; [/code] Well, this way I think I would need very much modification in source codes to make the PHP scripts talk to a PgSQL server :( After all I have thought of 3 methods of integration. I would like everyone who have an experience to share it with me and vote for the best methd that comes to your mind : 1. change the PHP programs somehow that the registration and password modification ( and maybe profile editing ) won't be accessed. This way I will write a centralized registeration and editing script where users can register and update their profile. When a user is registered or updated all the new information including username, password, name, last_name, address an etc will be updated in all connected databased + LDAP server. This way for example user "sample" with pass "sample" will be stored in almost 10 databases + LDAP server. Then each application will read from its own database ( MySQL, PgSQL or LDAP ) and authenticates the user. Also CAS can be integrated using LDAP as the main directory service and phpCAS or JavaCas as the client ticketing system. Pros : no change in any PHP or Java applications, but just integration of CAS for authentication. Centeral user managemet like what Yahoo offers ( Account Setting in Yahoo ). Seprate databased for each application that maybe increases security. Cons : Huge amount of data duplication as each username. password, address and other related fields will be the same in many databases! , High database loads due to multiple UPDATE of several tables in different DBs. 2. Change all the applications to use PgSQL and share the same database. Pros : No data duplication, fast database access ( no high loads like method no.1 ) Cons : Heavy source code editing 3. Change all the applications to use LDAP. Pros : No data duplication, fast database access ( no high loads like method no.1 ) Cons : Heavy source code editing, I'm not sure if LDAP can handle this much insert & update queries as it's mostly a directory service and is not usefull to be used as a dabatase for other informations like profiles and etc. -- Warm Regards, Amir ------=_Part_23347_15434436.1163120415779 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hi
 
First tI should thank Brew and Juan for their kind help.
Second it seems that the php applications doesn't use ADODB as here's a sample DB connection function of one of the PHP sctipts :
 
[code]
 
class DB {

 function DB(){

  $this->values = array();  

 }//DB

    function connect() {
  
        $this->conn_id = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS,1);
        mysql_select_db(SQL_DB,$this->conn_id);
        $version = $this->version();
       
        if($version >= 4.1){
         $encoding = extract_encoding("mysql");
         if($encoding){
          $sql_query = "SET NAMES '".$encoding."'";
          $this->execute($sql_query);
         }//if
        }//if

    }//connect
   
    function disconnect(){
     
     if($this->conn_id)
      @mysql_close($this->conn_id);
     
    }//disconnect
   
    function version(){
     
     $sql_query = "SELECT VERSION()";
     $version   = $this->single($sql_query);
     $version   = ereg_replace("[^0-9|\.]","",$version);
     return $version;
     
    }//version

    function execute($sql_query) {     
  
        if(!$this->conn_id){

            $this->connect();

        }//if
       
        preg_match_all("/\:[A-Za-z|0-9|_]{1,}\:/",$sql_query,$matches);
       
        $patterns = $matches[0];
       
        while(list(,$val) = each($patterns)){
         $key   = substr($val,1,strlen($val)-2);
         $value = stripslashes($this->values[$key]);
         $sql_query = hs_ereg_replace($val,mysql_escape_string($value),$sql_query);
        }//while
          
  reset($this->values);
  
        $this->sql_result = mysql_query($sql_query,$this->conn_id);

        if(!$this->sql_result && !hs_ereg("^LOCK TABLES",$sql_query) && $sql_query!="UNLOCK TABLES"){         
         die($sql_query.":".mysql_error($this->conn_id));
        }//if
       
        return $this->sql_result;

    }//execute

    function insert($sql_query){

     if((!$this->sql_result)||($sql_query != $this->sql_query)){

            $this->execute($sql_query);

        }//if

        $this->clean();
        return mysql_insert_id($this->conn_id);       

    }//insert
   
    function update($sql_query){

     if((!$this->sql_result)||($sql_query != $this->sql_query)){

            $this->execute($sql_query);

        }//if

        $this->clean();
        return mysql_affected_rows($this->conn_id);       

    }//update

    function fetch($sql_query){

        if((!$this->sql_result)||($sql_query != $this->sql_query)){

            $this->execute($sql_query);

        }//if

        $this->sql_query = $sql_query;
        $result = @mysql_fetch_object($this->sql_result);
        if(!$result)
         $this->clean();
         
        return $result;

    }//fetch

    function row($sql_query){

        if((!$this->sql_result)||($sql_query != $this->sql_query)){

            $this->execute($sql_query);

        }//if

        $this->sql_query = $sql_query;
        $result = @mysql_fetch_assoc($this->sql_result);
        if(!$result)
         $this->clean();
         
        return $result;

    }//row

    function result($sql_query){

     $ret_array = array();    

        while($this->res = $this->row($sql_query)){
         
            $ret_array[] = $this->res;

        }//while
        
        @mysql_free_result($this->res);      
        $this->clean();
        return $ret_array;

    }//result

    function single($sql_query){
     
        $this->execute($sql_query);
        $this->clean();
        return @mysql_result($this->sql_result,0);

    }//single
   
    function lock($table){
     
     $sql_query="LOCK TABLES `".$table."` WRITE";
     $this->execute($sql_query);
     
    }//lock
   
    function unlock(){
     
     $sql_query="UNLOCK TABLES";
     $this->execute($sql_query);
     
    }//unlock
   
    function clean(){
     
     $this->sql_query = "";
     
    }//clean

}//DB class

$DB = new DB;
 
[/code]
 
 
Well, this way I think I would need very much modification in source codes to make the PHP scripts talk to a PgSQL server :(
After all I have thought of 3 methods of integration. I would like everyone who have an experience to share it with me and vote for the best methd that comes to your mind :
 
1. change the PHP programs somehow that the registration and password modification ( and maybe profile editing ) won't be accessed. This way I will write a centralized registeration and editing script where users can register and update their profile. When a user is registered or updated all the new information including username, password, name, last_name, address an etc will be updated in all connected databased + LDAP server. This way for example user "sample" with pass "sample" will be stored in almost 10 databases + LDAP server. Then each application will read from its own database ( MySQL, PgSQL or LDAP ) and authenticates the user. Also CAS can be integrated using LDAP as the main directory service and phpCAS or JavaCas as the client ticketing system.
Pros : no change in any PHP or Java applications, but just integration of CAS for authentication. Centeral user managemet like what Yahoo offers ( Account Setting in Yahoo ). Seprate databased for each application that maybe increases security.
Cons : Huge amount of data duplication as each username. password, address and other related fields will be the same in many databases! , High database loads due to multiple UPDATE of several tables in different DBs.
 
2. Change all the applications to use PgSQL and share the same database.
Pros : No data duplication, fast database access ( no high loads like method no.1 )
Cons : Heavy source code editing
 
 
3. Change all the applications to use LDAP.
Pros : No data duplication, fast database access ( no high loads like method no.1 )
Cons : Heavy source code editing, I'm not sure if LDAP can handle this much insert & update queries as it's mostly a directory service and is not usefull to be used as a dabatase for other informations like profiles and etc.
 
 
--

Warm Regards,
Amir
------=_Part_23347_15434436.1163120415779--