<html>
<head>
<meta name="viewport" content="width=device-width" />
<base href="https://wiki.asterisk.org/wiki" />
<style type="text/css">
body, #email-content, #email-content-inner { font-family: Arial,FreeSans,Helvetica,sans-serif; }
body, p, blockquote, pre, code, td, th, li, dt, dd { font-size: 13px; }
small { font-size: 11px; }
body { width:100% !important; -webkit-font-smoothing: antialiased; }
body,
#email-wrapper { background-color: #f0f0f0; }
#email-wrapper-inner { padding: 20px; text-align: center; }
#email-content-inner { background-color: #fff; border: 1px solid #bbb; color: $menuTxtColour; padding:20px; text-align:left; }
#email-wrapper-inner > table { width: 100%; }
#email-wrapper-inner.thin > table { margin: 0 auto; width: 50%; }
#email-footer { padding: 0 16px 32px 16px; margin: 0; }
.email-indent { margin: 8px 0 16px 0; }
.email-comment { margin: 0 0 0 56px; }
.email-comment.removed { background-color: #ffe7e7; border: 1px solid #df9898; padding: 0 8px;}
#email-title-avatar { text-align: left; vertical-align: top; width: 48px; padding-right: 8px; }
#email-title-flavor { margin: 0; padding: 0 0 4px 0; }
#email-title-heading { font-size: 16px; line-height: 20px; min-height: 20px; margin: 0; padding: 0; }
#email-title .icon { border: 0; padding: 0 5px 0 0; text-align: left; vertical-align: middle; }
#email-actions { border-top: 1px solid #bbb; color: #505050; margin: 8px 0 0 0; padding: 0; }
#email-actions td { padding-top: 8px; }
#email-actions .left { max-width: 45%; text-align: left; }
#email-actions .right { text-align: right; }
.email-reply-divider { border-top: 1px solid #bbb; color: #505050; margin: 32px 0 8px 0; padding: 8px 0; }
.email-section-title { border-bottom: 1px solid #bbb; margin: 8px 0; padding: 8px 0 0 0; }
.email-metadata { color: #505050; }
a { color: #326ca6; text-decoration: none; }
a:hover { color: #336ca6; text-decoration: underline; }
a:active {color: #326ca6; }
a.email-footer-link { color: #505050; font-size: 11px; }
.email-item-list { list-style: none; margin: 4px 0; padding-left: 0; }
.email-item-list li { list-style: none; margin: 0; padding: 4px 0; }
.email-list-divider { color: #505050; padding: 0 0.35em; }
.email-operation-icon { padding-right: 5px; }
.avatar { -ms-interpolation-mode: bicubic; border-radius: 3px;}
.avatar-link { margin: 2px; }
.tableview th { border-bottom: 1px solid #69C; font-weight: bold; text-align: left; }
.tableview td { border-bottom: 1px solid #bbbbbb; text-align: left; padding: 4px 16px 4px 0; }
.aui-message { margin: 1em 0; padding: 8px; }
.aui-message.info { background-color: #e0f0ff; border: 1px solid #9eb6d4; }
.aui-message.success { background-color: #ddfade; border: 1px solid #93c49f; }
.aui-message.error,
.aui-message.removed { background-color: #ffe7e7; border: 1px solid #df9898; color: #000; }
.call-to-action-table { margin: 10px 1px 1px 1px;}
.call-to-cancel-container, .call-to-action-container { padding: 5px 20px; }
.call-to-cancel-container { border: 1px solid #aaa; background-color: #eee; border-radius: 3px; }
.call-to-cancel-container a.call-to-cancel-button { background-color: #eee; font-size: 14px; line-height: 1; padding: 0; margin: 0; color: #666; font-family: sans-serif;}
.call-to-action-container { border: 1px solid #486582; background-color: #3068A2; border-radius: 3px; padding: 4px 10px; }
.call-to-action-container a.call-to-action-button { background-color: #3068A2; font-size: 14px; line-height: 1; padding: 0; margin: 0; color: #fff; font-weight: bold; font-family: sans-serif; }
/** The span around the inline task checkbox image */
.diff-inline-task-overlay {
display: inline-block;
text-align: center;
height: 1.5em;
padding: 5px 0px 1px 5px;
margin-right: 5px;
/** Unfortunately, the negative margin-left is stripped out in gmail */
margin-left: -5px;
}
@media handheld, only screen and (max-device-width: 480px) {
div, a, p, td, th, li, dt, dd { -webkit-text-size-adjust: auto; }
small, small a { -webkit-text-size-adjust: 90%; }
td[id=email-wrapper-inner] { padding: 2px !important; }
td[id=email-content-inner] { padding: 8px !important; }
td[id="email-wrapper-inner"][class="thin"] > table { text-align: left !important; width: 100% !important; }
td[id=email-footer] { padding: 8px 12px !important; }
div[class=email-indent] { margin: 8px 0px !important; }
div[class=email-comment] { margin: 0 !important; }
p[id=email-title-flavor] a { display: block; } /* puts the username and the action on separate lines */
p[id=email-permalink] { padding: 4px 0 0 0 !important; }
table[id=email-actions] td { padding-top: 0 !important; }
table[id=email-actions] td.right { text-align: right !important; }
table[id=email-actions] .email-list-item { display: block; margin: 1em 0 !important; word-wrap: normal !important; }
span[class=email-list-divider] { display: none; }
}
</style>
</head>
<body style="font-family: Arial, FreeSans, Helvetica, sans-serif; font-size: 13px; width: 100%; -webkit-font-smoothing: antialiased; background-color: #f0f0f0">
<table id="email-wrapper" width="100%" cellspacing="0" cellpadding="0" border="0" style="background-color: #f0f0f0">
<tbody>
<tr valign="middle">
<td id="email-wrapper-inner" style="font-size: 13px; padding: 20px; text-align: center">
<table id="email-content" cellspacing="0" cellpadding="0" border="0" style="font-family: Arial, FreeSans, Helvetica, sans-serif; width: 100%">
<tbody>
<tr valign="top">
<td id="email-content-inner" align="left" style="font-family: Arial, FreeSans, Helvetica, sans-serif; font-size: 13px; background-color: #fff; border: 1px solid #bbb; padding: 20px; text-align: left">
<table id="email-title" cellpadding="0" cellspacing="0" border="0" width="100%">
<tbody>
<tr>
<td id="email-title-avatar" rowspan="2" style="font-size: 13px; text-align: left; vertical-align: top; width: 48px; padding-right: 8px"> <img class="avatar" src="cid:avatar_ce51dcf276530e4a4b00548e2a6d0905" border="0" height="48" width="48" style="-ms-interpolation-mode: bicubic; border-radius: 3px" /> </td>
<td valign="top" style="font-size: 13px">
<div id="email-title-flavor" class="email-metadata" style="margin: 0; padding: 0 0 4px 0; color: #505050">
<a href=" https://wiki.asterisk.org/wiki/display/~mjordan " style="color:#326ca6;text-decoration:none;; color: #326ca6; text-decoration: none">Matt Jordan</a> created a page:
</div> </td>
</tr>
<tr>
<td valign="top" style="font-size: 13px"> <h2 id="email-title-heading" style="font-size: 16px; line-height: 20px; min-height: 20px; margin: 0; padding: 0"> <a href="https://wiki.asterisk.org/wiki/display/AST/Managing+Realtime+Databases+with+Alembic" style="color: #326ca6; text-decoration: none"> <img class="icon" src="cid:page-icon" alt="" style="border: 0; padding: 0 5px 0 0; text-align: left; vertical-align: middle" /> <strong style="font-size:16px;line-height:20px;vertical-align:top;">Managing Realtime Databases with Alembic</strong> </a> </h2> </td>
</tr>
</tbody>
</table>
<div class="email-indent" style="margin: 8px 0 16px 0">
<div class="email-page">
<h1 id="ManagingRealtimeDatabaseswithAlembic-Overview">Overview</h1>
<p style="font-size: 13px">Asterisk 12 now uses <a href="https://pypi.python.org/pypi/alembic" class="external-link" rel="nofollow" style="color: #326ca6; text-decoration: none">Alembic</a> to help manage Asterisk Realtime Database schemas. This includes creation of SQL scripts for a variety of database vendors, but also much more. Alembic is a full database migration tool, with support for upgrading the schemas of existing databases, versioning of schemas, creation of new tables and databases, and a whole lot more. This page covers basic configuration of the Alembic configuration file for usage with Asterisk Realtime as well as basic usage of Alembic. While a full description of Alembic is beyond the scope of this page, the information on this page should help an Asterisk administrator create or upgrade an Asterisk installation.</p>
<h2 id="ManagingRealtimeDatabaseswithAlembic-BeforeyouBegin">Before you Begin</h2>
<p style="font-size: 13px">This tutorial assumes you already have some experience in setting up Realtime configuration with Asterisk for other modules. This page will not describe how to set up backend database connectors, and is written under the assumption that you will be using ODBC to connect to your database since the ODBC adaptor is capable of connecting to most commonly used database servers. For more information on configuring and setting up Asterisk Realtime, see <a href="https://wiki.asterisk.org/wiki/display/AST/Realtime+Database+Configuration" style="color: #326ca6; text-decoration: none">Asterisk Realtime Database</a> configuration.</p>
<h1 id="ManagingRealtimeDatabaseswithAlembic-InstallingAlembic">Installing Alembic</h1>
<p style="font-size: 13px">If you don't already have Alembic installed, perform the following:</p>
<div class="aui-message success shadowed information-macro" style="margin: 1em 0; padding: 8px; background-color: #ddfade; border: 1px solid #93c49f">
<span class="aui-icon icon-success">Icon</span>
<div class="message-content">
<p style="font-size: 13px">This does assume that you have pip installed. If you do not have pip installed, easy_install should work just as well. If you don't have pip or easy_install (or Python), then you should probably install those first.</p>
</div>
</div>
<div class="preformatted panel" style="border-width: 1px;">
<div class="preformattedContent panelContent">
<pre style="font-size: 13px">$ pip install alembic</pre>
</div>
</div>
<p style="font-size: 13px">And that's it!</p>
<h1 id="ManagingRealtimeDatabaseswithAlembic-BuildingtheDatabaseTables">Building the Database Tables</h1>
<p style="font-size: 13px">Alembic scripts were added to Asterisk in Asterisk 12, and will allow you to automatically populate your database with tables for most of the commonly used configuration options. The scripts are located in the <a href="http://svn.asterisk.org/svn/asterisk/trunk/contrib/ast-db-manage/" class="external-link" rel="nofollow" style="color: #326ca6; text-decoration: none">Asterisk contrib/ast-db-manage</a> folder:</p>
<div class="preformatted panel" style="border-width: 1px;">
<div class="preformattedContent panelContent">
<pre style="font-size: 13px">$ cd contrib/ast-db-manage</pre>
</div>
</div>
<p style="font-size: 13px">For the rest of this tutorial, we will assume that operations will be taken in the context of that directory.</p>
<p style="font-size: 13px"> <span style="line-height: 1.4285715;">Within this directory, you will find a configuration sample file, <code style="font-size: 13px">config.ini.sample</code>, which will need to be edited to connect to your database of choice. Open this file in your test editor of choice and then save a copy of this sample file as <code style="font-size: 13px">config.ini</code> - this will serve as the configuration file you actually use with Alembic.</span> </p>
<p style="font-size: 13px">There are two different parameters in <code style="font-size: 13px">config.ini</code> that require review: <code style="font-size: 13px">sqlalchemy.url</code> and <code style="font-size: 13px">script_location</code>. The first specifies the database to upgrade; the second which upgrades to perform.</p>
<ol>
<li style="font-size: 13px"> <p style="font-size: 13px">Update <code style="font-size: 13px">sqlalchemy.url</code> to the URL for your database. An example is shown below for a MySQL database:</p>
<div class="preformatted panel" style="border-width: 1px;">
<div class="preformattedContent panelContent">
<pre style="font-size: 13px">sqlalchemy.url = mysql://root:password@localhost/asterisk</pre>
</div>
</div> <p style="font-size: 13px">This would connect to a MySQL database as user <code style="font-size: 13px">root</code> with password <code style="font-size: 13px">password</code>. The database is <code style="font-size: 13px">asterisk</code>, located on <code style="font-size: 13px">localhost</code>. Different databases will require different URL schemas; however, they should in general follow the format outlined above. Alembic supports many different database technologies, including <code style="font-size: 13px">oracle</code>, <code style="font-size: 13px">postgresql</code>, and <code style="font-size: 13px">mssql</code>.</p> <p style="font-size: 13px">For more information, see the Alembic documentation on SQLAlchemy URLs: <a href="http://docs.sqlalchemy.org/en/rel_0_8/core/engines.html#database-urls" class="external-link" rel="nofollow" style="color: #326ca6; text-decoration: none">http://docs.sqlalchemy.org/en/rel_0_8/core/engines.html#database-urls</a> </p> </li>
<li style="font-size: 13px">Update <code style="font-size: 13px">script_location</code> to the schema to update. Asterisk currently supports two sets of schemas:
<ol>
<li style="font-size: 13px"> <code style="font-size: 13px">config</code> - the set of schemas for Asterisk Realtime databases</li>
<li style="font-size: 13px"> <code style="font-size: 13px">voicemail</code> - the schema for ODBC VoiceMail</li>
</ol> </li>
</ol>
<p style="font-size: 13px"> </p>
<div class="aui-message problem shadowed information-macro" style="margin: 1em 0; padding: 8px">
<p class="title" style="font-size: 13px">I'm sorry Dave, I'm afraid I can't let you do that.</p>
<span class="aui-icon icon-problem">Icon</span>
<div class="message-content">
<p style="font-size: 13px">Using config.ini for Alembic will populate tables for all of the configuration objects that can be populated this way, so if you really don't want a table for sip peers, iax friends, voicemail, meetme, and music on hold, you may need to exercise a little fine control. Back up your database before continuing and be prepared to delete tables that you don't want when you are finished.</p>
</div>
</div>
<p style="font-size: 13px">Your config.ini should be ready for use at this point, so close your text editor and return to the terminal. Then run:</p>
<div class="preformatted panel" style="border-width: 1px;">
<div class="preformattedContent panelContent">
<pre style="font-size: 13px">$ alembic -c config.ini upgrade head</pre>
</div>
</div>
<div class="aui-message hint shadowed information-macro" style="margin: 1em 0; padding: 8px">
<p class="title" style="font-size: 13px">Alembic makes upgrading less painful</p>
<span class="aui-icon icon-hint">Icon</span>
<div class="message-content">
<p style="font-size: 13px">As Asterisk changes and new fields are made controllable via realtime, the Alembic scripts will be updated as well and you will be able to simply run the alembic upgrade command again in order to modify your database. Always exercise due diligence and backup your database before upgrading though. Tables can be fixed easily. Repopulating the data if it's lost however isn't.</p>
</div>
</div>
<p style="font-size: 13px">At this point, if you configured your config.ini to connect to the database properly, your tables should be ready.</p>
</div>
</div>
<table id="email-actions" class="email-metadata" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-top: 1px solid #bbb; color: #505050; margin: 8px 0 0 0; padding: 0; color: #505050">
<tbody>
<tr>
<td class="left" valign="top" style="font-size: 13px; padding-top: 8px; max-width: 45%; text-align: left"> <span class="email-list-item"><a href="https://wiki.asterisk.org/wiki/display/AST/Managing+Realtime+Databases+with+Alembic" style="color: #326ca6; text-decoration: none">View Online</a> </span> <span class="email-list-divider" style="color: #505050; padding: 0 0.350em">·</span> <span class="email-list-item"><a href="https://wiki.asterisk.org/wiki/plugins/likes/like.action?contentId=26478362" style="color: #326ca6; text-decoration: none">Like</a> </span> <span class="email-list-divider" style="color: #505050; padding: 0 0.350em">·</span> <span class="email-list-item"><a href="https://wiki.asterisk.org/wiki/display/AST/Managing+Realtime+Databases+with+Alembic?showComments=true&showCommentArea=true#addcomment" style="color: #326ca6; text-decoration: none">Add Comment</a> </span> </td>
<td class="right" width="50%" valign="top" style="font-size: 13px; padding-top: 8px; text-align: right"> <span class="email-list-item"><a href="https://wiki.asterisk.org/wiki/users/removespacenotification.action?spaceKey=AST" style="color: #326ca6; text-decoration: none">Stop watching space</a> </span> <span class="email-list-divider" style="color: #505050; padding: 0 0.350em">·</span> <span class="email-list-item"><a href="https://wiki.asterisk.org/wiki/users/editmyemailsettings.action" style="color: #326ca6; text-decoration: none">Manage Notifications</a> </span> </td>
</tr>
</tbody>
</table> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td id="email-footer" align="center" style="font-size: 13px; padding: 0 16px 32px 16px; margin: 0"> <small style="font-size: 11px"> This message was sent by <a class="email-footer-link" style="color:#505050;font-size:11px;text-decoration:none;; color: #326ca6; text-decoration: none; color: #505050; font-size: 11px" href="http://www.atlassian.com/software/confluence">Atlassian Confluence</a> 5.1.5, <a class="email-footer-link" style="color:#505050;font-size:11px;text-decoration:none;; color: #326ca6; text-decoration: none; color: #505050; font-size: 11px" href="http://www.atlassian.com/software/confluence/overview/team-collaboration-software?utm_source=email-footer">Team Collaboration Software</a> </small> </td>
</tr>
</tbody>
</table>
</body>
</html>