✔ Welche SQL-Funktionen nutzt Ihr so?

[Dennis Wronka]

Ich find's ja auch gut, dass Du solche Punkte erwaehnst.
Ueber Kleinigkeiten wie ein Gaestebuch oder sowas bin ich ja mittlerweile auch hinaus, nur arbeite ich in Sachen SQL doch noch eher mit Standardfunktionen.
Wie gesagt, ich muss mich mal ausgiebig mit SQL beschaeftigen um zu sehen was es da noch gibt. Aber das schoene bei so einer Klasse ist auch, dass man immer noch erweitern kann.
Ich hab, um die Klasse zu testen, vorhin mal angefangen meine Website darauf umzubauen, ganz schoen Arbeit bei ca. 40 Scripts. Das Portal auf der Arbeit wird aber beim alten Code (das Ding basiert auf einer aelteren Website, ist aber mittlerweile was den Code angeht auf ca. die doppelte Groesse gewachsen) bleiben.

Was ich auf jeden Fall einbauen will, um den Umstieg von einem DBMS auf ein anderes zu erleichtern ist automatische Konvertierung der `` (MySQL) zu "" (PostgreSQL) und [] (MSSQL), und natuerlich auch in jede andere Richtung. Und dass natuerlich ohne irgendwelche Strings zu beschaedigen. `` erzeugt ja bei PostgreSQL und MSSQL einen Fehler, und alle Queries dann entsprechend umzuschreiben macht die Klasse dann im Grunde ueberfluessig.
Ich wollte auf jeden Fall nicht zu viele spezilisierte Funktionen einbauen die auch ueber Queries erreicht werden koennen. Und auch keine Funktionen die in der PHP-Doku bereits als deprecated gekennzeichnet sind.

Gibt es bei MSSQL das mssql_error nicht

Auch bei PostgreSQL gibt es das nicht, zumindest nicht direkt mit dem Namen. Dort habe ich auf pg_last_error() und mssql_get_last_message() zurueckgegriffen.

 

[Sir Robin]

Hi,

also ich kann dir nur empfehlen dir mal PostgreSQL näher anzusehen, falls du das noch nicht getan hast. Wenn du lange mit MySQL gearbeitet hast, dann wird dir PostgreSQL einiges zeigen und dir auch den Blick für neues Öffnen. Ging mir damals genauso, führt dann auch zu höherem Verständnis von DBMS an sich.

Ich habe mir damals selbst ein DB-Package gebastelt, so rein zum Lernen und schneller Anwendung, es bestand aus 3 Unterklassen, einmal halt DB_Connection zum Verbindungsaufbau etc. (halt die normalen Datenbank-Sachen), dann beim Result gab es eine ResultSet Klasse für's Arbeiten mit dem Ergebnis (num_rows war da zum Beispiel als Property integriert etc.) und einer Query-Klasse die auch Prepared Statements abwickelte. Aber naja, mittlerweile verwende ich PDO an vielen Stellen und für PHP gibt es da auch so nette Lösungen wie das Powerpaket aus Propel und Creole (näheres dazu findest du im mittlerweile kostenlos erhältichen Buch "Professionelle Softwareentwicklung in PHP5), was aber für viele wahrscheinlich der absolute Overkill ist.

Kurzum: es lohnt sich SQL näher kennen zu lernen, es liegt eine Menge Perfomance und Mächtigkeit drin und ist nicht selten der Flaschenhals in Anwendungen. Zu deiner Klasse: Ich bin gerne Betatester, wenn du mir was gibst (und ich die Zeit nebenher habe), ich denke auch für diese "kleinen, feinen" Lösungen gibt es Anwendungsgebiete, man muss ja nicht immer mit den Kanonen die Spatzen zerfleddern

 

[Dennis Wronka]

Hi,

also ich kann dir nur empfehlen dir mal PostgreSQL näher anzusehen, falls du das noch nicht getan hast. Wenn du lange mit MySQL gearbeitet hast, dann wird dir PostgreSQL einiges zeigen und dir auch den Blick für neues Öffnen. Ging mir damals genauso, führt dann auch zu höherem Verständnis von DBMS an sich.

