Re: [sqlite] Constraint error messages

[Petite Abeille]

On Mar 1, 2012, at 12:20 AM, Roger Binns wrote:

> There is a reason developers have gone to the trouble of naming their
> constraints!

Indeed. All these constraint names are meant to convey information. They are 
not decorative.

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

Re: [sqlite] Constraint error messages

[Jean-Christophe Deschamps]

Me too.  Either as a new standard way of working, or as something 
which can be turned on and off with a PRAGMA.  I accept that SQLite is 
meant to be fast, but having SQLite spit out which check was violated 
will result in my app running faster and more dependably than when I 
build the same logic into my own code.


I can only warmly agree to every "pro" reason exposed so far.

Not only that but even if that means a non-trivial code increase, I bet 
my hat that once the feature is there, its code will be shortly 
imperative in almost every serious use of SQLite.


I mean, even if such a safety net code portion clearly won't run as 
often as b-tree scans, it will be there for applications to robustly 
rely on it.  This will only promote more systematic use of constraints 
checks (that probably use much more code in SQLite core that this 
feature) and greatly simplify [and debug] applications.


So this extra code, even if seldom actually run, will certainly become 
very important to users, in my view more useful than native foreign 
keys.  (Not that I'd like DRH to remove native FK support)!



JcD

War does not determine who is right - only who is left. 


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

Re: [sqlite] Constraint error messages

[Simon Slavin]

On 1 Mar 2012, at 12:38am, Mario Becroft  wrote:

> Just adding my voice to the choir. The constraints are of limited value
> if you can't tell which one failed, and the system is not much more
> 'lite' if the constraints have to be duplicated using CHECK clauses
> anyway.

Me too.  Either as a new standard way of working, or as something which can be 
turned on and off with a PRAGMA.  I accept that SQLite is meant to be fast, but 
having SQLite spit out which check was violated will result in my app running 
faster and more dependably than when I build the same logic into my own code.

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

Re: [sqlite] Constraint error messages

[Mario Becroft]

Just adding my voice to the choir. The constraints are of limited value
if you can't tell which one failed, and the system is not much more
'lite' if the constraints have to be duplicated using CHECK clauses
anyway.

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

Re: [sqlite] Constraint error messages

[BareFeetWare]

On 01/03/2012, at 8:28 AM, Richard Hipp wrote:

> SQLite implements CHECK constraints by concatenating all expressions from all 
> CHECK constraints on the table using AND and then evaluating the resulting 
> boolean to see if it is false.  If it is false, an SQLITE_CONSTRAINT error is 
> raised.
> 
> We *could* keep track of each separate CHECK expression and remember the 
> constraint name and evaluate each expression separately and output a 
> customized error message for each failure.  But that would require a lot of 
> extra code space, which is not "lite".

Though I'm sure that your programming skill far exceeds mine, I understand what 
you're saying about some extra overhead, since it's a common programming choice 
we make each day "Will I lump the error checks together for a boolean result, 
or will I write a separate if/then for each check and notify of the actual 
error?"

However, I would urge and plead with you to consider providing doing the 
latter. All the constraints and error checking is of limited value when we 
can't tell what actual constraint or check failed, especially when it means I 
basically have to reinvent the wheel to perform the same checks externally, and 
hope that my logic replicates SQLite's.

I come across this frustration with nearly every SQLIte database I create. I 
fastidiously design my database schema to prevent inconsistent data, with many 
constraints (eg not null, foreign keys etc). When I import some data or have 
someone enter some data, I need SQLite to tell me which of those many 
constraints failed and where. Otherwise it makes the preparation largely 
useless and requires manual or application level duplication of the logic which 
is redundant, imprecise and difficult to maintain.

Thanks for your consideration and an otherwise outstanding product.

Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

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

Re: [sqlite] Constraint error messages

[BareFeetWare]

I've also tried also using it in an SQL transaction (eg a batch import script), 
but SQLite doesn't allow it. So, in a transaction, one approach I've used is to 
create a temp table, a temp trigger and then insert some test data just to be 
able to use the raise function to abort the transaction and post an error back.

Another work around is to create a Log table and populate it with error 
messages that should result from any dat that is outside the desired 
constraints, eg:

create table if not exists "Log"
(   ID integer primary key not null
,   "Date" date not null
,   "Error" text not null collate nocase
)
;
insert into "Log" ("Date", "Error")
select
datetime('now')
,   'columnValue ' || columnValue || ' is above maximum ' || maximumAllowed 
|| ' in row ' || rowid
from Source
where new.columnValue > maximumAllowed
;

But again, you have to duplicate the logic that is already in your constraints, 
which is frustrating and error prone.

Thanks,
Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

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

Re: [sqlite] Constraint error messages

[BareFeetWare]

On 01/03/2012, at 4:22 AM, Pete wrote:

> I would like to include as much error checking as possible in my database 
> schema.

That's an admirable aim. The whole point of constraints is to bring the error 
checking as close to the data model as possible.

> The problem I have is that the error messages that come back from constraint 
> violations are extremely generic (e.g. "constraint failed") and would mean 
> nothing to a user.  I tried including a name for constraints hoping I could 
> check the error message for the name and translate it into a meaningful user 
> message, but the name isn't returned in the error message.

Yes, this is very frustrating and reduces the effectiveness of the whole 
constraint and check facility.

> Are there any tricks by which to get meaningful error messages when a 
> constraint fails?  I saw the RAISE command - perhaps that could be used in a 
> CHECK constraint, but it feels like I would be duplicating built in 
> constraints if I do that, e.g CHECK (Col1 IS NOT NULL) for a NOT NULL 
> constraint.

The "raise" command is helpful in triggers, eg:

select raise(abort, 'columnValue is above maximum') where new.columnValue > 
maximumAllowed

But you have to create triggers that duplicate the constraints that you already 
have in your table schema. Very error prone, inconsistent, redundant and 
inefficient.

SQLite doesn't allow customisation the error message that raise provides, so I 
can show what error occurred, but not where it occurred. ie this is not allowed:

select raise(abort, 'columnValue ' || new.columnValue || ' is above maximum ' 
|| maximumAllowed || ' in row ' || new.rowid) where new.columnValue > 
maximumAllowed

Thanks,
Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

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

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 29/02/12 12:23, Carl Desautels wrote:
> I would like to be able to run one statement that sets the locale for
> upper() and lower()

If you register a function with the same name and number of arguments as a
builtin one, then yours will take priority.  You can simply register your
own lower/upper with one argument that looks wherever you want to know
what locale to use.  It will not affect the one registered by ICU for two
arguments.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9Os04ACgkQmOOfHg372QREcwCgigKHsMfew3d54rfV7hTg5iI+
JXoAoLfDRHglplt/JNxt8JzY57PDTUkb
=8+Au
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Constraint error messages

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 29/02/12 13:28, Richard Hipp wrote:
>> We *could* keep track of each separate CHECK expression and remember
>> the constraint name and evaluate each expression separately and
>> output a customized error message for each failure.  But that would
>> require a lot of extra code space, which is not "lite".

This is just bytecode.  It requires one extra instruction per CHECK.
Currently the code for something like A AND B AND C should look something
like the following since you can abort the processing of an AND the moment
an arm is false.

1. Evaluate A
2. If not true, goto error
3. Evaluate B
4. If not true, goto error
5. Evaluate C
6. If not true, goto error

Inserting an extra opcode to set the error message isn't a huge overhead,
and is required once per arm.  Or whatever is done for 'goto error' could
have a parameter pointing to the string name of the constraint.

A higher level approach depends on a 'raise' function, so A AND B AND C
can be rewritten to (A OR raise('A')) AND (B OR raise('B')) AND (C OR
raise('C'))

There is a reason developers have gone to the trouble of naming their
constraints!

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9OsrgACgkQmOOfHg372QTqggCgzT+w/n0nsdhvUTVJclICMGUx
FgQAoJLYXSyPLETHPvqD2oSD3m88N6MX
=3vVk
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Constraint error messages

[Richard Hipp]

On Wed, Feb 29, 2012 at 2:25 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 29/02/12 09:22, Pete wrote:
> > The problem I have is that the error messages that come back from
> > constraint violations are extremely generic (e.g. "constraint failed")
> > and would mean nothing to a user.
>
> An issue first reported in 2006:
>
>  http://www.sqlite.org/src/tktview?name=23b2128201
>
> I think (but have not tried) that you can make a user defined function and
> have that do the checks.  It can then either directly error, or put a
> message in a buffer somewhere that you can retrieve on constraint failure.
>

SQLite implements CHECK constraints by concatenating all expressions from
all CHECK constraints on the table using AND and then evaluating the
resulting boolean to see if it is false.  If it is false, an
SQLITE_CONSTRAINT error is raised.

We *could* keep track of each separate CHECK expression and remember the
constraint name and evaluate each expression separately and output a
customized error message for each failure.  But that would require a lot of
extra code space, which is not "lite".


>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk9Oe6wACgkQmOOfHg372QTEoQCg2K3BFZ+hwOAYKvSoKDCg1+kZ
> s6IAoJ1OyzISCz9sYK2nKBn+Z4wqCn8y
> =2KAk
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Set Locale for upper() and lower() using a pragma variable

[Carl Desautels]

From the ICU documentation, (
http://www.sqlite.org/src/artifact?ci=trunk=ext/icu/README.txt)

 To access ICU "language specific" case mapping, upper() or lower()
should be invoked with two arguments.[...]
lower('I', 'tr_tr') -> 'ı' (small dotless i)


With an ICU enabled build of SQLite, I would like to be able to run one
statement that sets the locale for upper() and lower() so that they default
to that locale instead of ASCII.

At the moment I am thinking of adding a "pragma" variable, which would then
be used by "icuCaseFunc16" as the default locale, how do I go about adding
a "pragma" variable?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Constraint error messages

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 29/02/12 09:22, Pete wrote:
> The problem I have is that the error messages that come back from 
> constraint violations are extremely generic (e.g. "constraint failed")
> and would mean nothing to a user.

An issue first reported in 2006:

  http://www.sqlite.org/src/tktview?name=23b2128201

I think (but have not tried) that you can make a user defined function and
have that do the checks.  It can then either directly error, or put a
message in a buffer somewhere that you can retrieve on constraint failure.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9Oe6wACgkQmOOfHg372QTEoQCg2K3BFZ+hwOAYKvSoKDCg1+kZ
s6IAoJ1OyzISCz9sYK2nKBn+Z4wqCn8y
=2KAk
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] accessing multiple databases

[Simon Slavin]

On 29 Feb 2012, at 6:07pm, Rob Richardson  wrote:

> IIRC, there's a connection string option that will choose between creating an 
> empty database and throwing an exception if you try opening a database that 
> doesn't exist.

Arguments to sqlite3_open_v2():

http://sqlite.org/c3ref/open.html

But further down the same page you'll see a 'mode' string which can be used the 
same way.

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

Re: [sqlite] accessing multiple databases

[Rob Richardson]

IIRC, there's a connection string option that will choose between creating an 
empty database and throwing an exception if you try opening a database that 
doesn't exist.  Perhaps if that option is set to throw an exception, then the 
ATTACH command would fail.  Or not.  

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

Re: [sqlite] accessing multiple databases

[jwzumwalt]

As a follow up...

Sqlite finds the first db fine useing a relative path but the ATTACH command
needs the FULL PATH. Ugg!



Are you sure it's really opening the DB you think it's opening?  I think
SQLite will create the file if it's not there, and you'll have nothing in
it.

Perhaps you should try it and specify the complete path, just in case.  I
don't know if the PDO object looks for the file before opening it.

Marc

-- 
View this message in context: 
http://old.nabble.com/accessing-multiple-databases-tp33410065p33415881.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] [SOLVED] accessing multiple databases

[jwzumwalt]

VERY NICE!!!  You were right!

I tried ./filename and that did not work, then I tried
$_SERVER[DOCUMENT_ROOT]/path/filename BINGO!
Thanks for teh help.

jan zumwalt



Are you sure it's really opening the DB you think it's opening?  I think
SQLite will create the file if it's not there, and you'll have nothing in
it.

Perhaps you should try it and specify the complete path, just in case.  I
don't know if the PDO object looks for the file before opening it.

Marc

-- 
View this message in context: 
http://old.nabble.com/accessing-multiple-databases-tp33410065p33415854.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Constraint error messages

[Pete]

I would like to include as much error checking as possible in my database
schema.  The problem I have is that the error messages that come back from
constraint violations are extremely generic (e.g. "constraint failed") and
would mean nothing to a user.  I tried including a name for constraints
hoping I could check the error message for the name and translate it into a
meaningful user message, but the name isn't returned in the error message.

Are there any tricks by which to get meaningful error messages when a
constraint fails?  I saw the RAISE command - perhaps that could be used in
a CHECK constraint, but it feels like I would be duplicating built in
constraints if I do that, e.g CHECK (Col1 IS NOT NULL) for a NOT NULL
constraint.

Thanks,


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

Re: [sqlite] SELECT average timestamp to get average time of day? (C M)

[C M]

On Wed, Feb 29, 2012 at 8:41 AM, Mark Belshaw
wrote:

> This is the first time I've posted a response to any mailing list, so I
> hope
> I'm doing it right and it appears where it should!
>

It sure did.  Thanks for participating.


> Not SQLite, but a technique we use in our Time & Attendance system to help
> with this sort of thing, where employees work nights / days and rotating
> shift patterns, is to include a Boolean "After Midnight" with each time. We
> are not looking for averages, so I won't get into the SQL you would use for
> that, but are trying to keep transactions for the same shift together,
> where
> they may be from different, consecutive, calendar days.
>
> So, an early morning transaction for an early morning shift would have, say
> False | 04:35, whereas an early morning Out from a night shift would be
> True
> | 05:02.
>

That's an interesting angle.  I may actually already be doing something
like that inadvertently, because I am processing different times of day and
likely events during them separately.  In other words, I might only handle
the 8pm--4am block in one action, which would be equivalent to your True
("After Midnight") case.

The arithmetic then becomes something like ([Out After Midnight * 24] + Out
> Time) - ([In After Midnight] * 24] + In Time). I guess it's really just a
> variation on the 'add 12 hours, do your stuff, then take it off again'
> method but, if you can determine the required state of After Midnight at
> the
> point of data collection, it gives you a method to persist this state to
> the
> database, rather than having to unpick each one on the fly.
>

