isqlite.groovy

@Grapes([@Grab('org.xerial:sqlite-jdbc:3.7.2'), @GrabConfig(systemClassLoader=true)])
import groovy.beans.Bindable
import groovy.sql.Sql
import groovy.swing.SwingBuilder
import java.awt.event.*
import javax.swing.*
import javax.swing.table.*
import javax.swing.tree.*
import java.util.logging.*

class Model {
    @Bindable def treeModel
}

model = new Model()
swing = new SwingBuilder()

opendb()

swing.edt { frame(id:'frame', title:'SQLPortable', size:[800,600], defaultCloseOperation:JFrame.EXIT_ON_CLOSE, show:true) {
    sqlFileChooser = fileChooser(dialogTitle:'Open SQL File', fileSelectionMode:JFileChooser.FILES_ONLY)
    popupMenu(id:'popup') {
        menuItem('Select All', actionPerformed: {
            sqlTab.selectedComponent.viewport.view.text = "select * from ${tree.selectionPath.lastPathComponent.userObject}"
            execute()
        })
        menuItem('Count All', actionPerformed: {
            sqlTab.selectedComponent.viewport.view.text = "select count(*) from ${tree.selectionPath.lastPathComponent.userObject}"
            execute()
        })
    }
    menuBar() {
        menu(text:'File') {
            menuItem(text:'Oepn Database', actionPerformed:{
                opendb()
            })
            menuItem(text:'Open SQL File', accelerator:shortcut('O'), actionPerformed:{
                if (sqlFileChooser.showOpenDialog(frame) == JFileChooser.APPROVE_OPTION) {
                    addTab(sqlFileChooser.selectedFile.name, sqlFileChooser.selectedFile.text)
                }
            })
            menuItem(text:'Clear Console', accelerator:shortcut('shift W'), actionPerformed:{
                console.text = ''
            })
        }
        menu(text:'Run') {
            menuItem(text:'Run', actionPerformed:{execute()}, accelerator:shortcut('ENTER'))
        }
        menu(text:'Tab') {
            menuItem(text:'Add Tab', accelerator:shortcut('T'), actionPerformed:{
                addTab()
            })
            menuItem(text:'Previous Tab', accelerator:shortcut(KeyEvent.VK_OPEN_BRACKET, KeyEvent.SHIFT_MASK), actionPerformed:{
                sqlTab.selectedIndex = sqlTab.selectedIndex ? (sqlTab.selectedIndex - 1) % sqlTab.tabCount : sqlTab.tabCount - 1
            })
            menuItem(text:'Next Tab', accelerator:shortcut(KeyEvent.VK_CLOSE_BRACKET, KeyEvent.SHIFT_MASK), actionPerformed:{
                sqlTab.selectedIndex = (sqlTab.selectedIndex + 1) % sqlTab.tabCount
            })
            menuItem(text:'Close Tab', accelerator:shortcut('W'), actionPerformed:{
                if (sqlTab.tabCount > 1) sqlTab.removeTabAt sqlTab.selectedIndex
            })
        }
    }
    splitPane() {
        tree(id:'tree', model:bind(source:model, 'treeModel'), rootVisible:false, mousePressed:{ e ->
            if (e.popupTrigger && tree.selectionCount == 1 && tree.selectionPath.lastPathComponent.children) {
                popup.show(e.source, e.x, e.y)
            }
        })
        splitPane(orientation:JSplitPane.VERTICAL_SPLIT, dividerLocation:300) {
            sqlTab = tabbedPane()
            tab = tabbedPane {
                resultPane = scrollPane(title:'Result') { table = table() }
                consolePane = scrollPane(title:'Console', autoscrolls:true) { console = textArea(tabSize:2) }
            }
        }
    }
}}

def node(value, children=[]) {
    children.inject(new DefaultMutableTreeNode(value)) { root, child ->
        root.add((child instanceof TreeNode) ? child : node(child)); root
    }
}

def opendb() {
    def databases = []
    new File(System.getProperty('user.home'), 'Library/Application Support/iPhone Simulator/').eachFileRecurse {
        if (it.name =~ /^.*\.sqlite$/) { databases << it }
    }

    def pane = swing.optionPane(message:'Select database', selectionValues:databases, optionType:JOptionPane.CLOSED_OPTION)
    pane.createDialog(null, 'Select database').show()

    sql = Sql.newInstance("jdbc:sqlite:${pane.inputValue}", 'org.sqlite.JDBC')

    def rootNode = node('root')
    def metaData = sql.connection.metaData
    def rst = metaData.getTables(null, null, '%', ['TABLE'] as String[])
    while (rst.next()) {
        def childNode = node(rst.getString('TABLE_NAME'))
        rootNode << childNode
        def rsc = metaData.getColumns(null, null, childNode.userObject, '%')
        while (rsc.next()) {
            childNode << node(rsc.getString('COLUMN_NAME'))
        }
    }

    model.treeModel = new DefaultTreeModel(rootNode)
}

def addTab(title = 'New SQL', text = '') {
    sqlTab.addTab(title, swing.widget(swing.scrollPane {textArea(text:text, tabSize:2)}))
    sqlTab.selectedIndex = sqlTab.tabCount - 1
}

def execute() {
    def editor = sqlTab.selectedComponent.viewport.view
    (editor.selectedText?:editor.text).split(';\\s*').each {text ->
        console.append("$text\n\n")
        try {
            if (text =~ /(?i)^\s*(select|with).*/) {
                def rows = sql.rows(text)
                table.setModel(new DefaultTableModel(
                    rows.collect {it.collect {k,v -> v}}.asType(Object[][]),
                    rows[0].collect {k,v -> k}.asType(Object[])
                ))
                tab.selectedComponent = resultPane
            } else {
                sql.execute(text)
                tab.selectedComponent = consolePane
            }
            console.append("Success.\n\n")
        } catch (e) {
            console.append("ERROR: $e.message\n\n")
            tab.selectedComponent = consolePane
        }
    }
}

addTab()