RE: Deleting from multiple tables

Madison, The key point is that if you're doing this for testing purposes, you may want to drop some of the tables and not all of the tables in your sample database. Yes, you can write the schema out, drop the database and then recreate the database. However, one has to ask what sort of privileges you need to do that? Do all shops give those privileges to their developers? The solution I suggest works pretty much most databases. I agree that RI is an issue and you can do this in metadata, or you can do this in a script. I can say that at a previous client where we needed to write JUnits that required setting and unsetting data in a test database, we had scripts in place where we could build the database, populate the database as needed. However even a small test database took 20-30 minutes to build and populate, depending on the server load. For the most part we simulated the database call and used mock data, however for some tests we needed to use the mock data along with test data from the test database. There we would be forced to trucate and rebuild the data in a specific subset of the tables, depending on the test. Since we were clear case, each developer had a copy of the code tree and we could always update the main branch if we needed to change the scripts. Also each developer had their own copy of the test database. Everytime you needed a new database you had to submit a request to the DBAs. This was done for a couple of reasons, most importantly, they needed to control how much disk space was being used. Imagine the size of the databases if every developer wanted to create a sample database just on NY or CA alone? This is why I suggested doing the truncation at the table level. Depending on the database, I believe you can suspend the RI and not actually drop it and recreate it, no? If you do it at the table level, you can still drop all of the tables if needed, or some of the tables. I suggested using meta data in the database to control the tables, however this can be done in an XML file that's in your development tree. HTH -G > Date: Wed, 11 Feb 2009 07:48:06 -0600 > From: mpruet1@verizon.net > To: im_gumby@hotmail.com > CC: informix-list@iiug.org > Subject: Re: Deleting from multiple tables > > Ian Michael Gumby wrote: > > > > > > > From: mpruet1@verizon.net > > > Subject: Re: Deleting from multiple tables > > > Date: Wed, 11 Feb 2009 12:31:47 +0000 > > > To: informix-list@iiug.org > > > > > > Andrew2006 wrote: > > > > I'd like to purge a test database of all data so I can start from > > > > scratch without having to run in "create table" SQL and the stored > > > > procedures. > > > > > > > > Is it possible to easily delete all rows from all tables in a > > > > database? > > > > > > > > I was wondering if this may be possible to do this in a stored proc > > > > like so: > > > > > > > > <pseudocode> > > > > FOR EACH table in systables WHERE table not a system table. > > > > DELETE FROM table;> > > > END FOR > > > > </pseudocode> > > > > > > > > Does anyone know if this is possible in Informix 10? > > > > > > > > Many thanks > > > > > > > > Andrew > > > I think that it would be much easier (and quicker) to do something more > > > like... > > > > > > dbschema -ss -d XYZ > /tmp/XYZ.sql> > > echo "drop database XYZ" | dbaccess > > > dbaccess sysmaster /tmp/XYZ > > > > > > > > > > I think the point was that he wanted to truncate the data in his test > > tables in the database so that he doesn't have > > to recreate the database tables. > > Explain the difference. In either case wouldn't you end up with the > same thing? Also don't forget that in order to truncate a table, you > must first drop all of the RI constraints. By using dbschema to rebuild > the database you don't. > > > > > > The simplest solution would be to use a single stored procedure ... > > > > The outer stored procedure trunc_db() would query the systables to get > > a list of all of the tables in the database. > > Then for each user table, you truncate it. > > > > If you know the list of tables and you do not want to truncate all of > > the tables you can do it either two ways. > > > > 1) Hard code the list of tables, > > 2) Create your own meta data table consisting of the columns test_name, > > tab_name where you can name individual tests > > and then list the tables that you need to truncate for that specific test. > > > > You can then use this data to also set up the load list for a specific test. > > > > HTH > > > > -G > > > > But hey! What do I know? Its not like I have written hundreds or more > > Junits where we have to recreate mock map data to test our code. > > (Oh wait, I did. But that was Oracle. Same concept.) > > > > -G > > > > > > ------------------------------------------------------------------------ > > Windows Live™: E-mail. Chat. Share. Get more ways to connect. Check it > > out. > > <http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t2_allup_explore_022009>; > > _________________________________________________________________ Windows Live™: E-mail. Chat. Share. Get more ways to connect. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_allup_howitworks_022009

