Re: [sqlite] Storing 64-Bit Addresses
Thursday, March 31, 2005, 5:53:12 PM, you wrote: >> The actual test I'm doing is something like: >> WHERE ?1 >= (base + begin) AND ?1 < (base + end) >> where ?1, base, begin, and end are all 64-bit addresses. > This is a test with a well known optimization for unsigned values: > WHERE (?1 - base - begin) < end > To make the < test unsigned in SQL use > WHERE ((?1 - base - begin) + 9223372036854775808)) < (end + > 9223372036854775808) > This is with all values stored "raw," i.e., without offset. Oops, I meant WHERE (?1 - base - begin) < (end - begin) To make the < test unsigned in SQL use WHERE ((?1 - base - begin) + 9223372036854775808)) < ((end - begin) + 9223372036854775808) This works as long as end > begin. e
Re: [sqlite] Storing 64-Bit Addresses
> The actual test I'm doing is something like: > WHERE ?1 >= (base + begin) AND ?1 < (base + end) > where ?1, base, begin, and end are all 64-bit addresses. This is a test with a well known optimization for unsigned values: WHERE (?1 - base - begin) < end To make the < test unsigned in SQL use WHERE ((?1 - base - begin) + 9223372036854775808)) < (end + 9223372036854775808) This is with all values stored "raw," i.e., without offset. e
Re: [sqlite] Storing 64-Bit Addresses
How about storing the 64 bit integers as binaries and write user functions (using sqlite3_create_function ) to do the computation and comparison? William Hachfeld wrote: On Thu, Mar 31, 2005 at 01:52:53PM -0800, Ted Unangst wrote: Store the length of the region, instead of the end. Or is that impossible? I'm not sure what physical property (begin + end) could refer to. Sorry Ted, the example I gave was misleading. It was meant purely as an illustration. The actual test I'm doing is something like: WHERE ?1 >= (base + begin) AND ?1 < (base + end) where ?1, base, begin, and end are all 64-bit addresses. In some places I could store the length of the region rather than "end", but I'd still have to do the computation on "begin" using 64-bit quantities.
Re: [sqlite] Storing 64-Bit Addresses
On Thu, Mar 31, 2005 at 01:52:53PM -0800, Ted Unangst wrote: > Store the length of the region, instead of the end. Or is that impossible? > I'm not sure what physical property (begin + end) could refer to. Sorry Ted, the example I gave was misleading. It was meant purely as an illustration. The actual test I'm doing is something like: WHERE ?1 >= (base + begin) AND ?1 < (base + end) where ?1, base, begin, and end are all 64-bit addresses. In some places I could store the length of the region rather than "end", but I'd still have to do the computation on "begin" using 64-bit quantities. -- William Hachfeld ([EMAIL PROTECTED], 651-683-3103) SGI Compilers & Tools
Re: [sqlite] Storing 64-Bit Addresses
William Hachfeld wrote: Heh Everyone, Have a question regarding storage of 64-bit unsigned values in SQLite... I'm working on a project where I want to store 64-bit addresses (unsigned values) as a column in an SQLite database. Currently I create the table using signed integers. For example: CREATE TABLE Example ( begin INTEGER, end INTEGER ); Before binding an address to a statement using sqlite3_bind_int64() I apply an offset to the address to translate it to a signed value. And when reading out an address using sqlite3_column_int64() I reverse the process. I.e. dbase_value = addr_value - offset addr_value = dbase_value + offset where offset = ((uin64_t)~0 >> 1) + 1 this works fine for simple uses where I'm just storing and retrieving the value. But if I want to do more complex queries involving arithmetic, for example: SELECT * From Example WHERE ? < (begin + end); Store the length of the region, instead of the end. Or is that impossible? I'm not sure what physical property (begin + end) could refer to. -- Ted Unangst www.coverity.com Coverity, Inc.
Re: [sqlite] Storing 64-Bit Addresses
> Before binding an address to a statement using sqlite3_bind_int64() I apply an > offset to the address to translate it to a signed value. And when reading out > an address using sqlite3_column_int64() I reverse the process. I.e. > dbase_value = addr_value - offset > addr_value = dbase_value + offset > where offset = ((uin64_t)~0 >> 1) + 1 > this works fine for simple uses where I'm just storing and retrieving the > value. But if I want to do more complex queries involving arithmetic, for > example: > SELECT * From Example WHERE ? < (begin + end); It is rather messy, but if you defer offsetting the values until you compare them it should work (except maybe / and %). 1. Don't adjust the values when inserting into db 2. Do adjust the values on either side of a compare. SELECT * From Example WHERE (? + 9223372036854775808) < ((begin + end) + 9223372036854775808); Of course, once you go this route, forget using indexes. e
Re: [sqlite] Storing 64-Bit Addresses
William Hachfeld wrote: On Thu, Mar 31, 2005 at 12:07:22PM -0800, Jay wrote: I suppose I could convert the unsigned address into a string. But UINT64_MAX (2^64 - 1) is: 18,446,744,073,709,551,615 which, without the commas, would require 20 bytes to store. Quite a bit more than the 8 bytes it should require... Did I mention that I'll be storing tens to hundreds of thousands of these addresses? ;-) Store as hexadecimals or even create your own base, say 26 (a-z) + 26 (A-Z) + 10 (0-9), give you base of 62 regards, Alex
[sqlite] Storing 64-Bit Addresses
Heh Everyone, Have a question regarding storage of 64-bit unsigned values in SQLite... I'm working on a project where I want to store 64-bit addresses (unsigned values) as a column in an SQLite database. Currently I create the table using signed integers. For example: CREATE TABLE Example ( begin INTEGER, end INTEGER ); Before binding an address to a statement using sqlite3_bind_int64() I apply an offset to the address to translate it to a signed value. And when reading out an address using sqlite3_column_int64() I reverse the process. I.e. dbase_value = addr_value - offset addr_value = dbase_value + offset where offset = ((uin64_t)~0 >> 1) + 1 this works fine for simple uses where I'm just storing and retrieving the value. But if I want to do more complex queries involving arithmetic, for example: SELECT * From Example WHERE ? < (begin + end); My transformation breaks down. E.g. where offset = 8, ? = 4, begin = 2, and end = 3: 4 < (2 + 3) --> true (4 - 8) < ((2 - 8) + (3 - 8)) = -4 < (-6 - 5) = -4 < -11 --> false Darn mathematics anyway! So after that long-winded explanation, my question is simply this. Does anyone have an idea how I can store a 64-bit unsigned integer in an SQLite column and still perform useful arithmetic and conditional operators on them? Thanks in advance for any ideas! -- William Hachfeld
