[sqlite] SQLite in Citrix Lab Manager

[Ahmed Adil Huq]

I have recently found that Citrix Lab Manager has embeded SQLite database.  
This can be added to bigname users list in the sqlite.org website.

Google cache link:

http://209.85.229.132/search?q=cache:9Xe1I5zCKsQJ:support.citrix.com/article/CTX120223+citrix+lab+manager+sqlite&cd=1&hl=en&ct=clnk&gl=uk


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

Re: [sqlite] AIX test failures for 3.6.17

[Shane Harrelson]

On Tue, Aug 18, 2009 at 3:28 PM, Ken wrote:
> Running make test resulted in the following failures on aix 5.3
> Note that the CFLAGS=-DSQLITE_DISABLE_DIRSYNC was set for the make.
>
> 14 errors out of 40926 tests
> Failures on these tests: backup2-10 io-4.1 io-4.2.3 nan-1.1.2 nan-1.1.3 
> nan-1.1.4 nan-1.1.5 nan-1.1.5 nan-4.7 nan-4.8 nan-4.14 nan-4.15 nan-4.16 
> nan-4.17
>
> Any suggestions or reason why the io test would fail?
> io-3.3... Ok
> io-4.1...
> Expected: [3]
>     Got: [2]
> io-4.2.1... Ok
> io-4.2.2... Ok
> io-4.2.3...
> Expected: [3]
>     Got: [2]
> io-4.3.1... Ok
> io-4.3.2... Ok
>
> Thanks,
> Ken
>

Most Unix's support fsync()'ing directories, and the expected sync
numbers in io.test assume that if you're on a "Unix" platform, you're
going to be calling fsync() on the directory.  On AIX, which typically
doesn't support fsync() on directories (hence, your compiler option
-DSQLITE_DISABLE_DIRSYNC), the sync counts are different then what the
tests were expecting.

I've checked in an update to io.test which I think should correct the
test for you.

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

Re: [sqlite] Increment a value if constraint violated

[Igor Tandetnik]

andrew fabbro wrote:
> What is the most efficient way to code either a trigger or the app so
> that it increments a "count" field if the "id" field exists?
>
> For example, say you have a table with fields of "id" and "count".
>
> First row is an id of 3, so "count" is set to 1.
> Next row is an id of 4, so "count" is set to 1.
> Third row is an id of 3 again, so "count" is incremented to 2.

insert or replace into mytable(id, count)
values (:id, coalesce((select count from mytable where id=:id), 0) + 1);

Igor Tandetnik 



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

Re: [sqlite] use of parentheses when not arithmetically or logically required

[John Machin]

On 19/08/2009 11:26 AM, Simon Slavin wrote:

> DRH's post trumps mine, of course.  I'm surprised to find that  
> brackets are optimised out of WHERE evaluations. 

Why? In the OP's example (all AND operators) the parentheses are 
redundant. In SQL, AND and OR are not guaranteed to be short-circuited; 
both operands may be evaluated. In a case like (a+b)*(c+d) the 
parentheses are necessary otherwise a+b*c+d would be interpreted as 
a+(b*c)+d. In parsing any expression in any language, one would expect 
unless documented otherwise that there was no other guarantee of order 
of evaluation -- if your expression has side-effects you are on your 
own. Generally parentheses are ignored immediately their purpose 
(overriding operator precedence) has been fulfilled.


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

Re: [sqlite] INSERT with multiple VALUES clause?

[Kit]

2009/8/18 Terrence Brannon :
> Hello, when converting a .sql file containing MySQL INSERT clauses,
> one often runs into the problem that the MySQL INSERT can accept
> multiple VALUES arguments:
>
>INSERT INTO actor VALUES (1,'PENELOPE','GUINESS','2006-02-15
> 04:34:33'),(2,'NICK','WAHLBERG','2006-02-15
> 04:34:33'),(3,'ED','CHASE','2006-02-15 04:34:33')
>
>  whereas the SQLite INSERT can only take one...
>
> Is there any possibility of extending the syntax of the SQLite insert
> to accept multiple VALUES arguments?

INSERT INTO actor
SELECT 1,'PENELOPE','GUINESS','2006-02-15 04:34:33'
UNION ALL
SELECT 2,'NICK','WAHLBERG','2006-02-15 04:34:33'
UNION ALL
SELECT 3,'ED','CHASE','2006-02-15 04:34:33';
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] use of parentheses when not arithmetically or logically required

