I'm trying to write an sql parsing tool for specific purposes. I'm going to run all the sql statements through mysql but any create statement needs to update the table if there are new fields. So it doubles as an alter statement.
The problem I'm having is a simple one but I'm wonder how this could be possible in PHP. Each statement I parse out will be called a Command and extend the SQLCommand super class.
I'm going to determine what command it's using by looping through each word of a command, adding it to a string where I try to match it to an associative array.
PHP Code:
class SQLCommand
{
private $sql;
private $executables;
function getExecutables()
{
return $this->executables;
}
}
class SQLCreate extends SQLCommand
{
}
PHP Code:
CREATE TABLE IF NOT EXISTS `builders` (
`bld_id` int(11) NOT NULL AUTO_INCREMENT,
`bld_name` varchar(30) NOT NULL,
`bld_brandLogo_med` mediumint(9) NOT NULL,
`bld_brandLogo_sm` mediumint(9) NOT NULL,
`bld_reportingName` varchar(75) NOT NULL,
`bld_defaultLeadsEmail` varchar(100) NOT NULL,
`bld_copyLeadsEmail` varchar(100) NOT NULL,
`bld_builderWebsite` varchar(255) NOT NULL,
`bld_LeadsPerMessage` enum('All','1') NOT NULL,
PRIMARY KEY (`bld_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
This would look for "CREATE" then "CREATE TABLE" (where it will make a match). I could create a long if-else or switch to choose what Command to pass back but here is what I'd like to do-
PHP Code:
function parseCommands()
{
$commandRegex = "|([^;]+)|";
preg_match_all($commandRegex, $this->sql, $matches);
foreach($matches[1] as $match)
{
$this->commands[] = self::SQLCommandFactory($match);
}
}
....
public static function SQLCommandFactory($commandSQL)
{
$knownCommands = array(
"CREATE TABLE" => &SQLCreate;
);
print_r($knownCommands);
return NULL;
}
Is this possible? So I can eventually do $CommandCls &= $knownCommands[$commandStr];
$Command = new *$CommandCls($commandStr);
Neither of those will work in PHP. The first is probably supposed to be =& which can be done in PHP, but you cannot dereference with * as that syntax isn't available in PHP (or more accurately, you cannot get the pointer directly in PHP, you can only retrieve the value, or assign the pointer through direct assignments in PHP).
PHP uses object instance pointers, which are *kinda* like pointers. They're not the same though. For most typical work, these are *mostly* synonymous in PHP 5 (php 4 is a different story):
PHP Code:
$obj = $objArray['anObject']; // and $obj = &$objArray['anObject'];
Changes to $obj in either reflect changes to both the $obj and the 'anObject' within the array. Where the second one would differ is on the destruction of the object itself since they share the same object instance pointer, but not the same pointer itself. The first would be an object pointer to an object pointer.
Now this all said, for what reason do you need to issue an alter command? Typically speaking, with the exception of installation process, you shouldn't need to ever issue a CREATE, DROP or ALTER SQL command in a project.
<?php
class A
{
function __toString(){return "A";}
}
$Class = 'A';
$Instance = new $Class();
echo $Instance;
echo "No Error";
?>
Though it's really dumb in my opinion if a string is treated like a simple data type.
Quote:
Originally Posted by Fou-Lu
Now this all said, for what reason do you need to issue an alter command? Typically speaking, with the exception of installation process, you shouldn't need to ever issue a CREATE, DROP or ALTER SQL command in a project.
You haven't worked on anything big enough or spanning multiple systems/servers.
That doesn't look like your original question at all. It will work though, assuming that's what you need to do.
PHP is a string based language. Strings are a primitive datatype as the union includes the char* within the zval.
As for large systems, no I guess not. I'm in a medium scale business with about 40TB of database usage and a little over 1300 servers many of which act together of course. I've only issued one table alteration in the past five years, and that was on a third party vendor software which chose a datatype too small to represent a number of bytes. The development team likely runs a lot of DDS statements within their code at runtime, but not a single one of those would make it into the production systems.
You're confusing classes with objects. This should have said it all.
Quote:
I'm in a medium scale business with about 40TB of database usage and a little over 1300 servers many of which act together of course. I've only issued one table alteration in the past five years . . .
Your business may hold a lot of data and use a lot of servers (bandwidth? performance loading?) but it sounds like your operations don't require the same structure as ours. Imagine.
Possibly more dynamic with a key-value table for each element? I'm only speculating but assuming something like this (if your system even requires to be this dynamic)- You'd probably have a performance boost with actual tables instead of this setup and you wouldn't need to spend so much on servers.
Ours is different however (Imagine). Not only does the codebase need to be shared across each server but tables are added and frequently changed while we make partnerships that require these changes/additions.
Quote:
Typically speaking, with the exception of installation process, you shouldn't need to ever issue a CREATE, DROP or ALTER SQL command in a project.
Typically does not mean always. Try expanding your horizons and consider that other operations may require different techniques to make them function.
Yep, I'm not going to lie. I didn't read through your code at all since I assumed you have a normalization issue.
If you have a normalized database, and you are still regularly issuing DDS', than that's fine. I can't see a reason for it even across platform; all that matters is that its normalized. But to me, constantly altering the structure does reek of a normalization issue.
Before you post, read our: 
