Re: [mdb-dev] counting fixed / variable data rows

[Brian B. <ca...@um...>]

My understanding is that this can only happen like this (forgive the SQL
syntax):

create atable (col1 int, col2 varchar(20))

now populate it

insert atable values(1,"test 1");
insert atable values(2,"test 2");
insert atable values(3,"test 3");

then go back and add a column

alter table atable add col3 int null;

so all rows already present in the database have 2 columns of data.  The
third column being null is not present. if I then do this:

update atable set col3 = col1 where col1 <= 2;

then rows 1 and 2 have 3 rows and the unmodified rows (row 3) has 2.

So, you can't actually have pieces of rows stored in two locations, but
you may have less than the current number of columns in a particular row.
Most of this, I am guessing at based on how Sybase/MS SQL/Oracle/et al.
act and from what appears to be otherwise extraneous overhead in each row
(why have a column count in each row if there is no other possible value
but the number of columns for that table). 

This change (with the slight mod) is in CVS.

Brian

On Tue, 14 Mar 2000 kn...@gr... wrote:

>    Date: Tue, 14 Mar 2000 08:27:41 -0500 (EST)
>    From: Brian Bruns <ca...@um...>
>    List-Id: MDB Tools development <mdbtools-dev.lists.sourceforge.net>
> 
>    Hi Karl,
> 
>    > On Mon, 13 Mar 2000 kn...@gr... wrote:
>    > 
>    > > The calculation of number of fixed and variable columns doesn't work for
>    > > some database material that I have.  I have replaced a portion of
>    > > data.c:mdb_read_row() with the following (perhaps non-optimal, but more
>    > > accurate) algorithm.
>    > > 
>    > >        /* find out all the important stuff about the row */
>    > > 
>    > >        num_cols = mdb->pg_buf [row_start];
>    > >        var_cols = 0;
>    > >        fixed_cols = 0;
>    > >        for (j = 0; j < table->num_cols; j++)
> 
>    How about if we change this  ^  to just num_cols, I think that'll take
>    care of my concern...I'll put this in and look at the other stuff tonight
>    (when I have a little more time, heading out the door now).
> 
> I was toying with that.  I didn't understand if there were the possibility
> that there were less than table->num_cols on a page.  In that case, there
> might be an instance where the index (j, in this case) were not to start at
> 0 (e.g., one page has the first 4 columns, another page has the next 5, and
> a third page has the last 3; these would start 0, 4, 9, with corresponding
> changes in num_cols).  That would complicate the algorithm somewhat.
> 
> -- Karl --
> 
> _______________________________________________
> mdbtools-dev mailing list
> mdb...@li...
> http://lists.sourceforge.net/mailman/listinfo/mdbtools-dev
>