Hi thanks again Igor,
I've successfully use sqlite3 on a quite real volume of data with custom
bitwise functions for data selection.
Here is the script Python 2.5 that serves as a proof of concept :
import os
import sqlite3
def blob_and(b1,b2):
op1 = str(b1)
op2 = str(b2)
i = 0
result = str()
for char in op1:
result = result + chr(ord(char) & ord(op2[i]))
i = i + 1
return buffer(result)
def blob_and_not_null(b1,b2):
result = blob_and(b1,b2)
for byte in result:
if ord(byte)!=0:
return 1
return 0
def select(requester,request):
print 'Selected by "' + request[:60] + '" : '
for row in requester.execute(request):
print '\t' + str(row[0])
def create_blob_from_node_list(length, arg):
if isinstance(arg,int):
nodes = [ arg ]
if isinstance(arg,list):
nodes = arg
nodes.sort()
result = str()
byte = 0
offset = 0
for node in nodes:
if node/8 > offset:
byte_char = hex(byte)[2:]
if len(byte_char)==1:
byte_char = '0'+ byte_char
result = result + byte_char
result = result + '00' * (node/8 - (len(result)/2))
byte = 0
offset = node/8
byte = byte | (0x80>>node%8)
byte_char = hex(byte)[2:]
if len(byte_char)==1:
byte_char = '0'+ byte_char
result = result + byte_char
result = result + '00' * (length/8 - (len(result)/2))
return "X'" + result + "'"
if(os.path.isfile('test_coveragedb.db')):
os.unlink('test_coveragedb.db')
connection = sqlite3.connect('test_coveragedb.db')
requester = connection.cursor()
request = 'CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT, bits
BLOB);'
print request
requester.execute(request)
blob = create_blob_from_node_list(200000, [])
request = 'INSERT INTO test(id,bits) VALUES(NULL, ' + blob + ');'
print request[:60]
requester.execute(request)
for i in range(0,999):
blob = create_blob_from_node_list(200000, [9,i])
request = 'INSERT INTO test(id,bits) VALUES(NULL, ' + blob + ');'
print request[:60]
requester.execute(request)
connection.commit()
print 'DB created and filled...'
connection.create_function('blob_and',2,blob_and)
print 'blob_and() function added...'
connection.create_function('blob_and_not_null',2,blob_and_not_null)
print 'blob_and_not_null() function added...'
print '[TEST] Selection of all tests which activate one node or more :'
select(requester,'SELECT id FROM test WHERE blob_and_not_null(bits,X\''
+ 'FF' * 25000 + '\');')
print '[TEST] Selection of all tests which don\'t activate any node :'
select(requester,'SELECT id FROM test WHERE
NOT(blob_and_not_null(bits,X\'' + 'FF' * 25000 + '\'));')
print '[TEST] Selection of the test no 11 knowing it activates the 10th
node :'
select(requester,'SELECT id FROM test WHERE bits=' +
create_blob_from_node_list(200000,[9]) + ';')
print '[TEST] Selection of all nodes which activate the 10th node :'
select(requester,'SELECT id FROM test WHERE blob_and_not_null(bits,' +
create_blob_from_node_list(200000,[9]) + ');')
requester.close()
connection.close()
print 'End of execution...'
Sorry, there is no comment.
I will try blob_open() and blob_read() later, to see if it really
increases performances...
Best regards,
Bruno.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