Genau das hab ich vor. Dafuer hab ich mir das ja jetzt auch mal draufgepackt, und eben nicht nur MySQL. Hatte auch schon einiges gelesen, dass PostgreSQL einiges mehr zu bieten hat als MySQL. Nur da man bei Hostern doch halt in der Regel MySQL antrifft hatte ich bisher darauf beschraenkt da ja die Funktion meiner Website doch auch von den Begebenheiten beim Hoster abhaengt. Auf der Arbeit kann ich im Grunde machen was ich will. Wenn ich lustig bin dann kann ich auch entscheiden, dass das Portal nicht mehr ueber MySQL laeuft sondern ueber PostgreSQL. Und wenn ich ganz ganz lustig bin kann ich das auch ueber SQLite machen, aber das steht im Grunde vollkommen ausser Frage.

Was ich mit der Klasse halt auch erreichen will ist dass die Funktionen die man so braucht auf einheitliche Weise genutzt werden koennen.
Allein wenn ich mir ansehen, dass die Query-Funktionen von MySQL und PostgreSQL die Datenbankresource einmal als zweiten und einmal als ersten Parameter uebergeben bekommen. Oder, dass das MSSQL-Pendant zu mysql_affected_rows() mssql_rows_affected() heisst. Solche kleinen Unstimmigkeiten werden dann halt durch die Klasse ausgebuegelt.

Kurzum: es lohnt sich SQL näher kennen zu lernen, es liegt eine Menge Perfomance und Mächtigkeit drin und ist nicht selten der Flaschenhals in Anwendungen. Zu deiner Klasse: Ich bin gerne Betatester, wenn du mir was gibst (und ich die Zeit nebenher habe), ich denke auch für diese "kleinen, feinen" Lösungen gibt es Anwendungsgebiete, man muss ja nicht immer mit den Kanonen die Spatzen zerfleddern

Das Wochenende werd ich wohl mal was Zeit haben ordentlich die MySQL- und PostgreSQL-Dokus zu inspizieren und auch mal was mehr mit PostgreSQL zu spielen.
Mal schauen was dabei am Ende rauskommt.

Auf jeden Fall mal vielen Dank fuer die Vorschlaege und natuerlich auch fuer das Angebot die Klasse mal zu testen. Ich werd mal gucken was sich da erstmal noch dran schrauben laesst und dann wohl auf Dein Angebot zurueckkommen.

An dieser Stelle werd ich den Thread einfach mal als erledigt markieren, bin aber natuerlich weiterhin fuer Vorschlaege und Ideen offen.

 

[Dennis Wronka]

Was ich auf jeden Fall einbauen will, um den Umstieg von einem DBMS auf ein anderes zu erleichtern ist automatische Konvertierung der `` (MySQL) zu "" (PostgreSQL) und [] (MSSQL), und natuerlich auch in jede andere Richtung. Und dass natuerlich ohne irgendwelche Strings zu beschaedigen.

So, diese Funktion hab ich mir heute mal aus den Fingern gezogen. Scheint soweit ganz gut zu funktionieren, muss ich halt noch was testen, aber duerfte (zumindest fuer den Standard-SQL-User) in Ordnung sein. Werde heute Abend mit MySQL und PostgreSQL testen und morgen dann mit MySQL und MSSQL.
Datentypen werden dabei nicht umgewandelt, sondern lediglich, wie oben beschrieben, die einzelnen Begrenzer fuer die Feldnamen ersetzt. Damit das ganze so flexibel wie moeglich ist hab ich mir dazu einiges an Code aus der Nase gezogen und die Funktion auf fast 200 Zeilen aufgeblasen. Werde mal schauen ob man da was kuerzen kann, sicher bin ich da aber nicht.
Naja, wollte halt nur mal kurz ein Update reinschaufeln. Vielleicht kann ich heut Abend noch etwas frohe Kunde verbreiten wenn ich dann mit normalen MySQL-Queries aus PostgreSQL Daten holen kann ohne halt selbst an den Queries zu fummeln.

Nachtrag: Hab grad mal ganz fix getestet und konnte mit einer MySQL-Style-Query vom MSSQL-Server abholen. Das ist doch schonmal was.
So kann ich dann, wenn ich meine Seite komplett auf die Klasse umgestrickt hab, auch einfach mal mit einer kurzen Aenderung im Config-Script meine Seite mit PostgreSQL arbeiten lassen und damit dann mal einen relativ umfangreichen Test laufen lassen.

