curttilmes/raku-dbsqlite

SQLite access for Raku

raku-dbsqlite's Issues

add query method .values

The query has a .value method for returning a single value when only one row is expected to satisfy the query.

Shouldn't there be an equivalent .values method returning an array of values when multiple rows are expected to be affected?

For example:

SELECT rowid FROM blah
ORDER BY rowid

In memory database support broken

my $s = DB::SQLite.new(filename => ':memory');
$s.execute('create table foo (x int, y text)');

This code writes a database to a file named ':memory' instead of in memory.

Found this out by trying to use the in-memory support for a test file.

Cannot locate native library 'libsqlite3.so': libsqlite3.so: cannot open shared object file: No such file or directory on Ubuntu 18.10

I tried switching one of my scripts from DBIish to DB::SQLite and failed because the library is named libsqlite3.so.0.8.6. on Ubuntu 18.10.
Please take over the native library search code from DBIish, thanks!

sqlite3 column names "oid" and "_rowid_" are not returned in a results hash

Quoting from the SQLIte docs:

Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed
integer key that uniquely identifies the row within its table. This integer is usually
called the "rowid". The rowid value can be accessed using one of the special
case-independent names "rowid", "oid", or "_rowid_" in place of a column name. 

DB::SQLite does recognize "oid" or "_rowid_" in a select query, but neither shows up as a hash key when used. Only the %hash<rowid> key is recognized regardless of the column name used in the select query.

prepare statements don't protect apostrophes

I thought by using a prepared statement with ? placeholders I wouldn't have to be concerned about taking care of embedded apostrophes. Something like this doesn't work as expected:

my $last = "O'Connor";
my $sth = $s.db.prepare('insert into person_tbl (last) values (?)');
$sth.execute($last);
BOOM

How to open a db in readonly mode?

I tried to pass the filename as file:db.sqlite?mode=ro but I get out of memory error.

Frequent "Database is locked" errors

I'm writing an application using DB::SQLite to access a database from a Cro HTTP service and another standalone daemon. I'm hitting a surprising amount of "Database is locked" errors with non-obvious causes.

I've managed to replicate this with this test program (run concurrently):

use DB::SQLite;

sub MAIN() {
    my $path = 'tmp.sqlite';
    my $exists = $path.IO ~~ :e;
    my $db = DB::SQLite.new(filename => $path);

    unless $exists {
        say "setting up test data";
        $db.query('CREATE TABLE t(foo, bar)');
        $db.query('INSERT INTO t(foo, bar) values(?, ?)', 1, 2);
    }

    # DB gets locked here
    say ">> running a query that will lock the database";
    my $value = $db.query('SELECT * FROM t WHERE foo = ?', 1).hash;

    # demonstrate the lock with any other write operation
    say ">> running another write query";
    $db.query('INSERT INTO t(foo, bar) values(?, ?)', 2, 3);

    say ">> waiting...";
    sleep 100;
}

This reliably causes a "database is locked" error if I run the script in one terminal, and then run a second copy of the script while the first is waiting.

I'm not sure if this is a usage error on my side, but it seems surprisingly easy to get the database stuck like this.

The Perl 5 implementation of my application runs reliably with DBD::SQLite - though the usage pattern there is very standardized (almost always prepare, execute, return fetchall_arrayref({}), though no explicit finish calls).

I'm planning to move this application to Postgres for other reasons (primarily notify support), but I wanted to provide some feedback. Thanks.

Out of memory when invoking several times .hashes

Hi,

I just got an error "Out of memory". The SQLite file is 368M.

Steps:

