Piping hot process automation

Get current code for the example shown below from here.

There is also another example that returns the response using sqlite's json_array and json_quote functions, parsing each row with a minimal picolisp function, in order to reliably handle dataset results with escaped characters etc. Get that code here.

The function

Pipe a list of commands to an external process. It uses separate channels for stdout and stderr from the process and returns their output as lists

(de runExe (Cmds . Exe)
   (use (LOut LErr)
      (let (ErrFile (tmp "exerr." (rand)))
         (finally (call "rm" ErrFile)
            (pipe
               (err ErrFile (out Exe (mapc prinl Cmds)))
               (setq LOut (make (while (line T) (link @)))) )
            (in ErrFile
               (setq LErr (make (while (line T) (link @)))) ) ) )
      (list LOut LErr) ) )

Auxillary functions, used in the examples, to generate random strings using a Lehmer pseudo random generator. Note: (randR) is much slower than the built-in (rand) function

(de randomLehmer (Seed)
      (let @Seed (or Seed (inc (abs (rand))))
         (fill
            '(()
               (job '((State . @Seed))
                  (setq State (% (* State 279470273) 4294967291)) ) ) ) ) )

(setq *RndFn (randomLehmer (time)))
(de randR (Lo Hi) (+ Lo (% (*RndFn) (+ (- Hi Lo) 1))))

(de makeRndName (Cnt)
   (default Cnt 7)
   (pack
      (make
         (for N Cnt
            (link
               (char
                  (case (randR 1 3)
                     (1 (randR 48 57))
                     (2 (randR 65 90))
                     (3 (randR 97 122)) ) ) ) ) ) ) )

The Example

After defining the above functions, the following code can be run from the REPL. (Bench results are for i7 laptop with ssd.)

Remove existing db file

: (when (info "testdb.sqlite") (call "rm" "testdb.sqlite"))
-> T

Insert 1,000,000 random names of length 7

: (bench (let (Sql '(
  ".mode quote"
  "drop table if exists test;"
  "create table test(id integer primary key, name text);"))
  (setq Sql
    (append
      Sql
      '("begin transaction;")
      (make (do 1000000 (link (pack "insert into test (name) values ('" (makeRndName 7) "');"))))
      '("commit;")
      '("select count(*) from test;")))
  (bench (runExe Sql sqlite3 "-bail" "testdb.sqlite"))))
2.575 sec
5.671 sec
-> (("1000000") NIL)
:

Insert another 1,000,000 random names of length 7

: (bench (let (Sql '(
  ".mode quote"))
  (setq Sql
    (append
      Sql
      '("begin transaction;")
      (make (do 1000000 (link (pack "insert into test (name) values ('" (makeRndName 7) "');"))))
      '("commit;")
      '("select count(*) from test;")))
  (bench (runExe Sql sqlite3 "-bail" "testdb.sqlite"))))
2.267 sec
5.316 sec
-> (("2000000") NIL)
:

Use a unique index to report collisions inserting 100,000 names of length 5

: (bench (let (Sql '(
  ".mode quote"
  "drop table if exists test;"
  "create table test(id integer primary key, name text, cnt int default 1);"
  "create unique index uix_test_name on test(name);"))
  (setq Sql
    (append
      Sql
      '("begin transaction;")
      (make (do 100000 (link (pack
        "insert into test (name) values ('" (makeRndName 5) "') on conflict (name) do update set cnt = cnt + 1;"))))
      '("commit;")
      '("select id, name, cnt from test where cnt > 1;")
      '("select 'Collision Count: '||count(*) from test where cnt > 1;")
      '("select 'Record Count: ' || count(*) from test;")))
  (bench (runExe Sql sqlite3 "testdb.sqlite"))))
1.106 sec
1.407 sec
-> (("972,'6gV70',2" "1750,'8043R',2" "3028,'IWuM1',2" "12389,'s5759',2"
     "33788,'3u0hL',2" "34040,'9mZ5r',2" "44977,'28y37',2" "50643,'68W82',2"
     "56487,'47o4C',2" "62538,'M79fC',2" "69240,'90578',2" "73182,'T2UNB',2"
     "74881,'44h87',2" "76664,'Bvq22',2" "81208,'7fgwo',2"
     "'Collision Count: 15'"
     "'Record Count: 99985'") NIL)
:

Use a unique index to report collisions inserting 100,000 names of length 7

: (bench (let (Sql '(
  ".mode quote"
  "drop table if exists test;"
  "create table test(id integer primary key, name text, cnt int default 1);"
  "create unique index uix_test_name on test(name);"))
  (setq Sql
    (append
      Sql
      '("begin transaction;")
      (make (do 100000 (link (pack
        "insert into test (name) values ('" (makeRndName 7) "') on conflict (name) do update set cnt = cnt + 1;"))))
      '("commit;")
      '("select id, name, cnt from test where cnt > 1;")
      '("select 'Collision Count: '||count(*) from test where cnt > 1;")
      '("select 'Record Count: ' || count(*) from test;")))
  (bench (runExe Sql sqlite3 "testdb.sqlite"))))
0.826 sec
1.093 sec
-> (("'Collision Count: 0'" "'Record Count: 100000'") NIL)
:

Example showing an error being reported back

: (bench (let (Sql '(
  ".mode quote"
  "create table if not exists test(id integer primary key, name text, cnt int default 1);"
  "create unique index uix_test_name on test(name);"))
  (runExe Sql sqlite3 "-bail" "testdb.sqlite")))
0.125 sec
-> (NIL ("Error: near line 3: index uix_test_name already exists"))
:

https://picolisp.com/wiki/?runexe

03feb25

llawrence