Ian Michael Gumby wrote: > Madison, > > The key point is that if you're doing this for testing purposes, you may > want to drop some of the tables and not all of the tables in your sample > database. It's always important to read all of the requirements prior to replying. Lord knows I've been guilty of not doing that often enough. But the original posting stated --- "Is it possible to easily delete all rows from all tables in a database?" So the business of not clearing out all of the tables is not a part of the scope of the requirements. The key words here are "easily" "all rows" "all tables".... ;-) > > Yes, you can write the schema out, drop the database and then recreate > the database. However, one has to ask what sort of privileges you need > to do that? Do all shops give those privileges to their developers? > > The solution I suggest works pretty much most databases. I agree that RI > is an issue and you can do this in metadata, or you can do this in a script. > > I can say that at a previous client where we needed to write JUnits that > required setting and unsetting data in a test database, we had scripts > in place where we could build the database, populate the database as > needed. However even a small test database took 20-30 minutes to build > and populate, depending on the server load. For the most part we > simulated the database call and used mock data, however for some tests > we needed to use the mock data along with test data from the test > database. There we would be forced to trucate and rebuild the data in a > specific subset of the tables, depending on the test. > > Since we were clear case, each developer had a copy of the code tree and > we could always update the main branch if we needed to change the > scripts. Also each developer had their own copy of the test database. > Everytime you needed a new database you had to submit a request to the > DBAs. This was done for a couple of reasons, most importantly, they > needed to control how much disk space was being used. Imagine the size > of the databases if every developer wanted to create a sample database > just on NY or CA alone? > > This is why I suggested doing the truncation at the table level. > Depending on the database, I believe you can suspend the RI and not > actually drop it and recreate it, no? If you do it at the table level, > you can still drop all of the tables if needed, or some of the tables. I > suggested using meta data in the database to control the tables, however > this can be done in an XML file that's in your development tree. > > HTH > > -G > > > > > Date: Wed, 11 Feb 2009 07:48:06 -0600 > > From: mpruet1@verizon.net > > To: im_gumby@hotmail.com > > CC: informix-list@iiug.org > > Subject: Re: Deleting from multiple tables > > > > Ian Michael Gumby wrote: > > > > > > > > > > From: mpruet1@verizon.net > > > > Subject: Re: Deleting from multiple tables > > > > Date: Wed, 11 Feb 2009 12:31:47 +0000 > > > > To: informix-list@iiug.org > > > > > > > > Andrew2006 wrote: > > > > > I'd like to purge a test database of all data so I can start from > > > > > scratch without having to run in "create table" SQL and the stored > > > > > procedures. > > > > > > > > > > Is it possible to easily delete all rows from all tables in a > > > > > database? > > > > > > > > > > I was wondering if this may be possible to do this in a stored proc > > > > > like so: > > > > > > > > > > <pseudocode> > > > > > FOR EACH table in systables WHERE table not a system table. > > > > > DELETE FROM table;> > > > > END FOR > > > > > </pseudocode> > > > > > > > > > > Does anyone know if this is possible in Informix 10? > > > > > > > > > > Many thanks > > > > > > > > > > Andrew > > > > I think that it would be much easier (and quicker) to do > something more > > > > like... > > > > > > > > dbschema -ss -d XYZ > /tmp/XYZ.sql> > > > echo "drop database XYZ" | dbaccess > > > > dbaccess sysmaster /tmp/XYZ > > > > > > > > > > > > > > I think the point was that he wanted to truncate the data in his test > > > tables in the database so that he doesn't have > > > to recreate the database tables. > > > > Explain the difference. In either case wouldn't you end up with the > > same thing? Also don't forget that in order to truncate a table, you > > must first drop all of the RI constraints. By using dbschema to rebuild > > the database you don't. > > > > > > > > > > The simplest solution would be to use a single stored procedure ... > > > > > > The outer stored procedure trunc_db() would query the systables to get > > > a list of all of the tables in the database. > > > Then for each user table, you truncate it. > > > > > > If you know the list of tables and you do not want to truncate all of > > > the tables you can do it either two ways. > > > > > > 1) Hard code the list of tables, > > > 2) Create your own meta data table consisting of the columns > test_name, > > > tab_name where you can name individual tests > > > and then list the tables that you need to truncate for that > specific test. > > > > > > You can then use this data to also set up the load list for a > specific test. > > > > > > HTH > > > > > > -G > > > > > > But hey! What do I know? Its not like I have written hundreds or more > > > Junits where we have to recreate mock map data to test our code. > > > (Oh wait, I did. But that was Oracle. Same concept.) > > > > > > -G > > > > > > > > > > ------------------------------------------------------------------------ > > > Windows Live': E-mail. Chat. Share. Get more ways to connect. Check it > > > out. > > > > <http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t2_allup_explore_022009>; > > > > > > ------------------------------------------------------------------------ > Windows Live': E-mail. Chat. Share. Get more ways to connect. See how it > works. > <http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_allup_howitworks_022009>;