[Simon Slavin]

On 19 Aug 2009, at 12:01am, yaconsult wrote:

> Simon,
>
> Thank you for the optimization.  I'll switch to using between.  This
> particular database is used for generating some statistics and  
> generating
> reports, so performance is not that big an issue.

DRH's post trumps mine, of course.  I'm surprised to find that  
brackets are optimised out of WHERE evaluations.  In my defence I did  
mention EXPLAIN, which is the way to find this stuff out for yourself.

> When you talk about "clever use of an index", are you referring to a
> combined index or merely indices on each column?

SQLite is good at working out the minimum amount of work to do to find  
your results.  I thought that your second form would give it more  
leeway to be clever than the one with brackets.  But it turns out I  
was wrong.

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

[sqlite] Clarification of string pointer lifetime

[Shaun Seckman (Firaxis)]

Hey everyone,

The documentation for sqlite3_column_text() states that
the string returned will always be NULL and that the pointers are valid
until sqlite3_step(), sqlite3_reset() or sqlite3_finalize() is called.
Is this ONLY when the same prepared statement is used in those methods
or is it when those functions are called with ANY prepared statement
pointer?

 

-Shaun

 

 

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

Re: [sqlite] use of parentheses when not arithmetically or logically required

[D. Richard Hipp]

On Aug 18, 2009, at 5:26 PM, yaconsult wrote:
>
> My question is if it makes a difference to use parentheses when not
> logically required, as in the following example where the only  
> logical used
> is "and":
>
>select * from log
>where
>  (response >= 200 and response < 300)
>  and
>  (port >= 8444 and port <= 8459)
>
> Is there any difference in execution or performance if the  
> parentheses are
> present or not?  I'm guessing not, but would like confirmation.
>
>select * from log
>where
>  response >= 200
>  and response < 300
>  and port >= 8444
>  and port <= 8459
>

No.  Both queries are identical.  You can see this by prefixing them  
both with EXPLAIN and looking at the content of the generated prepared  
statement, and observing that the prepared statements are identical.

Using "x BETWEEN y AND z" instead of "x>=y AND x<=z" is unlikely to  
make any measurable performance difference in the case where x is a  
simple column value.  If x is an expensive subquery of some kind, it  
could make a difference since with BETWEEN x is only evaluated once,  
whereas it would be evaluated twice for "x>=y AND x<=z".  Other than  
the number of times that x is evaluated, there is no difference in  
BETWEEN and "x>=y AND x<=z".  If an index is used to evaluate the x  
expression then the two forms really are completely identical.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] use of parentheses when not arithmetically or logically required

[yaconsult]

Simon,

Thank you for the optimization.  I'll switch to using between.  This
particular database is used for generating some statistics and generating
reports, so performance is not that big an issue.

When you talk about "clever use of an index", are you referring to a
combined index or merely indices on each column?

Thanks for your input!
-- 
View this message in context: 
http://www.nabble.com/use-of-parentheses-when-not-arithmetically-or-logically-required-tp25033803p25035025.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] use of parentheses when not arithmetically or logically required

[Simon Slavin]

On 18 Aug 2009, at 10:26pm, yaconsult wrote:

> My question is if it makes a difference to use parentheses when not
> logically required, as in the following example where the only  
> logical used
> is "and":
>
>select * from log
>where
>  (response >= 200 and response < 300)
>  and
>  (port >= 8444 and port <= 8459)
>
> Is there any difference in execution or performance if the  
> parentheses are
> present or not?  I'm guessing not, but would like confirmation.
>
>select * from log
>where
>  response >= 200
>  and response < 300
>  and port >= 8444
>  and port <= 8459

