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

4 files changed, 62 insertions, 37 deletions

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)
+);
+