[Box Backup-dev] Thinking of the future...
Ben Summers
boxbackup-dev@fluffy.co.uk
Sun, 29 Jan 2006 12:02:11 +0000
On 28 Jan 2006, at 12:18, Chris Wilson wrote:
> .
>
>> * Database interface
>>
>> Combination of autogenerated code and standard C++ style code
>> which allows easy, type-safe and fully named access to databases.
>> Includes drivers for MySQL, Postgresql (text and binary mode) and
>> Sqlite, with a system for abstracting out the differences in SQL
>> syntax. Two lines of code to run a query and get the results.
>
> I'm not sure how we would use this in Box - maybe backing up live
> databases?
I think it's best to dump those and backup the dump, to be quite honest.
>
> For other projects, I'm quite interested in the wxWidgets database
> framework, which seems to support a lot of databases and is well
> supported [http://www.wxwidgets.org/manuals/2.6.2/
> wx_odbcoverview.html]. It would be interesting to compare the wx
> framework with your own and combine the best features of both, for
> simplicity and power.
The wx framework seems to be hampered by over-abstractions and an
unwillingness to generate code by scripts, making it a little more
verbose and scary than it need be.
>
> Do you support parametrised queries? This is a personal bugbear of
> mine. I hate inserting data directly into a SQL query as it's
> dangerous and difficult to get right.
Of course, automating the most boring but easy to get wrong code was
a priority. Quite apart from the fact that it's a prime cause of SQL
injection attacks. And if you're using Postgres 7.4 or later, it uses
the extra bits in the protocol to send the parameters separately,
without turning them into text first.
I've put a copy of the test and documentation at the end, which may
give some idea to the API.
>
>> * Web application framework
>>
>> This is a bit of a "version one learning experience", but it's a
>> load of perl which writes C++ which complies into a standalone
>> HTTP server. Basically, it writes all the boring boilerplate code
>> for forms, internationalisation, and data binding to database
>> queries. Could be used for writing a nice cross-platform UI for
>> bbackupd/bbackupquery. Not perfect, but usable, if a bit weird to
>> get into.
>
> This could be very cool. I might not take on the cross-platform UI
> for bbackupd/bbackupquery, as I'm already working on one :-)
Ah yes, a proper GUI based app is much better.
> but I might be interested in writing one for bbstored.
It's a bit weird to get into, but can be quite effective.
>
>> Class for easily sending email via STMP servers, and validating
>> emails addresses (including looking up the RHS in DNS). Might be
>> handy for sending notifications direct, instead of via the dodgy
>> perl script.
>
> I'm not sure about this, I don't really like emails for
> notifications, especially as the current ones contain so little
> information. If they could be made more informative, and the
> current capability to call a script could be maintained as well,
> that would be very nice.
Error and status reporting is something that could definitely be
improved!
>
>> I hope the developers have noticed that Box Backup is mostly a
>> generic framework for writing UNIX daemons and clients really
>> quickly and easily.
>
> I had noticed, but it's not something I do every day :-)
All the more reason to use it when you have to.
>
> The autogen protocol libraries are potentially a big time and bug
> saver, but I'd really like to find out how it works in great detail
> before I'd be comfortable using it.
http://bbdev.fluffy.co.uk/svn/box/trunk/docs/common/lib_server/
Protocol.txt
The implementation is very simple, it just sends chunks of data down
a stream, with a minimal handshake at the beginning. See test/
basicserver.
Ben
------
TITLE lib/database
Provides a generic interface to a number of database servers.
Design aims:
* totally consitent across databases: abstract away differences in
API and SQL
* efficient
* easy to use
* parameters in query strings
* repeated queries with different values
* creation and destruction of backend objects happens automatically.
* autogeneration of code, as well as simple in-line SQL
* errors reported by exceptions only (so write code on assumption of
success)
For an example of usage, see test/database/testdatabase.cpp
SUBTITLE Connecting to a database
The DatabaseConnection object represents a connection to the
database, which is connected when the Connect() method is called, and
disconnected when the object is destroyed. For example
DatabaseConnection db;
db.Connect(std::string("sqlite"), connectionString, 1000 /* timeout
in ms */);
This will either connect or exception.
The first parameter is the driver name. The drivers available depend
on what is compiled into the application (based on what was available
when the code was compiled).
The second is the connection string, which is driver dependent, and
specifies everything the driver needs to know to make the connection.
For database servers, this is hostname, username, password and
database name, for SQLite it's simply the filename of the database.
The Timeout is the connection timeout, in milliseconds. Some drivers
may interpret this slightly differently, but all in an appropraite
manner.
SUBTITLE Making a query
The DatabaseQuery object makes a query to the database. By itself, it
cannot do anything useful, and a derived class must be used (for
example, DatabaseQueryGeneric).
Applications should not generate SQL statements at runtime. Instead,
a system of parameters is used to efficiently insert variable data
into SQL. For example
INSERT INTO table (field1,field2) VALUES ($1,$2)
The SQL statement is constant, but at runtime, the two values $1 and
$2 are passed as arguments to the Execute() function. Strings need
not be quoted. NULL values are passed as NULL pointers instead of
pointers to an actual value.
The exact way arguments are presented depends on the derived class --
in general, the derived class will have an Execute method which
allows for type-safe arguments (mainly with autogenerated code).
The same query can be executed many times, with different arguments.
Some drivers will handle this very efficiently if the statement can
be "prepared".
Once the query has been executed, the results can be read.
If the query does not return data (eg an INSERT statement), then
GetNumberChanges() will return the number of changes made.
Otherwise, data must be retrieved. GetNumberRows() and
GetNumberColumns() return the number of rows and columns respectively.
To read all the fields out, use a loop like
while(query.Next())
{
// handle row
}
Within the loop, use GetFieldInt() and GetFieldString() with the
column number to retrieve row values. (Given the database overhead,
it is recommended that if a value is used repeatly, the GetField*()
method is only called once, with the value stored in a local variable.)
With autogenerated code, suitable GetFieldName() functions will be
generated, which return named fields.
If the query returns a dataset of 1 row and 1 column exactly, then
GetSingleValueInt() and GetSingleValueString() can be used to
retrieve that one value without the necessity to call Next(). This is
provided for convienence.
SUBTITLE Generic query object
DatabaseQueryGeneric is a generic query object which can be used for
simple queries where it's not worth using autogenerated code.
The basic SQL statement is passed into the constructor, and then
several Execute methods are provided; no parameters, 1 int or string
parameter, or many parameters.
The many parameters method is declared as
void Execute(const char *Types, ...)
The first argument specifies the type of the following arguments, and
implied by the length of the string, the number of arguments. Little
type checking can be done -- for complex queries taking many
arguments, use autogenerated code! (Or where there are many columns
to return, where it's nicer to use names than numbers to refer to them.)
Each character in the string defines one argument. Values are
i = integer (value, cannot be NULL)
I = integer (pointer, can be null)
s = const char * string (pointer, can be NULL)
S = std::string (pointer, can be null)
N = null (corresponding argument must be NULL or 0)
For example,
int i1 = 24;
char *str1 = "test string";
std::string str2("Test string 2");
query.Execute("NiIIsssSS", NULL, 56, NULL, &i1, NULL, "constant
string", str1,
NULL, &str2);
Values are read out as any other DatabaseQuery object.
---------------
TITLE Autogenerated query objects
Perl scripts write query objects for even neater database queries.
SQL statments and their use can be separated from the code into
separate files, and/or included inline in the cpp files.
SUBTITLE Schema files
Schema files contain all the SQL commands necessary to set up a
database -- not just CREATE TABLE commands. Each command must be ;
terminated. Comments are started by # and continue to the end of the
file.
makedbcreate.pl will then generate two functions -- a create function
which executes all these SQL queries, and a drop function which DROPs
all the tables created.
makedbcreate.pl input.schema output.cpp output.h
The functions will be called input_Create(db) and input_Drop(db)
(where input.schema is the name of the schema file).
It is recommended that the makedbmake.pl script is used to generate
the makefile which will run this, see below.
SUBTITLE Query definition
Queries look like this
SQL Query
[
Name: Test2
Statement: SELECT fInteger,fString FROM tTest1 WHERE fInteger=$1 AND
fString=$2
Parameters: int, std::string StringParam NULL?
Results: int Integer, std::string String
]
and may appear anywhere in a .query or .cpp file. The lines are of
the form "Attribute: Value", where Value may extend over multiple lines.
Name - specifies the name of the C++ class generated.
Statement: The SQL statement, with $n insertation markers.
Parameters: Types and optional names of the parameters (corresponding
to insertation markers)
Results: Types and names of the results.
Flags: Options. Only one at the moment is SingleValue.
AutoIncrementValue: (see below)
If the type and name of a result or parameter is followed by the
string NULL?, then this means that item might be null. For
parameters, this means the parameter is passed in by a pointer (so
that it can be NULL), and for results, means a Is<Name>Null()
function is generated.
Types should be std::string, int or int32_t.
If the SingleValue option is set, then an additional static Do()
function is generated, which given a database connection and the
parameters, executes the query and returns the single value. Note the
Results still must be set, to obtain the type of the result value.
SUBTITLE Runtime statments
If the statement is the string "runtime", then the generated class
will be derived off DatabaseQueryGeneric, and the statement can be
specified at runtime.
This is useful for when exact WHERE clauses are not known until
runtime, but the results from the query are known.
SUBTITLE Query objects for other code generation systems
Other code generation systems (for example, the web application
framework) may use batabase query objects. To create one, do
my $query = Database::Query->new('Name' => 'Test2', 'Statement' =>
'SQL', ...);
where the parameters are the attributes as in the query definition
above.
SUBTITLE Auto-incrementing fields
If the
AutoIncrementValue: TableName ColumnName
attribute is set, then the autogenerated query will have an InsertedID
() method, which will return the auto-incremented value (see
AutoIncrement.txt). In addition, a Do() method will be generated,
which will return this value.
SUBTITLE Using an autogenerated query
The constructor of the autogenerated class just takes a reference to
the DatabaseConnection object. The Execute() function takes typed
parameters as described in the "Parameters" attribute. Other than
that, it's used exactly as any other query object, except that field
values are obtained by Get<Name>() functions, rather than generic
field functions referencing the column number.
The benefits over DatabaseQueryGeneric are:
1) Named query, with SQL moved away from the actual code to the
definition of the query.
2) It is possible to completely separate the database storage and the
SQL from C++ code.
3) One-shot "single result value" queries have a static one-line Do()
function.
4) Parameters to Execute and Do() are strongly typed, and when
multiple parameters are used, less clumsy to specify with the type
string.
5) Names of the fields are used instead of generic field retrieval
functions with column numbers.
6) Auto-incrementing fields are handled in a neater way.
SUBTITLE Setting up
Include the following lines in the Makefile.extra file:
========================================================================
# AUTOGEN SEEDING
autogen_db/testdb_schema.cpp: testdb.schema
../../lib/database/makedbmake.pl .
# include-makefile: Makefile.db
========================================================================
(assuming you have a schema file, otherwise, use a query file)
This will setup a makefile which will
1) For every .query file in the directory, autogenerate queries from
it in the simiarly named files within autogen_db.
2) For every .cpp file which contains SQL Query sections, generate
files withing autogen_db called name_query.h/cpp where the file is
called 'name.cpp'.
So, to use the queries within name.queries, include "autogen_db/
name.h" in your cpp file, where the query file is called "name.query'.
To use statements included in your cpp file, include "autogen_db/
name_query.h" in your cpp file, where that cpp file is called name.cpp.
----------------------
TITLE Database SQL Statement vendorisation
Each database server does some slightly non-standard things
differently. In an attempt to allow a single SQL statement to work
with multiple databases, even when using some of these "advanced"
features, SQL statements are optionally "vendorised" before being
passed to the database drivers.
This is a simple system of search and replace (with optional
arguments) for strings within the statment, called generic
representations. All strings begin with a ` character.
A generic respresentation is of the form
`NAME
where NAME is a defined name composed of A-Z and _ only. It may
optionally be given arguments, as
`NAME(a,b,c)
where a, b, and c are arbitary strings which are used within the
vendor specific representation of the generic representation.
Note that vendorisation is an optional step, for efficiency.
Autogenerated queries detect whether it is necessary, but queries
using DatabaseQueryGeneric must set a flag in the constructor if it's
necessary.
SUBTITLE Supported generic representations
All database drivers support the following:
* AUTO_INCREMENT_INT
The column type for a column which is automatically generated by the
database as an incrementing 4 byte integer -- useful for allocating
objects to new IDs. See AutoIncrement.txt for more details of how to
use this.
* LIMIT(offset,number)
Limit the number of results returned.
* CREATE_INDEX_CASE_INSENSTIVE(name, table, column)
Create an index of the column converted to lowercase, for use with
CASE_INSENSITIVE_COLUMN(). Create a normal index if the database does
not support this.
* CASE_INSENSITIVE_COLUMN(columnname)
For use with CREATE_INDEX_CASE_INSENSTIVE -- whenever you want to use
the column, use this macro. And make sure the value you compare it to
is all lower case.
* COLUMN_CASE_INSENSITIVE_ORDERING
When used in a type specifier for a column, mark the column as case
insensitively ordered. Will be ignored if the database does not
support it.
------------------------
//
------------------------------------------------------------------------
--
//
// File
// Name: testdatabase.cpp
// Purpose: Test database driver library
// Created: 10/5/04
//
//
------------------------------------------------------------------------
--
#include "Box.h"
#include <string.h>
#include <map>
#include <string>
#include "Test.h"
// include this next one first, to check required headers are built
in properly
#include "autogen_db/testqueries.h"
#include "DatabaseConnection.h"
#include "DatabaseQueryGeneric.h"
#include "DatabaseDriverRegistration.h"
#include "autogen_db/testdb_schema.h"
#include "autogen_db/testdatabase_query.h"
#include "autogen_DatabaseException.h"
#ifdef MODULE_lib_dbdrv_postgresql
// see notes in DbQueryPostgreSQL.h
#include "PostgreSQLOidTypes.h"
#endif
#include "MemLeakFindOn.h"
char *insertTestString[] = {"a string", "pants'", "\"hello\"",
"lampshade", 0};
int insertTestInteger[] = {324234, 322, 4, 1};
#define NUMBER_VALUES 4
void test_zero_changes_from_select(const char *driver, DatabaseQuery
&rQuery)
{
// Work around a bug in sqlite...
#ifdef PLATFORM_SQLITE3
if(::strcmp(driver, "sqlite") != 0)
#endif
{
TEST_THAT(rQuery.GetNumberChanges() == 0);
}
}
void check_pg_oid_types(DatabaseConnection &rdb)
{
#ifdef MODULE_lib_dbdrv_postgresql
/*
(note that there is extra whitespace after the introductory and end
lines)
SQL Query
[
Name: CheckOID
Statement: select oid,typname from pg_type;
Results: int OID, std::string Name
]
*/
CheckOID query(rdb);
query.Execute();
std::map<std::string,int> o;
while(query.Next())
{
o[query.GetName()] = query.GetOID();
}
bool all_postgresql_oid_types_correct = true;
#define CHECK_OID_VAL(name,value)
{std::map<std::string,int>::iterator i(o.find(name)); if(i == o.end()
|| i->second != value) {all_postgresql_oid_types_correct = false;}}
CHECK_OID_VAL("int4", INT4OID);
CHECK_OID_VAL("int2", INT2OID);
CHECK_OID_VAL("text", TEXTOID);
CHECK_OID_VAL("name", NAMEOID);
CHECK_OID_VAL("varchar", VARCHAROID);
CHECK_OID_VAL("bpchar", BPCHAROID);
CHECK_OID_VAL("bool", BOOLOID);
CHECK_OID_VAL("char", CHAROID);
CHECK_OID_VAL("oid", OIDOID);
CHECK_OID_VAL("int8", INT8OID);
//CHECK_OID_VAL("", );
TEST_THAT(all_postgresql_oid_types_correct);
if(all_postgresql_oid_types_correct)
{
::printf("PostgreSQL OID types correct.\n");
}
#endif
}
void test_database(const char *driver, const char *connectionstring)
{
// Is the driver available?
if(!Database::DriverAvailable(driver))
{
::printf("Driver %s not available, skipping tests for that database
\n", driver);
return;
}
::printf("Testing interface with driver %s...\n", driver);
DatabaseConnection db;
try
{
db.Connect(std::string(driver), std::string(connectionstring),
1000 /* timeout */);
}
catch(DatabaseException &e)
{
if(e.GetSubType() != DatabaseException::FailedToConnect) throw;
::printf("Failed to connect to database server with driver %s,
skipping rest of test\n", driver);
bool failedToConnect = false;
TEST_THAT(failedToConnect);
return;
}
// Check name
TEST_THAT(::strcmp(driver, db.GetDriverName()) == 0);
// If postgresql, check OID ids
if(::strcmp(driver, "postgresql") == 0)
{
check_pg_oid_types(db);
}
// Test string quoting
{
std::string quoted;
db.QuoteString("quotes", quoted);
TEST_THAT(quoted == "'quotes'");
}
// Create basic schema, using autogen function
testdb_Create(db);
// Insert some values
{
int last_insertid = -1;
DatabaseQueryGeneric insert(db, "INSERT INTO tTest1
(fInteger,fString) VALUES($1,$2)");
for(int n = 0; n < NUMBER_VALUES; ++n)
{
insert.Execute("is", insertTestInteger[n], insertTestString[n]);
int iid = db.GetLastAutoIncrementValue("tTest1", "fID");
TEST_THAT(iid > last_insertid);
last_insertid = iid;
}
}
// Read them out in sorted order
{
DatabaseQueryGeneric read(db, "SELECT fInteger,fString FROM tTest1
ORDER BY fInteger");
read.Execute();
test_zero_changes_from_select(driver, read);
TEST_THAT(read.GetNumberRows() == NUMBER_VALUES);
TEST_THAT(read.GetNumberColumns() == 2);
int n = NUMBER_VALUES - 1;
while(read.Next())
{
//::printf("|%d|%s|\n", read.GetFieldInt(0), read.GetFieldString
(1).c_str());
TEST_THAT(n >= 0);
TEST_THAT(read.GetFieldInt(0) == insertTestInteger[n]);
TEST_THAT(read.GetFieldString(1) == insertTestString[n]);
--n;
}
TEST_THAT(n == -1);
}
// Check single values work as expected
{
DatabaseQueryGeneric count(db, "SELECT COUNT(*) FROM tTest1");
count.Execute();
TEST_THAT(count.GetSingleValueInt() == NUMBER_VALUES);
// Check it works again (will have modified the data the first time
around)
TEST_THAT(count.GetSingleValueInt() == NUMBER_VALUES);
}
{
DatabaseQueryGeneric count(db, "SELECT fString FROM tTest1 WHERE
fInteger=4");
count.Execute();
TEST_THAT(count.GetSingleValueString() == "\"hello\"");
// Check it works again (will have modified the data the first time
around)
TEST_THAT(count.GetSingleValueString() == "\"hello\"");
}
// Check update row counts are OK
{
DatabaseQueryGeneric update(db, "UPDATE tTest1 SET fInteger=
(fInteger+1) WHERE fInteger>400");
update.Execute();
TEST_THAT(update.GetNumberChanges() == 1);
TEST_THAT(update.Next() == false);
}
// Try an autogenerated query
{
/*
(note that there is extra whitespace after the introductory and end
lines)
SQL Query
[
Name: Test1
Statement: SELECT fInteger,fString FROM tTest1 WHERE fInteger>$1
Parameters: int
Results: int Integer, std::string String
]
*/
Test1 query(db);
query.Execute(3);
int n = 0;
while(query.Next())
{
++n;
}
TEST_THAT(n == 3);
test_zero_changes_from_select(driver, query);
}
// And one which was autogenerated in another file
{
Test2 query(db);
std::string string("lampshade");
query.Execute(1, &string);
TEST_THAT(query.Next());
TEST_THAT(query.GetInteger() == 1);
TEST_THAT(query.GetString() == "lampshade");
TEST_THAT(!query.Next());
test_zero_changes_from_select(driver, query);
}
// And another which returns a single value
{
TEST_THAT(Test3::Do(db, 1) == "lampshade");
}
// A query, autogenerated, which takes the statement at runtime
{
TestRuntimeQuery query(db, "SELECT fInteger,fString FROM tTest1
WHERE fInteger=$1");
query.Execute("i", 1);
TEST_THAT(query.Next());
TEST_THAT(query.GetInteger() == 1);
TEST_THAT(query.GetString() == "lampshade");
TEST_THAT(!query.Next());
test_zero_changes_from_select(driver, query);
}
// And finally, a query which returns an insert value
{
/*
SQL Query
[
Name: Test4
Statement: INSERT INTO tTest1(fInteger,fString) VALUES($1,$2)
Parameters: int, std::string
AutoIncrementValue: tTest1 fID
]
*/
int id = Test4::Do(db, 56, "xx1");
int id2 = Test4::Do(db, 898, "pdfdd");
TEST_THAT(id2 > id);
{
Test4 query(db);
query.Execute(2938, "ajjd");
int id3 = query.InsertedValue();
TEST_THAT(id3 > id2);
}
}
// Drop the schema, using autogen function
testdb_Drop(db);
}
// Vendorisation test driver
class VendorTest : public DatabaseDriver
{
public:
VendorTest() {}
~VendorTest() {}
virtual const char *GetDriverName() const {return "vendortest";}
virtual void Connect(const std::string &rConnectionString, int
Timeout) {}
virtual DatabaseDrvQuery *Query() {return 0;}
virtual void Disconnect() {};
virtual void QuoteString(const char *pString, std::string
&rStringQuotedOut) const {}
virtual int32_t GetLastAutoIncrementValue(const char *TableName,
const char *ColumnName) {return 0;}
virtual const TranslateMap_t &GetGenericTranslations()
{
static DatabaseDriver::TranslateMap_t table;
const char *from[] = {"TEST", "ARGS2", "X1", "Z1", 0};
const char *to[] = {"OUTPUT", "GG[!0:!1:!0", "Y !01", "!0U", 0};
DATABASE_DRIVER_FILL_TRANSLATION_TABLE(table, from, to);
return table;
}
};
void test_vendorisation()
{
VendorTest driver;
std::string o;
bool printres = false;
#define TEST_TRANS(from, shouldbe) \
DatabaseQuery::TEST_VendoriseStatement(driver, from, o); \
if(printres) {::printf("|%s|->|%s|\n", from, o.c_str()); } \
TEST_THAT(o == shouldbe);
TEST_TRANS("0123TEST4567", "0123TEST4567");
TEST_TRANS("0123`TEST4567", "0123OUTPUT4567");
TEST_TRANS("0123`TEST", "0123OUTPUT");
TEST_TRANS("0123`X(ywy2yyy2)SS", "0123Y ywy2yyy21SS");
TEST_TRANS("0123`X(ywy2yyy2)", "0123Y ywy2yyy21");
TEST_TRANS("`X(ywy2yyy2)SS", "Y ywy2yyy21SS");
TEST_TRANS("`Z(sjs8u)SS", "sjs8uUSS");
TEST_TRANS("0123`ARGS(hy2, x23s)4567", "0123GG[hy2: x23s:hy24567");
TEST_TRANS("0123`ARGS(hy2, x23s)", "0123GG[hy2: x23s:hy2");
TEST_TRANS("0123`ARGS(, x23s)", "0123GG[: x23s:");
TEST_TRANS("0123`ARGS(hy2,)", "0123GG[hy2::hy2");
}
int test(int argc, const char *argv[])
{
// Test vendorisation
test_vendorisation();
// How many drivers?
const char *driverList = 0;
int nDrivers = Database::DriverList(&driverList);
TEST_THAT(driverList != 0);
::printf("%d drivers available: %s\n", nDrivers, driverList);
// Test the same code on all databases
test_database("sqlite", "testfiles/testdb.sqlite");
test_database("mysql", "testdb:testuser:password");
test_database("postgresql", "dbname = test");
return 0;
}