Hi All,<br>This is specifically for the SER/OpenSER developers, but I'm not a serdev list member so I'm posting here.<br><br>I've been using SER since version 0.8.X and I'm still running 0.8.14 production for my company PBX to this day.
<br><br>I was very excited as version 2 became a release candidate and I downloaded it for testing. I was pretty disappointed with one aspect of the new data model and I'm requesting that the developers consider a further revision on the data model.
<br><br>Basically, taking all fields out of the subscriber table like Last_name, first_name, email, timezone, rpid/asserted identity, etc, etc is not the greatest idea. It's a better idea from a database architecture and performance perspective to keep adding columns into that table for data that has a 1 to 1 relationship with a user, and that is common in > 90% of SER's use cases (
i.e. corporate, carrier/VSP.) I would suggest adding voicemail_password, and maybe every other field that is being added into the default attributes script that I saw in CVS recently. If you already know what attributes a user has (and they have a 1 to 1 relationship), then its far better from a db performance perspective to keep these attributes in the user table. I know that the code becomes more complicated, but I think it may be a tradeoff worth discussing.
<br><br>See this discussion ( <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056">http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056</a> ) between Oracle users and Tom, (an Oracle engineer/architect.) The full text of this discussion is very informative and I highly recommend people read it through.
<br><br>Tom's conclusion is that the type of data model being discussed, and now being used in SER fails for all but the most trivial of applications. Maybe SER *by itself* qualifies as "trivial" from a database architect's perspective, but think about things like Asterisk integration, which is quite common. You quickly run into some very nasty queries . . .
<br><br>Please note that I am not a software developer nor a database engineer, just a user who reads a lot, so I'm open to being the ignorant one here, but I thought that this should be discussed among users and developers.
<br><br>Thanks for considering,<br>Mahatma<br><br><a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056">http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056
</a> <br><br>The following is an excerpt from the above link:<br><br><pre>Here is a excerpt from my forthcoming book where I talk about this (and show you how <br>ugly, hard and inefficient queries against your very flexible model will be)
<br><br><br><br>(2)Do not use Generic Data Models<br><br>Frequently I see applications built on a generic data model for "maximum flexibility" or <br>applications built in ways that prohibit performance. Many times - these are one in the
<br>same thing! For example, it is well known you can represent any object in a database <br>using just four tables:<br><br>Create table objects ( oid int primary key, name varchar2(255) );<br><br>Create table attributes
<br>( attrId int primary key, attrName varchar2(255), <br>datatype varchar2(25) );<br><br>Create table object_Attributes <br>( oid int, attrId int, value varchar2(4000), <br>primary key(oid,attrId) );<br><br>Create table Links ( oid1 int, oid2 int,
<br>primary key (oid1, oid2) ); <br><br><br>That's it - no more CREATE TABLE for me! I can fill the attributes table up with rows <br>like this:<br><br>insert into attributes values ( 1, 'DATE_OF_BIRTH', 'DATE' );
<br>insert into attributes values ( 2, 'FIRST_NAME', 'STRING' );<br>insert into attributes values ( 3, 'LAST_NAME', 'STRING' );<br>commit; <br><br><br>And now I'm ready to create a PERSON record:
<br><br>insert into objects values ( 1, 'PERSON' );<br>insert into object_Attributes values( 1, 1, '15-mar-1965' );<br>insert into object_Attributes values( 1, 2, 'Thomas' );<br>insert into object_Attributes values( 1, 3, 'Kyte' );
<br>commit;<br><br>insert into objects values ( 2, 'PERSON' );<br>insert into object_Attributes values( 2, 1, '21-oct-1968' );<br>insert into object_Attributes values( 2, 2, 'John' );<br>insert into object_Attributes values( 2, 3, 'Smith' );
<br>commit; <br><br>And since I'm good at SQL, I can even query this record up to get the FIRST_NAME and <br>LAST_NAME of all PERSON records:<br><br>ops$tkyte@ORA920> select <br> max( decode(attrName, 'FIRST_NAME', value, null )) first_name,
<br> 2 max( decode( attrName, 'LAST_NAME', value, null ) ) last_name<br> 3 from objects, object_attributes, attributes<br> 4 where attributes.attrName in ( 'FIRST_NAME', 'LAST_NAME' )<br>
5 and object_attributes.attrId = attributes.attrId<br> 6 and object_attributes.oid = objects.oid<br> 7 and <a href="http://objects.name">objects.name</a> = 'PERSON'<br> 8 group by objects.oid<br>
9 /<br><br>FIRST_NAME LAST_NAME<br>-------------------- --------------------<br>Thomas Kyte<br>John Smith<br><br><br>Looks great, right? I mean, the developers don't have to create tables anymore, we can
<br>add columns at the drop of a hat (just requires an insert into the ATTRIBUTES table). The <br>developers can do whatever they want and the DBA can't stop them. This is ultimate <br>"flexibility". I've seen people try to build entire systems on this model.
<br><br>But, how does it perform? Miserably, terribly, horribly. A simple "select first_name, <br>last_name from person" query is transformed into a 3-table join with aggregates and all. <br>Further, if the attributes are "NULLABLE" - that is, there might not be a row in
<br>OBJECT_ATTRIBUTES for some attributes, you may have to outer join instead of just joining <br>which in some cases can remove more optimal query plans from consideration. <br><br>Writing queries might look pretty straightforward, but it's impossible to do in a
<br>performant fashion. For example, if we wanted to get everyone that was born in MARCH or <br>has a LAST_NAME = 'SMITH', we could simply take the query from above and just wrap an <br>inline view around that:<br>
<br><br>ops$tkyte@ORA920> select *<br> 2 from (<br> 3 select <br> max(decode(attrName, 'FIRST_NAME', value, null)) first_name,<br> 4 max(decode(attrName, 'LAST_NAME', value, null)) last_name,
<br> 5 max(decode(attrName, 'DATE_OF_BIRTH', value, null)) <br> date_of_birth<br> 6 from objects, object_attributes, attributes<br> 7 where attributes.attrName
in ( 'FIRST_NAME', <br> 'LAST_NAME', 'DATE_OF_BIRTH' )<br> 8 and object_attributes.attrId = attributes.attrId<br> 9 and object_attributes.oid = objects.oid
<br> 10 and <a href="http://objects.name">objects.name</a> = 'PERSON'<br> 11 group by objects.oid<br> 12 )<br> 13 where last_name = 'Smith'<br> 14 or date_of_birth like '%-mar-%'
<br> 15 /<br><br>FIRST_NAME LAST_NAME DATE_OF_BIRTH<br>-------------------- -------------------- --------------------<br>Thomas Kyte 15-mar-1965<br>John Smith 21-oct-1968
<br><br>So, it looks "easy" to query, but think about the performance! If you had a couple <br>thousand OBJECT records, and a couple tens of thousands of OBJECT_ATTRIBUTES - Oracle <br>would have to process the entire inner group by query first and then apply the WHERE
<br>clause. <br><br>This is not a made up data model, one that I crafted just to make a point. This is an <br>actual data model that I've seen people try to use. Their goal is ultimate flexibility. <br>They don't know what OBJECTS they need, they don't know what ATTRIBUTES they will have.
<br>Well - that is what the database was written for in the first place: Oracle implemented <br>this thing called SQL to define OBJECTS and ATTRIBUTES and lets you use SQL to query <br>them. You are trying to put a generic layer on top of a generic layer - and it fails each
<br>and every time except for the most trivial of applications.<br> </pre> <br>