[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [1778] trunk
- Subject: SF.net SVN: ledger-smb: [1778] trunk
- From: ..hidden..
- Date: Wed, 17 Oct 2007 16:16:36 -0700
Revision: 1778
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=1778&view=rev
Author: aurynn_cmd
Date: 2007-10-17 16:16:35 -0700 (Wed, 17 Oct 2007)
Log Message:
-----------
Serious updates to the Admin controller;
Creation of a User controller/object - I would like to use this to save/load roles, as well.
Creation of Person stored procs, Entity stored procs. Serious updates to the employee stored procs.
Modified Paths:
--------------
trunk/LedgerSMB/DBObject/Admin.pm
trunk/LedgerSMB/Employee.pm
trunk/sql/Pg-database.sql
trunk/sql/modules/Employee.sql
trunk/sql/modules/admin.sql
Added Paths:
-----------
trunk/LedgerSMB/DBObject/User.pm
trunk/sql/modules/Entity.sql
trunk/sql/modules/Person.sql
Modified: trunk/LedgerSMB/DBObject/Admin.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Admin.pm 2007-10-17 21:12:50 UTC (rev 1777)
+++ trunk/LedgerSMB/DBObject/Admin.pm 2007-10-17 23:16:35 UTC (rev 1778)
@@ -6,24 +6,39 @@
use LedgerSMB::DBObject::Employee;
use LedgerSMB::Contact;
-sub save_user {
+#[18:00:31] <aurynn> I'd like to split them employee/user and roles/prefs
+#[18:00:44] <aurynn> edit/create employee and add user features if needed.
+
+sub save {
+ $self->error("Cannot save an Adminstrator object.");
+}
+
+sub save_employee {
+
my $self = shift @_;
my $entity_id = shift @{ $self->exec_method( procname => "save_user" ) };
$self->merge($entity_id);
- my $employee = LedgerSMB::DBObject::Employee->new(base=>$self, copy=>'list',
+ my $person = LedgerSMB::DBObject::Person->new(base=>$self, copy=>'list',
merge=>[
'salutation',
'first_name',
'last_name',
+ ]
+ );
+ my $employee = LedgerSMB::DBObject::Employee->new(base=>$self, copy=>'list',
+ merge=>[
+ '',
+ 'first_name',
+ 'last_name',
'employeenumber',
]
);
$employee->{entity_id} = $entity_id->{id};
- $employee->save_employee();
+ $employee->save();
my $loc = LedgerSMB::DBObject::Location->new(base=>$self, copy=>'list',
merge=>[
@@ -36,10 +51,11 @@
'companyname',
]
);
- $loc->save_location();
- $loc->join_to_person(person=>$employee);
-
+ $loc->save();
+ $employee->set_location($loc->{id});
+ $loc->(person=>$employee);
+
my $workphone = LedgerSMB::Contact->new(base=>$self);
my $homephone = LedgerSMB::Contact->new(base=>$self);
my $email = LedgerSMB::Contact->new(base=>$self);
@@ -51,9 +67,29 @@
$homephone->save();
$email->save();
- my $roles = $self->exec_method( procname => "all_roles" );
- my $user_roles = $self->exec_method(procname => "get_user_roles", args=>[ $self->{ modifying_user } ] );
+ # now, check for user-specific stuff. Is this person a user or not?
+ my $user = LedgerSMB::DBObject::User->new(base=>$self, copy=>'list',
+ merge=>[
+ 'username',
+ 'password',
+ 'is_a_user'
+ ]
+ );
+
+ $user->get();
+ $user->save();
+}
+
+sub save_roles {
+
+ my $self = shift @_;
+
+ my $user = LedgerSMB::DBObject::User->new(base=>$self, copy=>'all');
+
+ my $roles = $self->exec_method( procname => "admin_all_roles" );
+ my $user_roles = $self->exec_method(procname => "admin_get_user_roles", args=>[ $self->{ username } ] );
+
my %active_roles;
for my $role (@{$user_roles}) {
@@ -86,6 +122,7 @@
);
}
}
+
}
sub save_group {
Added: trunk/LedgerSMB/DBObject/User.pm
===================================================================
--- trunk/LedgerSMB/DBObject/User.pm (rev 0)
+++ trunk/LedgerSMB/DBObject/User.pm 2007-10-17 23:16:35 UTC (rev 1778)
@@ -0,0 +1,52 @@
+package LedgerSMB::DBObject::User;
+
+use base qw/LedgerSMB::DBObject/;
+
+sub save {
+
+ my $self = shift @_;
+
+ my $user = $self->get();
+
+ if ($user->{id} && $self->{is_a_user}) {
+
+ # doesn't check for the password - that's done in the sproc.
+ $self->{id} = @{ $self->exec_method(procname=>'admin_save_user',
+ args=>[$user->{id}, $self->{username}, $self->{password}] ) }[0];
+ else {
+
+ return 0;
+ }
+ }
+ elsif ($user && !$self->{is_a_user}) {
+
+ # there WAS a user, and we've decided we want that user to go away now.
+
+ $self->{id} = $user->{id};
+ return $self->remove();
+
+ }
+ return 1;
+}
+
+sub get {
+
+ my $self = shift @_;
+
+ my ($user_id, $username) = @{ $self->exec_method(procname=>'admin_get_user',
+ args=>[$self->{id}])};
+
+ return {id=>$user_id, username=>$username};
+}
+
+sub remove {
+
+ my $self = shift;
+
+ my $code = $self->exec_method(procname=>"admin_delete_user", args=>[$self->{id}, $self->{username}]);
+ $self->{id} = undef; # never existed..
+
+ return $code->[0];
+}
+
+1;
\ No newline at end of file
Modified: trunk/LedgerSMB/Employee.pm
===================================================================
--- trunk/LedgerSMB/Employee.pm 2007-10-17 21:12:50 UTC (rev 1777)
+++ trunk/LedgerSMB/Employee.pm 2007-10-17 23:16:35 UTC (rev 1778)
@@ -48,6 +48,10 @@
sub save {
my $self = shift;
+
+ my $person = shift @{ $self->exec_method (procname => 'person_save',
+ args => [] )};
+
my $hashref = shift @{ $self->exec_method( procname => "employee_save" ) };
$self->merge( $hashref, 'id' );
}
@@ -70,4 +74,21 @@
$self->exec_method( procname => "employee_search" );
}
+sub set_location {
+
+ my $self = shift @_;
+ my $location = shift @_;
+
+ my $code = $self->exec_method ( procname => 'employee_set_location',
+ args=>[ $self->{id}, $location->{id} ] );
+
+ if ($code) {
+
+ # good, it worked.
+
+ return 1;
+ }
+ return 0;
+}
+
1;
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2007-10-17 21:12:50 UTC (rev 1777)
+++ trunk/sql/Pg-database.sql 2007-10-17 23:16:35 UTC (rev 1778)
@@ -10,7 +10,7 @@
CREATE TABLE entity (
id serial UNIQUE,
name text check (name ~ '[[:alnum:]_]'),
- entity_class integer not null,
+ entity_class integer references entity_class(id) not null ,
created date not null default current_date,
PRIMARY KEY(name,entity_class));
@@ -58,70 +58,6 @@
COMMENT ON TABLE users IS $$username is the actual primary key here because we do not want duplicate users$$;
-create table user_connection (
- user_id int not null references users(id) on delete cascade,
- dbname text not null,
- host text not null default 'localhost',
- port int not null default '5432'
-);
-/*
-CREATE VIEW users_conf as
- select
- users.id,
- loc.address1 || '\n'|| loc.address2 ||'\n' || loc.address3,
- em.employeenumber,
- company,
- loc.country,
- currency,
- dateformat,
- 'Pg',
- u_cx.host,
- u_cx.dbname,
- u_cx.dbport,
- users.username,
- p.email,
- p.fax,
- 50,
- p.first_name || ' ' || p.last_name,
- p.number_format,
- '', -- password
-
-
-;
-*/
-CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred,
- acs text,
- address text,
- businessnumber text,
- company text,
- countrycode text,
- currency text,
- dateformat text,
- dbdriver text default 'Pg',
- dbhost text default 'localhost',
- dbname text,
- dboptions text,
- dbpasswd text,
- dbport text,
- dbuser text,
- email text,
- fax text,
- menuwidth text,
- name text,
- numberformat text,
- password varchar(32) check(length(password) = 32),
- crypted_password text,
- print text,
- printer text,
- role text,
- sid text,
- signature text,
- stylesheet text,
- tel text,
- templates text,
- timeout numeric,
- vclimit numeric);
-
COMMENT ON TABLE users_conf IS 'This is a completely dumb table that is a place holder to get usersconf into the database. Next major release will have a much more sane implementation';
COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(id) is correct';
COMMENT ON COLUMN users_conf.password IS 'This means we have to get rid of the current password stuff and move to presumably md5()';
@@ -255,17 +191,56 @@
SELECT SETVAL('salutation_id_seq',7);
CREATE TABLE person (
- id serial PRIMARY KEY,
- entity_id integer references entity(id) not null,
- salutation_id integer references salutation(id),
- first_name text check (first_name ~ '[[:alnum:]_]') NOT NULL,
- middle_name text,
- last_name text check (last_name ~ '[[:alnum:]_]') NOT NULL,
- created date not null default current_date
+ id serial PRIMARY KEY,
+ entity_id integer references entity(id) not null,
+ salutation_id integer references salutation(id),
+ first_name text check (first_name ~ '[[:alnum:]_]') NOT NULL,
+ middle_name text,
+ last_name text check (last_name ~ '[[:alnum:]_]') NOT NULL,
+ created date not null default current_date
);
COMMENT ON TABLE person IS $$ Every person, must have an entity to derive a common or display name. The correct way to get class information on a person would be person.entity_id->entity_class_to_entity.entity_id. $$;
+create table entity_employee (
+
+ person_id integer references person(id) not null,
+ entity_id integer references entity(id) not null,
+ startdate date not null default current_date,
+ enddate date,
+ role varchar(20),
+ sales bool default 'f',
+ manager_id integer references entity(id),
+ employeenumber varchar(32),
+ dob date
+ PRIMARY KEY (person_id, entity_id)
+);
+
+-- notes are from entity_note
+-- ssn, iban and bic are from entity_credit_account
+--
+
+create view employee as
+ SELECT
+ ente.entity_id,
+ 3,
+ u.username,
+ ente.startdate,
+ ente.enddate,
+ en.note,
+ eca.ssn,
+ eca.iban,
+ eca.bic,
+ ente.manager_id,
+ ente.employeenumber,
+ ente.dob
+ FROM
+ entity_employee ente
+ JOIN
+ entity_credit_account eca on eca.entity_id = ente.entity_id,
+ entity_note en on en.entity_id = ente.entity_id,
+ user u on u.entity_id = ente.entity_id;
+
CREATE TABLE person_to_location (
location_id integer not null references location(id),
person_id integer not null references person(id) ON DELETE CASCADE,
@@ -473,25 +448,25 @@
);
CREATE TABLE entity_credit_account (
- id serial not null unique,
- entity_id int not null references entity(id) ON DELETE CASCADE,
- entity_class int not null references entity_class(id) check ( entity_class in (1,2) ),
- discount numeric,
- discount_terms int default 0,
- taxincluded bool default 'f',
- creditlimit NUMERIC default 0,
- terms int2 default 0,
- meta_number varchar(32),
- cc text,
- bcc text,
- business_id int,
- language_code varchar(6),
- pricegroup_id int references pricegroup(id),
- curr char(3),
- startdate date DEFAULT CURRENT_DATE,
- enddate date,
- threshold numeric default 0,
- PRIMARY KEY(entity_id, meta_number)
+ id serial not null unique,
+ entity_id int not null references entity(id) ON DELETE CASCADE,
+ entity_class int not null references entity_class(id) check ( entity_class in (1,2) ),
+ discount numeric,
+ discount_terms int default 0,
+ taxincluded bool default 'f',
+ creditlimit NUMERIC default 0,
+ terms int2 default 0,
+ meta_number varchar(32),
+ cc text,
+ bcc text,
+ business_id int,
+ language_code varchar(6),
+ pricegroup_id int references pricegroup(id),
+ curr char(3),
+ startdate date DEFAULT CURRENT_DATE,
+ enddate date,
+ threshold numeric default 0,
+ PRIMARY KEY(entity_id, meta_number)
);
@@ -568,24 +543,13 @@
--
--
-create table employee (
- entity_id integer not null references entity(id) on delete cascade primary key,
- entity_class_id integer references entity_class(id) not null check (entity_class_id = 3),
- login text,
- startdate date default current_date,
- enddate date,
- notes text,
- role varchar(20),
- sales bool default 'f',
- ssn varchar(20),
- iban varchar(34),
- bic varchar(11),
- managerid int,
- employeenumber varchar(32),
- dob date
-);
+
+
+
+
COMMENT ON TABLE employee IS $$ Is a metadata table specific to employees $$;
+
CREATE TABLE parts (
id serial PRIMARY KEY,
partnumber text,
Modified: trunk/sql/modules/Employee.sql
===================================================================
--- trunk/sql/modules/Employee.sql 2007-10-17 21:12:50 UTC (rev 1777)
+++ trunk/sql/modules/Employee.sql 2007-10-17 23:16:35 UTC (rev 1778)
@@ -1,174 +1,119 @@
-- VERSION 1.3.0
BEGIN;
-CREATE OR REPLACE FUNCTION employee_save
-(in_id integer, in_employeenumber varchar(32),
- in_salutation int, in_first_name varchar(64), in_last_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 int, 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 $$
-DECLARE
- e_id int;
- e entity;
- loc location;
- l_id int;
- per person;
- p_id int;
-BEGIN
- select * into e from entity where id = in_id and entity_class = 3;
-
- if found then
+
+CREATE OR REPLACE FUNCTION employee_save(
+ in_person int, in_entity int, in_startdate date, in_enddate date,
+ in_role text, in_sales boolean, in_dob date,
+ in_managerid integer, in_employeenumber text
+)
+returns int AS $$
+
+ DECLARE
+ e_ent entity_employee;
+ e entity;
+ p person;
+ BEGIN
+ select * into e from entity where id = in_entity and entity_class = 3;
- select l.* into loc from location l
- left join person_to_location ptl on ptl.location_id = l.id
- left join person p on p.id = ptl.person_id
- where p.entity_id = in_id;
+ IF NOT FOUND THEN
+ RAISE EXCEPTION 'No entity found for ID %', in_id;
+ END IF;
- select * into per from person p where p.entity_id = in_id;
+ select * into p from person where id = in_person;
- update location
- set
- line_one = in_address1,
- line_two = in_address2,
- city_province = in_city,
- mail_code = in_zipcode,
- country_id = in_country
- where id = loc.id;
-
- UPDATE employee
- SET
- employeenumber = in_employeenumber,
- startdate = in_startdate,
- enddate = in_enddate,
- role = in_role,
- sales = in_sales,
- ssn = in_ssn,
- dob = in_dob,
- managerid = in_managerid
- WHERE entity_id = in_id;
-
- update entity_note
- set
- note = in_note
- where entity_id = in_id;
-
- UPDATE entity_bank_account
- SET
- bic = in_bic,
- iban = in_iban
- WHERE entity_id = in_id;
-
- UPDATE person
- SET
- salutation_id = in_salutation,
- first_name = in_first_name,
- last_name = in_last_name
- WHERE entity_id = in_id;
+ IF NOT FOUND THEN
+ RAISE EXCEPTION 'No person found for ID %', in_id;
+ END IF;
- UPDATE person_to_contact
- set
- contact = in_homephone
- WHERE person_id = per.id
- AND contact_class_id = 11;
-
- UPDATE person_to_contact
- set
- contact = in_workphone
- WHERE person_id = per.id
- AND contact_class_id = 1;
-
- UPDATE person_to_contact
- set
- contact = in_email
- WHERE person_id = per.id
- AND contact_class_id = 12;
+ -- Okay, we're good. Check to see if we update or insert.
- return in_id;
+ select * into e_ent from entity_employee where person_id = in_person
+ and entity_id = in_entity;
+
+ IF NOT FOUND THEN
+ -- insert.
+
+ INSERT INTO entity_employee (person_id, entity_id, startdate,
+ enddate, role, sales, manager_id, employeenumber, dob)
+ VALUES (in_person, in_entity, in_startdate, in_enddate, in_role,
+ in_sales, in_managerid, in_employeenumber, in_dob);
+
+ return in_entity;
+ ELSE
- ELSIF NOT FOUND THEN
- -- first, create a new entity
- -- Then, create an employee.
-
- e_id := in_id; -- expect nextval entity_id to have been called.
- INSERT INTO entity (id, entity_class, name) VALUES (e_id, 3, in_first_name||' '||in_last_name);
-
- INSERT INTO entity_bank_account (entity_id, iban, bic)
- VALUES (e_id, in_iban, in_bic);
-
- p_id := nextval('person_id_seq');
- insert into person (id, salutation_id, first_name, last_name, entity_id)
- VALUES
- (p_id, in_salutation, in_first_name, in_last_name, e_id);
-
- if in_notes is not null then
- insert into entity_note (note_class, note, ref_key, vector)
- values (1, in_notes, e_id, '');
- END IF;
-
- insert into person_to_contact (person_id, contact_class_id, contact)
- VALUES (p_id, 1, in_workphone); -- work phone #
- insert into person_to_contact (person_id, contact_class_id, contact)
- VALUES (p_id, 11, in_homephone); -- Home phone #
- insert into person_to_contact (person_id, contact_class_id, contact)
- VALUES (p_id, 12, in_email); -- email address.
-
- INSERT INTO employee
- (employeenumber, startdate, enddate,
- role, sales, ssn,
- dob, managerid, entity_id, entity_class_id)
- VALUES
- (in_employeenumber, in_startdate, in_enddate,
- in_role, in_sales, in_ssn,
- in_dob, in_managerid, e_id, 3);
-
- l_id := nextval('location_id_seq');
- insert into location (id, location_class, line_one, line_two, city_province, country_id, mail_code)
- VALUES (
- l_id,
- 1,
- in_address1,
- in_address2,
- in_city,
- in_country,
- in_zipcode
- );
- insert into person_to_location (person_id, location_id)
- VALUES (p_id, l_id);
-
- return e_id;
- END IF;
-END;
-$$ LANGUAGE 'plpgsql';
+ -- update
+
+ UPDATE entity_employee
+ SET
+ startdate = in_startdate,
+ enddate = in_enddate,
+ role = in_role,
+ sales = in_sales,
+ manager_id = in_managerid
+ employeenumber = in_employeenumber,
+ dob = in_dob
+ WHERE
+ entity_id = in_entity
+ AND
+ person_id = in_person;
+
+ return in_entity;
+ END IF;
+ END;
+$$ language 'plpgsql';
+
+create view employees as
+ select
+ e.salutation,
+ e.first_name,
+ e.last_name,
+ ee.*
+ FROM entity e
+ JOIN entity_employees ee on e.id = ee.entity_id
+ where e.entity_class = 3;
+
+
-- why is this like this?
CREATE OR REPLACE FUNCTION employee_get
(in_id integer)
-returns employee as
+returns employees as
$$
DECLARE
- emp employee%ROWTYPE;
+ emp employees%ROWTYPE;
BEGIN
- SELECT * INTO emp FROM employees WHERE id = in_id;
+ SELECT
+ e.salutation,
+ e.first_name,
+ e.last_name,
+ ee.*
+ INTO emp
+ FROM employees ee
+ join entity e on ee.entity_id = e.id
+ WHERE ee.entity_id = in_id;
+
RETURN emp;
END;
$$ language plpgsql;
CREATE OR REPLACE FUNCTION employee_list_managers
(in_id integer)
-RETURNS SETOF employee as
+RETURNS SETOF employees as
$$
DECLARE
- emp employee%ROWTYPE;
+ emp employees%ROWTYPE;
BEGIN
FOR emp IN
- SELECT * FROM employee
- WHERE sales = '1' AND role='manager'
- AND entity_id <> coalesce(in_id, -1)
+ SELECT
+ e.salutation,
+ e.first_name,
+ e.last_name,
+ ee.*
+ FROM entity_employee ee
+ JOIN entity e on e.id = ee.entity_id
+ WHERE ee.sales = 't'::bool AND ee.role='manager'
+ AND ee.entity_id <> coalesce(in_id, -1)
ORDER BY name
LOOP
RETURN NEXT emp;
@@ -196,7 +141,7 @@
SELECT e.*, em.name AS manager, emn.note, en.name as name
FROM employee e
LEFT JOIN entity en on (e.entity_id = en.id)
-LEFT JOIN employee m ON (e.managerid = m.entity_id)
+LEFT JOIN entity_employee m ON (e.managerid = m.entity_id)
LEFT JOIN entity em on (em.id = m.entity_id)
LEFT JOIN entity_note emn on (emn.ref_key = em.id);
@@ -228,4 +173,17 @@
return;
END;
$$ language plpgsql;
-COMMIT;
\ No newline at end of file
+
+create or replace function employee_set_location
+ (in_employee int, in_location int)
+returns void as $$
+
+ INSERT INTO person_to_location (person_id,location_id)
+ VALUES (in_employee, in_location);
+
+ SELECT NULL;
+
+$$ language 'sql';
+
+COMMIT;
+
Added: trunk/sql/modules/Entity.sql
===================================================================
--- trunk/sql/modules/Entity.sql (rev 0)
+++ trunk/sql/modules/Entity.sql 2007-10-17 23:16:35 UTC (rev 1778)
@@ -0,0 +1,41 @@
+--
+BEGIN;
+
+CREATE OR REPLACE FUNCTION entity_save(
+ in_entity_id int, in_name text, in_entity_class INT
+) RETURNS INT AS $$
+
+ DECLARE
+ e entity;
+ e_id int;
+
+ BEGIN
+
+ select * into e from entity where id = in_entity_id;
+
+ IF NOT FOUND THEN
+ -- do the insert magic.
+ e_id = nextval('entity_id_seq');
+ insert into entity (id, name, entity_class) values
+ (e_id,
+ in_name,
+ in_entity_class
+ );
+ return e_id;
+
+ ELSIF FOUND THEN
+
+ update
+ entity
+ SET
+ name = in_name
+ entity_class = in_entity_class
+ WHERE
+ id = in_entity_id;
+ return in_entity_id;
+ END IF;
+ END;
+
+$$ language 'plpgsql';
+
+commit;
\ No newline at end of file
Added: trunk/sql/modules/Person.sql
===================================================================
--- trunk/sql/modules/Person.sql (rev 0)
+++ trunk/sql/modules/Person.sql 2007-10-17 23:16:35 UTC (rev 1778)
@@ -0,0 +1,52 @@
+begin;
+
+CREATE OR REPLACE FUNCTION person_save
+
+(in_id integer, in_salutation int,
+in_first_name text, in_last_name text
+)
+RETURNS INT AS $$
+
+ DECLARE
+ e_id int;
+ e entity;
+ loc location;
+ l_id int;
+ per person;
+ p_id int;
+ BEGIN
+
+ select * into e from entity where id = in_id and entity_class = 3;
+
+ IF NOT FOUND THEN
+ RAISE EXCEPTION 'No entity found for ID %', in_id;
+ END IF;
+
+ select * into per FROM person WHERE entity_id = in_id;
+
+ IF FOUND THEN
+
+ -- do an update
+
+ UPDATE person SET
+ salutation = in_salutation,
+ first_name = in_first_name,
+ last_name = in_last_name
+ WHERE
+ entity_id = in_id
+ AND
+ id = per.id;
+
+ ELSE
+
+ -- Do an insert
+
+ INSERT INTO person (salutation, first_name, last_name) VALUES
+ (in_salutation, in_first_name, in_last_name);
+
+
+ END IF;
+
+$$ language plpgsql;
+
+commit;
\ No newline at end of file
Modified: trunk/sql/modules/admin.sql
===================================================================
--- trunk/sql/modules/admin.sql 2007-10-17 21:12:50 UTC (rev 1777)
+++ trunk/sql/modules/admin.sql 2007-10-17 23:16:35 UTC (rev 1778)
@@ -1,3 +1,12 @@
+begin;
+
+create table lsmb_roles (
+
+ user_id integer not null references users,
+ role text not null
+
+);
+
CREATE OR REPLACE FUNCTION admin_add_user_to_role(in_user TEXT, in_role TEXT) returns INT AS $$
declare
@@ -19,7 +28,7 @@
RAISE EXCEPTION 'Cannot grant permissions to a non-existant user.';
END IF;
- stmt := 'GRANT '|| in_role ||' to '|| in_user;
+ stmt := 'GRANT '|| quote_ident(in_role) ||' to '|| quote_ident(in_user);
EXECUTE stmt;
@@ -49,7 +58,7 @@
RAISE EXCEPTION 'Cannot revoke permissions from a non-existant user.';
END IF;
- stmt := 'REVOKE '|| in_role ||' FROM '|| in_user;
+ stmt := 'REVOKE '|| quote_ident(in_role) ||' FROM '|| quote_ident(in_user);
EXECUTE stmt;
@@ -79,7 +88,7 @@
RAISE EXCEPTION 'Cannot grant permissions to a non-existant user.';
END IF;
- stmt := 'GRANT EXECUTE ON FUNCTION '|| in_func ||' to '|| in_role;
+ stmt := 'GRANT EXECUTE ON FUNCTION '|| quote_ident(in_func) ||' to '|| quote_ident(in_role);
EXECUTE stmt;
@@ -109,7 +118,7 @@
RAISE EXCEPTION 'Cannot revoke permissions from a non-existant function.';
END IF;
- stmt := 'REVOKE EXECUTE ON FUNCTION '|| in_func ||' FROM '|| in_role;
+ stmt := 'REVOKE EXECUTE ON FUNCTION '|| quote_ident(in_func) ||' FROM '|| quote_ident(in_role);
EXECUTE stmt;
@@ -147,7 +156,7 @@
raise exception 'Cannot add unknown permission';
END IF;
- stmt := 'GRANT '|| in_perm|| 'ON TABLE '|| in_table ||' to '|| in_role;
+ stmt := 'GRANT '|| quote_ident(in_perm) || 'ON TABLE '|| quote_ident(in_table) ||' to '|| quote_ident(in_role);
EXECUTE stmt;
@@ -171,8 +180,6 @@
RAISE EXCEPTION 'Cannot revoke permissions of a non-existant role.';
END IF;
- SELECT table_schema, table_name from
-
select table_name into a_table from information_schema.tables
where table_schema NOT IN ('information_schema','pg_catalog','pg_toast')
and table_type='BASE TABLE'
@@ -182,7 +189,7 @@
RAISE EXCEPTION 'Cannot revoke permissions from a non-existant table.';
END IF;
- stmt := 'REVOKE '|| in_role ||' FROM '|| in_user;
+ stmt := 'REVOKE '|| quote_literal(in_role) ||' FROM '|| quote_literal(in_user);
EXECUTE stmt;
@@ -191,18 +198,19 @@
$$ language 'plpgsql';
-create or replace function admin_get_user(in_user TEXT) returns setof user as $$
+create or replace function admin_get_user(in_user TEXT) returns setof users as $$
DECLARE
- a_user user;
+ a_user users;
BEGIN
- select * into a_user from user where username = in_user;
+ select * into a_user from users where username = in_user;
IF NOT FOUND THEN
RAISE EXCEPTION 'cannot find user %', in_user;
END IF;
- return a_user;
+ return next a_user;
+ return;
END;
$$ language plpgsql;
@@ -211,13 +219,13 @@
declare
u_role lsmb_roles;
- a_user user;
+ a_user users;
begin
select * into a_user from admin_get_user(in_user);
- FOR u_role IN select * from lsmb_roles WHERE user = a_user.id LOOP
+ FOR u_role IN select * from lsmb_roles lr WHERE lr.user_id = a_user.id LOOP
- RETURN NEXT a_role;
+ RETURN NEXT u_role;
END LOOP;
RETURN;
@@ -227,82 +235,55 @@
CREATE OR REPLACE FUNCTION admin_save_user(
in_id int,
+ in_entity_id INT,
in_username text,
- in_password TEXT,
- in_dbname TEXT,
- in_host TEXT,
- in_port TEXT
+ in_password TEXT
) returns int AS $$
DECLARE
- a_user user;
- v_entity_id int;
+ a_user users;
+ v_user_id int;
p_id int;
l_id int;
stmt text;
BEGIN
- select * into a_user from user where id = in_id;
+ select * into a_user from users lu where lu.id = in_id;
IF NOT FOUND THEN
-- Insert cycle
- --- First, create an entity.
+ --- The entity is expected to already BE created. See admin.pm.
if admin_is_user(in_username) then
-- uhm, this is bad.
- RAISE EXCEPTION
- "Fatal exception: Username already exists in Postgres; not
- a valid lsmb user.";
- end if;
-
- v_entity_id := nextval('entity_id_seq');
-
- INSERT INTO entity (id, name, entity_class) VALUES (
- v_entity_id,
- in_first_name || ' ' || in_last_name,
- 3
- );
-
+ RAISE EXCEPTION 'Fatal exception: Username already exists in Postgres; not
+ a valid lsmb user.';
+ end if;
-- create an actual user
- insert into users (name, entity_id) VALUES (
+
+ v_user_id := nextval('users_id_seq');
+ insert into users (id, name, entity_id) VALUES (
+ v_user_id,
in_username,
- v_entity_id
+ in_entity_id
);
-
- insert into user_connection (entity_id, database, host, port)
- VALUES (
- v_entity_id,
- in_database,
- in_host,
- in_port
- );
-
+
-- Finally, issue the create user statement
- stmt := $$CREATE USER $$||in_username||$$WITH ENCRYPTED PASSWORD '$$||in_password||$$;'$$;
+ stmt := 'CREATE USER ' || quote_ident( in_username ) || ' WITH ENCRYPTED PASSWORD ' || quote_literal (in_password);
execute stmt;
- return v_entity_id;
+ return v_user_id ;
ELSIF FOUND THEN
-- update cycle
- -- Only update if it's changed. Wewt.
- UPDATE entity SET name = in_first_name || ' ' || in_last_name
- WHERE entity_id = a_user.entity_id and
- name <> in_first_name || ' ' || in_last_name;
-
- stmt := $$ alter user $$ || in_username || $$ with encrypted password $1$$$ || in_password || $$$1$ $$;
+ stmt := ' alter user '|| quote_ident(in_username) || ' with encrypted password ' || quote_literal(in_password);
execute stmt;
-
- update user_connection set database = in_database, host = in_host, port = in_port
- where database <> in_database
- OR host <> in_host
- OR port <> in_port;
-
+
return a_user.id;
END IF;
@@ -341,7 +322,7 @@
stmt text;
BEGIN
- stmt := 'create role '||in_dbname||'_lsmb_$$' || in_group_name || '$$;';
+ stmt := 'create role '|| quote_ident(quote_ident(in_dbname) || '_lsmb_' || quote_ident(in_group_name) );
execute stmt;
return 1;
END;
@@ -352,17 +333,17 @@
DECLARE
stmt text;
- a_user user;
+ a_user users;
BEGIN
select * into a_user from users where username = in_username;
IF NOT FOUND THEN
- raise exception "User not found.";
+ raise exception 'User not found.';
ELSIF FOUND THEN
- stmt := $$ drop user $$ || a_user.username ||;
+ stmt := ' drop user ' || quote_ident(a_user.username);
execute stmt;
-- also gets user_connection
@@ -378,7 +359,7 @@
Drops the provided user, as well as deletes the entity and user configuration data.
$$;
-CREATE OR REPLACE FUNCTION admin_delete_group (in_group_name TEXT) returns bool as $$
+CREATE OR REPLACE FUNCTION admin_delete_group (in_dbname TEXT, in_group_name TEXT) returns bool as $$
DECLARE
stmt text;
@@ -390,14 +371,14 @@
if not found then
return 'f'::bool;
else
- stmt := 'drop role $dbname_lsmb_$$' || in_group_name || '$$;';
+ stmt := 'drop role ' || quote_ident(in_dbname || '_lsmb_' || in_group_name);
execute stmt;
return 't'::bool;
end if;
END;
$$ language 'plpgsql';
-comment on function admin_delete_group(text) IS $$
+comment on function admin_delete_group(text,text) IS $$
Deletes the input group from the database. Not designed to be used to
remove a login-capable user.
$$;
@@ -425,8 +406,10 @@
-- TODO: Add admin user
-CREATE OR REPLACE FUNCTION admin_audit_log () returns int as $$
+--CREATE OR REPLACE FUNCTION admin_audit_log () returns int as $$
-$$ language plpgsql;
\ No newline at end of file
+--$$ language plpgsql;
+
+commit;
\ No newline at end of file
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.