SQL Query Builder
[Estron support library]

Detailed Description

This class can be used to construct a basic sql query statement (of the type 'select', 'update' or 'insert') by simply making C calls indicating the table and the fields to query.

Its fairly limited in the range of sql syntax that it supports, but on the other hand, the code here is/should be general enough to work with any SQL implementation.


Defines

#define SQL_DBL_FMT   "%24.18g"

Typedefs

typedef struct SqlBuilder_s SqlBuilder

Enumerations

enum  SqlBuilderQType { SQL_UPDATE = 'm', SQL_INSERT = 'a', SQL_SELECT = 'q', SQL_DELETE = 'd' }

Functions

SqlBuilder * sql_builder_new (void)
SqlBuilder * sql_builder_copy (SqlBuilder *)
void sql_builder_destroy (SqlBuilder *)
void sql_builder_table (SqlBuilder *b, const gchar *tablename, SqlBuilderQType qtype)
void sql_builder_set_str (SqlBuilder *b, const gchar *tag, const gchar *val)
void sql_builder_set_char (SqlBuilder *b, const gchar *tag, gchar val)
void sql_builder_set_date (SqlBuilder *b, const gchar *tag, time_t val)
void sql_builder_set_int64 (SqlBuilder *b, const gchar *tag, gint64 val)
void sql_builder_set_int32 (SqlBuilder *b, const gchar *tag, gint32 val)
void sql_builder_set_double (SqlBuilder *b, const gchar *tag, gdouble val)
void sql_builder_where_str (SqlBuilder *b, const gchar *tag, const gchar *val, const gchar *op)
void sql_builder_where_int32 (SqlBuilder *b, const gchar *tag, gint32 val, const gchar *op)
const gchar * sql_builder_query (SqlBuilder *b)

Enumeration Type Documentation

enum SqlBuilderQType

Enumerator:
SQL_UPDATE  m == modify
SQL_INSERT  a == add
SQL_SELECT  q == query
SQL_DELETE  d == drop, delete

Definition at line 50 of file builder.h.

00050              {
00052     SQL_UPDATE = 'm',
00054     SQL_INSERT = 'a',
00056     SQL_SELECT = 'q',
00058     SQL_DELETE = 'd'
00059 } SqlBuilderQType;

Function Documentation

SqlBuilder* sql_builder_new ( void   ) 

The sql_builder_copy() routine makes a copy of the indicated object. Note that this routine is particularly useful for making a copy of a half-finished statement, and then using this copy to e.g. try out different 'where' clauses.

Definition at line 79 of file builder.c.

00080 {
00081     SqlBuilder *b = g_new (SqlBuilder, 1);
00082 
00083     b->qtype = 0; /* aka 'not initialized' */
00084 
00085     b->tag_base = g_malloc (INITIAL_BUFSZ);
00086     b->tbl_base = g_malloc (1000);
00087     b->val_base = g_malloc (INITIAL_BUFSZ);
00088     b->buflen = INITIAL_BUFSZ;
00089 
00090     b->ptag = b->tag_base;
00091     b->pval = b->val_base;
00092 
00093     /* null terminated strings */
00094     *(b->ptag) = 0x0;
00095     *(b->pval) = 0x0;
00096 
00097     b->tag_need_comma = 0;
00098     b->val_need_comma = 0;
00099     b->where_need_and = 0;
00100     b->got_where_term = 0;
00101 
00102     /* the escape area */
00103     b->escape = sqlEscape_new ();
00104     return (b);
00105 }

const gchar* sql_builder_query ( SqlBuilder *  b  ) 

The sql_builder_query() routine returns a valid SQL query statement that reflects the set of build calls just made. This string is clobbered when sql_builder_destroy() or sql_builder_Table() is called, so make a copy if you need it.

This resulting query string is probably general enough to work with almost any SQL db, I beleive.

Definition at line 423 of file builder.c.

00424 {
00425     if (!b) return NULL;
00426 
00427     switch (b->qtype)
00428     {
00429     case SQL_INSERT:
00430         b->ptag = stpcpy(b->ptag, b->val_base);
00431         b->ptag = stpcpy(b->ptag, ");");
00432         break;
00433 
00434     case SQL_UPDATE:
00435     case SQL_SELECT:
00436     case SQL_DELETE:
00437         b->ptag = stpcpy(b->ptag, b->tbl_base);
00438         if (b->got_where_term) b->ptag = stpcpy(b->ptag, b->val_base);
00439         b->ptag = stpcpy(b->ptag, ";");
00440         break;
00441 
00442     case 0:
00443         break;
00444 
00445     default:
00446         PERR ("mustn't happen");
00447     };
00448 
00449     PINFO ("%s\n", b->tag_base);
00450     return b->tag_base;
00451 }

void sql_builder_set_date ( SqlBuilder *  b,
const gchar *  tag,
time_t  val 
)

Bug:
fix the time handler and remove static buffer.

Definition at line 289 of file builder.c.

00290 {
00292     gchar buf[120];
00293     xxxgnc_secs_to_iso8601_buff (ts, buf);
00294     sql_builder_set_str (b, tag, buf);
00295 }

void sql_builder_set_str ( SqlBuilder *  b,
const gchar *  tag,
const gchar *  val 
)

Set tag-value pairs. Each of these adds the indicated tag and value to an UPDATE or INSERT statement. For SELECT statements, val may be NULL (and is ignored in any case).

Definition at line 229 of file builder.c.

