From 295ea12e8c1360dc85cf387969731c477ddd8700 Mon Sep 17 00:00:00 2001
From: chriskl <chriskl>
Date: Mon, 24 Mar 2003 06:59:23 +0000
Subject: [PATCH] Add Primary Key

---
 classes/database/Postgres.php   |  6 +--
 classes/database/Postgres71.php | 15 +-----
 classes/database/Postgres72.php | 23 +++++++-
 classes/database/Postgres73.php | 19 ++++++-
 constraints.php                 | 95 +++++++++++++++++++++++++++++++--
 lang/english.php                | 10 +++-
 lang/recoded/english.php        | 10 +++-
 7 files changed, 153 insertions(+), 25 deletions(-)

diff --git a/classes/database/Postgres.php b/classes/database/Postgres.php
index 1d7acd27..7bd8118d 100755
--- a/classes/database/Postgres.php
+++ b/classes/database/Postgres.php
@@ -4,7 +4,7 @@
  * A class that implements the DB interface for Postgres
  * Note: This class uses ADODB and returns RecordSets.
  *
- * $Id: Postgres.php,v 1.62 2003/03/22 15:17:59 chriskl Exp $
+ * $Id: Postgres.php,v 1.63 2003/03/24 06:59:23 chriskl Exp $
  */
 
 // @@@ THOUGHT: What about inherits? ie. use of ONLY???
