Search in sources :

Example 6 with DatabaseItemTree

use of com.kyj.fx.voeditor.visual.component.sql.dbtree.commons.DatabaseItemTree in project Gargoyle by callakrsos.

the class H2TableItemTree method applyChildren.

//	private static final int GETPRIMARY_KEY_SEQ = 5;
//	private static final int GETPRIMARY_PRIMARYKEY_NAME = 6;
@Override
public ObservableList<TreeItem<DatabaseItemTree<String>>> applyChildren(Connection con, String... args) throws Exception {
    DatabaseMetaData metaData = con.getMetaData();
    ResultSet tables = metaData.getColumns(null, args[0], args[1], "%");
    Set<String> primaryKeySet = toSet(metaData.getPrimaryKeys(null, args[0], args[1]), COLUMN_NAME);
    ObservableList<TreeItem<DatabaseItemTree<String>>> observableArrayList = FXCollections.observableArrayList();
    while (tables.next()) {
        /* 
			 * references 
			 * http://docs.oracle.com/javase/6/docs/api/java/sql/ DatabaseMetaData.html#getTables%28java.lang.String,%20java.lang. String,%20java.lang.String,%20java.lang.String%5b%5d%29 
			 */
        String columnName = tables.getString(COLUMN_NAME);
        SqliteColumnItemTree coumnItem = new SqliteColumnItemTree(this, columnName);
        coumnItem.setPrimaryKey(primaryKeySet.contains(columnName));
        TreeItem<DatabaseItemTree<String>> treeItem = new TreeItem<>(coumnItem);
        observableArrayList.add(treeItem);
    }
    return observableArrayList;
}
Also used : TreeItem(javafx.scene.control.TreeItem) ResultSet(java.sql.ResultSet) SqliteColumnItemTree(com.kyj.fx.voeditor.visual.component.sql.dbtree.sqlite.SqliteColumnItemTree) DatabaseItemTree(com.kyj.fx.voeditor.visual.component.sql.dbtree.commons.DatabaseItemTree) DatabaseMetaData(java.sql.DatabaseMetaData)

Example 7 with DatabaseItemTree

use of com.kyj.fx.voeditor.visual.component.sql.dbtree.commons.DatabaseItemTree in project Gargoyle by callakrsos.

the class PostgreSqlPane method menuExportMergeScriptOnAction.

/* (non-Javadoc)
	 * @see com.kyj.fx.voeditor.visual.component.sql.view.SqlPane#menuExportMergeScriptOnAction(javafx.event.ActionEvent)
	 */
