Application: gvSIG desktop

package org.gvsig.fmap.dal.store.jdbc2;

import java.io.File;

import java.net.URL;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Properties;

import org.apache.commons.collections4.CollectionUtils;

import org.apache.commons.io.FileUtils;

import org.apache.commons.io.FilenameUtils;

import org.apache.commons.io.IOUtils;

import org.apache.commons.lang3.BooleanUtils;

import org.apache.commons.lang3.StringUtils;

import org.apache.commons.lang3.math.NumberUtils;

import org.gvsig.expressionevaluator.ExpressionUtils;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.DataStore;

import static org.gvsig.fmap.dal.DataStore.H2SPATIAL_PROVIDER_NAME;

import org.gvsig.fmap.dal.DatabaseWorkspaceManager;

import org.gvsig.fmap.dal.exception.ValidateDataParametersException;

import org.gvsig.fmap.dal.feature.EditableFeature;

import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.EditableFeatureType;

import org.gvsig.fmap.dal.feature.Feature;

import org.gvsig.fmap.dal.feature.FeatureQuery;

import org.gvsig.fmap.dal.feature.FeatureStore;

import org.gvsig.fmap.dal.feature.NewFeatureStoreParameters;

import org.gvsig.fmap.dal.feature.impl.DefaultFeature;

import org.gvsig.fmap.dal.feature.spi.FeatureProvider;

import org.gvsig.fmap.dal.impl.expressionevaluator.DefaultFeatureAttributeEmulatorExpression;

import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorerParameters;

import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters;

import org.gvsig.tools.resourcesstorage.ResourcesStorage;

