Re: how to write binary data into BAT tail?

28 Jun 2013

      hi Martin,
I know the binary copy utility in postgresql there is a similar
function *PQputCopyData,
it can reduce time by 20 times .*
the following is the function i write my result into database* PostgreSQL:*
*
*
void *writeToDBBinary*(struct SAMPLE *points, char *fileName, int fileType)
{ //my process result is in input parameter struct SAMPLE
PGconn *conn = NULL;
    PGresult *pgrst = NULL;
conn = PQsetdbLogin(host, port, options, tty, dbname, user, password);
char *buf = (char*) malloc(1024 * sizeof (char));
unsigned short fieldNum = 21;
    char *sendHeader = "PGCOPY\n\377\r\n\0";
    unsigned int zero = '\0';
    struct strBuffer strBuf;
    strBuf.data = buf;
    strBuf.len = MAX_BUFFER;
int i = 0;
    int j = 0;
    int k = 0;
    struct SAMPLE *tSample;
    if (fileType) { //insert reference file
        //total 19 column, not include cid
        sprintf(sqlBuf, "*COPY %s(\
*
*
starid,crossid,catid,magnorm,ra,dec,background,classstar,ellipticity,flags,mag,mage,fwhm,pixx,pixy,thetaimage,vignet,\
*
*            magcalib,magcalibe,pixx1,pixy1 *
*            )FROM STDIN WITH BINARY", match_table);*
        pgrst = PQexec(conn, sqlBuf);
        if (PQresultStatus(pgrst) == PGRES_COPY_IN) {
            strBuf.cursor = 0;
            memcpy(strBuf.data, sendHeader, 11);
            strBuf.cursor += 11;
            memcpy(strBuf.data + strBuf.cursor, (char*) &zero, 4);
            strBuf.cursor += 4;
            memcpy(strBuf.data + strBuf.cursor, (char*) &zero, 4);
            strBuf.cursor += 4;
tSample = points->next;
            while (tSample) {
                addInt16(&strBuf, fieldNum); //column number, when add or
delete colume, must change this number
                addInt64(&strBuf, tSample->id);
                addInt64(&strBuf, tSample->crossid);
                addInt64(&strBuf, catid);
                addFloat8(&strBuf, -1.0);
                addFloat8(&strBuf, tSample->alpha);
                addFloat8(&strBuf, tSample->delta);
                addFloat8(&strBuf, tSample->background);
                addFloat8(&strBuf, tSample->classstar);
                addFloat8(&strBuf, tSample->ellipticity);
                addFloat8(&strBuf, tSample->flags);
                addFloat8(&strBuf, tSample->mag);
                addFloat8(&strBuf, tSample->mage);
                addFloat8(&strBuf, tSample->fwhm);
                addFloat8(&strBuf, tSample->pixx);
                addFloat8(&strBuf, tSample->pixy);
                addFloat8(&strBuf, tSample->thetaimage);
                addFloat8(&strBuf, tSample->vignet);
                addFloat8(&strBuf, tSample->magcalib);
                addFloat8(&strBuf, tSample->magcalibe);
                addFloat8(&strBuf, tSample->pixx);
                addFloat8(&strBuf, tSample->pixy);
                int copydatares = *PQputCopyData*(conn, strBuf.data,
strBuf.cursor);
                //if(i >5) break;
                i++;
                tSample = tSample->next;
                strBuf.cursor = 0;
            }
*  PQputCopyEnd(conn, NULL);*
        } else {
            printf("can not copy in!\n");
        }
        PQclear(pgrst);
so i am sure it's worthy to implement binarycopy in monetdb.
2013/6/28 Martin Kersten martin@monetdb.org
...
Hi
On 6/28/13 7:25 AM, hlfwm hlfwm wrote:
...
i want to create a monetdb function to help write my astronomical
*binary* processing result into database,this function will load the binary
result from memory directly into monetdb to save time,  so i defined a UDF
binaryCopy() ,
I am not sure such a function would improve your system much, because
it depends on the relative time compared to your astronomical processing.
Furthermore, to make sense you have to go all the way to link your
astro library as UDF as well.
Another issue is that you bypass the transaction management layers,
which makes the system sensitive for incorrectness.
In general, it is better to first identify 'how' bad the (BINARY) COPY
into would be in the total setup.
Martin
...
svom.c
int binaryCopy(str columnname, str buffer, int count)
{
     mvc *sql = NULL;
     sql_table *tmatch = NULL;
    /*BATs in which the data of the columns of the output table will be
stored*/
    BAT *tmatch_column = NULL;
    BUN nr_rows;
    /* pointer to the tail of above BAT, which is just a normal C-array */
    lng *tmatch_column_lng_t = NULL;
    flt *tmatch_column_flt_t = NULL;
    /*_bind_table is just a wrapper that tries to see if it can find the
table
     match_table under three default schemas*/
    if(!(tmatch = _bind_table(sql, NULL, "testtable")))
        //return sql_message("42S02!BINARYLOAD(**):no such table test");
        return 0;
    //nr_match = 20000;
    nr_rows = count;
/*
  create BATs for all columns of the output table 'tmatch'
*/
    if(strcmp(columnname,"starid")**==0 || strcmp(columnname,"crossid")==
**0 ||strcmp(columnname,"catid")==**0 ) {
         tmatch_column = BATnew(TYPE_void, TYPE_lng,nr_rows);
    }else {
         tmatch_column = BATnew(TYPE_void,TYPE_flt,nr_**rows);
    }
/*
  now tmatch_starid_t points to the first element of the BAT's tails
*/
    tmatch_column_lng_t = (lng*)Tloc(tmatch_column,**
BUNfirst(tmatch_column));
/*
     now we can write data into the BAT tail
*/
    if((strcmp(columnname,"starid"**)==0) ||
(strcmp(columnname,"crossid")=**=0) ||(strcmp(columnname,"catid")=**=0)
) {
*memcpy(tmatch_column_lng_t,**buffer,nr_rows*sizeof(lng));  //is it
right to write binary data into BAT???*
}else {
    memcpy(tmatch_column_flt_t, buffer, nr_rows*sizeof(flt));
}

/* Set proper properties for all BATs */
BATsetcount(tmatch_column,nr_**rows);

BATseqbase(tmatch_column,0);

/*
sorted:1,column is sorted in ascending order
*/
    tmatch_column->tsorted = 0;
/*
  if the tail is reverse sorted.
*/
    tmatch_column->trevsorted = 0;
/*
  if the tail contains NIL values
*/
    tmatch_column->T->nil = 0;
/*
if the tail doesn't contain NIL values
*/
    tmatch_column->T->nonil = 0;
/*
if the values in the tail are unique.Since BATkey only works with BAT
heads,

BATmirror is used to switch the head and tail of the BATs

*/
    if(strcmp(columnname,"starid")**==0) {
        BATkey(BATmirror(tmatch_**column),1); //only starid is 1
    }else {
    BATkey(BATmirror(tmatch_**column),0);
    }
/*
finally, append the result BATs to the result table

ignore errors, since we are finishing

*/
    _append_bat(sql,tmatch,**columnname,tmatch_column);
    /*
  The BATs now contain the data for the columns of the output tables,

append_bat registers in the system where to find the BAT for those

columns.
*/
/*
  To avoid memory leak, BAT reference counter must be decreased after use.

That can be done using BBPunfix.

*/
    if(tmatch_column) BBPunfix(tmatch_column->**batCacheid);
return 1; //success

}
Thanks!
______________________________**_________________
users-list mailing list
users-list@monetdb.org
http://mail.monetdb.org/**mailman/listinfo/users-listhttp://mail.monetdb.org/mailman/listinfo/users-list
______________________________**_________________
users-list mailing list
users-list@monetdb.org
http://mail.monetdb.org/**mailman/listinfo/users-listhttp://mail.monetdb.org/mailman/listinfo/users-list