Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

Integrated user database

Quote Reply
Integrated user database
Ok.

I think I catch what some people were saying about trying to move databases to external areas.

The problem is that in the current Links code, a database handle can be grabbed from anywhere, and it's used for all different purposes.

For instance, if you grab a handle to access the Users table '/defs/Users.def'
that handle is also used for accessing the sessions table, and Editors, etc.

_THIS_ is where I think people have hit problems with the databases.

With my mods, I've moved to actually grabbing a database handle for each of the tables I want to use. I've avoided this -- until I tried to move the users tables out to a central database.

What's happening, is that you _really_ should grab a database handle for _each_ table you want to access, and not take short cuts, unless you have a reason... such as the tables _MUST_ be in the same database -- forever.

In this case, I want (need) the Users table to be a central database, but the Editors, sessions, and other features to be in the local table. I may find the Sessions table is better off in the USERS database, since it's an attribute of the 'User' object, but for now, with the cookies, it needs to be 'site specific'. "Editor" status is by "site", since it's not a true attribute of the "user" object,but is tied directly to that user entering a site. (this can be argued... certainly :) )

But, I thought I'd post this, since I think it may answer some of the problems people have had trying to move databases, and why things don't seem to work right.

It's really better to grab a $USER, $CATDB, $LINKDB, $VALDB,etc object and use those to properly access each table/object. (You don't _have_ to, but confusion ensues).

To use a single sessions table, outside of the main database, it would seem the following values (taken from my updated mod, not the stock routines) would have to be upgraded:

Code:
my $server_cookie_name = '.' . "$LINKS{server_name_or_ip}";

my $session_cookie = $in->cookie (
-name => 's',
-value => $session,
-expires => '+3h',
-path => '/',
-domain => $server_cookie_name
);
my $user_cookie = $in->cookie (
-name => 'Username',
-value => $user,
-expires => '+1y',
-path => '/',
-domain => $server_cookie_name
);
my $server_cookie_name

Would have to turned into a routine, that would parse the $LINKS{server_name_or_ip} variable on whitespace, and then add the '.' to each token, and recreate the variable (or, just do it right the first time in the Links.pm file<G>)

But, the passed values would have to be: ".server1.com .server2.com .server3.com" for each of the servers that is part of the "allowed" network.

It gets a little complicated, but once set up, it should work without much fuss. One user database, and the cookie contains each of the allowed server names.

Anyway, I'm working on moving the user management to a separate database, and might have it working tonight, depending on how many places I have to do the "User" code stuff in.

If anyone has hit any other problems, please pass them on :)

But, I thought I'd post about the database handles thing, since that might be what's been biting some people unawares.

PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: Integrated user database In reply to
I believe the codes posted in the Two Databses Thread in the Links SQL Question Forum are also needed to connect/disconnect from multiple databases.

I also found that you do need to hack some of the subroutines in the DBSQL.pm, including:

sub get_record

I also had to hack the Admin_HTML.pm module to handle back-end admin processes, such as validating records into appropriate tables. I also had to edit the admin.cgi script to call the appropriate .def files from different databases...basically a simple matter of adding conditional if/elsif/else statements to call the appropriate .def file and in turn, the correct table in the correct database.

Regards,

Eliot Lee
Quote Reply
Re: Integrated user database In reply to
I'm going to try to pull all of those into one record in the FAQ. If anyone else has messages that helped on this, please tag them here.

What I'm doing here is a bit different than the Links problems, since I'm not really using any of the routines (such as build, validate, etc). It's actually simpler :) . I'm just trying to separate out the user validations. Fortunately these only occur in a few places, and for logged in users :)

I wonder if the new code is "cleaner" in that respect ie: individual handles for connections.

PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: Integrated user database In reply to
New code??? What new code? Do you mean in NG Links SQL? I really don't know...but the codes provided in the Thread I referenced does seem to help with connecting/disconnecting multiple databases.

Regards,

Eliot Lee
Quote Reply
Re: Integrated user database In reply to
Yeah, the new links code. I haven't had time to sart with the details... I've barely had time to go through and look at the features :) But, I did notice there are a few more database handles grabbed.

The biggest thing I hope to see, over all, with the NG links and modules, is a consistent way of behaving across routines, modules and eventually the plug ins.

PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: Integrated user database In reply to
Ok,

Here's an example of what I mean. This is from DB_Utils.pm, and is where the $USER variable is "filled in" in the routines.

Code:
sub authenticate {
# --------------------------------------------------------
# Make sure the current session is valid and gets the user info.
#
my $s = shift;
$s =~ /^[\d\w]+$/ || return undef;
(length $s < 26) || return undef;
if (! $CATDB) {
$CATDB = new Links::DBSQL $LINKS{admin_root_path} . "/defs/Category.def";
}
my $query = qq!
SELECT Users.*
FROM Users, Sessions
WHERE Users.Username = Sessions.Username AND
Sessions.ID = '$s'
!;
my $sth = $CATDB->prepare ($query);
$sth->execute();
my $user = undef;
if ($sth->rows) {
$user = $sth->fetchrow_hashref;
}
return $user;
}
Here, the database is connected to, using the information in the Category.def table, and that table is not even used in this routine. It was convenient, but since "User" data was being selected, shouldn't the program have grabbed the "User.def" file?

If the connection grabbed the "Users.def" file, then the connection would have been set up to the right database, or at least it's a start.

Anyway... this is probably my last hurrah with Links SQL 1.1x, before moving up. I want to get the editor/password/logon stuff really sorted away.


PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: Integrated user database In reply to
Wow....

Fixing the &authenticate routine seems to have fixed the problem!

I changed the def to /defs/Users.def

With the "DATABASE" being pointed to as my central users database.

I edited the Sessions.def file too, just in case.

Then, I fixed the Links.pm file on each of the sites to include the authorized domains for that cookie.

I've been able to bounce between the CreepyCards.com, DigitalPostcards.com and PostcardsPortal.com sites from editor to editor, logged in area to logged in area, without having to grab a new sessions ID. :)

I'm soooooo happy!

Right now registered users really can't do anything except add/modify on the PostcardsPortal, so if anyone wants to test things out, you need to register on either CreepyCards.com or DigitalPostcards.com, then try to add a site to the PostcardsPortal.com site.

I'm going to smooth over everything, try to move a few more domains to the central registry, and if it works, then I'll release the updated Editor/logon code since a few bug fixes and such have been applied (mostly to the templates).


PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: Integrated user database In reply to
Very nice! I am sure that people who are using multiple Links databases on multiple sites/domains will find this useful. For me...I only have two "domains"...one is a virtual sub-domain, but all the session/client cookies work fine between them...haven't noticed any problems...

Regards,

Eliot Lee
Quote Reply
Re: Integrated user database In reply to
A little update:

Seems you don't have to update the server_cookie names.

The program checks for the Username/Sessions data, then assigns a cookie for the current server. So, what happens, or seems to happen, is you get several cookies with the same session data.

I would like to say "cool!" but i'm waiting to get bit by it :)

Anyway, having one centralized system is the first major step to the commerce system, since the network really needs to have one unique Username/ID per customer/vendor. It starts to get tricky if you have more than that. Even email addresses start to lose it with people having multiple addresses, and switching ISP's so quickly (average is 3x a year still).

Next step is to integrate this with w3t, so there is _one_ UserID/Password/Username across the whole system. That way if someone signs up in any area, they are automatically signed up across the network. Forum/Cards/Links/etc.



PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: Integrated user database In reply to
Couple things you may or may not have thought about:

1) Deleting associated records

I have this working in the Delete Account option that allows users to delete their account. When they do this, the following associated records, their Site Reviews are deleted. The following records are updated with the super administrator Userid:

a) Banners
b) Links
c) Job Postings
d) Resume Postings

etc...

2) Multiple User Status

I am working on an AKO structure that will include separate "User" tables called Status and User_Status. In the former table, there is are two fields:

ID
Status

The Status field is ENUM with the following values:

a) Administrator
b) Super Administrator
c) Editor
d) Registered
e) Employer

The latter table includes the following fields:

ID
UserID
StatusID

The StatusID is pulled from the Status table. This allows for users to have multiple statuses and avoids the problems with keeping the Status field in the Users table.

I have not worked out all the kinks yet...but basically, the sub authenticate routine is going to have to be re-written to query the User_Status table in addition to the Users and Sessions tables.