> raku
To exit type 'exit' or '^D'
> use DB::SQLite;
Nil
> my $dbh = DB::SQLite.new(filename => 'data/proxy.sqlite');
DB::SQLite.new(filename => "data/proxy.sqlite", busy-timeout => 10000, max-connections => 5, connections => Concurrent::Stack.new)
> my $result = $dbh.query('select * from requests limit 1');
DB::SQLite::Result.new(stmt => DB::SQLite::Native::Statement.new, count => 10, sth => DB::SQLite::Statement.new(stmt => DB::SQLite::Native::Statement.new, count => 10, db => DB::SQLite::Connection.new(conn => DB::SQLite::Native.new, owner => DB::SQLite.new(filename => "data/proxy.sqlite", busy-timeout => 10000, max-connections => 5, connections => Concurrent::Stack.new))), finish => Bool::True, keys-cache => Any)
> $result.hash
{cache => 86400, content_type => image/jpeg, host => images-webcams.windy.com, id => 1, method => GET, port => 443, protocol => HTTP/1.1, raw_response => Buf[uint8]:0x<48 54 54 50 2F 31 2E 31 20 32 30 30 20 4F 4B 0D 0A 53 65 72 76 65 72 3A 20 6E 67 69 6E 78 2F 31 2E 31 30 2E 33 0D 0A 44 61 74 65 3A 20 53 75 6E 2C 20 33 31 20 4D 61 79 20 32 30 32 30 20 31 32 3A 30 36 3A 31 33 20 47 4D 54 0D 0A 43 6F 6E 74 65 6E 74 2D 54 79 70 65 3A 20 69 6D 61 67 65 2F 6A 70 65 67 ...>, status => 200 OK, uri => /16/1292677816/daylight/thumbnail/1292677816.jpg}
> $result.hashes
()
> say $_<content_type> for $result.hashes
image/jpeg
> say $_<content_type> for $result.hashes
image/jpeg
> say $_<content_type> for $result.hashes
image/jpeg
> say $_<content_type> for $result.hashes
image/jpeg
> say $_<content_type> for $result.hashes
out of memory
  in block  at /home/demanuel/.raku/sources/3481BCAD53FC3338AC505479B91D3AD1F5BF3724 (DB::SQLite::Result) line 42
  in method row at /home/demanuel/.raku/sources/3481BCAD53FC3338AC505479B91D3AD1F5BF3724 (DB::SQLite::Result) line 27
  in method pull-one at /home/demanuel/.raku/sources/51F6397FB5D02B2A0E15A62EC3ACF47669DBF3DC (DB::Result) line 21
  in block <unit> at <unknown file> line 1

> say $_<content_type> for $result.hashes
out of memory
  in block  at /home/demanuel/.raku/sources/3481BCAD53FC3338AC505479B91D3AD1F5BF3724 (DB::SQLite::Result) line 42
  in method row at /home/demanuel/.raku/sources/3481BCAD53FC3338AC505479B91D3AD1F5BF3724 (DB::SQLite::Result) line 27
  in method pull-one at /home/demanuel/.raku/sources/51F6397FB5D02B2A0E15A62EC3ACF47669DBF3DC (DB::Result) line 21
  in block <unit> at <unknown file> line 1

> 

can't use a named bind parameters with execute

The method signature of execute

    method execute(Bool :$finish, *@args, *%args)

is such that the third parameter (%args) is never going to be populated. *@args always steals the data that would be caught by *%args.

As a result it always raises an error if you try to call it with a hash parameter, because it uses the list block instead of the named parameter block.

here's a little test routine from in the REPL to prove this.

[253] > sub foo(Bool :$finish, *@args, *%args) { if @args { say "@args";}; if %args { say "%args";}}
&foo
[254] > foo(@list);
@args
[254] > foo(%hash);
@args
[254] > foo(@list, %hash);
@args

on a related note: the docs for this method don't match the signature of the method

=head2 B<execute>(**@args, Bool :$finish)

Rakudist test fails

Hi! I wonder, why zef complaints about sqlite dependencies, even after it's installed?

*** patch dir: /root/.sparrowdo/env/debian/.sparrowdo/files/patches/DB::SQLite, patch found ***
*** execute patch from /root/.sparrowdo/env/debian/.sparrowdo/files/patches/DB::SQLite/sparrowfile ***
15:15:19 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] trying to install libsqlite3-dev ...
15:15:19 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] installer - apt-get
15:15:19 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] Package: libsqlite3-dev
15:15:19 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] Version: 3.27.2-3
15:15:19 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] Status: install ok installed
[task check] stdout match <Status: install ok installed> True
15:15:20 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] trying to install sqlite3 ...
15:15:20 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] installer - apt-get
15:15:20 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] Package: sqlite3
15:15:20 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] Version: 3.27.2-3
15:15:20 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] Status: install ok installed
[task check] stdout match <Status: install ok installed> True
15:15:20 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] trying to install sqlite ...
15:15:20 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] installer - apt-get
15:15:20 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] Package: sqlite
15:15:20 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] Version: 2.8.17-15
15:15:20 10/29/2020 [install package(s): {:debian("libsqlite3-dev sqlite3 sqlite")}] Status: install ok installed
[task check] stdout match <Status: install ok installed> True
15:15:25 10/29/2020 [bash: zef install DB::SQLite] ===> Searching for missing dependencies: BitEnum, DB, NativeLibs:ver<0.0.7+>:auth<github:salortiz>, sqlite:from<native>, Test::When
15:15:58 10/29/2020 [bash: zef install DB::SQLite] ===> Failed to find dependencies: sqlite:from<native>
15:15:58 10/29/2020 [bash: zef install DB::SQLite] stderr: Failed to resolve some missing dependencies
15:15:58 10/29/2020 [bash: zef install DB::SQLite] task exit status: 1
15:15:58 10/29/2020 [bash: zef install DB::SQLite] task bash: zef install DB::SQLite FAILED
Error: non zero exit code: 1: OCI runtime error
The spawned command 'podman exec -it debian-rakudist sh /root/.sparrowdo/env/debian/.sparrowdo/sparrowrun.sh' exited unsuccessfully (exit code: 1, signal: 0)
  in block <unit> at /home/rakudist/.rakubrew/versions/moar-2020.05.1/share/perl6/site/resources/B048A8B0FC7EDBE1A75DF9FA9EA11DCB8F98BFF7 line 14
  in sub MAIN at /home/rakudist/.rakubrew/versions/moar-2020.05.1/share/perl6/site/bin//sparrowdo line 3
  in block <unit> at /home/rakudist/.rakubrew/versions/moar-2020.05.1/share/perl6/site/bin//sparrowdo line 1

