@Test
public void testUpdatablesPKChangePK() throws Exception {
jOOQAbstractTest.reset = false;
// [#979] some additional tests related to modifying an updatable's
// primary key. Setting it to the same value shouldn't result in an
// INSERT statement...
// This will result in no query
B book1 = create().fetchOne(TBook(), TBook_ID().equal(1));
book1.setValue(TBook_ID(), 1);
assertEquals(0, book1.store());
// This will result in an UPDATE
book1.setValue(TBook_ID(), 1);
book1.setValue(TBook_TITLE(), "new title");
assertEquals(1, book1.store());
assertEquals(4, create().selectCount().from(TBook()).fetchOne(0));
B book2 = create().fetchOne(TBook(), TBook_ID().equal(1));
assertEquals(1, (int) book2.getValue(TBook_ID()));
assertEquals("new title", book2.getValue(TBook_TITLE()));
// This should now result in an INSERT
book2.setValue(TBook_ID(), 5);
assertEquals(1, book2.store());
B book3 = create().fetchOne(TBook(), TBook_ID().equal(5));
assertEquals(5, (int) book3.getValue(TBook_ID()));
assertEquals("new title", book3.getValue(TBook_TITLE()));
}
/**
* Convenience method to create a new dummy book
*/
protected final B newBook(int id) {
B record = create().newRecord(TBook());
record.setValue(TBook_ID(), id);
record.setValue(TBook_AUTHOR_ID(), 1);
record.setValue(TBook_TITLE(), "XX");
record.setValue(TBook_PUBLISHED_IN(), 2000);
record.setValue(TBook_LANGUAGE_ID(), 1);
return record;
}
@Test
public void testUpdatablesVersionAndTimestamp() throws Exception {
if (TBook_REC_TIMESTAMP() == null && TBook_REC_VERSION() == null) {
log.info("SKIPPING", "Record version and timestamp tests");
}
jOOQAbstractTest.reset = false;
Factory create = create(new Settings().withExecuteWithOptimisticLocking(true));
boolean t = TBook_REC_TIMESTAMP() != null;
boolean v = TBook_REC_VERSION() != null;
// Test data integrity check
// -------------------------
if (t) assertEquals(2, create.selectCount().from(TBook()).where(TBook_REC_TIMESTAMP().isNotNull()).fetchOne(0));
if (v) assertEquals(2, create.selectCount().from(TBook()).where(TBook_REC_VERSION().isNotNull()).fetchOne(0));
// Version and timestamp shouldn't change when there are constraint violations
// -------------------------
B book1 = create.newRecord(TBook());
book1.setValue(TBook_ID(), 5);
try {
book1.store();
fail();
}
catch (DataAccessException expected) {}
if (t) assertNull(book1.getValue(TBook_REC_TIMESTAMP()));
if (v) assertNull(book1.getValue(TBook_REC_VERSION()));
// Test non-nullability of version and timestamp for new books
// -------------------------
B book2 = newBook(5);
assertEquals(1, book2.store());
Timestamp t2 = t ? book2.getValue(TBook_REC_TIMESTAMP()) : null;
Integer v2 = v ? book2.getValue(TBook_REC_VERSION()) : null;
if (t) assertNotNull(t2);
if (v) assertNotNull(v2);
// Test immutability of version and timestamp for non-stored books
// -------------------------
book2.refresh();
assertEquals(0, book2.store());
assertEquals(t2, t ? book2.getValue(TBook_REC_TIMESTAMP()) : null);
assertEquals(v2, v ? book2.getValue(TBook_REC_VERSION()) : null);
// Test resetting of version and timestamp for copied books
// -------------------------
B book3 = book2.copy();
book3.setValue(TBook_ID(), 6);
assertEquals(1, book3.store());
Timestamp t3 = t ? book3.getValue(TBook_REC_TIMESTAMP()) : null;
Integer v3 = v ? book3.getValue(TBook_REC_VERSION()) : null;
if (t) assertNotNull(t3);
if (v) assertNotNull(v3);
if (t && t2 != null) assertFalse(t2.equals(t3));
if (v && v2 != null) assertFalse(v2.equals(v3));
// Check if updating all records will lead to updated version and timestamp values
// -------------------------
// BOOK[ID=4] has version and timestamp set to null
B book4 = create().fetchOne(TBook(), TBook_ID().equal(4));
book4.setValue(TBook_TITLE(), "Blah");
assertEquals(1, book4.store());
Timestamp t4 = t ? book4.getValue(TBook_REC_TIMESTAMP()) : null;
Integer v4 = v ? book4.getValue(TBook_REC_VERSION()) : null;
if (t) assertNotNull(t4);
if (v) assertEquals(Integer.valueOf(1), v4);
book4.refresh();
if (t) assertEquals(t4, book4.getValue(TBook_REC_TIMESTAMP()));
if (v) assertEquals(v4, book4.getValue(TBook_REC_VERSION()));
// Increment both values
book4.setValue(TBook_TITLE(), "Blah 1");
assertEquals(1, book4.store());
Timestamp t4a = t ? book4.getValue(TBook_REC_TIMESTAMP()) : null;
Integer v4a = v ? book4.getValue(TBook_REC_VERSION()) : null;
if (t) assertNotNull(t4a);
if (v) assertEquals(Integer.valueOf(2), v4a);
book4.refresh();
if (t) assertEquals(t4a, book4.getValue(TBook_REC_TIMESTAMP()));
if (v) assertEquals(v4a, book4.getValue(TBook_REC_VERSION()));
// Don't change the book
assertEquals(0, book4.store());
if (t) assertEquals(t4a, book4.getValue(TBook_REC_TIMESTAMP()));
if (v) assertEquals(v4a, book4.getValue(TBook_REC_VERSION()));
book4.refresh();
if (t) assertEquals(t4a, book4.getValue(TBook_REC_TIMESTAMP()));
if (v) assertEquals(v4a, book4.getValue(TBook_REC_VERSION()));
}
@Test
public void testUpdatablesPK() throws Exception {
jOOQAbstractTest.reset = false;
B book = create().newRecord(TBook());
try {
book.refresh();
}
catch (InvalidResultException expected) {}
// Fetch the original record
B book1 = create().fetchOne(TBook(), TBook_TITLE().equal("1984"));
// Another copy of the original record
B book2 = create().fetchOne(TBook(), TBook_TITLE().equal("1984"));
// Immediately store the original record. That shouldn't have any effect
assertEquals(0, book1.store());
// Modify and store the original record
Integer id = book1.getValue(TBook_ID());
book1.setValue(TBook_TITLE(), "1985");
assertEquals(1, book1.store());
// Fetch the modified record
book1 = create().fetchOne(TBook(), TBook_ID().equal(id));
// Modify the record
book1.setValue(TBook_TITLE(), "1999");
assertEquals("1999", book1.getValue(TBook_TITLE()));
// And refresh it again
book1.refresh();
assertEquals("1985", book1.getValue(TBook_TITLE()));
assertEquals(0, book1.store());
// Refresh the other copy of the original record
assertEquals(id, book2.getValue(TBook_ID()));
assertEquals("1984", book2.getValue(TBook_TITLE()));
book2.refresh();
assertEquals(id, book1.getValue(TBook_ID()));
assertEquals(id, book2.getValue(TBook_ID()));
assertEquals("1985", book1.getValue(TBook_TITLE()));
assertEquals("1985", book2.getValue(TBook_TITLE()));
// No ON DELETE CASCADE constraints for Sybase ASE
if (getDialect() == SQLDialect.ASE) {
create().truncate(table("t_book_to_book_store")).execute();
}
// Delete the modified record
assertEquals(1, book1.delete());
assertEquals(0, book1.delete());
assertEquals(0, book2.delete());
// Fetch the remaining records
assertEquals(null, create().fetchOne(TBook(), TBook_ID().equal(id)));
// Store the record again from memory
assertEquals(1, book1.store());
book1.refresh();
book2.refresh();
assertEquals(id, book1.getValue(TBook_ID()));
assertEquals(id, book2.getValue(TBook_ID()));
assertEquals("1985", book1.getValue(TBook_TITLE()));
assertEquals("1985", book2.getValue(TBook_TITLE()));
// Copy the records and store them again as another one
book1 = book1.copy();
book2 = book2.copy();
assertNull(book1.getValue(TBook_ID()));
assertNull(book2.getValue(TBook_ID()));
assertEquals("1985", book1.getValue(TBook_TITLE()));
assertEquals("1985", book2.getValue(TBook_TITLE()));
// Can't store the copies yet, as the primary key is null
try {
book1.store();
} catch (DataAccessException expected) {}
try {
book2.store();
} catch (DataAccessException expected) {}
book1.setValue(TBook_ID(), 11);
book2.setValue(TBook_ID(), 12);
assertEquals(1, book1.store());
assertEquals(1, book2.store());
// Refresh the books
book1 = create().newRecord(TBook());
book2 = create().newRecord(TBook());
book1.setValue(TBook_ID(), 11);
book2.setValue(TBook_ID(), 12);
book1.refresh();
book2.refresh();
assertEquals(Integer.valueOf(11), book1.getValue(TBook_ID()));
assertEquals(Integer.valueOf(12), book2.getValue(TBook_ID()));
assertEquals("1985", book1.getValue(TBook_TITLE()));
assertEquals("1985", book2.getValue(TBook_TITLE()));
// Store a partial record
A author = create().newRecord(TAuthor());
author.setValue(TAuthor_ID(), 77);
author.setValue(TAuthor_LAST_NAME(), "Döblin");
assertEquals(1, author.store());
assertEquals(Integer.valueOf(77),
create().fetchOne(TAuthor(), TAuthor_LAST_NAME().equal("Döblin")).getValue(TAuthor_ID()));
// Store an empty record
S store = create().newRecord(TBookStore());
assertEquals(0, store.store());
// [#787] Store the same record twice.
author = create().newRecord(TAuthor());
author.setValue(TAuthor_ID(), 78);
author.setValue(TAuthor_LAST_NAME(), "Cohen");
assertEquals(1, author.store());
assertEquals(0, author.store()); // No INSERT/UPDATE should be made
author.setValue(TAuthor_FIRST_NAME(), "Arthur");
assertEquals(1, author.store()); // This should produce an UPDATE
assertEquals(1, create()
.select(count())
.from(TAuthor())
.where(TAuthor_FIRST_NAME().equal("Arthur"))
.and(TAuthor_LAST_NAME().equal("Cohen"))
.fetchOne(0));
// [#945] Set the same value twice
author = create().selectFrom(TAuthor())
.where(TAuthor_FIRST_NAME().equal("Arthur"))
.fetchOne();
author.setValue(TAuthor_FIRST_NAME(), "Leonard");
author.setValue(TAuthor_FIRST_NAME(), "Leonard");
assertEquals(1, author.store());
assertEquals(1, create()
.select(count())
.from(TAuthor())
.where(TAuthor_FIRST_NAME().equal("Leonard"))
.and(TAuthor_LAST_NAME().equal("Cohen"))
.fetchOne(0));
}