[Phplib-users] RE: sys_procedures
Brought to you by:
nhruby,
richardarcher
Menu
▾
▴
[Phplib-users] RE: sys_procedures
|
From: Bob B. <bo...@iN...> - 2001-11-08 19:53:07
|
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.
|
