Re: [Phplib-users] RE: sys_procedures

[Frank B. <fb...@sy...>]

Where should Bob's sys_procedure code go within phplib?  

I just had a look at this.  It didn't take me long to find that phplib has
a fundamental problem.  I only supports a single database per installation!
 Excuse me if this has been observed before, but I just realised it; I
never went much beyond local.inc before today.

Here's my suggestion:

1) DB_Sql in each of the db_*.inc files should be named specific to the
database being supported (like DB_Sql_pgsql or DB_Sql_mysql).

2) _prepend.php should be allowed to 'require' multiple database inc's if a
site wishes; and then add:
	class DB_SQL extends DB_Sql_xxx {
		/* include Bob's sys_procedure code here */
	}
Many of the one-liner functions could be moved here too, when they are
identical in all databases supported.

Or maybe I've got it backwards.  Should the common stuff be in the base
class and DB_Sql_PG and DB_Sql_mysql extend that one?  Then DB_Sql (as
above) will empty just for compatibility with existing versions.  This OO
stuff hurts my head.

In the meantime, I've decided that the interesting part of execproc()
should be a separate function, as in:

  function subsql($sql, $argv="") {
    if (is_array($argv)) {
      foreach ($argv as $key=>$val) {
        $sql = ereg_replace("{".strtoupper($key)."}", $val, $sql);
      }
    }
    return $sql;
  }

Frank


