DB_pgsql.php - adding native lookup for sequences? Re: RES: RES: [PEAR-DEV] DataObjects insert() for pgsql

Tomas, I've been pondering if this should really go into the postgres driver for DB, either as an additional argument for nextId: eg. function nextId($seq_name, $ondemand = true,$lookup = false) or as an extra method: function nextIdNative($table) { .... Any thoughts.. - it seems a bit more generic/usefull for DB.. Regards Alan Rezende Rodrigo wrote:

The dot is a separator between schema name and table name : Schema_name.Table_name. Default is public schema for tables. Schemas are just like prefixes that groups tables in a common topic. When you have huge systems, its necessary to logicaly separate tables in such way that you don't need to create new databases. And you can make relationships between the tables in diferent schemas. Schemas are ANSI standard. The code bellow is not correct, I rather : $dummy = explode('.',$this->__table); $schema_name = 'public'; if(isset($dummy[1])) { $schema_name = $dummy[0]; $table_name = $dummy[1]; } else { $table_name = $dummy[0]; } Thanks Rodrigo toGO Ltda. http://www.togoworks.com/ -----Mensagem original----- De: Alan Knowles [mailto:alan@akbkhome.com] Enviada em: quinta-feira, 9 de outubro de 2003 01:51 Para: Rezende Rodrigo Cc: pear-dev@lists.php.net; Matsushita Wagner Assunto: Re: RES: [PEAR-DEV] DataObjects insert() for pgsql Thanks - can you explain this.. - normally tables dont have a '.' in them.. ????

	        $dummy = explode('.',$this->__table);
	        $schema_name = $dummy[0];
	        $table_name  = $dummy[1];

Regards Alan Rezende Rodrigo wrote:

The sql bellow returns the sequence name of a collumn! But the sequence name must be parsed! If you want the next value, you must use $__DB->nextId($sequence_name).

 	        SELECT
 	                adef.adsrc  as sequence
 	        FROM
 	                pg_catalog.pg_attribute a LEFT JOIN	

pg_catalog.pg_attrdef adef

 	                ON a.attrelid=adef.adrelid
 	                AND a.attnum=adef.adnum
 	        WHERE
 	                        a.atthasdef = 't' AND
 	                        a.attname = '%s' AND
 	                a.attrelid = (SELECT oid FROM

pg_catalog.pg_class

				    WHERE relname='%s'
         	                AND relnamespace = (SELECT oid FROM

pg_catalog.pg_namespace WHERE

                 	        nspname = '%s'))
 	                AND a.attnum > 0 AND NOT a.attisdropped

-----Mensagem original----- De: Alan Knowles [mailto:alan@akbkhome.com] Enviada em: terça-feira, 7 de outubro de 2003 05:15 Para: Rezende Rodrigo Cc: pear-dev@lists.php.net; Matsushita Wagner Assunto: Re: [PEAR-DEV] DataObjects insert() for pgsql I presume this uses a table defined like. create table xxx ( id int default next_val(some_seqence),......); could you try attaching as a .txt file, so it doesnt get messed up.. and just try and explain how exploding a tablename, would give you a schema/tablename.. ?

      {
          if (!($seq = @$options['sequence_'. $this->__table])) {

	        $dummy = explode('.',$this->__table);
	        $schema_name = $dummy[0];
	        $table_name  = $dummy[1];

	        $sql_seq = "
	        SELECT
	                a.attname as attname, adef.adsrc  as sequence
	        FROM
	                pg_catalog.pg_attribute a LEFT JOIN

pg_catalog.pg_attrdef adef

	                ON a.attrelid=adef.adrelid
	                AND a.attnum=adef.adnum
	        WHERE
	                        a.atthasdef = 't' AND
	                        a.attname = '$key' AND
	                a.attrelid = (SELECT oid FROM pg_catalog.pg_class

WHERE relname='$table_name'

      	                AND relnamespace = (SELECT oid FROM

pg_catalog.pg_namespace WHERE

              	        nspname = '$schema_name'))
	                AND a.attnum > 0 AND NOT a.attisdropped
	        ;
      	";
	        $r = $__DB->getRow($sql_seq,DB_FETCHMODE_ASSOC);
	        $attrname_seq = $r['attname'];	
	        $sequence_name = explode('\'',$r['sequence']);

$sequence_name = (isset($sequence_name[1]))?$sequence_name[1]:'';

              $seq = $sequence_name;
          }
          $this->$key = $__DB->nextId($seq);
      }

      foreach($items as $k => $v) {
          if (!isset($this->$k) || $this->$k == '' || $this->$k ===

'null') {

              continue;
          }

          if ($leftq) {
              $leftq  .= ', ';
              $rightq .= ', ';
          }
          $leftq .= "$k ";

          if (strtolower($this->$k) === 'null') {
              $rightq .= " NULL ";
              continue;
          }
          if ($v & DB_DATAOBJECT_STR) {
              $rightq .= $__DB->quote($this->$k) . " ";
              continue;
          }

          if (is_numeric($this->$k)) {
              $rightq .=" {$this->$k} ";
              continue;
          }
          // at present we only cast to integers
          // - V2 may store additional data about float/int
          $rightq .= ' ' . intval($this->$k) . ' ';

      }
      if ($leftq) {
          $r = $this->_query("INSERT INTO {$this->__table} ($leftq)

VALUES

($rightq) ");

          if (PEAR::isError($r)) {
              DB_DataObject::raiseError($r);
              return false;
          }
          if ($r < 1) {
              DB_DataObject::raiseError('No Data Affected By

insert',DB_DATAOBJECT_ERROR_NOAFFECTEDROWS);

              return false;
          }

          if ($key &&
              ($items[$key] & DB_DATAOBJECT_INT) &&
              ($dbtype == 'mysql') &&
              (@$options['ignore_sequence_keys'] != 'ALL') &&
                  (
                      !@$options['ignore_sequence_keys'] ||
                      (is_array(@$options['ignore_sequence_keys']) &&

in_array($this->__table,@$options['ignore_sequence_keys']))

                  )
              )
          {
              $this->$key =

mysql_insert_id($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->co

nnection);

          }

          $this->_clear_cache();
          if ($key) {
              return $this->$key;
          }
          return true;
      }
      DB_DataObject::raiseError("insert: No Data specifed for query",

DB_DATAOBJECT_ERROR_NODATA);

      return false;

}

-- Can you help out? Need Consulting Services or Know of a Job? http://www.akbkhome.com