I don't know if this helps or if this going to be implemented in the NG version of Links SQL...I have not uploaded it since I am waiting for the finally BETA version before playing with it. Wink

Regards,

Eliot Lee
Quote Reply
Re: Integrated user database In reply to
Ok,

A bit more work with the "logic" of how this is done, and I think I'm solving a lot of problems. Users complained they could not edit their links, so I investigated, and after an hour or so, came up with a working solution that imposes some "overhead" for a log-on and authentication, but on my site there is only a limited amount of time a person would spend in the "authenticated" realm, so this overhead is acceptible (adding a site, modifying a site, joining, or editing).

Ok... back to DB_Utils.pm

In the &authenticate routine, you see that $CATDB is used to log-on to the Users.def database.

This is the root of all evil :)

As Alex explained in another thread, DBSQL.pm maintains a persistent connection, and MySQL can only work with one database at a time, so the problem comes from trying to access two databases at a time. In my other scripts, I did it sequentially.

So.... We need a way to grab the $USER information, then make sure that every other call to the databases is to the "local" database that we _think_ we are using.

The "solution" is to edit the &authenticate sub a bit more, so that it disconnects from a database if it is connected, connects to the User.def database, grabs the user information, and session information (or writes it) and then disconnects.

User.cgi is the only "program" that only needs toconnect to the User/Sessions databases. The other programs call &authenticate and then use the local database.

So..... for me, I was able to (finally) figure out that if I changed the $CATDB stuff in &authenticate to use $USERDB, then... it changes to:

Code:
sub authenticate {
# --------------------------------------------------------
# Make sure the current session is valid and gets the user info.
#
my $s = shift;
$s =~ /^[\d\w]+$/ || return undef;
(length $s < 26) || return undef;

if ($Links::DBSQL::DBH) {
$Links::DBSQL::DBH->disconnect; ### needs a fresh connection
undef $Links::DBSQL::DBH;
}

$USERDB = new Links::DBSQL $LINKS{admin_root_path} . "/defs/Users.def";

my $query = qq!
SELECT Users.*
FROM Users, Sessions
WHERE Users.Username = Sessions.Username AND
Sessions.ID = '$s'
!;
my $sth = $USERDB->prepare ($query);
$sth->execute();
my $user = undef;
if ($sth->rows) {
$user = $sth->fetchrow_hashref;
}

$Links::DBSQL::DBH->disconnect;
undef $Links::DBSQL::DBH;

return $user;
}
What happens, is that if there is already a connection, it's dropped, and $USERDB is assigned a database connection to the /defs/Users.def database.

Then, after the $USER record is put into $user, we disconnect from this database, so any _future_ connections are to the 'local' database as you expect it.

Now, I have this working on one site, and need to move this upgrade to the others, but it _is_ working.

The reason this works, is that the check for $USER occurs only at the beginning of each phase or "submit" of a form. Each later action uses the other tables.

Now, there _is_ going to be a problem with this in the admin.cgi when you try to get a list of Editors... but I think I have a quick solution for that as well.














PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: Integrated user database In reply to
Thanks, pugdog...I always wondered why $CATDB was being used as the DBSQL object in that subroutine. I applied the codes you gave and didn't notice any difference...the login/logout seem to work the same, unless there is something in the back-end that I am overlooking.

Regards,

Eliot Lee
Quote Reply
Re: Integrated user database In reply to
I've beat this pretty extensively, and more important, I ran grep a few hundred times, trying to find any areas where I might get caught.

It seems that once you "authenticate" and grab the $USER hash, you can re-set the DBSQL.pm to use the "local" tables.

As I said, there will be a problem in the admin.cgi trying to pair up editors, since in one select a join between the Users and Editors tables is done. I can think of a way to rearrange that, simply, as long as there are not too many editors. Might require a more robust solution on some sites, but I hope to have a solution for sites with only a few dozen or so editors in a day or two. Mostly, it's testing out the features without bringing my sites down <G>

I wonder how this is handled in the next version. (this sort of investigation is beyond me at this juncture <G>) If the new model doesn't allow multiple database connections (ie: different databases in the same "session") There are going to be scaleability problems. Hopefully, the design of the next version will inherently and explicitly link the "database" to the DBH object that is grabbed. That way, you can have a handle to Database1 and Database2 within the same process. Part of this is a MySQL problem, in that you can only connect to one database at a time, yet, you can query across them, if you explicitly name them.

