[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[6845] trunk/sql
- Subject: SF.net SVN: ledger-smb:[6845] trunk/sql
- From: ..hidden..
- Date: Wed, 12 Feb 2014 02:15:55 +0000
Revision: 6845
http://sourceforge.net/p/ledger-smb/code/6845
Author: einhverfr
Date: 2014-02-12 02:15:54 +0000 (Wed, 12 Feb 2014)
Log Message:
-----------
Internal and incoming file sql schema and roles handling. This does not include logic yet. Also deleted sql fixes entries from before beta 2
Modified Paths:
--------------
trunk/sql/Pg-database.sql
trunk/sql/modules/Fixes.sql
trunk/sql/modules/Roles.sql
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2014-02-11 22:13:57 UTC (rev 6844)
+++ trunk/sql/Pg-database.sql 2014-02-12 02:15:54 UTC (rev 6845)
@@ -4550,7 +4550,9 @@
(2, 'order'),
(3, 'part'),
(4, 'entity'),
- (5, 'eca');
+ (5, 'eca'),
+ (6, 'internal'),
+ (7, 'incoming');
COMMENT ON TABLE file_class IS
@@ -4634,6 +4636,37 @@
COMMENT ON TABLE file_eca IS
$$ File attachments primarily attached to customer and vendor agreements.$$;
+CREATE TABLE file_internal (
+ check (file_class = 6),
+ unique(id),
+ primary key (ref_key, file_name, file_class),
+ check (ref_key = 0)
+) inherits (file_base);
+
+COMMENT ON COLUMN file_internal.ref_key IS
+$$ Always must be 0, and we have no primary key since these files all
+are for internal use and against the company, not categorized.$$;
+
+COMMENT ON TABLE file_internal IS
+$$ This is for internal files used operationally by LedgerSMB. For example,
+company logos would be here.$$;
+
+CREATE TABLE file_incoming (
+ check (file_class = 7),
+ unique(id),
+ primary key (ref_key, file_name, file_class),
+ check (ref_key = 0)
+) inherits (file_base);
+
+
+COMMENT ON COLUMN file_incoming.ref_key IS
+$$ Always must be 0, and we have no primary key since these files all
+are for interal incoming use, not categorized.$$;
+
+COMMENT ON TABLE file_incoming IS
+$$ This is essentially a spool for files to be reviewed and attached. It is
+important that the names are somehow guaranteed to be unique, so one may want to prepend them with an email equivalent or the like.$$;
+
CREATE TABLE file_secondary_attachment (
file_id int not null,
source_class int references file_class(id),
Modified: trunk/sql/modules/Fixes.sql
===================================================================
--- trunk/sql/modules/Fixes.sql 2014-02-11 22:13:57 UTC (rev 6844)
+++ trunk/sql/modules/Fixes.sql 2014-02-12 02:15:54 UTC (rev 6845)
@@ -5,114 +5,8 @@
-- Chris Travers
--- BETA 1-3 (delete a month after Beta 4)
-
-
BEGIN;
-ALTER TABLE file_transaction DROP CONSTRAINT "file_transaction_ref_key_fkey";
-ALTER TABLE file_transaction ADD FOREIGN KEY (ref_key) REFERENCES transactions(id);
-
-COMMIT;
-
-BEGIN;
-
-ALTER TABLE country_tax_form ADD is_accrual bool not null default false;
-
-COMMIT;
-
-BEGIN;
-
-CREATE VIEW cash_impact AS
-SELECT id, '1'::numeric AS portion, 'gl' as rel, gl.transdate FROM gl
-UNION ALL
-SELECT id, CASE WHEN gl.amount = 0 THEN 0 -- avoid div by 0
- WHEN gl.transdate = ac.transdate
- THEN 1 + sum(ac.amount) / gl.amount
- ELSE
- 1 - (gl.amount - sum(ac.amount)) / gl.amount
- END , 'ar' as rel, ac.transdate
- FROM ar gl
- JOIN acc_trans ac ON ac.trans_id = gl.id
- JOIN account_link al ON ac.chart_id = al.account_id and al.description = 'AR'
- GROUP BY gl.id, gl.amount, ac.transdate
-UNION ALL
-SELECT id, CASE WHEN gl.amount = 0 THEN 0
- WHEN gl.transdate = ac.transdate
- THEN 1 - sum(ac.amount) / gl.amount
- ELSE
- 1 - (gl.amount + sum(ac.amount)) / gl.amount
- END, 'ap' as rel, ac.transdate
- FROM ap gl
- JOIN acc_trans ac ON ac.trans_id = gl.id
- JOIN account_link al ON ac.chart_id = al.account_id and al.description = 'AP'
- GROUP BY gl.id, gl.amount, ac.transdate;
-
-COMMENT ON VIEW cash_impact IS
-$$ This view is used by cash basis reports to determine the fraction of a
-transaction to be counted.$$;
-COMMIT;
-
-BEGIN;
-
-ALTER TABLE payroll_deduction_class ADD stored_proc_name name not null;
-
-COMMIT;
-
-BEGIN; -- Timecard types
-
-CREATE TABLE jctype (
- id int not null unique, -- hand assigned
- label text primary key,
- description text not null,
- is_service bool default true,
- is_timecard bool default true
-);
-
-INSERT INTO jctype (id, label, description, is_service, is_timecard)
-VALUES (1, 'time', 'Timecards for project services', true, true);
-
-INSERT INTO jctype (id, label, description, is_service, is_timecard)
-VALUES (2, 'materials', 'Materials for projects', false, false);
-
-INSERT INTO jctype (id, label, description, is_service, is_timecard)
-VALUES (3, 'overhead', 'Time/Overhead for payroll, manufacturing, etc', false, true);
-
-COMMIT;
-
--- BETA 2
-BEGIN;
-
-ALTER TABLE tax_extended DROP CONSTRAINT "tax_extended_entry_id_fkey";
-
-ALTER TABLE tax_extended ADD FOREIGN KEY (entry_id)
-REFERENCES acc_trans(entry_id);
-
-COMMIT;
-
-BEGIN;
-
-ALTER TABLE inventory_report_line add adjust_id int not null;
-
- alter table inventory_report_line add variance numeric not null;
-
-
-COMMIT;
-
-BEGIN;
-
---- EDI contact fixes
-
-
-INSERT INTO contact_class (id,class) values (18,'EDI Interchange ID');
-INSERT INTO contact_class (id,class) values (19,'EDI ID');
-
-SELECT SETVAL('contact_class_id_seq',19);
-
-COMMIT;
-
-BEGIN;
-
ALTER TABLE asset_report DROP CONSTRAINT "asset_report_gl_id_fkey";
ALTER TABLE asset_report ADD CONSTRAINT "asset_report_gl_id_fkey" FOREIGN KEY (gl_id) REFERENCES gl(id);
@@ -300,3 +194,44 @@
ALTER TABLE batch ADD FOREIGN KEY (locked_by) REFERENCES session(session_id)
ON DELETE SET NULL;
COMMIT;
+
+-- POST-BETA-5 FIXES
+
+BEGIN;
+INSERT INTO file_class (id, class) values (6, 'internal'), (7, 'incoming');
+COMMIT;
+
+BEGIN;
+CREATE TABLE file_internal (
+ check (file_class = 6),
+ unique(id),
+ primary key (ref_key, file_name, file_class),
+ check (ref_key = 0)
+) inherits (file_base);
+
+COMMENT ON COLUMN file_internal.ref_key IS
+$$ Always must be 0, and we have no primary key since these files all
+are for internal use and against the company, not categorized.$$;
+
+COMMENT ON TABLE file_internal IS
+$$ This is for internal files used operationally by LedgerSMB. For example,
+company logos would be here.$$;
+
+CREATE TABLE file_incoming (
+ check (file_class = 7),
+ unique(id),
+ primary key (ref_key, file_name, file_class),
+ check (ref_key = 0)
+) inherits (file_base);
+
+
+COMMENT ON COLUMN file_incoming.ref_key IS
+$$ Always must be 0, and we have no primary key since these files all
+are for interal incoming use, not categorized.$$;
+
+COMMENT ON TABLE file_incoming IS
+$$ This is essentially a spool for files to be reviewed and attached. It is
+important that the names are somehow guaranteed to be unique, so one may want to prepend them with an email equivalent or the like.$$;
+
+COMMIT;
+
Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql 2014-02-11 22:13:57 UTC (rev 6844)
+++ trunk/sql/modules/Roles.sql 2014-02-12 02:15:54 UTC (rev 6845)
@@ -174,6 +174,8 @@
SELECT lsmb__grant_perms('file_read', 'file_order', 'SELECT');
SELECT lsmb__grant_perms('file_read', 'file_links', 'SELECT');
SELECT lsmb__grant_perms('file_read', 'file_part', 'SELECT');
+SELECT lsmb__grant_perms('file_read', 'file_internal', 'SELECT');
+SELECT lsmb__grant_perms('file_read', 'file_incoming', 'SELECT');
SELECT lsmb__create_role('file_attach_tx');
SELECT lsmb__grant_perms('file_attach_tx', 'file_transaction', 'INSERT');
@@ -196,6 +198,9 @@
SELECT lsmb__grant_perms('file_attach_tx', 'file_base_id_seq', 'ALL');
SELECT lsmb__grant_perms('file_attach_order', 'file_base_id_seq', 'ALL');
SELECT lsmb__grant_perms('file_attach_part', 'file_base_id_seq', 'ALL');
+SELECT lsmb__grant_perms(role, 'file_incoming', 'DELETE')
+ FROM unnest(ARRAY['file_attach_tx'::text, 'file_attach_order',
+ 'file_attach_part']) role;
\echo Contact Management
SELECT lsmb__create_role('contact_read');
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
------------------------------------------------------------------------------
Android apps run on BlackBerry 10
Introducing the new BlackBerry 10.2.1 Runtime for Android apps.
Now with support for Jelly Bean, Bluetooth, Mapview and more.
Get your Android app in front of a whole new audience. Start now.
http://pubads.g.doubleclick.net/gampad/clk?id=124407151&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-commits mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-commits