1 package com.kodexa.client;
2
3 import com.fasterxml.jackson.annotation.JsonInclude;
4 import com.fasterxml.jackson.core.JsonProcessingException;
5 import com.fasterxml.jackson.core.type.TypeReference;
6 import com.fasterxml.jackson.databind.ObjectMapper;
7 import lombok.extern.slf4j.Slf4j;
8 import org.apache.commons.io.IOUtils;
9 import org.apache.commons.lang3.tuple.ImmutablePair;
10 import org.jdbi.v3.core.Handle;
11 import org.jdbi.v3.core.Jdbi;
12 import org.msgpack.jackson.dataformat.MessagePackFactory;
13 import org.sqlite.SQLiteConfig;
14
15 import java.io.*;
16 import java.nio.file.Files;
17 import java.nio.file.Path;
18 import java.security.MessageDigest;
19 import java.security.NoSuchAlgorithmException;
20 import java.util.*;
21 import java.util.stream.Collectors;
22
23
24
25
26 @Slf4j
27 public class SqlitePersistenceLayer {
28
29 private final static ObjectMapper OBJECT_MAPPER_MSGPACK;
30 private final Document document;
31
32 Map<Integer, String> nodeTypes = new HashMap<>();
33
34 Map<Integer, String> featureTypeNames;
35
36 private final String FEATURE_INSERT = "INSERT INTO ft (cn_id, f_type, binary_value, single, tag_uuid) VALUES (?,?,?,?,?)";
37 private final String CONTENT_NODE_INSERT = "INSERT INTO cn (pid, nt, idx) VALUES (?,?,?)";
38 private final String CONTENT_NODE_INSERT_WITH_ID = "INSERT INTO cn (id, pid, nt, idx) VALUES (?,?,?,?)";
39 private final String CONTENT_NODE_PART_INSERT = "INSERT INTO cnp (cn_id, pos, content, content_idx) VALUES (?,?,?,?)";
40
41 static {
42 OBJECT_MAPPER_MSGPACK = new ObjectMapper(new MessagePackFactory());
43 OBJECT_MAPPER_MSGPACK.setSerializationInclusion(JsonInclude.Include.NON_NULL);
44 }
45
46 private String dbPath;
47 private Jdbi jdbi;
48 private boolean tempFile = false;
49 private final MessageDigest md;
50
51 public SqlitePersistenceLayer(Document document) {
52 File file = null;
53
54 try {
55 md = MessageDigest.getInstance("SHA-1");
56
57 this.document = document;
58 file = File.createTempFile("kdxa", "kddb");
59 this.dbPath = file.getAbsolutePath();
60 this.tempFile = true;
61 file.deleteOnExit();
62 this.initializeLayer();
63 this.initializeDb();
64 } catch (IOException | NoSuchAlgorithmException e) {
65 throw new KodexaException("Unable to initialize the temp file for KDDB", e);
66 }
67 }
68
69 private void initializeDb() {
70
71 jdbi.withHandle(handle -> {
72 handle.execute("CREATE TABLE version (id integer primary key, version text)");
73 handle.execute("CREATE TABLE metadata (id integer primary key, metadata text)");
74 handle.execute("CREATE TABLE cn (id integer primary key, nt INTEGER, pid INTEGER, idx INTEGER)");
75 handle.execute(
76 "CREATE TABLE cnp (id integer primary key, cn_id INTEGER, pos integer, content text, content_idx integer)");
77
78 handle.execute("CREATE TABLE n_type (id integer primary key, name text)");
79 handle.execute("CREATE TABLE f_type (id integer primary key, name text)");
80
81 handle.execute(
82 "CREATE TABLE ft (id integer primary key, cn_id integer, f_type INTEGER, binary_value blob, single integer, tag_uuid text)");
83
84 handle.execute("CREATE UNIQUE INDEX n_type_uk ON n_type(name);");
85 handle.execute("CREATE UNIQUE INDEX f_type_uk ON f_type(name);");
86 handle.execute("CREATE INDEX cn_perf ON cn(nt);");
87 handle.execute("CREATE INDEX cn_perf2 ON cn(pid);");
88 handle.execute("CREATE INDEX cnp_perf ON cnp(cn_id, pos);");
89 handle.execute("CREATE INDEX f_perf ON ft(cn_id);");
90 handle.execute("CREATE INDEX f_perf2 ON ft(tag_uuid);");
91 handle.execute("CREATE TABLE IF NOT EXISTS content_exceptions (id integer primary key,tag text,message text,exception_details text,group_uuid text,tag_uuid text, exception_type text, severity text, node_uuid text)");
92 handle.execute("CREATE TABLE IF NOT EXISTS model_insights (id integer primary key,model_insight text)");
93 return handle;
94 });
95 }
96
97 public void close() {
98 if (tempFile) {
99 try {
100 Files.delete(Path.of(this.dbPath));
101 } catch (IOException e) {
102 throw new KodexaException("Unable to delete temp file", e);
103 }
104 }
105 }
106
107 public SqlitePersistenceLayer(InputStream kddbInputStream, Document document) {
108 final File tempFile;
109 try {
110 md = MessageDigest.getInstance("SHA-1");
111
112 this.document = document;
113 tempFile = File.createTempFile("kdxa", "kddb");
114 tempFile.deleteOnExit();
115 try (FileOutputStream out = new FileOutputStream(tempFile)) {
116 IOUtils.copy(kddbInputStream, out);
117 }
118 this.dbPath = tempFile.getAbsolutePath();
119 this.tempFile = true;
120 this.initializeLayer();
121 this.loadDocument();
122 } catch (IOException | NoSuchAlgorithmException e) {
123 throw new KodexaException("Unable to create persistence layer for KDDB object", e);
124 }
125 }
126
127 public SqlitePersistenceLayer(File kddbFile, Document document) {
128
129 try {
130 md = MessageDigest.getInstance("SHA-1");
131
132 this.dbPath = kddbFile.getAbsolutePath();
133 this.document = document;
134 this.initializeLayer();
135 } catch (NoSuchAlgorithmException e) {
136 throw new KodexaException("Unable to initialize KDDB", e);
137 }
138
139 }
140
141 private void initializeLayer() {
142 try {
143 Class.forName("org.sqlite.JDBC");
144 SQLiteConfig config = new SQLiteConfig();
145 config.setJournalMode(SQLiteConfig.JournalMode.OFF);
146 jdbi = Jdbi.create("jdbc:sqlite:" + dbPath, config.toProperties());
147 } catch (ClassNotFoundException e) {
148 throw new KodexaException("Unable to create persistence layer for KDDB object", e);
149 }
150 }
151
152 protected void loadDocument() {
153
154
155 jdbi.withHandle(handle -> {
156
157
158 try {
159 byte[] metadataPack = (byte[]) handle.createQuery("SELECT * FROM metadata")
160 .mapToMap()
161 .first().get("metadata");
162
163 Document baseDocument = OBJECT_MAPPER_MSGPACK.readValue(metadataPack, Document.class);
164 document.setSource(baseDocument.getSource());
165 document.setClasses(baseDocument.getClasses());
166 document.setLabels(baseDocument.getLabels());
167 document.setMetadata(baseDocument.getMetadata());
168 document.setUuid(baseDocument.getUuid());
169 document.setMixins(baseDocument.getMixins());
170 document.setVersion(baseDocument.getVersion());
171 handle.execute("CREATE TABLE IF NOT EXISTS content_exceptions (id integer primary key,tag text,message text,exception_details text,group_uuid text,tag_uuid text, exception_type text, severity text, node_uuid text)");
172 handle.execute("CREATE TABLE IF NOT EXISTS model_insights (id integer primary key,model_insight text)");
173
174
175 if (document.getVersion().equals("4.0.0") || document.getVersion().equals("2.0.0")) {
176
177 handle.execute("CREATE TABLE ft (id integer primary key,cn_id integer,f_type INTEGER, binary_value blob,single integer,tag_uuid text)");
178 handle.execute("insert into ft select f.id, f.cn_id, f.f_type, fv.binary_value, fv.single, null from f, f_value fv where fv.id = f.id");
179 handle.execute("drop table f");
180 handle.execute("drop table f_value");
181 handle.execute("CREATE INDEX f_perf ON ft(cn_id);");
182 handle.execute("CREATE INDEX f_perf2 ON ft(tag_uuid);");
183 document.setVersion("4.0.1");
184 flushMetadata();
185 }
186
187
188
189 nodeTypes =
190 handle.createQuery("SELECT id, name FROM n_type")
191 .mapToMap()
192 .collect(Collectors.toMap(x -> Integer.valueOf(String.valueOf(x.get("id"))), x -> String.valueOf(x.get("name"))));
193
194 featureTypeNames =
195 handle.createQuery("SELECT id, name FROM f_type")
196 .mapToMap()
197 .collect(Collectors.toMap(x -> Integer.valueOf(String.valueOf(x.get("id"))), x -> String.valueOf(x.get("name"))));
198 } catch (IOException e) {
199 throw new KodexaException("Unable to unpack metadata", e);
200 }
201
202 List<Map<String, Object>> contentNodes =
203 handle.createQuery("SELECT id, nt, pid, idx FROM cn where pid is null")
204 .mapToMap()
205 .list();
206 for (Map<String, Object> contentNode : contentNodes) {
207 document.setContentNode(buildNode(contentNode, handle));
208 }
209
210 return contentNodes;
211 });
212 }
213
214 public void updateNode(ContentNode node) {
215 jdbi.withHandle(handle -> {
216 updateNode(handle, node, node.getParentId(), false);
217 return null;
218 });
219 }
220
221 private ContentNode buildNode(Map<String, Object> contentNodeValues, Handle handle) {
222 ContentNodetNode">ContentNode contentNode = new ContentNode(this.document);
223 contentNode.setUuid(String.valueOf(contentNodeValues.get("id")));
224 contentNode.setType(nodeTypes.get(contentNodeValues.get("nt")));
225 contentNode.setIndex(Integer.parseInt(String.valueOf(contentNodeValues.get("idx"))));
226
227 Object parentId = contentNodeValues.get("pid");
228 contentNode.setParentId(parentId != null ? Integer.valueOf(String.valueOf(parentId)) : null);
229
230 List<Map<String, Object>> features =
231 handle.createQuery("SELECT id, f_type, binary_value, single FROM ft where cn_id = :nodeId").bind("nodeId", contentNode.getUuid())
232 .mapToMap()
233 .list();
234
235 List<Map<String, Object>> contentParts =
236 handle.createQuery("SELECT content, content_idx FROM cnp where cn_id = :nodeId order by pos asc").bind("nodeId", contentNode.getUuid())
237 .mapToMap()
238 .list();
239
240 List<String> parts = new ArrayList<>();
241 contentNode.setContentParts(new ArrayList<>());
242 for (Map<String, Object> contentPart : contentParts) {
243 if (contentPart.get("content") != null) {
244 contentNode.getContentParts().add(contentPart.get("content"));
245 parts.add(String.valueOf(contentPart.get("content")));
246 } else {
247 contentNode.getContentParts().add(contentPart.get("content_idx"));
248 }
249 }
250
251 contentNode.setContent(String.join(" ", parts));
252
253 for (Map<String, Object> feature : features) {
254 ContentFeatureature">ContentFeature contentFeature = new ContentFeature();
255
256 String[] featureParts = featureTypeNames.get(feature.get("f_type")).split(":");
257 contentFeature.setFeatureType(featureParts[0]);
258 if (featureParts.length > 1) {
259 contentFeature.setName(featureParts[1]);
260 } else {
261 contentFeature.setName("");
262 }
263 contentFeature.setSingle(Integer.valueOf(1).equals(feature.get("single")));
264 TypeReference<ArrayList<Object>> typeRef
265 = new TypeReference<>() {
266 };
267
268 try {
269 contentFeature.setValue(OBJECT_MAPPER_MSGPACK.readValue((byte[]) feature.get("binary_value"), typeRef));
270 } catch (IOException e) {
271 throw new KodexaException("Unable to unpack value for feature", e);
272 }
273
274 contentNode.getFeatures().add(contentFeature);
275 }
276
277 return contentNode;
278 }
279
280 public byte[] toBytes() {
281 try {
282 flushMetadata();
283 return Files.readAllBytes(Path.of(dbPath));
284 } catch (IOException e) {
285 throw new KodexaException("Unable to read KDDB file from " + dbPath);
286 }
287 }
288
289 private void updateNode(Handle handle, ContentNode contentNode, Integer parentId, boolean includeChildren) {
290 if (contentNode == null)
291 return;
292
293 int nodeTypeId = getNodeTypeId(handle, contentNode.getType());
294
295 if (contentNode.getUuid() == null) {
296 int nodeId = (int) handle.createUpdate(CONTENT_NODE_INSERT).bind(0, parentId).bind(1, nodeTypeId).bind(2, contentNode.getIndex()).executeAndReturnGeneratedKeys("id").mapToMap().first().get("last_insert_rowid()");
297 contentNode.setUuid(String.valueOf(nodeId));
298 } else {
299
300 handle.execute("delete from ft where cn_id=?", contentNode.getUuid());
301 handle.execute("delete from cnp where cn_id=?", contentNode.getUuid());
302 handle.execute("delete from cn where id=?", contentNode.getUuid());
303
304 handle.createUpdate(CONTENT_NODE_INSERT_WITH_ID)
305 .bind(0, contentNode.getUuid())
306 .bind(1, parentId)
307 .bind(2, nodeTypeId)
308 .bind(3, contentNode.getIndex())
309 .execute();
310 }
311
312 if (contentNode.getContentParts() == null || contentNode.getContentParts().isEmpty()) {
313 if (contentNode.getContent() != null) {
314 contentNode.setContentParts(List.of(contentNode.getContent()));
315 }
316 }
317
318 int pos = 0;
319 for (Object contentPart : contentNode.getContentParts()) {
320 if (contentPart instanceof String) {
321 handle.execute(CONTENT_NODE_PART_INSERT, contentNode.getUuid(), pos, contentPart, null);
322 } else {
323 handle.execute(CONTENT_NODE_PART_INSERT, contentNode.getUuid(), pos, null, contentPart);
324 }
325 pos++;
326 }
327
328 for (ContentFeature feature : contentNode.getFeatures()) {
329 writeFeature(handle, feature, contentNode);
330 }
331
332 if (includeChildren) {
333 for (ContentNode child : contentNode.getChildren()) {
334 updateNode(handle, child, Integer.valueOf(contentNode.getUuid()), true);
335 }
336 }
337 }
338
339 private int writeFeature(Handle handle, ContentFeature feature, ContentNode contentNode) {
340 int fTypeId = getFeatureTypeName(handle, feature.getFeatureType() + ":" + feature.getName());
341
342
343 try {
344 byte[] packedValue = OBJECT_MAPPER_MSGPACK.writeValueAsBytes(feature.getValue());
345 Formatter formatter = new Formatter();
346 for (byte b : md.digest(packedValue)) {
347 formatter.format("%02x", b);
348 }
349 String tagUuid = null;
350
351 if ("tag".equals(feature.getFeatureType()) && feature.getValue().size() > 0) {
352 tagUuid = (String) ((Map) feature.getValue().get(0)).get("uuid");
353 }
354
355 return (Integer) handle.createUpdate(FEATURE_INSERT).bind(0, contentNode.getUuid()).bind(1, fTypeId).bind(2, packedValue).bind(3, feature.isSingle()).bind(4, tagUuid).executeAndReturnGeneratedKeys("id").mapToMap().first().get("last_insert_rowid()");
356
357 } catch (JsonProcessingException e) {
358 throw new KodexaException("Unable to pack feature value", e);
359 }
360
361 }
362
363 private int getNodeTypeId(Handle handle, String type) {
364 Optional<Map<String, Object>> nodeType = handle.createQuery("SELECT id, name FROM n_type where name is :nodeType").bind("nodeType", type)
365 .mapToMap()
366 .findFirst();
367
368 return nodeType.map(stringObjectMap -> (int) stringObjectMap.get("id")).orElseGet(() -> (Integer) handle.createUpdate("INSERT INTO n_type(name) VALUES(?)").bind(0, type).executeAndReturnGeneratedKeys("id").mapToMap().first().get("last_insert_rowid()"));
369 }
370
371 private int getFeatureTypeName(Handle handle, String type) {
372
373 if (featureTypeNames != null) {
374 Integer hit = featureTypeNames.entrySet().stream()
375 .filter(e -> e.getValue().equals(type))
376 .map(Map.Entry::getKey)
377 .findFirst()
378 .orElse(null);
379
380 if (hit != null)
381 return hit;
382 }
383
384 Optional<Map<String, Object>> nodeType = handle.createQuery("SELECT id, name FROM f_type where name is :featureType").bind("featureType", type)
385 .mapToMap()
386 .findFirst();
387 Integer newId = nodeType.map(stringObjectMap -> (int) stringObjectMap.get("id")).orElseGet(() -> (Integer) handle.createUpdate("INSERT INTO f_type(name) VALUES(?)").bind(0, type).executeAndReturnGeneratedKeys("id").mapToMap().first().get("last_insert_rowid()"));
388 featureTypeNames =
389 handle.createQuery("SELECT id, name FROM f_type")
390 .mapToMap()
391 .collect(Collectors.toMap(x -> Integer.valueOf(String.valueOf(x.get("id"))), x -> String.valueOf(x.get("name"))));
392
393 return newId;
394 }
395
396 private void flushMetadata() {
397 jdbi.withHandle(handle -> {
398 try {
399 Documentt">Document copyDocument = new Document();
400 copyDocument.setUuid(document.getUuid());
401 copyDocument.setSource(document.getSource());
402 copyDocument.setClasses(document.getClasses());
403 copyDocument.setLabels(document.getLabels());
404 copyDocument.setMetadata(document.getMetadata());
405 copyDocument.setUuid(document.getUuid());
406 copyDocument.setMixins(document.getMixins());
407 copyDocument.setVersion(document.getVersion());
408
409 byte[] metadataBytes = OBJECT_MAPPER_MSGPACK.writeValueAsBytes(copyDocument);
410 handle.execute("INSERT INTO metadata(metadata, id) VALUES(?, ?)\n" +
411 " ON CONFLICT(id) DO UPDATE SET metadata=?", metadataBytes, 1, metadataBytes);
412 } catch (JsonProcessingException e) {
413 throw new KodexaException("Unable to flush metadata to KDDB", e);
414 }
415 return null;
416 });
417 }
418
419 public List<ContentNode> getChildNodes(ContentNode contentNode) {
420 return jdbi.withHandle(handle -> {
421 List<Map<String, Object>> childNodes =
422 handle.createQuery("SELECT id, nt, pid, idx FROM cn where pid is :nodeId").bind("nodeId", contentNode.getUuid())
423 .mapToMap()
424 .list();
425 List<ContentNode> children = new ArrayList<>();
426 for (Map<String, Object> childNode : childNodes) {
427 ContentNode child = buildNode(childNode, handle);
428 child.setParent(contentNode);
429 children.add(child);
430 }
431
432 return children;
433 });
434 }
435
436 public String getAllContentForContentNode(ContentNode contentNode, String separator) {
437 return null;
438 }
439
440 public int getNodeCountByType(String type) {
441 return (int) jdbi.withHandle(handle -> handle.createQuery("select count(1) as num from cn where nt=:nt").bind("nt", nodeTypes.entrySet()
442 .stream()
443 .filter(entry -> type.equals(entry.getValue()))
444 .map(Map.Entry::getKey).findFirst().orElse(-1)).mapToMap().first().get("num"));
445 }
446
447 public ImmutablePair<InputStream, Long> toInputStream() {
448 try {
449 flushMetadata();
450 return new ImmutablePair<>(new FileInputStream(dbPath), Files.size(Path.of(dbPath)));
451 } catch (IOException e) {
452 throw new KodexaException("Unable to read KDDB file from " + dbPath);
453 }
454 }
455
456 public List<ContentNode> getTaggedNodes() {
457
458
459 return jdbi.withHandle(handle -> {
460 List<ContentNode> nodes = new ArrayList<>();
461 List<Map<String, Object>> contentNodesRaw =
462 handle.createQuery("select * from cn where id in (select cn_id from ft where f_type in (select id from f_type where name like 'tag:%'))")
463 .mapToMap()
464 .list();
465 for (Map<String, Object> contentNodeRaw : contentNodesRaw) {
466 nodes.add(buildNode(contentNodeRaw, handle));
467 }
468 return nodes;
469 });
470 }
471
472 public List<ContentNode> getTaggedNodeByTagUuid(String tagUuid) {
473
474 return jdbi.withHandle(handle -> {
475 List<ContentNode> nodes = new ArrayList<>();
476 List<Map<String, Object>> contentNodesRaw =
477 handle.createQuery("select * from cn where id in (select cn_id from ft where tag_uuid = :tagUuid")
478 .bind("tagUuid", tagUuid)
479 .mapToMap()
480 .list();
481 for (Map<String, Object> contentNodeRaw : contentNodesRaw) {
482 nodes.add(buildNode(contentNodeRaw, handle));
483 }
484 return nodes;
485 });
486 }
487
488 public ContentNode getNodeByUuid(String nodeUuid) {
489
490 return jdbi.withHandle(handle -> {
491 Map<String, Object> contentNodeRaw =
492 handle.createQuery("select * from cn where id = :nodeUuid")
493 .bind("nodeUuid", nodeUuid)
494 .mapToMap()
495 .first();
496 return buildNode(contentNodeRaw, handle);
497 });
498 }
499
500 public List<ContentNode> getNodesByType(String nodeType) {
501
502 return jdbi.withHandle(handle -> {
503 List<ContentNode> nodes = new ArrayList<>();
504 List<Map<String, Object>> contentNodesRaw =
505 handle.createQuery("select * from cn where nt = :ntId")
506 .bind("ntId", getNodeTypeId(handle, nodeType))
507 .mapToMap()
508 .list();
509 for (Map<String, Object> contentNodeRaw : contentNodesRaw) {
510 nodes.add(buildNode(contentNodeRaw, handle));
511 }
512 return nodes;
513 });
514 }
515
516 public List<ContentException> getContentExceptions() {
517
518 return jdbi.withHandle(handle -> {
519 List<ContentException> exceptions = new ArrayList<>();
520 List<Map<String, Object>> rawExceptions =
521 handle.createQuery("select * from content_exceptions")
522 .mapToMap()
523 .list();
524 for (Map<String, Object> exception : rawExceptions) {
525 exceptions.add(buildException(exception, handle));
526 }
527 return exceptions;
528 });
529 }
530
531 private ContentException buildException(Map<String, Object> rawException, Handle handle) {
532 ContentExceptionption">ContentException contentException = new ContentException();
533 contentException.setId(String.valueOf(rawException.get("id")));
534 contentException.setMessage((String) rawException.get("message"));
535 contentException.setExceptionDetails((String) rawException.get("exception_details"));
536 contentException.setGroupUuid((String) rawException.get("group_uuid"));
537 contentException.setTagUuid((String) rawException.get("tag_uuid"));
538 contentException.setTag((String) rawException.get("tag"));
539 contentException.setExceptionType((String) rawException.get("exception_type"));
540 contentException.setSeverity((String) rawException.get("severity"));
541 contentException.setNodeUuid((String) rawException.get("node_uuid"));
542 return contentException;
543 }
544
545 public int getNumberOfInsights() {
546 return (int) jdbi.withHandle(handle -> handle.createQuery("select count(1) as num from model_insights").mapToMap().first().get("num"));
547 }
548 }