gvSIG projects pool

/*

 * gvSIG. Desktop Geographic Information System.

 * 

 * Copyright (C) 2007-2020 gvSIG Association.

 * 

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 3

 * of the License, or (at your option) any later version.

 * 

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 * 

 * You should have received a copy of the GNU General Public License 

 * along with this program. If not, see <https://www.gnu.org/licenses/>. 

 * 

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.vcsgis.lib;

import java.io.IOException;

import java.io.InputStream;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.function.Predicate;

import javax.json.JsonArray;

import javax.json.JsonObject;

import org.apache.commons.io.IOUtils;

import org.apache.commons.lang3.StringUtils;

import org.gvsig.json.Json;

import org.gvsig.json.JsonObjectBuilder;

import org.gvsig.tools.ToolsLocator;

import org.gvsig.tools.util.MapBuilder;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_AUTHENTICATION_EXPIRED;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_ADD_ENTITY_WITHOUT_PRIMARY_KEY;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_ADD_LAYER;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_AUTHENTICATE_USER;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_BUILD_REQUEST_RESPONSE;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_CHECKOUT;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_COMMIT;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_COMMIT_WITH_FEATURE_CODE_BLANK;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_COMMIT_WORKSPACE_OUTDATED;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_CREATE_TABLE;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_CREATE_TABLE_CHANGES;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_CREATE_TABLE_CONFIG;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_CREATE_TABLE_ENTITIES;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_EXECUTE_REQUEST;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_HANDLE_REQUEST;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_INITIALIZE_REPOSITORY;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_INSERT_CHANGE;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_INSERT_ENTITIES;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_INSERT_FEATURES;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_OPEN_CHANGES;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_OPEN_ENTITIES;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_OPEN_STORE;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_OPEN_WORKSPACE;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_OPEN_WORKSPACE_SERVEREXPLORER;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_PREPARE_UPDATE;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_REMOVE_CHANGES;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_REMOVE_ENTITY;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_RETRIEVE_ENTITIES;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_RETRIEVE_SOURCE_FEATURES;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_REVERT;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_UPDATE;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_UPDATE_CLEAN;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_CANT_UPDATE_ENTITIES;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_DBFILE_IS_NULL;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_ENTITY_ALREADY_EXISTS;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_ENTITY_NOT_HAS_VCSGISCODE;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_INVALID_AUTHENTICATION_TOKEN;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_INVALID_DATA;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_INVALID_REPOSITORY;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_INVALID_REVISION;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_INVALID_REVISION_FOR_ENTITY;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_INVALID_USERCODE;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_LABEL_IS_NULL;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_NO_ERROR;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_STORE_NOT_IN_VERSION_CONTROL;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_USER_NOT_AUTHORIZED;

import static org.gvsig.vcsgis.lib.VCSGisManager.ERR_WSEXPLORER_IS_NULL;

import static org.gvsig.vcsgis.lib.VCSGisManager.OP_ADD_ENTITY;

import static org.gvsig.vcsgis.lib.VCSGisManager.OP_DELETE;

import static org.gvsig.vcsgis.lib.VCSGisManager.OP_INSERT;

import static org.gvsig.vcsgis.lib.VCSGisManager.OP_UPDATE;

import static org.gvsig.vcsgis.lib.VCSGisManager.STATE_CONFLICT;

import static org.gvsig.vcsgis.lib.VCSGisManager.STATE_LOCAL_MODIFIED;

import static org.gvsig.vcsgis.lib.VCSGisManager.STATE_LOCAL_NEW;

import static org.gvsig.vcsgis.lib.VCSGisManager.STATE_LOCAL_OUTDATED;

import static org.gvsig.vcsgis.lib.VCSGisManager.STATE_LOCAL_OUTDATED_AND_MODIFIED;

import static org.gvsig.vcsgis.lib.VCSGisManager.STATE_LOCAL_UNMODIFIED;

import static org.gvsig.vcsgis.lib.VCSGisManager.TOPOLOGYPLAN_MODE_RECOMMENDED;

/**

 *

 * @author gvSIG Team

 */

public class VCSGisUtils {

    public static final String ENTITY_CODE = "EntityCode";

    public static final String ENTITY_NAME = "EntityName";

    public static final String ENTITY_DATATABLENAME = "DataTableName";

    public static final String ENTITY_DESCRIPTION = "Description";

