/**Extract table data as CSV from a PostgreSQL dump file. This code is released under the BSD License. Copyright (c) 2006, Lee Lofgren And Accounting Enhancements, Inc All rights reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: * Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. * Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. * Neither the name of Accounting Enhancements, Inc nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission. THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. **/ #include #include int debug=0; struct structBuffer { long bufferSize; int eof; long dataSize; char *buffer; //Buffer end does not use 0x0 as EndOfLine since the data could be in binary form. Use dataSize to determine the end-of-line. FILE *inFile; }; //Get current line ending in newline or carraige return. Skip empty lines (beginning with newline or carriage return). //Don't include Lf or Cr at end of line. //Buffer does not end in 0x0 since buffer can contain binary data (other than Lf or Cr). //Use buffer->dataSize to determine end of buffer. int getLine(struct structBuffer *buffer){ if (debug>4)printf("getLine(): start\n"); int result=0; //Default to fail int done; char curChar; if (buffer!=NULL&&buffer->inFile!=NULL){ //Make sure we have a buffer and that it contains a referenced file. buffer->dataSize=0; if (feof(buffer->inFile)){ buffer->eof=1; //Done with line since we are at the end of the file result=1; } else { done=0; buffer->dataSize=0; //Empty out the previous contents and fill buffer until we get to the newline character curChar=0x0; while(feof(buffer->inFile)==0&&((curChar=(char)fgetc(buffer->inFile))==0x0A||curChar==0x0D)){} //Skip empty lines if(curChar!=0x0&&curChar!=0x0A&&curChar!=0x0D)ungetc(curChar,buffer->inFile); //Re-add last character read if it wasn't a newline or carriage return while(feof(buffer->inFile)==0 && done==0){ curChar=(char)fgetc(buffer->inFile); if (curChar==0x0A||curChar==0x0D){ done=1; //At NewLine so we are done with this line result=1; } else { if (buffer->buffer==NULL||buffer->dataSize>=buffer->bufferSize){ if (debug>4)printf("getLine(): realloc buffer to %i\n",buffer->dataSize+1024); buffer->buffer=(char *)realloc(buffer->buffer,buffer->dataSize+1024); buffer->bufferSize=buffer->dataSize+1024; } if (buffer->buffer==NULL) { done=1; //Error unreallocated if (debug>0)printf("getLine(): failed. Unable to Reallocate memory\n"); } else { buffer->buffer[buffer->dataSize]=curChar; buffer->dataSize++; } } } if (debug>4)printf("getLine(): line = '%*s'\n",buffer->dataSize,buffer->buffer); } } if (debug>4)printf("getLine(): Result='%i'\n",result); return result; } int extractTable(char *tableName, FILE *inFile, FILE *outFile){ int result=0; int curCharInt; char curChar; int done=0; int minTableLineSize=0; //Used with strncmp to limit size of compare since buffer doesn't end with a 0x0; int tableNameLen; //Used to avoid strlen() in a loop. int index; int found=0; int fieldEnd; int quoteText; struct structBuffer *buffer=(struct structBuffer *)malloc(sizeof(struct structBuffer)); buffer->buffer=NULL; buffer->bufferSize=0; buffer->eof=0; buffer->inFile=inFile; buffer->dataSize=0; if (inFile==NULL||outFile==NULL||tableName==NULL||tableName[0]==0x0){ if (debug>0)printf("extractTable(): inFile=%i, OutFile=%i, or tableName='%s' are invalid\n",inFile,outFile,tableName); } else { if (debug>0)printf("extractTable(%s): Start\n",tableName); tableNameLen=strlen(tableName); minTableLineSize=10 + tableNameLen; //4 chars for COPY, a couple of spaces and a possible quote plus the table name length should be the max size that needs to be scanned. EX: COPY "myTableName" strlen("COPY") + 2 spaces + 1 quote + strlen("myTableName") + up to 3 more unexpected spaces. while(done==0){ if (getLine(buffer)==0||buffer->eof){ done=1; //failed if(debug>0)printf("extractTable(): Failed. Unextected eof or getLine() error\n"); } else { // Of if (getLine(buffer)==0||buffer->eof) if(buffer->dataSize>=minTableLineSize&&strncmp(buffer->buffer,"COPY ",5)==0){ index=5; //don't need to scan first 5 chars of buffer since "COPY " is already accounted for. found=0; while(index+tableNameLen3)printf("extractTable(): %*s for table name\n",tableNameLen,(buffer->buffer)+index); if ((((buffer->buffer)+index-1)[0]=='"'||((buffer->buffer)+index-1)[0]==' ')&&strncmp((buffer->buffer)+index,tableName,tableNameLen)==0&&(((buffer->buffer)+index+tableNameLen)[0]=='"'||((buffer->buffer)+index+tableNameLen)[0]==' '||((buffer->buffer)+index+tableNameLen)[0]=='('))found=1; //if we found the name, make sure that it isn't part of a longer name. Don't want a false positive (tblEmp vs tblEmpDependents when searching for tblEmp). index++; //This will move ahead 1 more even if we found the name but we don't care since it gets reset to zero before re-use. } if (found){ if(debug>0)printf("extractTable(): Table Found\n"); //If here then we are on the right table!!! Write out the header line (which is already comma delimited). Don't including Parens. index=0; while(indexdataSize&&buffer->buffer[index]!='(')index++; index++; while(indexdataSize&&buffer->buffer[index]!=')')fputc(buffer->buffer[index++],outFile); //Write out header of CSV file fputc(0x0D,outFile); fputc(0x0A,outFile); if(debug>0)printf("extractTable(): header written\n"); while(done==0){ if(getLine(buffer)==0||buffer->eof){ done=1; //Failed because we didn't find EndOfTable ("\." on a new line) if(debug>0)printf("extractTable(): unexpected eof or error in getLine()\n"); } else { // Of if(getLine(buffer)==0||buffer->eof) if (buffer->dataSize==2&&buffer->buffer[0]=='\\'&&buffer->buffer[1]=='.'){ //Are we at endOfTable done=1; //Success result=1; } else { // Of if (buffer->dataSize==2&&buffer->buffer[0]=='\\'&&buffer->buffer[1]=='.') //Write out a line index=0; quoteText=0; fieldEnd=0; if(debug>2)printf("extractTable(): Working on line '%*s'\n",buffer->dataSize,buffer->buffer); while(indexdataSize){ if (index>0)fputc(',',outFile); //If not first field then comma-separate while(fieldEnddataSize&&buffer->buffer[fieldEnd]!=0x09){ if(buffer->buffer[fieldEnd]==',')quoteText=1; //If text has comma then quote text fieldEnd++; } if(debug>3)printf("extractTable(): working on field '%*s'\n",fieldEnd-index,buffer->buffer+index); if (quoteText)fputc('"',outFile); //Open Quote //Copy out while(indexbuffer[index]=='\\'){ //Change Special characters back to binary values. if (index+1>=buffer->dataSize)fputc('\\',outFile); //If at EndOfLine then slash should stay since it obviously isn't part of a special character. else if (buffer->buffer[index+1]=='\\'){fputc('\\',outFile);index++;} else if (buffer->buffer[index+1]=='b'){fputc(0x08,outFile);index++;} else if (buffer->buffer[index+1]=='f'){fputc(0x0C,outFile);index++;} else if (buffer->buffer[index+1]=='n'){fputc(0x0A,outFile);index++;} else if (buffer->buffer[index+1]=='r'){fputc(0x0D,outFile);index++;} else if (buffer->buffer[index+1]=='t'){fputc(0x09,outFile);index++;} else if (buffer->buffer[index+1]=='v'){fputc(0x0B,outFile);index++;} else if (buffer->buffer[index+1]=='N'){index++;} else fputc('\\',outFile); //Treat slash with no valid special characters as a slash. } else { fputc(buffer->buffer[index],outFile); } index++; } if (quoteText)fputc('"',outFile); //Close Quote quoteText=0; fieldEnd++; //Move to next field index=fieldEnd; } fputc(0x0D,outFile); //End CSV line with CarriageReturn/Linefeed fputc(0x0A,outFile); } // Of Else Of if (buffer->dataSize==2&&buffer->buffer[0]=='\\'&&buffer->buffer[1]=='.') } //Of Else Of if(getLine(buffer)==0||buffer->eof) } // Of while(done==0) } //Of if(found) } //Of if(buffer->dataSize>=minTableLineSize&&strncmp(buffer->buffer,"COPY ",6)==0) } // Of Else Of if (getLine(buffer)==0||buffer->eof) } // Of while(done==0) if(debug>0)printf("extractTable(): done writting data\n"); } // Of if (inFile!=NULL&&outFile!=NULL&&tableName!=NULL&&tableName[0]!=0x0) if (buffer!=NULL){ if (buffer->buffer!=NULL)free(buffer->buffer); free(buffer); } if (debug>0)printf("extractTable(): Result='%i'\n",result); return result; } int main(int argc, char *argv[]){ int result=1; char *tableName=NULL; FILE *inFile=stdin; FILE *outFile=stdout; if (argc<2||argc>4|| argv[1][0]=='-'||argv[1][0]=='?'){ printf("Usage: %s TableNameToExtract [InputFile] [OutputFile]\n\nEx: %s tblRptContribution infile.psql outfile.csv\nYou don't need to quote the table name and it is case sensitive\n\nIf InputFile and OutputFile are left blank then this program can be used as part of a pipe stream.\nEx: openssl enc -d -aes-256-cbc -in encdump.psql -pass pass:mySecretPassword | funzip | %s myTableName | convertCsvToXls > myTableName.xls\n\nCopyright (c) 2006, Lee Lofgren and Accounting Enhancements\nThis program is Open Source and released under the BSD License.\nAll rights reserved.\nRedistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:\n * Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.\n * Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.\n * Neither the name of Accounting Enhancements, Inc nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.\n THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS \"AS IS\" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.",argv[0],argv[0],argv[0]); } else { tableName=argv[1]; if (argc>2)inFile=fopen(argv[2],"rb"); if (argc>3)outFile=fopen(argv[3],"wb"); result=extractTable(tableName,inFile,outFile); if (inFile!=NULL&&inFile!=stdin)fclose(inFile); if (outFile!=NULL&&outFile!=stdout)fclose(outFile); if (result==0)result=1; else result=0; } return result; }