Package adipe.translate.example.wizard

Source Code of adipe.translate.example.wizard.App

package adipe.translate.example.wizard;

import static com.google.common.base.Preconditions.checkNotNull;
import static com.google.common.base.Preconditions.checkState;
import static output.ExcelFileFormulas.QUIET_LAYOUT;

import java.awt.Component;
import java.awt.Desktop;
import java.awt.Dimension;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.KeyEvent;
import java.awt.event.WindowEvent;
import java.beans.PropertyChangeEvent;
import java.beans.PropertyChangeListener;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import javax.swing.AbstractAction;
import javax.swing.BorderFactory;
import javax.swing.Box;
import javax.swing.BoxLayout;
import javax.swing.JButton;
import javax.swing.JComponent;
import javax.swing.JDialog;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JProgressBar;
import javax.swing.JRootPane;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.KeyStroke;
import javax.swing.SwingUtilities;
import javax.swing.UIManager;
import javax.swing.UnsupportedLookAndFeelException;
import javax.swing.border.BevelBorder;
import javax.swing.filechooser.FileFilter;

import org.antlr.v4.runtime.misc.ParseCancellationException;

import output.ExcelFileFormulas;
import ra.Result;
import ra.Term;
import adipe.translate.Queries;
import adipe.translate.Schemas;
import adipe.translate.TranslationException;
import adipe.translate.ra.Schema;
import adipe.translate.ra.Terms;
import adipe.translate.sql.ColumnIndexesImpl;
import adipe.translate.sql.ColumnNamesImpl;
import adipe.translate.sql.SimpleColumn;

import com.google.common.collect.Lists;

import core.Column;
import core.ColumnLabels;
import core.Relation;

/**
* Example app that uses the SQLToAlgebra translation library.
*
* Demo app displaying a wizard that asks for the database scheme and the SQL query to be translated
* with regards to it.
*/
public class App implements Runnable, ActionListener {

    /** the default code creating a schema, displayed in the wizard */
    private static final String DEFAULT_SCHEMA_TEXT =
            "CREATE TABLE pts(\n"+
            "    id VARCHAR, x INT, y INT\n"+
            ");";
    /** the default sql queries displayed in the wizard */
    private static final String DEFAULT_QUERY_TEXT =
            "-- Order point pairs by distance:\n"+
            "  SELECT p1.id, p2.id,\n"+
            "         [sqrt((${p1.x}-${p2.x})*(${p1.x}-${p2.x})+(${p1.y}-${p2.y})*(${p1.y}-${p2.y}))]\n"+
            "    FROM pts p1 JOIN pts p2 ON p1.id < p2.id\n"+
            "   WHERE p1.x <> p2.x OR p1.y <> p2.y\n"+
            "ORDER BY 3";

    ExecutorService threadPool = Executors.newSingleThreadExecutor();

    /** thread safety: access {@link #resultsFrame} only from {@link threadPool} */
    JFrame resultsFrame;

    JFrame inputFrame;
    JTextArea schemaTextArea;
    JTextArea queryTextArea;
    JTextArea resultsTextArea;
    final JLabel resultsLabel = new JLabel();

    final JFileChooser fc = new JFileChooser();

    private App() {
        fc.setFileFilter(new FileFilter() {

            @Override
            public boolean accept(File f) {
                return f.isDirectory() || f.getName().toLowerCase().endsWith(".xlsx");
            }

            @Override
            public String getDescription() {
                return "Microsoft Excel Open XML Spreadsheet";
            }

        });
    }

    @Override
    public void run() {
        createInputWindow();
        improveFirstSpreadsheetSaveSpeed();
    }

    private void improveFirstSpreadsheetSaveSpeed() {
        threadPool.execute(new Runnable() {

            @Override
            public void run() {
                /* translating the first query, then writing the first spreadsheet file take longer time, so let's
                 * warm up here */
                Schema schema = Schemas.fromDDL(DEFAULT_SCHEMA_TEXT);
                try {
                    Term translated = Queries.getRaOf(schema, DEFAULT_QUERY_TEXT);
                    Result result = translated.execute();
                    try (OutputStream os = new OutputStream() {
                        @Override
                        public void write(int b) throws IOException {
                            // nop
                        }};
                        ExcelFileFormulas eff = new ExcelFileFormulas(os)) {
                        eff.addResult(result, ExcelFileFormulas.QUIET_LAYOUT);
                    } catch (IOException e) {
                        throw new RuntimeException("internal error");
                    }
                } catch (ParseCancellationException | TranslationException e) {
                    throw new RuntimeException("internal error");
                }

            }

        });
    }

