[sqlite] Please confirm what I THINK I know about blobs

2015-05-09 Thread Simon Slavin

On 9 May 2015, at 8:12pm, Drago, William @ CSG - NARDA-MITEQ  wrote:

> Best idea yet! Anyone see any issues with this?

It's actually a comment, and SQLite provides ways of putting proper comments in 
table definitions:

CREATE TABLE blob_table (
 ModelNo TEXT, -- new-style models as used from 2006 onwards
 SerialNo TEXT,
 VSWR BLOB -- array of ten double-length floats
)

These comments can be found if you look at the table definition in 
sqlite_master.

I've also seen SQL databases where the designer created an otherwise unused 
table to hold comments on every column, something like this:

CREATE TABLE _structure (
tableName TEXT,
columnName TEXT,
introduced TEXT,
variableType TEXT,
theComments TEXT
)

'introduced' was the edit of their program which first used the column 
(equivalent to 'checkin' as used by the SQLite development team).  
'variableType' was not the SQL type but the type of variable in the programming 
language they were using the database with.  This helped because the language 
had numerous variable types and subtle bugs could be introduced if you, for 
example, stored a value from an unsigned integer then did maths on the value in 
a long integer.

Simon.


[sqlite] Signal handling and mmap

2015-05-09 Thread James K. Lowden
On Tue, 5 May 2015 12:05:51 -0700
Charles Munger  wrote:

> At https://www.sqlite.org/mmap.html, the documentation mentions:
> 
> "An I/O error on a memory-mapped file cannot be caught and dealt with
> by SQLite. Instead, the I/O error causes a signal which, if not
> caught by the application, results in a program crash."
> 
> It seems to me that a naively implemented handler might introduce
> database coherency bugs and other issues. Is there anywhere where I
> can read about how one might implement such a signal handler
> correctly, to gracefully recover the same way any as other sqlite I/O
> error?

Not that I know of.  ISTM one goal should be to convert the signal into
something that results in a rollback.  Then perhaps the user can
remove unneeded files and release enough space for the operation to
continue.  But if the problem is failing hardware, no amount of
signal-handling will reliably prevent file corruption.  

--jkl


[sqlite] Please confirm what I THINK I know about blobs

2015-05-09 Thread Drago, William @ CSG - NARDA-MITEQ
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Eric Hill
> Sent: Saturday, May 09, 2015 2:14 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Please confirm what I THINK I know about blobs
>
> The comment approach could work, I guess, but why not just encode the
> type into the column's declared type?
>
> CREATE TABLE blob_table (
>   ModelNo TEXT,
>   SerialNo TEXT,
>   VSWR BLOB_DOUBLE
> )
>
> That's what I do with numeric columns that I need to identify as
> actually containing dates.  As I understand it, SQLite only scans those
> type names to choose a column affinity, so you are free to be as
> creative with them as you want.  sqlite3_column_decltype() will return
> whatever you put as the type.
>

Best idea yet! Anyone see any issues with this?

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com

> HTH,
>
> Eric

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.


[sqlite] Multiple Prepared Statements

2015-05-09 Thread Kees Nuyt
On Fri, 08 May 2015 14:49:54 -0700, Scott Doctor
 wrote:

> Can I prepare multiple statements then implement them in 
> arbitrary order (based on some logic)?

Yes.

> Or do the statements need to be prepared, stepped, finalized 
> serially?

No. You even don't have to _finalize() the statement, you can
just _reset() it to finish the _step() loop and keep the
statement around for reuse (with new bindings) later.

_reset() will free the resources that are allocated at the first
_step().

-- 
Regards,

Kees Nuyt


[sqlite] Bug: sqlite3_memory_used/highwater truncate to 32 bits

2015-05-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

The sqlite3_memory_used and highwater interfaces are defined to return
a 64 bit value.  They carefully call the 32 bit limited sqlite3_status
method and then cast to 64 bit.  Instead they should call
sqlite3_status64.