@Override
public void menuExportMergeScriptOnAction(ActionEvent e) {
    //		TableView<Map<String, Object>> tbResult = getTbResult();
    List<Map<String, Object>> items = getSelectedTabResultItems();
    //		TreeView<DatabaseItemTree<String>> schemaTree = getSchemaTree();
    //
    //		List<String> schemaList = schemaTree.getRoot().getChildren().stream().map(v -> v.getValue().getName()).collect(Collectors.toList());
    //
    String defaultSchema = "";
    try {
        Map<String, Object> findOne = DbUtil.findOne(connectionSupplier.get(), "select current_schema() as currentschema");
        if (findOne != null && !findOne.isEmpty()) {
            defaultSchema = ValueUtil.decode(findOne.get("currentschema"), "").toString();
        }
    } catch (Exception e4) {
        e4.printStackTrace();
    }
    //
    final String _defaultSchema = defaultSchema;
    //
    //		if (items.isEmpty())
    //			return;
    //
    //		// TODO :: DBMS에 따라 Merge문 생성 로직 분기 처리 필요.
    //
    //		Optional<Pair<String, String[]>> showInputDialog = DialogUtil.showInputCustomDialog(tbResult.getScene().getWindow(), "table Name",
    //				"테이블명을 입력하세요.", new CustomInputDialogAction<GridPane, String[]>() {
    //
    //					TextField txtSchema;
    //					TextField txtTable;
    //
    //					@Override
    //					public GridPane getNode() {
    //						GridPane gridPane = new GridPane();
    //						txtSchema = new TextField();
    //						txtTable = new TextField();
    //
    //						FxUtil.installAutoTextFieldBinding(txtSchema, () -> {
    //							return schemaList;
    //						});
    //
    //						FxUtil.installAutoTextFieldBinding(txtTable, () -> {
    //							return searchPattern(txtSchema.getText(), txtTable.getText()).stream().map(v -> v.getValue().getName())
    //									.collect(Collectors.toList());
    //						});
    //						txtSchema.setText(_defaultSchema);
    //
    //						//Default TableName
    //						TreeItem<DatabaseItemTree<String>> selectedItem = getSchemaTree().getSelectionModel().getSelectedItem();
    //						if (null != selectedItem) {
    //							DatabaseItemTree<String> value = selectedItem.getValue();
    //							if (value instanceof TableItemTree) {
    //								txtTable.setText(value.getName());
    //							}
    //						}
    //
    //						Label label = new Label("Schema : ");
    //						Label label2 = new Label("Table : ");
    //						gridPane.add(label, 0, 0);
    //						gridPane.add(label2, 1, 0);
    //						gridPane.add(txtSchema, 0, 1);
    //						gridPane.add(txtTable, 1, 1);
    //						return gridPane;
    //					}
    //
    //					@Override
    //					public String[] okClickValue() {
    //
    //						String schema = txtSchema.getText().trim();
    //						String table = txtTable.getText().trim();
    //
    //						String[] okValue = new String[2];
    //						okValue[0] = schema;
    //						okValue[1] = table;
    //						return okValue;
    //					}
    //
    //					@Override
    //					public String[] cancelClickValue() {
    //						return null;
    //					}
    //
    //				});
    Optional<Pair<String, String[]>> showTableInputDialog = showTableInputDialog(v -> v.getName());
    showTableInputDialog.ifPresent(op -> {
        if (!"OK".equals(op.getKey()))
            return;
        String[] resultValue = op.getValue();
        try {
            StringBuilder clip = new StringBuilder();
            PostgreTableItemTree dirtyTreeItem = new PostgreTableItemTree();
            String schemaName = (resultValue[0] == null || resultValue[0].isEmpty()) ? _defaultSchema : resultValue[0];
            String tableName = resultValue[1];
            String childrenSQL = dirtyTreeItem.getChildrenSQL(schemaName, tableName);
            List<Map<String, Object>> select = DbUtil.select(childrenSQL);
            List<String> pkList = new ArrayList<>();
            List<String> notPkList = new ArrayList<>();
            List<String> columnList = new ArrayList<>();
            for (Map<String, Object> map : select) {
                String columnName = map.get("column_name").toString();
                Object primaryKeyYn = map.get("primary_yn");
                columnList.add(columnName);
                if ("Y".equals(primaryKeyYn)) {
                    pkList.add(columnName);
                } else {
                    notPkList.add(columnName);
                }
            }
            String mergePreffix = String.format("with upsert as ( update %s set  ", tableName);
            String mergeMiddle = " returning * )\n";
            String collect = columnList.stream().collect(Collectors.joining(", ", "(", ")"));
            String insertPreffix = String.format("insert into %s", tableName);
            String insertend = " where not exists (select * from upsert);\n";
            List<String> valueList = items.stream().map(v -> {
                return ValueUtil.toJSONObject(v);
            }).map(v -> {
                String updateSetSql = notPkList.stream().map(str -> {
                    if (str == null)
                        return null;
                    else {
                        JsonElement jsonElement = v.get(str);
                        if (jsonElement == null) {
                            return str.concat("=").concat("null");
                        } else {
                            String dataValue = jsonElement.toString();
                            dataValue = dataValue.substring(1, dataValue.length() - 1);
                            if (dataValue.indexOf("'") >= 0) {
                                dataValue = StringUtils.replace(dataValue, "'", "''");
                            }
                            return str.concat("=").concat("'").concat(dataValue).concat("'");
                        }
                    }
                }).collect(Collectors.joining(", "));
                String updateWhereSql = pkList.stream().map(str -> {
                    if (str == null)
                        return null;
                    else {
                        if (null == v.get(str))
                            return null;
                        String dataValue = v.get(str).toString();
                        dataValue = dataValue.substring(1, dataValue.length() - 1);
                        if (dataValue.indexOf("'") >= 0) {
                            try {
                                dataValue = StringUtils.replace(dataValue, "'", "''");
                            } catch (Exception e1) {
                                e1.printStackTrace();
                            }
                        }
                        return str.concat(" = ").concat("'").concat(dataValue).concat("'");
                    }
                }).filter(str -> str != null).collect(Collectors.joining(" and "));
                String insertValueSql = columnList.stream().map(str -> {
                    if (str == null)
                        return null;
                    else {
                        JsonElement jsonElement = v.get(str);
                        if (jsonElement == null) {
                            return "null";
                        } else {
                            String dataValue = jsonElement.toString();
                            dataValue = dataValue.substring(1, dataValue.length() - 1);
                            if (dataValue.indexOf("'") >= 0) {
                                dataValue = StringUtils.replace(dataValue, "'", "''");
                            }
                            return "'".concat(dataValue).concat("'");
                        }
                    }
                }).collect(Collectors.joining(", "));
                return new StringBuilder().append(mergePreffix).append(updateSetSql).append(" where ").append(updateWhereSql).append(mergeMiddle).append(insertPreffix).append(collect).append(" select ").append(insertValueSql).append(insertend).toString();
            }).collect(Collectors.toList());
            valueList.forEach(str -> {
                clip.append(str);
            });
            SqlKeywords parent = new SqlKeywords();
            {
                MenuBar menuBar = new MenuBar();
                Menu menuFile = new Menu("File");
                MenuItem miSaveAs = new MenuItem("Save As");
                miSaveAs.setOnAction(ev -> {
                    File saveAsFile = DialogUtil.showFileSaveCheckDialog(getScene().getWindow(), chooser -> {
                        chooser.getExtensionFilters().add(new ExtensionFilter(GargoyleExtensionFilters.SQL_NAME, GargoyleExtensionFilters.SQL));
                    });
                    if (saveAsFile != null) {
                        ThreadUtil.createNewThreadAndRun("Saveas", () -> {
                            SaveSQLFileFunction function = new SaveSQLFileFunction();
                            function.apply(saveAsFile, parent.getText());
                        });
                    }
                });
                menuFile.getItems().add(miSaveAs);
                menuBar.getMenus().add(menuFile);
                parent.setTop(menuBar);
            }
            parent.setContent(clip.toString());
            parent.setWrapText(false);
            parent.setPrefSize(1200d, 800d);
            FxUtil.createStageAndShow(parent, stage -> {
                stage.initOwner(getScene().getWindow());
                stage.setTitle(String.format("[Merge Script] Table : %s", tableName));
            });
        } catch (Exception e2) {
            LOGGER.error(ValueUtil.toString(e2));
            DialogUtil.showExceptionDailog(e2, "에러발생, 테이블을 잘못 선택하셨을 수 있습니다.");
        }
    });
}
Also used : StringUtils(org.apache.commons.lang.StringUtils) Connection(java.sql.Connection) DbUtil(com.kyj.fx.voeditor.visual.util.DbUtil) TreeItem(javafx.scene.control.TreeItem) LoggerFactory(org.slf4j.LoggerFactory) DialogUtil(com.kyj.fx.voeditor.visual.util.DialogUtil) Supplier(java.util.function.Supplier) ArrayList(java.util.ArrayList) JsonElement(com.google.gson.JsonElement) ThreadUtil(com.kyj.fx.voeditor.visual.util.ThreadUtil) Map(java.util.Map) GargoyleExtensionFilters(com.kyj.fx.voeditor.visual.util.GargoyleExtensionFilters) SaveSQLFileFunction(com.kyj.fx.voeditor.visual.component.sql.functions.SaveSQLFileFunction) SqlKeywords(com.kyj.fx.voeditor.visual.component.text.SqlKeywords) Pair(javafx.util.Pair) Logger(org.slf4j.Logger) MenuBar(javafx.scene.control.MenuBar) MenuItem(javafx.scene.control.MenuItem) DatabaseTreeNode(com.kyj.fx.voeditor.visual.component.sql.dbtree.DatabaseTreeNode) ValueUtil(com.kyj.fx.voeditor.visual.util.ValueUtil) Collectors(java.util.stream.Collectors) File(java.io.File) PostgreDatabaseItemTree(com.kyj.fx.voeditor.visual.component.sql.dbtree.postgre.PostgreDatabaseItemTree) Menu(javafx.scene.control.Menu) FxUtil(com.kyj.fx.voeditor.visual.util.FxUtil) List(java.util.List) ResourceLoader(com.kyj.fx.voeditor.visual.momory.ResourceLoader) ActionEvent(javafx.event.ActionEvent) DatabaseItemTree(com.kyj.fx.voeditor.visual.component.sql.dbtree.commons.DatabaseItemTree) PostgreTableItemTree(com.kyj.fx.voeditor.visual.component.sql.dbtree.postgre.PostgreTableItemTree) Optional(java.util.Optional) ExtensionFilter(javafx.stage.FileChooser.ExtensionFilter) SqlKeywords(com.kyj.fx.voeditor.visual.component.text.SqlKeywords) ArrayList(java.util.ArrayList) MenuBar(javafx.scene.control.MenuBar) MenuItem(javafx.scene.control.MenuItem) JsonElement(com.google.gson.JsonElement) ExtensionFilter(javafx.stage.FileChooser.ExtensionFilter) Menu(javafx.scene.control.Menu) SaveSQLFileFunction(com.kyj.fx.voeditor.visual.component.sql.functions.SaveSQLFileFunction) Map(java.util.Map) File(java.io.File) Pair(javafx.util.Pair) PostgreTableItemTree(com.kyj.fx.voeditor.visual.component.sql.dbtree.postgre.PostgreTableItemTree)

