Convert the game! wireframe evolution from MySQL to PostgreSQL

Question

Convert the game! wireframe evolution from MySQL to PostgreSQL

I use plaframework 2.2.1, I created a MySQL project, but now I want to transfer my project to PostgreSQL, but with some errors recreating the evolution of the database.

My old evolution (1.sql) for mysql, which worked fine:

# --- Created by Ebean DDL
# To stop Ebean DDL generation, remove this comment and start using Evolutions

# --- !Ups

create table product (
  id                        bigint auto_increment not null,
  name                      varchar(255),
  price                     float,
  constraint pk_product primary key (id))
;

create table shop (
  id                        bigint auto_increment not null,
  name                      varchar(255),
  address_line1             varchar(255),
  address_line2             varchar(255),
  address_line3             varchar(255),
  city                      varchar(255),
  town                      varchar(255),
  phone_number              varchar(255),
  owner_email               varchar(255),
  constraint pk_shop primary key (id))
;

create table user (
  email                     varchar(255) not null,
  password                  varchar(255),
  first_name                varchar(255),
  last_name                 varchar(255),
  constraint pk_user primary key (email))
;


create table product_shop (
  product_id                     bigint not null,
  shop_id                        bigint not null,
  constraint pk_product_shop primary key (product_id, shop_id))
;
alter table shop add constraint fk_shop_owner_1 foreign key (owner_email) references user (email) on delete restrict on update restrict;
create index ix_shop_owner_1 on shop (owner_email);



alter table product_shop add constraint fk_product_shop_product_01 foreign key (product_id) references product (id) on delete restrict on update restrict;

alter table product_shop add constraint fk_product_shop_shop_02 foreign key (shop_id) references shop (id) on delete restrict on update restrict;

# --- !Downs

SET FOREIGN_KEY_CHECKS=0;

drop table product;

drop table product_shop;

drop table shop;

drop table user;

SET FOREIGN_KEY_CHECKS=1;

Then I removed 1.sql and recreated my evolution (1.sql) for postgresql below

 # --- !Ups

create table member (
  email                     varchar(255) PRIMARY KEY,
  password                  varchar(255),
  first_name                varchar(255),
  last_name                 varchar(255)
  )
;

create table product (
  id                        bigserial PRIMARY KEY,
  name                      varchar(255),
  price                     real
  )
;

create table shop (
  id                        bigserial PRIMARY KEY,
  name                      varchar(255),
  address_line1             varchar(255),
  address_line2             varchar(255),
  address_line3             varchar(255),
  city                      varchar(255),
  town                      varchar(255),
  phone_number              varchar(255),
  email                     varchar(255) REFERENCES member
  )
;


create table product_shop (
  product_id                     bigint REFERENCES product ON DELETE RESTRICT,
  shop_id                        bigint REFERENCES shop ON DELETE CASCADE,
  PRIMARY KEY (product_id, shop_id)
  )
;

Is there a difference between two sql?

-, 1.sql 1.sql mysql? , , , mysql, . .

[PersistenceException: Error getting sequence nextval]
In C:\Users\Myproject\app\models\Product.java at line 36.
33
34    public static Product create(String name,float price) {
35        Product product = new Product(name, price);
36        product.save();
37        product.saveManyToManyAssociations("shops");
38        return product;
39    }
40    public static void delete(Long id) {
41        find.ref(id).delete();

, 2.sql PgAdmin III?

+4

sql postgresql ebean playframework playframework-evolutions

akku 01 . '14 17:44

1

Fernando Correia · Accepted Answer · 2014-04-02T13:24:01+0000

, .

, MySQL ( ), :

1.sql. , , , , : - .
: id bigserial primary key constraint.
, PostgreSQL. (dropdb, createdb).
, , . . .
PgAdmin , Toad Extension Eclipse, , .

, Flyway . Play Framework.

Error getting sequence nextval, :

@Id
@SequenceGenerator(name="product_gen", sequenceName="product_id_seq", allocationSize=1)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="product_gen")
@Column(name="id")
public Long getId() { return id; }

, , sequenceName - , PostgreSQL.

.

Convert the game! wireframe evolution from MySQL to PostgreSQL

More articles: