Octopus for Atlas

• Overview,
• Documentation,
• Download
• JavaDoc
• HowTos
• Bugs/ToDos/Requests
• Release History
• Presentations

Overview

Octopus Replicator replicates SQL tables. It can work between different database backends as long as they contain equivalent schemas (e.g. Atlas Geometry Database, Tag database, etc.). It is configured to replicate between Oracle, MySQL and SQLite, but other possibilities can be supported too: Other database and files: Access, DB2, MSQL, Paradox, CJDBC, Excel, Informix, McKoi, PostgreSQL, Sybase, Csv, Hypersonic, Instantdb, MySQL, Oracle, SQLite, QED and XML. Database backup/restore. Database synchronisation. Octopus contains default mapping rules, they can be customised if needed. Octopus Replicator works in two steps: Generation of database schema description and conversions scripts (generate). Actual database replication itself (load). Replication task is managed by three files: build.xml - The definitions of tasks and their options (in Ant XML format). In general, a User doesn't have to touch this file. build.properties - The replication parameters. This file has to be modified to describe requested databases. map.properties - The definition of custom mapping. It is only needed when non-standard mapping is required (like different length of varibles, etc.).

Documentation

Octopus uses following files:

Octopus	top level directory
Octopus/ant	directory fr configuration files
Octopus/ant/build.xml	Ant build file with instructions
Octopus/ant/build.properties	Ant properties file
Octopus/ant/map.properties	customisation mapping rules
Octopus/run	working directory (all files here are generated by Octopus)
Octopus/run/LoaderJob.olj	Loader task description
Octopus/run/sql	directory for SQL scripts
Octopus/run/xml	directory for auxiliary schema definitions
Octopus/run/doml	directory database schema in DOML format
Octopus/run/GeneratorLog*.txt	Generator task log
Octopus/run/LoaderLog*.txt	Loader task log

The package distribution is available here. Java 1.5+ and Ant 1.5+ is required to run.

Notes

• Because MySQL server 4.* doesn't support setFetchSize command, large tables should be replicated in pieces defined by hand. This can be easily done by modifying importDefintion tag in run/xml/ImportDefinition.oli file. The load task should then be run for each chosen interval or run/xml/ImportDefinition.oli file should contain several importDefinition elements, one for each interval.
  <importDefinition name="rome_tags" tableName="rome_tags" selectStatement="select * from rome_tags where EventNumber &gt; 10 and EventNumber &lt; 20;">
• Some MySQL tables can't be replicated to Oracle because they have too long names.

Atlas Modifications to Octopus

1. Atlas GeometriDB: Support for Oracle/varchar2 to MySQL/varchar, etc.: Standard Octopus doesn't support that because Atlas Oracle database contains things like varchar2(4000) while MySQL can't have varchar longer than varchar(255). (This is now supported by Atlas Patch to Octopus via map.properties file. Note, that those user-supplied rules can be highly unsecure.)
2. Atlas GeometriDB: Support for Oracle small case column names: Standard Octopus doesn't support that. (This was a bug of Octopus. It has been fixed.)
3. AtlasDD: Support for replication of all tables belonging to an Oracle Schema. (This was a missing feature in Octopus. It has been implemented and fed back to Octopus developers.)
4. AtlasDD: Unique Index on multiple tables. (This was a bug of Octopus. It has been fixed.)
5. AtlasCondDB: MySQL tables contain a Primary Index with the name "PRIMARY". (While some MySQL commands accept that, it is not generaly supported as MySQL identifier because "PRIMARY" is an MySQL reserved word.Atlas Octopus Patch now creates commands which accept "PRIMARY" as a Primary Index name. Note, that using reserved words as Identifiers is a bug in Database design.)
6. Atlas GeometriDB: Support for SQLite: SQLite is not supported by Octopus as it introduces plaform-dependence (SQLite JDBC driver is a simple JNI wrapper over SQLite C++ native API). (SQLite JDBC driver has been installed and interfaced to Octopus (JDBC drivers are boycoted by LCG and the request for SQLite JDBC support has been refused).)
7. Atlas TagDB: Support for type names with blanks. (Fixed in Octopus CVS and included.)
8. Atlas TagDB: Support for Oracle Synonyms: Standard Octopus doesn't see Oracle Synonyms. (Fix has been made and fed to Octopus. (Indexes are unvisible for Synonyms.))
9. Atlas GeometryDB: Skipping Oracle system tables.

HowTos

How To Install

1. Install Java 1.5+ and Ant 1.5+. If you are on CERN AFS, you can use CERN Java Infrastructure by calling . /afs/cern.ch/sw/lcg/external/Java/bin/setup.sh
2. Download Octopus and untar.
3. Go to Octopus/lib/octopus, edit configure.properties to point to your Java home, run sh configure.
4. Go to Octopus/ant.

How To Use

# Inside Octopus/ant,
# edit build.properties and map.properties to satisfy your requirements.
...

# Generate replication scripts.
# The work is being done in your Octopus/run directory.
# Octopus generates confguration files (top level one is LoaderJob.olj)
# and sql scripts (in sql subdiretory) to be used later for replication.
ant generate

# Replicate (create replica) using generated scripts.
ant load

# Do everything in one step (= clean, generate, load).
ant replicate

# Recompile Atlas modifications to Octopus.
# They are placed in src subdirectory and results in Octopus/lib/OctopusPatch.jar library.
# To do the recompilation, a User has to copy the whole OctopusReplicator package
# (not just its ant directory used to run).
# Other sources, which could be eventualy modified, can be downloaded
# from http://octopus.objectweb.org site and put into src directory.
ant patch

# Ask for help.
ant -projecthelp

Bugs/ToDos/Requests

Bugs:

• ...

ToDos:

• LDPATH is not needed since 100% Java SQLite exists.
• Oracle tnsnames.ora file should be used.

Requests:

• ...

Release History

• 0.9.0:
  • First standalone release.
• 1.0.0:
  • Works standalone with Oracle, MySQL, SQLite.
• 1.1.0:
  • NestedVM 100% Java SQLite driver is used.
  • Indexes are not created.
• 1.1.1:
  • Oracle system tables are skipped.
  • Oracle synonyms are only processed if requested (as they are slow).
• 1.1.2:
  • Oracle has schema prepended to table name.

Presentations

• 2004:
  • SQL Replications during Atlas SW WS, Dec'04 in CERN
  • SQL Replications and Distributed SQL Clusters during Pool WS, Dec'04 in CERN
• 2005:
  • Heterogeneus Replication with Octopus during Atlas DB WS, Dec'05 in CERN
• 2006:
  • Heterogeneous Replication with Octopus during Metadata WS, Jul'06 in Oxford