Extra DDL

We use src/main/resources/extra-ddl.xml to supply extra DDL for things like views and stored procedures and sometimes indexes - effectively any DDL that isn't generated via mapping annotations.

The DDL in extra-ddl is then run as part of normal testing (run after dropCreate) and as part of DB migrations (as repeatable migrations).

Best practices

When using extra-ddl.xml there are a some practices that are good to follow:

• 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.
• Don't put all the DDL into a single large ddl-script entry. Instead group ddl into ddl-script 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 ddl-script entry.

Views

To define DB views which we can then use on an entity bean via @View we should add ddl-script elements into extra-ddl.xml to create the views. Each entry should allow for re-creating the view so generally includes drop view if exits and create view statements.

Each entry becomes a "repeatable" migration and will run whenever the hash of its content changes and run AFTER all the normal "version" migrations.

Note that it would be considered best practice to create a view in it's own ddl-script or include dependent views in their own ddl-script (views that are [dropped] and created together).

Examples:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<extra-ddl xmlns="http://ebean-orm.github.io/xml/ns/extraddl">

  <ddl-script name="1 product view">
     drop view if exists product_vw cascade;

     create view product_vw as
      ...;
  </ddl-script>

  <ddl-script name="2 promotion views">
    -- 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
      ...;
  </ddl-script>

</extra-ddl>
  

Stored procedures and Triggers

For stored procedures and triggers add a ddl-script for the related objects. Note that to support multiple databases we commonly specify the platforms attribute.

Examples:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<extra-ddl xmlns="http://ebean-orm.github.io/xml/ns/extraddl">

  <ddl-script name="3 store price trigger" platforms="postgres">
    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();
  </ddl-script>

</extra-ddl>
  

Any DDL

Note that there is not a restriction on the DDL we can put into extra-ddl.xml. 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 CREATE TABLE statements as that DDL is generated from the mapping annotations @Table etc.

Platform specific DDL

To support multiple database platforms we specify via the platforms attribute which database platforms the DDL applies to.

We can use the same script name with different platforms to handle database specific syntax for views, stored procedures etc.

Examples:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<extra-ddl xmlns="http://ebean-orm.github.io/xml/ns/extraddl">

  <ddl-script name="5 order agg view" platforms="h2">
    -- h2 specific DDL
    drop view order_agg_vw;
    create view order_agg_vw as
    ...;
  </ddl-script>

  <ddl-script name="5 order agg view" platforms="postgres,mysql,db2">
    -- DLL for postgres, mysql and db2
    create or replace view order_agg_vw as
    ...
  </ddl-script>

</extra-ddl>