[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3757] trunk
- Subject: SF.net SVN: ledger-smb:[3757] trunk
- From: ..hidden..
- Date: Tue, 27 Sep 2011 12:44:01 +0000
Revision: 3757
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3757&view=rev
Author: einhverfr
Date: 2011-09-27 12:44:00 +0000 (Tue, 27 Sep 2011)
Log Message:
-----------
Trunk is now dependent on Pg 8.4, but not on any Pg contrib modules
Modified Paths:
--------------
trunk/scripts/setup.pl
trunk/sql/Pg-database.sql
trunk/sql/modules/LOADORDER
Added Paths:
-----------
trunk/sql/modules/Menu.sql
Modified: trunk/scripts/setup.pl
===================================================================
--- trunk/scripts/setup.pl 2011-09-27 10:46:46 UTC (rev 3756)
+++ trunk/scripts/setup.pl 2011-09-27 12:44:00 UTC (rev 3757)
@@ -525,20 +525,7 @@
my $dbh = $request->{dbh};
$dbh->do('ALTER SCHEMA public RENAME TO lsmb12');
$dbh->do('CREATE SCHEMA PUBLIC');
- # Copying contrib script loading for now
- my $rc = 0;
- my $temp = $LedgerSMB::Sysconfig::temp;
- my @contrib_scripts = qw(pg_trgm tsearch2 tablefunc);
- for my $contrib (@contrib_scripts){
- my $rc2;
- $rc2=system("psql -f $ENV{PG_CONTRIB_DIR}/$contrib.sql >> $temp/dblog_stdout 2>>$temp/dblog_stderr");
- $rc ||= $rc2
- }
- my $rc2 = system("psql -f sql/Pg-database.sql >> $temp/dblog_stdout 2>>$temp/dblog_stderr");
-
- $rc ||= $rc2;
-
$database->load_modules('LOADORDER');
my $dbtemplate = LedgerSMB::Template->new(
user => {},
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2011-09-27 10:46:46 UTC (rev 3756)
+++ trunk/sql/Pg-database.sql 2011-09-27 12:44:00 UTC (rev 3757)
@@ -3226,31 +3226,6 @@
ARRAY[key||'='||value, key||'='||value,...]
$$;
-CREATE TYPE menu_item AS (
- position int,
- id int,
- level int,
- label varchar,
- path varchar,
- args varchar[]
-);
-
-
-CREATE VIEW menu_friendly AS
-SELECT t."level", t.path, t.list_order,
- (repeat(' '::text, (2 * t."level")) || (n.label)::text) AS label,
- n.id, n."position"
- FROM (connectby('menu_node'::text, 'id'::text, 'parent'::text,
- 'position'::text, '0'::text, 0, ','::text
- ) t(id integer, parent integer, "level" integer, path text,
- list_order integer)
- JOIN menu_node n USING (id));
-
-COMMENT ON VIEW menu_friendly IS
-$$ A nice human-readable view for investigating the menu tree. Does not
-show menu attributes or acls.$$;
-
-
--ALTER TABLE public.menu_friendly OWNER TO ledgersmb;
--
@@ -3283,13 +3258,6 @@
Example: SELECT as_array(ARRAY[id::text, class]) from contact_class
$$;
-CREATE INDEX company_name_gist__idx ON company USING gist(legal_name gist_trgm_ops);
-CREATE INDEX location_address_one_gist__idx ON location USING gist(line_one gist_trgm_ops);
-CREATE INDEX location_address_two_gist__idx ON location USING gist(line_two gist_trgm_ops);
-CREATE INDEX location_address_three_gist__idx ON location USING gist(line_three gist_trgm_ops);
-
-CREATE INDEX location_city_prov_gist_idx ON location USING gist(city gist_trgm_ops);
-CREATE INDEX entity_name_gist_idx ON entity USING gist(name gist_trgm_ops);
CREATE INDEX ap_approved_idx ON ap(approved);
CREATE INDEX ar_approved_idx ON ar(approved);
CREATE INDEX gl_approved_idx ON gl(approved);
Modified: trunk/sql/modules/LOADORDER
===================================================================
--- trunk/sql/modules/LOADORDER 2011-09-27 10:46:46 UTC (rev 3756)
+++ trunk/sql/modules/LOADORDER 2011-09-27 12:44:00 UTC (rev 3757)
@@ -1,5 +1,6 @@
# This is a simple list of files to be loaded from sql/modules in the order
# they shall be loaded.
+Menu.sql
Drafts.sql
chart.sql
Account.sql
Added: trunk/sql/modules/Menu.sql
===================================================================
--- trunk/sql/modules/Menu.sql (rev 0)
+++ trunk/sql/modules/Menu.sql 2011-09-27 12:44:00 UTC (rev 3757)
@@ -0,0 +1,151 @@
+
+CREATE TYPE menu_item AS (
+ position int,
+ id int,
+ level int,
+ label varchar,
+ path varchar,
+ parent int,
+ args varchar[]
+);
+
+
+
+CREATE OR REPLACE FUNCTION menu_generate() RETURNS SETOF menu_item AS
+$$
+DECLARE
+ item menu_item;
+ arg menu_attribute%ROWTYPE;
+BEGIN
+ FOR item IN
+ WITH RECURSIVE tree (path, id, parent, level, positions)
+ AS (select id::text as path, id, parent,
+ 0 as level, position::text
+ FROM menu_node where parent is null
+ UNION
+ select path || ',' || n.id::text, n.id,
+ n.parent,
+ t.level + 1,
+ t.positions || ',' || n.position
+ FROM menu_node n
+ JOIN tree t ON t.id = n.parent)
+ SELECT n.position, n.id, c.level, n.label, c.path, n.parent,
+ to_args(array[ma.attribute, ma.value])
+ FROM tree c
+ JOIN menu_node n USING(id)
+ JOIN menu_attribute ma ON (n.id = ma.node_id)
+ WHERE n.id IN (select node_id FROM menu_acl
+ WHERE pg_has_role(CASE WHEN role_name
+ ilike 'public'
+ THEN current_user
+ ELSE role_name
+ END, 'USAGE')
+ GROUP BY node_id
+ HAVING bool_and(CASE WHEN acl_type ilike 'DENY'
+ THEN FALSE
+ WHEN acl_type ilike 'ALLOW'
+ THEN TRUE
+ END))
+ or exists (select cn.id, cc.path
+ FROM tree cc
+ JOIN menu_node cn USING(id)
+ WHERE cn.id IN
+ (select node_id FROM menu_acl
+ WHERE pg_has_role(CASE WHEN role_name
+ ilike 'public'
+ THEN current_user
+ ELSE role_name
+ END, 'USAGE')
+ GROUP BY node_id
+ HAVING bool_and(CASE WHEN acl_type
+ ilike 'DENY'
+ THEN false
+ WHEN acl_type
+ ilike 'ALLOW'
+ THEN TRUE
+ END))
+ and cc.path::text
+ like c.path::text || ',%')
+ GROUP BY n.position, n.id, c.level, n.label, c.path, c.positions,
+ n.parent
+ ORDER BY string_to_array(c.positions, ',')::int[]
+ LOOP
+ RETURN NEXT item;
+ END LOOP;
+END;
+$$ language plpgsql;
+
+COMMENT ON FUNCTION menu_generate() IS
+$$
+This function returns the complete menu tree. It is used to generate nested
+menus for the web interface.
+$$;
+
+CREATE OR REPLACE FUNCTION menu_children(in_parent_id int) RETURNS SETOF menu_item
+AS $$
+SELECT * FROM menu_generate() where parent = $1;
+$$ language sql;
+
+COMMENT ON FUNCTION menu_children(int) IS
+$$ This function returns all menu items which are children of in_parent_id
+(the only input parameter).
+
+It is thus similar to menu_generate() but it only returns the menu items
+associated with nodes directly descendant from the parent. It is used for
+menues for frameless browsers.$$;
+
+CREATE OR REPLACE FUNCTION
+menu_insert(in_parent_id int, in_position int, in_label text)
+returns int
+AS $$
+DECLARE
+ new_id int;
+BEGIN
+ UPDATE menu_node
+ SET position = position * -1
+ WHERE parent = in_parent_id
+ AND position >= in_position;
+
+ INSERT INTO menu_node (parent, position, label)
+ VALUES (in_parent_id, in_position, in_label);
+
+ SELECT INTO new_id currval('menu_node_id_seq');
+
+ UPDATE menu_node
+ SET position = (position * -1) + 1
+ WHERE parent = in_parent_id
+ AND position < 0;
+
+ RETURN new_id;
+END;
+$$ language plpgsql;
+
+comment on function menu_insert(int, int, text) is $$
+This function inserts menu items at arbitrary positions. The arguments are, in
+order: parent, position, label. The return value is the id number of the menu
+item created. $$;
+
+CREATE VIEW menu_friendly AS
+WITH RECURSIVE tree (path, id, parent, level, positions)
+ AS (select id::text as path, id, parent,
+ 0 as level, position::text
+ FROM menu_node where parent is null
+ UNION
+ select path || ',' || n.id::text, n.id,
+ n.parent,
+ t.level + 1,
+ t.positions || ',' || n.position
+ FROM menu_node n
+ JOIN tree t ON t.id = n.parent)
+SELECT t."level", t.path,
+ (repeat(' '::text, (2 * t."level")) || (n.label)::text) AS label,
+ n.id, n."position"
+ FROM tree t
+ JOIN menu_node n USING (id)
+ ORDER BY string_to_array(t.positions, ',')::int[];
+
+COMMENT ON VIEW menu_friendly IS
+$$ A nice human-readable view for investigating the menu tree. Does not
+show menu attributes or acls.$$;
+
+
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.