    public static final String ENTITY_FEATUREIDFIELDNAME = "FeatureIdFieldName";

    public static final String ENTITY_FEATURETYPEASJSON = "FeatureTypeAsJson";

    public static final String ENTITY_FIELDFORLABEL = "FieldForLabel";

    public static final String ENTITY_GEOMETRYFIELDNAME = "GeometryFieldName";

    public static final String ENTITY_REVISIONCODE = "RevisionCode";

    public static final String ENTITY_TOPOLOGYPLANCODE = "TopologyPlanCode";

    public static final String ENTITY_USERCODE = "UserCode";

    public static final String ENTITY_CATEGORY = "Category";

    public static final String ENTITY_LABEL = "Label";

    public static final String ENTITY_AUTHORIZATIONS = "Autorizations";

    public static final String ENTITY_TOPOLOGYPLANMODE = "TopologyPlanMode";

//    private static final String[] JSONENTITY_ATTRNAMES = new String[] {

//        ENTITY_DATATABLENAME, 

//        ENTITY_DESCRIPTION, ENTITY_CODE, 

//        ENTITY_NAME, 

//        ENTITY_FEATUREIDFIELDNAME, 

//        ENTITY_FEATURETYPEASJSON, 

//        ENTITY_FIELDFORLABEL, 

//        ENTITY_GEOMETRYFIELDNAME, 

//        ENTITY_REVISIONCODE, 

//        ENTITY_TOPOLOGYPLANCODE, 

//        ENTITY_USERCODE,

//        ENTITY_CATEGORY,

//        ENTITY_LABEL

//    };

    private static final Map<Integer,String> ERRMSGS = new MapBuilder<>()

            .add(ERR_NO_ERROR, "_No_errors")

            .add(ERR_DBFILE_IS_NULL, "_dbfile_is_null")

            .add(ERR_WSEXPLORER_IS_NULL, "_workspace_explorer_is_null")

            .add(ERR_LABEL_IS_NULL, "_label_is_null")

            .add(ERR_CANT_OPEN_WORKSPACE, "_Cant_open_workspace")

            .add(ERR_CANT_OPEN_WORKSPACE_SERVEREXPLORER, "_Cant_open_workspace")

            .add(ERR_STORE_NOT_IN_VERSION_CONTROL, "_Store_not_in_control_version")

            .add(ERR_CANT_CREATE_TABLE, "_Cant_create_table")

            .add(ERR_CANT_CREATE_TABLE_CONFIG, "_Cant_create_config_table")

            .add(ERR_CANT_CREATE_TABLE_ENTITIES, "_Cant_create_table_entities")

            .add(ERR_CANT_CREATE_TABLE_CHANGES, "_Cant_create_table_changes")

            .add(ERR_ENTITY_ALREADY_EXISTS, "_Entity_already_exists")

            .add(ERR_ENTITY_NOT_HAS_VCSGISCODE, "_Entity_has_not_VCSGISCODE")

            .add(ERR_CANT_OPEN_ENTITIES, "_Cant_open_entities")

            .add(ERR_CANT_INSERT_ENTITIES, "_Cant_insert_entities")

            .add(ERR_CANT_OPEN_CHANGES, "_Cant_open_changes")

            .add(ERR_CANT_INSERT_CHANGE, "_Cant_insert_change")

            .add(ERR_CANT_OPEN_STORE, "_Cant_open_store")

            .add(ERR_CANT_RETRIEVE_SOURCE_FEATURES, "_Cant_retrieve_source_features")

            .add(ERR_CANT_INSERT_FEATURES, "_Cant_insert_features")

            .add(ERR_CANT_COMMIT, "_Cant_commit")

            .add(ERR_CANT_COMMIT_WORKSPACE_OUTDATED, "_Cant_commit_workspace_is_out_of_date")

            .add(ERR_CANT_CHECKOUT, "_Cant_checkout")

            .add(ERR_CANT_UPDATE_ENTITIES, "_Cant_update_entities")

            .add(ERR_CANT_PREPARE_UPDATE, "_Cant_prepare_update")

            .add(ERR_CANT_UPDATE, "_Cant_update")

            .add(ERR_CANT_EXECUTE_REQUEST, "_Cant_execute_request")

            .add(ERR_CANT_BUILD_REQUEST_RESPONSE,"_Cant_build_request_response")

