From bd4400988922b2560975a80498404d7ecd000c0b Mon Sep 17 00:00:00 2001
From: Derek Jones <derek.jones@ellislab.com>
Date: Thu, 29 May 2008 17:52:11 +0000
Subject: made MySQL/MySQLi forge use explicitly named KEYs, added ability to
 specify multi-column non-primary keys in table creation

---
 system/database/drivers/mssql/mssql_forge.php     | 16 ++++++++++++----
 system/database/drivers/mysql/mysql_forge.php     | 17 ++++++++++++++---
 system/database/drivers/mysqli/mysqli_forge.php   | 17 ++++++++++++++---
 system/database/drivers/oci8/oci8_forge.php       | 17 ++++++++++++++---
 system/database/drivers/odbc/odbc_forge.php       | 16 ++++++++++++----
 system/database/drivers/postgre/postgre_forge.php | 14 +++++++++++---
 system/database/drivers/sqlite/sqlite_forge.php   | 21 ++++++++++++++++-----
 system/plugins/captcha_pi.php                     |  4 ++--
 user_guide/changelog.html                         |  2 ++
 user_guide/database/forge.html                    | 12 ++++++++++--
 user_guide/libraries/trackback.html               |  4 ++--
 11 files changed, 109 insertions(+), 31 deletions(-)

diff --git a/system/database/drivers/mssql/mssql_forge.php b/system/database/drivers/mssql/mssql_forge.php
index ddd1bb6ae..6995d3422 100644
--- a/system/database/drivers/mssql/mssql_forge.php
+++ b/system/database/drivers/mssql/mssql_forge.php
@@ -147,16 +147,24 @@ class CI_DB_mssql_forge extends CI_DB_forge {
 			$primary_keys = $this->db->_protect_identifiers($primary_keys);
 			$sql .= ",\n\tPRIMARY KEY (" . implode(', ', $primary_keys) . ")";
 		}
-
+		
 		if (is_array($keys) && count($keys) > 0)
 		{
-			$keys = $this->db->_protect_identifiers($keys);
 			foreach ($keys as $key)
 			{
-				$sql .= ",\n\tFOREIGN KEY ($key)";
+				if (is_array($key))
+				{
+					$key = $this->db->_protect_identifiers($key);	
+				}
+				else
+				{
+					$key = array($this->db->_protect_identifiers($key));
+				}
+				
+				$sql .= ",\n\tFOREIGN KEY (" . implode(', ', $key) . ")";
 			}
 		}
-
+		
 		$sql .= "\n)";
 
 		return $sql;
