14. Database Access

//----------------------------------------------------------------------------------
As discussed in 14.1, many database options exist, one of which is JDBC.
Over 200 JDBC drivers are listed at the following URL:
http://developers.sun.com/product/jdbc/drivers/browse_all.jsp
//----------------------------------------------------------------------------------

//----------------------------------------------------------------------------------
// Groovy can make use of various Java persistence libraries and has special
// support built-in (e.g. datasets) for interacting wth RDBMS systems.
// Some of the options include:
//   object serialization (built in to Java)
//   pbeans: pbeans.sf.net
//   prevayler: http://www.prevayler.org
//   Berkeley DB Java edition: http://www.oracle.com/database/berkeley-db/je/
//   JDBC: Over 200 drivers are listed at http://developers.sun.com/product/jdbc/drivers
//   Datasets (special Groovy support)
//   XML via e.g. xstream or JAXB or XmlBeans or ...
//   ORM: over 20 are listed at http://java-source.net/open-source/persistence
//   JNI: can be used directly on a platform that supports e.g. DBM or via
//     a cross platform API such as Apache APR which includes DBM routines:
//     http://apr.apache.org/docs/apr-util/0.9/group__APR__Util__DBM.html
//   jmork: used for Firefox/Thunderbird databases, e.g. address books, history files
// JDBC or Datasets would normally be most common for all examples in this chapter.


// Example shown using berkeley db Java edition - not quite as transparent as
// cookbook example as Berkeley DB Java addition makes transactions visible.
import com.sleepycat.je.*
tx = null
envHome = new File("D:/Projects/GroovyExamples/Pleac/data/db")

myEnvConfig = new EnvironmentConfig()
myEnvConfig.setAllowCreate(true)
myEnv = new Environment(envHome, myEnvConfig)

myDbConfig = new DatabaseConfig()
myDbConfig.setAllowCreate(true)
myDb = myEnv.openDatabase(tx, "vendorDB", myDbConfig)

theKey = new DatabaseEntry("key".getBytes("UTF-8"))
theData = new DatabaseEntry("data".getBytes("UTF-8"))
myDb.put(tx, theKey, theData)
if (myDb.get(tx, theKey, theData, LockMode.DEFAULT) == OperationStatus.SUCCESS) {
    key = new String(theKey.data, "UTF-8")
    foundData = new String(theData.data, "UTF-8")
    println "For key: '$key' found data: '$foundData'."
}
myDb.delete(tx, theKey)
myDb.close()
myEnv.close()


// userstats using pbeans
import net.sourceforge.pbeans.*
// on *nix use: whotext = "who".execute().text
whotext = '''
gnat ttyp1 May 29 15:39 (coprolith.frii.com)
bill ttyp1 May 28 15:38 (hilary.com)
gnit ttyp1 May 27 15:37 (somewhere.org)
'''

class LoginInfo implements Persistent {
    LoginInfo() {}
    LoginInfo(name) { this.name = name; loginCount = 1 }
    String name
    int loginCount
}

def printAllUsers(store) {
    printUsers(store, store.select(LoginInfo.class).collect{it.name}.sort())
}

def printUsers(store, list) {
    list.each{
        println "$it  ${store.selectSingle(LoginInfo.class, 'name', it).loginCount}"
    }
}

def addUsers(store) {
    whotext.trim().split('\n').each{
        m = it =~ /^(\S+)/
        name = m[0][1]
        item = store.selectSingle(LoginInfo.class, 'name', name)
        if (item) {
            item.loginCount++
            store.save(item)
        } else {
            store.insert(new LoginInfo(name))
        }
    }
}

def ds = new org.hsqldb.jdbc.jdbcDataSource()
ds.database = 'jdbc:hsqldb:hsql://localhost/mydb'
ds.user = 'sa'
ds.password = ''
store = new Store(ds)
if (args.size() == 0) {
    addUsers(store)
} else if (args == ['ALL']) {
    printAllUsers(store)
} else {
    printUsers(store, args)
}
//----------------------------------------------------------------------------------

//----------------------------------------------------------------------------------
// Groovy would normally use JDBC here (see 14.1 for details)
import com.sleepycat.je.*
tx = null
envHome = new File("D:/Projects/GroovyExamples/Pleac/data/db")

myEnvConfig = new EnvironmentConfig()
myEnvConfig.setAllowCreate(true)
myEnv = new Environment(envHome, myEnvConfig)

myDbConfig = new DatabaseConfig()
myDbConfig.setAllowCreate(true)
myDb = myEnv.openDatabase(tx, "vendorDB", myDbConfig)