These would, as you suspect, have different results when seen as  
SQLite queries.  One of them tests every record against two pieces of  
logic, the other uses four constraints each one of which might be  
implemented by clever use of an index.  You might want to use EXPLAIN  
on them to see what each one does.

But I mostly wanted to note that neither of these are the best way to  
do it.  You would probably be best using the BETWEEN operator:



SELECT * FROM log
WHERE response BETWEEN 200 AND 299
  AND port BETWEEN 8444 AND 8458

Note that I had to change two of the integers because it tests for  
'<=' not '='.  (I keep forgetting to do this in my own programming.)   
This format allows the query optimiser to make the best possible use  
of the constraints:



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

[sqlite] use of parentheses when not arithmetically or logically required

[yaconsult]

I have searched the list and understand the need for parentheses when
required by logical or arithmetic operations or subselects.

My question is if it makes a difference to use parentheses when not
logically required, as in the following example where the only logical used
is "and":

select * from log
where
  (response >= 200 and response < 300)
  and
  (port >= 8444 and port <= 8459)

Is there any difference in execution or performance if the parentheses are
present or not?  I'm guessing not, but would like confirmation.

select * from log
where
  response >= 200
  and response < 300
  and port >= 8444
  and port <= 8459

Thanks!
-- 
View this message in context: 
http://www.nabble.com/use-of-parentheses-when-not-arithmetically-or-logically-required-tp25033803p25033803.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] AIX test failures for 3.6.17

[Ken]

Running make test resulted in the following failures on aix 5.3
Note that the CFLAGS=-DSQLITE_DISABLE_DIRSYNC was set for the make.

14 errors out of 40926 tests
Failures on these tests: backup2-10 io-4.1 io-4.2.3 nan-1.1.2 nan-1.1.3 
nan-1.1.4 nan-1.1.5 nan-1.1.5 nan-4.7 nan-4.8 nan-4.14 nan-4.15 nan-4.16 
nan-4.17

Any suggestions or reason why the io test would fail?
io-3.3... Ok
io-4.1...
Expected: [3]
 Got: [2]
io-4.2.1... Ok
io-4.2.2... Ok
io-4.2.3...
Expected: [3]
 Got: [2]
io-4.3.1... Ok
io-4.3.2... Ok

Thanks,
Ken

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

Re: [sqlite] Increment a value if constraint violated

[Pavel Ivanov]

>> So executing the insert/update/delete statement places RESERVED lock
>> right from the start before executing nested selects?
>
> Yes.

Then I stand corrected. Thank you.


Pavel

On Tue, Aug 18, 2009 at 2:04 PM, Dan Kennedy wrote:
>
> On Aug 19, 2009, at 1:01 AM, Pavel Ivanov wrote:
>
>>> Cannot happen. If not executed within an implicit transaction, each
>>> SQL statement is effectively wrapped in a transaction all of its own.
>>
>> So executing the insert/update/delete statement places RESERVED lock
>> right from the start before executing nested selects?
>
> Yes.
>
> Dan.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Increment a value if constraint violated

[Dan Kennedy]

On Aug 19, 2009, at 1:01 AM, Pavel Ivanov wrote:

>> Cannot happen. If not executed within an implicit transaction, each
>> SQL statement is effectively wrapped in a transaction all of its own.
>
> So executing the insert/update/delete statement places RESERVED lock
> right from the start before executing nested selects?

Yes.

Dan.

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

Re: [sqlite] Increment a value if constraint violated

[Pavel Ivanov]

> Cannot happen. If not executed within an implicit transaction, each
> SQL statement is effectively wrapped in a transaction all of its own.

So executing the insert/update/delete statement places RESERVED lock
right from the start before executing nested selects?

Pavel

On Tue, Aug 18, 2009 at 1:54 PM, Dan Kennedy wrote:
>
> On Aug 19, 2009, at 12:35 AM, Pavel Ivanov wrote:
>
>> You can always do
>>
>> insert into table (id, count) values (?1, (select count(*) + 1 from
>> table where id = ?1))
>>
>> Though I'd be cautious about race condition that seems like possible
>> here when after select returned some value and before insert was made
>> another process made another insert with the same id...
>
> Cannot happen. If not executed within an implicit transaction, each
> SQL statement is effectively wrapped in a transaction all of its own.
>
> Dan.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Increment a value if constraint violated