Example 8 with DatabaseItemTree

use of com.kyj.fx.voeditor.visual.component.sql.dbtree.commons.DatabaseItemTree in project Gargoyle by callakrsos.

the class SqliteTableItemTree method applyChildren.

/**
	 * 커넥션으로부터 스키마 정보 출력
	 */
@Override
public ObservableList<TreeItem<DatabaseItemTree<String>>> applyChildren(Connection con, String... args) throws Exception {
    DatabaseMetaData metaData = con.getMetaData();
    ResultSet tables = metaData.getColumns(null, null, args[1], "%");
    Set<String> primaryKeySet = toSet(metaData.getPrimaryKeys(null, null, args[1]), COLUMN_NAME);
    ObservableList<TreeItem<DatabaseItemTree<String>>> observableArrayList = FXCollections.observableArrayList();
    while (tables.next()) {
        /* 
			 * references 
			 * http://docs.oracle.com/javase/6/docs/api/java/sql/ DatabaseMetaData.html#getTables%28java.lang.String,%20java.lang. String,%20java.lang.String,%20java.lang.String%5b%5d%29 
			 */
        String columnName = tables.getString(COLUMN_NAME);
        SqliteColumnItemTree coumnItem = new SqliteColumnItemTree(this, columnName);
        coumnItem.setPrimaryKey(primaryKeySet.contains(columnName));
        TreeItem<DatabaseItemTree<String>> treeItem = new TreeItem<>(coumnItem);
        observableArrayList.add(treeItem);
    }
    return observableArrayList;
}
Also used : TreeItem(javafx.scene.control.TreeItem) ResultSet(java.sql.ResultSet) DatabaseItemTree(com.kyj.fx.voeditor.visual.component.sql.dbtree.commons.DatabaseItemTree) DatabaseMetaData(java.sql.DatabaseMetaData)