The full log is available here - http://rakudist.raku.org/sparky/report/debian/825

HTH

Alexey

Native lib name sqlite3 vs sqlite

looks like the library name on latest ubuntu is libsqlite3.so.

> use NativeLibs; NativeLibs::Searcher.try-versions('sqlite', 'sqlite3_libversion', 0);
(Any)
> use NativeLibs; NativeLibs::Searcher.try-versions('sqlite3', 'sqlite3_libversion', 0);
libsqlite3.so.0

May be the dependency name in META6.json should be changed to sqlite3:from< native > ?

another alternative would be (based on this comment ) :

{ "any": [ "sqlite3:from< native >", "sqlite:from< native >"] }

although this might be considered abusing the alternative dependencies suggestion.

Use of Nil in numeric context in DB::SQLite::Statement line 44

I'm using DB::SQLite in a project, and one particular statement I'm trying to execute is throwing this error.

Use of Nil in numeric context
  in block  at /home/tyil/.rakudo-star/rakudo-star-2019.03/install/share/perl6/site/sources/A96187CD3259EABBAF49C3BE8984779C46DABD52 (DB::SQLite::Statement) line 44
No such method 'hashes' for invocant of type 'Slip'. Did you mean 'hash'?
  in method get-next-for-project at /home/tyil/projects/personal/gtd-api/lib/App/GTD/Models/Sqlite/Thing.pm6 (App::GTD::Models::Sqlite::Thing) line 152
  in method next at /home/tyil/projects/personal/gtd-api/lib/App/GTD/Models/Sqlite/Project.pm6 (App::GTD::Models::Sqlite::Project) line 84
  in sub MAIN at /home/tyil/projects/personal/gtd-api/lib/App/GTD/Bin/ListProjects.pm6 (App::GTD::Bin::ListProjects) line 30
  in block <unit> at bin/gtd line 11

Looking at the source, it looks like .step is being set to Nil somewhere, though I don't have an idea how that could happen.

doc error in Result.pm6

In file lib/DB/SQLite/Result.pm6 there is a MySql reference at line 52:

DB::MySQL::Result -- Results from a MySQL query

provide a way to properly hot back up a db [FEATURE REQUEST]

The module fails to install due to hard-coded NativeLibs version

NativeLibs has been updated recently to v0.0.8. DB::SQLite depends specifically on v0.0.7 and consequently fails to install since Oct 16 when the dependency has been updated. I tried removing version adverb from the use statement in the code and testing. Tests are passing ok. Perhaps it worth leaving the version in META6 only and have it in 0.0.7+ form?

DB::SQLite doesn't like IntStr placeholders

DB::SQLite dies when passing an IntStr value as a placeholder. Most likely scenario is when passing a parameter on the command line via sub MAIN.

Example:

#!/usr/bin/env perl6
use v6.c;

use DB::SQLite;

my $db = DB::SQLite.new;

sub MAIN(Int $foo)
{
    say $db.query('SELECT 1 WHERE ? = ?', $foo, $foo).value;
}

This dies with:

Ambiguous call to 'AUTOGEN(DB::SQLite::Native::Statement: Int, IntStr)'; these signatures all match:
:(DB::SQLite::Native::Statement: Int $n, Int:D $v, *%_ --> Nil)
:(DB::SQLite::Native::Statement: Int $n, Str:D $v, *%_ --> Nil)
  in method execute at /opt/perl6-2018.10/share/perl6/site/sources/F735A230CF27D032781E817AC2AB5AC529C14695 (DB::SQLite::Statement) line 25
  in method query at /opt/perl6-2018.10/share/perl6/site/sources/F1B43C2361FC6DB5F165CBD906DB9F08A144DB8A (DB::Connection) line 48
  in method query at /opt/perl6-2018.10/share/perl6/site/sources/51223C7606846125E36A7A53A8884E0090F594CE (DB) line 21
  in sub MAIN at ./sqlite-test line 8
  in block <unit> at ./sqlite-test line 8

A workaround, of course, is to make sure all values are Ints:

    say $db.query('SELECT 1 WHERE ? = ?', +$foo, +$foo).value;

But it would be good if it wasn't necessary to work around this. I think it's safe to assume that an IntStr holds a number.