[asterisk-dev] [Code Review] 2731: Add database schema management using Alembic
Russell Bryant
reviewboard at asterisk.org
Thu Aug 1 12:44:00 CDT 2013
On Aug. 1, 2013, 8:51 a.m., Russell Bryant wrote:
> > But I too favor the old SQL files over an extra Alembic dependency and less readable files.
> >
> > If however using Alembic makes it easier to update the in-tree SQL files, I could see some use for it there.
>
> Russell Bryant wrote:
> The primary benefit is in schema migration. When Asterisk N+1 gets released, and you need to update your existing installation with some additional columns. You re-run alembic and it gets everything up to date for you.
>
> The secondary benefit is maintaining the schema in one place, as opposed to duplicated for each database (and completely out of sync or non-existent for some of them).
>
> It can generate the SQL files, as well. See the bottom of the README.
>
> Tilghman Lesher wrote:
> How about if, as a release task, we generate the SQL files and package them? Since it can upgrade existing installations, there's a use for having it in the tarball, but those who are unfamiliar (as newbies tend to be), can have the simple SQL files to reference.
>
> Russell Bryant wrote:
> What value does having the SQL files provide? Just so you don't have to install the dependencies to get your db created? Having both seems weird. If folks are uncomfortable with the dependency, I think I'd rather just drop it.
>
> Matt Jordan wrote:
> A couple observations:
>
> (1) The schemas between MySQL and PostgreSQL are often disjoint. This causes confusion.
> (2) We've had folders for Oracle and SQLServer that have been empty for... I don't know how long. Probably a long time. (Yes, Oracle. Yes, SQLServer. People do still use those however.)
> (3) We (as in the "collective Asterisk Developer Community We") are typically bad at remembering to update the realtime schemas when we make a change.
> (3.1) As a corollary, when we do remember to update the schemas, we often forget to do all of the supported schemas.
> (3.2) As a further corollary, when we do remember to update the supported schemas, we often forget to note the change in the CHANGES/UPGRADE files.
>
> Anything that helps alleviate *any* of the above points I'm a fan of. As a speculative bonus, we may be able to also produce wiki-able documentation for the schemas for a particular version as well - which is also nifty.
>
> I like this patch - it makes developer's lives easier and reduces the the number of places things have to be updated when a schema change is needed.
>
> Tilghman's concern about not having generated SQL files is justifiable however - not everyone will have Alembic, remember to run it, understand how to run it, or generally "get" what is going on. While I don't think we need the SQL files in the branches or even the tags, updating the release scripts to produce SQL files for the tarballs and/or packages is relatively minor and could be done for Asterisk 12. I'm okay with that.
>
> Tilghman Lesher wrote:
> +1
>
> That was the point I was trying to get across, and Matt expressed it a lot better than I did.
Cool, fine with me. It's easy enough to do.
As an exmaple, for MySQL:
$ alembic -c config.ini upgrade head --sql
INFO [alembic.migration] Context impl MySQLImpl.
INFO [alembic.migration] Generating static SQL
INFO [alembic.migration] Will assume non-transactional DDL.
CREATE TABLE alembic_version (
version_num VARCHAR(32) NOT NULL
);
INFO [alembic.migration] Running upgrade None -> 4da0c5f79a9c, Create tables
-- Running upgrade None -> 4da0c5f79a9c
CREATE TABLE sippeers (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(40) NOT NULL,
ipaddr VARCHAR(45),
port INTEGER,
regseconds INTEGER,
defaultuser VARCHAR(40),
fullcontact VARCHAR(80),
regserver VARCHAR(20),
useragent VARCHAR(20),
lastms INTEGER,
host VARCHAR(40),
type ENUM('friend','user','peer'),
context VARCHAR(40),
permit VARCHAR(95),
deny VARCHAR(95),
secret VARCHAR(40),
md5secret VARCHAR(40),
remotesecret VARCHAR(40),
transport ENUM('udp','tcp','udp,tcp','tcp,udp'),
dtmfmode ENUM('rfc2833','info','shortinfo','inband','auto'),
directmedia ENUM('yes','no','nonat','update'),
nat VARCHAR(29),
callgroup VARCHAR(40),
pickupgroup VARCHAR(40),
language VARCHAR(40),
disallow VARCHAR(200),
allow VARCHAR(200),
insecure VARCHAR(40),
trustrpid ENUM('yes','no'),
progressinband ENUM('yes','no','never'),
promiscredir ENUM('yes','no'),
useclientcode ENUM('yes','no'),
accountcode VARCHAR(40),
setvar VARCHAR(200),
callerid VARCHAR(40),
amaflags VARCHAR(40),
callcounter ENUM('yes','no'),
busylevel INTEGER,
allowoverlap ENUM('yes','no'),
allowsubscribe ENUM('yes','no'),
videosupport ENUM('yes','no'),
maxcallbitrate INTEGER,
rfc2833compensate ENUM('yes','no'),
mailbox VARCHAR(40),
`session-timers` ENUM('accept','refuse','originate'),
`session-expires` INTEGER,
`session-minse` INTEGER,
`session-refresher` ENUM('uac','uas'),
t38pt_usertpsource VARCHAR(40),
regexten VARCHAR(40),
fromdomain VARCHAR(40),
fromuser VARCHAR(40),
qualify VARCHAR(40),
defaultip VARCHAR(45),
rtptimeout INTEGER,
rtpholdtimeout INTEGER,
sendrpid ENUM('yes','no'),
outboundproxy VARCHAR(40),
callbackextension VARCHAR(40),
timert1 INTEGER,
timerb INTEGER,
qualifyfreq INTEGER,
constantssrc ENUM('yes','no'),
contactpermit VARCHAR(95),
contactdeny VARCHAR(95),
usereqphone ENUM('yes','no'),
textsupport ENUM('yes','no'),
faxdetect ENUM('yes','no'),
buggymwi ENUM('yes','no'),
auth VARCHAR(40),
fullname VARCHAR(40),
trunkname VARCHAR(40),
cid_number VARCHAR(40),
callingpres ENUM('allowed_not_screened','allowed_passed_screen','allowed_failed_screen','allowed','prohib_not_screened','prohib_passed_screen','prohib_failed_screen','prohib'),
mohinterpret VARCHAR(40),
mohsuggest VARCHAR(40),
parkinglot VARCHAR(40),
hasvoicemail ENUM('yes','no'),
subscribemwi ENUM('yes','no'),
vmexten VARCHAR(40),
autoframing ENUM('yes','no'),
rtpkeepalive INTEGER,
`call-limit` INTEGER,
g726nonstandard ENUM('yes','no'),
ignoresdpversion ENUM('yes','no'),
allowtransfer ENUM('yes','no'),
dynamic ENUM('yes','no'),
path VARCHAR(256),
supportpath ENUM('yes','no'),
PRIMARY KEY (id),
UNIQUE (name)
);
CREATE INDEX sippeers_name ON sippeers (name);
CREATE INDEX sippeers_name_host ON sippeers (name, host);
CREATE INDEX sippeers_ipaddr_port ON sippeers (ipaddr, port);
CREATE INDEX sippeers_host_port ON sippeers (host, port);
CREATE TABLE iaxfriends (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(40) NOT NULL,
type ENUM('friend','user','peer'),
username VARCHAR(40),
mailbox VARCHAR(40),
secret VARCHAR(40),
dbsecret VARCHAR(40),
context VARCHAR(40),
regcontext VARCHAR(40),
host VARCHAR(40),
ipaddr VARCHAR(40),
port INTEGER,
defaultip VARCHAR(20),
sourceaddress VARCHAR(20),
mask VARCHAR(20),
regexten VARCHAR(40),
regseconds INTEGER,
accountcode VARCHAR(20),
mohinterpret VARCHAR(20),
mohsuggest VARCHAR(20),
inkeys VARCHAR(40),
outkeys VARCHAR(40),
language VARCHAR(10),
callerid VARCHAR(100),
cid_number VARCHAR(40),
sendani ENUM('yes','no'),
fullname VARCHAR(40),
trunk ENUM('yes','no'),
auth VARCHAR(20),
maxauthreq INTEGER,
requirecalltoken ENUM('yes','no','auto'),
encryption ENUM('yes','no','aes128'),
transfer ENUM('yes','no','mediaonly'),
jitterbuffer ENUM('yes','no'),
forcejitterbuffer ENUM('yes','no'),
disallow VARCHAR(200),
allow VARCHAR(200),
codecpriority VARCHAR(40),
qualify VARCHAR(10),
qualifysmoothing ENUM('yes','no'),
qualifyfreqok VARCHAR(10),
qualifyfreqnotok VARCHAR(10),
timezone VARCHAR(20),
adsi ENUM('yes','no'),
amaflags VARCHAR(20),
setvar VARCHAR(200),
PRIMARY KEY (id),
UNIQUE (name)
);
CREATE INDEX iaxfriends_name ON iaxfriends (name);
CREATE INDEX iaxfriends_name_host ON iaxfriends (name, host);
CREATE INDEX iaxfriends_name_ipaddr_port ON iaxfriends (name, ipaddr, port);
CREATE INDEX iaxfriends_ipaddr_port ON iaxfriends (ipaddr, port);
CREATE INDEX iaxfriends_host_port ON iaxfriends (host, port);
CREATE TABLE voicemail (
uniqueid INTEGER NOT NULL AUTO_INCREMENT,
context VARCHAR(80) NOT NULL,
mailbox VARCHAR(80) NOT NULL,
password VARCHAR(80) NOT NULL,
fullname VARCHAR(80),
alias VARCHAR(80),
email VARCHAR(80),
pager VARCHAR(80),
attach ENUM('yes','no'),
attachfmt VARCHAR(10),
serveremail VARCHAR(80),
language VARCHAR(20),
tz VARCHAR(30),
deletevoicemail ENUM('yes','no'),
saycid ENUM('yes','no'),
sendvoicemail ENUM('yes','no'),
review ENUM('yes','no'),
tempgreetwarn ENUM('yes','no'),
operator ENUM('yes','no'),
envelope ENUM('yes','no'),
sayduration INTEGER,
forcename ENUM('yes','no'),
forcegreetings ENUM('yes','no'),
callback VARCHAR(80),
dialout VARCHAR(80),
exitcontext VARCHAR(80),
maxmsg INTEGER,
volgain NUMERIC(5, 2),
imapuser VARCHAR(80),
imappassword VARCHAR(80),
imapserver VARCHAR(80),
imapport VARCHAR(8),
imapflags VARCHAR(80),
stamp DATETIME,
PRIMARY KEY (uniqueid)
);
CREATE INDEX voicemail_mailbox ON voicemail (mailbox);
CREATE INDEX voicemail_context ON voicemail (context);
CREATE INDEX voicemail_mailbox_context ON voicemail (mailbox, context);
CREATE INDEX voicemail_imapuser ON voicemail (imapuser);
CREATE TABLE meetme (
bookid INTEGER NOT NULL AUTO_INCREMENT,
confno VARCHAR(80) NOT NULL,
starttime DATETIME,
endtime DATETIME,
pin VARCHAR(20),
adminpin VARCHAR(20),
opts VARCHAR(20),
adminopts VARCHAR(20),
recordingfilename VARCHAR(80),
recordingformat VARCHAR(10),
maxusers INTEGER,
members INTEGER NOT NULL,
PRIMARY KEY (bookid)
);
CREATE INDEX meetme_confno_starttime_endtime ON meetme (confno, starttime, endtime);
CREATE TABLE musiconhold (
name VARCHAR(80) NOT NULL,
mode ENUM('custom','files','mp3nb','quietmp3nb','quietmp3'),
directory VARCHAR(255),
application VARCHAR(255),
digit VARCHAR(1),
sort VARCHAR(10),
format VARCHAR(10),
stamp DATETIME,
PRIMARY KEY (name)
);
INSERT INTO alembic_version (version_num) VALUES ('4da0c5f79a9c');
[rbryant at x220 ast-db-manage(master)]$ alembic -c voicemail.ini upgrade head --sql
INFO [alembic.migration] Context impl MySQLImpl.
INFO [alembic.migration] Generating static SQL
INFO [alembic.migration] Will assume non-transactional DDL.
CREATE TABLE alembic_version (
version_num VARCHAR(32) NOT NULL
);
INFO [alembic.migration] Running upgrade None -> a2e9769475e, Create tables
-- Running upgrade None -> a2e9769475e
CREATE TABLE voicemail_messages (
dir VARCHAR(255) NOT NULL,
msgnum INTEGER NOT NULL,
context VARCHAR(80),
macrocontext VARCHAR(80),
callerid VARCHAR(80),
origtime INTEGER,
duration INTEGER,
recording BLOB,
flag VARCHAR(30),
category VARCHAR(30),
mailboxuser VARCHAR(30),
mailboxcontext VARCHAR(30),
msg_id VARCHAR(40)
);
ALTER TABLE voicemail_messages ADD CONSTRAINT voicemail_messages_dir_msgnum PRIMARY KEY (dir, msgnum);
CREATE INDEX voicemail_messages_dir ON voicemail_messages (dir);
INSERT INTO alembic_version (version_num) VALUES ('a2e9769475e');
- Russell
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviewboard.asterisk.org/r/2731/#review9270
-----------------------------------------------------------
On Aug. 1, 2013, 4:12 a.m., Russell Bryant wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviewboard.asterisk.org/r/2731/
> -----------------------------------------------------------
>
> (Updated Aug. 1, 2013, 4:12 a.m.)
>
>
> Review request for Asterisk Developers.
>
>
> Repository: Asterisk
>
>
> Description
> -------
>
> This patch replaces contrib/realtime/ with a new setup for managing the database schema required for database integration with Asterisk. In addition to initializing a database with the proper schema, alembic can do a database migration to assist with upgrading Asterisk in the future. Hopefully this helps make setting up and operating Asterisk with a database easier.
>
> With this the schema only needs to be maintained in one place instead of once per database. The schemas I have added here have a bit of improvement over the examples that were there before (some added consistency and added some missing indexes). Managing the schema in one place here also applies to all databases supported by SQLAlchemy.
>
> See contrib/ast-db-manage/README.md for more details.
>
>
> Diffs
> -----
>
> /trunk/CHANGES 395934
> /trunk/contrib/ast-db-manage/README.md PRE-CREATION
> /trunk/contrib/ast-db-manage/config.ini.sample PRE-CREATION
> /trunk/contrib/ast-db-manage/config/env.py PRE-CREATION
> /trunk/contrib/ast-db-manage/config/script.py.mako PRE-CREATION
> /trunk/contrib/ast-db-manage/config/versions/4da0c5f79a9c_create_tables.py PRE-CREATION
> /trunk/contrib/ast-db-manage/voicemail.ini.sample PRE-CREATION
> /trunk/contrib/ast-db-manage/voicemail/env.py PRE-CREATION
> /trunk/contrib/ast-db-manage/voicemail/script.py.mako PRE-CREATION
> /trunk/contrib/ast-db-manage/voicemail/versions/a2e9769475e_create_tables.py PRE-CREATION
> /trunk/contrib/realtime/mysql/iaxfriends.sql 395934
> /trunk/contrib/realtime/mysql/meetme.sql 395934
> /trunk/contrib/realtime/mysql/musiconhold.sql 395934
> /trunk/contrib/realtime/mysql/queue_log.sql 395934
> /trunk/contrib/realtime/mysql/sippeers.sql 395934
> /trunk/contrib/realtime/mysql/voicemail.sql 395934
> /trunk/contrib/realtime/mysql/voicemail_data.sql 395934
> /trunk/contrib/realtime/mysql/voicemail_messages.sql 395934
> /trunk/contrib/realtime/postgresql/realtime.sql 395934
>
> Diff: https://reviewboard.asterisk.org/r/2731/diff/
>
>
> Testing
> -------
>
> I ran schema upgrade/downgrade against MariaDB (MySQL compatible). The schema came out as expected. I haven't hooked it up to Asterisk, though...
>
>
> Thanks,
>
> Russell Bryant
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.digium.com/pipermail/asterisk-dev/attachments/20130801/3634bc00/attachment-0001.htm>
More information about the asterisk-dev
mailing list