[sqlite] Problem building amalgamation

[Jeff Archer]

SQLite 3.13.0

I have just grabbed the sqlite3.c and sqlite3.h from another (working)
project and dropped then into a new project and I am seeing compile errors.

New project is a C++ project in Eclipse.  using GCC 5.4.0 from cygwin64.
Just trying to make a simple command line app.

I'm sure I'm missing something simple but nothing is clicking...


g++ -std=c++11 -fpermissive -P -dD -o HelloCPP.exe HelloCPP.cpp sqlite3.c


Errors...

sqlite3.c:25153:48: error: cannot convert 'SrcList::SrcList_item*' to
'SrcList_item*' in initialization
 struct SrcList_item *pItem = >a[k];
^
sqlite3.c:25156:18: error: invalid use of incomplete type 'struct
SrcList_item'
 if( pItem->zDatabase ){
  ^

sqlite3.c:25157:49: error: invalid use of incomplete type 'struct
SrcList_item'
   sqlite3StrAccumAppendAll(pAccum, pItem->zDatabase);
 ^
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Crash on Android

[Jeff Archer]

Hi All,
Any thoughts on this will be greatly appreciated.

I am having an issue only on a specific tablet when it is running Android
4.2.2.  When Android is upgraded to 4.4.2 problems appears to be gone.  I
still want to understand root cause to know if problem is really gone.

My environment is a little unusual.  I am running SQLite amalgamation
wrapped by JDBC driver.  I was running 3.13.0 when issue was initially
found but have updated to 3.17.0 and no change.

Problem always occurs while doing same operation but unable to predict when
it will occur.  Problem occurs while reading in a specific table and a call
to sqlite3_column_text16() results in the following error being logged:
A/libc(5225): Fatal signal 11 (SIGSEGV) at 0x (code=128), thread
5225 (saltillo.chatpc)
and of course kills the process.

Also, I find that attaching a debugger or writing too many log messages
seems to make the problems disappear also.

Thanks,
Jeff
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to get these ordered correctly

[Jeff Archer]

Hi and thanks in advance for your help.

I have a table of words and counts and I am trying to make a query which
sorts in descending order of count but with each group of words that have
the same count sorted alphabetically.

MY table:
CREATE TABLE words
(id INTEGER PRIMARY KEY AUTOINCREMENT
,cnt INTEGER DEFAULT 1
,rvw INTEGER DEFAULT 0
,txt TEXT UNIQUE);

sqlite> select txt, cnt, rvw from words where txt!="" and rvw!=0 order by
cnt desc;
talk |2|1
be   |2|1
words|2|1
ready|2|1
to   |1|1
need |1|1

The result I am trying to get is:
be   |2|1
ready|2|1
talk |2|1
words|2|1
need |1|1
to   |1|1


T
​hanks,
​
Jeff
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Jeff Archer]

Sorry, that was just mistake in reducing the code for the email, please
ignore.

What do you mean "what matters is order of columns in table"?  or was that
just referring to the typo?


Jeff Archer
jeffarch...@gmail.com <jarch...@yahoo.com>


On Thu, Oct 6, 2016 at 9:52 AM, Clemens Ladisch <clem...@ladisch.de> wrote:

> Jeff Archer wrote:
> > Are there any performance or other considerations of the order of the
> > fields for an insert?
>
> No; what matters is the order of columns in the table.
>
> > INSERT INTO ​mytable(wid1,cnt,​dat,​wid3,wid2) VALUES (?,?,?,?)
> ​> - VS - ​
> > INSERT INTO ​mytable(wid1,wid2,wid3,cnt​,dat​) VALUES (?,?,?,?)
>
> Both statements will result in exactly the same error.  ;-)
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Order of fields for insert

[Jeff Archer]

Just a quick question.  I am actually deciding if I need to do some
performance testing of this but thought I might gain some quick insight.
My specific insert and table are below but really I am looking for a
general answer to the question not just this specific case.

Are there any performance or other considerations of the order of the
fields for an insert?
Are the following equivalent?  regardless of number of values inserting?
 regardless of size of the data being inserted?

INSERT INTO
​mytable(
wid1,cnt,
​dat,​
wid3,wid2) VALUES (?,?,?,?)
​ - VS - ​

INSERT INTO
​mytable(
wid1,wid2,wid3,cnt
​,dat​
) VALUES (?,?,?,?)


CREATE TABLE
​mytable
(
​id
 INTEGER PRIMARY KEY AUTOINCREMENT"
,
​wid1
 INTEGER REFERENCES
​othertable
(
​id
) ON DELETE CASCADE
,
​wid2
 INTEGER REFERENCES
othertable
(
​id
) ON DELETE CASCADE
,
​wid3
 INTEGER REFERENCES
othertable
(
​id
) ON DELETE CASCADE
,
​cnt
 INTEGER DEFAULT
​1
,dat TEXT
)

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

Re: [sqlite] Problem with savepoints

[Jeff Archer]

On Wed, Jul 6, 2016 at 10:46 AM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 07/06/2016 09:09 PM, Jeff Archer wrote:
>
>> Hi All,
>> I am a long time SQLite user but have generally used it from C++ in the
>> past.  In this project however, I am attempting to make a JDBC wrapper and
>> use from Android a newer version of SQLite (3.12.2) than that provided by
>> Android 4.2.2 (3.7.11).
>>
>
> Group 8 however can only do 26 successful inserts and on the 27th
>> sqlite3_step() is returning SQLITE_CANTOPEN (14).
>>
>
>
> ​​
> SQLite is failing to create a temporary file, as there is nowhere
> convenient to do this on Android. The usual fix is to build SQLite to store
> temp files in memory:
>
>   -DSQLITE_TEMP_STORE=3
>
>   https://www.sqlite.org/compile.html#temp_store
>
> Or execute
> ​​
> "PRAGMA temp_store = memory" after opening your db connection.
>

>
​Dan, many thanks.  This does indeed solve my problem.

Just for my information, what is the purpose of this temporary file?  I see
that -journal file is always stored to disk.
​
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with savepoints

[Jeff Archer]

Hi All,
I am a long time SQLite user but have generally used it from C++ in the
past.  In this project however, I am attempting to make a JDBC wrapper and
use from Android a newer version of SQLite (3.12.2) than that provided by
Android 4.2.2 (3.7.11).  Basically, what I have is SQLite compiled with the
NDK into a static library and then a Java wrapper to provide the JDBC
interfaces.  My application is then attempting to use the JDBC interfaces
to operate on the database.  This is generally working.  I can create/open
databases, add/retrieve data, etc. including making use of databases that
are originally created from the native android SQlite (3.7.11)

I have run into a strange issue when attempting to use savepoints.
I create first a transaction with "begin",
then create the outer savepoint "SAVEPOINT SQLITE_SAVEPOINT_1".  This
savepoint will be released after 8 groups have been inserted.
Next, for each of the 8 groups of data a group save point is created
"SAVEPOINT SQLITE_SAVEPOINT_2", data is inserted with sqlite3_step()
returning SQLITE_DONE, then savepoint is released "RELEASE SAVEPOINT
SQLITE_SAVEPOINT_2"
Repeat this successfully for groups 2 through 7.
Group 8 however can only do 26 successful inserts and on the 27th
sqlite3_step() is returning SQLITE_CANTOPEN (14).

This description is of having 128 inserts per group.  The exact number of
inserts seems to be dependent upon the length of the text being inserted
but as long as data does not change the failure point is predictable.

CREATE TABLE variables (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT
NULL, value TEXT NOT NULL)

INSERT INTO variables VALUES (null, '', '')

sqlite version 3.12.2

Thanks,

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

[sqlite] Trying to use in-memory database

[Jeff Archer]

Long time SQLite user but I don't think I have ever tried to do an
in-memory database before.
Just upgraded to 3.8.3.1 but I am not having any other failures with
existing code so I don't think that is any part of the problem.
I am trying to open the database with sqlite3_open16() using a filename of
L":MEMORY:".  I am getting a result code of 14 which is SQLITE_CANTOPEN.
It does open if I use a real filename.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Path Length Limit on Windows

[Jeff Archer]

>Date: Wed, 28 Aug 2013 14:21:24 -0400
>From: Richard Hipp 
>
>The latest check-in on trunk adds a new windows VFS module called
>"win32-longpath" which accepts the full-length 32KB pathnames.  Add the
>string "win32-longpath" as the 4th argument to sqlite3_open_v2() (on
>windows only, of course) and everything should just work after that.

