The mission is to deploy a CRUD implementation in all three frameworks by reverse engineering my schema from an existing Postgres Database which I will construct with raw SQL. Later I would like to add some data to the tables so let’s see how it handles some ETL (export transform load) in the form of a CSV file into some REST calls that I’d implement or some other type of messaging.

What I did not do! I think O’Reilly has the most comprehensive map of the history of all computer programming languages, however, GitHub has a list of languages that would seem to be current or relevant. Granted that some of this, to be effective, would mean investigating popular frameworks within the domain of languages. Well of that semi-complete list from GitHub I picked out this set: php, go, lua, haskell, erlang, scala, clojure, perl, javascript, rhino, nodejs, iOS, Objective-C, C++, C, Pascal, Pro-SQL, CoffeeScript, OCaml, Scheme, tcl, Smalltalk, Visual Basic. I think they are the most relevant. As it goes, however, either they do not reverse engineer schema from a PG (postgres) connection like most ORMs, or they do not have web or other application frameworks in order for a user to interact with the data, and many do not have IDEs or version managers they way that Ruby and Python do. (I cover the IDE topic later.) I think I picked the sweetspot of frameworks to test and skipped the ones that would distract me from the task.

Code Wars: PHP vs Ruby vs Python – Who Reigns Supreme [Infographic]

Let’s start with the schema design. The actual SQL is here. The “message” represents an ISO8583 message. You can follow the link to read more about the message. What’s important to know is that it represents the standard message used between certified credit card acquirers and their associations like Visa, MasterCard, Amex, Discover and many others.  It also represents the message used between the associations and their issuing processors. The message format is also reused by many POS terminal software vendors as well as gateway and technical acquirers/processors. Each association has slightly different implementations depending on their specific needs, however, most of the fields have common names and usage.

When building an endpoint or a gateway or any point in between there is a need to support ISO8583 and a bigger need to test the endpoint. As part of the testing phase it’s important to build a test harness that can generate the necessary test transactions. Depending the application’s position in the network it will have different testing needs. One thing for certain is that the messages and their contents need to be modeled, testable and repeatable. One huge challenge is TDD (test driven development) and another is simple regression testing. It’s my personal belief that a well defined toolset could be deployed in such a way that vendors across the board could contribute data a code for regression testing thus reducing the load on everyone. PS: while this mission is ISO8583 there is no reason why a request/response transaction could not be JSON, XML or S-exp; it’s a general enough schema.

• TABLE: message_field_dictionary – this is a list of the field names and their formats. Nothing else.
  • FIELD: id (PK)
  • FIELD: field_id (FK)
  • FIELD: field_name
  • FIELD: field_description
  • FIELD: field_format (i.e.; ‘YYYYMMDD’)
  • FIELD: data_types (a, an, ans)
  • FIELD: default_values (i.e.; ’1′, ’2′, ’3′, ‘abc’…)
• TABLE: message_request – this is the set of fields used in a particular request transaction. It is also possible that this represents the incoming request pattern. If the pattern does not match then that’s separate issue.
  • FIELD: id (PK)
  • FIELD: test_case_id (FK)
  • FIELD: field_id
  • FIELD: request_type
  • FIELD: field_value
• TABLE: message_response – this is a set of fields in the response generated from the response. This might also represent a response message based on the incoming request pattern.
  • FIELD: id (PK)
  • FIELD: test_case_id (FK)
  • FIELD: field_id
  • FIELD: response_type (i.e.; absent, required, optional, conditional)
  • FIELD: field_value (i.e.; a value, regex, or a combination set, private function or other field_id)
• TABLE: message_test_cases
  • FIELD: id (PK)
  • FIELD: test_case_id (FK)
  • FIELD: short_name (FK)
  • FIELD: description
  • FIELD: expected_results
  • FIELD: elapsed_ceiling
  • FIELD: is_active
  • FIELD: group_name
  • FIELD: sub_group_name
• TABLE: message_test_results
  • FIELD: id (PK)
  • FIELD: test_case_id
  • FIELD: started
  • FIELD: finished
  • FIELD: elapsed_time
  • FIELD: results
  • FIELD: request
  • FIELD: response
  • FIELD: errors
  • FIELD: trace
