Category : C Source Code
Archive   : SQLVER1.ZIP
Filename : TABLE.C

#ifndef COMPILE
#include "C:\LANGUAGE\C\TURBO.C\40.570\DEFS.H"
#endif

/* */
/*****************************************************************************/
/*
void makeview( )
-This routine creates a view/table when the user has selected only one
table for the view. It's purpose is to create a temporary table that
satisfies the selection criteria and doesn't contain deleted tuples.

Calls:
------
-checktuple ( );
-createtempfile( );
-fields ( );
-joinfields ( );
-makelist ( );
-maketable ( );
-readtuple ( );
-recycle ( );
-writetuple ( );

Called By:
----------
-select( );
*/
void makeview(temptable,table,predicate)
char *temptable,
*table,
*predicate;
{
FILE *source,
*destination;

struct LIST *list = fields(source = fopen(table,"r")),
*tuple = makelist(list);

createtempfile(temptable);
list = joinfields(table,list,0);
maketable(temptable,list->rlist);
destination = fopen(temptable,"a");

while(feof(source) == 0)
{
readtuple(tuple,source);
if (tuple->data.attribute[0] == '0')
if (checktuple(tuple,list,predicate))
{
writetuple(tuple->rlist,destination);
fputc(RECORD,destination);
}
}

fclose(destination);
fclose(source);
recycle(tuple);
recycle(list);

}

/* */
/*****************************************************************************/
/*
void maketable( );
-This routine is used to create a table by giving it a list that
contains all of the attributes for the table.

Calls:
------
-None

Called by:
----------
-create ( );
-createview ( );
-makedistinct ( );
-makeview ( );
-reordertable ( );

*/
void maketable(table,list)
char *table;
struct LIST *list;
{
FILE *fp;

strcat(table,TAB);
fp = fopen(table,"w"); /* create the table */

fprintf(fp,"%s %d %d%c",DEL,0,1,RECORD); /* delete flag attribute */

/* write table's structure to the header of the table */
while (list)
{
fprintf(fp,"%s %d %d%c",list->data.attribute,
list->data.type,
list->data.length,
RECORD);
list = list->rlist;
}
fputc(EOH,fp); /* mark end of header */
fclose(fp);

}

/* */
/****************************************************************************/
/*
void deletetuple( );

-This routine is used to mark a tuple as being deleted by writing the
character '1' to the "DEL" field.

Calls:
------
-None

Called By:
----------
-delete( );

*/
void deletetuple(fp)
FILE *fp;
{
fseek(fp,ftell(fp),SEEK_SET);
putc('1',fp); /* mark tuple as deleted */
fseek(fp,ftell(fp),SEEK_SET);
}

/* */
/****************************************************************************/
/*
void copytuple( );

-This routine is used to copy a constant value over a tuple that is
being processed.

Calls:
------
-None

Called By:
----------
-update( );
*/
void copytuple(tuplelist,constantlist,fieldlist,list)
struct LIST *tuplelist,
*constantlist,
*fieldlist,
*list;
{

while(list)
{

if (strcmp(list->data.attribute,fieldlist->data.attribute) == 0)
if(list->data.type == 0)
strcpy(tuplelist->data.attribute,constantlist->data.attribute);
else
tuplelist->data.value = constantlist->data.value;

fieldlist = fieldlist->rlist;
constantlist = constantlist->rlist;
tuplelist = tuplelist->rlist;
list = list->rlist;
}

}
/* */
/*****************************************************************************/
/*
void writetuple( );

-This routine writes a tuple to a table. Moreover, it writes the
necessary delimiters between each field so that they can later
be distinguished.

Calls:
------
-None

Called By:
----------
-insert ( );
-jointables ( );
-makedistinct( );
-makeview ( );
-reordertable( );
-update ( );


*/
void writetuple(datalist,fp)
struct LIST *datalist;
FILE *fp;
{

fputc('0',fp); /* mark tuple as not deleted */
fputc(FIELD,fp);

/* write constants for the new tuple to the file */
while(datalist)
{
if (datalist->data.type == 0)
{
char *fh = datalist->data.attribute;
while(*fh) fputc(*fh++,fp);
}
else
fprintf(fp,"%6ld",datalist->data.value);

datalist = datalist->rlist;
if (datalist) putc(FIELD,fp);
}

}
/* */
/*****************************************************************************/
/*
int readtuple( );

-This routine is used to read a tuple from a table. Moreover,
it separates the tuple into it's respective attributes.

Calls:
------
-None

Called By:
----------
-delete ( );
-jointables ( );
-makedistinct( );
-makeview ( );
-printfile ( );
-reordertable( );
-update ( );


*/
int readtuple(tuple,fp)
struct LIST *tuple;
FILE *fp;
{

while (tuple)
{
char *fh = tuple->data.attribute;
*fh = NULL;
while(feof(fp) == 0)
{
*fh = fgetc(fp);
if (*fh == FIELD) break;
if (*fh == RECORD) break;
*fh++;
}
*fh = NULL;
if(tuple->data.type) tuple->data.value = atol(tuple->data.attribute);
tuple = tuple->rlist;
}

return(0);
}

