package org.simantics.spreadsheet.graph; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.ObjectOutputStream; import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import org.simantics.Simantics; import org.simantics.databoard.Bindings; import org.simantics.databoard.binding.mutable.Variant; import org.simantics.databoard.util.binary.RandomAccessBinary; import org.simantics.datatypes.DatatypeResource; import org.simantics.datatypes.literal.Font; import org.simantics.datatypes.literal.RGB; import org.simantics.datatypes.utils.BTree; import org.simantics.db.ReadGraph; import org.simantics.db.Resource; import org.simantics.db.WriteGraph; import org.simantics.db.common.request.ObjectsWithType; import org.simantics.db.common.utils.LiteralFileUtil; import org.simantics.db.exception.DatabaseException; import org.simantics.db.exception.ServiceException; import org.simantics.db.layer0.util.Layer0Utils; import org.simantics.db.layer0.variable.Variable; import org.simantics.db.layer0.variable.Variables; import org.simantics.db.service.ClusteringSupport; import org.simantics.layer0.Layer0; import org.simantics.scl.runtime.tuple.Tuple2; import org.simantics.simulator.toolkit.StandardRealm; import org.simantics.spreadsheet.Range; import org.simantics.spreadsheet.graph.synchronization.SpreadsheetSynchronizationEventHandler; import org.simantics.spreadsheet.resource.SpreadsheetResource; import org.simantics.spreadsheet.util.SpreadsheetUtils; import org.simantics.structural.synchronization.client.Synchronizer; import gnu.trove.iterator.TObjectIntIterator; import gnu.trove.map.hash.TObjectIntHashMap; public class SpreadsheetGraphUtils { public static File extractInitialCondition(ReadGraph graph, Resource ic) throws DatabaseException, IOException { SpreadsheetResource SR = SpreadsheetResource.getInstance(graph); File temp = Simantics.getTempfile("excel","ic"); LiteralFileUtil.copyRandomAccessBinaryToFile(graph, ic, SR.InitialCondition_bytes, temp); if (temp.length() == 0) throw new FileNotFoundException("Snapshot file does not exist.\nThis seems to be a database bug that manifests as total loss of state file data.\nThis error prevents the program from crashing."); return temp; } public static RandomAccessBinary getOrCreateRandomAccessBinary(WriteGraph graph, Resource initialCondition) throws DatabaseException, IOException { SpreadsheetResource SR = SpreadsheetResource.getInstance(graph); // We put snapshot literals in their own clusters for now just to be safe Resource literal = graph.getPossibleObject(initialCondition, SR.InitialCondition_bytes); if (literal != null) { RandomAccessBinary rab = graph.getRandomAccessBinary(literal); rab.position(0); rab.removeBytes(rab.length(), RandomAccessBinary.ByteSide.Right); return rab; } else { Layer0 L0 = Layer0.getInstance(graph); ClusteringSupport cs = graph.getService(ClusteringSupport.class); literal = graph.newResource(cs.createCluster()); graph.claim(literal, L0.InstanceOf, null, L0.ByteArray); graph.claim(initialCondition, SR.InitialCondition_bytes, SR.InitialCondition_bytes_Inverse, literal); return graph.createRandomAccessBinary(literal, Bindings.BYTE_ARRAY.type(), null); } } public static Resource saveInitialCondition(WriteGraph graph, Variable run, Resource container, String name) throws DatabaseException { String sessionName = run.getParent(graph).getURI(graph); Resource bookResource = run.getRepresents(graph); StandardRealm realm = SpreadsheetSessionManager.getInstance().getOrCreateRealm(graph, sessionName); SpreadsheetBook book = realm.getEngine(); try { File temp = Simantics.getTempfile("excel", "ic"); System.err.println("Saving initial condition to " + temp.getAbsolutePath()); FileOutputStream fileOut = new FileOutputStream(temp); ObjectOutputStream out = new ObjectOutputStream(fileOut); out.writeObject(book); out.close(); fileOut.close(); Layer0 L0 = Layer0.getInstance(graph); SpreadsheetResource SR = SpreadsheetResource.getInstance(graph); Resource ic = graph.newResource(); graph.claim(ic, L0.InstanceOf, SR.InitialCondition); graph.addLiteral(ic, L0.HasName, L0.NameOf, L0.String, name, Bindings.STRING); RandomAccessBinary rab = getOrCreateRandomAccessBinary(graph, ic); LiteralFileUtil.copyRandomAccessBinaryFromFile(temp, rab); graph.claim(container, L0.ConsistsOf, L0.PartOf, ic); graph.deny(bookResource, SR.HasInitialCondition); graph.claim(bookResource, SR.HasInitialCondition, ic); graph.claim(ic, SR.InitialCondition_ConditionOf, bookResource); setDefaultInitialConditionForBook(graph, bookResource, ic); return ic; } catch (IOException e) { throw new DatabaseException(e); } } public static void setDefaultInitialConditionForBook(WriteGraph graph, Resource book, Resource ic) throws ServiceException { SpreadsheetResource SR = SpreadsheetResource.getInstance(graph); graph.deny(book, SR.Book_HasDefaultInitialCondition); graph.claim(ic, SR.InitialCondition_DefaultConditionOf, book); } public static void evaluateAll(ReadGraph graph, Variable run) throws DatabaseException { String sessionName = run.getParent(graph).getURI(graph); StandardRealm realm = SpreadsheetSessionManager.getInstance().getOrCreateRealm(graph, sessionName); SpreadsheetBook book = realm.getEngine(); book.accept(new EvaluateAll(book)); } public static void invalidateAll(ReadGraph graph, Variable run) throws DatabaseException { String sessionName = run.getParent(graph).getURI(graph); StandardRealm realm = SpreadsheetSessionManager.getInstance().getOrCreateRealm(graph, sessionName); SpreadsheetBook book = realm.getEngine(); book.accept(new InvalidateAll()); realm.getNodeManager().refreshVariables(); } public static boolean fullSynchronization(ReadGraph graph, Variable run) throws DatabaseException { return partialSynchronization(graph, run, null); } public static boolean partialSynchronization(ReadGraph graph, Variable run, TObjectIntHashMap changeFlags) throws DatabaseException { Synchronizer synchronizer = new Synchronizer(graph); String sessionName = run.getParent(graph).getURI(graph); Resource bookResource = run.getRepresents(graph); Variable configuration = Variables.getVariable(graph, bookResource); StandardRealm realm = SpreadsheetSessionManager.getInstance().getOrCreateRealm(graph, sessionName); SpreadsheetBook book = realm.getEngine(); SpreadsheetSynchronizationEventHandler handler = new SpreadsheetSynchronizationEventHandler(graph, book); // System.err.println("sessionName : " + sessionName); // System.err.println("bookResource : " + graph.getURI(bookResource)); // System.err.println("configuration : " + configuration.getURI(graph)); // System.err.println("realm : " + realm); // System.err.println("book : " + book); if (changeFlags == null) { synchronizer.fullSynchronization(configuration, handler); } else { TObjectIntIterator iter = changeFlags.iterator(); iter.advance(); Variable row = iter.key(); Variable rowParent = row.getParent(graph); while (!rowParent.equals(configuration)) { changeFlags.put(rowParent, 1); rowParent = rowParent.getParent(graph); } changeFlags.put(configuration, 1); synchronizer.partialSynchronization(configuration, handler, changeFlags); } // book.accept(new InvalidateAll()); // realm.getNodeManager().refreshVariables(); // mapping.currentRevision = synchronizer.getHeadRevisionId(); // mapping.setTrustUids(true); // Clean up queries // QueryControl qc = g.getService(QueryControl.class); // qc.flush(g); // TimeLogger.log("Finished full synchronization"); realm.getNodeManager().fireNodeListeners(); return handler.getDidChanges(); } public static Variable findCell(ReadGraph graph, Variable run, String reference) throws DatabaseException { int pos = reference.indexOf("!"); String sheetName = reference.substring(0, pos); String cellName = reference.substring(pos+1); String sessionName = run.getParent(graph).getURI(graph); StandardRealm realm = SpreadsheetSessionManager.getInstance().getOrCreateRealm(graph, sessionName); SpreadsheetBook book = realm.getEngine(); SpreadsheetEngine engine = book.getEngine(sheetName); if(engine == null) return null; Range r = SpreadsheetUtils.decodeCellAbsolute(cellName); SpreadsheetLine line = engine.getLine(r.startRow); if(line == null) return null; String path = line.getPath(); if(path == null) return null; Variable lineVariable = run.browse(graph, path); if(lineVariable==null) return null; return lineVariable.getChild(graph, cellName); } public static boolean asBoolean(Object object) { if(object instanceof Boolean) return (Boolean)object; else if(object instanceof Number) return ((Number)object).doubleValue() != 0; else if(object instanceof Variant) return asBoolean(((Variant)object).getValue()); else if(object instanceof String) { Double d = asDoubleWhereEmptyStringIsZero((String)object); if(d==null) return false; else return d != 0; } return false; } public static String asString(Object object) { if(object == null) return ""; if(object instanceof String) return (String)object; if(object instanceof Number) { double dVal = ((Number)object).doubleValue(); if(dVal == Math.floor(dVal)){ return ""+((Number)object).intValue(); } else { return object.toString(); } } else if(object instanceof Variant) { Object o = ((Variant) object).getValue(); if(o instanceof String) return (String)o; else if(o instanceof Number) asString((Number)o); else return o.toString(); } return object.toString(); } public static Double asDoubleWhereEmptyStringIsZero(Object object){ if(object instanceof Number) return ((Number)object).doubleValue(); else if(object instanceof String) { try { if(((String)object).isEmpty()) return 0.0; return Double.parseDouble((String)object); } catch (NumberFormatException e) { return null; } } else if(object instanceof Variant) { Object o = ((Variant) object).getValue(); return asDoubleWhereEmptyStringIsZero(o); } else if (SpreadsheetCell.EMPTY == object) { return null; } return null; } public static double asNumber(Object object) { if(object instanceof Number) { return ((Number)object).doubleValue(); } else if(object instanceof String) { try { String str = (String)object; if(str.isEmpty()) return 0; return Double.parseDouble((String)object); } catch (NumberFormatException e) { return 0; } } else if(object instanceof Variant) { Object o = ((Variant) object).getValue(); return asNumber(o); } else if (SpreadsheetCell.EMPTY == object) { return 0.0; } return 0.0; } public static Number asValidNumber(Object object) { if(object instanceof Number) { return (Number)object; } else if(object instanceof String) { try { return Double.parseDouble((String)object); } catch (NumberFormatException e) { return null; } } else if(object instanceof Variant) { Object o = ((Variant) object).getValue(); return asNumber(o); } else if (SpreadsheetCell.EMPTY == object) { return null; } return null; } public static boolean matchCriteria(Object value, Object criteria) { if(value==null || criteria==null) return false; if(value instanceof Variant){ Double dVal = asDoubleWhereEmptyStringIsZero(value); if(dVal==null) value = ((Variant)value).getValue(); else value = dVal; } if(criteria instanceof Variant){ Double dVal = asDoubleWhereEmptyStringIsZero(criteria); if(dVal==null) criteria = ((Variant)criteria).getValue(); else criteria = dVal; } if(criteria instanceof Number && value instanceof Number) { Number nc = (asNumber(criteria)); Number nv = (asNumber(value)); return nc.equals(nv); } if(criteria instanceof String){ boolean nums = false; Object valueObj = null; if(value instanceof Number){ valueObj = ((Number)value).doubleValue(); nums = true; } else valueObj = value.toString(); String sc = criteria.toString(); if(sc.length() >= 3){ String oper = sc.substring(0, 2); String criteriaStr = sc.substring(2); Double criteriaNum = null; try { criteriaNum = Double.parseDouble(criteriaStr); if(oper.equals("<>")){ if(!nums) return true; } else if(!nums) return false; nums = true; } catch (NumberFormatException e){ if(oper.equals("<>")){ if(nums) return true; } else if(nums) return false; nums = false; } if(oper.equals(">=")){ if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) >= 0 ; else return ((Number)valueObj).doubleValue() >= criteriaNum; } else if(oper.equals("<=")){ if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) <= 0 ; else return ((Number)valueObj).doubleValue() <= criteriaNum; } else if(oper.equals("<>")){ if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) != 0 ; else return ((Number)valueObj).doubleValue() != criteriaNum; } } if(sc.length() >= 2){ String oper = sc.substring(0, 1); String criteriaStr = sc.substring(1); Double criteriaNum = null; try { criteriaNum = Double.parseDouble(criteriaStr); if(!nums) return false; nums = true; } catch (NumberFormatException e){ if(nums) return false; nums = false; } if(oper.equals("<")){ if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) < 0; else return ((Number)valueObj).doubleValue() < criteriaNum; } else if(oper.equals(">")){ if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) > 0; else return ((Number)valueObj).doubleValue() > criteriaNum; } else if(oper.equals("=")){ if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) == 0; else return ((Number)valueObj).doubleValue() == criteriaNum; } } return sc.equals(valueObj); } else if (criteria instanceof Number){ return false; } throw new IllegalStateException(); } public static boolean excelEquals(Object left, Object right) { if(left instanceof String) { if(right instanceof String) { return ((String) left).toLowerCase().equals(((String) right).toLowerCase()); } } return left.equals(right); } public static List possibleConfigurationCellVariables(ReadGraph graph, Variable sheet, Range range) throws DatabaseException { List rowVariables = possibleConfigurationLineVariables(graph, sheet, range); List result = new ArrayList<>(); for (Variable variable : rowVariables) { Collection children = variable.getChildren(graph); for (Variable child : children) { if (variableInRange(graph, child, range)) { result.add(child); } } } return result; } public static Map possibleConfigurationLineResources(ReadGraph graph, Variable sheet, Range range) throws DatabaseException { Variable lines = sheet.getPossibleChild(graph, "Lines"); if (lines == null) throw new DatabaseException("Invalid input variable " + sheet.getURI(graph)); Resource linesR = lines.getRepresents(graph); BTree bt = new BTree(graph, linesR); List tuples = bt.searchRangeBTree(graph, Variant.ofInstance(range.startRow), Variant.ofInstance(range.endRow)); Map result = new HashMap<>(tuples.size()); for (Tuple2 tuple : tuples) { Integer lineNumber = (Integer)((Variant)tuple.c0).getValue(); Resource resource = (Resource)tuple.c1; result.put(lineNumber, resource); } return result; } public static List possibleConfigurationLineVariables(ReadGraph graph, Variable sheet, Range range) throws DatabaseException { Map rows = possibleConfigurationLineResources(graph, sheet, range); List result = new ArrayList<>(rows.size()); for (Resource row: rows.values()) { Variable lineVar = Variables.getPossibleVariable(graph, row); if (lineVar != null) result.add(lineVar); } return result; } public static List possibleRunLineVariables(ReadGraph graph, Variable sheetRun, Range range) throws DatabaseException { Variable run = sheetRun.getParent(graph); String sheetName = sheetRun.getName(graph); String sessionName = run.getParent(graph).getURI(graph); StandardRealm realm = SpreadsheetSessionManager.getInstance().getOrCreateRealm(graph, sessionName); SpreadsheetBook book = realm.getEngine(); SpreadsheetEngine engine = book.getEngine(sheetName); if(engine == null) return null; List result = new ArrayList<>(); int end = range.endRow < engine.lines.getMaxRow() ? range.endRow : engine.lines.getMaxRow(); for (int i = range.startRow; i <= end; i++) { SpreadsheetLine line = engine.getLine(i); if(line == null) continue; String path = line.getPath(); path = line.getPath(); if(path == null) continue; Variable lineVariable = run.browse(graph, path); if(lineVariable==null) continue; result.add(lineVariable); } return result; } public static List possibleRunCellVariables(ReadGraph graph, Variable sheetRun, Range range) throws DatabaseException { List runLineVariable = possibleRunLineVariables(graph, sheetRun, range); List result = new ArrayList<>(); for (Variable variable : runLineVariable) { // System.out.println("line: " + variable.getURI(graph)); for (Variable child : variable.getChildren(graph)) { // System.out.print("cell : " + child.getURI(graph)); if (variableInRange(graph, child, range)) { result.add(child); } } } return result; } private static boolean variableInRange(ReadGraph graph, Variable child, Range range) throws DatabaseException { String name = child.getName(graph); Range childRange = SpreadsheetUtils.decodeCellAbsolute(name); // System.out.print(" and range " + childRange); if (childRange != null && range.contains(childRange)) { // System.out.println(" => range.contains(childRange) = true"); return true; } // System.out.println(); return false; } public static Map createConfigurationLineResources(WriteGraph graph, Variable sheet, Range range) throws DatabaseException { Layer0 L0 = Layer0.getInstance(graph); SpreadsheetResource SHEET = SpreadsheetResource.getInstance(graph); Variable lines = sheet.getPossibleChild(graph, "Lines"); if (lines == null) throw new DatabaseException("Invalid input variable " + sheet.getURI(graph)); Resource linesR = lines.getRepresents(graph); BTree bt = new BTree(graph, linesR); Map result = new HashMap<>(); for (int lineNumber = range.startRow; lineNumber <= range.endRow; lineNumber++) { Resource line = graph.newResource(); graph.claim(line, L0.InstanceOf, null, SHEET.Line); graph.claimLiteral(line, L0.HasName, L0.NameOf, L0.String, "Row" + lineNumber, Bindings.STRING); bt.insertBTree(graph, Variant.ofInstance(lineNumber), line); result.put(lineNumber, line); } return result; } public static List getOrCreateConfigurationCellVariables(WriteGraph graph, Variable sheet, Range range) throws DatabaseException { List rows = possibleConfigurationLineVariables(graph, sheet, range); if (rows.isEmpty()) { createConfigurationLineResources(graph, sheet, range); rows = possibleConfigurationLineVariables(graph, sheet, range); } List cells = possibleConfigurationCellVariables(graph, sheet, range); if (cells.isEmpty()) { Iterator rowIterator = rows.iterator(); for (int rowNumber = range.startRow; rowNumber <= range.endRow; rowNumber++) { Variable row = rowIterator.next(); for (int colNumber = range.startColumn; colNumber <= range.endColumn; colNumber++) { String location = SpreadsheetUtils.cellName(rowNumber, colNumber); defaultCreateCell(graph, row, location, new Variant(Bindings.STRING, "")); } } } cells = possibleConfigurationCellVariables(graph, sheet, range); if(cells.isEmpty()) throw new DatabaseException("Unexpected problem while creating spreadsheet cell at '" + range + "'"); return cells; } private static void defaultCreateCell(WriteGraph graph, Variable parent, String location, Variant value) throws DatabaseException { Layer0 L0 = Layer0.getInstance(graph); SpreadsheetResource SHEET = SpreadsheetResource.getInstance(graph); Resource container = parent.getRepresents(graph); Resource cell = graph.newResource(); graph.claim(cell, L0.InstanceOf, null, SHEET.TextCell); graph.addLiteral(cell, L0.HasName, L0.NameOf, L0.String, location, Bindings.STRING); graph.addLiteral(cell, SHEET.Cell_content, SHEET.Cell_content_Inverse, L0.Variant, value, Bindings.VARIANT); graph.claim(cell, L0.PartOf, container); Resource book = Variables.getContext(graph, parent).getRepresents(graph); Collection objects = graph.sync(new ObjectsWithType(book, L0.ConsistsOf, SHEET.Style)); int styleId = SpreadsheetStyle.empty().getStyleId(); Resource style = null; for (Resource possibleStyle : objects) { int possibleStyleId = graph.getRelatedValue2(possibleStyle, SHEET.Style_id, Bindings.INTEGER); if (possibleStyleId == styleId) { style = possibleStyle; break; } } if (style == null) { style = graph.newResource(); graph.claim(style, L0.InstanceOf, null, SHEET.Style); graph.claim(style, L0.PartOf, book); int id = objects.size(); graph.claimLiteral(style, L0.HasName, "Style_" + id); graph.claimLiteral(style, SHEET.Style_id, styleId, Bindings.INTEGER); } graph.claim(cell, SHEET.Cell_HasStyle, style); Layer0Utils.addCommentMetadata(graph, "Created cell on location " + location + " with value " + value.toString()); } public static Resource createStyle(WriteGraph graph, Resource book, SpreadsheetStyle sstyle) throws DatabaseException { Layer0 L0 = Layer0.getInstance(graph); SpreadsheetResource SR = SpreadsheetResource.getInstance(graph); Resource style = graph.newResource(); graph.claim(style, L0.InstanceOf, null, SR.Style); graph.claim(style, L0.PartOf, book); int styleId = sstyle.getStyleId(); String styleName = sstyle.name; graph.claimLiteral(style, L0.HasName, styleName); //System.err.println("CREATING STYLE " + styleName + " WITH ID: " + styleId); graph.claimLiteral(style, SR.Style_id, styleId, Bindings.INTEGER); DatatypeResource DATATYPES = DatatypeResource.getInstance(graph); if (sstyle.foreground != null) graph.claimLiteral(style, SR.Cell_foreground, DATATYPES.RGB_Integer, sstyle.foreground, RGB.Integer.BINDING); if (sstyle.background != null) graph.claimLiteral(style, SR.Cell_background, DATATYPES.RGB_Integer, sstyle.background, RGB.Integer.BINDING); if (sstyle.align != -1) graph.claimLiteral(style, SR.Cell_align, sstyle.align, Bindings.INTEGER); if (sstyle.font != null) graph.claimLiteral(style, SR.Cell_font, DATATYPES.Font, sstyle.font, Font.BINDING); if (sstyle.border != -1) graph.claimLiteral(style, SR.Cell_border, sstyle.border); if (sstyle.formatString != null && !sstyle.formatString.isEmpty()) graph.claimLiteral(style, SR.Cell_formatString, sstyle.formatString, Bindings.STRING); if (sstyle.formatIndex != -1) graph.claimLiteral(style, SR.Cell_formatIndex, sstyle.formatIndex, Bindings.INTEGER); return style; } public static Resource createBook(WriteGraph graph, Resource parent, String name) throws DatabaseException { Layer0 L0 = Layer0.getInstance(graph); SpreadsheetResource SR = SpreadsheetResource.getInstance(graph); Resource book = graph.newResource(); graph.claim(book, L0.InstanceOf, SR.Book); graph.claimLiteral(book, L0.HasName, L0.NameOf, L0.String, name, Bindings.STRING); graph.claim(parent, L0.ConsistsOf, book); return book; } public static Variable constructAndInitializeRunVariable(WriteGraph graph, Resource root) throws DatabaseException { Variable run = SpreadsheetUtils.getBookVariable(graph, root); SpreadsheetGraphUtils.fullSynchronization(graph, run); SpreadsheetGraphUtils.evaluateAll(graph, run); SpreadsheetGraphUtils.saveInitialCondition(graph, run, root, "Initial"); return run; } }