I'll think about how to incorporate that in what I'm doing.

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

Re: [sqlite] Online backup API and sqlite3_interrupt()

[Richard Hipp]

On Wed, Feb 29, 2012 at 11:59 AM, Gregory Johnson wrote:

> Hi,
> I have an application where one thread (A) is executing various statements
> and another thread (B) is performing an online backup. These two threads
> share the same source database connection; the SQLite threading mode is set
> to serialized.
>
> I wish to stop the online backup from another thread as quickly and safely
> as possible. In particular, I want to interrupt a potentially time
> consuming sqlite3_backup_step call. I thought calling sqlite3_interrupt on
> the online backup destination database would be ideal, since I do not want
> to potentially stop A's execution by calling interrupt on the source
> database. However the online backup documentation (
> http://www.sqlite.org/c3ref/backup_finish.html) states that an application
> "must guarantee that the destination database connection is not passed to
> any other API (by any thread) after sqlite3_backup_init() is called."
>
> So, my question is: is it safe to call sqlite3_interrupt on the destination
> database connection of an online backup?
>

I think it is safe.  But it will also be a no-op.  The sqlite3_interrupt()
interface only stops SQL statements, not sqlite3_backup_step() calls.

That seems wrong doesn't it?  It never occurred to us to want to interrupt
an sqlite3_backup_step() call.  Maybe we should fix this.

On the other hand, if you want sqlite3_backup_step() to be "interruptable"
then just do a single step at a time in a loop and check for your own
interrupt mechanism on each iteration of the loop.  You don't need SQLite's
help for this.

I'm not sure what the right solution for SQLite is:  Do we change
sqlite3_backup_step() to be interruptable or not?  We'll have to think
about that some before reaching a decision.


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



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

Re: [sqlite] Sqlite3BTreeMovetoUnpacked SQLite SELECT Statement Workaround is I/O Bound and uses all the Physical Memory

[Frank Chang]

Daniel Kennedy, I tried your SQLite SELECT Statement workaround(the profiler 
output is attached) but the profiler is full of sqlite functions and the 
application runs slower because it is I/O bound and uses almost all the 
physical memory. 
I was thinking maybe we could write only one SQLITE SELECT statement and cache 
the blobs in memory
Could you please suggest how I could fix this I/O Bound SQLIte SELECT Statement 
Problem.
void cIntersectingDedupe::GetSubGraphBlob(sSUBGRAPHINFO *SubGraph_,/*unsigned 
long*/ int*& SubGraphBlob_,
  int *Size_) {
 int Size;

  //sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);

 // Grab a BLOB and put it into a unsigned long buffer. As the BLOB contains 
record numbers,
 //   we will never see a value of zero. Thus, we use 0 to mark the end of the 
array.
 //
 // Note that we can have BLOBs of size 0, though. If we used DistillSubGraph to
 //   dedupe the subgraph, some records will be consolidated into others. The
 //   donor record's BLOB gets zapped because all of it's BLOB was rolled into 
the
 //   donee (All your BLOB are belong to us!)
 
 // First time, open the BLOB for real, else we can re-open (faster):
   char SelectStatement[256];
   char WhereClause[256];
 strcpy(SelectStatement, "select [Rows] from AggregatedData");
   sprintf(WhereClause," where [RowId] = %d",SubGraph_->IteratorPos+1);
   strcat(SelectStatement, WhereClause);
   int ReturnValue=sqlite3_prepare(SubGraph_->Database,
SelectStatement,-1,
_->Statement);
 
   int status =  sqlite3_step(SubGraph_->Statement);
   if (status == SQLITE_ROW) {
  
SubGraphBlob_ =  (int*)sqlite3_column_blob(SubGraph_->Statement,0); //2);
Size = (sqlite3_column_bytes(SubGraph_->Statement,0)); //2);
// if (SubGraph_->hBlob==0)
//  
sqlite3_blob_open(SubGraph_->Database,"main","AggregatedData","Rows",SubGraph_->IteratorPos+1,0,_->hBlob);
// else
//  sqlite3_blob_reopen(SubGraph_->hBlob,SubGraph_->IteratorPos+1);
//
// Size=sqlite3_blob_bytes(SubGraph_->hBlob)/sizeof(unsigned long);
// sqlite3_blob_read(SubGraph_->hBlob,SubGraphBlob_,Size*sizeof(unsigned 
long),0);
// SubGraphBlob_[Size]=0;
 if (Size_!=0)
  *Size_=Size;
 }
}
void cIntersectingDedupe::IntersectBlobs(sSUBGRAPHINFO *SubGraph_,int 
*IntersectionBlob_,
  /*unsigned long*/int *SubGraphBlob_) {
 int Pos1,Pos2,PosOut;
 GetSubGraphBlob(SubGraph_,SubGraphBlob_);
 // Perform the intersection. We walk though the two blobs, if the blobs 
contain the same
 //   value, that value is copied to PosOut in Blob_, else, the blob that is 
'behind' is
 //   incremented so it can 'catch up' to the other:
 Pos1=Pos2=PosOut=0;
 while (IntersectionBlob_[Pos1]!=0 && SubGraphBlob_[Pos2]!=0) {
  if (IntersectionBlob_[Pos1]==SubGraphBlob_[Pos2]) {
   IntersectionBlob_[PosOut++]=IntersectionBlob_[Pos1++];
   Pos2++;
  } else if (IntersectionBlob_[Pos1]

[sqlite] Online backup API and sqlite3_interrupt()

[Gregory Johnson]

Hi,
I have an application where one thread (A) is executing various statements
and another thread (B) is performing an online backup. These two threads
share the same source database connection; the SQLite threading mode is set
to serialized.

I wish to stop the online backup from another thread as quickly and safely
as possible. In particular, I want to interrupt a potentially time
consuming sqlite3_backup_step call. I thought calling sqlite3_interrupt on
the online backup destination database would be ideal, since I do not want
to potentially stop A's execution by calling interrupt on the source
database. However the online backup documentation (
http://www.sqlite.org/c3ref/backup_finish.html) states that an application
"must guarantee that the destination database connection is not passed to
any other API (by any thread) after sqlite3_backup_init() is called."

So, my question is: is it safe to call sqlite3_interrupt on the destination
database connection of an online backup?

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

Re: [sqlite] SELECT average timestamp to get average time of day? (C M)

[Mark Belshaw]

This is the first time I've posted a response to any mailing list, so I hope
I'm doing it right and it appears where it should!

Not SQLite, but a technique we use in our Time & Attendance system to help
with this sort of thing, where employees work nights / days and rotating
shift patterns, is to include a Boolean "After Midnight" with each time. We
are not looking for averages, so I won't get into the SQL you would use for
that, but are trying to keep transactions for the same shift together, where
they may be from different, consecutive, calendar days. 

So, an early morning transaction for an early morning shift would have, say
False | 04:35, whereas an early morning Out from a night shift would be True
| 05:02.
The arithmetic then becomes something like ([Out After Midnight * 24] + Out
Time) - ([In After Midnight] * 24] + In Time). I guess it's really just a
variation on the 'add 12 hours, do your stuff, then take it off again'
method but, if you can determine the required state of After Midnight at the
point of data collection, it gives you a method to persist this state to the
database, rather than having to unpick each one on the fly.

Hope it's of some help :)

Mark Belshaw
-Original Message-


Message: 4
Date: Mon, 27 Feb 2012 14:09:55 -0500
From: C M 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] SELECT average timestamp to get average time of
day?
Message-ID:

Content-Type: text/plain; charset=ISO-8859-1

On Fri, Feb 24, 2012 at 4:53 PM, Marc L. Allen
wrote:

> You're trying to calculate it for individual people?  Can you count on 
> night-time people to stay night-time, or do you need to worry about 
> someone shifting by 12 hours?
>

It's for individuals, and it is possible for individuals to shift or drift
by any amount.

>
> If not, your best bet is, for the night-time people, add, say 6 hours 
> to all of their times, do your average, then subtract the 6 hours back
out.
>

Yes, this is a good idea, the same as was given in another response.
Thanks.

I found that this type of measure is referred to as the "mean of circular
quantities", and there is even a Wikipedia page about that...I had just
never thought about it before.  I also found the Mitsuta Method for dealing
with this type of issue.  But in any approach, things break down if data is
strewn all over a 24 hour period.


> There are cases where this will fail, but you might be able to detect 
> data sets that will cause this issue and ignore them.
>

I will have to just come up with a reasonable check of the data's variance
and if I find it is all over the clockface, let the user know that the mean
bedtime can't really be computed due to the erratic data.  Maybe if only a
few outliers are found I could filter them out.   I may post a follow-up
question regarding that.

Thanks!
Che




This e-mail has been scanned for all viruses by Star. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk

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

Re: [sqlite] SET NEW.FieldName in trigger

[Simon Slavin]

On 29 Feb 2012, at 12:36pm, hsymington  wrote:

> Thanks Simon; this does sound like a less headache-driven way of doing it. I
> was simplifying things slightly for an example; the actual situation is more
> complicated.

Okay, that explains it.  I'm going to let the other readers ponder your tax 
example.  Obviously, it is possible under SQLite, though I don't think there's 
a simple way of doing it which would work both under MySQL and SQLite.  I 
suspect that the best way to do it would be to store the tax calculations in 
another TABLE or VIEW and store only a CalculationType value in the sales table.

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

Re: [sqlite] SET NEW.FieldName in trigger

[hsymington]

Simon Slavin-3 wrote:
> 
>> Background: I've got a database schema in the form of a text file, which
>> some software reads and converts to a SQLite database. I also need php to
>> be
>> able to read that text file and convert it into a MySQL database. I'm
>> trying
>> to work out how to define triggers so that I can change the schema
>> information and have it work for both MySQL and SQLite... and am getting
>> a
>> headache in the process!)
> 
> Give up.  Although the basics of SQL are present in all SQL engines,
> trying to make anything but simple SQL work in more than one SQL engine
> tends to lead only to ridiculously complicated code.  I'm serious: it just
> doesn't work.  If all you have is simple tables and indexes you're fine. 
> If you're trying to put complicated database logic into your table
> definitions you always have to make changes when you move from one SQL
> implementation to another.  So it's not worth even trying.
> 
> However, if the above is really what you're trying to do then I think
> you're doing it wrong.  You don't want TRIGGERs or even DEFAULTs.  If you
> really have some sort of correspondence between SaleItem_Type and
> SaleItem_Description then it belongs in its own table.  Store only the
> SaleItem_Type in this table.  Make another TABLE called ItemTypes with
> columns 'typeNumber' and 'description' and feed it (1, 'Fish'), (2,
> 'Chips') etc..  Then when you want to know what text is associated with a
> type, look it up in that TABLE using a JOIN or any other method.  That's
> the SQL way of doing it.
> 
> 

Thanks Simon; this does sound like a less headache-driven way of doing it. I
was simplifying things slightly for an example; the actual situation is more
complicated. What I'm trying to do is something like the below. 

Prerequisites: 
A table called 'Sale', which has a column for TaxInclusive, and which has a
column for a UUID
A table called 'SaleItem', which has a column for Sale_UUID (i.e. the sale
it's linked to), a column for TaxRate, and a column for LinePrice.

If SaleItem_TaxRate<>0 then
 If (SELECT Sale.Sale_TaxInclusive FROM Sale, SaleItem WHERE
Sale.Sale_UUID=SaleItem.SaleItem_SaleUUID) = 1 then
  UPDATE SaleItem SET SaleItem_GrossAmount = SaleItem_LinePrice
WHERE SaleItem_ID=New.SaleItem_ID;
  UPDATE SaleItem SET SaleItem_TaxAmount =
SaleItem_LinePrice-(NEW.SaleItem_LinePrice/((NEW.SaleItem_TaxRate +
100)/100))  WHERE SaleItem_ID=New.SaleItem_ID;
 Else
  UPDATE SaleItem SET SaleItem_GrossAmount = SaleItem_LinePrice +
(SaleItem_LinePrice*SaleItem_TaxRate/100)) WHERE
SaleItem_ID=New.SaleItem_ID;
  UPDATE SaleItem SET SaleItem_TaxAmount =
(SaleItem_LinePrice*SaleItem_TaxRate/100)) WHERE
SaleItem_ID=New.SaleItem_ID;
 End