/* */
/*****************************************************************************/
/*
int makedistinct( )
-This routine creates a table that has no duplicate tuples in it.

Calls:
------
-checktuple ( );
-createtempfile ( );
-fields ( );
-joinfields ( );
-makelist ( );
-maketable ( );
-readtuple ( );
-recycle ( );
-writetuple ( );

Called by:
----------
-select ( );
-reordertable ( );

*/
void makedistinct(table,tempfile,predicate)
char *table,
*tempfile,
*predicate;
{
FILE *destination,
*source;
struct LIST *list = fields(source = fopen(table,"r")),
*tuplelist = makelist(list);

createtempfile(tempfile);
maketable(tempfile,list->rlist); /* move beyond the DEL attribute */
list = joinfields(table,list,0);

while (feof(source) == 0)
{
readtuple(tuplelist,source);
if (tuplelist->data.attribute[0] == '0') /* ignore deleted records */
{
int identical = 0;
struct LIST *dlist = fields(destination = fopen(tempfile,"r")) ;

while(feof(destination) == 0)
{
struct LIST *spointer = list,
*tpointer = tuplelist,
*dpointer = dlist;

readtuple(dpointer,destination);

while(spointer) /* check to see if the tuple is unique */
{
if (spointer->data.type == 0)
if (strcmp(dpointer->data.attribute,tpointer->data.attribute) != 0)
break;

if (spointer->data.type)
if (tpointer->data.value != dpointer->data.value)
break;

spointer = spointer->rlist;
dpointer = dpointer->rlist;
tpointer = tpointer->rlist;
}

if (spointer)
identical = 0;
else
{
identical = 1;
break;
}
}

fclose(destination);
recycle(dlist);

if (identical == 0) /* only add record if it is unique */
if (checktuple(tuplelist,list,predicate))
{
destination = fopen(tempfile,"a");
writetuple(tuplelist->rlist,destination);
fputc(RECORD,destination); /* add delimiter between tuples */
fclose(destination);
}

}

}
strcat(predicate,"");
fclose(destination);
fclose(source);
recycle(list);
recycle(tuplelist);

}

/* */
/*****************************************************************************/
/*
void printfile( )
-This routine prints a table on the screen.

Calls:
------
-checktuple ( );
-fields ( );
-joinfields ( );
-makelist ( );
-printheader( );
-printlist ( );
-readtuple ( );
-recycle ( );

Called By:
----------
-join ( );
-makedistinct( );
-reordertable( );
-select ( );

*/
void printfile(table,predicate)
char *table,
*predicate;
{

int selected = 0;
FILE *fp;
struct LIST *list = fields(fp = fopen(table,"r+t")), /* get table's fields */
*tuplelist = makelist(list);

printheader(list->rlist); /* print attribute header */
list = joinfields(table,list,0);
while (feof(fp) == 0)
{
readtuple(tuplelist,fp);
if (tuplelist->data.attribute[0] == '0') /* is tuple deleted ? */
if (checktuple(tuplelist,list,predicate))
{
selected++;
printlist(tuplelist->rlist); /* point beyond DEL */
printf("\n");
}
}

recycle(tuplelist);
recycle(list);
fclose(fp);
printf("\n %d Tuple(s) Selected\n",selected);

}

