Description: ------------ Using version 0.11.1 of LiveUser. Various database inconsistencies seem to be apparent: 1) LOTS of inconsistencies with the "ID" field used as primary key in many tables. Please decide whether this field is int(10) or int(11) and be consistent within all tables. There are problems even in tables which refer to the same entities. For example: liveuser_areas defines ID as: area_id int(11) yet, liveuser_areas_seq defines ID as: id int(10). liveuser_right_scopes: right_id int(11) liveuser_rights: right_id int(10) May lead to truncation issues in some cases. liveuser_perm_users defines perm_user and auth_user differently. perm_user utilizes an int as the primary key. auth_user utilizes a varchar. The admin class method addUser supposedly will add a user to both tables with the same id. How does this work or make sense considering the two different datatypes? 2) The "x_define_name" column. Sometimes it is varchar(20), sometimes it is varchar(32). 3) - Seems to be a problem with the SQL used to build the liveuser_translations table. We needed to modify two indexes to make them non-unique. Modify the following two indexes on the translations table to be non-unique: - uc_ltranslations_section_id - uc_translations_section_type

I partially fixed those issues in CVS. INT(11) i used everywhere now. Define names are 32 characters long. About auth_user_id an perm_user_id this is not inconsistent. LiveUser design allows you to have any number of authentication containers. Since you probably won't have control over, say the company employee database, the field type is varchar because there is no control of the container's user id. I didn't get the problem about the translations table.

1) Arnaud already addressed the issues. Generally there seems to be a misunderstanding in how the auth_user_id and perm_user_id work. The perm_user_id is the unique identifier for a user, just as the auth_user_id along with an auth_container_name is a unique identifier for a user. These two identifier are mapped to eachother in the liveuser_perm_users table. Remember that LiveUser also supports LDAP etc. That is why the liveuser_perm_users table needs to define auth_user_id as a varchar. The auth container also defines auth_user_id in the liveuser_users table as a varchar just to make life easier for people migrating over. However since the admin classes uses a sequence to generate auth_user_id's you can safely modify this to int(11) in liveuser_users. However if you would change this in liveuser_perm_users you will run into trouble if you start using other auth containers (like LDAP etc). 2) Arnaud seems to have fixed that one 3) I also dont quite understand your concerns on the liveuser_translations table. There is currently only a single primary key index on that table which looks quite alright to me

RE: Translations table. The primary key is fine. However, it appears that individual indexes were also added on the same two columns used within the primary key. - uc_ltranslations_section_id - uc_translations_section_type These indexes are defined as unique. This would appear to only allow one record with each section id and/or section type. This quickly broke when I added an application, area, and rights. Perhaps I misunderstand?

An addtional comment regarding database schema: The primary key for the liveuser_users table currently consists of auth_user_id and handle. I suggest changing the primary key to only consist of the auth_user_id. Rationale: 1) auth_user_id should always be unique, by itself. It is, by definition, a unique ID. 2) Many websites allow the user to use their email address as their login ID. This has a couple benefits. One, it is easier for the user to remember. Two, it allows for greater number of easily remembered login ID. For example, there may be many John Smiths. But, using UserIds (not emails) only the first lucky contestant gets to use jsmith as a UserId. The subsequent tries must use somthing like "jsmith02" etc... "Email as UserId" alleviates this. If you agree that using email as the LiveUser handle is a good idea, there is a problem. Currently, handle is part of the primary key. Many databases do not allow you to change a primary key value. Something to think about.

How about something like this? PRIMARY KEY auth_user_id (`auth_user_id`), UNIQUE KEY `handle` (`handle`)

Works for me.

This bug has been fixed in CVS. In case this was a documentation problem, the fix will show up at the end of next Sunday (CET) on pear.php.net. In case this was a pear.php.net website problem, the change will show up on the website in short time. Thank you for the report, and for helping us make PEAR better.