Hi,
Jian wanted to run Turbine in such a way that each user had his own
connection to the database.
My suggestion would be to develop multiple database connection pool
support in Turbine.
(This is not a complaint, and I am not in a position to work on it
right now. It is a collection of thoughts for those on the list
to consider.)
It is non uncommon for DBA's to want to control the security of their
database at the database level. i.e. they want to make certain tables
read-only for certain classes of users, etc.
They do this by creating roles, granting/revoking the appropriate
privileges to them, and then assigning these roles to users
(very similarly to Turbine's own security model).
Data security (permissions set on the database) is more secure
because no bug in the software application can get around it.
Application security is nicer because then
1. you can use connection pools,
2. you have a finer level of control on what gets secured,
3. and you don't have to maintain a list of users in the application
*and* a list of users in the database (just the userid(s)
associated with the connection pools).
There is at least one other reason to use multiple connection pools
(and various application server software packages have implemented them).
Multiple connection pools allows you to segment the user base into
prioritized classes of users (i.e. users, management, anonymous).
You may have 30 day-to-day users with a pool of 10 connections,
50 possible managers in different departments throughout the company
who occasionally need a report with a pool of 3 connections, and
an unlimited universe of anonymous users with a pool of 5 connections.
This means that if the number of anonymous internet users spikes up,
they have to wait on each other for a connection, while the normal
users are unimpacted by the spike because they draw from their own
pool of connections.
So, the proposal is:
* multiple connection pool capability added to Turbine
(i.e. pools 0..n, where 0 is the default)
* add a mapping from the Turbine VisitorRole to a pool number
(in the VisitorRole table?)
( POOL_NUMBER integer NOT NULL default 0, )
(This schema addition would not break any existing code.)
* the connection pool that a user gets a connection from is
specified by the role that the user is assigned in the
application (or the max(VisitorRole.POOL_NUMBER) because
he may be assigned to many roles).
The signature of getConnection would not change
(i.e. db = DBBroker.getInstance().getConnection( dbName );).
Inside DBBroker, however would be the logic to get the
connection from the right pool.
* the min/max connections, userid/password would all be
assignable independently to the different connection pools
in the properties file
Turbine would be enhanced and Jian could run it the way he wants to.
(He would have to create a role per user. Configure a different
pool for each role. Configure the min/max connections as 0/1 for
each pool and the userid/password appropriately.)
my $0.02.
Stephen
At 08:51 AM 6/15/00 -0700, you wrote:
>Sounds good, but it is very application specific. I think the most apps
>would prefer the connection pool be smaller than the total number of current
>users. I would say add it to the FAQ, but I'm not sure its working. Let me
>know if you feel I am wrong.
>
>John McNally
>
>----- Original Message -----
>From: Jian He <[EMAIL PROTECTED]>
>To: Turbine <[EMAIL PROTECTED]>
>Sent: Thursday, June 15, 2000 4:35 AM
>Subject: Re: DBFactory Patch
>
>
>> ÔÚ Èý, 14 6Ô 2000 , ÄãдÁË:
>> > What is your reason for wanting a db connection associated with a
>particular
>> > login?
>>
>> My corparate is going to developing a business system on
>Linux+Oracle+Tomcat.
>> There is a lot of vouchers and lists and reports in a account,
>> and a lot of accounts in a oracle database.
>> every user open a account must give the oracle username and password.
>>
>> if i open every account with the "system" username, i must write the
>dynamic sql:
>> StringBuffer sql = new StringBuffer();
>> sql.append( "SELECT * FROM " );
>> sql.append( username );
>> sql.append( ".voucher" );
>>
>> but if i open a account with the particular username, i can write the
>static sql:
>> String sql = "SELECT * FROM voucher";
>>
>> so, i must open a connection with a particular login. :)
>>
>> >
>> > ----- Original Message -----
>> > From: Jian He <[EMAIL PROTECTED]>
>> > To: Turbine <[EMAIL PROTECTED]>
>> > Sent: Wednesday, June 14, 2000 12:08 AM
>> > Subject: DBFactory Patch
>> >
>> >
>> > > Hi
>> > >
>> > > when i run DBFactory.getInstance().getConnection(driver, url, user1,
>> > pass1) to get the first connection,
>> > > it gives me the right connection.
>> > > but when i run DBFactory.getInstance().getConnection(driver, url,
>user2,
>> > pass2) to get the second connection,
>> > > it also gives me the first connection, it is not the right connection.
>> > >
>> > > so i changed the DBFactory, ConnectionPool, DBConnection classes to
>solve
>> > the problem:
>> > >
>> > > bash-2.03$ diff old/DBBroker.java DBBroker.java
>> > > 150c150,153
>> > > < url = url.trim();
>> > > ---
>> > > > StringBuffer key = new StringBuffer();
>> > > > key.append( url.trim() );
>> > > > key.append( ':' );
>> > > > key.append( username.trim() );
>> > > 152c155
>> > > < if ( pools.containsKey(url) )
>> > > ---
>> > > > if ( pools.containsKey( key ))
>> > > 154c157
>> > > < pool = (ConnectionPool) pools.get(url);
>> > > ---
>> > > > pool = (ConnectionPool) pools.get( key );
>> > > 159c162
>> > > < pools.put( url, pool );
>> > > ---
>> > > > pools.put( key, pool );
>> > > 259c262,263
>> > > < if (pools.containsKey( url ))
>> > > ---
>> > > > String key = url + ":" + connection.getUsername();
>> > > > if (pools.containsKey( key ))
>> > > 261c265
>> > > < ConnectionPool pool = (ConnectionPool)
>> > ools.get( url );
>> > > ---
>> > > > ConnectionPool pool = (ConnectionPool)
>> > ools.get( key );
>> > >
>> > > bash-2.03$ diff old/ConnectionPool.java ConnectionPool.java
>> > > 175c175
>> > > < return new DBConnection( db.getConnection(), url );
>> > > ---
>> > > > return new DBConnection( db.getConnection(), url,
>username );
>> > >
>> > > bash-2.03$ diff old/DBConnection.java DBConnection.java
>> > > 77a78,79
>> > > > /** The username of this connection. */
>> > > > private String username = "";
>> > > 86c88
>> > > < protected DBConnection(Connection connection, String url)
>> > > ---
>> > > > protected DBConnection(Connection connection, String url, String
>> > username)
>> > > 89a92
>> > > > this.username = username;
>> > > 114a118,121
>> > > > }
>> > > > public String getUsername()
>> > > > {
>> > > > return username;
>> > >
>> > >
>> > > ------------------------------------------------------------
>> > > To subscribe: [EMAIL PROTECTED]
>> > > To unsubscribe: [EMAIL PROTECTED]
>> > > Problems?: [EMAIL PROTECTED]
>> >
>> >
>> >
>> > ------------------------------------------------------------
>> > To subscribe: [EMAIL PROTECTED]
>> > To unsubscribe: [EMAIL PROTECTED]
>> > Problems?: [EMAIL PROTECTED]
>>
>>
>> ------------------------------------------------------------
>> To subscribe: [EMAIL PROTECTED]
>> To unsubscribe: [EMAIL PROTECTED]
>> Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
>> Problems?: [EMAIL PROTECTED]
>>
>
>
>
>------------------------------------------------------------
>To subscribe: [EMAIL PROTECTED]
>To unsubscribe: [EMAIL PROTECTED]
>Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
>Problems?: [EMAIL PROTECTED]
>
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
Problems?: [EMAIL PROTECTED]
