Research: Cross platform coding / working / database modelling environments

Posted on January 1, 2011

2


In order to build something more meaningful on multiple platforms I am considering several changes in my – from origin – Microsoft based approach.

Coding environments

Conditions:

  1. Cross platform – Should run on all platforms I care about (Windows, Android, Linux, Apple)

First line of options:

  1. Java – Yes. Is quite OK, although by default not asynchronous (you use while(true) { // do something } in a separate Thread to create listeners and I had to create my own Event Listener / Dispatcher based on reflection to make it somewhat more “up to date” – works as a platform for most things I need to do Server side or as Client Side framework.
  2. Apache / JSP – Yes. Does take some work on Windows to run, but who cares when you have OSX or Ubuntu
  3. BlazeDS – Yes. More a protocol/framework-type of thingy than a coding environment, but hey… Runs on Windows, OSX and Ubuntu AND works together with Apache over port 80.
  4. Flash – Runs on all platforms – albeit with some limitations on Android for now (some online applications crashing) – offers me a very rich set of multi-media programming options including streaming video and audio and Socket connections

To avoid in my case:

  1. PhP – Every time I see PhP code I think: “What the Fuck!?”. I might be on the wrong track here according to those who actively use it, but the way you “code” in PhP is so far from what I am used to from C#, ActionScript and Java that I rather not dive into it at all. Also: after VBscript and includes in MS Active Server Pages (.asp for short) I am done with that kind of environments. Regardless of its popularity, support and large user base.
  2. Ruby and Rails – I like the basics, but do not like the environment. I rather develop my own RAD environments and code generators.

Relational Databases

Conditions:

  1. Cross platform – The engine and the available admin tools should be cross-platform. Meaning that I am not stuck to one specific platform.
  2. Android – As I will build stuff for Android, the databases themselves should be usable or portable there too.

First line options:

  1. MySQL – I never liked the stories about mySQL. The long lack of support for built in mechanisms for relational integrity and nested queries made me quiver. I always considered mySQL to be in the same robustness-league as MS Access. It now being owned by Oracle is not a good advertisement for me either.
  2. PostgreSQL – Seems to be more solid and “Enterprise ready” from the beginning, meaning that it should do the things I expect from working with MS SQL Server. It seems to be possible to use it on Android – also see this: pgQuilt.

Comparisons:

  1. WikiVS – gives an up to date overview of the two engines
  2. final.gov – gives you the state of things in 2005
  3. PostgreSQL – on Postgres vs mySQL
  4. Odesk – mainly showing the big difference in popularity / available jobs. Bottom line: being an mySQL artist will guarentee you a job.
  5. Stack overflow – some answers on a question stating their preferences on PostgreSQL and mySQL
  6. Another nerd-discussion on Stack Overflow – going into other aspects of PostgreSQL and mySQL. Postgress is labelled to be the more mature and stable one.

Database Management / Modelling tools for OSX

I use and used the tools of SQL Server and MS Access to do my data modelling on Windows. Using Wine, this will also run on Linux. For the Mac I have to find different solutions. Here is an overview of the applications which are at least close to what I am looking for.

And the winner for now is..

Almost driven to desperation and “I should write my own tool for this..” I found the tool that I would have written if everything else had failed.

  1. DBschema – 100 euro ex VAT for a personal license, 50 euro for a student license – It is built to reverse-engineer and update your database-schemas. It hot-updates your running app (which includes risks if you have no clue what you are doing) and has a beautiful connect-mechanism taking all the basic stuff out of your hands. Connecting to the database was like: “click, click, click, type to fill in password, select the tables and.. ..BLAM!” done. It has some bugs, but taken that the other apps I tried did not allow a visual drag / drop = create & alter they are forgivable.

Prelimanary conclusions on the applications below:

My findings until now (Januari 2011) are sad. None of the tools with a Visual Data Modeller – including commercial products like Aqua Studio –  seem to be able to update your existing Database Scheme using the Visual Data Modeller. Forward Engineering seems to be aimed at creating a new database. This sucks deeply for Database RAD and feels backwards with almost 20 years of MS Access and SQL Server which CAN do that.

From all the tools discussed, after DBschema, Aqua Studio, and Navicat Lite seem to be the best. Sadly Aqua Studio loses a lot of credits by not allowing you (apparently) to update your existing database schema from the ERD / Visual Data Model so you might as well use a “read only” tool to visualize your data models.

Oracle offers the worst experience of all, with their website that seemed to be styled in 1997 and a registration process that I was only able to pass as I guessed the software was expecting me to use a password like “peter123Kaptein” (using capitals, numbers and lowercase mixed) instead of something like “peterkaptein” or “peter123kaptein” (note to who cares: I never use my own name in my password). After the 1.4 GB download the application simply “dissappears” when I try to run it, showing only traces of what went wrong when I check the Console. What the fuck, Oracle?

Commercial tools – that do not do the job I want them to do

  1. Aqua Data Studio – 399 USD, Visual Data Modelling, Reverse engeneering, Updates Database Model – A nice and easy to use tool that delivers the essentials for Database RAD – you would think. Editing the Visual Data Model basically creates a new version. Changes are not implemented in your current model.
  2. DBvisualizer – 130 USD, read only Visual Data Models. It is not as easy to use for modelling the present schema as Aqua Studio and DBschema is – even with the flaws – way better. The automatic layout of visual database schemas kicks ass though. It simply just has the best routine to layout the visual schema according to the shortest paths between tables.
  3. SQLEditor – 79 USD, Visual Data Modelling, Reverse Engineering, no option to modify existing data model. After a first good impression (easy to get the data model) it becomes worse and worse. I can drag lines between objects, to create PK –> FK relationships, instead of creating new connections I “replace” old ones. The SQL generated drops tables instead of modifying my existing model. Seems unusable and unfinished.
  4. SQL Power Architect – 495 USD, Visual Data Modelling, Reverse engeneering, Does not update Database Model – When I start to diagram the data model, the product directly shows its weakness: the incoming and outgoing lines of the PK –> FK relationships all collect at the same point on an entity / table. This obfuscates the datamodel. For the price minus 100 USD I can get Aqua Data Studio which creates clear diagrams out of the box.
  5. Visual Paradigm UML – 69 USD for basic edition, 796 USD for professional edition including ERD / OML, UML, Data Modelling, Data Model round trip – first tests show that this tool is horrid for database ERD work. Pricy, very cluttered GUI and easy to get lost. Whatever they can deliver: I say no thanks.
  6. Database Visual Architect – 399 USD, ERM, Reverse Engineering, Data Modelling, Data Model round trip, same makers as Visual Paradigm UML, same issues

Free tools – that do not do the job I want them to do

  1. SQL Power Architect Community Edition – Free, Visual Data Modelling, Reverse engeneering, Does not update Database Model – See the review above. Even when free, this product does not convince. I rather spend 400 USD when it comes to productivity versus “For free”. When you have none to spare, this might be a good option.
  2. Oracle JDBuilder – After fighting the registration form – it refuses a password not conform the rules but does not tell you what the rules are (has to contain variation like: numbers, a capital, and some lowercase characters) I had to explicitly re-select: “I accept the conditions” even though it was already selected from my previous session, the download is 1.4 GB! When I try to start the installer I get an: “out of memory error” with 2 GB RAM available. Bottom line: What a crap. The Oracle online experience is that of 1997. The installer is shit, not showing any feedback except for the failures in the Console.
  3. Navicat Lite – Free, no Visual Data Modelling, Creation and change of tables and fields – Quite OK for the basics, regarding usability a good replacement for the standard tool that come with the PostgreSQL but not a full replacement.
  4. SquirrelSQL – Free, Visual Data Models: read only, a lot of steps required to do refactoring. Navicat Lite beats Squirrel in usability. Used in combination might help (visualize in SquirrelSQL what you do in Navicat). However: if your time is money, buy a product that delivers all in one. If you have none to spare, see SQL Power Architec as a better alternative.
Advertisements
Posted in: 5: Research