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 v9 specific platform.
023 * <p>
024 * Uses serial types and getGeneratedKeys.
025 * </p>
026 */
027public class PostgresPlatform extends DatabasePlatform {
028
029  public PostgresPlatform() {
030    super();
031    this.platform = Platform.POSTGRES;
032    this.supportsNativeIlike = true;
033    this.selectCountWithAlias = true;
034    this.blobDbType = Types.LONGVARBINARY;
035    this.clobDbType = Types.VARCHAR;
036    this.nativeUuidType = true;
037    this.columnAliasPrefix = null;
038
039    this.dbEncrypt = new PostgresDbEncrypt();
040    this.historySupport = new PostgresHistorySupport();
041
042    // Use Identity and getGeneratedKeys
043    this.dbIdentity.setIdType(IdType.IDENTITY);
044    this.dbIdentity.setSupportsGetGeneratedKeys(true);
045    this.dbIdentity.setSupportsSequence(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        .build();
055
056    this.openQuote = "\"";
057    this.closeQuote = "\"";
058
059    DbPlatformType dbTypeText = new DbPlatformType("text", false);
060    DbPlatformType dbBytea = new DbPlatformType("bytea", false);
061
062    dbTypeMap.put(DbType.UUID, new DbPlatformType("uuid", false));
063    dbTypeMap.put(DbType.INET, new DbPlatformType("inet", false));
064    dbTypeMap.put(DbType.HSTORE, new DbPlatformType("hstore", false));
065    dbTypeMap.put(DbType.JSON, new DbPlatformType("json", false));
066    dbTypeMap.put(DbType.JSONB, new DbPlatformType("jsonb", false));
067
068    dbTypeMap.put(DbType.INTEGER, new DbPlatformType("integer", false));
069    dbTypeMap.put(DbType.DOUBLE, new DbPlatformType("float"));
070    dbTypeMap.put(DbType.TINYINT, new DbPlatformType("smallint"));
071    dbTypeMap.put(DbType.DECIMAL, new DbPlatformType("decimal", 38));
072    dbTypeMap.put(DbType.TIMESTAMP, new DbPlatformType("timestamptz"));
073
074    dbTypeMap.put(DbType.BINARY, dbBytea);
075    dbTypeMap.put(DbType.VARBINARY, dbBytea);
076
077    dbTypeMap.put(DbType.BLOB, dbBytea);
078    dbTypeMap.put(DbType.CLOB, dbTypeText);
079    dbTypeMap.put(DbType.LONGVARBINARY, dbBytea);
080    dbTypeMap.put(DbType.LONGVARCHAR, dbTypeText);
081  }
082
083  @Override
084  protected void addGeoTypes(int srid) {
085    dbTypeMap.put(DbType.POINT, geoType("point", srid));
086    dbTypeMap.put(DbType.POLYGON, geoType("polygon", srid));
087    dbTypeMap.put(DbType.LINESTRING, geoType("linestring", srid));
088    dbTypeMap.put(DbType.MULTIPOINT, geoType("multipoint", srid));
089    dbTypeMap.put(DbType.MULTILINESTRING, geoType("multilinestring", srid));
090    dbTypeMap.put(DbType.MULTIPOLYGON, geoType("multipolygon", srid));
091  }
092
093  private DbPlatformType geoType(String type, int srid) {
094    return new DbPlatformType("geometry(" + type + "," + srid + ")");
095  }
096
097  /**
098   * So we can generate varchar[], int[], uuid[] column definitions and use the associated scalar types.
099   */
100  @Override
101  public boolean isNativeArrayType() {
102    return true;
103  }
104
105  /**
106   * Create a Postgres specific sequence IdGenerator.
107   */
108  @Override
109  public PlatformIdGenerator createSequenceIdGenerator(BackgroundExecutor be, DataSource ds, int stepSize, String seqName) {
110
111    return new PostgresSequenceIdGenerator(be, ds, seqName, sequenceBatchSize);
112  }
113
114  @Override
115  protected String withForUpdate(String sql, Query.ForUpdate forUpdateMode) {
116    switch (forUpdateMode) {
117      case SKIPLOCKED:
118        return sql + " for update skip locked";
119      case NOWAIT:
120        return sql + " for update nowait";
121      default:
122        return sql + " for update";
123    }
124  }
125
126  @Override
127  public boolean tablePartitionsExist(Connection connection, String table) throws SQLException {
128
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   *
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    if (property == null) {
145      property = "";
146    }
147    if (pkey == null) {
148      pkey = "";
149    }
150    return "select partition('" + mode.name().toLowerCase() + "','" + tableName + "','" + pkey + "','" + property + "',1);";
151  }
152
153}