• TABLE: test_cards – I decided to put the test cards in a separate table because if contributors provided test transactions the actual card numbers and magstripes would be considered confidential data… and the associations do not want anyone recording that info with one possible exception.
  • FIELD: id – (PK)
  • FIELD: card_number (FK)
  • FIELD: serial_number (FK)
  • FIELD: expiration_date
  • FIELD: issue_date
  • FIELD: street
  • FIELD: zipcode
  • FIELD: pin
  • FIELD: atm_pin
  • FIELD: CVV
  • FIELD: CVV2
  • FIELD: track1
  • FIELD: track2
  • FIELD: track3
  • FIELD: reset_balance
  • FIELD: is_decrement
  • FIELD: actual_balance
  • FIELD: open_to_buy

The schema is self explanatory. I did not create any real indexes. I’m not certain (right now) whether I’m going to create any FKs or referential integrity. It depends on how much reverse engineering the different frameworks are going to execute. One thing for sure, this is not intended to be a lesson in DB design. Maybe another time. FKs might be required in order for the reverse engineering to work properly. Specially if I use tables to populate pulldowns and select lists.

The IDE I decided to use was RubyMine, PyCharm and IntelliJ. It is purely by coincidence that I decided to use this family of IDE from jetbrains. (for the record I’m currently using the demo version. I’m hoping that the licenses do not expire before I finish this article… this paragraph was written before coding began). What makes them interesting is that they support Django, Rails and Grails out of the box. After agonizing over it IntelliJ was the only reason why I included Grails. Java does not have a version manager like Ruby or Python, however, you can get there by collecting your jar files in a single folder alongside the JDK you’re using. And since many binary distributions of the JDK are in version folders it makes resetting the CLASSPATH and PATH easier… but still more manual than the ruby and python versions.

RubyMine heads up. When I originally installed RubyMine I had not installed RVM. I found a post from the folks at JetBrains about version 2.0.2 where they probably added RVM support. Anyway they said it just worked. That after a restart RubyMine would give you access to your tools. That was not the case. I had to take one extra step. I had to go into the preferences and navigate through the “Ruby and SDK” page. I also navigated through the gem sets for good measure. Now when I created my project I had access to my Rails version; previously unknown. I had a similar problem with PyCharm and it’s support for VirtualEnv but I will have to verify it with Django. (Shame on me. My desktop virtualenv did not have django installed. I will likely have to do a complete install based on my notes which were originally installed on my virtual machine and not my desktop).

Getting Started

Now that I’ve managed to crawl through the minutia of project preparation it’s time to start putting the project together. So Now that I have installed IntelliJ, PyCharm and RubyMine I have to create my project. I’m calling the project crud_fest_rb, crud_fest_py, and crud_fest_j.

Creating an empty rails and an empty django project was pretty simple. Specially after all the setup I’ve done in preparation. The one observation I’ll make is that there are a lot more artifacts in an empty rails project than there are in a similar django project. The Grails project has a lot more artifacts than that and since there is a compilation step it takes a lot longer to get started. One thing that bugs me about IntelliJ is that it starts the browser to a default page once it’s ready to run.

This project was not meant to be a JetBrains tutorial, however, I’ll mention a few more things. Rails started right away. Django required the user to enable the admin function, update the settings file to point to the proper DB, and then you had to manually execute the ‘manage.py syncdb’ command in order to create a default admin  user. This step will be required later in order to sync the db schema to the model. Rails and Grails are still under investigation.

One nice thing about the Rails and Django projects is that they respect SQLite3. Not that Grails goes out of it’s way to reject SQLite3 but the support is hard to come by. This means that when I put my SQL together it will need to support but SQLite3 and H2. Which will probably work but what a pain. I suppose I could use Postgres but there is nothing easier than a ‘cp’ command to reset the DB to it’s default. And if this project is successful then copying the output table to the target application means that that the SQLite DB file is now the config file.

The Schema