theKey = new DatabaseEntry("key".getBytes("UTF-8"))
theData = new DatabaseEntry("data".getBytes("UTF-8"))
myDb.put(tx, theKey, theData)
myDb.close()
// clear out database
returnCount = true
println myEnv.truncateDatabase(tx, "vendorDB", returnCount) + ' records deleted'
// remove database
myEnv.removeDatabase(tx, "vendorDB")
myEnv.close()
//----------------------------------------------------------------------------------

//----------------------------------------------------------------------------------
// Original cookbook example not likely in Groovy.
// Here is a more realistic example, copying pbeans -> jdbc
// Creation of pbeans database not strictly needed but shown for completion

import net.sourceforge.pbeans.*
import groovy.sql.Sql

def ds = new org.hsqldb.jdbc.jdbcDataSource()
ds.database = 'jdbc:hsqldb:hsql://localhost/mydb'
ds.user = 'sa'
ds.password = ''
store = new Store(ds)

class Person implements Persistent {
    String name
    String does
    String email
}

// populate with test data
store.insert(new Person(name:'Tom Christiansen', does:'book author', email:'tchrist@perl.com'))
store.insert(new Person(name:'Tom Boutell', does:'Poet Programmer', email:'boutell@boutell.com'))

people = store.select(Person.class)

db = new Sql(ds)

db.execute 'CREATE TABLE people ( name VARCHAR, does VARCHAR, email VARCHAR );'
people.each{ p ->
    db.execute "INSERT INTO people ( name, does, email ) VALUES ($p.name,$p.does,$p.email);"
}
db.eachRow("SELECT * FROM people where does like 'book%'"){
    println "$it.name, $it.does, $it.email"
}
db.execute 'DROP TABLE people;'
// => Tom Christiansen, book author, tchrist@perl.com
//----------------------------------------------------------------------------------

//----------------------------------------------------------------------------------
// Groovy would normally use JDBC here (see 14.1 for details)
import com.sleepycat.je.*

def copyEntries(indb, outdb) {
    cursor = indb1.openCursor(null, null)
    while (cursor.getNext(foundKey, foundData, LockMode.DEFAULT) == OperationStatus.SUCCESS)
        outdb.out(tx, foundKey, foundData)
    cursor.close()
}

tx = null
envHome = new File("D:/Projects/GroovyExamples/Pleac/data/db")

myEnvConfig = new EnvironmentConfig()
myEnvConfig.setAllowCreate(true)
myEnv = new Environment(envHome, myEnvConfig)

myDbConfig = new DatabaseConfig()
myDbConfig.setAllowCreate(true)
indb1 = myEnv.openDatabase(tx, "db1", myDbConfig)
indb2 = myEnv.openDatabase(tx, "db2", myDbConfig)
outdb = myEnv.openDatabase(tx, "db3", myDbConfig)
foundKey = new DatabaseEntry()
foundData = new DatabaseEntry()
copyEntries(indb1, outdb)
copyEntries(indb2, outdb)
cursor = indb2.openCursor(null, null)
while (cursor.getNext(foundKey, foundData, LockMode.DEFAULT) == OperationStatus.SUCCESS)
    outdb.out(tx, foundKey, foundData)
cursor.close()
indb1.close()
indb2.close()
outdb.close()
myEnv.close()
//----------------------------------------------------------------------------------

//----------------------------------------------------------------------------------
// If you are using a single file based persistence mechanism you can
// use the file locking mechanisms mentioned in 7.11 otherwise the
// database itself or the ORM layer will provide locking mechanisms.
//----------------------------------------------------------------------------------

//----------------------------------------------------------------------------------
// N/A for most Java/Groovy persistent technologies.
// Use indexes for RDBMS systems.
//----------------------------------------------------------------------------------

//----------------------------------------------------------------------------------
 // We can write a category that allows the ArrayList class
 // to be persisted as required.
 class ArrayListCategory {
     static file = new File('/temp.txt')
     public static void save(ArrayList self) {
         def LS = System.getProperty('line.separator')
         file.withWriter{ w ->
             self.each{ w.write(it + LS)  }
         }
     }
 }

 lines = '''
 zero
 one
 two
 three
 four
 '''.trim().split('\n') as ArrayList

 use(ArrayListCategory) {
     println "ORIGINAL"
     for (i in 0..<lines.size())
         println "${i}: ${lines[i]}"

     a = lines[-1]
     lines[-1] = "last"
     println "The last line was [$a]"

     a = lines[0]
     lines = ["first"] + lines[1..-1]
     println "The first line was [$a]"

     lines.add(3, 'Newbie')
     lines.add(1, 'New One')

     lines.remove(3)

     println "REVERSE"
     (lines.size() - 1).downto(0){ i ->
         println "${i}: ${lines[i]}"
     }
     lines.save()
 }
 // =>
 // ORIGINAL
 // 0: zero
 // 1: one
 // 2: two
 // 3: three
 // 4: four
 // The last line was [four]
 // The first line was [zero]
 // REVERSE
 // 5: last
 // 4: three
 // 3: Newbie
 // 2: one
 // 1: New One
 // 0: first
