DB: dropping table & its complition message

Help!

Testing my DbObject which is a sql generator or sql executor.
It is designed to add records when its instance is created.
and deletes record when its instance is deleted.
and updates record when its set accessor is invoked.

Most of the test passes but below tabled dropping fails.
But when i run it on debugger, the failing assertion passes.



testTableCreation

        PmEmployee createTable.
        self assert: PmEmployee tableExists. "PASS"
        PmEmployee deleteTable. "SQL: DROP TABLE Employee"
        self assert: PmEmployee tableExists not "fails at test time. but
passes at debug time."



I've seen similar problem while testing server socket accepting, so i
applied the same approach here in this problem by using ModalMsgLoop.




testTableCreation

        |mml|
        mml := ModalMsgLoop new.
        PmEmployee createTable.
        self assert: PmEmployee tableExists.
        PmEmployee when: ???? send: #signal to: mml.
        [PmEmployee deleteTable] fork.
        mml wait.
        self assert: PmEmployee tableExists not



What message should I hook to let it signal? I have no hint where it
is.

Howard,

Try the following:

> |mml|
> mml := ModalMsgLoop new.
> PmEmployee createTable.
> self assert: PmEmployee tableExists.
> [PmEmployee deleteTable. mml signal. ] fork.
> mml wait.
> self assert: PmEmployee tableExists not

Unless I'm missing something, the new thread will delete the table and
then signal the "semaphore".  Whether or not this will work will depend
on details of #deleteTable.

Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

Bill Schwab wrote:

> > [PmEmployee deleteTable. mml signal. ] fork.
> > mml wait.

Isn't this equivalent to ;


PmEmployee deleteTable.


With doubt, i followed your advice but the test result was same.







> Unless I'm missing something, the new thread will delete the table and
> then signal the "semaphore".  Whether or not this will work will depend
> on details of #deleteTable.



I hope this can help you help me.


DbObject>>deleteTable

        [self dbConnection exec: 'DROP TABLE ', self tableName.
        ^true]


        on: DBError
        do: [:exception| ^false]



DbObject>>tableName

        ^self subclassResponsibility




DbObject>>tableName

        ^'Employee'



Best Regards,
Howard

"Howard Oh" <[hidden email]> wrote in message
news:[hidden email]... ...

It is not obvious to me why this would be happening.  Have you modified
ODBCLibrary to use overlapped calls?  Is it possible that something in
either your deleteTable or tableExists depends upon finalization?  It is
possible that something is not getting finalized when the statements are
executed in rapid succession, but when you debug the finalization can happen
first.

Chris

Correcting above listing

PmEmployee>>tableName


        ^'Employee'


=Class hierarcy=

  DbObject - PmDbObject - PmEmployee

All these trouble arised by disobeying, "write test first, then write
code", but by doing "write code first, then write test".

Application using testees run fine. but tests are not clean.

UnitTesting is hard job to stick to.

Listing below might give you some more hint.


DbObject class>>tableExists

        ^self dbConnection tables includes: self tableName





DbObject class>>dbConnection

        ^(Smalltalk includes: self dbConnectionSymbol)
                ifTrue: [Smalltalk at: self dbConnectionSymbol]
                ifFalse:
                        [Smalltalk at: self dbConnectionSymbol put: self newDbConnection]



DbObject class>>dbConnectionSymbol

        "Give a key for dbConnection object stored in Smalltalk dictionary."
        ^self subclassResponsibility



PmDbObject class>>dbConnectionSymbol

        ^#PmDBConnection



PmDbObject class>>newDbConnection

        ^DBConnection new
                dsn: 'project managment';
                uid: 'sa';
                pwd: '1234567890';
                yourself

Howard Oh wrote:
> Most of the test passes but below tabled dropping fails.
> But when i run it on debugger, the failing assertion passes.

In addition to what Christopher asked, a question that occurred to me is
whether or not the underlying database's implementation of "DROP TABLE"
is synchronous with the call? Perhaps it's queued to a background
operation within the DB engine? It might not be pretty, but adding a
delay to the test case might take care of the problem.

