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    dbTypeMap.put(DbType.UUID, new DbPlatformType("uuid", false));
073    dbTypeMap.put(DbType.INET, new DbPlatformType("inet", false));
074    dbTypeMap.put(DbType.CIDR, new DbPlatformType("cidr", false));
075    dbTypeMap.put(DbType.HSTORE, new DbPlatformType("hstore", false));
076    dbTypeMap.put(DbType.JSON, new DbPlatformType("json", false));
077    dbTypeMap.put(DbType.JSONB, new DbPlatformType("jsonb", false));
078    dbTypeMap.put(DbType.INTEGER, new DbPlatformType("integer", false));
079    dbTypeMap.put(DbType.DOUBLE, new DbPlatformType("float"));
080    dbTypeMap.put(DbType.TINYINT, new DbPlatformType("smallint"));
081    dbTypeMap.put(DbType.TIMESTAMP, new DbPlatformType("timestamptz"));
082    dbTypeMap.put(DbType.LOCALDATETIME, new DbPlatformType("timestamp"));
083    dbTypeMap.put(DbType.BINARY, dbBytea);
084    dbTypeMap.put(DbType.VARBINARY, dbBytea);
085    dbTypeMap.put(DbType.BLOB, dbBytea);
086    dbTypeMap.put(DbType.CLOB, dbTypeText);
087    dbTypeMap.put(DbType.LONGVARBINARY, dbBytea);
088    dbTypeMap.put(DbType.LONGVARCHAR, dbTypeText);
089  }
090
091  @Override
092  public void configure(PlatformConfig config) {
093    super.configure(config);
094    forUpdateNoKey = config.isForUpdateNoKey();
095  }
096
097  @Override
098  protected void addGeoTypes(int srid) {
099    dbTypeMap.put(DbType.POINT, geoType("point", srid));
100    dbTypeMap.put(DbType.POLYGON, geoType("polygon", srid));
101    dbTypeMap.put(DbType.LINESTRING, geoType("linestring", srid));
102    dbTypeMap.put(DbType.MULTIPOINT, geoType("multipoint", srid));
103    dbTypeMap.put(DbType.MULTILINESTRING, geoType("multilinestring", srid));
104    dbTypeMap.put(DbType.MULTIPOLYGON, geoType("multipolygon", srid));
105  }
106
107  private DbPlatformType geoType(String type, int srid) {
108    return new DbPlatformType("geometry(" + type + "," + srid + ")");
109  }
110
111  /**
112   * So we can generate varchar[], int[], uuid[] column definitions and use the associated scalar types.
113   */
114  @Override
115  public boolean isNativeArrayType() {
116    return true;
117  }
118
119  /**
120   * Create a Postgres specific sequence IdGenerator.
121   */
122  @Override
123  public PlatformIdGenerator createSequenceIdGenerator(BackgroundExecutor be, DataSource ds, int stepSize, String seqName) {
124    return new PostgresSequenceIdGenerator(be, ds, seqName, sequenceBatchSize);
125  }
126
127  @Override
128  protected String withForUpdate(String sql, Query.LockWait lockWait, Query.LockType lockType) {
129    switch (lockWait) {
130      case SKIPLOCKED:
131        return sql + lock(lockType) + SKIP_LOCKED;
132      case NOWAIT:
133        return sql + lock(lockType) + NO_WAIT;
134      default:
135        return sql + lock(lockType);
136    }
137  }
138
139  private String lock(Query.LockType lockType) {
140    switch (lockType) {
141      case UPDATE: return FOR_UPDATE;
142      case NO_KEY_UPDATE: return FOR_NO_KEY_UPDATE;
143      case SHARE: return FOR_SHARE;
144      case KEY_SHARE: return FOR_KEY_SHARE;
145      case DEFAULT: return forUpdateNoKey ? FOR_NO_KEY_UPDATE : FOR_UPDATE;
146    }
147    return FOR_UPDATE;
148  }
149
150  @Override
151  public boolean tablePartitionsExist(Connection connection, String table) throws SQLException {
152    try (PreparedStatement statement = connection.prepareStatement("select count(*) from pg_inherits i WHERE  i.inhparent = ?::regclass")) {
153      statement.setString(1, table);
154      try (ResultSet resultSet = statement.executeQuery()) {
155        return resultSet.next() && resultSet.getInt(1) > 0;
156      }
157    }
158  }
159
160  /**
161   * Return SQL using built in partition helper functions to create some initial partitions.
162   * <p>
163   * Only use this if extra-ddl doesn't have some initial partitions defined (which it should).
164   */
165  @Override
166  public String tablePartitionInit(String tableName, PartitionMode mode, String property, String pkey) {
167    // default partition required pg11 but this is only used for testing but bumped test docker container to pg11 by default
168    return
169      "create table " + tableName + "_default" + " partition of " + tableName + " default;\n" +
170        "select partition('" + mode.name().toLowerCase() + "','" + tableName + "',1);";
171  }
172
173}