Extra DDL | Docs

<!doctype html> <html lang="en"> <head> <title>Extra DDL | Docs | Ebean</title> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <link rel="shortcut icon" href="/images/favicon.ico"> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto|Source+Sans+Pro|Ubuntu&display=swap"> <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.1.0/css/all.css" integrity="sha384-lKuwvrZot6UHsBSfcMvOkWwlCMgc0TaWr+30HWe3a4ltaBwTZhyTEggF5tJv8tbt" crossorigin="anonymous"> <link rel="stylesheet" href="/css/reset3.css"> <link rel="stylesheet" href="/css/site3.css"> <link rel="stylesheet" href="/css/pygments3.css"> </head> <body> <div id="main"> <div id="banner"> <header> <nav id="top"> <h1 id="breadcrumb"> <a class="nav-logo" href="/"><img src="/images/logo-200.png" height="35"></a>  <a href="/docs">Documentation</a><span class="sep"> / </span><span class="last">Extra DDL</span> </h1> <ul> <li><a onclick="toggleTheme();" title="switch dark light theme"><i class="fas fa-adjust"></i></a></li> </ul> </nav> </header> </div> <div class="grid grid-docs"> <aside> <nav class="side"> <ul> <li class="nav0 "> <a href="/docs/getting-started">Getting started</a> </li> <li class="nav0 "> <a href="/docs/intro">Introduction</a> </li> <li class="nav0 active"> <a class="active" href="/docs">Documentation</a> <ul> <li class="nav1 "> <a href="/docs/best-practice">Best practice</a> </li> <li class="nav1 "> <a href="/docs/query">Query</a> </li> <li class="nav1 "> <a href="/docs/persist">Persist</a> </li> <li class="nav1 "> <a href="/docs/transactions">Transactions</a> </li> <li class="nav1 "> <a href="/docs/mapping">Mapping</a> </li> <li class="nav1 active"> <a class="active" href="/docs/ddl-generation">DDL & Migrations</a> <ul> <li class="nav1 "> <a href="/docs/ddl-generation">DDL Generation</a> </li> <li class="nav1 active"> <a class="active" href="/docs/extra-ddl">Extra DDL</a> <ul class="nav nav-scroll"> <li > <a href="#views">Views</a> </li> <li > <a href="#procedures">Stored procedures</a> </li> <li > <a href="#any">Any DDL</a> </li> <li > <a href="#platforms">Platform specific DDL</a> </li> </ul> </li> <li class="nav1 "> <a href="/docs/db-migrations">DB Migrations</a> </li> <li class="nav1 "> <a href="/docs/db-migrations/detail">DB Migrations details</a> </li> </ul> </li> <li class="nav1 "> <a href="/docs/logging">Logging</a> </li> <li class="nav1 "> <a href="/docs/testing">Testing</a> </li> <li class="nav1 "> <a href="/docs/read-replicas">Read Replicas</a> </li> <li class="nav1 "> <a href="/docs/database">Database platforms</a> </li> <li class="nav1 "> <a href="/docs/multi-database">Multiple databases</a> </li> <li class="nav1 "> <a href="/docs/kotlin">Kotlin</a> </li> <li><a href="/docs/tuning">Tuning</a></li> <li class="nav1 "> <a href="/docs/features">Features</a> </li> </ul> </li> <li class="nav0 "> <a href="/support">Getting help</a> </li> <li class="nav0 "> <a target="_blank" href="/apidoc/13">API Javadoc</a> </li> <li class="nav0 "> <a href="/videos">Videos</a> </li> <li class="nav0 "> <a href="/docs/upgrading">Upgrading</a> </li> <li class="nav0 "> <a href="/releases">Releases</a> </li> </ul> </nav> </aside> <article> <form action="https://www.google.com/search" method="get" class="inline-form"> <input type="hidden" name="as_sitesearch" value="ebean.io"> <div id="page-search"> <div class="input-group"> <input class="frm" name="q" id="searchinput" type="text" placeholder="Search... (press 's' to focus)" data-placeholder-focus="Search... (use '↑', '↓' and '⏎' to select results)" data-placeholder-blur="Search... (press 's' to focus)" autocomplete="off"> <div class="input-group-btn"> <button class="frm" type="submit"><i class="fas fa-search"></i></button> </div> </div> <div id="page-search-results" style="display: none;"> <ul id="search-results-container" class="search-results"><li class=" active"><a href="/docs" title="Docs"><span style="color:#777;">Docs</span> Documentation </a></li><li class=""><small style="color:#999;">And 101 more...</small></li></ul> </div> </div> </form> <h2 id="overview">Extra DDL</h2> <p> We use <code>src/main/resources/extra-ddl.xml</code> to supply extra DDL for things like views and stored procedures and sometimes indexes - effectively any DDL that isn't generated via mapping annotations. </p> <p> The DDL in extra-ddl is then run as part of normal testing (run after <code>dropCreate</code>) and as part of DB migrations (as repeatable migrations). </p> <h3>Best practices</h3> <p> When using <code>extra-ddl.xml</code> there are a some practices that are good to follow: </p> <ul> <li> Prefix the script names with a number (e.g. 1,2,3 ...) to effectively order the execution of the repeatable migrations relative to each other. </li> <li> Don't put all the DDL into a single large <code>ddl-script</code> entry. Instead group ddl into <code>ddl-script</code> entries for things that will be dropped and re-created together. If we want to drop and re-create something by itself it needs to be in it's own <code>ddl-script</code> entry. </li> </ul> <h2 id="views">Views</h2> <p> To define DB views which we can then use on an entity bean via <code>@View</code> we should add <code>ddl-script</code> elements into extra-ddl.xml to create the views. Each entry should allow for re-creating the view so generally includes <code>drop view if exits</code> and <code>create view</code> statements. </p> <p> Each entry becomes a "repeatable" migration and will run whenever the hash of its content changes and run AFTER all the normal "version" migrations. </p> <p> Note that it would be considered best practice to create a view in it's own <code>ddl-script</code> or include dependent views in their own <code>ddl-script</code> (views that are [dropped] and created together). </p> <h4>Examples:</h4> <div class="syntax xml"><div class="highlight"><pre><span></span><span class="cp"><?xml version="1.0" encoding="UTF-8" standalone="yes"?></span> <span class="nt"><extra-ddl</span> <span class="na">xmlns=</span><span class="s">"http://ebean-orm.github.io/xml/ns/extraddl"</span><span class="nt">></span> <span class="nt"><ddl-script</span> <span class="na">name=</span><span class="s">"1 product view"</span><span class="nt">></span> drop view if exists product_vw cascade; create view product_vw as ...; <span class="nt"></ddl-script></span> <span class="nt"><ddl-script</span> <span class="na">name=</span><span class="s">"2 promotion views"</span><span class="nt">></span> -- 2 related/dependent views that drop and create together drop view if exists promotion_minprice_vw cascade; drop view if exists promotion_vw cascade; create view promotion_vw as ...; create view promotion_minprice_vw as ...; <span class="nt"></ddl-script></span> <span class="nt"></extra-ddl></span> </pre></div> </div> <h2 id="procedures">Stored procedures and Triggers</h2> <p> For stored procedures and triggers add a <code>ddl-script</code> for the related objects. Note that to support multiple databases we commonly specify the <code>platforms</code> attribute. </p> <h4>Examples:</h4> <div class="syntax xml"><div class="highlight"><pre><span></span><span class="cp"><?xml version="1.0" encoding="UTF-8" standalone="yes"?></span> <span class="nt"><extra-ddl</span> <span class="na">xmlns=</span><span class="s">"http://ebean-orm.github.io/xml/ns/extraddl"</span><span class="nt">></span> <span class="nt"><ddl-script</span> <span class="na">name=</span><span class="s">"3 store price trigger"</span> <span class="na">platforms=</span><span class="s">"postgres"</span><span class="nt">></span> create or replace function trigger_store_price_log() returns trigger as $$ begin ...; end; $$ LANGUAGE plpgsql; create trigger trg_store_price_log before update on store_price for each row execute procedure trigger_store_price_log(); <span class="nt"></ddl-script></span> <span class="nt"></extra-ddl></span> </pre></div> </div> <h2 id="any">Any DDL </h2> <p> Note that there is not a restriction on the DDL we can put into <code>extra-ddl.xml</code>. We use extra-dll for DDL that isn't modelled and generated by annotations. For example, we generally don't expect to use extra-ddl for <code>CREATE TABLE</code> statements as that DDL is generated from the mapping annotations <code>@Table</code> etc. </p> <h2 id="platforms">Platform specific DDL</h2> <p> To support multiple database platforms we specify via the <code>platforms</code> attribute which database platforms the DDL applies to. </p> <p> We can use the same script <code>name</code> with different <code>platforms</code> to handle database specific syntax for views, stored procedures etc. </p> <h4>Examples:</h4> <div class="syntax xml"><div class="highlight"><pre><span></span><span class="cp"><?xml version="1.0" encoding="UTF-8" standalone="yes"?></span> <span class="nt"><extra-ddl</span> <span class="na">xmlns=</span><span class="s">"http://ebean-orm.github.io/xml/ns/extraddl"</span><span class="nt">></span> <span class="nt"><ddl-script</span> <span class="na">name=</span><span class="s">"5 order agg view"</span> <span class="na">platforms=</span><span class="s">"h2"</span><span class="nt">></span> -- h2 specific DDL drop view order_agg_vw; create view order_agg_vw as ...; <span class="nt"></ddl-script></span> <span class="nt"><ddl-script</span> <span class="na">name=</span><span class="s">"5 order agg view"</span> <span class="na">platforms=</span><span class="s">"postgres,mysql,db2"</span><span class="nt">></span> -- DLL for postgres, mysql and db2 create or replace view order_agg_vw as ... <span class="nt"></ddl-script></span> <span class="nt"></extra-ddl></span> </pre></div> </div> <nav class="next"> <p class="next"> <a href="/docs/db-migrations" class="next btn">Next: DB Migrations</a> </p> </nav> </article> </div> </div> <script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script> <script src="/js/site3.js"></script> <script src="/js/search3.js"></script> <script async src="https://www.googletagmanager.com/gtag/js?id=UA-75181644-1"></script> <script> window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} gtag('js', new Date()); gtag('config', 'UA-75181644-1'); </script> </body> </html>

CINXE.COM

Extra DDL | Docs | Ebean