[sqlite] Unql
What ever happened to Unql and is there any chance it will be revived? It seemed like it would have been incredible useful. :( ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] index confusion
On 07/17/2011 09:50 AM, Simon Slavin wrote: > > On 17 Jul 2011, at 2:40pm, dcharno wrote: > >> I have a table where columns a and b form a unique key for column c. In >> an attempt to speed up queries I added an index on a and b. >> >>CREATE TABLE t(a TEXT, b TEXT, c TEXT, CONSTRAINT u UNIQUE(a,b)); >>CREATE INDEX iab ON t(a, b); >> >> But, an automatic index is being used even though it seems like the >> index terms should be usable according to the optimizer overview: >> >>sqlite> EXPLAIN QUERY PLAN SELECT * FROM t WHERE a="foo" AND b="bar"; >>0|0|0|SEARCH TABLE t USING INDEX sqlite_autoindex_t_1 (a=? AND b=?) >> (~1 rows) > > The CONSTRAINT you defined on the TABLE requires SQLite to make up its own > index. Because without that index it would have to scan every row of the > table whenever you INSERTed a new row to see if there was a clash. > > Since SQLite already has an idea index for your query it never get as far as > noticing that you made another one yourself. Thanks. That makes sense now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] index confusion
I have a table where columns a and b form a unique key for column c. In an attempt to speed up queries I added an index on a and b. CREATE TABLE t(a TEXT, b TEXT, c TEXT, CONSTRAINT u UNIQUE(a,b)); CREATE INDEX iab ON t(a, b); But, an automatic index is being used even though it seems like the index terms should be usable according to the optimizer overview: sqlite> EXPLAIN QUERY PLAN SELECT * FROM t WHERE a="foo" AND b="bar"; 0|0|0|SEARCH TABLE t USING INDEX sqlite_autoindex_t_1 (a=? AND b=?) (~1 rows) Even if I disable automatic indexing, its still being created: sqlite> PRAGMA automatic_index = 0; sqlite> EXPLAIN QUERY PLAN SELECT * FROM t WHERE a="foo" AND b="bar"; 0|0|0|SEARCH TABLE t USING INDEX sqlite_autoindex_t_1 (a=? AND b=?) (~1 rows) What am I missing here? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] detecting a corrupt database
I have a database that returns SQLITE_CORRUPT during certain queries, but other than that it opens, closes and generally works fine. - Is there a way to check the database when its open, other than running through a bunch of test queries? - Is there any general way to fix corruption issues? (probably not but I thought I'd ask) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] virtual tables
What are some of the things that people use virtual tables for? Are there any good example usages? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ Samples
centipede moto wrote: > I am new to c++ (I know php, c# etc), and thanks to this list I've gotten > sqlite3 to open a db connection without failing to find its libraries. But > now that I have an open db connection I am lost, I can work my way through > the c++ itself but I'm having a hard time finding c++ sqlite3 samples, > demonstrating basic querying and updating, table creation etc. I've seen > straight sqlite3 samples but haven't really found any noobie c++ samples / > tutorials for sqlite3. Are there any great resources out their for c++ & > sqlite3 greenhorns? > > Thank you SO much!! Consider getting a copy of "The Definitive Guide to SQLite" by Michael Owens. Chapter 6 will walk you through using the core C api. It also goes into the internals of SQLite and has an excellent reference section on SQL. Once you've mastered that, you can look at grabbing a C++ wrapper or creating your own. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] gentle intro to including sqlite in another program
P Kishor wrote: > so, I have read the tutes on the website, but just wanted to confirm... > > I want to take baby steps to including sqlite's capabilities in my > modeling program. Do I just include sqlite3.h and compile my program > and magic will happen, or do I need to do something else? > > Using Xcode. The model itself has about 30 or 40 different .c files > and about a dozen or so .h files. > > I am a C newbie (but, by golly, I will learn some by the end of this), > so be gentle in your replies. > Grab the amalgamation, add sqlite3.c to your makefile and include sqlite3.h in your program files as necessary. At some point, you may want or need to add some compilation options depending upon your system (e.g. thread safety or page size). I usually create a wrapper for the database access so I can have some abstraction which makes sense for the objects I am working with and isolate all the SQL into one area of the code. What this wrapper looks like will largely depend upon your system and how you use SQLite. If you program in C++, you could consider a C++ wrapper to make your life even simpler. SQLiteDatabase.cpp from WebKit can provide a good starting point or be used as inspiration to roll your own. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] httpd server ???
> The SQLite website is implemented using a profoundly simple HTTP > server that runs off of inetd. The complete source code is contained > in a single file of C code that is available on-line at: > > http://www.sqlite.org/docsrc/artifact/84d487ac34 Just to clarify, this code is part of Fossil which is licensed under GPL, correct? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] manual with sample C programs
> Could you tell me where can I find such documentation, or can you > recommend some books. "The Definitive Guide to SQLite" by Michael Owens explains the SQLite API in detail and provides a number of samples in C. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.4 planned for 2008-10-15
D. Richard Hipp wrote: > On Oct 9, 2008, at 9:11 PM, dcharno wrote: > >>> If you have issues or concerns with any aspect of the upcoming >>> release, now would be a good time to raise them. >> Is there any way to have both the BNF and syntax diagrams in the SQL >> Syntax? > > > Not really. Explain to me again why you want BNF instead of syntax > diagrams? Most people find the syntax diagrams to be much easier to > understand. I could imagine its a lot of effort to create those syntax diagrams. They didn't seem any easier to follow, at least to me. Maybe they grow on you ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.4 planned for 2008-10-15
> If you have issues or concerns with any aspect of the upcoming > release, now would be a good time to raise them. Is there any way to have both the BNF and syntax diagrams in the SQL Syntax? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite C++
Darko Miletic wrote: >> Maybe he is looking for a C++ wrapper for Sqlite. > > Than look no further. SOCI is the definite sqlite c++ wrapper. > > http://soci.sourceforge.net/ This wrapper looked really promising. But, the SQLite backend wasn't being maintained and is no longer officially supported in the latest releases. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite in embedded environment
Steven Woody wrote: > On Sat, Jun 28, 2008 at 11:30 AM, dcharno <[EMAIL PROTECTED]> wrote: >>> As an option, we also think about Berkeley DB, do you experts has >>> experience using Berkeley DB on ARM/Linux with ulibc or glibc? >> Berkeley DB may also be an option. It really depends upon what you are >> trying to accomplish, what your data set looks like, etc. >> >> Ironically yes; I am translating Berkeley DB databases into SQLite for >> analysis. > > :-) sounds like a good method Except for legacy issues, I can't think of any reason you'd ever need both. If your options are SQLite and Berkeley DB then a couple of points to consider: With SQLite, you obviously have the full power of SQL to model your system's data and write arbitrarily complex queries to filter and analyze your data. And SQL is standard so it is well documented and easy for other team members to access. Berkeley DB is a persistent hash table. Its good if your data is primarily key/value based and you only need to do key lookup. But, its really just a storage layer. It doesn't provide any type of query capability for filtering or searching through your data -- all of that has to be written as a layer above Berkeley DB by you. Berkeley DB is a bit bigger than SQL Compare the licenses. Be sure to read the license of Berkeley DB to make sure it is compatible with your application. http://en.wikipedia.org/wiki/Berkeley_DB#Licensing SQLite is in the public domain, so you are free to do with it what you please. http://www.sqlite.org/copyright.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite in embedded environment
> As an option, we also think about Berkeley DB, do you experts has > experience using Berkeley DB on ARM/Linux with ulibc or glibc? Berkeley DB may also be an option. It really depends upon what you are trying to accomplish, what your data set looks like, etc. Ironically yes; I am translating Berkeley DB databases into SQLite for analysis. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite in embedded environment
> I am considering to use SQLite in my current embedded application > project. It's a ARM9/Linux. Do you experts think it is a good idea？ > And, is there any tips or considerations in this combination? Currently using SQLite on an ARM7 running ucLinux. SQLite is an excellent choice for many embedded applications, but you need to read up to make sure it is an appropriate match for your problem. A good starting point is: http://www.sqlite.org/whentouse.html The things I really like: - the library is very small, but you get an awful lot of functionality. - SQLite uses a single database file and its cross platform. So, you can pull the database over to a PC and easily do analysis of your data using a variety of languages like python or tcl. - the mailing list has lots of very smart and helpful people. HTH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite C++
>> Is there any future plan to develop sqlite in C++. >> > > Why would anybody want to do that? Maybe he is looking for a C++ wrapper for Sqlite. http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexing and Search speed
From: Dennis Cote <[EMAIL PROTECTED]> > No, that's not true. A sub-query is like any other query. I have > rearranged the query to make it more readable. > > select types.Track,types.URL > from ALBUM > inner join (select * from MUSIC where Artist_Id =?) as types > on ALBUM.AlbumId=types.Album_Id > order by ALBUM.YomiAlbumName; > > In this case the sub-query is returning the subset of the Music table by > the specified artist. This result table is given the name "types", and > it is joined to the album table. I didn't know this was possible -- my trusty "learn SQL in 10 minutes" has failed me. But it makes sense from your explanation. Thanks. In most cases like this, I've 'hidden' the join inside a view so the syntax is clean. But, I expect moving the filtering before the join is much more efficient, no? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexing and Search speed
> select types.Track,types.URL from ALBUM inner join (select * from MUSIC > where Artist_Id =?) types on ALBUM.AlbumId=types.Album_Id order by > ALBUM.YomiAlbumName ; How does the subquery work in this statement? I thought subqueries could only retrieve a single column. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step back (again)
Jeff Hamilton wrote: > It shouldn't matter, the rowid is guaranteed to but unique since it's > the row's key into the table data b-tree. The ORDER BY in my example > adds sorting based on that value when the titles are the same so you > in effect have a unique sort key that is ordered. Ahh. Makes sense now. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step back (again)
Jeff Hamilton wrote: > What about something like this: > > SELECT title FROM tracks > WHERE singer='Madonna' >AND (title<:firsttitle OR (title=:firsttitle AND rowid<:firstrowid)) > ORDER BY title DESC, rowid ASC > LIMIT 5; > > Then you only have to remember the single title and rowid of the first > item in the list. You'd have to add the rowid ASC to your index as > well, but the index already needs to store the rowid so I don't think > it would take more space. But, I think the rowid has no specific ordering to it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step back (again)
>> In the next query, dump any rows where (title = >> last_seen_title) and (rowid != last_seen_rowid). > > Up until you hit the last seen rowid, yes. That was my first idea as > well. Right. Tried it in a quick prototype and it seemed to worked okay. > The big thing to remember is that the rowids aren't going to be > ordered in any way. Their main useful property, in this context, is I was actually trying to think if there was a way to do that. Have a column of a view that was just an incrementing sequence. Then its a simple matter of using this virtual index to search for the next on-screen window. Really have to take some time to learn about these custom functions. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step back (again)
Jay A. Kreibich wrote: > You quoted the backward example, but I'm going to use the forward version. > > In addition to the "last seen title", remember the RowID for every row > with the same "last seen title". > > For the forward query use "AND title>=:firsttitle" and pitch rows > that match any of the remembered RowIDs. (Backwards use "<=".) > > You'll also need to increase your LIMIT by the number of RowIDs you > remembered. If you don't end up pitching rows and get a full set of > data before the LIMIT is hit, you can just call _finalize on the > statement. Thanks. It seems remembering the rowids would be a bit cumbersome and potentially error prone esp when you change from scroll down to scroll up. I think you might get the same result by remembering the last seen title and its rowid. In the next query, dump any rows where (title = last_seen_title) and (rowid != last_seen_rowid). Also considered trying to make a sort of signum user defined function that might be able to mask off the rows right in the select statement. I haven't done a custom function yet though, so I'm not sure if it would work. The other thought was to simulate a bidirectional or random-access cursor by first doing a query for rowids and then doing a query for the specific row when requested. Less than ideal certainly, but was more along the line of what the gui folk want to deal with in the first place. Its yuk all around ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] step back (again)
[EMAIL PROTECTED] wrote:
> This issue keeps coming up so I did a wiki page.
> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
I'm using the method described in the wiki and it was working pretty
well until I hit a data set where the sorting column was not unique.
Here is the query from the wiki:
SELECT title FROM tracks
WHERE singer='Madonna'
AND title<:firsttitle
ORDER BY title DESC
LIMIT 5;
Imagine if several songs have the same title -- this could happen, for
example, if the ID3 tags are messed up ('untitled', 'untiled') or if the
user has several versions of the same song. Using this query, we'll end
up skipping over all the songs with the same title.
Any thoughts on how to handle this?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] step back (again)
[EMAIL PROTECTED] wrote:
> This issue keeps coming up so I did a wiki page.
> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
I'm using the method described in the wiki and it was working pretty
well until I hit a data set where the sorting column was not unique.
Here is the query from the wiki:
SELECT title FROM tracks
WHERE singer='Madonna'
AND title<:firsttitle
ORDER BY title DESC
LIMIT 5;
Imagine if several songs have the same title -- this could happen, for
example, if the ID3 tags are messed up ('untitled', 'untiled') or if the
user has several versions of the same song. Using this query, we'll end
up skipping over all the songs with the same title.
Any thoughts on how to handle this?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DeviceSQL
I would like to recommend that Encriq create a forum or mailing list of their own for those who are interesting in learning more. For me, what might be an interesting product is quickly being overshadowed by this thread. You raise some interesting points. There is nothing secret about the benchmarks. We will make the code that was used to run benchmarks available to anyone who wants to see it and verify results. If you want to find a third party to verify, be my guest. The benchmark report goes into some depth on the design and rationale for the benchmark. Frankly, as much as I like the idea about taking DeviceSQL open source, you don't need to do so, just to verify performance claims. Do you need to read the code to verify reliability as your next few sentences seems to imply? For that to be true, the reader would have to be able to spot bugs through inspection. While that is certainly one way to spot bugs, I seriously doubt that any shop would rely on code inspection, when millions of dollars of potential recall costs are on the line. In fact the SQLite marketing does not rely on code inspection as its argument for why the code is reliable. Check it out. All of that said, I do admire the elegance of the SQLite code. It makes entertaining reading. Unfortunately elegance does not translate into performance or reliability. Regards, Steve James Steward-2 wrote: steveweick wrote: Richard has it right this time. Today DeviceSQL uses no SQLite code. One of the things we might consider is bolting the SQLite parser/front end to our table engine, in theory to get the both worlds. Just an idea at the moment. Such an interesting discussion to be following. I must say though, it seems DeviceSQL has opened the door to speculation due to unsubstantiated claims in advertising, as far as I see it. IMHO, so long as there is no independent, unbiased, side by side test results presented somewhere by some reliable source, there will always be some room for "ifs" and "buts" by both sides. Maybe DeviceSQL should go open source, so the public can judge for them selves the qualities of the two products. There would still be money to be made from paid support. Who knows, both parties could benefit, and customers too. At least there'd be a clearer view of the pros and cons. There is something to be said for a product being open source, that is the code is scrutinized by the world. Closed shop code can possibly still be very good, but without seeing it, how would we know? Reminds me of a story about a cat: dead or alive, we won't know until we open the box it's in, and prior to that, is it only half dead? One only has to look at the MSDN code examples to see the ugliness of closed source code development...(sorry Bill) JS. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: AW: [sqlite] sqlite performance, locking & threading
Can we please stop this thread? John Stanton wrote: Emerson, one posts to a forum like this to get help and other ideas, not to spark a debate. Many talented people gave you some of their time to help you solve your problem and one in particular gave you a well conceived and executed piece of software free of charge. Appreciate their charity. If you have some insights which have escaped the rest of us, implement them and present the results to the world and dazzle us. BTW, a major advantage of Open Source software is that you do not need to have people explain it to you. You have the source and that explains everything. That is particularly so with Sqlite, which is clearly written and consequently the source reads like a book. A few minutes with the source and grep and you have your answers. Emerson Clarke wrote: John, Um, alright then... But i think your preaching to the converted, simplifying things is what i always try to do. And not just when theres a problem If you followed the thread fully you would realise that there was never a problem with my design, though that didnt stop many people from chirping in and offering suggestions. The problem i had was with sqlite not being compatible with the simple design that i wanted. I did try several alternate designs, but only as a way of working around the problem i had with sqlite. It took a long time but eventually i managed to get someone to explain why sqlite had that particular problem, and i was able to modify the sqlite source to resolve the issue. Unfortunately no one has yet commented on my solution, or the problem which it addresses. Basically sqlite has thread safety checking routines which work a little like mutexe's. Every time you enter a bit of code which is potentially thread unsafe it sets a magic number, then resets it when it comes out. This is an attempt to detect when two threads are accessing the same bit of code at the same time. Clearly its not 100% reliable, and is subject to all kinds of thread races, but it does provide some measure of protection. Unfortunately though, the way it has been coded, an unbalanced safety check is performed in the sqlite3_step() function. This is equivalent to entering a mutex but never leaving, which causes deadlock in a multithreaded program. Only in this situation sqlite throws a misuse error any time two or more threads use sqlite3_step() at the same time, even if those threads are synchronised and perfectly safe. The easy solution is to disable the safety checks, the propper solution is to balance out the checks in sqlite3_step() so that users who actually wish to use sqlite in a multithreaded program are free to synchronise access to the api without error and there is still a reasonable level of safety checking for users who do not synchronise properly. Emerson On 1/5/07, John Stanton <[EMAIL PROTECTED]> wrote: Work on turning "reasonable" into "adequate" or "good" and it will help you get an intuitive feel for the design of programs such as yours. Then your programs will be simple, fast and robust, as Einstein counselled - "Make it as simple a possible, but no simpler". I also suggest that you take Niklaus Wirth's advice and when you run into a difficulty backtrack your work and scrap everything until you reach a point where there are no problems and start again from that point taking a different approach having learned a lesson from your previous attempt. By the way, I doubt whether you are using a modern operating system, it is most likely to be old technology like Windows or Linux. Neither supports much in the way of parallelism. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Inserting uniquely
Lloyd wrote: Hi List, Is there is any easy way to insert a value to a table, if and only if it is not existing ? http://sqlite.org/lang_conflict.html ON CONFLICT clause conflict-clause ::= ON CONFLICT conflict-algorithm conflict-algorithm ::= ROLLBACK | ABORT | FAIL | IGNORE | REPLACE The ON CONFLICT clause is not a separate SQL command. It is a non-standard clause that can appear in many other SQL commands. It is given its own section in this document because it is not part of standard SQL and therefore might not be familiar. The syntax for the ON CONFLICT clause is as shown above for the CREATE TABLE command. For the INSERT and UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR", to make the syntax seem more natural. For example, instead of "INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE". The keywords change but the meaning of the clause is the same either way. The ON CONFLICT clause specifies an algorithm used to resolve constraint conflicts. There are five choices: ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. The default algorithm is ABORT. This is what they mean: - To unsubscribe, send email to [EMAIL PROTECTED] -