Reported indirectly via https://github.com/rogerbinns/apsw/issues/191

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlVOsbwACgkQmOOfHg372QSkLgCgkqMS2hWqLFU0246u8aqx+JtN
NkcAn3iDFhK5/+pq5v0n6vTprxZflaXZ
=4QMd
-END PGP SIGNATURE-


[sqlite] Please confirm what I THINK I know about blobs

2015-05-09 Thread Eric Hill
The comment approach could work, I guess, but why not just encode the type into 
the column's declared type?

CREATE TABLE blob_table (
  ModelNo TEXT,
  SerialNo TEXT,
  VSWR BLOB_DOUBLE
)

That's what I do with numeric columns that I need to identify as actually 
containing dates.  As I understand it, SQLite only scans those type names to 
choose a column affinity, so you are free to be as creative with them as you 
want.  sqlite3_column_decltype() will return whatever you put as the type.

HTH,

Eric

Sent from Windows Mail

From: William Drago
Sent: ?Saturday?, ?May? ?9?, ?2015 ?7?:?18? ?AM
To: sqlite-users at mailinglists.sqlite.org

On 5/9/2015 6:40 AM, Eduardo Morras wrote:
> On Sat, 09 May 2015 06:09:41 -0400
> William Drago  wrote:
>
>> All,
>>
>> Say you encounter a blob in a database. There's no way to
>> tell if that blob carries bytes, floats, doubles, etc, correct?
>>
>> Assuming the above is true, then is it always prudent to
>> store some metadata along with your blobs so that they can
>> be identified in the future?
>>
>> Example table:
>>
>> ModelNo TEXT (e.g. SO-239)
>> SerialNo TEXT (e.g. 101)
>> VSWR BLOB (e.g. x'feab12c...')
>> VSWR_Type TEXT (e.g. double)
>>
>>
>> Does this make sense?
> You can use SQL comments on CREATE TABLE, those comments aren't deleted from 
> SQLITE_MASTER table, you can query it as a normal table.
>
> CREATE TABLE blob_table (
>   ModelNo TEXT,  -- e.g. S0-239
>   SerialNo TEXT, -- e.g. 101
>   VSWR BLOB  -- double, e.g. x'feab12c'
> );
>
> SELECT sql from sqlite_master where type='table' AND tbl_name='blob_table';
>
> will return
>
> CREATE TABLE blob_table (
>   ModelNo TEXT,  -- e.g. S0-239
>   SerialNo TEXT, -- e.g. 101
>   VSWR BLOB  -- double, e.g. x'feab12c'
> )

This is a clever idea and saves the addition of a column
just for blob type. Is this a reliable feature of SQLite?
Does anyone see any issues with this as opposed to using a
dedicated column?

Thanks,
-Bill


___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Please confirm what I THINK I know about blobs

2015-05-09 Thread Eduardo Morras
On Sat, 09 May 2015 06:09:41 -0400
William Drago  wrote:

> All,
> 
> Say you encounter a blob in a database. There's no way to 
> tell if that blob carries bytes, floats, doubles, etc, correct?
> 
> Assuming the above is true, then is it always prudent to 
> store some metadata along with your blobs so that they can 
> be identified in the future?
> 
> Example table:
> 
> ModelNo TEXT (e.g. SO-239)
> SerialNo TEXT (e.g. 101)
> VSWR BLOB (e.g. x'feab12c...')
> VSWR_Type TEXT (e.g. double)
> 
> 
> Does this make sense?

You can use SQL comments on CREATE TABLE, those comments aren't deleted from 
SQLITE_MASTER table, you can query it as a normal table.

CREATE TABLE blob_table (
 ModelNo TEXT,  -- e.g. S0-239
 SerialNo TEXT, -- e.g. 101
 VSWR BLOB  -- double, e.g. x'feab12c'
);

SELECT sql from sqlite_master where type='table' AND tbl_name='blob_table';

will return

CREATE TABLE blob_table (
 ModelNo TEXT,  -- e.g. S0-239
 SerialNo TEXT, -- e.g. 101
 VSWR BLOB  -- double, e.g. x'feab12c'
)

(Note that ';' is deleted)

I check it in Firefox Sqlite3 Manager extension.

> Thanks,
> -Bill


---   ---
Eduardo Morras 