@@ -944,7 +944,7 @@ class Postgres extends BaseDB {
 	 * @param $name (optional) The name to give the key, otherwise default name is assigned
 	 * @return 0 success
 	 */
-	function addPrimaryKeyConstraint($table, $fields, $name = '') {
+	function addPrimaryKey($table, $fields, $name = '') {
 		// This function can be faked with a unique index and a catalog twiddle, however
 		// how do we ensure that it's only used on NOT NULL fields?
 		return -99; // Not supported.
@@ -956,7 +956,7 @@ class Postgres extends BaseDB {
 	 * @param $name The name of the primary key
 	 * @return 0 success
 	 */
-	function dropPrimaryKeyConstraint($table, $name) {
+	function dropPrimaryKey($table, $name) {
 		$this->fieldClean($name);
 		
 		$sql = "DROP INDEX \"{$name}\"";
diff --git a/classes/database/Postgres71.php b/classes/database/Postgres71.php
index 66a52686..32eddb29 100644
--- a/classes/database/Postgres71.php
+++ b/classes/database/Postgres71.php
@@ -4,7 +4,7 @@
  * A class that implements the DB interface for Postgres
  * Note: This class uses ADODB and returns RecordSets.
  *
- * $Id: Postgres71.php,v 1.26 2003/03/22 15:18:00 chriskl Exp $
+ * $Id: Postgres71.php,v 1.27 2003/03/24 06:59:23 chriskl Exp $
  */
 
 // @@@ THOUGHT: What about inherits? ie. use of ONLY???
@@ -249,19 +249,6 @@ class Postgres71 extends Postgres {
 		return $this->execute($sql);
 	}	
 	 
-	/**
-	 * Adds a primary key constraint to a table
-	 * @param $table The table to which to add the primery key
-	 * @param $fields (array) An array of fields over which to add the primary key
-	 * @param $name (optional) The name to give the key, otherwise default name is assigned
-	 * @return 0 success
-	 */
-	function addPrimaryKeyConstraint($table, $fields, $name = '') {
-		// This function can be faked with a unique index and a catalog twiddle, however
-		// how do we ensure that it's only used on NOT NULL fields?
-		return -99; // Not supported.
-	}
-
 	/**
 	 * Drops a primary key constraint from a table
 	 * @param $table The table from which to drop the primary key
diff --git a/classes/database/Postgres72.php b/classes/database/Postgres72.php
index 9d1be2e0..cc018a0f 100644
--- a/classes/database/Postgres72.php
+++ b/classes/database/Postgres72.php
@@ -4,7 +4,7 @@
  * A class that implements the DB interface for Postgres
  * Note: This class uses ADODB and returns RecordSets.
  *
- * $Id: Postgres72.php,v 1.27 2003/03/14 03:14:52 chriskl Exp $
+ * $Id: Postgres72.php,v 1.28 2003/03/24 06:59:24 chriskl Exp $
  */
 
 
@@ -110,7 +110,28 @@ class Postgres72 extends Postgres71 {
 		return $this->selectSet($sql);
 	}
 
+	// Constraint functions
+	
+	/**
+	 * Adds a primary key constraint to a table
+	 * @param $table The table to which to add the primery key
+	 * @param $fields (array) An array of fields over which to add the primary key
+	 * @param $name (optional) The name to give the key, otherwise default name is assigned
+	 * @return 0 success
+	 * @return -1 no fields given
+	 */
+	function addPrimaryKey($table, $fields, $name = '') {
+		if (!is_array($fields) || sizeof($fields) == 0) return -1;
+		$this->fieldClean($table);
+		$this->fieldArrayClean($fields);
+		$this->fieldClean($name);
+		
+		$sql = "ALTER TABLE \"{$table}\" ADD ";
+		if ($name != '') $sql .= "CONSTRAINT \"{$name}\" ";
+		$sql .= "PRIMARY KEY (\"" . join('","', $fields) . "\")";
 
+		return $this->execute($sql);
+	}
 
 	// Function functions
 
diff --git a/classes/database/Postgres73.php b/classes/database/Postgres73.php
index 8d29ac55..f880f864 100644
--- a/classes/database/Postgres73.php
+++ b/classes/database/Postgres73.php
@@ -4,7 +4,7 @@
  * A class that implements the DB interface for Postgres
  * Note: This class uses ADODB and returns RecordSets.
  *
- * $Id: Postgres73.php,v 1.28 2003/03/22 15:18:00 chriskl Exp $
+ * $Id: Postgres73.php,v 1.29 2003/03/24 06:59:24 chriskl Exp $
  */
 
 // @@@ THOUGHT: What about inherits? ie. use of ONLY???
@@ -382,6 +382,23 @@ class Postgres73 extends Postgres72 {
 		return $this->execute($sql);
 	}
 
+	// Constraint functions
+	
+	/**
+	 * Drops a primary key constraint from a table
+	 * @param $table The table from which to drop the primary key
+	 * @param $name The name of the primary key
+	 * @return 0 success
+	 */
+	function dropPrimaryKey($table, $name) {
+		$this->fieldClean($table);
+		$this->fieldClean($name);
+		
+		$sql = "ALTER TABLE \"{$table}\" DROP CONSTRAINT \"{$name}\"";
+
+		return $this->execute($sql);
+	}
+		
 	// View functions
 	
 	/**
diff --git a/constraints.php b/constraints.php
index 75fcc0a5..3f5e94a8 100644
--- a/constraints.php
+++ b/constraints.php
@@ -3,15 +3,91 @@
 	/**
 	 * List constraints on a table
 	 *
-	 * $Id: constraints.php,v 1.8 2003/03/23 03:13:57 chriskl Exp $
+	 * $Id: constraints.php,v 1.9 2003/03/24 06:59:23 chriskl Exp $
 	 */
 
 	// Include application functions
 	include_once('libraries/lib.inc.php');
+	include_once('classes/class.select.php');
 
 	$action = (isset($_REQUEST['action'])) ? $_REQUEST['action'] : '';
 	$PHP_SELF = $_SERVER['PHP_SELF'];
 
+	/**
+	 * Confirm and then actually add a PRIMARY KEY constraint
+	 */
+	function addPrimaryKey($confirm, $msg = '') {
+		global $PHP_SELF, $data, $localData, $misc;
+		global $lang;
+
+		if (!isset($_POST['name'])) $_POST['name'] = '';
+
+		if ($confirm) {
+			if (!isset($_POST['name'])) $_POST['name'] = '';
+			//if (!isset($_POST['cols'])) $_POST['cols'] = '';
+	
+			echo "<h2>", htmlspecialchars($_REQUEST['database']), ": {$lang['strtables']}: ",
+				htmlspecialchars($_REQUEST['table']), ": {$lang['straddpk']}</h2>\n";
+			$misc->printMsg($msg);
+			
+			$attrs = &$localData->getTableAttributes($_REQUEST['table']);
+	
+			$selColumns = new XHTML_select('TableColumnList', true, 10);
+			$selColumns->set_style('width: 10em;');
+	
+			if ($attrs->recordCount() > 0) {
+				while (!$attrs->EOF) {
+					$selColumns->add(new XHTML_Option($attrs->f['attname']));
+					$attrs->moveNext();
+				} 
+			}
+	
+			$selIndex = new XHTML_select('IndexColumnList[]', true, 10);
+			$selIndex->set_style('width: 10em;');
+			$selIndex->set_attribute('id', 'IndexColumnList');
+			$buttonAdd = new XHTML_Button('add', '>>');
+			$buttonAdd->set_attribute('onclick', 'buttonPressed(this);');
+			$buttonAdd->set_attribute('type', 'button');
+	
+			$buttonRemove = new XHTML_Button('remove', '<<');
+			$buttonRemove->set_attribute('onclick', 'buttonPressed(this);');
+			$buttonRemove->set_attribute('type', 'button');
+	
+			echo "<form onsubmit=\"doSelectAll();\" name=\"formIndex\" action=\"$PHP_SELF\" method=\"post\">\n";	
+	
+			echo "<table>\n";
+			echo "<tr><th class=\"data\" colspan=\"3\">{$lang['strname']}</th></tr>";
+			echo "<tr>";
+			echo "<td class=\"data1\" colspan=\"3\"><input type=\"text\" name=\"name\" size=\"32\" maxlength=\"{$data->_maxNameLen}\" /></td></tr>";
+			echo "<tr><th class=\"data\">{$lang['strtablecolumnlist']}</th><th class=\"data\">&nbsp;</th><th class=data>{$lang['strindexcolumnlist']}</th></tr>\n";
+			echo "<tr><td class=\"data1\">" . $selColumns->fetch() . "</td>\n";
+			echo "<td class=\"data1\" align=\"center\">" . $buttonRemove->fetch() . $buttonAdd->fetch() . "</td>";
+			echo "<td class=data1>" . $selIndex->fetch() . "</td></tr>\n";
+			echo "</table>\n";
+	
+			echo "<p><input type=\"hidden\" name=\"action\" value=\"save_add_primary_key\">\n";
+			echo $misc->form;
+			echo "<input type=\"hidden\" name=\"table\" value=\"", htmlspecialchars($_REQUEST['table']), "\">\n";
+			echo "<input type=\"submit\" value=\"{$lang['stradd']}\"> <input type=reset value=\"{$lang['strreset']}\"></p>\n";
+			echo "</form>\n";
+			
+			echo "<p><a class=\"navlink\" href=\"$PHP_SELF?{$misc->href}&table=", urlencode($_REQUEST['table']), 
+				"\">{$lang['strshowallconstraints']}</a></p>\n";
+		}
+		else {
+			// Check that they've given at least one column
+			if (!isset($_POST['IndexColumnList']) || !is_array($_POST['IndexColumnList']) 
+					|| sizeof($_POST['IndexColumnList']) == 0) addPrimaryKey(true, $lang['strpkneedscols']);
+			else {
+				$status = $localData->addPrimaryKey($_POST['table'], $_POST['IndexColumnList'], $_POST['name']);
+				if ($status == 0)
+					doDefault($lang['strpkadded']);
+				else
+					addPrimaryKey(true, $lang['strpkaddedbad']);
+			}
+		}
+	}
+
 	/**
 	 * Confirm and then actually add a CHECK constraint
 	 */
@@ -138,13 +214,24 @@
 			echo "<p>{$lang['strnoconstraints']}</p>\n";
 		
 		echo "<p><a href=\"{$PHP_SELF}?action=add_check&{$misc->href}&table=", urlencode($_REQUEST['table']),
-			"\">{$lang['straddcheck']}</a></p>\n";
+			"\">{$lang['straddcheck']}</a> |\n";
+		echo "<a href=\"{$PHP_SELF}?action=add_primary_key&{$misc->href}&table=", urlencode($_REQUEST['table']),
+			"\">{$lang['straddpk']}</a></p>\n";
 	}
 
-	$misc->printHeader($lang['strtables'] . ' - ' . $_REQUEST['table'] . ' - ' . $lang['strconstraints']);
-	$misc->printBody();
+	$misc->printHeader($lang['strtables'] . ' - ' . $_REQUEST['table'] . ' - ' . $lang['strconstraints'],
+		"<script src=\"indexes.js\" type=\"text/javascript\"></script>");
+
+	echo "<body onload=\"init();\">";
 
 	switch ($action) {
+		case 'add_primary_key':
+			addPrimaryKey(true);
+			break;
+		case 'save_add_primary_key':
+			if (isset($_POST['cancel'])) doDefault();
+			else addPrimaryKey(false);
+			break;
 		case 'add_check':
 			addCheck(true);
 			break;
diff --git a/lang/english.php b/lang/english.php
index c4b3188f..c32d9afc 100755
--- a/lang/english.php
+++ b/lang/english.php
@@ -4,7 +4,7 @@
 	 * Language template file for WebDB. Use this to base language
 	 * files.
 	 *
-	 * $Id: english.php,v 1.64 2003/03/22 15:18:00 chriskl Exp $
+	 * $Id: english.php,v 1.65 2003/03/24 06:59:24 chriskl Exp $
 	 */
 
 	// Language and character set
@@ -77,6 +77,7 @@
 	$lang['strwhere'] = 'Where';
 	$lang['strinstead'] = 'Do Instead';
 	$lang['strwhen'] = 'When';
+	$lang['strformat'] = 'Format';
 
 	// Error handling
 	$lang['strnoframes'] = 'You need a frames-enabled browser to use this application.';
@@ -133,6 +134,9 @@
 	$lang['straddcolumn'] = 'Add column';
 	$lang['strcolumnadded'] = 'Column added.';
 	$lang['strcolumnaddedbad'] = 'Column add failed.';
+	$lang['strschemaanddata'] = 'Schema & Data';
+	$lang['strschemaonly'] = 'Schema Only';
+	$lang['strdataonly'] = 'Data Only';
 
 	// Users
 	$lang['struseradmin'] = 'User Admin';
@@ -296,6 +300,10 @@
 	$lang['strcheckneedsdefinition'] = 'Check constraint needs a definition.';
 	$lang['strcheckadded'] = 'Check constraint added.';
 	$lang['strcheckaddedbad'] = 'Failed to add check constraint.';
+	$lang['straddpk'] = 'Add Primary Key';
+	$lang['strpkneedscols'] = 'Primary key requires at least one column.';
+	$lang['strpkadded'] = 'Primary key added.';
+	$lang['strpkaddedbad'] = 'Failed to add primary key.';
 
 	// Functions
 	$lang['strfunction'] = 'Function';
diff --git a/lang/recoded/english.php b/lang/recoded/english.php
index b37be016..76799f08 100644
--- a/lang/recoded/english.php
+++ b/lang/recoded/english.php
@@ -4,7 +4,7 @@
 	 * Language template file for WebDB. Use this to base language
 	 * files.
 	 *
-	 * $Id: english.php,v 1.19 2003/03/22 15:18:01 chriskl Exp $
+	 * $Id: english.php,v 1.20 2003/03/24 06:59:24 chriskl Exp $
 	 */
 
 	// Language and character set
@@ -77,6 +77,7 @@
 	$lang['strwhere'] = 'Where';
 	$lang['strinstead'] = 'Do Instead';
 	$lang['strwhen'] = 'When';
+	$lang['strformat'] = 'Format';
 
 	// Error handling
 	$lang['strnoframes'] = 'You need a frames-enabled browser to use this application.';
@@ -133,6 +134,9 @@
 	$lang['straddcolumn'] = 'Add column';
 	$lang['strcolumnadded'] = 'Column added.';
 	$lang['strcolumnaddedbad'] = 'Column add failed.';
+	$lang['strschemaanddata'] = 'Schema &amp; Data';
+	$lang['strschemaonly'] = 'Schema Only';
+	$lang['strdataonly'] = 'Data Only';
 
 	// Users
 	$lang['struseradmin'] = 'User Admin';
@@ -296,6 +300,10 @@
 	$lang['strcheckneedsdefinition'] = 'Check constraint needs a definition.';
 	$lang['strcheckadded'] = 'Check constraint added.';
 	$lang['strcheckaddedbad'] = 'Failed to add check constraint.';
+	$lang['straddpk'] = 'Add Primary Key';
+	$lang['strpkneedscols'] = 'Primary key requires at least one column.';
+	$lang['strpkadded'] = 'Primary key added.';
+	$lang['strpkaddedbad'] = 'Failed to add primary key.';
 
 	// Functions
 	$lang['strfunction'] = 'Function';
-- 
2.39.5