Skip to content

Tilda Command Line Utilities

Jump to bottom
Laurent Hasson edited this page Oct 25, 2018 · 12 revisions

Tilda provides a number of command-line utilities to manage various processes involved when using the framework. Key utilities are available to generate code, migrate database, reverse engineer existing databases and so on. Additionally, there some purely utilitarian utilities that have enabled us to create cross-platform scripts using basic tasks such as counting lines, generating a password hash etc...

🎈 NOTE: All those utilities provide command-line help if called without any parameter, and expect the classpath to be set up properly when calling them via a java.exe command line. A typical Windows CMD script can be found in java_run.cmd.

🎈 NOTE: All those utilities (all Tilda code in general) use Log4J2 for logging purpose and employ an Ultra-Logging philosophy to generate richly detailed logs that document every step involved in a given process. Ultra-Logging is implemented in the project to "ensure that bugs or issues that surface can eventually be all traced and debugged post-facto out of log files". That makes the logs (1) very informative about the details of what is going on, but also (2) quite verbose.

🎈 NOTE: All those utilities make use of tongue-in-cheek ASCII art for key messages, including success. If the utility is successful, you'll get a Woohoo!

================================================================================
               __    __                  _                          _   
              / / /\ \ \  ___     ___   | |__     ___     ___      / \  
              \ \/  \/ / / _ \   / _ \  | '_ \   / _ \   / _ \    /  /  
               \  /\  / | (_) | | (_) | | | | | | (_) | | (_) |  /\_/   
                \/  \/   \___/   \___/  |_| |_|  \___/   \___/   \/     
 The database was automatically migrated to match the Application's data model.    
=================================================================================

Main Utilities

Gen

This utility is central to Tilda as it takes the JSON files that define your models and generate a variety of artifacts, including Java code for ORM functionality, JSON and CSV import and export code generation, SQL scripts, a Security layer, Migration scripts, as well as fully browsable and searchable documentation.

Central to Tilda is the promise to make complex data models Transparent throughout all their incarnations (SQL to Java to the Browser) and easy to iteratively develop and evolve. Building a V1 application depending on a complex data model is often easy. To keep the model flexible, clean and easy to modify across multiple product versions over time is always a challenge. Gen's implementation addresses several common issues:

  • Generated code (whether Java, SQL or JavaScript) is human readable and very consistent.
  • Generated code is fully documented with JavaDoc
  • Many data model concepts are implemented as direct compile time constructs (tables, views, columns, aggregates, identity, common queries, indices...) so changing something automatically translates into a compile-time change that can then easily be addressed through compiler warnings/errors across a large code base. This is different from many other frameworks that manage themselves through mostly runtime capabilities. For example, an index definition generates a LookupByXXX or LookupWhereXXX function, and so making any changes will cause all code locations referring to the index fail compilation. Similarly, making a field not-nullable changes the signature of the Create function etc...
  • The model is augmented with enough meta-data even at the database level to allow for rich automated migration capabilities (see below).
  • Fully browsable documentation is generated for the database implementation and is always kept in sync by design, including view specifications (e.g., dependencies, formulas, filters...)

Syntax

java_run tilda.Gen C:/Projects/OSS/Tilda/src/tilda/data/_tilda.Tilda.json ^
                   C:/Projects/Xyz/src/com/myco/toto/data/_tilda.Toto.json

Migrate

This utility takes the model definitions available in a deployed environment and will migrate the database target to match. It is multi-tenant aware and can migrate multiple databases in a cluster simultaneously, including multiple databases at different version levels.

Migrate automates many database migration tasks such as:

  • Adding new columns, tables, views
  • Modifying a column's type or nullable status
  • Renaming columns, tables and views
  • Adding/removing/changing primary key definitions
  • Adding/removing/changing unique index definitions
  • Adding new non-unique indices
  • Manage additional constructs such as complex views with pivoting, formulas or time series, along with other database artifacts such as the Refill_* functions.
  • Maintain standard tilda_app and tilda_readonly roles and ACL rules for managed artifacts.

