/*

 *   SONEWS News Server

 *   see AUTHORS for the list of contributors

 *

 *   This program is free software: you can redistribute it and/or modify

 *   it under the terms of the GNU General Public License as published by

 *   the Free Software Foundation, either version 3 of the License, or

 *   (at your option) any later version.

 *

 *   This program is distributed in the hope that it will be useful,

 *   but WITHOUT ANY WARRANTY; without even the implied warranty of

 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 *   GNU General Public License for more details.

 *

 *   You should have received a copy of the GNU General Public License

 *   along with this program.  If not, see <http://www.gnu.org/licenses/>.

 */

package org.sonews.storage.impl;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.PreparedStatement;

import java.util.ArrayList;

import java.util.Enumeration;

import java.util.List;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

import java.util.regex.PatternSyntaxException;

import javax.mail.Header;

import javax.mail.internet.MimeUtility;

import org.sonews.config.Config;

import org.sonews.util.Log;

import org.sonews.feed.Subscription;

import org.sonews.storage.Article;

import org.sonews.storage.ArticleHead;

import org.sonews.storage.Channel;

import org.sonews.storage.Group;

import org.sonews.storage.Storage;

import org.sonews.storage.StorageBackendException;

import org.sonews.util.Pair;

/**

 * JDBCDatabase facade class.

 * @author Christian Lins

 * @since sonews/0.5.0

 */

// TODO: Refactor this class to reduce size (e.g. ArticleDatabase GroupDatabase)

public class JDBCDatabase implements Storage

{

  public static final int MAX_RESTARTS = 2;

  private Connection        conn = null;

  private PreparedStatement pstmtAddArticle1 = null;

  private PreparedStatement pstmtAddArticle2 = null;

  private PreparedStatement pstmtAddArticle3 = null;

  private PreparedStatement pstmtAddArticle4 = null;

  private PreparedStatement pstmtAddGroup0   = null;

  private PreparedStatement pstmtAddEvent = null;

  private PreparedStatement pstmtCountArticles = null;

  private PreparedStatement pstmtCountGroups   = null;

  private PreparedStatement pstmtDeleteArticle0 = null;

  private PreparedStatement pstmtDeleteArticle1 = null;

  private PreparedStatement pstmtDeleteArticle2 = null;

  private PreparedStatement pstmtDeleteArticle3 = null;

  private PreparedStatement pstmtGetArticle0 = null;

  private PreparedStatement pstmtGetArticle1 = null;

  private PreparedStatement pstmtGetArticleHeaders0 = null;

  private PreparedStatement pstmtGetArticleHeaders1 = null;

  private PreparedStatement pstmtGetArticleHeads = null;

  private PreparedStatement pstmtGetArticleIDs   = null;

  private PreparedStatement pstmtGetArticleIndex    = null;

  private PreparedStatement pstmtGetConfigValue = null;

  private PreparedStatement pstmtGetEventsCount0 = null;

  private PreparedStatement pstmtGetEventsCount1 = null;

  private PreparedStatement pstmtGetGroupForList = null;

  private PreparedStatement pstmtGetGroup0     = null;

  private PreparedStatement pstmtGetGroup1     = null;

  private PreparedStatement pstmtGetFirstArticleNumber = null;

  private PreparedStatement pstmtGetListForGroup       = null;

  private PreparedStatement pstmtGetLastArticleNumber  = null;

  private PreparedStatement pstmtGetMaxArticleID       = null;

  private PreparedStatement pstmtGetMaxArticleIndex    = null;

  private PreparedStatement pstmtGetOldestArticle      = null;

  private PreparedStatement pstmtGetPostingsCount      = null;

  private PreparedStatement pstmtGetSubscriptions  = null;

  private PreparedStatement pstmtIsArticleExisting = null;

  private PreparedStatement pstmtIsGroupExisting = null;

  private PreparedStatement pstmtPurgeGroup0     = null;

  private PreparedStatement pstmtPurgeGroup1     = null;

  private PreparedStatement pstmtSetConfigValue0 = null;

  private PreparedStatement pstmtSetConfigValue1 = null;

