SQLite using ops

[anishsapkota]

Hi,
I am trying to build SQLite image locally using the SQLite ELF.
I used this command: ops pkg from-run --name <name of pkg> --version <version> <path to ELF>

it created a local pkg for me:
which I tried executing using : ops pkg load --local <name_version> -a test.db

Terminal output:

running local instance
booting /home/ubuntu/.ops/images/sqlite3 ...
en1: assigned 10.0.2.15

so the sqlite cli is not running.. please help

[eyberg]

what exactly are you wanting to do?

you can do something like this:

eyberg@box:~$ ops run /usr/bin/sqlite -c config.json
running local instance
booting /home/eyberg/.ops/images/sqlite ...
en1: assigned 10.0.2.15
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             //bob.db
1    temp             //./sqlite_BeB6x9eMg6LH6uI
eyberg@box:~$ cat config.json
{
  "Args": ["bob.db", ".databases"],
  "Files": ["bob.db"]
}

but you should know that running sqlite (w/out sql) and just passing a test.db will put you into an interactive mode which we don't support as these are intended to run as servers/daemons or non-interactive jobs that do work and spin down

if you want to incorporate sqlite into your application all languages have bindings for that, if you want to run a sqlite server
you could run something like sqld - https://repo.ops.city/v2/packages/eyberg/sqld/0.1.0/x86_64/show

[anishsapkota]

.timer on works on normal sqlite3

[eyberg]

I believe the timer only works inside the sqlite shell itself, not passed as a command line arg

if you are benchmarking I'd encourage you to implement this functionality inside the language itself

[anishsapkota]

Hi,
is it possible to store bob.db file inside a volume and link it to a sqlite image? I could not figure it out.
I still have this issue that the changes made to the db are lost after the instance shuts down. :(
if I start a instance with state 1 and do some inserts and move it to state 2. I expect the new instance to start from state 2 not state 1.

[anishsapkota]

it seems like the changes made to the files passed as FILES : ["bob.db"], do not reflect on original "bob.db" file

[anishsapkota]

I am also trying to explore sqld, ops pkg get eyberg/sqld:0.1.0, but this command gets me package not found

[anishsapkota]

I tried a new approach with c, so this is csqlite.c file.
I compiled it with gcc csqlite.c -o csqlite -lsqlite3
It works as expected in normal environment.
so I packaged it using ops, : ops pkg from-run --name csqlite --version 1.1 csqlite

I tried to run it using ops pkg load --local csqlite_1.1 -c config.json

// config.json

{
  "BaseVolumeSz": "400m",
  "Args":  ["TPC-H-small.db"],
  "Files": ["TPC-H-small.db"],
  "Dirs":["TPCH-sqlite","outputs", "usr"]
}

usr folder :

. -lib -libsqlite3.so.0 1 directory, 1 file
// csqlite.c

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#include <sys/time.h>
#include <time.h>

#define NUM_QUERIES 22
#define QUERY_DIR "./TPCH-sqlite/queries/"
#define OUTPUT_NANOS "./outputs/nanos_sqlite/"

void execute_query(sqlite3 *db, const char *query, const char *output_file) {
    char *errMsg = 0;
    FILE *fp = fopen(output_file, "w");
    if (!fp) {
        fprintf(stderr, "Can't open output file %s\n", output_file);
        return;
    }

    // Callback to write query results to the output file
    int callback(void *data, int argc, char **argv, char **azColName) {
        for (int i = 0; i < argc; i++) {
            fprintf(fp, "%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
        }
        fprintf(fp, "\n");
        return 0;
    }

    struct timeval start, end;
    gettimeofday(&start, NULL);

    int rc = sqlite3_exec(db, query, callback, 0, &errMsg);
    
    gettimeofday(&end, NULL);
    fclose(fp);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
    } else {
        long seconds = (end.tv_sec - start.tv_sec);
        long micros = ((seconds * 1000000) + end.tv_usec) - (start.tv_usec);
        printf("Query execution time: %ld seconds %ld microseconds\n", seconds, micros);
    }
}

int main(int argc, char *argv[]) {
    if (argc < 2) {
        fprintf(stderr, "Usage: %s [db_file]\n", argv[0]);
        return 1;
    }

    const char *db_file = argv[1];

    sqlite3 *db;
    int rc = sqlite3_open(db_file, &db);
    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return 1;
    } else {
        fprintf(stderr, "Opened database successfully\n");
    }

    char query_file[256];
    char query[4096];
    char output_file[256];

    for (int i = 1; i <= NUM_QUERIES; i++) {
        snprintf(query_file, sizeof(query_file), QUERY_DIR "query%d.sql", i);
        snprintf(output_file, sizeof(output_file), "%d.txt", i);

        FILE *fp = fopen(query_file, "r");
        if (!fp) {
            fprintf(stderr, "Can't open query file %s\n", query_file);
            continue;
        }

        // Read the query file into a string
        size_t len = fread(query, 1, sizeof(query), fp);
        query[len] = '\0';  // Ensure null termination
        fclose(fp);

	printf("Preparing....Query %d\n", i);
	printf("Executing query %d on sqlite3...\n", i);
	snprintf(output_file, sizeof(output_file), OUTPUT_NORMAL "query%d.txt", i);
	execute_query(db, query, output_file);
    }

    sqlite3_close(db);
    return 0;
}

I am getting this error:

`
ubuntu@ubuntu:~/DBPRO$ ops pkg load --local csqlite_1.1 -c config_1.json
running local instance
booting /home/ubuntu/.ops/images/csqlite ...
en1: assigned 10.0.2.15
Opened database successfully
Preparing....Query 1
Executing query 1 on sqlite3...
en1: assigned FE80::D029:89FF:FEA4:B3C

*** signal 11 received by tid 2, errno 0, code 2
fault address 0xffef69b8

*** Thread context:

 frame: 00ff800042200800
  type: thread
  spsr: 0000000060000800
   esr: 000000008200000f instruction abort in el0
   far: 00000000ffef69b8
   elr: 00000000ffef69b8
   far: 00000000ffef69b8

active_cpu: 00000000ffffffff
stack top: 0000000000000000

   x0: 0000000000000000  q0: 400057f1eb7464001c358203b0000000
   x1: 000000000000000a  q1: 40024000000000000000000000000000
   x2: 00000000004ba1a8  q2: 0000000000000000000000003f800000
   x3: 00000000004ba158  q3: f00ff00ff00ff00f000f0000000f0000
   x4: 00000000004b82b8  q4: f00ff00ff00ff00ff00ff00ff00ff00f
   x5: 000000000000017f  q5: ffffffff0f000f00ffffffff0f000f00
   x6: fefefefefefefeff  q6: 00000000ccccccc000000000ccccccc0
   x7: 7f7f7f7f7f7f7f7f
   x8: 0101010101010101
   x9: cccccccccccccccd
  x10: 000000000002414e
  x11: 0000000000000000
  x12: 3935343431303530
  x13: 3534353433363037
  x14: 0000000000000000
  x15: 0000000000000000
  x16: 0000061118441d68
  x17: 0000002edde921b0
  x18: 0000000100000000
  x19: 00000000004ba1f8
  x20: 000000000000000a
  x21: 00000000ffef69b8
  x22: 00000000004ba1a8
  x23: 00000000004ba158
  x24: 00000000004b8308
  x25: 0000000000000000
  x26: 00000000ffef6978
  x27: 00000000ffef6930
  x28: 000000000000000a
  x29: 00000000ffef68b0
  x30: 0000061118384b84
   sp: 00000000ffef68b0
 fpsr: 00ff000000022f38
 fpcr: 00ff000000000420

frame trace:
00000000ffef68b8: 000000000042de40
00000000ffef6948: 000000000042e0f8
00000000ffef69f8: 0000002edde373fc
00000000ffef7c58: 0000002edde374cc
00000000ffef7d68: 000000000042db70

kernel load offset ffffffffa14d8000

loaded klibs:

stack trace:
00000000ffef68b0: 00000000ffef6940
00000000ffef68b8: 000000000042de40
00000000ffef68c0: 00000000ffef7dc8
00000000ffef68c8: 0000000000000002
00000000ffef68d0: 000000000043ecf8
00000000ffef68d8: 0000160243f06040
00000000ffef68e0: 000000000042df04
00000000ffef68e8: 0000002eddfaa000
00000000ffef68f0: 0000000000000000
00000000ffef68f8: 00000000ffef7de0
00000000ffef6900: 000000000043ecf8
00000000ffef6908: 0000000000000000
00000000ffef6910: 00000000ffef7dc8
00000000ffef6918: 00000611183f4d90
00000000ffef6920: 000000000043ecf8
00000000ffef6928: 00000000004c1078
00000000ffef6930: 00000000ffef6eac
00000000ffef6938: fc23b15fe2df8d00
00000000ffef6940: 00000000ffef69f0
00000000ffef6948: 000000000042e0f8
00000000ffef6950: 2e2e2e336574696c
00000000ffef6958: 00000000ffef6b48
00000000ffef6960: 00000000ffef6c48
00000000ffef6968: 00000000004b8308
00000000ffef6970: 0000000000000000
00000000ffef6978: 0000000000000000
00000000ffef6980: f00ff00ff00ff00f
00000000ffef6988: f00ff00ff00ff00f
00000000ffef6990: 000000006650ec2d
00000000ffef6998: 0000000000010c00
00000000ffef69a0: ccccccc0cc000000
00000000ffef69a8: ccccccc0cc000000

core dump
`

[eyberg]

so there are a handful of issues you are running into here:

1. for 'pkg not found' on sqld - the vast majority of the stuff on
   repo.ops.city is x86 not arm64, so for instance on my mac:

➜  ~ ops pkg get eyberg/sqld:0.1.0
package not found
➜  ~ ops pkg get eyberg/sqld:0.1.0 --arch=amd64
 100% |████████████████████████████████████████|
➜  ~

you'll want to specify the proper arch for what the pkg is if it's not the architecture that you are currently on (being detected)

2. I think one of the issues you are running into is that when you do a
   'ops pkg load' you are creating both an image and an instance at the
   same time - any arguments you specify are put into the image when you
   run it; (there are ways around this, eg: https://docs.ops.city/ops/klibs#cloud-init-http-s-to-file-env-klib but that's a bit more complicated than what we should look at right now);

by default, while you can do a '-s' or '--skipbuild' that will prevent new
args from being applied; so what this means is that you can't simply
pass a 'insert' and then a 'select' on different runs and get diff
results; what you could possibly do is a 'insert' then do a 'ops image
cp' to cp the file .db back; to elaborate when you do a 'ops image
create' you are creating a new 'disk image' and when you do a 'ops
instance create' you are creating a new 'virtual machine' that is
completely separate from your local filesystem and your normal 'real'/physical machine;

you have to copy the files out of it to get your results; considering your use-case is that
you want to benchmark i'd put both of these under the same program and
run it together

3. the c example you pasted has a nested function which I don't think is
   going to work here by default; we disable stack exec by default and to turn it on
   you need to compile nanos specifically for it as shown in
   cannot enable executable stack as shared object requires: Permission denied nanos#1993 (comment) - basically you just need to flag that 'VMAP_FLAG_EXEC' - we disable this by default because it's heavily abused - w/out this i was able to repro what you saw but w/it on it worked

[anishsapkota]

Thank you, I actually took out the nested function making it static and it works now. Thank you very much . :)