            .add(ERR_CANT_HANDLE_REQUEST,"_Cant_handle_request")

            .add(ERR_CANT_UPDATE_CLEAN,"_Cant_update_clean")

            .add(ERR_CANT_COMMIT_WITH_FEATURE_CODE_BLANK,"_Cant_commit_with_feature_code_blank")

            .add(ERR_CANT_ADD_ENTITY_WITHOUT_PRIMARY_KEY,"Cant_add_entity_without_primary_key")

            .add(ERR_CANT_INITIALIZE_REPOSITORY, "_Cant_initialize_repository")

            .add(ERR_INVALID_DATA, "_Data_can_be_empty")

            .add(ERR_INVALID_REPOSITORY, "_Invalid_repository")

            .add(ERR_INVALID_REVISION, "_Invalid_revision")

            .add(ERR_INVALID_REVISION_FOR_ENTITY, "_Invalid_revision_for_entity")

            .add(ERR_CANT_RETRIEVE_ENTITIES, "_Cant_retrieve_entities")

            .add(ERR_CANT_ADD_LAYER, "_Cant_add_layer_to_view")

            .add(ERR_CANT_REMOVE_ENTITY, "_Cant_remove_entity")

            .add(ERR_CANT_REMOVE_CHANGES, "_Cant_remove_change")

            .add(ERR_CANT_AUTHENTICATE_USER, "_Cant_authenticate_user")

            .add(ERR_INVALID_AUTHENTICATION_TOKEN, "_Invalid_authentication_token")

            .add(ERR_USER_NOT_AUTHORIZED, "_User_not_authorized")

            .add(ERR_AUTHENTICATION_EXPIRED,"_Authentication_expired")

            .add(ERR_INVALID_USERCODE, "_Invalid_user_code")

            .add(ERR_CANT_REVERT, "_Cant_revert")

            .build();

    private static final Map<Integer,String> OPERATION_LABELS = new MapBuilder<Integer,String>()

            .add(OP_DELETE, "Delete")

            .add(OP_UPDATE, "Update")

            .add(OP_INSERT, "Insert")

            .add(OP_ADD_ENTITY, "Add entity")

            .build();

    private static final Map<Integer,String> STATE_LABELS = new MapBuilder<Integer,String>()

        .add(STATE_LOCAL_UNMODIFIED, "Unmodified")

        .add(STATE_LOCAL_NEW, "New")

        .add(STATE_LOCAL_MODIFIED, "Modified")

        .add(STATE_LOCAL_OUTDATED, "Outdated")

        .add(STATE_LOCAL_OUTDATED_AND_MODIFIED, "Outdated and modified")

        .add(STATE_CONFLICT, "Conflict")

        .build();

    private static final Map<String, Map<String, String>> SQL_TEMPLATES_BY_PROVIDER = new HashMap<>();

    public static String getErrorMessage(int errcode) {

        String msg = ERRMSGS.get(errcode);

        msg = ToolsLocator.getI18nManager().getTranslation(msg);

        return msg;

    }

    public static void copy(VCSGisEntity source, VCSGisEntityEditable target, Predicate<String> excludes) {

        target.setDataTableName(source.getDataTableName());

        target.setDescription(source.getDescription());

        target.setEntityCode(source.getEntityCode());

        target.setEntityName(source.getEntityName());

        target.setFeatureIdFieldName(source.getFeatureIdFieldName());

        target.setFeatureTypeAsJson(source.getFeatureTypeAsJson());

        target.setFieldForLabel(source.getFieldForLabel());

        target.setGeometryFieldName(source.getGeometryFieldName());

        target.setRepositoryRevisionCode(source.getRepositoryRevisionCode());

        target.setTopologyPlanCode(source.getTopologyPlanCode());

        target.setUserCode(source.getUserCode());

        target.setCategory(source.getCategory());

        target.setLabel(source.getLabel());

        target.setAuthorizations(source.getAuthorizations());

        target.setTopologyPlanMode(source.getTopologyPlanMode());

    }

