@Override
public void updateBook(BookModel bookModel) {
create
.update(BOOK)
.set(BOOK.TITLE, bookModel.getTitle())
.set(BOOK.DATE_PUBLISH, bookModel.getDatePublish())
.set(BOOK.SHORT_DESCRIPTION, bookModel.getShortDescription())
.where(BOOK.ID_BOOK.equal(bookModel.getIdBook()))
.execute();
List<Integer> existsAuthors =
create
.select(AUTHOR_BOOK.ID_AUTHOR)
.from(AUTHOR_BOOK)
.where(AUTHOR_BOOK.ID_BOOK.equal(bookModel.getIdBook()))
.fetchInto(Integer.class);
logger.debug("Existing authors: " + existsAuthors);
List<Integer> newAuthors = new ArrayList<>();
List<Integer> authors = new ArrayList<>();
/** */
for (String idAuthor : bookModel.getAuthors()) {
int id = Integer.valueOf(idAuthor);
authors.add(id);
if (!existsAuthors.contains(id)) {
newAuthors.add(id);
}
}
existsAuthors.removeAll(authors);
logger.debug("New authors: " + newAuthors);
if (!newAuthors.isEmpty()) {
InsertValuesStep2<AuthorBookRecord, Integer, Integer> insertStep =
create.insertInto(AUTHOR_BOOK, AUTHOR_BOOK.ID_AUTHOR, AUTHOR_BOOK.ID_BOOK);
InsertValuesStep2<AuthorBookRecord, Integer, Integer> valuesStep = null;
for (Integer idAuthor : newAuthors) {
valuesStep = insertStep.values(idAuthor, bookModel.getIdBook());
}
valuesStep.execute();
}
logger.debug("Old authors: " + existsAuthors);
if (!existsAuthors.isEmpty()) {
create.delete(AUTHOR_BOOK).where(AUTHOR_BOOK.ID_AUTHOR.in(existsAuthors)).execute();
}
}
@Override
public void update() throws IOException {
ResourceFileLocation location = getLocation("KEGG Reaction");
HSQLDBLocation connection = connection();
try {
Hsqldb.createReactionSchema(connection.getConnection());
DSLContext create = DSL.using(connection.getConnection(), HSQLDB);
Set<String> compoundIds = Sets.newHashSetWithExpectedSize(10000);
InsertValuesStep2<?, String, String> reactionInsert =
create.insertInto(REACTION, REACTION.ACCESSION, REACTION.EC);
InsertValuesStep1<?, String> compoundInsert = create.insertInto(COMPOUND, COMPOUND.ACCESSION);
List<String[]> reactants = new ArrayList<String[]>(10000);
List<String[]> products = new ArrayList<String[]>(10000);
KEGGReactionParser parser =
new KEGGReactionParser(
location.open(), KEGGField.ENTRY, KEGGField.EQUATION, KEGGField.ENZYME);
Map<KEGGField, StringBuilder> entry;
while ((entry = parser.readNext()) != null) {
if (isCancelled()) break;
String equation = entry.get(KEGGField.EQUATION).toString();
String ec =
entry.containsKey(KEGGField.ENZYME)
? entry.get(KEGGField.ENZYME).toString().trim()
: "";
String[] sides = equation.split("<=>");
String[][] left = getParticipants(sides[0]);
String[][] right = getParticipants(sides[1]);
Matcher matcher = ACCESSION.matcher(entry.get(KEGGField.ENTRY).toString());
if (!ec.isEmpty()) ec = ec.split("\\s+")[0].trim();
if (matcher.find()) {
String accession = matcher.group(1);
reactionInsert.values(accession, ec);
for (String[] participant : left) {
String cid = participant[1];
if (compoundIds.add(cid)) compoundInsert.values(cid);
participant = Arrays.copyOf(participant, 3);
participant[2] = accession;
reactants.add(participant);
}
for (String[] participant : right) {
String cid = participant[1];
if (compoundIds.add(cid)) compoundInsert.values(cid);
participant = Arrays.copyOf(participant, 3);
participant[2] = accession;
products.add(participant);
}
}
}
// do the inserts
fireProgressUpdate("inserting reactions and compounds");
reactionInsert.execute();
compoundInsert.execute();
fireProgressUpdate("inserting reaction relations");
for (int i = 0, end = reactants.size() - 1; i <= end; i++) {
String[] participant = reactants.get(i);
double coef = Double.parseDouble(participant[0]);
String cid = participant[1];
String acc = participant[2];
create
.insertInto(REACTANT)
.set(REACTANT.COEFFICIENT, coef)
.set(
REACTANT.COMPOUND_ID,
create.select(COMPOUND.ID).from(COMPOUND).where(COMPOUND.ACCESSION.eq(cid)))
.set(
REACTANT.REACTION_ID,
create.select(REACTION.ID).from(REACTION).where(REACTION.ACCESSION.eq(acc)))
.execute();
}
for (int i = 0, end = products.size() - 1; i <= end; i++) {
String[] participant = products.get(i);
double coef = Double.parseDouble(participant[0]);
String cid = participant[1];
String acc = participant[2];
create
.insertInto(PRODUCT)
.set(PRODUCT.COEFFICIENT, coef)
.set(
PRODUCT.COMPOUND_ID,
create.select(COMPOUND.ID).from(COMPOUND).where(COMPOUND.ACCESSION.eq(cid)))
.set(
PRODUCT.REACTION_ID,
create.select(REACTION.ID).from(REACTION).where(REACTION.ACCESSION.eq(acc)))
.execute();
}
} catch (SQLException e) {
throw new IOException(e);
} finally {
location.close();
try {
connection.commit();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
try {
connection.close();
} catch (SQLException e) {
}
}
}
}