Defaults table revision. Most COA's and System->defaults are known to be broken.

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@413 4979c152-3d1c-0410-bac9-87ea11338e46

22 files changed, 335 insertions, 185 deletions

diff --git a/Changelog b/Changelog
index 12309378..aca88128 100644
--- a/Changelog
+++ b/Changelog
@@ -4,6 +4,7 @@ Database:
 * Added script to configure Slony replication (Chris Browne)
 * Added defined primary keys to all tables (Chris T)
 * Database upgrades now use psql (Chris T)
+* Defaults table now uses a simple key->value system (Chris T)
 
 Security:
 * Added whitelist of allowed directories to file editor (Seneca)
diff --git a/LedgerSMB/AA.pm b/LedgerSMB/AA.pm
index 3dfda666..d624eaa0 100755
--- a/LedgerSMB/AA.pm
+++ b/LedgerSMB/AA.pm
@@ -213,8 +213,10 @@ sub post_transaction {
 
 
 	$query = q|
-		SELECT fxgain_accno_id, fxloss_accno_id
-		  FROM defaults|;
+		SELECT (SELECT value FROM defaults 
+		         WHERE setting_key = 'fxgain_accno_id'), 
+		       (SELECT value FROM defaults
+		         WHERE setting_key = 'fxloss_accno_id')|;
 
 	my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
 
diff --git a/LedgerSMB/AM.pm b/LedgerSMB/AM.pm
index fc2cc677..028d7183 100755
--- a/LedgerSMB/AM.pm
+++ b/LedgerSMB/AM.pm
@@ -59,9 +59,21 @@ sub get_account {
 
 	# get default accounts
 	$query = qq|
-		SELECT inventory_accno_id, income_accno_id, expense_accno_id,
-		       fxgain_accno_id, fxloss_accno_id
-		  FROM defaults|;
+		SELECT (SELECT value FROM defaults
+		         WHERE setting_key = 'inventory_accno_id')
+		       AS inventory_accno_id,
+		       (SELECT value FROM defaults
+		         WHERE setting_key = 'income_accno_id')
+		       AS income_accno_id, 
+		       (SELECT value FROM defaults
+		         WHERE setting_key = 'expense_accno_id')
+		       AS expense_accno_id,
+		       (SELECT value FROM defaults
+		         WHERE setting_key = 'fxgain_accno_id')
+		       AS fxgain_accno_id, 
+		       (SELECT value FROM defaults
+		         WHERE setting_key = 'fxloss_accno_id')
+		       AS fxloss_accno_id|;
 
 	$sth = $dbh->prepare($query);
 	$sth->execute || $form->dberror($query);
@@ -249,8 +261,9 @@ sub delete_account {
 	for (qw(income_accno_id expense_accno_id)){
 		$query = qq|
 			UPDATE parts
-			   SET $_ = (SELECT $_
-			               FROM defaults)
+			   SET $_ = (SELECT value
+			               FROM defaults
+			              WHERE setting_key = '$_')
 			 WHERE $_ = ?|;
 
 		$sth = $dbh->prepare($query);
@@ -950,7 +963,7 @@ sub recurring_transactions {
 
 	my $dbh = $form->{dbh};
 
-	my $query = qq|SELECT curr FROM defaults|;
+	my $query = qq|SELECT value FROM defaults where setting_key = 'curr'|;
 
 	my ($defaultcurrency) = $dbh->selectrow_array($query);
 	$defaultcurrency = $dbh->quote($defaultcurrency =~ s/:.*//g);
@@ -1308,8 +1321,11 @@ sub save_preferences {
 	$dbh->prepare($query)->execute(@queryargs) || $form->dberror($query);
 
 	# get default currency
-	$query = qq|SELECT curr, businessnumber
-				  FROM defaults|;
+	$query = qq|
+		SELECT value, (SELECT value FROM defaults
+		                WHERE setting_key = 'businessnumber')
+		  FROM defaults
+		 WHERE setting_key = 'curr'|;
 
 	($form->{currency}, $form->{businessnumber}) = 
 			$dbh->selectrow_array($query);
@@ -1406,10 +1422,16 @@ sub defaultaccounts {
 	my $dbh = $form->{dbh};
 
 	# get defaults from defaults table
-	my $query = qq|SELECT * FROM defaults|;
+	my $query = qq|
+		SELECT setting_key, value FROM defaults
+		 WHERE setting_key LIKE ?|;
 	my $sth = $dbh->prepare($query);
-	$sth->execute || $form->dberror($query);
+	$sth->execute('%accno_id') || $form->dberror($query);
 
+	my $ref;
+	while ($ref = $sth->fetchrow_hashref(NAME_lc)){
+		$form->{$ref->{setting_key}} = $ref->{value};
+	}
 	my $ref = $sth->fetchrow_hashref(NAME_lc);
 	for (keys %$ref) { $form->{$_} = $ref->{$_} }
 
@@ -1644,10 +1666,15 @@ sub closedto {
 	my $dbh = $form->{dbh};
 
 	my $query = qq|
-		SELECT closedto, revtrans, audittrail
-		  FROM defaults|;
+		SELECT (SELECT value FROM defaults 
+		         WHERE setting_key = 'closedto'), 
+		       (SELECT value FROM defaults
+		         WHERE setting_key = 'revtrans'), 
+		       (SELECT value FROM defaults
+		         WHERE setting_key = 'audittrail')|;
 
-	($form->{closedto}, $form->{revtrans}, $form->{audittrail}) = $dbh->selectrow_array($query);
+	($form->{closedto}, $form->{revtrans}, $form->{audittrail}) 
+		= $dbh->selectrow_array($query);
 
 	$dbh->commit;
 
@@ -1659,24 +1686,20 @@ sub closebooks {
 	my ($self, $myconfig, $form) = @_;
 
 	my $dbh = $form->{dbh};
-	my $query = qq|UPDATE defaults SET|;
-
-	if ($form->{revtrans}) {
-		$query .= qq| revtrans = '1'|;
-	} else {
-		$query .= qq| revtrans = '0'|;
-	}
-
-	$query .= qq|, closedto = |.$dbh->quote($form->{closedto});
-
-	if ($form->{audittrail}) {
-		$query .= qq|, audittrail = '1'|;
-	} else {
-		$query .= qq|, audittrail = '0'|;
+	my $query = qq|
+		UPDATE defaults SET value = ? 
+		 WHERE setting_key = ?|;
+	my $sth = $dbh->prepare($query);
+	for (qw(revtrans, closedto, audittrail)){
+		
+		if ($form->{$_}){
+			$val = 1;
+		} else {
+			$val = 0;
+		}
+		$sth->execute($val, $_);
 	}
 
-	# set close in defaults
-	$dbh->do($query) || $form->dberror($query);
 
 	if ($form->{removeaudittrail}) {
 		$query = qq|
diff --git a/LedgerSMB/CP.pm b/LedgerSMB/CP.pm
index 8e1a21f6..8cd22162 100755
--- a/LedgerSMB/CP.pm
+++ b/LedgerSMB/CP.pm
@@ -88,8 +88,12 @@ sub paymentaccounts {
 	$sth->finish;
 
 	# get currencies and closedto
-	$query = qq|SELECT curr, closedto, current_date
-				  FROM defaults|;
+	$query = qq|
+		SELECT value, (SELECT value FROM defaults
+		                WHERE setting_key = 'closedto'), 
+		       current_date
+		  FROM defaults
+		 WHERE setting_key = 'curr'|;
 
 	($form->{currencies}, $form->{closedto}, $form->{datepaid}) = $dbh->selectrow_array($query);
 
@@ -325,8 +329,11 @@ sub post_payment {
 		$form->{exchangerate} = 1;
 	}
 
-	my $query = qq|SELECT fxgain_accno_id, fxloss_accno_id
-					 FROM defaults|;
+	my $query = qq|
+		SELECT (SELECT value FROM defaults 
+		         WHERE setting_key='fxgain_accno_id'), 
+		       (SELECT value FROM defaults
+		         WHERE setting_key='fxloss_accno_id'|;
 
 	my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
 
@@ -573,8 +580,11 @@ sub post_payments {
 		$form->{exchangerate} = 1;
 	}
 
-	my $query = qq|SELECT fxgain_accno_id, fxloss_accno_id
-					 FROM defaults|;
+	my $query = qq|
+		SELECT (SELECT value FROM defaults 
+		         WHERE setting_key='fxgain_accno_id'), 
+		       (SELECT value FROM defaults
+		         WHERE setting_key='fxloss_accno_id'|;
 
 	my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
 
diff --git a/LedgerSMB/CT.pm b/LedgerSMB/CT.pm
index f3df8113..c3dcdf29 100755
--- a/LedgerSMB/CT.pm
+++ b/LedgerSMB/CT.pm
@@ -111,7 +111,7 @@ sub create_links {
 
 		($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
 
-		$query = qq|SELECT current_date FROM defaults|;
+		$query = qq|SELECT current_date|;
 		($form->{startdate}) = $dbh->selectrow_array($query);
 
 	}
@@ -188,8 +188,10 @@ sub create_links {
 	$sth->finish;
 
 	# get currencies
-	$query = qq|SELECT curr AS currencies
-				  FROM defaults|;
+	$query = qq|
+		SELECT value AS currencies
+		  FROM defaults
+		  WHERE setting_key = 'curr'|;
 
 	($form->{currencies}) = $dbh->selectrow_array($query);
 
@@ -1085,7 +1087,7 @@ sub pricelist {
 		$sth->finish;
 	}
 
-	$query = qq|SELECT curr FROM defaults|;
+	$query = qq|SELECT value FROM defaults where setting_key = 'curr'|;
 	($form->{currencies}) = $dbh->selectrow_array($query);
 
 	$query = qq|SELECT id, partsgroup 
diff --git a/LedgerSMB/Form.pm b/LedgerSMB/Form.pm
index 4e1d5aad..22dbf06f 100755
--- a/LedgerSMB/Form.pm
+++ b/LedgerSMB/Form.pm
@@ -68,7 +68,7 @@ sub new {
 	$self->{lynx} = 1 if $self->{path} =~ /lynx/i;
 
 	$self->{version} = "1.1.1";
-	$self->{dbversion} = "2.6.18";
+	$self->{dbversion} = "1.2.0";
 
 	bless $self, $type;
 
@@ -1993,9 +1993,9 @@ sub all_years {
 	$dbh = $self->{dbh};
 
 	# get years
-	my $query = qq|SELECT (SELECT MIN(transdate) FROM acc_trans),
-						  (SELECT MAX(transdate) FROM acc_trans)
-					 FROM defaults|;
+	my $query = qq|
+		SELECT (SELECT MIN(transdate) FROM acc_trans),
+		       (SELECT MAX(transdate) FROM acc_trans)|;
 
 	my ($startdate, $enddate) = $dbh->selectrow_array($query);
 
@@ -2167,30 +2167,43 @@ sub create_links {
 
 		$sth->finish;
 
-		$query = qq|SELECT d.curr AS currencies, d.closedto, d.revtrans
-					  FROM defaults d|;
+		for (qw(curr closedto revtrans)){
+			$query = qq|
+				SELECT value FROM defaults 
+				 WHERE setting_key = '$_'|;
 
-		$sth = $dbh->prepare($query);
-		$sth->execute || $self->dberror($query);
+			$sth = $dbh->prepare($query);
+			$sth->execute || $self->dberror($query);
 
-		$ref = $sth->fetchrow_hashref(NAME_lc);
-		for (keys %$ref) { $self->{$_} = $ref->{$_} }
-		$sth->finish;
+			(undef, $val) = $sth->fetchrow_array();
+			if ($_ eq 'curr'){
+				$form->{currencies} = $val;
+			} else {
+				$form->{$_} = $val;
+			}
+			$sth->finish;
+		}
 
 	} else {
 
-		# get date
-		$query = qq|
-			SELECT current_date AS transdate,
-				d.curr AS currencies, d.closedto, d.revtrans
-			FROM defaults d|;
+		for (qw(current_date curr closedto revtrans)){
+			$query = qq|
+				SELECT value FROM defaults 
+				 WHERE setting_key = '$_'|;
 
-		$sth = $dbh->prepare($query);
-		$sth->execute || $self->dberror($query);
+			$sth = $dbh->prepare($query);
+			$sth->execute || $self->dberror($query);
 
-		$ref = $sth->fetchrow_hashref(NAME_lc);
-		for (keys %$ref) { $self->{$_} = $ref->{$_} }
-		$sth->finish;
+			(undef, $val) = $sth->fetchrow_array();
+			if ($_ eq 'curr'){
+				$form->{currencies} = $val;
+			} elsif ($_ eq 'current_date'){
+				$form->{transdate} = $val;
+			} else {
+				$form->{$_} = $val;
+			}
+			$sth->finish;
+		}
 
 		if (! $self->{"$self->{vc}_id"}) {
 			$self->lastname_used($myconfig, $dbh, $vc, $module);
@@ -2279,13 +2292,11 @@ sub current_date {
 		}
 
 		$query = qq|SELECT to_date(?, ?) 
-				+ ? AS thisdate
-			FROM defaults|;
+				+ ? AS thisdate|;
 		@queryargs = ($thisdate, $dateformat, $days);
 
 	} else {
-		$query = qq|SELECT current_date AS thisdate
-					  FROM defaults|;
+		$query = qq|SELECT current_date AS thisdate|;
 		@queryargs = ();
 	}
 
@@ -2608,16 +2619,14 @@ sub save_recurring {
 		$interval{'Pg'} = 
 			"(date '$s{startdate}' + interval '$advance $s{unit}')";
 
-		$query = qq|SELECT $interval{$myconfig->{dbdriver}}
-					  FROM defaults|;
+		$query = qq|SELECT $interval{$myconfig->{dbdriver}}|;
 
 		my ($enddate) = $dbh->selectrow_array($query);
 
 		# calculate nextdate
 		$query = qq|
 			SELECT current_date - date ? AS a,
-				date ? - current_date AS b
-			FROM defaults|;
+				date ? - current_date AS b|;
 
 		$sth = $dbh->prepare($query);
 		$sth->execute($s{startdate}, $enddate);
@@ -2637,8 +2646,8 @@ sub save_recurring {
 
 				$interval{Oracle} = $interval{PgPP} = $interval{Pg};
 
-				$query = qq|SELECT $interval{$myconfig->{dbdriver}}
-							  FROM defaults|;
+
+				$query = qq|SELECT $interval{$myconfig->{dbdriver}}|;
 
 				($nextdate) = $dbh->selectrow_array($query);
 			}
@@ -2651,8 +2660,7 @@ sub save_recurring {
 
 			$nextdate = $self->{recurringnextdate};
 
-			$query = qq|SELECT '$enddate' - date '$nextdate'
-						  FROM defaults|;
+			$query = qq|SELECT '$enddate' - date '$nextdate'|;
 
 			if ($dbh->selectrow_array($query) < 0) {
 				undef $nextdate;
@@ -2751,7 +2759,11 @@ sub update_defaults {
 		$dbh = $_[3];
 	}
 
-	my $query = qq|SELECT $fld FROM defaults FOR UPDATE|;
+	my $query = qq|
+		SELECT value FROM defaults 
+		 WHERE setting_key = ? FOR UPDATE|;
+	$sth = $dbh->prepare($query);
+	$sth->execute($fld);
 	($_) = $dbh->selectrow_array($query);
 
 	$_ = "0" unless $_;
@@ -2855,11 +2867,13 @@ sub update_defaults {
 		}
 	}
 
-	$query = qq|UPDATE defaults
-				   SET $fld = ?|;
+	$query = qq|
+		UPDATE defaults
+		   SET value = ?
+		 WHERE setting_key = ?|;
 
 	$sth = $dbh->prepare($query); 
-	$sth->execute($dbvar) || $self->dberror($query);
+	$sth->execute($dbvar, $fld) || $self->dberror($query);
 
 	$dbh->commit;
 
@@ -3028,7 +3042,9 @@ sub audittrail {
 
 	if ($audittrail->{id}) {
 
-		$query = qq|SELECT audittrail FROM defaults|;
+		$query = qq|
+			SELECT value FROM defaults 
+			 WHERE setting_key = 'audittrail'|;
 
 		if ($dbh->selectrow_array($query)) {
 
@@ -3112,7 +3128,7 @@ sub audittrail {
 
 	} else {
 
-		$query = qq|SELECT current_timestamp FROM defaults|;
+		$query = qq|SELECT current_timestamp|;
 		my ($timestamp) = $dbh->selectrow_array($query);
 
 		$rv = "$audittrail->{tablename}|$audittrail->{reference}|$audittrail->{formname}|$audittrail->{action}|$timestamp|";
diff --git a/LedgerSMB/HR.pm b/LedgerSMB/HR.pm
index 1abf3e6b..ee5a2c32 100755
--- a/LedgerSMB/HR.pm
+++ b/LedgerSMB/HR.pm
@@ -93,7 +93,7 @@ if ($form->{deductions}) {
     
   } else {
 
-    $query = qq|SELECT current_date FROM defaults|;
+    $query = qq|SELECT current_date|;
     ($form->{startdate}) = $dbh->selectrow_array($query);
   
   }
diff --git a/LedgerSMB/IC.pm b/LedgerSMB/IC.pm
index c5ff74f6..f96b1294 100755
--- a/LedgerSMB/IC.pm
+++ b/LedgerSMB/IC.pm
@@ -953,7 +953,7 @@ sub all_parts {
   my @a = qw(partnumber description);
   my $sortorder = $form->sort_order(\@a, \%ordinal);
 
-  my $query = qq|SELECT curr FROM defaults|;
+  my $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
   my ($curr) = $dbh->selectrow_array($query);
   $curr =~ s/:.*//;
   
@@ -1678,20 +1678,28 @@ sub create_links {
 
 
   if ($form->{id}) {
-    $query = qq|SELECT weightunit, curr AS currencies
-                FROM defaults|;
-    ($form->{weightunit}, $form->{currencies}) = $dbh->selectrow_array($query);
+    $query = qq|SELECT value FROM defaults WHERE setting_key = 'weightunit'|;
+    ($form->{weightunit}) = $dbh->selectrow_array($query);
+    $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
+    ($form->{currencies}) = $dbh->selectrow_array($query);
 
   } else {
-    $query = qq|SELECT d.weightunit, current_date AS priceupdate,
-                d.curr AS currencies,
+    # FIXME left joins not working
+    $query = qq|SELECT (SELECT value FROM defaults 
+		WHERE setting_key = 'weightunit') AS weightunit, 
+		current_date AS priceupdate,
+                (SELECT value FROM defaults WHERE setting_key = 'curr') 
+		AS currencies,
                 c1.accno AS inventory_accno, c1.description AS inventory_description,
 		c2.accno AS income_accno, c2.description AS income_description,
 		c3.accno AS expense_accno, c3.description AS expense_description
-	        FROM defaults d
-		LEFT JOIN chart c1 ON (d.inventory_accno_id = c1.id)
-		LEFT JOIN chart c2 ON (d.income_accno_id = c2.id)
-		LEFT JOIN chart c3 ON (d.expense_accno_id = c3.id)|;
+		FROM chart c1, chart c2, chartc3 
+		WHERE c1.id IN (SELECT value FROM defaults 
+			WHERE setting_key = 'inventory_accno_id')
+		AND c2.id IN (SELECT value FROM defaults
+			WHERE setting_key = 'income_accno_id')
+		AND c3.id IN (SELECT value FROM defaults
+			WHERE setting_key = 'expense_accno_id')|;
     $sth = $dbh->prepare($query);
     $sth->execute || $form->dberror($query);
 
diff --git a/LedgerSMB/IR.pm b/LedgerSMB/IR.pm
index 60c8abe3..2f794824 100755
--- a/LedgerSMB/IR.pm
+++ b/LedgerSMB/IR.pm
@@ -72,7 +72,13 @@ sub post_invoice {
 	($null, $form->{department_id}) = split(/--/, $form->{department});
 	$form->{department_id} *= 1;
  
-	$query = qq|SELECT fxgain_accno_id, fxloss_accno_id FROM defaults d|;
+	$query = qq|
+		SELECT (SELECT value FROM defaults
+		         WHERE setting_key = 'fxgain_accno_id') 
+		       AS fxgain_accno_id, 
+		       (SELECT value FROM defaults
+		         WHERE setting_key = 'fxloss_accno_id')
+		       AS fxloss_accno_id|;
 	my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
   
 	$query = qq|
@@ -976,41 +982,71 @@ sub retrieve_invoice {
 	if ($form->{id}) {
 		# get default accounts and last invoice number
 		$query = qq|
-			SELECT (SELECT c.accno FROM chart c
-			         WHERE d.inventory_accno_id = c.id) 
+			SELECT (select c.accno FROM chart c 
+			         WHERE c.id = (SELECT value FROM defaults 
+			                        WHERE setting_key = 
+			                              'inventory_accno_id'))
 			       AS inventory_accno,
+
 			       (SELECT c.accno FROM chart c
-				 WHERE d.income_accno_id = c.id) 
+				 WHERE c.id = (SELECT value FROM defaults
+			                        WHERE setting_key =
+			                              'income_accno_id'))
 			       AS income_accno,
+
 			       (SELECT c.accno FROM chart c
-			         WHERE d.expense_accno_id = c.id) 
+			         WHERE c.id = (SELECT value FROM defaults
+			                        WHERE setting_key =
+			                              'expense_accno_id'))
 			       AS expense_accno,
+
 			       (SELECT c.accno FROM chart c
-			         WHERE d.fxgain_accno_id = c.id) 
+			         WHERE c.id = (SELECT value FROM defaults
+			                        WHERE setting_key =
+			                              'fxgain_accno_id'))
 			       AS fxgain_accno,
+
 			       (SELECT c.accno FROM chart c
-			         WHERE d.fxloss_accno_id = c.id) 
-			       AS fxloss_accno, d.curr AS currencies
-	 		  FROM defaults d|;
+			         WHERE c.id = (SELECT value FROM defaults
+			                        WHERE setting_key =
+			                              'fxloss_accno_id'))
+			       AS fxloss_accno, 
+			       (SELECT value FROM defaults
+			         WHERE setting_key = 'curr') AS currencies|;
 	} else {
 		$query = qq|
-			SELECT (SELECT c.accno FROM chart c
-			         WHERE d.inventory_accno_id = c.id) 
+			SELECT (select c.accno FROM chart c 
+			         WHERE c.id = (SELECT value FROM defaults 
+			                        WHERE setting_key = 
+			                              'inventory_accno_id'))
 			       AS inventory_accno,
+
 			       (SELECT c.accno FROM chart c
-			         WHERE d.income_accno_id = c.id) 
+				 WHERE c.id = (SELECT value FROM defaults
+			                        WHERE setting_key =
+			                              'income_accno_id'))
 			       AS income_accno,
+
 			       (SELECT c.accno FROM chart c
-			         WHERE d.expense_accno_id = c.id) 
+			         WHERE c.id = (SELECT value FROM defaults
+			                        WHERE setting_key =
+			                              'expense_accno_id'))
 			       AS expense_accno,
+
 			       (SELECT c.accno FROM chart c
-			         WHERE d.fxgain_accno_id = c.id) 
+			         WHERE c.id = (SELECT value FROM defaults
+			                        WHERE setting_key =
+			                              'fxgain_accno_id'))
 			       AS fxgain_accno,
+
 			       (SELECT c.accno FROM chart c
-			         WHERE d.fxloss_accno_id = c.id) 
-			       AS fxloss_accno, d.curr AS currencies,
-			       current_date AS transdate
-	 		  FROM defaults d|;
+			         WHERE c.id = (SELECT value FROM defaults
+			                        WHERE setting_key =
+			                              'fxloss_accno_id'))
+			       AS fxloss_accno, 
+			       (SELECT value FROM defaults
+			         WHERE setting_key = 'curr') AS currencies,
+			       current_date AS transdate|;
 	}
 	my $sth = $dbh->prepare($query);
 	$sth->execute || $form->dberror($query);
@@ -1259,7 +1295,9 @@ sub exchangerate_defaults {
 	my $var;
   
 	# get default currencies
-	my $query = qq|SELECT substr(curr,1,3), curr FROM defaults|;
+	my $query = qq|
+		SELECT substr(value,1,3), value FROM defaults
+		 WHERE setting_key = 'curr'|;
 	my $eth = $dbh->prepare($query) || $form->dberror($query);
 	$eth->execute;
 	($form->{defaultcurrency}, $form->{currencies}) = $eth->fetchrow_array;
diff --git a/LedgerSMB/IS.pm b/LedgerSMB/IS.pm
index 74b35352..05b4dd06 100755
--- a/LedgerSMB/IS.pm
+++ b/LedgerSMB/IS.pm
@@ -48,8 +48,9 @@ sub invoice_details {
 
 	my $query = qq|
 		SELECT ?::date - ?::date
-                       AS terms, weightunit
-		  FROM defaults|;
+                       AS terms, value
+		  FROM defaults
+		 WHERE setting_key = 'weightunit'/|;
 	my $sth = $dbh->prepare($query);
 	$sth->execute($form->{duedate}, $form->{transdate})
 		|| $form->dberror($query);
@@ -817,7 +818,13 @@ sub post_invoice {
 	($null, $form->{department_id}) = split(/--/, $form->{department});
 	$form->{department_id} *= 1;
 
-	$query = qq|SELECT fxgain_accno_id, fxloss_accno_id FROM defaults|;
+	$query = qq|
+		SELECT (SELECT value FROM defaults 
+		         WHERE setting_key = fxgain_accno_id) 
+		       AS fxgain_accno_id, 
+		       (SELECT value FROM defaults
+		         WHERE setting_key = fxloss_accno_id) 
+		       AS fxloss_accno_id|;
 	my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);
 
 	$query = qq|
@@ -1715,11 +1722,14 @@ sub retrieve_invoice {
 
 	if ($form->{id}) {
 		# get default accounts and last invoice number
-		$query = qq|SELECT d.curr AS currencies FROM defaults d|;
+		$query = qq|
+			SELECT value AS currencies FROM defaults
+			 WHERE setting_key = 'curr'|;
 	} else {
 		$query = qq|
-			SELECT d.curr AS currencies, current_date AS transdate
-			  FROM defaults d|;
+			SELECT value AS currencies, current_date AS transdate
+			  FROM defaults
+			 WHERE setting_key = 'curr'|;
 	}
 	my $sth = $dbh->prepare($query);
 	$sth->execute || $form->dberror($query);
@@ -1979,7 +1989,9 @@ sub exchangerate_defaults {
 	my $var;
   
 	# get default currencies
-	my $query = qq|SELECT substr(curr,1,3), curr FROM defaults|;
+	my $query = qq|
+		SELECT substr(value,1,3), value FROM defaults
+		 WHERE setting_key = 'curr'|;
 	my $eth = $dbh->prepare($query) || $form->dberror($query);
 	$eth->execute;
 	($form->{defaultcurrency}, $form->{currencies}) = $eth->fetchrow_array;
diff --git a/LedgerSMB/JC.pm b/LedgerSMB/JC.pm
index c8974818..fcac1b1e 100755
--- a/LedgerSMB/JC.pm
+++ b/LedgerSMB/JC.pm
@@ -45,7 +45,7 @@ sub get_jcitems {
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
-  my $query = qq|SELECT current_date FROM defaults|;
+  my $query = qq|SELECT current_date|;
   ($form->{transdate}) = $dbh->selectrow_array($query);
 
   ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
diff --git a/LedgerSMB/OE.pm b/LedgerSMB/OE.pm
index 1a51a6cf..0992b00e 100755
--- a/LedgerSMB/OE.pm
+++ b/LedgerSMB/OE.pm
@@ -776,7 +776,9 @@ sub retrieve {
 	my $var;
 	my $ref;
 
-	$query = qq|SELECT curr, current_date FROM defaults|;
+	$query = qq|
+		SELECT value, current_date FROM defaults
+		 WHERE setting_key = 'curr'|;
 	($form->{currencies}, $form->{transdate}) = 
 		$dbh->selectrow_array($query);
   
@@ -952,7 +954,9 @@ sub exchangerate_defaults {
 	my $buysell = ($form->{vc} eq "customer") ? "buy" : "sell";
   
 	# get default currencies
-	my $query = qq|SELECT substr(curr,1,3), curr FROM defaults|;
+	my $query = qq|
+		SELECT substr(value,1,3), value FROM defaults
+		 WHERE setting_key = 'curr'|;
 	($form->{defaultcurrency}, $form->{currencies}) 
 		= $dbh->selectrow_array($query);
 
@@ -1568,7 +1572,9 @@ sub order_details {
 
 	$form->format_string(qw(text_amount text_decimal));
 
-	$query = qq|SELECT weightunit FROM defaults|;
+	$query = qq|
+		SELECT value FROM defaults 
+		 WHERE setting_key = 'weightunit'|;
 	($form->{weightunit}) = $dbh->selectrow_array($query);
   
 	$dbh->commit;
@@ -2193,7 +2199,7 @@ sub get_soparts {
 
 	}
 
-	$query = qq|SELECT current_date FROM defaults|;
+	$query = qq|SELECT current_date|;
 	($form->{transdate}) = $dbh->selectrow_array($query);
   
 	# foreign exchange rates
diff --git a/LedgerSMB/PE.pm b/LedgerSMB/PE.pm
index 604e62e7..02b7b59d 100755
--- a/LedgerSMB/PE.pm
+++ b/LedgerSMB/PE.pm
@@ -283,7 +283,7 @@ sub list_stock {
   }
   $sth->finish;
 
-  $query = qq|SELECT current_date FROM defaults|;
+  $query = qq|SELECT current_date|;
   ($form->{stockingdate}) = $dbh->selectrow_array($query) if !$form->{stockingdate};
   
   $dbh->disconnect;
@@ -378,8 +378,7 @@ sub get_job {
   my $ref;
 
   if ($form->{id}) {
-    $query = qq|SELECT weightunit
-		FROM defaults|;
+    $query = qq|SELECT value FROM defaults WHERE setting_key = 'weightunit'|;
     ($form->{weightunit}) = $dbh->selectrow_array($query);
 
     $query = qq|SELECT pr.*,
@@ -396,7 +395,9 @@ sub get_job {
 		LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
 		WHERE pr.id = $form->{id}|;
   } else {
-    $query = qq|SELECT weightunit, current_date AS startdate FROM defaults|;
+    $query = qq|
+		SELECT value, current_date AS startdate FROM defaults
+		 WHERE setting_key = 'weightunit'|;
   }
 
   $sth = $dbh->prepare($query);
@@ -491,7 +492,7 @@ sub get_customer {
   my $ref;
 
   if (! $form->{startdate}) {
-    $query = qq|SELECT current_date FROM defaults|;
+    $query = qq|SELECT current_date|;
     ($form->{startdate}) = $dbh->selectrow_array($query);
   }
   
@@ -654,7 +655,7 @@ sub stock_assembly {
   my $rvh = $dbh->prepare($query) || $form->dberror($query);
 
   if (! $form->{stockingdate}) {
-    $query = qq|SELECT current_date FROM defaults|;
+    $query = qq|SELECT current_date|;
     ($form->{stockingdate}) = $dbh->selectrow_array($query);
   }
   
@@ -1375,7 +1376,7 @@ sub project_sales_order {
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
-  my $query = qq|SELECT current_date FROM defaults|;
+  my $query = qq|SELECT current_date|;
   my ($transdate) = $dbh->selectrow_array($query);
   
   $form->all_years($myconfig, $dbh);
@@ -1468,8 +1469,7 @@ sub get_jcitems {
 
   $sth->finish;
 
-  $query = qq|SELECT curr
-              FROM defaults|;
+  $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|;
   ($form->{currency}) = $dbh->selectrow_array($query);
   $form->{currency} =~ s/:.*//;
   $form->{defaultcurrency} = $form->{currency};
diff --git a/LedgerSMB/RP.pm b/LedgerSMB/RP.pm
index b1cd54d7..fa036bc8 100755
--- a/LedgerSMB/RP.pm
+++ b/LedgerSMB/RP.pm
@@ -572,7 +572,7 @@ sub get_accounts {
   $sth->finish;
 
   if ($form->{method} eq 'cash' && !$todate) {
-    ($todate) = $dbh->selectrow_array(qq|SELECT current_date FROM defaults|);
+    ($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
   }
 
   if ($fromdate) {
@@ -1353,13 +1353,13 @@ sub aging {
   my $dbh = $form->dbconnect($myconfig);
   my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir';
 
-  my $query = qq|SELECT curr FROM defaults|;
+  my $query = qq|SELECT value FROM defaults WHERE settings_key = 'curr'|;
   ($form->{currencies}) = $dbh->selectrow_array($query);
   
   ($null, $form->{todate}) = $form->from_to($form->{year}, $form->{month}) if $form->{year} && $form->{month};
   
   if (! $form->{todate}) {
-    $query = qq|SELECT current_date FROM defaults|;
+    $query = qq|SELECT current_date|;
     ($form->{todate}) = $dbh->selectrow_array($query);
   }
     
@@ -1721,7 +1721,7 @@ sub tax_report {
 
     my $todate = $form->{todate};
     if (! $todate) {
-      ($todate) = $dbh->selectrow_array(qq|SELECT current_date FROM defaults|);
+      ($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
     }
     
     $cashwhere = qq|
diff --git a/LedgerSMB/Session/DB.pm b/LedgerSMB/Session/DB.pm
index 0c8dda45..7b59a718 100755
--- a/LedgerSMB/Session/DB.pm
+++ b/LedgerSMB/Session/DB.pm
@@ -52,7 +52,8 @@ sub session_check {
 		$timeout = "$myconfig{timeout} seconds";
 	}
 
-	$checkQuery->execute($sessionid, $token, $timeout) || $form->dberror('Looking for session: ');
+	$checkQuery->execute($sessionid, $token, $timeout) 
+		|| $form->dberror('Looking for session: ');
 	my $sessionValid = $checkQuery->rows;
 
 	if($sessionValid){
diff --git a/LedgerSMB/User.pm b/LedgerSMB/User.pm
index 166a3d3c..a16cf7d5 100755
--- a/LedgerSMB/User.pm
+++ b/LedgerSMB/User.pm
@@ -131,7 +131,9 @@ sub login {
 				or $self->error($DBI::errstr);
 
 		# we got a connection, check the version
-		my $query = qq|SELECT version FROM defaults|;
+		my $query = qq|
+			SELECT value FROM defaults 
+			 WHERE setting_key = 'version'|;
 		my $sth = $dbh->prepare($query);
 		$sth->execute || $form->dberror($query);
 
@@ -228,13 +230,8 @@ sub dbconnect_vars {
 
 	$form->{dboptions} = $dboptions{$form->{dbdriver}}{$form->{dateformat}};
 
-	if ($form->{dbdriver} =~ /Pg/) {
-		$form->{dbconnect} = "dbi:$form->{dbdriver}:dbname=$db";
-	}
+	$form->{dbconnect} = "dbi:$form->{dbdriver}:dbname=$db";
 
-	if ($form->{dbdriver} eq 'Oracle') {
-		$form->{dbconnect} = "dbi:Oracle:sid=$form->{sid}";
-	}
 
 	if ($form->{dbhost}) {
 		$form->{dbconnect} .= ";host=$form->{dbhost}";
@@ -411,7 +408,12 @@ sub process_query {
 	return unless (-f $filename);
   
 	open(FH, "$filename") or $form->error("$filename : $!\n");
+	$ENV{PGPASSWORD} = $form->{dbpasswd};
+	$ENV{PGUSER} = $form->{dbuser};
+	$ENV{PGDATABASE} = $form->{db};
+	
 	open(PSQL, "| psql") or $form->error("psql : $! \n");
+	print PSQL "\\o spool/log \n";
 	while (<FH>){
 		print PSQL $_;
 	}
@@ -516,7 +518,9 @@ sub dbneedsupdate {
 			$sth->execute || $form->dberror($query);
 
 			if ($sth->fetchrow_array) {
-				$query = qq|SELECT version FROM defaults|;
+				$query = qq|
+					SELECT value FROM defaults
+					 WHERE setting_key = 'version'|;
 				my $sth = $dbh->prepare($query);
 				$sth->execute;
 	
@@ -574,7 +578,9 @@ sub dbupdate {
 				or $form->dberror;
 
 		# check version
-		$query = qq|SELECT version FROM defaults|;
+		$query = qq|
+			SELECT value FROM defaults
+			 WHERE setting_key = 'version'|;
 		my $sth = $dbh->prepare($query);
 		# no error check, let it fall through
 		$sth->execute;
diff --git a/README.svn-status b/README.svn-status
index bfdb8a15..3f2886b1 100644
--- a/README.svn-status
+++ b/README.svn-status
@@ -1,4 +1,3 @@
-Dataset creation is temporarily broken because of changes to the defaults table structure that are not found in the chart.sql files or the application.  Will 
-fix ASAP.
+Dataset creation is now fixed (and mandatory).  Only Default and Canadian English (General) COA's currently work.
 
 Chris T
diff --git a/bin/admin.pl b/bin/admin.pl
index 74f3f6cf..4913b9b3 100755
--- a/bin/admin.pl
+++ b/bin/admin.pl
@@ -880,7 +880,8 @@ sub delete {
 
 	$form->{templates} = ($form->{templates}) ? "${LedgerSMB::Sysconfig::templates}/$form->{templates}" : "$templates/$form->{login}";
 
-	$form->error($locale->text("[_1] locked!", ${LedgerSMB::Sysconfig::memberfile}) if (-f ${memberfile}.LCK);
+	$form->error($locale->text("[_1] locked!", 
+		${LedgerSMB::Sysconfig::memberfile})) if (-f ${memberfile}.LCK);
 
 	open(FH, ">${memberfile}.LCK") or $form->error("${memberfile}.LCK : $!");
 	close(FH);
diff --git a/sql/Canada-English_General-chart.sql b/sql/Canada-English_General-chart.sql
index d17caa94..99946c9c 100755
--- a/sql/Canada-English_General-chart.sql
+++ b/sql/Canada-English_General-chart.sql
@@ -66,5 +66,17 @@ insert into chart (accno,description,charttype,gifi_accno,category,link) values
 insert into tax (chart_id,rate) values ((select id from chart where accno = '2310'),0.06);
 insert into tax (chart_id,rate) values ((select id from chart where accno = '2320'),0.08);
 --
-update defaults set inventory_accno_id = (select id from chart where accno = '1520'), income_accno_id = (select id from chart where accno = '4020'), expense_accno_id = (select id from chart where accno = '5010'), fxgain_accno_id = (select id from chart where accno = '4450'), fxloss_accno_id = (select id from chart where accno = '4450'), curr = 'CAD:USD:EUR', weightunit = 'kg';
+
+INSERT INTO defaults (setting_key, value) values ('inventory_accno_id',  
+	(select id from chart where accno = '1520'));
+INSERT INTO defaults (setting_key, value) values ('income_accno_id',
+	 (select id from chart where accno = '4020')); 
+INSERT INTO defaults (setting_key, value) values ('expense_accno_id', 
+	(select id from chart where accno = '5010')); 
+INSERT INTO defaults (setting_key, value) values ('fxgain_accno_id', 
+	(select id from chart where accno = '4450'));
+INSERT INTO defaults (setting_key, value) values ('fxloss_accno_id',
+ 	(select id from chart where accno = '4450')); 
+INSERT INTO defaults (setting_key, value) values ('curr', 'CAD:USD:EUR');
+INSERT INTO defaults (setting_key, value) values ('weightunit', 'kg');
 --
diff --git a/sql/Default-chart.sql b/sql/Default-chart.sql
index 3d222463..c0f103b6 100755
--- a/sql/Default-chart.sql
+++ b/sql/Default-chart.sql
@@ -73,5 +73,17 @@ insert into tax (chart_id,rate) values ((select id from chart where accno = '231
 insert into tax (chart_id,rate) values ((select id from chart where accno = '2320'),0.14);
 insert into tax (chart_id,rate) values ((select id from chart where accno = '2330'),0.3);
 --
-update defaults set inventory_accno_id = (select id from chart where accno = '1520'), income_accno_id = (select id from chart where accno = '4020'), expense_accno_id = (select id from chart where accno = '5010'), fxgain_accno_id = (select id from chart where accno = '4450'), fxloss_accno_id = (select id from chart where accno = '5810'), curr = 'USD:CAD:EUR', weightunit = 'kg';
---
+insert into  defaults (setting_key, value) 
+VALUES ('inventory_accno_id', (select id from chart where accno = '1520')); 
+INSERT INTO defaults (setting_key, value) 
+VALUES ('income_accno_id', (select id from chart where accno = '4020')); 
+INSERT INTO defaults (setting_key, value) 
+VALUES ('expense_accno_id', (select id from chart where accno = '5010')); 
+INSERT INTO defaults (setting_key, value) 
+VALUES ('fxgain_accno_id', (select id from chart where accno = '4450'));
+INSERT INTO defaults (setting_key, value) 
+VALUES ('fxloss_accno_id', (select id from chart where accno = '5810'));
+INSERT INTO defaults (setting_key, value) 
+VALUES ('curr', 'USD:CAD:EUR');
+INSERT INTO defaults (setting_key, value) 
+VALUES ('weightunit', 'kg');
diff --git a/sql/Pg-functions.sql b/sql/Pg-functions.sql
index 26d23473..b42e2083 100755
--- a/sql/Pg-functions.sql
+++ b/sql/Pg-functions.sql
@@ -1,3 +1,4 @@
+CREATE LANGUAGE plpgsql;
 --
 CREATE FUNCTION del_yearend() RETURNS OPAQUE AS '
 begin
diff --git a/sql/Pg-tables.sql b/sql/Pg-tables.sql
index d490110f..92dfbf26 100755
--- a/sql/Pg-tables.sql
+++ b/sql/Pg-tables.sql
@@ -59,27 +59,27 @@ CREATE TABLE defaults (
   expense_accno_id int,
   fxgain_accno_id int,
   fxloss_accno_id int,
-  sinumber text,
-  sonumber text,
-  yearend varchar(5),
-  weightunit varchar(5),
-  businessnumber text,
-  version varchar(8) PRIMARY KEY,
-  curr text,
-  closedto date,
-  revtrans bool DEFAULT 't',
-  ponumber text,
-  sqnumber text,
-  rfqnumber text,
-  audittrail bool default 'f',
-  vinumber text,
-  employeenumber text,
-  partnumber text,
-  customernumber text,
-  vendornumber text,
-  glnumber text,
-  projectnumber text
-);
+*/
+\COPY defaults FROM stdin WITH DELIMITER |
+sinumber|1
+sonumber|1
+yearend|1
+businessnumber|1
+version|1.2.0
+closedto|\N
+revtrans|1
+ponumber|1
+sqnumber|1
+rfqnumber|1
+audittrail|0
+vinumber|1
+employeenumber|1
+partnumber|1
+customernumber|1
+vendornumber|1
+glnumber|1
+projectnumber|1
+\.
 -- */
 CREATE TABLE acc_trans (
   trans_id int,
@@ -565,17 +565,6 @@ CREATE TABLE jcitems (
   notes text
 );
 
--- Session tracking table
-
-
-CREATE TABLE session(
-session_id serial PRIMARY KEY,
-sl_login VARCHAR(50),
-token VARCHAR(32) CHECK(length(token) = 32),
-last_used TIMESTAMP default now(),
-users_id INTEGER NOT NULL references users(id)
-);
-
 
 insert into transactions (id, table_name) SELECT id, 'ap' FROM ap;
 
@@ -708,7 +697,6 @@ field_id SERIAL PRIMARY KEY,
 table_id INT REFERENCES custom_table_catalog,
 field_name TEXT
 );
-INSERT INTO defaults (version) VALUES ('2.6.18');
 
 INSERT INTO taxmodule (
   taxmodule_id, taxmodulename
@@ -718,7 +706,8 @@ INSERT INTO taxmodule (
 
 -- USERS stuff --
 CREATE TABLE users (id serial UNIQUE, username varchar(30) primary key);
-COMMENT ON TABLE users IS 'username is the actual primary key here because we don't want duplicate users';
+COMMENT ON TABLE users IS 
+$$username is the actual primary key here because we don't want duplicate users$$;
 CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred,
                         acs text,
                         address text,
@@ -778,3 +767,14 @@ CREATE FUNCTION update_user(int4,text) RETURNS int4 AS $$
 COMMENT ON FUNCTION update_user(int4,text) IS $$ Takes int4 which is users.id and text which is username. Will update username based on id. Username is unique $$;
 
 
+-- Session tracking table
+
+
+CREATE TABLE session(
+session_id serial PRIMARY KEY,
+sl_login VARCHAR(50),
+token VARCHAR(32) CHECK(length(token) = 32),
+last_used TIMESTAMP default now(),
+users_id INTEGER  -- NOT NULL references users(id)
+);
+