/* */
/***************************************************************************/
/*
int file ( );

-This routine is used to determine if a table has already exists.
If the table does exist, 99 is returned, otherwise zero is returned.

Calls:
------
-None

Called By:
----------
-command ( );
-createtempfile ( );

*/
int file(table)
char *table;
{
FILE *fp;
char checkfile[15];
strcpy(checkfile,table);
strcat(checkfile,TAB);
if ((fp = fopen(checkfile,"r+")) == NULL) return(0);
fclose(fp);
return(99);
}

/* */
/****************************************************************************/
/*
void createtempfile( );
-This routine will create a filename that does not exist so that it
can later be opened and used temporarily. It should be deleted
after it has been used.

Calls:
------
-file( );
Called By:
----------
-makedistinct( );
-makeview ( );
-reordertable( );

*/
void createtempfile(fname)
char *fname;
{

char number[17] = "",
tname[17] = "S";
int i = 0;

while(file(tname) != 0) /* process until we have a new file name */
{
strcpy(tname,"S");
strcat(tname,itoa(i,number,10));
i++;
}

strcpy(fname,tname);

}

/* */
/****************************************************************************/
/*
void reordertable( )
-This routine takes a list of attributes and a table name and reorders
those attributes from the original table into the new desired format.

Calls:
------
-checktuple ( );
-createtempfile ( );
-fields ( );
-makedistinct ( );
-makelist ( );
-maketable ( );
-readtuple ( );
-recycle ( );
-writetuple ( );
-usegarbage ( );

Called By:
----------
-join ( );
-select ( );


*/
void reordertable(fieldlist,tablelist,distinct,tempfile,predicate)
struct LIST *fieldlist,
*tablelist;
int distinct;
char *tempfile,
*predicate;
{

int fieldnum = 1;

FILE *source,
*destination;

struct LIST *list = fields(source = fopen(tablelist->data.attribute,"r")),
*tfieldlist = fieldlist,
*tuplelist = makelist(list),
*field = NULL,
*tuple = NULL;

createtempfile(tempfile);

/* insert the table's name before all of the fields */
list = joinfields(tablelist->data.attribute,list,0);

/* order the attributes in the source file to the desired order */
while(tfieldlist)
{
struct LIST *tlist = list->rlist,
*ttuplelist = tuplelist->rlist;
while(tlist)
if (strcmp(tfieldlist->data.attribute,tlist->data.attribute) == 0)
{
tfieldlist->data.type = tlist->data.type;
tfieldlist->data.value = tlist->data.value;
tfieldlist->data.length = tlist->data.length;
ttuplelist->data.order = fieldnum;
break;
}
else
{
tlist = tlist->rlist;
ttuplelist = ttuplelist->rlist;
}
tfieldlist = tfieldlist->rlist;
fieldnum++;
}

maketable(tempfile,fieldlist);
field = fields(destination = fopen(tempfile,"r"));
fclose(destination);
destination = fopen(tempfile,"a");
tuple = makelist(field);

while(feof(source) == 0)
{
readtuple(tuplelist,source);
if (tuplelist->data.attribute[0] == '0') /* is tuple deleted */
{
fieldnum = 1;
tfieldlist = tuple->rlist;

/* copy source tuple to destination tuple in the order desired */
while(tfieldlist)
{
struct LIST *ttuplelist = tuplelist->rlist;
while(ttuplelist->data.order != fieldnum)
ttuplelist = ttuplelist->rlist;
if (ttuplelist->data.type)
tfieldlist->data.value = ttuplelist->data.value;
else
strcpy(tfieldlist->data.attribute,ttuplelist->data.attribute);

tfieldlist = tfieldlist->rlist;
fieldnum++;
}
if (checktuple(tuplelist,list,predicate))
{
writetuple(tuple->rlist,destination);
fputc(RECORD,destination); /* add delimiter between tuples */
}
}
}

strcpy(predicate,""); /* reset the predicate. it is no longer needed */
recycle(field);
recycle(list);
recycle(tuple);
recycle(tuplelist);

fclose(source);
fclose(destination);

if (distinct)
{
char tempfile1[17] = "";
makedistinct(tempfile,tempfile1,predicate);
unlink(tempfile);
strcpy(tempfile,tempfile1);
}

}

