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