[Dan Kennedy]

On Aug 19, 2009, at 12:54 AM, Dan Kennedy wrote:

>
> On Aug 19, 2009, at 12:35 AM, Pavel Ivanov wrote:
>
>> You can always do
>>
>> insert into table (id, count) values (?1, (select count(*) + 1 from
>> table where id = ?1))
>>
>> Though I'd be cautious about race condition that seems like possible
>> here when after select returned some value and before insert was made
>> another process made another insert with the same id...
>
> Cannot happen. If not executed within an implicit transaction, each
> SQL statement is effectively wrapped in a transaction all of its own.

Where I say "implicit transaction" above, I mean "explicit transaction".
Must be getting late...

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

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

Re: [sqlite] Increment a value if constraint violated

[Dan Kennedy]

On Aug 19, 2009, at 12:35 AM, Pavel Ivanov wrote:

> You can always do
>
> insert into table (id, count) values (?1, (select count(*) + 1 from
> table where id = ?1))
>
> Though I'd be cautious about race condition that seems like possible
> here when after select returned some value and before insert was made
> another process made another insert with the same id...

Cannot happen. If not executed within an implicit transaction, each
SQL statement is effectively wrapped in a transaction all of its own.

Dan.

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

Re: [sqlite] Increment a value if constraint violated

[Pavel Ivanov]

You can always do

insert into table (id, count) values (?1, (select count(*) + 1 from
table where id = ?1))

Though I'd be cautious about race condition that seems like possible
here when after select returned some value and before insert was made
another process made another insert with the same id. Looks like you
have to prepend it with BEGIN IMMEDIATE or BEGIN EXCLUSIVE.


Pavel

On Tue, Aug 18, 2009 at 1:06 PM, andrew fabbro wrote:
> What is the most efficient way to code either a trigger or the app so that
> it increments a "count" field if the "id" field exists?
>
> For example, say you have a table with fields of "id" and "count".
>
> First row is an id of 3, so "count" is set to 1.
> Next row is an id of 4, so "count" is set to 1.
> Third row is an id of 3 again, so "count" is incremented to 2.
>
> I could do this:
>
> 1. try insert
> 2. if SQLITE_CONSTRAINT, fetch the value of count
> 3. increment count
> 4. issue an UPDATE
>
> This seems wasteful...is there an efficient way to do this, say as a
> trigger?
>
> Thanks!
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rows where people connected in the past 2 weeks?

[Robert Citek]

To expand on things to try:

sqlite> select julianday('now');

sqlite> select julianday('2009-08-01');

sqlite> select julianday('now') - julianday('2009-08-01');

And maybe have a look here:

http://sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Regards,
- Robert

On Tue, Aug 18, 2009 at 1:25 PM, Pavel Ivanov wrote:
> Just try these and you'll see why.
>
> sqlite> select julianday('18-08-2009');
>
> sqlite> select julianday('now') - julianday('18-08-2009');
>
>
> Pavel
>
> On Tue, Aug 18, 2009 at 12:58 PM, Gilles Ganault 
> wrote:
>> Hello
>>
>> I thought this query would work to read a date column that holds the
>> DD-MM- date when the user last logged on, and check how many of
>> them logged on in the past two weeks for the last time:
>>
>> SELECT COUNT(id) FROM members WHERE (julianday('now') -
>> julianday(dateconnection)) < 15;
>>
>> This is returning no row, even though I know a lot of rows have a
>> connection date within the last two weeks.
>>
>> Any idea why this is wrong?
>>
>> Thank you.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rows where people connected in the past 2 weeks?

[Pavel Ivanov]

Just try these and you'll see why.

sqlite> select julianday('18-08-2009');

sqlite> select julianday('now') - julianday('18-08-2009');


Pavel

