SQL Database Instead of Flat File

[Tom Mayo]

Folks,

I am considering migrating RoverLog's runtime storage of the log to a Sqlite3 database instead of raw Cabrillo data.

This should permit faster operation and permit a standardized interface within the code.

Below is an example prototype script for calculating the score.

The key is the Sqlite3 database query "select" used twice below.  This means no more Tcl for loop over each entry in the log.  The queries are much faster that loops and this makes it so RoverLog doesn't have to have multiple databases---one for dupes, one for the whole log, one for unique call/sent/recd, etc.

Tom.

#assumes sqlite3 database "logdb" is already open and loaded.

set bands [ list 50 144 222 432 902 1.2G 2.3G 3.4G 5.7G 10G ]

set qsopt [ list  1   1   2   2   3    3    4    4    4   4 ]

for { set i 0 } { $i < 10 } { incr i } {

  set b [ lindex $bands $i ]

  set qp [ lindex $qsopt $i ]

  # puts "counting qsos for $b"

  set q [ logdb eval { select count(*) from ( select distinct substr(sent,0,5), call, substr(recd,0,5) from log where band=$b ) } ]

  set bs [ expr $q * $qp ]

  set tq [ expr $tq + $bs ]

  # puts "counting multipliers for $b"

  set m [ logdb eval { select count(*) from ( select distinct substr(recd,0,5) from log where band=$b ) } ]

  set tm [ expr $tm + $m ]

  puts "b=$b q=$q qp=$qp bs=$bs m=$m"

}

puts "tm=$tm"

puts "tq=$tq"

set ts [ expr $tm * $tq ]

puts "ts=$ts"

[Tom Mayo]

And now the Python version:

import sqlite3

def instr(a, b):

    return a.find(b) + 1

bands = [ "50", "144", "222", "432", "902", "1.2G", "2.3G", "3.4G", "5.7G", "10G" ]

qsopt = [ 1,    1,   2,   2,   3,    3,    4,    4,    4,   4 ]

print "opening log"

logdb = sqlite3.connect('n1mu_log.db')

print "reading log"

tq = 0

tqp = 0

tm = 0

for i in range(len(bands)):

  b = bands[i]

  qp = qsopt[i]

  # print "counting qsos for", b

  logdb.create_function("instr", 2, instr)

  c = logdb.execute('''select count(*) from ( select distinct substr(sent,0,5), ifnull(nullif(substr(call,0,instr(call,'/')),''),call), substr(recd,0,5) from log where band=:b )''', {"b": b})

  q = c.fetchone()[0]

  tq = tq + q

  bs = q * qp

  tqp = tqp + bs

  # print "counting multipliers", b

  c = logdb.execute('''select count(*) from ( select distinct substr(recd,0,5) from log where band=:b )''', {"b": b})

  m = c.fetchone()[0]

  tm = tm + m

  print "b=", b, "q=", q, "qp=", qp, "bs=", bs, "m=", m

print "tq=", tq

print "tm=", tm

print "tqp=", tqp

ts = tm * tqp

print "ts=", ts

print "closing log"

logdb.close

Output:

opening log

reading log

b= 50 q= 1193 qp= 1 bs= 1193 m= 246

b= 144 q= 478 qp= 1 bs= 478 m= 53

b= 222 q= 184 qp= 2 bs= 368 m= 44

b= 432 q= 266 qp= 2 bs= 532 m= 40

b= 902 q= 68 qp= 3 bs= 204 m= 29

b= 1.2G q= 88 qp= 3 bs= 264 m= 27

b= 2.3G q= 80 qp= 4 bs= 320 m= 33

b= 3.4G q= 69 qp= 4 bs= 276 m= 28

b= 5.7G q= 45 qp= 4 bs= 180 m= 12

b= 10G q= 18 qp= 4 bs= 72 m= 7

tq= 2489

tm= 519

tqp= 3887

ts= 2017353

closing log

[John Isham]

That's a nice score... what's the test dataset? W2SZ's log from this weekend??

73 de john aa1i

> --
> You received this message because you are subscribed to the Google Groups
> "RoverLog" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to roverlog+u...@googlegroups.com.
> To post to this group, send email to rove...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/roverlog/e87b6141-0b7a-40cd-b7cd-a4480f523841%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>

[Tom Mayo]

No, it's very old data.  2006.

To view this discussion on the web visit https://groups.google.com/d/msgid/roverlog/CABQ7yN_tOFD-hkJA5rOdJtU_LD0znkGcG0%2BXgFqswx5vsY6p3w%40mail.gmail.com.

[Buck Calabro]

I think SQLIte is a great idea.

73 de kc2hiz

  --buck

To view this discussion on the web visit https://groups.google.com/d/msgid/roverlog/CAAboCC-f1Y-MGU46qdvsmi4u8s52rbrGn0cJBGH_OiD8PJf6EA%40mail.gmail.com.