Nachtrag 2: So, ich hab meine Website nun komplett auf Nutzung der Klasse umgestellt und es funktioniert soweit alles. Ich musste eine Query wegen dem LIMIT umschreiben (hatte dort mit LIMIT Offset,Limit gearbeitet, PostgreSQL unterstuetzt aber nur LIMIT Limit OFFSET Offset, welches auch von MySQL unterstuetzt wird) und den Code zum optimieren der Tabellen umschreiben sodass dieser nur bei MySQL ausgefuehrt wird (Gibt es bei PostgreSQL nichts zum optimieren? Ist das dort nicht noetig?). Aber wie gesagt, soweit laeuft die Seite wunderbar, ich kann jetzt frei zwischen MySQL und PostgreSQL umschalten und die Queries werden bei Bedarf (also wenn ich PostgreSQL nutze) automatisch konvertiert (also die Backticks werden zu Double Quotes).

 

[Dennis Wronka]

Ich musste eine Query wegen dem LIMIT umschreiben (hatte dort mit LIMIT Offset,Limit gearbeitet, PostgreSQL unterstuetzt aber nur LIMIT Limit OFFSET Offset, welches auch von MySQL unterstuetzt wird) ...

Dazu moechte ich dann auch gleich mal ein kurzes Update raushauen: Dies wird nun auch automatisch erledigt, ich komme also bislang gut voran. Hatte mich letztes Wochenende mal etwas mehr mit SQL beschaeftigt, vor allem mit JOIN, da ich damit bislang eigentlich garnichts gemacht hab und bin jetzt dabei Queries meiner Website zusammenzufassen.
Auch die zusammengefassten Queries funktionieren weiterhin sowohl bei MySQL als auch PostgreSQL. Mit MSSQL kann ich ja nicht gross testen da ich da nur auf der Arbeit rankomme und dort auch nur lesen kann. Muss mal gucken ob ich das nicht einrichten kann, dass ich auch dort mal die Datenbank fuer meine Website klarmache damit ich meine Website dann auch mal mit MSSQL testen kann.
Das bringt mich aber gleich zum naechsten Punkt, und zwar geht es dabei wieder um LIMIT. Diese Funktion gibt es ja in MSSQL nicht, dort gibt es scheinbar nur TOP, und das, wie es aussieht, auch noch ohne Offset.
Da werde ich dann also eine Art Emulation einbauen muessen. Der umgekehrte Weg duerfte da ja recht einfach aussehen.
Aus

SELECT TOP 5 * FROM [my_table]

wird dann halt in MySQL

SELECT * FROM `my_table` LIMIT 5

Hier ist auch der umgekehrte Weg nicht so schwierig, jedoch duerfte es lustig werden das Offset einzubauen.
Falls da jemand eine Idee zu hat, ich bin immer dankbar fuer Vorschlaege.

Nachtrag: So, die Konvertierung von TOP zu LIMIT und umgekehrt funktioniert nun auch. Nun muss ich mir halt noch was wegen dem Offset einfallen lassen.

Nachtrag 2: Damit Ihr Euch mal ein Bild davon machen koennt wie die ganze Konvertierung zur Zeit ablaeuft dachte ich poste ich mal meine "kleine" Funktion convertsql(). Dies ist die aus der Klasse herausgetrennte "Standalone-Version", kann also, wenn Ihr wollt, direkt eingesetzt werden. Gueltige Werte fuer den 2. Parameter sind mysql, pgsql und mssql. Alle anderen Werte fuehren lediglich dazu, dass die uebergebene Query so rauskommt wie sie reingegangen ist.
Wie gesagt, das Offset funktioniert im Zusammenhang mit MSSQL noch nicht, aber ansonsten hatte ich bisher keine Probleme. Falls jemand was findet waere ich dankbar davon zu erfahren.