I have created the SQL. I was not going to embed the code directly but the Gist is here. It’s only six small tables and the foreign keys are few. There are a few constraints which should be removed when the constraints are fully represented in code instead of schema. (Keep in mind that when calculating performance things like O(log<n>) no longer makes sense when there are cascading reads based on constraints. And frankly it does not make any sense to have the constraint modeled in code and SQL at the same time.)

Import the Schema

… into the project is the next step.

Django

PyCharm could execute the following commands but currently it feels better to execute them manually from the command line.

The first step is making sure that the DBs are configured properly in the settings.py file. In a recent version of Django the developers made it possible and easy to support multiple and different databases simultaneously. That means I could connect to one DB for one set of actions and another DB for a different set. There are plenty of interesting use-cases here. So let’s configure the DB:

DATABASES = {
 'default': {
     'ENGINE': 'django.db.backends.sqlite3', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
     'NAME': '/tmp/crud_fest.db', # Or path to database file if using sqlite3.
     'USER': '', # Not used with sqlite3.
     'PASSWORD': '', # Not used with sqlite3.
     'HOST': '', # Set to empty string for localhost. Not used with sqlite3.
     'PORT': '', # Set to empty string for default. Not used with sqlite3.
 },
 'messages': {
     'ENGINE': 'django.db.backends.sqlite3', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
     'NAME': '/tmp/crud_fest_py.db', # Or path to database file if using sqlite3.
     'USER': '', # Not used with sqlite3.
     'PASSWORD': '', # Not used with sqlite3.
     'HOST': '', # Set to empty string for localhost. Not used with sqlite3.
     'PORT': '', # Set to empty string for default. Not used with sqlite3.
 }
}

(The indenting is not exact here but that’s a WordPress thing)

The second step is to make certain that the admin functionality that we previous enabled (and now stored in the default database: crud_fest.db) has been sync’d properly. (you’ll need to answer some questions about the admin user including the username and password.)

/Users/rbucker/git/flafreeit/crud_fest_py/manage.py syncdb

Now that the admin tables have been created, you’ll need to create the actual crud_fest_py tables.

cd ${HOME}/git/flafreeit/crud_fest_db
sqlite3 /tmp/crud_fest_py.db <./setup.sql

And then the last step is to dump or reverse engineer the table(s) into a models.py file.

/Users/rbucker/git/flafreeit/crud_fest_py/manage.py inspectdb > /Users/rbucker/git/flafreeit/crud_fest_py/test_config/models.py

Looking at the models.py file you’ll see something like (looks like I have some trimming to do; the admin tables were included):

# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
# * Rearrange models' order
# * Make sure each model has one field with primary_key=True
# Feel free to rename the models, but don't rename db_table values or field names.
#
# Also note: You'll have to insert the output of 'django-admin.py sqlcustom [appname]'
# into your database.
from django.db import models
class MessageFieldDictionary(models.Model):
    id = models.IntegerField(null=True, primary_key=True, blank=True)
    field_id = models.IntegerField(unique=True, null=True, blank=True)
    field_name = models.CharField(unique=True, max_length=25, blank=True)
    field_description = models.TextField(blank=True)
    field_format = models.CharField(max_length=200, blank=True)
    data_types = models.CharField(max_length=200, blank=True)
    default_values = models.CharField(max_length=200, blank=True)
    class Meta:
        db_table = u'message_field_dictionary'

This is just a sample of the tables that inspectdb generated… because there is one final step. Now that we have a models.py file with the individual schema we need to tell Django about the tables and the individual fields that need to be editable. There are some shortcuts; the online docs are really good. So we are going to create an admin.py file like this.

from django.contrib import admin
from myproject.myapp.models import MessageFieldDictionary

class MessageFieldDictionaryAdmin(admin.ModelAdmin):
    pass
admin.site.register(MessageFieldDictionary, MessageFieldDictionaryAdmin)

Once this last step is completed then you need to launch the django server and navigate to the admin site with your favorite browser. All of your tables should be there. You might still need to customize the widgets but this is the place where we stop.

One Final note. Sadly my decision to use SQLite means that I might have to do all of this all over again. It seems that the foreign keys have not been incorporated into the results of the ‘inspectdb’ command. There is a pragme in SQLite that enables FKs but it has to be compiled in beforehand. There was also at least one multi field constraint that does not appear.

But this is a good place to stop for now.

Rails

The first thing I noticed is that there is no code in Rails for reverse engineering a legacy database the way that Django does. So I had to install a missing gem.

gem install rmre

Then I had to reverse engineer my DB.

cd ${HOME}/git/flafreeit/crud_fest_rb
rmre -d /tmp/crud_fest_py.db -o ./app/models/

After the command completed I was returned to the command line. There were no error messages so I assume that it completed OK. I looked in the ./app/models/ directory and noticed that there were a handful of new files. These files were 1:1 with the table names. Here’s an example:

class MessageFieldDictionary < ActiveRecord::Base
    set_table_name 'message_field_dictionary'
end

This is a little hinky because none of the field names or types have been included. After doing some searching I found that this is OK and that ActiveRecord will fill in the blanks. I don’t know if I buy that. I like that Django fills in the holes and this sparse programming … *sigh*.

There is another command that is interesting:

rake db:schema:dump

This will dump the schema into a file db/schema.rb. It represents the complete schema. I suppose that this code could be copied to the model files. But for the moment this is not required… I think the db:migrate command will regenerate this db/schema.rb file when it completes.

Another caveat here is that it is possible to have multiple databases configured in the database.yml file. The difference, however, is that ActiveRecord needs to know which database goes with which definition. So there is some manual work to be done here. There are some simple google searches you can execute and most of them make perfect sense. It’s a little beyond the scope here even though I described the python version.

Grails

Grails supports multiple databases in it’s DataSource.groovy file. Since I’m working with Grails 2.0.0 there is a possibility that the latest Hibernate is included. Hibernate is the ORM that Grails uses to communicate with the DB. But the tool for reverse engineering needs to be installed.

cd ${HOME}/git/flafreeit/crud_fest_j
grails install-plugin db-reverse-engineer

The output was pretty simple.

rbucker@rmac[crud_fest_j]$ grails install-plugin db-reverse-engineer
| Plugin installed.

… but I have no idea which version was installed. So we move forward for the moment. The next step is to locate the h2 command line version.

java -cp ${HOME}/lib/grails-2.0.0/lib/com.h2database/h2/jars/h2-1.2.147.jar org.h2.tools.Shell

You’ll need to answer a few default questions. You can accept the default values for the moment.

Now that H2 is running and pointing to the same repository as the DataSource.groovy, now we need to run the SQL to create the database tables as we did previously. (I had to make some changes to the code because there are some differences with H2.

Now we try to do the reverse engineering…. actually, it’s not going to happen. I’m going to leave this up to the reader to complete. And if anyone wants to contribute, please, by all means. For the moment This is the end of the road for this project.

Conclusion

It is safe to say that I’m done with this project. While it seems plausible that I could reverse engineer a database using the Grails Plugin – the amount of configuration required just amazes me. Spring is heavily dependent on XML config files and it appears that Grails uses some of each. One thing for certain is that Java has these huge namespaces everywhere so just the slights config requirement for reverse engineering is so incredibly painful. I’m really surprised that the Grails guys did not do more Groovy scripting for this sort of thing.

Ruby/Rails on the other hand still required an outside GEM in order to perform the reverse engineering. I’m surprised that with Rails3 and 3.2 that they never addressed the issue directly. And not to mention that the resulting models were still sparsely emitted.

Finally, Django seems to have gotten it right. It emits the code in it’s entirety. Getting to the CRUD is a simple matter of some manual labor which the user could script easy enough. My vote is going to Python as the all around winner and Ruby a close second. The Java code is on hold, maybe we can call this a “did not finish”. The overall performance of the dependencies and the compile step make it a less valuable experience.And let’s not forget the JDK version madness.

So for the time being I’m inclined to purchase a license for PyCharm and RubyMine just because this is where I’m going to be spending my time for a little while… and it’s my money.