# HG changeset patch
# User peter
# Date 1198939354 0
# Node ID a885f557678f57f992c39552292e6b591be9bf54
# Parent  e858e7e1aab4f2c552ea3e41fdb423528fdb9dc3
[feladat @ 102]
Fixed the queries so both MySQL and PostgreSQL are supported.
The 'REGEXP' queries are currently fixed with a workaround.

diff -r e858e7e1aab4 -r a885f557678f edit.php
--- a/edit.php	Thu Dec 27 12:36:04 2007 +0000
+++ b/edit.php	Sat Dec 29 14:42:34 2007 +0000
@@ -30,7 +30,7 @@
 		foreach ($_POST["rowid"] as $x_user => $x_value){
 			$x_userid = $db->queryOne("SELECT id FROM record_owners WHERE user_id = '".$_POST["userid"]."' AND record_id='".$x_value."'");
 			if (empty($x_userid)) {
-				$db->query("INSERT INTO record_owners SET user_id = '".$_POST["userid"]."',record_id='".$x_value."'");
+				$db->query("INSERT INTO record_owners (user_id, record_id) VALUES ('".$_POST["userid"]."','".$x_value."')");
 			}
 		}
 	}
diff -r e858e7e1aab4 -r a885f557678f inc/database.inc.php
--- a/inc/database.inc.php	Thu Dec 27 12:36:04 2007 +0000
+++ b/inc/database.inc.php	Sat Dec 29 14:42:34 2007 +0000
@@ -50,5 +50,18 @@
 /* erase info */
 $mysql_pass = $dsn = '';
 
+// Add support for regular expressions in both MySQL and PostgreSQL
+if ( $dbdsntype == "mysql" ) 
+{
+	$sql_regexp = "REGEXP";
+} 
+elseif ( $dbdsntype == "pgsql" ) 
+{
+	$sql_regexp = "~";
+}
+else
+{
+	error(_('Unknown database type in inc/config.inc.php.'));
+};
 
 ?>
diff -r e858e7e1aab4 -r a885f557678f inc/dns.inc.php
--- a/inc/dns.inc.php	Thu Dec 27 12:36:04 2007 +0000
+++ b/inc/dns.inc.php	Sat Dec 29 14:42:34 2007 +0000
@@ -201,7 +201,7 @@
 	}
 	else
 	{
-		$prio='';
+		$prio=0;
 	}
 	// Validate the TTL, it has to be numeric.
 	$ttl = (!isset($ttl) || !is_numeric($ttl)) ? $DEFAULT_TTL : $ttl;
diff -r e858e7e1aab4 -r a885f557678f inc/record.inc.php
--- a/inc/record.inc.php	Thu Dec 27 12:36:04 2007 +0000
+++ b/inc/record.inc.php	Sat Dec 29 14:42:34 2007 +0000
@@ -37,10 +37,10 @@
 	 * YYYYMMDDnn
 	 */
 
-	$sqlq = "SELECT `notified_serial` FROM `domains` WHERE `id` = '".$domain_id."'";
+	$sqlq = "SELECT notified_serial FROM domains WHERE id = '".$domain_id."'";
 	$notified_serial = $db->queryOne($sqlq);
 
-	$sqlq = "SELECT `content` FROM `records` WHERE `type` = 'SOA' AND `domain_id` = '".$domain_id."'";
+	$sqlq = "SELECT content FROM records WHERE type = 'SOA' AND domain_id = '".$domain_id."'";
 	$content = $db->queryOne($sqlq);
     $need_to_update = false;
 	
@@ -101,7 +101,7 @@
 		{	
 			$new_soa .= $soa[$i] . " "; 
 		}
-		$sqlq = "UPDATE `records` SET `content` = '".$new_soa."' WHERE `domain_id` = '".$domain_id."' AND `type` = 'SOA' LIMIT 1";
+		$sqlq = "UPDATE records SET content = '".$new_soa."' WHERE domain_id = '".$domain_id."' AND type = 'SOA'";
 		$db->Query($sqlq);
 		return true;
 	}