    public static JsonObjectBuilder toJsonBuilder(VCSGisEntity entity, Predicate<String> excludes) {

        JsonObjectBuilder jsonEntity = Json.createObjectBuilder();

        if( excludes==null || !excludes.test(ENTITY_DATATABLENAME) ) {

            jsonEntity.add(ENTITY_DATATABLENAME, entity.getDataTableName());

        }

        if( excludes==null || !excludes.test(ENTITY_DESCRIPTION) ) {

            jsonEntity.add(ENTITY_DESCRIPTION, entity.getDescription());

        }

        if( excludes==null || !excludes.test(ENTITY_CODE) ) {

            jsonEntity.add(ENTITY_CODE, entity.getEntityCode());

        }

        if( excludes==null || !excludes.test(ENTITY_NAME) ) {

            jsonEntity.add(ENTITY_NAME, entity.getEntityName());

        }

        if( excludes==null || !excludes.test(ENTITY_FEATUREIDFIELDNAME) ) {

            jsonEntity.add(ENTITY_FEATUREIDFIELDNAME, entity.getFeatureIdFieldName());

        }

        if( excludes==null || !excludes.test(ENTITY_FEATURETYPEASJSON) ) {

            jsonEntity.add(ENTITY_FEATURETYPEASJSON, entity.getFeatureTypeAsJson());

        }

        if( excludes==null || !excludes.test(ENTITY_FIELDFORLABEL) ) {

            jsonEntity.add(ENTITY_FIELDFORLABEL, entity.getFieldForLabel());

        }

        if( excludes==null || !excludes.test(ENTITY_GEOMETRYFIELDNAME) ) {

            jsonEntity.add(ENTITY_GEOMETRYFIELDNAME, entity.getGeometryFieldName());

        }

        if( excludes==null || !excludes.test(ENTITY_REVISIONCODE) ) {

            jsonEntity.add(ENTITY_REVISIONCODE, entity.getRepositoryRevisionCode());

        }

        if( excludes==null || !excludes.test(ENTITY_TOPOLOGYPLANCODE) ) {

            jsonEntity.add(ENTITY_TOPOLOGYPLANCODE, entity.getTopologyPlanCode());

        }

        if( excludes==null || !excludes.test(ENTITY_USERCODE) ) {

            jsonEntity.add(ENTITY_USERCODE, entity.getUserCode());

        }

        if( excludes==null || !excludes.test(ENTITY_CATEGORY) ) {

            jsonEntity.add(ENTITY_CATEGORY, entity.getCategory());

        }

        if( excludes==null || !excludes.test(ENTITY_LABEL) ) {

            jsonEntity.add(ENTITY_LABEL, entity.getLabel());

        }

        if( excludes==null || !excludes.test(ENTITY_AUTHORIZATIONS) ) {

            jsonEntity.add(ENTITY_AUTHORIZATIONS, entity.getAuthorizations());

        }

        if( excludes==null || !excludes.test(ENTITY_TOPOLOGYPLANMODE) ) {

            jsonEntity.add(ENTITY_TOPOLOGYPLANMODE, entity.getTopologyPlanMode());

        }

        return jsonEntity;

    }

    public static void fromJson(VCSGisEntityEditable entity, JsonObject jsonEntity) {

        if( jsonEntity.containsKey(ENTITY_DATATABLENAME) ) {

            entity.setDataTableName(jsonEntity.getString(ENTITY_DATATABLENAME));

        }

        if( jsonEntity.containsKey(ENTITY_DESCRIPTION) ) {

            entity.setDescription(jsonEntity.getString(ENTITY_DESCRIPTION, null));

        }

        if( jsonEntity.containsKey(ENTITY_CODE) ) {

            entity.setEntityCode(jsonEntity.getString(ENTITY_CODE, null));

        }

        if( jsonEntity.containsKey(ENTITY_NAME) ) {

            entity.setEntityName(jsonEntity.getString(ENTITY_NAME, null));

        }

        if( jsonEntity.containsKey(ENTITY_FEATUREIDFIELDNAME) ) {

            entity.setFeatureIdFieldName(jsonEntity.getString(ENTITY_FEATUREIDFIELDNAME, null));

        }

        if( jsonEntity.containsKey(ENTITY_FEATURETYPEASJSON) ) {

            entity.setFeatureTypeAsJson(jsonEntity.getString(ENTITY_FEATURETYPEASJSON, null));

        }

        if( jsonEntity.containsKey(ENTITY_FIELDFORLABEL) ) {

            entity.setFieldForLabel(jsonEntity.getString(ENTITY_FIELDFORLABEL, null));

        }

        if( jsonEntity.containsKey(ENTITY_GEOMETRYFIELDNAME) ) {

            entity.setGeometryFieldName(jsonEntity.getString(ENTITY_GEOMETRYFIELDNAME, null));

        }

        if( jsonEntity.containsKey(ENTITY_REVISIONCODE) ) {

            entity.setRepositoryRevisionCode(jsonEntity.getString(ENTITY_REVISIONCODE, null));

        }

        if( jsonEntity.containsKey(ENTITY_TOPOLOGYPLANCODE) ) {

            entity.setTopologyPlanCode(jsonEntity.getString(ENTITY_TOPOLOGYPLANCODE, null));

        }

        if( jsonEntity.containsKey(ENTITY_USERCODE) ) {

            entity.setUserCode(jsonEntity.getString(ENTITY_USERCODE, null));                

        }

        if( jsonEntity.containsKey(ENTITY_CATEGORY) ) {

            entity.setCategory(jsonEntity.getString(ENTITY_CATEGORY, null));                

        }

        if( jsonEntity.containsKey(ENTITY_LABEL) ) {

            entity.setLabel(jsonEntity.getString(ENTITY_LABEL, null));                

        }

        if( jsonEntity.containsKey(ENTITY_AUTHORIZATIONS) ) {

            entity.setAuthorizations(jsonEntity.getString(ENTITY_AUTHORIZATIONS, null));                

        }

        if( jsonEntity.containsKey(ENTITY_TOPOLOGYPLANMODE) ) {

            entity.setTopologyPlanMode(jsonEntity.getInt(ENTITY_TOPOLOGYPLANMODE, TOPOLOGYPLAN_MODE_RECOMMENDED));                

        }

    }

    public static boolean isAuthoriced(VCSGisEntity entity, String operation, String userid) {

        String s = entity.getAuthorizations();

        if( StringUtils.isBlank(s) ) {

            return true;

        }

        JsonObject authorizations = Json.createObject(s);

        if( authorizations==null ) {

            return false;

        }

        if( StringUtils.isBlank(userid) || StringUtils.isBlank(operation)) {

            return false;

        }

        JsonArray op = authorizations.getJsonArray(operation);

        if( op == null ) {

           return false; 

        }

        userid = userid.toLowerCase();

        if( op.contains(userid) ) {

            return true;

        }

        return false;

    }

    public static String getOperationLabel(int operation) {

        return OPERATION_LABELS.getOrDefault(operation, "unknown");

    }

    public static String getStateLabel(int state) {

        return STATE_LABELS.getOrDefault(state, "unknown");

    }

    @SuppressWarnings("null")

    private static Map<String,String> getSqlTemplates(String name) {

      final int SEARCHING_ITEM = 0;

      final int READING_ITEM = 1;

      InputStream sqls = VCSGisUtils.class.getResourceAsStream("/org/gvsig/vcsgis/lib/"+name+".sql"); // org.gvsig.vcsgis.lib

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

      List<String> lines;

        try {

            lines = IOUtils.readLines(sqls);

        } catch (IOException ex) {

            throw new RuntimeException("Can't read sql templates for provider "+name, ex);

        }

      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("@begin ") ) {

                    currentItem = line.substring(7).trim();

                    sb = new StringBuilder();

                    state = READING_ITEM;

                } else if( line.toLowerCase().startsWith("-- ") ) {

                    // 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("@end ") ) {

                    if( !StringUtils.equals(currentItem,line.substring(5).trim()) ) {

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

                    }

                    String s = sb.toString();

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

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

                    }

                    expecteds.put(currentItem, s);

                    state = SEARCHING_ITEM;

                } else if( line.toLowerCase().startsWith("-- ") ) {

                    // do nothing skip comment

                } else {

                    sb.append(line);

                }

                break;

        }

        lineno++;

      }

      if( state!=SEARCHING_ITEM ) {

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

      }

      return expecteds;

    }

    public static String getSqlTemplate(String providerName, String sqlId){

        if(SQL_TEMPLATES_BY_PROVIDER.get(providerName) == null) {

            SQL_TEMPLATES_BY_PROVIDER.put(providerName, getSqlTemplates(providerName));

        }

        return SQL_TEMPLATES_BY_PROVIDER.get(providerName).get(sqlId);

    }

}