[Babase] Rankdate Changes in Babase

Karl O. Pinc kop at meme.com
Mon Mar 22 17:20:23 EDT 2010


Ok, so here's the problem:

Once upon a time it was only females that had rows in MATUREDATES.
We gave males maturity dates without updating the rules.
So, all the error messages say "female", and none of the checks
take sex into account.  Or at least those checks that are done
when the MATUREDATES table is updated.  On the PARTS table only
the females are checked against MATUREDATES.

So, a male can have a row entered in PARTS and be part of an
MCE interaction without having his maturity date checked
but when it comes time to update his maturity date the
system does check MCE interactions and we get an error.

We need a review of the maturity date rules vis males.
Or maybe a re-review and I need to double check the code
to make sure it corresponds to the written rules.   This
could just be a regular old bug and nothing nefarious
regards our decision to give males rows on MATUREDATES.

On 03/22/2010 03:50:29 PM, Niki Learn wrote:
> Sorry, I don't think it's checking rankdate, only maturedate.  Sorry
> for confusing that.  So it looks from the error like babase only 
> cares
> that the maturedate is before the MCEs (and doesn't care if there's a
> rankdate or not), which is how it should be doing it.  So I think
> that's A-okay.  But if you converted from FoxPro with all the checks
> in place then it is peculiar that BOZ has mounts before his
> maturedate...  So there might be a bug there.
> 
> -----Original Message-----
> From: babase-bounces at eeblistserv.Princeton.EDU
> [mailto:babase-bounces at eeblistserv.Princeton.EDU] On Behalf Of Karl 
> O.
> Pinc
> Sent: Monday, March 22, 2010 4:43 PM
> To: The Baboon Database Project
> Subject: Re: [Babase] Rankdate Changes in Babase
> 
> Comments interspersed throughout below.
> 
> On 03/22/2010 03:17:20 PM, Lacey Maryott Roerish wrote:
> > On Mon, Mar 22, 2010 at 4:07 PM, Niki Learn <nlearn at princeton.edu>
> > wrote:
> > 
> > >  Babase already says his matured date is 1997-05-01…?  How did
> this
> > even
> > > throw an error about MCE’s when BOZ doesn’t have a rnkdate? 
> > Shouldn’t it
> > > ignore males where there is no rnkdate??
> 
> Assuming for the moment that males are not required to have a rnkdate
> the problem is that the rule that's doing the checking when the
> maturedates are updated has a bug.  It should not try to check
> against rnkdate when no such date exists.
> 
>   And females and juveniles
> > for that
> > > matter since they don’t have rnkdates…  Karl does this SQL update
> > statement
> > > do if there’s no rnkdate?
> > >
> > I started by rolling back the maturedates table, and just did
> > everyone. I
> > didn't restrict to animals who had anything BUT the first. So it
> > caught him
> > by chance. This update had nothing to do with rnkdate, i was just
> > using a
> > function that rolls back the dates that happens to be called
> rnkdate,
> > on the
> > maturedates table.
> 
> Right.  The function is called rnkdate() because it produces a
> rnkdate-like date from a normal date.
> 
> > 
> > >
> > >
> > > So two mounts in March 1996.  Males are not supposed to get 
> mounts
> > recorded
> > > until they are mature either, right?
> > >
> > >
> > >
> > > As for how it got in…wouldn’t this data have been entered in
> FoxPro
> > and
> > > converted to Babase, presumably without full Babase controls in
> > place?
> 
> We did the conversion will all controls in place, excepting any
> bugs fixed since.
> 
> > > Maybe there wasn’t a rule to keep it from being entered into 
> > FoxPro?
> >  If
> > > that’s the case then maybe we just need to delete the erroneous
> > mounts,
> > > yes?  And fix the error message…  ha ha ha.
> > >
> > >
> > >
> > > *From:* babase-bounces at eeblistserv.Princeton.EDU [mailto:
> > > babase-bounces at eeblistserv.Princeton.EDU] *On Behalf Of *Lacey
> > Maryott
> > > Roerish
> > > *Sent:* Monday, March 22, 2010 3:39 PM
> > >
> > > *To:* The Baboon Database Project
> > > *Subject:* Re: [Babase] Rankdate Changes in Babase
> > >
> > >
> > >
> > > I have now run into an even stranger error, which I think we
> should
> > try to
> > > integrate into a conference call. It looks like babase has a rule
> > that MCE's
> > > cannot occur before a male has reached testicular enlargment.  
> So,
> > when I
> > > tried to move the dates, BOZ threw an error.
> > >
> > > update maturedates
> > > set matured = rnkdate(matured);
> > >
> > > *SQL error:*
> > >
> > > ERROR:  BOZ: 1997-05-01: Female's mounts, consortships, and
> > ejaculations
> > > must not come before the maturity date
> > >
> > > *In statement:*
> > > update maturedates
> > > set matured = rnkdate(matured);
> > >
> > >
> > >
> > > Lines 1-2: ERROR: BOZ: 1997-05-01: Female's mounts, consortships,
> > and
> > > ejaculations must not come before the maturity date
> > >
> > > At first I thought this was an issue with the function, since the
> > function
> > > is named rnkdate and we are messing with maturedates table. 
> > However,
> > BOZ
> > > doesn't even have a rankdate.  He matured in May 1997, has NO MCE 
> > in
> > 1997. I
> > > looked back and he does have some M records in 1996. So, it seems
> > like what
> > > we are really catching here is the fact that someone data was 
> > forced
> > into
> > > babase and this particular error was missed later on.
> 
> I seem to recall that we made the decision that males need not
> have a rnkdate for MCEs to be allowed.
> 
> If this is the case then I'll go ahead and fix the bug that
> checks rnkdate.
> 
> > >
> > >
> > >
> > > I'm also not sure why the error message is alerting us to a
> problem
> > with
> > > females havng these lines when BOZ is a male.
> 
> That could be another bug.  Or it could be that you're seeing the
> error when the computer is looking at BOZ but, because you're 
> updating
> the entire database "at once", there's really another female who's
> got the problem.  One that happened to interact with BOZ.
> 
> The checks done just look for errors in the data, sometimes far 
> afield
> from the row being updated.  Sometimes throughout the entire 
> database.
> When writing them I didn't focus on what might happen when updates
> were done to more than one row at a time.  At times I probably lost
> track and didn't even consider the possibility, particularly when
> I was trying to optimize for performance.  It's hard to say
> whether it's worth worrying about, but will produce strange error
> reporting.
> 
> So we may not have the problem we think we have at all.
> 
> What happens if you just update BOZ?
> 
> -- This tests an update of BOZ.
> begin transaction;
> update maturedates
>  set matured = rnkdate(matured)
>  where sname = 'BOZ';
> rollback transaction;
> 
> Are you computer savvy enough to produce a text file that
> updates one individual at a time as follows?
> If not let me know and I'll do it.
> 
> begin transaction;
> 
> update maturedates
>  set matured = rnkdate(matured)
>  where sname = 'A21";
> 
> update maturedates
>  set matured = rnkdate(matured)
>  where sname = 'ABB';
> 
> update maturedates
>  set matured = rnkdate(matured)
>  where sname = 'ABD';
> 
> ...
> 
> update maturedates
>  set matured = rnkdate(matured)
>  where sname = 'ZUR';
> 
> commit transaction;
> 
> This should tell us what's going on.
> 
> > >
> > >
> > >
> > > Anyway, I think we did uncover another error and I think it 
> merits
> > some
> > > discussion. I Am going to proceed to work on the update until we 
> > can
> > all
> > > talk about this.
> > >
> > >
> > >
> > > It looks like we already need to reschedule Thursday's as Susan's
> > schedule
> > > has a conflict...
> > >
> > >
> > >
> > > Let me know what you think
> > >
> > > Lacey
> > >
> > >
> > >
> > > On Mon, Mar 22, 2010 at 11:00 AM, Jeanne Altmann
> > <altj at princeton.edu>
> > > wrote:
> > >
> > > Excellent, thanks Lacey
> > >
> > > jeanne
> > >
> > >
> > >
> > > *From:* babase-bounces at eeblistserv.princeton.edu [mailto:
> > > babase-bounces at eeblistserv.princeton.edu] *On Behalf Of *Lacey
> > Maryott
> > > Roerish
> > > *Sent:* Monday, March 22, 2010 10:59 AM
> > >
> > >
> > > *To:* The Baboon Database Project
> > > *Subject:* Re: [Babase] Rankdate Changes in Babase
> > >
> > >
> > >
> > > I did some checking this morning and there don't seem to be
> glaring
> > errors
> > > between maturedates and rankdates tables.  No maturedates occur
> > after
> > > rankdates(which was to be expected given babase contraints, but I
> > just
> > > wanted to check). All of the matdates falling in the middle of a
> > month are
> > > equal to their rankdates counterpart, or occur in different 
> years,
> > which
> > > would would expect to see with the 'On' ranked dates.
> > >
> > >
> > >
> > > I am going to go ahead and roll all maturedates back to the 
> first,
> > and then
> > > all rankdates.  From there we can pick at larger errors like
> > rankdates and
> > > ranktypes not matching up. This at least standardizes this part 
> of
> > it!
> > >
> > >
> > >
> > > Cheers
> > >
> > > Lacey
> > >
> > > On Sat, Mar 20, 2010 at 10:03 AM, Susan Alberts 
> <alberts at duke.edu>
> > wrote:
> > >
> > > Most maturedates that don't fall on the first of the month are
> 'by'
> > dates
> > > but a number are 'on' dates from earlier decades. These used to 
> be
> > done with
> > > dates other than the first of the month -- there is nothing wrong
> > with them,
> > > but they can be changed.
> > >
> > > For rankdates, indeed most "on" dates are first of the month, but
> a
> > few are
> > > not. There were some instances where we actually saw the day on
> > which rank
> > > was attained, and entered those dates. All are from earlier
> decades
> > and all
> > > can be changed.
> > >
> > > Susan
> > >
> > >
> > >
> > > On Mar 19, 2010, at 4:22 PM, Jeanne Altmann wrote:
> > >
> > > Ah, yes. I assume all of these are ‘by’ dates.  If not. Something
> > more is
> > > wrong. Thanks for the update on progress.
> > > jeanne
> > >
> > > From: babase-bounces at eeblistserv.princeton.edu [mailto:
> > > babase-bounces at eeblistserv.princeton.edu] On Behalf Of Lacey 
> > Maryott
> > > Roerish
> > > Sent: Friday, March 19, 2010 4:15 PM
> > > To: The Baboon Database Project
> > > Subject: Re: [Babase] Rankdate Changes in Babase
> > >
> > > Susan, Jeanne, Niki and Karl
> > >
> > >    I am running into some problems with changing the rankdates to
> > the first
> > > of the month.  There is a rule that the matured date has to be
> less
> > than or
> > > equal to rank date (With good reason). However, all of the
> > maturedates that
> > > also occur mid-month are throwing an error. So, it looks as 
> though
> 
> > I
> > will
> > > have to roll those back as well. Will pick at this and try to fix
> > all of the
> > > maturedates before rolling the rankdates back.
> > >
> > >    I spoke with Karl about this and there is a work-around that
> > could fix
> > > the majority while skipping the ones that violate this rule, but
> it
> > kind of
> > > seems like if I am going to be elbow deep in the data anyway, I
> > should just
> > > fix the whole problem at once.   Does this fit with how princeton
> > feels
> > > about the timeline of this project?  I expect I can have it all
> > fixed my
> > > mid-morning monday, I just want to do some cross-checking of
> > maturedates and
> > > rankdates and make sure there aren't any glaring errors before i
> > change a
> > > whole bunch of data in 2 tables.
> > >
> > > Let me know
> > >
> > > Lacey
> > >
> > > On Fri, Mar 19, 2010 at 3:25 PM, Jeanne Altmann 
> > <altj at princeton.edu>
> > > wrote:
> > >
> > >
> > > -----Original Message-----
> > > From: babase-bounces at eeblistserv.princeton.edu
> > > [mailto:babase-bounces at eeblistserv.princeton.edu] On Behalf Of
> Karl
> > O.
> > > Pinc
> > > Sent: Friday, March 19, 2010 3:15 PM
> > > To: The Baboon Database Project
> > > Subject: Re: [Babase] Rankdate Changes in Babase
> > >
> > > On 03/19/2010 01:48:28 PM, Lacey Maryott Roerish wrote:
> > > > Susan, (et al)
> > > >
> > > >       Jeanne, Karl, Niki and I just finished discussing how to
> > handle
> > > > some
> > > > recent issues discovered in babase related to Rankdates.  It 
> can
> > be
> > > > difficult to extract number of males on a given date if some 
> > males
> > > > rankdates
> > > > occur in the middle of the month.  For this reason,  I will be
> > > > changing all
> > > > rankdates (Both BY and ON) to the beginning on the month using 
> a
> > > > function
> > > > Karl had previously developed.  This will simply make querying
> > > > easier,
> > > > and
> > > > will also sync up rankdates data with actual rank data, which
> are
> > all
> > > > recorded on the 1st of the month.
> > >
> > > We will also then put a rule in requiring that rankdates.rnkdate
> be
> > > on the first of the month.  (Once Lacey let's me know she's done
> > > changing the data.)
> > >
> > > The problem has been that nobody ever uses the rnkdate() 
> function,
> > > which sets a date to the first of the month, when comparing
> > > ranks.rnkdate with rankdates.ranked.  They'll still have to use 
> > this
> > > function when doing arbitrary date comparisons against either
> > > ranks.rnkdate or, now, rankdates.ranked so we're not really
> > > making the problem go away, but are pushing it farther to the
> > > fringes.  In any case the ranks.rnkdate/rankdate.ranked problem
> > > has been discussed every few years so it's clear there's a
> > > problem there that this change will make go away.
> > >
> > >
> > > Karl <kop at meme.com>
> > > Free Software:  "You don't pay back, you pay forward."
> > >                -- Robert A. Heinlein
> > >
> > > Agreed; it will remain a potentially valuable function that we
> need
> > to
> > > remember the situations you note.  However, with the proposed
> > change, we
> > > will have the ranked-by date regularized, which is good, and we 
> > will
> > > make moot the most common use for the function. Thanks Karl for
> the
> > > patient reminder about the function and all for brainstorming on 
> > the
> > > various issues.
> > >
> > > jeanne
> > >
> > > _______________________________________________
> > > Babase mailing list
> > > Babase at www.eco.princeton.edu
> > > http://www.eco.princeton.edu/mailman/listinfo/babase
> > >
> > > _______________________________________________
> > > Babase mailing list
> > > Babase at www.eco.princeton.edu
> > > http://www.eco.princeton.edu/mailman/listinfo/babase
> > >
> > >
> > >
> > > --
> > > - -
> > > Lacey K. Maryott Roerish
> > > Alberts Lab
> > > Department of Biology
> > > Duke University
> > > ph: 919-660-7306
> > > fax: 919-660-7293
> > > Lacey.Maryott at duke.edu
> > > _______________________________________________
> > > Babase mailing list
> > > Babase at www.eco.princeton.edu
> > > http://www.eco.princeton.edu/mailman/listinfo/babase
> > >
> > >
> > >
> > > --------------------------------------------------------
> > > Susan Alberts, Dept of Biology, Duke University, Box 90338, 
> Durham
> > NC
> > > 27708, 919-660-7272 (Ph), 919-660-7293 (Fax)
> > >
> > >
> > >
> > >   _______________________________________________
> > > Babase mailing list
> > > Babase at www.eco.princeton.edu
> > > http://www.eco.princeton.edu/mailman/listinfo/babase
> > >
> > >
> > >
> > >
> > > --
> > > - -
> > > Lacey K. Maryott Roerish
> > > Alberts Lab
> > > Department of Biology
> > > Duke University
> > > ph: 919-660-7306
> > > fax: 919-660-7293
> > > Lacey.Maryott at duke.edu
> > >
> > >
> > > _______________________________________________
> > > Babase mailing list
> > > Babase at www.eco.princeton.edu
> > > http://www.eco.princeton.edu/mailman/listinfo/babase
> > >
> > >
> > >
> > >
> > > --
> > > - -
> > > Lacey K. Maryott Roerish
> > > Alberts Lab
> > > Department of Biology
> > > Duke University
> > > ph: 919-660-7306
> > > fax: 919-660-7293
> > > Lacey.Maryott at duke.edu
> > >
> > > _______________________________________________
> > > Babase mailing list
> > > Babase at www.eco.princeton.edu
> > > http://www.eco.princeton.edu/mailman/listinfo/babase
> > >
> > >
> > 
> > 
> > -- 
> > - -
> > Lacey K. Maryott Roerish
> > Alberts Lab
> > Department of Biology
> > Duke University
> > ph: 919-660-7306
> > fax: 919-660-7293
> > Lacey.Maryott at duke.edu
> > 
> 
> ------quoted attachment------
> > _______________________________________________
> > Babase mailing list
> > Babase at www.eco.princeton.edu
> > http://www.eco.princeton.edu/mailman/listinfo/babase
> > 
> 
> 
> 
> 
> Karl <kop at meme.com>
> Free Software:  "You don't pay back, you pay forward."
>                  -- Robert A. Heinlein
> 
> 
> _______________________________________________
> Babase mailing list
> Babase at www.eco.princeton.edu
> http://www.eco.princeton.edu/mailman/listinfo/babase
> 
> 
> _______________________________________________
> Babase mailing list
> Babase at www.eco.princeton.edu
> http://www.eco.princeton.edu/mailman/listinfo/babase
> 
> 




Karl <kop at meme.com>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein




More information about the Babase mailing list