/*

 Copyright (c) 2005 Health Market Science, Inc.

 This library is free software; you can redistribute it and/or

 modify it under the terms of the GNU Lesser General Public

 License as published by the Free Software Foundation; either

 version 2.1 of the License, or (at your option) any later version.

 This library 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

 Lesser General Public License for more details.

 You should have received a copy of the GNU Lesser General Public

 License along with this library; if not, write to the Free Software

 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307

 USA

 You can contact Health Market Science at info@healthmarketscience.com

 or at the following address:

 Health Market Science

 2700 Horizon Drive

 Suite 200

 King of Prussia, PA 19406

 */

 package com.healthmarketscience.jackcess;

 import java.io.BufferedReader;

 import java.io.Closeable;

 import java.io.File;

 import java.io.FileNotFoundException;

 import java.io.FileReader;

 import java.io.Flushable;

 import java.io.IOException;

 import java.io.RandomAccessFile;

 import java.nio.ByteBuffer;

 import java.nio.channels.Channels;

 import java.nio.channels.FileChannel;

 import java.sql.ResultSet;

 import java.sql.ResultSetMetaData;

 import java.sql.SQLException;

 import java.util.ArrayList;

 import java.util.Arrays;

 import java.util.Collection;

 import java.util.ConcurrentModificationException;

 import java.util.Date;

 import java.util.HashMap;

 import java.util.HashSet;

 import java.util.Iterator;

 import java.util.LinkedList;

 import java.util.List;

 import java.util.Map;

 import java.util.NoSuchElementException;

 import java.util.Set;

 import org.apache.commons.lang.builder.ToStringBuilder;

 import org.apache.commons.logging.Log;

 import org.apache.commons.logging.LogFactory;

 /**

  * An Access database.

  * <p>

  * There is now experimental, optional support for large indexes (disabled by

  * default).  This optional support can be enabled via a few different means:

  * <ul>

  * <li>Setting the system property {@value #USE_BIG_INDEX_PROPERTY} to

  *     {@code "true"} will enable "large" index support accross the jvm</li>

  * <li>Calling {@link #setUseBigIndex} with {@code true} on a Database

  *     instance will enable "large" index support for all tables subsequently

  *     created from that instance</li>

  * <li>Calling {@link #getTable(String,boolean)} can selectively

  *     enable/disable "large" index support on a per-table basis (overriding

  *     any Database or system property setting)</li>

  * </ul>

  *

  * @author Tim McCune

  */

 public class Database

   implements Iterable<Table>, Closeable, Flushable

 {

   private static final Log LOG = LogFactory.getLog(Database.class);

   /** this is the default "userId" used if we cannot find existing info.  this

       seems to be some standard "Admin" userId for access files */

   private static final byte[] SYS_DEFAULT_SID = new byte[2];

   static {

     SYS_DEFAULT_SID[0] = (byte) 0xA6;

     SYS_DEFAULT_SID[1] = (byte) 0x33;

   }

   /** default value for the auto-sync value ({@code true}).  this is slower,

       but leaves more chance of a useable database in the face of failures. */

   public static final boolean DEFAULT_AUTO_SYNC = true;

   /** system property which can be used to make big index support the

       default. */

   public static final String USE_BIG_INDEX_PROPERTY =

     "com.healthmarketscience.jackcess.bigIndex";

   /** Batch commit size for copying other result sets into this database */

   private static final int COPY_TABLE_BATCH_SIZE = 200;

   /** System catalog always lives on page 2 */

   private static final int PAGE_SYSTEM_CATALOG = 2;

   /** Name of the system catalog */

   private static final String TABLE_SYSTEM_CATALOG = "MSysObjects";

   /** this is the access control bit field for created tables.  the value used

       is equivalent to full access (Visual Basic DAO PermissionEnum constant:

       dbSecFullAccess) */

   private static final Integer SYS_FULL_ACCESS_ACM = 1048575;

   /** ACE table column name of the actual access control entry */

   private static final String ACE_COL_ACM = "ACM";

   /** ACE table column name of the inheritable attributes flag */

   private static final String ACE_COL_F_INHERITABLE = "FInheritable";

   /** ACE table column name of the relevant objectId */

   private static final String ACE_COL_OBJECT_ID = "ObjectId";

   /** ACE table column name of the relevant userId */

   private static final String ACE_COL_SID = "SID";

   /** Relationship table column name of the column count */

   private static final String REL_COL_COLUMN_COUNT = "ccolumn";

   /** Relationship table column name of the flags */

   private static final String REL_COL_FLAGS = "grbit";

   /** Relationship table column name of the index of the columns */

   private static final String REL_COL_COLUMN_INDEX = "icolumn";

   /** Relationship table column name of the "to" column name */

   private static final String REL_COL_TO_COLUMN = "szColumn";

   /** Relationship table column name of the "to" table name */

   private static final String REL_COL_TO_TABLE = "szObject";

   /** Relationship table column name of the "from" column name */

   private static final String REL_COL_FROM_COLUMN = "szReferencedColumn";

   /** Relationship table column name of the "from" table name */

   private static final String REL_COL_FROM_TABLE = "szReferencedObject";

   /** Relationship table column name of the relationship */

   private static final String REL_COL_NAME = "szRelationship";

   /** System catalog column name of the page on which system object definitions

       are stored */

   private static final String CAT_COL_ID = "Id";

   /** System catalog column name of the name of a system object */

   private static final String CAT_COL_NAME = "Name";

   private static final String CAT_COL_OWNER = "Owner";

   /** System catalog column name of a system object's parent's id */

   private static final String CAT_COL_PARENT_ID = "ParentId";

   /** System catalog column name of the type of a system object */

   private static final String CAT_COL_TYPE = "Type";

   /** System catalog column name of the date a system object was created */

   private static final String CAT_COL_DATE_CREATE = "DateCreate";

   /** System catalog column name of the date a system object was updated */

   private static final String CAT_COL_DATE_UPDATE = "DateUpdate";

   /** System catalog column name of the flags column */

   private static final String CAT_COL_FLAGS = "Flags";

   /** Empty database template for creating new databases */

   private static final String EMPTY_MDB = "com/healthmarketscience/jackcess/empty.mdb";

   /** Prefix for column or table names that are reserved words */

   private static final String ESCAPE_PREFIX = "x";

   /** Prefix that flags system tables */

   private static final String PREFIX_SYSTEM = "MSys";

   /** Name of the system object that is the parent of all tables */

   private static final String SYSTEM_OBJECT_NAME_TABLES = "Tables";

   /** Name of the table that contains system access control entries */

   private static final String TABLE_SYSTEM_ACES = "MSysACEs";

   /** Name of the table that contains table relationships */

   private static final String TABLE_SYSTEM_RELATIONSHIPS = "MSysRelationships";

   /** System object type for table definitions */

   private static final Short TYPE_TABLE = (short) 1;

   /** the columns to read when reading system catalog initially */

   private static Collection<String> SYSTEM_CATALOG_COLUMNS =

     new HashSet<String>(Arrays.asList(CAT_COL_NAME, CAT_COL_TYPE, CAT_COL_ID));

   /**

    * All of the reserved words in Access that should be escaped when creating

    * table or column names

    */

   private static final Set<String> RESERVED_WORDS = new HashSet<String>();

   static {

     //Yup, there's a lot.

     RESERVED_WORDS.addAll(Arrays.asList(

        "add", "all", "alphanumeric", "alter", "and", "any", "application", "as",

        "asc", "assistant", "autoincrement", "avg", "between", "binary", "bit",

        "boolean", "by", "byte", "char", "character", "column", "compactdatabase",

        "constraint", "container", "count", "counter", "create", "createdatabase",

        "createfield", "creategroup", "createindex", "createobject", "createproperty",

        "createrelation", "createtabledef", "createuser", "createworkspace",

        "currency", "currentuser", "database", "date", "datetime", "delete",

        "desc", "description", "disallow", "distinct", "distinctrow", "document",

        "double", "drop", "echo", "else", "end", "eqv", "error", "exists", "exit",

        "false", "field", "fields", "fillcache", "float", "float4", "float8",

        "foreign", "form", "forms", "from", "full", "function", "general",

        "getobject", "getoption", "gotopage", "group", "group by", "guid", "having",

        "idle", "ieeedouble", "ieeesingle", "if", "ignore", "imp", "in", "index",

        "indexes", "inner", "insert", "inserttext", "int", "integer", "integer1",

        "integer2", "integer4", "into", "is", "join", "key", "lastmodified", "left",

        "level", "like", "logical", "logical1", "long", "longbinary", "longtext",

        "macro", "match", "max", "min", "mod", "memo", "module", "money", "move",

        "name", "newpassword", "no", "not", "null", "number", "numeric", "object",

        "oleobject", "off", "on", "openrecordset", "option", "or", "order", "outer",

        "owneraccess", "parameter", "parameters", "partial", "percent", "pivot",

        "primary", "procedure", "property", "queries", "query", "quit", "real",

        "recalc", "recordset", "references", "refresh", "refreshlink",

        "registerdatabase", "relation", "repaint", "repairdatabase", "report",

        "reports", "requery", "right", "screen", "section", "select", "set",

        "setfocus", "setoption", "short", "single", "smallint", "some", "sql",

        "stdev", "stdevp", "string", "sum", "table", "tabledef", "tabledefs",

        "tableid", "text", "time", "timestamp", "top", "transform", "true", "type",

        "union", "unique", "update", "user", "value", "values", "var", "varp",

        "varbinary", "varchar", "where", "with", "workspace", "xor", "year", "yes",

        "yesno"

     ));

   }

   /** Buffer to hold database pages */

   private ByteBuffer _buffer;

   /** ID of the Tables system object */

   private Integer _tableParentId;

   /** Format that the containing database is in */

   private final JetFormat _format;

   /**

    * Map of UPPERCASE table names to page numbers containing their definition

    * and their stored table name.

    */

   private Map<String, TableInfo> _tableLookup =

     new HashMap<String, TableInfo>();

   /** set of table names as stored in the mdb file, created on demand */

   private Set<String> _tableNames;

   /** Reads and writes database pages */

   private final PageChannel _pageChannel;

   /** System catalog table */

   private Table _systemCatalog;

   /** System access control entries table */

   private Table _accessControlEntries;

   /** page number of the system relationships table */

   private Integer _relationshipsPageNumber;

   /** System relationships table (initialized on first use) */

   private Table _relationships;

   /** SIDs to use for the ACEs added for new tables */

   private final List<byte[]> _newTableSIDs = new ArrayList<byte[]>();

   /** for now, "big index support" is optional */

   private boolean _useBigIndex;

   /**

    * Open an existing Database.  If the existing file is not writeable, the

    * file will be opened read-only.  Auto-syncing is enabled for the returned

    * Database.

    * @param mdbFile File containing the database

    */

   public static Database open(File mdbFile) throws IOException {

     return open(mdbFile, false);

   }

   /**

    * Open an existing Database.  If the existing file is not writeable or the

    * readOnly flag is <code>true</code>, the file will be opened read-only.

    * Auto-syncing is enabled for the returned Database.

    * @param mdbFile File containing the database

    * @param readOnly iff <code>true</code>, force opening file in read-only

    *                 mode

    */

   public static Database open(File mdbFile, boolean readOnly)

     throws IOException

   {

     return open(mdbFile, readOnly, DEFAULT_AUTO_SYNC);

   }

   /**

    * Open an existing Database.  If the existing file is not writeable or the

    * readOnly flag is <code>true</code>, the file will be opened read-only.

    * @param mdbFile File containing the database

    * @param readOnly iff <code>true</code>, force opening file in read-only

    *                 mode

    * @param autoSync whether or not to enable auto-syncing on write.  if

    *                 {@code true}, writes will be immediately flushed to disk.

    *                 This leaves the database in a (fairly) consistent state

    *                 on each write, but can be very inefficient for many

    *                 updates.  if {@code false}, flushing to disk happens at

    *                 the jvm's leisure, which can be much faster, but may

    *                 leave the database in an inconsistent state if failures

    *                 are encountered during writing.

    */

   public static Database open(File mdbFile, boolean readOnly, boolean autoSync)

     throws IOException

   {    

     if(!mdbFile.exists() || !mdbFile.canRead()) {

       throw new FileNotFoundException("given file does not exist: " + mdbFile);

     }

     return new Database(openChannel(mdbFile,

                                     (!mdbFile.canWrite() || readOnly)),

                         autoSync);

   }

   /**

    * Create a new Database

    * @param mdbFile Location to write the new database to.  <b>If this file

    *    already exists, it will be overwritten.</b>

    */

   public static Database create(File mdbFile) throws IOException {

     return create(mdbFile, DEFAULT_AUTO_SYNC);

   }

   /**

    * Create a new Database

    * @param mdbFile Location to write the new database to.  <b>If this file

    *    already exists, it will be overwritten.</b>

    * @param autoSync whether or not to enable auto-syncing on write.  if

    *                 {@code true}, writes will be immediately flushed to disk.

    *                 This leaves the database in a (fairly) consistent state

    *                 on each write, but can be very inefficient for many

    *                 updates.  if {@code false}, flushing to disk happens at

    *                 the jvm's leisure, which can be much faster, but may

    *                 leave the database in an inconsistent state if failures

    *                 are encountered during writing.

    */

   public static Database create(File mdbFile, boolean autoSync)

     throws IOException

   {    

     FileChannel channel = openChannel(mdbFile, false);

     channel.truncate(0);

     channel.transferFrom(Channels.newChannel(

         Thread.currentThread().getContextClassLoader().getResourceAsStream(

             EMPTY_MDB)), 0, Integer.MAX_VALUE);

     return new Database(channel, autoSync);

   }

   private static FileChannel openChannel(File mdbFile, boolean readOnly)

     throws FileNotFoundException

   {

     String mode = (readOnly ? "r" : "rw");

     return new RandomAccessFile(mdbFile, mode).getChannel();

   }

   /**

    * Create a new database by reading it in from a FileChannel.

    * @param channel File channel of the database.  This needs to be a

    *    FileChannel instead of a ReadableByteChannel because we need to

    *    randomly jump around to various points in the file.

    */

   protected Database(FileChannel channel, boolean autoSync) throws IOException

   {

     _format = JetFormat.getFormat(channel);

     _pageChannel = new PageChannel(channel, _format, autoSync);

     // note, it's slighly sketchy to pass ourselves along partially

     // constructed, but only our _format and _pageChannel refs should be

     // needed

     _pageChannel.initialize(this);

     _buffer = _pageChannel.createPageBuffer();

     readSystemCatalog();

   }

   public PageChannel getPageChannel() {

     return _pageChannel;

   }

   public JetFormat getFormat() {

     return _format;

   }

   /**

    * @return The system catalog table

    */

   public Table getSystemCatalog() {

     return _systemCatalog;

   }

   /**

    * @return The system Access Control Entries table

    */

   public Table getAccessControlEntries() {

     return _accessControlEntries;

   }

   /**

    * Whether or not big index support is enabled for tables.

    */

   public boolean doUseBigIndex() {

     return _useBigIndex;

   }

   /**

    * Set whether or not big index support is enabled for tables.

    */

   public void setUseBigIndex(boolean useBigIndex) {

     _useBigIndex = useBigIndex;

   }

   /**

    * Read the system catalog

    */

   private void readSystemCatalog() throws IOException {

     _systemCatalog = readTable(TABLE_SYSTEM_CATALOG, PAGE_SYSTEM_CATALOG,

                                defaultUseBigIndex());

     for(Map<String,Object> row :

           Cursor.createCursor(_systemCatalog).iterable(

               SYSTEM_CATALOG_COLUMNS))

     {

       String name = (String) row.get(CAT_COL_NAME);

       if (name != null && TYPE_TABLE.equals(row.get(CAT_COL_TYPE))) {

         if (!name.startsWith(PREFIX_SYSTEM)) {

           addTable((String) row.get(CAT_COL_NAME), (Integer) row.get(CAT_COL_ID));

         } else if(TABLE_SYSTEM_ACES.equals(name)) {

           int pageNumber = (Integer)row.get(CAT_COL_ID);

           _accessControlEntries = readTable(TABLE_SYSTEM_ACES, pageNumber,

                                             defaultUseBigIndex());

         } else if(TABLE_SYSTEM_RELATIONSHIPS.equals(name)) {

           _relationshipsPageNumber = (Integer)row.get(CAT_COL_ID);

         }

       } else if (SYSTEM_OBJECT_NAME_TABLES.equals(name)) {

         _tableParentId = (Integer) row.get(CAT_COL_ID);

       }

     }

     // check for required system values

     if(_accessControlEntries == null) {

       throw new IOException("Did not find required " + TABLE_SYSTEM_ACES +

                             " table");

     }

     if(_tableParentId == null) {

       throw new IOException("Did not find required parent table id");

     }

     if (LOG.isDebugEnabled()) {

       LOG.debug("Finished reading system catalog.  Tables: " +

                 getTableNames());

     }

   }

   /**

    * @return The names of all of the user tables (String)

    */

   public Set<String> getTableNames() {

     if(_tableNames == null) {

       _tableNames = new HashSet<String>();

       for(TableInfo tableInfo : _tableLookup.values()) {

         _tableNames.add(tableInfo.tableName);

       }

     }

     return _tableNames;

   }

   /**

    * @return an unmodifiable Iterator of the user Tables in this Database.

    * @throws IllegalStateException if an IOException is thrown by one of the

    *         operations, the actual exception will be contained within

    * @throws ConcurrentModificationException if a table is added to the

    *         database while an Iterator is in use.

    */

   public Iterator<Table> iterator() {

     return new TableIterator();

   }

   /**

    * @param name Table name

    * @return The table, or null if it doesn't exist

    */

   public Table getTable(String name) throws IOException {

     return getTable(name, defaultUseBigIndex());

   }

   /**

    * @param name Table name

    * @param useBigIndex whether or not "big index support" should be enabled

    *                    for the table (this value will override any other

    *                    settings)

    * @return The table, or null if it doesn't exist

    */

   public Table getTable(String name, boolean useBigIndex) throws IOException {

     TableInfo tableInfo = lookupTable(name);

     if ((tableInfo == null) || (tableInfo.pageNumber == null)) {

       return null;

     }

     return readTable(tableInfo.tableName, tableInfo.pageNumber, useBigIndex);

   }

   /**

    * Create a new table in this database

    * @param name Name of the table to create

    * @param columns List of Columns in the table

    */

   public void createTable(String name, List<Column> columns)

     throws IOException

   {

     validateIdentifierName(name, _format.MAX_TABLE_NAME_LENGTH, "table");

     if(getTable(name) != null) {

       throw new IllegalArgumentException(

           "Cannot create table with name of existing table");

     }

     if(columns.isEmpty()) {

       throw new IllegalArgumentException(

           "Cannot create table with no columns");

     }

     if(columns.size() > _format.MAX_COLUMNS_PER_TABLE) {

       throw new IllegalArgumentException(

           "Cannot create table with more than " +

           _format.MAX_COLUMNS_PER_TABLE + " columns");

     }

     Set<String> colNames = new HashSet<String>();

     // next, validate the column definitions

     for(Column column : columns) {

       column.validate(_format);

       if(!colNames.add(column.getName().toUpperCase())) {

         throw new IllegalArgumentException("duplicate column name: " +

                                            column.getName());

       }

     }

     if(Table.countAutoNumberColumns(columns) > 1) {

       throw new IllegalArgumentException(

           "Can have at most one AutoNumber column per table");

     }

     //Write the tdef page to disk.

     int tdefPageNumber = Table.writeTableDefinition(columns, _pageChannel,

                                                     _format);

     //Add this table to our internal list.

     addTable(name, Integer.valueOf(tdefPageNumber));

     //Add this table to system tables

     addToSystemCatalog(name, tdefPageNumber);

     addToAccessControlEntries(tdefPageNumber);    

   }

   /**

    * Finds all the relationships in the database between the given tables.

    */

   public List<Relationship> getRelationships(Table table1, Table table2)

     throws IOException

   {

     // the relationships table does not get loaded until first accessed

     if(_relationships == null) {

       if(_relationshipsPageNumber == null) {

         throw new IOException("Could not find system relationships table");

       }

       _relationships = readTable(TABLE_SYSTEM_RELATIONSHIPS,

                                  _relationshipsPageNumber,

                                  defaultUseBigIndex());

     }

     int nameCmp = table1.getName().compareTo(table2.getName());

     if(nameCmp == 0) {

       throw new IllegalArgumentException("Must provide two different tables");

     }

     if(nameCmp > 0) {

       // we "order" the two tables given so that we will return a collection

       // of relationships in the same order regardless of whether we are given

       // (TableFoo, TableBar) or (TableBar, TableFoo).

       Table tmp = table1;

       table1 = table2;

       table2 = tmp;

     }

     List<Relationship> relationships = new ArrayList<Relationship>();

     Cursor cursor = createCursorWithOptionalIndex(

         _relationships, REL_COL_FROM_TABLE, table1.getName());

     collectRelationships(cursor, table1, table2, relationships);

     cursor = createCursorWithOptionalIndex(

         _relationships, REL_COL_TO_TABLE, table1.getName());

     collectRelationships(cursor, table2, table1, relationships);

     return relationships;

   }

   /**

    * Finds the relationships matching the given from and to tables from the