//----------------------------------------------------------------------------------

//----------------------------------------------------------------------------------
// example using pbeans
import net.sourceforge.pbeans.*
def ds = new org.hsqldb.jdbc.jdbcDataSource()
ds.database = 'jdbc:hsqldb:hsql://localhost/mydb'
ds.user = 'sa'
ds.password = ''
store = new Store(ds)

class Person implements Persistent {
    String name
    String does
    String email
}

name1 = 'Tom Christiansen'
name2 = 'Tom Boutell'

store.insert(new Person(name:name1, does:'book author', email:'tchrist@perl.com'))
store.insert(new Person(name:name2, does:'shareware author', email:'boutell@boutell.com'))

tom1 = store.selectSingle(Person.class, 'name', name1)
tom2 = store.selectSingle(Person.class, 'name', name2)

println "Two Toming: $tom1 $tom2"

if (tom1.name == tom2.name && tom1.does == tom2.does && tom1.email == tom2.email)
    println "You're having runtime fun with one Tom made two."
else
    println "No two Toms are ever alike"

tom2.does = 'Poet Programmer'
store.save(tom2)
// =>
// Two Toming: Person@12884e0 Person@8ab708
// No two Toms are ever alike
//----------------------------------------------------------------------------------

//----------------------------------------------------------------------------------
// Use one of the mechanisms mentioned in 14.1 to load variables at the start
// of the script and save them at the end. You can save the binding, individual
// variables, maps of variables or composite objects.
//----------------------------------------------------------------------------------

//----------------------------------------------------------------------------------
import groovy.sql.Sql

users = ['20':'Joe Bloggs', '40':'Bill Clinton', '60':'Ben Franklin']

def source = new org.hsqldb.jdbc.jdbcDataSource()
source.database = 'jdbc:hsqldb:mem:PLEAC'
source.user = 'sa'
source.password = ''
db = new Sql(source)

db.execute 'CREATE TABLE users ( uid INT, login CHAR(8) );'
users.each{ uid, login ->
    db.execute "INSERT INTO users ( uid, login ) VALUES ($uid,$login);"
}
db.eachRow('SELECT uid, login FROM users WHERE uid < 50'){
    println "$it.uid $it.login"
}
db.execute 'DROP TABLE users;'
// =>
// 20 Joe Bloggs
// 40 Bill Clinton
//----------------------------------------------------------------------------------

//----------------------------------------------------------------------------------
// variation to cookbook: uses Firefox instead of Netscape, always assumes
// argument is a regex, has some others args, retains no args to list all

// uses jmork mork dbm reading library:
//     http://www.smartwerkz.com/projects/jmork/index.html
import mork.*
def cli = new CliBuilder()
cli.h(longOpt: 'help', 'print this message')
cli.e(longOpt: 'exclude', 'exclude hidden history entries (js, css, ads and images)')
cli.c(longOpt: 'clean', 'clean off url query string when reporting urls')
cli.v(longOpt: 'verbose', 'show referrer and first visit date')
def options = cli.parse(args)
if (options.h) { cli.usage(); System.exit(0) }
regex = options.arguments()
if (regex) regex = regex[0]
reader = new FileReader('Pleac/data/history.dat')
morkDocument = new MorkDocument(reader)
tables = morkDocument.tables
tables.each{ table ->
    table.rows.each { row ->
        url = row.getValue('URL')
        if (options.c) url = url.tokenize('?')[0]
        if (!regex || url =~ regex) {
            if (!options.e || row.getValue('Hidden') != '1') {
                println "$url\n    Last Visited: ${date(row,'LastVisitDate')}"
                if (options.v) {
                    println "    First Visited: ${date(row,'FirstVisitDate')}"
                    println "    Referrer: ${row.getValue('Referrer')}"
                }
            }
        }
    }
}
def date(row, key) {
    return new Date((long)(row.getValue(key).toLong()/1000))
}
// $ groovy gfh -ev oracle' =>
// http://www.oracle.com/technology/products/jdev/index.html
//     Last Visited: Thu Feb 15 20:20:36 EST 2007
//     First Visited: Thu Feb 15 20:20:36 EST 2007
//     Referrer: http://docs.codehaus.org/display/GROOVY/Oracle+JDeveloper+Plugin
//----------------------------------------------------------------------------------