I say this, because sites will need (or want) to run GossMail and Links, and who knows what else... and each program will need to be in separate databases, for maintainability and security. It would be a real shame to have to put all the tables into one database...that would be a huge, disorganized mess :)

PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: Integrated user database In reply to
Ohh...

I think I missed the point of your post.

<G> if you do it right you shouldn't notice any difference! That's the point.

But, next time you try to use different databases, you won't hit the "authenticate/sessions" problem, or "database.table doesn't exist" error, because the $USER is grabbed, then disconnected, and the next connect is at the "local" level via whatever /defs/table.def you pass in.

For what you are doing, it would get complicated, since you need to connect, disconnect, several times to different databases, perhaps each trying to use data from the other.

But, the nice thing about the $USER data, is that it's a self-contained object. It's obtained, and verified only once per "run" (click, submit, process, whatever). After that, it's passed around and around, until the next time the user initiates an action and it's re-verified, but the program never looks back at the "table" data for the information.

Thank goodness!!! :)

In your site, Eliot, you've already worked around most of the problems. Anyone else, will just be hitting them.

By the end of the weekend, I should have a new editor/password log on zip released, that includes this, and allows moving the user information out of the Links database.

The next step in this evolution, is making it compatible with w3t.

According to Alex, the new version will be able to handle things like that, and I'm only vaguely aware of how. But, I really can't start to test it too heavily until the next release with import scripts.



PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: Integrated user database In reply to
In Reply To:
I say this, because sites will need (or want) to run GossMail and Links, and who knows what else... and each program will need to be in separate databases, for maintainability and security. It would be a real shame to have to put all the tables into one database...that would be a huge, disorganized mess :)
I agree...I currently have four databases running with an average of 10 tables per database, Links, of course, having about 25 tables. I am in the process of moving the Users, Sessions, and Bookmarks into another database for easier management.

Regards,

Eliot Lee
Quote Reply
Re: Integrated user database In reply to
Okay, pugdog...I have run into client-side incompatiblity issues regarding cookies, I believe....

I have tried your codes with both IE 5.0 and Netscape 4.073 and it works fine in Netscape, but NOT in IE...the login page keeps re-loading, but the "sessions" are created.

Did you change anything in the authentication codes in the other .cgi scripts?

Here is what I have tried:

1) Installed your codes.
2) Cleared out all cookies in both Netscape and IE.
3) Cleared all Sessions.
4) Tried logging in...problem occurred with IE.

Then I did the following:

1) De-installed your codes and restored the DB_Utils.pm file.
2) Cleared all cookies.
3) Cleared all sessions.
4) Logged in...same problem.

Any thoughts???

It is NOT a huge deal since 70% of my web visitors use Netscape, 20% IE, and 10% other (Opera, Lynx, etc.).

But it would be nice that my scripts were cross-browser compatible.

The weird thing I was able to replicate this problem via three different computers (One Mac and two PCs using IE 4.0 and 5.0 and Netscape 4.06 and 4.73) at my office.

Regards,

Eliot Lee
Quote Reply
Re: Integrated user database In reply to
Here's the weirdest thing.... I use MSIE, the current version. I just upgraded, after I wrote those codes, (Windows auto update notification).

I have not had any problems with those cookies.

I have even logged in as my users, on different sites than the user logged in on, to make changes to their records -- and no cookie problems with MSIE.

I really haven't tried it with NN.

If you want, I can send you the DB_Utils file and you can diff it on yours.

I try to keep a record of all my changes. All my files have been modified with the previous changes in the editor/password log on stuff, but I don't think that would make too much effect.


PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: Integrated user database In reply to
That would be great...I would appreciate it.

Thanks.

Regards,

Eliot Lee
Quote Reply
Re: Integrated user database In reply to
Weird! I tested the script some more today...and I found out that the login worked fine from the ERROR screen, but not the main login screen via IE 5.0.

But when I copied the codes (similar to the codes in the ERROR template file), the login worked again...

Weird!

Regards,

Eliot Lee