[sqlite] Select question
Hey guys.
I have the following query:
SELECT a.* FROM dailyRankingTable a, friendTable b WHERE upper(b.player)
= upper('?') AND upper(b.friend) = upper(a.name)
(ignore the uppers for now - I'm going to refactor soon)
I would like this query to also select the first 10 elements of
dailyRankingTable regardless - is that possible in one select?
Thanks,
Ian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select question
select top 10 * from dailyRankingTable
union all
SELECT a.* FROM dailyRankingTable a, friendTable b WHERE upper(b.player)
= upper('?') AND upper(b.friend) = upper(a.name)
Top 10 does not work for SQlite I believe. But you might can find something
like it.
/Andreas
On Mon, May 10, 2010 at 11:18 AM, Ian Hardingham wrote:
> Hey guys.
>
> I have the following query:
>
> SELECT a.* FROM dailyRankingTable a, friendTable b WHERE upper(b.player)
> = upper('?') AND upper(b.friend) = upper(a.name)
>
> (ignore the uppers for now - I'm going to refactor soon)
>
> I would like this query to also select the first 10 elements of
> dailyRankingTable regardless - is that possible in one select?
>
> Thanks,
> Ian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Andreas Henningsson
"Vanligt sunt förnuft är inte särkilt vanligt." -- Voltaire
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Database in Shared Memory
Hi, I am trying to implement a shared queue (to asynchronously exchange messages between processes) using SQLite. Since I do not need my queues to be persistent (at least for now), I do not want to use disk based SQLite database (for better performance). I see there is an option to create purely in-memory DBs, but I don't see anything for the shared memory. Any ideas about how to implement a DB in shared memory? Thanks, Manuj ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign constraints and table recreation
On 10 May 2010, at 7:34am, Patrick Earl wrote: >PRAGMA foreign_keys = ON; > >CREATE TABLE ParkingLot (Id int NOT NULL PRIMARY KEY); >CREATE TABLE Car (Id int NOT NULL PRIMARY KEY, ParkingLotId int > NOT NULL REFERENCES ParkingLot (Id) DEFERRABLE INITIALLY DEFERRED); >INSERT INTO ParkingLot (Id) VALUES (1); >INSERT INTO Car (Id, ParkingLotId) VALUES (2, 1); > >BEGIN TRANSACTION; >CREATE TABLE ParkingLotTemp (Id int NOT NULL PRIMARY KEY); >INSERT INTO ParkingLotTemp (Id) SELECT Id FROM ParkingLot; >DROP TABLE ParkingLot; >ALTER TABLE ParkingLotTemp RENAME TO ParkingLot; >COMMIT TRANSACTION; > > Even though at the end of the transaction you can select and find the > appropriate rows in the car and parking lot tables, committing the > transaction causes a foreign constraint violation. I'm not sure how you expected this to work. You declare ParkingLot as a parent table of Car, but then you DROP TABLE ParkingLot, leaving Car an orphan. The only legitimate way to do this is to DROP TABLE Car first, or to remove the foreign key constraint from it (which SQLite doesn't let you do). The fact that you rename another table 'ParkingLot' later has nothing to do with your constraint: the constraint is linked to the table, not to the table's name. If you're going to make a temporary copy of ParkingLot, then make a temporary copy of Car too: CREATE TABLE CarTemp (Id int NOT NULL PRIMARY KEY, ParkingLotId int NOT NULL REFERENCES ParkingLotTemp (Id) DEFERRABLE INITIALLY DEFERRED); INSERT INTO CarTemp (Id) SELECT Id FROM Car; Then you can drop both original tables and rename both 'temp' tables. However, I don't see why you're doing any of this rather than just adding and removing rows from each table as you need. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select question
On 10 May 2010, at 10:50am, Andreas Henningsson wrote:
> select top 10 * from dailyRankingTable
>
> union all
>
> SELECT a.* FROM dailyRankingTable a, friendTable b WHERE upper(b.player)
> = upper('?') AND upper(b.friend) = upper(a.name)
>
> Top 10 does not work for SQlite I believe. But you might can find something
> like it.
Sort in descending order of rank, and add LIMIT 10 to the end of your SELECT
statement. Depending on your names, it'll look something like
SELECT * FROM dailyRankingTable ORDER BY rank DESC LIMIT 10
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should this work?
The select/group by part of your statement will group table SERIESDATA by text-column series_id (aliased to id) and return the min and max data_index for each grouping, assuming those columns are populated with data for each row. The set will have three columns and some number of rows, one per id. id | min(data_index) | max(data_index) However, it is not clear to me where you want to put that aggregated set. Do you have another *table* called SERIESID with those three columns in it? Regards Tim Romano Swarthmore PA On Mon, May 10, 2010 at 2:43 AM, Matt Young wrote: > # series data looks like: > create table seriesdata ( >data_index INTEGER PRIMARY KEY autoincrement, >series_id text, >year numeric, >value numeric); > # and is filled > insert into seriesid >select >s.series_id as id, min(data_index),max(data_index) >from >seriesdata as s >group by >id; > > # I intend seriesid to pick up the minand max values of data_index for > each unique series_id > ___ > 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] Select question
On 10 May 2010, at 12:20pm, Simon Slavin wrote: > Sort in descending order of rank, and add LIMIT 10 to the end of your SELECT > statement. Depending on your names, it'll look something like > > SELECT * FROM dailyRankingTable ORDER BY rank DESC LIMIT 10 Hahaha. Wrong way up. That should be either 'ORDER BY rank' without the 'DESC' or 'ORDER BY points DESC'. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Database in Shared Memory
> Any ideas about how to implement a DB in shared memory? It's impossible with current SQLite code base. Even if you try to implement your own database cache and will allocate it in some shared memory it won't work because along with cache pages SQLite also stores some internal information which should be bound to one process. So your best shot is to re-write SQLite's part related to shared cache and use some inter-process locks there instead of mutexes (they are there used now). Probably this approach will work. But the best solution will be to use standard IPC mechanisms and to not make things over-complicated. Generally databases should be used if you need at least some kind of persistence between process restarts. Pavel On Sun, May 9, 2010 at 8:01 PM, Manuj Bhatia wrote: > Hi, > > I am trying to implement a shared queue (to asynchronously exchange messages > between processes) using SQLite. > Since I do not need my queues to be persistent (at least for now), I do not > want to use disk based SQLite database (for better performance). > > I see there is an option to create purely in-memory DBs, but I don't see > anything for the shared memory. > Any ideas about how to implement a DB in shared memory? > > Thanks, > Manuj > ___ > 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] SQLite Database in Shared Memory
TCP-socket listening daemon + SQLite in-memory database may be helpful. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Database in Shared Memory
On Mon, May 10, 2010 at 11:15:59AM -0400, Pavel Ivanov scratched on the wall: > > Any ideas about how to implement a DB in shared memory? > > It's impossible with current SQLite code base. Even if you try to > implement your own database cache and will allocate it in some shared > memory it won't work because along with cache pages SQLite also stores > some internal information which should be bound to one process. Yes... in-memory DBs are strongly tied to the database connection that creates them. They cannot be accessed by multiple processes. The only option for a true multi-access in-memory DB would be to write a VFS module. That's a somewhat non-trivial piece of code. The other option would be to use a standard file-based DB and just turn all the syncs off. Most of the DB would live in the OS disk cache, and you'd get nearly the same performance. > But the best solution will be to use standard IPC mechanisms and to > not make things over-complicated. Generally databases should be used > if you need at least some kind of persistence between process > restarts. Agreed. There are other, likely better, ways of solving this problem. Protected queues for IPC message passing is an old, old problem. Many advanced threading libs have the tools for this. If not, there are many different message-passing libraries out there like MPI. -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] SQLite Database in Shared Memory
On 10 May 2010, at 4:47pm, Alexey Pechnikov wrote: > TCP-socket listening daemon + SQLite in-memory database may be helpful. Yes. You can make one process, which handles all your SQLite transactions, and receives its orders from other processes via inter-process calls or TCP/IP. I've seen a few solutions which do this and they work fine. But that process will itself become some sort of bottleneck if you have many processes calling it. And I think that the original post in this thread described a situation where that was not a good solution. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign constraints and table recreation
Thanks Simon. I believe you're correct in that I can recreate all dependant tables. I had attempted this trick earlier, but was doing so in the context of immediate mode constraints, and that made the re-insertion of data and dropping of tables exceptionally complicated in some cases (such as circular references between tables). So to summarize, the strategy for modifying a table with foreign constraints enabled is to: 1. Find all direct and indirect dependants of the table being modified. 2. Create temporary tables for all of these. 3. Copy the data from the main tables into these temporary tables. 3a. If no circular dependencies, do a topological sort on the tables to get the correct insertion order. 3b. If circular dependencies, either use deferred constraints or come up with a sophisticated algorithm to reinsert the original data (needs to take into account not null columns with circular references present). 4. Drop all the original tables, again with similar steps to 3a and 3b. 5. Rename all the temporary tables to their original names. I will try this algorithm today and report back if I fail. Since I don't have time to imagine an algorithm to delete/insert/update rows in an order that doesn't break constraints, I've ended up using deferred constraints (undesirable in my case) just to support table modification. It would be great if the kind of complexity above was somehow encapsulated in the database engine, instead of having users work around it with non-trivial steps. As a side note, the above algorithm isn't likely to be particularly performant on databases with significant data present. In the general case of multiple individual modifications to tables (such as in the context of a database change manangement framework), the amount of work being done by the DB to modify the table is quite time consuming. Thanks for your help with this. Patrick Earl On Mon, May 10, 2010 at 5:18 AM, Simon Slavin wrote: > > On 10 May 2010, at 7:34am, Patrick Earl wrote: > >> PRAGMA foreign_keys = ON; >> >> CREATE TABLE ParkingLot (Id int NOT NULL PRIMARY KEY); >> CREATE TABLE Car (Id int NOT NULL PRIMARY KEY, ParkingLotId int >> NOT NULL REFERENCES ParkingLot (Id) DEFERRABLE INITIALLY DEFERRED); >> INSERT INTO ParkingLot (Id) VALUES (1); >> INSERT INTO Car (Id, ParkingLotId) VALUES (2, 1); >> >> BEGIN TRANSACTION; >> CREATE TABLE ParkingLotTemp (Id int NOT NULL PRIMARY KEY); >> INSERT INTO ParkingLotTemp (Id) SELECT Id FROM ParkingLot; >> DROP TABLE ParkingLot; >> ALTER TABLE ParkingLotTemp RENAME TO ParkingLot; >> COMMIT TRANSACTION; >> >> Even though at the end of the transaction you can select and find the >> appropriate rows in the car and parking lot tables, committing the >> transaction causes a foreign constraint violation. > > I'm not sure how you expected this to work. You declare ParkingLot as a > parent table of Car, but then you DROP TABLE ParkingLot, leaving Car an > orphan. The only legitimate way to do this is to DROP TABLE Car first, or to > remove the foreign key constraint from it (which SQLite doesn't let you do). > The fact that you rename another table 'ParkingLot' later has nothing to do > with your constraint: the constraint is linked to the table, not to the > table's name. > > If you're going to make a temporary copy of ParkingLot, then make a temporary > copy of Car too: > > CREATE TABLE CarTemp (Id int NOT NULL PRIMARY KEY, ParkingLotId int > NOT NULL REFERENCES ParkingLotTemp (Id) DEFERRABLE INITIALLY DEFERRED); > INSERT INTO CarTemp (Id) SELECT Id FROM Car; > > Then you can drop both original tables and rename both 'temp' tables. > However, I don't see why you're doing any of this rather than just adding and > removing rows from each table as you need. > > Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign constraints and table recreation
On 10 May 2010, at 5:32pm, Patrick Earl wrote: > 1. Find all direct and indirect dependants of the table being modified. > 2. Create temporary tables for all of these. > 3. Copy the data from the main tables into these temporary tables. > 3a. If no circular dependencies, do a topological sort on the tables > to get the correct insertion order. > 3b. If circular dependencies, either use deferred constraints or come > up with a sophisticated algorithm to reinsert the original data (needs > to take into account not null columns with circular references > present). > 4. Drop all the original tables, again with similar steps to 3a and 3b. > 5. Rename all the temporary tables to their original names. It should not be possible to have circular dependencies. Because you somehow got the data in in the first place, and /that/ wouldn't have been possible had you had circular dependencies. Part of normalising your data structure includes making sure that you haven't duplicated data. I do note that you appear to be trying to solve an extremely general case, as if you, the programmer, have no idea why your schema is the way it is. I have to warn you that if you're going to solve the general case, you are going to run into situations which are not solvable without considering individual rows of a table. For instance, consider a genealogy database with a TABLE like this: People: rowid INTEGER nameTEXT sex TEXT fatherIDINTEGER REFERENCES People(id) motherIDINTEGER REFERENCES People(id) If the father or mother is unknown, you use NULL. You can try to populate a clone of this TABLE but unless you insert the records in the right order you're going to get errors at some points as you create the records. Alternatively you can put NULLs in all fatherID and motherID fields when you create the rows, then go back and set the right values once all the rows exist. I think the only way to solve the general solution is to forget the complicated logic in your steps and just create temp TABLEs for all the TABLEs in your database, whether you want to modify them or not. That way you can completely ignore any logic analysis, insert the data in any order you want, and rely on DEFERRABLE/DEFERRED to prevent error messages. But this gets back to an earlier point of mine: why go through any of this performance at all ? Why do you need to create temporary copies of databases only to originally delete and replace the originals ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should this work?
Reading your response, thinks. I did however find that the incoming data did not conform to what I expects (contiguous series_id), so the code worked, my thinking did not. On 5/10/10, Tim Romano wrote: > The select/group by part of your statement will group table SERIESDATA by > text-column series_id (aliased to id) and return the min and max data_index > for each grouping, assuming those columns are populated with data for each > row. The set will have three columns and some number of rows, one per id. > > id | min(data_index) | max(data_index) > > However, it is not clear to me where you want to put that aggregated set. Do > you have another *table* called SERIESID with those three columns in it? > > > Regards > Tim Romano > Swarthmore PA > > > On Mon, May 10, 2010 at 2:43 AM, Matt Young wrote: > >> # series data looks like: >> create table seriesdata ( >>data_index INTEGER PRIMARY KEY autoincrement, >>series_id text, >>year numeric, >>value numeric); >> # and is filled >> insert into seriesid >>select >>s.series_id as id, min(data_index),max(data_index) >>from >>seriesdata as s >>group by >>id; >> >> # I intend seriesid to pick up the minand max values of data_index for >> each unique series_id >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign constraints and table recreation
On Mon, May 10, 2010 at 10:58 AM, Simon Slavin wrote: > > It should not be possible to have circular dependencies. Because you somehow > got the data in in the first place, and /that/ wouldn't have been possible > had you had circular dependencies. Part of normalising your data structure > includes making sure that you haven't duplicated data. I meant foreign keys that cause tables to have circular relationships. For example, a customer might have a list of credit cards (the credit cards table has a customer id) and the customer has a default credit card (the customer table has a credit card id). I realize you could make a third table to store the "default credit card" relationship, but as you observed, I'm looking at the general case. > I do note that you appear to be trying to solve an extremely general case, as > if you, the programmer, have no idea why your schema is the way it is. I > have to warn you that if you're going to solve the general case, you are > going to run into situations which are not solvable without considering > individual rows of a table. Indeed, hence why it's so complicated without using deferred constraints. Unfortunately, enabling deferred constraints leads to later detection of errors during typical development. With immediate constraints, even using null in fields temporarily might not solve the issue, since there may be not-null constraints to deal with. In any case, suffice to say that it is indeed quite complicated. Getting back to one of the points that started this conversation, the complex nature of operations needed to transactionally modify tables with foreign key integrity preservation suggests to me that this would be something the database engine could provide a helping hand with. It wouldn't necessarily need to be full support for alter table, other options presented previously would also help. > But this gets back to an earlier point of mine: why go through any of this > performance at all ? Why do you need to create temporary copies of databases > only to originally delete and replace the originals ? The simplified example I provided had no changes to the tables, but in the real scenario, at least one of the tables will need some sort of modification. Thanks for the detailed replies. I appreciate your insight. Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update: set multiple values
Simon, can you expand your syntax, or are you just saying, "get x,y,z store them in a set of variables, then run update with appropriate bindings"? Hopefully this related question isn't called hijacking a thread. I feel this belongs together under set multiple values using the update query. I'm toying with something similar, and don't want to get the run multiple updates so that the C code can stay simple. create table x (sn int primary key , comboid, property1 int , property2 int , property3 int ...) create table dictionary (comboid int primary key, property1 int , property2 int, property3 int (original insert into x was sn, comboid=-1 /*a flag to indicate this needs an update*/ with property 1, 2, and 3 being correct. The original insert was honking, very slow thing that I've given up hope of salvaging since the below beats it 10 to 100:1 in speed) want to update each sn with the comboid from the dictionary where property1, 2, and 3 match. currently I'm leaning on insert or replace into x (sn, comboid, property1, property2, property3) values select x.sn, d.comboid, x.property1, x.property2, x.property3 from x inner join dictionary d on x.property1 =d.property1 and x.property=d.property2 and x.property3 =d.roperty3 where x.comboid=-1; This somehow feels like cheating, though it seems to produce an acceptable result quickly enough (on my relatively small db) Adam On Sun, May 9, 2010 at 5:23 PM, Simon Slavin wrote: > > On 9 May 2010, at 8:41pm, Simon Hax wrote: > > > I think in sqlite the following is not possible: > > > > update T > > set (a,b,c) = ( select x,y,z from ...) > > > > Does anyone know how to do in an easy way ? > > Do your SELECT first, then set the multiple variables to the values > retrieved from that: > > UPDATE T SET a=x,b=y,c=z WHERE ... > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update: set multiple values
On 10 May 2010, at 9:25pm, Adam DeVita wrote: > Simon, can you expand your syntax, or are you just saying, "get x,y,z store > them in a set of variables, then run update with appropriate bindings"? Just that. You have a programming language with variables, so use it. That's what your programming language is for. You might be able to get extremely clever and work out some contorted SQLite syntax which will do the whole thing in one SQL command, but why bother ? It'll be hell to work out what's wrong if you get an error message. And it'll be difficult to document because you have to explain your perverse syntax. Better to use two extremely simple SQL commands and say "We get three values here ... then we use them in this UPDATE.". Faster and simpler to write, debug and document. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Select via Wi-fi very slow
Hello, I'll try to explain my problem: I have a Symbol MC3090 Data Collector with VB.Net 2005. I have a database with 80,000 records on the computer. For example: I search all words that begin with "shirt" and show in the Grid Collector. Sometimes search found 200 records. When I do a query via wi-fi takes 1 minute. How can I decrease this time? On the computer the same search takes a few seconds ... Public ConnStringDados As String = "Data Source=" & Address & "\" & NameDB & ";Version=3;Compress=True;Synchronous=Off;Cache Size=8000;" My select: "SELECT codigo, description FROM Product WHERE description Like '" & Word _Search & "%'" Thanks, Ernany ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] .import csv with no error
Hi Everyone,
I have patched my SQLite3.exe source (shell.c) to have an ".import"
function witch can read ".mod csv" exports :
- can read QUOTE + TEXT + CRLF + TEXT + QUOTE
- can read QUOTE + SEPARATOR + QUOTE
- can read QUOTE + TEXT + SEPARATOR + TEXT + QUOTE
- can read QUOTE + TEXT + 2QUOTES + TEXT + QUOTE
So, I can export my table like this :
.mod csv
.output table.csv
select * from table;
.output stdout
And import like this :
.mod csv
.import table.csv table
example of csv file :
1,"this is ok",
2,"this is
a new line",""
3,"look ! , the coma ..."," and the ""quotes"" !"
Patch source code of shell.c :
==
/* patch for shell.c version 3_6_23_1 */
// I modified the local_getline() function
// from line 41:
#if defined(HAVE_READLINE) && HAVE_READLINE==1
# include
# include
#else
# define readline(p) local_getline(p,stdin,0)
# define add_history(X)
# define read_history(X)
# define write_history(X)
# define stifle_history(X)
#endif
// from line 368:
static char *one_input_line(const char *zPrior, FILE *in){
char *zPrompt;
char *zResult;
if( in!=0 ){
return local_getline(0, in, 0); /* not in csv mode */
}
// function local_getline(), from line 320 :
/* NB: bCsvMode = 1, if the function is used by the .import command */
static char *local_getline(char *zPrompt, FILE *in, const int bCsvMode){
char *zLine;
int nLine;
int n;
int eol;
int bEscaped; /* Escape CRLF when quoted */
if( zPrompt && *zPrompt ){
printf("%s",zPrompt);
fflush(stdout);
}
nLine = 100;
zLine = malloc( nLine );
if( zLine==0 ) return 0;
n = 0;
eol = 0;
bEscaped = 0;
while( !eol ){
if( n+100>nLine ){
nLine = nLine*2 + 100;
zLine = realloc(zLine, nLine);
if( zLine==0 ) return 0;
}
if( fgets(&zLine[n], nLine - n, in)==0 ){
if( n==0 ){
free(zLine);
return 0;
}
zLine[n] = 0;
eol = 1;
break;
}
while( zLine[n] ){
if( zLine[n]=='"' && bCsvMode ) bEscaped = 1 - bEscaped;
n++;
}
if( !bEscaped && n>0 && zLine[n-1]=='\n' ){
n--;
if( n>0 && zLine[n-1]=='\r' ) n--;
zLine[n] = 0;
eol = 1;
}
}
zLine = realloc( zLine, n+1 );
return zLine;
}
// and from line 1628 :
sqlite3_exec(p->db, "BEGIN", 0, 0, 0);
zCommit = "COMMIT";
while( (zLine = local_getline(0, in, 1))!=0 ){ /* csv mode */
char *z;
char *q;
int bEscaped; /* to escape quote + comma + quote */
int bFieldQuoted;
bEscaped = 0;
i = 0;
lineno++;
if( *zLine=='"' ){
azCol[0] = zLine + 1; /* ignore the first quote */
bFieldQuoted = 1;
}else{
azCol[0] = zLine;
bFieldQuoted = 0;
}
for(i=0, z=zLine, q=zLine; *z ; z++){
if( *z=='"' ) bEscaped = 1 - bEscaped;
if( bEscaped==0 && *z==p->separator[0] && strncmp(z, p->separator,
nSep)==0 ){
if( bFieldQuoted==1 && *q=='"') *q = 0; /* ignore the last quote */
*z = 0;
i++;
if( ihttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_CORRUPT error
Hey Guy's, I have ported SQLITE over VRTX based embedded platform, now after using for a 1 year suddenly started getting SQLITE_CORRUPT ERORR, used queue and mutex at application level to make sqilte DB access thread safe as sqlite library itself is compiled without thread safe options, Strange thing is that whenever SQLITE_CORRUPT error comes it comes only while accessing a particular table out of 5 tables other tables are fine, pragma integrity_check; also fails after this error. I will highly appreciate if someone can give out possible cause of this kinda issue. Thanks,DJ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
