[asterisk-dev] [design] Realtime changes

[Atis Lezdins]

On Mon, Jan 5, 2009 at 7:47 PM, Tilghman Lesher
<tilghman at mail.jeffandtilghman.com> wrote:
> On Wednesday 31 December 2008 15:07:11 Tilghman Lesher wrote:
>> Once again, I am proposing some realtime changes, this time in the quest
>> to make voicemail ODBC storage completely dependent upon realtime, instead
>> of containing its own set of ODBC commands.  This may eventually allow us
>> to make even IMAP a realtime backend of sorts.
>>
>> The two APIs that I am proposing adding are as follows:
>>
>> ast_copy_realtime("family",
>>       <criteria>, SENTINEL,
>>       <field-list>, SENTINEL,
>>       <new-fields>, SENTINEL)
>>
>> where <criteria> is a set of pairs of data, with at least one pair
>> specified, detailing the criteria for selection of a row to copy;
>> <field-list> is a list of fields to copy directly; and <new-fields> is a
>> set of data pairs, specifying field names and values to insert along with
>> the fields that are being copied.  For SQL-based databases, this command
>> roughly translates to:
>> INSERT INTO "family" (<field-list>,<new-fields-names>) SELECT
>> <field-list>,<new-fields-values> FROM "family" WHERE <criteria>;
>> where <new-fields> are separated into their respective field names and
>> field values.
>>
>> ast_blobupdate_realtime("family",
>>       <criteria-pairs>, SENTINEL,
>>       <field-name>, <blob-value>, <blob-len>, SENTINEL)
>>
>> This should allow us to store Binary Large OBjects into the realtime
>> database without needing to worry about embedded NULLs in the data.
>>
>> Anybody feel strongly that I should do either of these a different way?
>
> Here's the issue for this second method.  Currently, when we store a
> voicemail, we do so with a single operation, which specifies a binary type
> for the blob field and text types for all other fields.  With this change,
> we'd be doing 2 operations for each storage of a voicemail.  First, a realtime
> store, which sets the main parameters of the voicemail (duration, origtime,
> etc.) and a second blobupdate to actually store the binary audio.
>
> I toyed with the idea of making a second store method, where the parameters
> are specified in a set of 4 each, specifying name, value, type, and length,
> but I'm not wild about that idea, either.
>

Actually this type implementation should be better, because Asterisk
could pass integer values as integer (for example timestamp in
queue_log, regseconds in sip_peers), thus MySQL would automatically
translate them to DATETIME forth and back as necessary. So, no more
unixtime in db.

As i recall, i played with this a while ago, and it was either not
working or slowing everything down because of string->integer
conversions.. Sorry, don't remember the case now and haven't yet
installed SQL browser after HDD crash :p

Regards,
Atis

-- 
Atis Lezdins,
VoIP Project Manager / Developer,
IQ Labs Inc,
atis at iq-labs.net
Skype: atis.lezdins
Cell Phone: +371 28806004
Cell Phone: +1 800 7300689
Work phone: +1 800 7502835