function convertsql($query,$type)
{
	preg_match_all('/`([^`]|``)+`/',$query,$btmatches);
	preg_match_all("/'([^']|\\\')*'/",$query,$sqmatches);
	preg_match_all('/"([^"]|\\\")+"/',$query,$dqmatches);
	preg_match_all('/\[[^]]+\]/',$query,$sbmatches);
	$matches=array('bt'=>array(),'sq'=>array(),'dq'=>array(),'sb'=>array());
	for ($x=0;$x<count($btmatches[0]);$x++)
		{
			$match=true;
			for ($y=0;$y<count($sqmatches[0]);$y++)
				{
					if (strpos($sqmatches[0][$y],$btmatches[0][$x])!==false)
						{
							$match=false;
						}
				}
			for ($y=0;$y<count($dqmatches[0]);$y++)
				{
					if (strpos($dqmatches[0][$y],$btmatches[0][$x])!==false)
						{
							$match=false;
						}
				}
			for ($y=0;$y<count($sbmatches[0]);$y++)
				{
					if (strpos($sbmatches[0][$y],$btmatches[0][$x])!==false)
						{
							$match=false;
						}
				}
			if ($match==true)
				{
					$matches['bt'][]=$btmatches[0][$x];
				}
		}
	for ($x=0;$x<count($sqmatches[0]);$x++)
		{
			$match=true;
			for ($y=0;$y<count($btmatches[0]);$y++)
				{
					if (strpos($btmatches[0][$y],$sqmatches[0][$x])!==false)
						{
							$match=false;
						}
				}
			for ($y=0;$y<count($dqmatches[0]);$y++)
				{
					if (strpos($dqmatches[0][$y],$sqmatches[0][$x])!==false)
						{
							$match=false;
						}
				}
			for ($y=0;$y<count($sbmatches[0]);$y++)
				{
					if (strpos($sbmatches[0][$y],$sqmatches[0][$x])!==false)
						{
							$match=false;
						}
				}
			if ($match==true)
				{
					$matches['sq'][]=$sqmatches[0][$x];
				}
		}
	for ($x=0;$x<count($dqmatches[0]);$x++)
		{
			$match=true;
			for ($y=0;$y<count($btmatches[0]);$y++)
				{
					if (strpos($btmatches[0][$y],$dqmatches[0][$x])!==false)
						{
							$match=false;
						}
				}
			for ($y=0;$y<count($sqmatches[0]);$y++)
				{
					if (strpos($sqmatches[0][$y],$dqmatches[0][$x])!==false)
						{
							$match=false;
						}
				}
			for ($y=0;$y<count($sbmatches[0]);$y++)
				{
					if (strpos($sbmatches[0][$y],$dqmatches[0][$x])!==false)
						{
							$match=false;
						}
				}
			if ($match==true)
				{
					$matches['dq'][]=$dqmatches[0][$x];
				}
		}
	for ($x=0;$x<count($sbmatches[0]);$x++)
		{
			$match=true;
			for ($y=0;$y<count($btmatches[0]);$y++)
				{
					if (strpos($btmatches[0][$y],$sbmatches[0][$x])!==false)
						{
							$match=false;
						}
				}
			for ($y=0;$y<count($sqmatches[0]);$y++)
				{
					if (strpos($sqmatches[0][$y],$sbmatches[0][$x])!==false)
						{
							$match=false;
						}
				}
			for ($y=0;$y<count($dqmatches[0]);$y++)
				{
					if (strpos($dqmatches[0][$y],$sbmatches[0][$x])!==false)
						{
							$match=false;
						}
				}
			if ($match==true)
				{
					$matches['sb'][]=$sbmatches[0][$x];
				}
		}
	if ($type=='mysql')
		{
			if (preg_match('|TOP [\d]+|i',$query,$limit)==1)
				{
					$limit=preg_replace('|TOP ([\d]+)|i','${1}',$limit[0]);
					$query=preg_replace('| TOP [\d]+|i','',$query);
					$query.=' LIMIT '.$limit;
				}
			for ($x=0;$x<count($matches['dq']);$x++)
				{
					$replacement=substr($matches['dq'][$x],1,-1);
					if (strpos($replacement,'`')!==false)
						{
							$replacement=str_replace('`','``',$replacement);
						}
					$query=str_replace($matches['dq'][$x],'`'.$replacement.'`',$query);
				}
			for ($x=0;$x<count($matches['sb']);$x++)
				{
					$replacement=substr($matches['sb'][$x],1,-1);
					if (strpos($replacement,'`')!==false)
						{
							$replacement=str_replace('`','``',$replacement);
						}
					$query=str_replace($matches['sb'][$x],'`'.$replacement.'`',$query);
				}
		}
	elseif ($type=='pgsql')
		{
			$query=preg_replace('|LIMIT ([\d]+),([\d]+)|i','LIMIT ${2} OFFSET ${1}',$query);
			if (preg_match('|TOP [\d]+|i',$query,$limit)==1)
				{
					$limit=preg_replace('|TOP ([\d]+)|i','${1}',$limit[0]);
					$query=preg_replace('| TOP [\d]+|i','',$query);
					$query.=' LIMIT '.$limit;
				}
			for ($x=0;$x<count($matches['bt']);$x++)
				{
					$replacement=substr($matches['bt'][$x],1,-1);
					if (strpos($replacement,'"')!==false)
						{
							$replacement=str_replace('"','""',$replacement);
						}
					$query=str_replace($matches['bt'][$x],'"'.$replacement.'"',$query);
				}
			for ($x=0;$x<count($matches['sb']);$x++)
				{
					$replacement=substr($matches['sb'][$x],1,-1);
					if (strpos($replacement,'"')!==false)
						{
							$replacement=str_replace('"','""',$replacement);
						}
					$query=str_replace($matches['sb'][$x],'"'.$replacement.'"',$query);
				}
		}
	elseif ($type=='mssql')
		{
			if (preg_match('|LIMIT [\d]+|i',$query,$limit)==1)
				{
					$limit=preg_replace('|LIMIT ([\d]+)|i','${1}',$limit[0]);
					$query=preg_replace(array('| LIMIT [\d]+|i','|([\w]+) |'),array('','${1} TOP '.$limit.' '),$query,1);
				}
			for ($x=0;$x<count($matches['bt']);$x++)
				{
					$replacement=substr($matches['bt'][$x],1,-1);
					if (strpos($replacement,']')!==false)
						{
							$replacement=str_replace(']',']]',$replacement);
						}
					$query=str_replace($matches['bt'][$x],'['.$replacement.']',$query);
				}
			for ($x=0;$x<count($matches['dq']);$x++)
				{
					$replacement=substr($matches['dq'][$x],1,-1);
					if (strpos($replacement,']')!==false)
						{
							$replacement=str_replace(']',']]',$replacement);
						}
					$query=str_replace($matches['dq'][$x],'['.$replacement.']',$query);
				}
		}
	return $query;
}

 

