001/**
002 * Copyright (c) 2015-2022, Michael Yang 杨福海 (fuhai999@gmail.com).
003 * <p>
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 * <p>
008 * http://www.apache.org/licenses/LICENSE-2.0
009 * <p>
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016package io.jboot.db.dialect;
017
018import com.jfinal.plugin.activerecord.Model;
019import com.jfinal.plugin.activerecord.Record;
020import com.jfinal.plugin.activerecord.Table;
021import com.jfinal.plugin.activerecord.dialect.AnsiSqlDialect;
022import io.jboot.db.model.*;
023import io.jboot.utils.StrUtil;
024
025import java.sql.PreparedStatement;
026import java.sql.SQLException;
027import java.util.List;
028import java.util.Map;
029import java.util.Set;
030
031
032public class JbootClickHouseDialect extends AnsiSqlDialect implements JbootDialect {
033
034    @Override
035    public void getModelGeneratedKey(Model<?> model, PreparedStatement pst, Table table) throws SQLException {
036        // doNothing() ; clickhouse 不支持生成主键
037    }
038
039
040    @Override
041    public String forDbDeleteById(String tableName, String[] pKeys) {
042        tableName = tableName.trim();
043        trimPrimaryKeys(pKeys);
044        StringBuilder sql = new StringBuilder("ALTER TABLE ").append(tableName).append(" DELETE WHERE ");
045        for (int i = 0; i < pKeys.length; i++) {
046            if (i > 0) {
047                sql.append(" AND ");
048            }
049            sql.append(pKeys[i]).append(" = ?");
050        }
051        return sql.toString();
052    }
053
054
055    @Override
056    public String forModelDeleteById(Table table) {
057        String[] pKeys = table.getPrimaryKey();
058        StringBuilder sql = new StringBuilder(45);
059        sql.append("ALTER TABLE ");
060        sql.append(table.getName());
061        sql.append(" DELETE WHERE ");
062        for (int i = 0; i < pKeys.length; i++) {
063            if (i > 0) {
064                sql.append(" AND ");
065            }
066            sql.append(pKeys[i]).append(" = ?");
067        }
068        return sql.toString();
069    }
070
071
072    @Override
073    public void forDbUpdate(String tableName, String[] pKeys, Object[] ids, Record record, StringBuilder sql, List<Object> paras) {
074        tableName = tableName.trim();
075        trimPrimaryKeys(pKeys);
076
077        sql.append("ALTER TABLE ").append(tableName).append(" UPDATE ");
078        for (Map.Entry<String, Object> e : record.getColumns().entrySet()) {
079            String colName = e.getKey();
080            if (!isPrimaryKey(colName, pKeys)) {
081                if (paras.size() > 0) {
082                    sql.append(", ");
083                }
084                sql.append(colName).append(" = ? ");
085                paras.add(e.getValue());
086            }
087        }
088        sql.append(" WHERE ");
089        for (int i = 0; i < pKeys.length; i++) {
090            if (i > 0) {
091                sql.append(" AND ");
092            }
093            sql.append(pKeys[i]).append(" = ?");
094            paras.add(ids[i]);
095        }
096    }
097
098    @Override
099    public void forModelUpdate(Table table, Map<String, Object> attrs, Set<String> modifyFlag, StringBuilder sql, List<Object> paras) {
100        sql.append("ALTER TABLE ").append(table.getName()).append(" UPDATE ");
101        String[] pKeys = table.getPrimaryKey();
102        for (Map.Entry<String, Object> e : attrs.entrySet()) {
103            String colName = e.getKey();
104            if (modifyFlag.contains(colName) && !isPrimaryKey(colName, pKeys) && table.hasColumnLabel(colName)) {
105                if (paras.size() > 0) {
106                    sql.append(", ");
107                }
108                sql.append(colName).append(" = ? ");
109                paras.add(e.getValue());
110            }
111        }
112        sql.append(" WHERE ");
113        for (int i = 0; i < pKeys.length; i++) {
114            if (i > 0) {
115                sql.append(" AND ");
116            }
117            sql.append(pKeys[i]).append(" = ?");
118            paras.add(attrs.get(pKeys[i]));
119        }
120    }
121
122
123    @Override
124    public String forFindByColumns(String alias, List<Join> joins, String table, String loadColumns, List<Column> columns, String orderBy, Object limit) {
125        StringBuilder sqlBuilder = SqlBuilder.forFindByColumns(alias, joins, table, loadColumns, columns, orderBy, ' ');
126
127        if (limit != null) {
128            sqlBuilder.append(" LIMIT " + limit);
129        }
130
131        return sqlBuilder.toString();
132    }
133
134
135    @Override
136    public String forFindCountByColumns(String alias, List<Join> joins, String table, String loadColumns, List<Column> columns) {
137        return SqlBuilder.forFindCountByColumns(alias, joins, table, loadColumns, columns, ' ');
138    }
139
140
141    @Override
142    public String forDeleteByColumns(String alias, List<Join> joins, String table, List<Column> columns) {
143        StringBuilder sqlBuilder = new StringBuilder(45);
144        sqlBuilder.append("ALTER TABLE ")
145                .append(table)
146                .append(" DELETE ");
147
148//        SqlBuilder.buildAlias(sqlBuilder, alias);
149        SqlBuilder.buildJoinSql(sqlBuilder, joins, ' ');
150        SqlBuilder.buildWhereSql(sqlBuilder, columns, ' ');
151
152        return sqlBuilder.toString();
153    }
154
155
156    @Override
157    public String forPaginateSelect(String loadColumns) {
158        return "SELECT " + loadColumns;
159    }
160
161
162    @Override
163    public String forPaginateFrom(String alias, List<Join> joins, String table, List<Column> columns, String orderBy) {
164        return SqlBuilder.forPaginateFrom(alias, joins, table, columns, orderBy, ' ');
165    }
166
167    @Override
168    public String forPaginateTotalRow(String select, String sqlExceptSelect, Object ext) {
169        if (ext instanceof Model) {
170            if (io.jboot.db.model.CPI.hasAnyJoinEffective((JbootModel) ext)) {
171                String distinct = JbootModelExts.getDistinctColumn((JbootModel) ext);
172                if (StrUtil.isNotBlank(distinct)) {
173                    return "SELECT count(DISTINCT " + distinct + ") " + replaceOrderBy(sqlExceptSelect);
174                }
175            } else {
176                String[] primaryKeys = com.jfinal.plugin.activerecord.CPI.getTable((Model) ext).getPrimaryKey();
177                if (primaryKeys != null && primaryKeys.length == 1) {
178                    return "select count(" + primaryKeys[0] + ") " + replaceOrderBy(sqlExceptSelect);
179                }
180            }
181        }
182
183
184        //return "select count(*) " + replaceOrderBy(sqlExceptSelect);
185        return super.forPaginateTotalRow(select, sqlExceptSelect, ext);
186    }
187}