On Tue, Aug 18, 2009 at 12:58 PM, Gilles Ganault wrote:
> Hello
>
> I thought this query would work to read a date column that holds the
> DD-MM- date when the user last logged on, and check how many of
> them logged on in the past two weeks for the last time:
>
> SELECT COUNT(id) FROM members WHERE (julianday('now') -
> julianday(dateconnection)) < 15;
>
> This is returning no row, even though I know a lot of rows have a
> connection date within the last two weeks.
>
> Any idea why this is wrong?
>
> Thank you.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Increment a value if constraint violated

[Simon Slavin]

On 18 Aug 2009, at 6:06pm, andrew fabbro wrote:

> What is the most efficient way to code either a trigger or the app  
> so that
> it increments a "count" field if the "id" field exists?
>
> For example, say you have a table with fields of "id" and "count".
>
> First row is an id of 3, so "count" is set to 1.
> Next row is an id of 4, so "count" is set to 1.
> Third row is an id of 3 again, so "count" is incremented to 2.

Isn't there a way to express 'count' as a calculation, like 'max', so  
you don't have to store the value in a field ?

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

[sqlite] Increment a value if constraint violated

[andrew fabbro]

What is the most efficient way to code either a trigger or the app so that
it increments a "count" field if the "id" field exists?

For example, say you have a table with fields of "id" and "count".

First row is an id of 3, so "count" is set to 1.
Next row is an id of 4, so "count" is set to 1.
Third row is an id of 3 again, so "count" is incremented to 2.

I could do this:

1. try insert
2. if SQLITE_CONSTRAINT, fetch the value of count
3. increment count
4. issue an UPDATE

This seems wasteful...is there an efficient way to do this, say as a
trigger?

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

[sqlite] Rows where people connected in the past 2 weeks?

[Gilles Ganault]

Hello

I thought this query would work to read a date column that holds the
DD-MM- date when the user last logged on, and check how many of
them logged on in the past two weeks for the last time:

SELECT COUNT(id) FROM members WHERE (julianday('now') -
julianday(dateconnection)) < 15;

This is returning no row, even though I know a lot of rows have a
connection date within the last two weeks.

Any idea why this is wrong?

Thank you.

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

Re: [sqlite] INSERT with multiple VALUES clause?

[Jay A. Kreibich]

On Tue, Aug 18, 2009 at 09:42:53AM -0500, Beau Wilkinson scratched on the wall:
> >On Tue, Aug 18, 2009 at 08:28:13AM -0500, Beau Wilkinson scratched on the 
> >wall:

> > It is also worth noting that every non-SQL Relational language (e.g.
> >  Tutorial-D) I've looked at supports some form of multiple inserts, usually
> >  into multiple tables, with one command.  If you're a stickler for
> > constraints, and believe enforcement shouldn't be delayed (as most
> >  theory-heavy folks do), you have to have something like this.

> Doesn't what you said about constraints imply that the individual
> sub-INSERTs must be treated as if they all arrive at the database
> at once, e.g. for purposes of constraint checking? This will be a
> detail to consider if this gets implemented...

  The most common area where INSERTs start to trip over constraints is
  when you have a heavily entwined nest of foreign key constraints.
  SQLite doesn't support key constraints nativity, and the trigger-based
  workaround can't deal with constraint chains.  So the problem is
  actually greatly reduced in SQLite, but only because the constraint
  checking isn't there at all.

  Normally constraints are only enforced when a transaction is
  committed.  This is how one typically gets around any constraint
  problems.  Since "pure" SQL doesn't allow multiple INSERTs in a
  single statement, if you have a constraint situation that requires
  several INSERTs to add a valid set of records, you would wrap all the
  INSERT statements into a single transaction.  Since, in theory, the
  transaction isn't visible to other database users, everything is
  good-- assuming you can keep track of your own changes.

  The theory people are uncomfortable with this, however.  You really
  have to grok constraints to their fullest to appreciate the depth of
  this, but the basic idea is that if any constraint is EVER violated,
  all bets are off about the consistency and "correctness" of the whole
  database.  That sounds a bit excessive, but one must remember that
  the Relational Model is a formal mathematical system, so allowing
  temporary violation of constraints is similar to temporarily allowing
  zero plus one to equal something other than one while you're in the
  middle of a complex calculation.

  That's a different discussion for a different day, however.  The SQL
  model allows violations within an open constraint, under the
  assumption that the data isn't "really" part of the database until it
  is committed, at which point the data is checked to be sure it is
  valid.  As long as you understand the ramifications of that, it seems
  like a workable model to me.

  What all that means for this conversation is that a multi-valued
  INSERT issued outside outside of a transaction (i.e. in auto-commit
  mode) would play out the exact same as opening a transaction,
  applying several discrete INSERT statements, and closing the
  transaction.  So the code modifications should be minimal, since every
  command is issuing an implied BEGIN/END anyways.

  Similarly, if we're already inside a transaction, the INSERT should
  just play out as if it was several discrete INSERT statements.

  In both cases all of the sub-INSERTs "arrive at the database" at the
  same time, since the SQL definition of "arrive at the database" is
  when the transaction is committed.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT with multiple VALUES clause?

