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

[..hidden..]

Revision: 3481
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3481&view=rev
Author:   einhverfr
Date:     2011-07-10 08:57:16 +0000 (Sun, 10 Jul 2011)

Log Message:
-----------
More pkey fixes to sql upgrade scripts.  Adding file roles.  All tests now pass.

Modified Paths:
--------------
    trunk/sql/modules/Roles.sql

Added Paths:
-----------
    trunk/sql/upgrade/3480-pkeys-2.sql

Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql	2011-07-09 15:51:17 UTC (rev 3480)
+++ trunk/sql/modules/Roles.sql	2011-07-10 08:57:16 UTC (rev 3481)
@@ -1,5 +1,31 @@
 GRANT ALL ON SCHEMA public TO public; -- required for Pg 8.2
+
+-- Basic file attachments
+
+CREATE ROLE "lsmb_<?lsmb dbname ?>__file_read"
+WITH INHERIT NOLOGIN;
+
+GRANT SELECT ON file_base, file_secondary_attachment 
+      TO "lsmb_<?lsmb dbname ?>__file_read";
+
+CREATE ROLE "lsmb_<?lsmb dbname ?>__file_attach_tx"
+WITH INHERIT NOLOGIN;
+
+GRANT INSERT, UPDATE ON file_transaction, file_order_to_tx TO
+ "lsmb_<?lsmb dbname ?>__file_attach_tx";
+
+CREATE ROLE "lsmb_<?lsmb dbname ?>__file_attach_order"
+WITH INHERIT NOLOGIN;
+
+GRANT INSERT, UPDATE 
+      ON file_order, 
+         file_order_to_order,
+         file_tx_to_order
+      TO "lsmb_<?lsmb dbname ?>__file_attach_tx";
+
+
 -- Contacts
+
 CREATE ROLE "lsmb_<?lsmb dbname ?>__contact_read"
 WITH INHERIT NOLOGIN;
 
@@ -196,7 +222,10 @@
 
 INSERT INTO menu_acl (node_id, acl_type, role_name)
 values (4, 'allow', 'lsmb_<?lsmb dbname ?>__ar_transaction_create');
+INSERT INTO menu_acl (node_id, acl_type, role_name) 
+values (198, 'allow', 'lsmb_<?lsmb dbname ?>__ar_transaction_create_voucher');
 
+
 CREATE ROLE "lsmb_<?lsmb dbname ?>__ar_invoice_create"
 WITH INHERIT NOLOGIN
 IN ROLE "lsmb_<?lsmb dbname ?>__ar_transaction_create";
@@ -231,7 +260,8 @@
 
 CREATE ROLE "lsmb_<?lsmb dbname ?>__ar_transaction_list"
 WITH INHERIT NOLOGIN
-IN ROLE "lsmb_<?lsmb dbname ?>__contact_read";
+IN ROLE "lsmb_<?lsmb dbname ?>__contact_read",
+"lsmb_<?lsmb dbname ?>__file_read";
 
 GRANT SELECT ON ar TO "lsmb_<?lsmb dbname ?>__ar_transaction_list";
 GRANT SELECT ON acc_trans TO "lsmb_<?lsmb dbname ?>__ar_transaction_list";
@@ -269,7 +299,8 @@
 WITH INHERIT NOLOGIN
 IN ROLE "lsmb_<?lsmb dbname ?>__ar_transaction_create",
 "lsmb_<?lsmb dbname ?>__ar_invoice_create",
-"lsmb_<?lsmb dbname ?>__ar_transaction_list";
+"lsmb_<?lsmb dbname ?>__ar_transaction_list",
+"lsmb_<?lsmb dbname ?>__file_attach_tx";
 
 CREATE ROLE "lsmb_<?lsmb dbname ?>__sales_order_create"
 WITH INHERIT NOLOGIN
@@ -307,7 +338,8 @@
 
 CREATE ROLE "lsmb_<?lsmb dbname ?>__sales_order_list"
 WITH INHERIT NOLOGIN