[sqlite] Please confirm what I THINK I know about blobs

2015-05-09 Thread William Drago
On 5/9/2015 6:40 AM, Eduardo Morras wrote:
> On Sat, 09 May 2015 06:09:41 -0400
> William Drago  wrote:
>
>> All,
>>
>> Say you encounter a blob in a database. There's no way to
>> tell if that blob carries bytes, floats, doubles, etc, correct?
>>
>> Assuming the above is true, then is it always prudent to
>> store some metadata along with your blobs so that they can
>> be identified in the future?
>>
>> Example table:
>>
>> ModelNo TEXT (e.g. SO-239)
>> SerialNo TEXT (e.g. 101)
>> VSWR BLOB (e.g. x'feab12c...')
>> VSWR_Type TEXT (e.g. double)
>>
>>
>> Does this make sense?
> You can use SQL comments on CREATE TABLE, those comments aren't deleted from 
> SQLITE_MASTER table, you can query it as a normal table.
>
> CREATE TABLE blob_table (
>   ModelNo TEXT,  -- e.g. S0-239
>   SerialNo TEXT, -- e.g. 101
>   VSWR BLOB  -- double, e.g. x'feab12c'
> );
>
> SELECT sql from sqlite_master where type='table' AND tbl_name='blob_table';
>
> will return
>
> CREATE TABLE blob_table (
>   ModelNo TEXT,  -- e.g. S0-239
>   SerialNo TEXT, -- e.g. 101
>   VSWR BLOB  -- double, e.g. x'feab12c'
> )

This is a clever idea and saves the addition of a column 
just for blob type. Is this a reliable feature of SQLite? 
Does anyone see any issues with this as opposed to using a 
dedicated column?

Thanks,
-Bill




[sqlite] VBA Sqllite blob data

2015-05-09 Thread William Drago
You may find what you need here:

https://sqliteforexcel.codeplex.com/

Good luck,
-Bill

On 5/8/2015 3:15 PM, Preston King wrote:
> Does anyone have an example of how to read sqlite blob records, that are not 
> pictures, into Excel? I have been trying to find some VBA code to do this but 
> am not having much luck. Thanks
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> -
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2015.0.5941 / Virus Database: 4342/9728 - Release Date: 05/08/15
>
>



[sqlite] Please confirm what I THINK I know about blobs

2015-05-09 Thread Richard Hipp
On 5/9/15, William Drago  wrote:
> All,
>
> Say you encounter a blob in a database. There's no way to
> tell if that blob carries bytes, floats, doubles, etc, correct?

As far as SQLite is concerned, a BLOB is just bytes.  The
interpretation of those bytes (as floats, doubles, a JPEG thumbnail, a
file compressed with zlib, etc.) is entirely up to the application -
SQLite does not know or care.

>
> Assuming the above is true, then is it always prudent to
> store some metadata along with your blobs so that they can
> be identified in the future?
>

I suppose that depends on the application.  If the field always holds
exactly the same thing (ex: a JPEG) then the metadata is not really
needed.  On the other hand, I have added a separate "mimetype" column
to accompany BLOB fields in cases where the BLOB might be different
things.

-- 
D. Richard Hipp
drh at sqlite.org


[sqlite] Please confirm what I THINK I know about blobs

2015-05-09 Thread William Drago
All,

Say you encounter a blob in a database. There's no way to 
tell if that blob carries bytes, floats, doubles, etc, correct?

Assuming the above is true, then is it always prudent to 
store some metadata along with your blobs so that they can 
be identified in the future?

Example table:

ModelNo TEXT (e.g. SO-239)
SerialNo TEXT (e.g. 101)
VSWR BLOB (e.g. x'feab12c...')
VSWR_Type TEXT (e.g. double)


Does this make sense?

Thanks,
-Bill






[sqlite] Please confirm what I THINK I know about blobs

2015-05-09 Thread Scott Doctor

My design philosophy is that if I have to think about what something is, 
then that thought is a piece of information that should accompany the 
blob.  Consider ten years from now when someone else is looking at the 
database for the first time. Will they know what is in that blob? Column 
names should be descriptive of its contents. If you cannot describe the 
contents in a word, then that row should have at least a simple text 
column so a description can tag the blob.