[John Machin]

On 18/08/2009 11:28 PM, Beau Wilkinson wrote:
> That said, if you're in posession of the source code,
 > you can certainly hack something up to support that.
 > A better option might be to pre-process the MySQL file
 > using C, Perl, XSLT (just kidding - don't use XSLT)
 > or whatever else you prefer for this kind of rote file manipulation

"rote" is relative; it's certainly just a flick of the wrist if you're 
willing to bet on there being no ')' characters in the text literals, 
otherwise it gets a bit hairy...

Here's an attempt at something fairly general using Python regular 
expressions; just point this at the remainder of the statement after the 
  VALUES keyword:

import re
value_literal = r"""
 (?:
 ' (?: [^'] | '' ) * ' # text literal
 |
 [^,)\s] + # any other literal
 )
 """
value_list_re = r"\(\s*LIT\s*(?:,\s*LIT\s*)*\)".replace("LIT", 
value_literal)
data = """
 (1,'PENELOPE','GUINESS','2006-02-15 04:34:33'), (2,'NICK','WAHLBERG',
 '2006-02-15 04:34:33'),(3,'ED','CHASE','2006-02-15 04:34:33'),
 (4,'Seamus', 'O''Reilly'),(4.1,x'f00baa'),(5678),
 (6, 'William','Pitt (the Elder)'),(6.1, 'Willie', 'Pitt (the 
Younger)'),
 (  7  , 'spaced'  ,  'out'  )
 """
rx = re.compile(value_list_re, re.VERBOSE)
for vlist in rx.findall(data):
 print vlist

and here's the output:
(1,'PENELOPE','GUINESS','2006-02-15 04:34:33')
(2,'NICK','WAHLBERG',
 '2006-02-15 04:34:33')
(3,'ED','CHASE','2006-02-15 04:34:33')
(4,'Seamus', 'O''Reilly')
(4.1,x'f00baa')
(5678)
(6, 'William','Pitt (the Elder)')
(6.1, 'Willie', 'Pitt (the Younger)')
(  7  , 'spaced'  ,  'out'  )

Cheers,
John


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

Re: [sqlite] INSERT with multiple VALUES clause?

[Beau Wilkinson]

>On Tue, Aug 18, 2009 at 08:28:13AM -0500, Beau Wilkinson scratched on the wall:
>> To me that seems like an annoying deviation from standard practice.
>> Do other databases support such an INSERT?
>
>Yes.  MySQL, PostgreSQL, and SQLServer all support this syntax.
>
>Oracle supports a slightly modified syntax that allows multple rows
>to be inserted into multiple tables with one INSERT ALL.  The syntax
>for that is basically INSERT ALL INTO... INTO... INTO...
>
> DB2 and HSQL (and SQLite) do not support this.
>
> Personally, I think the first syntax would be a nice enhancement.
>
> It is also worth noting that every non-SQL Relational language (e.g.
>  Tutorial-D) I've looked at supports some form of multiple inserts, usually
>  into multiple tables, with one command.  If you're a stickler for
> constraints, and believe enforcement shouldn't be delayed (as most
>  theory-heavy folks do), you have to have something like this.

