001package io.ebean.config.dbplatform.postgres;
002
003import io.ebean.BackgroundExecutor;
004import io.ebean.Query;
005import io.ebean.annotation.PartitionMode;
006import io.ebean.annotation.Platform;
007import io.ebean.config.PlatformConfig;
008import io.ebean.config.dbplatform.DatabasePlatform;
009import io.ebean.config.dbplatform.DbPlatformType;
010import io.ebean.config.dbplatform.DbType;
011import io.ebean.config.dbplatform.IdType;
012import io.ebean.config.dbplatform.PlatformIdGenerator;
013import io.ebean.config.dbplatform.SqlErrorCodes;
014
015import javax.sql.DataSource;
016import java.sql.Connection;
017import java.sql.PreparedStatement;
018import java.sql.ResultSet;
019import java.sql.SQLException;
020import java.sql.Types;
021
022/**
023 * Postgres 10+ platform.
024 * <p>
025 * Defaults to use "generated by default as identity".
026 */
027public class PostgresPlatform extends DatabasePlatform {
028
029  private static final String SKIP_LOCKED = " skip locked";
030  private static final String NO_WAIT = " nowait";
031  private static final String FOR_UPDATE = " for update";
032  private static final String FOR_NO_KEY_UPDATE = " for no key update";
033  private static final String FOR_SHARE = " for share";
034  private static final String FOR_KEY_SHARE = " for key share";
035
036  private boolean forUpdateNoKey;
037
038  public PostgresPlatform() {
039    super();
040    this.platform = Platform.POSTGRES;
041    this.supportsNativeIlike = true;
042    this.supportsDeleteTableAlias = true;
043    this.selectCountWithAlias = true;
044    this.blobDbType = Types.LONGVARBINARY;
045    this.clobDbType = Types.VARCHAR;
046    this.nativeUuidType = true;
047    this.truncateTable = "truncate table %s cascade";
048    this.dbEncrypt = new PostgresDbEncrypt();
049    this.historySupport = new PostgresHistorySupport();
050
051    // Use Identity and getGeneratedKeys
052    this.dbIdentity.setIdType(IdType.IDENTITY);
053    this.dbIdentity.setSupportsGetGeneratedKeys(true);
054    this.dbIdentity.setSupportsSequence(true);
055    this.dbIdentity.setSupportsIdentity(true);
056
057    this.dbDefaultValue.setNow("current_timestamp");
058
059    this.exceptionTranslator =
060      new SqlErrorCodes()
061        .addAcquireLock("55P03")
062        .addDuplicateKey("23505")
063        .addDataIntegrity("23000", "23502", "23503", "23514")
064        .addSerializableConflict("40001")
065        .build();
066
067    this.openQuote = "\"";
068    this.closeQuote = "\"";
069
070    DbPlatformType dbTypeText = new DbPlatformType("text", false);
071    DbPlatformType dbBytea = new DbPlatformType("bytea", false);
072
073    dbTypeMap.put(DbType.UUID, new DbPlatformType("uuid", false));
074    dbTypeMap.put(DbType.INET, new DbPlatformType("inet", false));
075    dbTypeMap.put(DbType.CIDR, new DbPlatformType("cidr", false));
076    dbTypeMap.put(DbType.HSTORE, new DbPlatformType("hstore", false));
077    dbTypeMap.put(DbType.JSON, new DbPlatformType("json", false));
078    dbTypeMap.put(DbType.JSONB, new DbPlatformType("jsonb", false));
079
080    dbTypeMap.put(DbType.INTEGER, new DbPlatformType("integer", false));
081    dbTypeMap.put(DbType.DOUBLE, new DbPlatformType("float"));
082    dbTypeMap.put(DbType.TINYINT, new DbPlatformType("smallint"));
083    dbTypeMap.put(DbType.TIMESTAMP, new DbPlatformType("timestamptz"));
084
085    dbTypeMap.put(DbType.BINARY, dbBytea);
086    dbTypeMap.put(DbType.VARBINARY, dbBytea);
087
088    dbTypeMap.put(DbType.BLOB, dbBytea);
089    dbTypeMap.put(DbType.CLOB, dbTypeText);
090    dbTypeMap.put(DbType.LONGVARBINARY, dbBytea);
091    dbTypeMap.put(DbType.LONGVARCHAR, dbTypeText);
092  }
093
094  @Override
095  public void configure(PlatformConfig config) {
096    super.configure(config);
097    forUpdateNoKey = config.isForUpdateNoKey();
098  }
099
100  @Override
101  protected void addGeoTypes(int srid) {
102    dbTypeMap.put(DbType.POINT, geoType("point", srid));
103    dbTypeMap.put(DbType.POLYGON, geoType("polygon", srid));
104    dbTypeMap.put(DbType.LINESTRING, geoType("linestring", srid));
105    dbTypeMap.put(DbType.MULTIPOINT, geoType("multipoint", srid));
106    dbTypeMap.put(DbType.MULTILINESTRING, geoType("multilinestring", srid));
107    dbTypeMap.put(DbType.MULTIPOLYGON, geoType("multipolygon", srid));
108  }
109
110  private DbPlatformType geoType(String type, int srid) {
111    return new DbPlatformType("geometry(" + type + "," + srid + ")");
112  }
113
114  /**
115   * So we can generate varchar[], int[], uuid[] column definitions and use the associated scalar types.
116   */
117  @Override
118  public boolean isNativeArrayType() {
119    return true;
120  }
121
122  /**
123   * Create a Postgres specific sequence IdGenerator.
124   */
125  @Override
126  public PlatformIdGenerator createSequenceIdGenerator(BackgroundExecutor be, DataSource ds, int stepSize, String seqName) {
127    return new PostgresSequenceIdGenerator(be, ds, seqName, sequenceBatchSize);
128  }
129
130  @Override
131  protected String withForUpdate(String sql, Query.LockWait lockWait, Query.LockType lockType) {
132    switch (lockWait) {
133      case SKIPLOCKED:
134        return sql + lock(lockType) + SKIP_LOCKED;
135      case NOWAIT:
136        return sql + lock(lockType) + NO_WAIT;
137      default:
138        return sql + lock(lockType);
139    }
140  }
141
142  private String lock(Query.LockType lockType) {
143    switch (lockType) {
144      case UPDATE: return FOR_UPDATE;
145      case NO_KEY_UPDATE: return FOR_NO_KEY_UPDATE;
146      case SHARE: return FOR_SHARE;
147      case KEY_SHARE: return FOR_KEY_SHARE;
148      case DEFAULT: return forUpdateNoKey ? FOR_NO_KEY_UPDATE : FOR_UPDATE;
149    }
150    return FOR_UPDATE;
151  }
152
153  @Override
154  public boolean tablePartitionsExist(Connection connection, String table) throws SQLException {
155    try (PreparedStatement statement = connection.prepareStatement("select count(*) from pg_inherits i WHERE  i.inhparent = ?::regclass")) {
156      statement.setString(1, table);
157      try (ResultSet resultSet = statement.executeQuery()) {
158        return resultSet.next() && resultSet.getInt(1) > 0;
159      }
160    }
161  }
162
163  /**
164   * Return SQL using built in partition helper functions to create some initial partitions.
165   * <p>
166   * Only use this if extra-ddl doesn't have some initial partitions defined (which it should).
167   */
168  @Override
169  public String tablePartitionInit(String tableName, PartitionMode mode, String property, String pkey) {
170    // default partition required pg11 but this is only used for testing but bumped test docker container to pg11 by default
171    return
172      "create table " + tableName + "_default" + " partition of " + tableName + " default;\n" +
173        "select partition('" + mode.name().toLowerCase() + "','" + tableName + "',1);";
174  }
175
176}