Re: mathprof's app

mathprof You need triggers, stored procedures, the serial datatype and non-null constraints, all of which are available in SE. It would perhaps be better if you read up the relevant sections in the FM. That may give you more ideas. HTH Sujit mathprof@bigfoot.com on 04/29/99 06:35:12 PM To: Informix User Group Mailing List <informix-list@iiug.org> cc: (bcc: Sujit Pal) Subject: Thanks again to everyone for welcoming me to this list. I wasn't quite sure how to "bundle" my newbie questions, so instead I thought I'd mention an "application" I'd like to write (using Informix-SE) and point out the questions I have, as well as ask if anyone sees additional problems or has comments on how I could write the application better. I start with two tables: TABLE people: lastname char(255), firstname char(255), zipcode(255) TABLE comments: username char(255), timestamp datetime year to second, refid int, comment char(255) Basically, I want to be able to make a comment anytime I change (UPDATE or INSERT) 'people'. In a perfect world, I want to type something like: EXAMPLE 1>> insert into people values ('Frapples','Robert','12345') WITH COMMENT "met Bob at Halloween party" OR EXAMPLE 2>> update people set zipcode='12345' where zipcode='54321' WITH COMMENT "the 54321 zip code has now changed to the 12345 zip code" I realize I can't actually rewrite the SQL interpreter (nor would I want to), so in real life, this might be a procedure or something. The results I would want are: EXAMPLE 1>> insert into people values ('Frapples','Robert','12345') insert into comments values ("mathprof",current,***,"met Bob at Halloween party") where "***" would be the rowid of the row just inserted into 'people'. EXAMPLE 2>> update people set zipcode='12345' where zipcode='54321' FOR EACH ROW AFFECTED BY THE UPDATE: insert into comments values ("mathprof",current,***,"the 54321 zip code has now changed to the 12345 zip code") where "***" would be the rowid of each row affected by the update (so, if 15 rows were changed, this would insert 15 rows into comments) In this way, I would have a history of changes made to a given row (or at least a history of comments whenever that row was changed). Note: the username field is semi-useless since I'm the only one using my machine right now-- however, if we get into a situation where multiple people are updating a table, it's useful to know who did the actual update. For realistic purposes, I'm assuming I'll need to create a procedure that takes two arguments: the SQL command I want to execute, and the comment I want to enter. For examples, if I named the procedure XYZ: CALL PROCEDURE XYZ("insert into people values ('Frapples','Robert','12345')", "met Bob at Halloween party"); CALL PROCEDURE XYZ("update people set zipcode='12345' where zipcode='54321'", "the 54321 zip code has now changed to the 12345 zip code"); Here are the questions/problems/issues I had: *Is it possible to write a procedure that does what I want? A procedure that executes its first argument as an SQL command, stores what rows were changed/inserted, and then inserts rows into a second table based on those values? *In an absolutely ideal world, I'd like to enforce comments-- in other words, no one could UPDATE/INSERT the 'people' table without making a comment -- is there any way to do this? (eg, "table 'people' can only be updated by procedure xyz") *I believe that quoted strings in Informix-SE have a limit of 255 characters-- this isn't a problem for the comment (since I'm storing it in a char(255) anyway), but, in more complex situations, the SQL query could easily exceed 255 characters. How do I work around this? *In the 'comments' table, I'm using the 'rowid' from the 'people' table to refer to a specific record. For example, if the 'comments' table had a row like: username timestamp refid comment ======== ========= ===== ======================== mathprof 1999-01-01 00:00:00 7 Blah then it would refer to the record whose rowid is 7 in the 'people' table. Is this a good idea? Would 'update statistics' ruin my indexing? Is there a better way to refer to a specific row in another table? *Philosophically speaking, is my approach valid? Should I be using a completely different and unrelated approach? I've seen some discussion that a database should be used to STORE data, and that a secondary application program should be used to MANIPULATE data. I like the unified approach of having the database do everything, because the database knows a lot more about its own data than an external program. Thanks for any help!