SF.net SVN: ledger-smb:[4379] trunk/sql

[..hidden..]

Revision: 4379
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4379&view=rev
Author:   einhverfr
Date:     2012-03-05 03:25:57 +0000 (Mon, 05 Mar 2012)
Log Message:
-----------
Correcting db load issues including Roles.sql

Modified Paths:
--------------
    trunk/sql/Pg-database.sql
    trunk/sql/modules/Company.sql
    trunk/sql/modules/Employee.sql
    trunk/sql/modules/Payment.sql
    trunk/sql/modules/Roles.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2012-03-04 13:42:25 UTC (rev 4378)
+++ trunk/sql/Pg-database.sql	2012-03-05 03:25:57 UTC (rev 4379)
@@ -775,11 +775,11 @@
 SELECT SETVAL('contact_class_id_seq',17);
 
 CREATE TABLE entity_to_contact (
-  entity_id integer not null references person(id) ON DELETE CASCADE,
+  entity_id integer not null references person(entity_id) ON DELETE CASCADE,
   contact_class_id integer references contact_class(id) not null,
   contact text check(contact ~ '[[:alnum:]_]') not null,
   description text,
-  PRIMARY KEY (person_id,contact_class_id,contact));
+  PRIMARY KEY (entity_id,contact_class_id,contact));
   
 COMMENT ON TABLE entity_to_contact IS 
 $$ This table stores contact information for entities$$;
@@ -876,7 +876,7 @@
 
 CREATE TABLE employee_class (
     label text not null primary key,
-    id serial not null unique,
+    id serial not null unique
 );
 
 CREATE TABLE employee_to_ec (
@@ -944,8 +944,7 @@
    ec_id int not null references employee_class(id),
    payment_date date not null,
    created_by int references entity_employee(entity_id),
-   approved_by int references  entity_employee(entity_id),
-   primary key(id)
+   approved_by int references  entity_employee(entity_id)
 );
 
 --TODO:  Add payroll line items, approval process, registry for locale functions, etc
@@ -1726,7 +1725,7 @@
 CREATE TABLE eca_tax (
   eca_id int references entity_credit_account(id) on delete cascade,
   chart_id int REFERENCES account(id),
-  PRIMARY KEY (customer_id, chart_id)
+  PRIMARY KEY (eca_id, chart_id)
 );
 
 COMMENT ON TABLE eca_tax IS $$ Mapping customers and vendors to taxes.$$;
@@ -1833,7 +1832,7 @@
        (4, 'Fund', '0', '40'),
        (5, 'Customer', '0', '50'),
        (6, 'Vendor', '0', '60'),
-       (7, 'Lot',  0, 50);
+       (7, 'Lot',  '0', 50);
 
 CREATE TABLE bu_class_to_module (
    bu_class_id int references business_unit_class(id),

Modified: trunk/sql/modules/Company.sql
===================================================================
--- trunk/sql/modules/Company.sql	2012-03-04 13:42:25 UTC (rev 4378)
+++ trunk/sql/modules/Company.sql	2012-03-05 03:25:57 UTC (rev 4379)
@@ -304,7 +304,7 @@
 CREATE OR REPLACE FUNCTION eca__get_taxes(in_credit_id int)
 returns setof eca_tax AS
 $$
-select * from eca_tax where customer_id = $1;
+select * from eca_tax where eca_id = $1;
 $$ language sql;
 
 COMMENT ON FUNCTION eca__get_taxes(in_credit_id int) IS

Modified: trunk/sql/modules/Employee.sql
===================================================================
--- trunk/sql/modules/Employee.sql	2012-03-04 13:42:25 UTC (rev 4378)
+++ trunk/sql/modules/Employee.sql	2012-03-05 03:25:57 UTC (rev 4379)
@@ -4,9 +4,9 @@
 -- Public License v 2 or at your option any later version.
 
 -- Docstrings already added to this file.
+BEGIN;
 
 
-
 CREATE OR REPLACE FUNCTION employee__save 
 (in_entity_id int, in_start_date date, in_end_date date, in_dob date, 
 	in_role text, in_ssn text, in_sales bool, in_manager_id int, 
@@ -177,8 +177,6 @@
 --
 -- % type is pg_trgm comparison.
 
-CREATE INDEX notes_idx ON entity_note USING gist(note gist_trgm_ops);
-
 --Testing this more before replacing employee__search with it.
 -- Consequently not to be publically documented yet, --CT
 
@@ -231,4 +229,4 @@
     SELECT entity_id, $2
       FROM person WHERE id = $1;
 $$ language 'sql';
-
+COMMIT;

Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql	2012-03-04 13:42:25 UTC (rev 4378)
+++ trunk/sql/modules/Payment.sql	2012-03-05 03:25:57 UTC (rev 4379)
@@ -1304,7 +1304,7 @@
 CREATE VIEW overpayments AS
 SELECT p.id as payment_id, p.reference as payment_reference, p.payment_class, p.closed as payment_closed,
        p.payment_date, ac.chart_id, c.accno, c.description as chart_description,
-       p.department_id, abs(sum(ac.amount)) as available, cmp.legal_name, 
+       abs(sum(ac.amount)) as available, cmp.legal_name, 
        eca.id as entity_credit_id, eca.entity_id, eca.discount, eca.meta_number
 FROM payment p
 JOIN payment_links pl ON (pl.payment_id=p.id)
@@ -1316,7 +1316,7 @@
       AND (pl.type = 2 OR pl.type = 0)
       AND c.link LIKE '%overpayment%'
 GROUP BY p.id, c.accno, p.reference, p.payment_class, p.closed, p.payment_date,
-      ac.chart_id, chart_description, p.department_id,  legal_name, eca.id,
+      ac.chart_id, chart_description,legal_name, eca.id,
       eca.entity_id, eca.discount, eca.meta_number;
 
 CREATE OR REPLACE FUNCTION payment_get_open_overpayment_entities(in_account_class int)

Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql	2012-03-04 13:42:25 UTC (rev 4378)
+++ trunk/sql/modules/Roles.sql	2012-03-05 03:25:57 UTC (rev 4379)
@@ -21,7 +21,7 @@
 
 GRANT EXECUTE ON FUNCTION budget__reject(in_id int) 
 TO "lsmb_<?lsmb dbname ?>__budget_approve";
-CRATE ROLE "lsmb_<?lsmb dbname ?>__business_units_manage"
+CREATE ROLE "lsmb_<?lsmb dbname ?>__business_units_manage"
 WITH INHERIT NOLOGIN;
 
 GRANT INSERT, UPDATE, DELETE ON business_unit_class, business_unit

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.