/* */
/****************************************************************************/
/*
void join( )
-This routine sets up the linked lists and table to join the variable
number of tables.

Calls:
------
-createtempfile ( );
-fields ( );
-joinfields ( );
-jointables ( );
-makedistinct ( );
-makelist ( );
-maketable ( );
-recycle ( );
-reordertable ( );
-tablelistfields( );



Called By:
----------
-select ( );

*/

void join(tablelist,selectlist,distinct,tempfile,predicate)
struct LIST *tablelist,
*selectlist;
int distinct;
char *tempfile,
*predicate;
{

FILE *jointable;
struct LIST *attributes = NULL,
*jointuple = NULL;

createtempfile(tempfile);
attributes = tablelistfields(tablelist);

/* create new file structure of tables to be joined */
maketable(tempfile,attributes);

recycle(attributes);
attributes = fields(jointable = fopen(tempfile,"r"));
fclose(jointable);
jointable = fopen(tempfile,"a");
jointuple = makelist(attributes);

/* join all of the tables in the tablelist */
jointables(jointable,attributes,jointuple,tablelist,predicate);
strcpy(predicate,""); /* reset the predicate. it is no longer needed */

fclose(jointable);
recycle(jointuple);
recycle(attributes);

strcpy(tablelist->data.attribute,tempfile);

if (selectlist)
{
selectlist = joinfields(tempfile,selectlist,0);
reordertable(selectlist,tablelist,distinct,tempfile,predicate);
unlink(tablelist->data.attribute);
}
else
if (distinct)
{
makedistinct(tablelist->data.attribute,tempfile,predicate);
unlink(tablelist->data.attribute);
}

}

/* */
/****************************************************************************/
/*
void copyjointuple( )
-This routine copies data from a tuple of one table into the jointuple
which will contain the data from all tuples (1 from each table) that
are invloved in the join operation.

Calls:
------
-None

Called By:
----------
-jointables( );

*/
void copyjointuple(joinfield,field,jointuple,tuple)
struct LIST *joinfield,
*field,
*jointuple,
*tuple;

{

/* move all pointers past the DEL attribute */
field = field->rlist;
tuple = tuple->rlist;
joinfield = joinfield->rlist;
jointuple = jointuple->rlist;

while(joinfield)
{
if (strcmp(joinfield->data.attribute,field->data.attribute) == 0)
{
if(joinfield->data.type)
jointuple->data.value = tuple->data.value;
else
strcpy(jointuple->data.attribute,tuple->data.attribute);
field = field->rlist;
tuple = tuple->rlist;
}
if (field == NULL) break; /* no more attributes to copy */
joinfield = joinfield->rlist;
jointuple = jointuple->rlist;
}
}

/* */
/****************************************************************************/
/*
void jointables( )
-This routine joins a variable number of tables. The approach used is
to first read the first tuple from all of the tables, then upon
reaching the last table, read it (the entire table) determine if
the predicate is satisfied and write it to disk if it is. Having
processed one tuple from all of the previous tables (except the
last) the recursion will bring us back to read the second tuple
from the previous tables. The recursion will provide a way to
get the cross product of all of the tables. When the last tuple
in the first table and the last tuple in the last table have been
read, then the join is complete.

Calls:
------
-checktuple ( );
-copyjointuple( );
-fields ( );
-joinfields ( );
-jointables ( );
-makelist ( );
-readtuple ( );
-recycle ( );
-writetuple ( );

Called By:
----------
-join ( );
-jointables( );

*/
void jointables(jointable,joinheader,jointuple,tablelist,predicate)
FILE *jointable;
struct LIST *joinheader,
*jointuple,
*tablelist;
char *predicate;
{
FILE *table;

struct LIST *field = fields(table = fopen(tablelist->data.attribute,"r")),
*tuple = makelist(field);
field = joinfields(tablelist->data.attribute,field,0);

while(feof(table) == 0)
{
readtuple(tuple,table);
/* ignore deleted records */
if (tuple->data.attribute[0] == '1') continue;
if (feof(table)) break;
copyjointuple(joinheader,field,jointuple,tuple);
if (tablelist->rlist)
jointables(jointable,joinheader,jointuple,tablelist->rlist,predicate);
else
if (checktuple(jointuple,joinheader,predicate))
{
writetuple(jointuple->rlist,jointable);
fputc(RECORD,jointable);
}
}

clearerr(table);
fclose(table);
recycle(field);
recycle(tuple);

}

/* */
/****************************************************************************/