Migrate understands the full data model being deployed, and assumes the model has been properly validated by Gen previously. It can do interesting things such as:

  • Change the identify of a table
  • Manage indices
  • Change the size of string columns
  • Convert an int column to a string
  • Keep dependent views in sync

One important consideration is that Migrate is non destructive. This means a few things:

  • If a column no longer exists in the model, it's not removed from the table unless explicitly stated in a migration element.
  • If a non unique index exists in the database but not in the model, it won't be affected.
  • If a developer in a personal schema, creates views outside of Tilda that depend on Tilda-managed entities, Migrate will attempt to migrate those as well if possible, i.e., capture the DDL for those views, including comments and ACL and drop them and restore them as appropriate.

Migrate is pretty powerful but has limitations nevertheless that are pretty much intrinsic to software development life cycle rules of APIs in general:

  • If you remove a column that is used by a Tilda view, you'll get an error in Gen and will need to correct the model. However, if an external view (non managed) depends on a deleted column, Migrate won't be able to handle it.
  • If you add a new column that is not nullable and doesn't specify a default value, migration will fail.
  • If you shrink a column's string size from 1024 to 10 for example and there are values in the database with a length > 10, migration will fail.
  • If you change a column from nullable to not nullable and there are null values in the database, migration will fail.
  • If you change the type of a column and some values can't be converted (e.g., String to Int with a value of 'abc' in the database), migration will fail.
  • If you change an identity of table (a primary key definition of a unique index) and there are duplicate rows in the database, migration will fail.

When migration fails, it typically requires human intervention to either "fix" the data model, or manually migrate the database (i.e., creating a separate migration script to address any of those issues). These limitations are pretty standard in any type of software that defines interfaces and APIs (which a data-driven model sort of does). For example, if you remove a public method in a public class that is part of an API, you'll break any software that was depending on that method. The same issue arises for columns in tables.

The same way Gen operates, Migrate is there to support a rapid iterative development process where "breaking the schema" is (1) encouraged, and (2) easily manageable. In a project involving over 1000 tables and views across a couple dozen schemas, we have been able to maintain a pretty clean model across many product releases and keep a pretty sane overall data model for our applications.

To run the utility is straightforward: just call it with a classpath that includes your runtime environment (i.e., your JAR files). The utility will automatically identify all the Tilda resources that were generated from the Gen utility and included as part of your build process.

java_run tilda.Migrate

As for all Tilda code, we use ultra-logging techniques that generate rich detailed logs of the whole process including each model found in the runtime environment, analysis steps, and migration actions.

🎈 NOTE: The utility will remind you with some ascii art that it's always a good idea to have a backup before performing a migration. Although the Tilda project strives to protect your data and perform non-destructive migrations, there is always the possibility of a bug somewhere or an unforeseen condition.

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!! A MIGRATION HAS BEEN REQUESTED. AS A RESULT, DATA IN YOUR DATABASE MAY BE CHANGED.
!!!     ______ ____  ______   ____   ___    ______ __ __ __  __ ____  _____    ___   
!!!    / ____// __ \/_  __/  / __ ) /   |  / ____// //_// / / // __ \/ ___/   /__ \ 
!!!   / / __ / / / / / /    / __  |/ /| | / /    / ,<  / / / // /_/ /\__ \     / _/ 
!!!  / /_/ // /_/ / / /    / /_/ // ___ |/ /___ / /| |/ /_/ // ____/___/ /    /_/   
!!!  \____/ \____/ /_/    /_____//_/  |_|\____//_/ |_|\____//_/    /____/    (_)    
!!!
!!! THE FOLLOWING DATABASE(S) WILL BE ANALYZED:
!!!     ===> MAIN: jdbc:postgresql://localhost/Pepper?reWriteBatchedInserts=true&USER=postgres
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

🎈 NOTE: You can run the migration utility as a database administrator, which is often useful in a development environment. In production however, running with a regular role is strongly recommended, and the utility will always warn you if you are using a superuser account. when running in a normal account, you will be prompted to escalate your role when Migrate deals with ACL definitions in the databse, which in some databases such as PostreSQL requires superuser abilities.

