DatabaseUpdateDaoTest.java

/////////////////////////////////////////////////////////////////////////////
//
// Project ProjectForge Community Edition
//         www.projectforge.org
//
// Copyright (C) 2001-2014 Kai Reinhard (k.reinhard@micromata.de)
//
// ProjectForge is dual-licensed.
//
// This community edition is free software; you can redistribute it and/or
// modify it under the terms of the GNU General Public License as published
// by the Free Software Foundation; version 3 of the License.
//
// This community edition is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
// Public License for more details.
//
// You should have received a copy of the GNU General Public License along
// with this program; if not, see http://www.gnu.org/licenses/.
//
/////////////////////////////////////////////////////////////////////////////

package org.projectforge.database;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;

import javax.sql.DataSource;

import org.junit.Test;
import org.projectforge.address.AddressDO;
import org.projectforge.common.DatabaseDialect;
import org.projectforge.continuousdb.DatabaseSupport;
import org.projectforge.continuousdb.Table;
import org.projectforge.continuousdb.TableAttribute;
import org.projectforge.continuousdb.TableAttributeType;
import org.projectforge.continuousdb.UpdaterConfiguration;
import org.projectforge.test.TestBase;
import org.springframework.jdbc.core.JdbcTemplate;

public class DatabaseUpdateDaoTest extends TestBase
{
  private MyDatabaseUpdater myDatabaseUpdater;

  private DataSource dataSource;

  @Test
  public void createTableScript()
  {
    final Table table = new Table("t_test") //
    .addAttribute(new TableAttribute("pk", TableAttributeType.INT).setPrimaryKey(true).setGenerated(true))//
    .addAttribute(new TableAttribute("counter", TableAttributeType.INT)) //
    .addAttribute(new TableAttribute("money", TableAttributeType.DECIMAL, 8, 2).setNullable(false)) //
    .addAttribute(new TableAttribute("address_fk", TableAttributeType.INT).setForeignTable(AddressDO.class));
    final UpdaterConfiguration config = new UpdaterConfiguration();
    config.setDialect(DatabaseDialect.HSQL);
    final MyDatabaseUpdateDao dao = new MyDatabaseUpdateDao(config);
    StringBuffer buf = new StringBuffer();
    dao.buildCreateTableStatement(buf, table);
    assertEquals("CREATE TABLE t_test (\n" //
        + "  pk INT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,\n" //
        + "  counter INT,\n" //
        + "  money DECIMAL(8, 2) NOT NULL,\n" //
        + "  address_fk INT,\n" //
        + "  FOREIGN KEY (address_fk) REFERENCES T_ADDRESS(pk)\n" //
        + ");\n", buf.toString());
    config.setDialect(DatabaseDialect.PostgreSQL);
    buf = new StringBuffer();
    dao.buildCreateTableStatement(buf, table);
    assertEquals("CREATE TABLE t_test (\n" //
        + "  pk INT4,\n" //
        + "  counter INT4,\n" //
        + "  money DECIMAL(8, 2) NOT NULL,\n" //
        + "  address_fk INT4,\n" //
        + "  PRIMARY KEY (pk),\n" //
        + "  FOREIGN KEY (address_fk) REFERENCES T_ADDRESS(pk)\n" //
        + ");\n", buf.toString());
  }

