Which cross platform scripting language should we adopt for a group of DBAs?

Question

Hi there,

I wanted to get the community's feedback on a language choice our team is looking to make in the near future. We are a software developer, and I work in a team of Oracle and SQL Server DBAs supporting a cross platform Java application which runs on Oracle Application Server. We have SQL Server and Oracle code bases, and support customers on Windows, Solaris and Linux servers.

Many of the tasks we do on a frequent basis are insufficiently automated, and where they are, tend to be much more automated via shell scripts, with little equivalent functionality on Windows. Unfortunately, we now have this problem of redeveloping scripts and so on, on two platforms. So, I wish for us to choose a cross platform language to script in, instead of using Bash and awkwardly translating to Cygwin or Batch files where necessary.

It would need to be:

1. Dynamic (so don't suggest Java or C!)
2. Easily available on each platform (Windows, Solaris, Linux, perhaps AIX)
3. Require very little in the way of setup (root access not always available!)
4. Be easy for shell scripters, i.e. DBAs, to adopt, who are not hardcore developers.
5. Be easy to understand other people's code
6. Friendly with SQL Server and Oracle, without messing around.
7. A few nice XML features wouldn't go amiss.

It would be preferable if it would run on the JVM, since this will almost always be installed on every server (certainly on all application servers) and we have many Java developers in our company, so sticking to the JVM makes sense. This isn't exclusive though, since I know Python is a very viable language here.

I have created a list of options, but there may be more: Groovy, Scala, Jython, Python, Ruby, Perl.

No one has much experience of any, except I have quite a lot of Java and Groovy experience myself. We are looking for something dynamic, easy to pick up, will work with both SQL server and Oracle effortlessly, has some XML simplifying features, and that won't be a turnoff for DBAs. Many of us are very Bash orientated - what could move us away from this addiction?

What are people's opinions on this?

thanks!

Chris

Answer 1

Although I prefer working on the JVM, one thing that turns me off is having to spin up a JVM to run a script. If you can work in a REPL this is not such a big deal, but it really slows you down when doing edit-run-debug scripting.

Now of course Oracle has a lot of Java stuff where interaction moght be needed, but that is something only you can estimate how important it is. For plain Oracle DB work I have seen very little Java and lots fo PLSQL/SQL.

If your dba now do their work in bash, then they will very likely pickup perl in a short time as there is a nice, logical progression path.

Since ruby was designed to be an improved version of perl, it might fit in that category too. Actually python also.

Scala is statically typed like Java, albeit with much better type inference.

My recommendation would be to go the Perl route. The CPAN is its ace in the hole, you do not have to deal with the OO stuff which might turn off some DBA's (although it is there for the power users).

Answer 2

I've been in a similar situation, though on a small scale. The previous situation was that any automation on the SQL Server DBs was done with VBScript, which I did start out using. As I wanted something cross-platform (and less annoying than VBScript) I went with Python.

What I learnt is:

• Obviously you want a language that comes with libraries to access your databases comfortably. I wasn't too concerned with abstracting the differences away (ie, I still wrote SQL queries in the relevant dialect, with parameters). However, I'd be a bit less happy with PHP, for example, which has only very vendor-specific libraries and functions for certain databases. I see it's not on your list.
• THE major obstacle was authentication. If your SQL Server uses Windows domain authentication, you'll have to work to get in. Another system also had specific needs as it required RSA tokens to be supported.

For the second point, Python is quite versatile enough to work around the difficulties, but it was getting into "badly supported" territory, especially on Windows. It was easy to work around the first problem from a Windows host, and for a Unix host it is possible though not easy. If you're using SQL Server authentication, it becomes a lot easier.

From your other choices, I'd expect various ways of authenticating and DB drivers to exist for Perl, which philosophically would be easier for DBAs used to shell scripting. Ruby - no experience, but it tends to have spotty support for some of the odder authentication methods and connectors. Scala I'd expect to be a bit too much of a "programmer's programming language" -- OOO and FP? It's a very interesting language, but maybe not the one I'd chose at first. As for the rest of the Java-based options, I don't have an opinion, but do check that all the connection types you want to make are solidly supported.

Answer 3

You can opt for Python. Its dynamic(interpreted) , is available on Windows/Linux/Solaris, has easy to read syntax so that your code maintenance is easy. There modules/libraries for Oracle interaction and various other database servers as well. there are also library support for XML. All 7 points are covered.

Answer 4

If you want a dynamic language and there already a lot of Java developers in your company, then Groovy seems an obvious choice, as it's very easy for Java developers to pick up (also, you said you have some Groovy experience yourself).

Groovy runs on the JVM and has excellent support for working with XML. It also has provides a very straightforward syntax for working with relational databases.

It comes with a console and a shell (though I never use the shell) which make it really easy to test/run scripts or snippets of Groovy code.

Answer 5

The XML thing almost calls for Scala. Now, I love Scala, but I suggest Python here.

Answer 6

I think your best three options are Groovy, Python, and Scala. All three let you write code at a high level (compared to C/Java). Python has its own perfectly adequate DB bindings, and Groovy and Scala can use ones made for Java.

The advantages of Python are that it is widely used already, so there are tons of tools, libraries, expertise, etc. available around it. It has a particularly clean syntax, which makes working with it aesthetically pleasing. The disadvantages are that it is slow (which may not be an issue for you), untyped (so you have runtime errors instead of compile-time errors), and you can't really switch back and forth between Jython and Python, so you have to pick whether you want the large amount of Python stuff, or the huge amount of Java stuff, minus a lot of the nice Python stuff.

The advantages of Groovy are that you know it already and it interoperates well with Java libraries. Its disadvantages are also slowness and lack of static typing. (So in contrast to Python, the choice is: do you value Python's clean syntax and wide adoption more, or do you value the vast set of Java libraries more in a language made to work well in that environment?)

The advantages of Scala are that it is statically typed (i.e. if the code gets past the compiler, it has a greater chance of working), is fast (as fast as Java if you care to work hard enough), and interoperates well with Java libraries. The disadvantages are that it imposes a bit more work on you to make the static typing work (though far, far less than Java while simultaneously being more safe), and that the canonical style for Scala is a hybrid object/functional blend that feels more different than the other two (and thus requires more training to use at full effectiveness IMO). In contrast to Groovy, the question would be whether familiarity and ease of getting started is more important than speed and correctness.

Personally, I now do almost all of my work in Scala because my work requires speed and because the compiler catches those sort of errors in coding that I commonly make (so it is the only language I've used where I am not surprised when large blocks of code run correctly once I get them to compile). But I've had good experiences with Python in other contexts--interfacing with large databases seems like a good use-case.

(I'd rule out Perl as being harder to maintain with no significant benefits over e.g. Python, and I'd rule out Ruby as being not enough more powerful than Python to warrant the less-intuitive syntax and lower rate of adoption/tool availability.)