Would it be possible to have a sqlite3_open16_v2() also?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support SQLite in México

[Jeff Archer]

>Francisco Puente Moreno fpm.mty74 at hotmail.com
>Mon Apr 29 11:46:05 EDT 2013
>
>Hello, I'm working for a Mexican company and we are thinking use SQLite
for some develops, but >we need to know if there is any company in México
that give direct support to SQLite if we have a >contingency.
>Thanks in advance!
>Regards!
>Francisco Puente, Eng.

I would recommend that you follow this list for a few weeks while building
a sample app using SQLite and using this list for support.  You will find
the support to be top-notch.  I have paid for support from companies and
gotten far less.  I have been using SQLite3 in my product since 2009.
 Highly recommended.


Jeff Archer
Vice President of Software Engineering
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to achieve fastest possible write performance for a strange and limited case

[Jeff Archer]

Thanks for everyone's help and thoughts on this issue.

My findings on Windows 7 Pro 64 using a PCI based SSD is that for my
smallish image the BLOBs were faster than individual files.
Basically, in line with a table that someone posted earlier in this
thread.

After many experiments, with many variations on grouping writes into
transactions, the single writer proves to be the most limiting factor.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to achieve fastest possible write performance for a strange and limited case

[Jeff Archer]

On Fri, Mar 29, 2013 at 4:46 PM, Richard Hipp  wrote:
> On Fri, Mar 29, 2013 at 4:41 PM, Michael Black  wrote:
>
>> I think many people would tell you not to store your images in your
>> database.
>> Just store a filepath to them.
>> That will speed things up quite a bit and even possibly prevent having to
>> use an SSD.
>>
>> With the filepath your processing apps can use file locking too if you need
>> it.
>>
>
> Lots of people think this.  But experiments suggest otherwise, depending on
> the size of your images.
>
> For BLOBs smaller than about 100KB, it is actually faster to read them out
> of an SQLite database than it is out of a file.  Presumably the extra
> overhead of the open/close is what makes the difference.  For BLOBs larger
> than 100KB it is faster to store them in a file.  That's for reading - I've
> never run the experiment for writing.  Note also that the 100KB is highly
> operating-system and file-system and storage-media dependent and so your
> mileage may vary.
>

FWIW.  My tests are definitely indicating that BLOBs in database are
significantly faster.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[Jeff Archer]

>On Fri, Mar 29, 2013 at 4:05 PM, Simon Slavin  wrote:
>
>No.  The two things have nothing to do with one-another.  Transactions are 
>about grouping changes together
>so that either they all happen or none happen.  Journalling is about surviving 
>through crashes and
>power-failures, and being able to undo changes.  Transactions are about 
>programming.  Journalling is about
>hardware failure.  Either one can be useful without the other.

Thank you.  This helps my understanding.  This would be good
information to have on the documentation page for PRAGMA journal_mode;
 Or, maybe this is common knowledge and I just illustrate my
ignorance.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[Jeff Archer]

>Date: Thu, 28 Mar 2013 14:13:51 +0100
>From: ibrahim 
>
>... You should compare
>
>a) Journal mode=on  w/wo single transaction
>b) Journal mode=off w/wo single transaction

So, this means I can use transaction when I have journal_mode = off?
I did not understand that this was a possibility?   I have re-read the
PRAGMA journal_mode documentation and still it sounds to me like this
cannot be done:  "The OFF journaling mode disables the atomic commit
and rollback capabilities of SQLite."  Maybe I am not reading
throughly...  Maybe all it truely says definitively is "Applications
must avoid using the ROLLBACK command when the journal mode is OFF."

I will rerun my experiment with a single transaction with journal_mode = off.

Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to achieve fastest possible write performance for a strange and limited case

[Jeff Archer]

I have previously made an apparently bad assumption about this so now I
would like to go back to the beginning of the problem and ask the most
basic question first without any preconceived ideas.

This use case is from an image processing application.  I have a large
amount of intermediate data (way exceeds physical memory on my 24GB
machine).  So, I need to store it temporarily on disk until getting to next
phase of processing.  I am planning to use a large SSD dedicated to holding
this temporary data.  I do not need any recoverability in case of hardware,
power or other failure.   Each item to be stored is 9 DWORDs, 4 doubles and
2 variable sized BLOBS which are images.

I could write directly to a file myself.  But I would need to provide some
minimal indexing, some amount of housekeeping to manage variable
sized BLOBS and some minimal synchronization so that multiple instances of
the same application could operate simultaneously on a single set of data.

So, then I though that SQLite could manage these things nicely for me so
that I don't have to write and debug indexing and housekeeping code that
already exists in SQLite.

So, question is:  What is the way to get the fastest possible performance
from SQLite when I am willing to give up all recoverability guarantees?
Or, is it simple that I should just write directly to file myself?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[Jeff Archer]

>From: "James K. Lowden" 
>To: sqlite-users@sqlite.org
>
> Your experiment is telling you different: transaction control costs more
than I/O.
But shouldn't transactions be disabled when journal_mode = off?  Maybe that
is a faulty assumption.  If so, what is the point of journal_mode = off?
For this purpose, I am very happy to give all the ACID promises.

If I understand your point #2, I think you are saying that all of the
inserts within a single transaction are not written to the disk (database
or journal) until the transaction is committed.  But that can't quite be
the answer because if kept my transaction open long enough I would simple
run out of memory and that doesn't seem to happen even when I have 1
million plus inserts.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[Jeff Archer]

On Thu, Mar 28, 2013 at 8:24 AM, ibrahim <ibrahim.a...@googlemail.com> wrote:
> On 28.03.2013 13:09, Jeff Archer wrote:
>>
>> On Wed, Mar 27, 2013 at 6:35 PM, Simon Slavin <slav...@bigfraud.org>
>> wrote:
>>>
>>> Reasonable figures.  With 5764 writes to the disk in separates
>>> transactions you have quite a lot of reading of data plus 5764 attempts to
>>> update the database file.  The updates have to be done in the right order,
>>> and each update has to wait for the disk to be rotated into the right
>>> position, though each update changes only a small amount of data (probably
>>> two sectors).
>>
>> But my most basic question remains.  Why is single transaction faster
>> than PRAGMA journal_mode = off?
>>
>> Seems to me that with no journal there should only be single set of
>> writes to the actual db and that journaling would double the number of
>> writes because data has to be written to journal file as well.
>>
>> 2.5 sec with journal
>> 5.5 sec without journal   <= seems like this sould be the smaller number
>>
> You should read the sections 3 forward.
>
> http://www.sqlite.org/atomiccommit.html
>
> A single transaction happens mostly in memory then is flushed to the OS
> Cache in one step. The Journal file (the amount of pages that will be
> changed) is small while inserting new data into a database and the OS File
> Cache is usually large enough ...
>

Yes, I have read this.  (And now re-read it)

So, since much more work must be done when using journal file, why
does it take longer to do the inserts when there is NO journal file?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[Jeff Archer]

On Thu, Mar 28, 2013 at 8:35 AM, Niall O'Reilly <niall.orei...@ucd.ie> wrote:
>
> On 28 Mar 2013, at 12:09, Jeff Archer wrote:
>
>> But my most basic question remains.  Why is single transaction faster
>> than PRAGMA journal_mode = off?
>>
>> Seems to me that with no journal there should only be single set of
>> writes to the actual db and that journaling would double the number of
>> writes because data has to be written to journal file as well.
>>
>> 2.5 sec with journal
>> 5.5 sec without journal   <= seems like this sould be the smaller number
>
> Your base-line for comparison is the case of multiple transactions
> with journalling.
>
> When you turn off journalling, you save something; when you
> consolidate the activity into a single transaction, you save
> something else.  What you're seeing is that the saving achieved
> with reference to your base-line measurement by using a single
> transaction exceeds that achieved by disabling journalling.
>
> /Niall
>

Agreed.  But the question is: Why?  Without journaling only half as
much writing to disk should occur so why would it take longer?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[Jeff Archer]

On Wed, Mar 27, 2013 at 6:35 PM, Simon Slavin  wrote:
>
> Reasonable figures.  With 5764 writes to the disk in separates transactions 
> you have quite a lot of reading of data plus 5764 attempts to update the 
> database file.  The updates have to be done in the right order, and each 
> update has to wait for the disk to be rotated into the right position, though 
> each update changes only a small amount of data (probably two sectors).

But my most basic question remains.  Why is single transaction faster
than PRAGMA journal_mode = off?

Seems to me that with no journal there should only be single set of
writes to the actual db and that journaling would double the number of
writes because data has to be written to journal file as well.

2.5 sec with journal
5.5 sec without journal   <= seems like this sould be the smaller number
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance with journal_mode = off

[Jeff Archer]

On Wed, Mar 27, 2013 at 5:46 PM, David King  wrote:
>
> > I am populating a database with 5764 records using the exact same data set
> > each time into a newly created file.
> > When I use no explicit transactions (default atomic commit) it takes 17.7
> > seconds.
> > When I set journal_mode = off, same operation takes 5.5 seconds.
> > If I do all 5764 inserts within a single transaction only 2.5 seconds.
>
>
> That sounds about right, yeah. With journalling, most disk writes have to be 
> done twice (once to the journal and once to the data file).

Which is why I expected journal_mode = off to make it faster.  But it
is 3 seconds faster when I leave journaling enabled and do all writes
within a single transaction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance with journal_mode = off

[Jeff Archer]

Could someone please confirm if this makes sense.  It is not what I
expected.  I have repeated several times so I believe these are the correct
numbers.
I am populating a database with 5764 records using the exact same data set
each time into a newly created file.
When I use no explicit transactions (default atomic commit) it takes 17.7
seconds.
When I set journal_mode = off, same operation takes 5.5 seconds.
If I do all 5764 inserts within a single transaction only 2.5 seconds.


Jeff Archer
Vice President of Software Engineering
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about behavior when journal_mode = off

[Jeff Archer]

If all connections (1 per thread) of all processes (multiple simultaneous)
issue command "PRAGMA journal_mode = off", is access to a common database
file located on the local machine still synchronized correctly between the
random accesses (reads and writes) occurring across all?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Journal mode

[Jeff Archer]

Documentation doesn't make this clear.  Does PRAGMA journal_mode = OFF;
affect a single connection or will it affect all connections in my process?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite4 questions

[Jeff Archer]

Will SQLite4 be capable of reading a SQLite3 database to facilitate
upgrade?  (I'm guessing 'no')

I assume SQLite4 will be distributed in a familiar amalgamation format.
Will it be feasible to include the amalgamation of both SQLite3 and SQLite4
into the same application to create an upgrade facility?

Is there any ETA on  a complete and stable release of SQLite4?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ranking in a view

[Jeff Archer]

There was recently a post where someone was trying to update the ranking of
records in a table and someone else presented a clever view which did the
ranking and thus required no table update.  I wanted to try and understand
how this view worked but when I have looked back to find it, I can't.
Could someone please repost that view or help me find it in some way?

Thanks in advance,

Jeff
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] The database disk image is malformed

[Jeff Archer]

I have encountered an error of the database disk image is malformed.
Database was send in by a customer so I'm not exactly sure how it came to
be this.  I have found that this can be repaired by SQLiteExpertPro by the
Database | Repair functionality but have not been able yet to find anything
in the C API that might be used to implement this functionality.

Could someone please point me to some information on how I might make my
product be able to recover from this situation.

SQLite3 version 3.7.13
Windows 7 x64

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to get the amalgation as individual files

[Jeff Archer]

On Fri, Sep 21, 2012 at 4:01 PM, Nico Williams wrote:

> >Install Cygwin, use that to make the amalgamation (after you've made
> >whatever changes to the canonical sources), then build the amalgation
> >in native Windows.


Please excuse my ignorance.
OK.  I have Cygwin.  Fossil.  I have cloned the repository and opened.   I
believe I now have the source tree of SQLite.
Not sure what I need to do next.
I have the Cygwin command prompt open and believe it is running the bash
shell.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to get the amalgation as individual files

[Jeff Archer]

Igor Tandetnik Fri Sep 21 15:26:16 EDT 2012
> ..."Canonical Source Code" section

I knew about this but unfortunately I am Windows only.  So the "Note that a
unix-like development environment, including a recent version of
Tcl<http://www.tcl.tk/>,
is required in order to build from the canonical sources." trips me up.  I
was hoping there is a way to get the individual files after they have been
formulated by the unix-like development tools but before they are combined
into the amalgamation file.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it possible to get the amalgation as individual files

[Jeff Archer]

I assume that the amalgation exists as single files before it is combined
into one file.
Is is possible to get these individual files so that I can build a debug
version because Visual Studio debugger does not handle well the 138244 line
amalgation file?

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_CONFIG_LOG callback indicating SQLITE_SCHEMA (17) error

[Jeff Archer]

I am still chasing this error message that I get through SQLITE_CONFIG_LOG
callback during the sqlite3_step():
errcode: SQLITE_SCHEMA  (17)
message: statement aborts at 80: [INSERT INTO [Scans](ScanID, Timestamp,
EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, @Result);] database
schema has changed
I have been told this is merely informational and that SQLite has
re-prepared the statement however I am still trying to understand the cause
out of fear that something unexplained will bite me later.

I have a theory that the following statement executing in another process
may be triggering SQLite to think the schema has changed.

CREATE TABLE IF NOT EXISTS [_dbProperties]
(PropIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,PropName  VARCHAR NOT NULL UNIQUE
,PropValue VARCHAR NOT NULL
);
I didn't consider this initially because the _dbProperties table has to
exist as it was created by the first process and I was assuming that the
"IF NOT EXISTS" would be preventing it from actually doing anything but now
I am believing that executing this statement even when the table exists is
causing SQLite to think the schema has changed.

Does this seem plausible?

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error indicated through SQLITE_CONFIG_LOG callback but sqlite3_step() does not fail

[Jeff Archer]

>Simon Slavin slavins at bigfraud.org Fri Jun 29 17:16:36 EDT 2012
>
>Do you do the _prepare() first, then make a change to the database schema
?  For instance
>
>Start of app
>Prepare the INSERT statement
>CREATE TABLE
>Bind the INSERT statement
>Step the INSERT statement

No.
Create Table

Then in a separate function
Prepare
Bind
Step
Finalize

Callback happens during call to sqlite3_step()
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error indicated through SQLITE_CONFIG_LOG callback but sqlite3_step() does not fail

[Jeff Archer]

>Pavel Ivanov paivanof at gmail.com Fri Jun 29 17:06:42 EDT 2012
>
>Because SQLite successfully re-prepared this statement behind the
>scenes and thus was able to successfully finish sqlite3_step()
>function.

What could cause it to "re-prepare" the statement?
Is this something I need to find and fix?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error indicated through SQLITE_CONFIG_LOG callback but sqlite3_step() does not fail

[Jeff Archer]

>Kevin Benson kevin.m.benson at gmail.com Fri Jun 29 16:32:55 EDT 2012
>
>The mention of SQLITE_SCHEMA error and sqlite3VdbeExec() sounds like maybe
>you're fighting an expired statement???

Not likely.  I do Prepare, Bind, Step, Finalize using a wrapper function.
The database file was created only moments before.

My biggest question is: Why after this error being indicate through the
SQLITE_CONFIG_LOG callback would the sqlite3_step() succeeded?

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Not sure how to interrupt this

[Jeff Archer]

>Igor Tandetnik itandetnik at mvps.org Thu Jun 28 09:38:27 EDT 2012
>My guess is that a) you have prepared your statement with
sqlite3_prepare_v2 (as opposed to sqlite3_prepare)...
Statement was prepared with sqlite3_prepare16_v2()
Database file was created only moments earlier.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Not sure how to interrupt this

[Jeff Archer]

>Marc L. Allen mlallen at outsitenetworks.com Thu Jun 28 09:31:40 EDT 2012
>I think he wants to know why he is receiving what appears to be an error
notification via the callback.

>Adam DeVita adevita at verifeye.com Thu Jun 28 09:04:44 EDT 2012
>What is the problem?

Yes, the error message through the SQLITE_CONFIG_LOG callback is
inconsistent with the success indicated by SQLITE_DONE.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Not sure how to interrupt this

[Jeff Archer]

>Igor Tandetnik itandetnik at mvps.org Thu Jun 28 09:57:06 EDT 2012
>Did you prepare the INSERT statement in between running CREATE TABLE and
CREATE INDEX,
>by any chance? Or perhaps CREATE TABLE for a different table?

No, the CREATE INDEXs were done right after the CREATE TABLE.
I have a wrapper function, Execute(), that does the Prepare, Bind, Step,
Reset, Finalize sequence.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Not sure how to interrupt this

