Re: [sqlite] sqlite3 - DB gets locked
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Vinod Nanjaiah wrote: > a> Is there any way to unlock a DB that has got locked? > b> What are the usual reasons for a DB getting locked? > c> Is there any DB setting that would prevent the DB from getting locked? http://www.sqlite.org/lockingv3.html It is the first result of http://www.google.com/search?q=sqlite+db+locked Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAksV/XUACgkQmOOfHg372QR+zwCfQaGC6lMxNvmk9I3EP/HcemK2 7MQAn3wNc+ZBMkVniqtjrbMBXjvEnbNj =W4q8 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 - DB gets locked
Hi, I am using sqlite-3.6.19. I find that the Database that I create gets locked often. There are about 9 processes that access my DB. I have written a wrapper for the sqlite3 library calls in which I am doing the DB open and DB close for every operation (read/write) on the DB. a> Is there any way to unlock a DB that has got locked? b> What are the usual reasons for a DB getting locked? c> Is there any DB setting that would prevent the DB from getting locked? Thank you, Vinod http://www.mindtree.com/email/disclaimer.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow JOIN on two indexed text fields. Why?????
Thank you! That was it. I've been pulling my hair out over this all day. I should have seen it. I've never used STRING in my own tables and I inherited this from someone else and didn't even think twice that the type difference would be the issue. Thanks you again. sorka wrote: > > This is driving me nuts. I have two tables I'm trying to join together on > two text fields. > > CREATE TABLE tmp_role ( programId INTEGER, > roleNameINTEGER, > positionINTEGER, > isNew BOOL, > personIdINTEGER, > nameSTRING); > > This table has up to a few dozen records at any one time. > > AND > > CREATE TABLE person ( > personId INTEGER PRIMARY KEY, > name text UNIQUE > ); > > This table has 10s of thousands of records. > > If I do this query: > SELECT person.ROWID FROM tmp_role JOIN person ON tmp_role.name = > person.name; > > to find the ROWID of each row in person who's name matches that of the > name in tmp_role, it takes about 1 second per matcha really long time. > > However, if I instead take each of names in tmp_role and do a seperate > select like this: > SELECT ROWID FROM person WHERE name = "Carell|Steve"; > > and do it for each name, the search takes only a few ms for few dozen > records in tmp_role. > > Now the real problem I'm trying to solve is an UPDATE like this: > > UPDATE tmp_role SET personId = (SELECT ROWID FROM person WHERE > tmp_role.name = person.name); > > If I break this up into a bunch of different statements to iterate through > the records in tmp_role and then execute a single statement for each name, > I can accomplish this update statement fairly quickly, but as it is > authored above, it's taking about 24 seconds for 24 records or about 1000 > times longer than if I do it the long way :( > > > > > > -- View this message in context: http://old.nabble.com/Slow-JOIN-on-two-indexed-text-fields.-Why--tp26601433p26602612.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] Slow JOIN on two indexed text fields. Why?????
Igor Tandetnik wrote: > sorka wrote: >> This is driving me nuts. I have two tables I'm trying to join >> together on two text fields. >> >> CREATE TABLE tmp_role ( programId INTEGER, >> roleNameINTEGER, >> positionINTEGER, >> isNew BOOL, >> personIdINTEGER, >> nameSTRING); >> >> This table has up to a few dozen records at any one time. >> >> AND >> >> CREATE TABLE person ( >>personId INTEGER PRIMARY KEY, >>name text UNIQUE >> ); >> >> This table has 10s of thousands of records. >> >> If I do this query: >> SELECT person.ROWID FROM ApgDb.tmp_role JOIN PgDb.person ON >> tmp_role.name = person.name; >> >> to find the ROWID of each row in person who's name matches that of >> the name in tmp_role, it takes about 1 second per matcha really >> long time. > > The problem seems to be with the affinity of tmp_role.name column. > STRING doesn't have any special meaning to SQLite, and so the column > ends up with NUMERIC affinity. On the other hand, TEXT gives the > column TEXT affinity. For some reason I don't quite understand, this > prevents SQLite from using the index on person(name). Try this, it > should run much faster: > > SELECT person.ROWID FROM tmp_role JOIN person ON cast(tmp_role.name > as text) = person.name; > > For more details about data types, column affinity and such, see > http://sqlite.org/datatype3.html Ah, now I understand why the index is not used. As described in section 3 "Comparison Expressions" in the aforementioned document, when comparing NUMERIC and TEXT columns, an attempt is made to convert the text to a number, so that 12 would be considered equal to '12' and '012' and '12.0'. But when the index on person(name) was built, it interpreted these three values as strings and considered them distinct. That's why the index can't be used. Bottom line is, don't use STRING as column type, use TEXT or CHAR or similar. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow JOIN on two indexed text fields. Why?????
sorka wrote: > This is driving me nuts. I have two tables I'm trying to join > together on two text fields. > > CREATE TABLE tmp_role ( programId INTEGER, > roleNameINTEGER, > positionINTEGER, > isNew BOOL, > personIdINTEGER, > nameSTRING); > > This table has up to a few dozen records at any one time. > > AND > > CREATE TABLE person ( >personId INTEGER PRIMARY KEY, >name text UNIQUE > ); > > This table has 10s of thousands of records. > > If I do this query: > SELECT person.ROWID FROM ApgDb.tmp_role JOIN PgDb.person ON > tmp_role.name = person.name; > > to find the ROWID of each row in person who's name matches that of > the name in tmp_role, it takes about 1 second per matcha really > long time. The problem seems to be with the affinity of tmp_role.name column. STRING doesn't have any special meaning to SQLite, and so the column ends up with NUMERIC affinity. On the other hand, TEXT gives the column TEXT affinity. For some reason I don't quite understand, this prevents SQLite from using the index on person(name). Try this, it should run much faster: SELECT person.ROWID FROM tmp_role JOIN person ON cast(tmp_role.name as text) = person.name; For more details about data types, column affinity and such, see http://sqlite.org/datatype3.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Convert Access sql to SQLite sql
I am currently converting a project that uses Access as a database to using SQLite. Part of this involves rewriting sql queries. So far this has been OK, however I have struck a problem with an UPDATE query. The following access sql query updates some fields in a table depending on existing values in other fields in the table and uses a sub query UPDATE EvAtemp INNER JOIN [SELECT EstimateIndex.Date, QAK1Data.JobNo, CodesIndex.Code, FunctionsIndex.Function, EstimateDetail.Hours, EstimateDetail.Rate, EstimateDetail.EmpTime, EstimateIndex.Contingent, EstimateDetail.Hours*EstimateDetail.Rate AS Charge, (EstimateDetail.Hours*EstimateDetail.Rate)*(1+(EstimateIndex.Contingent/100)) AS TotalCharge FROM (((EstimateDetail INNER JOIN EstimateIndex ON EstimateDetail.EstID = EstimateIndex.EstID) INNER JOIN QAK1Data ON EstimateIndex.EstProjNo = QAK1Data.ProjRecID) INNER JOIN CodesIndex ON EstimateDetail.EstCodeRec = CodesIndex.CodesIndexRecID) INNER JOIN FunctionsIndex ON EstimateDetail.EstFuncRec = FunctionsIndex.FunctionsIndexRecID]. AS Q ON EvAtemp.JobNo = Q.JobNo SET EvAtemp.EstimateDate = [Q].[Date], EvAtemp.CodeEst = [Q].[Code], EvAtemp.FunctionEst = [Q].[Function], EvAtemp.HoursEst = [Q].[Hours], EvAtemp.RateEst = [Q].[Rate], EvAtemp.EmpTimeE = [Q].[EmpTime], EvAtemp.Contingent = [Q].[Contingent], EvAtemp.ChargeEst = [Q].[Charge], EvAtemp.TotalCharge = [Q].[TotalCharge] WHERE (((EvAtemp.Code)=[Q].[Code]) AND ((EvAtemp.Function)=[Q].[Function])); My attempt in SQLite is UPDATE EvAtemp SET [EstDate] = [Q].[Date], [CodeEst] = [Q].[Code], [FunctionEst] = [Q].[Function], [HoursEst] = [Q].[Hours], [RateEst] = [Q].[Rate], [EmpTimeE] = [Q].[EmpTime], [Contingent] = [Q].[Contingent], [ChargeEst] = [Q].[Charge], [TotalCharge] = [Q].[TotalCharge ] (SELECT [EstimateIndex].[Date], [QAK1Data].[JobNo], [CodesIndex].[Code], [FunctionsIndex].[Function], [EstimateDetail].[Hours], [EstimateDetail].[Rate], [EstimateDetail].[EmpTime], [EstimateIndex].[Contingent], [EstimateDetail].[Hours] * [EstimateDetail].[Rate] AS [Charge], ([EstimateDetail].[Hours] * [EstimateDetail].[Rate]) * (1 + ([EstimateIndex].[Contingent] / 100)) AS [TotalCharge] FROM [EstimateIndex] INNER JOIN [EstimateDetail] ON [EstimateIndex].[EstID] = [EstimateDetail].[EstID] INNER JOIN [QAK1Data] ON [EstimateIndex].[EstProjNo] = [QAK1Data].[ProjRecID] INNER JOIN [CodesIndex] ON [EstimateDetail].[EstCodeRec] = [CodesIndex].[CodesIndexRecID] INNER JOIN [FunctionsIndex] ON [EstimateDetail].[EstFuncRec] = [FunctionsIndex].[FunctionsIndexRecID]) [Q] WHERE [Code]=[Q].[Code] AND [Function]=[Q].[Function] AND [ProjNo] = [Q].[JobNo] This does not work. I have tried many variations with no success. I hope that some one can point me in the right direction with an example of correct useage. thanks Phil ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Slow JOIN on two indexed text fields. Why?????
This is driving me nuts. I have two tables I'm trying to join together on two text fields. CREATE TABLE tmp_role ( programId INTEGER, roleNameINTEGER, positionINTEGER, isNew BOOL, personIdINTEGER, nameSTRING); This table has up to a few dozen records at any one time. AND CREATE TABLE person ( personId INTEGER PRIMARY KEY, name text UNIQUE ); This table has 10s of thousands of records. If I do this query: SELECT person.ROWID FROM ApgDb.tmp_role JOIN PgDb.person ON tmp_role.name = person.name; to find the ROWID of each row in person who's name matches that of the name in tmp_role, it takes about 1 second per matcha really long time. However, if I instead take each of names in tmp_role and do a seperate select like this: SELECT ROWID FROM person WHERE name = "Carell|Steve"; and do it for each name, the search takes only a few ms for few dozen records in tmp_role. Now the real problem I'm trying to solve is an UPDATE like this: UPDATE tmp_role SET personId = (SELECT ROWID FROM person WHERE tmp_role.name = person.name); If I break this up into a bunch of different statements to iterate through the records in tmp_role and then execute a single statement for each name, I can accomplish this update statement fairly quickly, but as it is authored above, it's taking about 24 seconds for 24 records or about 1000 times longer than if I do it the long way :( -- View this message in context: http://old.nabble.com/Slow-JOIN-on-two-indexed-text-fields.-Why--tp26601433p26601433.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] Possibly a bug in SQLite?
Brandon Wang wrote:
> I've come upon a interesting scenerio.
>
> .sqlite> .schema rg_configuration
> CREATE TABLE 'rg_configuration' (
>"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
>"configurationName" TEXT NOT NULL,
>"host" TEXT,
>"user" TEXT,
>"parentArch" INTEGER NOT NULL,
>"parentJob" INTEGER NOT NULL,
>"parentSubblock" INTEGER NOT NULL,
>"parentBlock" INTEGER NOT NULL,
>"canBeRun" INTEGER DEFAULT (1)
> );
> [Addititonal indices, triggers, etc. here]
>
> One of my scripts attempts to execute the following:
>
> INSERT INTO main.rg_configuration (configurationName, parentArch,
> parentJob, parentSubblock, parentBlock, canBeRun) VALUES
> ('full_chip.nofeedthru', 9565, 3014, 33, 8, 1);
>
> Upon which I get the error:
>
> SQL error: PRIMARY KEY must be unique
>
> I'm not specifying the primary key, id. Is there some error on my
> part?
My guess is, you have an INSERT trigger that does try to insert a duplicate,
possibly into a different table.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Possibly a bug in SQLite?
Hello,
I've come upon a interesting scenerio.
.sqlite> .schema rg_configuration
CREATE TABLE 'rg_configuration' (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"configurationName" TEXT NOT NULL,
"host" TEXT,
"user" TEXT,
"parentArch" INTEGER NOT NULL,
"parentJob" INTEGER NOT NULL,
"parentSubblock" INTEGER NOT NULL,
"parentBlock" INTEGER NOT NULL,
"canBeRun" INTEGER DEFAULT (1)
);
[Addititonal indices, triggers, etc. here]
One of my scripts attempts to execute the following:
INSERT INTO main.rg_configuration (configurationName, parentArch, parentJob,
parentSubblock, parentBlock, canBeRun) VALUES ('full_chip.nofeedthru', 9565,
3014, 33, 8, 1);
Upon which I get the error:
SQL error: PRIMARY KEY must be unique
I'm not specifying the primary key, id. Is there some error on my part?
Thanks!
-Brandon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Add better support to 64-bit Windows
isNT() 64-bit Windows is NT only sqlite3Hwtime() Inline assembler cannot be used for Visual C++ of the x64 target. Compiler intrinsics __rdtsc() function. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Broken RSS
Hello, the RSS at http://www.sqlite.org/src/timeline.rss is broken. The contents of several elements are not properly escaped (< and > characters), so several readers can't parse it - Google Reader for example. Best regards, Filip Navara ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sampling Data
Simon Slavin-3 wrote: > > >> But then I would have to do another statement for the next row like this: >> Avg(watts) from tblData where minute = 5 or minute = 6 or minute = 7 or >> minute = 8. > > Sure. It's called writing software. > Yes, I have this solved via software already. I pull in the whole result then average them together before I put them in a strongly typed array. I think I would get some speed gains if I could get it from just a sqlite statement. Simon Slavin-3 wrote: > > Work out a calculation you can do within SQLite which converts your > minutes to one value for each four minutes. Perhaps some equivalent of > int(minute/4) would do it. Then use this value for a GROUP BY clause. > > Simon. > Genius! I looked it up and here's the sqlite syntax incase anyone else runs into this: select CAST(workoutID/5 as integer) as myField I haven't implemented this fully yet but it looks like it's going to work. -- View this message in context: http://old.nabble.com/Sampling-Data-tp26597669p26599354.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] Sampling Data
SELECT mingroup, avg(watt) FROM ( SELECT int(minute/5) as mingroup, watt FROM samples ) ; not tested Oliver > -Ursprüngliche Nachricht- > Von: "npearson99" > Gesendet: 01.12.09 21:08:00 > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] Sampling Data > > > Simon Slavin-3 wrote: > > > > > > SELECT minute,avg(watt) FROM samples GROUP BY minute > > > > > > Thanks for the quick response! I could accomplish it that way but I would > have to query many many times. My data has about 8000 records. > > The data looks like this for the minute and watts columns > Minute Watt > 1 100 > 2 110 > 3 111 > 4 95 > 5 120 > 6 90 > 7 15 > 8 220 > > It goes on like that for 8000+ records > > I want to average every four rows. > > So I could go select Avg(watts) from tblData where minute = 1 or minute =2 > or minute =3 or minute = 4 > But then I would have to do another statement for the next row like this: > Avg(watts) from tblData where minute = 5 or minute = 6 or minute = 7 or > minute = 8. > > This would result in 2000 records with "smoothed" data out of a 8000 row > record set. > > My problem is that i need it to step every n number of rows (in this case 4) > to get to the next set of data. > -- > View this message in context: > http://old.nabble.com/Sampling-Data-tp26597669p26598244.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 > ___ Preisknaller: WEB.DE DSL Flatrate für nur 16,99 Euro/mtl.! http://produkte.web.de/go/02/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sampling Data
npearson99 wrote: > The data looks like this for the minute and watts columns > Minute Watt > 1 100 > 2 110 > 3 111 > 4 95 > 5 120 > 6 90 > 7 15 > 8 220 > > It goes on like that for 8000+ records > > I want to average every four rows. Perhaps something like this: select min(Minute), max(Minute), avg(Watt) from mytabe group by (Minute - 1)/4; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sampling Data
On 1 Dec 2009, at 8:06pm, npearson99 wrote: > So I could go select Avg(watts) from tblData where minute = 1 or minute =2 > or minute =3 or minute = 4 SELECT avg(watts) FROM tblData WHERE minute BETWEEN 1 AND 4 would be far faster, especially if you have an index on minute. > But then I would have to do another statement for the next row like this: > Avg(watts) from tblData where minute = 5 or minute = 6 or minute = 7 or > minute = 8. Sure. It's called writing software. > This would result in 2000 records with "smoothed" data out of a 8000 row > record set. > > My problem is that i need it to step every n number of rows (in this case 4) > to get to the next set of data. Work out a calculation you can do within SQLite which converts your minutes to one value for each four minutes. Perhaps some equivalent of int(minute/4) would do it. Then use this value for a GROUP BY clause. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sampling Data
Simon Slavin-3 wrote: > > > SELECT minute,avg(watt) FROM samples GROUP BY minute > > Thanks for the quick response! I could accomplish it that way but I would have to query many many times. My data has about 8000 records. The data looks like this for the minute and watts columns Minute Watt 1 100 2 110 3 111 4 95 5 120 6 90 7 15 8 220 It goes on like that for 8000+ records I want to average every four rows. So I could go select Avg(watts) from tblData where minute = 1 or minute =2 or minute =3 or minute = 4 But then I would have to do another statement for the next row like this: Avg(watts) from tblData where minute = 5 or minute = 6 or minute = 7 or minute = 8. This would result in 2000 records with "smoothed" data out of a 8000 row record set. My problem is that i need it to step every n number of rows (in this case 4) to get to the next set of data. -- View this message in context: http://old.nabble.com/Sampling-Data-tp26597669p26598244.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] Sampling Data
On 1 Dec 2009, at 7:29pm, npearson99 wrote: > Example Table: > tableID > Minute > Watt > > I'm trying to sum average the watt column by minute. Use 'avg(X)' on the result of a SELECT which finds all the samples within a particular minute. http://www.sqlite.org/lang_aggfunc.html It is unfortunate that the SQLite documentation includes no examples, and Google turns out just examples where people are defining their own aggregate functions. However, I understand that aggregate functions can be used as follows: SELECT avg(watt) FROM samples WHERE minute = 10 You may even be able to make SQLite obtain all the values you want in one statement: SELECT minute,avg(watt) FROM samples GROUP BY minute but that depends on how your want your own software to work and how you want to handle situations where there are zero samples in a particular minute. Simon. PS: Your questions was very clearly phrased. That's the way to get eager answers. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sampling Data
I had another question before about moving averages via sql statement and the response I got was great. Now I'm trying to "sample" the data. I guess it would be a form of smoothing but I'm not sure what to call it. I want to do something like this: row1 = (item[0] + item[1] + item[2] + item[3] )/4) row2 = (item[4] + item[5] + item[6] + item[7] )/4) row3 = (item[8] + item[9] + item[10] + item[11] )/4) I'm using some pseudo code here, I hope this make sense. Example Table: tableID Minute Watt I'm trying to sum average the watt column by minute. Thanks in advance. -- View this message in context: http://old.nabble.com/Sampling-Data-tp26597669p26597669.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] sqlite3ExprCodeIN() problems with SQLITE_OMIT_SUBQUERY
On Dec 1, 2009, at 12:17 PM, Ralf Junker wrote: > > always made me believe that all options to omit features are > supported. > Now I am not sure which ones I can really trust. > > Could you clear up my confusion? > There are several dozen individual compile-time options. We obviously cannot test every combination of compile-time option since there is an exponential explosion in the number of test cases. And so, our strategy is to test only those compile-time configurations that are in common use or which are used by companies that provide material support to SQLite. So, for example, SQLITE_SECURE_DELETE was unsupported for a long time. Then we discovered that SQLITE_SECURE_DELETE is used in Firefox. So now we are very careful to test SQLITE_SECURE_DELETE before each release. Actually, we know exactly which compile-time options Mozilla uses, and we test that exact combination of compile- time options. A different set of compile-time options are used by Symbian/Nokia, and by Bloomberg, and so forth. We test all of these configurations carefully. But those are only a handful of configurations out of the billions and billions of possible ways of configuring SQLite. As it happens, we are not aware of any major product using SQLITE_OMIT_SUBQUERY. Nor do any of our sponsors use or desire that option, as far as we are aware. Nor is it an especially useful option, in most peoples view. And so testing and maintaining SQLITE_OMIT_SUBQUERY is pretty low on our list of priorities. I'm sorry to disappoint, but the fact is that we do have to prioritize things. The SQLite developers do not have any idle cycles at the moment. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3ExprCodeIN() problems with SQLITE_OMIT_SUBQUERY
On 01.12.2009 18:35, Jay A. Kreibich wrote: > Important Note: The SQLITE_OMIT_* compile-time options are > unsupported. Oops! Thanks for focusing my eyes - they tend to skip introductions and move right to the details. Now having that read, let me point out that in spite of the statement the SQLITE_OMIT_... options have worked nearly flawlessly for me over the last few years. If there were little glitches, they were usually fixed very quickly. Thank you, SQLite team! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3ExprCodeIN() problems with SQLITE_OMIT_SUBQUERY
On Tue, Dec 01, 2009 at 06:17:49PM +0100, Ralf Junker scratched on the wall: > Reading > >http://www.sqlite.org/compile.html > > always made me believe that all options to omit features are supported. Read closer: 1.6 Options To Omit Features ... Important Note: The SQLITE_OMIT_* compile-time options are unsupported. The SQLITE_OMIT_* compile-time options are usually untested and are almost certainly untested in combination. Any or all of these options may be removed from the code in future releases and without warning. For any particular release, some of these options may cause compile-time or run-time failures, particularly when used in combination with other options. The "Important Note" is in bold and italics. I suggest you re-read the whole intro to section 1.6. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3ExprCodeIN() problems with SQLITE_OMIT_SUBQUERY
On 01.12.2009 18:05, D. Richard Hipp wrote: > The key point to bare in mind here is that SQLITE_OMIT_SUBQUERY is > not a supported compile-time option. None of the major users of > SQLite make use of SQLITE_OMIT_SUBQUERY. We do not test it. And it > appears that it is broken in the current implementation. It is > unlikely to be fixed for 3.6.21. Thank you for pointing this out! I was not aware that there are supported and unsupported compile-time options. Reading http://www.sqlite.org/compile.html always made me believe that all options to omit features are supported. Now I am not sure which ones I can really trust. Could you clear up my confusion? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3ExprCodeIN() problems with SQLITE_OMIT_SUBQUERY
On Dec 1, 2009, at 11:53 AM, Ralf Junker wrote: > Compiling with SQLITE_OMIT_SUBQUERY, sqlite3ExprCodeIN() is compiled > out > but at the same time still required at other places in expr.c. > > As such, expr.c does not link well with SQLITE_OMIT_SUBQUERY defined. > > After I wrapped the remaining references to sqlite3ExprCodeIN() by > #ifndef SQLITE_OMIT_SUBQUERY it compiles fine. Am I safe to do so or > are > there any caveats? It looks like adding the extra #ifndefs breaks other things - specifically the IN operator. The key point to bare in mind here is that SQLITE_OMIT_SUBQUERY is not a supported compile-time option. None of the major users of SQLite make use of SQLITE_OMIT_SUBQUERY. We do not test it. And it appears that it is broken in the current implementation. It is unlikely to be fixed for 3.6.21. > > Ralf > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3ExprCodeIN() problems with SQLITE_OMIT_SUBQUERY
Compiling with SQLITE_OMIT_SUBQUERY, sqlite3ExprCodeIN() is compiled out but at the same time still required at other places in expr.c. As such, expr.c does not link well with SQLITE_OMIT_SUBQUERY defined. After I wrapped the remaining references to sqlite3ExprCodeIN() by #ifndef SQLITE_OMIT_SUBQUERY it compiles fine. Am I safe to do so or are there any caveats? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with #include in fts3Int.h
I have a compilation problem with line 22 in fts3Int.h: #include According to http://gcc.gnu.org/onlinedocs/cpp/Include-Syntax.html, includes are used for system header files, which sqlite3.h is not IMHO. I tried to replace with #include "sqlite3.h" but this resulted in a number of unknown identifiers further down the code. Finally I changed it to #include "sqliteInt.h" and the code compiled happily. Testing showed that it also worked OK. Am I on the right track? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_BUSY
I have two threads in a Linux process using sqlite 3.6.12 in shared cache mode. One thread opens the database file in read only mode (sqlite3_open_v2()), sets to read uncommitted and only ever performs selects from the database. The other thread inserts, updates and deletes rows from the database. On occasion the return code of SQLITE_BUSY is returned from sqlite3_step(). Is this possible with the other thread in read only mode? I have a busy handler installed but I would like to understand what's going on here. TIA ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error message from sqlite3_tokenizer_module.xCreate?
On 01.12.2009 09:01, Dan Kennedy wrote: > I don't think it is possible at the moment. Unfortunately. Thanks for the clarification, Dan! I observe that you are currently writing the "official" FTS3 documentation in preparation for the next release of SQLite. Maybe you want to make tokenizer error messages possible before the docs are finished and the "unfortunate" xCreate API is carved in stone? ;-) Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .read command
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 FrankLane wrote: > Can I pass parameters to the .read command? Like "select * from test where > data=whatever" and then pass the value of whatever to the .read file > somehow? No. Doing this is starting the slippery slope of becoming a programming language. There are several approaches to this: - - Generate a file for .read containing what you need - - The shell can also read commands from its standard input so you can have another program generating the commands on the fly piping them into the SQLite shell. - - Pick a programming language and use its bindings to SQLite. If you are new to programming I'd recommend Python but you'll find most scripting languages relatively easy to pick up (eg TCL, Ruby, Perl, PHP). You can also use "legacy" languages like Java, C and C++. - - Pick a programming language and use its generic database adaptor interface (eg ODBC/ADO for Microsoft, JDBC for Java) with a SQLite plugin. This is generally a little harder but lets your code be somewhat database independent. The list of programming languages and their SQLite support is at: http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAksUznUACgkQmOOfHg372QQ+EACgg6D7J/YSuHDfC0V1NyXaVfOi Z0gAoL6Wb1XIj2Iq2ymXixTtwql5KkSF =/lYZ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error message from sqlite3_tokenizer_module.xCreate?
On Dec 1, 2009, at 2:17 PM, Ralf Junker wrote:
> On 30.11.2009 20:33, Grzegorz Wierzchowski wrote:
>> Monday 30 of November 2009 12:29:10 Ralf Junker napisał(a):
>>> I am passing various arguments to
>>> sqlite3_tokenizer_module.xCreate. In case
>>> they are invalid, I would like to return an explaining error
>>> message in
>>> addition to SQLITE_ERROR. I did not find a way to do this. Is it
>>> at all
>>> possible?
>>>
>>> Thanks, Ralf
>>
>> The last argument of xCreate() is char **pzErr.
>> It is exactly designed for the purpose you describe.
>> See also description in doc-zip: sqlite-3_6_18-docs/vtab.html, or
>> http://www.sqlite.org/vtab.html
>
> Thank you for your answer! I believe you are mixing up the virtual
> table
> sqlite3_module.xCreate() in sqlite3.h and
> sqlite3_tokenizer_module.xCreate() in fts3_tokenizer.h.
>
> The latter does not have the pzErr argument:
>
> struct sqlite3_tokenizer_module {
>
>
>
> int (*xCreate)(
> int argc, /* Size of argv array */
> const char *const*argv, /* Tokenizer argument
> strings */
> sqlite3_tokenizer **ppTokenizer /* OUT: Created tokenizer */
> );
>
> So I believe my question remains unanswered. Any suggestion, anyone?
I don't think it is possible at the moment. Unfortunately.
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
