Re: [Phplib-users] RE: sys_procedures
Brought to you by:
nhruby,
richardarcher
Menu
▾
▴
Re: [Phplib-users] RE: sys_procedures
|
From: Bob B. <bo...@iN...> - 2001-11-09 16:15:59
|
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.
> >
> > _______________________________________________
> > Phplib-users mailing list
> > Php...@li...
> > https://lists.sourceforge.net/lists/listinfo/phplib-users
>
>
>_______________________________________________
>Phplib-users mailing list
>Php...@li...
>https://lists.sourceforge.net/lists/listinfo/phplib-users
|