[Jeff Archer]

>Igor Tandetnik itandetnik at mvps.org Thu Jun 28 09:38:27 EDT 2012
>My guess is that a) you have prepared your statement with
sqlite3_prepare_v2 (as opposed to sqlite3_prepare)...


Statement was prepared with sqlite3_prepare16_v2()

Database file was created only moments earlier.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Not sure how to interrupt this

[Jeff Archer]

I am getting back SQLITE_DONE (101) from sqlite3_step() and the statement
is clearly being executed.

SQL: "INSERT INTO [Scans](ScanID, Timestamp, EndTime, Result) VALUES(NULL,
@Timestamp, @Timestamp, @Result);"

The table has been created as:
CREATE TABLE [Scans]
(ScanID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,Timestamp   DATETIME NOT NULL UNIQUE
,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME
,Result  VARCHAR
);
CREATE INDEX Scans_vwScan_Index on Scans(ScanID, Timestamp, EndTime,
Result);
CREATE INDEX Scans_Timestamp_Index on Scans(Timestamp);

I have SQLITE_CONFIG_LOG callback installed:
sqlite3_config(SQLITE_CONFIG_LOG, cb_sqlite_config_log, /*pUserData*/NULL);

I get the following message through the SQLITE_CONFIG_LOG callback during
the sqlite3_step():
errcode: SQLITE_SCHEMA  (17)
message: statement aborts at 80: [INSERT INTO [Scans](ScanID, Timestamp,
EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, @Result);] database
schema has changed

SQLITE_VERSION"3.7.13"  - amalgamation

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Something strange

[Jeff Archer]

I have seen something that seems strange and wonder if anyone has any
thoughts?

I have been using SQLite for a long time now and never seen anything
similar.  Had been using 3.7.6.1 and have now upgraded to 3.7.13 but
behavior has not changed.

In a Debug build this code works as expected but in Release build I am
having only 2 of the 7 rows be inserted.  I have found a workaround in that
if I Sleep() for 100ms after each INSERT the Release build then works as
expected.

Unfortunately a lot of code involved so it will take a while to pare down
to small test case and since I have a workaround I will hope for a general
answer first.

Inside a BEGIN IMMEDIATE, I do a SELECT from table Objectives and get back
7 rows which are held in a list.  Iterate that list and for each do a
SAVEPOINT then INSERT a row into table Calibration then do RELEASE.  When
finished iterating the list do COMMIT.

Windows 7 64bit.  Visual Studio 2010.  Real HDD not SSD.  The file is on
local machine but is opened using a network path.  i.e.
\\MACHINENAME\Data\Calibration.db
Thank you in advance for your time on this matter.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler Error

[jeff archer]

>I believe any compilation options that require changes in SQL parser
>require compiling from original sources as well. They cannot be used
>with amalgamation file which has already generated SQL parser's code.
>
>Pavel
 
OK.  But this call in the parser source...
 
sqlite3FinishTrigger
 (pParse, yymsp[-1].minor.yy203, );
Should be enclosed in...
 
#ifdef SQLITE_OMIT_TRIGGER
sqlite3FinishTrigger(pParse, yymsp[-1].minor.yy203, );
#endif

 Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compile Error

[jeff archer]

I am attempting to compile SQLite on windows with SQLITE_OMIT_TRIGGER defined 
and I get the following errors.

Sure enought the code in yy_...() is not exclosed in ifndef SQLITE_OMIT_TRIGGER

1> Creating library ..\..\Bin\x64\Debug\SqlUtils.lib and object 
..\..\Bin\x64\Debug\SqlUtils.exp
1>sqlite3.obj : error LNK2019: unresolved external symbol 
sqlite3DeleteTriggerStep referenced in function yy_destructor
1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3DropTrigger 
referenced in function yy_reduce
1>sqlite3.obj : error LNK2019: unresolved external symbol 
sqlite3TriggerSelectStep referenced in function yy_reduce
1>sqlite3.obj : error LNK2019: unresolved external symbol 
sqlite3TriggerDeleteStep referenced in function yy_reduce
1>sqlite3.obj : error LNK2019: unresolved external symbol 
sqlite3TriggerInsertStep referenced in function yy_reduce
1>sqlite3.obj : error LNK2019: unresolved external symbol 
sqlite3TriggerUpdateStep referenced in function yy_reduce
1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3BeginTrigger 
referenced in function yy_reduce
1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3FinishTrigger 
referenced in function yy_reduce
1>..\..\Bin\x64\Debug\SqlUtils.dll : fatal error LNK1120: 8 unresolved externals
 Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite3 memory leaks in my c++ dll application

[jeff archer]

>From: Khanh Nguyen <kn95...@gmail.com>  
>Date: Wed, 20 Apr 2011 11:00:57 -0500
>
>SQLite...memory leaks...

The VS debugger will stop at the specific allocation number if you add this 
line 
early on in your program execution.
_crtBreakAlloc = ;
Where  is the allocation number reported in the Detected Memory Leaks!

I use SQLite from VS 2010 C++ 64 bit very heavily in my product.  I am not 
aware 
of any memory leaks which can be attibuted to SQLite.

Also, you should be aware that under the right circumstances it is possible to 
trick the debug memory allocator into reporting false positives in the leaked 
memory. Your said that your are using GetProcAddress() to make calls which 
makes 
me think you *may* be getting to these false positives.  Basically, I believe 
it 
comes down to allocating memory in one dll and deallocating in another.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What happens if you insert more than your RAM

[jeff archer]

>Tue Apr 19 18:35:27 GMT 2011 Danny dragonslayer2k at yahoo.com 
>
>Depends on access type.  If accessing sequentially, paging would be minimal, 
>that is, you would process the "segment" that fits into memory, then page 
>in another "segment" and process that, etc., etc.
Even so it would now be accessed at disk speeds and thus very slow by 
comparison.
And OBTW, the db won't likely do sequential access.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What happens if you insert more than your RAM

[jeff archer]

Wouldn't it page to disk, thrash and be very slow first?  

