unit testing informix database
Berny asked how to unit test an Informix database itself (tables, views, stored procedures) and what tools people use. Ian Michael Gumby questioned whether a database can be "unit tested" at all and said the request was too vague. Vagner suggested Apache JMeter, arguing it can drive SQL/stored-procedure load without writing Java (useful for stress and latch-contention testing). Ian Goddard gave a detailed approach: treat DDL scripts as the units, write testable requirements (e.g. constraint violations must fail), create and drop the database per run, and wrap it in a framework such as DbUnit/JUnit, DUnit, or just shell plus dbaccess. No single tool was settled on and no definitive resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, what experiences have you made with unit testing an Informix database? What tools have you used? Thank you for your answers, Regards, Berny
Uhm, could you explain by what you mean by Unit Testing? You can't unit test a database since you don't have the source code. Did you mean Unit testing of an *application* that connects to the database? -G > From: berny68@googlemail.com > Subject: unit testing informix database > Date: Thu, 16 Jul 2009 12:44:01 -0700 > To: informix-list@iiug.org > > Hello, > what experiences have you made with unit testing an Informix database? > What tools have you used? > Thank you for your answers, > > Regards, > Berny > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Lauren found her dream laptop. Find the PC that’s right for you. http://www.microsoft.com/windows/choosepc/?ocid=ftp_val_wl_290
Hello Ian Michael, I've been given the task to unit test the database itself, that means to unit test the procedures/functions/tables/views in the database directly. Any experience/opinion on this is very much welcome. Regards, Berny
On Jul 16, 6:19 pm, berny <bern...@googlemail.com> wrote: > Hello Ian Michael, > > I've been given the task to unit test the database itself, that means > to unit test the procedures/functions/tables/views in the database > directly. Any experience/opinion on this is very much welcome. > > Regards, > Berny Hey, Try Jmeter! It is easy to configure ! http://jakarta.apache.org/jmeter/ Vagne
Uhm... Ok. First, you can forget any JUnit/Jmeter testing tool because your database isn't written in java, unless you're testing specifically any code that runs within the database. Second. You're still being too vague to be able to help. How do you test a table? It exists. Ok, you've tested that it exists. You want to write Java code that connects and validates the database engine? Ok, you can do that. You can then write JUnits. But that doesn't really help you if you want to test and validate SQL stuff like load scripts which can be written in a whole slew of scripting languages. > From: pontes.vagner@gmail.com > Subject: Re: unit testing informix database > Date: Thu, 16 Jul 2009 14:30:25 -0700 > To: informix-list@iiug.org > > On Jul 16, 6:19 pm, berny <bern...@googlemail.com> wrote: > > Hello Ian Michael, > > > > I've been given the task to unit test the database itself, that means > > to unit test the procedures/functions/tables/views in the database > > directly. Any experience/opinion on this is very much welcome. > > > > Regards, > > Berny > > Hey, > > Try Jmeter! It is easy to configure ! > > http://jakarta.apache.org/jmeter/ > > Vagne > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Hotmail® has ever-growing storage! Don’t worry about storage limits. http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage_062009
berny wrote: > Hello, > what experiences have you made with unit testing an Informix database? > What tools have you used? > Thank you for your answers, Unit testing is normally carried out against executable code. For each unit of code you require a set of testable statement of requirement. An existing database isn't really executable code as such. However you could regard the schema, in the form of a set of DDL statements, as executable. A whole schema would, I think, be too large to be considered a unit. A set of DDL statements to set up a table and its indexes etc seems more manageable. An example might be a file containing the DDL to set up an empty table to represent an order header. Testable statements might be along the lines of "It should not be possible to insert a row which has no customer code" and "It should not be possible to insert a row in which there is no customer corresponding to the customer code". In order to be repeatable a set of tests should set up their own data. In terms of database testing this would require that the database be created at the start of the tests and dropped at the end so that there is no hangover from one run to the next. The tests outlined above will, therefore, start by running DDL to create the database. The next step would be to create the customer table which is obviously a prerequisite for setting up the constraint on the order header. This implies that we already have a tested unit in the form of a file of DDL for this. The table should be empty. The next step would be to run the unit under test creating the table with its indexes, constraints, permissions etc. We can now run the actual tests to see if the statements of requirement have been met. The first test might then be to execute a statement to insert a row with a null customer code. This tests the first requirement and the test succeeds if the insert fails. The next test would be to execute a statement to insert a row with a non-null customer code (remember the customer table is empty). This is one test against the second requirement and again the test succeeds if the insert fails. Next insert a row into the customer table and then attempt an insert into the order header against a different customer code to that just inserted. This is a second test against the second statement - it checks that the previous test wasn't simply failing because the customer table was empty - and again succeeds if the insert fails. There is an implicit requirement that it should be possible to insert an order if the customer constraint is not violated - if there weren't the other requirements would be meaningless. We test for that by attempting to insert an order including the customer code added in the previous test and this test succeeds if the insert succeeds. Finally drop the database. In all of this unexpected responses should be logged. This sequence of tests should be wrapped up in some executable form which can be rerun as required, for instance if the units creating the customer table or the order table are changed. As unit testing is normally used as part of an incremental development methodology it would be quite normal to expect such changes and, indeed, that the sets of tests would grow. For instance a status code could be added to the order header and tests relating to this could then be added to the unit tests. Stored procedures and triggers can also be introduced incrementally although these would more closely follow unit testing procedures for executable code. Note that it's implicit in the tests just outlined that the unit to create the customer table provides a customer code against which the constraint can operate. You would need a set of units, which would need to be run in the correct sequence to build the database and a set of unit tests. This set of units together with some executable wrapper to ensure the correct sequence can then be used to create the developers' sandbox databases and to recreate them as the database schema is developed. It can also be used as the basis of the DDL needed to set up the production database. It's quite likely, however, that the tested units would have to be modified for production as it's unlikely that the test and development environments would allow the same extent sizes that would be set for production. It's important, therefore, that your management realises that this is a limitation on such testing. How you implement this depends on your development environment. A number of development environments include unit testing frameworks. A little Googling reveals DbUnit at http://www.dbunit.org but this seems intended to package database unit testing for an environment which includes JUnit. If you're not using Java and JUnit this doesn't seem quite so useful and if, for instance, your development environment is Delphi & DUnit you might need to wrap it all up in Pascal. However I think it likely that you could put together a test system as outlined in shell/dbaccess without too much difficulty albeit without all the GUI bells & whistles of the established frameworks. Having said all that my only experience of a project which used unit testing (Delphi, DUnit & SQL Server) didn't introduce it until the maintenance phase by which time the database design was well established and unit testing didn't directly test database design or function. Neither am I wholly convinced that the Agile approach of development by accretion (which seems to me a truer description than "incremental") is the best way of designing a database. -- Ian Hotmail is for spammers. Real mail address is igoddard at nildram co uk
On 16 jul, 21:29, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > Uhm... > > Ok. > > First, you can forget any JUnit/Jmeter testing tool because your database isn't written in java, unless you're testing specifically any code that runs within the database. > > Second. You're still being too vague to be able to help. > > How do you test a table? It exists. Ok, you've tested that it exists. > > You want to write Java code that connects and validates the database engine? > Ok, you can do that. You can then write JUnits. > > But that doesn't really help you if you want to test and validate SQL stuff like load scripts which can be written in a whole slew of scripting languages. > > > > > > > From: pontes.vag...@gmail.com > > Subject: Re: unit testing informix database > > Date: Thu, 16 Jul 2009 14:30:25 -0700 > > To: informix-l...@iiug.org > > > On Jul 16, 6:19 pm, berny <bern...@googlemail.com> wrote: > > > Hello Ian Michael, > > > > I've been given the task to unit test the database itself, that means > > > to unit test the procedures/functions/tables/views in the database > > > directly. Any experience/opinion on this is very much welcome. > > > > Regards, > > > Berny > > > Hey, > > > Try Jmeter! It is easy to configure ! > > >http://jakarta.apache.org/jmeter/ > > > Vagne > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list > > _________________________________________________________________ > Hotmail® has ever-growing storage! Don’t worry about storage limits.http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_...- Ocultar texto das mensagens anteriores - > > - Mostrar texto das mensagens anteriores - Hi Ian, You can use Jmeter to test Informix without the source code. How to test the table? Findind latch contention For example, in another day I need to reproduce latch contention in Informix 7,I wrote my sql statement and put in Junit, I ran the stress test reproducing more than 4000 concurrent users and the contention occurs. After the contention I made changes in my table and sql statement to avoid contention. Another examples, when you need to reproduce: - Denyal of Services - Incomplete connections - Latch contention in stored procedure calls - Latch contention in tables/index How to do this tests? You just configure Jmeter, you dont need to write java code. I suggested Jmeter because it is easy to use. Regards Vagner