Sql2cpp

From LinuxMCE
Jump to: navigation, search


This page was written by Pluto and imported with their permission when LinuxMCE branched off in February, 2007. In general any information should apply to LinuxMCE. However, this page should be edited to reflect changes to LinuxMCE and remove old references to Pluto.

How does it work?

sql2cpp creates a C++ library that makes it very easy to work with a relational database. Just run sql2cpp passing in the login information for a database. This will create a stand-alone library with a "Database" class that has members for each of the tables. Each table has a "Table" class that has members to get row(s), and each table has a "Row" class with members for each of the fields. sql2cpp creates member functions that make it very easy to traverse foreign key relationships. You can traverse multiple tables across several relationships on one command line, and with one line get pointers to all other rows referencing it. This often reduces 20 or so lines of code down to 2 or 3 and makes the code much more readable.

Examples:

You have a simple database called "Accounting". It contains tables "Sale", "Product" and "Manufacturer". The respective Primary keys are "PK_Sale", "PK_Product", and "PK_Manufacturer". Sale has a foreign key to Product (FK_Product), indicating the product that was sold. Product has a foreign key to Manufacturer (FK_Manufacturer) indicating who made the Product. sql2cpp will create classes Database_Accounting, Table_Sale, Row_Sale, Table_Product, Row_Product, Table_Manufacturer, Row_Manufacturer.

If you want to get the row for sale #82 (ie where the primary key is 82), just do:

Database_Accounting myAccounting;
Row_Sale *pSale = myAccounting.Table_Sale.get().GetRow(82);

Then if you want to know the name of the Manufacturer who made the product sold, you can traverse the relationships across all 3 tables with 1 line:

string Name = pSale->FK_Product_getrow()->FK_Manufacturer_getrow()->Name_get();

If you want to want to get a list of all products made by manufacturer #94, just create a vector of Row_Product, and call this function:

vector<Row_Product *> vectProducts;
myAccounting.Manufacturer_get().GetRow(94).Product_FK_Manufacturer_getrows(&vectProducts);

That will fill the vector with all the rows in Product that reference this manufacturer by the field FK_Manufacturer.

You want to add a new manufacturer:

Row_Manufacturer *pRow_Manufacturer = myAccounting.Table_Manufacturer.AddRow();
pRow_Manufacturer->Name_set("Acme Tools"); // Set the other fields too
myAccounting.Table_Manufacturer.Commit(); // Commits any new, modified or deleted rows
int New_Auto_Increment_PrimaryKey = pRow_Manufacturer->PK_Manufacturer_get(); // It was set during the Commit
pRow_Manufacturer->Delete(); // Delete the record after all


Give it a try

To try it out, just run sql2cpp with login information for your database. It will create a complete project including a gcc-compatible Makefile and a Microsoft Visual Studio .NET project. If you want it to handle traversing foreign keys you will need to follow our naming convention, described in the Programmer's Guide, so sql2cpp can figure out what are foreign keys and what tables they refer to.