Else
  UPDATE SaleItem SET SaleItem_GrossAmount = SaleItem_LinePrice
WHERE SaleItem_ID=New.SaleItem_ID;
  UPDATE SaleItem SET SaleItem_TaxAmount = 0 WHERE
SaleItem_ID=New.SaleItem_ID;
End

If you think there's a way of doing that sort of thing - plus further nested
Ifs - in tables, rather than with triggers, then I'd bite your hand off to
hear it, because I can't see how to do it. I don't particularly want to do
it in the application logic if I can help it, because if I'm trying to
replicate this functionality in two applications (let's say C and PHP) then
there's a chance of having two different sets of logic if an update I
perform is correct in one and incorrect in another. 

Thanks,

Hamish

-- 
View this message in context: 
http://old.nabble.com/SET-NEW.FieldName-in-trigger-tp33413040p33413634.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SET NEW.FieldName in trigger

[Simon Slavin]

On 29 Feb 2012, at 11:06am, hsymington  wrote:

> Simon Slavin-3 wrote:
> 
>>> CREATE TRIGGER UpdateSaleItemDescription BEFORE INSERT ON SaleItem
>>> BEGIN
>>> SET NEW.SaleItem_Description='Fish';
>>> END;
>> 
>> Yes.  You can look at values using 'new.' but you cannot change them. 
>> However, you do not need to.  To perform such an operation as you list
>> above simply define a default value for the SaleItem_Description column.
> 
> Thanks, Simon. 
> 
> If I wanted to do
> CREATE TRIGGER UpdateSaleItemDescription AFTER INSERT ON SaleItem 
> BEGIN 
> UPDATE SaleItem SET SaleItem_Description='Fish' WHERE SaleItem_Type='1' AND
> SaleItem_ID=New.SaleItem_ID; 
> 
> UPDATE SaleItem SET SaleItem_Description='Chips' WHERE SaleItem_Type='2' AND
> SaleItem_ID=New.SaleItem_ID; 
> 
> END; 
> 
> ...then I couldn't use default values, because you can't make defaults
> conditional.

You could do that by using conditionals in the 'DEFAULT' definition.  So for 
instance you can do

CREATE TABLE SaleItem (...
SaleItem_Description DEFAULT (CASE WHEN SaleItem_Type='1' THEN 'Fish'
WHEN SaleItem_Type='2' THEN 'Chips'
ELSE 'Fried onion rings' END))

But see below: you probably shouldn't be doing this at all.

> (Background: I've got a database schema in the form of a text file, which
> some software reads and converts to a SQLite database. I also need php to be
> able to read that text file and convert it into a MySQL database. I'm trying
> to work out how to define triggers so that I can change the schema
> information and have it work for both MySQL and SQLite... and am getting a
> headache in the process!)

Give up.  Although the basics of SQL are present in all SQL engines, trying to 
make anything but simple SQL work in more than one SQL engine tends to lead 
only to ridiculously complicated code.  I'm serious: it just doesn't work.  If 
all you have is simple tables and indexes you're fine.  If you're trying to put 
complicated database logic into your table definitions you always have to make 
changes when you move from one SQL implementation to another.  So it's not 
worth even trying.

However, if the above is really what you're trying to do then I think you're 
doing it wrong.  You don't want TRIGGERs or even DEFAULTs.  If you really have 
some sort of correspondence between SaleItem_Type and SaleItem_Description then 
it belongs in its own table.  Store only the SaleItem_Type in this table.  Make 
another TABLE called ItemTypes with columns 'typeNumber' and 'description' and 
feed it (1, 'Fish'), (2, 'Chips') etc..  Then when you want to know what text 
is associated with a type, look it up in that TABLE using a JOIN or any other 
method.  That's the SQL way of doing it.

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

Re: [sqlite] SET NEW.FieldName in trigger

[hsymington]

Simon Slavin-3 wrote:
> 
>> CREATE TRIGGER UpdateSaleItemDescription BEFORE INSERT ON SaleItem
>> BEGIN
>> SET NEW.SaleItem_Description='Fish';
>> END;
> 
> Yes.  You can look at values using 'new.' but you cannot change them. 
> However, you do not need to.  To perform such an operation as you list
> above simply define a default value for the SaleItem_Description column.
> 

Thanks, Simon. 

If I wanted to do
CREATE TRIGGER UpdateSaleItemDescription AFTER INSERT ON SaleItem 
BEGIN 
UPDATE SaleItem SET SaleItem_Description='Fish' WHERE SaleItem_Type='1' AND
SaleItem_ID=New.SaleItem_ID; 

UPDATE SaleItem SET SaleItem_Description='Chips' WHERE SaleItem_Type='2' AND
SaleItem_ID=New.SaleItem_ID; 

END; 

...then I couldn't use default values, because you can't make defaults
conditional. 

(Background: I've got a database schema in the form of a text file, which
some software reads and converts to a SQLite database. I also need php to be
able to read that text file and convert it into a MySQL database. I'm trying
to work out how to define triggers so that I can change the schema
information and have it work for both MySQL and SQLite... and am getting a
headache in the process!)

Thanks,

Hamish
  
-- 
View this message in context: 
http://old.nabble.com/SET-NEW.FieldName-in-trigger-tp33413040p33413181.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SET NEW.FieldName in trigger

[Simon Slavin]

On 29 Feb 2012, at 10:46am, hsymington  wrote:

> CREATE TRIGGER UpdateSaleItemDescription BEFORE INSERT ON SaleItem
> BEGIN
> SET NEW.SaleItem_Description='Fish';
> END;
> 
> [snip]
> Is it correct that I can't do the first, or am I misreading the syntax? 

Yes.  You can look at values using 'new.' but you cannot change them.  However, 
you do not need to.  To perform such an operation as you list above simply 
define a default value for the SaleItem_Description column.

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

[sqlite] SET NEW.FieldName in trigger

[hsymington]

Hi -
In MySQL, I can do 

CREATE TRIGGER UpdateSaleItemDescription BEFORE INSERT ON SaleItem
BEGIN
SET NEW.SaleItem_Description='Fish';
END;

and I can't do

CREATE TRIGGER UpdateSaleItemDescription AFTER INSERT ON SaleItem
BEGIN
UPDATE SaleItem SET SaleItem_Description='Fish' WHERE
SaleItem_ID=New.SaleItem_ID;
END;
 
because that's trying to update the table that's already being updated, and
MySQL won't let you. 

SQLite appears to be the opposite: I can't do the first example in SQLite,
but I can do the second.

Is it correct that I can't do the first, or am I misreading the syntax? 

Best wishes,

Hamish
-- 
View this message in context: 
http://old.nabble.com/SET-NEW.FieldName-in-trigger-tp33413040p33413040.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] sqlite3.dll wrapper to import .csv file

[Simon Slavin]

On 29 Feb 2012, at 4:53am, Rick Guizawa  wrote:

> Hi All, I am using sqlite3.dll in my c# winform app, I was wondering if
> anyone knows how to import .csv file into sqlite db table using c#
> sqlite3.dll wrapper function.

SQLite has no functions for handling .csv files.

You can either write your own code to read the .csv file and make a database 
out of it, or you can use the sqlite3 command-line shell tool (which is its own 
stand-alone program, and not part of the SQLite library) to do it.  You can 
find the tool here:

http://www.sqlite.org/download.html

and documentation for it here:

http://www.sqlite.org/sqlite.html

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