Hi Daniele, Thanks for the explanation. I agree, that looks very very useful. We could continue this, e.g. PRODOTTO could have more foreign keys such as the product owner, etc. The test framework could walk the graph and generate a minimal set of test data that is required to insert the data you're interested in. Tricky cases like hierarchical or cyclical data could be detected and handled.
I have created a feature request for this: https://github.com/jOOQ/jOOQ/issues/8142 I definitely think that jOOQ could offer more database testing functionality. Your suggestion seems quite actionable, so I'm positive that a useful result could be produced soon. If you have any additional ideas thatyou would like to share, that you think jOOQ should help users with, I'm all ears :) Thanks again, Lukas On Wed, Dec 19, 2018 at 8:46 PM <[email protected]> wrote: > Hi Lukas, > > in this example the database is composed by 41 table, but only 2 are > relevant for the scenario (sorry for the italian table names but that's it) > > These are the table involved: MACCHINA, PRODUZIONE, PRODOTTO. > > [image: Screenshot from 2018-12-19 19-25-33.png] > __id is always primary key > __* are not null > Produzione have a not null foreign key towards Prodotto. > > here is the full ddl: > > CREATE TABLE `macchina` ( >> `__id` bigint(20) unsigned NOT NULL, >> `__createdby` varchar(255) NOT NULL, >> `__createdon` datetime NOT NULL, >> `__lastmodifiedby` varchar(255) NOT NULL, >> `__lastmodifiedon` datetime NOT NULL, >> `__ownedby` varchar(255) NOT NULL, >> `__ownedon` datetime NOT NULL, >> `nome` varchar(255) DEFAULT NULL, >> PRIMARY KEY (`__id`) >> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; >> CREATE TABLE `prodotto` ( >> `__id` bigint(20) unsigned NOT NULL, >> `__createdby` varchar(255) NOT NULL, >> `__createdon` datetime NOT NULL, >> `__lastmodifiedby` varchar(255) NOT NULL, >> `__lastmodifiedon` datetime NOT NULL, >> `__ownedby` varchar(255) NOT NULL, >> `__ownedon` datetime NOT NULL, >> `nome` varchar(255) NOT NULL, >> `peso_bigbag` int(11) DEFAULT NULL, >> PRIMARY KEY (`__id`), >> UNIQUE KEY `Prodotto__uniqueConstraint1` (`nome`) >> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; >> CREATE TABLE `produzione` ( >> `__id` bigint(20) unsigned NOT NULL, >> `__createdby` varchar(255) NOT NULL, >> `__createdon` datetime NOT NULL, >> `__lastmodifiedby` varchar(255) NOT NULL, >> `__lastmodifiedon` datetime NOT NULL, >> `__ownedby` varchar(255) NOT NULL, >> `__ownedon` datetime NOT NULL, >> `data_ora_inizio` datetime DEFAULT NULL, >> `id__prodotto_1` bigint(20) unsigned NOT NULL, >> `id__prodotto_1__associatedby` varchar(255) NOT NULL, >> `id__prodotto_1__associatedon` datetime NOT NULL, >> PRIMARY KEY (`__id`), >> KEY `Produzione__prodotto_1_fk` (`id__prodotto_1`), >> >> CONSTRAINT `Produzione__prodotto_1_fk` FOREIGN KEY (`id__prodotto_1`) >> REFERENCES `prodotto` (`__id`) >> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > > The following is a code snippet of a test method. > > > [image: Screenshot from 2018-12-19 18-38-04.png] > cleanInsert() is a facility method > flatXml() create a dbunit flat xml and return a dbunit IDataSet > Jooq come in play with: > > row(PRODUZIONE).endRow(), > row(MACCHINA).col(t -> t.NOME, "myname").endRow() > > As you can see, also if PRODUZIONE has not null columns I don't need to > specify these values, the "framework" create a random value and set > automatically. If I did, the "framework" preserve my value. > Same as for MACCHINA but in this case I specified a custom value. Java is > not too much user friendly here, other languages xtend/kotilin should do a > great job here to make more fluent the definition. Moreover eclipse has a > bug (https://bugs.eclipse.org/bugs/show_bug.cgi?id=460921) preventing > autocompletition (grrrr). > > Other interesting part is that PRODUZIONE has a foreign key towards > PRODOTTO and I don't need to create any PRODUCT. The framework will > populate che column just to satisfy the not null constraint but do not > create any PRODOTTO. I disable foreignkey checks to allow insert. > > The execute() part is something related to my business logic. > > The assert part are just some sugar aroung jooq, hiding > datasource/connection handling. You already did great job here. > > Usually my tests database setup takes at most 10-15 lines of code. > > Any comments? > > On Wednesday, December 19, 2018 at 10:41:52 AM UTC+1, Lukas Eder wrote: >> >> >> >> On Wed, Dec 19, 2018 at 10:15 AM Daniele Antonini <[email protected]> >> wrote: >> >>> Well, I've searched a bit on mailing list and found some posts about >>> testing, >>> >>> Like this one: >>> https://groups.google.com/forum/m/#!searchin/jooq-user/Dataset/jooq-user/SHoA9GA2MwE >>> >>> Other posts are mocking database layer, and yes it is interesting and >>> useful in some scenarios, but I found this approach too much time consuming >>> (developing time) so my question is regarding database integration testing. >>> I don't care too much that my tests could be slow (execution time). >>> Execution time could be handled with an appropriate continuous integration >>> cluster. >>> >> >> Mocking really doesn't scale. It's useful to test infrastructure logic >> (like binding logic on the JDBC layer or on the jOOQ layer), but not an >> entire database / business layer. At some point, a real database is needed, >> ideally from the same vendor. >> > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
