Re: [Phplib-users] RE: sys_procedures
Brought to you by:
nhruby,
richardarcher
Menu
▾
▴
Re: [Phplib-users] RE: sys_procedures
|
From: Philip S. <ph...@st...> - 2001-11-09 08:09:25
|
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.
>
> _______________________________________________
> Phplib-users mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phplib-users
|
