July 5, 2009

Comparing Database Structures

Overview

(Deutschsprachige Anleitung in liesmich.txt)
  • Djembe helps in keeping the structures of several database for development, testing, integration and production synchronized
  • Database independent due to the usage of JDBC drivers
  • Compare databases offline
  • Export results to Excel
  • Structures are saved as XML, thus allowing any further processing
  • usable via command line or with its own GUI

Environment:
  • Java 6 or newer
  • JDBC Driver (drivers for Oracle, MS SQL Server, Postgres, MySQL, Derby/JavaDB and Firebird were tested successfully)
Verified for Windows XP SP3 and openSUSE 11.

The main two components are:
  • The scanner analyzes database structures and saves them as xml files.
  • The comparer reads previously stored xml scans and writes the comparison result as plain text log files, respectively presenting the result as GUI table.
Both components are configured via plain text property files:
  • file suffix .djs for scanners
  • file suffix .djc for comparisons
Currently, the following database objects are processed:
  • Tables with columns and privileges
  • Views with columns and privileges
  • Columns with type, length, and nullable flag
  • Sequences/generators with privileges
  • Oracle synonyms
  • Trigger (without source code so far)
  • Primary- and foreign keys
  • Indexes
  • Types
(Privilege, type, trigger and other vendor specific scanners are not part of the
free standard edition)

Usage

For installation, just copy the application folder to the desired place and add the required JDBC drivers (to the "lib" folder usually).
Please don't forget to copy your license key "djembe-license.txt" into the root folder of the application.
java -jar djembe.jar 
-ui | 
-scan [scan-properties] | 
-compare [compare-properties]
-ui
Launches the graphical user interface of Djembe.

-scan
Reads a database and stores its structure as xml file.

-compare
Compares two databases.

Examples:
java -jar djembe.jar -scan scan-my-db.djs
java -jar djembe.jar -compare compare-my-dbs.djc

Under Windows, you can use "djembe.exe" or "djembe.bat", under linux "djembe.sh" for running the graphical user interface as well.


Privilege markup in scans and comparison results:
[grantor] -> [grantee]: [privilege]


Column Aliases:
They allow to look at different data types as synonyms causing no difference alert.
A typical example would be to equalize Oracle's specific "varchar2" and the standard "varchar".
You can set up as many mappings as you like, each one comma separated:

[mapping-1], [mapping-2], ..., [mapping-n]

Data types and flags are separated by spaces:

[Leading data type] [-is] [-id] [Alias-1] ... [Alias-n]

Flags:
-id ignore digits
-is ignore size

Example:
"int4 -id -is number numeric, varchar2 varchar"

Fields of type "int4" are put together with "number" and "numeric".
Both size and number of digits are ignored.
Additionally, data types "varchar2" and "varchar" are taken as identical.

Useful mappings:
varchar2 varchar,
int4 -id -is number numeric,
boolean -id -is char,
date -id -is timestamp,
blob -is bytea,
bpchar char,
numeric number


Scanner-Properties

url =             (database URL to analyze)
alias =           (short database name, free to choose)
driver =          (JDBC driver class)
driverUrl =       (JDBC driver URL)
user =            (database user, might need administrator rights !)
schema =          (database schema)
filter =          (name filter for tables, views, etc.)
scanner.classes = (comma separated list of scanner classes to run)
replace =         (automatically replaces written xml result file, true oder false)
exportName =      (name pattern of the written xml file)
Folder "templates" contains several sample property files for various databases like Oracle, Postgres or MySql.

Comparer-Properties

comparer.log.charset =           (charset of the generated log file)
comparer.ignoreIndexNames =      (ignore index names, true oder false)
comparer.log.name =              (name pattern of the generated log file)
comparer.ignoreForeignKeyNames = (ignore foreign key names, true oder false)
comparer.ignorePrivileges =      (ignore privileges, true oder false)
comparer.ignorePrimaryKeyNames = (ignore primary key names, true oder false)
comparer.ignoreColumnTypes =     (ignore data types, true oder false)
comparer.ignoreSchemas =         (ignore schemas, true oder false)
comparer.database.1 =            (first, previously scanned database as xml)
comparer.database.2 =            (second, previously scanned database as xml)
comparer.log.replace =           (automatically overwrite the generated log file, true oder false)
comparer.column.aliases =        (mappings for column aliases)
comparer.log.separator =         (separator char of the generated log file)

