Terrible terrible database schema!

I’d like to file a bug to get a new database schema but had a couple of quick and easy questions for some who might be more knowledgeable than me in regards to the Asterisk side of things. (My background is all PHP and MySQL – I’ve been developing a couple of modules for our company lately.)

  • Are there any cases where an extension might not be numeric?
  • What’s the difference between devices and users? Can one exist without the other, and if so, under what circumstances?

I think that’s all I need to know. Basically I’d just like to see some primary keys added, some uniqueness constraints, very basic and elementary data integrity stuff. Nothing that wouldn’t be backwards compatible.

yes there is a lot of really bad schema in there and as you dig further you will probably find it gets worse:-( It has grown organically over a long period of time, and with contributions from a very wide range of sources and experience levels and with very little scrutiny early on.

As far as extensions being non-numeric, today no, in the future it’s possible. (Technically, phones can dial 0-9,A-D - in addition to the world of VoIP which can dial anything).

As far as devices being non-numeric, the constraints in FreePBX are somewhat artificial. We should let them be non-numeric (e.g. a MAC for instance) but we have restricted it only because of the unknowns through out the code base that have always assumed they would be numeric.

As far as devices and users, they are completely different and either one can exists without the other, as well as a single user can be associated with multiple devices. (And this can be fixed or on demand by logging into a device). A device is just that, a device. (usually a phone). A user is someone on the system. You call users, you can’t call a device (other then special circumstances such as in paging a device). The fact that a user is associated with a device, (permanently or logged in at the moment) is what makes the device ring.

To get a better understanding, switch FreePBX into devicesanduser mode (see amportal.conf).

Thanks for the quick response; this sounds like something you guys are already aware of, so is there a need (or a point) to file a bug on it? Some things like indexing and uniqueness constraints could be done without altering the data at all, and other things like adding a primary key field should be harmless as well.


So long as the suggested changes are consistent with MySQL 4 and 5, and Sqlite, you are welcome to file a ticket where you think some changes should be introduced, or discuss it here, or start a wiki page to discuss it.

We’ve been chipping away each release at little things that have been more problematic and haven’t really bothered yet on some of the fundamentals (although in a lot of cases, you are right and they are easy wins). For example, modules that use system recordings were storing the recording file name instead of the Recording ID. So if you went and changed a recording, it was not changed int he modules that used it … That was fixed in 2.5 making changes to all module that were guilty of that - that is just one example of some of the bigger offenders that we have tried to hit.

I have test your case, and it’s ok to have non-numeric extension. I create some configuration files for channels based on FreePBX configuration, and then add entries into astDB, and nothing irregular happen, except the dialplan seem too ‘crappy’ because there’s alot of place alphabetic won’t do…
For DID, you must keep it numeric.
For extension, you must change both devices and users table, in MySQL and AMPUSER & DEVICE tree in astDB.
All other things work the same.

In my systems I use numbers which contain 0-9 ,* and #. The * is no problem but significant effort is needed to get # working in asterisk. Firstly pedantic mode must be enabled in sip.conf. This gets chan_sip.c to escape the # character and recognise an escaped # character as required by the SIP standard. Unfortunately the implementation of this escaping in chan_sip.c is incomplete and many sip header fields are left unescaped. Therefore in practise much manual hacking of chan_sip.c is necessary to get a solution that works correctly. I have not looked at asterisk 1.6 so perhaps the situation is somewhat improved. Even so unless you are very comfortable modifying chan_sip.c I would recommend that you avoid using the # in dialled numbers if possible