00230 {
00231     if (!b || !tag) return;
00232     if (!val) val= "";
00233 
00234     val = sqlEscapeString (b->escape, val);
00235 
00236     if (b->tag_need_comma) b->ptag = stpcpy(b->ptag, ", ");
00237     b->tag_need_comma = 1;
00238 
00239     switch (b->qtype)
00240     {
00241     case SQL_INSERT:
00242         b->ptag = stpcpy(b->ptag, tag);
00243 
00244         if (b->val_need_comma) b->pval = stpcpy(b->pval, ", ");
00245         b->val_need_comma = 1;
00246         b->pval = stpcpy(b->pval, "'");
00247         b->pval = stpcpy(b->pval, val);
00248         b->pval = stpcpy(b->pval, "'");
00249         break;
00250 
00251     case SQL_UPDATE:
00252         b->ptag = stpcpy(b->ptag, tag);
00253         b->ptag = stpcpy(b->ptag, "='");
00254         b->ptag = stpcpy(b->ptag, val);
00255         b->ptag = stpcpy(b->ptag, "' ");
00256         break;
00257 
00258     case SQL_SELECT:
00259         b->ptag = stpcpy(b->ptag, tag);
00260         break;
00261 
00262     case SQL_DELETE:
00263         break;
00264 
00265     case 0:
00266         PERR ("must specify a table and a query type first!");
00267         break;
00268 
00269     default:
00270         PERR ("mustn't happen");
00271     };
00272 
00273 }

void sql_builder_table ( SqlBuilder *  b,
const gchar *  tablename,
SqlBuilderQType  qtype 
)

The sql_builder_table() routine starts building a new SQL query on table 'tablename'. Any previously started query is erased.

When building 'select' type statments, crude table joins are supported: the 'tablename' can in fact be a comma-separated list of tables. This field is copied directly as follows: "SELECT ... FROM tablename WHERE ..." so anything valid in that position is tolerated.

Definition at line 165 of file builder.c.

00166 {
00167     gchar * ptbl;
00168 
00169     if (!b || !tablename) return;
00170     b->qtype = qt;
00171 
00172     b->ptag = b->tag_base;
00173     b->pval = b->val_base;
00174     ptbl = b->tbl_base;
00175 
00176     /* null terminated strings */
00177     *(b->ptag) = 0x0;
00178     *(b->pval) = 0x0;
00179     *ptbl = 0x0;
00180 
00181     b->tag_need_comma = 0;
00182     b->val_need_comma = 0;
00183     b->where_need_and = 0;
00184     b->got_where_term = 0;
00185 
00186     switch (qt)
00187     {
00188     case SQL_INSERT:
00189         b->ptag = stpcpy(b->ptag, "INSERT INTO ");
00190         b->ptag = stpcpy(b->ptag, tablename);
00191         b->ptag = stpcpy(b->ptag, " (");
00192 
00193         b->pval = stpcpy(b->pval, ") VALUES (");
00194         break;
00195 
00196     case SQL_UPDATE:
00197         b->ptag = stpcpy(b->ptag, "UPDATE ");
00198         b->ptag = stpcpy(b->ptag, tablename);
00199         b->ptag = stpcpy(b->ptag, " SET ");
00200 
00201         b->pval = stpcpy(b->pval, " WHERE ");
00202         break;
00203 
00204     case SQL_SELECT:
00205         b->ptag = stpcpy(b->ptag, "SELECT ");
00206 
00207         ptbl = stpcpy(ptbl, " FROM ");
00208         ptbl = stpcpy(ptbl, tablename);
00209 
00210         b->pval = stpcpy(b->pval, " WHERE ");
00211         break;
00212 
00213     case SQL_DELETE:
00214         b->ptag = stpcpy(b->ptag, "DELETE ");
00215         ptbl = stpcpy(ptbl, " FROM ");
00216         ptbl = stpcpy(ptbl, tablename);
00217 
00218         b->pval = stpcpy(b->pval, " WHERE ");
00219         break;
00220 
00221     };
00222 
00223 }

void sql_builder_where_str ( SqlBuilder *  b,
const gchar *  tag,
const gchar *  val,
const gchar *  op 
)

The sql_builder_where_*() routines are used to construct the 'WHERE' part of SQL SELECT and UPDATE clauses. The 'tag' must be a valid fieldname, and 'val' must be a value. If 'op' is NULL, it is assumed to be '=', otherwise, one can specify an op '<', '>' and so on.

Definition at line 362 of file builder.c.

00364 {
00365     if (!b || !tag || !val) return;
00366     b->got_where_term = 1;
00367 
00368     switch (b->qtype)
00369     {
00370     case SQL_INSERT:
00371         /* there is no where clause, so we do the set as a utility */
00372         sql_builder_set_str (b, tag, val);
00373         break;
00374 
00375     case SQL_UPDATE:
00376     case SQL_SELECT:
00377     case SQL_DELETE:
00378         val = sqlEscapeString (b->escape, val);
00379 
00380         if (b->where_need_and) b->pval = stpcpy(b->pval, " AND ");
00381         b->where_need_and = 1;
00382 
00383         b->pval = stpcpy(b->pval, tag);
00384         if (op)
00385         {
00386                 b->pval = stpcpy(b->pval, " ");
00387                 b->pval = stpcpy(b->pval, op);
00388                 b->pval = stpcpy(b->pval, " '");
00389         }
00390         else
00391         {
00392                 b->pval = stpcpy(b->pval, "='");
00393         }
00394         b->pval = stpcpy(b->pval, val);
00395         b->pval = stpcpy(b->pval, "'");
00396 
00397         break;
00398 
00399     case 0:
00400         PERR ("must specify a table and a query type first!");
00401         break;
00402 
00403 
00404     default:
00405         PERR ("mustn't happen");
00406     };
00407 }

Generated on Tue Apr 29 21:27:54 2008 for estron by  doxygen 1.5.5