>On Mon, 18 Apr 2011 09:46:44 -0400, Pavel Ivanov <paiva...@gmail.com> wrote:
>You won't be able to insert. The statement will fail.
>
>On Mon, Apr 18, 2011 at 9:44 AM, Adam DeVita <adev...@verifeye.com> wrote:
>> Good day,
>>
>> What happens if you insert more than your RAM size into an in memory
>> database?
>> (I'm particularly interested in the Windows context).
>>
Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Building sqlite for windows in a proper way

[jeff archer]

>On Mon, Apr 18, 2011 at 8:42 AM, Kuba Nowak  wrote:
>
>Does anyone one know how to build sqlite to get the same binary as on
>download page ?
>

What do you use to compare the speed between the builds of SQLite?

Are using debug build from VS2010?  My experience is that VS debug builds run 
most code significantly slower than the release builds.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with error codes

[Jeff Archer]

> drh on Tue Apr 12 22:23:38

> 

>Set your breakpoint on the function winAccess if you want to try to debug

>this.

 

Which I cannot do since attaching debugger masks the problem.

 

Now I am really confused.  I have modified winAccess to simply return the
result of GetFileAttributesExW() if the error is not ERROR_FILE_NOT_FOUND
and this appears to eliminate the crash.

 

Could someone please advise on how good or bad of an idea this change is?

 

How well is SQLite tested on Windows 7 64 bit?

 

 

Jeff Archer

Nanotronics Imaging

jsarc...@nanotronicsimaging.com

<330>819-4615

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with error codes

[jeff archer]

>drh   Tue Apr 12 22:23:38 GMT 2011
>
>Set your breakpoint on the function winAccess if you want to try to debug
>this.

If only it were that easy.  Its intermittent and attaching a debugger seems to 
mask the problem.  It's never been seen with only a single process and occurs 
more when there are more simultaneous processes.  To process 26 images with 8 
processes we are launching 8 processes for the first 8 images, then when each 
process terminates a new process is launghed on the next image until all are 
processed.

I am using a BEGIN IMMEDIATE transaction at the beginning of each process to 
check and maybe update the database.  This is to have write lock already if 
update is required and to serialize access to the database for making the check 
and potentially updating some config data.

I'm not really thinking of a good way to approach this.  I would greatly 
appreciate if anyone has any ideas.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help with error codes

[jeff archer]

I have a process which analyzes an image then writes results to database.  
(sqlite 3.7.4)
When I run multiple of these in parallel, each on its own image but all on the 
same database, and all on same machine (Windows 7) I get an error from 
sqlite3_step() when attempting to execute "BEGIN IMMEDIATE".  I get 
proportionaly more errors when I run more processes in parallel.  A high 
percentage of the error occurs just after successful open when trying to check 
for some existing values to confirm this is correct file.

The error looks like it is always from sqlite3_step() on the "BEGIN IMMEDIATE"
I am getting a return from sqlite3_step() of 3338.
sqlite3_errmsg16() is returning "disk I/O error"
sqlite3_prepare16_v2 returned success.

And now that I am digging into this I am not sure I have this correct.
Am I supposed to get my error code from sqlite3_step() because I have now also 
noticed sqlite3_extended_errcode()?
Is 3338 an extended error code?Do I need to use sqlite3_open_v2 to get the 
extended error code?

 Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[jeff archer]

>From: Patrick Earl 
>Subject: Re: [sqlite] Lack of "decimal" support
>
>If SQLite can't decide on a base-10 format itself, perhaps the answer
>lies in enhancing the API to allow for custom type storage and
>operators.
>
 
So, like a virtual type interface.  This would be a nice feature.  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to delete all indexes

[Jeff Archer]

On Wed, Mar 16, 2011 at 5:59 PM, Simon Slavin  wrote:
> You should almost never be creating indexes on the fly.  Bear in mind that if 
> SQLite finds a search that would be better with an index, it creates it 
> itself and it is far better at working out the best index than you are.  The 
> only disadvantage is that it will recreate the index each time you do that 
> SELECT.
>

Yes.  The code was only supposed to create 1 specific index but bug caused more.

Is there any way to know when SQLite has created a temporary index?

It would be nice to have an option to allow SQLite to save these
temporary indexes when SQLite is being used in a desktop environment.
Probably by #define at compile time.  Doesn't seem like this would add
much test burden since the creation of the temporary indexex must be
tested now, likewise saving index is clearly tested now.  So I see
only new test required for the actual save that would be done in this
new execution path.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to delete all indexes

[Jeff Archer]

Hello all and thank you in advance for your help.

 

I have just realized that a buggy code has been secretly creating indexes.
Thus I have need to make a generic function that can delete all indexes in a
database.

 

I have found that I can use this select to get the names of all existing
indexes:

select name from sqlite_master where type = 'index' and sql is not null;

 

Then I can iterate the result and drop each index.

 

This leaves me with 2 questions.

1.   How stable are the column names and table contexts of the
sqlite_master table?

2.   Is there a better way to accomplish this without resorting to
explicit query of the sqlite_master table?

 

Jeff Archer

Nanotronics Imaging

jsarc...@nanotronicsimaging.com

<330>819-4615

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query help

[Jeff Archer]

>From: Igor Tandetnik [mailto:itandet...@mvps.org] 
>Sent: Tuesday, March 01, 2011 5:47 PM
>
>On 3/1/2011 5:01 PM, Jeff Archer wrote:
>> And this select which I would like to modify to only return lowest X,Y 
>> value for each ImageID.
>
>What does "lowest" mean? If you have two points (100, 200) and (200, 100), 
>which one is "lower"?
>--
>Igor Tandetnik
>
Sorry, I guess I wasn't very clear.  
For this purpose either would be fine.  The values should tend to be (1.5, 1.5).
I think it will just happen to work out if I could get the first row for each 
ImageID since the values should have been entered in ascending order.  I 
realize this will probably not be guaranteed to get lowest X,Y but for my 
purpose at the moment this is OK.

Thanks,
Jeff


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query help

[Jeff Archer]

Hi all, and thanks in advance for you help.

 

I have the following schema

 

CREATE TABLE [Scans] 

(ScanIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

,Timestamp DATETIME NOT NULL UNIQUE

,EndTime   DATETIME NOT NULL DEFAULT CURRENT_TIME

,ResultVARCHAR

);

 

CREATE TABLE [Images] 

(ImageID   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

,ScanIDINTEGER NOT NULL REFERENCES [Scans] DEFERRABLE INITIALLY
DEFERRED

,Filename  VARCHAR NOT NULL

,NoINTEGER NOT NULL

,X REAL NOT NULL DEFAULT 0.0

,Y REAL NOT NULL DEFAULT 0.0

,Z REAL NOT NULL DEFAULT 0.0

,R INTEGER NOT NULL DEFAULT 0

,C INTEGER NOT NULL DEFAULT 0

,wMicrons  REAL NOT NULL DEFAULT 0.0

,hMicrons  REAL NOT NULL DEFAULT 0.0

,wPixels   INTEGER NOT NULL DEFAULT 0

,hPixels   INTEGER NOT NULL DEFAULT 0

,UNIQUE (ScanID, Filename, No));

 

CREATE INDEX Images_ScanID_Index on Images(ScanID);

 

CREATE TABLE [Analyzers] 

(AnalyzerIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

,Name  VARCHAR NOT NULL UNIQUE

);

 

CREATE TABLE [Analysis] 

(AnalysisIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

,AnalyzerIDINTEGER NOT NULL REFERENCES [Analyzers] DEFERRABLE INITIALLY
DEFERRED

,ScanIDINTEGER NOT NULL REFERENCES [Scans] DEFERRABLE INITIALLY
DEFERRED

,Timestamp DATETIME NOT NULL

,EndTime   DATETIME NOT NULL DEFAULT CURRENT_TIME

,NumDefectsINTEGER DEFAULT 0

,ResultVARCHAR

);

 

CREATE INDEX Analysis_AnalyzerID_Index on Analysis(AnalyzerID);

 

CREATE INDEX Analysis_ScanID_Index on Analysis(ScanID);

 

CREATE TABLE [Defects] 

(DefectID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

,ImageID   INTEGER NOT NULL REFERENCES [Images] DEFERRABLE INITIALLY
DEFERRED

,AnalysisIDINTEGER NOT NULL REFERENCES [Analysis] DEFERRABLE INITIALLY
DEFERRED

,X REAL NOT NULL DEFAULT 0.0

,Y REAL NOT NULL DEFAULT 0.0

,W REAL NOT NULL DEFAULT 0.0

,H REAL NOT NULL DEFAULT 0.0

,Area  REAL NOT NULL DEFAULT 0.0

);

 

And this select which I would like to modify to only return lowest X,Y value
for each ImageID.

 

select Defects.DefectID

, Defects.ImageID

, Defects.AnalysisID

, Defects.X

, Defects.Y

, Defects.W

, Defects.H

, Defects.Area

, Images.X + Defects.X as DefectX

, Images.Y + Defects.Y as DefectY

from Defects join Images on Defects.ImageID = Images.ImageID

 

I believe that the data is all stored such that the first stored defect for
each ImageID will have the lowest X,Y values.  At least for now this
assumption is probably good enough.  

 

 

Jeff Archer

Nanotronics Imaging

jsarc...@nanotronicsimaging.com

<330>819-4615

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with join

[Jeff Archer]

>Date: Fri, 18 Feb 2011 07:34:06 -0800
>From: Jim Morris <jmor...@bearriver.com>
>
>A correlated sub-query might work for you.
>
>SELECT
>[Analyzers].[AnalyzerID]
>, [Analyzers].[Name] AS [Analyzer]
>, [Analysis].[AnalysisID]
>, [Analysis].[ScanID]
>, [Analysis].[Timestamp]
>, [Analysis].[EndTime]
>, (SELECT COUNT(*) AS NumDefects FROM Defects d where d.AnalysisID =
Analysis.AnalysisID) as NumDefects,
>, [Analysis].[Result]
>FROM  [Analysis]
>JOIN [Analyzers] ON [Analyzers].[AnalyzerID] = [Analysis].[AnalyzerID]
>ORDER BY [Analysis].[Timestamp];

Thank you, Jim.  This is very handy technique for me know as I am still a
SQL beginner.


--
Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler warnings in R-Tree code under Visual Studio Express

[Jeff Archer]

>Date: Fri, 18 Feb 2011 06:13:37 -0800 (PST)
>From: Marian Cascaval <mariancasca...@yahoo.com>
>
>I'm suspecting there's something wrong with Studio Express C++.
>I use both 2008 and 2010 and I notice on 2008 that the IDE loses track of
code
>in case of large source files. Sometimes comments that should have a
certain
>colour have a different one and so on.
>
>For example, the first warning at line 120736 there is a variable 'area'
which
>is of type 'float' defined just 3 lines above. Yet in the line at 120736:
>
>area = area * (DCOORD(p->aCoord[ii+1]) - DCOORD(p->aCoord[ii]));
>
>the IntelliSense shows that the second instance of 'area' is of type 'int'.
>
>To prove this is a problem specific only to Microsoft's IDEs, could you
give the
>simplest code example (with SQLITE_ENABLE_RTREE=1) to compile it and see if
we
>get different results with the warnings?
>
>Marian Cascaval
All version of Visual Studio up to and including 2010 have issues with
source files longer than 64K lines.  The editing works OK but the debugger
cannot locate code lines and it confuses Intellisense.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler warnings in R-Tree code under Visual

[Jeff Archer]

>Date: Fri, 18 Feb 2011 13:12:09 +
>From: "Black, Michael (IS)" <michael.bla...@ngc.com>
>
>I'm of the opinion that all such warnings should be permanently fixed.
Such warnings do point to potential problems.
>And not by disabling the warning but by fixing the code (explicit casts for
example).
>
>How many people try this and get worried about possible problems?  If you
simply fix the code once all those people will feel a whole lot better when
they crank >up the warnings and see nothing.
>
>I went through this exercise myself for SQLite at one point and looked at
each and every warning to try and determine if there was a possible error
caused by all >the miscasts.
>
>I don't understand the resistance to fixing these.  And I'd feel a whole
lot better with somebody who KNOWS the code saying "yeah, explicit cast is
OK here".
I really agree with this.  I compile all production code wth highest warning
level possible and make it always compile warning free.  Why would you NOT
want the compiler to point out potential issues.  This lets the developer
basically make an approval by inserting a cast or whatever.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help with join

[jeff archer]

Here is my existing schema.  I would like to change this and remove the 
"NumDefects" from the [Analysis] table.
To do this I need to update the [vwAnalysis] view to determine the number of 
defects from the [Defects] table by the AnalysisID.
I can't seem to get the right select for the new version of the view.
 
CREATE TABLE [Analyzers] 
(AnalyzerID    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,Name  VARCHAR NOT NULL UNIQUE
);
CREATE TABLE [Analysis] 
(AnalysisID    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,AnalyzerID    INTEGER NOT NULL REFERENCES [Analyzers] DEFERRABLE INITIALLY 
DEFERRED
,ScanID    INTEGER NOT NULL REFERENCES [Scans] DEFERRABLE INITIALLY DEFERRED
,Timestamp DATETIME NOT NULL
,EndTime   DATETIME NOT NULL DEFAULT CURRENT_TIME
,NumDefects    INTEGER DEFAULT 0
,Result    VARCHAR
);
CREATE TABLE [Defects] 
(DefectID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,ImageID   INTEGER NOT NULL REFERENCES [Images] DEFERRABLE INITIALLY 
DEFERRED
,AnalysisID    INTEGER NOT NULL REFERENCES [Analysis] DEFERRABLE INITIALLY 
DEFERRED
,X REAL NOT NULL DEFAULT 0.0
,Y REAL NOT NULL DEFAULT 0.0
,W REAL NOT NULL DEFAULT 0.0
,H REAL NOT NULL DEFAULT 0.0
);
CREATE VIEW [vwAnalysis] AS 
SELECT [Analyzers].[AnalyzerID] 
 , [Analyzers].[Name] AS [Analyzer] 
 , [Analysis].[AnalysisID] 
 , [Analysis].[ScanID] 
 , [Analysis].[Timestamp] 
 , [Analysis].[EndTime] 
 , [Analysis].[NumDefects] 
 , [Analysis].[Result] 
FROM   [Analysis] 
JOIN [Analyzers] ON [Analyzers].[AnalyzerID] = [Analysis].[AnalyzerID] 
ORDER BY [Analysis].[Timestamp];

 
This is what I would like to do but I do not understand how to make the JOIN 
correctly.
 
SELECT [Analyzers].[AnalyzerID] 
 , [Analyzers].[Name] AS [Analyzer] 
 , [Analysis].[AnalysisID] 
 , [Analysis].[ScanID] 
 , [Analysis].[Timestamp] 
 , [Analysis].[EndTime]
 , COUNT(DefectID) AS NumDefects                                        
<<== 
count of Defects that match the AnalysisID
 , [Analysis].[Result] 
FROM   [Analysis] 
JOIN [Analyzers] ON [Analyzers].[AnalyzerID] = [Analysis].[AnalyzerID]
ORDER BY [Analysis].[Timestamp];
 

Thanks in advance for your help with this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about having 2 db connections on same

[Jeff Archer]

>Date: Sun, 13 Feb 2011 22:14:29 -0500
>From: Pavel Ivanov 
>
>> I do a transaction on connection 1 using BEGIN IMMEDIATE, some rows updated,
>> COMMIT.
>> Then, when I attempt same sequence on connection 2, when I do first call to 
>>step
>> to execute begin transaction and it never returns.
>
>Do you check result code from COMMIT and is it successful? My guess is
>you have some SELECT statements which are not reset or finalized
>before COMMIT. And your COMMIT in such situation should return
>SQLITE_BUSY. If that's true then second BEGIN IMMEDIATE just can't
>finish because another writing transaction is not finished yet.
>
>Pavel

Thanks, Pavel.  My wrapper class for SQLite prevents a COMMIT with an 
outstanding statement.  You are however correct, it was a prior failed COMMIT.  
It just happen on the one place in my code where I do not check return from 
COMMIT.  It was a "constraint failure".

Thanks also to others who have offered suggestion.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about having 2 db connections on same thread

[jeff archer]

I thought this would be OK but now that I am seeing it fail I am having my 
doubts.  


I have created 2 db connections on the same thread, both on the same file.  
i.e. 
2 calls to sqlite3_open16() (use exact same filename both times) have created 2 
sqlite3 instances.  I do this because I have 2 classes which each have their 
own 
set of tables in this single db file.  I thought it would be OK because I do 
not 
intemix operations on the 2 connections.

I do a transaction on connection 1 using BEGIN IMMEDIATE, some rows updated, 
COMMIT.
Then, when I attempt same sequence on connection 2, when I do first call to 
step 
to execute begin transaction and it never returns.

 Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 command line utility

[jeff archer]

From: BareFeetWare list@barefeetware.com
Sent: Thu, January 27, 2011 8:50:15 AM
>
>INSERT INTO "Microscopes" VALUES(null, 'nSpec', 'default');
>
>create temp table "Store" (MicroscopeID);
>
>insert into "Store" select last_insert_rowid();
>
>INSERT INTO "Objectives"(ObjectiveID, MicroscopeID, TurretPos, Name, 
>Description, Magnification, NA, WD) VALUES( 1, (select MicroscopeID from 
>Store), 
>0, '5X'  , 'CFI LU 
>
>Plan Fluor BD 5X'  ,  5.0, 0.15, 18.0);
>
>INSERT INTO "Objectives"(ObjectiveID, MicroscopeID, TurretPos, Name, 
>Description, Magnification, NA, WD) VALUES( 2, (select MicroscopeID from 
>Store), 
>1, '10X' , 'CFI LU 
>
>Plan Fluor BD 10X' ,  10.0, 0.30, 15.0);

Thank you.  This is very convenient.  And seems so obvious now that I have seen 
it.  Not sure why I didn't figure this out on my own.
 Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3 command line utility

[Jeff Archer]

Is it possible to use parameter binding from the sqlite3 command line utility?
I would like to make a small illustration of my problem but it only seems to 
have an issue with I am doing an insert and using a bound parameter for a 
column 
which is a foreign key.
I have foreign key on by: PRAGMA foreign_keys = ON;

BEGIN IMMEDIATE;

CREATE TABLE IF NOT EXISTS [Microscopes] 
(MicroscopeID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,Name VARCHAR NOT NULL
,Description  VARCHAR
);

CREATE TABLE IF NOT EXISTS [Objectives] 
(ObjectiveID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,MicroscopeID INTEGER NOT NULL REFERENCES [Microscopes] DEFERRABLE 
INITIALLY 
DEFERRED
,TurretPos    INTEGER
,Name VARCHAR NOT NULL
,Description  VARCHAR NOT NULL
,Magnification    REAL NOT NULL
,NA   REAL NOT NULL
,WD   REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS [Calibrations] 
(CalibrationID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,ObjectiveID   INTEGER NOT NULL REFERENCES [Objectives] DEFERRABLE INITIALLY 
DEFERRED
,MicroscopeID  INTEGER NOT NULL REFERENCES [Microscopes] DEFERRABLE INITIALLY 
DEFERRED
,Timestamp DATETIME NOT NULL UNIQUE
,Description   VARCHAR
);

INSERT INTO [Microscopes] VALUES(@MicroscopeID, 'nSpec', 'default');

INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, 
Description, Magnification, NA, WD) VALUES( 1, @MicroscopeID, 0, '5X'  , 'CFI 
LU 
Plan Fluor BD 5X'  ,   5.0, 0.15, 18.0);
INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, 
Description, Magnification, NA, WD) VALUES( 2, @MicroscopeID, 1, '10X' , 'CFI 
LU 
Plan Fluor BD 10X' ,  10.0, 0.30, 15.0);
INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, 
Description, Magnification, NA, WD) VALUES( 3, @MicroscopeID, 2, '20X' , 'CFI 
LU 
Plan Fluor BD 20X' ,  20.0, 0.45,  4.5);
INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, 
Description, Magnification, NA, WD) VALUES( 4, @MicroscopeID, 3, '50X' , 'CFI 
LU 
Plan Fluor BD 50X' ,  50.0, 0.80,  1.0);
INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, 
Description, Magnification, NA, WD) VALUES( 5, @MicroscopeID, 4, '100X', 'CFI 
LU 
Plan Fluor BD 100X', 100.0, 0.90,  1.0);

COMMIT;

The issue is with the @MicroscopeID in the 5 inserts into the Objectives 
table.  
If I remove the foreign key constraint on MicroscopeID in the Objectives 
table I 
get MicroscopeID of
1
NULL
NULL
NULL
NULL

My version is 3.7.3.

I have SQLite Expert and have tried same SQL and it appears to work correctly 
after prompting me for a value of @MicroscopeID 6 times.

In my code I have tried making 1 bind to @MicroscopeID and I have tried it with 
appending the digits 1 to 6 and making 6 calls to bind with same result.

My app is 64 bit and SQLite Expert is 32 bit.

Any thought or suggestion will be appreciated as I am not sure how to proceed.  


Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[jeff archer]

From: Simon Slavin  Date: Tue, 21 Dec 2010 14:19:25 +
>This was my first thought when I considered implementing an SQLite-over-IP 
>protocol: that there was no user model and therefore no need for passwords.  
>Mounting a >database on the server would mean that anyone who knew your IP 
>address, port number and database name could access the data.
>
>So my conclusion was that you would need to implement, in the first case, a 
>simple user privilege model, nominating which users (or which IP addresses) 
>could access >which databases.

In SQLiteServer code the user validation could be handled by a callback into 
the 
application code that is hosting the SQLiteServer amalgamation code.  The 
application code could leave default of NULL and have no user validation or 
supply function and provide appropriate user validation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Jeff Archer]

Just my two cents...

A SQLite Server would be userful.  I have considered creating this myself.  I 
have thought that it would need to be kept zero config and it should be 
provided 
in amalgamation form to keep simple to embed in applications.  Wouldn't need 
incredible throughput as it is a ServerLite.  Would very conveniently avoid the 
issues of sharing database file across network.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Troubleshooting...

[jeff archer]

>> My only guess is that basic_string::c_str() doesn't really provide a pointer 
>> to 
>>a null-terminated c-style string, >but a facsimile of one that SQLite doesn't 
>>like.
>
>c_str() provides pointer to the data that string has with additional
>null byte added at the end. That's it. Whether it is a null-terminated
>c-style string depends on what data you put into it. If that data has
>null bytes in it then SQLite will see only part of your string, though
>I'm not sure that it was the actual problem you have seen.

You may probably already know this but maybe I'll remind you.  The pointer 
returned by c_str() is only valid in the statement where it is used or possibly 
as long as the life of the basic_string it came from.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Just compiled SQLite in Visual Studio

[Jeff Archer]

>From: john darnell 
>Date: Mon, 29 Nov 2010 17:10:04 -0600
>
>I just added it to a Visual Studio 8 project, turned off the use of 
>precompiled 
>headers (the project is a C++ project) and compiled the SQLite.c >file without 
>any errors.  Is it really that easy, or am I about ready to be hit by a Mac 
>truck filled with bugaboos?

I believe it is that easy.  I have been using SQLite 3.6.23 and now 3.7.3 with 
VS2008 and VS2010 in both 32 and 64 bit applications for a while now with no 
issues.  I have a number of use case with multiple applications (on same 
machine) in same db file with no issue as long as writer is not too fast.  I 
understand there is a test suite that you can run to test your build of 
sqlite.  
I have never run it.  I only test the feature functionality of my applications 
with the assumption that SQLite will work correctly.  It hasn't let me down yet.

Jeff
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to make this calculation in VIEW

[Jeff Archer]

Thank you, Simon and Owen.

I have tried and both solutions work equally well.  I have actually used C code 
to make the calculation as I pull out the data.
But to help further my understanding of SQL/SQLite, can anyone comment on 
potential performance differences of the 2 SQL solutions.

CREATE VIEW IF NOT EXISTS [vw_patterns0] AS 
SELECT Patterns.Name AS Pattern, 
   Patterns.Origin_X, 
   Patterns.Origin_Y, 
   Patterns.wMicrons, 
   Patterns.hMicrons, 
   COUNT(DISTINCT Offset_X) AS nTilesX, 
   COUNT(DISTINCT Offset_Y) AS nTilesY, 
   Patterns.wPixels,
   Patterns.hPixels,   
   Patterns.wMicrons * COUNT(DISTINCT Offset_X) AS wTotalMicrons,
   Patterns.hMicrons * COUNT(DISTINCT Offset_Y) AS hTotalMicrons,
   Patterns.wPixels * COUNT(DISTINCT Offset_X) AS wTotalPixels,
   Patterns.hPixels * COUNT(DISTINCT Offset_Y) AS hTotalPixels,
   Patterns.Description 
FROM [Tiles] INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID 
GROUP BY Tiles.PatternID;

CREATE VIEW IF NOT EXISTS [vw_patterns1] AS 
SELECT Patterns.Name AS Pattern, 
   Patterns.Origin_X, 
   Patterns.Origin_Y, 
   Patterns.wMicrons, 
   Patterns.hMicrons, 
   COUNT(DISTINCT Offset_X) AS nTilesX, 
   COUNT(DISTINCT Offset_Y) AS nTilesY, 
   Patterns.wPixels,
   Patterns.hPixels,
   Patterns.Description 
FROM [Tiles] INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID 
GROUP BY Tiles.PatternID;

CREATE VIEW IF NOT EXISTS [vw_patterns2] AS 
SELECT Pattern, 
   Origin_X, 
   Origin_Y, 
   wMicrons, 
   hMicrons, 
   nTilesX, 
   nTilesY, 
   wMicrons * nTilesX AS wTotalMicrons,
   hMicrons * nTilesY AS hTotalMicrons,
   wPixels * nTilesX AS wTotalPixels,
   hPixels * nTilesY AS hTotalPixels,
   wPixels,
   hPixels,
   Description 
FROM [vw_patterns1];
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to make this calculation in VIEW

[Jeff Archer]

First let me say thank you to all for the very good support that receive here.

I have the these tables and view

// Patterns table
CREATE TABLE IF NOT EXISTS [Patterns] (
PatternID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
Name  VARCHAR NOT NULL UNIQUE,
Description   VARCHAR NOT NULL,
Origin_X  REAL NOT NULL DEFAULT 0.0,
Origin_Y  REAL NOT NULL DEFAULT 0.0,
wMicrons  REAL NOT NULL DEFAULT 0.0,
hMicrons  REAL NOT NULL DEFAULT 0.0,
wPixels   INTEGER NOT NULL DEFAULT 0.0,
hPixels   INTEGER NOT NULL DEFAULT 0.0
);

// Tiles table
CREATE TABLE IF NOT EXISTS [Tiles] (\n"
TileID    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n"
PatternID INTEGER NOT NULL REFERENCES [Patterns] DEFERRABLE INITIALLY 
DEFERRED,\n"
Offset_X  REAL NOT NULL DEFAULT 0.0,\n"
Offset_Y  REAL NOT NULL DEFAULT 0.0\n"
);

// vwPatterns
CREATE VIEW IF NOT EXISTS [vwPatterns] AS 
SELECT Patterns.Name AS Pattern, 
   Patterns.Origin_X, 
   Patterns.Origin_Y, 
   Patterns.wMicrons, 
   Patterns.hMicrons, 
   COUNT(DISTINCT Offset_X) AS nTilesX, 
   COUNT(DISTINCT Offset_Y) AS nTilesY, 
   Patterns.wPixels,
   Patterns.hPixels,
   Patterns.Description 
FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID 
GROUP BY Tiles.PatternID;

I would like to add these calculated columns to my view but not sure how to 
make 
this work. 

   Patterns.wMicrons * nTilesX AS wTotalMicrons,
   Patterns.hMicrons * nTilesY AS hTotalMicrons,
   Patterns.wPixels * nTilesX AS wTotalPixels,
   Patterns.hPixels * nTilesY AS hTotalPixels,

i.e.
// vwPatterns
CREATE VIEW IF NOT EXISTS [vwPatterns] AS 
SELECT Patterns.Name AS Pattern, 
   Patterns.Origin_X, 
   Patterns.Origin_Y, 
   Patterns.wMicrons, 
   Patterns.hMicrons, 
   COUNT(DISTINCT Offset_X) AS nTilesX, 
   COUNT(DISTINCT Offset_Y) AS nTilesY, 
   Patterns.wPixels,
   Patterns.hPixels,
   Patterns.wMicrons * nTilesX AS wTotalMicrons,
   Patterns.hMicrons * nTilesY AS hTotalMicrons,
   Patterns.wPixels * nTilesX AS wTotalPixels,
   Patterns.hPixels * nTilesY AS hTotalPixels,
   Patterns.Description 
FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID 
GROUP BY Tiles.PatternID;

But it get error: "no such column: nTilesX"

It works if I substitute a constant for the nTilesX and nTilesY so I think I do 
not know how to correctly reference these.

Thank you.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with query

[Jeff Archer]

>From: Drake Wilson <dr...@begriffli.ch> Sun, November 14, 2010 7:50:19 AM
>> SELECT COUNT(Offset_Y) FROM (SELECT DISTINCT Offset_Y FROM Tiles WHERE 
>>PatternID 
>>
>> = 1);
>> 
>> Is it possible to have a single query that will generate a row for each 
>> PattenID, COUNT(Offset_Y) combination?
>
>Does SELECT PatternID, COUNT(DISTINCT Offset_Y) FROM Tiles GROUP BY
>PatternID do what you're looking for?

That's it.  Thank you.  I also joined in the pattern name.

CREATE VIEW [vwPatterns] AS 
SELECT Patterns.Name AS Pattern, Patterns.Origin_X, Patterns.Origin_Y, 
COUNT(DISTINCT Offset_X) AS Width, COUNT(DISTINCT Offset_Y) AS Height, 
Patterns.Description 

FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID  GROUP 
BY 
Tiles.PatternID;

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help with query

[Jeff Archer]

I have a table

CREATE TABLE [Tiles] (
TileID    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
PatternID INTEGER NOT NULL REFERENCES [Patterns] DEFERRABLE INITIALLY 
DEFERRED,
Offset_X  REAL NOT NULL DEFAULT 0.0,
Offset_Y  REAL NOT NULL DEFAULT 0.0
);

And I can get the number of unique Y offsets in a pattern like so:

SELECT COUNT(Offset_Y) FROM (SELECT DISTINCT Offset_Y FROM Tiles WHERE 
PatternID 
= 1);

Is it possible to have a single query that will generate a row for each 
PattenID, COUNT(Offset_Y) combination?
i.e.
1 | 29
2 | 37
3 | 45

Thanks,
Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about SQLite features.

[Jeff Archer]

>From: "Kees Nuyt" <k.n...@zonnet.nl>
>Thursday, November 11, 2010 10:34:51 AM
> 
>Stored procedures don't enforce business rules by
>themselves. Constraints and triggers do.
>To enforce business rules stored as procedures in the
>database, one would need an access system which prevents
>direct modification of table data. 
>As an embedded database SQLite definately is in an other
>niche of the DBMS market.
>Stored procedures can often be replaced by INSTEAD OF
>triggers on views, that's quite powerful.

OK, they can't truly enforce business rules but nothing enforces good design on 
my C++ code either.
What it does is allow the business rules to be packaged as a complete unit with 
the data and I claim that would be better design and more maintainable over the 
life of the project.  


Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about SQLite features.

[jeff archer]

I hope this doesn't get posted twice.  I don't think it will since I sent form 
wrong account first time.  Sorry.

>From: "Kees Nuyt" <k.n...@zonnet.nl>
>Thursday, November 11, 2010 10:34:51 AM
> 
>Stored procedures don't enforce business rules by
>themselves. Constraints and triggers do.
>To enforce business rules stored as procedures in the
>database, one would need an access system which prevents
>direct modification of table data. 
>As an embedded database SQLite definately is in an other
>niche of the DBMS market.
>Stored procedures can often be replaced by INSTEAD OF
>triggers on views, that's quite powerful.

OK, they can't truly enforce business rules but nothing enforces good design on 
my C++ code either.
What it does is allow the business rules to be packaged as a complete unit with 
the data and I claim that would be better design and more maintainable over the 
life of the project.  


Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about SQLite features.

[jeff archer]

>From: "Olaf Schmidt" <s...@online.de>
>Wednesday, November 10, 2010 9:07:19 AM
>
>[Stored procedures in SQLite]
>
>IMO stored procedure-support only makes sense in "Server-Instances" which run 
>on 
>their own...

I disagree.  The overall design and structure of applications using SQLite and 
therefor SQLite itself would benefit from SQLite supporting stored procedures.  
This would allow all code necessary for enforcing the business rules of the 
data 
to be stored in the database itself.  This is just a good basic design 
principal.

Please don't anyone take this comment wrongly.  I really like SQLite and I am 
very committed to its use in my projects.  Many thanks to all who make SQLite 
possible.  It is an excellent product.  And the support available through this 
mailing list is truly top notch.  I have paid (sometimes dearly) for a lot less 
competent and complete support.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] A question about transactions

[jeff archer]

I am using SQLite from C++ and I have implemented a class to manager nested 
transactions using savepoints.  I have currently implemented as a stack of 
transactions such that the first Begin uses BEGIN IMMEDIATE, while subsequent 
levels use SAVEPOINT T where  is a sequentially increasing number 
starting at 0001.  Commit does RELEASE on the latest SAVEPOINT or a real COMMIT 
once savepoint stack is cleared.  Rollback does ROLLBACK TO or a real ROLLBACK 
once savepoint stack is cleared.

Is this OK to mix savepoints with transactions like this?

Thanks,
Jeff
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query help

[jeff archer]

I have a table containing width and height of images with columns wPixels, 
hPixels.  I would like to select all rows that have either a unique wPixels or 
a 
unique hPixels value.

for this data:
10, 20
10, 20
10, 30
10, 3015, 10
15, 30
15, 30
15, 30

I would like to select:
10, 20
10, 30
15, 10
15, 30

 Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] General questions from a newbe

[jeff archer]

I'm new to SQLite but have some experience with MS SQL Server.  I am looking 
for 
a quick confirmation from expert that my planed use of SQLite is reasonable and 
maybe point out if there are any gotchas.  I've done some prototyping and tried 
most of this out but it is based on how I've done things in the past so maybe 
not best way in SQLite.  My prototypes have only been very small.

I will have two general usage patterns.  1) Few and infrequent single row 
updates.  2) I will start a secondary processes that will scan through a table 
(1000-2 rows) and for each row run an analysis that will take 1 to 90 
seconds and then update another table with the results.

I'm on windows.  I have compiled SQLite into a dll and wrapped it in some C++ 
classes.  SQLite is not exported from the dll.  I plan to use this in both 32 
bit and 64 bit applications to access potentially the same files.  


I will use the following options when I compile SQLite:
SQLITE_THREADSAFE=2
SQLITE_DEFAULT_FILE_FORMAT=4

I have my own compilation of sqlite3.exe that I compile the code of sqlite3.c 
and shell.c into the same application using the same options as above.

I call my dll and exe: sqlutils.dll and sqlutil.exe 

Generally, in the past I have always used normalized data but thought I read 
this might not always be most efficient in SQLite.  Is there any "rule of 
thumb"?

My tables will be relativly small.  Most probably in the 1000-2 rows range.

I will call stored procedure from C++ for any actually changes to the data.

I will use views to make queries simply from c++ code.

Thank you for you time in looking into this,
Jeff
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users