Use Groovy to Find Database Size

Last week I found a SQL statement to find the size of rows in a database. That was helpful but I wanted to be able to run this from the command line. Sounds like a good job for the Groovy database features.

import groovy.sql.Sql

//connect to the database
def sql = Sql.newInstance('jdbc:oracle:thin:user/pass@host:1521:database')

//define the statement to run
def sizeStatement = """select table_name, num_rows, avg_row_len,
ROUND((num_rows * avg_row_len / 1048576),2) "mb_used"
from user_tables
where num_rows > 0"""

//create a date to output (not needed for SQL, just for reporting purposes
def formattedDate = String.format('%tD %
//output the results
sql.eachRow(sizeStatement) { row ->
 println "${formattedDate},${row.table_name},${row.mb_used}"
}

A nice little script. The only catch is that my database is Oracle so I need to pass in the driver jar when I run the script from the command line like this

groovy -classpath OracleThinDrivers.jar CheckSize.groovy

Read more...

Getting the Size of a Database

I recently needed to get an estimated size of an Oracle database. It turns out to be not too hard.

--by table
select table_name, num_rows, avg_row_len,
 ROUND((num_rows * avg_row_len / 1048576),2) "MB Used"
from user_tables
where num_rows > 0

--total
select SUM(ROUND((num_rows * avg_row_len / 1048576),2)) "Total MB Used"
from user_tables

Read more...

Return Statements in Closures

I found an interesting little "quirk" that I was not expecting with Groovy today having to do with return statements in a closure.

Let's say I have some code like this:

String someMethod(){
 def myList = ["1", "a", "test", "z"]
 myList.each{
  println "in each loop for: " + it
  if(it == "test"){
   println "\tfound"
   return it
  }
  println "\tafter if"
 }
}

def value = someMethod()
println value;

The output is:

in each loop for: 1
 after if
in each loop for: a
 after if
in each loop for: test
 found
in each loop for: z
 after if
[1, a, test, z]

I was hoping for this:

in each loop for: 1
 after if
in each loop for: a
 after if
in each loop for: test
 found
test

Where it would return from the method once the value is found. It turns out each{} doesn't work this way. I am sure there are good (complicated) reasons for this, but for now I'm just going to change my method to use a good old for loop.

String someMethod2(){
 def myList = ["1", "a", "test", "z"]
 for(int i = 0; i < myList.size; i++){
  println "in each loop for: " + myList[i]
  if(myList[i] == "test"){
   println "\tfound"
   return myList[i]
  }
  println "\tafter if"
 }
}

Read more...

Groovy Stopwatch

Groovy Stopwatch

Before I started using Groovy, I had created a simple little stopwatch Swing application that basically had one button (Start/Stop) and displayed a time. If you are interested, that is here. I wanted to improve it a bit so I could put a countdown so it would give me a few seconds before it started to time. As a little background, I needed that "feature" so when I was timing this UI I was working on it would give me some time to click "start" on my stopwatch and then switch to the UI I was testing. Not the most advanced tool, but it works fine for my needs.

Instead of updating my Java class, which was already fairly complicated due to the nature of Swing programming, I decided to use Groovy's SwingBuilder.

Here is the final result

Download the Groovy code here.

It was interesting to find that my original Java class for this simple app was 80 lines and my enhanced application in Groovy was only 61, even though I added new features. I probably could have made each of them a little shorter if I really cared about lines of code, but for comparison sake I thought it was interesting how much shorter Groovy was.

For the application itself, here is the code that sets up and displays the Swing UI:

import groovy.swing.SwingBuilder;
import java.awt.BorderLayout as BL
import javax.swing.JFrame

startTime = 0
started = false

swing = new SwingBuilder()
myFrame = swing.frame(title:'Stopwatch', show: true, size:[200,130], defaultCloseOperation: JFrame.EXIT_ON_CLOSE){
 panel (layout: new BL()){
  panel(constraints: BL.NORTH){
   label 'Countdown time'
   countdownField = textField (text:'5', columns:4)
  }
  panel(constraints: BL.CENTER){
   startStopButton = button (text: 'Start/Stop', actionPerformed: {
    if(!started){
     countdownThenStart()
    } else {
     stopTimer()
    }
   })
  }
  panel(constraints: BL.SOUTH){
   messages = label (text:'Click Start')
  }
 }
}

The countdownThenStart() and stopTimer() methods are explained below. One interesting thing I found out was that if I wanted to reference a button (like "startStopButton"), label (like "messages"), or text field (like "countdownField") again, all I had to do was assign it to a variable. If I didn't need it, then I didn't assign it.

I could have made this even simplier if I didn't use the BorderLayout (remove the "constraints: BL.NORTH") or explicitly set the size. I could have just used "pack: true" in place of size. However, since I was learning how to do this, I added them in.

Also, be sure to put the "defaultCloseOperation: JFrame.EXIT_ON_CLOSE" in the frame. I forgot and ended up with dozens of instances of Java running.

To do the countdown, here is the method I used:

def countdownThenStart(){
 int secondsInt = Integer.parseInt(countdownField.text)
 enableFields(false)
 
 def th = Thread.start{
  for(int i = secondsInt; i > 0; i--){   
   messages.text = i
   sleep(1000);
  }
  messages.text = "Go..."
  startTimer()
 }
}

This was a little tricky. I had a problem displaying the countdown. It would just display "Go..." and no digits. It turns out I had to wrap those messages in a separate thread and then it worked like a charm.

Finally, the rest of the helper methods look like this:

def startTimer() {
 started = true
 startTime = new Date().getTime()
 enableFields(true)
}

def stopTimer() {
 stopTime = new Date().getTime()
 started = false
 messages.text = ((stopTime - startTime) / ((double)1000)) + " seconds"  
}

def enableFields(def enable){
 countdownField.enabled = enable
 startStopButton.enabled = enable
}

Nothing surprising or confusing here I hope.

Overall, the SwingBuilder was very easy to use. I've written a number of little Swing apps in the past, and I doubt I will ever need to use anything other than Groovy's SwingBuilder for them again.

Read more...

Word Frequency for YouTube Videos

YouTube has a feature where you can browse the top viewed videos over a specific time-frame (today, this week, this month, or all time). I thought it would be interesting to see which words (if any) pop up more than others. By just glancing at the list I guessed that "justin" and "beiber" would top the list. I thought I'd write some quick Groovy code to see if I was right.

The Stats:

Here is what I found when I looked at the top 160 most viewed videos of all time (as of today):

Top 25 Words:

Word	Count	Freq
official	19	3%
music	12	1%
song	7	1%
cyrus	7	1%
miley	7	1%
version	6	0%
gaga	6	0%
lady	6	0%
bieber	6	0%
justin	6	0%
jason	5	0%
feat	5	0%
dance	5	0%
love	5	0%
baby	5	0%
high	4	0%
david	4	0%
best	4	0%
this	4	0%
sean	3	0%
nuki	3	0%
iglesias	3	0%
enrique	3	0%
goes	3	0%
like	3	0%

Other Stats:
Total words: 619
Total unique words: 424

The Code:

All the source code is located here (box.net)

Here are the guts of the program:

def html = new XmlSlurper(new SAXParser()).parse(urlString)
html.'**'.findAll{ it.@class == 'video-title'}.each {nextVideo ->
 //split the title using regex on non-word characters
 nextVideo.text().split(/\W/).each{nextWord ->
  def lowerCase = nextWord.toLowerCase()
  //limit the results to "interesting" words
  if(lowerCase.length() >= minWordLength && !(lowerCase in ignoreList)){
   wordFreq[lowerCase] = wordFreq[lowerCase] == null ? 1 : wordFreq[lowerCase] + 1
  }
 }
}

Here is what it does:
1. Parse the YouTube URL using XmlSlurper
2. Find all the titles on the page
4. Split the title to get individual words
5. Convert it to lower case to make comparisons easier
7. Limit the words by a minimum length (to get rid of stuff like "of", "and", "the") and ignore other words (like "video")
8. Update the wordFreq map

I am not very comfortable with minimum length and ignoring words, but without that the top 10 words were: video, the, official, ft, music, i, t, you, in, on. That is much less interesting that the filtered list, in my opinion.

Read more...