diff --git a/system/database/drivers/mysql/mysql_forge.php b/system/database/drivers/mysql/mysql_forge.php
index a631e4301..28143a04d 100644
--- a/system/database/drivers/mysql/mysql_forge.php
+++ b/system/database/drivers/mysql/mysql_forge.php
@@ -153,16 +153,27 @@ class CI_DB_mysql_forge extends CI_DB_forge {
 
 		if (count($primary_keys) > 0)
 		{
+			$key_name = $this->db->_protect_identifiers(implode('_', $primary_keys));
 			$primary_keys = $this->db->_protect_identifiers($primary_keys);
-			$sql .= ",\n\tPRIMARY KEY (" . implode(', ', $primary_keys) . ")";
+			$sql .= ",\n\tPRIMARY KEY ".$key_name." (" . implode(', ', $primary_keys) . ")";
 		}
 
 		if (is_array($keys) && count($keys) > 0)
 		{
-			$keys = $this->db->_protect_identifiers($keys);
 			foreach ($keys as $key)
 			{
-				$sql .= ",\n\tKEY ($key)";
+				if (is_array($key))
+				{
+					$key_name = $this->db->_protect_identifiers(implode('_', $key));
+					$key = $this->db->_protect_identifiers($key);	
+				}
+				else
+				{
+					$key_name = $this->db->_protect_identifiers($key);
+					$key = array($key_name);
+				}
+				
+				$sql .= ",\n\tKEY {$key_name} (" . implode(', ', $key) . ")";
 			}
 		}
 
diff --git a/system/database/drivers/mysqli/mysqli_forge.php b/system/database/drivers/mysqli/mysqli_forge.php
index f767acbea..da79bc6ac 100644
--- a/system/database/drivers/mysqli/mysqli_forge.php
+++ b/system/database/drivers/mysqli/mysqli_forge.php
@@ -153,16 +153,27 @@ class CI_DB_mysqli_forge extends CI_DB_forge {
 
 		if (count($primary_keys) > 0)
 		{
+			$key_name = $this->db->_protect_identifiers(implode('_', $primary_keys));
 			$primary_keys = $this->db->_protect_identifiers($primary_keys);
-			$sql .= ",\n\tPRIMARY KEY (" . implode(', ', $primary_keys) . ")";
+			$sql .= ",\n\tPRIMARY KEY ".$key_name." (" . implode(', ', $primary_keys) . ")";
 		}
 
 		if (is_array($keys) && count($keys) > 0)
 		{
-			$keys = $this->db->_protect_identifiers($keys);
 			foreach ($keys as $key)
 			{
-				$sql .= ",\n\tKEY ($key)";
+				if (is_array($key))
+				{
+					$key_name = $this->db->_protect_identifiers(implode('_', $key));
+					$key = $this->db->_protect_identifiers($key);	
+				}
+				else
+				{
+					$key_name = $this->db->_protect_identifiers($key);
+					$key = array($key_name);
+				}
+				
+				$sql .= ",\n\tKEY {$key_name} (" . implode(', ', $key) . ")";
 			}
 		}
 
diff --git a/system/database/drivers/oci8/oci8_forge.php b/system/database/drivers/oci8/oci8_forge.php
index 9f3fac54f..6266c75a3 100644
--- a/system/database/drivers/oci8/oci8_forge.php
+++ b/system/database/drivers/oci8/oci8_forge.php
@@ -135,10 +135,21 @@ class CI_DB_oci8_forge extends CI_DB_forge {
 			$sql .= ",\n\tPRIMARY KEY (" . implode(', ', $primary_keys) . ")";
 		}
 
-		if (count($keys) > 0)
+		if (is_array($keys) && count($keys) > 0)
 		{
-			$keys = $this->db->_protect_identifiers($keys);
-			$sql .= ",\n\tUNIQUE COLUMNS (" . implode(', ', $keys) . ")";
+			foreach ($keys as $key)
+			{
+				if (is_array($key))
+				{
+					$key = $this->db->_protect_identifiers($key);	
+				}
+				else
+				{
+					$key = array($this->db->_protect_identifiers($key));
+				}
+				
+				$sql .= ",\n\tUNIQUE COLUMNS (" . implode(', ', $key) . ")";
+			}
 		}
 		
 		$sql .= "\n)";
diff --git a/system/database/drivers/odbc/odbc_forge.php b/system/database/drivers/odbc/odbc_forge.php
index 60df616c3..10924abe2 100644
--- a/system/database/drivers/odbc/odbc_forge.php
+++ b/system/database/drivers/odbc/odbc_forge.php
@@ -146,16 +146,24 @@ class CI_DB_odbc_forge extends CI_DB_forge {
 			$primary_keys = $this->db->_protect_identifiers($primary_keys);
 			$sql .= ",\n\tPRIMARY KEY (" . implode(', ', $primary_keys) . ")";
 		}
-
+		
 		if (is_array($keys) && count($keys) > 0)
 		{
-			$keys = $this->db->_protect_identifiers($keys);
 			foreach ($keys as $key)
 			{
-				$sql .= ",\n\tFOREIGN KEY ($key)";
+				if (is_array($key))
+				{
+					$key = $this->db->_protect_identifiers($key);	
+				}
+				else
+				{
+					$key = array($this->db->_protect_identifiers($key));
+				}
+				
+				$sql .= ",\n\tFOREIGN KEY (" . implode(', ', $key) . ")";
 			}
 		}
-
+		
 		$sql .= "\n)";
 
 		return $sql;
diff --git a/system/database/drivers/postgre/postgre_forge.php b/system/database/drivers/postgre/postgre_forge.php
index f8dfca8a1..ef5783451 100644
--- a/system/database/drivers/postgre/postgre_forge.php
+++ b/system/database/drivers/postgre/postgre_forge.php
@@ -134,13 +134,21 @@ class CI_DB_postgre_forge extends CI_DB_forge {
 			$primary_keys = $this->db->_protect_identifiers($primary_keys);
 			$sql .= ",\n\tPRIMARY KEY (" . implode(', ', $primary_keys) . ")";
 		}
-
+		
 		if (is_array($keys) && count($keys) > 0)
 		{
-			$keys = $this->db->_protect_identifiers($keys);
 			foreach ($keys as $key)
 			{
-				$sql .= ",\n\tFOREIGN KEY ($key)";
+				if (is_array($key))
+				{
+					$key = $this->db->_protect_identifiers($key);	
+				}
+				else
+				{
+					$key = array($this->db->_protect_identifiers($key));
+				}
+				
+				$sql .= ",\n\tFOREIGN KEY (" . implode(', ', $key) . ")";
 			}
 		}
 
diff --git a/system/database/drivers/sqlite/sqlite_forge.php b/system/database/drivers/sqlite/sqlite_forge.php
index 25c74a731..a6866c877 100644
--- a/system/database/drivers/sqlite/sqlite_forge.php
+++ b/system/database/drivers/sqlite/sqlite_forge.php
@@ -144,13 +144,24 @@ class CI_DB_sqlite_forge extends CI_DB_forge {
 			$primary_keys = $this->db->_protect_identifiers($primary_keys);
 			$sql .= ",\n\tPRIMARY KEY (" . implode(', ', $primary_keys) . ")";
 		}
-
-		if (count($keys) > 0)
+		
+		if (is_array($keys) && count($keys) > 0)
 		{
-			$keys = $this->db->_protect_identifiers($keys);
-			$sql .= ",\n\tUNIQUE (" . implode(', ', $keys) . ")";
+			foreach ($keys as $key)
+			{
+				if (is_array($key))
+				{
+					$key = $this->db->_protect_identifiers($key);	
+				}
+				else
+				{
+					$key = array($this->db->_protect_identifiers($key));
+				}
+				
+				$sql .= ",\n\tUNIQUE (" . implode(', ', $key) . ")";
+			}
 		}
-
+		
 		$sql .= "\n)";
 
 		return $sql;
diff --git a/system/plugins/captcha_pi.php b/system/plugins/captcha_pi.php
index df95788ee..baa0a8dc5 100644
--- a/system/plugins/captcha_pi.php
+++ b/system/plugins/captcha_pi.php
@@ -92,8 +92,8 @@ Here is a table prototype:
 	 captcha_time int(10) unsigned NOT NULL,
 	 ip_address varchar(16) default '0' NOT NULL,
 	 word varchar(20) NOT NULL,
-	 PRIMARY KEY (captcha_id),
-	 KEY (word)
+	 PRIMARY KEY `captcha_id` (`captcha_id`),
+	 KEY `word` (`word`)
 	)
 
 
diff --git a/user_guide/changelog.html b/user_guide/changelog.html
index 9e39cb4b0..b59b9ef9b 100644
--- a/user_guide/changelog.html
+++ b/user_guide/changelog.html
@@ -63,6 +63,8 @@ SVN Revision: not currently released</p>
 <ul>
 	<li>Database
 		<ul>
+			<li>Modified MySQL/MySQLi Forge class to give explicit names to keys</li>
+			<li>Added ability to set multiple column non-primary keys to the <a href="database/forge.html">Forge class</a></li>
 			<li>Added ability to set additional database config values in <a href="database/connecting.html">DSN connections</a> via the query string.</li>
 			</ul>
 	</li>
diff --git a/user_guide/database/forge.html b/user_guide/database/forge.html
index f9363f4e3..f679a4f29 100644
--- a/user_guide/database/forge.html
+++ b/user_guide/database/forge.html
@@ -164,11 +164,19 @@ already be running, since the forge class relies on it.</p>
     // gives id INT(9) NOT NULL AUTO_INCREMENT</code></p>
 <h2><a name="add_key" id="add_key"></a>Adding Keys</h2>
 <p>Generally speaking, you'll want your table to have Keys. This is accomplished with <dfn>$this-&gt;dbforge-&gt;add_key('field')</dfn>. An optional second parameter set to TRUE will make it a primary key. Note that <dfn>add_key()</dfn> must be followed by a call to <dfn>create_table()</dfn>.</p>
+<p>Multiple column non-primary keys must be sent as an array.  Sample output below is for MySQL.</p>
 <p><code>$this-&gt;dbforge-&gt;add_key('blog_id', TRUE);<br />
-    // gives PRIMARY KEY (blog_id)<br />
+    // gives PRIMARY KEY `blog_id` (`blog_id`)<br />
     <br />
+    $this-&gt;dbforge-&gt;add_key('blog_id', TRUE);<br />
+    $this-&gt;dbforge-&gt;add_key('site_id', TRUE);<br />
+    // gives PRIMARY KEY `blog_id_site_id` (`blog_id`, `site_id`)<br />
+	<br />
     $this-&gt;dbforge-&gt;add_key('blog_name');<br />
-    // gives KEY (blog_name)</code></p>
+    // gives KEY `blog_name` (`blog_name`)<br />
+    <br />
+    $this-&gt;dbforge-&gt;add_key(array('blog_name', 'blog_label'));<br />
+    // gives KEY `blog_name_blog_label` (`blog_name`, `blog_label`)</code></p>
 <h2><a name="create_table" id="create_table"></a>Creating a table</h2>
 <p>After fields and keys have been declared, you can create a new table with</p>
 <p><code>$this-&gt;dbforge-&gt;create_table('table_name');<br />
diff --git a/user_guide/libraries/trackback.html b/user_guide/libraries/trackback.html
index f86ccca02..4f60f5ab5 100644
--- a/user_guide/libraries/trackback.html
+++ b/user_guide/libraries/trackback.html
@@ -152,8 +152,8 @@ CREATE TABLE trackbacks (
  blog_name varchar(100) NOT NULL,
  tb_date int(10) NOT NULL,
  ip_address varchar(16) NOT NULL,
- PRIMARY KEY (tb_id),
- KEY (entry_id)
+ PRIMARY KEY `tb_id` (`tb_id`),
+ KEY `entry_id` (`entry_id`)
 );</textarea>
 
 
-- 
cgit v1.2.3-24-g4f1b