Re: [sqlite] integrity_check: Beating the system

[Dan]

> All:
> I need to check a database for readability before my application
> starts.  I was originally going to keep an MD5 on the database and
> check it each time at powerup.  This seems to take a great deal of
> time so instead I thought abou having the database do an integrity
> check at powerup, however this too takes a great deal of time.  My
> last idea was to issue a set of simple select statements against the
> database and check if they are successful.  I realize this doesnt
> 'guarantee' my data is fine like an MD5 would, nor does it really
> validate the integrity of the database like an integrity check
> would but can I assume to some degree of comfort that if these
> select statements succeed then I can access these tables in the
> database error free?

Probably. But there is always the possibility that an index has
become corrupted. Linear scans of database tables won't touch the
indexes, so you won't know for sure.

Another option, which is clearly marked as an experimental feature
in the source code (interpret that as you will), is "PRAGMA  
quick_check".
This is similar to integrity_check, but not as rigorous or time
consuming.

Dan.




> I ran some test cases (I know you guys and gals like actual numbers
> and not theory), and came up with the following
> sqlite3 'pragma integrity_check'
> real0m 11.20s
> user0m 1.85s
> sys 0m 8.70s
>
> md5sum -c ...
> real0m 10.07s
> user0m 1.32s
> sys 0m 8.16s
>
> sqlite3 'SELECT STATEMENTS FROM IMPORTANT TABLES'
> real0m 2.34s
> user0m 0.19s
> sys 0m 1.74s
>
>
> Now for clarity The database contains AV metadata and data.  I
> have tables such as ImageData, ImageDescription, AudioData, and so on.
>  I also have tables that are not 'important' (suffice it to say, I
> wont access them during runtime under normal conditions).  I care
> about the AV data being accessible I am optimistically assuming
> that if the data is corrupt, the image will still display (with
> perhaps some bad pixels) and / or the audio has some unwanted clicks
> or pops.
>
>
> The big question Using a group of select statements to check the
> database for accessibility seem reasonable?  Or am I taking a large
> gamble doing so?
> ___
> 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] integrity_check: Beating the system

[Rich Rattanni]

> Couldn't you go ahead and do your quick check on startup and then do the
> integrity check later when the database is otherwise unoccupied?

I was thinking of doing a hybrid as you suggested.

> If your database is smaller than system memory then there is also value
> in just reading the entire file so that it is cached by the OS which
> will cause initial queries to be a lot quicker because they won't have
> to page in things from disk.

Yea, I have heard of the old 'cat myfile > /dev/null' to get pages
cached in the OS.
I didn't include the numbers, but I tested that as well and this takes
about 9 seconds
(reading the whole file to dev/null).
I then performed both tests above and the time was slightly more than
just flat out
issuing the command.

> If you look at your timing figures you can see that the integrity check
> and md5sum are spending all their time in system - ie reading the file
> into memory.  You may find a more effective way of reading into memory
> using appropriate block sizes, asynchronous I/O etc.  You can also tweak
> SQLite block sizes to match OS block sizes.

I will look into that.  Thank you for your feedback!

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

Re: [sqlite] Client/Srever SQLite

[Alex Katebi]

slowness is fixed. Can't tell the difference between client/server speed
from library.

On Sat, Jun 14, 2008 at 8:32 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:

> Hi All,
>
>Looks like there is some interest. I will announce when I release it.
> Currently I am developing an interactive user shell client. This shell is
> used for my client/server development. It can also be embedded for any
> clients user interface.
> The request/response is a little slow for some reason. I need to fix this
> issue.
>
> Thanks,
> -Alex
>
>
>
> On Mon, Jun 2, 2008 at 11:40 AM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
>
>>
>> Hi All,
>>
>>   I am using remote procedure calls (RPC) for SQLite in my application. I
>> have implemented a few SQLite RPC functions that I needed successfully.
>> I am wondering if there are other people like me who need this.
>> If there are enough people who could benefit from this I can make it
>> available as an open source public domain software.
>> Then people can add more functions as needed.
>>
>> Thanks,
>> -Alex
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] integrity_check: Beating the system

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Rich Rattanni wrote:
> This seems to take a great deal of
> time so instead I thought abou having the database do an integrity
> check at powerup, however this too takes a great deal of time.  

Couldn't you go ahead and do your quick check on startup and then do the
integrity check later when the database is otherwise unoccupied?

If your database is smaller than system memory then there is also value
in just reading the entire file so that it is cached by the OS which
will cause initial queries to be a lot quicker because they won't have
to page in things from disk.

If you look at your timing figures you can see that the integrity check
and md5sum are spending all their time in system - ie reading the file
into memory.  You may find a more effective way of reading into memory
using appropriate block sizes, asynchronous I/O etc.  You can also tweak
SQLite block sizes to match OS block sizes.

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

iD8DBQFIVxm5mOOfHg372QQRAj5dAJ0bOWxUKVN0SsEIk/ESrRDBTJtkjgCffyG/
Yo4WR6YTD3qb5u3lTaL7ivU=
=Bn+D
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 to SQLite2?

[David Baird]

On Mon, Jun 16, 2008 at 6:18 PM, Gilles Ganault <[EMAIL PROTECTED]> wrote:
> We have an application that can only read SQLite2 databases. Is there
> an easy way to convert a SQLite3 database file into the SQLite2
> format?

This might work:

echo .dump | sqlite3 input.db | sqlite output.db

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

[sqlite] SQLite3 to SQLite2?

[Gilles Ganault]

Hello

We have an application that can only read SQLite2 databases. Is there
an easy way to convert a SQLite3 database file into the SQLite2
format?

Thank you.

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

Re: [sqlite] Display all the columns of the table in single line.

[Jay A. Kreibich]

On Mon, Jun 16, 2008 at 04:52:56PM -0700, Joanne Pham scratched on the wall:
> Thanks Igor,
> Do I need to upgrade to latest vresion of SQLite?
> I have to rebuild the SQLite library again. What version that I 
> need to upgrade to for group_concat function

  group_concat() became official in 3.5.4:

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

   -j


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

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Display all the columns of the table in single line.

[Joanne Pham]

Thanks Igor,
Do I need to upgrade to latest vresion of SQLite?
I have to rebuild the SQLite library again. What version that I need to upgrade 
to for group_concat function
Thanks,
JP



- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, June 16, 2008 3:46:20 PM
Subject: Re: [sqlite] Display all the columns of the table in single line.

Joanne Pham <[EMAIL PROTECTED]> wrote:
> I got this error message. I had 3.5.2 SQLite.
> sqlite> select group_concat(appName) from CDB.appMapTable;
> SQL error: no such function: group_concat

Upgrade to more recent SQLite version. group_concat is fairly new.

Igor Tandetnik 



___
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] Fwd: SQLite bug on AFP?

[BareFeet]

Can the Mac OS X locking problem please be fixed in the standard build  
and source code?

See below.

Thanks,
Tom
BareFeet

  
From: BareFeet <[EMAIL PROTECTED]>
Date: 12 June 2008 10:50:40 AM
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] SQLite bug on AFP?

Following up:

>>> I think the solution might be as simple as compiling with -
>>> DSQLITE_ENABLE_LOCKING_STYLE=1.  This option only works on a Mac.
>>> It enables some Apple-contributed code that does file locking that
>>> works on AFP as well as on other network filesystems that the Mac
>>> supports.
>>

AFAIK, this option is:

1. Enabled in the SQLite bundled on Mac OS X systems.

2. Disabled in the SQLite binary for Mac OS X on the SQLite web site:
http://www.sqlite.org/download.html
http://www.sqlite.org/sqlite3-3_5_9-osx-x86.bin.gz

3. Disabled in the source code.

Can you please tell me:

Can you please enable this by default in the Mac OS X binary and
source code? This would help with compatibility between the behavior
of the default SQLite install and any updates. For instance, at least
one JDBC developer (which affects at least a dozen SQLite GUI tools on
the Mac) will only include the locking fix if the "standard" Mac OS
binary also includes it.

Thanks,
Tom
BareFeet

  --
SQLite GUI tools for Mac OS X compared at:
http://www.tandb.com.au/sqlite/compare/

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

[sqlite] temp_store_directory issue with SQLite 3.5.9

[Arun Bhalla]

Hello,

We came across an issue last week with SQLite 3.5.9 on Windows.  A 
program stopped working on Windows when we upgraded from SQLite 3.4.1 to 
SQLite 3.5.9.  It turned out that the cause was that SQLite 3.5.9 (and 
perhaps SQLite 3.5.x in general) was not fully honoring the 
temp_store_directory pragma.  That is, the pathname for the pager's 
statement journal (Pager::zStmtJrnl) is computed when the pager is 
opened but not necessarily recomputed when the temp_store_directory is 
changed, so the pager may try to use a different directory than 
intended.  This was aggravated by some bug (apparently in MinGW) where 
GetTempPath() is only cycling through the TMP, TEMP, and USERPROFILE 
environment variables to find a viable temporary folder when running as 
Administrator (or perhaps a user in the Administrators group).

My fix was to compute Pager::zStmtJrnl in pagerStmtBegin(), right before 
opening the statement journal.  It appears that some rearchitecture in 
SQLite 3.6.0 (http://www.sqlite.org/cvstrac/chngview?cn=5190) removes 
this bug, so I won't submit a patch, but I'll be happy to provide one 
should anyone request it.

By the way, it seems a little bit questionable that 
sqlite3_temp_directory is a global, and hence the temp_store_directory 
pragma is also global, affecting all databases opened within the same 
process.

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

Re: [sqlite] Display all the columns of the table in single line.

[Igor Tandetnik]

Joanne Pham <[EMAIL PROTECTED]> wrote:
> I got this error message. I had 3.5.2 SQLite.
> sqlite> select group_concat(appName) from CDB.appMapTable;
> SQL error: no such function: group_concat

Upgrade to more recent SQLite version. group_concat is fairly new.

Igor Tandetnik 



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

Re: [sqlite] Source code for 3.4.2

[Eric Holmberg]

Okay, I found an old post on mail-archive.com
(http://www.mail-archive.com/sqlite-users@sqlite.org/msg02844.html)
which stated that I should just use the date.

So for version 3.4.2, I used 2007-08-13 18:00 UTC which is marked at
Version 3.4.2 according to the timeline of the wiki (see
http://www.sqlite.org/cvstrac/timeline?d=30=2007-Aug-14=2=sqlite;
s=0=1=1).  Let me know if I'm off base here.

Actual command used:

> cvs update -D '2007-08-13 18:00:00 UTC' 

Thanks,

Eric Holmberg
Applications Engineer, Arrow Electronics



From: Eric Holmberg 
Sent: Monday, June 16, 2008 2:48 PM
To: 'sqlite-users@sqlite.org'
Subject: Source code for 3.4.2


Sorry ahead of time for a silly question, but how do I get
version 3.4.2 of the source code?  I didn't see any tags in CVS (but I'm
not a CVS user, so that may be user error on my part).
 
Any help with the exact command would be appreciated.
 
Thanks,
 
Eric Holmberg
Applications Engineer, Arrow Electronics
 

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

[sqlite] Strange issue with sqlite3_column_text16 on Mac

[Ben Frech]

I'm using sqlite3 for the first time and I've been having an aggravating
problem I hope someone can help with.

I'm doing a simple query on a database I created using the sqlite3 command
line utility. sqlite3_column_text16 produces garbage on Mac OS 10.5 using
XCode 3, but with windows xp and Visual Studio 2005 the same code produces
the expected results from the same database. The other sqlite_column
functions I've tested yield the expected results on both platforms.

In each case I compiled a static library from the newest Sqlite 3.5.9
sources. When the program executes sqlite reports no errors and calls to
sqlite3_column_bytes16() after sqlite3_column_text16() yield the same
(correct) count regardless of OS.

Thanks in advance for helping.

--
Ben Frech
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Display all the columns of the table in single line.

[Joanne Pham]

Hi Igor,
I got this error message. I had 3.5.2 SQLite.
sqlite> select group_concat(appName) from CDB.appMapTable;
SQL error: no such function: group_concat
Any ideas what was the problem here.
Thanks,
JP



- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, June 13, 2008 7:45:08 PM
Subject: Re: [sqlite] Display all the columns of the table in single line.

"Joanne Pham" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> I ran the following select :
> select appName from appMapTable
> but I would like all the appName return as single row and seperate by
> comma. Can we do that using sqlite commands.

select group_concat(appName) from appMapTable;

Igor Tandetnik 



___
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] Segmentation Fault when using mod_python / mod_wsgi