I definitely see what you mean about constraints; I guess when I was doing 
heavy (Oracle) database programming I must have been sidestepping the issue by 
turning things constraints off selectively, or maybe just inserting things in a 
certain order.

Doesn't what you said about constraints imply that the individual sub-INSERTs 
must be treated as if they all arrive at the database at once, e.g. for 
purposes of constraint checking? This will be a detail to consider if this gets 
implemented...

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT with multiple VALUES clause?

[Jay A. Kreibich]

On Tue, Aug 18, 2009 at 08:28:13AM -0500, Beau Wilkinson scratched on the wall:
> To me that seems like an annoying deviation from standard practice.
> Do other databases support such an INSERT? 

  Yes.  MySQL, PostgreSQL, and SQLServer all support this syntax.

  Oracle supports a slightly modified syntax that allows multple rows
  to be inserted into multiple tables with one INSERT ALL.  The syntax
  for that is basically INSERT ALL INTO... INTO... INTO...

  DB2 and HSQL (and SQLite) do not support this.

  Personally, I think the first syntax would be a nice enhancement.



  It is also worth noting that every non-SQL Relational language (e.g.
  Tutorial-D) I've looked at supports some form of multiple inserts, usually
  into multiple tables, with one command.  If you're a stickler for
  constraints, and believe enforcement shouldn't be delayed (as most
  theory-heavy folks do), you have to have something like this.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT with multiple VALUES clause?

[Beau Wilkinson]

To me that seems like an annoying deviation from standard practice. Do other 
databases support such an INSERT? Is it envisioned by standards? I suspect the 
answer is "no" in both cases, and this is a classic example of how "less" 
functionality is actually "more" useful.

That said, if you're in posession of the source code, you can certainly hack 
something up to support that. A better option might be to pre-process the MySQL 
file using C, Perl, XSLT (just kidding - don't use XSLT) or whatever else you 
prefer for this kind of rote file manipulation


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Terrence Brannon [scheme...@gmail.com]
Sent: Tuesday, August 18, 2009 7:50 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] INSERT with multiple VALUES clause?

Hello, when converting a .sql file containing MySQL INSERT clauses,
one often runs into the problem that the MySQL INSERT can accept
multiple VALUES arguments:

INSERT INTO actor VALUES (1,'PENELOPE','GUINESS','2006-02-15
04:34:33'),(2,'NICK','WAHLBERG','2006-02-15
04:34:33'),(3,'ED','CHASE','2006-02-15 04:34:33')


 whereas the SQLite INSERT can only take one...

Is there any possibility of extending the syntax of the SQLite insert
to accept multiple VALUES arguments?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT with multiple VALUES clause?

[Terrence Brannon]

Hello, when converting a .sql file containing MySQL INSERT clauses,
one often runs into the problem that the MySQL INSERT can accept
multiple VALUES arguments:

INSERT INTO actor VALUES (1,'PENELOPE','GUINESS','2006-02-15
04:34:33'),(2,'NICK','WAHLBERG','2006-02-15
04:34:33'),(3,'ED','CHASE','2006-02-15 04:34:33')


 whereas the SQLite INSERT can only take one...

Is there any possibility of extending the syntax of the SQLite insert
to accept multiple VALUES arguments?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Re triving the database name from the sqlite pointer ....

[Simon Davies]

2009/8/18 Atul_Vaidya :
>
> Hi,
>   I have a Sqlite3 pointer. Is there any way to get the filename of this
> pointer ?

Execute "PRAGMA database_list;"

See http://www.sqlite.org/pragma.html#pragma_database_list

> Regards,
> Atul

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

[sqlite] Re triving the database name from the sqlite pointer ....

[Atul_Vaidya]

Hi,
   I have a Sqlite3 pointer. Is there any way to get the filename of this
pointer ?
Regards,
Atul
-- 
View this message in context: 
http://www.nabble.com/Retriving-the-database-name-from-the-sqlite-pointer--tp25020127p25020127.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