[ic] Re: how to remove records from database

[Russ Riggs]

Mark Johnson wrote:

> Russ Riggs wrote:
> >
> > That is the correct syntax.  You can use either LIKE or MATCHES
> > and you can use either the '%' or the '*'....assuming, of course, that
> > mysql is ansi compliant.  So you should be able to mass delete with
> > either
> >
> > delete from merchandising where sku matches 'os*';
> >
> > or
> >
> > delete from merchandising where sku like 'os%';
>
> I have never seen matches or * used in this context. The 'matches'
> operator does have a function in mysql regarding a table that has been
> full-text indexed, but I believe this is mysql-specific. In any case,
> the matches and * do not work in mysql or oracle.
>

Both are text oriented matching schemes.  The LIKE option only accepts
the '%' and the '_' as wild card characters whereas MATCHES accepts
the '*', the '?', and '[...]' (bracketed) wildcarding.  I've always used
MATCHES as it has a more complete wildcard set.  I haven't used Oracle
in ages but have used the above with Informix for over 15 years.  I don't
think it's an Informix only extension although they are famous (infamous?)
for adding
them.  Anyway, that's from  one of my sql syntax manuals from Informix.

>
> >
> > Now to a question I have.  I prefer to handle the maintenance of the
> > database thru the admin UI as opposed to mucking directly with the
> > database.  I deleted all the items from the database via the 'items'
> > editor an assumed, erroneously, that all records that are related to
> > those items (processing too) would be handled...ie., any order,
> > specials, promos, etc., would be automatically dealt with.  Can someone
> > tell me a quick and dirty way to zero out the database so that I may
> > add my own merchandise so I can see what this thing looks like?
>
> There is no feature of the UI admin to support this sort of cascading
> delete. It would be, in my estimation, imprudent to make this type of
> data deletion a default behavior. You must also remember that the UI is
> designed to support the utilization of GDBM files, in which there exist
> no such concept as a foreign key.
>

I was unaware that the admin UI was for gdbm only.  I prefer to use an
rdbms as they typically come with a full set of tools (at the very least
an sql editor) that allows me to maniuplate the database when necessary.
I am considering using mysql as the data repository but if the admin UI
is for gdbm then I may rethink that.  Do you know if anyone has written
an admin interface for an rdbms?  Wouldn't think it'd be too much of an
effort to write one if one is conversant with the ic tags.

>
> You can define your own form action which could cascade through the
> tables of interest and delete all occurrences of related data, but the
> management of that relation, and on which tables you wish to have the
> cascading delete occur, would be entirely up to you to define and
> maintain. For that matter, you could perform the same feature in a
> mv_click, or in an AutoLoad global sub, but you still will have to
> define and maintain the relations to be enforced.
>

This sounds like more of an effort than using a good rdbms and
cascading deletes.  I like the fact that they enforce data integrity
thru referential constraints and, as you say below, casacading
deletes are intrinsic to both Oracle and Informix.  Another mechanism
would be to set triggers and do the collateral deletes when the
actual product is deleted....

>
> In the case of Oracle (and probably other databases that enforce
> foreign-key constraints), you can define your tables in such a manner so
> as to have this functionality occur independent of Interchange or any
> other application that uses the data. If you define all your FK
> constraints with the ON DELETE CASCADE option, what you desire will
> happen automatically.
>

Just so.  But one of the site design criteria I have is to keep software
expenditures
down to about zero.  So I am constrained to use gpl software which actually
suits me just fine.  If mysql doesn't support triggers/cascading deletes then
i'll
work around that fact.

Thanks for the response but I still have my original question:  how do I zero

out the database.  I.e., no categories, not products, no promos, etc., etc.

>
> --
> Mark Johnson
> Senior Systems Architect - Professional Services
> Red Hat, Inc.
> E-Business Solutions
> markj@redhat.com
> 703-456-2912
> _______________________________________________
> Interchange-users mailing list
> Interchange-users@lists.akopia.com
> http://lists.akopia.com/mailman/listinfo/interchange-users