Show sqlbuilder usage

sqldemo.d

import sqlbuilder.uda;
import sqlbuilder.dataset;
import sqlbuilder.dialect.sqlite;

// Note all of these are sql-injection-proof

struct users
{
    @primaryKey @autoIncrement int id; // a non-unique primary key doesn't make sense
    string name;
    string email;
}

DataSet!users ds; // make a dataset out of it

string dropSQL = dropTableSql!users;
string createSQL = createTableSql!users; // generate create table statement

enum createSQLEnum = createTableSql!users; // works at compile-time

auto insertSQL = insert(users(-1, "John Smith", "[email protected]"));
// or
auto insertSQL2 = insert(users.tableDef)
    .set(ds.name, "John Smith".param)
    .set(ds.email, "[email protected]".param);

// no insertOrReplace support yet

// Select query example with all columns returned
auto getUserByEmail = select(ds).where(ds.email, " = ", "[email protected]".param);

// Select query example with a specific column returned
auto getNameByEmail = select(ds.name).where(ds.email, " = ", "[email protected]".param);

// some actual usage with a real connection:
auto conn = makeConnection; // makeConnection is 3rd party, not part of sqlbuilder library
conn.exec(dropSQL);
conn.exec(createSQL);

// using insert without the specific connection "create" command doesn't store
// the new id, but you can get it from the 3rd-party library.
conn.perform(insertSQL);
// or using create just gives you everything you need
users newUser = conn.create(users(-1, "John Smith", "[email protected]"));

users u1 = conn.fetchOne(getUserByEmail); // throws exception if it doesn't exist
string n1 = conn.fetchOne(getNameByEmail, "(No user by that email)"); // uses a default value if it doesn't exist

// do counting
auto c = conn.fetchTotal(select(ds.id));
auto c2 = conn.fetchTotal(select(ds.id).where(ds.name, " = ", "john".param));