[Eric Holmberg]

I'm trying to solve a RedHat Enterprise Linux 4 issue where I have
built, from source, Python 2.5.2, mod_python v3.2.8, and SQLite 3.5.9.
SQLite works fine in the Python 2.5.2 interpreter, but when running from
mod_python (I also tried mod_wsgi), I get a segmentation fault in the
Python file Objects/dictobject.c:571 when running a query.

I would like to try SQLite version 3.4.2 to get to a known setup just to
verify the problem isn't being caused elsewhere.

Any ideas are welcome.
 


Python test code

 
"""\
This program verifies that mod_python is able to use the SQLite module
integrated into
Python.
"""
import sqlite3
 
from mod_python import apache
 
def handler(req):
req.content_type = 'text/plain'
req.send_http_header()
 
# query SQLite3
req.write('Opening database connection ')
con = sqlite3.connect('/rh241/u01/TestWsgiPylons/test.db')
 
req.write('[OK]\r\nQuerying database ')

# THIS CAUSES A SEGMENTATION FAULT (see GDB snippet below)
cur = con.execute('select * from test')
 
req.write('[OK]\r\nFetching result set')
results = cur.fetchall()
req.write('[OK]\r\n')
req.write('Query results = ' + str(results) + '\n')
 

return apache.OK

--
GDB Output
--
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread -1209055008 (LWP 25808)]
PyDict_GetItem (op=0x0, key=0xb7ce82cc) at Objects/dictobject.c:571
571 if (!PyDict_Check(op))
(gdb)



Thanks,
 
Eric Holmberg
Applications Engineer, Arrow Electronics
Engineering Solutions Center
Denver, Colorado
ESC:  877-ESC-8950, [EMAIL PROTECTED]  
Direct:  303-824-4537, [EMAIL PROTECTED]
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Windows VC++ 2008 Express Edition compile howto?

[Stephen Woodbridge]

Hi all,

I would like to compile sqlite and spatialite using Windows VC++ 2008 
Express Edition. I looked around on the website and wiki but knowing 
almost nothing about these tools there was no obvious answer to me.

Does anyone have a step by step howto compile sqlite with VC++ 2008 
Express Edition that they can share. Shouldn't we also have a wiki or 
documentation page for this?

Likewise I need to compile the rtree extension. If you can include that 
step I am pretty sure I can mirror that to figure out the Spatialite 
extensions.

Thanks for any help you can provide to get me started.

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

[sqlite] Source code for 3.4.2

[Eric Holmberg]

Sorry ahead of time for a silly question, but how do I get version 3.4.2
of the source code?  I didn't see any tags in CVS (but I'm not a CVS
user, so that may be user error on my part).
 
Any help with the exact command would be appreciated.
 
Thanks,
 
Eric Holmberg
Applications Engineer, Arrow Electronics
Engineering Solutions Center
Denver, Colorado
ESC:  877-ESC-8950, [EMAIL PROTECTED]  
Direct:  303-824-4537, [EMAIL PROTECTED]
 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DB does not get updated after many writes

[Igor Tandetnik]

sethuarun
<[EMAIL PROTECTED]> wrote:
> I have a peculiar problem with sqlite library. I built an application
> using sqlite for DB operations. The application keeps updating the
> DB. Everything goes fine for around one or two days. But after that
> DB does not get updated at all.

At this point, do you by any chance have .journal file 
constantly present on the hard drive in the same folder where 
 file is? My guess would be you have a bug in your code 
whereby you open a transaction and never close it.

Igor Tandetnik



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

[sqlite] Nested select performance issues

[Bryan Hansen]

Hello,

I am developing a small app for determining the available combinations
of reagents stored in a sqlite database where no two reagents have the
same conjugate. I have tried two approaches to this query, one has
very good performance and the other is very poor. I am trying to
understand why the second has such bad performance. Both queries
return the correct answer.

The first option creates temp tables populated from select statements,
then a cross join between all of the temp tables with a big where
clause to get just the rows where no conjugate appears more than once.
This query executes in < 2 seconds.

CREATE TEMP TABLE 'CD4'  AS SELECT Conjugate FROM Catalog WHERE
Species IN ('Human') AND Conjugate NOT IN ('UNLB', 'Purified') AND
ProductName='CD4';
CREATE TEMP TABLE 'CD19' AS SELECT Conjugate FROM Catalog WHERE
Species IN ('Human') AND Conjugate NOT IN ('UNLB', 'Purified') AND
ProductName='CD19';
CREATE TEMP TABLE 'CD20' AS SELECT Conjugate FROM Catalog WHERE
Species IN ('Human') AND Conjugate NOT IN ('UNLB', 'Purified') AND
ProductName='CD20';
CREATE TEMP TABLE 'CD21' AS SELECT Conjugate FROM Catalog WHERE
Species IN ('Human') AND Conjugate NOT IN ('UNLB', 'Purified') AND
ProductName='CD21';
CREATE TEMP TABLE 'CD22' AS SELECT Conjugate FROM Catalog WHERE
Species IN ('Human') AND Conjugate NOT IN ('UNLB', 'Purified') AND
ProductName='CD22';
CREATE TEMP TABLE 'CD23' AS SELECT Conjugate FROM Catalog WHERE
Species IN ('Human') AND Conjugate NOT IN ('UNLB', 'Purified') AND
ProductName='CD23';
CREATE TEMP TABLE 'CD24' AS SELECT Conjugate FROM Catalog WHERE
Species IN ('Human') AND Conjugate NOT IN ('UNLB', 'Purified') AND
ProductName='CD24';

SELECT count(*) FROM
'CD4', 'CD19', 'CD20', 'CD21', 'CD22', 'CD23', 'CD24'