[Dennis Wronka]

So, und wieder ein Update.
Wie es aussieht funktioniert nun auch die OFFSET-Emulation fuer MSSQL. Das ganze ist natuerlich ein wenig mit Vorsicht zu geniessen da dabei ja nur so getan wird als ob.

Hier mal der entsprechende Code-Ausschnitt:

if (preg_match('|LIMIT [\d]+|i',$query,$limit)==1)
	{
		if (preg_match('|LIMIT [\d]+ OFFSET [\d]+|i',$query,$offset)==1)
			{
				$limit=preg_replace('|LIMIT ([\d]+)|i','${1}',$limit[0]);
				$this->offset=preg_replace('|LIMIT [\d]+ OFFSET ([\d]+)|i','${1}',$offset[0]);
				$query=preg_replace(array('| LIMIT [\d]+ OFFSET [\d]+|i','|([\w]+) |'),array('','${1} TOP '.($limit+$this->offset).' '),$query,1);
			}
		else
			{
				$limit=preg_replace('|LIMIT ([\d]+)|i','${1}',$limit[0]);
				$query=preg_replace(array('| LIMIT [\d]+|i','|([\w]+) |'),array('','${1} TOP '.$limit.' '),$query,1);
			}
	}

Am Anfang der Funktion convertsql() (dieser Code ist Teil der Funktion) wird $this->limit jedes Mal auf 0 gesetzt damit es nicht zu Problemen kommt nachdem mal eine Query mit LIMIT und OFFSET gearbeitet hat.
Zusaetzlich hab ich noch die Query-Funktion selbst und auch die Num-Rows-Funktion geaendert damit dort das richtige Ergebnis angezeigt wird.

public function query($query)
{
	$function='query_'.$this->type;
	$this->resultset=$this->{$function}($this->convertsql($query));
	if ($this->resultset===false)
		{
			return false;
		}
	else
		{
			if ($this->offset!=0)
				{
					for ($x=0;$x<$this->offset;$x++)
						{
							$this->fetch_row();
						}
				}
			return true;
		}
}

public function num_rows()
{
	$function='num_rows_'.$this->type;
	return $this->{$function}()-$this->offset;
}

Das scheint dann auch soweit zu funktionieren, was schonmal einen guten Schritt vorwaerts markiert. Ich will mal schauen ob ich hier auf der Arbeit nicht mal die Datenbank meiner Website auch auf unseren MSSQL-Server schubsen kann um zu gucken ob meine Seite dann auch mit MSSQL lauffaehig ist. Theoretisch sollte es jetzt auf jeden Fall gehen.