/**
 * Logback: the reliable, generic, fast and flexible logging framework.
 * Copyright (C) 1999-2013, QOS.ch. All rights reserved.
 *
 * This program and the accompanying materials are dual-licensed under
 * either the terms of the Eclipse Public License v1.0 as published by
 * the Eclipse Foundation
 *
 *   or (per the licensee's choosing)
 *
 * under the terms of the GNU Lesser General Public License version 2.1
 * as published by the Free Software Foundation.
 */
package ch.qos.logback.access.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.hsqldb.Server;

public class DBAppenderHSQLTestFixture {

  public static final String DRIVER_CLASS = "org.hsqldb.jdbcDriver";
  String serverProps;
  String url;
  String user = "sa";
  String password = "";
  Server server;
  boolean isNetwork = true;


  void setUp() throws SQLException {
    if (isNetwork) {
      if (url == null) {
        url = "jdbc:hsqldb:hsql://localhost/test";
      }

      server = new Server();

      server.setDatabaseName(0, "test");
      server.setDatabasePath(0, "mem:test;sql.enforce_strict_size=true");
      server.setLogWriter(null);
      server.setErrWriter(null);
      server.setTrace(false);
      server.setSilent(true);
      server.start();
    } else {
      if (url == null) {
        url = "jdbc:hsqldb:file:test;sql.enforce_strict_size=true";
      }
    }

    try {
      Class.forName(DRIVER_CLASS);
    } catch (Exception e) {
      e.printStackTrace();
      System.out.println(this + ".setUp() error: " + e.getMessage());
    }
    Thread.yield();
    
    createTables();
  }

  void tearDown() throws SQLException {
    dropTables();
    if (isNetwork) {
      server.stop();
      server = null;
    }
  }

  Connection newConnection() throws SQLException {
    return DriverManager.getConnection(url, user, password);
  }

  private void createTables() throws SQLException {
    Connection conn = newConnection();
    StringBuffer buf = new StringBuffer();
    buf.append("CREATE TABLE access_event (");
    buf.append("timestmp BIGINT NOT NULL,");
    buf.append("requestURI VARCHAR(254),");
    buf.append("requestURL VARCHAR(254),");
    buf.append("remoteHost VARCHAR(254),");
    buf.append("remoteUser VARCHAR(254),");
    buf.append("remoteAddr VARCHAR(254),");
    buf.append("protocol VARCHAR(254),");
    buf.append("method VARCHAR(254),");
    buf.append("serverName VARCHAR(254),");
    buf.append("postContent VARCHAR(254),");
    buf.append("event_id INT NOT NULL IDENTITY);");
    query(conn, buf.toString());

    buf = new StringBuffer();
    buf.append("CREATE TABLE access_event_header (");
    buf.append("event_id INT NOT NULL,");
    buf.append("header_key  VARCHAR(254) NOT NULL,");
    buf.append("header_value LONGVARCHAR,");
    buf.append("PRIMARY KEY(event_id, header_key),");
    buf.append("FOREIGN KEY (event_id) REFERENCES access_event(event_id));");
    query(conn, buf.toString());
  }

  private  void dropTables() throws SQLException {
    Connection conn = newConnection();
    
    StringBuffer buf = new StringBuffer();
    buf.append("DROP TABLE access_event_header IF EXISTS;");
    query(conn, buf.toString());
    
    buf = new StringBuffer();
    buf.append("DROP TABLE access_event IF EXISTS;");
    query(conn, buf.toString());
  }

  private  void query(Connection conn, String expression) throws SQLException {
    Statement st = null;
    st = conn.createStatement();

    int i = st.executeUpdate(expression);
    if (i == -1) {
      System.out.println("db error : " + expression);
    }

    st.close();
  }
}
