[ANN] CGo-free sqlite database/sql driver 1.4.0 for linux/amd64 released

[Jan Mercl]

From the change log (https://godoc.org/modernc.org/sqlite#hdr-Changelog)

2020-08-26 v1.4.0:

First stable release for linux/amd64. The database/sql driver and its tests are CGo free. Tests of the translated sqlite3.c library still require CGo.

$ make full

...

SQLite 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0ff3f
0 errors out of 928271 tests on 3900x Linux 64-bit little-endian
WARNING: Multi-threaded tests skipped: Linked against a non-threadsafe Tcl build
All memory allocations freed - no leaks
Maximum memory usage: 9156360 bytes
Current memory usage: 0 bytes
Number of malloc()  : -1 calls
--- PASS: TestTclTest (1785.04s)
PASS
ok      modernc.org/sqlite  1785.041s
$

[Dan Kortschak]

On Wed, 2020-08-26 at 23:50 +0200, Jan Mercl wrote:
> From the change log (

This is just what I have been looking for for a little toy I'm writing.

[Mandolyte]

A couple of questions:

• What is the state of Windows/MacOS?
• How about Windows Subsystem for Linux?

Thanks!

[Jan Mercl]

On Thu, Aug 27, 2020 at 2:12 AM Mandolyte <ceci...@gmail.com> wrote:

> What is the state of Windows/MacOS?

Planned, not started. Collaborators and maintainers of other platforms
are welcome.

> How about Windows Subsystem for Linux?

WSL does not appear on the goosList:
https://golang.org/src/go/build/syslist.go so I don't know what
supporting it means.

[Nick]

Quoth Jan Mercl:

> First stable release for linux/amd64. The database/sql driver and its tests are
> CGo free. Tests of the translated sqlite3.c library still require CGo.

This is really cool, congratulations!

I'm now going to ask some really ignorant questions, so sorry for
that!

I took a little tour around the code, am I correct in thinking that
it's mostly a clever translation of the C code to Go, using
generator.go? What is 'ccgo' that seems to be doing all this? I see
that lib/sqlite_linux_amd64.go is created by the makeSqlite()
function, but is the capi_linux_amd64.go file generated from the
same command? Sorry, but I don't yet understand this ccgo thing.

Am I correct in thinking that this implementation, now it passes all
the tests, should be essentially a drop-in replacement for something
like github.com/mattn/go-sqlite3, with similar performance as it's
just translated-from-C? What happens if one (erroneously) tries to
build a project that uses this library, using a non-supported
architecture? Will go complain?

Thanks, anyway, looks very cool, I look forward to learning more!

Nick

[Jan Mercl]

On Thu, Aug 27, 2020 at 2:46 PM Nick <golan...@njw.name> wrote:

> I took a little tour around the code, am I correct in thinking that
> it's mostly a clever translation of the C code to Go, using
> generator.go? What is 'ccgo' that seems to be doing all this?

ccgo is a C compiler producing Go code. It's a work in progress and its code is so far the only documentation available, sadly. The project is modernc.org/ccgo/v3 hosted currently here: https://gitlab.com/cznic/ccgo/-/tree/master/v3

> I see
> that lib/sqlite_linux_amd64.go is created by the makeSqlite()
> function, but is the capi_linux_amd64.go file generated from the
> same command? Sorry, but I don't yet understand this ccgo thing.

ccgo produces a capi_$GOOS_$GOARC file automatically for non main C programs. This file simply has a map of extern symbols the Go package defines. ccgo uses this information for linking via, for example -lmodenrc.org/sqlite/lib. Similarly to a standard C compiler, libc is "imported" automatically as if the last item to ccgo is -lmodernc.org/libc. This can be overridden by the -ccgo-crt-pah flag. (The libc project is a continuation of the earlier modernc.org/crt project.)

> Am I correct in thinking that this implementation, now it passes all
> the tests, should be essentially a drop-in replacement for something
> like github.com/mattn/go-sqlite3, with similar performance as it's
> just translated-from-C?

Mattn's package exposes much more above the sql/database driver and adds custom URL parameters for various options specific to that project. However, it should be possible to create a drop-in replacement package as everything in modernc.org/sqlite/lib is exported and "consumable". What would be needed is to write manually a client Go <-> ccgo produced Go bridges for the functions that mattn's package uses from sqlite3.c (probably just a few dozens of them). The ccgo produced Go has an "ABI" that's quite distinct from usual Go functions. And it's not documented yet. But I can provide the info to anyone who would like to turn it into the proper documentation in a series of questions and answers, held on the issue tracker, for example.

Wrt performance, I expect the Go port to be definitely slower on average. It's a long story to explain why and there's a lot of different reasons. However, the slow down depends on what C code is measured. On a set of 24 benchmarks from ComptCert, the average ratio was about 1.25 (times slower than gcc -O) for the same C code in an earlier (still crt based) version. But then you can find some other C code where addresses of local variables are taken or where closures are used and it gets the highest performance hit. Correctness and completeness is so far the priority. I hope some performance improvements are still possible afterwards. For example, the current libc does not yet buffer stdout. Here are the current numbers on a rather old machine where you can see how it (I guess) hurts binarytrees.c:

=== RUN   TestCompCert
=== RUN   TestCompCert/gcc
=== RUN   TestCompCert/ccgo
=== CONT  TestCompCert
    all_test.go:1165:   compiler           test    T         comp  exec match    coef
    all_test.go:1167:        gcc          aes.c    70.965ms  true  true  true   1.000 *
    all_test.go:1167:        gcc    almabench.c   253.403ms  true  true  true   1.000 *
    all_test.go:1167:        gcc  binarytrees.c    33.907ms  true  true  true   1.000 *
    all_test.go:1167:        gcc       bisect.c    81.671ms  true  true  true   1.018
    all_test.go:1167:        gcc        chomp.c   237.238ms  true  true  true   1.000 *
    all_test.go:1167:        gcc     fannkuch.c   174.135ms  true  true  true   1.000 *
    all_test.go:1167:        gcc          fft.c    67.835ms  true  true  true   1.000 *
    all_test.go:1167:        gcc        fftsp.c    40.645ms  true  true  true   1.000 *
    all_test.go:1167:        gcc         fftw.c    66.590ms  true  true  true   1.000 *
    all_test.go:1167:        gcc          fib.c    45.877ms  true  true  true   1.000 *
    all_test.go:1167:        gcc       integr.c    28.862ms  true  true  true   1.000 *
    all_test.go:1167:        gcc  knucleotide.c   130.633ms  true  true  true   1.000 *
    all_test.go:1167:        gcc        lists.c    60.898ms  true  true  true   1.000 *
    all_test.go:1167:        gcc   mandelbrot.c   106.127ms  true  true  true   1.000 *
    all_test.go:1167:        gcc        nbody.c   149.378ms  true  true  true   1.000 *
    all_test.go:1167:        gcc       nsieve.c   134.007ms  true  true  true   1.000 *
    all_test.go:1167:        gcc   nsievebits.c    99.903ms  true  true  true   1.000 *
    all_test.go:1167:        gcc       perlin.c    38.010ms  true  true  true   1.000 *
    all_test.go:1167:        gcc        qsort.c    91.263ms  true  true  true   1.000 *
    all_test.go:1167:        gcc         sha1.c    59.152ms  true  true  true   1.000 *
    all_test.go:1167:        gcc         sha3.c    29.127ms  true  true  true   1.000 *
    all_test.go:1167:        gcc    siphash24.c    87.284ms  true  true  true   1.000 *
    all_test.go:1167:        gcc     spectral.c   168.282ms  true  true  true   1.022
    all_test.go:1167:        gcc        vmach.c    59.304ms  true  true  true   1.000 *
    all_test.go:1167:       ccgo          aes.c    82.205ms  true  true  true   1.158
    all_test.go:1167:       ccgo    almabench.c   349.994ms  true  true  true   1.381
    all_test.go:1167:       ccgo  binarytrees.c   240.471ms  true  true  true   7.092
    all_test.go:1167:       ccgo       bisect.c    80.192ms  true  true  true   1.000 *
    all_test.go:1167:       ccgo        chomp.c   316.546ms  true  true  true   1.334
    all_test.go:1167:       ccgo     fannkuch.c   231.494ms  true  true  true   1.329
    all_test.go:1167:       ccgo          fft.c    79.481ms  true  true  true   1.172
    all_test.go:1167:       ccgo        fftsp.c   103.185ms  true  true  true   2.539
    all_test.go:1167:       ccgo         fftw.c    93.375ms  true  true  true   1.402
    all_test.go:1167:       ccgo          fib.c    73.022ms  true  true  true   1.592
    all_test.go:1167:       ccgo       integr.c    32.963ms  true  true  true   1.142
    all_test.go:1167:       ccgo  knucleotide.c   295.192ms  true  true  true   2.260
    all_test.go:1167:       ccgo        lists.c    72.562ms  true  true  true   1.192
    all_test.go:1167:       ccgo   mandelbrot.c   152.969ms  true  true  true   1.441
    all_test.go:1167:       ccgo        nbody.c   149.822ms  true  true  true   1.003
    all_test.go:1167:       ccgo       nsieve.c   157.365ms  true  true  true   1.174
    all_test.go:1167:       ccgo   nsievebits.c   113.934ms  true  true  true   1.140
    all_test.go:1167:       ccgo       perlin.c    67.065ms  true  true  true   1.764
    all_test.go:1167:       ccgo        qsort.c   105.136ms  true  true  true   1.152
    all_test.go:1167:       ccgo         sha1.c    82.178ms  true  true  true   1.389
    all_test.go:1167:       ccgo         sha3.c    70.727ms  true  true  true   2.428
    all_test.go:1167:       ccgo    siphash24.c   108.887ms  true  true  true   1.248
    all_test.go:1167:       ccgo     spectral.c   164.702ms  true  true  true   1.000 *
    all_test.go:1167:       ccgo        vmach.c    78.095ms  true  true  true   1.317
    all_test.go:1174: Considered tests: 24/24
    all_test.go:1182:       ccgo   3.301562213s  1.426
    all_test.go:1182:        gcc   2.314496632s  1.000
--- PASS: TestCompCert (46.13s)
    --- PASS: TestCompCert/gcc (16.60s)
    --- PASS: TestCompCert/ccgo (29.48s)

> What happens if one (erroneously) tries to
> build a project that uses this library, using a non-supported
> architecture? Will go complain?

Yes, all of the os/arch specific code is by guarded like in foo_$GOOS_$GOARCH.go, so the compiler targeting other GOOS/GOARCH should definitely fail the build.

[Amnon]

Fantastic work!
And this will be super useful to the community.
I have used sqlite memory dbs  for unit tests in the past, and this always caused me portability pain.

Just one question. 
How will you track upstream changes in the original C sqlite?

[Jan Mercl]

On Thu, Aug 27, 2020, 18:49 Amnon <amn...@gmail.com> wrote

Just one question. 
How will you track upstream changes in the original C sqlite?

That's accomplished at the moment by updating the remote urls at https://gitlab.com/cznic/sqlite/-/blob/master/generator.go#L203 as well as the associated directories few lines bellow and running 'go generate' and 'go test', roughly speaking. With more supported os/arch combinations it will get a bit more complicated in the future, but the underlying mechanism remains the same: update the C sources, produce updated Go sources.

[Andrey T.]

Jan,

This is fantastic news! Congratulation on hitting such a milestone!

(patiently waiting for the windows and macos)

Andrey

[Tyler Compton]

Thanks Jan, this is very exciting! I'm looking forward to an opportunity to use this.

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/CAA40n-XnjDzZnc31UOBn-gHoueT10FrvLCyZh9c8VK7K8ObKjg%40mail.gmail.com.

[Amnon]

Is there any way we can use this library with Gorm?

[Jan Mercl]

I haven't yet used Gorm. Does it rely only on the API database/sql provides or is something more needed?

In the first instance you should be theoretically good to go right now on some popular Linux architectures - namely amd64, 386, arm and arm64.

Otherwise please let me know about what's missing and I hope it can be fixed.

[Amnon]

I have not poked in the internals
but Gorm has drivers for the dbs it supports which implement a common interface.
The sqlite driver is "github.com/go-gorm/sqlite" which pulls talks to 
the CGO "github.com/mattn/go-sqlite3".
I could just try forking "github.com/go-gorm/sqlite" and 
replacing mattn/go-sqlite3 with "modernc.org/sqlite".

I'll have a play around when I get a bit of time.