At 11:03 PM 11/9/01 -0800, Philip Strnad wrote:
>Bob,
>
>For the include file I was thinking of something like this:
>
><?
>$queries = array("get_2_specials" => "select * from table where x =
{variable}",
>...);
>?>
>
>Then you could access the query in $queries easily by looking it up by its
name and
>you could have your function to replace the variables with real values
passed to
>the function in a hash or whatever.  I think this would be pretty
organized, but
>again it's just a matter of preference.
>
>About the databases and which class this stuff could be added to:  the
only reason
>I said it would be good for the MySQL class is that for other databases that
>support exectuion of server-side code in the database itself (Oracle, SQL
Server,
>etc), it would probably be preferable to use the database's built in language
>instead of doing the sys_procedures thing.  Although the sys_procedure
thing would
>work in these other databases it would probably not add much value, unless of
>course you want to make your code really database independent.  I think
the general
>direction for database abstraction classes is always to make them generic
so they
>work with different databases so that might be the way to go.
>
>Anyway, those are just my thoughts on this.  Since I do use MySQL for my
PHP stuff
>I would probably take advantage of this if it were available.
>
>Philip
>
>
>Bob Bowker wrote:
>
>> Philip --
>>
>> Yes, an include file could contain the SQL, but I think that managing this
>> storage scheme would soon become counter-productive ... keeping track of
>> which file contains which procedure, then committing the extra clock cycles
>> to open and parse every needed text file (vs the clock cycles for MySQL to
>> do the same thing) ... but yes, this would work ...
>>
>> I'm not very knowledgeable when it comes to the other DBs supported by PHP
>> and phpLib, but I'm sure that a similar - or even cross-DB - version could
>> be written.  But I think that, if anything, an example class extension in
>> local.inc may be preferable to adding everything to one DB class ... two
>> advantages:
>>
>> 1. if a user understands the sys_procedures concepts, they'll be used, and
>> if not, they'll be ignored.  To me this is better than more posts that "...
>> the distribution is broken because I can't get it to work" ...
>>
>> 2. users with the ability to apply the concepts to other DB packages will
>> be able to do so - if we commit the code to mysql.inc then others are
left out.
>>
>> Just my 2 cents worth ... but I'm glad this methodology seems to be useful
>> to a few others.
>>
>> This methodology is indeed probably a direct descendent of functions built
>> into other databases ... I haven't credited Cheng-Wei Cheng enough in these
>> posts.  He worked for us about 2 years ago, making major contributions to
>> what is still the biggest site we've done.  He built the framework for much
>> of the database schema, and in fact wrote the original version of our DB
>> class functions which handle the procedures.  And his Computer Sciences
>> degree probably laid the foundation for this ...
>>
>> Bob.
>>
>> At 12:08 AM 11/9/2001 -0800, you wrote:
>> >This is pretty cool.  It looks similar to real stored procedures ala
PL/SQL in
>> >Oracle, except that the procedures are pure SQL queries and they are not
>> >executed
>> >directly in the database (you have to query to get the query into your PHP
>> >script, and then send it back to the database for it to get executed).  It
>> >also
>> >reminds me of the Perl DBI or any other db interface that makes use of
"bind
>> >variables."
>> >
>> >I guess you could also store the queries in an include file right?  But
some
>> >people prefer to have stuff in the database.  I agree that this might be a
>> >useful
>> >addition to the db class, especially for MySQL users.
>> >
>> >Philip
>> >
>> >
>> >Bob Bowker wrote:
>> >
>> > > Brian --
>> > >
>> > > Nope, no reason at all ... except that the two-step seemed a bit more
>> > > logical at the time - my only defence is, "that's the way my mind
works"
>> > > ...!  It also makes subsequent use of the procedure in the same
script easy
>> > > - the procedure is already loaded and the class' variables are already
>> > > initialized.  Something to think about if you call the procedure
within a
>> > > loop ...
>> > >
>> > > Adding this to phpLib might be possible, but as you say, it's not yer
>> > > Daddy's Chevrolet, and there are already enough complaints that "...
the
>> > > distribution is busted cause I can't make it work".  Adding a couple of
>> > > vars and 2 functions to your DB class is easy, and upgrades to
phpLib are
>> > > simpler to install ...
>> > >
>> > > Maybe an "Example_SysProcedures_DB extends DB_Poe" in the local.inc as
>> > > distributed would do the trick, but ...
>> > >
>> > > My version of this code is available for anyone to fix up, improve, or
>> > > include in phpLib ... just give me a shout if you make it better so
I can
>> > > benefit, too!
>> > >
>> > > Bob.
>> > >
>> > > At 01:25 PM 11/8/2001 -0600, you wrote:
>> > > >Very nice.. not to complicate things, but is there any reason why you
>> > > >couldn't/wouldn't combine proc and execproc? Something like this:
>> > > >
>> > > >$parameters[FORMAT] = "CD";
>> > > >$parameters[TRACK]  = "Smells Like Teen Spirit";
>> > > >$parameters[ARTIST] = "Nirvana";
>> > > >
>> > > >$db->execproc ( "get_2_specials", $parameters );
>> > > >
>> > > >Regardless, I think this is a VERY powerful concept. Anybody else
up for
>> > > >adding it to PHPLib officially? Granted, it's a little
high(er)-level, but
>> > > >it wouldn't affect the class in the slightest for those that didn't
choose
>> > > >to use it.
>> > > >
>> > > >BPopp
>> > > >
>> > > >
>> > > >-----Original Message-----
>> > > >From: Bob Bowker [mailto:bo...@iN...]
>> > > >Sent: Thursday, November 08, 2001 12:02 PM
>> > > >To: Php...@li...
>> > > >Cc: jwi...@ma...; bp...@ct...;
>> > > >Kar...@co...
>> > > >Subject: sys_procedures
>> > > >
>> > > >
>> > > >Hi --
>> > > >
>> > > >OK, here goes ...
>> > > >
>> > > >==================================================
>> > > >"sys_procedures" is a table which contains the actual SQL queries ...
>> > > >
>> > > >CREATE TABLE sys_procedures (
>> > > >    id int(11) NOT NULL auto_increment,
>> > > >    name varchar(20) NOT NULL default '',
>> > > >    description varchar(40) default NULL,
>> > > >    sql text,
>> > > >    dt_create datetime NOT NULL default '0000-00-00 00:00:00',
>> > > >    dt_modify timestamp(14) NOT NULL,
>> > > >    argc int(2) unsigned NOT NULL default '0',
>> > > >    PRIMARY KEY  (id),
>> > > >    KEY name (name,dt_create,dt_modify)
>> > > >) TYPE=MyISAM;
>> > > >
>> > > >==================================================
>> > > >Within your DB class, add the following:
>> > > >
>> > > >    /* Store procedure enhancement: configurable*/
>> > > >    var $procedure_table = "sys_procedures";
>> > > >    var $proc_name;
>> > > >    var $sql = array();
>> > > >    var $argc;
>> > > >    var $finalQuery;
>> > > >
>> > > >     function proc($name) {
>> > > >        if ( !is_array($this->sql) ) {
>> > > >           $this->sql[$name] = "";
>> > > >           $this->argc[$name] = 0;
>> > > >        }
>> > > >        if ( strlen($name) > 0) {
>> > > >           ##This should be the only line of embedded Sql in the
>> > application
>> > > >           $qry = "SELECT sql, argc FROM ".$this->procedure_table."
WHERE
>> > > >name = '".$name."' ";
>> > > >           if (strlen($this->sql[$name]) == 0  && $this->query($qry)){
>> > > >              $this->next_record();
>> > > >              $this->sql[$name] = $this->f("sql");
>> > > >              $this->argc[$name] = $this->f("argc");
>> > > >           }
>> > > >           $this->proc_name = $name;
>> > > >           return true;
>> > > >        }
>> > > >        return false;
>> > > >     }
>> > > >
>> > > >     function execproc($argv="") {
>> > > >        $this->finalQuery = $this->sql[$this->proc_name];
>> > > >        if (is_array($argv)) {
>> > > >           reset($argv);
>> > > >           $c = count($argv);
>> > > >           for ($i=0 ; $i<$c ; $i++) {
>> > > >              $value = $argv[key($argv)];
>> > > >              $this->finalQuery =
>> > > >ereg_replace("{".strtoupper(key($argv))."}", $value,
$this->finalQuery);
>> > > >              next($argv);
>> > > >           }
>> > > >        }
>> > > >        $qry = $this->finalQuery;
>> > > >        if (( count($argv) >= $this->argc[$this->proc_name] ) ||
>> > > >($this->argc[$this->proc_name] == 0) ) {
>> > > >           return $this->query($qry);
>> > > >        } else {
>> > > >           return false;
>> > > >        }
>> > > >     }
>> > > >
>> > > >==================================================
>> > > >Let's say you have an SQL query that searches 400,000 records for
>> > "Madonna"
>> > > >and "DVD" ... the data is normalized across a half-dozen tables,
and the
>> > > >query uses JOIN and every MySQL trick I know to speed things up.
I'm also
>> > > >going to want to search (elsewhere) for "CD", and "Collector", and
>> > > >"VHS".  I used to debug the query in DVDs, then cut-and-paste it
into the
>> > > >CD script, change the variables, and debug it all over again ...
ditto for
>> > > >Collector, VHS, and so on.
>> > > >
>> > > >Or, in the CD PHP code, you can do the following:
>> > > >           $word = "Madonna"; $fmt = "CD";
>> > > >           $query = $db->proc("get_2_specials");
>> > > >           $argv = array(FORMAT=>"$fmt",TYPE=>"$word");
>> > > >           $db->execproc($argv);
>> > > >
>> > > >Or, in the DVD "department", the following:
>> > > >           $word = "Madonna"; $fmt = "DVD";
>> > > >           $query = $db->proc("get_2_specials");
>> > > >           $argv = array(FORMAT=>"$fmt",TYPE=>"$word");
>> > > >           $db->execproc($argv);
>> > > >
>> > > >For clarity, I've deleted a dozen other members of the array which get
>> > > >passed to the class function ... but I'm sure you get the idea.  The
>> > > >"finalQuery" variable, by the way, is *very* useful for debugging!
>> > > >
>> > > >==================================================
>> > > >In practice ...
>> > > >
>> > > >1. I write the query and test it using hard values and "explain" in
>> > > >PhpMyAdmin ...
>> > > >
>> > > >2. when the tables are optimized and the query is working the way I
>> > want, I
>> > > >copy the query into my PHP script and substitute variables for the
hard
>> > > >values ...
>> > > >
>> > > >3. when that's working the way I want, I re-write the query
substituting
>> > > >placeholders for the variables and store that in the sys_procedures
table
>> > > >...
>> > > >
>> > > >4. finally, I substitute the 3 lines (or so) of PHP code for the query
>> > > >itself in my script, and I'm done.
>> > > >
>> > > >The tables are now optimized for the query, and the debugged query is
>> > > >available to all other scripts (I can cut-and-paste just the 3
lines of
>> > > >code) ... and I may get out of here on time tonight!
>> > > >
>> > > >==================================================
>> > > >
>> > > >Please let me know if I've left anything out ... and give a shout
if I can
>> > > >help ...
>> > > >
>> > > >Bob.