-IN ROLE "lsmb_<?lsmb dbname ?>__contact_read";
+IN ROLE "lsmb_<?lsmb dbname ?>__contact_read",
+"lsmb_<?lsmb dbname ?>__file_read";
 
 GRANT SELECT ON oe TO "lsmb_<?lsmb dbname ?>__sales_order_list";
 GRANT SELECT ON orderitems TO "lsmb_<?lsmb dbname ?>__sales_order_list";
@@ -322,7 +354,8 @@
 
 CREATE ROLE "lsmb_<?lsmb dbname ?>__sales_quotation_list"
 WITH INHERIT NOLOGIN
-IN ROLE "lsmb_<?lsmb dbname ?>__contact_read";
+IN ROLE "lsmb_<?lsmb dbname ?>__contact_read",
+"lsmb_<?lsmb dbname ?>__file_read";
 
 GRANT SELECT ON oe TO "lsmb_<?lsmb dbname ?>__sales_quotation_list";
 GRANT SELECT ON orderitems TO "lsmb_<?lsmb dbname ?>__sales_quotation_list";
@@ -343,7 +376,8 @@
 "lsmb_<?lsmb dbname ?>__sales_order_create",
 "lsmb_<?lsmb dbname ?>__sales_quotation_create",
 "lsmb_<?lsmb dbname ?>__sales_order_list",
-"lsmb_<?lsmb dbname ?>__sales_quotation_list";
+"lsmb_<?lsmb dbname ?>__sales_quotation_list",
+"lsmb_<?lsmb dbname ?>__file_attach_tx";
 
 -- AP
 CREATE ROLE "lsmb_<?lsmb dbname ?>__ap_transaction_create"
@@ -373,7 +407,8 @@
 GRANT INSERT ON acc_trans TO "lsmb_<?lsmb dbname ?>__ap_transaction_create_voucher";
 GRANT ALL ON acc_trans_entry_id_seq TO "lsmb_<?lsmb dbname ?>__ap_transaction_create_voucher";
 
--- TODO add Menu ACLs
+INSERT INTO menu_acl (node_id, acl_type, role_name) 
+values (199, 'allow', 'lsmb_<?lsmb dbname ?>__ap_transaction_create_voucher');
 
 CREATE ROLE "lsmb_<?lsmb dbname ?>__ap_invoice_create"
 WITH INHERIT NOLOGIN
@@ -406,7 +441,8 @@
 
 CREATE ROLE "lsmb_<?lsmb dbname ?>__ap_transaction_list"
 WITH INHERIT NOLOGIN
-IN ROLE "lsmb_<?lsmb dbname ?>__contact_read";
+IN ROLE "lsmb_<?lsmb dbname ?>__contact_read",
+"lsmb_<?lsmb dbname ?>__file_read";
 
 GRANT SELECT ON ap TO "lsmb_<?lsmb dbname ?>__ap_transaction_list";
 GRANT SELECT ON acc_trans TO "lsmb_<?lsmb dbname ?>__ap_transaction_list";
@@ -1628,6 +1664,7 @@
 GRANT SELECT ON parts_translation,  project_translation TO public;
 GRANT SELECT ON asset_report_class, asset_rl_to_disposal_method,
                 asset_disposal_method TO PUBLIC;
+GRANT SELECT ON mime_type, file_class TO PUBLIC;
 
 GRANT EXECUTE ON FUNCTION user__get_all_users() TO public;
 

Added: trunk/sql/upgrade/3480-pkeys-2.sql
===================================================================
--- trunk/sql/upgrade/3480-pkeys-2.sql	                        (rev 0)
+++ trunk/sql/upgrade/3480-pkeys-2.sql	2011-07-10 08:57:16 UTC (rev 3481)
@@ -0,0 +1,12 @@
+ALTER TABLE file_secondary_attachment 
+    ADD PRIMARY KEY(file_id, source_class, dest_class, ref_key);
+
+ALTER TABLE  file_tx_to_order
+    ADD PRIMARY KEY(file_id, source_class, dest_class, ref_key);
+
+ALTER TABLE file_order_to_order 
+    ADD PRIMARY KEY(file_id, source_class, dest_class, ref_key);
+
+ALTER TABLE file_order_to_tx
+    ADD PRIMARY KEY(file_id, source_class, dest_class, ref_key);
+


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