Database Refactoring | Databases Due to variations in data types and SQL syntax, the following databases are currently supported out of the box. Additional databases as well as enhancements to support for the below databases are available through Liquibase extensions Please find further information about which JDBC driver, URL, classes etc. these databases need, by clicking on the database-specific links in the table below. As of Liquibase v3.1, support for some less common databases has been moved out of Liquibase core and into extensions. To re-enable support for these databases, install the corresponding extension: Since Liquibase is built on top of standard JDBC, the only ties it has to the underlying database is through the SQL that can vary from DBMS to DBMS. You may also run into problem with the SQL generated by the change/refactoring tags on unsupported databases. If, for some reason, the DatabaseChangeLog table cannot be created on your database, the base creation SQL that you can modify to suit your needs is:
Oracle Date functions and parameters Oracle Date Functions Version 10.1 Date Current Date SELECT CURRENT_DATE FROM dual; SELECT SYSDATE FROM dual; Current Time SELECT CURRENT_TIME FROM dual; Formats J Julian Date -- Day -- Month -- Year -- Fill Mode Formats That Can Be Used With ROUND And TRUNC Functions - One greater than the first two digits of a four-digit year - Year (rounds up on July 1) - ISO Year - Quarter (rounds up on 16th day of the 2nd month of the quarter) - Month (rounds up on the sixteenth day) - Same day of the week as the first day of the year - Same day of the week as the first day of the ISO year - Same day of the week as the first day of the month - Day - Starting day of the week - Hours - Minute SELECT SYSDATE + 1 FROM dual; SELECT SYSDATE - 1 FROM dual; Add A Month To A Date ADD_MONTHS( , SELECT add_months(SYSDATE, 2) FROM dual; Returns the current date of the server as a value in the Gregorian calendar of datatype DATE col sessiontimezone format a30 SELECT sessiontimezone, current_date FROM dual; SELECT DUMP(SYSDATE) FROM dual; FROM t;
The 38 Subsystems of ETL -- InformationWeek Three little letters -- E,T, and L -- obscure the reality of 38 subsystems vital to successful data warehousing. The extract-transform-load (ETL) system, or more informally, the "back room," is often estimated to consume 70 percent of the time and effort of building a data warehouse. But there hasn't been enough careful thinking about just why the ETL system is so complex and resource intensive. Everyone understands the three letters: You get the data out of its original source location (E), you do something to it (T), and then you load it (L) into a final set of tables for the users to query. When asked about breaking down the three big steps, many designers say, "Well, that depends." It depends on the source, it depends on funny data idiosyncrasies, it depends on the scripting languages and ETL tools available, it depends on the skills of the in-house staff, and it depends on the query and reporting tools the end users have. The 38 Subsystems Extract system. More Insights
Database Refactoring | Bestpractices This page describes a number of best practices that you can apply on your project. Organizing your changeLogs The most common way to organize your changelogs is by major release. Directory Structure com example db changelog db.changelog-master.xml db.changelog-1.0.xml db.changelog-1.1.xml db.changelog-2.0.xml DatabasePool.java AbstractDAO.java db.changelog-master.xml The master.xml includes the changelog for the releases in the correct order. The db.changelog-master.xml is the changelog you pass to all Liquibase calls. Managing Stored Procedures Try to maintain separate changelog for Stored Procedures and use runOnChange=”true”. One Change per ChangeSet As far as possible, Avoid multiple changes per changeset to avoid failed autocommit statements that can leave the database in an unexpected state. ChangeSet Ids Choose what works for you. Document ChangeSets Use <comments> in the change sets. Always think about rollback Reference Data Management Leverage Liquibase to manage your Reference Data.
Traitements temporels en SQL - Cl La difficulté des calculs portant sur des dates ou des horaires (et parfois les deux) est lié à la codification même de la mesure du temps ainsi qu'au fait que le temps est une entropie (disons le, même une "isentroprie"), c'est à dire qu'il s'écoule de manière uniforme (iso) et dans un seul sens irréversible (vers le futur). La mesure du temps n'obéit pas à des règles conventionnelles, comme le comptage décimal ou l'origine zéro. En effet : Les années comptent tantôt 365 tantôt 366 jours en fait 365 jours 5 heures 48 min. 45,97 sec... Les mois de 29 à 31 jours Il n'y a pas d'année 0, mais il y a des années négatives (avant JC !) Les siècles et millénaires commencent par une année unitaire (1, 1901, 2001...) Tous ces éléments font que les calculs, notamment de comptage du temps obéissent à des algorithmes complexes. De ce fait, la norme SQL 2, propose une solution générale assez intelligente. Le lundi est considéré comme le premier jour de la semaine. 1.1. La norme propose les types :
Open source data quality, data profiling and master data management | DataCleaner Database Refactoring | Faq What license is Liquibase released under? Liquibase is released under the Apache License, version 2.0. Where can I get the source code? The source is available from the main download page How does Liquibase compare to tools that compare development database with production databases to generate change lists? Liquibase works better because it understands what the changes are. What if multiple processes/application servers attempt to migrate the database at the same time? Liquibase uses a distributed locking system to only allow one process to update the database at a time. Does Liquibase work with branches? Yes. Why do I have to specify an “author” tag? Why not just an “id” tag? What if I really don’t want to specify an author tag? There are times an organization would not want to have changes tied back to a particular individual or if the original author isn’t actually known. How can I specify vendor specific features such as ENGINE=InnoDB in MySQL?
Joda Time - Java date and time API - User Guide Architecture Overview The major building blocks of joda time are introduced below. These are the concepts of instant, interval, duration, period, chronology and timezones. We then say a few words about the role of interfaces in the library design, which is a little different than the norm. We end with a few words on package structure. Instants The most frequently used concept in Joda-Time is that of the instant. Within Joda-Time an instant is represented by the ReadableInstant interface. The millisecond instant can be converted to any date time field using a Chronology. We discuss the chronology concept a litte further on in this overview. A companion mutable class to DateTime is MutableDateTime. Other implementations of ReadableInstant include Instant and DateMidnight. Fields The main API of DateTime has been kept small, limited to just get methods for each calendar field. Properties There is much more power available, however, through the use of what is termed a property. Intervals Periods
Data Modeling & Profiling Tool: SQL Power Architect | SQL Power Software Features Accesses source databases via JDBC Connects to multiple source databases concurrently Compares data models & database structures and identifies discrepancies Drag-and-drop source tables & columns into the playpen Remembers the origin of each column Generates source-to-target visual Mapping Reports Forward/reverse engineers PostgreSQL, Oracle, MS SQL Server & more Saves source data structure snapshots in the project, so you can work remotely All project data is stored in an easy-to-parse XML format OLAP schema modeling: Cubes, Measures, Dimensions, Hierarchies & Levels Visual Mapping Report for data lineage tracking Free Download (Open Source, GPL v.3 licensed) (Community Edition) Data Architects, DBA's, Analysts and Designers rely on Data Modeling tools to facilitate and simplify their data Modeling efforts, while maximizing the use of their resources. SQL Power Architect can also open multiple source databases concurrently, even if they are from competing database vendors.
Liquibase