  @Test
  public void createAndDropTable()
  {
    logon(ADMIN);
    final Table table = new Table("t_test") //
    .addAttribute(new TableAttribute("name", TableAttributeType.VARCHAR, 5).setPrimaryKey(true))//
    .addAttribute(new TableAttribute("counter", TableAttributeType.INT)) //
    .addAttribute(new TableAttribute("money", TableAttributeType.DECIMAL, 8, 2).setNullable(false)) //
    .addAttribute(new TableAttribute("address_fk", TableAttributeType.INT).setForeignTable("t_address").setForeignAttribute("pk"));
    final StringBuffer buf = new StringBuffer();
    final MyDatabaseUpdateDao databaseUpdateDao = myDatabaseUpdater.getDatabaseUpdateDao();
    databaseUpdateDao.buildCreateTableStatement(buf, table);
    assertEquals("CREATE TABLE t_test (\n" //
        + "  name VARCHAR(5),\n" //
        + "  counter INT,\n" //
        + "  money DECIMAL(8, 2) NOT NULL,\n" //
        + "  address_fk INT,\n" //
        + "  PRIMARY KEY (name),\n" //
        + "  FOREIGN KEY (address_fk) REFERENCES t_address(pk)\n" //
        + ");\n", buf.toString());
    assertTrue(databaseUpdateDao.createTable(table));
    assertTrue(databaseUpdateDao.doesTableExist("t_test"));
    assertTrue(databaseUpdateDao.dropTable("t_test"));
    assertTrue(databaseUpdateDao.dropTable("t_test"));
    assertTrue(databaseUpdateDao.createTable(table));
    final JdbcTemplate jdbc = new JdbcTemplate(dataSource);
    jdbc.execute("INSERT INTO t_test (name, counter, money) VALUES('test', 5, 5.12);");
    assertFalse("Data base is not empty!", databaseUpdateDao.dropTable("t_test"));
    jdbc.execute("DELETE FROM t_test;");
  }

  @Test
  public void buildAddUniqueConstraint()
  {
    final StringBuffer buf = new StringBuffer();
    final MyDatabaseUpdateDao databaseUpdateDao = myDatabaseUpdater.getDatabaseUpdateDao();
    databaseUpdateDao.buildAddUniqueConstraintStatement(buf, "t_user_right", "t_user_right_user_fk_key", "user_fk", "right_id");
    assertEquals("ALTER TABLE t_user_right ADD CONSTRAINT t_user_right_user_fk_key UNIQUE (user_fk, right_id);\n", buf.toString());
  }

  @Test
  public void buildAddTableColumn()
  {
    final StringBuffer buf = new StringBuffer();
    final MyDatabaseUpdateDao databaseUpdateDao = myDatabaseUpdater.getDatabaseUpdateDao();
    databaseUpdateDao.buildAddTableAttributesStatement(buf, "t_task", new TableAttribute("workpackage_code", TableAttributeType.VARCHAR,
        100, false), new TableAttribute("user_fk", TableAttributeType.INT).setForeignTable("t_user").setForeignAttribute("pk"));
    assertEquals("-- Does already exist: ALTER TABLE t_task ADD COLUMN workpackage_code VARCHAR(100) NOT NULL;\n" //
        + "ALTER TABLE t_task ADD COLUMN user_fk INT;\n"
        + "ALTER TABLE t_task ADD CONSTRAINT t_task_user_fk FOREIGN KEY (user_fk) REFERENCES t_user(pk);\n", buf.toString());
  }

  @Test
  public void createAndDropTableColumn()
  {
    final MyDatabaseUpdateDao databaseUpdateDao = myDatabaseUpdater.getDatabaseUpdateDao();
    databaseUpdateDao.addTableAttributes("t_task", new TableAttribute("test1", TableAttributeType.DATE), new TableAttribute("test2",
        TableAttributeType.INT));
    assertTrue(databaseUpdateDao.doesTableAttributeExist("t_task", "test1"));
    assertTrue(databaseUpdateDao.doesTableAttributeExist("t_task", "test2"));
    databaseUpdateDao.dropTableAttribute("t_task", "test1");
    assertFalse(databaseUpdateDao.doesTableAttributeExist("t_task", "test1"));
    assertTrue(databaseUpdateDao.doesTableAttributeExist("t_task", "test2"));
    databaseUpdateDao.dropTableAttribute("t_task", "test2");
    assertFalse(databaseUpdateDao.doesTableAttributeExist("t_task", "test1"));
    assertFalse(databaseUpdateDao.doesTableAttributeExist("t_task", "test2"));
  }

  @Test
  public void renameTableAttribute()
  {
    assertEquals("ALTER TABLE t_test ALTER COLUMN old_col RENAME TO new_col",
        DatabaseSupport.getInstance().renameAttribute("t_test", "old_col", "new_col"));
  }

  public void setMyDatabaseUpdater(final MyDatabaseUpdater myDatabaseUpdater)
  {
    this.myDatabaseUpdater = myDatabaseUpdater;
  }

  public void setDataSource(final DataSource dataSource)
  {
    this.dataSource = dataSource;
  }
}