[sqlite] Sqlite Virtual Table Example with non-trivial xBestIndex (was: Porting SQLite to another operating system (not supported out of the box))
Having personally written about a dozen virtual table implementations I can confirm that those implementations needing a nontrivial xBestIndex function are all based on building an SQLite interface on substantial proprietary storage subsystems like an in-memory ISAM table (with configurable record and key structure), a Faircom CTree driver (adding configurable record and key structure), a partition provider (allowing storage to be split between several tables of identical structure by configurable record fields), etc. One of the more challenging tasks involved adding a fastbit based index to a variable record length event logfile for an OLTP application. Simple virtual tables require retrieval by rowid (e.g. memory address or file offset) at best. -Ursprüngliche Nachricht- Von: Jay Kreibich [mailto:j...@kreibi.ch] Gesendet: Mittwoch, 11. Februar 2015 04:04 An: Peter Aronson; General Discussion of SQLite Database Betreff: Re: [sqlite] Porting SQLite to another operating system (not supported out of the box) On Feb 10, 2015, at 11:21 AM, Peter Aronson pbaron...@att.net wrote: You could add VFS creation if you ever do a revised edition (along with a virtual table example that actually used xBestIndex and xFilter…) Given that the book is over four years old and covers to the end of SQLIte3 3.6.x, there are a lot of things that would need to go into a revised edition… including a lot more examples of everything, according to reviews. We greatly underestimated the number of SQLite developers that were touching SQL for the first time, and I would have never guessed people would have considered yet another SQL lesson to be so important, given that there are a million books and a bazillion websites on learning SQL basics. You can literally find books on “SQL For Dummies” (Allen Taylor) to “SQL For Smarties” (Joe Celko), and everything in-between. That last book (or books, actually) is awesome, BTW, and the “Advanced SQL Programming” one should be on the shelf of every database programmer doing anything more advanced than an address book. Regardless, if we do a second edition (and at this point that’s an extremely, exceptionally big “if”), VFS is unlikely to make the cut. Consider that out of the thousands of SQLite applications and billions of installed databases, there are likely less than 100 production VFS modules in the whole world. Spending a lot of time and pages, driving up the cost of the book, covering an extremely advanced and obscure topic is a poor trade-off (every page averages about a day to write/edit/prep, and adds about $0.10 to the price of the book). If you need that level of integration and detail, working in the guts of the I/O and locking system, you should likely hand the task to a systems engineer that is familiar with the problem domain and isn’t afraid of looking through a few headers and examples to figure it all out. It’s advanced, custom stuff that is, almost by definition, not textbook work. It is the kind of work that requires digging through nitty-gritty code, documentation, and examples from both SQLite and your environment. This is the kind of thing that’s learned from years of experience, not by reading it in a book. That isn’t meant to be a criticism of the original poster— there is a huge difference between asking if anyone knows where to start looking, and asking for detailed step-by-step instructions. In fact, if we did decide to put some information about VFS modules in a book, it would likely be a discussion of how the structures and APIs fit together, what they’re used for, and the types of things that can be done with them— exactly the kind of info you need to get started, but not much beyond that. After all, what goes in those functions is going to be extremely dependent on the environment the VFS is trying to use. I might say similar things about the xBestIndex() and xFilter() functions. While the APIs and how they are used is a tad confusing, their purpose and function should be reasonably straight forward to someone comfortable with relational data management and design. While the book attempts to cover how the APIs are meant to perform their tasks (and has a six page discussion on their purpose and use), actually writing such a function is extremely dependent on understanding the virtual table being design— and the data in it. I feel it is something that just needs to be done by a skilled engineer, with a lot of detailed knowledge about the problem that’s trying to be solved. Again, there aren’t any real textbook examples here; yes, I could write a contrived example, but if they didn’t understand from a general description, a single specific example is unlikely to help anyone in their specific case. At the end of the day, both functions are an optimizations anyways. You can write a functional virtual table without them, it might just run a tad slower. If you really need
Re: [sqlite] Multi-thread mode question
In serialized mode, SQLite will acquire the mutex when it detects you are starting to use the database handle (somewhere between entering sqlite3_prepare and the first sqlite3_step) and then HANG ON TO IT, NOT LETTING GO until the calling thread is finished (like when sqlite3_step returns SQLITE_DONE or the thread calls sqlite3_reset or sqlite3_finalize). In multithread mode, you are taking over this responsibility; if you take care, you may nest several selects from different threads into a single transaction, but need to be aware of the fact that they will all commit or rollback together. -Ursprüngliche Nachricht- Von: Neo Anderson [mailto:neo_in_mat...@msn.com] Gesendet: Montag, 09. Februar 2015 06:34 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Multi-thread mode question Does the application work if you configure SQLite to serialized mode? Yes. But I am confused why serialized mode works while multi-thread mode always cause crashes because I also wrap calls around statement handle. even if you wrap the sqlite3_ calls... you'll need to wrap the entire lifetime of the statment... Do I need to do this in serialized mode (suppose I use a single connection across multiple threads)? Date: Sun, 8 Feb 2015 03:31:46 -0800 From: d3c...@gmail.com To: sqlite-users@sqlite.org Subject: Re: [sqlite] Multi-thread mode question it's better to use a connection per thread... the connection resource isn't very big... even if you wrap the sqlite3_ calls... you'll need to wrap the entire lifetime of the statment... if you do a execute and then start stepping and getting values while another thread starts another statement... that's 3 individual locks, but it doesn't lock the context of the statement being used... it will lead to bizarre crashes in the database; similar to double-releasing memory or delayed reference of memory that has been released. On Sun, Feb 8, 2015 at 3:00 AM, Dan Kennedy danielk1...@gmail.com wrote: On 02/08/2015 04:30 PM, Neo Anderson wrote: The doc says: Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads. I have a scenario that every sqlite3_calls around a single database connection is protected by a recursive mutex, but I have very strange runtime error in sqlite3.c and each time the error occurs at a different place. Does this mean the following statement is true: In muti-thead mode, a single database connection cannot be shared among threads even if any activity around the connection is protected by a mutex. Not true. The only difference between multi-threaded and serialized mode is that, internally, every sqlite3_xxx() API call grabs a recursive mutex to prevent two threads from simultaneously accessing the database handle structure. i.e. the same thing your code is doing externally. Note that calls on statement handles (i.e. sqlite3_step(), sqlite3_column_text() etc.) count as calls on the database handle that created them. So you need to protect them with the same mutex. Does the application work if you configure SQLite to serialized mode? Dan. ___ 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 ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] regarding looping in vdbe for sqlite table joins!
SCAN in the query plan = Rewind...Next LOOP in opcodes SEARCH in the query plan = Column...Seek in opcodes SQLite has determined that creating an automatic index on the referenced tables should be faster than performing a full table scan for the general case. asql explain query plan select * from em,idv,mny,lo where em.name=lo.name and idv.id=mny.id; sele order from deta - 0 0 0 SCAN TABLE em (~100 rows) 0 1 3 SEARCH TABLE lo USING INDEX sqlite_autoindex_lo_1 (name=?) (~1 rows) 0 2 1 SCAN TABLE idv (~100 rows) 0 3 2 SEARCH TABLE mny USING INDEX sqlite_autoindex_mny_1 (id=?) (~1 rows) asql explain select * from em,idv,mny,lo where em.name=lo.name and idv.id=mny.id; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 NULL 1 Goto 0 42000 NULL 2 OpenRead 0 2 1 2 00 em 3 OpenRead 3 8 1 2 00 lo 4 OpenRead 4 9 1 Keyinfo(1,BINARY) 00 sqlite_autoindex_lo_1 5 OpenRead 1 4 1 2 00 idv 6 OpenRead 2 6 1 2 00 mny 7 OpenRead 5 7 1 Keyinfo(1,BINARY) 00 sqlite_autoindex_mny_1 8 Rewind 0 35000 NULL 9 Column 0 0 100 em.name 10IsNull 1 34000 NULL 11Affinity 1 1 0 d 00 NULL 12SeekGe 4 341 1 00 NULL 13IdxGE 4 341 1 01 NULL 14IdxRowid 4 2 000 NULL 15Seek 3 2 000 NULL 16Rewind 1 34000 NULL 17Column 1 0 300 idv.id 18IsNull 3 33000 NULL 19Affinity 3 1 0 d 00 NULL 20SeekGe 5 333 1 00 NULL 21IdxGE 5 333 1 01 NULL 22IdxRowid 5 4 000 NULL 23Seek 2 4 000 NULL 24Column 0 0 500 em.name 25Column 0 1 600 em.age 26Column 1 0 700 idv.id 27Column 1 1 800 idv.name 28Column 5 0 900 mny.id 29Column 2 1 10 00 mny.sal 30Column 4 0 11 00 lo.name 31Column 3 1 12 00 lo.addr 32ResultRow 5 8 000 NULL 33Next 1 17001 NULL 34Next 0 9 001 NULL 35Close 0 0 000 NULL 36Close 3 0 000 NULL 37Close 4 0 000 NULL 38Close 1 0 000 NULL 39Close 2 0 000 NULL 40Close 5 0 000 NULL 41Halt 0 0 000 NULL 42Transaction1 0 000 NULL 43VerifyCookie 1 4 000 NULL 44TableLock 1 2 0 em 00 NULL 45TableLock 1 8 0 lo 00 NULL 46TableLock 1 4 0 idv00 NULL 47TableLock 1 6 0 mny00 NULL 48Goto 0 2 000 NULL -Ursprüngliche Nachricht- Von: Sairam Gaddam [mailto:gaddamsai...@gmail.com] Gesendet: Montag, 02. Februar 2015 16:24 An: sqlite-users@sqlite.org Betreff: [sqlite] regarding looping in vdbe for sqlite table joins! Normally for executing joins in sqlite,the vdbe program opens 1 loop for each and every table but in my code(attached that file via pastebin) ,i am facing an issue because it is opening only 2 loops even if i use 4 tables in joining operation. can anyone explain why it happened like that and loops for which tables got opened? code : http://pastebin.com/PHV4K4Hh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien
Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery
NO. Only if all of the following apply - you are parsing and/or displaying raw returned column names - your select contains more than one table (a table joined to itself counts as 2) - your tables have columns that share the same name -Ursprüngliche Nachricht- Von: Tim Streater [mailto:t...@clothears.org.uk] Gesendet: Montag, 26. Jänner 2015 13:00 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery On 26 Jan 2015 at 07:33, Hick Gunter h...@scigames.at wrote: It is never a good idea to rely on automatically assigned column names. If you want reproducible, predictable, release independant column names then please assign them with the AS clause. So you're saying that if I do: create table wiggy (a,b,c,d,e,f,g,h); and later do: select a, b, c, d, e, f from wiggy; then I should really be doing: select a as a, b as b, c as c, d as d, e as e, f as f from wiggy; That'll be a bit tedious changing the more than 300 select statements in my app. I notice that in the PHP doc for SQLite result sets, they carefully talk about the column names returned in the result set rathe than just column names, although I suppose that is just to indicate that names may be provided with AS. -- Cheers -- Tim ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database locked in PHP
Maybe you can use the (linux, c) code I posted recently to determine which process/thread is locking the database file. -Ursprüngliche Nachricht- Von: Lev [mailto:leventel...@gmail.com] Gesendet: Sonntag, 25. Jänner 2015 01:36 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] database locked in PHP On Sat, 24 Jan 2015 20:59:22 + Simon Slavin slavins-drl2ohjjk6tg9huczpv...@public.gmane.org wrote: and set it to 6 (60 seconds) or so. Okay, I try that, but I still don't understand how can a single threaded application get a locked error. Levente ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery
It is never a good idea to rely on automatically assigned column names. If you want reproducible, predictable, release independant column names then please assign them with the AS clause. -Ursprüngliche Nachricht- Von: Marcus Bergner [mailto:marcusberg...@gmail.com] Gesendet: Sonntag, 25. Jänner 2015 14:16 An: sqlite-users@sqlite.org Betreff: [sqlite] Invalid column prefix returned in SELECT with joined subquery Hi, Using the latest amalgamation build sqlite-autoconf-3080801 I'm seeing the following inconsistent behaviour: $ ./sqlite3 sqlite .headers on sqlite pragma short_column_names; 1 sqlite pragma full_column_names; 0 sqlite create table tbl1 (id1 integer, s1 text); create table tbl2 (id2 sqlite integer, s2 text); insert into tbl1 values (1, 'v1'); insert sqlite into tbl2 values (1, 'v2'); select x.id1, x.s1, y.s2 from tbl1 x sqlite inner join tbl2 y on x.id1=y.id2; id1|s1|s2 1|v1|v2 So far so good, everything as expected. If I rewrite the above select statement to do a join with a subquery instead the resulting output changes in an unexpected way. sqlite select x.id1, x.s1, subq.s2 from tbl1 x inner join (select * sqlite from tbl2 y where y.id2=1) subq on x.id1=subq.id2; x.id1|x.s1|subq.s2 1|v1|v2 Here we get unexpected column prefixes on all fetched columns. If I rewrite the query again to a subq.* query the behaviour is different again where only the first two columns have prefixes. sqlite select x.id1, x.s1, subq.* from tbl1 x inner join (select * from tbl2 y where y.id2=1) subq on x.id1=subq.id2; x.id1|x.s1|id2|s2 1|v1|1|v2 Expected behaviour: returned columns should not contain prefixes in any of the above scenarios. Kind regards, Marcus Bergner ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shell tool question
Yes. I'm using several layers of .read files to load the appropriate subset of extensions for each class of process (OLTP, user query, subsystem, specific tools,...) -Ursprüngliche Nachricht- Von: Simon Slavin [mailto:slav...@bigfraud.org] Gesendet: Mittwoch, 21. Jänner 2015 17:35 An: General Discussion of SQLite Database Betreff: [sqlite] Shell tool question Quickie: In the Shell Tool, is '.read' recursive ? In other words, if I use .read to read a script and I have .read in a script, will the shell tool finish both scripts correctly ? I ran a quick test and I know what I got, but I wanted to be reassured I wasn't just lucky. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite - Exception Calling SQLiteModule.DeclareTable
I have always wondered why people will insist on using human readable column names (with embedded spaces and special characters) in the implementation layer (SQL code) instead of the presentation layer (user interface). The clutter introduced into queries by having to quote the column names by far outweighs any gain from having some strange field name displayed instead of some_strange_field_name... -Ursprüngliche Nachricht- Von: Mike Nicolino [mailto:mike.nicol...@centrify.com] Gesendet: Donnerstag, 22. Jänner 2015 02:17 An: General Discussion of SQLite Database Betreff: Re: [sqlite] System.Data.SQLite - Exception Calling SQLiteModule.DeclareTable Figured this one out. DeclareTable doesn't like any 'quoting' around the column names in the sql. It works fine with just straight column names. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mike Nicolino Sent: Saturday, January 17, 2015 6:17 PM To: sqlite-users@sqlite.org Subject: [sqlite] System.Data.SQLite - Exception Calling SQLiteModule.DeclareTable I'm getting an exception calling SQLiteModule.DeclareTable that seems to imply the 'create table' sql being passed is invalid: SQL logic error or missing database. Yet using that same sql on the same connection as a create table call succeeds. Reviewing the virtual table docs don't imply there are restrictions on the create table sql for virtual tables so I'm at a loss to what's wrong. The create table sql (the line breaks here are for readability and not present in the actual string send to DeclareTable): create table xxx( Username text, DisplayName text, Email text, LastLogin integer, LastInvite integer, Status text, SourceDs text, Data text, SourceDsLocalized text ) Anyone have any input on what might be wrong? Thanks! ___ 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 ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update Statements using Sub query
You are requesting the field Column Name (with embedded whitestpace in the field name) from a query with a where clause that forces it to be the string 'Date'. Perhaps your are looking for SELECT Date -Ursprüngliche Nachricht- Von: MikeSnow [mailto:michael.sab...@gmail.com] Gesendet: Dienstag, 20. Jänner 2015 14:10 An: sqlite-users@sqlite.org Betreff: [sqlite] Update Statements using Sub query I was wondering if anyone could help I am trying to use Excel to create update statements based on certain criteria. For example...this works UPDATE CDR_Adjusted SET DateTime=DATE|| ||TIME; (This is what I need, a simple Concat 2013-10-11 7:59 But when I try to do this UPDATE CDR_Adjusted SET DateTime =( SELECT [Column Name] FROM Providers_Import WHERE Provider = 'abc' AND Version = '2013-2014' AND [Column Name] = 'Date' ) || Time ; I get this Date || Time Not the update, I would expect above (In the table Providers_Input, the value in the [Column Name] is 'Date') Thanks is advance -- View this message in context: http://sqlite.1065341.n5.nabble.com/Update-Statements-using-Sub-query-tp80175.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 ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Page_size increase from 1k to 4k made my REPLACE INTO slower. why?
From what little you reveal I assume this is some kind of datalogging application. I also assume there will be a primary key (call ?) and also suspect that there are a number of secondary indices for data retrieval. Since you make no mention of transactions, I must infer that you are using autocommit, i.e. the data is committed to disk for each and every row. For the primary key, only the rightmost page of the b-tree will be affected; for other indices, it is quite likely that random pages (different for each row) will be hit. In this scenario, increasing the page size means that 4 times as many sectors need to hit the disk surface for each statement. To increase speed, consider committing the inserts at regular intervals (1 per second maybe?), so that disk updates (which is where the time is spent) happen only once every quite a few records. Larger pages then have a chance of getting filled and may also improve the locality of writes, i.e. fewer seeks and more consecutively written sectors. You might also consider having a logger thread that writes into alternating table(s) (e.g. even and odd seconds' data) with INTEGER PRIMARY KEY and no other indices; and a transfer thread that copies the entries over into the real table in a batch transaction. -Ursprüngliche Nachricht- Von: Andy (KU7T) [mailto:k...@ku7t.org] Gesendet: Montag, 19. Jänner 2015 09:06 An: sqlite-users@sqlite.org Betreff: [sqlite] Page_size increase from 1k to 4k made my REPLACE INTO slower. why? Hi, I am using System.Data.SQLite, Version=1.0.92.0. I read that increasing the page_size to 4k on modern OS is a good thing and should speed things up. However, I have a particular query that takes substantially longer. I tried to make sure that nothing else is changed, so I am a little puzzled. Can anyone explain why this is? Query is this, in case this gives a clue: REPLACE INTO PacketSpots (Call, TS, Band, Freq, QSXFreq, Comment, Spotter, Bearing, CountryPrefix, Sect, ZN, Exchange1, GridSquare, MiscText, Name, NR, Continent, Sunrise, Sunset, Latitude, Longitude, QualityTag) VALUES (@Call, @TS, @Band, @Freq, @QSXFreq, @Comment, @Spotter, @Bearing, @CountryPrefix, @Sect, @ZN, @Exchange1, @GridSquare, @MiscText, @Name, @NR, @Continent, @Sunrise, @Sunset, @Latitude, @Longitude, @QualityTag) SqliteParameters not show. The query used to be sub 1 ms, now it is 28 ms. Since I get quite a few calls per second, this is a big change. Anyone any ideas or tips? Thanks Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformatted output by sqlite3
Works as specified. The .width command sets the output width of a column to a certain number of characters; output that is shorter is padded on the right (or the left, if the width is negative); output that is too long is truncated. Use the printf() function to define the format of real numbers. -Ursprüngliche Nachricht- Von: Stephan Buchert [mailto:stephanb...@gmail.com] Gesendet: Montag, 19. Jänner 2015 11:53 An: sqlite-users@sqlite.org Betreff: [sqlite] Malformatted output by sqlite3 (Prompt erased for easier paste and copy): CREATE TABLE satpos(msec INTEGER, latitude REAL, longitude REAL); INSERT INTO satpos VALUES (86386217,-0.0318895369716216,-167.689719869132); INSERT INTO satpos VALUES (86386716,-2.93238037697483e-06,-167.690497310632); INSERT INTO satpos VALUES (86387217,0.0319616241531195,-167.69127664905); SELECT * FROM satpos; 86386217|-0.0318895369716216|-167.689719869132 86386716|-2.93238037697483e-06|-167.690497310632 86387217|0.0319616241531195|-167.69127664905 So far, so good, but: .mode column .width -8 -7 -8 SELECT * FROM satpos; 86386217 -0.0318 -167.689 86386716 -2.9323 -167.690 86387217 0.03196 -167.691 In the 2nd row, 2nd column -0. or -2.9e-6 would make me happy. But -2.9323 definitely messes up my stuff. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inserting/removing large number of rows with index
It depends in how you define update the index. If you mean write to disk then this happens once, at the end of the transaction (the exact process differs depending on the journal mode). If you mean change the index structure in memory then (as already noted) the changes will happen for each record touched. For large batch updates, it may be faster to drop the index before performing the changes and recreate it afterwards. -Ursprüngliche Nachricht- Von: Baruch Burstein [mailto:bmburst...@gmail.com] Gesendet: Donnerstag, 15. Jänner 2015 22:19 An: General Discussion of SQLite Database Betreff: [sqlite] Inserting/removing large number of rows with index Hi, If I have a table with an index, and INSERT or DELETE a large number of rows in one statement, does sqlite stop to update the index for each record, or is it smart enough to update the index just once for all the changed records? -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database is locked
Use the following code snippet (add error checking, set v_file to the full path
name of your SQLite db file) to check.
It attempts to take the same locks as SQLite would, but prints the pid of the
blocking process. It also prints the journal mode.
#include sys/types.h
#include sys/stat.h
#ifdef AIX64
#include /usr/include/sys/flock.h
#endif
#include unistd.h
#include fcntl.h
#include stdio.h
#include sqliteInt.h
static const char *g_mode[] = {
[0/0], [del], [WAL], [?/?]
};
charv_buff[256];
int v_mode;
struct flockv_pending; // = { F_WRLCK, SEEK_SET, PENDING_BYTE ,
1, 0};
struct flockv_reserved; // = { F_WRLCK, SEEK_SET, RESERVED_BYTE ,
1, 0};
struct flockv_shared;// = { F_WRLCK, SEEK_SET, SHARED_FIRST ,
SHARED_SIZE, 0};
int v_fd= -1;
int v_ret = -1;
memset( v_pending, 0, sizeof( v_pending));
v_pending.l_type= F_WRLCK;
v_pending.l_whence = SEEK_SET;
v_pending.l_start = PENDING_BYTE;
v_pending.l_len = 1;
v_pending.l_pid = 0;
memset( v_reserved, 0, sizeof( v_reserved));
v_reserved.l_type = F_WRLCK;
v_reserved.l_whence = SEEK_SET;
v_reserved.l_start = RESERVED_BYTE;
v_reserved.l_len= 1;
v_reserved.l_pid= 0;
memset( v_shared, 0, sizeof( v_shared));
v_shared.l_type = F_WRLCK;
v_shared.l_whence = SEEK_SET;
v_shared.l_start= SHARED_FIRST;
v_shared.l_len = SHARED_SIZE;
v_shared.l_pid = 0;
/* open the file readonly */
v_fd = open(v_file,O_RDONLY);
// read the file header
read(v_fd, v_buff, 100);
// interpret the bytes
switch (v_buff[18]) {
case 0: v_mode = (v_buff[18] == v_buff[19]) ? 0 : 3; break; // empty
case 1: v_mode = (v_buff[18] == v_buff[19]) ? 1 : 3; break; //
journal_mode=delete
case 2: v_mode = (v_buff[18] == v_buff[19]) ? 2 : 3; break; //
journal_mode=wal
default: v_mode = 3; break; // invalid
};
/* check for a PENDING lock */
fcntl(v_fd,F_GETLK,v_pending);
/* check for a RESERVED lock */
fcntl(v_fd,F_GETLK,v_reserved);
/* check for a SHARED/EXCLUSIVE lock */
fcntl(v_fd,F_GETLK,v_shared);
/* print in ascending restrictivity */
if (v_pending.l_type == F_RDLCK)
printf(%s File:%s, Process %d PENDING (SHARED)\n ,g_mode[v_mode]
,v_file, (v_ret = v_pending .l_pid));
if (v_shared .l_type == F_RDLCK)
printf(%s File:%s, Process %d SHARED\n ,g_mode[v_mode]
,v_file, (v_ret = v_shared .l_pid));
switch (v_reserved.l_type) {
case F_WRLCK:
case F_RDLCK:
printf(%s File:%s, Process %d RESERVED\n ,g_mode[v_mode]
,v_file, (v_ret = v_reserved.l_pid));
break;
default: break;
}
if (v_pending.l_type == F_WRLCK)
printf(%s File: %s,Process %d PENDING (EXCLUSIVE)\n,g_mode[v_mode]
,v_file, (v_ret = v_pending .l_pid));
if (v_shared .l_type == F_WRLCK)
printf(%s File %s, Process %d EXCLUSIVE\n ,g_mode[v_mode]
,v_file, (v_ret = v_shared .l_pid));
if (v_ret == -1)
printf(%s File:%s, none\n,g_mode[v_mode]
,v_file);
-Ursprüngliche Nachricht-
Von: Roman Fleysher [mailto:roman.fleys...@einstein.yu.edu]
Gesendet: Mittwoch, 14. Jänner 2015 18:54
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] database is locked
SQLite shell version 3.7.2
on Linux 2.6.18
NTFS
From: sqlite-users-boun...@sqlite.orgmailto:sqlite-users-boun...@sqlite.org
[sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 12:50 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked
On 1/14/15, Roman Fleysher
roman.fleys...@einstein.yu.edumailto:roman.fleys...@einstein.yu.edu wrote:
Dear SQLiters,
There has been a lot of discussion, I remember, on this subject by others.
Please forgive me for asking this for a millionth time.
I somehow got my database in a locked state. I updated a table
yesterday and I am rather sure that no one on our multi-user system is
updating it now.
The time stamp on the file is from yesterday, showing correct time. I
need to update a table (I use shell) and I get database is locked.
Is there a way to figure out what is happening? Clear the lock?
What operating system and filesystem are you using? And are you specifying an
alternative VFS for SQLite or using the default?
Thank you for your help,
Roman
___
sqlite-users mailing list
sqlite-users@sqlite.orgmailto:sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
D. Richard Hipp
d...@sqlite.orgmailto:d...@sqlite.org
Re: [sqlite] help with query
Step 1: count the occurrences: SELECT data1,count() AS count FROM table GROUP BY data1; Step 2: get the rows with a count above the limit SELECT data1,count() AS count FROM table GROUP BY data1 HAVING count = 3; Step 3: get the keys from the rows SELECT data1 FROM (SELECT data1,count() AS count FROM table GROUP BY data1 HAVING count = 3); Step 4: retrieve the original rows SELECT * FROM table WHERE data1 IN (SELECT data1 FROM (SELECT data1,count() AS count FROM table GROUP BY data1 HAVING count = 3)); -Ursprüngliche Nachricht- Von: Hajo Locke [mailto:hajo.lo...@gmx.de] Gesendet: Dienstag, 13. Jänner 2015 08:30 An: sqlite-users@sqlite.org Betreff: [sqlite] help with query Hello list, i have a problem finding right query, hope you can help me. I have a sample table like this: http://pastebin.com/8qyBzdhH I want to select all lines where at least 3 lines in column data1 have same value. My expected result-set ist this: http://pastebin.com/UcaXLVx9 How can this be done in one query? I tested with something like this: SELECT *,count(*) as mycount FROM `table` group by data1 having mycount=3; But this results in summarized output, but i need every single line. I would need something like: select * from `table` where count(data1)3; But this is not allowed. Do you have any hints for me? Thanks, Hajo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] xBestIndex/XFilter and virtual tables
SQLite is asking your virtual table questions: 1) what is the cost of a full table scan? (your answer: 1000) 2) what is the cost of a lookup by id? (your answer: 1) 3) when performing a key lookup, do you promise to return only rows matching the key? (your answer in returned in the omit field) Quesition 1 establishes the base line cost of a full table scan. SQLite knows it can answer any query for a constant set of constraints by incurring this cost Question 2 establishes the cost of retrieving a single row. The answer 1 is probably somewhat overoptimistic in terms of real cost which is O(log n). SQLite now needs to determine if scanning once (1000) is more or less costly than performing n key lookups. This cost depends on the answer to Question 3. If you promise only to return rows matching the given id, the cost is (n * 1) = SQLite will choose to perform n calls to xFilter, once for each element of the key set. If you cannot promise to return only the matching rows, how many rows will you return? The conservative estimate is no more than there are rows in the table (=1000) = SQLite must choose to scan the table once. -Ursprüngliche Nachricht- Von: Venkat Murty [mailto:vmu...@fiberlink.com] Gesendet: Sonntag, 11. Jänner 2015 23:52 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] xBestIndex/XFilter and virtual tables Estimated cost and row for each call to xBestIndex (sqlite3_index_info is filled with the below values for each call) a) When there is one non-usable constraint idxNum = 0 estimatedCost = 1000.00 estimatedRows = 1000 b) when there is one usable constraint idxNum = 1 estimatedCost = 1.00 estimatedRows = 1 xFilter is called with idxNum = 0 and argc = 0 If aConstraintUsage[0].omit = 1 then xFilter is called multiple times with idxNum = 1. If aConstraintUsage[0].omit = 0 then xFilter is called with idxNum = 0 and argc = 0 Fiberlink Disclaimer: The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] xBestIndex/XFilter and virtual tables
BTW: SQLite will also ask your virtual table about GROUP BY/ORDER BY capabilities if either of the clauses is included in the SELECT. -Ursprüngliche Nachricht- Von: Hick Gunter [mailto:h...@scigames.at] Gesendet: Montag, 12. Jänner 2015 08:02 An: 'General Discussion of SQLite Database' Betreff: Re: [sqlite] xBestIndex/XFilter and virtual tables SQLite is asking your virtual table questions: 1) what is the cost of a full table scan? (your answer: 1000) 2) what is the cost of a lookup by id? (your answer: 1) 3) when performing a key lookup, do you promise to return only rows matching the key? (your answer in returned in the omit field) Quesition 1 establishes the base line cost of a full table scan. SQLite knows it can answer any query for a constant set of constraints by incurring this cost Question 2 establishes the cost of retrieving a single row. The answer 1 is probably somewhat overoptimistic in terms of real cost which is O(log n). SQLite now needs to determine if scanning once (1000) is more or less costly than performing n key lookups. This cost depends on the answer to Question 3. If you promise only to return rows matching the given id, the cost is (n * 1) = SQLite will choose to perform n calls to xFilter, once for each element of the key set. If you cannot promise to return only the matching rows, how many rows will you return? The conservative estimate is no more than there are rows in the table (=1000) = SQLite must choose to scan the table once. -Ursprüngliche Nachricht- Von: Venkat Murty [mailto:vmu...@fiberlink.com] Gesendet: Sonntag, 11. Jänner 2015 23:52 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] xBestIndex/XFilter and virtual tables Estimated cost and row for each call to xBestIndex (sqlite3_index_info is filled with the below values for each call) a) When there is one non-usable constraint idxNum = 0 estimatedCost = 1000.00 estimatedRows = 1000 b) when there is one usable constraint idxNum = 1 estimatedCost = 1.00 estimatedRows = 1 xFilter is called with idxNum = 0 and argc = 0 If aConstraintUsage[0].omit = 1 then xFilter is called multiple times with idxNum = 1. If aConstraintUsage[0].omit = 0 then xFilter is called with idxNum = 0 and argc = 0 Fiberlink Disclaimer: The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector
Maybe you mean (assuming there is not more than one record in t2 for a given SSID-CELLID-SECTOR) UPDATE t1 ... -Ursprüngliche Nachricht- Von: MikeSnow [mailto:michael.sab...@gmail.com] Gesendet: Freitag, 09. Jänner 2015 00:12 An: sqlite-users@sqlite.org Betreff: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector I was wondering if anyone could let me know where I am going wrong. I am getting the error... Error while executing query: no such column: t1.*B.Switch-Tower-Sector but, the column, t1.[*B.Switch-Tower-Sector], does exist. I get results when I do select[*B.Switch-Tower-Sector] from t1; but an error when I do the UPDATE statement... UPDATE t2 SET [*B.ANT_ORIENTATION] = (SELECT t2.ANT_ORIENTATION FROM t2 WHERE t2.[*SSID-CELLID-SECTOR] = t1.[*B.Switch-Tower-Sector]); Any help would be much appreciated. thanks mike -- View this message in context: http://sqlite.1065341.n5.nabble.com/Error-while-executing-query-no-such-column-t1-B-Switch-Tower-Sector-tp79905.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 ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn
00 NULL asql insert into T values (1,2,1); rows 1 asql select count(),a from T group by a having b 0; coun a - 1 1 asql insert into T values (2,0,3); rows 1 asql select count(),a from T group by a having b 0; coun a - 1 1 asql insert into T values (2,1,3); rows 1 asql select count(),a from T group by a having b 0; coun a - 1 1 2 2 asql insert into T values (2,0,4); rows 1 asql select count(),a from T group by a having b 0; coun a - 1 1 -Ursprüngliche Nachricht- Von: James K. Lowden [mailto:jklow...@schemamania.org] Gesendet: Donnerstag, 08. Jänner 2015 03:56 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn On Mon, 5 Jan 2015 06:39:42 + Hick Gunter h...@scigames.atmailto:h...@scigames.at wrote: This is completely legal and well defined. HAVING is applied to the RESULT set of a SELECT. I beg to differ. It's both invalid SQL and (therefore) undefined. Furthermore, it's illogical. Consider: create table T (a int, b int, c int); insert into T values (1, 2, 1); select count(*), a from T group by a having b 0; Surely select count(*), a from T produces 1 row, count(*) a - 1 1 It *outputs* 1 row with 2 fields but what does having b 0 mean if HAVING is applied to the RESULT set of a SELECT? There is no B! Internally there is, because *you explicitly asked for it* If ad argumentum we say B refers to the B in the table, the question remains: how to interpret the having clause? Is it TRUE If 1. there exists a row for a given value of A for which B 0? 2. all rows for a given A have B 0? It so happens (per http://sqlfiddle.com/#!5/34e8d/1) that sqlite accepts the query and uses interpretation #2. But that is arbitrary: HAVING is not a FORALL or EXISTS quantifier. It has a single, clunky job: to test aggregates. #3: a randomly selected b from the subset of records having a given a If the single aggregate function is MIN or MAX, the returned value for b will be from one of the records where the minimum or maximum respectively occurs. Most SQL DBMSs reject the query outright. As well they should, because it is nonsensical. --jkl ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna,Austria Tel: +43 1 80100 - 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn
This is completely legal and well defined.
HAVING is applied to the RESULT set of a SELECT.
The select asks to count the distinct kontrola in each group of kvadrat and
datum, the HAVING clause specifies returning only those records with pocet 1.
If there were no pocet column in table b, this would return only the non-empty
groups, which is what the OP intended.
As there is a pocet column in table b, the HAVING clause refers to the original
b.pocet which contains a (from the POV of the programmer) randomly selected
from the group rows' value. This is a documented SQLite feature.
SELECT kvadrat, datum, counted_pocet as pocet from (SELECT kvadrat, datum
,pocet, count(distinct kontrola) as counted_pocet from b group by kvadrat,
datum HAVING pocet 1);
-Ursprüngliche Nachricht-
Von: James K. Lowden [mailto:jklow...@schemamania.org]
Gesendet: Samstag, 03. Jänner 2015 00:45
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] New column in select will not mask column of the same
name in having clause and sqlite won't warn
On Sun, 28 Dec 2014 17:46:08 +0100
Tomas Telensky tomas.telen...@gmail.com wrote:
select kvadrat, datum, count(distinct kontrola) as pocet from b group
by kvadrat, datum having pocet 1
The problem was that pocet was actually a column in table b and I
didn't notice, and the having clause was using the table column
instead of the newly derived column specified in select clause.
So far so good, but sqlite should at least issue any warning, right?
I would say it should raise an error. The HAVING clause should include at
least one aggregate. Comparing a column to a constant is the job of WHERE.
The accepted syntax is ambiguous. Was the HAVING applied before or after the
aggregation. IOW, did you get
1. the count for each {kvadrat, datum} pair for which pocet 1, or 2. the
count of {kvadrat, datum} pairs that have at least one pocet 1
?
In the first case the counts would be smaller by the number of rows for which
pocet = 1. In the second case results rows would be eliminated for pairs that
contain only rows for which pocet = 1.
--jkl
___
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] Suggestion for syntax enhancement for virtual tables
Temporary virtual tables sounds like an interesting concept. Does the xDestroy() function get called on such a beast (as opposed to xDisconnect() when the connection is closed)? Should that function delete the backing store (even if a non-temporary virtual table is still connected)? -Ursprüngliche Nachricht- Von: Baruch Burstein [mailto:bmburst...@gmail.com] Gesendet: Donnerstag, 01. Jänner 2015 08:38 An: General Discussion of SQLite Database Betreff: [sqlite] Suggestion for syntax enhancement for virtual tables For creating temporary virtual tables, currently you need to do: CREATE VIRTUAL TABLE temp.t ... Can this syntax be made to work too (similar to creating regular tables)? CREATE VIRTUAL TEMP TABLE t ... or CREATE TEMP VIRTUAL TABLE t ... -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ 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] SQL newbie, how to implement a delete correctly.
Select * from child01 where p01_id in (select rowid from parent01 where ...); Or Select c.* from parent01 p join child01 c on p.rowid=c.p01_id where ...; -Ursprüngliche Nachricht- Von: Jonathan Leslie [mailto:jlesli...@yahoo.com] Gesendet: Dienstag, 30. Dezember 2014 15:58 An: sqlite-users@sqlite.org Betreff: [sqlite] SQL newbie, how to implement a delete correctly. I have a database schema with several tables, and it uses foreign key relationships. I'm trying to figure out a good implementation for deleting a record. As we know simply removing a record that is used as a foreign key in another table have serious implications. The first thing I'd like is an sql script that displays the effected records of a source record. For example If I select a single record from a table I want to search the entire database for references to that record. how ca I do that? Here is the sample DB I'm using: a parent table has no foreign constraints, a child table has foreign constraints, but is not inherited by any other table, and a parentchild table has both foreign constraints, and is inherited by some other table. deleting a record from the child01 record is not a problem, as it is not referenced in any other table. 1) deleting a record from parent01 is not as straightforward. parent01 records are used in the table child01. I would like a script that I could run that would show what records that WOULD be effected should I delete a particular record of parent01 2) I imagine there will be some follow up questions, but lets see where question (1) gets me and if I can figure out stuff from there. TIA, Jleslie48 -- start of database ---CREATE TABLE parent01 ( p01_id integer NOT NULL PRIMARY KEY, description text, low_lim real, upper_lim real, enable_atm boolean, pc01_id integer); CREATE TABLE child01 ( scene_gen_cfg_id integer NOT NULL PRIMARY KEY, description text, target_model_file char(64), p01_id integer, p04_id integer, po2_id integer, pc01_id integer, FLITES_cfg_file text, /* Foreign keys */ CONSTRAINT p02_id_fk FOREIGN KEY (po2_id) REFERENCES parent02(po2_id), FOREIGN KEY (p04_id) REFERENCES parent04(p04_id), FOREIGN KEY (p01_id) REFERENCES parent01(p01_id), CONSTRAINT pc01_id_fk FOREIGN KEY (pc01_id) REFERENCES parentchild01(pc01_id)); CREATE TABLE parent02 ( po2_id integer NOT NULL PRIMARY KEY, description text, curve_shader_src text); CREATE TABLE parentchild01 ( pc01_id integer NOT NULL PRIMARY KEY, description text, p03_id integer, /* Foreign keys */ CONSTRAINT p03_id_fk FOREIGN KEY (p03_id) REFERENCES parent03_nodes(p03_id)); CREATE TABLE parent03_nodes ( p03_id integer NOT NULL PRIMARY KEY, description text, parent_node_id integer, name char(64), scale_x real, scale_y real, scale_z real); CREATE TABLE parent04 ( p04_id integer NOT NULL PRIMARY KEY, description text, vertical_fov real, horizontal_fov real); -- end of database --- ___ 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] SQL newbie, how to implement a delete correctly.
Maybe this will work: - declare the foreign keys as ON DELETE CASCADE - register an update hook function - BEGIN - DELETE FROM parent01 WHERE ... (storing the db, table and rowids OUTSIDE SQLite) - ROLLBACK - print whatever you want using the stored info -Ursprüngliche Nachricht- Von: Jonathan Leslie [mailto:j...@jonathanleslie.com] Gesendet: Dienstag, 30. Dezember 2014 16:53 An: General Discussion of SQLite Database Betreff: Re: [sqlite] SQL newbie, how to implement a delete correctly. Sorry, I wasn't clear. what if there are other child tables, say child02- childxx, and you don't know the names of the table, you want to search the entire database? I want a report something like this: for parent01.p01_id == 123, the following records contain references to p01_id == 123: child01 table:child01.scene_gen_cfg_id== 222child01.scene_gen_cfg_id== 432... child02 table:child02.xxx_id = 2432... etc. the idea is without knowing all the names of all the tables, find all references to parent01.p01_id (where value is xxx) From: Hick Gunter h...@scigames.at To: 'Jonathan Leslie' j...@jonathanleslie.com; 'General Discussion of SQLite Database' sqlite-users@sqlite.org Sent: Tuesday, December 30, 2014 10:35 AM Subject: Re: [sqlite] SQL newbie, how to implement a delete correctly. Select * from child01 where p01_id in (select rowid from parent01 where ...); Or Select c.* from parent01 p join child01 c on p.rowid=c.p01_id where ...; -Ursprüngliche Nachricht- Von: Jonathan Leslie [mailto:jlesli...@yahoo.com] Gesendet: Dienstag, 30. Dezember 2014 15:58 An: sqlite-users@sqlite.org Betreff: [sqlite] SQL newbie, how to implement a delete correctly. I have a database schema with several tables, and it uses foreign key relationships. I'm trying to figure out a good implementation for deleting a record. As we know simply removing a record that is used as a foreign key in another table have serious implications. The first thing I'd like is an sql script that displays the effected records of a source record. For example If I select a single record from a table I want to search the entire database for references to that record. how ca I do that? Here is the sample DB I'm using: a parent table has no foreign constraints, a child table has foreign constraints, but is not inherited by any other table, and a parentchild table has both foreign constraints, and is inherited by some other table. deleting a record from the child01 record is not a problem, as it is not referenced in any other table. 1) deleting a record from parent01 is not as straightforward. parent01 records are used in the table child01. I would like a script that I could run that would show what records that WOULD be effected should I delete a particular record of parent01 2) I imagine there will be some follow up questions, but lets see where question (1) gets me and if I can figure out stuff from there. TIA, Jleslie48 -- start of database ---CREATE TABLE parent01 ( p01_id integer NOT NULL PRIMARY KEY, description text, low_lim real, upper_lim real, enable_atm boolean, pc01_id integer); CREATE TABLE child01 ( scene_gen_cfg_id integer NOT NULL PRIMARY KEY, description text, target_model_file char(64), p01_id integer, p04_id integer, po2_id integer, pc01_id integer, FLITES_cfg_file text, /* Foreign keys */ CONSTRAINT p02_id_fk FOREIGN KEY (po2_id) REFERENCES parent02(po2_id), FOREIGN KEY (p04_id) REFERENCES parent04(p04_id), FOREIGN KEY (p01_id) REFERENCES parent01(p01_id), CONSTRAINT pc01_id_fk FOREIGN KEY (pc01_id) REFERENCES parentchild01(pc01_id)); CREATE TABLE parent02 ( po2_id integer NOT NULL PRIMARY KEY, description text, curve_shader_src text); CREATE TABLE parentchild01 ( pc01_id integer NOT NULL PRIMARY KEY, description text, p03_id integer, /* Foreign keys */ CONSTRAINT p03_id_fk FOREIGN KEY (p03_id) REFERENCES parent03_nodes(p03_id)); CREATE TABLE parent03_nodes ( p03_id integer NOT NULL PRIMARY KEY, description text, parent_node_id integer, name char(64), scale_x real, scale_y real, scale_z real); CREATE TABLE parent04 ( p04_id integer NOT NULL PRIMARY KEY, description text, vertical_fov real, horizontal_fov real); -- end of database --- ___ 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
Re: [sqlite] Partial index to find maximum
create the primary key index ordered properly CREATE TABLE t (..., PRIMARY KEY ( a ASC, b DESC)...); SELECT b FROM t WHERE a = ? LIMIT 1; If you insist on using a partial index for this (for example if each a has a lot of b entries) you could add a field b_is_max and keep it current using triggers. CREATE INDEX t_max ON t (a,b) WHERE b_is_max; SELECT b FROM t WHERE a=? AND b_is_max; CREATE TRIGGER t_ins AFTER INSERT ON t WHEN new.b (SELECT b FROM t WHERE a = new.a AND b_is_max) BEGIN UPDATE t SET b_is_max = 0 WHERE a = new.a AND b_is_max; UPDATE t SET b_is_max = 1 WHERE a = new.a AND b = new.b; END; CREATE TRIGGER t_del BEFORE DELETE ON t WHEN old. b_is_max) BEGIN UPDATE t SET b_is_max = 0 WHERE a = old.a AND b = old.b); UPDATE t SET b_is_max = 1 WHERE a = old.a AND b = (SELECT max(b) FROM t WHERE a = old.a and b old.b); END; CREATE TRIGGER t_upd_new AFTER UPDATE ON t WHEN new.b (SELECT b FROM t WHERE a = new.a AND b_is_max) BEGIN UPDATE t SET b_is_max = 0 WHERE a = new.a AND b_is_max; UPDATE t SET b_is_max = 1 WHERE a = new.a AND b = new.b; END; CREATE TRIGGER t_upd_old BEFORE UPDATE ON t WHEN old.b_is_max) BEGIN UPDATE t SET b_is_max = 0 WHERE a = old.a AND b = old.b); UPDATE t SET b_is_max = 1 WHERE a = old.a AND b =(SELECT max(b) FROM t WHERE a = old.a and b old.b); END; Triggers shown are untested. -Ursprüngliche Nachricht- Von: Baruch Burstein [mailto:bmburst...@gmail.com] Gesendet: Montag, 29. Dezember 2014 09:34 An: General Discussion of SQLite Database Betreff: [sqlite] Partial index to find maximum Hi, I have a table with a 2 column PK, say 'a' and 'b'. I need to find, for a given value of 'a', the highest matching 'b'. The query itself it simple: SELECT max(b) FROM t WHERE a=:whatever To speed this up, I would add an index on 'a'. Now, the question is is there some way to tell the index that I am only interested in the maximum value of b? For example, for the following table: a|b 1|1 1|2 2|2 2|3 I only need the index to contain the rows (1,2) and (2,3). The docs for partial indexes say that they can't contain functions (like max()). Any suggestions? -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ 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] Row filtering prior to aggregate function execution
NO. WHERE constraints are applied on the input set (i.e. the rows which evaluate to security() = FALSE are excluded from the aggregation) HAVING constraints are applied on the output set (which is too late, and with randomly chosen* values) * For a non-aggregated and ungrouped field, the value returned will be from a randomly chosen record. If there is a single MIN or MAX aggregate, from a randomly chosen record whose field value for the MIN or MAX field matches that value. -Ursprüngliche Nachricht- Von: Staffan Tylen [mailto:staffan.ty...@gmail.com] Gesendet: Freitag, 19. Dezember 2014 11:28 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Row filtering prior to aggregate function execution Would this work? SELECT SUM(...),COUNT(...), ... FROM ... WHERE ... GROUP BY ... HAVING security(...) ... Staffan On Fri, Dec 19, 2014 at 7:47 AM, Hick Gunter h...@scigames.at wrote: SELECT ...,sum(...),count() FROM ... WHERE security(...) ... With a user defined function security(). -Ursprüngliche Nachricht- Von: Roland Martin [mailto:rolandsmar...@gmail.com] Gesendet: Donnerstag, 18. Dezember 2014 17:09 An: sqlite-users@sqlite.org Betreff: [sqlite] Row filtering prior to aggregate function execution I have a need to filter the result set before aggregate functions are performed. For example, a query with a group by clause produces a result set of 5 rows with count() and sum(). For each of the 5 rows I need the value of a single column to serve as input into a security check. If the security checks passes the row is part of the final result set. If the security check fails the row is discarded and is not part of the final result set. The final result set in the example could be 0-5 rows and I would like the result values of count() and sum() to be accurate. Unfortunately I cannot query the security system to find out all valid values and add these values to the where clause. I have to give a value to the security system and it returns pass or fail. Based on the above, is there a callback or other mechanism I can use to participate in the result set generation? Thanks for the help - Roland Martin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ 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 ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Row filtering prior to aggregate function execution
SELECT ...,sum(...),count() FROM ... WHERE security(...) ... With a user defined function security(). -Ursprüngliche Nachricht- Von: Roland Martin [mailto:rolandsmar...@gmail.com] Gesendet: Donnerstag, 18. Dezember 2014 17:09 An: sqlite-users@sqlite.org Betreff: [sqlite] Row filtering prior to aggregate function execution I have a need to filter the result set before aggregate functions are performed. For example, a query with a group by clause produces a result set of 5 rows with count() and sum(). For each of the 5 rows I need the value of a single column to serve as input into a security check. If the security checks passes the row is part of the final result set. If the security check fails the row is discarded and is not part of the final result set. The final result set in the example could be 0-5 rows and I would like the result values of count() and sum() to be accurate. Unfortunately I cannot query the security system to find out all valid values and add these values to the where clause. I have to give a value to the security system and it returns pass or fail. Based on the above, is there a callback or other mechanism I can use to participate in the result set generation? Thanks for the help - Roland Martin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] '.timer on' in the shell tool
Units are CPU Seconds. user time is spent within user code, i.e. SQLite, sys time is spent within system calls, i.e. reading/writing files. The balance between the times depends on various parameters, including the state of the disc cache and the complexity of your INSERT...SELECT statements. Performing complex calculations to arrive at field values will tend to increase user time. Performing complex joins will tend to increase sys time as more data needs to be read from file. It is hard to tell without the schemata and the SQL involved. Look at the output from EXPLAIN and EXPLAIN QUERY PLAN to get an idea of the work involved. -Ursprüngliche Nachricht- Von: Simon Slavin [mailto:slav...@bigfraud.org] Gesendet: Montag, 15. Dezember 2014 10:12 An: General Discussion of SQLite Database Betreff: [sqlite] '.timer on' in the shell tool Okay. I used '.timer on' in the shell tool. SQLite 3.7.13, if it matters. Here are two sample lines I got in response to different INSERT ... SELECT commands: CPU Time: user 880.710398 sys 353.260288 CPU Time: user 5073.001124 sys 11609.266484 The two commands were issued one after another on a computer which was otherwise idle. Question 1: What are the units ? Question 2: I would have expected consistency in that user time was always greater than system time. Or perhaps the other way around. Why is a different one greater for the two examples ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Planner for Virtual Tables: link table evaluation transitive property of constraints not used
I would concur in that SQLite is asking which subset of the given constraints yields the most efficient access. The possible query plans are 1) A() - B(ID) - C(LINKID) 2) C() - B(LINKID) - A(ID) 3) B() - A(ID) + C(LINKID) or B() - C(LINKID) + A(ID) 4) A() - C() - B(ID,LINKID) or C() - A() - B(ID,LINKID) Assuming unique keys in A and C and cardinalities of a, b and c we have estimated costs (in # of records retrieved): 1) a + a*b/a + a*b/a*1 = a + 2b 2) c + c*b/c + c*b/c*1 = c + 2b 3) b + b*1 + b*1 = 3b 4) a + a*c + a*c*b/a/c = a + a*c + b (resp. c + a*c + b) So which is the smallest cost? We know that b = a*c, which makes query plan 4 at least as expensive as plans 1 or 2 respectively. Choosing between plans 1 and 2 means starting with the smaller of the two tables (assume a c). So how do plans 1 and 3 compare? Plan 3 is better only for very sparse link tables where b a c is true. -Ursprüngliche Nachricht- Von: Dan Kennedy [mailto:danielk1...@gmail.com] Gesendet: Montag, 15. Dezember 2014 12:24 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Query Planner for Virtual Tables: link table evaluation transitive property of constraints not used On 12/12/2014 09:22 PM, Josef Kučera wrote: Hello, I am trying to use SQLite's marvellous Virtual Table mechanism as a SQL layer for querying an in memory storage. This works good, but I have a problem with more complex queries. When querying a real SQLite database it correctly moves the constant conditions across joined tables to optimize the execution plan (I think this was implemented in the 3.7.17 release). Unfortunately for virtual tables this does not seem to be supported. I can overcome this limitation by manually tuning the SQL, but it will help if the query planner can do this automatically. The major problem I have is with link table evaluation. Imagine a SQL like select * from A join B on A.ID=B.ID join C on C.ID=B.LINKID. The current implementation evaluates cost of B only as B (ID, LINKID) causing the execution to perform a full scan on either A or C. This seems to be caused by the implementation of whereLoopAddVirtual() function. I think it should evaluate cost for terms separated by tables in the right term as well, e.g. for the mentioned SQL, table B, it should try B(), B(ID), B(LINKID), B(ID, LINKID) instead of only B() and B(ID, LINKID). What should I do? You want this (or the same thing with the roles of A and C reversed): * a full-scan on A, * a lookup on B by (b.id=?) * a lookup on C by (c.id=?) correct? It's tricky. As you say, xBestIndex() will currently be invoked twice - once with no constraints usable and once with both b.id=? and b.linkid=? usable. I guess the reason it is not invoked in the other ways you suggest is that that strategy might conceivably require a huge number of xBestIndex() calls if there were more than a few other tables in the join. You could change the query so that only one of the constraints is visible to the virtual table implementation. Say: select * from A join B on A.ID=B.ID join C on C.ID=+B.LINKID Or rework the virtual table code so that it knows only to use one of b.id=? or b.linkid=? at a time. If the xBestIndex only uses one of the constraints, the planner should do the right thing. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] replace many rows with one
Both, I guess Insert into ... select a,b,sum(theCount) group by a,b; -Ursprüngliche Nachricht- Von: Simon Slavin [mailto:slav...@bigfraud.org] Gesendet: Mittwoch, 10. Dezember 2014 12:39 An: General Discussion of SQLite Database Betreff: [sqlite] replace many rows with one Dear folks, A little SQL question for you. The database file concerned is purely for data manipulation at the moment. I can do anything I like to it, even at the schema level, without inconveniencing anyone. I have a TABLE with about 300 million (sic.) entries in it, as follows: CREATE TABLE s2 (a TEXT, b TEXT, theCount INTEGER) There are numerous cases where two or more rows (up to a few thousand in some cases) have the same values for a and b. I would like to merge those rows into one row with a 'theCount' which is the total of all the merged rows. Presumably I do something like CREATE TABLE s2merged (a TEXT, b TEXT, theCount INTEGER) INSERT INTO s2merged SELECT DISTINCT ... FROM s2 and there'll be a TOTAL() in there somewhere. Or is it GROUP BY ? I can't seem to get the right phrasing. Also, given that this is the last operation I'll be doing on table s2, will it speed things up to create an index on s2 (a,b), or will the SELECT just spend the same time making its own temporary index ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database is locked for SQLITE_BUSY
I think the error messages are distinct enough as is.
SQLITE_BUSY means that some connection is BUSY with a write transaction and has
locked the database file; presumably, it will be possible to write to the
database when the current writer has finished, just not now or within the
specified busy timeout.
SQLITE_LOCKED otoh means that the calling application is in error and has
specified two or more transactions whose table access modes are incompatible
and whose table access orders differ. This situation is resolvable only if at
least one involved transaction is rolled back.
const char *sqlite3ErrStr(int rc){
static const char* const aMsg[] = {
...
/* SQLITE_BUSY*/ database is locked,
/* SQLITE_LOCKED */ database table is locked,
...
};
-Ursprüngliche Nachricht-
Von: Jonathan Moules [mailto:j.mou...@hrwallingford.com]
Gesendet: Mittwoch, 03. Dezember 2014 12:01
An: 'General Discussion of SQLite Database'
Betreff: [sqlite] database is locked for SQLITE_BUSY
Hi,
Just a quick request/suggestion.
Currently SQLITE_BUSY events return an error of Database is locked. Is it
possible to change this to Database is busy or something similar?
I ask because when someone then goes googling for SQLite database locked,
they'll end up thinking they're hitting the SQLITE_LOCKED event which as best I
can tell is very different to the SQLITE_BUSY event.
I'm sure I can't be the only person who has been tripped up by that one.
Thanks,
Jonathan
HR Wallingford and its subsidiaries uses faxes and emails for confidential and
legally privileged business communications. They do not of themselves create
legal commitments. Disclosure to parties other than addressees requires our
specific consent. We are not liable for unauthorised disclosures nor reliance
upon them.
If you have received this message in error please advise us immediately and
destroy all copies of it.
HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom Registered in
England No. 02562099
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Column name as a variable
SELECT table_name FROM sqlite_master; And then, in your programming language of choice, execute SELECT count() FROM table_name For each received table name. You cannot use a variable instead of a table name in SQL. -Ursprüngliche Nachricht- Von: Paul Sanderson [mailto:sandersonforens...@gmail.com] Gesendet: Montag, 17. November 2014 11:55 An: General Discussion of SQLite Database Betreff: [sqlite] Column name as a variable Is it possible to get a row count for each of the tables in a database using a SQL query. i.e. is there a way I could use each row in sqlite_master and use table_name to somehow do a select count(*) from sqlite.master.table_name Thanks Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR
I would agree with the suspicion that your data is changing shape i.e. the cardinality of index fields is becoming very different from what ANALYZE stored. As for bypassing the query planner/code generator you might want to contact Prakash Premkumar prakash.p...@gmail.com who is apparently quite determined to go this route. I think judicious use of the available hints (i.e. CROSS JOIN and USING) will get you further quicker. We almost exclusively use virtual tables here (for which ANALYZE is useless) and find CROSS JOIN to be our best friend in fixing queries that xBestIndex return values cannot coax into performing well. -Ursprüngliche Nachricht- Von: RP McMurphy [mailto:rpm0...@yahoo.com] Gesendet: Freitag, 14. November 2014 15:32 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR I am resending this message below from 3 days ago because it never made it to the list. RP PS: Messages seem to take a long time to go through the gmane system, at least half a day and sometimes more in my experience so far. On Tue, 11/11/14, RP McMurphy rpm0...@yahoo.com wrote: Subject: Re: Discrepancy with indexing and WHERE clause with AND/OR To: sqlite-users@sqlite.org Date: Tuesday, November 11, 2014, 6:31 PM If you can provide any examples where ANALYZE makes a query slower, I suspect the developer team would like to see them. After we run analyze and then let the process run for a while the DB contents change - and it can change quite considerably depending upon what is happening. I suspect that the analyze data gets stale, but I don't know how to track such things in sqlite. Anyhow we can't keep running analyze every few minutes because it takes a long time to run with our DB and it appears to block all other actions until it is done. A this point we are considering writing VDBE code directly and bypassing the parser. Has anyone else done this? Is it going to be a huge ugly can-of-worms if we do that? RP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite] Support for 23,10 Precision number format
Data types are 64bit integer (~18 decimal digits) and 64 Bit IEEE Float(11 bit exponent, 52 bit fraction), so no. Store the numbers as TEXT (human readable) or BLOB (e.g. 128Bit binary) and write user-defined functions to manipulate them. -Ursprüngliche Nachricht- Von: Dinesh Navsupe [mailto:dinesh.navs...@gmail.com] Gesendet: Donnerstag, 13. November 2014 13:23 An: sqlite-users@sqlite.org Betreff: [sqlite] [SQLite] Support for 23,10 Precision number format Hi, Does any of SQLite data Type support 23,10 precision format for Number? If yes, could you pleas help with right data type or approach to achieve this. If No, then is there something that can be added to SQLite and how quickly? Thanks, Dinesh Navsupe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite] Support for 23,10 Precision number format
IIRC there was a programmer working for a bank that managed to siphon off the sub-unit fractions that the interest calculating software generated (how much interest is owed for $1 at 0,25% p.a. for 2 days*) onto his own account and temporarily got rich quick. $1 * 0,25% = $25 (interest for 1 year) $25 * 2 / 360 = $0,1389 (interest for 2 days) This is split into 13 cents for the client and nearly 0,9 cents that the bank keeps -Ursprüngliche Nachricht- Von: Simon Slavin [mailto:slav...@bigfraud.org] Gesendet: Donnerstag, 13. November 2014 18:07 An: General Discussion of SQLite Database Betreff: Re: [sqlite] [SQLite] Support for 23,10 Precision number format On 13 Nov 2014, at 3:44pm, Dominique Devienne ddevie...@gmail.com wrote: On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin slav...@bigfraud.org wrote: 100,000,000,000,000,000,000 Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more 9,999,999,999,999.99 i.e. just under 10 trillion max, with 10 decimal digits accuracy, and not 100 million trillion. But he's using the field to store an amount of money in. So why ask for anything with ten places after the decimal point ? No genuine currency requires more than three places. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple threads reading different data
This is the I (Isolation) in ACID. WAL mode allows the writer to pretend that no transactions are outstanding and begin and even commit a write transaction. This change sits in the Wal file until all prior transactions have been completed and the change can be copied to the db. As long as your threads are within a transaction begun before the change happened, they will continue to see the old state. Maybe your threads have unfinished statements (missing call to sqlite3_reset() or sqlite3_finalize() functions), which cause the implicit transaction to be very much longer than expected. -Ursprüngliche Nachricht- Von: Daniel Polski [mailto:dan...@agelektronik.se] Gesendet: Dienstag, 11. November 2014 12:59 An: General Discussion of SQLite Database Betreff: [sqlite] Multiple threads reading different data I'm accessing a database from multiple threads, where each thread has a separate database connection. When some specific event happens, I want the threads to evaluate the situation and act accordingly. I have setup a temporary trigger which fires at the event and which in turn calls a user defined function updating a timestamp with the time of the event. The threads compare this timestamp with a local timstamp storing the last synchronization, and if they mismatch they fetch data from the database and process it. Now here's where it starts to seem a little tricky to me. It seems like the separate connections don't see the update made to the database from another thread if the fetching is done quickly after the update (as soon as the timestamps mismatch). I'm thinking that this maybe has to do with me using WAL mode, and that the update is not yet processed enough for the other threads to fetch the new data (so they still selects the old data), even though the trigger is set to fire after update. Is this maybe the case? If so, any suggestions how to solve it? Regards, Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does using e.g. LTRIM function remove collation?
I get the following results for the second select:
A
B
a (lowercase!!!)
Are you sure you ran the exact query stated?
-Ursprüngliche Nachricht-
Von: James K. Lowden [mailto:jklow...@schemamania.org]
Gesendet: Samstag, 08. November 2014 01:52
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Does using e.g. LTRIM function remove collation?
On Thu, 6 Nov 2014 17:02:26 -0500
Richard Hipp d...@sqlite.org wrote:
sqlite INSERT INTO test VALUES ('b'), ('A'), ('B'), ('a'); SELECT *
sqlite FROM test;
b
A
B
a
...
sqlite SELECT * FROM test WHERE LTRIM(col)'b';
A
B
A
...
Works as designed. See
https://www.sqlite.org/datatype3.html#collation and in particular the
three rules under section 6.1.
Aren't you overlooking the fact that the WHERE clause is changing not only
which rows are selected, but the *values* of those rows?
I don't understand how any WHERE clause can convert 'a' to 'A' in the database.
I'm unable to find any suggestion of such in the collation documentation.
--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite where clause tree
Your tree is wrong. I would expect that operator precedence is handled in the parser. The code generator will happily implement any tree, regardless of how insane it may be. -Ursprüngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Montag, 10. November 2014 10:40 An: General Discussion of SQLite Database Betreff: [sqlite] SQLite where clause tree The where clause in sqlite is encoded as a tree Let's say I have select statement like : SELECT * from employee where salary = 3+5*4+3; The tree which takes care of operator precedence is : = /\ salary+ / \ 3 3 / * /\ 5 4 If I am constructing this tree for where clause by myself, should i take the operator precedence in to account while constructing it or will sqlite take care of precedence,given any tree (i.e constructing it with out taking precedence into account) ? Kindly give me some pointers towards this tree construction Thanks a lot for your time. Regards Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does using e.g. LTRIM function remove collation?
I was just refuting the claim that the WHERE clause converts 'a' to 'A' in the
database. It doesn't. So either the change was caused by a copy-paste error
when creating the mail or by actually runing a (different) query that produces
it.
Anyway, in the WHERE clause, the arguments to '' are 'ltrim(col)' and 'b'.
Neither the function ltrim() nor the literal 'b' have a collation, so the
comparison proceeds using BINARY. Which is as documented.
-Ursprüngliche Nachricht-
Von: James K. Lowden [mailto:jklow...@schemamania.org]
Gesendet: Dienstag, 11. November 2014 00:05
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Does using e.g. LTRIM function remove collation?
On Mon, 10 Nov 2014 08:43:24 +
Hick Gunter h...@scigames.at wrote:
I get the following results for the second select:
A
B
a (lowercase!!!)
Are you sure you ran the exact query stated?
I didn't run it. Your mail showed 3 uppercase letters:
sqlite SELECT * FROM test WHERE LTRIM(col)'b';
A
B
A
--jkl
-Ursprüngliche Nachricht-
Von: James K. Lowden [mailto:jklow...@schemamania.org]
Gesendet: Samstag, 08. November 2014 01:52
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Does using e.g. LTRIM function remove collation?
On Thu, 6 Nov 2014 17:02:26 -0500
Richard Hipp d...@sqlite.org wrote:
sqlite INSERT INTO test VALUES ('b'), ('A'), ('B'), ('a'); SELECT
sqlite * FROM test;
b
A
B
a
...
sqlite SELECT * FROM test WHERE LTRIM(col)'b';
A
B
A
...
Works as designed. See
https://www.sqlite.org/datatype3.html#collation and in particular
the three rules under section 6.1.
Aren't you overlooking the fact that the WHERE clause is changing not
only which rows are selected, but the *values* of those rows?
I don't understand how any WHERE clause can convert 'a' to 'A' in the
database. I'm unable to find any suggestion of such in the collation
documentation.
--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This communication (including any attachments) is intended for the use
of the intended recipient(s) only and may contain information that is
confidential, privileged or legally protected. Any unauthorized use or
dissemination of this communication is strictly prohibited. If you
have received this communication in error, please immediately notify
the sender by return e-mail message and delete all copies of the
original communication. Thank you for your cooperation.
___
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
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite as a meta database
xCreate should specify CREATE TABLE x (label TEXT); which assumes that rowid
is the implicit index
xBestIndex needs to return:
1) cost = 1, index = 0 if a single constraint -1 (rowid) and operation
EQ is provided (assuming the record position can be calculated from the index)
2) cost = n/2, index = 0if a single constraint -1 (rowid) and
operation LE/GE/LT/GT is provided (as on average, half the records need to be
searched)
3) cost = ld(n), index = 1 if a single constraint 0 (label) and operation
EQ is provided (assuming the associative array is implemented via a binary tree)
4) cost = n/2, index = 1if a single constraint 0 (label) and
operation LE/GE/LT/GT/MATCH is provided (as on average, half the records need
to be searched)
5) cost = n, index = 0 otherwise (if more than one constraint is
supplied, pick your favorite)
If a constraint is used, the argv index needs to be set, so ist value can be
retrieved by xFilter.
Minimally you need to implement 1, 3 and 5. You can implement 2 and 4 later,
when the others work. I would suggest storing the operation in the idxString
variable.
xFilter needs to consider the cases:
1) set the current record to the one indexed by the passed index, set cursor to
expire when xNext is called
3) set the current record to the one located via the passed text, set cursor to
expire when xNext is called
5) set the current record to the first one, xNext needs to iterate until the
last record was returned
2) set the record range according to the operation and the passed index, xNext
needs to point to the record in the range in order
4) set the record range accoridng tot he operation and the passed text, xNext
needs to point to the record in the range in order
Note that all cases are specializations of set range and set iterator
function.
You can add processing of the ORDER BY clause later.
-Ursprüngliche Nachricht-
Von: Mike Beddo [mailto:mike.be...@dataventures.com]
Gesendet: Donnerstag, 06. November 2014 01:10
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] SQLite as a meta database
Hi,
I am intruding into this thread, because I face a similar issue. At our company
we have a proprietary database storing customer grocery transactions which
basically only a C programmer can get to. It is by no stretch of the
imagination a relational database. Basically, everything is an integer and we
rely on associative tables in our C codes to map integers back into human
readable labels for output purposes. For instance, a particular UPC
0 might map to integer 123, so we work with 123 in our codes, and
when we output the results we map 123 back to 0.
There is intense interest in providing a SQL front-end. I thought to start
with, I'd see if I could develop a virtual table for our various associative
tables. Doing this would help educate me before embarking on the bigger chore
of handling the database itself. Sounds simple enough, but I could use some
help/advice.
There are several associative tables that I would need a virtual table for:
items, stores, customers. I thought something like
CREATE VIRTUAL TABLE items USING aa_module('/path/to/database', 'items');
The arguments to aa_module (the thing I'm trying to write) give the path to the
database to open, and which associative map to create a virtual table for.
If we think of 'items' as a pointer to the items associative array, for
instance, then in our code we would use expressions like:
int inx = AA_inx(items, '0'); /* gives the index associated with
upc 0 */ char *upc = AA_lbl(items, 123); /* gives the upc string
associated with item inx 123 */
The table create string to use in xCreate/xConnect would be CREATE TABLE x(inx
INTEGER, upc VARCHAR(13));I think that in my xBestIndex function that if the
constraint involves equality then the cost is 1 lookup, but for any other type
of constraint the cost is the number of items in the AA map, since this thing
isn't sorted.
Could someone give me an idea of what a minimal xBestIndex/xFilter skeleton
might look like? I walked though ext/misc/amatch.c from the
www.sqlite.org/src/artifact tree, but I'm a little lost.
Thanks,
Mike Beddo
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Hick Gunter
Sent: Wednesday, November 05, 2014 6:16 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] SQLite as a meta database
Hi,
we have extensive experience with respect to the use of virtual tables in
SQLite. In fact, the only native SQLite tables we use are in a configuration
checking tool.
We have providers from in-memory indexed tables, CTree (r) files, Oracle
tables (read only), structured disk files, in-memory structures, binary
records, etc.
The trick is to be able to formulate your queries solely via comparison
operators. This type
Re: [sqlite] SQLite as a meta database
Hi, we have extensive experience with respect to the use of virtual tables in SQLite. In fact, the only native SQLite tables we use are in a configuration checking tool. We have providers from in-memory indexed tables, CTree (r) files, Oracle tables (read only), structured disk files, in-memory structures, binary records, etc. The trick is to be able to formulate your queries solely via comparison operators. This type of constraint gets passed to your xBestIndex function and can be processed there. e.g. provide 2 virtual fields _function and _frame SELECT * from VA, VB where VA._function='Intersect' and VA._frame=VB.geom; When called for VA or VB with the constraints (_function,=) and (frame,=) your xBestIndex function should return - a value proportional to the effort of locating a record via the internal index as estimated cost - a number that signifies use internal index - set the omit flag fort he contraints - set the argvIndex values for the constraints When called for VA or VB without constraints, your xBestIndex function should return - a value proportional to the effort of a full table scan as estimated cost - a number that signifies full table scan This will make SQLite read VB via full table scan, and look up VA via the internal index. For each row retrieved from VB, your xFilter function will be called with the parameter values Intersect and VB.geom. SQLite will expect to retrieve exactly those rows of VA the Intersect with VB.geom. Assuming that all _functions are commutative e.g. F(a,b) = F(b,a) you could provide a symmetrical solution: SELECT * from VA, VB where VA._function='Intersect' and VB._function='Intersect' and VA._frame=VB.geom and VB._frame=VA.geom; SQLite would then choose the smaller product of full table scan * lookup. I think it should be possible to have SQLite omit all the checks; if not, _frame needs to return geom (best guess...). -Ursprüngliche Nachricht- Von: Hugo Mercier [mailto:hugo.merc...@oslandia.com] Gesendet: Mittwoch, 05. November 2014 10:09 An: General Discussion of SQLite Database Betreff: [sqlite] SQLite as a meta database Hi all, Following a first discussion on sqlite-dev that was probably not the right place to post, I've been invited to repost here for a broader audience :) I am a developer on QGIS and I am investigating the possible use of SQLite / Spatialite to extend QGIS relational-oriented features. For now, we have what we call data providers that allow to open / read / modify geographic data from different data sources, more or less structured data from regular files or from local or remote databases. Some database concepts are little by little put into QGIS, but some of us feel this is not exactly the right place for that. So I am considering the use of the virtual table mechanism of SQLite to embed a powerful SQL engine in QGIS. The idea would be to expose each type of GIS layer as a virtual table in SQLite. Then the user could use them for advanced queries such as (spatial) joins. GIS layers can already be RDBMS, like Postgresql/Postgis, MSSQL, Oracle spatial, etc. There have been discussions on QGIS ML about that, and we are concerned about the performances of such an approach [1] [2] [3] The main concern is about how to translate a main query that must in the end be split into queries to different databases. And especially regarding the use of native indices of such databases. From previous answers on sqlite-dev, using dedicated fields estimatedCost and estimatedRows in xBestIndex could be enough to orient the planner if native indices on regular columns are present (and if the virtual table knows that) For geometry column(s) that might be more complicated if I am correct. For a query such as: SELECT * FROM VA, VB where Intersects(VA.geom, VB.geom) where VA are virtual tables of say a PostGIS table and a Shapefile respectively, there is no way to inform xBestIndex to use the native spatial indices of VA or VB during the query. Native spatial indices must be locally copied and explicitly used with spatialite like : SELECT * FROM VA, VB where Intersects(VA.geom, VB.geom) AND VA.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'VA' AND search_frame = VB.geom ) Avoiding such explicit syntax and index duplication would require something like the implementation of GIST [4] in Sqlite, and having more generic constraints passed to xBestIndex, I guess. Not very easy. Are there other possibilies that I am missing ? The other concern is about accessing the parsed SQL query before executing it. This could be used to process the query in order to : - collect information on it : table names, column names and types, especially detecting geometry columns - bypass SQLite execution if the query is actually to be made on tables of the same database - possibly do SQL-SQL transformations Apparently accessing this parse tree is often asked for here, and some said
Re: [sqlite] Index without backing table
Can you give an example of what such a beast would look like?
CREATE TABLE not_exist (A, B, C, D, E);-- for illustrative purposes
CREATE INDEX no_table ON not_exist (A,B,C,D,E); -- has to be covering index
The only efficient order to return and/or select rows is by {A}, {A,B},
{A,B,C}, ...
All other selects need to perform a full table/index scan, e.g. SELECT ...
WHERE C=15;
All other orders need to perform a sort, e.g. SELECT ... ORDER BY C;
-Ursprüngliche Nachricht-
Von: Paul [mailto:de...@ukr.net]
Gesendet: Freitag, 31. Oktober 2014 11:30
An: General Discussion of SQLite Database
Betreff: [sqlite] Index without backing table
Is there a way to have index without table?
Is it possible to achieve this with virtual talbe tricks but without
implementing B-tree myself?
I need this both for space effciency and to minimize disk I/O (by avoiding
table updates).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Table qualification not supported in some cases (was: quasi-bug related to locking, and attached databases)
Indices, foreign keys, ... all work only within a single DB file, so allowing a qualifier would suggest functionality that is not present and probably quite hard to provide. How would one keep an index residing in one DB file consistent with a table in a different file if only one of the files is attached? In these cases, the qualification is implied. NB: IIRC this also applies to views, but seems not to be prominently documented. Eg. CREATE INDEX db.index_name ON [=db.]table_name ... CREATE TABLE db.table_name (...) FOREIGN KEY (...) REFERENCES [=db.]referenced_table CREATE TRIGGER db.trigger_name ... ON [=db.]table_name ... CREATE VIEW db.view_name AS SELECT ... FROM [=db.]table_name ... -Ursprüngliche Nachricht- Von: Stephan Beal [mailto:sgb...@googlemail.com] Gesendet: Montag, 27. Oktober 2014 20:18 An: General Discussion of SQLite Database Betreff: Re: [sqlite] quasi-bug related to locking, and attached databases On Mon, Oct 27, 2014 at 11:59 AM, Stephan Beal sgb...@googlemail.com wrote: On Mon, Oct 27, 2014 at 11:53 AM, Hick Gunter h...@scigames.at wrote: TEMP tables get created in database temp; which is located in a file or in memory depending on the SQLITE_TEMP_STORE preprocessor symbol and the pragma temp_store. Which reveals my ignorance on the topic ;). IIRC we aren't using a specific temp store - we're using whatever's compiled in by default. So... maybe paying for a :memory: handle we don't really use won't be as painful as i first thought. Just add a pragma call to ensure that we're using disk instead of memory for temp store. Follow-up: injecting a :memory: db as the first-opened DB turned out to be a very small change (because the code was set up for that at one point), and it turns out that using ATTACH for all three of our library-level DBs gives us three or four minor features/benefits we didn't have before. e.g. it was impossible to close one of the three DBs in one particular (and as yet hypothetical) use case, but we can now attach/detach each one at will without regard for the others or which one was opened first (as that role is now taken by the :memory: placeholder). All in all, making that change was a win. Sidebar: it turns out there are some contexts where fossil does not allow db.table qualification (namely (REFERENCES D.T) and (CREATE INDEX ... ON D.T(F)) do not allow it), but that's a topic for another thread if/when it becomes problematic (so far it's only a hypothetical problem, and not one worth losing any sleep over). @Gunter: Vielen Dank for clarifying where TEMP tables go: that misunderstanding was why i migrated away from this setup in the first place. (Und schoenen Gruss aus Muenchen!) -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do. -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
SQLite treats each attached database as a separate entity. Attaching the same
file twice is just asking for problems.
The query specifies that the destination db be locked for write and the source
db for read; which translates to two locks that cannot coexist on one
underlying db file.
-Ursprüngliche Nachricht-
Von: dave [mailto:d...@ziggurat29.com]
Gesendet: Sonntag, 26. Oktober 2014 00:36
An: 'General Discussion of SQLite Database'; sqlite-...@sqlite.org
Betreff: Re: [sqlite] quasi-bug related to locking, and attached databases
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephan Beal
Sent: Saturday, October 25, 2014 3:32 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] quasi-bug related to locking,and attached
databases
...
Simon, FYI: this is the 'main' db aliasing problem i brought up a
couple months ago, which we solved by using your suggestion: re-attach
the db directly after opening it, so that (as Dave said) all DBs have
well-known internal names regardless of what order they get opened in.
Reproduced here with 3.8.6 on Linux/x64:
sqlite insert or replace into main.dest ( name, value ) values
('allow',(select value from aux.source where name = 'allow'));
Error: database is locked
Really, it's a fundamental problem irrespective of 'self attached databases',
it happens any time you attach a database more than once. I wish I realized
that when I first reported it. E.g.:
C:\Documents and Settings\personsqlite3 db2.db SQLite version 3.6.16 Enter
.help for instructions Enter SQL statements terminated with a ;
sqlite create table dest ( name text, value text ); create table source
sqlite ( name text, value text ); insert into source ( name, value )
sqlite values ( 'allow', 'yes' ); .exit
C:\Documents and Settings\personsqlite3 db1.db SQLite version 3.6.16 Enter
.help for instructions Enter SQL statements terminated with a ;
sqlite attach database 'db2.db' as dest; attach database 'db2.db' as
sqlite src; .datavases
unknown command or invalid arguments: datavases. Enter .help for help
sqlite .databases
seq name file
--- ---
--
0main C:\Documents and Settings\person\db1.db
2dest C:\Documents and Settings\person\db2.db
3src C:\Documents and Settings\person\db2.db
sqlite insert or replace into dest.dest (name, value) values
('allow',(select value from src.source where name = 'allow' )); SQL error:
database is locked
I would imagine that a possible clean fix would be for the pager to have a
'lock count', locking only when it goes from 0 to 1. If that is actually
workable, then all the rest of sqlite can blythely carry on with no
modification.
-dave
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
How about always referencing all tables via attached db names? That way, main is never referenced, neither explicitly nor implicitly, and is therefore never locked. -Ursprüngliche Nachricht- Von: Stephan Beal [mailto:sgb...@googlemail.com] Gesendet: Montag, 27. Oktober 2014 09:43 An: General Discussion of SQLite Database Betreff: Re: [sqlite] quasi-bug related to locking, and attached databases On Mon, Oct 27, 2014 at 9:23 AM, Hick Gunter h...@scigames.at wrote: SQLite treats each attached database as a separate entity. Attaching the same file twice is just asking for problems. The query specifies that the destination db be locked for write and the source db for read; which translates to two locks that cannot coexist on one underlying db file. That's the thing - if we leave out the explicit DB names then it works as expected (or against expectations, depending on one's world view). It's only when adding the explicit db name qualification that it locks. i agree, attaching an opened DB is a huge kludge, but the problem is (summarized): - this app (libfossil) managed 3 different databases. Which one of those gets opened first is unknown/unknowable, and there is no requirement than any of them get opened, or maybe only a subset will. fossil(1) has the same setup but juggles the dbs somewhat differently and does not expose any interface to the outside world, so this has so far remained an internal problem with no effect on clients. - sqlite automatically names the first db main, and provides to way to change that. - end effect is: client code must know which order the dbs were opened in order to know which name is correct for each logical DB. This is a painfully leaky abstraction, though. - a couple months back Simon suggested ATTACHing the db to itself so that we can effectively alias main to the well-known name we have specified for that db instance. It worked like a charm until Dave discovered this weird locking behaviour. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do. -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
TEMP tables get created in database temp; which is located in a file or in memory depending on the SQLITE_TEMP_STORE preprocessor symbol and the pragma temp_store. -Ursprüngliche Nachricht- Von: Stephan Beal [mailto:sgb...@googlemail.com] Gesendet: Montag, 27. Oktober 2014 11:44 An: General Discussion of SQLite Database Betreff: Re: [sqlite] quasi-bug related to locking, and attached databases On Mon, Oct 27, 2014 at 11:08 AM, Stephan Beal sgb...@googlemail.com wrote: - TEMP tables get created in the MAIN db (assuming my memory of the docs is correct), which means we can (though accidental misuse or carelessness) end up filling up RAM with temporary tables (which we use regularly to process large data amounts). This is my biggest concern with this approach. In fact, it seems impossible to use any db _except_ the main one for temp tables: sqlite .databases seq name file --- --- -- 0main /home/portal/tmp/bar.db 2foo /home/portal/tmp/foo.db sqlite create temp table foo.baz(z); Error: temporary table name must be unqualified Which rules out use of a :memory: db has the local main - we make use of temp tables with arbitrarily large data sets. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do. -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unbinding parameters
http://www.sqlite.org/c3ref/clear_bindings.html -Ursprüngliche Nachricht- Von: Baruch Burstein [mailto:bmburst...@gmail.com] Gesendet: Donnerstag, 23. Oktober 2014 13:47 An: General Discussion of SQLite Database Betreff: [sqlite] Unbinding parameters It says here (https://www.sqlite.org/c3ref/bind_blob.html) that a) Unbound parameters are interpreted as NULL, and b) Bindings are not cleared by sqlite3_reset() Is there any way to clear all bindings, so that if I don't set them again they will insert NULL? -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] struct SrcList
I estimate that you have about a 1 : 2^^32 chance of assigning the correct value. I guess it is an index into a table of cursors required for processing the statement and there will be assertions to satisfy. -Ursprüngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Montag, 06. Oktober 2014 11:59 An: General Discussion of SQLite Database Betreff: [sqlite] struct SrcList Hi, struct SrcList has a field int iCursor, if I'm manually filling up this struct ,can I assign any integral value to iCursor,so that this value can be used by OpenRead or OpenReadWrite opcode when it accesses this particular table ? Thanks Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Division accuracy
Floating point values are represented as n bits of mantissa * 2 ^^ m bits of
exponent
The egde cases are inserting in sorted order.
Descending:
The first row is tagged with 1.0
Each new first row is tagged with 1/2 the previous.
This will either lose 1 bit of mantissa or decrement the exponent.
This means you will run out of values before reaching n + 2 ^^ (m-1)
Ascending:
The rows are assigned consecutive integers.
This means you will run out of values before reaching 2 ^^ n (because then the
smallest increment becomes 2)
With n=53 and m=11 this gives you about 1000 inserts in descending order, which
at a rate of 3/sec gives you all of 333 seconds (about 5 1/2 Minutes) between
renumbering runs.
Of course you could implement a special number format with n=m=32 for 2^^31
inserts, which at a rate of 3/sec gives you a mere 22 years between renumbering
runs. You would need to write a user defined functions to manipulate and
compare the values though.
-Ursprüngliche Nachricht-
Von: Scott Robison [mailto:sc...@casaderobison.com]
Gesendet: Mittwoch, 24. September 2014 18:58
An: rsm...@rsweb.co.za; General Discussion of SQLite Database
Betreff: Re: [sqlite] Division accuracy
On Wed, Sep 24, 2014 at 10:49 AM, RSmith rsm...@rsweb.co.za wrote:
I'm trying to find what the limit is for dividing in terms of accuracy.
Basically I have one program that inserts values to a table and
determine sort order using one standard trick that has a REAL column
named SortOrder which gets the value Highest_previous_value+1 if an
insert happens with something that needs to be sorted at the end of the table.
For any other position, the SortOrder gets assigned the value:
((Prev.Sortorder + Next.Sortorder) / 2)
{snipped}
A quick bit of test code shows me that after 53 iterations you'll run out of
precision, which makes sense because there are 53 mantissa bits in a normalized
double including the implicit leading 1 bit).
My quick dirty test code which may be useful.
#include stdio.h
int main()
{
double lo = 1.0;
double hi = 2.0;
int count = 0;
while (lo != hi)
{
double mid = (lo + hi) / 2.0;
printf(%d %f\n, ++count, mid);
lo = mid;
}
return 0;
}
--
Scott Robison
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Results of Joins in sqlite
You are free to build your own result conversion routine on top of the SQLite
Api.
May I suggest selecting the rowids of the tables too i.e.
SELECT t1.rowid, t2.rowid, t3.rowid, .. more fields ... FROM ...your
join...;
When you first come across a new rowid you can create your memory object and
populate it from the required fields.
The disadvantage ist hat SQLite will still retrieve all requested fields for
each result row.
Or maybe you might like to
SELECT t1.rowid, t2.rowid, t3.rowid FROM ...your join...;
and then populate your structure by binding the rowid received and executing
the appropriate
SELECT ...t1 fields FROM t1 where t1.rowid=?;
SELECT ...t2 fields FROM t2 where t2.rowid=?;
SELECT ...t3 fields FROM t3 where t3.rowid=?;
-Ursprüngliche Nachricht-
Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
Gesendet: Mittwoch, 24. September 2014 09:32
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Results of Joins in sqlite
Thanks for the reply Clemens. Yes.I'm sorry. It will not have 8 columns.
When one result row comes in , I want to create an object for each table (My
application map one struct to one table) and put the columns of respective
tables in their individual structs. i,e I would set column 0 and column 1 in
result row to the struct of T1 and column 2 and 3 to that of T2 and so on.
and I set pointers between T1 and T2 , T2 and T3.
When a new row comes in and if the object for that row is already created.
i.e if r11 again comes as output, I will not create a new object,instead I
would use the old object and set pointers between the old T1 and object and the
T2 object (if r21 is new,else do not set pointers and do not create
objects)
similarly for T3. Thus,for the above case (the example we discussed), there
will be one pointer from T1 row to T2 row and 3 pointers from T2 row to the
3 T3 rows(one pointer per row).
The end data structure will look like a graph.
I want the API to return this graph./ is there an alternative ?
API call : graph* sqlite3_join(select_stmt*)
Thanks
Prakash
On Wed, Sep 24, 2014 at 12:45 PM, Clemens Ladisch clem...@ladisch.de
wrote:
Prakash Premkumar wrote:
Let's say I have tables T1,T2 and T3 with 2 columns each and I am
joining them.
The result rows will have 8 columns each.
No. The result will have between 4 and 6 columns, depending on how
you do the joins.
Example:
CREATE TABLE T1(ID1, Name);
INSERT INTO T1 VALUES('r11',NULL);
CREATE TABLE T2(ID2, ID1);
INSERT INTO T2 VALUES('r21','r11');
CREATE TABLE T3(ID3, ID2);
INSERT INTO T3 VALUES('r31','r21');
INSERT INTO T3 VALUES('r32','r21');
INSERT INTO T3 VALUES('r33','r21');
SELECT * FROM T1 NATURAL JOIN T2 NATURAL JOIN T3;
ID1 NameID2 ID3
-- -- -- --
r11 r21 r31
r11 r21 r32
r11 r21 r33
sqlite produces 3 result rows , but I would like to produce one
result
row
where the results are linked and the rows r11 and r21 are not
repeated.(It
is similar to an adjacency list representation)
The output of an SQLite query always has a fixed number of columns,
and for specific data, a fixed number of rows.
Please specify exactly how the output for this example data should
look like.
The efficiency I gain with the 2nd approach, is the reduction is in
the number of copies .i.e each row is copied only once.
Your program has to read either the value of ID1, to compare it with
the last one, or some other indication that there is a new T1 row.
What's the difference? Or how exactly should the API look like?
Regards,
Clemens
___
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
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Results of Joins in sqlite
How about writing something that uses the SQLite Api as intended and works
first?
-Ursprüngliche Nachricht-
Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
Gesendet: Mittwoch, 24. September 2014 12:54
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Results of Joins in sqlite
Thanks a lot Hick,for your approach.
With the approach you suggested, we are creating extra queries and if the join
is on n tables there will be n+1 queries , and each query will have to go
through a query planning stage.
Is there an alternative idea ?
Thanks a lot
Prakash
On Wed, Sep 24, 2014 at 3:34 PM, Hick Gunter h...@scigames.at wrote:
You are free to build your own result conversion routine on top of the
SQLite Api.
May I suggest selecting the rowids of the tables too i.e.
SELECT t1.rowid, t2.rowid, t3.rowid, .. more fields ... FROM
...your join...;
When you first come across a new rowid you can create your memory
object and populate it from the required fields.
The disadvantage ist hat SQLite will still retrieve all requested
fields for each result row.
Or maybe you might like to
SELECT t1.rowid, t2.rowid, t3.rowid FROM ...your join...;
and then populate your structure by binding the rowid received and
executing the appropriate
SELECT ...t1 fields FROM t1 where t1.rowid=?; SELECT ...t2 fields
FROM t2 where t2.rowid=?; SELECT ...t3 fields FROM t3 where
t3.rowid=?;
-Ursprüngliche Nachricht-
Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
Gesendet: Mittwoch, 24. September 2014 09:32
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Results of Joins in sqlite
Thanks for the reply Clemens. Yes.I'm sorry. It will not have 8 columns.
When one result row comes in , I want to create an object for each
table (My application map one struct to one table) and put the columns
of respective tables in their individual structs. i,e I would set
column 0 and column 1 in result row to the struct of T1 and column 2
and 3 to that of T2 and so on.
and I set pointers between T1 and T2 , T2 and T3.
When a new row comes in and if the object for that row is already created.
i.e if r11 again comes as output, I will not create a new
object,instead I would use the old object and set pointers between the
old T1 and object and the T2 object (if r21 is new,else do not set
pointers and do not create
objects)
similarly for T3. Thus,for the above case (the example we discussed),
there will be one pointer from T1 row to T2 row and 3 pointers from T2
row to the
3 T3 rows(one pointer per row).
The end data structure will look like a graph.
I want the API to return this graph./ is there an alternative ?
API call : graph* sqlite3_join(select_stmt*)
Thanks
Prakash
On Wed, Sep 24, 2014 at 12:45 PM, Clemens Ladisch clem...@ladisch.de
wrote:
Prakash Premkumar wrote:
Let's say I have tables T1,T2 and T3 with 2 columns each and I am
joining them.
The result rows will have 8 columns each.
No. The result will have between 4 and 6 columns, depending on how
you do the joins.
Example:
CREATE TABLE T1(ID1, Name);
INSERT INTO T1 VALUES('r11',NULL); CREATE TABLE T2(ID2, ID1);
INSERT INTO T2 VALUES('r21','r11'); CREATE TABLE T3(ID3, ID2);
INSERT INTO T3 VALUES('r31','r21'); INSERT INTO T3
VALUES('r32','r21'); INSERT INTO T3 VALUES('r33','r21');
SELECT * FROM T1 NATURAL JOIN T2 NATURAL JOIN T3;
ID1 NameID2 ID3
-- -- -- --
r11 r21 r31
r11 r21 r32
r11 r21 r33
sqlite produces 3 result rows , but I would like to produce one
result
row
where the results are linked and the rows r11 and r21 are not
repeated.(It
is similar to an adjacency list representation)
The output of an SQLite query always has a fixed number of columns,
and for specific data, a fixed number of rows.
Please specify exactly how the output for this example data should
look like.
The efficiency I gain with the 2nd approach, is the reduction is
in the number of copies .i.e each row is copied only once.
Your program has to read either the value of ID1, to compare it with
the last one, or some other indication that there is a new T1 row.
What's the difference? Or how exactly should the API look like?
Regards,
Clemens
___
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
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail
Re: [sqlite] Crash when binding default column value
My guess would be that finalizing the create table statement makes the bound
value go out of scope and thus be unavailable to the insert statement.
Bound values reside somewhere in the internal prepared statement structure and
do not get copied into the database file, even if they happen to be default
column values.
-Ursprüngliche Nachricht-
Von: Nathaniel Trellice [mailto:napt...@yahoo.co.uk]
Gesendet: Mittwoch, 24. September 2014 13:36
An: sqlite-users@sqlite.org
Betreff: [sqlite] Crash when binding default column value
Dear list members,
I'm trying to set a default value for a column in my CREATE TABLE statement.
I really, really want to bind the default value (using sqlite3_bind_*) rather
than expressing it in SQL text to avoid the following problems:
* SQL injection attacks;
* floating point value rounding in conversion to/from text;
* how else can I set a default value for a binary blob column?
The SQL syntax tree for the CREATE TABLE statement at
http://www.sqlite.org/lang_createtable.html suggests binding a default value is
permitted. In particularcreate-table-stmt:column-def:column-constraint:expr:
can be a bind parameter.
The example code, below, highlights the problem I'm having. The code creates a
table with a bound default value for one of the two columns. The statement is
prepared (no syntax error warning), and the value bound successfully. But upon
inserting a row that requires using the default parameter, it crashes in
function sqlite3ExprCodeTarget(). (Inserting a row with both column values
explicitly set works fine.)
Tech details: using sqlite-amalgamation-3080600 on Centos 7, compiling with
GCC4.8.2.
Am I making a silly mistake? Or, contrary to the documentation, is binding a
default column value in a CREATE TABLE statement not possible? If it's not
possible, can anyone suggest another way to achieve the goal of avoiding
conversion ofdefault column values to SQL text?
Many thanks,
Nathaniel
Example code:
#include stdio.h
#include stdlib.h
#include sqlite3.h
int main(int argc, char* argv[])
{
sqlite3 *db = NULL;
sqlite3_stmt *pStmt = NULL;
int c;
/* Open database */
c = sqlite3_open_v2(
test.db, db, (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE), 0);
if (c) {
fprintf(stderr, Can't open database: %s\n, sqlite3_errmsg(db));
exit(0);
}
/* Table creation SQL */
const char *sql =
CREATE TABLE test_table (
name TEXT DEFAULT ('joe'),
interest TEXT DEFAULT (?1));
/* Prepare SQL statement */
c = sqlite3_prepare(db, sql, -1, pStmt, NULL);
if (c != SQLITE_OK) {
fprintf(stderr, Can't prepare statement: %s\n, sqlite3_errmsg(db));
exit(0);
}
/* Bind SQL statement value */
const char *golf = golf;
c = sqlite3_bind_text(pStmt, 1, golf, -1, SQLITE_STATIC);
if (c != SQLITE_OK) {
fprintf(stderr, Can't bind statement: %s\n, sqlite3_errmsg(db));
exit(0);
}
/* Evaluate SQL statement */
c = sqlite3_step(pStmt);
if (c != SQLITE_DONE) {
fprintf(stderr, Can't evaluate statement: %s\n, sqlite3_errmsg(db));
exit(0);
}
/* Destroy prepared SQL statement */
c = sqlite3_finalize(pStmt);
if (c != SQLITE_OK) {
fprintf(stderr, Can't destroy statement: %s\n, sqlite3_errmsg(db));
exit(0);
}
/* Insert row */
const char *sql2 = INSERT INTO test_table (name) VALUES ('jack');;
char *errmsg = NULL;
c = sqlite3_exec(db, sql2, NULL, NULL, errmsg);
if (c != SQLITE_OK) {
fprintf(stderr, Can't insert row: %s\n, errmsg);
exit(0);
}
sqlite3_free(errmsg);
/* Close database */
c = sqlite3_close(db);
if (c != SQLITE_OK) {
fprintf(stderr, Can't close database: %s\n, sqlite3_errmsg(db));
exit(0);
}
return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Expose struct Mem and struct Vdbe to other application
IMHO you are going down a dark and dangerous passage. If your approach really does require severe hacking of SQLite internals then maybe that is an indication that you really need to change the approach or acquire a different tool. Maybe you are trying to nail it with a set of pliers. Doable, but not as neat as using a hammer in the first place, and also hard on the pliers. -Ursprüngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Dienstag, 23. September 2014 06:29 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Expose struct Mem and struct Vdbe to other application Thanks a lot for your reply Hick. I'm trying to split the results of joins. I have one struct per table and if I am joining 3 tables, I would like to fill the objects of the respective structs with the values from the ResultSet in Vdbe pResultSet and I want to do it before the callback(interface for the external applications) is called Is there are a way , other than using the sqlite_column_ functions, since I have to make one function call per column, it is very costly. Can I not expose the Vdbe and Mem structs by placing it in sqlite3.h file like struct sqlite3,which is exposed that way ? Thanks Prakash On Mon, Sep 22, 2014 at 8:38 PM, Hick Gunter h...@scigames.at wrote: Use the sqlite3_column_ functions to return result fields... Or you need to use the non-amalgamation sources and integrate them into your build environment. Such use is probably strongly discouraged by SQLite developers, as the internal structures are subject to change without notice. Also, a given VDBE Program implements EXACTLY the SQL query it was prepared with and works only with EXACTLY the schema it was prepared against. Are you trying to implement stored procedures? -Ursprüngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Montag, 22. September 2014 15:37 An: General Discussion of SQLite Database Betreff: [sqlite] Expose struct Mem and struct Vdbe to other application Hi, Let's assume I am writing a c code which directly invokes the sqlite_step statement. After the execution of the statement, I would like to access the pResultRow of Vdbe (which obtained by pVbe = (Vdbe*) pStmt ). How can I expose the struct Vdbe,Mem and the likes to external applications. Including them in the header file gave me the error incomplete defintion of struct Can you kindly help me? Thanks Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ 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 ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Expose struct Mem and struct Vdbe to other application
Just like any other definition provided in a .h file... If you have the amalgamation sources (just a sqlite3.c and sqlite3.h file) you need to either split sqlite3.c into its component files or download the component files directly. Or maybe just extract the vdbeint.h file. Such practices are not encouraged by the SQLite Dev team. -Ursprüngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Dienstag, 23. September 2014 09:25 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Expose struct Mem and struct Vdbe to other application Thanks a lot for your opinion Hick. But the act of exposing struct Vdbe should be simple right. It's there sitting on my source code and my application needs to access it, just like it can access struct sqlite3. Can you kindly tell me how that can be done ? P.S. I am almost always going to compile my application along with sqlite source code. Thanks Prakash On Tue, Sep 23, 2014 at 11:54 AM, Hick Gunter h...@scigames.at wrote: IMHO you are going down a dark and dangerous passage. If your approach really does require severe hacking of SQLite internals then maybe that is an indication that you really need to change the approach or acquire a different tool. Maybe you are trying to nail it with a set of pliers. Doable, but not as neat as using a hammer in the first place, and also hard on the pliers. -Ursprüngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Dienstag, 23. September 2014 06:29 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Expose struct Mem and struct Vdbe to other application Thanks a lot for your reply Hick. I'm trying to split the results of joins. I have one struct per table and if I am joining 3 tables, I would like to fill the objects of the respective structs with the values from the ResultSet in Vdbe pResultSet and I want to do it before the callback(interface for the external applications) is called Is there are a way , other than using the sqlite_column_ functions, since I have to make one function call per column, it is very costly. Can I not expose the Vdbe and Mem structs by placing it in sqlite3.h file like struct sqlite3,which is exposed that way ? Thanks Prakash On Mon, Sep 22, 2014 at 8:38 PM, Hick Gunter h...@scigames.at wrote: Use the sqlite3_column_ functions to return result fields... Or you need to use the non-amalgamation sources and integrate them into your build environment. Such use is probably strongly discouraged by SQLite developers, as the internal structures are subject to change without notice. Also, a given VDBE Program implements EXACTLY the SQL query it was prepared with and works only with EXACTLY the schema it was prepared against. Are you trying to implement stored procedures? -Ursprüngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Montag, 22. September 2014 15:37 An: General Discussion of SQLite Database Betreff: [sqlite] Expose struct Mem and struct Vdbe to other application Hi, Let's assume I am writing a c code which directly invokes the sqlite_step statement. After the execution of the statement, I would like to access the pResultRow of Vdbe (which obtained by pVbe = (Vdbe*) pStmt ). How can I expose the struct Vdbe,Mem and the likes to external applications. Including them in the header file gave me the error incomplete defintion of struct Can you kindly help me? Thanks Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ 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 ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel
Re: [sqlite] Definition of struct sqlite3_stmt
It is at the very end of vdbeint.h -Ursprüngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Montag, 22. September 2014 07:29 An: General Discussion of SQLite Database Betreff: [sqlite] Definition of struct sqlite3_stmt Hi, Can you please tell me where is the definition of the struct sqlite3_stmt ? A search in sqlite3.c yields on the typedef statement of the struct , typedef struct sqlite3_stmt sqlite3_stmt; Thanks for your help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Expose struct Mem and struct Vdbe to other application
Use the sqlite3_column_ functions to return result fields... Or you need to use the non-amalgamation sources and integrate them into your build environment. Such use is probably strongly discouraged by SQLite developers, as the internal structures are subject to change without notice. Also, a given VDBE Program implements EXACTLY the SQL query it was prepared with and works only with EXACTLY the schema it was prepared against. Are you trying to implement stored procedures? -Ursprüngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Montag, 22. September 2014 15:37 An: General Discussion of SQLite Database Betreff: [sqlite] Expose struct Mem and struct Vdbe to other application Hi, Let's assume I am writing a c code which directly invokes the sqlite_step statement. After the execution of the statement, I would like to access the pResultRow of Vdbe (which obtained by pVbe = (Vdbe*) pStmt ). How can I expose the struct Vdbe,Mem and the likes to external applications. Including them in the header file gave me the error incomplete defintion of struct Can you kindly help me? Thanks Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Query to Vdbe Instructions
The sqlite3_prepare() functions convert the SQL statement into an executable VDBE program. You can view the results in the Sqlite shell by typing: .explain explain query; which will show the VDBE opcodes generated for the query (the .explain switches the output format to something suitable for explain). If you just want to know what the optimizer was thinking, use: explain query plan query; -Ursprüngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Dienstag, 16. September 2014 07:53 An: sqlite-users@sqlite.org Betreff: [sqlite] SQL Query to Vdbe Instructions Hi, Can you please tell me which function/set of functions convert the SQL query to Vdbe program ? Thanks Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SET (x,y) = (x1,y1)?
Maybe you can reformulate the query to fit INSERT OR UPDATE INTO t SELECT t.a,t.b,...,s.x,s.y FROM t, s ... -Ursprüngliche Nachricht- Von: Mark Lawrence [mailto:no...@null.net] Gesendet: Montag, 15. September 2014 10:51 An: sqlite-users@sqlite.org Betreff: [sqlite] SET (x,y) = (x1,y1)? I occasionally have the need to update two columns based on complex sub queries, which are often very similar UPDATE t SET x = ( SELECT 1...), y = ( SELECT 2...)-- nearly the same as SELECT 1 ; Normally one could use a CTE to do the work once: WITH cte AS ( SELECT 1 AS x, 2 AS y ) UPDATE t SET x = cte.x, y = cte.y ; However CTEs don't work within triggers. I was wondering hard it would be to support the SET syntax as shown in the subject line. I believe something like that works in PostgreSQL and I could use it in SQLite for performance reasons. UPDATE t SET (x,y) = (SELECT 1,2) ; Alternatively, is there any effort underway to make CTEs work inside triggers? -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cannot retrieve SQLite Db Data Immediately After Application Startup
Maybe you are mixing C malloc/free with sqlite3 memory allocation routines? Like allocating from sqlite and then freeing to C or vice versa? -Ursprüngliche Nachricht- Von: Bob Moran [mailto:bmo...@cicaccess.com] Gesendet: Mittwoch, 03. September 2014 06:25 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Cannot retrieve SQLite Db Data Immediately After Application Startup Found more of what the issue is. I noticed that my SQL text was being overwritten on the return from the call to prepare_v2. Stepping through the SQlite3 code I discovered that a malloc call for 500+ bytes was returning a pointer 8 bytes below my SQL string. Don't have the foggiest notion as to why. My string is on the heap. If I wait some time before making the call (my program is just waiting for input, then the malloc call returns a pointer far removed from my passed in SQL text. Must be some kind on Linux issue? Bob Moran Continental Access / NAPCO Security Technologies 355 Bayview Ave. Amityville, N.Y. 11701 631.842.9400 x327 rmo...@cicaccess.com -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns Sent: Tuesday, September 02, 2014 12:45 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Cannot retrieve SQLite Db Data Immediately After Application Startup On 29/08/14 12:55, Bob Moran wrote: The return code (rc) is SQLITE_OK, but stmnt is NULL (0) if I start the application and wait for at least 1 minute, everything works. You get NULL back from prepare with SQLITE_OK if the statement doesn't do anything. Examples are empty strings or comments. Chances are that is actually what is happening in your case. Roger ___ 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 --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to control cpu usage while transaction in progress
SQLite is supposed to process queries as fast as possible. Run your heavyweight queries in a dedicated thread and use your OS' way of prioritizing threads to lessen the felt impact on interactive threads (at the cost of increasing elapsed time). -Ursprüngliche Nachricht- Von: dd [mailto:durga.d...@gmail.com] Gesendet: Mittwoch, 20. August 2014 10:40 An: General Discussion of SQLite Database Betreff: [sqlite] How to control cpu usage while transaction in progress Hi all, Executing like query in a transaction. Query works with multiple tables and table has 1 million records on desktop clients. CPU goes high when transaction in progress. Is there any way to control the CPU without adding sleep statements? Is there any alternative solution for like queries? (for ex: delete * from emp where empname like %a) Thanks, dd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with Update Statement
Obviously the problem was caused by incorrectly cobbling together theSQLite statement. AFAICT the original code produces UPDATE RecordGrid SET LineNumber='something',self_empty_info_gender_PRect='something,something,something,something' WHERE RecordGridID=' Which is clearly invalid (the RHS of the WHERE condition is not terminated), with extraneous text after the end of the value ignored ( no operators to concatenate the strings). This would have become clear if you had dumped the CommandText as previously suggested -Ursprüngliche Nachricht- Von: Wycliff Mabwai [mailto:wycliff.mab...@digitaldividedata.com] Gesendet: Dienstag, 19. August 2014 20:26 An: sqlite-users Betreff: Re: [sqlite] Problem with Update Statement Solved the problem with parameterized queries as below, without parameterized queries the update statement doesn't work on SQLite. ... SQLITEcmd200.CommandText = UPDATE RecordGrid SET LineNumber=@LineNumber, self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE RecordGridID=@RecordGridID SQLITEcmd200.Parameters.AddWithValue(@LineNumber,reade300.GetInt32(11)) SQLITEcmd200.Parameters.AddWithValue(@self_empty_info_gender_PRect,... SQLITEcmd200.Parameters.AddWithValue(@RecordGridID,... ... SQLITEcmd2.CommandText = UPDATE RecordGrid SET LineNumber=' reade20.GetInt32(11) ',self_empty_info_gender_PRect= ' IIf(reade20.IsDBNull(22), String.Empty, reade20.GetString(22)) , IIf(reade20.IsDBNull(23), String.Empty, reade20.GetString(23)) , IIf(reade20.IsDBNull(24), String.Empty, reade20.GetString(24)) , IIf(reade20.IsDBNull(25), String.Empty, reade20.GetString(25)) ' WHERE RecordGridID='chombo' --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Producing RFC4180-compliant CSV output
How about piping your csv file through unix2dos? -Ursprüngliche Nachricht- Von: Peter Waller [mailto:pe...@scraperwiki.com] Gesendet: Donnerstag, 24. Juli 2014 11:27 An: sqlite-users@sqlite.org Cc: developers Betreff: [sqlite] Producing RFC4180-compliant CSV output Hi All, We're using `sqlite -csv` to generate CSV files because it is the best performing thing available to us. It turns out however that the CSV spec demands CRLF line endings [1]. There is software in the wild that barfs on non-CRLF-line-ending files, such as SQL Server Integration Services [2]. Currently as best as we can tell from inspecting the source the output depends on the platform you run on, depending on what text mode means there. On Linux and on Windows using the official binaries, that means you get the wrong line endings (just 'plain' \n). We have made a patch which works well in our environment which just makes CSV output \r\n instead of just \n. On Windows using the same compiler as the official binaries are built with the behaviour should be the same, but it may produce different output if a different compiler is used, because the behaviour of a file opened in text mode could vary. Would a patch be accepted to change the line endings to \r\n? Thanks, - Peter [1] http://tools.ietf.org/html/rfc4180#section-2 [2] https://en.wikipedia.org/wiki/SQL_Server_Integration_Services ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preferred cast in C#
Why is the column nullable if you require a default value to be returned?
-Ursprüngliche Nachricht-
Von: Random Coder [mailto:random.co...@gmail.com]
Gesendet: Dienstag, 15. Juli 2014 03:50
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Preferred cast in C#
Could you not do something like this to handle the nullable types?
T GetValueT(string field)
{
object obj = reader[field];
if (obj is System.DBNull)
return default(T);
else
return (T)obj;
}
Assuming the type is nullable, it should do the right thing, and if it's an
unexpected type, it'll throw an exception when casting to T.
On Mon, Jul 14, 2014 at 4:07 PM, Edward Ned Harvey (sqlite)
sql...@nedharvey.com wrote:
I understand there are only 5 data types in Sqlite, and that the
column type isn't necessarily the type of object returned in a query.
Is there a more seamless way to cast responses than this?
I would really love to have an easy way of putting a long? into the
database, and then getting a long? back out. Maybe it exists and I'm
just doing it the hard way right now...
string employeeName;
object myObj = reader[employeeName];
if (myObj is System.DBNull)
employeeName = null;
else if (myObj is string)
employeeName = (string)myObj;
else
throw new Exception(Unexpected object type);
___
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
---
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any
purposes, or disclose its contents to any person as to do so could be a breach
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CRUD Statistics
You can use the sqlite3_update_hook() interface to supply a callback that is invoked for (most, see documentation) INSERT, UPDATE and DELETE operations (database name, table name and rowid are passed). You can use the sqlite3_commit_hook() and sqlite3_rollback_hook() functions to determine when the accumulated counts should be saved or discarded. Retrievals are a little more difficult, as the sqlite3_stmt_status() will only return a total number of rows retrieved across all affected tables. Alternatively you may consider implementing a statistics virtual table extension that queries the native table and counts xFilter/xNext/xUpdate calls (the easy part). Writing a good xBestIndex function may be challenging though. -Ursprüngliche Nachricht- Von: Sandu Buraga [mailto:sandu.bur...@gmail.com] Gesendet: Dienstag, 08. Juli 2014 11:16 An: sqlite-users@sqlite.org Betreff: [sqlite] CRUD Statistics Hi, Is there a possibility to extract a statistic of how many CRUD operations do I have during an application life-cycle? I would like to find-out how many INSERTs do I have, how many SELECTs and so on. In particular I am focusing for multithreaded scenario, with multiple read/write operations simultaneously, using WAL. Thanks Sandu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-3.8.5: query takes quite a while to execute although there is a limit 0 clause
Output from .explain explain query plan select... explain select... would be interesting -Ursprüngliche Nachricht- Von: Nissl Reinhard [mailto:reinhard.ni...@fee.de] Gesendet: Dienstag, 08. Juli 2014 11:46 An: sqlite-users@sqlite.org Betreff: [sqlite] sqlite-3.8.5: query takes quite a while to execute although there is a limit 0 clause Hi, when I run this query: select * from ( WITH Processes AS ( SELECT DISTINCT d.SummaryId, d.ProcessId, d.Client FROM ProfileDetail d WHERE d.Client 'unknown' ) SELECT q.*, p.Client FROM QueryTrace q INNER JOIN Processes p ON q.SummaryId = p.SummaryId AND q.ProcessId = p.ProcessId ORDER BY q.Duration DESC ) limit 0 it takes quite a while to return no result (no result is expected due to the limit 0 clause). So I thought, the query optimizer isn't smart enough and moved the limit 0 clause to the inner statement for testing: select * from ( WITH Processes AS ( SELECT DISTINCT d.SummaryId, d.ProcessId, d.Client FROM ProfileDetail d WHERE d.Client 'unknown' ) SELECT q.*, p.Client FROM QueryTrace q INNER JOIN Processes p ON q.SummaryId = p.SummaryId AND q.ProcessId = p.ProcessId ORDER BY q.Duration DESC limit 0 ) This query still takes a while to execute. By mistake, I had accidentally also tried the query with two limit 0 clauses like this: select * from ( WITH Processes AS ( SELECT DISTINCT d.SummaryId, d.ProcessId, d.Client FROM ProfileDetail d WHERE d.Client 'unknown' ) SELECT q.*, p.Client FROM QueryTrace q INNER JOIN Processes p ON q.SummaryId = p.SummaryId AND q.ProcessId = p.ProcessId ORDER BY q.Duration DESC limit 0 ) limit 0 This query runs by magnitudes faster than the former ones. Inspired by this behavior, I've tried the following query, which also has two limit 0 clauses, but unmodified inner statement (this is a constraint in my use case): select * from ( select * from ( WITH Processes AS ( SELECT DISTINCT d.SummaryId, d.ProcessId, d.Client FROM ProfileDetail d WHERE d.Client 'unknown' ) SELECT q.*, p.Client FROM QueryTrace q INNER JOIN Processes p ON q.SummaryId = p.SummaryId AND q.ProcessId = p.ProcessId ORDER BY q.Duration DESC ) limit 0 ) limit 0 This query is also fast. While it seems that I now have a workaround for my use case, it would be nice if a single limit 0 clause at the outer statement would be sufficient for a fast query. Bye. -- Reinhard Nißl, TB3, -198 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-3.8.5: query takes quite a while to execute although there is a limit 0 clause
29001 60Close 2 0 000 61Close 8 0 000 62OpenPseudo 9 26900 63Sort 7 76000 64 Column 7 2 26 00 65 Column 9 0 17 20 66 Column 9 1 18 00 67 Column 9 2 19 00 68 Column 9 3 20 00 69 Column 9 4 21 00 70 Column 9 5 22 00 71 Column 9 6 23 00 72 Column 9 7 24 00 73 Column 9 8 25 00 74 Yield 1 0 000 75Next 7 64000 76EndCoroutine 1 0 000 77Integer0 31000 78Goto 0 93000 79InitCoroutine 1 0 200 80Yield 1 93000 81 Copy 1732000 82 Copy 1833000 83 Copy 1934000 84 Copy 2035000 85 Copy 2136000 86 Copy 2237000 87 Copy 2338000 88 Copy 2439000 89 Copy 2540000 90 ResultRow 329 000 91 IfZero 3193-1 00 92Goto 0 80000 93Halt 0 0 000 94Transaction0 0 280 01 95TableLock 0 4 0 ProfileDetail 00 96TableLock 0 9 0 QueryTrace 00 97String80 4 0 unknown00 98Goto 0 1 000 Bye. -- Reinhard Nißl, TB3, -198 -Ursprüngliche Nachricht- Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] Im Auftrag von Hick Gunter Gesendet: Dienstag, 8. Juli 2014 11:58 An: General Discussion of SQLite Database Betreff: Re: [sqlite] sqlite-3.8.5: query takes quite a while to execute although there is a limit 0 clause Output from .explain explain query plan select... explain select... would be interesting -Ursprüngliche Nachricht- Von: Nissl Reinhard [mailto:reinhard.ni...@fee.de] Gesendet: Dienstag, 08. Juli 2014 11:46 An: sqlite-users@sqlite.org Betreff: [sqlite] sqlite-3.8.5: query takes quite a while to execute although there is a limit 0 clause Hi, when I run this query: select * from ( WITH Processes AS ( SELECT DISTINCT d.SummaryId, d.ProcessId, d.Client FROM ProfileDetail d WHERE d.Client 'unknown' ) SELECT q.*, p.Client FROM QueryTrace q INNER JOIN Processes p ON q.SummaryId = p.SummaryId AND q.ProcessId = p.ProcessId ORDER BY q.Duration DESC ) limit 0 it takes quite a while to return no result (no result is expected due to the limit 0 clause). So I thought, the query optimizer isn't smart enough and moved the limit 0 clause to the inner statement for testing: select * from ( WITH Processes AS ( SELECT DISTINCT d.SummaryId, d.ProcessId, d.Client FROM ProfileDetail d WHERE d.Client 'unknown' ) SELECT q.*, p.Client FROM QueryTrace q INNER JOIN Processes p ON q.SummaryId = p.SummaryId AND q.ProcessId = p.ProcessId ORDER BY q.Duration DESC limit 0 ) This query still takes a while to execute. By mistake, I had accidentally also tried the query with two limit 0 clauses like this: select * from ( WITH Processes AS ( SELECT DISTINCT d.SummaryId, d.ProcessId, d.Client FROM ProfileDetail d WHERE d.Client 'unknown' ) SELECT q.*, p.Client FROM QueryTrace q INNER JOIN Processes p ON q.SummaryId = p.SummaryId AND q.ProcessId = p.ProcessId ORDER BY q.Duration DESC limit 0 ) limit 0 This query runs by magnitudes faster than the former ones. Inspired by this behavior, I've tried the following query, which also has two limit 0 clauses, but unmodified inner statement (this is a constraint in my use case): select * from ( select * from ( WITH Processes AS ( SELECT DISTINCT d.SummaryId, d.ProcessId, d.Client FROM ProfileDetail d WHERE d.Client 'unknown' ) SELECT q.*, p.Client FROM QueryTrace q INNER JOIN Processes p ON q.SummaryId = p.SummaryId AND q.ProcessId = p.ProcessId ORDER BY q.Duration DESC ) limit 0 ) limit 0 This query is also fast. While it seems that I now
Re: [sqlite] Error xBestIndex returned an invalid plan
fred is only known if your select statement references only that one table
and the value is supplied as a literal.
If the value is a bound variable or part of an (explicit or implicit) join
expression the value(s) (there may be more than one) will not be known until
well after the sqlite3_prepare() call.
-Ursprüngliche Nachricht-
Von: Dominique Devienne [mailto:ddevie...@gmail.com]
Gesendet: Freitag, 04. Juli 2014 16:22
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Error xBestIndex returned an invalid plan
On Fri, Jul 4, 2014 at 2:50 PM, Hick Gunter h...@scigames.at wrote:
SELECT * FROM person WHERE name = 'fred'; will have SQLite asking what is
the cost of a partial table scan on field name ( {name, '='} ) and the
answer should be the average number of entries that must be read to locate
all matching entries ( e.g. the cost of finding the first one plus the
average number of duplicates: ld n + (n / distinct names) ). You need to
record that the first argv is the name.
What bugs me is that you don't get to know about 'fred', i.e. you must return a
cost without knowing what value the query will use (bind peeking [1]), which in
the case of range queries (non-unique index on the column used by the where
clause) can make a big difference.
Many of my virtual tables are based on C++ associative containers which
implement equal_range() efficiently, such that I could use it to obtain an
exact cardinality with zero I/O involved, but SQLite doesn't allow me to do
that. And I think that's a shame. You don't even have access to it when it's a
literal value, not only bind values. Of course, when the value comes from a
subquery, or via a join, that's different, but in the case of literals and
binds, that's a shame.
My $0.02. --DD
[1]
https://asktom.oracle.com/pls/asktom/f?p=100:11:0P11_QUESTION_ID:492078000346228806
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
---
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any
purposes, or disclose its contents to any person as to do so could be a breach
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error xBestIndex returned an invalid plan
The values on the RHS of constraints are generally not known until (bound values) or after (joins) the query is executed. At that point, having a better estimate is moot. -Ursprüngliche Nachricht- Von: Dominique Devienne [mailto:ddevie...@gmail.com] Gesendet: Montag, 07. Juli 2014 10:39 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Error xBestIndex returned an invalid plan On Mon, Jul 7, 2014 at 10:25 AM, Hick Gunter h...@scigames.at wrote: fred is only known if your select statement references only that one table and the value is supplied as a literal. Known to whom? AFAIK, even in that case, http://www.sqlite.org/vtab.html#xbestindex does not know about 'fred'. Which field of struct sqlite3_index_info would 'fred' be available from? aConstraint.iTermOffset which is internal to SQLite? All I'm saying is that vtables would have the opportunity to report correct/better estimates if the right-hand-sides of constraints, when they are literals or bind values (i.e. bind peeking), were available to xBestIndex. I don't know what that entails, but I'm making it known to SQLite devs, just in case, in the hope it might become available in the future, even if only in a reduced capacity like the single-table-with-literal use case you mentioned Hick. Thanks, --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite INNER JOIN vtable
The type of join is unaffected by the type of table (native or virtual). Pretend all tables are native SQLite tables. You must return correct results from your xBestIndex function for the cost-based optimiser to select an efficient plan. -Ursprüngliche Nachricht- Von: Micka [mailto:mickamus...@gmail.com] Gesendet: Montag, 07. Juli 2014 17:11 An: General Discussion of SQLite Database Betreff: [sqlite] sqlite INNER JOIN vtable Hi, In my case I've a normal sqlite table : toto and an vtable : special This is the typical request that I have : SELECT toto.id, toto.name, special.info FROM toto INNER JOIN special ON ( special.id = toto.specialid AND toto.test == TRUE ) I don't know if I should use LEFT JOIN, RIGHT JOIN or INNER JOIN in this case ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error xBestIndex returned an invalid plan
As you noticed, you were asking to have the values of unusable constraints
passed to your vt_filter function.
But how are you telling your vt_filter function which fields the passed values
belong to? I don't see how the column number of the first constraint - usable
or not- is going to be sufficient information. Neither is there a check for the
type of operation requested taken into account.
Since your filter/next functions are probably going to return arbitrary
records, the fact that you are not setting any omit flags allows SQLite to
recheck the conditions and return something vaguely resembling the correct
result set. Just with a lot of work filtering out superfluous records and
missing any records that should have been returned by filter/next (but were
not).
Also, you are writing the field aOrderBy, which is documented to be an input
field.
-Ursprüngliche Nachricht-
Von: Micka [mailto:mickamus...@gmail.com]
Gesendet: Freitag, 04. Juli 2014 09:57
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Error xBestIndex returned an invalid plan
Ok I looked at the source sqlite3.c and saw that :
This is my correction :
int vt_best_index(sqlite3_vtab *p_svt, sqlite3_index_info *p_info){
sVTAB* p_vt = (sVTAB*)p_svt;
int i;
int j=1;
printf(vt_best_index %d\n, p_info-nConstraint);
p_info-idxNum=0;
if(p_info-nConstraint!=0){
p_info-idxNum=p_info-aConstraint[0].iColumn;
p_info-idxStr=p_vt-acNameTab;
printf(best index constraint column %d\n, p_info-aConstraint[0].iColumn);
for(i=0;ip_info-nConstraint;i++){
*if(p_info-aConstraint[i].usable){*
p_info-aConstraintUsage[i].argvIndex=j++;
}
}
}
if(p_info-nOrderBy==0){
p_info-aOrderBy=NULL;
}
return SQLITE_OK;
}
Thx every one ! And Keith Medcalf ... you should use your time better !
Micka,
On Fri, Jul 4, 2014 at 9:49 AM, Micka mickamus...@gmail.com wrote:
I've no word for this ! .
On Fri, Jul 4, 2014 at 9:46 AM, Keith Medcalf kmedc...@dessus.com wrote:
The spacing and indentation are atrocious?
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of Micka
Sent: Friday, 4 July, 2014 01:29
To: General Discussion of SQLite Database
Subject: [sqlite] Error xBestIndex returned an invalid plan
Hi, I wanted to know what could be wrong with :
int vt_best_index(sqlite3_vtab *p_svt, sqlite3_index_info *p_info){
sVTAB* p_vt = (sVTAB*)p_svt;
int i;
printf(vt_best_index %d\n, p_info-nConstraint);
p_info-idxNum=0;
if(p_info-nConstraint!=0){
p_info-idxNum=p_info-aConstraint[0].iColumn;
p_info-idxStr=p_vt-acNameTab;
printf(best index constraint column %d\n, p_info-
aConstraint[0].iColumn);
for(i=0;ip_info-nConstraint;i++){
p_info-aConstraintUsage[i].argvIndex=(1+i);
}
}
if(p_info-nOrderBy==0){
p_info-aOrderBy=NULL;
}
return SQLITE_OK;
}
thx you very much ^^
___
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
---
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any
purposes, or disclose its contents to any person as to do so could be a breach
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error xBestIndex returned an invalid plan
AFAIKT you currently have two supported operation modes:
a) full table scan
b) access via exact ID
To implement a) you should make p_info-estimatedCost be the number of rows in
your table (I suggest leaving p_info-idxNum as 0).
To implement b) you need to check for a constraint with
- iColumn == index of your ID column
- op == SQLITE_INDEX_CONSTRAINT_EQ
- usable != 0
In that case you need to set p_info-estimatedCost to 1 (you will be retrieving
only this row), argvIndex to 1, omit to 1 and p_info-idxNum to something
different (I suggest -1).
You can begin thinking about range scans and index scans later.
-Ursprüngliche Nachricht-
Von: Micka [mailto:mickamus...@gmail.com]
Gesendet: Freitag, 04. Juli 2014 11:54
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Error xBestIndex returned an invalid plan
Hi,
Thx for this constructive message ;)
I don't have a lot of experience with the this part of Sqlite which is very
powerful !
In the xbestIndex function i'm using only this line :
p_info-idxNum=p_info-aConstraint[0].iColumn;
Because in my xfilter function I needed to know the index of the first column.
( do you know a better way to do that ? )
and
p_info-idxStr=p_vt-acNameTab;
This part was in case I needed to know the name of my table ... But I don't
really need it, because I'm using sqlite3_vtab *p_svt to store the extra data
that I need.
That part :
for(i=0;ip_info-nConstraint;i++){
if(p_info-aConstraint[i].usable){
p_info-aConstraintUsage[i].argvIndex=j++;
}
}
is to give the order of index . Because the role of this function is to give
the order of index right ?
In my xFilter function I'm detecting if the column index giving by idxNum is
the ID of my table. If it is, I'm positioning the cursor at the correct
position. IF the index of the column is different of ID, I'm positioning the
cursor at the beginning of my table.
YES, it's not perfect, but most of the time it works ^^ .
you said :
But how are you telling your vt_filter function which fields the passed values
belong to?
for the moment I'm only using the first column. most of the time it's the ID.
I don't see how the column number of the first constraint - usable or not- is
going to be sufficient information. Neither is there a check for the type of
operation requested taken into account.
Yes I will have to improve it .
Since your filter/next functions are probably going to return arbitrary
records, the fact that you are not setting any omit flags allows SQLite to
recheck the conditions and return something vaguely resembling the correct
result set. Just with a lot of work filtering out superfluous records and
missing any records that should have been returned by filter/next (but were
not).
Yes I have to improve that too ^^
I don't know why I should use omit ? When do you need it ? Sorry ... I'm a
beginner ..
Also, you are writing the field aOrderBy, which is documented to be an input
field.
thx, I've deleted it !
Micka,
On Fri, Jul 4, 2014 at 11:20 AM, Hick Gunter h...@scigames.at wrote:
As you noticed, you were asking to have the values of unusable
constraints passed to your vt_filter function.
But how are you telling your vt_filter function which fields the
passed values belong to? I don't see how the column number of the
first constraint
- usable or not- is going to be sufficient information. Neither is
there a check for the type of operation requested taken into account.
Since your filter/next functions are probably going to return
arbitrary records, the fact that you are not setting any omit flags
allows SQLite to recheck the conditions and return something vaguely
resembling the correct result set. Just with a lot of work filtering
out superfluous records and missing any records that should have been
returned by filter/next (but were not).
Also, you are writing the field aOrderBy, which is documented to be an
input field.
-Ursprüngliche Nachricht-
Von: Micka [mailto:mickamus...@gmail.com]
Gesendet: Freitag, 04. Juli 2014 09:57
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Error xBestIndex returned an invalid plan
Ok I looked at the source sqlite3.c and saw that :
This is my correction :
int vt_best_index(sqlite3_vtab *p_svt, sqlite3_index_info *p_info){
sVTAB* p_vt = (sVTAB*)p_svt;
int i;
int j=1;
printf(vt_best_index %d\n, p_info-nConstraint);
p_info-idxNum=0;
if(p_info-nConstraint!=0){
p_info-idxNum=p_info-aConstraint[0].iColumn;
p_info-idxStr=p_vt-acNameTab;
printf(best index constraint column %d\n,
p_info-aConstraint[0].iColumn); for(i=0;ip_info-nConstraint;i++){
*if(p_info-aConstraint[i].usable){*
p_info-aConstraintUsage[i].argvIndex=j++;
}
}
}
if(p_info-nOrderBy==0){
p_info-aOrderBy=NULL;
}
return SQLITE_OK;
}
Thx every one ! And Keith Medcalf ... you should use your time better !
Micka,
On Fri, Jul 4, 2014 at 9:49 AM
Re: [sqlite] Bug? sqlite3_column_name returns name of PK for rowid
Of course it does. The good news is that SQLite tends to stick with whatever it
comes up with first unless there is a significant change to the query.
-Ursprüngliche Nachricht-
Von: Tim Streater [mailto:t...@clothears.org.uk]
Gesendet: Freitag, 04. Juli 2014 13:28
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Bug? sqlite3_column_name returns name of PK for rowid
On 04 Jul 2014 at 11:43, Simon Slavin slav...@bigfraud.org wrote:
On 3 Jul 2014, at 10:22pm, Martin Kleusberg mkleusb...@gmail.com wrote:
I've encountered some odd behaviour when using the
sqlite3_column_name function.
Sorry, but column names are guaranteed only if you use an 'AS' clause
in your SELECT command. For every other situation, there's no telling
what you'll get. For instance
SELECT fred FROM MyTable
may return with a column name of 'fred' or 'main.fred'.
If you're using column names in your programming, always do
SELECT fred AS fred FROM MyTable
Hum. Does this apply using PHP to interface to SQLite as follows:
$res = $dbh-query ('SELECT fred FROM MyTable');
$reg = $res-fetchArray (SQLITE3_ASSOC);
$myvar = $reg['fred'];
I hope not. That's a lot of queries to change.
--
Cheers -- Tim
---
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any
purposes, or disclose its contents to any person as to do so could be a breach
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error xBestIndex returned an invalid plan
In the case outlined it is easy. If idxNum == 0 then there are no arguments. If
idxNum == -1 then the only argument is the ID value of the single row to be
retrieved.
Lets assume you are implementing a virtual table person (name text, age int,
...) that stores the rows in a flat file (the offset of the record being the
rowid) and also has an index on name.
When preparing a query, SQLite will ask a series of questions via calls to
vt_best_index. Your function answers these questions by filling in certain
fields and also recording any additional information required for the vt_filter
function (passed as idxStr). The vt_filter function retrieves the idxStr and
knows what argv to expect (and what to do with it), if any.
SELECT * FROM person; will have SQLite asking what is the cost of a full
table scan (no constraints) and the answer should be the number of rows in the
table (n).
SELECT * FROM person WHERE name = 'fred'; will have SQLite asking what is
the cost of a partial table scan on field name ( {name, '='} ) and the answer
should be the average number of entries that must be read to locate all
matching entries ( e.g. the cost of finding the first one plus the average
number of duplicates: ld n + (n / distinct names) ). You need to record that
the first argv is the name.
More complex queries will involve SQLite asking more than one question and then
choosing the answer it thinks will offer the best performance.
... join person on (person.id = ...) join ... on (... = person.name)... will
have SQLite asking the cost of a full table scan (n), a rowid access (1) and a
name access (ld n + n/d).
Think of the questions as envelopes. SQLite writes the constraints on the
outside, you write the cost on the outside, mark the constraints used, write
down how to answer the question on a piece of paper and seal it inside the
envelope. SQLite will then pick one of the envelopes, pull out the paper, and
hand it to your v_filter function along with the requested arguments.
-Ursprüngliche Nachricht-
Von: Micka [mailto:mickamus...@gmail.com]
Gesendet: Freitag, 04. Juli 2014 13:49
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Error xBestIndex returned an invalid plan
thx but how do you know in your vt_filter function :
int vt_filter( sqlite3_vtab_cursor *cur, int idxNum, const char *idxStr, int
argc, sqlite3_value **argv ){
that argv[0] is column index 4 by example ?
Micka,
On Fri, Jul 4, 2014 at 12:17 PM, Hick Gunter h...@scigames.at wrote:
AFAIKT you currently have two supported operation modes:
a) full table scan
b) access via exact ID
To implement a) you should make p_info-estimatedCost be the number of
rows in your table (I suggest leaving p_info-idxNum as 0).
To implement b) you need to check for a constraint with
- iColumn == index of your ID column
- op == SQLITE_INDEX_CONSTRAINT_EQ
- usable != 0
In that case you need to set p_info-estimatedCost to 1 (you will be
retrieving only this row), argvIndex to 1, omit to 1 and
p_info-idxNum to something different (I suggest -1).
You can begin thinking about range scans and index scans later.
-Ursprüngliche Nachricht-
Von: Micka [mailto:mickamus...@gmail.com]
Gesendet: Freitag, 04. Juli 2014 11:54
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Error xBestIndex returned an invalid plan Hi,
Thx for this constructive message ;)
I don't have a lot of experience with the this part of Sqlite which is
very powerful !
In the xbestIndex function i'm using only this line :
p_info-idxNum=p_info-aConstraint[0].iColumn;
Because in my xfilter function I needed to know the index of the first
column. ( do you know a better way to do that ? )
and
p_info-idxStr=p_vt-acNameTab;
This part was in case I needed to know the name of my table ... But I
don't really need it, because I'm using sqlite3_vtab *p_svt to store
the extra data that I need.
That part :
for(i=0;ip_info-nConstraint;i++){
if(p_info-aConstraint[i].usable){
p_info-aConstraintUsage[i].argvIndex=j++;
}
}
is to give the order of index . Because the role of this function is
to give the order of index right ?
In my xFilter function I'm detecting if the column index giving by
idxNum is the ID of my table. If it is, I'm positioning the cursor at
the correct position. IF the index of the column is different of ID,
I'm positioning the cursor at the beginning of my table.
YES, it's not perfect, but most of the time it works ^^ .
you said :
But how are you telling your vt_filter function which fields the
passed values belong to?
for the moment I'm only using the first column. most of the time it's
the ID.
I don't see how the column number of the first constraint - usable or
not- is going to be sufficient information. Neither is there a check
for the type of operation requested taken into account.
Yes I will have to improve
Re: [sqlite] Problem with many connections
How about this? sqlite3_stmt *sqlite3_next_stmt(sqlite3 *pDb, sqlite3_stmt *pStmt); This interface returns a pointer to the next prepared statement after pStmt associated with the database connection pDb. If pStmt is NULL then this interface returns a pointer to the first prepared statement associated with the database connection pDb. If no prepared statement satisfies the conditions of this routine, it returns NULL. The database connection pointer D in a call to sqlite3_next_stmt(D,S) must refer to an open database connection and in particular must not be a NULL pointer. -Ursprüngliche Nachricht- Von: Simon Slavin [mailto:slav...@bigfraud.org] Gesendet: Mittwoch, 02. Juli 2014 18:18 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Problem with many connections On 2 Jul 2014, at 5:03pm, Grzegorz Sikorski g.sikor...@kelvatek.com wrote: I admit I had missed this sentence, but to be honest, it seems to be very confusing behaviour. I'm glad you have found the problem. It's not good and I don't think this is a good way to handle things. I hope SQLite4 changes this and if there are statements still open either returns an error code or automatically closes any open statements. Or both. It would also be nice if there was an API call you could make on a database handle which would return the number of statements which were open on that database. I have no idea what to do about open transactions if their database connection is closed. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with many connections
This function is already available in the sqlite3 C interface. It would be quite easy to implement a virtual table prepared_statements ( db int hidden, stmt int, sql text) to allow select * from prepared_statements where db = ? and bind your db handle. xBestIndex would need to check for constraint db presence xFilter would call sqlite3_next_stmt with P2 = NULL to retrieve the first prepared statement xNext would call sqlite3_next_stmt with P2 = last retrieved statement xColumn would call sqlite3_sql on the last retrieved statement to return the SQL if stored by a prepare_v2 If no parameter db is specified, the table would return no rows, as there is currently no method that I know of available to find all connections. -Ursprüngliche Nachricht- Von: Simon Slavin [mailto:slav...@bigfraud.org] Gesendet: Donnerstag, 03. Juli 2014 09:36 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Problem with many connections On 3 Jul 2014, at 8:24am, Hick Gunter h...@scigames.at wrote: How about this? sqlite3_stmt *sqlite3_next_stmt(sqlite3 *pDb, sqlite3_stmt *pStmt); This interface returns a pointer to the next prepared statement after pStmt associated with the database connection pDb. If pStmt is NULL then this interface returns a pointer to the first prepared statement associated with the database connection pDb. If no prepared statement satisfies the conditions of this routine, it returns NULL. The database connection pointer D in a call to sqlite3_next_stmt(D,S) must refer to an open database connection and in particular must not be a NULL pointer. I don't know how practical that would be to implement, but it looks good to me. If I understand your design properly some users would pass NULL and see if they got NULL back. Others would iterate down the list. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] evaluate expression stored in a column
Not possible in SQL. b.answer || a.epxr is a string. It does not get evaluated. Unless you write your own eval() function that executes its parameter as an SQL statement, which is going to be very slow as each result row needs to prepare, step and finalize its very own statement. -Ursprüngliche Nachricht- Von: Keshav Tadimeti [mailto:ktadim...@aol.com] Gesendet: Mittwoch, 02. Juli 2014 20:18 An: sqlite-users@sqlite.org Betreff: [sqlite] evaluate expression stored in a column Hello all I have the following requirement: Table a id int expr text result integer Insert into a values (10,' 2'); Table b --- id int answer text Insert into b values (10,'10'); I need to evaluate the condition that 10 2 and store the result of that expression in a.result. I am failing to have the expression evaluated. select b.answer || a.expr from a inner join b on a.id = b.id; -- gives 10 2 I need to see 1 (since 10 2). Kindly help!! Best -- Using Opera's mail client: http://www.opera.com/mail/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with many connections
What is your sequence of calls? What do you mean by one connection for the application lifetime and others on demand for each transaction? A connection is created/destroyed (aka opened/closed) with sqlite3_open resp. sqlite3_close calls. This opens/closes the underlying file handles. A statement is created/destroyed with sqlite3_prepare resp. sqlite3_finalize calls. Unfinalized statements will prevent sqlite3_close from working and return an error code. My guess is you are not finalizing your statements and not checking sqlite3_close return status, thus missing SQLite having a lot of open files. Is there a lsof command or a /proc filesystem or equivalent? -Ursprüngliche Nachricht- Von: Grzegorz Sikorski [mailto:g.sikor...@kelvatek.com] Gesendet: Mittwoch, 02. Juli 2014 13:02 An: sqlite-users@sqlite.org Betreff: [sqlite] Problem with many connections Hi, I am not sure if my previous email had reached the list, so I just repeat it: I am developing sharding database using SQLite3 for embedded application. My code works fine up to about 1020 connections to the database. After around this number, I get an error unable to open database file. I double checked, permissions are OK and I think I always properly close all connections and never open the same file twice (I normally keep one connection opened for whole application lifetime and open others on demand for each transaction). I found this topic:http://stackoverflow.com/questions/22801987/sqlite3-unable-to-open-database-file-ios and I am not sure if there is any reason why keeping opened connection in whole application lifetime is really something I should do? I would prefer to open database only when it is needed, to avoid risk of file corruption on power loss. Is there any known issue with multiple open/close operations? Regards, Greg -- ExchangeDefender Message Security: Click below to verify authenticity https://admin.exchangedefender.com/verify.php?id=s62B2QLa005874from=g.sikor...@camlintechnologies.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sequential numbers
-Ursprüngliche Nachricht- Von: RSmith [mailto:rsm...@rsweb.co.za] Gesendet: Mittwoch, 25. Juni 2014 21:54 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Sequential numbers On 2014/06/25 21:38, Dave Wellman wrote: Hi Petite, Many thanks fo rthsuggestion, it works a treat!. Hi Simon, Thanks for the thoughts but in this particular processing that is not going to happen (delete a few rows). In this processing we always empty the table completely before re-populating it. I've gone with the TRIGGEr solution, it works really well. Hi Dave, as a side note - if you never remove rows, AND in stead of emptying the table (Truncation) you actually DROP and Re-CREATE it... then a standard AUTOINCREMENT rowid alias is almost guaranteed to actually produce you sequential numbering from 1 onwards. It really only starts doing funnies when you remove rows - but if you don't break the chain, the rowid won't either. I know the documentation does not guarantee it simply because the behaviour can go haywire after amendments to the table, but I have yet to see a rowid NOT start at 1 in a brand-new table, or indeed just randomly miss a beat as you add rows. never happens - until that first row is deleted. Sometimes however one doesn't intend to delete things, but it happens, so I think the trigger solution by Mr. Bee is still safer - though this would equally mess up the sequence if a deletion happens - BUT, you can just manually go change the values in that column then until they are happy again and the trigger should perform business-as-usual from then on. Just another thought, Have a great day! SQLite will do that automagically http://www.sqlite.org/compile.html#omit_truncate_optimization A default build of SQLite, if a DELETE statement has no WHERE clause and operates on a table with no triggers, an optimization occurs that causes the DELETE to occur by dropping and recreating the table. Dropping and recreating a table is usually much faster than deleting the table content row by row. This is the truncate optimization. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Like and percent character
Probably you are using a variant of the printf() function to generate your statement and it is interpreting the %m as strerror(errno) (see man 3 printf), whereas it is ignoring %' (thousands separator for decimal conversions) either because it does not support this conversion or it is missing the conversion specifier. Try inserting (3,'icka') into your table. If it works in the shell but not in your program, then it is nearly always your program that is to blame. -Ursprüngliche Nachricht- Von: Micka [mailto:mickamus...@gmail.com] Gesendet: Dienstag, 03. Juni 2014 08:58 An: sqlite-users@sqlite.org Betreff: [sqlite] Like and percent character Hi, I'm having trouble with the percent character . By example in my table I have : id name 1 micka 2 mickael I would like to do that : Select * from table name where name LIKE '%micka%' with my linux c program, the result is 0 but with the sqlite3 command program it works I also tested this : Select * from table name where name LIKE 'micka%' and this time, it works in my linux c program ... I'm using the last package of sqlite3-dev ... https://packages.debian.org/wheezy/sqlite3 Why ? Thx you ! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Storing opaque data, but would like sorted indexes on some fields
create table mytable (f1, f2, f3, ..., data blob); -- duplicate the data OR create table mytable (f1, f2, f3, ..., dref integer); -- reference the data (e.g. record position in file) and providing the values of the internal fields on insert -Ursprüngliche Nachricht- Von: Hayden Livingston [mailto:halivings...@gmail.com] Gesendet: Samstag, 31. Mai 2014 04:46 An: General Discussion of SQLite Database Betreff: [sqlite] Storing opaque data, but would like sorted indexes on some fields I have a binary format that is effectively structured data. I currently have multiple indexes but none of them are sorted, because in my toy system ORDER BY's are not supported, an implicit ORDER BY time of record inserted exists because it is a single threaded application. My indexing story is that I know a priori what my index will be, and I just double write them. I want to support ORDER BY on basically one additional field, and I'm having a hard time coming up with a solution that doesn't involved effectively writing on my B+Tree which has knowledge of my serialization binary format. I've thought to myself, maybe I could just store a duplicate copy of the data sorted by this field into SQLite. The data footprint is not small, but I'm willing to pay that disk cost if that's the only option. My only concern with moving to SQLite is performance design decisions that are built into a database. My system is basically only doing the work needed to iterate over my data. No concurrency, sequential reads. When/If I move to SQLite, what kind of things will I start to pay for? Concurrency? Other fluff data structure needs for SQLite? Obviously I imagine that whatever field I choose to get a sorted index on, I expose as it as a field. Then the non SQLite question: how much work would it be to duplicate a B-tree like data structure for my binary data? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple reads and writes to a single DB connection from multiple threads
If you compile with SQLITE_THREADSAFE=1 then multiple calls from different threads will be serialized by SQLite. Serialized means that only one thread at a time will be allowed to run within SQLite; API calls from other threads will block until the currently running thread returns. If your application uses virtual tables implemented with native tables, then there may be an issue with deadlocks (appl - sqlite3_step - virtual table code - sqlite3_step would cause the same thread to recursively enter SQLite). Progress will generally not be faster than using a single threaded approach, unless there is a lot of processing going on outside of SQLite. Sharing one connection between different threads means the threads also share the (implicit or explicit) transaction, i.e. in simple terms, changes written by one thread will immediately be visible to all the other threads and will only be committed if/when the outermost transaction commits. From my reading of the interface spec I can't see that registering an update hook is only allowed on one connection per process. Indeed, there is a parameter to identify the connection the hook should be registered to, which would be rather pointless if only one hook per process were allowed. -Ursprüngliche Nachricht- Von: prashantbkdhas [mailto:prashant.d...@gmail.com] Gesendet: Samstag, 31. Mai 2014 00:25 An: sqlite-users@sqlite.org Betreff: [sqlite] Multiple reads and writes to a single DB connection from multiple threads Hi,I looked at a few of the multithreading docs and posts and it looks like SQLite3 supports reads and writes from multiple threads provided SQLite3 is compile with THREADSAFE option. In this case each thread has it own DB connection. My question is if I have a single DB connection which is used by multiple threads then are simultaneous reads and writes supported from these threads. I understand writes will lock the DB but other than this will this work. Basically are there issues if a read is happening on the DB connection can another read happen in the same DB connection from another thread. On the same thread this is not an issue. I was wondering whether there could be issues when doing from multiple threads.The reason I am sharing a single connection with multiple threads is because I need to listen to the DB change notification. Currently SQLite3 supports listening to notifications from a single connection only. For this reason I am maintaining a single connection shared by multiple threads.ThanksPrashant -- View this message in context: http://sqlite.1065341.n5.nabble.com/Multiple-reads-and-writes-to-a-single-DB-connection-from-multiple-threads-tp75972.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 --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
No. The internal table stores only unique keys. -Ursprüngliche Nachricht- Von: Humblebee [mailto:fantasia.d...@gmail.com] Gesendet: Donnerstag, 22. Mai 2014 11:39 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Simple Select from IN - from a newbie. Thank you everyone for your kind input and suggestions. That is quite a lot to consider. I didn't realize it would be so difficult for a Select statement to return a string. Out of curiosity, when I do: SELECT * FROM Person WHERE id IN(2,2,3,3) It doesn't seem to give back 4 rows, only 2. Looks like the duplicates are not being returned. Is there someway to get back all 4 rows. On 5/22/14, Keith Medcalf [via SQLite] ml-node+s1065341n75767...@n5.nabble.com wrote: Andy Goth wrote: And honestly, please don't give people with no knowledge of SQL theory the power to set your SQL schema in stone. I am sure you mean Relational Theory, when using a database implementing Relational semantics, such as SQLite. There is no requirement that SQL (Structured Query Language) be implemented to query a Relational Database. There are many implementations which use SQL to query data from hierarchical, network, network extended, and a myriad of other underlying database storage mechanisms. SQL no more binds the relational model than using COBOL (a computer programming language) binds the implementation to a 4341 SysPlex running OS/VS1. SQLite implements an SQL interface using a relational access model against an ISAM datastore. Storing mutivalued (array) items is a violation of the Relational Model, not SQL and not ISAM. If you used, for example, ADABAS, then you could store arrays in a table field and perform SQL operations against them as if they were a BCNF normalized relational N:M join table. Some other not-so-relational relational databases support nonstandard means of achieving the same thing. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ If you reply to this email, your message will be added to the discussion below: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbi e-tp75751p75767.html To unsubscribe from Simple Select from IN - from a newbie., visit http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=uns ubscribe_by_codenode=75751code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3N TF8MTk4Njk4NTgwNw== -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75769.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 --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
select p.id,p.name from TeamPersonTable tp join PersonTable p on (p.id = tp.personId) where tp.teamId = 1; -Ursprüngliche Nachricht- Von: Humblebee [mailto:fantasia.d...@gmail.com] Gesendet: Donnerstag, 22. Mai 2014 13:40 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Simple Select from IN - from a newbie. TeamTable +---+ | id | name | +---+ | 1 | blue| | 2 | green | +---| PersonTable +--+ | id | name | +--+ | 4 | john | | 5 | bill | +-+---+ TeamPersonTable +---+ | teamId | personId | +---+ | 1 | 4 | | 1 | 5 | | 2 | 4 | | 2 | 5 | +-+--+ So a person can belong to any teams. Query Input: teamId = 1 Result: personNames = john,bill personIds = 4,5 On 5/22/14, RSmith [via SQLite] ml-node+s1065341n75776...@n5.nabble.com wrote: On 2014/05/22 13:02, Humblebee wrote: @RSmith, Very much appreciate you taking the time to write such a detailed and awesome explanation of how the string and list works in SQL. I had no idea what goes on there. It is my pleasure, sadly I know exactly how it feels to be under the wrong impression (I have been so on this very list!). As for now, I'm following the good advice of all the much more knowledgeable people on this list to normalize the data by adding a Person_Team table to the database. My last question is what SQL statement could I use to get the information out of these 3 tables. That is great news- I promise you will not be sorry. As for the question, once you decided how the tables will look, could you again paste them here with a little bit of example data in each and then say exactly how you want the resulting data to look after the query is run - and we will try suggest the most optimized ways of achieving it. A bit of knowledge about how often the tables will be updated/inserted to, and how often the query will be run, and how much every table is expected to grow over time, would all help to make the best decisions. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ If you reply to this email, your message will be added to the discussion below: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbi e-tp75751p75776.html To unsubscribe from Simple Select from IN - from a newbie., visit http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=uns ubscribe_by_codenode=75751code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3N TF8MTk4Njk4NTgwNw== -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75778.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 --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
You show a field parId in your TeamTable, but select it from the PersonTable. Maybe you mean SELECT * FROM PersonTable WHERE id IN (SELECT personIDs FROM TeamTable WHERE parId = 4); -Ursprüngliche Nachricht- Von: fantasia dosa [mailto:fantasia.d...@gmail.com] Gesendet: Mittwoch, 21. Mai 2014 15:00 An: sqlite-users@sqlite.org Betreff: [sqlite] Simple Select from IN - from a newbie. Hi all wonderful people on this list. I'm a newbilie so my questions might seem very well-- kinda dumb so please forgive me. I'm trying to do the following in Sqlite. TeamTable - parId: 4 personIDs : 1,5,9,6 PersonTable - id: name: SELECT * FROM PersonTable WHERE id IN (SELECT personIDs FROM PersonTable WHERE parId = 4); The result from the above query is empty whereas when I do: SELECT * FROM PersonTable WHERE id IN(1,5,9,6) Then I get the result that I need. Any help is greatly appreciated. Cheers. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and collations
Actually SQLite does support X'...' literals for creating blobs.
-Ursprüngliche Nachricht-
Von: Dominique Devienne [mailto:ddevie...@gmail.com]
Gesendet: Dienstag, 13. Mai 2014 18:19
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] LIKE operator and collations
On Tue, May 13, 2014 at 5:50 PM, Jan Slodicka j...@resco.net wrote:
So one could replace LIKE 'xxx%' by BETWEEN('xxx', 'xxx' + '\uDBFF\uDFFD').
make that
BETWEEN('xxx', 'xxx' + char(1114109))
I don't think SQlite supports \u literals, nor does it support hex
literals, so must use the decimal equivalent to U+10FFFD.
C:\Users\DDeviennesqlite3
SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter .help for usage hints.
Connected to a transient in-memory database.
Use .open FILENAME to reopen on a persistent database.
sqlite select '\uDBFF\uDFFD';
\uDBFF\uDFFD
sqlite select char(1114109);
􏿽
sqlite select char(0x10FFFD);
Error: unrecognized token: 0x10FFFD
sqlite select typeof(char(1114109));
text
sqlite select length(char(1114109));
1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
---
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any
purposes, or disclose its contents to any person as to do so could be a breach
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and collations
I was under the impression you wanted to achieve this:
asql select hex('abc' || X'10FFFD');
hex('abc' || X'10FFFD')
---
61626310FFFD
asql select length('abc' || X'10FFFD');
length('abc' || X'10FFFD')
--
6
asql select typeof('abc' || X'10FFFD');
typeof('abc' || X'10FFFD')
--
text
-Ursprüngliche Nachricht-
Von: Dominique Devienne [mailto:ddevie...@gmail.com]
Gesendet: Mittwoch, 14. Mai 2014 09:28
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] LIKE operator and collations
On Wed, May 14, 2014 at 8:30 AM, Hick Gunter h...@scigames.at wrote:
Actually SQLite does support X'...' literals for creating blobs.
Note sure how that's relevant Hick. We don't need a blob, but a integer for
char(). I was obviously talking about *number* literals (prefixed with 0b, 0,
0x for binary / octal / hexa), not blob literals.
I'd be +1 to having char() also accept blobs (1 to 3 bytes long) instead of
integer, since x'10FFFD' is closer to U+10FFFD than 1114109 is.
But Dr. Hipp would need to agree with that. Until then, 1114109 it must be. --DD
C:\Users\DDeviennesqlite3
SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter .help for usage hints.
Connected to a transient in-memory database.
Use .open FILENAME to reopen on a persistent database.
sqlite select char(x'10FFFD');
sqlite select typeof(char(x'10FFFD'));
text
sqlite select length(char(x'10FFFD'));
0
sqlite
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
---
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any
purposes, or disclose its contents to any person as to do so could be a breach
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I know the python functions registered on SQLite ?
We register our defined functions in a linked list in memory for the same
reason (and have also implemented a .func pattern in the shell to list
all/matching registered functions)
-Ursprüngliche Nachricht-
Von: big stone [mailto:stonebi...@gmail.com]
Gesendet: Donnerstag, 08. Mai 2014 18:35
An: sqlite-users@sqlite.org
Betreff: [sqlite] How do I know the python functions registered on SQLite ?
Hello,
Is there a way to get the list of all the 'external' functions created in a
SQLite connexion ?
(maybe there is no solution except a manual housekeeping)
#* (example of external function creation in Python) import sqlite3 conn =
sqlite.connect(:memory:) def mysqrt(s):
return (%s %s**.5);
conn.create_function('mysqrt', 1, mysqrt)
#*
Regards,
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
---
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any
purposes, or disclose its contents to any person as to do so could be a breach
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WITHOUT ROWID option
Judging from the documentation there is not (optional methods may have a NULL pointer in the method table). Maybe this will/has change(d) 2.12 The xRowid Method int (*xRowid)(sqlite3_vtab_cursor *pCur, sqlite_int64 *pRowid); A successful invocation of this method will cause *pRowid to be filled with the rowid of row that the virtual table cursor pCur is currently pointing at. This method returns SQLITE_OK on success. It returns an appropriate error code on failure. The xRowid method is required for every virtual table implementation. -Ursprüngliche Nachricht- Von: Max Vlasov [mailto:max.vla...@gmail.com] Gesendet: Freitag, 09. Mai 2014 12:10 An: General Discussion of SQLite Database Betreff: Re: [sqlite] WITHOUT ROWID option On Wed, May 7, 2014 at 6:31 PM, Richard Hipp d...@sqlite.org wrote: On Wed, May 7, 2014 at 9:00 AM, Marco Bambini ma...@sqlabs.net wrote: What is the best way to know if a table has been created with the WITHOUT ROWID option? (1) You could send SELECT rowid FROM table (2) Run both PRAGMA index_list(table) Is there a way for a virtual table implementation to report that there's no rowid support before first xRowId call takes place? Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] duplicate row in sqlite3 database
Look into the ON CONFLICT clause -Ursprüngliche Nachricht- Von: techi eth [mailto:techi...@gmail.com] Gesendet: Donnerstag, 08. Mai 2014 11:14 An: General Discussion of SQLite Database Betreff: [sqlite] duplicate row in sqlite3 database Hi, SQlite3 have any method where it can avoid adding duplicate row or throwing error status code on duplication., Techi- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Storing amount?
SQLite does not care about (or enforce) magnitude and precision hints. numeric alone is just as good, or even integer. Textual values that look like numbers will be stored as integer (if they evaluate to a whole number) or real (if not). Increasing a salary of 5000 by 3% will result in a change of the stored type. -Ursprüngliche Nachricht- Von: Werner Kleiner [mailto:sqliteh...@web.de] Gesendet: Donnerstag, 08. Mai 2014 13:42 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Storing amount? Hello Simon, thanks for help. I use the PDO library. The typeOf results in real If I change the swsalary column to numeric(10,0) it results in integer and then the value of 5 ist stored correct without . (dot). So is it better to take datatype numeric for storing amounts without dots or floating points? (In the textfield I have forbitten to fill in non numeric characters, only numbers allowed) regards Werner -- View this message in context: http://sqlite.1065341.n5.nabble.com/Storing-amount-tp75527p75529.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 --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it better that sqlite support share One Database Connection In Multithreads?
Imagine the following sequence on a multi thread shared connection. Thread A prepares a SELECT statement Thread A steps the statement a couple of times to retrieve some data Thread B comes along an finalizes the statement What do you propose should happen when thread A tries to step the statement? What if thread A is in the middle of INSERT ... SELECT when B commits? -Ursprüngliche Nachricht- Von: NULL [mailto:the-figh...@qq.com] Gesendet: Sonntag, 04. Mai 2014 11:55 An: drh; sqlite-users Betreff: [sqlite] Is it better that sqlite support share One Database Connection In Multithreads? Hi, In sqlite's Document,it's said that any time you can't share ONE database connection across more than one threads.In my mind,this restrict is much bad,expecially for using memory based database. So,can the changes come at next version? Zhouran 05/04/2014 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_text issue
-Ursprüngliche Nachricht- Von: lyx [mailto:sdu...@163.com] Gesendet: Montag, 05. Mai 2014 05:00 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] sqlite3_bind_text issue I have tried to use SQL_TRANSIENT instead of SQLITE_STATIC in sqlite3_bind_text. But the result is still not correct. The column num is correct now but the row number embedded in column data in every row is all assigned to zero now. It should be increased row by row by my program. Can anyone throw some light on this issue? Thanks. SQLITE_DONE means that the statement has finished executing successfully. sqlite3_step() should not be called again on this virtual machine without first calling sqlite3_reset() to reset the virtual machine back to its initial state. --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in division?
BTW: The FROM clause is optional in SQLite. It is required only for expressions involving fields. SELECT 2/4 AS RESULT; Will also work, without the overhead of accessing every row of TABLE and performing DISTINCT processing and maybe even without obtaining a lock on the database. -Ursprüngliche Nachricht- Von: Gene Connor [mailto:neothreeei...@hotmail.com] Gesendet: Mittwoch, 30. April 2014 06:21 An: sqlite-users@sqlite.org Betreff: [sqlite] Bug in division? SELECT DISTINCT 2/4 AS RESULT FROM TABLE;returns 0 SELECT DISTINCT 2/4.0 AS RESULT FROM TABLE;returns 0.5 (correct) SELECT DISTINCT 2.0/4 AS RESULT FROM TABLE;returns 0.5 (correct) SELECT DISTINCT 2.0/4.0 AS RESULT FROM TABLE;returns 0.5 (correct) SELECT DISTINCT 2.0/4.01 AS RESULT FROM TABLE;returns 0.49875 (correct) As long as one or both numbers has at least one decimal place, it calcs correctly. Verified that it also happens in queries using tables and real data. Windows SQLite version 3.8.4.3 2014-04-03 16:53:12 Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
The vtable split method will happily accept a field from a join as in Select t.key,c.value from table t cross join cmlist on c.commalist=t.field; Virtual tables don't declare virtual indices; they return an index number and an index string from their BestIndex method. -Ursprüngliche Nachricht- Von: Dominique Devienne [mailto:ddevie...@gmail.com] Gesendet: Samstag, 05. April 2014 10:24 An: General Discussion of SQLite Database Betreff: Re: [sqlite] comma-separated string data On Saturday, April 5, 2014, Max Vlasov max.vla...@gmail.com wrote: On Fri, Apr 4, 2014 at 10:20 PM, peter korinis kori...@earthlink.netjavascript:; wrote: A data column in a link table contains comma-separated string data, where How do you 'parse' a table entry like: 4,66,51,3009,2,678, . to extract these values and use them in an SQL statement, perhaps a WHERE id='66'? In similar cases I use my virtual table explained here: http://www.mail-archive.com/sqlite-users@sqlite.org/msg63453.html Actually the table works more like function, so only one instance is required in the db to apply this trick. [...] This trick successfully works with joins and everything else. I don't think it works in this case Max, because your technique relies on the where clause being a literal, whereas here, if I'm reading between the lines correctly, the poster wants the the equivalent of Oracle's TABLE() operator. In this case, a vtable can still help, but one specific to the source table, with only the source table's PK columns plus the one to un-nest / parse. Basically xNext behaves like a compound iterator, with the outer iterator scanning the source table (using normal SQL and the SQLite API), and the inner iterator returning the CSV values one at a time of the current outer iterator's value / row. That's basically normalizing on the fly. The vtable should ALSO declare an index on the PK columns to avoid full scans with a where clause or a join. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Snippet function with Distinct query fails
My guess is that your windows version does not have the fts3 extension loaded/compiled in. -Ursprüngliche Nachricht- Von: Gopalan M [mailto:mgopa...@sofist.com] Gesendet: Mittwoch, 19. März 2014 10:46 An: sqlite-users@sqlite.org; supp...@sqlite.org Betreff: [sqlite] Snippet function with Distinct query fails Sir, am using the Snippet function in my SQL select command to get a list of snippets matching my text search. This query works fine under LINUX. *QUERY* select distinct judge, snippet(VjudgeS,'b','/b') from vjudges where judge MATCH 'Shah' However, it fails in windows. Please let me know what the solution is. Here the error when i execute the query in Windows *Error* SQL logic error or missing database. Thanks Gopalan M mgopa...@sofist.com Phone: 91-44-28155334/28150085 Fax: 91-44-28155336 70, Thyagaraya Rd. T. Nagar Chennai 600017 Tamil Nadu, India www.sofist.com -- Looking for a college degree? Your one-stop SAT preparation solution: www.SATWhiz.comhttp://www.satwhiz.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to query key-value-pairs similar to PostgreSQL HSTORE type?
A virtual table would do the trick. CREATE VIRTUAL TABLE IF NOT EXISTS key_val USING keyval; Inside the xCreate function you would call sqlite3_declare_vtab with CREATE TABLE x (key text, value text, _content hidden text); Then you can SELECT value FROM key_val WHERE _content = 'your string' and key = 'some_key'; Or even SELECT ... k.value ... FROM table t CROSS JOIN key_val k ON (_content = t.field and key = 'some_key'); The xFilter function needs to store/parse the string, xNext move to the next key/value pair and xColumn return the current key and or value. -Ursprüngliche Nachricht- Von: Stefan Keller [mailto:sfkel...@gmail.com] Gesendet: Dienstag, 18. März 2014 02:47 An: General Discussion of SQLite Database; spatialite-us...@googlegroups.com Betreff: [sqlite] How to query key-value-pairs similar to PostgreSQL HSTORE type? Hi, I have a column which contains a string structure taken from the PostgreSQL HSTORE key-value-pairs type. This is an example of one column value: operator=police,name=Zurich,some_key=some_value Any suggestions on how to query this most efficiently (like [select value from some_key])? Yours, Stefan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error Database or disk if full shown when indexing for 2 billion records.
I think the idea was to
SELECT md5_str FROM table WHERE rowid = ?;
instead of
SELECT md5('mm' || ?);
or even
SELECT rowid FROM table WHERE md5_str = ?;
which is a fast way of finding the inverse function for md5.
-Ursprüngliche Nachricht-
Von: RSmith [mailto:rsm...@rsweb.co.za]
Gesendet: Montag, 17. März 2014 14:39
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Error Database or disk if full shown when indexing for
2 billion records.
That's insane... well done :)
To create the Index you will need at least as much disk space as already
used... so you will need another 87GB (approx), not just another 50GB.
And it will take no longer to create the table + Index from the start than it
will take to create first the table, then the index - plus you will need less
overall disk-space if you do it from the start, but much more than 87GB... try
making the table without the Rowid.. that should save you a lot:
CREATE TABLE t (str_md5 VARCHAR(32) PRIMARY KEY COLLATE NOCASE) WITHOUT ROWID;
That should work the best for md5 hashes and might even get to under your GB
total space (including the key).
What I would do is create that table, then add only 1 million entries indexed
and all... see the total size, multiply it with the amount of entries you need
in total, as a rough guide to how much you can put in the space.
HTH!
Ryan
On 2014/03/17 15:32, Yi Wang wrote:
I inserted 2 billion records with only 1 column with name of
str_md5, the value is the MD5 encrypted value of mm+rowid(such like
MD5(mm121212...).
I didn't not create primary key for the only column b/c i am not sure
it would slow the speed of insert command. The whole insert of 2
billion records took me over 18hours.
But when I hope to index the column with the sql command of
”create index tableMD5_idx on tableMD5(on str_md5);
The index command ran for around 1 hour and then the error Database
or disk if full shown up.
FYI: The sqlite file only contains that only 1 table with 1 column.
The size of the DB file took around 87G disk space.
And the disp space is still 50G more free space to reach full. So I am
not sure whether it's the space problom b/c according to my previous
experience, the index command would increase around 1/3 size of the
currnet DB file (I took a test DB with one hundres million records
which took abour 7G space, and after the index command the final size
reach to 9G around.)
So any ideas? Thanks in advance.
Yi.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
---
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any
purposes, or disclose its contents to any person as to do so could be a breach
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3.exe file not produced by sqlite-autoconf-3080400.tar.gz on Cygwin
That is because you are statically linking the SQLite shell and the SQLite
library into a single executable file instead of having the shell (sqlite3.exe)
and the library (sqlite3.dll?) in separate files.
-Ursprüngliche Nachricht-
Von: Keith Christian [mailto:keith1christ...@gmail.com]
Gesendet: Dienstag, 11. März 2014 17:11
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] sqlite3.exe file not produced by
sqlite-autoconf-3080400.tar.gz on Cygwin
Richard,
Thanks for the reply. Your instructions produced a working sqlite3.exe in the
Cygwin environment, using sqlite-amalgamation-3080401.zip.
The resulting file is quite large, almost 14 times the size of the
sqlite3 version 3.8.3 packages with Cygwin:
ls -l /usr/bin/sqlite3.exe
-rwxr-xr-x 1 kchris Domain Users 60957 Feb 4 04:45 /usr/bin/sqlite3.exe
ls -l ./sqlite3.exe
-rwxr-xr-x 1 kchris Domain Users 845353 Mar 11 10:05 ./sqlite3.exe
But it works!
./sqlite3.exe
SQLite version 3.8.4.1 2014-03-11 15:27:36 Enter .help for usage hints.
Connected to a transient in-memory database.
Use .open FILENAME to reopen on a persistent database.
sqlite .quit
Keith
On Tue, Mar 11, 2014 at 8:30 AM, Richard Hipp d...@sqlite.org wrote:
On Tue, Mar 11, 2014 at 9:46 AM, Keith Christian
keith1christ...@gmail.comwrote:
A few days ago, I successfully compiled the previous version of the
autoconf tar package (sqlite-autoconf-3080300.tar.gz) and a
sqlite3.exe file was produced on a Cygwin environment.
This morning, I downloaded sqlite-autoconf-3080400.tar.gz, unpacked,
ran 'make clean' and 'make', but no sqlite3.exe file was produced.
I'm sorry you are having trouble.
I don't know what is wrong because everything works fine when I try it.
As an alternative, consider downloading
sqlite-amalgamation-3080400.zip and then running:
gcc -o sqlite3.exe -I. sqlite3.c shell.c
I believe the issue is in some changes in the Makefiles produced
between 3080300 and 3080400.
Makefile.am and Makefile.in are identical between 3080300 and 3080400.
The cygwin environment on the machine has not been updated since
3080300 was compiled on 7 Mar.
See the output of 'wdiff' below. Recent versions of the VIM editor
have a nice syntax mode for wdiff, copy the output below into VIM and
then type syn on and set filetype=wdiff at the colon prompt.
wdiff -s ../../sqlite-autoconf-3080300/tea/Makefile
../../sqlite-autoconf-3080400/tea/Makefile|grep \[-.*-\]|sed 'G;G'
PKG_LIB_FILE= [-libsqlite3.8.3.dll-] {+libsqlite3.8.4.dll+}
PKG_STUB_LIB_FILE = [-libsqlitestub3.8.3.a-] {+libsqlitestub3.8.4.a+}
INSTALL = [-/usr/bin/install-] {+$(SHELL)
$(srcdir)/tclconfig/install-sh+} -c
PACKAGE_VERSION = [-3.8.3-] {+3.8.4+}
#DEFS = $(TCL_DEFS) -DPACKAGE_NAME=\sqlite\
-DPACKAGE_TARNAME=\sqlite\ [--DPACKAGE_VERSION=\3.8.3\-]
{+-DPACKAGE_VERSION=\3.8.4\+} -DPACKAGE_STRING=\sqlite\
[-3.8.3\-] {+3.8.4\+} -DPACKAGE_BUGREPORT=\\ -DPACKAGE_URL=\\
{+-DBUILD_sqlite=/\*\*/+} -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
-DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
-DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1
-DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DNO_VALUES_H=1 -DHAVE_LIMITS_H=1
-DHAVE_SYS_PARAM_H=1 -DUSE_SYSTEM_SQLITE=1 -DUSE_THREAD_ALLOC=1
-D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1
-DMODULE_SCOPE=extern [--DNO_VIZ=1-] {+-DHAVE_NO_SEH=1
-DHAVE_CAST_TO_UNION=1+} -DTCL_WIDE_INT_TYPE=long\ long
-DUSE_TCL_STUBS=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1
$(PKG_CFLAGS)
DEFS= -DPACKAGE_NAME=\sqlite\
-DPACKAGE_TARNAME=\sqlite\ [--DPACKAGE_VERSION=\3.8.3\-]
{+-DPACKAGE_VERSION=\3.8.4\+} -DPACKAGE_STRING=\sqlite\
[-3.8.3\-] {+3.8.4\+} -DPACKAGE_BUGREPORT=\\ -DPACKAGE_URL=\\
{+-DBUILD_sqlite=/\*\*/+} -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
-DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
-DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1
-DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DNO_VALUES_H=1 -DHAVE_LIMITS_H=1
-DHAVE_SYS_PARAM_H=1 -DUSE_SYSTEM_SQLITE=1 -DUSE_THREAD_ALLOC=1
-D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1
-DMODULE_SCOPE=extern [--DNO_VIZ=1-] {+-DHAVE_NO_SEH=1
-DHAVE_CAST_TO_UNION=1+} -DTCL_WIDE_INT_TYPE=long\ long
-DUSE_TCL_STUBS=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1
$(PKG_CFLAGS)
Keith
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
D. Richard Hipp
d...@sqlite.org
___
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] Virtual table API performance
Your VT1 table already has an xColumn implementation, possibly doing something
like
switch( p_column )
{
case 1: sqlite3_result_xxx( p_ctx, v_rec-f1, ...); break;
...
case n: sqlite3_result_xxx( p_ctx, v_rec-fn, ...); break;
}
This needs to have two cases added:
case n+1: sqlite3_result_int64( p_ctx, (uintptr_t)v_rec );
case n+2: sqlite3_result_int64( p_ctx, (uintptr_t)func );
where
static int func( p_rec, p_ctx, p_column );
calls
xColumn( v_cursor, p_ctx, p_column );
with a dummy cursor structure as defined for your table.
The VT2 table can then prepare select __rec,__func from VT1, and in its
xColumn implementation it calls
v_rec = (void *)sqlite3_column_int64( v_stmt, 0 ); // this can be stored
and cleared in the xNext function
v_func = (func *)sqlite3_column_int64( v_stmt, 1 ); // this can be
stored
v_func( v_rec, p_ctx, p_column );
As for your second example, as written it does not suffer from the effect
because you are already selecting c1, c2 and c3 at the bottom level.
Rewritten as
Select processrow(c1,c2,c3) from VT2(select * from VT1);
results in the VT1 xColumn function getting called (via the VT2 xColumn
function) just 3 times per row.
Additionally, you may like to select __func from VT1 limit 1 and store that
in your xFilter implementation; and then select __rec from VT1 in your xNext
implementation to have sqlite3_result_int64() called half as often.
HTH
-Ursprüngliche Nachricht-
Von: Eleytherios Stamatogiannakis [mailto:est...@gmail.com]
Gesendet: Dienstag, 04. März 2014 14:15
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Virtual table API performance
Could you explain some more your solution?
Does it work in this following case:
select * from VT2(select * from VT1);
by directly passing the rows from VT1 to VT2 (short-circuiting SQLite)?
What would happen in the following case?:
select * from VT2(select processrow(c1,c2,c3) from VT1);
Regards,
l.
On 03/03/14 14:17, Hick Gunter wrote:
We have gotten around this problem by defining virtual fields that contain
a reference to the current record and the entrypoint of a wrapper around
the xColumn function. That way only two fields get passed upwards through the
virtual table stack and the top level virtual table's xColumn implementation
calls straight through to the bottom layer's wrapper.
It does take some care to avoid sorting in between the layers and
re-preparation of statements on schema changes.
-Ursprüngliche Nachricht-
Von: Elefterios Stamatogiannakis [mailto:est...@gmail.com]
Gesendet: Sonntag, 02. März 2014 20:39
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Virtual table API performance
We have both input and output virtual tables that avoid hitting the hard disk
and are also able to compress the incoming and outgoing data.
We have a virtual table that takes as input a query and sends the data to a
port on another machine. This virtual table is called OUTPUT. And another
virtual table that takes as input data from another port and forwards it into
SQLite. Lets call it INPUT. A query that uses these two virtual tables
would look like this in madIS:
OUTPUT ip:192.168.0.1 port:8080 select * from INPUT('port:8081');
We actually use queries like above (actually we don't do it directly to ports
but to buffered named pipes that are then forwarded via netcat) to run
distributed queries on clusters, connecting all the local SQLite/madIS
instances on the different machines together.
The main point that i want to make with above explanation is that we don't
view SQLite only as a traditional database. We also view it as a data stream
processing machine, that doesn't have the requirement for the data to be
stored on a hard disk.
Under this view, the efficiency of the virtual table api is very important.
Above query only uses 2 VTs in it, but we have other queries that use a lot
more VTs than that.
estama
On 2/3/2014 9:34 ìì, Max Vlasov wrote:
On Sun, Mar 2, 2014 at 5:21 PM, Elefterios Stamatogiannakis
est...@gmail.com wrote:
Our main test case is TPCH, a standard DB benchmark. The lineitem
table of TPCH contains 16 columns, which for 10M rows would require
160M xColumn callbacks, to pass it through the virtual table API.
These callbacks are very expensive, especially when at the other end
sits a VM (CPython or PyPy) handling them.
Ok, not stating that the performance improvment is impossible, I will
explain why I'm a little sceptical about it.
For every bulk insert we have a theoretical maxiumum we'd all glad to
see sqlite would perform with - the speed of simple file copying.
Sqlite can't be faster than that, but to be on par is a good goal.
This is not possible when an insert means also modification of other
parts of the file, for example when there's an index involved
Re: [sqlite] Virtual table API performance
My guess: Yes. It would require implementing an new opcode, either only for virtual tables or also for native tables too, that accepts a list of field numbers (currently there are only 5 parameters possible for an opcode and some of them have fixed meanings). And the logic to generate theses opcodes based on the capabilities of the loaded table module combined with the requirements of the subject query (fields required for JOIN are fetched separately from those required for the result set) and the result of the xBestIndex calls (where it is possible to set the omit flag to suppress generation of a comparison). This also adds to the complexity of register allocation. Take for example a join that needs 3 fields for the comparison, 2 of which are also required for the result set of 7 fields total. Do you request all 10 fields at once (wastes up to 9 fields worth of effort if the JOIN is not met)? Or the 3 fields first and the 5 others only if the join matches (must allocate consecutive registers to build a result set)? Or 3 first and then 7 (which approximates the current behavior, as the 2 common fields are fetched twice on a match)? And a set of new sqlite3_result routines that specify which of the various requested fields' value is being set. -Ursprüngliche Nachricht- Von: J. Merrill [mailto:j.merr...@enlyton.com] Gesendet: Dienstag, 04. März 2014 16:23 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Virtual table API performance Eleytherios Stamatogiannakis wrote Our main test case is TPCH, a standard DB benchmark. The lineitem table of TPCH contains 16 columns, which for 10M rows would require 160M xColumn callbacks, to pass it through the virtual table API. These callbacks are very expensive, especially when at the other end sits a VM (CPython or PyPy) handling them. Would it be very difficult to arrange for an option that would request that SQLite issue a single more-complex xMultiColumns (a sample name) callback request, with a way for multiple results to be returned, rather than many xColumn callbacks? This would reduce the number of calls across the VM boundary. Applications that don't implement xMultiColumns (and request its use) would see no change; those that do would get the performance boost. J. Merrill -- View this message in context: http://sqlite.1065341.n5.nabble.com/Why-does-type-affinity-declared-on-a-foreign-key-column-affect-join-speed-tp74183p74295.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 --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual table API performance
We have gotten around this problem by defining virtual fields that contain a
reference to the current record and the entrypoint of a wrapper around the
xColumn function. That way only two fields get passed upwards through the
virtual table stack and the top level virtual table's xColumn implementation
calls straight through to the bottom layer's wrapper.
It does take some care to avoid sorting in between the layers and
re-preparation of statements on schema changes.
-Ursprüngliche Nachricht-
Von: Elefterios Stamatogiannakis [mailto:est...@gmail.com]
Gesendet: Sonntag, 02. März 2014 20:39
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Virtual table API performance
We have both input and output virtual tables that avoid hitting the hard disk
and are also able to compress the incoming and outgoing data.
We have a virtual table that takes as input a query and sends the data to a
port on another machine. This virtual table is called OUTPUT. And another
virtual table that takes as input data from another port and forwards it into
SQLite. Lets call it INPUT. A query that uses these two virtual tables would
look like this in madIS:
OUTPUT ip:192.168.0.1 port:8080 select * from INPUT('port:8081');
We actually use queries like above (actually we don't do it directly to ports
but to buffered named pipes that are then forwarded via netcat) to run
distributed queries on clusters, connecting all the local SQLite/madIS
instances on the different machines together.
The main point that i want to make with above explanation is that we don't view
SQLite only as a traditional database. We also view it as a data stream
processing machine, that doesn't have the requirement for the data to be stored
on a hard disk.
Under this view, the efficiency of the virtual table api is very important.
Above query only uses 2 VTs in it, but we have other queries that use a lot
more VTs than that.
estama
On 2/3/2014 9:34 ìì, Max Vlasov wrote:
On Sun, Mar 2, 2014 at 5:21 PM, Elefterios Stamatogiannakis
est...@gmail.com wrote:
Our main test case is TPCH, a standard DB benchmark. The lineitem
table of TPCH contains 16 columns, which for 10M rows would require
160M xColumn callbacks, to pass it through the virtual table API.
These callbacks are very expensive, especially when at the other end
sits a VM (CPython or PyPy) handling them.
Ok, not stating that the performance improvment is impossible, I will
explain why I'm a little sceptical about it.
For every bulk insert we have a theoretical maxiumum we'd all glad to
see sqlite would perform with - the speed of simple file copying.
Sqlite can't be faster than that, but to be on par is a good goal.
This is not possible when an insert means also modification of other
parts of the file, for example when there's an index involved. But
let's forget about it. Finally when new data is added, sqlite should
write a number of database pages, the cost of this part is absolutely
in the hands of the media (driver) and OS (driver). But for every
database page write there's also price to pay in CPU units, for many
actions sqlite should do before actual value is translated from what
the developer provided to what actually appears on disk.
The illustration of the CPU price is the following example
CREATE TABLE t(Value)
on my ssd drive mulitply inserts (thousands)
insert into t (Value) values ('123456689 // this string
contains many symbols, for example 1024) performed with the speed
30 MB/Sec
but the query
insert into t (Value) values (10) // this is a small integer
value only
3 Mb/Sec
Both shows almost full cpu load. Why such difference? Because with
latter query the system can do more than 30 MB of writes in 1 second,
but it should wait for sqlite spending 10 seconds in preparations.
The former is better because CPU cost of passing a large text value to
sqlite is comparatively low comparing to the time spent in I/O in
writing this on disk.
So CPU price to pay isn't avoidable and notice that in example this is
not virtual table API, this is bind API. I suppose that the price we
pay for CPU spent in virtual table API is on par with an average price
payed in sqlite as a whole. This means that if I transfom the avove
queries into inserts from virtual tables, the final speed difference
will be similar. And this also means that for your comparision tests
(when you get x3 difference), the CPU price sqlite pays inside bind
api and in its code wrapping xColumn call is probably similar. The
rest is the share your code pays.
Well, I know that there are differences in CPU architectures and
probably there are platform where compiled code for bind api and
virtual tables api behaves a little differently making the costs more
diffrent. But imagine that hard task of fine tuning and refactoring
just to get a noticeable difference for a particular platform.
Max
Re: [sqlite] Network Storage
As a quick search will reveal, many network file systems have broken locking. This can and will lead to database corruption as soon as more than one process attempts to write to the database because SQLite relies on file locking to implement transactions. SQLite will happily open whatever path you provide for the database file. -Ursprüngliche Nachricht- Von: Richard Schülein [mailto:r...@noveltech.de] Gesendet: Montag, 24. Februar 2014 13:08 An: sqlite-users@sqlite.org Betreff: [sqlite] Network Storage Hi, i have an general question regarding SQLite and Network. As I can read here http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork there can be problems with that…. Do anybody have experience on that. We use SQLLite on an embedded Linuxsystem on an Audioserver which store his files on local harddisc and this device is also using SQLLite on the internal harddisc. Currently we think about to support also support for Network Storages (NAS, SMB-Share etc). Is there anything needed to store the Database itself on a shared network devices? In my opinion this is similar to an USB drive etc. That means, that the drive don’t even know, where the database is stored. Or is there really a difference between local storage and network storage from the view on the Database access? Best regards/Mit freundlichen Grüßen Richard Schülein Novel-Tech GmbH Am Krautgarten 4 D-91717 Wassertrüdingen Germany fon: +49 (0) 9832 / 706 814 fax: +49 (0) 9832 / 706 818 HRB 4282 Amtsgericht Ansbach www.noveltech.de http://www.noveltech.de/ email: r...@noveltech.de USt-ID DE814809232 WEEE-Reg.Nr. DE82764730 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
