[Babase] Database warnings (and some errors that can't be caught
dynamically)
Catherine Markham
amarkham at princeton.edu
Thu Jul 20 13:05:56 EDT 2006
Hi Karl,
Thanks for sending this so quickly. I glanced through the document and
there was no mention of warnings for dispersal dates relative to a
baboon's age. I checked with Jeanne and she said that's not a problem -
it must have just been dropped from the programming because of the range
of male dispersal ages as well as the range ages for males who have not
dispersed.
As for your first email that possibly got hung-up, nothing was listed in
the admin interface.
Thanks,
Catherine
Karl O. Pinc wrote:
> Hi,
>
> I decided to attach the SQL rather than append it.
>
> It would not hurt to run these queries against the
> data and see what sort of warnings or errors show
> their heads.
>
> Catherine, I did send another mail to the list
> straight from papio with the SQL as its content.
> I think it got hung up waiting for list approval
> because I didn't send it from my regular account
> so you might want to approve or reject it.
>
>
> Karl <kop at meme.com>
> Free Software: "You don't pay back, you pay forward."
> -- Robert A. Heinlein
>
>
> ------------------------------------------------------------------------
>
> -- Copyright (C) 2004, 2005, 2006, Karl O. Pinc
> --
> -- Babase is free software; you can redistribute it and/or modify
> -- it under the terms of the GNU General Public License as published by
> -- the Free Software Foundation; either version 2 of the License, or
> -- (at your option) any later version.
> --
> -- This program is distributed in the hope that it will be useful,
> -- but WITHOUT ANY WARRANTY; without even the implied warranty of
> -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
> -- GNU General Public License for more details.
> --
> -- Karl O. Pinc <kop at meme.com>
>
> -- DO NOT EDIT THIS FILE. It was automatically generated. Edit
> -- the *.m4 files instead. (Files _should_ be re-created by
> -- typing 'make' at the command line.)
>
>
>
> -- Run a bunch of queries reporting problems in the database.
> --
> -- $Id: check_integrity.m4,v 1.9 2004/11/08 02:23:04 kop Exp $
>
> --
> -- MATUREDATES
> --
>
> -- Warning condition
> -- It's unusual to have a mature date within 3 years of the birthdate,
> -- although 2 lodge females have managed it.
> SELECT biograph.sname, birth, matured
> , 'Warning: Matdate within 3 years of birthdate.'
> FROM biograph, maturedates
> WHERE biograph.sname = maturedates.sname
> AND birth >= matured - CAST ('3 years' AS interval)
> ORDER BY biograph.sname;
>
> -- Warning condition
> -- It's unusual to have a mature date more than 7 years after the birthdate,
> -- although ALE the immigrent anubis male has managed it.
> SELECT biograph.sname, birth, matured
> , 'Warning: Matdate 7 years or more after birthdate.'
> FROM biograph, maturedates
> WHERE biograph.sname = maturedates.sname
> AND birth <= matured - CAST ('7 years' AS interval)
> ORDER BY biograph.sname;
>
> -- Warning condition
> -- It's unusual to have a mature female with no sexual cycles on
> -- or after the sexual maturity date.
> SELECT sname, matured
> , 'Warning: Mature female with no CYCPOINTS rows on or after maturity'
> FROM maturedates, biograph
> WHERE biograph.sname = maturedates.sname
> AND biograph.sex = 'F'
> AND maturdates.sname
> NOT IN (SELECT cycpoints.sname
> FROM cycpoints
> WHERE cycpoints.sname = maturedates.sname
> AND cycpoints.date >= maturedates.date);
>
> --
> -- DISPERSEDATES
> --
>
> -- Warning condition
> -- Dispersal before maturity.
> SELECT dispersedates.sname, dispersedates.dispersed, maturedates.matured
> , 'Warning: Dispersed before maturity'
> FROM dispersedates LEFT OUTER JOIN maturedates
> ON (maturedates.sname = dispersedates.sname)
> WHERE maturedates.matured IS NULL
> OR dispersedates.dispersed < maturedates.matured
> ORDER BY dispersedates.sname;
> --
> -- PREGS
> --
>
> -- Not needed because we now generate the parity
> -- -- Error condition
> -- -- Parity, modulo 100, must be contigious.
> -- SELECT pid, parity
> -- , 'Error: Parity out of order'
> -- FROM pregs outerpregs
> -- , cycpoints as outercycpoints
> -- , cycles as outercycles
> -- WHERE parity % 100 != 1
> -- AND outerpregs.conceive = outercycpoints.cpid
> -- AND outercycles.cid = outercycpoints.cid
> -- AND NOT EXISTS
> -- (SELECT 1
> -- FROM pregs AS innerpregs
> -- , cycpoints as innercycpoints
> -- , cycles AS innercycles
> -- WHERE innercycles.sname = outercycles.sname
> -- AND innercycpoints.cid = innercycles.cid
> -- AND innerpregs.conceive = innercycpoints.cpid
> -- AND outerpregs.parity % 100 = innerpregs.parity % 100 + 1
> -- LIMIT 1)
> -- ORDER BY pid, parity;
>
> -- Not needed, checked for in the triggers.
> -- -- Warning condition
> -- -- resumption of menses cycle should not have a mdate.
> -- SELECT devent.sname, pregs.parity, pregs.pid
> -- , mevent.date, mevent.edate, mevent.ldate
> -- , 'Warning: Mdate on first cycle after pregnancy'
> -- FROM pregs, cycpoints devent, cycpoints mevent
> -- WHERE pregs.resume = devent.cpid
> -- AND devent.cid = mevent.cid
> -- AND mevent.code = 'M'
> -- ORDER BY devent.sname, pregs.parity;
>
> -- We check below that you can't cycle while pregnant.
> -- -- Warning condition
> -- -- More than one pregs/female with no resume.
> -- SELECT conception.sname, bad_pregs.pid, bad_pregs.parity
> -- , 'Warning: more than one PREGS row/female with no resume cycle'
> -- FROM pregs AS bad_pregs, pregs AS other_pregs
> -- , cycpoints AS conception, cycpoints AS bad_conception
> -- WHERE other_pregs.conceive = conception.cpid
> -- AND bad_pregs.conceive = bad_conception.cpid
> -- AND bad_conception.sname = conception.sname
> -- AND bad_pregs.resume IS NULL
> -- AND other_pregs.resume IS NULL
> -- AND bad_pregs.oid != other_pregs.oid
> -- ORDER BY conception.sname, bad_pregs.pid, bad_pregs.parity;
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Babase mailing list
> Babase at www.eco.princeton.edu
> http://www.eco.princeton.edu/mailman/listinfo/babase
More information about the Babase
mailing list