    public static void main(String args[]) throws ClassNotFoundException, InstantiationException, IllegalAccessException, UnsupportedLookAndFeelException {
        UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());
        javax.swing.SwingUtilities.invokeLater(new App());
    }

    @Override
    public void actionPerformed(final ActionEvent ev) {
        if ("algebra".equals(ev.getActionCommand())) {
            final String createTableStatements = schemaTextArea.getText();
            final String sqlQuery = queryTextArea.getText();

            queryTextArea.requestFocusInWindow();

            if (resultsFrame == null) {
                createAlgebraResultsDialog();
            }

            resultsLabel.setText("Working...");
            resultsTextArea.setText("");

            threadPool.execute(new Runnable() {
                @Override
                public void run() {
                    translateAndShowInAlgebraResultsDialog(createTableStatements, sqlQuery);
                }
            });
        } else {
            if (fc.showSaveDialog(inputFrame) == JFileChooser.APPROVE_OPTION) {
                File f = fc.getSelectedFile();
                String filePath = f.getAbsolutePath();
                if (filePath.toLowerCase().endsWith(".xlsx") == false) {
                    f = new File(filePath + ".xlsx");
                }
                final File xlsxFile = f;

                if (xlsxFile.exists()) {
                    int choice = JOptionPane.showOptionDialog(
                        inputFrame,
                        String.format("Overwrite '%s'?", f.getName()),
                        "Overwrite?",
                        JOptionPane.YES_NO_OPTION,
                        JOptionPane.WARNING_MESSAGE,
                        null,
                        new Object[] { "Overwrite", "Choose different path"  },
                        "Choose different path"
                    );

                    if (choice != JOptionPane.YES_OPTION) {
                        javax.swing.SwingUtilities.invokeLater(new Runnable() {
                            @Override
                            public void run() {
                                actionPerformed(ev);
                            }
                        });
                        return;
                    }
                }

                final JProgressBar progressBar = new JProgressBar();
                progressBar.setIndeterminate(true);

                JOptionPane optionPane = new JOptionPane(
                        "Translating and exporting as spreadsheet...",
                        JOptionPane.INFORMATION_MESSAGE,
                        JOptionPane.DEFAULT_OPTION,
                        null,
                        new Object[] { },
                        null
                );

                optionPane.add(progressBar);

                final JDialog dialog = new JDialog();
                dialog.setTitle("Exporting spreadsheet");
                dialog.setModal(true);
                dialog.setContentPane(optionPane);
                dialog.setDefaultCloseOperation(JDialog.DO_NOTHING_ON_CLOSE);
                dialog.pack();
                dialog.setLocationRelativeTo(inputFrame);

                threadPool.execute(new Runnable() {
                    @Override
                    public void run() {
                        translateAndSaveSpreadsheet(xlsxFile, dialog, progressBar);
                    }
                });

                dialog.setVisible(true);
            }
        }
    }

    protected void translateAndSaveSpreadsheet(final File xlsxFile, final JDialog dialog, final JProgressBar progressBar) {
        final String xlsxFilename = xlsxFile.getName();

        String message0;
        int messageType0;
        boolean includeOpenOption0;

        try {
            final String createTableStatements  = schemaTextArea.getText();
            String sqlSource              = queryTextArea.getText();
            final Schema databaseSchema = Schemas.fromDDL(createTableStatements);
            // TODO show a meaningful error message if syntax error in the CREATE TABLE statement, e.g.,
            // duplicate table names
            // TODO show a meaningful error message if the CREATE TABLE statements are broken
            // TODO extract the code that parses the schema and the query from the two translateAnd* functions
            List<String> queries          = Lists.newArrayList();
            List<Term> formulas           = Lists.newArrayList();
            List<Term> inputTables        = Lists.newArrayList();
            final List<Result> results    = Lists.newArrayList();

            declareTablesThenTranslate(sqlSource, databaseSchema, queries, formulas, inputTables);

            Iterator<String> tableNamesIter = databaseSchema.tableNames().iterator();
            for (Term inputTable : inputTables) {
                // add input tables to {@link results} so that they get borders
                // also add labels with column names
                // TODO refactor this and move to QuietSpreadsheetLayout
                Result result = inputTable.execute();
                ColumnLabels labels = result.getLabels();
                String tableName = tableNamesIter.next();
                ColumnNamesImpl cn = databaseSchema.schemaTable(tableName);
                ColumnIndexesImpl ci = cn.asColumnIndexesLookup();
                Iterator<SimpleColumn> scs = ci.iterator();
                for (Column c : result.getResultRelation()) {
                    SimpleColumn sc = scs.next();
                    labels.put(c, tableName+"."+sc.name());
                }

                results.add(result);
            }

            // add the SQL query to the comments for the result columns
            // TODO add column name or expression
            // TODO add only one formula's sql code, not all sql formulas' codes
            // TODO make sure the comment is wide enough so that no wrapping occurs
            String sourceComment = "|--SQL query:\n"+sqlSource;
            sourceComment = sourceComment.replace("\n", "\n|   ");
            for (Term formula : formulas) {
                // TODO refactor this and move to QuietSpreadsheetLayout
                Result result = formula.execute();
                ColumnLabels labels = result.getLabels();
                Relation resultRelation = result.getResultRelation();
                for (Column col : resultRelation) {
                    labels.put(col, sourceComment);
                }
                results.add(result);
            }

            // TODO move the following inner try block outside the outer try block
            try (
                FileOutputStream out = new FileOutputStream(xlsxFile);
                ExcelFileFormulas excel = new ExcelFileFormulas(out);
            )
            {
                for (Result result : results) {
                    excel.addResult(result, QUIET_LAYOUT);
                }
                message0 = String.format("Done saving '%s'.", xlsxFilename);
                messageType0 = JOptionPane.INFORMATION_MESSAGE;
                includeOpenOption0 = true;
            } catch (Exception e) {
                message0 = String.format("Error: could not write file '%s' because: %s", xlsxFilename, e.toString());
                messageType0 = JOptionPane.ERROR_MESSAGE;
                includeOpenOption0 = false;
            }
        } catch (ParseCancellationException e) {
            message0 = String.format("%s", e.getMessage());
            // TODO increase the size of the dialog box to make the message fit
            messageType0 = JOptionPane.ERROR_MESSAGE;
            includeOpenOption0 = false;
        } catch (TranslationException e) {
            message0 = String.format("There is an error in your SQL query.\n%s", e.getMessage());
            // TODO include the SQL query with a pointer to where the error seems to be
            messageType0 = JOptionPane.ERROR_MESSAGE;
            includeOpenOption0 = false;
        } catch (RuntimeException e) {
            message0 = String.format("An error occurred when translating your SQL query.\n%s", e);
            // TODO when does this happen?
            messageType0 = JOptionPane.ERROR_MESSAGE;
            includeOpenOption0 = false;
            // TODO extract the code for attempting to translate and for forming an error message
            //      from the two execution paths for displaying relational algebra and for saving
            //      the spreadsheet
        }

        final String message            = message0;
        final int messageType           = messageType0;
        final boolean includeOpenOption = includeOpenOption0;

        SwingUtilities.invokeLater(new Runnable() {
            @Override
            public void run() {
                dialog.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
                final JOptionPane optionPane = new JOptionPane(
                        message,
                        messageType,
                        JOptionPane.DEFAULT_OPTION,
                        null,
                        includeOpenOption ? new Object[] { "Dismiss", "Open" } : new Object[] { "Dismiss" },
                        "Dismiss"
                );
                optionPane.addPropertyChangeListener(
                    new PropertyChangeListener() {
                        @Override
                        public void propertyChange(PropertyChangeEvent evt) {
                            String prop = evt.getPropertyName();

                            if (dialog.isVisible()
                             && (evt.getSource() == optionPane)
                             && (prop.equals(JOptionPane.VALUE_PROPERTY)))
                             {
                                if ("Open".equals(optionPane.getValue())) {
                                    try {
                                        Desktop.getDesktop().open(xlsxFile);
                                    } catch (IOException e) {
                                        e.printStackTrace();
                                    }
                                }
                                dialog.dispose();
                             }
                        }
                    }
                );
                dialog.setContentPane(optionPane);
                dialog.validate();
                dialog.repaint();
            }
        });
    }

    private void translateAndShowInAlgebraResultsDialog(String createTableStatements, String sqlSource) {
        Schema databaseSchema = null;
        String resultsText = null;

        try {
            databaseSchema = Schemas.fromDDL(createTableStatements);
        } catch (Exception e) {
            resultsText = e.getMessage();
            resultsLabel.setText("Parsing the database schema caused the following error:");
        }

        if (databaseSchema != null) {
            try {
                List<String> queries    = Lists.newArrayList();
                List<Term> formulas     = Lists.newArrayList();
                List<Term> inputTables  = Lists.newArrayList();

                declareTablesThenTranslate(sqlSource, databaseSchema, queries, formulas, inputTables);

                for (Term inputTable : inputTables) {
                    inputTable.execute();
                }

                StringBuilder sb = new StringBuilder();
                for (Term formula : formulas) {
                    sb.append(Terms.indent(formula));
                }

                resultsText = sb.toString();

                resultsLabel.setText("The queries in relational algebra:");
            } catch (ParseCancellationException e) {
                resultsText = e.getMessage();
                resultsLabel.setText("Translation failed with the following error:");
            } catch (TranslationException e) {
                resultsText = "There is an error in your SQL query.\n" + e.getMessage();
                resultsLabel.setText("Translation failed with the following error:");
            } catch (RuntimeException e) {
                resultsText = "An error occurred when translating your SQL query.\n"+e.toString();
                resultsLabel.setText("Translation failed with the following error:");
            }
        }

        checkNotNull(resultsFrame);

        resultsTextArea.setText(resultsText);
        resultsFrame.setVisible(true);
        resultsTextArea.repaint();
        resultsLabel.repaint();
        resultsFrame.toFront();
        resultsFrame.requestFocus();
    }

    /**
     * Translates the given {@code sqlQueries}, making sure the tables declared in
     * {@code databaseSchema} are assigned spreadsheet columns in the order of
     * appearance in the schema.
     * @param queries        must be non-null, out parameter: the queries found in {@code sqlQueries}, in order of appearance
     * @param formulas       must be non-null, out parameter: the formulas of the above parameters, in order of appearance
     * @side  the lists {@code queries} and {@code formulas} will be cleared and populated
     * @throws TranslationException
     * @throws TranslationException when a derived table is parsed, but has a derived column list of the wrong size
     * @throws ParseCancellationException
     * @throws RuntimeException
     * @return the result of executing the select from all tables query
     * TODO update
     */
    private void declareTablesThenTranslate(String sqlQueries,
                final Schema databaseSchema,
                List<String> queries, List<Term> formulas,
                List<Term> inputTables
            ) throws TranslationException, ParseCancellationException, RuntimeException{
        /* Fragile code: adds additional query to the front in order to assign excel
         * columns to all tables, in the order of definition as opposed to
         * assigning columns only to tables referenced in the queries, in that order.
         * TODO update */
        StringBuilder selectFromAll = new StringBuilder();
        for (String tableName : databaseSchema.tableNames()) {
            selectFromAll.append("SELECT * FROM ");
            selectFromAll.append(tableName);
            selectFromAll.append(";\n");
        }
        sqlQueries = selectFromAll + sqlQueries;
        /* */

        Queries.getMultipleRasOf(databaseSchema, sqlQueries, queries, formulas);

        /* Now remove the forged statement's results (?) */
        queries = queries.subList(databaseSchema.size(), queries.size());
        for (int i = 0; i < databaseSchema.size(); ++i) {
            // TODO make O(1)
            Term t = formulas.remove(0);
            inputTables.add(t);
        }

        if (formulas.isEmpty()) {
            throw new TranslationException("An empty string was input as a query");
        }
    }

    private void createAlgebraResultsDialog() {
        checkState(resultsFrame == null);

        resultsFrame = new JFrame("Results");

        JRootPane root = resultsFrame.getRootPane();
        root.getInputMap(JComponent.WHEN_IN_FOCUSED_WINDOW).put(
                KeyStroke.getKeyStroke(KeyEvent.VK_ESCAPE, 0),
                "closeResultsFrame"
        );
        root.getActionMap().put("closeResultsFrame", new AbstractAction() {
            private static final long serialVersionUID = 2343868465317523658L;

            @Override
            public void actionPerformed(ActionEvent e) {
                resultsFrame.dispatchEvent(
                    new WindowEvent(resultsFrame, WindowEvent.WINDOW_CLOSING)
                );
            }
        });

        resultsLabel.setFont(new Font("Dialog", Font.BOLD, 16));
        resultsLabel.setText("Working...");

        resultsTextArea = new JTextArea(16, 100);
        resultsTextArea.setFont(new Font("Monospaced", Font.PLAIN, 14));
        resultsTextArea.setEditable(false);
        JScrollPane resultsScrollPane = new JScrollPane(resultsTextArea);
        resultsScrollPane.setAlignmentX(Component.LEFT_ALIGNMENT);
//        resultsScrollPane.setBorder(BorderFactory.createEmptyBorder(10, 10, 10, 10));

        JPanel panel = new JPanel();
        panel.setLayout(new BoxLayout(panel, BoxLayout.PAGE_AXIS));
        panel.add(resultsLabel);
        panel.add(resultsScrollPane);

        resultsFrame.add(panel);

        resultsFrame.pack();
        resultsFrame.setLocationByPlatform(true);
        resultsFrame.setVisible(true);
    }

    private void createInputWindow() {
        inputFrame = new JFrame("SQLToAlgebra");
        inputFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        inputFrame.getContentPane().setLayout(new BoxLayout(inputFrame.getContentPane(), BoxLayout.PAGE_AXIS));

        String version = getClass().getPackage().getImplementationVersion();
        String versionIndicator="";
        if (version != null) {
            versionIndicator = " " + version;
        }
        JLabel label1 = new JLabel(String.format("You are using SQLToAlgebra%s.", versionIndicator));
        label1.setFont(new Font("Dialog", Font.BOLD, 16));
        inputFrame.getContentPane().add(label1);

        inputFrame.getContentPane().add(Box.createRigidArea(new Dimension(0,12)));

        JLabel label2 = new JLabel("The translation occurs in the context of a database schema. Enter the schema below.");
        label2.setFont(new Font("Dialog", Font.PLAIN, 16));
        inputFrame.getContentPane().add(label2);

        schemaTextArea = new JTextArea(DEFAULT_SCHEMA_TEXT, 12, 100);
        schemaTextArea.setFont(new Font("Monospaced", Font.PLAIN, 14));

        JScrollPane scrollPane1 = new JScrollPane(schemaTextArea);
        scrollPane1.setAlignmentX(Component.LEFT_ALIGNMENT);
        //scrollPane1.setBorder(BorderFactory.createEmptyBorder(10,10,10,10));
        inputFrame.getContentPane().add(scrollPane1);

        inputFrame.getContentPane().add(Box.createRigidArea(new Dimension(0,12)));

        JLabel label3 = new JLabel("In the following text area, enter the SQL queries you want to translate.");
        label3.setFont(new Font("Dialog", Font.PLAIN, 16));
        inputFrame.getContentPane().add(label3);

        queryTextArea = new JTextArea(DEFAULT_QUERY_TEXT, 12, 100);
        queryTextArea.setFont(new Font("Monospaced", Font.PLAIN, 14));

        JScrollPane scrollPane2 = new JScrollPane(queryTextArea);
        scrollPane2.setAlignmentX(Component.LEFT_ALIGNMENT);
        //scrollPane2.setBorder(BorderFactory.createEmptyBorder(10,10,0,10));
        inputFrame.getContentPane().add(scrollPane2);

        JButton button1 = new JButton("Algebra");
        button1.setActionCommand("algebra");
        button1.setMnemonic(KeyEvent.VK_A);
        button1.addActionListener(this);

        JButton button2 = new JButton("Spreadsheet...");
        button2.setActionCommand("Spreadsheet");
        button2.setMnemonic(KeyEvent.VK_S);
        button2.addActionListener(this);

        JPanel buttonsPanel = new JPanel();
        buttonsPanel.setLayout(new BoxLayout(buttonsPanel, BoxLayout.LINE_AXIS));
        buttonsPanel.setAlignmentX(Component.CENTER_ALIGNMENT);
        buttonsPanel.setBorder(
            BorderFactory.createCompoundBorder(
                BorderFactory.createTitledBorder(
                        BorderFactory.createEtchedBorder(), "Translate to:", BevelBorder.LOWERED, 0
                ),
                BorderFactory.createEmptyBorder(2,4,4,4)
            )
        );
        buttonsPanel.add(button1);
        buttonsPanel.add(Box.createRigidArea(new Dimension(4,0)));
        buttonsPanel.add(button2);

        JPanel buttonsAndLabelOuterPanel = new JPanel();
        buttonsAndLabelOuterPanel.setLayout(new BoxLayout(buttonsAndLabelOuterPanel, BoxLayout.LINE_AXIS));
        buttonsAndLabelOuterPanel.setBorder(BorderFactory.createEmptyBorder(10,10,10,10));
        buttonsAndLabelOuterPanel.add(Box.createHorizontalGlue());
        buttonsAndLabelOuterPanel.setAlignmentX(Component.LEFT_ALIGNMENT);
        buttonsAndLabelOuterPanel.add(buttonsPanel);
        inputFrame.getContentPane().add(buttonsAndLabelOuterPanel);

        inputFrame.pack();
        inputFrame.setLocationByPlatform(true);
        inputFrame.setVisible(true);
    }
}
TOP

Related Classes of adipe.translate.example.wizard.App

TOP
Copyright © 2018 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.