@@ -284,7 +284,7 @@
 	if (is_numeric($id))
 	{
 	    $did = recid_to_domid($id);
-		$db->query('DELETE FROM records WHERE id=' . $id . ' LIMIT 1');
+		$db->query('DELETE FROM records WHERE id=' . $id );
 		if ($type != 'SOA')
 		{
 			update_soa_serial($did);
@@ -364,7 +364,7 @@
 				$ttl = $GLOBALS["DEFAULT_TTL"];
 
 				// Build and execute query
-				$sql = "INSERT INTO records (domain_id, name, content, type, ttl, prio, change_date) VALUES ('$iddomain', '$domain', '$ns1 $hm 1', 'SOA', $ttl, '', '$now')";
+				$sql = "INSERT INTO records (domain_id, name, content, type, ttl, prio, change_date) VALUES ('$iddomain', '$domain', '$ns1 $hm 1', 'SOA', $ttl, 0, '$now')";
 				$db->query($sql);
 
 				// Done
@@ -386,7 +386,7 @@
 						$type     = $r["type"];
 						$content  = parse_template_value($r["content"], $domain, $webip, $mailip);
 						$ttl      = $r["ttl"];
-						$prio     = $r["prio"];
+						$prio     = intval($r["prio"]);
 
 						// If no ttl is given, use the default.
 						if (!$ttl)
@@ -719,8 +719,8 @@
         AND record_owners.record_id = records.id
 	AND records.domain_id = ".$id."
 
-	GROUP BY name, owner, users.fullname
-	ORDER BY name";
+	GROUP BY domains.name, owner, users.fullname, domains.type
+	ORDER BY domains.name";
 	
 	$result = $db->queryRow($sqlq);
 
@@ -748,7 +748,7 @@
 			LEFT JOIN zones ON domains.id=zones.domain_id
 			LEFT JOIN users ON zones.owner=users.id
 			WHERE domains.id=$id
-			GROUP BY name, owner, users.fullname
+			GROUP BY domains.name, owner, users.fullname, domains.type, zones.id
 			ORDER BY zones.id";
 
 		// Put the first occurence in an array and return it.
@@ -863,6 +863,7 @@
 function get_domains($userid=true,$letterstart=all,$rowstart=0,$rowamount=999999)
 {
 	global $db;
+	global $sql_regexp;
 	if((!level(5) || !$userid) && !level(10) && !level(5))
 	{
 		$add = " AND zones.owner=".$_SESSION["userid"];
@@ -879,15 +880,15 @@
 	FROM domains
 	LEFT JOIN zones ON domains.id=zones.domain_id 
 	LEFT JOIN records ON records.domain_id=domains.id
-	WHERE 1 $add ";
+	WHERE 1=1 $add ";
 	if ($letterstart!=all && $letterstart!=1) {
-	   $sqlq.=" AND substring(domains.name,1,1) REGEXP '^".$letterstart."' ";
+	   $sqlq.=" AND substring(domains.name,1,1) ".$sql_regexp." '^".$letterstart."' ";
 	} elseif ($letterstart==1) {
-	   $sqlq.=" AND substring(domains.name,1,1) REGEXP '^[[:digit:]]'";
+	   $sqlq.=" AND substring(domains.name,1,1) ".$sql_regexp." '^[[:digit:]]'";
 	}
-	$sqlq.=" GROUP BY domainname, domain_id
+	$sqlq.=" GROUP BY domainname, domains.id
 	ORDER BY domainname
-	LIMIT $rowstart,$rowamount";
+	LIMIT $rowamount OFFSET $rowstart";
 
 	$result = $db->query($sqlq);
 	$result2 = $db->query($sqlq); 
@@ -922,7 +923,7 @@
 		$andnot 
 		AND domains.name LIKE '".$letterstart."%' 
 		AND (zones.domain_id != records.domain_id AND zones.owner!='".$_SESSION["userid"]."')
-		GROUP BY domainname, domain_id
+		GROUP BY domainname, domains.id
 		ORDER BY domainname";
 
 		$result_extra = $db->query($sqlq);
@@ -937,9 +938,9 @@
 		AND (records.id = record_owners.record_id
 		AND domains.id = records.domain_id)
 		$andnot 
-		AND substring(domains.name,1,1) REGEXP '^[[:digit:]]'
+		AND substring(domains.name,1,1) ".$sql_regexp." '^[[:digit:]]'
 		AND (zones.domain_id != records.domain_id AND zones.owner!='".$_SESSION["userid"]."')
-		GROUP BY domainname, domain_id
+		GROUP BY domainname, domains.id
 		ORDER BY domainname";
 
 		$result_extra[$i] = $db->query($sqlq);
@@ -1009,6 +1010,7 @@
 
 function zone_count($userid=true, $letterstart=all) {
         global $db;
+	global $sql_regexp;
         if((!level(5) || !$userid) && !level(10) && !level(5))
         {
 		// First select the zones for which we have ownership on one or more records.
@@ -1034,12 +1036,12 @@
         if ($letterstart!=all && $letterstart!=1) {
            $add .=" AND domains.name LIKE '".$letterstart."%' ";
         } elseif ($letterstart==1) {
-           $add .=" AND substring(domains.name,1,1) REGEXP '^[[:digit:]]'";
+           $add .=" AND substring(domains.name,1,1) ".$sql_regexp." '^[[:digit:]]'";
         }
 
         if (level(5))
         {
-                $query = 'SELECT count(distinct domains.id) as zone_count FROM domains WHERE 1 '.$add;
+                $query = 'SELECT count(distinct domains.id) as zone_count FROM domains WHERE 1=1 '.$add;
         }
         else
         {
@@ -1109,7 +1111,7 @@
 		AND record_owners.record_id = records.id
 		AND records.domain_id = ".$id."
 		GROUP bY record_owners.record_id
-		LIMIT $rowstart,$rowamount");
+		LIMIT $rowamount OFFSET $rowstart");
 
 		$ret = array();
 		if($result->numRows() == 0)
@@ -1131,7 +1133,7 @@
 
 		} else {
 
-		$result = $db->query("SELECT id FROM records WHERE domain_id=$id LIMIT $rowstart,$rowamount");
+		$result = $db->query("SELECT id FROM records WHERE domain_id=$id LIMIT $rowamount OFFSET $rowstart");
 		$ret = array();
 		if($result->numRows() == 0)
 		{
@@ -1202,7 +1204,7 @@
 	switch($S_INPUT_TYPE)
 	{
 		case '0': 
-			$sqlq = "SELECT * FROM `records` WHERE `content` = '".$question."' ORDER BY `type` DESC";
+			$sqlq = "SELECT * FROM records WHERE content = '".$question."' ORDER BY type DESC";
 			$result = $db->query($sqlq);
 			$ret_r = array();
 			while ($r = $result->fetchRow())
@@ -1224,12 +1226,12 @@
 			break;
 	    
 		case '1' :
-			$sqlq = "SELECT `domains`.*, count(`records`.`id`) AS `numrec`, `zones`.`owner`, `records`.`domain_id`
-					FROM `domains`, `records`, `zones`  
-					WHERE `domains`.`id` = `records`.`domain_id` 
-					AND `zones`.`domain_id` = `domains`.`id` 
-					AND `domains`.`name` = '".$question."' 
-					GROUP BY (`domains`.`id`)";
+			$sqlq = "SELECT domains.id, domains.name, count(records.id) AS numrec, zones.owner, records.domain_id
+					FROM domains, records, zones  
+					WHERE domains.id = records.domain_id 
+					AND zones.domain_id = domains.id 
+					AND domains.name = '".$question."' 
+					GROUP BY domains.id, domains.name, zones.owner, records.domain_id";
 
 			$result = $db->query($sqlq);
 			$ret_d = array();
@@ -1246,7 +1248,7 @@
 				}
 			}
 
-			$sqlq = "SELECT * FROM `records` WHERE `name` = '".$question."' OR `content` = '".$question."' ORDER BY `type` DESC";
+			$sqlq = "SELECT * FROM records WHERE name = '".$question."' OR content = '".$question."' ORDER BY type DESC";
 			$result = $db->query($sqlq);
 			while ($r = $result->fetchRow())
 			{
@@ -1277,7 +1279,7 @@
 	global $db;
         if (is_numeric($id))
 	{
-		$type = $db->queryOne("SELECT `type` FROM `domains` WHERE `id` = '".$id."'");
+		$type = $db->queryOne("SELECT type FROM domains WHERE id = '".$id."'");
 		if($type == "")
 		{
 			$type = "NATIVE";
@@ -1295,7 +1297,7 @@
 	global $db;
         if (is_numeric($id))
 	{
-		$slave_master = $db->queryOne("SELECT `master` FROM `domains` WHERE `type` = 'SLAVE' and `id` = '".$id."'");
+		$slave_master = $db->queryOne("SELECT master FROM domains WHERE type = 'SLAVE' and id = '".$id."'");
 		return $slave_master;
         }
         else
@@ -1317,7 +1319,7 @@
 		{
 			$add = ", master=''";
 		}
-		$result = $db->query("UPDATE `domains` SET `type` = '" .$type. "'".$add." WHERE `id` = '".$id."'");
+		$result = $db->query("UPDATE domains SET type = '" .$type. "'".$add." WHERE id = '".$id."'");
 	}
         else
         {
@@ -1332,7 +1334,7 @@
 	{
        		if (is_valid_ip($slave_master) || is_valid_ip6($slave_master))
 		{
-			$result = $db->query("UPDATE `domains` SET `master` = '" .$slave_master. "' WHERE `id` = '".$id."'");
+			$result = $db->query("UPDATE domains SET master = '" .$slave_master. "' WHERE id = '".$id."'");
 		}
 		else
 		{
diff -r e858e7e1aab4 -r a885f557678f inc/toolkit.inc.php
--- a/inc/toolkit.inc.php	Thu Dec 27 12:36:04 2007 +0000
+++ b/inc/toolkit.inc.php	Sat Dec 29 14:42:34 2007 +0000
@@ -146,6 +146,7 @@
 function zone_letter_start($letter,$userid=true)
 {
         global $db;
+	global $sql_regexp;
         $sqlq = "SELECT domains.id AS domain_id,
         zones.owner,
         records.id,
@@ -153,7 +154,7 @@
         FROM domains
         LEFT JOIN zones ON domains.id=zones.domain_id 
         LEFT JOIN records ON records.domain_id=domains.id
-        WHERE 1";
+        WHERE 1=1";
         if((!level(5) || !$userid) && !level(10) && !level(5))
         {
 		// First select the zones for which we have ownership on one or more records.
@@ -171,7 +172,7 @@
 		}
 		$sqlq .= ')';
         }
-        $sqlq .= " AND substring(domains.name,1,1) REGEXP '^".$letter."' LIMIT 1";
+        $sqlq .= " AND substring(domains.name,1,1) ".$sql_regexp." '^".$letter."' LIMIT 1";
         $result = $db->query($sqlq);
         $numrows = $result->numRows();
         if ( $numrows == "1" ) 
diff -r e858e7e1aab4 -r a885f557678f inc/users.inc.php
--- a/inc/users.inc.php	Thu Dec 27 12:36:04 2007 +0000
+++ b/inc/users.inc.php	Sat Dec 29 14:42:34 2007 +0000
@@ -57,7 +57,7 @@
 			users.active
 		ORDER BY
 			users.fullname
-	 	LIMIT $rowstart,$rowamount";
+	 	LIMIT $rowamount OFFSET $rowstart";
 
 	// Execute the huge query.
 	$result = $db->query($sqlq);