sqlCopy.c File Reference

sqlCopy buffers sql inserts and performs batch copy's to the database. Why do this? Because this method is roughtly 15x faster than individual sql inserts for a typical fossology table insert. More...

#include "sqlCopy.h"

Include dependency graph for sqlCopy.c:

Go to the source code of this file.

Macros
#define	growby   128
	Grow DataBuf by this number of bytes.

Functions
psqlCopy_t	fo_sqlCopyCreate (PGconn *pGconn, char *TableName, int BufSize, int NumColumns,...)
	Constructor for sqlCopy_struct. More...
int	fo_sqlCopyAdd (psqlCopy_t pCopy, char *DataRow)
	Add a data row to an sqlCopy Use '\N' to pass in a null. More...
int	fo_sqlCopyExecute (psqlCopy_t pCopy)
	Execute the copy (ie insert the buffered records into the database). More...
void	fo_sqlCopyDestroy (psqlCopy_t pCopy, int ExecuteFlag)
	Destructor for sqlCopy_struct. More...
void	fo_sqlCopyPrint (psqlCopy_t pCopy, int PrintBytes)
	Print the sqlCopy_struct. More...

Detailed Description

sqlCopy buffers sql inserts and performs batch copy's to the database. Why do this? Because this method is roughtly 15x faster than individual sql inserts for a typical fossology table insert.

Note that data to be inserted is stored in memory (pCopy->DataBuf), not an external file. So the caller should give some consideration to the number of records buffered.

How to use:
1. Get an sqlCopy_struct pointer from fo_sqlCopyCreate().
2. Add records you want inserted into a single table in the
   database with fo_sqlCopyAdd().  This will buffer the recs
   until no more data will fit into DataBuf.  At that point the
   records will be added to the database table in a single copy stmt.
3. When the program is done, call fo_sqlCopyDestroy().  This will flush
   the remaining records out of the buffer and free memory.
 
Two other functions may also come in handy:
1. fo_sqlCopyExecute() will execute the copy to database immediately.
2. fo_sqlCopyPrint() will print the sqlCopy structure.
   It is good for debugging.

Definition in file sqlCopy.c.

Function Documentation

fo_sqlCopyAdd()

int fo_sqlCopyAdd	(	psqlCopy_t	pCopy,
		char *	DataRow
	)

Add a data row to an sqlCopy Use '\N' to pass in a null.

Parameters

pCopy	Pointer to sqlCopy struct
DataRow	Row to insert

 The fields in DataRow needs to be tab delimited.
 All strings should be escaped with PQescapeStringConn()

 For example, to insert a row with two character fields and an
 integer field, DataRow might look like:
   Mydata<tab>string  <tab> string number 2 <tab> 36
 This could be created by:
   snprintf(buf, sizeof(buf), "%s\t%s\t%d\n", str1, str2, val);

Returns

0 if failure

Definition at line 146 of file sqlCopy.c.

fo_sqlCopyCreate()

psqlCopy_t fo_sqlCopyCreate	(	PGconn *	pGconn,
		char *	TableName,
		int	BufSize,
		int	NumColumns,
			...
	)

Constructor for sqlCopy_struct.

Parameters

pGconn	Database connection
TableName	Table to be used
BufSize	Size of the copy buffer in bytes. If BufSize is smaller than needed to hold any single row, then BufSize is automatically increased.
NumColumns	number of column names passed in.
...	Variable char *ColumnNames

Returns

sqlCopy_struct. On failure, ERROR to stdout, return 0

Definition at line 51 of file sqlCopy.c.

fo_sqlCopyDestroy()

void fo_sqlCopyDestroy	(	psqlCopy_t	pCopy,
		int	ExecuteFlag
	)

Destructor for sqlCopy_struct.

This will execute fo_sqlCopyExecute() if the ExecuteFlag is true and there are records that need to be written.

Parameters

pCopy	Pointer to sqlCopy struct
ExecuteFlag	0 if DataRows should not be written, 1 if DataRows should be written

Returns

void

Definition at line 293 of file sqlCopy.c.

fo_sqlCopyExecute()

int fo_sqlCopyExecute

(

psqlCopy_t

pCopy

)

Execute the copy (ie insert the buffered records into the database).

Then reset pCopy (effectively empty it).

Parameters

pCopy

Pointer to sqlCopy struct

Returns

0 on Failure (with msg), 1 on success.

Definition at line 241 of file sqlCopy.c.

fo_sqlCopyPrint()

void fo_sqlCopyPrint	(	psqlCopy_t	pCopy,
		int	PrintBytes
	)

Print the sqlCopy_struct.

This is used for debugging.

Parameters

pCopy	Pointer to sqlCopy struct
PrintBytes	Number of DataBuf bytes to print. If zero, print the whole buffer.

Returns

void

Definition at line 313 of file sqlCopy.c.