Re: [Serusers] Help for mysql to ser 2.0.0. rc1

The entire database structure of SER 2.0 has changed somewhat drastically. The subscriber table no longer exists. ALL user attributes are now stored in the usr_attrs table in classic single attribute/value pair model. I have somewhat mixed impressions about the new schema. It has some logical points for storing system code, but for DB management, it's a bit nightmarish. Taking the sheer strength of a complex, well-optimised relational database and essentially breaking it down into container for a bunch of attribute value pairs is... well... painful to look at. ;) If you want to learn more about the new DB structure, it's here: http://www.iptel.org/files/db-structure-0.10.pdf On the positive (?) side, the new method of storing usr_attrs allows for a dynamic level of user attributes without adjusting the table itself. On the negative side, it makes for a complete impossibility for having more than the two predefined (attribute/value) primary keys, or for checking any sort of referential integrity against anything other than an attribute or a value. Code you write surrounding the new DB (and I know there's this general feeling that the DB is for the back-end of SER and the back-end of SER ONLY, and that any sort of front-end code should go in a different DB, but that's just 100% silly) will have to create joins and other complex structures in order to grasp most of the necessary data for a user, as well as sort through multiple rows of data for each user instead of gathering data in a tidy little row. You will also have to keep careful track of the attributes you've allotted somewhere, as otherwise, your code is going to be a horrific mess if you ever DO change anything, since it won't be just a simple table structure change, it will be a change in the way you handle all the data grabbed from the table. Expect your usr_attrs table to grow FAR faster than your subscriber table did, and plan for space accordingly. Also expect to redo any optimisations you've written for how you sort through the code, as there will be a considerably larger amount of code to sort through per user, so optimisation will be more difficult on the front-end side. On the bright side, expect to be able to handle added features with much greater ease if you code your front-end software correctly. No longer will you have to decide whether a landline phone number or privacy preference gets tied to a subscriber in the subscriber table, or gets tied to a subscriber in the user_preferences table (although, to be honest, I don't think anyone ever did that). It will all go in one table, referenced by a unique (although not primary-keyed (?)) userID. On the not so bright side, expect migration to SER 2.0 from your old DB scheme and front-end codebase to be a nightmare and several halves. :) Liu Wenlong wrote: