content: package cn.datax.common.database.dialect;
  
  import cn.datax.common.database.core.DbColumn;
  import cn.datax.common.database.core.DbTable;
  import cn.datax.common.database.exception.DataQueryException;
  import org.springframework.jdbc.core.RowMapper;
  import org.springframework.util.StringUtils;
  
  import java.sql.ResultSet;
  
  /**
   * SQLServer 2005 数据库方言
   *
   * @author AllDataDC
   * @date 2022-11-14
   */
  public class SQLServer2008Dialect extends AbstractDbDialect {
  
      @Override
      public String columns(String dbName, String tableName) {
          return "select columns.name AS colName, columns.column_id AS COLPOSITION, columns.max_length AS DATALENGTH, columns.precision AS DATAPRECISION, columns.scale AS DATASCALE, " +
                  "columns.is_nullable AS NULLABLE, types.name AS DATATYPE, CAST(ep.value  AS NVARCHAR(128)) AS COLCOMMENT, e.text AS DATADEFAULT, " +
                  "(select top 1 ind.is_primary_key from sys.index_columns ic left join sys.indexes ind on ic.object_id = ind.object_id and ic.index_id = ind.index_id and ind.name like 'PK_%' where ic.object_id=columns.object_id and ic.column_id=columns.column_id) AS COLKEY " +
                  "from sys.columns columns LEFT JOIN sys.types types ON columns.system_type_id = types.system_type_id " +
                  "LEFT JOIN syscomments e ON columns.default_object_id= e.id " +
                  "LEFT JOIN sys.extended_properties ep ON ep.major_id = columns.object_id AND ep.minor_id = columns.column_id AND ep.name = 'MS_Description' " +
                  "where columns.object_id = object_id('" + tableName + "') order by columns.column_id ";
      }
  
      @Override
      public String tables(String dbName) {
          return "select tables.name AS TABLENAME, CAST(ep.value AS NVARCHAR(128)) AS TABLECOMMENT " +
                  "from sys.tables tables LEFT JOIN sys.extended_properties ep ON ep.major_id = tables.object_id AND ep.minor_id = 0";
      }
  
      private static String getOrderByPart(String sql) {
          String loweredString = sql.toLowerCase();
          int orderByIndex = loweredString.indexOf("order by");
          if (orderByIndex != -1) {
              return sql.substring(orderByIndex);
          } else {
              return "";
          }
      }
  
      @Override
      public String buildPaginationSql(String originalSql, long offset, long count) {
          StringBuilder pagingBuilder = new StringBuilder();
          String orderby = getOrderByPart(originalSql);
          String distinctStr = "";
  
          String loweredString = originalSql.toLowerCase();
          String sqlPartString = originalSql;
          if (loweredString.trim().startsWith("select")) {
              int index = 6;
              if (loweredString.startsWith("select distinct")) {
                  distinctStr = "DISTINCT ";
                  index = 15;
              }
              sqlPartString = sqlPartString.substring(index);
          }
          pagingBuilder.append(sqlPartString);
  
          // if no ORDER BY is specified use fake ORDER BY field to avoid errors
          if (StringUtils.isEmpty(orderby)) {
              orderby = "ORDER BY CURRENT_TIMESTAMP";
          }
          StringBuilder sql = new StringBuilder();
          sql.append("WITH selectTemp AS (SELECT ").append(distinctStr).append("TOP 100 PERCENT ")
                  .append(" ROW_NUMBER() OVER (").append(orderby).append(") as __row_number__, ").append(pagingBuilder)
                  .append(") SELECT * FROM selectTemp WHERE __row_number__ BETWEEN ")
                  //FIX#299：原因：mysql中limit 10(offset,size) 是从第10开始（不包含10）,；而这里用的BETWEEN是两边都包含，所以改为offset+1
                  .append(offset + 1)
                  .append(" AND ")
                  .append(offset + count).append(" ORDER BY __row_number__");
          return sql.toString();
      }
  
      @Override
      public RowMapper<DbColumn> columnMapper() {
          return (ResultSet rs, int rowNum) -> {
              DbColumn entity = new DbColumn();
              entity.setColName(rs.getString("COLNAME"));
              entity.setDataType(rs.getString("DATATYPE"));
              entity.setDataLength(rs.getString("DATALENGTH"));
              entity.setDataPrecision(rs.getString("DATAPRECISION"));
              entity.setDataScale(rs.getString("DATASCALE"));
              entity.setColKey("1".equals(rs.getString("COLKEY")) ? true : false);
              entity.setNullable("1".equals(rs.getString("NULLABLE")) ? true : false);
              entity.setColPosition(rs.getInt("COLPOSITION"));
              entity.setDataDefault(rs.getString("DATADEFAULT"));
              entity.setColComment(rs.getString("COLCOMMENT"));
              return entity;
          };
      }
  
      @Override
      public RowMapper<DbTable> tableMapper() {
          return (ResultSet rs, int rowNum) -> {
              DbTable entity = new DbTable();
              entity.setTableName(rs.getString("TABLENAME"));
              entity.setTableComment(rs.getString("TABLECOMMENT"));
              return entity;
          };
      }
  }
  