Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Development schema, production schema

DZone's Guide to

Development schema, production schema

· Java Zone
Free Resource

Microservices! They are everywhere, or at least, the term is. When should you use a microservice architecture? What factors should be considered when making that decision? Do the benefits outweigh the costs? Why is everyone so excited about them, anyway?  Brought to you in partnership with IBM.

Most of us separate development data from production data, physically or at least, logically (except maybe Chuck Norris (official website, no kidding!)). If you’re lucky and you can afford multiple Oracle / other-expensive-database licenses, you might clone the same schema / owner name for every application instance on different servers. But sometimes, you can’t do that, and you have to put all schemata in the same box and name them:

  • DB_DEV
  • DB_TEST
  • DB_PROD

Or worse… several productive instances in the same box

Another, similar use case is when you deploy several instances of the same application in the same environment. For instance, you have a blogging server with 10 users. Every user has their own independent blog with their own tables. You may either resolve this problem by creating multiple schemata / owners again:

  • DB_USER1
  • DB_USER2
  • DB_USER3

Or, by adding prefixes / suffixes to your tables within a single schema:

  • DB.USER1_POSTS
  • DB.USER1_COMMENTS
  • DB.USER2_POSTS
  • DB.USER2_COMMENTS

This means that in every executed SQL statement, you’d have to patch relevant environments (DEV, TEST, PROD) or users (USER1, USER2, USER3) into all of your database artefacts. There are a lot of things that can go wrong.

Let jOOQ do that for you, instead

With jOOQ, it’s simple. jOOQ always generates the schema / owner in the generated SQL statements. This is usually the name of your development schema from which you generated source code. So if you have select from a DB_DEV.POSTS table, you’ll do this:

OracleFactory create = new OracleFactory(connection);
create.select(TEXT)
      .from(POSTS)
      .fetch();

// jOOQ generates:
// SELECT "DB_DEV"."POSTS"."TEXT" FROM "DB_DEV"."POSTS"

When you run this statement productively, you probably want this instead

// Create a mapping indicating that instead of DB_DEV,
// you want jOOQ to render DB_PROD as the schema
SchemaMapping mapping = new SchemaMapping();
mapping.add(DB_DEV, "DB_PROD");
OracleFactory create = new OracleFactory(connection, mapping);
create.select(TEXT)
      .from(POSTS)
      .fetch();

// jOOQ now generates:
// SELECT "DB_PROD"."POSTS"."TEXT" FROM "DB_PROD"."POSTS"

The same applies for prefixed tables. Let’s say, USER1 is logged in, and you want to prefix your tables with “USER1_”

// Create a mapping renaming some tables
SchemaMapping mapping = new SchemaMapping();
mapping.add(POSTS, "USER1_POSTS");
mapping.add(COMMENTS, "USER1_COMMENTS");
OracleFactory create = new OracleFactory(connection, mapping);
create.select(TEXT, COMMENT)
      .from(POSTS)
      .join(COMMENTS)
      .using(POST_ID)
      .fetch();

// jOOQ now generates:
// SELECT "DB"."USER1_POSTS"."TEXT",
//        "DB"."USER1_COMMENTS"."COMMENT"
//   FROM "DB"."USER1_POSTS"
//   JOIN "DB"."USER1_COMMENTS"
// USING ("POST_ID")

For more information, refer to the manual:

http://www.jooq.org/manual/ADVANCED/SchemaMapping

 

From http://lukaseder.wordpress.com/2011/11/09/development-schema-production-schema/

Discover how the Watson team is further developing SDKs in Java, Node.js, Python, iOS, and Android to access these services and make programming easy. Brought to you in partnership with IBM.

Topics:

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}