Example 9 with DatabaseItemTree

use of com.kyj.fx.voeditor.visual.component.sql.dbtree.commons.DatabaseItemTree in project Gargoyle by callakrsos.

the class H2Pane method apply.

@Override
public TreeItem<DatabaseItemTree<String>> apply(String t, Supplier<Connection> conSupplier) {
    try {
        DatabaseItemTree<String> databaseItemTree = new H2DatabaseItemTree("databases", conSupplier);
        TreeItem<DatabaseItemTree<String>> createNode = new DatabaseTreeNode().createNode(databaseItemTree);
        return createNode;
    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}
Also used : H2DatabaseItemTree(com.kyj.fx.voeditor.visual.component.sql.dbtree.H2.H2DatabaseItemTree) H2DatabaseItemTree(com.kyj.fx.voeditor.visual.component.sql.dbtree.H2.H2DatabaseItemTree) DatabaseItemTree(com.kyj.fx.voeditor.visual.component.sql.dbtree.commons.DatabaseItemTree) DatabaseTreeNode(com.kyj.fx.voeditor.visual.component.sql.dbtree.DatabaseTreeNode)

Example 10 with DatabaseItemTree

use of com.kyj.fx.voeditor.visual.component.sql.dbtree.commons.DatabaseItemTree in project Gargoyle by callakrsos.

the class DatabaseTreeNode method createNode.

/**
	 * 파일 트리를 생성하기 위한 노드를 반환한다.
	 *
	 * @Date 2015. 10. 14.
	 * @param f
	 * @return
	 * @User KYJ
	 */
public TreeItem<DatabaseItemTree<String>> createNode(final DatabaseItemTree<String> f) {
    TreeItem<DatabaseItemTree<String>> treeItem = new TreeItem<DatabaseItemTree<String>>(f) {

        private boolean isLeaf;

        private boolean isFirstTimeChildren = true;

        private boolean isFirstTimeLeaf = true;

        @Override
        public ObservableList<TreeItem<DatabaseItemTree<String>>> getChildren() {
            if (isFirstTimeChildren) {
                isFirstTimeChildren = false;
                super.getChildren().setAll(buildChildren(this));
            }
            return super.getChildren();
        }

        @Override
        public boolean isLeaf() {
            if (isFirstTimeLeaf) {
                isFirstTimeLeaf = false;
                DatabaseItemTree<String> f = getValue();
                isLeaf = (f instanceof ColumnItemTree);
            }
            return isLeaf;
        }

        private ObservableList<TreeItem<DatabaseItemTree<String>>> buildChildren(TreeItem<DatabaseItemTree<String>> treeItem) {
            DatabaseItemTree<String> f = treeItem.getValue();
            // if (treeItem.getChildren().isEmpty()) {
            if (f == null) {
                return FXCollections.emptyObservableList();
            } else {
                try {
                    f.read();
                } catch (GargoyleConnectionFailException e) {
                    LOGGER.error(ValueUtil.toString(e));
                    DialogUtil.showExceptionDailog(e);
                } catch (Exception e) {
                    LOGGER.error(ValueUtil.toString(e));
                    return FXCollections.emptyObservableList();
                }
            }
            if (f == null) {
                return FXCollections.emptyObservableList();
            }
            if (f instanceof ColumnItemTree) {
                return FXCollections.emptyObservableList();
            }
            ObservableList<TreeItem<DatabaseItemTree<String>>> files = f.getChildrens();
            if (files != null) {
                ObservableList<TreeItem<DatabaseItemTree<String>>> children = FXCollections.observableArrayList();
                for (TreeItem<DatabaseItemTree<String>> childFile : files) {
                    DatabaseItemTree<String> value = childFile.getValue();
                    TreeItem<DatabaseItemTree<String>> createNode = createNode(value);
                    children.add(createNode);
                }
                return children;
            }
            return FXCollections.emptyObservableList();
        }
    };
    Node imageNode = getGraphics(f);
    treeItem.setGraphic(imageNode);
    return treeItem;
}
Also used : TreeItem(javafx.scene.control.TreeItem) Node(javafx.scene.Node) DatabaseItemTree(com.kyj.fx.voeditor.visual.component.sql.dbtree.commons.DatabaseItemTree) ColumnItemTree(com.kyj.fx.voeditor.visual.component.sql.dbtree.commons.ColumnItemTree) GargoyleConnectionFailException(com.kyj.fx.voeditor.visual.exceptions.GargoyleConnectionFailException) GargoyleConnectionFailException(com.kyj.fx.voeditor.visual.exceptions.GargoyleConnectionFailException)

Aggregations

DatabaseItemTree (com.kyj.fx.voeditor.visual.component.sql.dbtree.commons.DatabaseItemTree)11 DatabaseTreeNode (com.kyj.fx.voeditor.visual.component.sql.dbtree.DatabaseTreeNode)6 TreeItem (javafx.scene.control.TreeItem)5 DatabaseMetaData (java.sql.DatabaseMetaData)3 ResultSet (java.sql.ResultSet)3 MySQLDatabaseItemTree (com.kyj.fx.voeditor.visual.component.sql.dbtree.mysql.MySQLDatabaseItemTree)2 PostgreDatabaseItemTree (com.kyj.fx.voeditor.visual.component.sql.dbtree.postgre.PostgreDatabaseItemTree)2 JsonElement (com.google.gson.JsonElement)1 H2DatabaseItemTree (com.kyj.fx.voeditor.visual.component.sql.dbtree.H2.H2DatabaseItemTree)1 ColumnItemTree (com.kyj.fx.voeditor.visual.component.sql.dbtree.commons.ColumnItemTree)1 TableItemTree (com.kyj.fx.voeditor.visual.component.sql.dbtree.commons.TableItemTree)1 OracleDatabaseItemTree (com.kyj.fx.voeditor.visual.component.sql.dbtree.oracle.OracleDatabaseItemTree)1 PostgreTableItemTree (com.kyj.fx.voeditor.visual.component.sql.dbtree.postgre.PostgreTableItemTree)1 SqliteColumnItemTree (com.kyj.fx.voeditor.visual.component.sql.dbtree.sqlite.SqliteColumnItemTree)1 SqliteDatabaseItemTree (com.kyj.fx.voeditor.visual.component.sql.dbtree.sqlite.SqliteDatabaseItemTree)1 SaveSQLFileFunction (com.kyj.fx.voeditor.visual.component.sql.functions.SaveSQLFileFunction)1 SqlKeywords (com.kyj.fx.voeditor.visual.component.text.SqlKeywords)1 GargoyleConnectionFailException (com.kyj.fx.voeditor.visual.exceptions.GargoyleConnectionFailException)1 ResourceLoader (com.kyj.fx.voeditor.visual.momory.ResourceLoader)1 DbUtil (com.kyj.fx.voeditor.visual.util.DbUtil)1