Scott Doctor
scott at scottdoctor.com

On 5/9/2015 4:18 AM, William Drago wrote:
> On 5/9/2015 6:40 AM, Eduardo Morras wrote:
>> On Sat, 09 May 2015 06:09:41 -0400
>> William Drago  wrote:
>>
>>> All,
>>>
>>> Say you encounter a blob in a database. There's no way to
>>> tell if that blob carries bytes, floats, doubles, etc, correct?
>>>
>>> Assuming the above is true, then is it always prudent to
>>> store some metadata along with your blobs so that they can
>>> be identified in the future?
>>>
>>> Example table:
>>>
>>> ModelNo TEXT (e.g. SO-239)
>>> SerialNo TEXT (e.g. 101)
>>> VSWR BLOB (e.g. x'feab12c...')
>>> VSWR_Type TEXT (e.g. double)
>>>
>>>
>>> Does this make sense?
>> You can use SQL comments on CREATE TABLE, those comments aren't 
>> deleted from SQLITE_MASTER table, you can query it as a normal table.
>>
>> CREATE TABLE blob_table (
>>   ModelNo TEXT,  -- e.g. S0-239
>>   SerialNo TEXT, -- e.g. 101
>>   VSWR BLOB  -- double, e.g. x'feab12c'
>> );
>>
>> SELECT sql from sqlite_master where type='table' AND 
>> tbl_name='blob_table';
>>
>> will return
>>
>> CREATE TABLE blob_table (
>>   ModelNo TEXT,  -- e.g. S0-239
>>   SerialNo TEXT, -- e.g. 101
>>   VSWR BLOB  -- double, e.g. x'feab12c'
>> )
>
> This is a clever idea and saves the addition of a column just for blob 
> type. Is this a reliable feature of SQLite? Does anyone see any issues 
> with this as opposed to using a dedicated column?
>
> Thanks,
> -Bill
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>



[sqlite] Please confirm what I THINK I know about blobs

2015-05-09 Thread Darren Duncan
In addition to this, where a BLOB represents something that could often be a 
file on a disk, methods used to identify the types of those files could often 
be 
used.  For example, with many binary file types the first few bytes of the file 
are signatures for its type, eg with JPEG files for example.  This can't be 
counted on for all BLOBs, but can work for some.  Meta-data is good to have. -- 
Darren Duncan

On 2015-05-09 3:20 AM, Richard Hipp wrote:
> On 5/9/15, William Drago  wrote:
>> All,
>>
>> Say you encounter a blob in a database. There's no way to
>> tell if that blob carries bytes, floats, doubles, etc, correct?
>
> As far as SQLite is concerned, a BLOB is just bytes.  The
> interpretation of those bytes (as floats, doubles, a JPEG thumbnail, a
> file compressed with zlib, etc.) is entirely up to the application -
> SQLite does not know or care.
>
>>
>> Assuming the above is true, then is it always prudent to
>> store some metadata along with your blobs so that they can
>> be identified in the future?
>>
>
> I suppose that depends on the application.  If the field always holds
> exactly the same thing (ex: a JPEG) then the metadata is not really
> needed.  On the other hand, I have added a separate "mimetype" column
> to accompany BLOB fields in cases where the BLOB might be different
> things.
>



[sqlite] Multiple Prepared Statements

2015-05-09 Thread Simon Slavin

On 8 May 2015, at 11:40pm, Scott Doctor  wrote:

> So if I have a loop that finds a row with some data (statement1)
> then based on values from that row sets fields in other rows
> statement2 find a row to set new data
> statement3 set column to something,
> repeat n times.
> then go back and do it all again x times.
> I can prepare the 3 statements first then loop until I am done then finalize 
> the 3 statements.
> That sure saves much overhead from preparing and finalizing 3*n*x times.

Yep.  There's nothing stopping you from doing that.  But if the changes you 
make would modify the result of SELECTs which are still running, you may find 
it difficult to predict how they would interact.

Simon.