On Feb 11, 11:41 am, Madison Pruet <mpru...@verizon.net> wrote: > Ian Michael Gumby wrote: > > Madison, > > > The key point is that if you're doing this for testing purposes, you may > > want to drop some of the tables and not all of the tables in your sample > > database. > > It's always important to read all of the requirements prior to replying. > Lord knows I've been guilty of not doing that often enough. > > But the original posting stated --- > > "Is it possible to easily delete all rows from all tables in a > database?" > > So the business of not clearing out all of the tables is not a part of > the scope of the requirements. The key words here are "easily" "all > rows" "all tables".... ;-) > > > I wasn't going to respond, but I think that the OP hit upon an important point that should be addressed. First, your use of copying the schema to a temp file and then dropping the database and then recreating it is a cute hack. But its not a good solution in that its not portable, nor scalable. I suggested that you consider the permissions required to run your script. It takes more permissions to be able to create a database than it does to truncate tables. The OP said a lot of things. When he said that he needed to delete rows from tables in a *test* database, it implied that he was looking for a repeatable solution. When he stated that he thought about a stored procedure, it implied that he needs to do this 'refresh' frequently. The underlying issue is one that *all* developers of database centric code face. How do you test your code which requires an underlying test database? Any solution that you come up with needs to be scalable, portable and should be 'pattern' centric. The solution that I proposed is based on a Java / JUnit environment, however it could be implemented in a C/C++/ESQL/C environment or a C# .Net environment. (I just don't know what testing tools are available in those environments these days) As a developer, when you write your code, you need to also write your JUnit test cases. In a JUnit, you should create a Java Class to handle database maintenance and it should have a db_setup() method, and a db_takedown() method. If you want, you can create an interface that enforces this behavior. There are other required methods too, but lets not go there for now.... So in your db_setup(), one of the input arguments should be the file name of your control file for this test. Since everyone wants to standardize on XML, you can create a simple XML schema where you specify the table names, what action you want to perform, and the name of the data file that needs to be loaded. Then you can run your test, and validate your code. In db_takedown(), you can either leave it blank, or you could if necessary have a control file similar to the one you used to setup the database, only this one resets the database to its normal state. Now when you run your JUnits, your JUnit can instantiate this class, pass in the connection data, set up a database connection, do the db_setup(), run the test and then run the db_takedown() code. Seems like a lot of work. Only its not. You write this once, and you can use it for any testing you may have. Now you're wondering why this is better. Simple: 1) Less overhead. Instead of dropping the entire database and having to recreate it, I only truncate the tables that I need to. Even if it means truncating every table. Truncating a table requires less effort. Again, imagine a developer running a series of 1000 JUnits where maybe 100 or so require actual database connections. Each time you run through your JUnit testing cycle, you're going to drop and recreate the database 100 times. Not very efficient. 2) Less required permissions. In a small environment where you have maybe 1-5 developers where only 1-2 people are doing the persistence layer work, you can let them have DBA authority over the test box. But when you have 100+ developers working globally, forget about it! Not really feasible and you as the DBA will be spending more time cleaning up accidents than its worth. I mean, lets face it. Do you want some global resource with maybe 3-5 years of Java experience and minimal database experience having DBA authority to create and drop databases? Even in a test environment, that can be very, very dangerous and costly. 3) Portability. If you implement this framework, you can use it against any database that supports truncate syntax. (Oracle, DB2, JavaDB, ...) All in addition to IDS. While Informix is a breeze to work with and create databases on the fly, other database environments are as friendly. In addition, a good DBA is going to be a bit of a control freak. That is that they want to know which databases exist, why, how large, and when they can be removed to reclaim disk space. You may not appreciate it, but when you have 100+ developers and some of them want to create a database to contain a copy of the production map data for all of NY or CA, that's a very, very large test database. If each developer did that and had multiple copies for different projects, you'll run out of disk space very, very fast. ;-) (No, I've never seen this happen! :-P ) 4) Reusability. This solution will allow you to change the tables and the data as needed. So for test A, you can set your database up one way. For test B, another. And you don't have to change a line of code in the class, just the XML file. Oh and if you have 100's of tables that you need to modify, you can write a simple utility to create a framework of the file and populates it with all of the user table names... ;-) I'm sure there are a couple of other reasons, but I think I hit the high lights. Why is this important for Informix? Simple. Informix holds a tiny piece of the overall database market. If you were to poll 100 random developers, how many of them would know anything about Informix? (Not a lot. Note that I said *developers* and not *DBAs*). It would be a good idea if there was some cross pillar collaboration. Information Management doesn't do a lot of development work. While its one thing for Guy to write a blog about this on his IBM page, its not going to get as much visibility as if it was part of a Rational wonk's column in a Dr. Dobbs mag. (Or some other article.) So if this solution is implemented in an Informix environment and the article is written and mentions Informix, you're going to get more visibility. [This is a *BIG* hint to Jerry ;-) ] Does that help explain where I'm coming from and why I don't think your cute little hack cuts it as a good solution? What I'm proposing is fairly trivial and could be UML'd out and worked out pretty quickly. But hey! What do I know? -G