/**
* This file is part of HIDB2.
*
* HIDB2 is free software: you can redistribute it and/or modify
* it under the terms of the GNU Lesser Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* HIDB2 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 Public License for more details.
*
* You should have received a copy of the GNU Lesser Public License
* along with HIDB2. If not, see <http://www.gnu.org/licenses/>.
*/
package hidb2.kern;
import hidb2.kern.db.MigrationScript;
import java.io.File;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Logger;
import org.h2.fulltext.FullTextLucene;
/**
* H2 DB implementation of the DataStore
*
*/
public class H2DataStore implements DataStore
{
final static Logger log = Logger.getLogger("hidb2.kern");
/** Database scheme version. SHALL BE MODIFIED when database scheme is changed! */
public final static String SCHEME_LEVEL = "0.0.2";
private String _dbVersion = "";
/**
* Migration scripts table.
* Source Version, Destination Version, Class Name
* Migration classes shall implements <i>MigrationScript</i> interface
*/
public final static String MIGRATION_SCRIPTS[][] =
{
{
"0.0.1", "0.0.2", "hidb2.kern.db.Migrate1"
}
};
/** Tabespace name (for query purpose */
public final static String TABLESPACE_PUB = "PUBLIC";
public final static String SQL_CREATE_DESCR_LAYOUT_1 = "CREATE TABLE T_DESCRLAYOUT(DESCR_ID INT NOT NULL PRIMARY KEY, ISVERT BOOLEAN, COL_CNT INT(2), IMG_IDX INT(4))";
public final static String SQL_CREATE_DESCR_LAYOUT_2 = "ALTER TABLE T_DESCRLAYOUT ADD FOREIGN KEY(DESCR_ID) REFERENCES T_DESCR(ID)";
public final static String SQL_CREATE_DESCR_LAYOUT_3 = "CREATE TABLE T_ATTRLAYOUT(DESCR_ID INT NOT NULL, COL_CNT INT NOT NULL, WIDTH INT(4), HEIGHT INT(4))";
public final static String SQL_CREATE_DESCR_LAYOUT_4 = "ALTER TABLE T_ATTRLAYOUT ADD FOREIGN KEY(DESCR_ID) REFERENCES T_DESCR(ID)";
public final static String SQL_CREATE_DESCR_LAYOUT_5 = "ALTER TABLE T_ATTRLAYOUT ADD PRIMARY KEY(DESCR_ID, COL_CNT)";
private int _openStatus = C_UNKNOWN;
static
{
try
{
Class.forName("org.h2.Driver");
}
catch (ClassNotFoundException e)
{
log.warning("H2 driver not found");
}
}
// List of real types
protected static List<AttrType> _attrTypeList = null;
protected List<DataPath> _dataPathLst;
protected List<FolderDescription> _fileDescrLst;
protected List<ListDescription> _listDescrLst;
/**
* Path to the directory that holds the DB
* Ex : /home/toto/MyDBDir/
*/
private String _dbPath;
private Connection _cnx;
// Pre-compiled statments
private PreparedStatement Stat_NextSEQ_FOLDER = null;
private PreparedStatement Stat_NextSEQ_LIST = null;
private PreparedStatement Stat_NextSEQ_CARD = null;
private PreparedStatement Stat_NextSEQ_DESCR = null;
private PreparedStatement Stat_NextSEQ_DATAPATH = null;
private PreparedStatement Stat_DelATTR_DEST = null;
private StatKit[] StatGenTypes;
private void initStatements()
{
try
{
Stat_NextSEQ_CARD = getCnx().prepareStatement("SELECT nextval('SEQ_CARDID')");
Stat_NextSEQ_FOLDER = getCnx().prepareStatement("SELECT nextval('SEQ_FOLDERID')");
Stat_NextSEQ_LIST = getCnx().prepareStatement("SELECT nextval('SEQ_LISTID')");
Stat_NextSEQ_DESCR = getCnx().prepareStatement("SELECT nextval('SEQ_DESCRID')");
Stat_NextSEQ_DATAPATH = getCnx().prepareStatement("SELECT nextval('SEQ_DATAPATHID')");
Stat_DelATTR_DEST = getCnx().prepareStatement("DELETE FROM T_ATTR WHERE DESCR_ID=?");
// Prepare Statements for extended types
// Delete datatable for extend attribut types
StatGenTypes = new StatKit[AttrType.values().length];
for (AttrType at : AttrType.values())
{
if (at.extended)
{
try
{
Method m = at.dataClass.getDeclaredMethod("getStatements", DataStore.class);
StatKit statTab = (StatKit) m.invoke(null, this);
StatGenTypes[at.ordinal()] = statTab;
}
catch (SecurityException e)
{
// Should not occur
e.printStackTrace();
}
catch (NoSuchMethodException e)
{
// Should not occur
e.printStackTrace();
}
catch (IllegalArgumentException e)
{
e.printStackTrace();
}
catch (IllegalAccessException e)
{
e.printStackTrace();
}
catch (InvocationTargetException e)
{
e.printStackTrace();
}
}
}
}
catch (SQLException sqlex)
{
log.warning("Failure :" + sqlex.getMessage());
}
}
public final static long getNext(PreparedStatement sNext)
{
long id = -1;
try
{
ResultSet rs = sNext.executeQuery();
if (rs.next())
{
id = rs.getLong(1);
}
rs.close();
}
catch (SQLException sqlex)
{
log.warning("Failure :" + sqlex.getMessage());
}
return id;
}
public long getNextDescrID()
{
return getNext(Stat_NextSEQ_DESCR);
}
public long getNextDataPathID()
{
return getNext(Stat_NextSEQ_DATAPATH);
}
public long getNextFolderID()
{
return getNext(Stat_NextSEQ_FOLDER);
}
public long getNextListID()
{
return getNext(Stat_NextSEQ_LIST);
}
public long getNextCardID()
{
return getNext(Stat_NextSEQ_CARD);
}
public void deleteDescrAttr(long descrID) throws SQLException
{
Stat_DelATTR_DEST.clearParameters();
Stat_DelATTR_DEST.setLong(1, descrID);
/* nbrows = */Stat_DelATTR_DEST.executeUpdate();
}
@Override
public PreparedStatement getDelete(AttrType t)
{
return StatGenTypes[t.ordinal()].delete;
}
@Override
public PreparedStatement getFullSelect(AttrType t)
{
return StatGenTypes[t.ordinal()].fullSelect;
}
@Override
public PreparedStatement getInsertSelect(AttrType t)
{
return StatGenTypes[t.ordinal()].insert;
}
@Override
public PreparedStatement getUpdate(AttrType t)
{
return StatGenTypes[t.ordinal()].update;
}
public int getOpenStatus()
{
return _openStatus;
}
@Override
public String getDBVersion()
{
return _dbVersion;
}
@Override
public String getExecVersion()
{
return SCHEME_LEVEL;
}
@Override
public int migrate()
{
int res = C_OK;
String clzzName = null;
// Find the migration class
// TODO : Manage script chains
for (int i = 0; i < MIGRATION_SCRIPTS.length; i++)
{
if (MIGRATION_SCRIPTS[i][0].compareTo(_dbVersion) == 0)
{
clzzName = MIGRATION_SCRIPTS[i][2];
break;
}
}
// Run it
if (clzzName != null)
{
try
{
Class<?> clzz = Class.forName(clzzName);
MigrationScript ms = (MigrationScript) clzz.newInstance();
res = ms.migrate(this);
if (res == C_OK)
{
// Update database version
Statement s = _cnx.createStatement();
Date today = new Date(System.currentTimeMillis());
log.info("INSERT INTO T_GENINFO VALUES ( '" + SCHEME_LEVEL + "' , '" + today.toString() + "', '"
+ ms.getInfo() + "')");
/* boolean b = */s.execute("INSERT INTO T_GENINFO VALUES ( '" + SCHEME_LEVEL + "' , '" + today.toString()
+ "', '" + ms.getInfo() + "')");
commit();
s.close();
}
}
catch (ClassNotFoundException e)
{
log.warning("Migration Script Class " + clzzName + " not found");
e.printStackTrace();
res = C_ERROR;
}
catch (InstantiationException e)
{
e.printStackTrace();
res = C_ERROR;
}
catch (IllegalAccessException e)
{
e.printStackTrace();
res = C_ERROR;
}
catch (SQLException e)
{
e.printStackTrace();
res = C_FAIL;
}
}
return res;
}
/**
* Initialize the access to the database
*/
public H2DataStore(String pathName)
{
_openStatus = open(pathName);
try
{
// Test for database consistency : VERSION VARCHAR(10), TLASTIDX TIMESTAMP, CMT VARCHAR(1024)
Statement s = _cnx.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM T_GENINFO WHERE TLASTIDX=(SELECT MAX(TLASTIDX) FROM T_GENINFO)");
rs.next();
_dbVersion = rs.getString(1);
Timestamp ts = rs.getTimestamp(2);
log.info("DB version is " + _dbVersion + " since " + ts);
rs.close();
s.close();
if (_dbVersion.compareTo(SCHEME_LEVEL) != 0)
{
log.warning("Migration to version " + SCHEME_LEVEL + " nedeed");
_openStatus = C_INCONSISTENCY;
// throw new IllegalArgumentException("Migration to version " + SCHEME_LEVEL + " nedeed");
}
}
catch (SQLException seqx)
{
createScheme();
}
if (_openStatus == C_OK)
{
initStatements();
// Read AttrbutType
AttrType.values();
// Read DataPath
getDataPathList();
// Read ListDescription
getListDescriptionList();
}
}
@Override
public String getTableSpace()
{
return TABLESPACE_PUB;
}
private void dropDataTables()
{
try
{
Statement s = _cnx.createStatement();
Statement s2 = _cnx.createStatement();
ResultSet rs = s2.executeQuery("SELECT ID FROM T_DESCR");
while (rs.next())
{
long id = rs.getLong(1);
s.execute("DROP TABLE IF EXISTS " + "T_" + id);
}
s2.close();
}
catch (SQLException sqex)
{
// Nothing to do
}
}
private void dropScheme() throws SQLException
{
// Clean all
Statement s = _cnx.createStatement();
// Deletion order depends on created tables
// Delete datatable for extend attribut types
for (AttrType at : AttrType.values())
{
if (at.extended)
{
try
{
Method m = at.dataClass.getDeclaredMethod("getTableDelete", (Class[]) null);
String[] dropTableScript = (String[]) m.invoke(null, (Object[]) null);
for (String cmd : dropTableScript)
{
s.execute(cmd);
}
}
catch (SecurityException e)
{
// Should not occur
e.printStackTrace();
}
catch (NoSuchMethodException e)
{
// Should not occur
e.printStackTrace();
}
catch (IllegalArgumentException e)
{
e.printStackTrace();
}
catch (IllegalAccessException e)
{
e.printStackTrace();
}
catch (InvocationTargetException e)
{
e.printStackTrace();
}
}
}
// Drop all indexes
try
{
FullTextLucene.dropAll(getCnx());
}
catch (SQLException sqlex)
{
// Usually just means that the db was empty.
log.warning("Error at Lucene level:" + sqlex.getMessage());
// sqlex.printStackTrace();
}
// Drop data tables
dropDataTables();
s.execute("DROP TABLE IF EXISTS T_ATTR, T_DESCR, T_DATAPATH, T_ATTRTYPE, T_GENINFO");
}
/**
* Drop all and create a new scheme
*/
public void createScheme()
{
try
{
// Deletion order depends on created tables
try
{
dropScheme();
}
catch (SQLException sqlex)
{
log.warning("Error at drop Scheme level:" + sqlex.getMessage());
}
// Clean all
Statement s = _cnx.createStatement();
s.execute("CREATE SEQUENCE IF NOT EXISTS SEQ_FOLDERID");
s.execute("CREATE SEQUENCE IF NOT EXISTS SEQ_LISTID");
s.execute("CREATE SEQUENCE IF NOT EXISTS SEQ_DESCRID");
s.execute("CREATE SEQUENCE IF NOT EXISTS SEQ_DATAPATHID");
s.execute("CREATE SEQUENCE IF NOT EXISTS SEQ_CARDID");
// Prepare Lucene indexation feature
s.execute("CREATE ALIAS IF NOT EXISTS FTL_INIT FOR \"org.h2.fulltext.FullTextLucene.init\"");
s.execute("CALL FTL_INIT()");
s.execute("CREATE TABLE T_GENINFO(VERSION VARCHAR(10), TLASTIDX TIMESTAMP, CMT VARCHAR(1024))");
s.execute("CREATE TABLE T_DATAPATH(ID INT NOT NULL PRIMARY KEY, "
+ "NAME VARCHAR(1024), CMT VARCHAR(1024), DIRNAME VARCHAR(1024))");
// DTYPE : 1 Folder, 2 Card, 3 List
s.execute("CREATE TABLE T_DESCR(ID INT NOT NULL PRIMARY KEY, "
+ "NAME VARCHAR(1024), CMT VARCHAR(1024), DTYPE INT(1), TNAME VARCHAR(32), ICON_IDX INT(4),"
+ "LABEL_IDX INT(4), CARD_ID INT)");
// GUI_CNT : Display Order in GUI
// COL_CNT : Used to compute the Sql Name and the order in the memory list for data retrieval
// DEFEXT : contains a set of int, double, string dedicated to the attribut definition
s.execute("CREATE TABLE T_ATTR(DESCR_ID INT NOT NULL, COL_CNT INT NOT NULL, "
+ "NAME VARCHAR(1024), CMT VARCHAR(1024), ATYPE INT, GUI_CNT INT(4), COL_WIDTH INT(4), DEFEXT ARRAY)");
s.execute("ALTER TABLE T_ATTR ADD FOREIGN KEY(DESCR_ID) REFERENCES T_DESCR(ID)");
s.execute("ALTER TABLE T_ATTR ADD PRIMARY KEY(DESCR_ID, COL_CNT)");
s.execute(SQL_CREATE_DESCR_LAYOUT_1);
s.execute(SQL_CREATE_DESCR_LAYOUT_2);
s.execute(SQL_CREATE_DESCR_LAYOUT_3);
s.execute(SQL_CREATE_DESCR_LAYOUT_4);
s.execute(SQL_CREATE_DESCR_LAYOUT_5);
// Create datatable for extend attribut types
for (AttrType at : AttrType.values())
{
if (at.extended)
{
try
{
Method m = at.dataClass.getDeclaredMethod("getTableDecl", (Class[]) null);
String[] declTableScript = (String[]) m.invoke(null, (Object[]) null);
for (String cmd : declTableScript)
{
s.execute(cmd);
}
}
catch (SecurityException e)
{
// Should not occur
e.printStackTrace();
}
catch (NoSuchMethodException e)
{
// Should not occur
e.printStackTrace();
}
catch (IllegalArgumentException e)
{
e.printStackTrace();
}
catch (IllegalAccessException e)
{
e.printStackTrace();
}
catch (InvocationTargetException e)
{
e.printStackTrace();
}
}
}
// Initial filling
s.execute("INSERT INTO T_GENINFO VALUES ( '" + SCHEME_LEVEL + "' , '2010-01-01', 'Test DB')");
commit();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
public Connection getCnx()
{
return _cnx;
}
@Override
public int close()
{
try
{
if (_cnx != null)
{
_cnx.close();
_cnx = null;
}
}
catch (SQLException e)
{
e.printStackTrace();
}
return C_OK;
}
@Override
public int commit()
{
try
{
getCnx().commit();
}
catch (SQLException e)
{
e.printStackTrace();
}
return 0;
}
/**
* Return a list of completed read FolderDescriptions.
*/
public List<FolderDescription> getFolderDescriptionList()
{
if (_fileDescrLst == null)
{
_fileDescrLst = new LinkedList<FolderDescription>();
try
{
// Retrieve files known in database
PreparedStatement prep = _cnx.prepareStatement("SELECT * FROM T_DESCR WHERE DTYPE=1");
ResultSet rs = prep.executeQuery();
while (rs.next())
{
FolderDescription folder = new FolderDescription(rs.getLong(1));
folder.setName(rs.getString(2));
folder.setComment(rs.getString(3));
// (4) : DTYPE
folder.setTName(rs.getString(5));
folder.setIconAttrIndex(rs.getInt(6));
folder.setLabelAttrIndex(rs.getInt(7));
folder.setCardID(rs.getLong(8));
folder.read(this);
_fileDescrLst.add(folder);
}
prep.close();
}
catch (SQLException sqex)
{
sqex.printStackTrace();
}
}
return _fileDescrLst;
}
private int open(String pathName)
{
int res = C_OK;
_dbPath = pathName.endsWith(File.separator) ? pathName : pathName + File.separator;
String cnxStr = "jdbc:h2:" + _dbPath + "hidb"; //;TRACE_LEVEL_SYSTEM_OUT=3";
try
{
_cnx = DriverManager.getConnection(cnxStr, "sa", "");
_cnx.setAutoCommit(false);
}
catch (SQLException e)
{
// Database does probably not exist
e.getErrorCode();
log.warning("Database [" + cnxStr + "] does probably not exist " + e.getMessage());
_cnx = null;
res = C_ERROR;
}
return res;
}
@Override
public int rollback()
{
try
{
_cnx.rollback();
}
catch (SQLException e)
{
e.printStackTrace();
}
return 0;
}
/**
* @see deyme.hidb.kern.itf.ImageDB#addFolderDescription(FolderDescription,
* int)
*/
public int addFolderDescription(FolderDescription fde, int position)
{
fde.setStatus(StatusCycle.CREATED);
_fileDescrLst.add(position, fde);
return C_OK;
}
/**
* @see deyme.hidb.kern.itf.ImageDB#removeFolderDescription(FolderDescription)
*/
public int removeFolderDescription(FolderDescription fde)
{
int ret = C_OK;
if (getFolderDescriptionList().contains(fde))
{
if (fde.getStatus() != StatusCycle.CREATED)
{
fde.setStatus(StatusCycle.DELETED);
}
getFolderDescriptionList().remove(fde);
}
else
{
ret = C_FAIL;
}
return ret;
}
/**
* @see deyme.hidb.kern.itf.ImageDB#addListDescription(ListDescription,
* int)
*/
public int addListDescription(ListDescription lde, int position)
{
_listDescrLst.add(position, lde);
return C_OK;
}
/**
* @see deyme.hidb.kern.itf.ImageDB#removeListDescription(ListDescription)
*/
public int removeListDescription(ListDescription lde)
{
int ret = C_OK;
if (getListDescriptionList().contains(lde))
{
if (lde.getStatus() != StatusCycle.CREATED)
{
lde.setStatus(StatusCycle.DELETED);
}
getListDescriptionList().remove(lde);
}
else
{
ret = C_FAIL;
}
return ret;
}
/**
* @return
*/
public List<DataPath> getDataPathList()
{
if (_dataPathLst == null)
{
_dataPathLst = new ArrayList<DataPath>();
try
{
// Retrieve DataPath known in database
PreparedStatement prep = _cnx.prepareStatement("SELECT * FROM T_DATAPATH ORDER BY ID");
ResultSet rs = prep.executeQuery();
while (rs.next())
{
DataPath dp = new DataPath(rs.getLong(1));
dp.setName(rs.getString(2));
dp.setComment(rs.getString(3));
dp.setPath(rs.getString(4));
_dataPathLst.add(dp);
}
prep.close();
}
catch (SQLException sqex)
{
sqex.printStackTrace();
}
}
return _dataPathLst;
}
/**
* Write (insert, update or delete) a Datapath.
* Impact (on delete) should have been managed before.
*
* @param dp
* @return
*/
public int writeDataPathList()
{
int res = C_OK;
for (Iterator<DataPath> it = this._dataPathLst.iterator(); it.hasNext();)
{
DataPath dp = it.next();
try
{
PreparedStatement prep = null;
switch (dp.getStatus())
{
case CREATED:
prep = _cnx.prepareStatement("INSERT INTO T_DATAPATH (ID, NAME, CMT, DIRNAME) VALUES (?,?,?,?)");
prep.setLong(1, dp.getID());
prep.setString(2, dp.getName());
prep.setString(3, dp.getComment());
prep.setString(4, dp.getPath());
/* int nbrows */
prep.executeUpdate();
break;
case MODIFIED:
prep = _cnx.prepareStatement("UPDATE T_DATAPATH SET NAME=?, CMT=?, DIRNAME=? WHERE ID=?");
prep.setString(1, dp.getName());
prep.setString(2, dp.getComment());
prep.setString(3, dp.getPath());
prep.setLong(4, dp.getID());
/* int nbrows */
prep.executeUpdate();
break;
case DELETED:
prep = _cnx.prepareStatement("DELETE FROM T_DATAPATH WHERE ID=?");
prep.setLong(1, dp.getID());
/* int nbrows */
prep.executeUpdate();
it.remove();
break;
}
if (prep != null)
{
prep.close();
dp.setStatus(StatusCycle.UNCHANGED);
}
}
catch (SQLException sqex)
{
sqex.printStackTrace();
}
}
return res;
}
public DataPath findDataPath(long id)
{
// Ensure that datapaths have been retrieved
for (DataPath dp : getDataPathList())
{
if (id == dp.getID())
{
return dp;
}
}
return null;
}
@Override
public int[] getDataPathUsage(DataPath dp)
{
int[] references = new int[AttrType.values().length];
for (AttrType at : AttrType.values())
{
if (at.extended)
{
PreparedStatement prep = StatGenTypes[at.ordinal()].datapathReference;
if (prep != null)
{
try
{
prep.clearParameters();
prep.setLong(1, dp.getID());
ResultSet rs = prep.executeQuery();
if (rs.next())
{
references[at.ordinal()] = rs.getInt(1);
}
}
catch (SQLException sqlx)
{
log.warning(sqlx.getMessage());
references[at.ordinal()] = -2;
}
}
else
{
references[at.ordinal()] = -1;
}
}
}
return references;
}
@Override
public int remove(DataPath dp)
{
int ret = C_OK;
if (getDataPathList().contains(dp))
{
if (dp.getStatus() != StatusCycle.CREATED)
{
dp.setStatus(StatusCycle.DELETED);
}
else
{
getDataPathList().remove(dp);
}
}
else
{
ret = C_FAIL;
}
return ret;
}
/**
* Find the first correct DataPath.
* @param fullname Shall be an AbsolutePath (see File)
* @return null if not found
*/
public DataPath findDataPath(String fullname)
{
for (DataPath dp : getDataPathList())
{
if (fullname.startsWith(dp.getPath()))
{
return dp;
}
}
return null;
}
/**
* Return a list of completed read ListDescriptions.
*
* @return
*/
public List<ListDescription> getListDescriptionList()
{
if (_listDescrLst == null)
{
_listDescrLst = new LinkedList<ListDescription>();
try
{
// Retrieve list descriptions known in database
PreparedStatement prep = _cnx.prepareStatement("SELECT * FROM T_DESCR WHERE DTYPE=3 ORDER BY ID");
ResultSet rs = prep.executeQuery();
while (rs.next())
{
ListDescription ld = new ListDescription(rs.getLong(1));
ld.setName(rs.getString(2));
ld.setComment(rs.getString(3));
// (4) DTYPE=3
ld.setTName(rs.getString(5));
ld.setIconAttrIndex(rs.getInt(6));
ld.setLabelAttrIndex(rs.getInt(7));
ld.read(this);
_listDescrLst.add(ld);
}
prep.close();
}
catch (SQLException sqex)
{
sqex.printStackTrace();
}
}
return _listDescrLst;
}
/*
* (non-Javadoc)
*
* @see deyme.hidb.kern.itf.ImageDB#createFolderDescription()
*/
public FolderDescription createFolderDescription()
{
// Calc attribut ID
long id = getNextDescrID();
FolderDescription fd = new FolderDescription(id);
// Get an ID for the card description
long idc = getNextDescrID();
fd.setCardID(idc);
fd.setStatus(StatusCycle.CREATED);
fd.getCardDescription().setStatus(StatusCycle.CREATED);
getFolderDescriptionList().add(fd);
return fd;
}
/*
* (non-Javadoc)
*
* @see deyme.hidb.kern.itf.ImageDB#createFileInstance(deyme.hidb.kern.itf.FolderDescription)
*/
public Folder createFolder(FolderDescription fd)
{
// Calc attribut ID
long id = getNextFolderID();
return new Folder(fd, this, id);
}
@Override
public int remove(Folder fld)
{
switch (fld.getStatus())
{
case CREATED:
fld.setStatus(StatusCycle.WEAK);
fld.getLstCard().clear();
break;
case MODIFIED:
case UNCHANGED:
for (Card crd : fld.getLstCard())
{
remove(fld, crd);
}
fld.setStatus(StatusCycle.DELETED);
break;
}
// The folder has to be saved to be really deleted.
return C_OK;
}
@Override
public Card createCard(Folder fld)
{
long id = -1L;
// Create a new card - ID is generated by the database at write time
Card crd = new Card(fld, id);
fld.getLstCard().add(crd);
crd.setStatus(StatusCycle.CREATED);
if (fld.getStatus() == StatusCycle.UNCHANGED)
{
fld.setStatus(StatusCycle.MODIFIED);
}
return crd;
}
@Override
public int remove(Folder fld, Card crd)
{
switch (crd.getStatus())
{
case CREATED:
crd.setStatus(StatusCycle.WEAK);
fld.getLstCard().remove(crd);
if (fld.getStatus() == StatusCycle.UNCHANGED)
{
fld.setStatus(StatusCycle.MODIFIED);
}
break;
case MODIFIED:
case UNCHANGED:
crd.setStatus(StatusCycle.DELETED);
if (fld.getStatus() == StatusCycle.UNCHANGED)
{
fld.setStatus(StatusCycle.MODIFIED);
}
break;
}
return C_OK;
}
/*
* (non-Javadoc)
*
* @see deyme.hidb.kern.itf.ImageDB#createDataPath()
*/
public DataPath createDataPath()
{
// Calc datapath ID
long id = getNextDataPathID();
DataPath pth = new DataPath(id);
pth.setStatus(StatusCycle.CREATED);
_dataPathLst.add(pth);
return pth;
}
/*
* (non-Javadoc)
*
* @see deyme.hidb.kern.itf.ImageDB#createListDescription()
*/
public ListDescription createListDescription()
{
// Calc attribut ID
long id = getNextDescrID();
ListDescription ld = new ListDescription(id);
ld.setStatus(StatusCycle.CREATED);
getListDescriptionList().add(ld);
return ld;
}
/*
* (non-Javadoc)
*
* @see deyme.hidb.kern.itf.ImageDB#search(deyme.hidb.kern.itf.Attribut)
*/
public FolderDescription search(Attribut attr)
{
for (FolderDescription fd : _fileDescrLst)
{
for (Attribut a : fd.getAttributList())
{
if (attr.equals(a))
{
return fd;
}
}
// Search in sub description if not found
for (Attribut a : fd.getCardDescription().getAttributList())
{
if (attr.equals(a))
{
return fd;
}
}
}
return null;
}
//-----------------------------------------------------------------------------------
// Data Read/Write operations
//-----------------------------------------------------------------------------------
/**
* Read (in DB) the folders associated to a FolderDescription (lazzy loading).
* This method does not keep trace (in memory) of this read.
*/
public List<Folder> readData(FolderDescription fd)
{
List<Folder> dataLst = new ArrayList<Folder>();
try
{
// Build the SELECT & Execute it
Statement s = getCnx().createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM " + fd.getTName());
// Convert / set data
while (rs.next())
{
Folder folder = new Folder(fd, this, rs, false);
dataLst.add(folder);
}
s.close();
}
catch (SQLException sqex)
{
sqex.printStackTrace();
}
return dataLst;
}
/**
* Read the layout(s) recorded for a AttributedDescription
*/
public AttributedDescriptionLayout readLayout(AttributedDescription ad)
{
AttributedDescriptionLayout layout = new AttributedDescriptionLayout(0L, ad);
try
{
// Build the SELECT & Execute it
Statement s = getCnx().createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM T_DESCRLAYOUT WHERE DESCR_ID=" + ad.getID());
// Convert / set data
if (rs.next())
{
// Read Description layout data : TABLE T_DESCRLAYOUT(DESCR_ID INT NOT NULL PRIMARY KEY, ISVERT BOOLEAN, COL_CNT INT(2), IMG_IDX INT(4)
AttributedDescriptionLayout adl = new AttributedDescriptionLayout(rs.getLong(1), ad);
adl.setVertical(rs.getBoolean(2));
adl.setColumnCount(rs.getInt(3));
adl.setImageIdx(rs.getInt(4));
// Read Attribut layout data : TABLE T_ATTRLAYOUT(DESCR_ID INT NOT NULL, COL_CNT INT NOT NULL, WIDTH INT(4), HEIGHT INT(4)
Statement s2 = getCnx().createStatement();
ResultSet rs2 = s2.executeQuery("SELECT * FROM T_ATTRLAYOUT WHERE DESCR_ID=" + ad.getID());
while (rs2.next())
{
int colCnt = rs2.getInt(2);
AttrLayout al = adl.getLstLayout().get(colCnt);
al._size.x = rs2.getInt(3);
al._size.y = rs2.getInt(4);
}
s2.close();
}
s.close();
}
catch (SQLException sqex)
{
sqex.printStackTrace();
}
return layout;
}
@Override
public int writeLayout(AttributedDescriptionLayout ald)
{
// TODO Auto-generated method stub
log.warning("Not implemented yet");
return C_OK;
}
@Override
public EnumItem createEnumItem(ListDescription ld)
{
// Calc attribut ID
long id = getNextListID();
return new EnumItem(ld, id);
}
@Override
public List<EnumItem> readData(ListDescription ld)
{
List<EnumItem> dataLst = new ArrayList<EnumItem>();
try
{
// Build the SELECT & Execute it
Statement s = getCnx().createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM " + ld.getTName());
// Convert / set data
while (rs.next())
{
EnumItem folder = new EnumItem(ld, this, rs);
dataLst.add(folder);
}
s.close();
}
catch (SQLException sqex)
{
sqex.printStackTrace();
}
return dataLst;
}
@Override
public int remove(EnumItem rlst)
{
switch (rlst.getStatus())
{
case CREATED:
rlst.setStatus(StatusCycle.WEAK);
break;
case MODIFIED:
case UNCHANGED:
rlst.setStatus(StatusCycle.DELETED);
break;
}
// The reflist has to be saved to be really deleted.
return C_OK;
}
/**
* Execute a simple search based on Lucene indexation engine.
* @param q
* @return
* @throws SQLException
*/
private List<FolderSearchResult> searchSimple(FolderSearchQuery q) throws SQLException
{
List<FolderSearchResult> lstSR = new LinkedList<FolderSearchResult>();
// TODO : Manage 'allWord' in search
// Build the SELECT & Execute it
Statement s = getCnx().createStatement();
ResultSet rs = FullTextLucene.searchData(getCnx(), q.searchPattern, 0, 0);
while (rs.next())
{
FolderSearchResult prev_res = null;
// String schema = rs.getString(1); Schema name is ignored
String table_name = rs.getString(2);
for (FolderSearchResult fsr : lstSR)
{
if (table_name.equals(fsr.getTableName()))
{
prev_res = fsr;
break;
}
}
if (prev_res == null)
{
prev_res = new FolderSearchResult(table_name);
lstSR.add(prev_res);
}
// Object columns = rs.getObject(3); Column names are ignored
Object keys = rs.getObject(4);
for (Object o : (Object[]) keys)
{
prev_res.addID(Integer.parseInt(((String) o)));
}
}
s.close();
return lstSR;
}
/**
* Advanced search implementation : Automatic SQL query creation.
*
* @param q
* @return
* @throws SQLException
*/
private List<FolderSearchResult> searchAdvanced(FolderSearchQuery q) throws SQLException
{
List<FolderSearchResult> lstSR = new LinkedList<FolderSearchResult>();
FolderSearchResult prev_res = new FolderSearchResult(q.fd.getTName());
// Build the SELECT & Execute it
Statement s = getCnx().createStatement();
String select = "SELECT ID FROM " + q.fd.getTName() + " WHERE ";
boolean altOp = false;
String baseOp = q.allWord ? " AND " : " OR ";
String op = "";
for (QueryAttr qa : q.lstQAttr)
{
AttrType t = qa.getAttr().getType();
if (altOp)
{
op = baseOp;
}
String sep = ((t == AttrType.T_String) || (t == AttrType.T_Date) || (t == AttrType.T_Time)) ? "'" : "";
switch (qa._test)
{
case ET_EQUALS: // TODO: Check Min/Max data types
select = select + op + "(" + qa.getAttr().getColName() + "=" + sep + qa.getMin() + sep + ")";
altOp = true;
break;
case ET_IN:
select = select + op + "(" + "(" + qa.getAttr().getColName() + ">=" + sep + qa.getMin() + sep + ")"
+ " AND (" + qa.getAttr().getColName() + "<=" + sep + qa.getMax() + sep + ")" + ")";
altOp = true;
break;
case ET_GREATER:
select = select + op + "(" + qa.getAttr().getColName() + ">=" + sep + qa.getMin() + sep + ")";
altOp = true;
break;
case ET_LESSER:
select = select + op + "(" + qa.getAttr().getColName() + "<=" + sep + qa.getMin() + sep + ")";
altOp = true;
break;
case ET_CONT:
if (t == AttrType.T_String)
{
select = select + op + "(" + qa.getAttr().getColName() + " LIKE " + sep + '%' + qa.getMin() + '%' + sep
+ ")";
altOp = true;
}
break;
case ET_BEGINS:
if (t == AttrType.T_String)
{
select = select + op + "(" + qa.getAttr().getColName() + " LIKE " + sep + qa.getMin() + '%' + sep + ")";
altOp = true;
}
break;
case ET_ENDS:
if (t == AttrType.T_String)
{
select = select + op + "(" + qa.getAttr().getColName() + " LIKE " + sep + '%' + qa.getMin() + sep + ")";
altOp = true;
}
break;
}
}
log.info("Query:" + select);
ResultSet rs = s.executeQuery(select);
while (rs.next())
{
int id = rs.getInt(1);
prev_res.addID(id);
}
s.close();
lstSR.add(prev_res);
return lstSR;
}
@Override
public List<FolderSearchResult> search(FolderSearchQuery q)
{
List<FolderSearchResult> lstSR = null;
try
{
lstSR = q.useAnyFolder ? searchSimple(q) : searchAdvanced(q);
}
catch (SQLException sqex)
{
sqex.printStackTrace();
}
return lstSR;
}
@Override
public FolderDiscretResult cubicSearch(FolderSearchQuery q)
{
FolderDiscretResult res = new FolderDiscretResult(q);
try
{
// Build the SELECT & Execute it
Statement s = getCnx().createStatement();
QueryAttr qa = q.lstQAttr.get(0);
String[] lh = res.getLinesHeaders();
for (int i = 0; i < lh.length; i++)
{
String select = "SELECT ID FROM " + q.fd.getTName() + " WHERE ";
AttrType t = qa.getAttr().getType();
// if (altOp)
// {
// op = baseOp;
// }
String sep = ((t == AttrType.T_String) || (t == AttrType.T_Date) || (t == AttrType.T_Time)) ? "'" : "";
log.info("Query:" + select);
ResultSet rs = s.executeQuery(select);
while (rs.next())
{
int id = rs.getInt(1);
// TODO prev_res.addID(id);
}
s.close();
}
}
catch (SQLException sqex)
{
sqex.printStackTrace();
}
return res;
}
@Override
public Folder find(FolderDescription fd, int id)
{
Folder folder = null;
try
{
// Build the SELECT & Execute it
PreparedStatement s = fd.getIDSelect(this);
s.setInt(1, id);
ResultSet rs = s.executeQuery();
// Convert / set data
if (rs.next())
{
folder = new Folder(fd, this, rs, false);
}
rs.close();
}
catch (SQLException sqex)
{
sqex.printStackTrace();
}
return folder;
}
@Override
public Folder find(CardDescription cd, int id)
{
Folder folder = null;
try
{
PreparedStatement s = getCnx().prepareStatement("SELECT FOLDER_ID FROM " + cd.getTName() + " WHERE ID=?");
s.setInt(1, id);
ResultSet rs = s.executeQuery();
if (rs.next())
{
// Retrieve the folder
int folder_id = rs.getInt(1);
folder = find(cd.getParent(), folder_id);
}
else
{
log.warning("No card found:" + id);
}
s.close();
}
catch (SQLException sqex)
{
sqex.printStackTrace();
}
return folder;
}
@Override
public AttributedDescription find(String tblName)
{
for (FolderDescription fd : getFolderDescriptionList())
{
if (tblName.equals(fd.getTName()))
{
return fd;
}
else
{
if (tblName.equals(fd.getCardDescription().getTName()))
{
return fd.getCardDescription();
}
}
}
for (ListDescription ld : getListDescriptionList())
{
if (tblName.equals(ld.getTName()))
{
return ld;
}
}
return null;
}
}