ЖЖ в БД (скрипт на Groovy)

В продолжении темы маленьких скриптов на groovy — еще один.
Предыдущие: Большие письма в Gmail, Упражнение на сложение (LATEX)

Новый скрипт показывает основы работы с XML и базой данных в Groovy. В качестве задачи выберем сохранение нашей уютной ЖЖшки из XML в базу данных.
Зачем это делать? — SQL нам расскажет всё о нашем (или чужом) ЖЖ — темы, комменты, таги — насколько фантазии хватит собирать статистику

Сначало нам надо скачать ЖЖ в XML.
Это сделает чужая утилита — ljdump
Придется установить Питон, открыть IDLE (Python GUI), загрузить туда утилиту и запустить. Всё спросит она сама.

После её пробега у вас будет директория с файлами LXXX — посты и CXXX — комменты.

А на эти XMLи мы и запустим мой скрипт.
В этом виде он использует pure Java, embedded базу данных Hypersonic (HSQLDB), но можно подключиться к любой, конечно же. Только убедитесь, что JDBC driver у вас в classpath.

Парсинг и работа с БД такого типа годятся только для скриптов и небольших программ. В энтерпрайзе никто не будет загружать весь XML в память (а будут использовать SAX), и никто не будет напрямую слать SQL (а будет Connection Pool, prepared statement, batch, Hibernate какой нибудь).

То, что мы парсим выглядит примерно так:

<?xml version="1.0"?>
<event>
<itemid>1</itemid>
<eventtime>2006-04-09 16:52:00</eventtime>
<url>http://my-ljnick.livejournal.com/460.html</url>
<event_timestamp>1144601520</event_timestamp>
<reply_count>3</reply_count>
<props>
<commentalter>1148051408</commentalter>
</props>
<subject>Открыл себе журнал</subject>
<event>Но писать в него пока не буду</event>
<anum>204</anum>
</event>

Вот скрипт:

import groovy.sql.Sql
import java.sql.Timestamp

def sql = Sql.newInstance("jdbc:hsqldb:file:lj", "sa", "", "org.hsqldb.jdbcDriver")

// delete table if previously created
try { sql.execute("drop table POST")} catch (Exception e) {}
try { sql.execute("drop table COMMENT")} catch (Exception e) {}

// create table
sql.execute('''create table POST (
    id integer not null primary key,
    url varchar(100),
    subject varchar(100),
    text varchar(10000),
    postdate timestamp
)''')

sql.execute('''create table COMMENT (
    id integer not null primary key,
    postid integer,
    user varchar(100),
    subject varchar(100),
    text varchar(10000),
    commentdate timestamp,
    parentcomment integer
)''')

sql.execute(''' alter table COMMENT add constraint c_postid foreign key (postid) references POST (id)
''')

def dir = "D:\\development\\ljdump-1.5.1\\my_lj\\"
List<File> files = new File(dir).listFiles().findAll { it.name.startsWith("L")  }
files.each {
  record ->
  def event = new XmlSlurper().parse(record)
  int id = Integer.parseInt(event.itemid.text())
  Date d = Date.parse("yyy-MM-dd HH:mm:ss", event.eventtime.text())   //No time zone
  java.sql.Timestamp sqlDate = new java.sql.Timestamp(d.time)
  String url = event.url.text()
  String subject = event.subject.text()
  String text = event.event.text()

  sql.execute("insert into post (id,url,subject, text, postdate) values ($id, $url, $subject,$text,$sqlDate )")

  File commentsFile = new File(dir + record.name.replace('L', 'C'));
  if (commentsFile.exists()) {

    def comments = new XmlSlurper().parse(commentsFile).comment

    comments.each() {
      comment ->
      String state = comment.state.text()
      if (!"D".equals(state)) { // not deleted
        int commentid = Integer.parseInt(comment.id.text())
        String user = comment.user.text()
        String parentIdStr = comment.parentid.text()
        int parentid = 0;
        if (!parentIdStr.isEmpty())
          Integer.parseInt(parentIdStr)
        Date commentDate = Date.parse("yyy-MM-dd HH:mm:ss", comment.date.text().replace('T',' '))  
        Timestamp sqlCommentTime = new Timestamp(commentDate.time)
        String commentsubject = comment.subject.text();
        String body = comment.body.text()

        sql.execute("insert into comment (id,user,subject,text,commentdate,parentcomment, postid) values ($commentid,$user, $commentsubject,  $body, $sqlCommentTime, $parentid, $id)")
      }
    }
  }
}
sql.close();

Конечно же его можно улучшить:
1. Сохранять автора поста (Важно, если парсите комьюнити)
2. Сделать чтобы получал директорию с файлами и URL базы данных как аргумент

Можно улучшить схему:
1. Сохранять таги
2. Хранить авторов в отдельной таблице
3. Добавить индексы

Можно добавить директиву Grab, чтобы не надо было добавлять драйверы в classpath (я пробывал, у меня не получилось :( )

Теперь глянем как эту базу можно использовать.
Составим список моих постов у которых больше 50 комментов:

@Grab('hsqldb:hsqldb:1.8.0.7')

import groovy.sql.Sql
Sql sql = Sql.newInstance("jdbc:hsqldb:file:lj", "sa", "", "org.hsqldb.jdbcDriver")
sql.eachRow("select subject, url, (select count (*) from comment where postid = mypost.id) as numcom   from POST as mypost order by numcom desc")
        { row -> if (row.numcom>50) println row}

Вот и всё.
Надеюсь кому нибудь будет полезно.