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
and as part of DB migrations (as repeatable migrations).
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-scriptentry. Instead group ddl into
ddl-scriptentries 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
To define DB views which we can then use on an entity bean via
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
(views that are [dropped] and created together).
<?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
the related objects. Note that to support multiple databases we commonly
<?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>
Note that there is not a restriction on the DDL we can put into
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
Platform specific DDL
To support multiple database platforms we specify via the
which database platforms the DDL applies to.
We can use the same script
name with different
handle database specific syntax for views, stored procedures etc.
<?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>