  private PreparedStatement pstmtUpdateGroup     = null;

  /** How many times the database connection was reinitialized */

  private int restarts = 0;

  /**

   * Rises the database: reconnect and recreate all prepared statements.

   * @throws java.lang.SQLException

   */

  protected void arise()

    throws SQLException

  {

    try

    {

      // Load database driver

      Class.forName(

        Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DBMSDRIVER, "java.lang.Object"));

      // Establish database connection

      this.conn = DriverManager.getConnection(

        Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DATABASE, "<not specified>"),

        Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_USER, "root"),

        Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_PASSWORD, ""));

      this.conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

      if(this.conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE)

      {

        Log.get().warning("Database is NOT fully serializable!");

      }

      // Prepare statements for method addArticle()

      this.pstmtAddArticle1 = conn.prepareStatement(

        "INSERT INTO articles (article_id, body) VALUES(?, ?)");

      this.pstmtAddArticle2 = conn.prepareStatement(

        "INSERT INTO headers (article_id, header_key, header_value, header_index) " +

        "VALUES (?, ?, ?, ?)");

      this.pstmtAddArticle3 = conn.prepareStatement(

        "INSERT INTO postings (group_id, article_id, article_index)" +

        "VALUES (?, ?, ?)");

      this.pstmtAddArticle4 = conn.prepareStatement(

        "INSERT INTO article_ids (article_id, message_id) VALUES (?, ?)");

      // Prepare statement for method addStatValue()

      this.pstmtAddEvent = conn.prepareStatement(

        "INSERT INTO events VALUES (?, ?, ?)");

      // Prepare statement for method addGroup()

      this.pstmtAddGroup0 = conn.prepareStatement(

        "INSERT INTO groups (name, flags) VALUES (?, ?)");

      // Prepare statement for method countArticles()

      this.pstmtCountArticles = conn.prepareStatement(

        "SELECT Count(article_id) FROM article_ids");

      // Prepare statement for method countGroups()

      this.pstmtCountGroups = conn.prepareStatement(

        "SELECT Count(group_id) FROM groups WHERE " +

        "flags & " + Channel.DELETED + " = 0");

      // Prepare statements for method delete(article)

      this.pstmtDeleteArticle0 = conn.prepareStatement(

        "DELETE FROM articles WHERE article_id = " +

        "(SELECT article_id FROM article_ids WHERE message_id = ?)");

      this.pstmtDeleteArticle1 = conn.prepareStatement(

        "DELETE FROM headers WHERE article_id = " +

        "(SELECT article_id FROM article_ids WHERE message_id = ?)");

      this.pstmtDeleteArticle2 = conn.prepareStatement(

        "DELETE FROM postings WHERE article_id = " +

        "(SELECT article_id FROM article_ids WHERE message_id = ?)");

      this.pstmtDeleteArticle3 = conn.prepareStatement(

        "DELETE FROM article_ids WHERE message_id = ?");

      // Prepare statements for methods getArticle()

      this.pstmtGetArticle0 = conn.prepareStatement(

        "SELECT * FROM articles  WHERE article_id = " +

        "(SELECT article_id FROM article_ids WHERE message_id = ?)");

      this.pstmtGetArticle1 = conn.prepareStatement(

        "SELECT * FROM articles WHERE article_id = " +

        "(SELECT article_id FROM postings WHERE " +

        "article_index = ? AND group_id = ?)");

      // Prepare statement for method getArticleHeaders()

      this.pstmtGetArticleHeaders0 = conn.prepareStatement(

        "SELECT header_key, header_value FROM headers WHERE article_id = ? " +

        "ORDER BY header_index ASC");

      // Prepare statement for method getArticleHeaders(regular expr pattern)

      this.pstmtGetArticleHeaders1 = conn.prepareStatement(

        "SELECT p.article_index, h.header_value FROM headers h " +

          "INNER JOIN postings p ON h.article_id = p.article_id " +

          "INNER JOIN groups g ON p.group_id = g.group_id " +

            "WHERE g.name          =  ? AND " +

                  "h.header_key    =  ? AND " +

                  "p.article_index >= ? " +

        "ORDER BY p.article_index ASC");

      this.pstmtGetArticleIDs = conn.prepareStatement(

        "SELECT article_index FROM postings WHERE group_id = ?");

      // Prepare statement for method getArticleIndex

      this.pstmtGetArticleIndex = conn.prepareStatement(

              "SELECT article_index FROM postings WHERE " +

              "article_id = (SELECT article_id FROM article_ids " +

              "WHERE message_id = ?) " +

              " AND group_id = ?");

      // Prepare statements for method getArticleHeads()

      this.pstmtGetArticleHeads = conn.prepareStatement(

        "SELECT article_id, article_index FROM postings WHERE " +

        "postings.group_id = ? AND article_index >= ? AND " +

        "article_index <= ?");

      // Prepare statements for method getConfigValue()

      this.pstmtGetConfigValue = conn.prepareStatement(

        "SELECT config_value FROM config WHERE config_key = ?");

      // Prepare statements for method getEventsCount()

      this.pstmtGetEventsCount0 = conn.prepareStatement(

        "SELECT Count(*) FROM events WHERE event_key = ? AND " +

        "event_time >= ? AND event_time < ?");

      this.pstmtGetEventsCount1 = conn.prepareStatement(

        "SELECT Count(*) FROM events WHERE event_key = ? AND " +

        "event_time >= ? AND event_time < ? AND group_id = ?");

      // Prepare statement for method getGroupForList()

      this.pstmtGetGroupForList = conn.prepareStatement(

        "SELECT name FROM groups INNER JOIN groups2list " +

        "ON groups.group_id = groups2list.group_id " +

        "WHERE groups2list.listaddress = ?");

      // Prepare statement for method getGroup()

      this.pstmtGetGroup0 = conn.prepareStatement(

        "SELECT group_id, flags FROM groups WHERE Name = ?");

      this.pstmtGetGroup1 = conn.prepareStatement(

        "SELECT name FROM groups WHERE group_id = ?");

      // Prepare statement for method getLastArticleNumber()

      this.pstmtGetLastArticleNumber = conn.prepareStatement(

        "SELECT Max(article_index) FROM postings WHERE group_id = ?");

      // Prepare statement for method getListForGroup()

      this.pstmtGetListForGroup = conn.prepareStatement(

        "SELECT listaddress FROM groups2list INNER JOIN groups " +

        "ON groups.group_id = groups2list.group_id WHERE name = ?");

      // Prepare statement for method getMaxArticleID()

      this.pstmtGetMaxArticleID = conn.prepareStatement(

        "SELECT Max(article_id) FROM articles");

      // Prepare statement for method getMaxArticleIndex()

      this.pstmtGetMaxArticleIndex = conn.prepareStatement(

        "SELECT Max(article_index) FROM postings WHERE group_id = ?");

      // Prepare statement for method getOldestArticle()

      this.pstmtGetOldestArticle = conn.prepareStatement(

        "SELECT message_id FROM article_ids WHERE article_id = " +

        "(SELECT Min(article_id) FROM article_ids)");

      // Prepare statement for method getFirstArticleNumber()

      this.pstmtGetFirstArticleNumber = conn.prepareStatement(

        "SELECT Min(article_index) FROM postings WHERE group_id = ?");

      // Prepare statement for method getPostingsCount()

      this.pstmtGetPostingsCount = conn.prepareStatement(

        "SELECT Count(*) FROM postings NATURAL JOIN groups " +

        "WHERE groups.name = ?");

      // Prepare statement for method getSubscriptions()

      this.pstmtGetSubscriptions = conn.prepareStatement(

        "SELECT host, port, name FROM peers NATURAL JOIN " +

        "peer_subscriptions NATURAL JOIN groups WHERE feedtype = ?");

      // Prepare statement for method isArticleExisting()

      this.pstmtIsArticleExisting = conn.prepareStatement(

        "SELECT Count(article_id) FROM article_ids WHERE message_id = ?");

      // Prepare statement for method isGroupExisting()

      this.pstmtIsGroupExisting = conn.prepareStatement(

        "SELECT * FROM groups WHERE name = ?");

      // Prepare statement for method setConfigValue()

      this.pstmtSetConfigValue0 = conn.prepareStatement(

        "DELETE FROM config WHERE config_key = ?");

      this.pstmtSetConfigValue1 = conn.prepareStatement(

        "INSERT INTO config VALUES(?, ?)");

      // Prepare statements for method purgeGroup()

      this.pstmtPurgeGroup0 = conn.prepareStatement(

        "DELETE FROM peer_subscriptions WHERE group_id = ?");

      this.pstmtPurgeGroup1 = conn.prepareStatement(

        "DELETE FROM groups WHERE group_id = ?");

      // Prepare statement for method update(Group)

      this.pstmtUpdateGroup = conn.prepareStatement(

        "UPDATE groups SET flags = ?, name = ? WHERE group_id = ?");

    }

    catch(ClassNotFoundException ex)

    {

      throw new Error("JDBC Driver not found!", ex);

    }

  }

  /**

   * Adds an article to the database.

   * @param article

   * @return

   * @throws java.sql.SQLException

   */

  @Override

  public void addArticle(final Article article)

    throws StorageBackendException

  {

    try

    {

      this.conn.setAutoCommit(false);

      int newArticleID = getMaxArticleID() + 1;

      // Fill prepared statement with values;

      // writes body to article table

      pstmtAddArticle1.setInt(1, newArticleID);

      pstmtAddArticle1.setBytes(2, article.getBody());

      pstmtAddArticle1.execute();

      // Add headers

      Enumeration headers = article.getAllHeaders();

      for(int n = 0; headers.hasMoreElements(); n++)

      {

        Header header = (Header)headers.nextElement();

        pstmtAddArticle2.setInt(1, newArticleID);

        pstmtAddArticle2.setString(2, header.getName().toLowerCase());

        pstmtAddArticle2.setString(3, 

          header.getValue().replaceAll("[\r\n]", ""));

        pstmtAddArticle2.setInt(4, n);

        pstmtAddArticle2.execute();

      }

      // For each newsgroup add a reference

      List<Group> groups = article.getGroups();

      for(Group group : groups)

      {

        pstmtAddArticle3.setLong(1, group.getInternalID());

        pstmtAddArticle3.setInt(2, newArticleID);

        pstmtAddArticle3.setLong(3, getMaxArticleIndex(group.getInternalID()) + 1);

        pstmtAddArticle3.execute();

      }

      // Write message-id to article_ids table

      this.pstmtAddArticle4.setInt(1, newArticleID);

      this.pstmtAddArticle4.setString(2, article.getMessageID());

      this.pstmtAddArticle4.execute();

      this.conn.commit();

      this.conn.setAutoCommit(true);

      this.restarts = 0; // Reset error count

    }

    catch(SQLException ex)

    {

      try

      {

        this.conn.rollback();  // Rollback changes

      }

      catch(SQLException ex2)

      {

        Log.get().severe("Rollback of addArticle() failed: " + ex2);

      }

      try

      {

        this.conn.setAutoCommit(true); // and release locks

      }

      catch(SQLException ex2)

      {

        Log.get().severe("setAutoCommit(true) of addArticle() failed: " + ex2);

      }

      restartConnection(ex);

      addArticle(article);

    }

  }

  /**

   * Adds a group to the JDBCDatabase. This method is not accessible via NNTP.

   * @param name

   * @throws java.sql.SQLException

   */

  @Override

  public void addGroup(String name, int flags)

    throws StorageBackendException

  {

    try

    {

      this.conn.setAutoCommit(false);

      pstmtAddGroup0.setString(1, name);

      pstmtAddGroup0.setInt(2, flags);

      pstmtAddGroup0.executeUpdate();

      this.conn.commit();

      this.conn.setAutoCommit(true);

      this.restarts = 0; // Reset error count

    }

    catch(SQLException ex)

    {

      try

      {

        this.conn.rollback();

        this.conn.setAutoCommit(true);

      }

      catch(SQLException ex2)

      {

        ex2.printStackTrace();

      }

      restartConnection(ex);

      addGroup(name, flags);

    }

  }

  @Override

  public void addEvent(long time, int type, long gid)

    throws StorageBackendException

  {

    try

    {

      this.conn.setAutoCommit(false);

      this.pstmtAddEvent.setLong(1, time);

      this.pstmtAddEvent.setInt(2, type);

      this.pstmtAddEvent.setLong(3, gid);

      this.pstmtAddEvent.executeUpdate();

      this.conn.commit();

      this.conn.setAutoCommit(true);

      this.restarts = 0;

    }

    catch(SQLException ex)

    {

      try

      {

        this.conn.rollback();

        this.conn.setAutoCommit(true);

      }

      catch(SQLException ex2)

      {

        ex2.printStackTrace();

      }

      restartConnection(ex);

      addEvent(time, type, gid);

    }

  }

  @Override

  public int countArticles()

    throws StorageBackendException

  {

    ResultSet rs = null;

    try

    {

      rs = this.pstmtCountArticles.executeQuery();

      if(rs.next())

      {

        return rs.getInt(1);

      }

      else

      {

        return -1;

      }

    }

    catch(SQLException ex)

    {

      restartConnection(ex);

      return countArticles();

    }

    finally

    {

      if(rs != null)

      {

        try

        {

          rs.close();

        }

        catch(SQLException ex)

        {

          ex.printStackTrace();

        }

        restarts = 0;

      }

    }

  }

  @Override

  public int countGroups()

    throws StorageBackendException

  {

    ResultSet rs = null;

    try

    {

      rs = this.pstmtCountGroups.executeQuery();

      if(rs.next())

      {

        return rs.getInt(1);

      }

      else

      {

        return -1;

      }

    }

    catch(SQLException ex)

    {

      restartConnection(ex);

      return countGroups();

    }

    finally

    {

      if(rs != null)

      {

        try

        {

          rs.close();

        }

        catch(SQLException ex)

        {

          ex.printStackTrace();

        }

        restarts = 0;

      }

    }

  }

  @Override

  public void delete(final String messageID)

    throws StorageBackendException

  {

    try

    {

      this.conn.setAutoCommit(false);

      this.pstmtDeleteArticle0.setString(1, messageID);

      int rs = this.pstmtDeleteArticle0.executeUpdate();

      // We do not trust the ON DELETE CASCADE functionality to delete

      // orphaned references...

      this.pstmtDeleteArticle1.setString(1, messageID);

      rs = this.pstmtDeleteArticle1.executeUpdate();

      this.pstmtDeleteArticle2.setString(1, messageID);

      rs = this.pstmtDeleteArticle2.executeUpdate();

      this.pstmtDeleteArticle3.setString(1, messageID);

      rs = this.pstmtDeleteArticle3.executeUpdate();

      this.conn.commit();

      this.conn.setAutoCommit(true);

    }

    catch(SQLException ex)

    {

      throw new StorageBackendException(ex);

    }

  }

  @Override

  public Article getArticle(String messageID)

    throws StorageBackendException

  {

    ResultSet rs = null;

    try

    {

      pstmtGetArticle0.setString(1, messageID);

      rs = pstmtGetArticle0.executeQuery();

      if(!rs.next())

      {

        return null;

      }

      else

      {

        byte[] body     = rs.getBytes("body");

        String headers  = getArticleHeaders(rs.getInt("article_id"));

        return new Article(headers, body);

      }

    }

    catch(SQLException ex)

    {

      restartConnection(ex);

      return getArticle(messageID);

    }

    finally

    {

      if(rs != null)

      {

        try

        {

          rs.close();

        }

        catch(SQLException ex)

        {

          ex.printStackTrace();

        }

        restarts = 0; // Reset error count

      }

    }

  }

  /**

   * Retrieves an article by its ID.

   * @param articleID

   * @return

   * @throws StorageBackendException

   */

  @Override

  public Article getArticle(long articleIndex, long gid)

    throws StorageBackendException

  {  

    ResultSet rs = null;

    try

    {

      this.pstmtGetArticle1.setLong(1, articleIndex);

      this.pstmtGetArticle1.setLong(2, gid);

      rs = this.pstmtGetArticle1.executeQuery();

      if(rs.next())

      {

        byte[] body    = rs.getBytes("body");

        String headers = getArticleHeaders(rs.getInt("article_id"));

        return new Article(headers, body);

      }

      else

      {

        return null;

      }

    }

    catch(SQLException ex)

    {

      restartConnection(ex);

      return getArticle(articleIndex, gid);

    }

    finally

    {

      if(rs != null)

      {

        try

        {

          rs.close();

        }

        catch(SQLException ex)

        {

          ex.printStackTrace();

        }

        restarts = 0;

      }

    }

  }

  /**

   * Searches for fitting header values using the given regular expression.

   * @param group

   * @param start

   * @param end

   * @param headerKey

   * @param pattern

   * @return

   * @throws StorageBackendException

   */

  @Override

  public List<Pair<Long, String>> getArticleHeaders(Channel group, long start,

    long end, String headerKey, String patStr)

    throws StorageBackendException, PatternSyntaxException

  {

    ResultSet rs = null;

    List<Pair<Long, String>> heads = new ArrayList<Pair<Long, String>>();

    try

    {

      this.pstmtGetArticleHeaders1.setString(1, group.getName());

      this.pstmtGetArticleHeaders1.setString(2, headerKey);

      this.pstmtGetArticleHeaders1.setLong(3, start);

      rs = this.pstmtGetArticleHeaders1.executeQuery();

      // Convert the "NNTP" regex to Java regex

      patStr = patStr.replace("*", ".*");

      Pattern pattern = Pattern.compile(patStr);

      while(rs.next())

      {

        Long articleIndex = rs.getLong(1);

        if(end < 0 || articleIndex <= end) // Match start is done via SQL

        {

          String headerValue  = rs.getString(2);

          Matcher matcher = pattern.matcher(headerValue);

          if(matcher.matches())

          {

            heads.add(new Pair<Long, String>(articleIndex, headerValue));

          }

        }

      }

    }

    catch(SQLException ex)

    {

      restartConnection(ex);

      return getArticleHeaders(group, start, end, headerKey, patStr);

    }

    finally

    {

      if(rs != null)

      {

        try

        {

          rs.close();

        }

        catch(SQLException ex)

        {

          ex.printStackTrace();

        }

      }

    }

    return heads;

  }

  private String getArticleHeaders(long articleID)

    throws StorageBackendException

  {

    ResultSet rs = null;

    try

    {

      this.pstmtGetArticleHeaders0.setLong(1, articleID);

      rs = this.pstmtGetArticleHeaders0.executeQuery();

      StringBuilder buf = new StringBuilder();

      if(rs.next())

      {

        for(;;)

        {

          buf.append(rs.getString(1)); // key

          buf.append(": ");

          String foldedValue = MimeUtility.fold(0, rs.getString(2));

          buf.append(foldedValue); // value

          if(rs.next())

          {

            buf.append("\r\n");

          }

          else

          {

            break;

          }

        }

      }

      return buf.toString();

    }

    catch(SQLException ex)

    {

      restartConnection(ex);

      return getArticleHeaders(articleID);

    }

    finally

    {

      if(rs != null)

      {

        try

        {

          rs.close();

        }

        catch(SQLException ex)

        {

          ex.printStackTrace();

        }

      }

    }

  }

  @Override

  public long getArticleIndex(Article article, Group group)

    throws StorageBackendException

  {

    ResultSet rs = null;

    try

    {

      this.pstmtGetArticleIndex.setString(1, article.getMessageID());

      this.pstmtGetArticleIndex.setLong(2, group.getInternalID());

      rs = this.pstmtGetArticleIndex.executeQuery();

      if(rs.next())

      {

        return rs.getLong(1);

      }

      else

      {

        return -1;

      }

    }

    catch(SQLException ex)

    {

      restartConnection(ex);

      return getArticleIndex(article, group);

    }

    finally

    {

      if(rs != null)

      {

        try

        {

          rs.close();

        }

        catch(SQLException ex)

        {

          ex.printStackTrace();

        }

      }

    }

  }

  /**

   * Returns a list of Long/Article Pairs.

   * @throws java.sql.SQLException

   */

  @Override

  public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, long first,

    long last)

    throws StorageBackendException

  {

    ResultSet rs = null;

    try

    {

      this.pstmtGetArticleHeads.setLong(1, group.getInternalID());

      this.pstmtGetArticleHeads.setLong(2, first);

      this.pstmtGetArticleHeads.setLong(3, last);

      rs = pstmtGetArticleHeads.executeQuery();

      List<Pair<Long, ArticleHead>> articles 

        = new ArrayList<Pair<Long, ArticleHead>>();

      while (rs.next())

      {

        long aid  = rs.getLong("article_id");

        long aidx = rs.getLong("article_index");

        String headers = getArticleHeaders(aid);

        articles.add(new Pair<Long, ArticleHead>(aidx, 

                        new ArticleHead(headers)));

      }

      return articles;

    }

    catch(SQLException ex)

    {

      restartConnection(ex);

      return getArticleHeads(group, first, last);

    }

    finally

    {

      if(rs != null)

      {

        try

        {

          rs.close();

        }

        catch(SQLException ex)

        {

          ex.printStackTrace();

        }

      }

    }

  }

  @Override

  public List<Long> getArticleNumbers(long gid)

    throws StorageBackendException

  {

    ResultSet rs = null;

    try

    {

      List<Long> ids = new ArrayList<Long>();

      this.pstmtGetArticleIDs.setLong(1, gid);

      rs = this.pstmtGetArticleIDs.executeQuery();

      while(rs.next())

      {

        ids.add(rs.getLong(1));

      }

      return ids;

    }

    catch(SQLException ex)

    {

      restartConnection(ex);

      return getArticleNumbers(gid);

    }

    finally

    {

      if(rs != null)

      {

        try

        {

          rs.close();

          restarts = 0; // Clear the restart count after successful request

        }

        catch(SQLException ex)

        {

          ex.printStackTrace();

        }

      }

    }

  }

  @Override

  public String getConfigValue(String key)

    throws StorageBackendException

  {

    ResultSet rs = null;

    try

    {

      this.pstmtGetConfigValue.setString(1, key);

      rs = this.pstmtGetConfigValue.executeQuery();

      if(rs.next())

      {

        return rs.getString(1); // First data on index 1 not 0

      }

      else

      {

        return null;

      }

    }

    catch(SQLException ex)

    {

      restartConnection(ex);

      return getConfigValue(key);

    }

    finally

    {

      if(rs != null)

      {

        try

        {

          rs.close();

        }

        catch(SQLException ex)

        {

          ex.printStackTrace();

        }

        restarts = 0; // Clear the restart count after successful request

      }

    }

  }

  @Override

  public int getEventsCount(int type, long start, long end, Channel channel)

    throws StorageBackendException

  {

    ResultSet rs = null;

    try

    {

      if(channel == null)

      {

        this.pstmtGetEventsCount0.setInt(1, type);

        this.pstmtGetEventsCount0.setLong(2, start);

        this.pstmtGetEventsCount0.setLong(3, end);

        rs = this.pstmtGetEventsCount0.executeQuery();

      }

      else

      {

        this.pstmtGetEventsCount1.setInt(1, type);

        this.pstmtGetEventsCount1.setLong(2, start);

        this.pstmtGetEventsCount1.setLong(3, end);

        this.pstmtGetEventsCount1.setLong(4, channel.getInternalID());

        rs = this.pstmtGetEventsCount1.executeQuery();

      }

      if(rs.next())

      {

        return rs.getInt(1);

      }

      else

      {

        return -1;

      }

    }

    catch(SQLException ex)

    {

      restartConnection(ex);

      return getEventsCount(type, start, end, channel);

    }

    finally

    {

      if(rs != null)

      {

        try

        {

          rs.close();