Location: PHPKode > projects > NOLA > utilities/makesql.php
<?
//this file will create database schemas for other dbs, from the mysql database.sql
//CAUTION - the last line of the database.sql file must be the end of an sqlstatement, or it will infinite loop
	$fp1=fopen('../documentation/sqlscripts/MySQL/database.sql', 'r');
	$fp2=fopen('../documentation/sqlscripts/MSSQL/database.sql', 'w');
	$fp3=fopen('../documentation/sqlscripts/Oracle/database.sql', 'w');
	while (!feof($fp1)) {
		while (!strpos($statement,';')) { //find the entire statement
			$line.=fgets($fp1,4096);
			if (strpos($line,'#')===0) $line=substr($line,0,strpos($line,'#')); //remove #style comments
			$statement.=$line;
			unset($line);
            if ($statement[strlen($statement)-1]!="\n") $statement.="\n"; //preserve dos/unix linebreaks
		};
		$statement=ltrim($statement); //make sure we strip leading spaces
		if (strpos($statement,'create database')===0) $skip=1; //remove create database statements
		if (strpos($statement,'use')===0) $skip=1; //remove use database statements
		if (!$skip) { //if we should translate this statement to the other db's
			$msline=ms($statement);
			$oraline=ora($statement);
			fputs($fp2,$msline."\n\n");
			fputs($fp3,$oraline."\n\n");
		};
		unset($statement); //get ready for next statement
		unset($skip);
		unset($msline);
		unset($oraline);
		echo '.'; //just to show the user that we are doing something
	};
	fclose($fp1); //close file handles
	fclose($fp2);
	fclose($fp3);
	echo '<br>Done';

	function ms($statement) { //ms sql-ize statement
		$statement=str_replace('auto_increment','identity',$statement); //change auto_increment keyword
		$statement=str_replace('double','real',$statement); //change double types to real
		$statement=str_replace('blob','text',$statement); //change blob types to text
		$statement=removeforeignkeys($statement);
		return $statement;
	};
	
	function ora($statement) { //oracle-ize statement
		$statement=str_replace('text','blob',$statement); //change text types to blob
		$statement=str_replace('double','double precision',$statement); //change double syntax
		$statement=str_replace('datetime','date',$statement); //change datetime syntax
		$statement=removeforeignkeys($statement);
		$statement=removedefaults($statement);
		$statement=createtriggers($statement); //needs to be done before auto_increments are removed :)
		$statement=str_replace(' auto_increment','',$statement); //change auto_increment keyword
		return $statement;
	};

	function removeforeignkeys($statement) {
		while (strpos($statement,', key(')) { //remove foreign keys like , key(foo)
			$begin=strpos($statement,', key(');
			$end=strpos($statement,')',$begin)+1;
			$statement=substr($statement,0,$begin).substr($statement,$end); 
		};
		while (strpos($statement,',key(')) { //remove foreign keys like ,key(foo)
			$begin=strpos($statement,',key(');
			$end=strpos($statement,')',$begin)+1;
			$statement=substr($statement,0,$begin).substr($statement,$end); 
		};
		while (strpos($statement,', key (')) { //remove foreign keys like , key (foo)
			$begin=strpos($statement,', key (');
			$end=strpos($statement,')',$begin)+1;
			$statement=substr($statement,0,$begin).substr($statement,$end); 
		};
		while (strpos($statement,',key (')) { //remove foreign keys like ,key (foo)
			$begin=strpos($statement,',key (');
			$end=strpos($statement,')',$begin)+1;
			$statement=substr($statement,0,$begin).substr($statement,$end); 
		};
		return $statement;
	};
 

 
	function removedefaults($statement) {
     	while (strpos($statement,' default ')) { //remove all keys like , key(foo)
			$begin=strpos($statement,' default');
			$end=strpos($statement,',',$begin);
			$statement=substr($statement,0,$begin).substr($statement,$end);
//echo substr($statement,0,$begin).substr($statement,$end);
		};
		return $statement;
	};

	function createtriggers($statement) {
		if (strpos($statement,'auto_increment')) { 
			$tablename=gettablename($statement);
			$statement.="\n".'create sequence '.$tablename.'seq increment by 1 start with 1;'."\n";
			$statement.='create trigger '.$tablename.'trig before insert on '.$tablename."\n";
			$statement.='for each row'."\n";
			$statement.='when (new.id is null)'."\n";
			$statement.="\t".'begin select '.$tablename.'seq.nextval into :new.id from dual;'."\n";
			$statement.='end'."\n";
		};
		return $statement;
	};

	function gettablename($statement) {
        $begin=strpos(strtolower($statement),'create table ')+13;
        $end=strpos($statement,'(',$begin);
		$tablename=substr($statement,$begin,$end-$begin);
        $tablename=rtrim($tablename); //make sure we strip trailing spaces
		return $tablename;
	};
?>
Return current item: NOLA