[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [856] trunk/sql
- Subject: SF.net SVN: ledger-smb: [856] trunk/sql
- From: ..hidden..
- Date: Tue, 06 Mar 2007 19:14:39 -0800
Revision: 856
http://svn.sourceforge.net/ledger-smb/?rev=856&view=rev
Author: einhverfr
Date: 2007-03-06 19:14:39 -0800 (Tue, 06 Mar 2007)
Log Message:
-----------
Adding proof-of-concept HR stored procedures and some beginnings of the security rewrite in temporary development files.
Added Paths:
-----------
trunk/sql/modules/
trunk/sql/modules/README
trunk/sql/modules/hr.sql
trunk/sql/modules/security.sql
Added: trunk/sql/modules/README
===================================================================
--- trunk/sql/modules/README (rev 0)
+++ trunk/sql/modules/README 2007-03-07 03:14:39 UTC (rev 856)
@@ -0,0 +1,3 @@
+This set of files is just for initial testing and proof of concept purposes.
+They will be moved out of here into appropriate upgrade scripts as necessary
+and are intended only for collaborative development purposes.
Added: trunk/sql/modules/hr.sql
===================================================================
--- trunk/sql/modules/hr.sql (rev 0)
+++ trunk/sql/modules/hr.sql 2007-03-07 03:14:39 UTC (rev 856)
@@ -0,0 +1,177 @@
+
+CREATE TABLE location (
+ id SERIAL PRIMARY KEY,
+ companyname text,
+ address1 text,
+ address2 text,
+ city text,
+ state text,
+ country text,
+ zipcode text
+);
+
+CREATE SEQUENCE employee_id_seq;
+SELECT setval('employee_id_seq', (select max(id) + 1 FROM employee));
+
+ALTER TABLE employee ADD COLUMN locations_id integer;
+ALTER TABLE employee ADD FOREIGN KEY (locations_id) REFERENCES location(id);
+ALTER TABLE employee ALTER COLUMN id DROP DEFAULT;
+ALTER TABLE employee ALTER COLUMN id SET DEFAULT nextval('employee_id_seq');
+
+CREATE OR REPLACE FUNCTION location_save
+(in_id int, in_companyname text, in_address1 text, in_address2 text,
+ in_city text, in_state text, in_zipcode text, in_country text)
+returns integer AS
+$$
+DECLARE
+ location_id integer;
+BEGIN
+ UPDATE location
+ SET companyname = in_companyname,
+ address1 = in_address1,
+ address2 = in_address2,
+ city = in_city,
+ state = in_state,
+ zipcode = in_zipcode,
+ country = in_country
+ WHERE id = in_id;
+ IF FOUND THEN
+ return in_id;
+ END IF;
+ INSERT INTO location
+ (companyname, address1, address2, city, state, zipcode, country)
+ VALUES
+ (in_companyname, in_address1, in_address2, in_city, in_state,
+ in_zipcode, in_country);
+ SELECT lastval('location_id_seq') INTO location_id;
+ return location_id;
+END;
+$$ LANGUAGE PLPGSQL;
+
+create or replace function employee_save
+(in_id integer, in_location_id integer, in_employeenumber varchar(32),
+ in_name varchar(64), in_address1 varchar(32), in_address2 varchar(32),
+ in_city varchar(32), in_state varchar(32), in_zipcode varchar(10),
+ in_country varchar(32), in_workphone varchar(20),
+ in_homephone varchar(20), in_startdate date, in_enddate date,
+ in_notes text, in_role varchar(20), in_sales boolean, in_email text,
+ in_ssn varchar(20), in_dob date, in_iban varchar(34),
+ in_bic varchar(11), in_managerid integer) returns int
+AS
+$$
+BEGIN
+ UPDATE employee
+ SET location_id = in_location_id,
+ employeenumber = in_employeenumber,
+ name = in_name,
+ address1 = in_address1,
+ address2 = in_address2,
+ city = in_city,
+ state = in_state,
+ zipcode = in_zipcode,
+ country = in_country,
+ workphone = in_workphone,
+ homephone = in_homephone,
+ startdate = in_startdate,
+ enddate = in_enddate,
+ notes = in_notes,
+ role = in_role,
+ sales = in_sales,
+ email = in_email,
+ ssn = in_ssn,
+ dob=in_dob,
+ iban = in_iban,
+ bic = in_bic,
+ manager_id = in_managerid
+ WHERE id = in_id;
+
+ IF FOUND THEN
+ return in_id;
+ END IF;
+ INSERT INTO employee
+ (location_id, employeenumber, name, address1, address2,
+ city, state, zipcode, country, workphone, homephone,
+ startdate, enddate, notes, role, sales, email, ssn,
+ dob, iban, bic, managerid)
+ VALUES
+ (in_location_id, in_employeenumber, in_name, in_address1,
+ in_address2, in_city, in_state, in_zipcode, in_country,
+ in_workphone, in_homephone, in_startdate, in_enddate,
+ in_notes, in_role, in_sales, in_email, in_ssn, in_dob,
+ in_iban, in_bic, in_managerid);
+ SELECT currval('employee_id_seq') INTO employee_id;
+ return employee_id;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION employee_get
+(in_id integer)
+returns employee as
+$$
+DECLARE
+ emp employee%ROWTYPE;
+BEGIN
+ SELECT * INTO emp FROM employee WHERE id = in_id;
+ RETURN emp;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION employee_list_managers
+(in_id integer)
+RETURNS SETOF employee as
+$$
+DECLARE
+ emp employee%ROWTYPE;
+BEGIN
+ FOR emp IN
+ SELECT * FROM employee
+ WHERE sales = '1' AND role='manager'
+ AND id <> coalesce(in_id, -1)
+ ORDER BY name
+ LOOP
+ RETURN NEXT emp;
+ END LOOP;
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION employee_delete
+(in_id integer) returns void as
+$$
+BEGIN
+ DELETE FROM employee WHERE id = in_id;
+ RETURN;
+END;
+$$ language plpgsql;
+
+-- as long as we need the datatype, might as well get some other use out of it!
+CREATE OR REPLACE VIEW employee_search AS
+SELECT e.*, m.name AS manager
+FROM employee e JOIN employee m ON (e.managerid = m.id);
+
+CREATE OR REPLACE FUNCTION employee_search
+(in_startdatefrom date, in_startdateto date, in_name varchar, in_notes text,
+ in_enddateto date, in_enddatefrom date)
+RETURNS SETOF employee_search AS
+$$
+DECLARE
+ emp employee_search%ROWTYPE;
+BEGIN
+ FOR emp IN
+ SELECT * FROM employee_search
+ WHERE coalesce(startdate, 'infinity'::timestamp)
+ >= coalesce(in_startdateto, '-infinity'::timestamp)
+ AND coalesce(startdate, '-infinity'::timestamp) <=
+ coalesce(in_startdatefrom,
+ 'infinity'::timestamp)
+ AND coalesce(enddate, '-infinity'::timestamp) <=
+ coalesce(in_enddateto, 'infinity'::timestamp)
+ AND coalesce(enddate, 'infinity'::timestamp) >=
+ coalesce(in_enddatefrom, '-infinity'::timestamp)
+ AND lower(name) LIKE '%' || lower(in_name) || '%'
+ AND lower(notes) LIKE '%' || lower(in_notes) || '%'
+ AND (sales = 't' OR coalesce(in_sales, 'f') = 'f')
+ LOOP
+ RETURN NEXT emp;
+ END LOOP;
+END;
+$$ language plpgsql;
Added: trunk/sql/modules/security.sql
===================================================================
--- trunk/sql/modules/security.sql (rev 0)
+++ trunk/sql/modules/security.sql 2007-03-07 03:14:39 UTC (rev 856)
@@ -0,0 +1,108 @@
+create table modules (
+id SERIAL PRIMARY KEY,
+mod_name TEXT UNIQUE NOT NULL,
+comments text default ''
+);
+comment on table modules is $$name may be used as an alternate key.
+Comments should be used to provide the admin of the system with an
+understanding of what the module does. Names and comments are also subject to
+string freezes since they may be translated by the application.$$;
+
+-- not adding comments to these because they are translated anyway.
+insert into modules (mod_name) values ('AR');
+insert into modules (mod_name) values ('AP');
+insert into modules (mod_name) values ('HR');
+insert into modules (mod_name) values ('Order Entry');
+insert into modules (mod_name) values ('Goods and Services');
+insert into modules (mod_name) values ('Recurring Transactions');
+insert into modules (mod_name) values ('System');
+
+create or replace function add_module (text, text) returns int AS $$
+insert into modules (mod_name, comments) values ($1, $2);
+select currval(modules_id_seq);
+$$ language sql;
+
+create or replace function get_all_modules () returns setof modules as $$
+select id, mod_name, comments from modules;
+$$ language sql;
+
+create or replace function get_module_by_id (int) returns modules as $$
+select id, modname, comments from modules where id = $1;
+$$ language sql;
+
+create or replace function get_module_by_name (text) returns modules as $$
+select id, modname, comments from modules where mod_name = $1;
+$$ language sql;
+
+create or replace function save_module (int, text, text) returns bool as $$
+update modules set mod_name = $2, comments=$3 where id = $1;
+$$ language sql;
+
+create table mod_relation (
+id serial primary key,
+mod_id int not null references modules(id),
+rel_name text NOT NULL,
+rel_type "char" CHECK IN ('t', 's')
+);
+
+comment on table mod_relation is $$reltype is 't' for tables or views and 's'
+for sequences. rel_name is the name of the table.$$;
+
+create or replace function register_table (text, text) returns int AS $$
+insert into mod_relation (mod_id, relname, reltype) values
+((select id from modules where mod_name = $1), $2, 't');
+select 1;
+$$ language sql;
+
+create or replace function register_sequence (text, text) returns int as $$
+insert into module_relation (mod_id, relname, reltype) values
+((select id from modules where mod_name = $1), $2, 's');
+select 1;
+$$ language sql;
+
+select register_table('System', 'modules');
+select register_table('System', 'mod_relation');
+select register_sequence('System', 'modules_id_seq');
+select register_sequence('System', 'mod_relation_id_seq');
+
+create or replace function change_my_password(text) returns bool as $$
+begin
+execute 'alter user ''' || session_user || ''' with encrypted password '''
+ || $1 || '''';
+return true;
+end;
+$$ language plpgsql security definer;
+
+comment on function change_my_password is $$ This function must be created as a superuser to work!$$;
+
+create table db_users (
+id serial primary key,
+username text unique not null,
+active bool default true not null
+);
+
+comment on db_users is $$This is a list of users applicable to this
+dataset. Note that the user creation script must connect to the dataset to be
+used and add the username to this table. Otherwise the user will not be able
+to log in.$$;
+
+create table preferences
+(id integer primary key references db_users(id) deferrable initially deferred,
+employee_id integer references employees(id), deferrable initially deferred,
+ countrycode text,
+ currency text,
+ dateformat text,
+ menuwidth text,
+ printer text,
+ signature text,
+ stylesheet text,
+ templates text,
+ timeout numeric,
+ vclimit numeric
+
+);
+
+create or replace function add_user (text) returns bool as $$
+insert into dataset_users (username) values ($1);
+select true;
+$$ language sql;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.