sqlite support added to sqlbuilder

[zoujiaqing]

On Saturday, 30 December 2023 at 22:11:55 UTC, Steven 
Schveighoffer wrote:
> https://code.dlang.org/packages/sqlbuilder
>
> This project is something I use extensively in my work project, 
> and allows dynamic building of SQL statements in a way that 
> automatically deals with joins.
>
> It also automatically serializes directly to structs 
> representing database rows. It was featured in my dconf 2022 
> online talk -- Model all the things.
>
> I just added support to use sqlite. The API isn't stable yet, 
> but still super useful. It's one of those build-it-as-I-need-it 
> things, so while there's a semblance of a plan, things that are 
> finished are things that I've needed.
>
> An example (with sqlite):
>
> ```d
> import d2sqlite3;
>
> import std.stdio;
> import std.file : exists;
> import std.array;
>
> // yeah, I know, I need a package include here...
> import sqlbuilder.uda;
> import sqlbuilder.dataset;
> import sqlbuilder.dialect.sqlite;
> import sqlbuilder.types;
>
> import d2sqlite3;
>
> struct Author
> {
>     @primaryKey @autoIncrement int id;
>     string firstname;
>     string lastname;
>     static @refersTo!Book @mapping("author_id") Relation books;
> }
>
> struct Book
> {
>     @primaryKey @autoIncrement int id;
>     string title;
>     string description;
>     @mustReferTo!Author("author") int author_id;
> }
>
> void main()
> {
>     auto shouldInitialize = !exists("books.sqlite");
>     auto db = Database("books.sqlite");
>     if(shouldInitialize)
>     {
>         // create the tables
>         db.execute(createTableSql!Author);
>         db.execute(createTableSql!Book);
>
>         // add some books and authors
>         Author walter = Author(
>                 firstname: "Walter",
>                 lastname: "Bright");
>         db.create(walter); // automatic serialization to sql 
> insertion statement
>         Author andrei = Author(
>                 firstname: "Andrei",
>                 lastname: "Alexandrescu");
>         db.create(andrei);
>
>         db.create(Book(
>                 title: "The D Programming Language",
>                 description: "The OG D manual",
>                 author_id: andrei.id));
>         db.create(Book(
>                 title: "Modern C++ Design",
>                 description: "The OG C++ template manual",
>                 author_id: andrei.id));
>         db.create(Book(
>                 title: "The D specification",
>                 description: "The Spec of the D programming 
> language",
>                 author_id: walter.id));
>     }
>
>     // get an author by name
>     DataSet!Author ads;
>     auto andrei = db.fetchOne(select(ads).where(ads.firstname, 
> " = 'Andrei'"));
>     // do some selections based on the dataset of books
>     DataSet!Book bds;
>     foreach(booktitle, author; db.fetch(select(bds.title, 
> bds.author)))
>     {
>         writefln("Found book %s, written by %s %s",
>                      booktitle, author.firstname, 
> author.lastname);
>     }
>     auto andreiBooks = db.fetch(select(bds)
>                     .where(bds.author_id, " = ", 
> andrei.id.param)).array;
>     writeln("Andrei's books: ", andreiBooks);
> }
> ```
>
> Code is very similar for using mysql as well, just import 
> mysql-native and sqlbuilder.dialect.mysql.
>
> Next up would be postgresql, not sure when I'll have a need to 
> build that...
>
> -Steve

Thank you! I want postgresql :)