From derby-user-return-12486-apmail-db-derby-user-archive=db.apache.org@db.apache.org Fri Mar 19 15:29:25 2010 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 5811 invoked from network); 19 Mar 2010 15:29:24 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 19 Mar 2010 15:29:24 -0000 Received: (qmail 932 invoked by uid 500); 19 Mar 2010 15:29:24 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 904 invoked by uid 500); 19 Mar 2010 15:29:24 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 885 invoked by uid 99); 19 Mar 2010 15:29:24 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 19 Mar 2010 15:29:24 +0000 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=RCVD_IN_DNSWL_NONE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [209.85.220.226] (HELO mail-fx0-f226.google.com) (209.85.220.226) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 19 Mar 2010 15:29:17 +0000 Received: by fxm26 with SMTP id 26so471212fxm.15 for ; Fri, 19 Mar 2010 08:28:56 -0700 (PDT) MIME-Version: 1.0 Received: by 10.223.63.208 with SMTP id c16mr10646604fai.29.1269012535909; Fri, 19 Mar 2010 08:28:55 -0700 (PDT) X-Originating-IP: [193.206.186.101] In-Reply-To: <1f5d398f1003190823o27f94c3bna76f152cd830a8f1@mail.gmail.com> References: <1f5d398f1003011116n663ac9e1i151c26601215cf83@mail.gmail.com> <4B8E5D84.2070807@Sun.COM> <1f5d398f1003172305o3ef9c62fx4e7a1dfc98cfae91@mail.gmail.com> <4BA2381B.3030600@gmail.com> <1f5d398f1003180820m19ca728ag479e2b53f986c98f@mail.gmail.com> <1f5d398f1003180927o52c621b2k8ef897c5cecf30b8@mail.gmail.com> <1f5d398f1003190211h3fe6bffcsa141d8edcfb75fbc@mail.gmail.com> <4BA34785.2030205@Sun.COM> <1f5d398f1003190736m4ba4624el63807787c7fc4bf4@mail.gmail.com> <1f5d398f1003190823o27f94c3bna76f152cd830a8f1@mail.gmail.com> Date: Fri, 19 Mar 2010 16:28:55 +0100 Message-ID: <1f5d398f1003190828q1b844075yc518d5a2c5b1ced8@mail.gmail.com> Subject: Re: cleaning database between test cases From: Gabriele Kahlout To: Derby Discussion Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Checked: Checked by ClamAV on apache.org Here is the code for wrapper methods that might be incorrect: public final static Connection connectToDerby(final File parentPath, final boolean create) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException { // Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); final Connection con =3D DriverManager.getConnection("jdbc:derby:" + parentPath.getPath() + File.separator + "db.sqlwrapper;create=3D" + create); return con; } public static boolean shutDownDerby() { try { Connection con =3D connectToDerby(); con.close(); con =3D DriverManager.getConnection("jdbc:derby:db.sqlwrapper;shutdown=3Dtrue"); } catch (SQLException e) { // with throw exception on successful deletion. return true; } return false; } 2010/3/19, Gabriele Kahlout : > Here is the shortest test that shows the problem: > > import com.mysimpatico.sqlwrapper.*; > import java.io.File; > import java.sql.*; > > /* > * To change this template, choose Tools | Templates > * and open the template in the editor. > */ > /** > * > * @author simpatico > */ > public class DbDeletionTest { > > public static boolean deleteDir(File dir) { > if (dir.isDirectory()) { > String[] children =3D dir.list(); > for (int i =3D 0; i < children.length; i++) { > boolean success =3D deleteDir(new File(dir, children[i]))= ; > if (!success) { > return false; > } > } // The directory is now empty so delete it return > dir.delete(); } > } > return dir.delete(); > } > > public static void test() throws Exception { > final File dir =3D new File("test"); > dir.mkdir(); > final Connection con =3D SqlWrapper.connectToDerby(dir, true); > final Statement st =3D con.createStatement(); > SqlWrapper.setVendor(SqlWrapper.vendor.JAVADB); > > final String expTableName =3D "Expressions"; > final String exp =3D "expression"; > final ReferencedColumn expColumn =3D new ReferencedColumn(exp, > SqlWrapper.VARCHAR, 100); > final Table expTable =3D new Table(expTableName, expColumn, > expColumn); > st.executeUpdate(SqlWrapper.create(expTable)); > con.close(); > SqlWrapper.shutDownDerby(); > deleteDir(dir); > System.out.println("test executed."); > > } > > public static void main(String[] args) { > try { > test(); > test(); > } > catch (Exception e) { > e.printStackTrace(); > } > } > } > The libraries needed are: derby.jar, SqlWrapper.jar (just wraps around > sql statements, u can replace it with sql strings). > http://memorizeasy.googlecode.com/svn/tags/live/lib/derby.jar > http://memorizeasy.googlecode.com/svn/tags/live/lib/sqlwrapper.jar > > The output is: > test executed. > Shutting down due to severe error. > java.sql.SQLException: Table/View 'EXPRESSIONS' already exists in Schema > 'APP'. > at > org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown > Source) > at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown > Source) > at > org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unk= nown > Source) > at > org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknow= n > Source) > at > org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown > Source) > at > org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown > Source) > at > org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(Unknow= n > Source) > at DbDeletionTest.test(DbDeletionTest.java:42) > at DbDeletionTest.main(DbDeletionTest.java:53) > Caused by: java.sql.SQLException: Table/View 'EXPRESSIONS' already > exists in Schema 'APP'. > at > org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown > Source) > at > org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcro= ssDRDA(Unknown > Source) > > 2010/3/19, Gabriele Kahlout : >> 'rm -R myDbDirectory' in *nix' >> How do you programmatically, from java do that (OS independent)? >> What I do is I iterate over the db folder and delete all contents, as >> returned by the java.io API. >> >> However, even doing that seems not sufficient. >> I see that the test folder (the one that contains db.sqlwrapper) is >> deleted after the first test, and created in the setup of the next. >> However, I get: >> >> Caused by: ERROR X0Y32: Table/View 'EXPRESSIONS' already exists in Schem= a >> 'APP'. >> >> I think it has to do with the copy in memory not being updated with >> the disk. That is because through the debugger, I've even manually >> deleted the db, but to no avail. >> Maybe this is more of a Java question now: >> How do I make sure the memory is flushed/sync with the disk? Or that >> derby, when creating a connection is not fooled by the cache in >> memory? >> >> >> 2010/3/19, Kristian Waagan : >>> On 19.03.10 10:11, Gabriele Kahlout wrote: >>>> Also, I've copied from the code what I seem to need, and then end up >>>> with when I use the db again in a 2nd method. >>>> >>>> Caused by: java.sql.SQLException: Database '/Volumes/STORE N >>>> GO/ws/MemorizEasy/test/db.sqlwrapper' not found. >>>> >>>> Note that after destroying the schemas, I shut down the db, and delete >>>> the test folder of the previous method. >>>> >>> >>> Hi Gabriele, >>> >>> I don't know the requirements of your application, but note that in >>> general you have three options: >>> >>> - clean the database and reuse it >>> This is what we're doing in the Derby test framework (see the class >>> that Bryan pointed to), because it would take much longer to actually >>> delete and recreate the database files all the time. Here we use >>> meta-data to detect all objects added to the database during the test, >>> and then we drop them. >>> >>> - simply just delete the database on disk (i.e. 'rm -R myDbDirectory' >>> in *nix) and create a new one for the next test (through JDBC, using >>> 'jdbc:derby:...;create=3Dtrue'). >>> You should shut down the database before deleting the files on disk= . >>> >>> - if you just need a database for a short period of time or similar, >>> use an in-memory db. >>> Remember to drop it [1] to free up the memory. >>> >>> It's hard to tell exactly what went wrong above, but are you sure the >>> database is still on disk? >>> Can you locate the directory? (you should see the directories >>> db.sqlwrapper, db.sqlwrapper/seg0 and db.sqlwrapper/log) >>> If not, why did it get deleted? >>> >>> >>> -- >>> Kristian >>> >>> [1] Note that proper support for this is added in 10.6, by using the >>> "drop=3Dtrue" attribute. In 10.5, the mechanism is different. >>> >>>> 2010/3/18, Gabriele Kahlout: >>>> >>>>> I reached here: >>>>> http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apa= che/derbyTesting/junit/CleanDatabaseTestSetup.java?view=3Dmarkup >>>>> >>>>> But then what do I do? The code relies on some other class, and it >>>>> seems like I'd have to import the whole package to use it. Is that it= ? >>>>> Besides that one cannot delete the APP schema (which is the one i >>>>> use), while the code seems to want to do that. >>>>> If not, is there how I can use this without concerning myself with th= e >>>>> impl. details? >>>>> >>>>> 2010/3/18, Gabriele Kahlout: >>>>> >>>>>> Can you provide a link to it? Also, is it in some library I could >>>>>> use? >>>>>> >>>>>> 2010/3/18, Bryan Pendleton: >>>>>> >>>>>>>> For testing, how can I make sure the tearDown completely deletes >>>>>>>> the >>>>>>>> db, and setUp creates a completely new copy, without using >>>>>>>> in-memory >>>>>>>> db? >>>>>>>> >>>>>>> We do this in the Derby regression test suite. >>>>>>> >>>>>>> Have a look at >>>>>>> org.apache.derbyTesting.functionTests.junit.CleanDatabaseTestSetup.= java >>>>>>> >>>>>>> thanks, >>>>>>> >>>>>>> bryan >>>>>>> >>>>>>> >>>>>> >>>>>> -- >>>>>> Regards, >>>>>> K. Gabriele >>>>>> >>>>>> --- unchanged since 25/1/10 --- >>>>>> P.S. Unless a notification (LON), please reply either with an answer >>>>>> OR with " ACK" appended to this subject within 48 hours. Otherwise, = I >>>>>> might resend. >>>>>> In(LON, this) =E2=88=A8 In(48h, TimeNow) =E2=88=A8 =E2=88=83x. In(x,= MyInbox) =E2=88=A7 IsAnswerTo(x, >>>>>> this) =E2=88=A8 (In(subject(this), subject(x)) =E2=88=A7 In(ACK, sub= ject(x)) =E2=88=A7 >>>>>> =C2=ACIsAnswerTo(x,this)) =E2=87=92 =C2=ACIResend(this). >>>>>> >>>>>> Also note that correspondence may be received only from specified a >>>>>> priori senders, or if the subject of this email ends with a code, eg= . >>>>>> -LICHT01X, then also from senders whose reply contains it. >>>>>> =E2=88=80x. In(x, MyInbox) =E2=87=92 In(senderAddress(x), MySafeSend= erList) =E2=88=A8 (=E2=88=83y. >>>>>> In(y, subject(this) ) =E2=88=A7 In(y,x) =E2=88=A7 isCodeLike(y, -LIC= HT01X) ). >>>>>> >>>>>> >>>>> >>>>> -- >>>>> Regards, >>>>> K. Gabriele >>>>> >>>>> --- unchanged since 25/1/10 --- >>>>> P.S. Unless a notification (LON), please reply either with an answer >>>>> OR with " ACK" appended to this subject within 48 hours. Otherwise, I >>>>> might resend. >>>>> In(LON, this) =E2=88=A8 In(48h, TimeNow) =E2=88=A8 =E2=88=83x. In(x, = MyInbox) =E2=88=A7 IsAnswerTo(x, >>>>> this) =E2=88=A8 (In(subject(this), subject(x)) =E2=88=A7 In(ACK, subj= ect(x)) =E2=88=A7 >>>>> =C2=ACIsAnswerTo(x,this)) =E2=87=92 =C2=ACIResend(this). >>>>> >>>>> Also note that correspondence may be received only from specified a >>>>> priori senders, or if the subject of this email ends with a code, eg. >>>>> -LICHT01X, then also from senders whose reply contains it. >>>>> =E2=88=80x. In(x, MyInbox) =E2=87=92 In(senderAddress(x), MySafeSende= rList) =E2=88=A8 (=E2=88=83y. >>>>> In(y, subject(this) ) =E2=88=A7 In(y,x) =E2=88=A7 isCodeLike(y, -LICH= T01X) ). >>>>> >>>>> >>>> >>>> >>> >>> >> >> >> -- >> Regards, >> K. Gabriele >> >> --- unchanged since 25/1/10 --- >> P.S. Unless a notification (LON), please reply either with an answer >> OR with " ACK" appended to this subject within 48 hours. Otherwise, I >> might resend. >> In(LON, this) =E2=88=A8 In(48h, TimeNow) =E2=88=A8 =E2=88=83x. In(x, MyI= nbox) =E2=88=A7 IsAnswerTo(x, >> this) =E2=88=A8 (In(subject(this), subject(x)) =E2=88=A7 In(ACK, subject= (x)) =E2=88=A7 >> =C2=ACIsAnswerTo(x,this)) =E2=87=92 =C2=ACIResend(this). >> >> Also note that correspondence may be received only from specified a >> priori senders, or if the subject of this email ends with a code, eg. >> -LICHT01X, then also from senders whose reply contains it. >> =E2=88=80x. In(x, MyInbox) =E2=87=92 In(senderAddress(x), MySafeSenderLi= st) =E2=88=A8 (=E2=88=83y. >> In(y, subject(this) ) =E2=88=A7 In(y,x) =E2=88=A7 isCodeLike(y, -LICHT01= X) ). >> > > > -- > Regards, > K. Gabriele > > --- unchanged since 25/1/10 --- > P.S. Unless a notification (LON), please reply either with an answer > OR with " ACK" appended to this subject within 48 hours. Otherwise, I > might resend. > In(LON, this) =E2=88=A8 In(48h, TimeNow) =E2=88=A8 =E2=88=83x. In(x, MyIn= box) =E2=88=A7 IsAnswerTo(x, > this) =E2=88=A8 (In(subject(this), subject(x)) =E2=88=A7 In(ACK, subject(= x)) =E2=88=A7 > =C2=ACIsAnswerTo(x,this)) =E2=87=92 =C2=ACIResend(this). > > Also note that correspondence may be received only from specified a > priori senders, or if the subject of this email ends with a code, eg. > -LICHT01X, then also from senders whose reply contains it. > =E2=88=80x. In(x, MyInbox) =E2=87=92 In(senderAddress(x), MySafeSenderLis= t) =E2=88=A8 (=E2=88=83y. > In(y, subject(this) ) =E2=88=A7 In(y,x) =E2=88=A7 isCodeLike(y, -LICHT01X= ) ). > --=20 Regards, K. Gabriele --- unchanged since 25/1/10 --- P.S. Unless a notification (LON), please reply either with an answer OR with " ACK" appended to this subject within 48 hours. Otherwise, I might resend. In(LON, this) =E2=88=A8 In(48h, TimeNow) =E2=88=A8 =E2=88=83x. In(x, MyInbo= x) =E2=88=A7 IsAnswerTo(x, this) =E2=88=A8 (In(subject(this), subject(x)) =E2=88=A7 In(ACK, subject(x)= ) =E2=88=A7 =C2=ACIsAnswerTo(x,this)) =E2=87=92 =C2=ACIResend(this). Also note that correspondence may be received only from specified a priori senders, or if the subject of this email ends with a code, eg. -LICHT01X, then also from senders whose reply contains it. =E2=88=80x. In(x, MyInbox) =E2=87=92 In(senderAddress(x), MySafeSenderList)= =E2=88=A8 (=E2=88=83y. In(y, subject(this) ) =E2=88=A7 In(y,x) =E2=88=A7 isCodeLike(y, -LICHT01X) = ).