[sqlite] Support for System.Data.SQLite: Different API type for int/integer columns
Hello, I have encountered the same problem as this: https://stackoverflow.com/q/4925084 The answers don't explain why there is a bitness difference at run-time between the types retrieved from INT and INTEGER columns, and that's my question. From reading https://sqlite.org/datatype3.html I understand there should be no difference whatsoever between defining a column INT or INTEGER (other than whether a primary key may become a rowid alias). I don't mean the bitness how the integers are stored in the disk database, but the values returned by the System.Data.SQLite API (in particular via a DataTable loaded by a SQLiteDataReader). I have verified that declaring a column (which isn't any kind of key) as INT causes System.Data.SQLite to return Int32/int (possibly, depending on the value? Not sure); and declaring INTEGER causes the same value to be returned as Int64/long. Can anyone explain this, or point to where this is actually documented, if I've missed it? It's the first time I use this mailing list, after searching for the answer on sqlite.org and around the Web; I hope I haven't missed any RTFM, otherwise please let me know. Thanks Xavier Porras ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] collate function / international sort on linux
Thanks to several posts in this forum, I now have a custom collation defined with sqlite3_create_collation, which uses Window's CompareStringA. I managed to add it to main.c and recompiled sqlite (I finally got my compilation running on Windows with MingW/Msys). For my needs properly does case-insensitive and characters with diacritics sorts. For example: AAA amigo ándale Andalucía ángel Azul Now I need to have this function, CompareStringA or similar/better, on linux. Does anybody have it or can point me where to look for it? jp It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite - select/read only version
Sqlite's size is perfect as it is, but I have a couple of apps where an even smaller "reader" could be used: 1. A cgi-bin based application, hosted at a third party place - no fast-cgi, no registering isapi dlls, etc. just plain cgi-bin which gets loaded/executed every time it is called (win32 + IIS ). The cgi-bin program (150k) loads sqlite3.dll (380k) every time, so I am just trying to take it to the smallest size possible. A static link to my app might make the combination smaller (sqlite3.exe is 410k), but I want to make it even smaller. 2. For distributing/replicating databases on embedded devices, where the device itself doesn't do any updates to the database. For example, a DVR which receives daily non-incremental (full) updates of programming or a PDA which receives and displays statistics of some sort. The PDA wouldn't need all the overhead of data update routines. Wouldn't it be nice to say that the sqlite reader version is only 128k (or less)? Also, in theory, since it doesn't have to worry about locks, transactions, synchrounous=off, etc., shouldn't it run even faster? jp --- [EMAIL PROTECTED] wrote: > Building a read-only version of SQLite is easy if > you > don't care about leaving all of the (unused) writing > code in place. But you are the first person to ask > me > about a read-only version of SQLite that also cares > about the footprint. As far as I know, this has not > been done before. > > What are you doing that the 250K standard SQLite is > too big for you? > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to > [EMAIL PROTECTED] > - > > Don't pick lemons. See all the new 2007 cars at Yahoo! Autos. http://autos.yahoo.com/new_cars.html - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite - select/read only version
Has anybody tried to build a query-only (select only) version of sqlite? I need to make a read-only version, without all the CREATE xxx, INSERT, DELETE, UPDATE, etc., for reporting purposes, which in theory should result in a significantly smaller .dll. Just like Acrobat has Acrobat Reader, I think sqlite could have a "sqlite reader", suitable when you just want to deploy the database. Ideas, anyone? jp Never miss an email again! Yahoo! Toolbar alerts you the instant new Mail arrives. http://tools.search.yahoo.com/toolbar/features/mail/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] custom collation problem with delphi
Thanks Ralf, that seems to be more stable - the process is not crashing anymore (ran it twice, no errors). Pardon my ignorance - I am still using cdecl, how can I use 'register'? Doesn't sqlite expects cdecl? jp --- Ralf Junker <[EMAIL PROTECTED]> wrote: > Hello jp, > > with DISQLite3, I use the the following colation > callback function without problems: > > function SQLite3_Compare_System_NoCase_Ansi( > UserData: Pointer; > l1: Integer; const s1: Pointer; > l2: Integer; const s2: Pointer): Integer; > begin > Result := CompareStringA(LOCALE_SYSTEM_DEFAULT, > NORM_IGNORECASE, s1, l1, s2, l2) - 2; > end; > > This function should be functionally equivalent to > your implementation, but accesses the Win32 API > directly. Also, it does not implicitly convert the > PChar pointers to AnsiStrings like your call > "copy(B,1,lenB);" implicitly does. > > Notes: > > * The above function is not declared as "cdecl" > because DISQLite3 uses the faster "register" calling > convention instead. > > * The above function (just as yours) does not treat > UTF-8 sequences properly. You might want to consider > a WideString function instead, depending on the data > you are processing. > > Regards, > > Ralf > Don't pick lemons. See all the new 2007 cars at Yahoo! Autos. http://autos.yahoo.com/new_cars.html - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Extension functions for SQLite in C for free
> T wrote: > > Last month, Mikey C wrote (in part): > > > >> I've had these functions hanging around for some > time... if anyone > >> wants the code, please take it. > >> > >> I have all the code as a MS Visual Studio 2003 > project. It is based > >> on source code 3.3.5 > > > > > >> replace(X,Y,Z) Returns the string X with every > occurence of Y > >> replaced by Z. > > > > > >> rightstr(X,Y) Returns the Y last characters of > the string X. > > > > > >> strfilter(X,Y) Returns the string X with the > characters not in Y > >> removed. > > > > > >> trim(X) Returns a string equal to X but with all > the whitespaces at > >> the begining and at the end removed. > > > > > >> median(X) Returns the value of the group such > that the number of > >> elements smaller is equal to the number of larger > elements. > > > > > >> mode(X) Returns the most frequent value in the > sample X. > > > > > >> stdev(X) Returns the standard deviation of the > sample X. > > > > > >> http://www.nabble.com/file/6285/SQLite.zip > SQLite.zip > >> -- > >> View this message in context: > http://www.nabble.com/Extension- > >> > functions-for-SQLite-in-C-for-free-tf3182921.html#a8833684 > > > > > > For the non-C programmer (but with access to > standard UNIX C compile > > tools etc), can someone please explain or point to > a step by step > > procedure to add these functions so they are > accessible within the > > sqlite3 shell command? > > > > Thanks, > > Tom > > This requires that you produce a custom version of > the sqlite3 shell > incorporating the extra functions. That requires > some basic programming > understanding. I think you can compile the functions into a loadable library (follow the instructions on creating a sqlite loadable extension), and then load them in the console app. In linux: gcc myLoadableLibs.c -shared -o myLoadableLibs.so then sqlite3> .load /home/jp/myLoadableLibs.so I am not sure how to do that on VC though. jp. Don't pick lemons. See all the new 2007 cars at Yahoo! Autos. http://autos.yahoo.com/new_cars.html - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] custom collation problem with delphi
I have a custom collation which worked well in 3.3.6, but now gives random errors on 3.3.13. Might just be coincidence but wanted to get feedback from the community. Under random circumstances, my Delphi function (compare function defined in sqlite3_create_collation) doesn't seem to receive the right parameters. After debugging, the error... "Access violation at address 00405190 in module 'app.exe'. Read of address 016D000" ...happens in line #15, like if the pointer to the second variable (B) is invalid: 1 FUNCTION fnComp(user:pointer; 2 lenA:integer; A:pChar; 3 lenB:integer; B:pChar 4 ):integer; cdecl; 5 6 VAR S1,S2 :string; 7 i:integer; 8 BEGIN 9 S1 := ''; 10 S2 := ''; 11 IF lenA>0 THEN 12S1 := copy(A,1,lenA); 13 14 IF lenB>0 THEN 15S2 := copy(B,1,lenB); // error happens here! 16 17 i := ansiCompareText(S1,S2); 18 19 IF (i=0) THEN 20IF (lena<lenb) THEN 21 i := -1 22ELSE 23 i := 1 24 ELSE ; 25 26 Result := i; 27 END; ... The collation is created using: sqlResult:=sqlite3_create_collation(db3, 'myCollate', SQLITE_UTF8, self, fnComp); - - - - No error happens when using the BINARY and NOCASE collation. Does anybody have similar experiences or have a clue of what might be going on? The error happens after processing several thousand records, but sometimes happens on an INSERT, other times during a SELECT, and never in exactly the same place (even with the same set of data/database). jp It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Custom collate - on field or index or both?
Thanks all! This worked (winxp, sqlite 3.3.13) and does use the index: > SELECT lastname FROM people > WHERE country_id='US' AND lastname COLLATE > mycollate >'A' > ORDER BY country_id,lastname COLLATE mycollate > LIMIT 100; jp --- Dennis Cote <[EMAIL PROTECTED]> wrote: > Dan Kennedy wrote: > > On Thu, 2007-03-01 at 15:13 -0800, jp wrote: > > > >> Hi, I have a custom collation sequence (e.g. > >> mycollate). Are there any advantages in terms of > >> performance of declaring this collation at the > table > >> level, instead of just at the index level? > >> > >> For example, if I have: > >> > >> CREATE TABLE people AS ( > >> country_id char(02), > >> lastname varchar(100), > >> phone varchar(50) > >> ); > >> > >> CREATE UNIQUE INDEX people_mycollate on people ( > >> country_id, > >> lastname COLLATE mycollate > >> ); > >> - - - - - - > >> ...will the following use the index (about 500k > recs)? > >> - - - - - - > >> SELECT lastname FROM people > >> WHERE country_id='US' AND lastname>'A' > >> ORDER BY country_id,lastname COLLATE mycollate > >> LIMIT 100; > >> - - - - - - > >> > > > > SQLite will use the index to implement the > country_id='US' clause, > > and the ORDER BY, but not the lastname>'A' clause. > This is because > > the lastname>'A' doesn't use the "mycollate" > collation. > > > > If the COLLATE clause was specified as part of the > table > > definition, then lastname>'A' would be a > "mycollate" comparison > > and the index would be used for this too. > > > > Dan. > > > > > I'm not sure if it is all implemented yet, but > Richard has checked in > some changes to the collation handling. > > The query should use the index as created if the > comparison is changed > to use the specified collation like this. > > SELECT lastname FROM people > WHERE country_id='US' AND lastname COLLATE > mycollate >'A' > ORDER BY country_id,lastname COLLATE mycollate > LIMIT 100; > > In this case you are using explicit collations > everywhere the lastname > is used. > > If you change the table definition then mycollate > will be the default or > implicit collation for that field. The OP said that > he wanted to use > different collations for this field in different > queries, but that > doesn't mean that he couldn't use mycollate as the > default collation and > apply any others that he needed using explicit > collation clauses like > the query above. > > HTH > Dennis Cote > > - > To unsubscribe, send email to > [EMAIL PROTECTED] > - > > Looking for earth-friendly autos? Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center. http://autos.yahoo.com/green_center/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Custom collate - on field or index or both?
--- Dan Kennedy <[EMAIL PROTECTED]> wrote: > > > On Thu, 2007-03-01 at 15:13 -0800, jp wrote: > > Hi, I have a custom collation sequence (e.g. > > mycollate). Are there any advantages in terms of > > performance of declaring this collation at the > table > > level, instead of just at the index level? > > > > For example, if I have: > > > > CREATE TABLE people AS ( > > country_id char(02), > > lastname varchar(100), > > phone varchar(50) > > ); > > > > CREATE UNIQUE INDEX people_mycollate on people ( > > country_id, > > lastname COLLATE mycollate > > ); > > - - - - - - > > ...will the following use the index (about 500k > recs)? > > - - - - - - > > SELECT lastname FROM people > > WHERE country_id='US' AND lastname>'A' > > ORDER BY country_id,lastname COLLATE mycollate > > LIMIT 100; > > - - - - - - > > SQLite will use the index to implement the > country_id='US' clause, > and the ORDER BY, but not the lastname>'A' clause. > This is because > the lastname>'A' doesn't use the "mycollate" > collation. > > If the COLLATE clause was specified as part of the > table > definition, then lastname>'A' would be a "mycollate" > comparison > and the index would be used for this too. > > Dan. So, is creating an index with "collate" useless if you didn't specify the collate at the table level? Is there any other way to force sqlite to use the index with the collate? jp Get your own web address. Have a HUGE year through Yahoo! Small Business. http://smallbusiness.yahoo.com/domains/?p=BESTDEAL - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Custom collate - on field or index or both?
Hi, I have a custom collation sequence (e.g. mycollate). Are there any advantages in terms of performance of declaring this collation at the table level, instead of just at the index level? For example, if I have: CREATE TABLE people AS ( country_id char(02), lastname varchar(100), phone varchar(50) ); CREATE UNIQUE INDEX people_mycollate on people ( country_id, lastname COLLATE mycollate ); - - - - - - ...will the following use the index (about 500k recs)? - - - - - - SELECT lastname FROM people WHERE country_id='US' AND lastname>'A' ORDER BY country_id,lastname COLLATE mycollate LIMIT 100; - - - - - - I want to separate the table definition from the search/query/sort order, to have the flexibility of creating/dropping indexes as needed for different collations depending on the user's locale. jp The fish are biting. Get more visitors on your site using Yahoo! Search Marketing. http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Best way to compare two databases
Assumming that the clients will NOT change the original database, I can think of 2 methods: 1. Whole DB: drop all indices, then vacuum, then zip, then propagate. On the clients (suscribers), unzip, re-create all indices. 2. Incremental, add a field to stamp the date on all your records, then export to a separate database (zip it). On clients, apply the changes. 500k records don't say much - how big (in MB) is your DB? I can also suggesst changing just the FTP site to another ISP who has unlimited (or a very large GB monthly limit). jp. Juan Perez wrote: (excuse me for the other mail, i clicked accidentally the button...) Hi: I think that i explained me bad. In my work i do next until now: Phase 1: I generate automatically a database from a CRM Phase 2: I put the database in the FTP for the commercials of the enterprise. They are located in different parts of the country. Phase 2: The commercials uses my applicaction with the database to work. The problem is that now, the datasase is too big (and we pay the FTP to an ISP for the used size and the consumed wide of band). So, i now want to change the process to: Phase 1: I generate automatically a database from a CRM Phase 2: As I already have the previous database, i will generate a diff file in the format i explained in the previous mail. Phase 3: I put the little diff file in the FTP ant the commercials downloades it.. Phase 4: The commercials brings up to date the database using my application (it needs to be changed to do it). Phase 5: The commercials can use my applicaction with the new database to work. So, the question is ¿how to do, in the best way, the new phase 2? 2006/9/4, Juan Perez <[EMAIL PROTECTED]>: Hi: I think that i explained me bad. In my work i do next work: I generate automatically a database from a CRM 2006/9/4, Paul Smith <[EMAIL PROTECTED]>: > At 16:48 04/09/2006, you wrote: > >Hi all: > > > > I have developed a program that uses a sqlite database. > > Until now the users downloaded an entire new version of the > >database weekly from the FTP server. > > But now the database is too big (about 500.000 records) and i want > >to make a database actualization system. > > So, what is the best way (having the old database and the new one) > >to obtain a file with the differences. Something like this: > > Hmm, I don't think I'd do it that way. If you do that, then you need > to have a copy of the old & new database to compare. > > One way around it is to have a 'journal' table which just contains > all the SQL queries which have been actioned (you have to take care > if you use transactions) along with an incrementing serial number. > Then, the user's software can say 'I have all journal entries up to > 252376', and then you can just given them all the journal entries > after that number, and they can run the SQL on their end. which will give. > > You can make your routine which modifies the database just keep a > copy of the SQL used whenever the action succeeds, and store that in > the Journal table. > > > > PaulVPOP3 - Internet Email Server/Gateway > [EMAIL PROTECTED] http://www.pscs.co.uk/ > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] An estimate on how many users?
Has anybody tried to estimate how many end-users are using sqlite? This would include actual firefox users and users of any other product that uses sqlite. Somebody asked me, "how many people are using sqlite" (or sqlite based products)? jp. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Disable custom collate
Hi, I created a table with a field and index that uses a custom collation function MYCOLLATE. The problem I am having is portability, I cannot perform simple selects on the table on other sqlite based applications since the custom collation function is not available. Does anybody out ther know of a PRAGMA or directive or method to disable the usage of a custom collation once created? or a way to drop it? jp.
[sqlite] vacuum changes db format
I don't know if it is a bug or works as designed, but, should VACUUM be changing the format of the DB? I created a database with sqlite 3.2.7, and after a VACUUM in sqlite 3.3.6 I could no longer open it in the older program - error message #1, unsupported file format. jp
Re: [sqlite] disabling rollback journal
Michael Scharf wrote: Without journalling, you cannot have a ROLLBACK command. And the semantics of UPDATE become UPDATE OR FAIL instead of the default UPDATE OR ABORT. The difference is subtle, but important. There are other reasons to disable the rollback journal: Suppose you want to create a database for querying only. The real data persistence happens somewhere else. The database is there to organize the data and to access the data. Once the database is filled, it will not be modified. If the filling fails, you try to refill it again. You can also think of using it for a 'bulk fill': you only use is when you file the database with the (massive) initial data. From then on you use sqlite with journaling... Michael I agree. I have a particular need for a "read only" environment, where editing happens elsewhere. This readonly database has extra indices and denormalized tables to speed up searches, but no need to INSERT, DELETE, UPDATE or CREATE/DROP anything. I also think a "readonly" version of sqlite would be nice to have. An even smaller dll/lib which only allows for SELECTs. I might attempt to do this when I have the time. Has anybody done anything like this? jp
Re: [sqlite] How can I get the type of a column?
John Stanton wrote: I endorse Jay's approach. In our web page software using Sqlite everything is TEXT except for dates in Sqlite format. We use declared types to indicate classes of text, such as decimal numbers and dates. There are no radix conversion involved in data manipulation (apart from dates), and we use a set of routines which perform decimal arithmetic on text strings so that we are not trapped in the floating point bearpit. It all fits in very elegantly with Sqlite and makes web page generation simple. Once you appreciate that Sqlite's typing makes it easier, not harder, it is easier. JS Jay Sprenkle wrote: On 5/10/06, sqlite <[EMAIL PROTECTED]> wrote: We're C++ programmers and C++ is all about data types. It's a statically typed language. I dealt with it pretty simply in my application. It produces web pages and web pages are text. Anything I retrieve is always retrieved as text. No conversions needed, nice and simple. Regarding dates, given the many different ways DATES are handled across platforms, I stick to using dates in char format, always stored as MMDD (optionally with HHNNSSss with 24-hour format, and always UTC). Easy to sort, easy to search, easy to handle, and very portable. I do have to create routines in the particular programming language to do the conversion, data entry and formatting, but it saves me a lot of work knowing that storage is always the same. jp
Re: [sqlite] sqlite puzzle
Thanks all. Actually I was just looking for the position of a single name. Based on your feedback, this one works to get the results: SELECT count(*) FROM clients WHERE name<'foo'; but its performance is directly proportional to the position of the name in the table. For example, searching for Zach takes longer than searching for Abigail. It seems it is not using any index, but rather doing a record by record sweep on the 'count'. jp
[sqlite] sqlite puzzle
SQLite provides a way to get the N-th row given a SQL statement, with LIMIT 1 and OFFSET . Can the reverse be done in an efficient way? For example, given a table with 1million names, how can I return the row number for a particular element? i.e. something like SELECT rownum FROM (SELECT name, FROM clients WHERE name='foo' ORDER BY name) I tried having rownum as the rowid, but it doesn't work since the names were not originally entered alphabetically (and wouldn't work as new names are entered). I need to synchronize a virtual list, so that the selected list # item corresponds to 'foo'. Any ideas? jp
Re: [sqlite] Most appropriate Web based database? (Newbie)
Sounds like an interesting setup! Maybe off topic, but, would you care to elaborate on that topic? Server configuration, virtualization software running, etc.? [EMAIL PROTECTED] wrote: Lenster <[EMAIL PROTECTED]> wrote: The application needs to be available to about twenty users on a daily basis, with most of those users making no more than five 'write' transactions a day, and around twenty 'read' transactions a day. The SQLite website is itself backed by SQLite. It handles between 5000 and 6000 users per day, with each user doing about 10 writes on average and dozens of queries. This is all accomplished on a server that is a virtual machine (using User Mode Linux) that is one of 24 virtual machines on the physical server. There are actually several other websites running on the same virtual machine, though the SQLite website takes most of the load. The SQLite database that backs the SQLite website has no difficulty handling this load. It could scale to much more traffic simply by devoting more of the physical server to the task. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Scrolling thru an index
In SQLite these can be combined into one query that gets the desired rows. select * from mytable where Name >= ( select Name from mytable where Name < 'Sprenkle' order by Name desc limit 1 offset 50)order by Name limit 101; This query works as expected in SQLite so it should be a work around for your union bug. Very, very nice. The idea is right on! I made a tweak: The query works well, except when searching names within the first 50, i.e. if I search 'AAA', it doesn't bring anything. To fix it, I added a "coalesce(x,'')", where x is the inner select: select * from mytable where name >= Coalesce( (select name from mytable where name < 'A' order by name desc limit 1 offset 50) ,'') order by name limit 101; This works well on the full range. Thanks all! jp
Re: [sqlite] Scrolling thru an index
Jay Sprenkle wrote:
My application is geared towards users who want to find a specific name
in a list of names, and then want to have the possibility to scroll
backwards or forwards. For example, if I search for "Sprenkle" I want
to show the user a window with "Sprenkle" in the middle, preceded by the
50 names before it, and followed by the 50 names after it, and also to
be able to smoothly scroll in either direction.
I know the index contains sufficient data to do this, but there seems to
be no way to use it from SQLite.
Get it in two chunks,
the first 100 names after the name in question:
select x from mytable where Name > 'sprenkle' limit 100
and the 100 names before the name in question:
select x from mytable where Name < 'sprenkle' limit 100 order by x desc
Right, that is the way I ended up doing it. I used "<=" instead of "<"
and added an "order by" to the first one ("order is never guaranteed
unless specifically declared").
Using a UNION of those two SELECTs does not work in 3.3.4 (bug?).
Executing them separately does work.
Thanks,
jp.
Re: [sqlite] Scrolling thru an index
Ulrik Petersen wrote: Hi JP, JP wrote: Anyway, maybe separate topic, I tried to create a "snapshot" window of the above using plain SQL, but it doesn't seem to work on Sqlite 3.3.4: CREATE TABLE clients (custid integer primary key, lastname varchar(50)); CREATE INDEX cidx ON (lastname); (insert 10,000 records here) SELECT idx,lastname FROM ( -- get names before search criteria SELECT '1' as idx,lastname FROM (select lastname from customers where lastname<'sprenkle' ORDER BY lastname DESC LIMIT 50) UNION -- get names matching search criteria plus 50 more SELECT '2' as idx,lastname FROM (select lastname from customers where lastname>='sprenkle' ORDER BY lastname ASC LIMIT 50) ) order by 1,2; Individually, the queries work fine. In UNION, each seems to lose the inner order clause and show innacurate results. jp Instead of "order by 1,2", don't you mean "order by idx"? Ulrik P. no, order by 1,2 is equivalent to "idx,lastname". Maybe I should have used 'a' and 'b' to make things more clear in the SELECT statements. jp.
Re: [sqlite] Scrolling thru an index
Jay Sprenkle wrote: Is there a way I can scroll thru a particular index? For example: 1. Scroll forward/backward on a given set of records 2. Start at position X 3. Start at a record that matches a criteria SQL is optimized to manipulate a set of records. It's much faster to execute "update mytable set mycolumn = 0 where mycondition = true" than to iterate through them and set them to zero individually. Can you do whatever it is with sql instead of code? The index is used transparently to locate rows faster. You don't iterate the index, you iterate the rows. I am thinking more in terms of the query/read part, than on updates. My application is geared towards users who want to find a specific name in a list of names, and then want to have the possibility to scroll backwards or forwards. For example, if I search for "Sprenkle" I want to show the user a window with "Sprenkle" in the middle, preceded by the 50 names before it, and followed by the 50 names after it, and also to be able to smoothly scroll in either direction. I know the index contains sufficient data to do this, but there seems to be no way to use it from SQLite. I understand that getting the data by "chunks" or "pages" the way SQL does is perfect for client/server models. But, given that SQLite is more geared towards standalone/embedded systems, it wouldn't hurt to have an extra mechanism for 'local data scrolling', maybe thru standard SQL cursors, which it currently doesn't support. Anyway, maybe separate topic, I tried to create a "snapshot" window of the above using plain SQL, but it doesn't seem to work on Sqlite 3.3.4: CREATE TABLE clients (custid integer primary key, lastname varchar(50)); CREATE INDEX cidx ON (lastname); (insert 10,000 records here) SELECT idx,lastname FROM ( -- get names before search criteria SELECT '1' as idx,lastname FROM (select lastname from customers where lastname<'sprenkle' ORDER BY lastname DESC LIMIT 50) UNION -- get names matching search criteria plus 50 more SELECT '2' as idx,lastname FROM (select lastname from customers where lastname>='sprenkle' ORDER BY lastname ASC LIMIT 50) ) order by 1,2; Individually, the queries work fine. In UNION, each seems to lose the inner order clause and show innacurate results. jp
[sqlite] Scrolling thru an index
In a previous message: The way indices work in SQLite is that there is one row in the index for each row in the table but the index rows are in index order. ... D. Richard Hipp <[EMAIL PROTECTED]> Is there a way I can scroll thru a particular index? For example: 1. Scroll forward/backward on a given set of records 2. Start at position X 3. Start at a record that matches a criteria All of these based on a specific index? This is kind of the way Dbase worked (browse, seek, locate, etc.) Given that the "indexes" are there, can it be done? Has someone done anything similar? jp
