[asterisk-dev] app_directory realtime

[Ken Ouellette]

Instead of a complex query, my thought was adding a column to the
required database schema that would contain the dialpad equivalent of
the last three letters of a user's last name. This column could be
indexed and accessed via a simple query:

SELECT * FROM voicemail_table WHERE dialpadlastname = '532' AND
context='foo';

/kwo
-----Original Message-----
From: asterisk-dev-bounces at lists.digium.com
[mailto:asterisk-dev-bounces at lists.digium.com] On Behalf Of Tilghman
Lesher
Sent: Thursday, April 27, 2006 1:22 AM
To: Asterisk Developers Mailing List
Subject: Re: [asterisk-dev] app_directory realtime

On Wednesday 26 April 2006 10:40, Ken Ouellette wrote:
> Is there any reason why the appropriate subset of users couldn't be
> retrieved after the user was prompted? Given a reasonable
> distribution in the first three letters of user's last names, this
> should result in a much smaller result set being returned.

Theoretically, yes, but unfortunately, the way we'd have to structure
a single query is not currently a way that we support with the ODBC
abstraction.  We'd have to create a new method that supports doing an
OR between multiple parameters (yet still let most parameters be an
AND.  For example, with app_directory, if the first number pushed were
2, the resulting query would be:

SELECT * FROM voicemail_table WHERE (fullname LIKE '% A%' OR fullname
LIKE '% B%' OR fullname LIKE '% C%') AND context='foo';

That assumes that all names are properly capitalized in the database,
which may or may not matter (doesn't matter on MySQL, matters on
virtually every other database).  Also note that due to the structure
of this query, you couldn't use an index, which means that on a busy
database, it won't matter whether we select all rows or just a subset,
as it will incur the same load.

While we could force in a single query like this, it's not good
abstraction, especially considering that we'd like to support other
realtime backends, such as LDAP, in the future.  If we encode a long
SQL string in the API, we'd have to do a very complex translation on
that query for the LDAP driver.

If you'd like to attempt an abstraction to the Realtime subsystem
that would support such a complex query without directly encoding the
whole query as a single string, we'd be happy to see such a patch on
the bugtracker.  However, as I've outlined the reasons here, this is
not a simple task.  This is why we haven't done it already.  ;-)

-- 
Tilghman
_______________________________________________
--Bandwidth and Colocation provided by Easynews.com --

asterisk-dev mailing list
To UNSUBSCRIBE or update options visit:
   http://lists.digium.com/mailman/listinfo/asterisk-dev