Converting Play! framework evolution from MySQL to PostgreSQL

Question

I am using plaframework 2.2.1, I had made a project MySQL but now i want to shift my project to PostgreSQL but having some errors recreating the DB evolution.

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

# --- 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 have deleted 1.sql and recreated my evolution(1.sql) for postgresql given 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 any difference between the two sql?

Do i need to add something to make my new 1.sql function equal to that of my old 1.sql in my mysql evolution? My new evolution created my database but when i am trying to insert values in my shop table it shows same page and its not working same as it worked with mysql,means not loading the next page. When inserting in product table it shows this.

[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();

I also cant find my database created by 2.sql in PgAdmin III?

Answer 1

Make sure the database is in a consistent state.

Assuming you have not migrated the data from the previous MySQL database and that you're working in development mode (not in production mode), so you don't have to worry about preserving data:

• Rename your migration back to 1.sql. Just because you executed the migration in a previous database, it doesn't mean that when you're going to execute it in a completely new database it's a second evolution: for the new database, it's still the first one.
• Declare your primary key columns like this: id bigserial primary key and remove the constraint.
• Make sure you have an empty database in PostgreSQL. Drop the database and recreate it (dropdb, createdb).
• Run your database migration and watch the output to make sure the migration was executed. See Managing database evolutions.
• Use PgAdmin or a similar tool such as Toad Extension for Eclipse to verify that the database structure was correctly created.

Alternatively, you may find out that Flyway provides a more comprehensive approach to database migrations. There is a plugin for Play Framework.

To avoid the exception Error getting sequence nextval, properly annotate the entity class definitions like this:

@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; }

Inspect the database to make sure that sequenceName is the name of the sequence created by PostgreSQL.

For more information see:

• Ebean looks for wrong sequence name in Play Framework 2
• Configure JPA to let PostgreSQL generate the primary key value
• EJB3 JPA: 3 Ways Of Generating Primary Key Through @GeneratedValue
• PersistenceException: Error getting sequence nextval
• Play Framework: Error getting sequence nextval using H2 in-memory database
• Annotation Type GeneratedValue