Re: UU code

["Alexey Parshin" <alexeyp@gmail.com>]

Stored procedures is the only way to modify most of our data. However, most of the data can be read by anyone, so you are not limited on selects. So, unless user just reads the data, the user session should always start from login.

2007/3/7, sergey@total-knowledge.com <sergey@total-knowledge.com>:

Basically it means that _only_ stored procedures can be used in model
class' operations in order to modify/access DB data, they have to be
written as generic as possible.
Also stored procedures may be used in not-login-protected pages on UI and
it means that not every DB session should start from calling
login(username, password) stored proc.
See below for more...

> In PostgreSQL, there is now session variables. Our only possibility is to
> use temporary table(s). Every database session should start from calling
> login(username,password) stored proc. After successful authentication the
> login information is stored and all the stored procs  would use it
> automatically. This way, we can allow/disallow modifications of data on
> row
> level. So, if we manage to keep the database connection after the login
> page
> - there is now need for keeping login information. Currently, we can store
> username and password this way. Let me know if we need more session
> variables.

Maybe user's first and last names too for now would be nice to have.

Actually, there is user id in that table that points to person_list(pl_id). You can get all the user data this way.
Just for a shortcut (for error messages, etc) I've added user first/last names there.
The temp table is created as:

    CREATE TEMP TABLE session_info(
      si_person int,
      si_person_name varchar(40)
    );

and is only available for read (for anyone but db owner). It should always contain just one record. The repeatable logins overwrite that record.

-- 
Alexey Parshin,
http://www.sptk.net