[ic] Re: how to remove records from database

[Mark Johnson]

Russ Riggs wrote:
> 
> 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.

I just tried it in postgres, and it also doesn't work. I believe you
have stumbled onto a db-dependent feature. I can certainly understand
why a vendor might wish to create an extended regular-expression
matching feature as many times I have had to give up and write perl
scripts to do what seems should be easy to do in a database.

> 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.

It's not for GDBM only; it also functions perfectly well with a
relational db. The problem is that it fully supports the use of GDBM,
and in order to do that, the feature set has to adhere to the lowest
common denominator. Only if and when a decision is made not to support
the GDBM data model will IC be able to use higher-level features of a
generic RDBMS. Even still, you quickly run into variations that limit
what can feasibly be done based on whichever database you use.

> > 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....
> 

Agreed. And, for those db's that can have such features, you can even
have IC build/set them for you when you create the tables. Obviously,
none of this makes any sense in the context of GDBM files.

> > 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.

You might investigate Postgres. I would suspect it has cascading
deletes, and I know it has triggers. MySQL certainly does not have
either (MySQL doesn't even support foreign keys).

> 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.

Delete all the data (but not the header with the field names!) from your
.txt files in catroot/products corresponding to the tables you want
wiped out, remove the corresponding .sql files, and restart interchange.


-- 
Mark Johnson
Senior Systems Architect - Professional Services
Red Hat, Inc.
E-Business Solutions
markj@redhat.com
703-456-2912