Thread: PgSQL and integration between 2 PHP programs and a Java program

Amir......

It seems to me you have greater issues than simply combining the three username and password tables!!!

What if there are existing users on the three different systems with the same user name?  Are they garanteed to be the same person?  Maybe so, if they have the same password, but what will you do if they have different passwords?

But beyond that, from what you say, you have to convert three different systems from whatever database programs you are using now to postgreSQL.  The current databases are doing more than just keeping track of users and passwords, right?  Possibly you plan to keep the existing database program and just use postgreSQL to log in?

And do you hope your users will log in once and go back and forth between your aplications without logging in again, or not?

At any rate, unless the applications are very simple, there is a lot more work to be done than simply joining the three password tables together!!!!!

brew

--

Strange Brew (brew@theMode.com)

Check out my Stock Option website http://www.callpix.com
and my Musicians Free Classified http://www.TheMode.com

Hi, folks!

On 11/9/06, Servers24 Network <servers24@gmail.com> wrote:
> This was the description of my project. If anyone have a better solution
> please let me know. I also have researched about using LDAP as the main DB
> server but seems it can only be used as the CAS and not the main DB for
> applications as it's main use is as a directory and can not be used in a
> high volume of changing data environment. Also very much modification should
> be done in each application to be able to talk to LDAP server!!

PHP can take advantage of php4-ldap/php5-ldap to communicate with LDAP
server with no much trouble... Of course, that depends on your actual
class design, but with good OO design it should be quite simple to
replace/extend existing classes/methods for LDAP.  Same thing could be
said for Java.

> Also I have another question : do I need to edit MySQL queries very much in
> my PHP applications to interact with a PgSQL or not? Because most of the
> queries are the same and maybe very few should be changed ( as far as I
> know! ).

Again, if you're using some ORM or database abstraction layer
(PHP-ADODB, for example of the latter), it could be as simple as
changing your dsn configuration with maybe a very few noticeable
differences in sql-dialects.  If you're using just plain old php
database apis (e.g. mysql_connect, mysql_query), both mysql and pgsql
apis are very similar in php (there's mysql_connect for pg_connect,
mysql_query for pg_query and so on)...  Just watch out for things like
parameter ordering and so on (documentation is your friend), but of
course, database abstraction is highly recommended.

In any case, LDAP could be a good option for storing authentication
and authorization information in a centralized repository (most modern
frameworks, languages, technologies can integrate pretty well with
LDAP), and keep your application-specific data in whatever repository
seems appropiate.

Greetings!

 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

Hi, folks!

On 11/9/06, Brew <brew@themode.com> wrote:
> Amir.....
>
>
> >
> > 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 :
>
>
> I vote for number 1.
>
> Unless you have some other reason to do a major rewrite, it seems the
> easiest to share the username and passwords across applications.  It's nice
> to make things perfect, but sometimes you have to look at how much effort
> versus the return.
>
> > 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.
>
> Yes, but disk space is cheaper than the labor, and time, of all that
> rewriting.  And further, except for the added CAS table, it's no more than
> the space you are already using.  No wait, that's not so, each table will
> have ALL the users now, not just a subset.  Oh, well.
>
> As far as updating, that only happens when a user makes a change, or maybe
> once when he logs in.  And if it's too slow you can find a way to do it in
> the background, let the user set it in the CAS and be done, then find
> another way to propagate the changes to the other database tables.  A
> trigger (maybe, not sure) or some recurring job that finds data that hasn't
> been propagated and does it, then sets a flag column for that user that it's
> been synced.
>
> I'm sure others on the list can think of some more (maybe better) ways.
>
> So I'm voting for number one.  On the other hand, I can think of times that
> I've cobbled something together and then realized I should have taken the
> time to do it right in the first place.  It's a hard call to make up
> front!!!
>
>
> brew
>
> --
>
> Strange Brew (brew@theMode.com)
>
> Check out my Stock Option website http://www.callpix.com
> and my Musicians Free Classified http://www.TheMode.com

Indeed, it's a hard call, but if you go for option 1 (keeping account
information in all your existing databases AND LDAP repository),
perhaps you could take some time and try something like PostgreSQL
DBI-Link (http://pgfoundry.org/projects/dbi-link/), in which
theoretically you could access any data source available for perl DBI
(and that includes of course pgsql, mysql, ldap, and many others) as
postgresql tables, so the overhead and complications of updating
several databases could be diminished.

Also, I'd dare to suggest a 4th option for consideration: centralize
account information only (perhaps in LDAP repository), keep
application-specific information in application-specific databases. Of
course, that would mean some code rewriting (but not for all
application queries, just for account information ones, and you could
take advantage of your CAS apis). I've seen this approach work in
several scenarios, but of course, there's no perfect solution for
every situation, and YMMV.

Thanks a lot to all you guys keeping up the good work in the
PostgreSQL community, and any other suggestions/experiences regarding
this case will surely be interesting.

Greetings!

Hi, Amir!

On 11/10/06, Servers24 Network <servers24@gmail.com> wrote:
 I don't really
> know how this DBI-Link works, but the way Juan said, maybe I can have
> Liferay write user information to a PgSQL DB and have DBI-Link propagates
> the data (username, password, first_name, last_name, city, state, country,
> zipcode and ... ) inside Liferay's DB to all appropriate DBs.
> Also I will disable ANY modification to the above mentioned fields in other
> applications so that a user need to change for e.g his name in Liferay, and
> then it will take effect in other applications as well.
> So in this method, I will use a PgSQL server with 2 databases ( one for
> DBI-Link and one for Liferay ) and a MySQL server with maybe 10 databases
> for other applications. In this case, CAS will read authentication
> information from Liferay DB in PgSQL.
> Any comments or ideas ?!

For how DBI-Link works, maybe pgfoundry docs (like
http://pgfoundry.org/docman/view.php/1000045/48/IMPLEMENTATION.txt)
can give a good big picture. Reading the mentioned document, it seems
like there could be better performance when reading through DBI-Link
from other datasources than writing to several other datasources from
PostgreSQL "DBI-linked" tables (just guessing, there can be some other
factors involved, and, as always, experiences and benchmarks are
welcome). If this is true, maybe it would be better to have the other
10 DBs in PostgreSQL reading from the centralized datasource, but of
course that might not be practical in your case, if it means a lot of
code rewriting.  Anyway, I hope this helps and any feedback will be
followed with interest! ;)

Greetings!