###################################################################################################################
###                                                                                                             ###
###  W A R N I N G :   T H I S   C O N N E C T I O N   U S E S   A   S U P E R U S E R   A C C O U N T   ! ! !  ###
###  =========================================================================================================  ###
###                                                                                                             ###
###  _|    _|    _|    _|_|_|       _|         _|_|    _|      _|    _|_|       _|_|_|  _|_|    _|_|_|    _|    ###
###  _|_|  _|  _|  _|    _|       _|  _|     _|      _|  _|  _|  _|  _|  _|       _|    _|  _|  _|      _|  _|  ###
###  _|  _|_|  _|  _|    _|       _|_|_|     _|  _|  _|  _|  _|  _|  _|  _|       _|    _|  _|  _|_|    _|_|_|  ###
###  _|    _|  _|  _|    _|       _|  _|     _|  _|  _|  _|  _|  _|  _|  _|       _|    _|  _|  _|      _|  _|  ###
###  _|    _|    _|      _|       _|  _|       _|_|    _|      _|    _|_|       _|_|_|  _|_|    _|_|_|  _|  _|  ###
###                                                                                                             ###
###################################################################################################################

🎈 NOTE: The utility requires "human attendance" to proceed by prompting the user to perform the analysis (entering 'y'), and after reviewing recommended migration actions, approve the actual migration process (by entering 'yes'). An additional step might be required when using a non superuser role to deploy the ACL information. the utility will prompt for a superuser id and password.

Docs

This utility extracts HTML documentation and SQL scripts from a deployed environment (via component JARs).

Reverse

This utility can take a pre-existing database schema and generate Tilda JSON model definition files automatically in most cases.

Load

This utility implements efficient CSV loading including various modes such as INSERT or UPSERT. It is multi-tenant aware and can import large CSV-based datasets across multiple databases in a cluster.

Import

This utility implements a general entry point for richer JSON-based dataset importing which allows more flexibility than the CSV-based load, and is backed by custom code plugin to load various aspects of your models. This utility is multi-tenant aware and can import multiple JSON-based sample datasets across multiple databases in a cluster

Secondary Helper Utilities

LineCount

This is a simple utility to count lines in a file. We encountered a case where we wanted to check the integrity of some CSV files that has over 100 million lines. Using traditional count utilities was a pain and often difficult for people working on Linux not familiar with all the command line richness that already exists there. So we created this simple utility that can take in multiple file names, and counts lines with regular progress updates.

Syntax

java_run tilda.LineCount <file_name>+

Sample Output

java_run tilda.LineCount C:\Projects\repos\CMSData\data\claims.csv C:\Projects\repos\CMSData\data\claims_processed.csv

1003.231139.527| Counting lines in 'C:\Projects\repos\CMSData\data\claims.csv'.
1003.231142.733| Processed 5,000,000 lines so far at 1,562,908.4 lines/s.
1003.231144.960| Processed 10,000,000 lines so far at 1,838,212.1 lines/s.
1003.231146.795| Processed 15,000,000 lines so far at 2,063,983.4 lines/s.
1003.231147.666| Processed 17,377,462 lines for 'C:\Projects\repos\CMSData\data\claims.csv' in 8s 143ms (2,133,846.8 lines/s).
1003.231147.666| Counting lines in 'C:\Projects\repos\CMSData\data\claims_processed.csv'.
1003.231150.288| Processed 5,000,000 lines so far at 1,906,961.0 lines/s.
1003.231152.571| Processed 10,000,000 lines so far at 2,043,553.1 lines/s.
1003.231154.644| Processed 13,622,500 lines for 'C:\Projects\repos\CMSData\data\claims_processed.csv' in 6s 971ms (1,953,980.1 lines/s).
1003.231154.644| DONE

PswdHash

Syntax

java_run tilda.PswdHash <clear_text>+

Sample Output

java_run tilda.PswdHash 1234567890

ErAyJqbYvpxujNXlXcbHkgyqo53xSquS1ePqk0DRyKTT0LjkMU8fbvExukvxzrkYarh8gBrw1clbG++4ztriuQ==

SearchAndReplace

Clone this wiki locally