WHERE 'CD4'.Conjugate <> 'CD19'.Conjugate AND 'CD4'.Conjugate <>
'CD20'.Conjugate AND 'CD4'.Conjugate <> 'CD21'.Conjugate AND
'CD4'.Conjugate <> 'CD22'.Conjugate AND 'CD4'.Conjugate <>
'CD23'.Conjugate AND 'CD4'.Conjugate <> 'CD24'.Conjugate AND
'CD19'.Conjugate <> 'CD20'.Conjugate AND 'CD19'.Conjugate <>
'CD21'.Conjugate AND 'CD19'.Conjugate <> 'CD22'.Conjugate AND
'CD19'.Conjugate <> 'CD23'.Conjugate AND 'CD19'.Conjugate <>
'CD24'.Conjugate AND 'CD20'.Conjugate <> 'CD21'.Conjugate AND
'CD20'.Conjugate <> 'CD22'.Conjugate AND 'CD20'.Conjugate <>
'CD23'.Conjugate AND 'CD20'.Conjugate <> 'CD24'.Conjugate AND
'CD21'.Conjugate <> 'CD22'.Conjugate AND 'CD21'.Conjugate <>
'CD23'.Conjugate AND 'CD21'.Conjugate <> 'CD24'.Conjugate AND
'CD22'.Conjugate <> 'CD23'.Conjugate AND 'CD22'.Conjugate <>
'CD24'.Conjugate AND 'CD23'.Conjugate <> 'CD24'.Conjugate

ORDER BY 'CD4'.Conjugate,'CD19'.Conjugate, 'CD20'.Conjugate,
'CD21'.Conjugate, 'CD22'.Conjugate, 'CD23'.Conjugate,
'CD24'.Conjugate;

DROP TABLE 'CD4';
DROP TABLE 'CD19';
DROP TABLE 'CD20';
DROP TABLE 'CD21';
DROP TABLE 'CD22';
DROP TABLE 'CD23';
DROP TABLE 'CD24';

The second approach uses nested select statements rather than temp
tables. The logic is the same as the query above, only that the temp
tables have been replaced by the equivalent select statements. The
performance of this query is horrible, executing in > 3 minutes.

SELECT count(*) FROM
  (SELECT Conjugate FROM Catalog WHERE Species IN ('Human') AND
Conjugate NOT IN ('UNLB', 'Purified') AND ProductName='CD4') AS 'CD4',
  (SELECT Conjugate FROM Catalog WHERE Species IN ('Human') AND
Conjugate NOT IN ('UNLB', 'Purified') AND ProductName='CD19') AS
'CD19',
  (SELECT Conjugate FROM Catalog WHERE Species IN ('Human') AND
Conjugate NOT IN ('UNLB', 'Purified') AND ProductName='CD20') AS
'CD20',
  (SELECT Conjugate FROM Catalog WHERE Species IN ('Human') AND
Conjugate NOT IN ('UNLB', 'Purified') AND ProductName='CD21') AS
'CD21',
  (SELECT Conjugate FROM Catalog WHERE Species IN ('Human') AND
Conjugate NOT IN ('UNLB', 'Purified') AND ProductName='CD22') AS
'CD22',
  (SELECT Conjugate FROM Catalog WHERE Species IN ('Human') AND
Conjugate NOT IN ('UNLB', 'Purified') AND ProductName='CD23') AS
'CD23',
  (SELECT Conjugate FROM Catalog WHERE Species IN ('Human') AND
Conjugate NOT IN ('UNLB', 'Purified') AND ProductName='CD24') AS
'CD24'

WHERE 'CD4'.Conjugate <> 'CD19'.Conjugate AND 'CD4'.Conjugate <>
'CD20'.Conjugate AND 'CD4'.Conjugate <> 'CD21'.Conjugate AND
'CD4'.Conjugate <> 'CD22'.Conjugate AND 'CD4'.Conjugate <>
'CD23'.Conjugate AND 'CD4'.Conjugate <> 'CD24'.Conjugate AND
'CD19'.Conjugate <> 'CD20'.Conjugate AND 'CD19'.Conjugate <>
'CD21'.Conjugate AND 'CD19'.Conjugate <> 'CD22'.Conjugate AND
'CD19'.Conjugate <> 'CD23'.Conjugate AND 'CD19'.Conjugate <>
'CD24'.Conjugate AND 'CD20'.Conjugate <> 'CD21'.Conjugate AND
'CD20'.Conjugate <> 'CD22'.Conjugate AND 'CD20'.Conjugate <>
'CD23'.Conjugate AND 'CD20'.Conjugate <> 'CD24'.Conjugate AND
'CD21'.Conjugate <> 'CD22'.Conjugate AND 'CD21'.Conjugate <>
'CD23'.Conjugate AND 'CD21'.Conjugate <> 'CD24'.Conjugate AND
'CD22'.Conjugate <> 'CD23'.Conjugate AND 'CD22'.Conjugate <>
'CD24'.Conjugate AND 'CD23'.Conjugate <> 'CD24'.Conjugate

ORDER BY 

Re: [sqlite] SQL question

[Darren Duncan]

Gregor, why did you do that more complicated version with the subquery and 
sorting et al?  The short version that RBS would have worked a lot better; 
you just need to say?

   update binary_report_fmt
   set column_id = column_id + 1
   where column_id > 1;

... and then insert a new row with column_id = 2.  All that other stuff you 
did just makes things unnecessarily more complicated, and possibly buggy.

On a related matter, UPDATE statements are atomic operations, so the fact 
that the id is a primary key doesn't matter.  Since you're incrementing all 
the id values simultaneously, there are no duplicate values at any time, so 
the primary key constraint would remain happy.

-- Darren Duncan

Gregor Brandt wrote:
> Hi, thanks this is great.  Of course I forgot to mention that the id  
> is a primary key, so I get an error about duplicate primary keys.
> 
> I tried this:
> 
> update binary_report_fmt set column_id = column_id + 1 where column_id  
> = (select column_id from binary_report_fmt where column_id >= 3 order  
> by column_id desc);
> 
> but it only updates the last item.   I guess I can make it a non- 
> primary key..then it works perfectly.
> 
> Gregor
> 
> On 16-Jun-08, at 10:04 AM  , [EMAIL PROTECTED] wrote:
> 
>> update
>> table
>> set id = id + 1
>> WHERE
>> id > 1
>>
>> RBS
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] integrity_check: Beating the system

[Rich Rattanni]

All:
I need to check a database for readability before my application
starts.  I was originally going to keep an MD5 on the database and
check it each time at powerup.  This seems to take a great deal of
time so instead I thought abou having the database do an integrity
check at powerup, however this too takes a great deal of time.  My
last idea was to issue a set of simple select statements against the
database and check if they are successful.  I realize this doesnt
'guarantee' my data is fine like an MD5 would, nor does it really
validate the integrity of the database like an integrity check
would but can I assume to some degree of comfort that if these
select statements succeed then I can access these tables in the
database error free?