import org.gvsig.tools.util.HasAFile;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public abstract class AbstractTestUtils {

    public static final Logger LOGGER = LoggerFactory.getLogger(AbstractTestUtils.class);

    protected static int dbcounter = 1;

    protected Properties properties = null;

    public AbstractTestUtils() {

    }

    public File getTargetFolder() throws Exception {

        URL url = this.getClass().getResource("/");

        if (url == null) {

            url = AbstractTestUtils.class.getResource("/");

        }

        File x = new File(url.toURI());

        File target = x.getParentFile();

        return target;

    }

    public File getResource(String name) throws Exception {

        File x = new File(getTargetFolder(), name);

        return x;

    }

    public File getResourceAsFile(String pathname) throws Exception {

        URL url = this.getClass().getResource(pathname);

        if (url == null) {

            url = AbstractTestUtils.class.getResource(pathname);

        }

        if( StringUtils.equalsIgnoreCase(url.getProtocol(),"file") ) {

            File x = new File(url.toURI());

            return x;

        }

        File tmp = new File( this.getTargetFolder(), FilenameUtils.getName(pathname));

        IOUtils.copy(url, tmp);

        return tmp;

    }

    public File getFile(File name) throws Exception {

        File f = this.getResource(String.format(

                "%s-%d-%03d",

                name.getPath(),

                System.currentTimeMillis(),

                dbcounter++

        )

        );

        FileUtils.forceMkdir(f.getParentFile());

        return f;

    }

    public File getFolder(File name) throws Exception {

        File f = this.getResource(String.format(

                "%s-%d-%03d",

                name.getPath(),

                System.currentTimeMillis(),

                dbcounter++

        )

        );

        FileUtils.forceMkdir(f);

        return f;

    }

    public JDBCServerExplorerParameters getH2SpatialServerExplorerParameters(String dbname) throws Exception {

        DataManager dataManager = DALLocator.getDataManager();

        JDBCServerExplorerParameters conn = (JDBCServerExplorerParameters) dataManager.createServerExplorerParameters(H2SPATIAL_PROVIDER_NAME);

        File dbfile = this.getResource(String.format(

                "test-dbs/%s-%d-%03d",

                dbname,

                System.currentTimeMillis(),

                dbcounter++

        )

        );

//        System.out.println("#### dbfile: " + dbfile.getAbsolutePath());

        FileUtils.forceMkdir(dbfile.getParentFile());

        ((HasAFile) conn).setFile(dbfile);

        return conn;

    }

    public FeatureProvider getFeatureProvider(Feature feature) {

        return ((DefaultFeature) feature).getData();

    }

    public Expecteds getExpecteds(String name) throws Exception {

        Expecteds x = new Expecteds();

        x.normaliceSpaces = false;

        x.removeNL = true;

        x.stripStart = true;

        x.end_of_item = null;

        File f = getResourceAsFile(getExpectedsPath() + "/" + name);

        x.parse(f);

        return x;

    }

    public static class Expecteds {

        private Map<String, List<String>> expecteds = new HashMap<>();

        public boolean normaliceSpaces = false;

        public boolean stripStart = false;

        public boolean removeNL = false;

        public boolean replace_nl_by_space = false;

        public boolean trim_end = false;

        public String startLineComment = "-- ";

        public String end_of_item = ";";

        private static class CommandLine {

            private final String[] cmd;

            private CommandLine(String line, String prefix) {

                line = StringUtils.substring(line, prefix.length());

                line = StringUtils.normalizeSpace(line);

                this.cmd = StringUtils.split(line);

            }

            public boolean isTrue(int argn, boolean defaultValue) {

                if (argn >= this.cmd.length) {

                    return defaultValue;

                }

                return BooleanUtils.toBoolean(this.cmd[argn]);

            }

            public String get(int argn) {

                return this.cmd[argn];

            }

            public int size() {

                return this.cmd.length;

            }

        }

        public Expecteds() throws Exception {

        }

        public void parse(File f) throws Exception {

            final int SEARCHING_ITEM = 0;

            final int READING_ITEM = 1;

            boolean localNormaliceSpaces = false;

            boolean localStripStart = false;

            boolean localRemoveNL = false;

            boolean local_replace_nl_by_space = false;

            boolean local_trim_end = false;            

            String name = f.getName();

            List<String> lines = FileUtils.readLines(f);

            StringBuilder sb = null;

            String currentItem = null;

            int lineno = 1;

            int state = SEARCHING_ITEM;

            for (String line : lines) {

                line = line + "\n";

                switch (state) {

                    case SEARCHING_ITEM:

                        if (line.toLowerCase().startsWith(startLineComment + "normalize-spaces")) {

                            CommandLine cmd = new CommandLine(line, startLineComment);

                            normaliceSpaces = cmd.isTrue(1, true);

                        } else if (line.toLowerCase().startsWith(startLineComment + "end-of-item")) {

                            CommandLine cmd = new CommandLine(line, startLineComment);

                            end_of_item = cmd.get(1);

                        } else if (line.toLowerCase().startsWith(startLineComment + "strip-start")) {

                            CommandLine cmd = new CommandLine(line, startLineComment);

                            stripStart = cmd.isTrue(1, true);

                        } else if (line.toLowerCase().startsWith(startLineComment + "remove-nl")) {

                            CommandLine cmd = new CommandLine(line, startLineComment);

                            removeNL = cmd.isTrue(1, true);

                        } else if (line.toLowerCase().startsWith(startLineComment + "replace-nl-by-space")) {

                            CommandLine cmd = new CommandLine(line, startLineComment);

                            replace_nl_by_space = cmd.isTrue(1, true);

                        } else if (line.toLowerCase().startsWith(startLineComment + "trim-end")) {

                            CommandLine cmd = new CommandLine(line, startLineComment);

                            trim_end = cmd.isTrue(1, true);

                        } else if (line.toLowerCase().startsWith(startLineComment + "begin ")) {

                            CommandLine cmd = new CommandLine(line, startLineComment);

                            currentItem = cmd.get(1);

                            sb = new StringBuilder();

                            state = READING_ITEM;

                            localNormaliceSpaces = normaliceSpaces;

                            localStripStart = stripStart;

                            localRemoveNL = removeNL;

                            local_replace_nl_by_space = replace_nl_by_space;

                            local_trim_end = trim_end;

                        } else if (line.toLowerCase().startsWith(startLineComment + "rem ")) {

                            // do nothing skip comment

                        } else if (!StringUtils.isBlank(line)) {

                            throw new IllegalStateException("Syntax error at '" + name + "', line " + lineno + ".");

                        }

                        break;

                    case READING_ITEM:

                        if (line.toLowerCase().startsWith(startLineComment + "normalize-spaces")) {

                            CommandLine cmd = new CommandLine(line, startLineComment);

                            localNormaliceSpaces = cmd.isTrue(1, true);

                        } else if (line.toLowerCase().startsWith(startLineComment + "strip-start")) {

                            CommandLine cmd = new CommandLine(line, startLineComment);

                            localStripStart = cmd.isTrue(1, true);

                        } else if (line.toLowerCase().startsWith(startLineComment + "remove-nl")) {

                            CommandLine cmd = new CommandLine(line, startLineComment);

                            localRemoveNL = cmd.isTrue(1, true);

                        } else if (line.toLowerCase().startsWith(startLineComment + "replace-nl-by-space")) {

                            CommandLine cmd = new CommandLine(line, startLineComment);

                            local_replace_nl_by_space = cmd.isTrue(1, true);

                        } else if (line.toLowerCase().startsWith(startLineComment + "trim-end")) {

                            CommandLine cmd = new CommandLine(line, startLineComment);

                            local_trim_end = cmd.isTrue(1, true);

                        } else if (line.toLowerCase().startsWith(startLineComment + "end ")) {

                            CommandLine cmd = new CommandLine(line, startLineComment);

                            if (!StringUtils.equals(currentItem, cmd.get(1)) )  {

                                throw new IllegalStateException("Syntax error at '" + name + "', line " + lineno + ", expected '" + startLineComment + "end " + currentItem + "', and found " + line + ".");

                            }

                            String s = sb.toString();

                            if (s.endsWith("\n")) {

                                s = s.substring(0, s.length() - 1);

                            }

                            if (localNormaliceSpaces) {

                                s = StringUtils.normalizeSpace(s);

                            }

                            if( local_trim_end ) {

                                s = StringUtils.stripEnd(s, " ");

                            }

                            List<String> value = expecteds.get(currentItem);

                            if( value == null ) {

                                value = new ArrayList<>();

                                expecteds.put(currentItem, value);

                            }

                            if( StringUtils.isNotBlank(s) ) {

                                value.add(s);

                            }

                            state = SEARCHING_ITEM;

                        } else if (line.toLowerCase().startsWith(startLineComment + "rem ")) {

                            // do nothing skip comment

                        } else {

                            if (localStripStart) {

                                line = StringUtils.stripStart(line, null);

                            }

                            if( local_replace_nl_by_space ) {

                                line = line.replace('\n', ' ');

                            }

                            if (localRemoveNL) {

                                line = StringUtils.remove(line, "\n");

                                line = StringUtils.remove(line, "\r");

                            }

                            if( this.end_of_item!=null ) {

                                String s = StringUtils.stripEnd(line, null);

                                if( s!=null && s.endsWith(this.end_of_item) ) {

                                    List<String> value = expecteds.get(currentItem);

                                    if( value == null ) {

                                        value = new ArrayList<>();

                                        expecteds.put(currentItem, value);

                                    }

                                    s = s.substring(0, s.length() - this.end_of_item.length());

                                    value.add(s);

                                    line = null;

                                }

                            }

                            if( StringUtils.isNotBlank(line) ) {

                                sb.append(line);

                            }

                        }

                        break;

                }

                lineno++;

            }

            if (state != SEARCHING_ITEM) {

                throw new IllegalStateException("Syntax error at '" + name + "', expected '" + startLineComment + "end " + currentItem + "' and found EOF .");

            }

        }

        public boolean match(String name, int i, String value) {

            String exp = this.get(name, i);

            return FilenameUtils.wildcardMatch(value, exp);

        }

        public String get(String name) {

            List<String> value = this.expecteds.get(name);

            if( CollectionUtils.isEmpty(value) ) {

                return null;

            }

            return value.get(0);

        }

        public List<String> getAll(String name) {

            List<String> value = this.expecteds.get(name);

            if( CollectionUtils.isEmpty(value) ) {

                return null;

            }

            return value;

        }

        public String get(String name, int index) {

            List<String> value = this.expecteds.get(name);

            if( CollectionUtils.isEmpty(value) ) {

                return null;

            }

            return value.get(index);

        }

        public int size(String name) {

            List<String> value = this.expecteds.get(name);

            if( CollectionUtils.isEmpty(value) ) {

                return 0;

            }

            return value.size();

        }

    }

    public void removeResource(JDBCServerExplorer explorer, String storeName, String resourceName) throws Exception {

        JDBCStoreParameters storeParams = explorer.get(storeName);

        ResourcesStorage resourcesStorage = explorer.getResourcesStorage(storeParams);

        if( resourcesStorage.allowRemove() ) {

            resourcesStorage.remove(resourceName);

        }

    }

    public void info_h2sql(String label, File file) {

        System.out.println("");

        System.out.println("#h2console "+label+": "+file.getName());

        System.out.println("cd '"+file.getParent()+"'; h2sql -d '"+file.getName()+"' -user SA");

        System.out.println("jdbc:h2:"+file.getAbsolutePath()+";MODE=PostgreSQL;SCHEMA=PUBLIC;ALLOW_LITERALS=ALL");

        System.out.println("jdbc:h2:tcp://127.0.1.1:9123/"+file.getAbsolutePath()+";MODE=PostgreSQL;SCHEMA=PUBLIC;ALLOW_LITERALS=ALL");

        System.out.println("");

    }

    public void info_jdbc(String label, JDBCServerExplorerParameters parameters) {

        if( StringUtils.equalsIgnoreCase(parameters.getProviderName(), FeatureStore.H2SPATIAL_PROVIDER_NAME) ) {

            info_h2sql(label, ((HasAFile)parameters).getFile());

            return;

        }

        System.out.println("");

        System.out.println("# Connection "+label);

        System.out.println(parameters.getUrl());

        System.out.println("");

    }

    public void info_jdbc(String label, JDBCServerExplorer explorer) {

        info_jdbc(label, explorer.getParameters());

    }

    public void info_jdbc(JDBCServerExplorer explorer) {

        JDBCServerExplorerParameters params = explorer.getParameters();

        info_jdbc(params.getDBName(), params);

    }

    protected boolean getPropertyBoolean(String name) {

        Properties props = this.getProperties();

        return BooleanUtils.toBoolean(props.getProperty(name));

    }

    protected int getPropertyInt(String name, int defaultValue) {

        Properties props = this.getProperties();

        return NumberUtils.toInt(props.getProperty(name), defaultValue);

    }

    protected String getProperty(String name) {

        Properties props = this.getProperties();

        return props.getProperty(name);

    }

    protected Properties getProperties() {

        if( this.properties == null ) {

            try {

                Properties props = new Properties();

                this.properties = props;

            } catch(Exception ex) {

                throw new RuntimeException(ex);

            }

        }

        return this.properties;

    }

    public boolean isTheDatabaseAvailable() {

        return true;

    }

    public FeatureStore openCSVStore(String pathname) throws Exception {

        DataManager dataManager = DALLocator.getDataManager();

        File f = getResourceAsFile(pathname);

        FeatureStore store = (FeatureStore) dataManager.openStore(

                DataStore.CSV_PROVIDER_NAME, 

                "file=",f,

                "automaticTypesDetection=", false,

                "locale=","en"

        );

        return store;

    }

    public FeatureStore openCountriesStore() throws Exception {

        return this.openCSVStore("/org/gvsig/fmap/dal/store/jdbc2/countries.csv");

    }

    public FeatureStore openSourceStore1() throws Exception {

        return this.openCSVStore("/org/gvsig/fmap/dal/store/jdbc2/testCreateSource1.csv");

    }

    public FeatureStore openSourceStore2() throws Exception {

        return this.openCSVStore("/org/gvsig/fmap/dal/store/jdbc2/testCreateSource2.csv");

    }

    public FeatureStore openStore(JDBCServerExplorer explorer, String name) throws Exception {

        JDBCStoreParameters params = explorer.get(name);

        DataManager dataManager = DALLocator.getDataManager();

        FeatureStore store;

        try {

            store = (FeatureStore) dataManager.openStore(

                    getProviderName(), 

                    params

            );

        } catch(ValidateDataParametersException ex) {

            LOGGER.warn(ex.getLocalizedMessageStack());

            throw ex;

        }

        return store;

    }

    public void create_table_from(JDBCServerExplorer targetExplorer, String targetName, FeatureStore sourceStore) throws Exception {

        NewFeatureStoreParameters params = (NewFeatureStoreParameters) targetExplorer.getAddParameters(

                targetName

        );

        EditableFeatureType ft = params.getDefaultFeatureType();

        ft.addAll(sourceStore.getDefaultFeatureType());

        targetExplorer.add(getProviderName(), params, true);

    }

    public void insert_into_from(JDBCServerExplorer targetExplorer, String targetName, FeatureStore sourceStore, int mode) throws Exception {

        FeatureStore targetStore = openStore(targetExplorer, targetName);

        targetStore.edit(mode);

        try {

            for (Feature sourceFeature : sourceStore.getFeatureSet()) {

                EditableFeature targetFeature = targetStore.createNewFeature(sourceFeature);

                targetStore.insert(targetFeature);

            }

        } finally {

            targetStore.finishEditing();

        }

    }

    public JDBCServerExplorer openServerExplorer(String dbname) throws Exception {        

        DataManager dataManager = DALLocator.getDataManager();

        JDBCServerExplorerParameters params = this.getServerExplorerParameters(dbname);

        JDBCServerExplorer explorer = (JDBCServerExplorer) dataManager.openServerExplorer(

                this.getProviderName(), 

                params

        );

        return explorer;

    }

    public JDBCServerExplorer openServerExplorer(File dbfile) throws Exception {        

        DataManager dataManager = DALLocator.getDataManager();

        JDBCServerExplorerParameters params = this.getServerExplorerParameters(dbfile);

        JDBCServerExplorer explorer = (JDBCServerExplorer) dataManager.openServerExplorer(

                this.getProviderName(), 

                params

        );

        return explorer;

    }

    public void drop_tables(JDBCServerExplorer explorer, String...tables) throws Exception {

        for (String table : tables) {

            String sql = "DROP TABLE IF EXISTS \""+table+"\"";

            explorer.execute(sql);

            removeResource(explorer, table, "dal");

        }

    }

    public DatabaseWorkspaceManager initWorkspace(String name) throws Exception {

        JDBCServerExplorer explorer = this.openServerExplorer(name);

        DataManager manager = DALLocator.getDataManager();

        DatabaseWorkspaceManager ws = manager.createDatabaseWorkspaceManager(explorer.getParameters());

        ws.connect();

        if (!ws.existsTable(DatabaseWorkspaceManager.TABLE_CONFIGURATION)) {

            ws.createTable(DatabaseWorkspaceManager.TABLE_CONFIGURATION);

        }

        if (!ws.existsTable(DatabaseWorkspaceManager.TABLE_RESOURCES)) {

            ws.createTable(DatabaseWorkspaceManager.TABLE_RESOURCES);

        }

        return ws;

    }

    public List<String> getExpectedSQLs(String name) throws Exception {

        File f = getResourceAsFile(getExpectedsPath() + "/" + name);

        List<String> SQLs = new ArrayList<>();

        List<String> lines = FileUtils.readLines(f);

        StringBuilder sb = new StringBuilder();

        for (String line : lines) {

            line = StringUtils.stripStart(line, null);

            if (line.startsWith("--")) {

                continue;

            }

            if (line.endsWith(";")) {

                sb.append(line.substring(0, line.length() - 1));

                SQLs.add(sb.toString());

                sb.setLength(0);

            } else {

                sb.append(line);

            }

        }

        return SQLs;

    }

//    public abstract String getExpectedResourcesPrefix(); // Ex. "h2spatial"

    public abstract String getExpectedsPath();

    public abstract JDBCHelper createJDBCHelper() throws Exception;

    public abstract String getProviderName();

    public abstract JDBCServerExplorerParameters getServerExplorerParameters(String dbname) throws Exception;

    public void runSQLToCheckSyntax(String testName, FeatureStore datacsv, String testTableName, String sql) throws Exception {

        JDBCServerExplorer explorer = this.openServerExplorer(testName);

        this.info_jdbc(explorer);

        this.drop_tables(explorer, testTableName);

        this.create_table_from(explorer, testTableName, datacsv);

        this.insert_into_from(explorer, testTableName, datacsv, FeatureStore.MODE_APPEND);

        explorer.execute(sql);

    }

    public void output_results(String testName, String sqlresult, String sqlexpected) {

        System.out.println("###### "+testName+"()");

        System.out.println("###### SQL:" + sqlresult+";");

        System.out.println("###### EXP:" + sqlexpected+";");

    }

    public EditableFeatureAttributeDescriptor addExtraColumn(EditableFeatureType eFeatureType, FeatureQuery query, String name, int type, String exp) {

        EditableFeatureAttributeDescriptor extraColumn = query.getExtraColumn().add(name, type);

        extraColumn.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression(exp)));

        return extraColumn;

    }

    public JDBCServerExplorerParameters getServerExplorerParameters(File dbfile) throws Exception {

        DataManager dataManager = DALLocator.getDataManager();

        JDBCServerExplorerParameters conn = (JDBCServerExplorerParameters) dataManager.createServerExplorerParameters(this.getProviderName());

//        System.out.println("#### dbfile: " + dbfile.getAbsolutePath());

        FileUtils.forceMkdir(dbfile.getParentFile());

        ((HasAFile) conn).setFile(dbfile);

        return conn;

    }

}