Available Scanner

default.TableScanner
default.ViewScanner
royalty.TypeScanner

oracle.SequenceScanner
oracle.SynonymScanner
oracle.TriggerScanner

postgres.SequenceScanner
postgres.DomainScanner
postgres.TriggerScanner

firebird.GeneratorScanner
firebird.TriggerScanner

mysql.TriggerScanner

derby.SynonymScanner

File Suffixes

.djs = scanner configuration
.djc = comparison configuration
.djd = database dump

Get it here:

Downloads

Soon to come:
Professional version with more scanners for privileges, types, triggers and other vendor specific objects.


February 1, 2009

Various small how-tos for the JSF beginner

A - Changing the locale of the current running session

1. Set up a managed session bean for storing the locale.

2. Intialize the locale from the browser settings:
public SessionBean() {
   FacesContext context = FacesContext.getCurrentInstance();
   HttpServletRequest request =
      (HttpServletRequest)context.getExternalContext().getRequest();
   sessionLocale = request.getLocale();

   if (!Locale.GERMAN.equals(sessionLocale) &&
       !Locale.ENGLISH.equals(sessionLocale)) {
      sessionLocale = Locale.ENGLISH;
   }
}
Make sure to fall back to your default locale in case of unknown locales.


3. Implement a simple getter and setter for the locale:
public Locale getLocale() {
   return sessionLocale;
}

public void setLocale(Locale sessionLocale) {
   this.sessionLocale = sessionLocale;
}
4. In every JSF page, ask for the current locale:
<f:view locale="#{sessionBean.locale}">

5. For switching the locale, add flag buttons or a user profile form.


B - Redirect to another place
void redirect(String url) {
   FacesContext context = FacesContext.getCurrentInstance();
   HttpServletResponse response = (HttpServletResponse)context.getExternalContext().getResponse();
   String path = context.getExternalContext().getRequestContextPath();
   response.sendRedirect(path + url);
}
The url given does not contain the application's context as prefix

C - Get a resource text for the current locale:
String getResourceText(String key) {
   String text = null;

   String bundleName = FacesContext.getCurrentInstance().getApplication().getMessageBundle();
   Locale loc = FacesContext.getCurrentInstance().getViewRoot().getLocale();
   ResourceBundle bundle = ResourceBundle.getBundle(bundleName, loc);

   try {
      text = bundle.getString(key);
   }
   catch (MissingResourceException e) {
      log.error(e);
      text = "! " + key + " !";
   }

   return text;
}

D - Get current session id
public String getSessionId() {
   FacesContext context = FacesContext.getCurrentInstance();
   return ((HttpSession)context.getExternalContext().getSession(false)).getId();
}

E - Get a request parameter
Object param = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap().get("theParam");

F - Clear input of text fields and selected drop-down-lists
public void clearFields() {
   UIViewRoot uiRoot = FacesContext.getCurrentInstance().getViewRoot();
   recurseChildren(uiRoot);
}

protected void recurseChildren(UIComponentBase comp) {
   for (int i = 0; i < comp.getChildren().size(); i++) {
      UIComponentBase child = (UIComponentBase)comp.getChildren().get(i);
      recurseChildren(child);
   }

   if (comp instanceof HtmlInputText || comp instanceof HtmlInputTextarea || comp instanceof HtmlSelectOneMenu) {
      ((UIInput)comp).setValue("");
   }
}

G - Clean up expired sessions

1. Write a session listener:
public class CleanupListener implements HttpSessionListener {
   public void sessionCreated(HttpSessionEvent event) {
   }

   public void sessionDestroyed(HttpSessionEvent event) { 
      // clean up right here ... 
   }
}
2. Register the listener in the web.xml:
<listener>
   <listener-class>
      the.full.package.name.CleanupListener
   </listener-class>
</listener>

H - Fix the character encoding
If you get wrong page encodings for JSF post requests although you specified the content in every single page, setting up a servlet filter helps. Example to force UTF-8:
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) {
   response.setCharacterEncoding("UTF-8");
   request.setCharacterEncoding("UTF-8");