I ran some test cases (I know you guys and gals like actual numbers
and not theory), and came up with the following
sqlite3 'pragma integrity_check'
real0m 11.20s
user0m 1.85s
sys 0m 8.70s

md5sum -c ...
real0m 10.07s
user0m 1.32s
sys 0m 8.16s

sqlite3 'SELECT STATEMENTS FROM IMPORTANT TABLES'
real0m 2.34s
user0m 0.19s
sys 0m 1.74s


Now for clarity The database contains AV metadata and data.  I
have tables such as ImageData, ImageDescription, AudioData, and so on.
 I also have tables that are not 'important' (suffice it to say, I
wont access them during runtime under normal conditions).  I care
about the AV data being accessible I am optimistically assuming
that if the data is corrupt, the image will still display (with
perhaps some bad pixels) and / or the audio has some unwanted clicks
or pops.


The big question Using a group of select statements to check the
database for accessibility seem reasonable?  Or am I taking a large
gamble doing so?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Kees Nuyt]

On Mon, 16 Jun 2008 10:36:18 -0600, you wrote:

>Hi, thanks this is great.  Of course I forgot to mention that the id  
>is a primary key, so I get an error about duplicate primary keys.
>
>I tried this:
>
>update binary_report_fmt set column_id = column_id + 1 where column_id  
>= (select column_id from binary_report_fmt where column_id >= 3 order  
>by column_id desc);
>
>but it only updates the last item.   I guess I can make it a non- 
>primary key..then it works perfectly.
>
>Gregor

Add a large value to all column_id that have to be changed,
then subtract that same value minus 1.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Structural detection of AUTOINCREMENT

[Csaba]

Thanks for your reply (and also thanks, Richard, for
your reply about sqlite3_table_column_metadata)
I've interspersed a few remarks, please see below.

On Mon, Jun 16, 2008 at 4:50 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Csaba wrote:
>> Is there any way to detect, based strictly on querying the
>> structure of a table/database whether there is an AUTOINCREMENT
>> set?  That is to say, without analyzing the original SQL creation
>> statement, and without inserting a new element into the database.
>>
>
> Yes there is. The autoincrement keyword can only be applied to an
> integer primary key column, and when it is, sqlite creates a entry in
> its internal sqlite_sequence table with the name set to the name of the
> table.
>
> Note, a table's sequence entry isn't created until the first row has
> been added to the table. Also, the internal sequence table is not listed
> by a normal .tables meta command, but it is listed if you use a wildcard
> pattern.

Thanks for this excellent information

> BTW, the autoincrement keyword must appear after the integer primary key
> phrase, not in the middle as you have show it.

This was an interesting note.  I am using the SQLite that came with my
PHP v. 5.2.6 (built Feb 13, 2008), which is SQLite 2.8.17.
Turns out that the AUTOINCREMENT key was accepted in the position
as I had stated and was autoincrementing just fine.  However, it
turned out that I got the same autoincrementing functionality when
I removed the AUTOINCREMENT keyword!  No surprise that I couldn't
find a structural difference between the two in this version of SQLite.

I tested with:

$dbFileName = "delme.db";
// prepare to put the database in the current directory:
$dbPath = dirname(__FILE__) . "/$dbFileName";
@unlink ($dbPath);// kill it in case it's already there
if (!($db = sqlite_open($dbPath, 0666, $sqliteerror)))
  die("Cannot open database file: $dbPath");

$tbl = "myTable";
$tblSpec = "Id INTEGER AUTOINCREMENT PRIMARY KEY, RunLen INTEGER";
$sql = "CREATE TABLE $tbl ($tblSpec)";
sqlite_query ($db, $sql);  // created here
sqlite_query ($db, "INSERT INTO $tbl VALUES(null,13)");
sqlite_query ($db, "INSERT INTO $tbl VALUES(null,96)");
$aOut = sqlite_array_query($db, "SELECT * FROM $tbl");// get results


> Since autoincrement can only be used with integer primary key columns,
> the column name in the table can be determined by looking at the output
> of the pragma table_info() command. It will be the primary key column
> indicated by the value 1 in the pk column.

Yes.  I've also got this in my version

> SQLite version 3.5.9
> Enter ".help" for instructions
> sqlite> create table t (id integer primary key autoincrement);
> sqlite> .tables '%'
> sqlite_sequence  t
> sqlite> .header on
> sqlite> .mode column
> sqlite> select * from sqlite_sequence;
> sqlite> insert into t values(1);
> sqlite> select * from sqlite_sequence;
> nameseq
> --  --
> t   1
> sqlite> pragma table_info(t);
> cid nametypenotnull dflt_value  pk
> --  --  --  --  --  --
> 0   id  integer 0   1
>
> HTH
> Dennis Cote

Useful example, thanks.  PHP provides a way to ask sqlite
to carry out a direct command (via http://php.net/sqlite_exec )
but they did not provide for retrieving any data from this
function.  In light of this, is there any way to direct output from
an sqlite command into a table?

Though the sqlite_sequence table seems not to exist in
my version of SQLite, should you be able to see it in yours
by querying the sqlite_master table ala:
SELECT * FROM sqlite_master WHERE type='table';
I ask this because sqlite_master itself is not listed in sqlite_master

Thanks,
Csaba Gabor from Vienna
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Gregor Brandt]

Hi, thanks this is great.  Of course I forgot to mention that the id  
is a primary key, so I get an error about duplicate primary keys.

I tried this:

update binary_report_fmt set column_id = column_id + 1 where column_id  
= (select column_id from binary_report_fmt where column_id >= 3 order  
by column_id desc);

but it only updates the last item.   I guess I can make it a non- 
primary key..then it works perfectly.

Gregor

On 16-Jun-08, at 10:04 AM  , [EMAIL PROTECTED] wrote:

> update
> table
> set id = id + 1
> WHERE
> id > 1
>
> RBS
>
>
>> Sorry about this, but this is a SQL question and not a SQLite  
>> specific
>> question.
>>
>> Is there a way to increment a value in a table in-situ.   Without
>> reading it, incrementing it, writing it?
>>
>> I need to insert an entry into a table, it has an id, all entries  
>> with
>> id's >= the id need to be incremented to keep the id's unique
>>
>> ie
>>
>> 1 bob
>> 2 joe
>> 3 irene
>>
>> someone wants to insert 2 sarah, I need the table to look like this
>>
>> 1 bob
>> 2 sarah
>> 3 joe
>> 4 irene
>>
>>
>> Any help would be great.
>> thanks
>>
>> -
>> Gregor
>>
>>
>> ___
>> 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] DB does not get updated after many writes

[Mihai Limbasan]

I have a peculiar problem with sqlite library. I built an application using

sqlite for DB operations. The application keeps updating the DB. Everything
goes fine for around one or two days. But after that DB does not get updated
at all. Strangely those sqlite library calls don't return any error,
everything is fine. But its just that DB does not get updated. I am using
sqlite version 3.5.0

If anyone has any solution for this problem, it will be of great help.
  
I doubt that anyone will be able to help you unless you provide more 
(or, for that matter, *any*) details about your environment, such as 
operating system hardware constraints, compiler brand and version, and 
so on and so forth.


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

Re: [sqlite] SQL question

[bartsmissaert]

update
table
set id = id + 1
WHERE
id > 1

RBS


> Sorry about this, but this is a SQL question and not a SQLite specific
> question.
>
> Is there a way to increment a value in a table in-situ.   Without
> reading it, incrementing it, writing it?
>
> I need to insert an entry into a table, it has an id, all entries with
> id's >= the id need to be incremented to keep the id's unique
>
> ie
>
> 1 bob
> 2 joe
> 3 irene
>
> someone wants to insert 2 sarah, I need the table to look like this
>
> 1 bob
> 2 sarah
> 3 joe
> 4 irene
>
>
> Any help would be great.
> thanks
>
> -
> Gregor
>
>
> ___
> 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] SQL question

[P Kishor]

On 6/16/08, Gregor Brandt <[EMAIL PROTECTED]> wrote:
> Sorry about this, but this is a SQL question and not a SQLite specific
>  question.
>
>  Is there a way to increment a value in a table in-situ.   Without
>  reading it, incrementing it, writing it?
>
>  I need to insert an entry into a table, it has an id, all entries with
>  id's >= the id need to be incremented to keep the id's unique
>
>  ie
>
>  1 bob
>  2 joe
>  3 irene
>
>  someone wants to insert 2 sarah, I need the table to look like this

-- to keep track of the id that is going to be duplicated
UPDATE table
SET id = -1
WHERE id = 2


INSERT INTO table VALUES (2, 'sarah');

>
>  1 bob
>  2 sarah
>  3 joe
>  4 irene
>

UPDATE table
SET id = id + 1
WHERE id > 2 OR id = -1



>
>  Any help would be great.
>  thanks
>
>  -
>  Gregor
>
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] SQL question

[Gregor Brandt]

Sorry about this, but this is a SQL question and not a SQLite specific  
question.

Is there a way to increment a value in a table in-situ.   Without  
reading it, incrementing it, writing it?

I need to insert an entry into a table, it has an id, all entries with  
id's >= the id need to be incremented to keep the id's unique

ie

1 bob
2 joe
3 irene

someone wants to insert 2 sarah, I need the table to look like this

1 bob
2 sarah
3 joe
4 irene


Any help would be great.
thanks

-
Gregor


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

Re: [sqlite] Implementing fast database rotation

[Dennis Cote]

Al wrote:
> 
> I'm using sqlite to implement a fast logging system in an embbeded system. For
> mainly space but also performance reason, I need to rotate the databases.
> 
> The database is queried regularly and I need to keep at least $min rows in 
> it. 
> 
> What I plan, is inside my logging loop, to do something like this.
> 
> while(1) {
> read_informations_from_several_sources();
> INSERT(informations);
> 
> if(count > max) {
>/* I want to move all oldest rows in another database */ 
>BEGIN;
>INSERT INTO logs_backup
> SELECT * FROM logs order by rowid limit ($max - $min);
> 
>DELETE FROM logs WHERE rowid IN (SELECT rowid FROM logs ORDER BY rowid
> LIMIT ($max - $min));
>COMMIT;
> }
> }
> 
> rowid is an autoincremented field.
> I am not an sql expert, and would like to find the fastest solution to move 
> the
> oldest rows into another database. Am I doing silly things ? Can it be 
> improved ?
> 

Al,

There have been a few past discussions of using SQLite to create a FIFO 
table with a fixed maximum size. See 
http://www.nabble.com/limiting-table-size--to2035232.html#a2063150 for 
one way to do this. You would have to modify the insert trigger to make 
the backup copy of the old row before it is deleted.

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

Re: [sqlite] Structural detection of AUTOINCREMENT

[Dennis Cote]

Csaba wrote:
> Is there any way to detect, based strictly on querying the structure of a
> table/database
> whether there is an AUTOINCREMENT set?  That is to say, without analyzing
> the
> original SQL creation statement, and without inserting a new element into
> the database.
> 

Yes there is. The autoincrement keyword can only be applied to an 
integer primary key column, and when it is, sqlite creates a entry in 
its internal sqlite_sequence table with the name set to the name of the 
table.

Note, a table's sequence entry isn't created until the first row has 
been added to the table. Also, the internal sequence table is not listed 
by a normal .tables meta command, but it is listed if you use a wildcard 
pattern.

BTW, the autoincrement keyword must appear after the integer primary key 
phrase, not in the middle as you have show it.

Since autoincrement can only be used with integer primary key columns, 
the column name in the table can be determined by looking at the output 
of the pragma table_info() command. It will be the primary key column 
indicated by the value 1 in the pk column.


 SQLite version 3.5.9
 Enter ".help" for instructions
 sqlite> create table t (id integer primary key autoincrement);
 sqlite> .tables '%'
 sqlite_sequence  t
 sqlite> .header on
 sqlite> .mode column
 sqlite> select * from sqlite_sequence;
 sqlite> insert into t values(1);
 sqlite> select * from sqlite_sequence;
 nameseq
 --  --
 t   1
 sqlite> pragma table_info(t);
 cid nametypenotnull dflt_value  pk
 --  --  --  --  --  --
 0   id  integer 0   1

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

[sqlite] DB does not get updated after many writes

[sethuarun]

I have a peculiar problem with sqlite library. I built an application using
sqlite for DB operations. The application keeps updating the DB. Everything
goes fine for around one or two days. But after that DB does not get updated
at all. Strangely those sqlite library calls don't return any error,
everything is fine. But its just that DB does not get updated. I am using
sqlite version 3.5.0

If anyone has any solution for this problem, it will be of great help.

Regards,
Sethu
-- 
View this message in context: 
http://www.nabble.com/DB-does-not-get-updated-after-many-writes-tp17866059p17866059.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] what's the difference between exec and prepare-bind-step(C api)?

[Jay A. Kreibich]

On Mon, Jun 16, 2008 at 06:07:46AM -0500, John Stanton scratched on the wall:
> You are expecting a miracle to ask for help without being allowed to 
> report the problem.
> 
> Sqlite3_exec just encapsulates prepare and step.  If your code runs 
> slower you have a logic error somewhere.

  unless your queries involve lots of strings or blobs, and you're
  using the SQLITE_TRANSIENT flag for sqlite3_bind_text|blob*().  In that
  case, the prepare-bind-step cycle can be slower, even if you're
  properly preparing each statement only once.

  If that's the case, try to structure your code to use SQLITE_STATIC
  memory management of the bind params.

   -j

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

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE TRIGGER not called on INSERT OR REPLACE statement

[Bharath Booshan L]

> Well, if I epxlicitly run DELETE and then INSERT, would you also expect
> an UPDATE trigger to run? After all, "on the whole" the operation is an
> update.

Oh!! May be I was wrong in thinking that INSERT OR REPLACE would keep the
PRIMARY KEY as it is..

Am a stupid guy..

Thanks Igor for your valuable time,

Bharath


On 6/16/08 6:39 PM, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote:

> "Bharath Booshan L"
> <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>> IF INSERT OR REPLACE statement performs REPLACE operation, then again
>> insert_trigger is being invoked, which as per the documentation ( i.e
>> it
>> actually performs DELETE and INSERT) is fine.
>> 
>> Am I wrong in expecting that INSERT OR REPLACE should trigger
>> update_trigger
> 
> Yes. Didn't you just explain why it doesn't happen?
> 
>> when on the whole the operation performed is actually an UPDATE?
> 
> Well, if I epxlicitly run DELETE and then INSERT, would you also expect
> an UPDATE trigger to run? After all, "on the whole" the operation is an
> update.
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 



---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.


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

Re: [sqlite] UPDATE TRIGGER not called on INSERT OR REPLACE statement

[Igor Tandetnik]

"Bharath Booshan L"
<[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> IF INSERT OR REPLACE statement performs REPLACE operation, then again
> insert_trigger is being invoked, which as per the documentation ( i.e
> it
> actually performs DELETE and INSERT) is fine.
>
> Am I wrong in expecting that INSERT OR REPLACE should trigger
> update_trigger

Yes. Didn't you just explain why it doesn't happen?

> when on the whole the operation performed is actually an UPDATE?

Well, if I epxlicitly run DELETE and then INSERT, would you also expect 
an UPDATE trigger to run? After all, "on the whole" the operation is an 
update.

Igor Tandetnik 



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

[sqlite] UPDATE TRIGGER not called on INSERT OR REPLACE statement

[Bharath Booshan L]

Hello Sqlite users, experts,

 I am in a state of confusion and I request you to help me out please.

 
 Can "INSERT OR REPLACE" trigger if actual operation performed is REPLACE?

Say , For example, if I have 2 triggers on table T1, one trigger, say
insert_trigger, is set to trigger after INSERT, and another, say
update_trigger, set to trigger after UPDATE.

If INSERT OR REPLACE statement performs INSERT operation, the insert_trigger
is being invoked, which is fine.

IF INSERT OR REPLACE statement performs REPLACE operation, then again
insert_trigger is being invoked, which as per the documentation ( i.e it
actually performs DELETE and INSERT) is fine.

Am I wrong in expecting that INSERT OR REPLACE should trigger update_trigger
when on the whole the operation performed is actually an UPDATE?

I apologize, if my question is too silly.

Regards,

Bharath



---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.


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

Re: [sqlite] SQlite and C works with "like" but not with "="

[Daniel White]

Thanks again Dan and Igor. Since I don't need to write
to the database (only read from it atm), collate binary
will do nicely.

I'll keep an eye on those error messages in future!

Dan


On Sun, 15 Jun 2008 09:13:52 +0100, Dan <[EMAIL PROTECTED]> wrote:

> On Jun 15, 2008, at 8:55 AM, Daniel White wrote:
>
>> Cheers both of you, it seems this problem is indeed
>> linked with the "no such collation sequence: iunicode"
>> error as Dan mentioned.
>>
>> After some research, I found out that the root of
>> the problem is unsurmountable at present. I quote from:
>> http://www.mediamonkey.com/forum/viewtopic.php?p=84197
>>
>> "It's a real shame that simple queries like "select *
>>  from Songs where SongTitle = 'ABC'" aren't viable.
>> However, SQLite doesn't have good collation included,
>> there's absolutely no support for Unicode sorting or
>> case insensitive comparisons. We will try to do something
>> about it, but to be honest, I don't know if there's any
>> easy fix..."
>>
>> ...and...
>>
>> "IUNICODE is our collation that's there in order to
>> support Unicode sorting - which SQLite can't do internally."
>>
>> Oh well, there are one or two 'hacks' around it. The first is
>> to use COLLATE BINARY, or COLLATE NOCASE after the SQL query.
>> This appears okay on the surface, but probably ignores
>> unicode chars or something. It may also slow down the query (?)
>>
>> The other idea is to simply use "LIKE 'xyz'" instead of
>> "= 'xyz'". To my limited knowledge, apart from the case
>> sensivity of the latter, these don't differ in the outcome,
>> because there are no % signs around the former statement.
>> Although it would be nice, I'm not too bothered about case
>> sensitivity for my purposes.
>>
>> Which solution would you guys recommend?
>
> Just using 'COLLATE BINARY' is a good idea. There is a pretty
> good chance that memcmp() and whatever is being used for IUNICODE
> are the same for the '=' operator. If you don't have any non-ASCII
> characters in the song names, this will almost certainly work.
>
> Using COLLATE BINARY will prevent SQLite from using any index
> created on the song_title column (as the index will have been
> created using IUNICODE).
>
> Or you could copy all the data into a new table - one that uses
> only the default available collation sequences:
>
>CREATE TEMP TABLE my_songs AS SELECT * FROM songs;
>
> then query my_songs instead of songs. That wouldn't help you any
> more than using COLLATE binary explicitly in every query though.
>
> Or you could ask the vendor for the source code to the IUNICODE
> collation function.
>
> Using SQLite's ICU extension to try to create an equivalent collation
> sequence is also possible, but a bit dangerous. If the collation
> sequence you create turns out to be "mostly compatible" instead of
> "completely compatible", then you might wind up with segfaults or
> a corrupted database at some point in the future.
>
> Dan.
>
>
>
>
>> Cheers, Dan
>>
>>
>>
>>> And you are saying the statement
>>>
>>> SELECT SongTitle FROM songs WHERE SongTitle='Hexion';
>>>
>>> doesn't return any rows?
>>
>> Correct. It's weird I know. I also tried with different
>> names in different fields (Artist etc.), and I get the
>> same problem. "like" is okay, but = doesn't work.
>>
>> Here are the files again:
>> http://www.skytopia.com/stuff/MMdatabase.zip   (1.6 M)
>> http://www.skytopia.com/stuff/sqlite.cpp   (1k)
>>
>> Dan
>>
>>
>> On Sat, 14 Jun 2008 15:54:50 +0100, Igor Tandetnik
>> <[EMAIL PROTECTED]>
>> wrote:
>>
>>> "Daniel White" <[EMAIL PROTECTED]>
>>> wrote in message news:[EMAIL PROTECTED]
> Which way is it stored in the database? Show the output of this
> statement:
>
> SELECT SongTitle FROM songs WHERE SongTitle like 'hexion';

 There are 8 records of Hexion in the database, so after a printout
 to the console with a carriage return after each value, I basically
 get:
 Hexion
 Hexion
 Hexion
 Hexion
 Hexion
 Hexion
 Hexion
 Hexion
>>>
>>> And you are saying the statement
>>>
>>> SELECT SongTitle FROM songs WHERE SongTitle='Hexion';
>>>
>>> doesn't return any rows? With all due respect, I find it difficult to
>>> believe. Would it be possible for you to email a copy of the database
>>> file to me at [EMAIL PROTECTED] ? Perhaps a stripped-down version,
>>> with just enough data to reproduce the problem.
>>>
>>> Igor Tandetnik
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> --
>> www.skytopia.com
>> ___
>> 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] Something to think about: Saving Select ID Lists

[Igor Tandetnik]

"MoDementia" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> One thing that I really miss form another database is the ability to
> reuse select statements.
>
> Select * FROM Transactions Where DateTime Between x and y Saving
> Unique DataID to 1

How about

create temp view view1 as
Select distinct DataID FROM Transactions Where DateTime Between x and y;

> GetList 1

I'm not sure what that's supposed to do, but perhaps

select * from view1;

> Select * From Data Using 1

It's not clear what that's supposed to do either, but perhaps

select * from Data where ID in (select DataID from view1);

Igor Tandetnik 



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

Re: [sqlite] AUTOINCREMENT detection

[D. Richard Hipp]

On Jun 16, 2008, at 4:42 AM, Csaba wrote:

> Hi, I didn't get a response to my first post to this group
> and I didn't get a copy, so perhaps it didn't go through...
>
> Is there any way to detect, based strictly on querying
> the structure of a table/database whether there is an
> AUTOINCREMENT set?  That is to say, without
> analyzing the original SQL creation statement, and
> without inserting a new element into the database.
>
> How is the difference between these two detectable?
> CREATE Table myTab ('AUTOINCREMENT col' INTEGER AUTOINCREMENT  
> PRIMARY KEY)
> vs.
> CREATE Table myTab ('AUTOINCREMENT col' INTEGER PRIMARY KEY)
>

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

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] what's the difference between exec and prepare-bind-step(C api)?

[John Stanton]

You are expecting a miracle to ask for help without being allowed to 
report the problem.

Sqlite3_exec just encapsulates prepare and step.  If your code runs 
slower you have a logic error somewhere.

Jong-young Park wrote:
> Deal all,
> 
> I have used some SQLite application with sprintf and sqlite3_exec.
> It takes about 1 hour.
> 
> And for test, they are changed to prepare-bind-step with same logic.
> After that, it takes about 2 hours.
> 
> I tested it on ARM board and I don't know why.
> Please advise me.
> 
> 
> * Sorry. I can't support application source for company security.
> ___
> 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] [newbie] Compiling for Windows CE

[noel frankinet]

David Stephenson a écrit :
> Hi,
>
> I'm trying to compile 3.5.9 to Windows CE using embedded C++ 4.0 and I get
> the following error:
>   
Hello David,
You probably should compile for a console project ( or a dll it depends)
Noël
> corelibc.lib(pegwmain.obj) : error LNK2019: unresolved external symbol
> _WinMain referenced in function _WinMainCRTStartup
> How can I resolve this?
>
> David
> ___
> 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] [newbie] Compiling for Windows CE

[David Stephenson]

Hi,

I'm trying to compile 3.5.9 to Windows CE using embedded C++ 4.0 and I get
the following error:

corelibc.lib(pegwmain.obj) : error LNK2019: unresolved external symbol
_WinMain referenced in function _WinMainCRTStartup
How can I resolve this?

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

[sqlite] AUTOINCREMENT detection

[Csaba]

Hi, I didn't get a response to my first post to this group
and I didn't get a copy, so perhaps it didn't go through...

Is there any way to detect, based strictly on querying
the structure of a table/database whether there is an
AUTOINCREMENT set?  That is to say, without
analyzing the original SQL creation statement, and
without inserting a new element into the database.

How is the difference between these two detectable?
CREATE Table myTab ('AUTOINCREMENT col' INTEGER AUTOINCREMENT PRIMARY KEY)
vs.
CREATE Table myTab ('AUTOINCREMENT col' INTEGER PRIMARY KEY)

I have not found a difference upon looking at:
SELECT * FROM sqlite_master WHERE type='table' AND name='myTab';
PRAGMA table_info('myTab');

The following is empty:
PRAGMA index_list('myTab');
so index_info does not seem relevant either.

Thanks,
Csaba Gabor from Vienna
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] wtl list view article on codeproject

[noel frankinet]

Hello,

I've written a small article to show how to connect sqlite to a wtl 
listview on windows.

http://www.codeproject.com/KB/list/alphaview.aspx

I hope it will help somebody

Best wishes
Noël Frankinet
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users