> testTableCreation
> PmEmployee createTable.
> self assert: PmEmployee tableExists. "PASS"
> PmEmployee deleteTable. "SQL: DROP TABLE Employee"

        Processor sleep: 200. "some suitable delay for the delete to actually
occur?"

> self assert: PmEmployee tableExists not "fails at test time. but
> passes at debug time."
>

--
Bill Dargel            [hidden email]
Shoshana Technologies
100 West Joy Road, Ann Arbor, MI 48105  USA

Thanks, Bill.

Following your advice, #deleteTable is evolved to;

DbObject class>>deleteTable

        [self dbConnection exec: 'DROP TABLE ', self tableName.
        ^true]


        on: DBError
        do: [:exception| "^false"
                (exception tag errors anySatisfy: [:detail| detail sqlState = 'S1000'
])
                        ifTrue: [
                                Processor sleep: 200.
                                ^self deleteTable"retry"]
                        ifFalse: [^false]].


Test passes. Wonderful!

But, suitable delay is questionable. Will it still be suitable when DB
is deadly busy?

Things can be much better when, this waiting for DB by some Callback
from DB or #trigger: from "Dolphin Database Connection".

Best Regards,
Howard J. Oh

Howard Oh wrote:
Hmm. What exception are you getting, that you're effectively ignoring? I
hadn't noticed that in your earlier version of #deleteTable. I'd think
that getting a DBError should, in general, cause a test to fail, not
just be ignored. I'm also wondering if it's just the delay, or if its
the retry of the delete that's now allowing the tests to pass.

As far as doing a retry, I'd write it as "exception retry" instead of
the "self deleteTable" recursion. Also, if you truly have an error
situation, it would turn into infinite recursion or retries. Might want
to put in a counter and only retry a limited number of times before
passing on the error.

> Test passes. Wonderful!
>
> But, suitable delay is questionable. Will it still be suitable when DB
> is deadly busy?

_Assuming_ that the DROP TABLE is asynchronous with respect to the
#exec: call, one approach would be to write your #deleteTable so that it
is synchronous. After doing the drop, have it check for the existence of
the table, delaying and repeating the check until the table has gone
away. With a suitable timeout or repeat count so that it doesn't keep
checking forever.

--
Bill Dargel            [hidden email]
Shoshana Technologies
100 West Joy Road, Ann Arbor, MI 48105  USA

Howard Oh wrote:
> Help!
>
> Testing my DbObject which is a sql generator or sql executor.
> It is designed to add records when its instance is created.
> and deletes record when its instance is deleted.
> and updates record when its set accessor is invoked.
>
> Most of the test passes but below tabled dropping fails.
> But when i run it on debugger, the failing assertion passes.


This usually indicates a timing window.

It *could* be a hidden toggle, stack phase error, or
memory bounds error, but the first is uncommon and the
other two are at best rare, for Smalltalk.


> testTableCreation
>
> PmEmployee createTable.
> self assert: PmEmployee tableExists. "PASS"
> PmEmployee deleteTable. "SQL: DROP TABLE Employee"


---> There is a timing window right here.


> self assert: PmEmployee tableExists not "fails at test time. but
> passes at debug time."


---> The assert will fail if the delete hasn't finished yet.
---> Single stepping with the debugger means there will be
     plenty of time for the delete to finish before you
     hit the assert.
>
>
> I've seen similar problem while testing server socket accepting, so i
> applied the same approach here in this problem by using ModalMsgLoop.
>

Misleading.  Try the following...

    >>testTableCreation
        | short n |
        short := Delay forMilliseconds: 100.
        PmEmployee createTable.
  self assert: PmEmployee tableExists. "PASS"
  PmEmployee deleteTable. "SQL: DROP TABLE Employee"
        [ short wait.
          PmEmployee tableExists
        ] whileTrue: [n := n + 1].
        Transcript nextPutAll: 'Delete took ', n asString , '00ms'; cr.

If the table never goes away,
the test will never finish.
A good thing, IMO.

But you can always insert
    self assert: n < somethingYouThinkIsReasonable.
into the loop if you prefer a positive failure.


Regards,

-cstb