Simple MySQL ORM for C is a Python script, that connects to the existing MySQL database, fetches all the table's structures and generates according structures and functions for ANSI C language. These will enable you to easy manipulate with data in database, using the ANSI C.

Once upon a time I need to store and retrieve structured data into powerful database. I prefer MySQL and C language. I got bored of writing it always from scratch and of the overhead of solutions found on the Web, so I wrote my own. It's very simple, very stupid and very bad written (Python, which I used, is not my mother language, sorry). But if someone would find it interesting, I can imagine making it more usable. And even documented...

Consider this rather a technological preview, than a manual. I finally cancelled the project I wrote this for, so it is not very tested. Most of the mysql types are not supported. One of the reasons, why I created this is that I don't like old MySQL C API, so I used "modern" prepared-statements API.. but I found it has its own disadvantages, because it is not yet complete.

Now, how to start? First, you create your tables. I prefer this way - because only then can you fully use your database capabilities.

CREATE DATABASE ex1;

CREATE TABLE ex_customer (
  id int NOT NULL auto_increment,
  name char(32),
  PRIMARY KEY  (id)
);

CREATE TABLE ex_item (
  customer_id int,
  itemname char(32)
); 

Now we create simple conf file, named "db.py":

dbname = "ex1"
name = "db"
tables = { }

...and now let's rock'n'roll...

python rdb.py

Well, it was a bit less audible than proper rock. Also you may ask, how the hell I could connect to the database? Well, there's just the default user coded in... But take a look at what we got. There are two new files: db.h and db.c. The first one contains structures representing tables and "methods" operating with these structures. The db.c file contains source for these methods, along with initialization of statements. Let's see the structures:

typedef struct db_ex_customer {
        int id;
        char * name;
} db_ex_customer;

typedef struct db_ex_item {
        int customer_id;
        char * itemname;
} db_ex_item;
	

I believe, I don't have to further explain the meaning of this. Let's use them! I create new file ex1.c. I will not handle error states for keeping code readable:

#include <db.h>
#include <stdio.h>
#include <string.h>
#include <time.h>


int main (int argc, char **argv)
{
	int ret;
	MYSQL global_mysql;
	MYSQL *m;
	
	db_ex_customer *cust1;
	db_ex_item *item1, *item2;

	mysql_init (& global_mysql);

	/*
	 * connect to MySQL as usual
	 */
	m = mysql_real_connect (& global_mysql, "localhost", "root", "", "ex1", 3036, NULL, 0);

	/*
	 * pass the MySQL connection to function, that initializes the "ORM"
	 */
	ret = db_init (& global_mysql);

	/*
	 * the *__new method creates empty structure
	 */
	cust1 = db_ex_customer__new ();
	/*
	 * setting the structure attribute with allocated string,
	 * it will be freed during call of *__free method
	 */
	cust1->name = strdup ("alesak");

	/*
	 * this method inserts the structure into according table.
	 * If it has serial field, its value is reflected into structure
	 */
	ret = db_ex_customer__insert (cust1);

	item1 = db_ex_item__new ();
	/*
	 * let's use the serial value from newly inserted customer
	 */
	item1->customer_id = cust1->id;
	item1->itemname = strdup ("simple orm");

	ret = db_ex_item__insert (item1);

	item2 = db_ex_item__new ();
	item2->customer_id = cust1->id;
	item2->itemname = strdup ("advanced orm");

	ret = db_ex_item__insert (item2);
		
	db_ex_customer__free (cust1);
	db_ex_item__free (item1);
	db_ex_item__free (item2);

	return (0);
}
		

I compile the program like that..

cc -I. `mysql_config --cflags` ex1.c db.c `mysql_config --libs` -o ex1

.. run it and it really works. That means, stores some data into the database. It looks elegant, at least if you ask me. Now how to retrieve the data? Let's suppose we know the key of the record we want. I created ex2.c

#define _XOPEN_SOURCE 500
#include <db.h>
#include <stdio.h>
#include <string.h>
#include <time.h>


int main (int argc, char **argv)
{
	int ret;
	MYSQL global_mysql;
	MYSQL *m;
	
	db_ex_customer *cust1;
	db_ex_item *item1, *item2;

	mysql_init (& global_mysql);

	m = mysql_real_connect (& global_mysql, "localhost", "root", "", "ex1", 3036, NULL, 0);

	ret = db_init (& global_mysql);

	cust1 = db_ex_customer__get_by_id (3);
	if (cust1) {
		fprintf (stdout, "I have customer named \'%s\'\n", cust1->name);
		db_ex_customer__free (cust1);
	}

	return (0);
}
		

I compile it as the previous one and run:

cc -I. `mysql_config --cflags` ex2.c db.c `mysql_config --libs` -o ex2
./ex2
I have customer named 'alesak'

And the last thing. I don't want the ORM to create queries of related data automatically, because I don't trust its ability to do it well (and to be honest, if using default MyISAM, it's not possible to determine, what is related). Well, now I want to create my own, super-complex SELECT of ex_items related to ex_customer. I edit my db.py:

dbname = "ex1"
name = "db"
tables = {
		"ex_item" :[("get", "get_customer_items",
			[("INTEGER", "customer_id")], "SELECT ex_item.* FROM ex_item WHERE customer_id = ?")]
	}

Re-running the rdb.py script will add new set of functions db_ex_item__get_customer_items_*. These are "open", which takes one INTEGER parameter and opens cursor on specified SQL, "fetch", which fetches one row from this cursor and "close", which closed it. We can extend our ex2.c to this

#define _XOPEN_SOURCE 500
#include <db.h>
#include <stdio.h>
#include <string.h>
#include <time.h>


int main (int argc, char **argv)
{
	int ret;
	MYSQL global_mysql;
	MYSQL *m;
	
	db_ex_customer *cust1;
	db_ex_item *item1, *item2;

	mysql_init (& global_mysql);

	m = mysql_real_connect (& global_mysql, "localhost", "root", "", "ex1", 3036, NULL, 0);

	ret = db_init (& global_mysql);

	cust1 = db_ex_customer__get_by_id (3);
	if (cust1) {
		fprintf (stdout, "I have customer named \'%s\'..\n", cust1->name);

		db_ex_item__get_customer_items_open (cust1->id);

		while ((item1 = db_ex_item__get_customer_items_fetch ()) != NULL) {
			fprintf (stdout, ".. and found his item named \'%s\'\n", item1->itemname);
			db_ex_item__free (item1);
		}
		db_ex_item__get_customer_items_close ();

		db_ex_customer__free (cust1);
	}

	return (0);
}

As you would expect, it prints:

I have customer named 'alesak'..
.. and found his item named 'simple orm'
.. and found his item named 'advanced orm'

And that's all, folks! Here it is: rdb.py. Or rather don't download this junk and just let me know, if you liked the idea: