Re: [sqlite] sqlite3_prepare_v2(pDb, stmt, -1, &pReadStmt, 0) for read the table
"Joanne Pham" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I used sqlite3_prepare_v2(pDb,stmt,-1,&pReadStmt,0) > for preparing the statement and used sqlite3_step(pReadStmt); to get > each row. > my stmt is : >select remoteId, bytesIn from compressTable > then it worked ok > > but if my statement has the attach database then it didn't work > because the sqlite3_prepare_v2 has problem. attach database > 'CommonDB' as CDB; select remoteId, bytesIn from compressTable where > remoteId in (select remoteId from CDB.remoteWXTable where > remoteType=1); detach database CDB; That's three statements, not one. You need to prepare and execute each one separately. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_prepare_v2(pDb, stmt, -1, &pReadStmt, 0) for read the table
Hi All, I used sqlite3_prepare_v2(pDb,stmt,-1,&pReadStmt,0) for preparing the statement and used sqlite3_step(pReadStmt); to get each row. my stmt is : select remoteId, bytesIn from compressTable then it worked ok but if my statement has the attach database then it didn't work because the sqlite3_prepare_v2 has problem. attach database 'CommonDB' as CDB; select remoteId, bytesIn from compressTable where remoteId in (select remoteId from CDB.remoteWXTable where remoteType=1); detach database CDB; So the question is how attach the database before the select. What I should do in this case to make it work with the attach database statement. Thank in advance, JL __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
> On 4/9/08, Richard Klein <[EMAIL PROTECTED]> wrote: >>> Richard Klein <[EMAIL PROTECTED]> >>> wrote: One question: I thought that the '\' character is not part of the SQL standard. (That's why I specified '/' instead as my ESCAPE character). >>> I'm not sure I understand. You can use any character as an escape >>> character. E.g. LIKE '2!_%' ESCAPE '!'. A backslash is as good as any. >>> >>> Igor Tandetnik >> I'm a SQL newbie, so I may very well be wrong about this ... >> >> My understanding is that you can use any legal SQL character as an escape >> character, but that a backslash is not a legal SQL character. >> >> I got this impression from a sentence I read on the SQLite website (in >> the description of literal numeric values): >> >> "C-style escapes using the backslash character are not supported because >> they are not standard SQL." >> > > If I understand correctly Igor Tandetnik's comment "A backslash is as > good as any.", in the above quote the phrase "they are not standard > SQL" refers to C-style escapes and not to the use of the backslash > character as an escape defined with ESCAPE. > > LIKE '2\_%' > > is not valid. > > LIKE '2\_%' ESCAPE '\' > > is valid. > > Robert Wishlaw > I just now checked the use of backslash as an escape character in LIKE clauses, and it works just fine. So, regardless of whether backslash is a legal SQL character, it is apparently acceptable to SQLite, at least in LIKE clauses. - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
On 4/9/08, Richard Klein <[EMAIL PROTECTED]> wrote:
> > Richard Klein <[EMAIL PROTECTED]>
> > wrote:
> >> One question: I thought that the '\' character is not part of the
> >> SQL standard. (That's why I specified '/' instead as my ESCAPE
> >> character).
> >
> > I'm not sure I understand. You can use any character as an escape
> > character. E.g. LIKE '2!_%' ESCAPE '!'. A backslash is as good as any.
> >
> > Igor Tandetnik
>
> I'm a SQL newbie, so I may very well be wrong about this ...
>
> My understanding is that you can use any legal SQL character as an escape
> character, but that a backslash is not a legal SQL character.
>
> I got this impression from a sentence I read on the SQLite website (in
> the description of literal numeric values):
>
> "C-style escapes using the backslash character are not supported because
> they are not standard SQL."
>
If I understand correctly Igor Tandetnik's comment "A backslash is as
good as any.", in the above quote the phrase "they are not standard
SQL" refers to C-style escapes and not to the use of the backslash
character as an escape defined with ESCAPE.
LIKE '2\_%'
is not valid.
LIKE '2\_%' ESCAPE '\'
is valid.
Robert Wishlaw
> So then I checked the spec, and found syntax definitions (reproduced at
> the end of this email) that do seem to confirm that backslash ('\') is
> not a legal SQL language character.
>
> I haven't checked to see whether SQLite accepts backslash as an escape
> character.
>
> - Richard Klein
>
> ==
>
>::=
>
>|
>|
>
>::=
>
>|
>
>::=
> A | B | C | D | E | F | G | H | I | J | K | L | M | N |
> O
>| P | Q | R | S | T | U | V | W | X | Y | Z
>
>::=
> a | b | c | d | e | f | g | h | i | j | k | l | m | n |
> o
>| p | q | r | s | t | u | v | w | x | y | z
>
>::=
>0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
>
>::=
>
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>|
>
>::= !! space character in character set in use
>
>::= "
>
>::= %
>
>::= &
>
>::= '
>
>::= (
>
>::= )
>
>::= *
>
>::= +
>
>::= ,
>
>::= -
>
>::= .
>
>::= /
>
>::= :
>
>::= ;
>
>::= <
>
>::= =
>
>::= >
>
>::= ?
>
>::= [
>
>::= ]
>
>::= _
>
>::= |
>
>
> ___
> 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] Left Join help
Dennis, Thanks for the reply. The original schema was causing us way to many problems. We ended up going with the second schema and the last query I posted we managed to get it down to .058 secs instead of 2.9 secs. I do appreciate all the suggestions and assistance. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Wednesday, April 09, 2008 6:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Left Join help Andy Smith wrote: > > With all the changes that got me down to 4.8 seconds. Better. Can you post the exact query that ran in 4.8 seconds? Did you set case_sensitive_like on before you ran the query? > We are > also trying a schema in which we break up the container so it's a bit > smaller. Now my other 54 queries run sub .5 secs except 1. Which is a > variant of the one I have asked for help on. Here is the new version on > a schema based on the container table being split apart. > > explain query plan SELECT a.id, a.title, a.dateAdded, a.url, a.path, > a.containerID, a.mimeType, a.width, a.height, a.genreID, a.thumbnailID, > a.releaseYearID, a.artistID, (SELECT w.title AS containerName FROM > allAlbums AS w WHERE w.id=a.containerID), (SELECT x.title AS genreName > FROM allgenres AS x WHERE x.id=a.genreID), (SELECT y.title AS > releaseYearName FROM allreleaseYears AS y WHERE y.id=a.releaseYearID), > (SELECT z.title AS artistName FROM allartists AS z WHERE > z.id=a.artistID) FROM mediaImage AS a INNER JOIN mediaAudio AS b ON > b.containerID=a.containerID WHERE b.title LIKE 'Opus%' ESCAPE '\' ORDER > BY a.title, a.id LIMIT 0,9; > > 0|0|TABLE mediaImage AS a WITH INDEX mediaImage_title ORDER BY > 1|1|TABLE mediaAudio AS b WITH INDEX mediaAudio_containerID > 0|0|TABLE allAlbums AS w USING PRIMARY KEY > 0|0|TABLE allgenres AS x USING PRIMARY KEY > 0|0|TABLE allreleaseYears AS y USING PRIMARY KEY > 0|0|TABLE allartists AS z USING PRIMARY KEY > > This runs in about 2.9 seconds. Still way slower than needed. I am > really debating whether this can be sped up to meet the < 1 second goal. > This query has the same issues as the first one. The escape clause on the like invalidates the use of an index. The order of the tables a and b is sub optimal. The first scan is a full table scan using the title index for the ordering only. It will be faster swap the order the tables are scanned to eliminate all the records that don't have the correct title first. Instead of this: FROM mediaImage AS a INNER JOIN mediaAudio AS b ON b.containerID=a.containerID WHERE b.title LIKE 'Opus%' ESCAPE '\' ORDER BY a.title, a.id LIMIT 0,9; try this: FROM mediaAudio AS b JOIN mediaImage AS a ON b.containerID=a.containerID WHERE b.title LIKE 'Opus%' ORDER BY a.title, a.id LIMIT 0,9; Make sure you turn on case_sensitive_like before executing the query. The title index will be used to select the b table records based on the like condition, then the a table records will be matched by the containerID. This query (and the first one) will have to sort the result records instead of using the title index for the ordering, but there will hopefully be few records to sort, so it should be fast. How many records does this query return? HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This message is confidential to Prodea Systems, Inc unless otherwise indicated or apparent from its nature. This message is directed to the intended recipient only, who may be readily determined by the sender of this message and its contents. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient:(a)any dissemination or copying of this message is strictly prohibited; and(b)immediately notify the sender by return message and destroy any copies of this message in any form(electronic, paper or otherwise) that you have.The delivery of this message and its information is neither intended to be nor constitutes a disclosure or waiver of any trade secrets, intellectual property, attorney work product, or attorney-client communications. The authority of the individual sending this message to legally bind Prodea Systems is neither apparent nor implied,and must be independently verified. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
> Richard Klein <[EMAIL PROTECTED]>
> wrote:
>> One question: I thought that the '\' character is not part of the
>> SQL standard. (That's why I specified '/' instead as my ESCAPE
>> character).
>
> I'm not sure I understand. You can use any character as an escape
> character. E.g. LIKE '2!_%' ESCAPE '!'. A backslash is as good as any.
>
> Igor Tandetnik
I'm a SQL newbie, so I may very well be wrong about this ...
My understanding is that you can use any legal SQL character as an escape
character, but that a backslash is not a legal SQL character.
I got this impression from a sentence I read on the SQLite website (in
the description of literal numeric values):
"C-style escapes using the backslash character are not supported because
they are not standard SQL."
So then I checked the spec, and found syntax definitions (reproduced at
the end of this email) that do seem to confirm that backslash ('\') is
not a legal SQL language character.
I haven't checked to see whether SQLite accepts backslash as an escape
character.
- Richard Klein
==
::=
|
|
::=
|
::=
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O
| P | Q | R | S | T | U | V | W | X | Y | Z
::=
a | b | c | d | e | f | g | h | i | j | k | l | m | n | o
| p | q | r | s | t | u | v | w | x | y | z
::=
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
::=
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
::= !! space character in character set in use
::= "
::= %
::= &
::= '
::= (
::= )
::= *
::= +
::= ,
::= -
::= .
::= /
::= :
::= ;
::= <
::= =
::= >
::= ?
::= [
::= ]
::= _
::= |
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
Richard Klein wrote: > > One question: I thought that the '\' character is not part of the > SQL standard. (That's why I specified '/' instead as my ESCAPE > character). > The '\' character is valid character in standard SQL. It just doesn't have the property of being an escape character as it does in C and mySQL (I believe). In standard SQL you have to explicitly say which character you want to use as an escape character using the escape clause. Any character will work. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Left Join help
Andy Smith wrote: > > With all the changes that got me down to 4.8 seconds. Better. Can you post the exact query that ran in 4.8 seconds? Did you set case_sensitive_like on before you ran the query? > We are > also trying a schema in which we break up the container so it's a bit > smaller. Now my other 54 queries run sub .5 secs except 1. Which is a > variant of the one I have asked for help on. Here is the new version on > a schema based on the container table being split apart. > > explain query plan SELECT a.id, a.title, a.dateAdded, a.url, a.path, > a.containerID, a.mimeType, a.width, a.height, a.genreID, a.thumbnailID, > a.releaseYearID, a.artistID, (SELECT w.title AS containerName FROM > allAlbums AS w WHERE w.id=a.containerID), (SELECT x.title AS genreName > FROM allgenres AS x WHERE x.id=a.genreID), (SELECT y.title AS > releaseYearName FROM allreleaseYears AS y WHERE y.id=a.releaseYearID), > (SELECT z.title AS artistName FROM allartists AS z WHERE > z.id=a.artistID) FROM mediaImage AS a INNER JOIN mediaAudio AS b ON > b.containerID=a.containerID WHERE b.title LIKE 'Opus%' ESCAPE '\' ORDER > BY a.title, a.id LIMIT 0,9; > > 0|0|TABLE mediaImage AS a WITH INDEX mediaImage_title ORDER BY > 1|1|TABLE mediaAudio AS b WITH INDEX mediaAudio_containerID > 0|0|TABLE allAlbums AS w USING PRIMARY KEY > 0|0|TABLE allgenres AS x USING PRIMARY KEY > 0|0|TABLE allreleaseYears AS y USING PRIMARY KEY > 0|0|TABLE allartists AS z USING PRIMARY KEY > > This runs in about 2.9 seconds. Still way slower than needed. I am > really debating whether this can be sped up to meet the < 1 second goal. > This query has the same issues as the first one. The escape clause on the like invalidates the use of an index. The order of the tables a and b is sub optimal. The first scan is a full table scan using the title index for the ordering only. It will be faster swap the order the tables are scanned to eliminate all the records that don't have the correct title first. Instead of this: FROM mediaImage AS a INNER JOIN mediaAudio AS b ON b.containerID=a.containerID WHERE b.title LIKE 'Opus%' ESCAPE '\' ORDER BY a.title, a.id LIMIT 0,9; try this: FROM mediaAudio AS b JOIN mediaImage AS a ON b.containerID=a.containerID WHERE b.title LIKE 'Opus%' ORDER BY a.title, a.id LIMIT 0,9; Make sure you turn on case_sensitive_like before executing the query. The title index will be used to select the b table records based on the like condition, then the a table records will be matched by the containerID. This query (and the first one) will have to sort the result records instead of using the title index for the ordering, but there will hopefully be few records to sort, so it should be fast. How many records does this query return? HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
Richard Klein <[EMAIL PROTECTED]> wrote: > One question: I thought that the '\' character is not part of the > SQL standard. (That's why I specified '/' instead as my ESCAPE > character). I'm not sure I understand. You can use any character as an escape character. E.g. LIKE '2!_%' ESCAPE '!'. A backslash is as good as any. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] about addition extension
Ken wrote: > > Recomend getting the example from the wiki working first then move on to your > own. > That's good advice. Also, it would be better if you post your messages as replies to one of the existing message threads you have already started on this topic (rather than starting a new thread with each question). Having all the messages on a topic in a single thread makes it far easier for others to read and respond. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
> Xuanvinh Vu <[EMAIL PROTECTED]> wrote: >> Sorry it was actually quite simple for the WHERE clause it should be >> >> WHERE Name LIKE '2_%' >> >> to select Name that starts with the string "2_" > > Note that '_' is one of the special characters LIKE operator recognizes: > it matches any character (the other one, %, matches any sequence of > characters). Thus, the condition you show is actually looking for > strings that begin with '2' and are at least two characters long. > > You want something like this: > > WHERE Name LIKE '2\_%' ESCAPE '\' > > Igor Tandetnik Looks like Igor's and my replies crossed in the (e)mail! One question: I thought that the '\' character is not part of the SQL standard. (That's why I specified '/' instead as my ESCAPE character). - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
> Xuanvinh Vu <[EMAIL PROTECTED]> wrote: >> I have search google but have not found a solution. I have a field >> called Name and I want to select the rows that have Name starts with >> certain string. How could I do this? > > select * from tableName where Name LIKE 'xyz%'; > > Igor Tandetnik I would add that it seems from Xuanvinh's post that he is looking for names that start with '2_'. Since LIKE considers '_' to be a metacharacter that matches any single character, wouldn't the '_' need to be escaped? Something like this: select * from tableName where Name LIKE '2/_%' ESCAPE '/'; - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
Xuanvinh Vu <[EMAIL PROTECTED]> wrote: > Sorry it was actually quite simple for the WHERE clause it should be > > WHERE Name LIKE '2_%' > > to select Name that starts with the string "2_" Note that '_' is one of the special characters LIKE operator recognizes: it matches any character (the other one, %, matches any sequence of characters). Thus, the condition you show is actually looking for strings that begin with '2' and are at least two characters long. You want something like this: WHERE Name LIKE '2\_%' ESCAPE '\' Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
Thanks! :)> To: sqlite-users@sqlite.org> From: [EMAIL PROTECTED]> Date: Wed, 9 Apr 2008 18:32:02 -0400> Subject: Re: [sqlite] Select row names that start with this string...> > Xuanvinh Vu <[EMAIL PROTECTED]> wrote:> > I have search google but have not found a solution. I have a field> > called Name and I want to select the rows that have Name starts with> > certain string. How could I do this?> > select * from tableName where Name LIKE 'xyz%';> > Igor Tandetnik > > > > ___> sqlite-users mailing list> sqlite-users@sqlite.org> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Going green? See the top 12 foods to eat organic. http://green.msn.com/galleries/photos/photos.aspx?gid=164&ocid=T003MSN51N1653A ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
Sorry it was actually quite simple for the WHERE clause it should be WHERE Name LIKE '2_%' to select Name that starts with the string "2_" thx> From: [EMAIL PROTECTED]> To: sqlite-users@sqlite.org> Date: Wed, 9 Apr 2008 18:21:28 -0400> Subject: [sqlite] Select row names that start with this string...> > > I have search google but have not found a solution. I have a field called Name and I want to select the rows that have Name starts with certain string. How could I do this?> > SELECT Name, BuildNum AS 'Build', ComputerName AS 'Computer Name', Time AS 'TimeStamp', Description, OSType FROM blackboxes WHERE LEFT(Name,2) LIKE '2_' ORDER BY Time DESC;> > unfortunately LEFT isnt a sqlite function.> > Vin> _> Going green? See the top 12 foods to eat organic.> http://green.msn.com/galleries/photos/photos.aspx?gid=164&ocid=T003MSN51N1653A> ___> sqlite-users mailing list> sqlite-users@sqlite.org> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Get in touch in an instant. Get Windows Live Messenger now. http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_getintouch_042008 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
Xuanvinh Vu <[EMAIL PROTECTED]> wrote: > I have search google but have not found a solution. I have a field > called Name and I want to select the rows that have Name starts with > certain string. How could I do this? select * from tableName where Name LIKE 'xyz%'; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Select row names that start with this string...
I have search google but have not found a solution. I have a field called Name and I want to select the rows that have Name starts with certain string. How could I do this? SELECT Name, BuildNum AS 'Build', ComputerName AS 'Computer Name', Time AS 'TimeStamp', Description, OSType FROM blackboxes WHERE LEFT(Name,2) LIKE '2_' ORDER BY Time DESC; unfortunately LEFT isnt a sqlite function. Vin _ Going green? See the top 12 foods to eat organic. http://green.msn.com/galleries/photos/photos.aspx?gid=164&ocid=T003MSN51N1653A ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is performance of v3.5.7 improved with new bitvec?
On Wed, Apr 09, 2008 at 05:14:33PM +0200, Aladdin Lamp? scratched on the wall: > > Hi all! > Following the recent thread "Virtual tables used to query big external > database", and the discussion with Mike Owens and Jay A. Kreibich, it > seems that : > > - The "old" way of dealing with dirty pages with bitmaps limited SQLite > to an approximate maximal capacity of 10s of GBs, as opposed to therical > TBs, because it imposed to malloc 256 bytes for every 1Mb of database > during each transaction. > > - The "new" way of dealing with dirty pages with a bitvec structure > (introduced in SQLite v3.5.7) allows for sparse bitmaps and is then > supposed to push away the "10s of GBs" limit. Just to be clear, the bitvec stuff can greatly reduce memory use for the average-case, but doesn't change the worst-case. If you have a transaction that touches a lot of pages (especially if they're spread out in the file) the bitvec can still grow to be quite large. > Now the questions are: > 1) What are the new practical limits with SQLite v3.5.7? Depends on your environment. A full-blown desktop with 4GB of RAM is going to have much different practical limits than an iPhone. It also depends on what you're doing. None of this really matters if you're using the database read-only. > 2) Does somebody have any real-life experience (or home-made tests and > figures) on SQLite v3.5.7 and really big tables? (say 100 000 000 lines). Personally, I've only gotten to about five or six million rows in a ~6GB db. That was pre-3.5.7 anyways. > 3) Does the new "bitvec" algorithm really help with such a big table? The bitvec stuff has nothing directly to do with table size, only the total database size. That said, if a single table makes up most of a database, it might be easier to dirty a larger number of pages with a single transaction. I'm less clear on that aspect, however. > I am mainly interested in performance of INSERTs If you mean "speed" when you use the word "performance", the bitvec changes aren't likely to have any significant impact unless the old bit-vector was getting so huge it was forcing the VM system to page things out to disk. -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] What is the standard way to store dates and do operations with dates please?
* Dennis Cote:
> From the wikipedia article you cited:
>
> Note: although many references say that the Julian in "Julian day"
> refers to Scaliger's father, Julius Scaliger, in the introduction to
> Book V of his Opus de Emendatione Temporum ("Work on the Emendation of
> Time") he states, "Iulianum vocavimus: quia ad annum Iulianum dumtaxat
> accomodata est", which translates more or less as "We have called it
> Julian merely because it is accommodated to the Julian year." This
> Julian refers to Julius Caesar, who introduced the Julian calendar in 46 BC.
>
> I can't vouch for the veracity of this note, but he he seems to know
> what he is talking about and has given what is purported to be a
> reference from the original author that backs his claim (as best I can
> tell from the quoted Latin and its translation). As always you have to
> take everything on wikipedia with a grain of salt, but this looks
> authoritative.
"Calendrical Calculations" by Reingold and Dershowitz has the following
to say about the matter:
| It is often claimed [...] that Scaliger named the [Julian] period [a
| method of counting years] after his father, the Renaissance physician
| Julius Cæsar Scaliger, but this claim is not borne out by examination
| of Scaliger's great work, /De Emendatione Temporum/, from which the
| section quote above [Iulianam vocavimus: quia ad annum Iulianum
| dumtaxat accommodata est] is taken.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] about addition extension
1. remove main.
2. compile file with -shared (to create a .so)
3. select loadd_extension('filename.so') ; This will load your .so into the
address space.
4. Use your function in a sql statement.
Recomend getting the example from the wiki working first then move on to your
own.
HTH,
Ken
dark0s dark0s <[EMAIL PROTECTED]> wrote: Ok, but I did not understand step by
step how work. In wiki there is:
#include
SQLITE_EXTENSION_INIT1
static void halfFunc(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
sqlite3_result_double(context, 0.5*sqlite3_value_double(argv[0]));
}
int sqlite3_extension_init(
sqlite3 *db,
char **pzErrMsg,
const sqlite3_api_routines *pApi
){
SQLITE_EXTENSION_INIT2(pApi)
sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0, 0);
return 0;
}
I understood that in my program I must insert sqlite3ext.h
instead sqlite3.h, but I have got a doubt:
Must I insert sqlite3_extension_init implementation,
like below?
#include
SQLITE_EXTENSION_INIT1
int main(int argc, char* argv[]) {
int rc;
char* sql;
sqlite3* db;
sqlite3_stmt* stmt;
const char* tail;
rc = sqlite3_open("dbforext.db",&db);
if (rc) {
fprintf(stderr, "E' impossibile aprire il file %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
static void soundex(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
...
}
int sqlite3_extension_init(
sqlite3 *db,
char **pzErrMsg,
const sqlite3_api_routines *pApi
){
SQLITE_EXTENSION_INIT2(pApi)
sqlite3_create_function(db, "soundex", 1, SQLITE_UTF8, NULL, soundex, NULL,
NULL);
}
sql = "select soundex(saverio);";
sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail);
if (rc != SQLITE_OK) {
fprintf(stderr, "Errore SQL: %s\n", sqlite3_errmsg(db));
}
rc = sqlite3_step(stmt);
sqlite3_close(db);
return 0;
}
Excuse for my ignorance with sqlite3, but I am newbye.
Savio
-
Inviato da Yahoo! Mail.
La casella di posta intelligente.
___
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] about addition extension
Ok, but I did not understand step by step how work. In wiki there is:
#include
SQLITE_EXTENSION_INIT1
static void halfFunc(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
sqlite3_result_double(context, 0.5*sqlite3_value_double(argv[0]));
}
int sqlite3_extension_init(
sqlite3 *db,
char **pzErrMsg,
const sqlite3_api_routines *pApi
){
SQLITE_EXTENSION_INIT2(pApi)
sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0, 0);
return 0;
}
I understood that in my program I must insert sqlite3ext.h
instead sqlite3.h, but I have got a doubt:
Must I insert sqlite3_extension_init implementation,
like below?
#include
SQLITE_EXTENSION_INIT1
int main(int argc, char* argv[]) {
int rc;
char* sql;
sqlite3* db;
sqlite3_stmt* stmt;
const char* tail;
rc = sqlite3_open("dbforext.db",&db);
if (rc) {
fprintf(stderr, "E' impossibile aprire il file %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
static void soundex(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
...
}
int sqlite3_extension_init(
sqlite3 *db,
char **pzErrMsg,
const sqlite3_api_routines *pApi
){
SQLITE_EXTENSION_INIT2(pApi)
sqlite3_create_function(db, "soundex", 1, SQLITE_UTF8, NULL, soundex, NULL,
NULL);
}
sql = "select soundex(saverio);";
sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail);
if (rc != SQLITE_OK) {
fprintf(stderr, "Errore SQL: %s\n", sqlite3_errmsg(db));
}
rc = sqlite3_step(stmt);
sqlite3_close(db);
return 0;
}
Excuse for my ignorance with sqlite3, but I am newbye.
Savio
-
Inviato da Yahoo! Mail.
La casella di posta intelligente.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Left Join help
Dennis, With all the changes that got me down to 4.8 seconds. Better. We are also trying a schema in which we break up the container so it's a bit smaller. Now my other 54 queries run sub .5 secs except 1. Which is a variant of the one I have asked for help on. Here is the new version on a schema based on the container table being split apart. explain query plan SELECT a.id, a.title, a.dateAdded, a.url, a.path, a.containerID, a.mimeType, a.width, a.height, a.genreID, a.thumbnailID, a.releaseYearID, a.artistID, (SELECT w.title AS containerName FROM allAlbums AS w WHERE w.id=a.containerID), (SELECT x.title AS genreName FROM allgenres AS x WHERE x.id=a.genreID), (SELECT y.title AS releaseYearName FROM allreleaseYears AS y WHERE y.id=a.releaseYearID), (SELECT z.title AS artistName FROM allartists AS z WHERE z.id=a.artistID) FROM mediaImage AS a INNER JOIN mediaAudio AS b ON b.containerID=a.containerID WHERE b.title LIKE 'Opus%' ESCAPE '\' ORDER BY a.title, a.id LIMIT 0,9; 0|0|TABLE mediaImage AS a WITH INDEX mediaImage_title ORDER BY 1|1|TABLE mediaAudio AS b WITH INDEX mediaAudio_containerID 0|0|TABLE allAlbums AS w USING PRIMARY KEY 0|0|TABLE allgenres AS x USING PRIMARY KEY 0|0|TABLE allreleaseYears AS y USING PRIMARY KEY 0|0|TABLE allartists AS z USING PRIMARY KEY This runs in about 2.9 seconds. Still way slower than needed. I am really debating whether this can be sped up to meet the < 1 second goal. Here is the schema for the alternate layout: CREATE TABLE "allAlbums" ( -- fundamental information "id" INTEGERPRIMARY KEY AUTOINCREMENT, "title"VARCHAR(255) NOT NULL COLLATE NOCASE, "dateAdded"TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, -- housekeeping... "dateModified" TIMESTAMPDEFAULT CURRENT_TIMESTAMP ); CREATE TABLE "allArtists" ( -- fundamental information "id" INTEGERPRIMARY KEY AUTOINCREMENT, "title"VARCHAR(255) NOT NULL COLLATE NOCASE, "dateAdded"TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, -- housekeeping... "dateModified" TIMESTAMPDEFAULT CURRENT_TIMESTAMP ); CREATE TABLE "allGenres" ( -- fundamental information "id" INTEGERPRIMARY KEY AUTOINCREMENT, "title"VARCHAR(255) NOT NULL COLLATE NOCASE, "dateAdded"TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, -- housekeeping... "dateModified" TIMESTAMPDEFAULT CURRENT_TIMESTAMP ); CREATE TABLE "allPlaylists" ( -- fundamental information "id" INTEGERPRIMARY KEY AUTOINCREMENT, "title"VARCHAR(255) NOT NULL COLLATE NOCASE, "dateAdded"TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, -- housekeeping... "dateModified" TIMESTAMPDEFAULT CURRENT_TIMESTAMP ); CREATE TABLE "allReleaseYears" ( -- fundamental information "id" INTEGERPRIMARY KEY AUTOINCREMENT, "title"VARCHAR(255) NOT NULL COLLATE NOCASE, "dateAdded"TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, -- housekeeping... "dateModified" TIMESTAMPDEFAULT CURRENT_TIMESTAMP ); CREATE TABLE "allTags" ( -- fundamental information "id" INTEGERPRIMARY KEY AUTOINCREMENT, "title"VARCHAR(255) NOT NULL COLLATE NOCASE, "dateAdded"TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, -- housekeeping... "dateModified" TIMESTAMPDEFAULT CURRENT_TIMESTAMP ); CREATE TABLE "container" ( -- fundamental container information "id" INTEGER PRIMARY KEY AUTOINCREMENT, "title"VARCHAR(255) NOT NULL COLLATE NOCASE, -- enumerations -- 10: music album -- 11: music artist -- 12: music genre -- 13: music composer -- 14: music conductor -- 15: release year -- 16: photo roll -- 17: photo album / slide show -- 18: events -- 19: keyword -- 20: people -- 21: places -- 22: user-defined tag -- 23: playlist -- 24: video season -- 25: video series -- 26: channel -- 27: director -- 28: actor -- 29: producer -- 30: audio series -- 31: media folder "type" INTEGER NOT NULL, "dateAdded" TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL, "dateCreated"TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL, -- generic information about the media container "ownerID"INTEGER DEFAULT NULL REFERENCES "user(userid)", "details"TEXT DEFAULT NULL,-- lyrics, plot, etc. "detailsURI" TEXT DEFAULT NULL,-- lyrics, plot, etc. -- non-NULL only for slideshows and playlists "duration" INTEGER UNSIGNED DEFAULT NULL,-- in seconds "transition" tinyint
Re: [sqlite] Left Join help
Andy Smith wrote: > I have to get this down to < 1 sec. I have 50 queries that are all > formatted similar. We have created another schema where the container > is split into smaller tables which might help in speeding this up. From > what I see the Left joins are killing the speed. > I don't think that is your problem. You are doing a full table scan of a large table. The last four left joins are required no matter what, but they will be quick since they use the container primary key to get the required record directly. You want to use the most specific test you can to eliminate as many records as possible early on. The problem is that your LIKE test can't use an index as given. See http://www.sqlite.org/optoverview.html for details. You need to make a few changes to your query to get it to use the index on the mediaitem title for the like test. First remove the unnecessary escape clause from the like clause. The set the case_sensitive_like pragma on. Next drop the index mediaitem_type so that sqlite will use the title index instead. Finally rearrange the order of the a and b tables so the like test is applied first using the index. sqlite> drop index mediaitem_type; sqlite> pragma case_sensitive_like = 1; sqlite> explain query plan ...> SELECT ...> a.id, ...> a.title, ...> a.type, ...> a.dateAdded, ...> a.url, ...> a.path, ...> a.containerID, ...> a.mimeType, ...> a.width, ...> a.height, ...> a.genreID, ...> a.thumbnailID, ...> a.releaseYearID, ...> a.artistID, ...> w.title AS containerName, ...> x.title AS genreName, ...> y.title AS releaseYearName, ...> z.title AS artistName ...> FROM mediaitem AS b ...> LEFT JOIN mediaitem AS a ON b.containerID=a.containerID ...> LEFT JOIN container AS w ON w.id=a.containerID ...> LEFT JOIN container AS x ON x.id=a.genreID ...> LEFT JOIN container AS y ON y.id=a.releaseYearID ...> LEFT JOIN container AS z ON z.id=a.artistID ...> WHERE b.title LIKE 'Opus%' --remove the escape clause ...> AND b.type=0 ...> AND a.type=1 ...> ORDER BY a.title, a.id ...> LIMIT 0,9; 0|0|TABLE mediaitem AS b WITH INDEX mediaitem_title 1|1|TABLE mediaitem AS a WITH INDEX mediaitem_containerID 2|2|TABLE container AS w USING PRIMARY KEY 3|3|TABLE container AS x USING PRIMARY KEY 4|4|TABLE container AS y USING PRIMARY KEY 5|5|TABLE container AS z USING PRIMARY KEY With these changes you will use the title index to quickly reduce the table to only those records that match the title. For each of these records it will check the type, then join the records with the same containerID using that index with another check for the required type. Finally it will select the indicated records from the container table based on the rows selected. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Left Join help
Andy I get this explain output: order|from|detail 0|0|TABLE mediaitem AS a WITH INDEX mediaitem_type 1|1|TABLE mediaitem AS b WITH INDEX mediaitem_type 2|2|TABLE container AS w USING PRIMARY KEY 3|3|TABLE container AS x USING PRIMARY KEY 4|4|TABLE container AS y USING PRIMARY KEY 5|5|TABLE container AS z USING PRIMARY KEY Media Item Type is a terrible index! It only has 3 values so drop it. After dropping the index i get this explain: drop index mediaitem_type ...> ; sqlite> .read media.sql order|from|detail 0|0|TABLE mediaitem AS a WITH INDEX mediaitem_title ORDER BY 1|1|TABLE mediaitem AS b WITH INDEX mediaitem_containerID 2|2|TABLE container AS w USING PRIMARY KEY 3|3|TABLE container AS x USING PRIMARY KEY 4|4|TABLE container AS y USING PRIMARY KEY 5|5|TABLE container AS z USING PRIMARY KEY See if that is better? Andy Smith <[EMAIL PROTECTED]> wrote: I have to get this down to < 1 sec. I have 50 queries that are all formatted similar. We have created another schema where the container is split into smaller tables which might help in speeding this up. From what I see the Left joins are killing the speed. Here is what I am seeing when running explain query plan: sqlite> explain query plan SELECT a.id, a.title, a.type, a.dateAdded, a.url, a.path, a.containerID, a.mimeType, a.width, a.height, a.genreID, a.thumbnailID, a.releaseYearID, a.artistID, w.title AS containerName, x.title AS genreName, y.title AS releaseYearName, z.title AS artistName FROM mediaitem AS a LEFT JOIN mediaitem AS b ON b.containerID=a.containerID LEFT JOIN container AS w ON w.id=a.containerID LEFT JOIN container AS x ON x.id=a.genreID LEFT JOIN container AS y ON y.id=a.releaseYearID LEFT JOIN container AS z ON z.id=a.artistID WHERE a.type=1 AND b.type=0 AND b.title LIKE 'Opus%' ESCAPE '\' ORDER BY a.title, a.id LIMIT 0,9; 0|0|TABLE mediaitem AS a WITH INDEX mediaitem_title ORDER BY 1|1|TABLE mediaitem AS b WITH INDEX mediaitem_containerID 2|2|TABLE container AS w USING PRIMARY KEY 3|3|TABLE container AS x USING PRIMARY KEY 4|4|TABLE container AS y USING PRIMARY KEY 5|5|TABLE container AS z USING PRIMARY KEY Schema: sqlite> .schema CREATE TABLE "container" ( -- fundamental container information "id" INTEGER PRIMARY KEY AUTOINCREMENT, "title"VARCHAR(255) NOT NULL, -- enumerations -- 10: music album -- 11: music artist -- 12: music genre -- 13: music composer -- 14: music conductor -- 15: release year -- 16: photo roll -- 17: photo album / slide show -- 18: events -- 19: keyword -- 20: people -- 21: places -- 22: user-defined tag -- 23: playlist -- 24: video season -- 25: video series -- 26: channel -- 27: director -- 28: actor -- 29: producer -- 30: audio series -- 31: media folder "type" INTEGER NOT NULL, "dateAdded" TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL, "dateCreated"TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL, -- generic information about the media container "details"TEXT DEFAULT NULL,-- lyrics, plot, etc. "detailsURI" TEXT DEFAULT NULL,-- lyrics, plot, etc. -- non-NULL only for slideshows and playlists "duration" INTEGER UNSIGNED DEFAULT NULL,-- in seconds "transition" tinyint DEFAULT '0', -- 0: no effect -- 1: fade in/out -- non-NULL only for media folders "upnpShared" tinyint DEFAULT '0', "parentalLocked" tinyint DEFAULT '0', -- voting and ratings "rating" BIGINT UNSIGNED DEFAULT NULL, "voteCount" INTEGER UNSIGNED DEFAULT NULL, "avgRating" FLOATDEFAULT NULL, -- housekeeping... "dateModified" TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL, "locked" tinyint DEFAULT '0', "extended" tinyint DEFAULT '0' ); CREATE TABLE "containeraux" ( -- used for certain types of containers to ensure title/type uniqueness "title"VARCHAR(255) NOT NULL REFERENCES "container(title)", "type" INTEGER NOT NULL REFERENCES "container(type)" ); CREATE TABLE "extend" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "title" VARCHAR(255) NOT NULL, "document" TEXT NOT NULL, "contact"VARCHAR(255) NOT NULL, "dateAdded" TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL, -- housekeeping... "dateModified" TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL ); CREATE TABLE "extension" ( -- fundamental extension information "id" INTEGER PRIMARY KEY AUTOINCREMENT, "extendID" INTEGER NOT NULL REFERENCES "extend(id)", -- exactly o
[sqlite] Addition extension function failed!
Ok, but I did not understand step by step how work. In wiki there is:
#include
SQLITE_EXTENSION_INIT1
static void halfFunc(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
sqlite3_result_double(context, 0.5*sqlite3_value_double(argv[0]));
}
int sqlite3_extension_init(
sqlite3 *db,
char **pzErrMsg,
const sqlite3_api_routines *pApi
){
SQLITE_EXTENSION_INIT2(pApi)
sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0, 0);
return 0;
}
I understood that in my program I must insert sqlite3ext.h
instead sqlite3.h, but I have got a doubt:
Must I insert sqlite3_extension_init implementation,
like below?
#include
SQLITE_EXTENSION_INIT1
int main(int argc, char* argv[]) {
int rc;
char* sql;
sqlite3* db;
sqlite3_stmt* stmt;
const char* tail;
rc = sqlite3_open("dbforext.db",&db);
if (rc) {
fprintf(stderr, "E' impossibile aprire il file %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
static void soundex(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
...
}
int sqlite3_extension_init(
sqlite3 *db,
char **pzErrMsg,
const sqlite3_api_routines *pApi
){
SQLITE_EXTENSION_INIT2(pApi)
sqlite3_create_function(db, "soundex", 1, SQLITE_UTF8, NULL, soundex, NULL,
NULL);
}
sql = "select soundex(saverio);";
sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail);
if (rc != SQLITE_OK) {
fprintf(stderr, "Errore SQL: %s\n", sqlite3_errmsg(db));
}
rc = sqlite3_step(stmt);
sqlite3_close(db);
return 0;
}
Excuse for my ignorance with sqlite3, but I am newbye.
Savio
-
Inviato da Yahoo! Mail.
La casella di posta intelligente.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Left Join help
I have to get this down to < 1 sec. I have 50 queries that are all formatted similar. We have created another schema where the container is split into smaller tables which might help in speeding this up. From what I see the Left joins are killing the speed. Here is what I am seeing when running explain query plan: sqlite> explain query plan SELECT a.id, a.title, a.type, a.dateAdded, a.url, a.path, a.containerID, a.mimeType, a.width, a.height, a.genreID, a.thumbnailID, a.releaseYearID, a.artistID, w.title AS containerName, x.title AS genreName, y.title AS releaseYearName, z.title AS artistName FROM mediaitem AS a LEFT JOIN mediaitem AS b ON b.containerID=a.containerID LEFT JOIN container AS w ON w.id=a.containerID LEFT JOIN container AS x ON x.id=a.genreID LEFT JOIN container AS y ON y.id=a.releaseYearID LEFT JOIN container AS z ON z.id=a.artistID WHERE a.type=1 AND b.type=0 AND b.title LIKE 'Opus%' ESCAPE '\' ORDER BY a.title, a.id LIMIT 0,9; 0|0|TABLE mediaitem AS a WITH INDEX mediaitem_title ORDER BY 1|1|TABLE mediaitem AS b WITH INDEX mediaitem_containerID 2|2|TABLE container AS w USING PRIMARY KEY 3|3|TABLE container AS x USING PRIMARY KEY 4|4|TABLE container AS y USING PRIMARY KEY 5|5|TABLE container AS z USING PRIMARY KEY Schema: sqlite> .schema CREATE TABLE "container" ( -- fundamental container information "id" INTEGER PRIMARY KEY AUTOINCREMENT, "title"VARCHAR(255) NOT NULL, -- enumerations -- 10: music album -- 11: music artist -- 12: music genre -- 13: music composer -- 14: music conductor -- 15: release year -- 16: photo roll -- 17: photo album / slide show -- 18: events -- 19: keyword -- 20: people -- 21: places -- 22: user-defined tag -- 23: playlist -- 24: video season -- 25: video series -- 26: channel -- 27: director -- 28: actor -- 29: producer -- 30: audio series -- 31: media folder "type" INTEGER NOT NULL, "dateAdded" TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL, "dateCreated"TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL, -- generic information about the media container "details"TEXT DEFAULT NULL,-- lyrics, plot, etc. "detailsURI" TEXT DEFAULT NULL,-- lyrics, plot, etc. -- non-NULL only for slideshows and playlists "duration" INTEGER UNSIGNED DEFAULT NULL,-- in seconds "transition" tinyint DEFAULT '0', -- 0: no effect -- 1: fade in/out -- non-NULL only for media folders "upnpShared" tinyint DEFAULT '0', "parentalLocked" tinyint DEFAULT '0', -- voting and ratings "rating" BIGINT UNSIGNED DEFAULT NULL, "voteCount" INTEGER UNSIGNED DEFAULT NULL, "avgRating" FLOATDEFAULT NULL, -- housekeeping... "dateModified" TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL, "locked" tinyint DEFAULT '0', "extended" tinyint DEFAULT '0' ); CREATE TABLE "containeraux" ( -- used for certain types of containers to ensure title/type uniqueness "title"VARCHAR(255) NOT NULL REFERENCES "container(title)", "type" INTEGER NOT NULL REFERENCES "container(type)" ); CREATE TABLE "extend" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "title" VARCHAR(255) NOT NULL, "document" TEXT NOT NULL, "contact"VARCHAR(255) NOT NULL, "dateAdded" TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL, -- housekeeping... "dateModified" TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL ); CREATE TABLE "extension" ( -- fundamental extension information "id" INTEGER PRIMARY KEY AUTOINCREMENT, "extendID" INTEGER NOT NULL REFERENCES "extend(id)", -- exactly one of the following is non-NULL "mediaitemID"INTEGER DEFAULT NULL REFERENCES "mediaitem(id)", "containerID"INTEGER DEFAULT NULL REFERENCES "container(id)", "keyvalue" VARCHAR(255) NOT NULL, "valvalue" VARCHAR(255) NOT NULL, "dateAdded" TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL, -- housekeeping... "dateModified" TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL ); CREATE TABLE "mediaitem" ( -- fundamental mediaitem information "id" INTEGER PRIMARY KEY AUTOINCREMENT, "title" VARCHAR(255) NOT NULL, -- enumerations -- 0: audio -- 1: image -- 2: video "type" INTEGER NOT NULL, "dateAdded" TIMESTAMPDEFAULT CURRENT_TIMESTAMP NOT NULL,
Re: [sqlite] Addition extension function failed!
dark0s dark0s wrote: > I have a new problem, I am attempting add extension function to sqlite3. > My program labsinf.c now build successfully, but I didn't add my extension. I > don't understand what it is miss. > Help me please. > > > bash-3.1# gcc -lsqlite3 labsinf.c -o inf > bash-3.1# ./inf > bash-3.1# sqlite3 dbforext.db > SQLite version 3.5.7 > Enter ".help" for instructions > sqlite> select soundex(); > SQL error: no such function: soundex The sqlite3_create_function() call create a function that is available to the database connection used in the create call only. Your function is available in your inf program after it is created, but is no longer available after it exits. The sqlite3 command shell does not know anything about your custom function, so it can't be used there. You need to create a loadable extension module and load that into the shell for the function to be available in the shell. See http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions and the shell's .load commnad for additional info. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Addition extension function failed!
For the solution see:
http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions
1. you may need to re-compile sqlite with loadable extensions, depending upon
the version of sqlite you may need to edit the makefile.
2. Create a .sqliterc file that loads your .so file
select load_extension(' /home/user/lib/labsinf.so');
3. Remove the "main from your code" and Compile using -shared flag to create a
.so file that can be loaded at runtime.
HTH,
Ken
dark0s dark0s <[EMAIL PROTECTED]> wrote: I have a new problem, I am attempting
add extension function to sqlite3.
My program labsinf.c now build successfully, but I didn't add my extension. I
don't understand what it is miss.
Help me please.
The program labsinf.c make inf, and after I typed ./inf, it makes dbforext.db.
Some output is below:
bash-3.1# gcc -lsqlite3 labsinf.c -o inf
bash-3.1# ./inf
bash-3.1# sqlite3 dbforext.db
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> select soundex(saverio);
SQL error: no such column: saverio
sqlite> select soundex(savio);
SQL error: no such column: savio
sqlite> select soundex();
SQL error: no such function: soundex
sqlite> select soundex;
SQL error: no such column: soundex
The program is below:
#include
#include
#include
#include
void soundex(sqlite3_context* ctx, int nargs, sqlite3_value** values) {
int i,j;
char c,r;
int d;
int count;
char* str2;
char* result;
int dim;
const char* str;
char ret[4];
str = sqlite3_value_text(values[0]);
dim = strlen(str);
for (i=0;i for (i=0;i<=dim;i++) str2[i] = toupper(str2[i]);
for (i=0;i<=dim;i++)
switch (str[i]) {
case 'A': case 'E': case 'I': case 'O':
case 'U': case 'H': case 'W': case 'Y': str2[i] = '0';
}
for (i=1;i
switch (str2[i]) {
case 'B': case 'F':
case 'P': case 'V': str2[i] = '1'; break;
case 'C': case 'G':
case 'J': case 'K':
case 'Q': case 'S':
case 'X': case 'Z': str2[i] = '2'; break;
case 'D': case 'T': str2[i] = '3'; break;
case 'L': str2[i] = '4'; break;
case 'M': case 'N': str2[i] = '5'; break;
case 'R': str2[i] = '6'; break;
}
count=1;
for (i=0;i
if (str2[i] != str2[i+1]) count++;
result = malloc(count);
j=0;
for (i=0;i
if (str2[i] != str2[i+1]) {
result[j]=str2[i];
j++;
}
for (i=0;i<4;i++) printf("%c", result[i]);
for (i=0;i<4;i++) ret[i] = result[i];
printf("\n\n");
sqlite3_result_text(ctx,ret, 4, SQLITE_TRANSIENT);
}
int main(int argc, char* argv[]) {
int rc;
char* sql;
sqlite3* db;
sqlite3_stmt* stmt;
const char* tail;
rc = sqlite3_open("dbforext.db",&db);
if (rc) {
fprintf(stderr, "E' impossibile aprire il file %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
sqlite3_create_function(db, "soundex", 1, SQLITE_UTF8, NULL, soundex, NULL,
NULL);
sql = "select soundex(saverio);";
sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail);
if (rc != SQLITE_OK) {
fprintf(stderr, "Errore SQL: %s\n", sqlite3_errmsg(db));
}
rc = sqlite3_step(stmt);
sqlite3_close(db);
return 0;
}
-
Inviato da Yahoo! Mail.
La casella di posta intelligente.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
dark0s dark0s <[EMAIL PROTECTED]> wrote: I have a new problem, I am attempting
add extension function to sqlite3.
My program labsinf.c now build successfully, but I didn't add my extension. I
don't understand what it is miss.
Help me please.
The program labsinf.c make inf, and after I typed ./inf, it makes dbforext.db.
Some output is below:
bash-3.1# gcc -lsqlite3 labsinf.c -o inf
bash-3.1# ./inf
bash-3.1# sqlite3 dbforext.db
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> select soundex(saverio);
SQL error: no such column: saverio
sqlite> select soundex(savio);
SQL error: no such column: savio
sqlite> select soundex();
SQL error: no such function: soundex
sqlite> select soundex;
SQL error: no such column: soundex
The program is below:
#include
#include
#include
#include
void soundex(sqlite3_context* ctx, int nargs, sqlite3_value** values) {
int i,j;
char c,r;
int d;
int count;
char* str2;
char* result;
int dim;
const char* str;
char ret[4];
str = sqlite3_value_text(values[0]);
dim = strlen(str);
for (i=0;i for (i=0;i<=dim;i++) str2[i] = toupper(str2[i]);
for (i=0;i<=dim;i++)
switch (str[i]) {
case 'A': case 'E': case 'I': case 'O':
case 'U': case 'H': case 'W': case 'Y': str2[i] = '0';
}
for (i=1;i
switch (str2[i]) {
case 'B': case 'F':
case 'P': case 'V': str2[i] = '1'; break;
case 'C': case 'G':
case 'J': case 'K':
case 'Q': case 'S':
case 'X': case 'Z': str2[i] = '2'; break;
case 'D': case 'T': str2[i] = '3'; break;
case 'L': str2[i] = '4'; break;
c
Re: [sqlite] Left Join help
Andy Smith wrote: > I have quiet a few queries similar to this doing multiple Left Joins and > they run extremely slow > 6 secs. Is there a better way to be writing > the below query for sqlite. > Andy, You didn't show your table and index definitions, so it's hard to be sure about what would be best. The following trace shows what I think your tables should look like, and a couple of indexes that will help for a slightly rearranged version of your query. The query plan uses the indexes to locate the subset of the records with type=0, then checks their title, next it uses the other index to find the matching records and checks that their type=1. These records should lead directly to the required container records using the primary key on that table. I would suspect this is reasonably fast. Let me know if it helps. HTH Dennis Cote SQLite version 3.5.7 Enter ".help" for instructions sqlite> sqlite> create table container ( ...> id integer primary key, ...> title text ...> ); sqlite> sqlite> create table mediaitem ( ...> id integer primary key, ...> title text, ...> type integer, ...> containerID integer references container, ...> genreID integer references container, ...> thumbnailID integer references container, ...> releaseYearID integer references container, ...> artistID integer references container, ...> dateAdded, ...> url, ...> path, ...> mimeType, ...> width, ...> height ...> ); sqlite> sqlite> create index media_type on mediaitem(type); sqlite> create index media_container on mediaitem(containerID); sqlite> sqlite> explain query plan ...> SELECT ...> a.id, ...> a.title, ...> a.type, ...> a.dateAdded, ...> a.url, ...> a.path, ...> a.containerID, ...> a.mimeType, ...> a.width, ...> a.height, ...> a.genreID, ...> a.thumbnailID, ...> a.releaseYearID, ...> a.artistID, ...> w.title AS containerName, ...> x.title AS genreName, ...> y.title AS releaseYearName, ...> z.title AS artistName ...> FROM mediaitem AS b ...> LEFT JOIN mediaitem AS a ON b.containerID=a.containerID ...> LEFT JOIN container AS w ON w.id=a.containerID ...> LEFT JOIN container AS x ON x.id=a.genreID ...> LEFT JOIN container AS y ON y.id=a.releaseYearID ...> LEFT JOIN container AS z ON z.id=a.artistID ...> WHERE b.title LIKE 'Opus%' ESCAPE '\' ...> AND b.type=0 ...> AND a.type=1 ...> ORDER BY a.title, a.id ...> LIMIT 0,9; 0|0|TABLE mediaitem AS b WITH INDEX media_type 1|1|TABLE mediaitem AS a WITH INDEX media_container 2|2|TABLE container AS w USING PRIMARY KEY 3|3|TABLE container AS x USING PRIMARY KEY 4|4|TABLE container AS y USING PRIMARY KEY 5|5|TABLE container AS z USING PRIMARY KEY ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Left Join help
Andy Smith <[EMAIL PROTECTED]> wrote: > I have quiet a few queries similar to this doing multiple Left Joins > and > they run extremely slow > 6 secs. Is there a better way to be writing > the below query for sqlite. > > > > SELECT a.id, a.title, a.type, a.dateAdded, a.url, a.path, > a.containerID, a.mimeType, a.width, a.height, a.genreID, > a.thumbnailID, > a.releaseYearID, a.artistID, w.title AS containerName, x.title AS > genreName, y.title AS releaseYearName, z.title AS artistName FROM > mediaitem AS a LEFT JOIN mediaitem AS b ON b.containerID=a.containerID > LEFT JOIN container AS w ON w.id=a.containerID LEFT JOIN container AS > x > ON x.id=a.genreID LEFT JOIN container AS y ON y.id=a.releaseYearID > LEFT > JOIN container AS z ON z.id=a.artistID WHERE a.type=1 AND b.type=0 AND > b.title LIKE 'Opus%' ESCAPE '\' ORDER BY a.title, a.id LIMIT 0,9; There's no point to use LEFT JOIN between a and b. Your WHERE clause discards all records where b fields are NULLs anyway. See if changing it to plain old JOIN helps. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Addition extension function failed!
I have a new problem, I am attempting add extension function to sqlite3.
My program labsinf.c now build successfully, but I didn't add my extension. I
don't understand what it is miss.
Help me please.
The program labsinf.c make inf, and after I typed ./inf, it makes dbforext.db.
Some output is below:
bash-3.1# gcc -lsqlite3 labsinf.c -o inf
bash-3.1# ./inf
bash-3.1# sqlite3 dbforext.db
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> select soundex(saverio);
SQL error: no such column: saverio
sqlite> select soundex(savio);
SQL error: no such column: savio
sqlite> select soundex();
SQL error: no such function: soundex
sqlite> select soundex;
SQL error: no such column: soundex
The program is below:
#include
#include
#include
#include
void soundex(sqlite3_context* ctx, int nargs, sqlite3_value** values) {
int i,j;
char c,r;
int d;
int count;
char* str2;
char* result;
int dim;
const char* str;
char ret[4];
str = sqlite3_value_text(values[0]);
dim = strlen(str);
for (i=0;ihttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Left Join help
I have quiet a few queries similar to this doing multiple Left Joins and they run extremely slow > 6 secs. Is there a better way to be writing the below query for sqlite. SELECT a.id, a.title, a.type, a.dateAdded, a.url, a.path, a.containerID, a.mimeType, a.width, a.height, a.genreID, a.thumbnailID, a.releaseYearID, a.artistID, w.title AS containerName, x.title AS genreName, y.title AS releaseYearName, z.title AS artistName FROM mediaitem AS a LEFT JOIN mediaitem AS b ON b.containerID=a.containerID LEFT JOIN container AS w ON w.id=a.containerID LEFT JOIN container AS x ON x.id=a.genreID LEFT JOIN container AS y ON y.id=a.releaseYearID LEFT JOIN container AS z ON z.id=a.artistID WHERE a.type=1 AND b.type=0 AND b.title LIKE 'Opus%' ESCAPE '\' ORDER BY a.title, a.id LIMIT 0,9; Thanks, Andy This message is confidential to Prodea Systems, Inc unless otherwise indicated or apparent from its nature. This message is directed to the intended recipient only, who may be readily determined by the sender of this message and its contents. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient:(a)any dissemination or copying of this message is strictly prohibited; and(b)immediately notify the sender by return message and destroy any copies of this message in any form(electronic, paper or otherwise) that you have.The delivery of this message and its information is neither intended to be nor constitutes a disclosure or waiver of any trade secrets, intellectual property, attorney work product, or attorney-client communications. The authority of the individual sending this message to legally bind Prodea Systems is neither apparent nor implied,and must be independently verified. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Direct access to Btree routines in SQLite
On Apr 9, 2008, at 10:26 AM, Aladdin Lampé wrote: > > Hi Phil and list! > > Thank you for this very instructive post about SQLite's internals > and btrees. I'm just curious about what could be real-life use cases > of having direct access to the btree stuff. As I understand your > example, you store (key,value) pairs inside the btree and then get > them back. Then, what's the purpose of not using a normal SQLite > table to do so? > > On the other hand, since those "direct btree information" are > necessarily stored inside a reguar SQLite file, doesn't this > introduce possible side effects with other functions of the sqlite > library, which would not be aware that some btree roots inside the > file are neither a table nor an index? (vacuum, etc.) > > Last but not least, I am currently developping a virtual table and > I've just realized that I could use SQLite btrees for indexing data > coming from an "external database" (cf recent thread about virtual > tables and access to big external databases). > According to you, would it be a good idea to use this technique in > order to implement an alternative indexing technique (for "external > tables") based on SQLite btrees? > > Thanks a lot for sharing about that, any help would be greatly > appreciated, The use of SQLite's internal BTree routines by external applications is fraught with peril and is strongly discouraged. The BTree interface changes, sometimes in very subtle ways, without notice and with no documentation apart from comments on the code. And the BTree routines are not tested except for the usages modes employed by SQLite itself. If you ignore this advice and decide to use the BTree routines directly, and your code breaks or malfunctions due to some unseen subtly or some future change, then the rule is: "No Tears". D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is performance of v3.5.7 improved with new bitvec?
Hi all! Following the recent thread "Virtual tables used to query big external database", and the discussion with Mike Owens and Jay A. Kreibich, it seems that : - The "old" way of dealing with dirty pages with bitmaps limited SQLite to an approximate maximal capacity of 10s of GBs, as opposed to therical TBs, because it imposed to malloc 256 bytes for every 1Mb of database during each transaction. - The "new" way of dealing with dirty pages with a bitvec structure (introduced in SQLite v3.5.7) allows for sparse bitmaps and is then supposed to push away the "10s of GBs" limit. Now the questions are: 1) What are the new practical limits with SQLite v3.5.7? 2) Does somebody have any real-life experience (or home-made tests and figures) on SQLite v3.5.7 and really big tables? (say 100 000 000 lines). 3) Does the new "bitvec" algorithm really help with such a big table? I am mainly interested in performance of INSERTs (for creating the big table) and SELECTs (for queries). UPDATEs, DROPs, TRIGGERs etc. have a lower priority in my case. Those questions are really important for me because if SQLite is now able to handle really big tables, I no longer need to implement my own "virtual table" in order to link SQLite to a "big external database"... because I could directly use SQLite itself for the whole application! (no virtual table and no "external" database needed). Thank you for any help about that subject. Have a nice day, Aladdin _ Découvrez les profils Messenger de vos amis ! http://home.services.spaces.live.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inserting with related items that may not exist yet
[EMAIL PROTECTED] wrote: > I'm new to SQLite and SQL in general and I have a question about > handling foreign key relationships when adding items to a database > when the related items may not already exist. For example, if I have > two tables: foods with "id", "name", and "type_id" columns and > food_types with "id" and "name" columns. The "type_id" of the foods > table relates to the "id" of the food_types table. > > If I want to insert a new row in the foods table, but it has a food > type that's not already in the food_types table, what's the best way > to add it? Should I first insert a new row into the food_types table > then use last_insert_rowid() to get the type_id to insert into the > foods table? Sometimes the food_type will already exist so should I do > a SELECT first to see if it exists and if not then do the insert > (assuming all within a single transaction to avoid a race if another > thread is also inserting)? This feels like the wrong way to do it. > No, that is exactly the right way to do it. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update
Mahalakshmi.m wrote: > > "CREATE TABLE ALBUM(AlbumId INTEGER PRIMARY KEY NOT NULL,AlbumName TEXT NOT > NULL COLLATE NOCASE ,AlbumTrackCount INTEGER,UNIQUE(AlbumName));" > > AlbumId AlbumName AlbumTrackCount > 1 aaa 3 > 2 ddd 2 > 3 ccc 1 > > Here I am maintaining the Number of track for that particular Album in > AlbumTrackCount. Bcoz I need to find the total number of track so instead of > using "select Count(*)from MUSIC where Album_Id = 1 ;" I will just > read > the AlbumTrackCount from ALBUM table.This speed up my performance. > > "CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT > NULL,Album_Id INTEGER);" > > IdTrack Album_Id > 1 t1 1 > 2 t2 1 > 3 t3 1 > 4 t4 2 > 5 t5 2 > 6 t6 3 > > I want to update all the Album to some new name say 'xxx' then i have to > delete all the records in ALBUM table and to insert one new Album with name > as 'xxx' and the AlbumTrackCount should now become 7.After that I have to > change the Album_Id in MUSIC also. > > So after updating > AlbumId AlbumName AlbumTrackCount > 1 xxx 7 > And all the Album_Id value should be 1. > > Can any one help to solve this. > I'm not sure why you want to do this, but if that's what you want to do I would suggest using triggers to maintain the counts in the album table. These triggers would also delete unreferenced records. create trigger up_music_album after update of Album_Id on MUSIC begin update ALBUM set AlbumTrackCount = AlbumTrackCount - 1 where AlbumId = Old.Album_id; update ALBUM set AlbumTrackCount = AlbumTrackCount + 1 where AlbumId = New.Album_id; delete from ALBUM where AlbumTrackCount = 0; end; create trigger in_music after insert on MUSIC begin update ALBUM set AlbumTrackCount = AlbumTrackCount + 1 where AlbumId = New.Album_id; end; create trigger del_music after delete on MUSIC begin update ALBUM set AlbumTrackCount = AlbumTrackCount - 1 where AlbumId = Old.Album_id; delete from ALBUM where AlbumTrackCount = 0; end; With these triggers in place your update becomes one update to change the name of the album you want to retain, and another to set every music record that doesn't already refer to that album so that it does refer to the retained record. The update trigger will decrement the count of the album records and remove them when they are no longer referenced by the music table. update ALBUM set AlbumName = 'xxx' where AlbumId = 1; update MUSIC set Album_Id = 1 where Album_id != 1; HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update
That is such a simple update statement. Have you tried writing one yourself? Get a good book on the SQL language and you'll learn. I think most people on the list do not mind helping but this is just actually doing the coding for you. And you won't learn anything if someone else does the work, right? So dig in make an attempt and if that fails ask "whats wrong with my attempt"? HTH, Ken "Mahalakshmi.m" <[EMAIL PROTECTED]> wrote: Hi, "CREATE TABLE ALBUM(AlbumId INTEGER PRIMARY KEY NOT NULL,AlbumName TEXT NOT NULL COLLATE NOCASE ,AlbumTrackCount INTEGER,UNIQUE(AlbumName));" AlbumId AlbumName AlbumTrackCount 1 aaa 3 2 ddd 2 3 ccc 1 Here I am maintaining the Number of track for that particular Album in AlbumTrackCount. Bcoz I need to find the total number of track so instead of using "select Count(*) from MUSIC where Album_Id = 1 ;" I will just read the AlbumTrackCount from ALBUM table.This speed up my performance. "CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT NULL,Album_Id INTEGER);" Id Track Album_Id 1 t1 1 2 t2 1 3 t3 1 4 t4 2 5 t5 2 6 t6 3 I want to update all the Album to some new name say 'xxx' then i have to delete all the records in ALBUM table and to insert one new Album with name as 'xxx' and the AlbumTrackCount should now become 7.After that I have to change the Album_Id in MUSIC also. So after updating AlbumId AlbumName AlbumTrackCount 1 xxx 7 And all the Album_Id value should be 1. Can any one help to solve this. ___ 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] Direct access to Btree routines in SQLite
Hi Phil and list!
Thank you for this very instructive post about SQLite's internals and btrees.
I'm just curious about what could be real-life use cases of having direct
access to the btree stuff. As I understand your example, you store (key,value)
pairs inside the btree and then get them back. Then, what's the purpose of not
using a normal SQLite table to do so?
On the other hand, since those "direct btree information" are necessarily
stored inside a reguar SQLite file, doesn't this introduce possible side
effects with other functions of the sqlite library, which would not be aware
that some btree roots inside the file are neither a table nor an index?
(vacuum, etc.)
Last but not least, I am currently developping a virtual table and I've just
realized that I could use SQLite btrees for indexing data coming from an
"external database" (cf recent thread about virtual tables and access to big
external databases).
According to you, would it be a good idea to use this technique in order to
implement an alternative indexing technique (for "external tables") based on
SQLite btrees?
Thanks a lot for sharing about that, any help would be greatly appreciated,
Aladdin
> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Date: Mon, 7 Apr 2008 19:28:30 -0500
> Subject: [sqlite] Direct access to Btree routines in SQLite
>
> I am developing an application that needs both SQL capability and also
> simple Btree functions. I searched the SQLite FAQ for information about
> direct access to the Btree level, but was unable to find any help or
> examples. I have successfully gained access to the Btree routines after
> making minor changes to the amalgamation source which I then compiled with
> MS Visual Studio and linked with my application.
>
> Note: Since the Btree routines are only semi-documented, and my procedure
> requires changes to the SQLite source, there is no guarantee that this will
> work with future versions.
>
> Changes to SQLite amalgamation code:
>
> 1. Insert the following definition to make the routines externally callable
> (rather than static):
>
> #define SQLITE_PRIVATE
>
> 2. Extract the embedded Btree.h header file from the amalgamation and create
> a Btree.h file. The embedded section to be extracted is enclosed in:
> _BTREE_H_ Use the Btree.h file with your application along with sqlite3.h.
>
> 3. Add the following routine which returns a pointer to the Btree structure
> connected to a specified database handle:
>
> /*--
>
> * Get a pointer to the Btree structure associated with an entry in the
> database table.
> */
> int sqlite3GetBtreePointer(sqlite3 *db, int dbIndex, Btree **pBt) {
> Db *pDb;
> /*
> * Get the Btree handle out of the database table.
> */
> pDb = &db->aDb[dbIndex];
> *pBt = pDb->pBt;
> /*
> * Finished
> */
> return(SQLITE_OK);
>
> -- This is all the changes required to SQLite --
>
> Here is an example program that creates a database with a Btree table,
> writes a couple of records to it and then verifies that it can be accessed.
> The routine can be called to either create a new database or open and check
> an existing one.
>
> /*---
> * Btree test.
> *
> * Input arguments:
> * Create = true to create a new database with a Btree table.
> * Create = false to open an existing database and check it.
> */
> void BtreeTest(bool Create)
> {
> static char *FileName = "C:\\Test\\Btree.db";
> int status,Result;
> unsigned int DataSize;
> Btree *bt;
> sqlite3 *db;
> BtCursor *BtCursor;
> sqlite3_stmt *pStmt;
> int BtPage = -1;
> char *Key1 = "1";
> char *Data1 = "Record 1";
> char *Key2 = "2";
> char *Data2 = "Record 2";
> char buf[100],Command[200];
>
> /*
> * Decide if the test run should create the database and table or open an
> existing one.
> */
> if (Create) {
> /*
> * Create a database with a Btree table.
> */
> /* Start with a new database */
> DeleteFile(FileName);
> /* Create a new database */
> status =
> sqlite3_open_v2(FileName,&db,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE,0);
> /* Get a pointer to the Btree for the primary database (# 0) */
> status = sqlite3GetBtreePointer(db,0,&bt);
> /* Create a Btree table within the database. We get back the root
> page in BtPage */
> status = sqlite3BtreeBeginTrans(bt,1);
> status = sqlite3BtreeCreateTable(bt,&BtPage,0);
> status = sqlite3BtreeCommit(bt);
> /* Write the root page number of the Btree to a table so that we can
> find it later */
> status = sqlite3_exec(db,"CREATE TABLE Configuration (BtreeName TEXT
> UNIQUE, RootPage INTEGER)",0,0,0);
> sprintf(Command,"INSERT INTO Configuration VALUES
> (\'MyBtree\',%d)",BtPage);
> status = sqlite3_exec(db,Command,0,0,0);
> /* Create a cursor to go with the Btree (BtPage is the root page
> index #) */
> status = sqlite3BtreeCursor(bt,BtPage,1,0,0,&BtCursor);
> /* Write a couple of records to the Btree table */
> st
[sqlite] upgrade sqlite
I have just instaleld apache 2.2 and php 5.2.5 on a windows xp machine. I would also like the current version of sqlite. running sqlite3.exe gives me a verion of 3.3.15. Is this the version of sqlite3.exe or of the dqlite database engine? If this is the engine version how do I upgrade it? I do not see a sqlite.dll file anywhere. I have also googled this topic and have come up empty. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does SQLite require 64-bit integers?
Gerhard Häring wrote: > I need to know if SQLite works at all if the platform doesn't have a > 64-bit integer type. > > I see that SQLite has some #ifdefing like > > #ifdef SQLITE_INT64_TYPE > > that seems to be for the case when the platform does not have the type > natively, but I don't see where this would actually be implemented. > > If SQLite does indeed require the platform to have a native 64-bit > integer type, then I can remove a few untested and silly #ifdef > HAVE_LONG_LONG parts from my own code. I wonder why nobody bothered to answer. A closer look at the SQLite source code suggests that the #ifdef is useless and SQLite indeed requires the C compiler to support long longs (64 bit integers). -- Gerhard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Inserting with related items that may not exist yet
I'm new to SQLite and SQL in general and I have a question about handling foreign key relationships when adding items to a database when the related items may not already exist. For example, if I have two tables: foods with "id", "name", and "type_id" columns and food_types with "id" and "name" columns. The "type_id" of the foods table relates to the "id" of the food_types table. If I want to insert a new row in the foods table, but it has a food type that's not already in the food_types table, what's the best way to add it? Should I first insert a new row into the food_types table then use last_insert_rowid() to get the type_id to insert into the foods table? Sometimes the food_type will already exist so should I do a SELECT first to see if it exists and if not then do the insert (assuming all within a single transaction to avoid a race if another thread is also inserting)? This feels like the wrong way to do it. Or is there a better or more automated way to handle this? My database will have several related columns like this. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WinCE Memory Problem
You may want to read the docs for the following functions:
- sqlite3_release_memory: http://www.sqlite.org/c3ref/release_memory.html
- sqlite3_soft_heap_limit: http://www.sqlite.org/c3ref/soft_heap_limit.html
Additionally some pragmas may help, if properly used:
http://www.sqlite.org/pragma.html
- pragma cache_size=number-of-pages;
- pragma default-cache-size=number-of-pages;
Additionally you should wrap the insert into a transaction to improve
performance.
Mike
-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Yang WenYuan
Gesendet: Mittwoch, 9. April 2008 10:23
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] WinCE Memory Problem
Now, I change my code as follows, but, the problem is still there. The PDA
memory usage become larger and larger. Did I miss something? Anything wrong
with my code? Thanks.
sqlcmd = sqlite3_mprintf( "INSERT INTO TEST( BARCODE, TAGTYPE,
PRINTDATA ) VALUES( ?, ?, ? );" );
rc = sqlite3_prepare( db, sqlcmd, -1, &stat, 0 );
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}
for( i = 0 ; i < 20; i ++ )
{
// ...
// chrBarCode, tagtype and chrPrintData is changed for every
loop
// ...
rc = sqlite3_bind_text( stat, 1, chrBarCode, strlen(
chrBarCode ), NULL );
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}
rc = sqlite3_bind_int( stat, 2, tagtype );
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}
rc = sqlite3_bind_blob( stat, 3, chrPrintData, length, NULL
);
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}
rc = sqlite3_step( stat );
if( rc != SQLITE_DONE )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}
rc = sqlite3_reset( stat );
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}
}
rc = sqlite3_finalize( stat );
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}
Best Regards,
WenYuan
--- Michael Ruck <[EMAIL PROTECTED]> wrote:
> No, you only need to create the statement once. And bind all variables
> inside the loop. This at least saves computation time. Additionally
> you should use sqlite3_mprintf instead of sprintf to protect against
> sql injection (if that is an issue for you.)
>
> You're already doing it with the blob, why not with the other fields?
>
> Mike
>
>
> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Im Auftrag von Yang WenYuan
> Gesendet: Mittwoch, 9. April 2008 08:11
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] WinCE Memory Problem
>
> Thanks, Mike.
>
> Because, each record has different barcode, tagtype, and printdata.
> That means, I need to call:
>
> sprintf( sqlcmd, "INSERT INTO TEST( BARCODE, TAGTYPE, PRINTDATA )
> VALUES( %s, %d, ? );", chrBarCode, tagtype ); rc = sqlite3_prepare(
> db, sqlcmd, -1, &stat, 0 );
>
> to update the contents of the each records inside the loop. Am I
> right?
>
> Any suggestion? Thanks.
>
> WenYuan
>
>
>
>
>
> --- Michael Ruck <[EMAIL PROTECTED]>
> wrote:
>
> > You should only prepare the statement once before
> the loop. The only
> > thing you should do in the loop itself is bind
> varying data and call
> > sqlite_step.
> > There's no need to call prepare, reset, finalize
> inside the loop. If
> > chrPrintData doesn't change you can also move
> bind_blob in front of
> > the loop.
> >
> > Mike
> >
> > -Ursprüngliche Nachricht-
> > Von: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] Im
> Auftrag von Yang WenYuan
> > Gesendet: Mittwoch, 9. April 2008 06:08
> > An: sqlite-users@sqlite.org
> > Betreff: [sqlite] WinCE Memory Problem
> >
> > I use the Sqlite in the PDA which is WinCE OS. I
> need to add more than
> > 200,000 records. I used sqlite3_prepare->
> > sqlite3_bind_blob->sqlite3_step->sqlite3_finalize
> to write each record
> > in to Database. However, I found that after each
> record is inserted,
Re: [sqlite] WinCE Memory Problem
Now, I change my code as follows, but, the problem is
still there. The PDA memory usage become larger and
larger. Did I miss something? Anything wrong with my
code? Thanks.
sqlcmd = sqlite3_mprintf( "INSERT INTO TEST( BARCODE,
TAGTYPE, PRINTDATA ) VALUES( ?, ?, ? );" );
rc = sqlite3_prepare( db, sqlcmd, -1, &stat, 0 );
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}
for( i = 0 ; i < 20; i ++ )
{
// ...
// chrBarCode, tagtype and chrPrintData is changed
for every loop
// ...
rc = sqlite3_bind_text( stat, 1, chrBarCode, strlen(
chrBarCode ), NULL );
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}
rc = sqlite3_bind_int( stat, 2, tagtype );
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}
rc = sqlite3_bind_blob( stat, 3, chrPrintData,
length, NULL );
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}
rc = sqlite3_step( stat );
if( rc != SQLITE_DONE )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}
rc = sqlite3_reset( stat );
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}
}
rc = sqlite3_finalize( stat );
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}
Best Regards,
WenYuan
--- Michael Ruck <[EMAIL PROTECTED]> wrote:
> No, you only need to create the statement once. And
> bind all variables
> inside the loop. This at least saves computation
> time. Additionally you
> should use sqlite3_mprintf instead
> of sprintf to protect against sql injection (if that
> is an issue for you.)
>
> You're already doing it with the blob, why not with
> the other fields?
>
> Mike
>
>
> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Im Auftrag
> von Yang WenYuan
> Gesendet: Mittwoch, 9. April 2008 08:11
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] WinCE Memory Problem
>
> Thanks, Mike.
>
> Because, each record has different barcode, tagtype,
> and printdata. That
> means, I need to call:
>
> sprintf( sqlcmd, "INSERT INTO TEST( BARCODE,
> TAGTYPE, PRINTDATA ) VALUES(
> %s, %d, ? );", chrBarCode, tagtype ); rc =
> sqlite3_prepare( db, sqlcmd, -1,
> &stat, 0 );
>
> to update the contents of the each records inside
> the loop. Am I right?
>
> Any suggestion? Thanks.
>
> WenYuan
>
>
>
>
>
> --- Michael Ruck <[EMAIL PROTECTED]>
> wrote:
>
> > You should only prepare the statement once before
> the loop. The only
> > thing you should do in the loop itself is bind
> varying data and call
> > sqlite_step.
> > There's no need to call prepare, reset, finalize
> inside the loop. If
> > chrPrintData doesn't change you can also move
> bind_blob in front of
> > the loop.
> >
> > Mike
> >
> > -Ursprüngliche Nachricht-
> > Von: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] Im
> Auftrag von Yang WenYuan
> > Gesendet: Mittwoch, 9. April 2008 06:08
> > An: sqlite-users@sqlite.org
> > Betreff: [sqlite] WinCE Memory Problem
> >
> > I use the Sqlite in the PDA which is WinCE OS. I
> need to add more than
> > 200,000 records. I used sqlite3_prepare->
> > sqlite3_bind_blob->sqlite3_step->sqlite3_finalize
> to write each record
> > in to Database. However, I found that after each
> record is inserted,
> > the PDA memory became larger and larger. In the
> end, the whole PDA
> > memory is occupied by this application and the
> system halt. Is there
> > any thing I miss to release the memory?
> > Following is my code:
> >
> > sqlite3 *db;
> > sqlite3_stmt * stat;
> > char *zErrMsg = 0;
> > char sqlcmd[ 512 ];
> > int rc;
> > char chrBarCode[ 16 ], chrPrintData[ 512 ]; int
> tagtype;
> >
> >
> >
> > for( i = 0; i < 200; i ++ )
> > {
> > sprintf( sqlcmd, "INSERT INTO TEST( BARCODE,
> TAGTYPE, PRINTDATA )
> > VALUES( %s, %d, ? );", chrBarCode, tagtype );
> >
> > rc = sqlite3_prepare( db, sqlcmd, -1, &stat, 0 );
> > if( rc
[sqlite] Why "Primary Key" and "rowid" column appear more than one time?
I just create table as below:
CREATE TABLE msglog (uuid varchar primary key,uid varchar,...)
After several days run, I found something very strange in it;
I exec sql: select rowid,* from msglog where rowid >53 and rowid < 55 and
msgtype='TM' order by uuid
then I get two rows:
rowid uuidtime
54 {EE44A54B-6502-4566-A513-D167255BDB7E} 1207635900 TM
54 {EE44A54B-6502-4566-A513-D167255BDB7E} 1207636764 TM
I exec sql: select rowid,* from msglog where rowid=54 and msgtype='TM'
order by uuid
then I get this:
rowid uuidtime
54 {EE44A54B-6502-4566-A513-D167255BDB7E} 1207636764 TM
why primary key and rowid can appear twice?
anybody know this?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WinCE Memory Problem
No, you only need to create the statement once. And bind all variables
inside the loop. This at least saves computation time. Additionally you
should use sqlite3_mprintf instead
of sprintf to protect against sql injection (if that is an issue for you.)
You're already doing it with the blob, why not with the other fields?
Mike
-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Yang WenYuan
Gesendet: Mittwoch, 9. April 2008 08:11
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] WinCE Memory Problem
Thanks, Mike.
Because, each record has different barcode, tagtype, and printdata. That
means, I need to call:
sprintf( sqlcmd, "INSERT INTO TEST( BARCODE, TAGTYPE, PRINTDATA ) VALUES(
%s, %d, ? );", chrBarCode, tagtype ); rc = sqlite3_prepare( db, sqlcmd, -1,
&stat, 0 );
to update the contents of the each records inside the loop. Am I right?
Any suggestion? Thanks.
WenYuan
--- Michael Ruck <[EMAIL PROTECTED]> wrote:
> You should only prepare the statement once before the loop. The only
> thing you should do in the loop itself is bind varying data and call
> sqlite_step.
> There's no need to call prepare, reset, finalize inside the loop. If
> chrPrintData doesn't change you can also move bind_blob in front of
> the loop.
>
> Mike
>
> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Im Auftrag von Yang WenYuan
> Gesendet: Mittwoch, 9. April 2008 06:08
> An: sqlite-users@sqlite.org
> Betreff: [sqlite] WinCE Memory Problem
>
> I use the Sqlite in the PDA which is WinCE OS. I need to add more than
> 200,000 records. I used sqlite3_prepare->
> sqlite3_bind_blob->sqlite3_step->sqlite3_finalize to write each record
> in to Database. However, I found that after each record is inserted,
> the PDA memory became larger and larger. In the end, the whole PDA
> memory is occupied by this application and the system halt. Is there
> any thing I miss to release the memory?
> Following is my code:
>
> sqlite3 *db;
> sqlite3_stmt * stat;
> char *zErrMsg = 0;
> char sqlcmd[ 512 ];
> int rc;
> char chrBarCode[ 16 ], chrPrintData[ 512 ]; int tagtype;
>
>
>
> for( i = 0; i < 200; i ++ )
> {
> sprintf( sqlcmd, "INSERT INTO TEST( BARCODE, TAGTYPE, PRINTDATA )
> VALUES( %s, %d, ? );", chrBarCode, tagtype );
>
> rc = sqlite3_prepare( db, sqlcmd, -1, &stat, 0 );
> if( rc != SQLITE_OK )
> {
> sqlite3_close(db);
> return -1;
> }
>
> rc = sqlite3_bind_blob( stat, 1, chrPrintData, length, NULL );
> if( rc != SQLITE_OK )
> {
> sqlite3_close(db);
> return -1;
> }
>
> rc = sqlite3_step( stat );
> if( rc != SQLITE_DONE )
> {
> sqlite3_close(db);
> return -1;
> }
>
> rc = sqlite3_reset( stat );
> if( rc != SQLITE_OK )
> {
> sqlite3_close(db);
> return -1;
> }
>
> rc = sqlite3_finalize( stat );
> if( rc != SQLITE_OK )
> {
> sqlite3_close(db);
> return -1;
> }
>
> }
>
>
> WenYuan
>
>
>
>
>
__
> Search, browse and book your hotels and flights through Yahoo! Travel.
> http://sg.travel.yahoo.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
>
__
Search, browse and book your hotels and flights through Yahoo! Travel.
http://sg.travel.yahoo.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
