Thank you Adam. I appreciate the tips. I went ahead and tried to integrate H2, with a goal of comparing performance with both real postgres and with hashtables. But I quickly ran into postgres compatibility issues:
1. Postgres adds ::regClass to the end of nextVal function calls on table columns. H2 doesn't like that. Fortunately, I was able to workaround that by just simply removing ::regClass from the ddl. :-) 2. We extensively use jsonb columns and jsonb queries, and H2 appears to not support that. I suspect that it may also not support network address column types and functions, which we use a lot. If you have any suggestions for working around that, or a different approach, I'd appreciate that. I'm not sure we'll do extensive re-"design for testability" on those things. On Sunday, July 3, 2016 at 1:51:08 AM UTC-4, [email protected] wrote: > > Having spent much time on many part of this, hopefully I can save you some > time Jacob > > (1) If your tests require pgSQL running, they are not unit tests - they > are integration tests. In the limit these types of tests produce a test > suite that is too slow to be useful, but also likely too much code to > economically fix, and the likelihood that you will have to throw away the > system is very real. > > (2) DAO is a useful abstraction, and they make testing DAO-dependent > really easy - just mock the DAO! But how do you test the DAO's themselves? > The job of the DAO is to present a simple interface to client-code, and > reliably perform some type of database-manipulation. Clearly the best way > test this is to run a DAO against a database and see if the desired > manipulation results from calling a DAO method. (Worth noting that jOOQ > allows you to mock-out DSL.using(jooq).* but this is not usable at a > level that invites concise and reliable DAO testing) > > (3) If you have to run a database (or something like a database), then you > need to be able to do joins, etc. FTR I think it is short-sighted to try > to do this using hash tables - H2 is designed just for this purpose! > > (4) .. and this is the punch-line. JOOQ captures much meta-data when you > do jOOQ-generate, and can generate DDL for you in H2! > > DSLContext dsl = DSL.using(yourJooqH2Config); > > // The TABLE, UNIQUE and PRIMARY_KEY flags tell jOOQ what DDL Meta-Data you > would like jOOQ to copy from your > > // original schema into the H2 in-mem db. FOREIGN-KEY can be problematic. > > // queries should contain only one query (the CREATE TABLE) query > Query[] queries = dsl.ddl(tableToCreateInH2, TABLE, UNIQUE, > PRIMARY_KEY).queries(); > > > > > On Friday, July 1, 2016 at 10:06:20 AM UTC-4, Jacob G wrote: >> >> I see what you mean. We unit test our DAO layer against the real postgres >> db. But at the service layer, I'm working on re-designing our test classes >> to use "memory DAOs". I started with the approach to write memory DAO >> versions of every DAO class, essentially instead of JOOQ queries to do >> hashtable stream filter/map, etc. It's extremely fast: test classes that >> took 10 seconds to execute now take less than 100 ms. But it's a lot of >> effort, and forces us to make sure our real DAO classes are properly unit >> tested in all cases. >> >> Hopefully, H2 will give us similar speed and configuration simplicity, >> without all the development effort! >> >> FYI, for the hashtable implementation I started, I use a class that >> maintains all the hashtables to support join emulation: >> >> // >> // The purpose of this class is to enable memory dal classes to emulate the >> following sql >> // features by providing a central service of all repositories by type: >> // 1) sql joins >> // 2) long-based primary key sequence generation >> // >> @Singleton >> public class MemoryRepositoryService implements IMemoryRepositoryService { >> >> private final Map<Class<? extends UpdatableRecord<?>>, >> Map<?, ? extends UpdatableRecord<?>>> _repositories; >> private final Map<Class<? extends UpdatableRecord<?>>, Class<?>> _idTypes; >> >> private AtomicLong _sequenceGenerator; >> >> public MemoryRepositoryService() { >> _repositories = new HashMap<>(); >> _idTypes = new HashMap<>(); >> _sequenceGenerator = new AtomicLong(); >> } >> >> @Override >> public <R extends UpdatableRecord<R>, T> Map<T, R> repository(Class<R> >> valueClass, >> Class<T> >> keyClass) >> { >> if (_repositories.containsKey(valueClass)) { >> validateKeyClass(keyClass); >> return (Map<T, R>) _repositories.get(valueClass); >> } else { >> return syncRepository(valueClass, keyClass); >> } >> } >> >> private synchronized <R extends UpdatableRecord<R>, T> >> Map<T, R> syncRepository(Class<R> valueClass, >> Class<T> keyClass) >> { >> Map<T, R> repository; >> >> // double checked lock >> if (_repositories.containsKey(valueClass)) { >> validateKeyClass(keyClass); >> repository = (Map<T, R>) _repositories.get(valueClass); >> } else { >> repository = new ConcurrentHashMap<>(); >> _idTypes.put(valueClass, keyClass); >> _repositories.put(valueClass, repository); >> } >> >> return repository; >> } >> >> private <T> void validateKeyClass(Class<T> keyClass) { >> if (!_idTypes.get(keyClass).equals(keyClass)) { >> throw new IllegalArgumentException( >> "Key type does not match existing registered type"); >> } >> } >> >> @Override >> public synchronized void clear() { >> _repositories.values().forEach(Map::clear); >> } >> >> @Override >> public long nextLong() { >> return _sequenceGenerator.incrementAndGet(); >> } >> } >> >> >> >> >> On Friday, July 1, 2016 at 9:33:07 AM UTC-4, Lukas Eder wrote: >>> >>> First, you'll add a bunch of values in 1-2 hashmaps. Then you refactor. >>> Then you figure out that very often, these hashmaps are rather similar. >>> Then you notice that minimal transactionality is nice. And perhaps locking. >>> And suddenly, you have implemented a full scale RDBMS built on hashmaps >>> that can be queried using a DSL that looks like SQL... Why go through all >>> that hassle? :) >>> >>> The nice thing about H2 is that in principle, you could just keep a copy >>> of your .db file somewhere and restore that at the end of a test that >>> includes writing... >>> >>> Cheers, >>> Lukas >>> >>> 2016-07-01 15:09 GMT+02:00 Jacob G <[email protected]>: >>> >>>> Thanks. I'll take a look at it, particularly to evaluate in-process >>>> mode, performance, and complexity of configuration, schema setup and >>>> teardown between each unit test method. The nice thing about hash tables >>>> is >>>> there is no schema setup or teardown. >>>> >>>> On Friday, July 1, 2016 at 8:04:37 AM UTC-4, Lukas Eder wrote: >>>>> >>>>> Yes, that exists, and it has a name! H2 in-memory database. :) (or >>>>> HSQLDB or Derby) >>>>> >>>>> Hope this helps. >>>>> Lukas >>>>> >>>>> 2016-07-01 13:46 GMT+02:00 Jacob G <[email protected]>: >>>>> >>>>>> For unit testing, I'd like to be able to use in-memory hash tables >>>>>> for data rather than a database. Rather than re-implement memory-based >>>>>> DAO >>>>>> classes for every JOOQ-based DAO class, I'd rather have JOOQ execute >>>>>> itself >>>>>> on hash tables. Conceptually, it seems very doable since Jooq's powerful >>>>>> metadata model lends itself to being rendered and executed in >>>>>> alternative >>>>>> ways to sql. >>>>>> >>>>>> Has anyone thought about this, or even worked on or implemented such >>>>>> a solution? >>>>>> >>>>>> If I were to go about it, what approach would you suggest, and are >>>>>> there existing hooks I can use to redirect Jooq to a different hash >>>>>> table >>>>>> implementation. I see that there is a hook for connections once sql is >>>>>> rendered, but I'd like to hook in before that, while the query is still >>>>>> an >>>>>> object. Is that what AbstractDelegatingQuery is for? Any other concerns? >>>>>> >>>>>> -- >>>>>> 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. >>>> >>> >>> -- 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.
