Re: [sqlite] Please fix the EBCDIC support

Please try the latest version of SQLite on trunk to see if that works better. Specifically, apply the patch at http://www.sqlite.org/src/vpatch?from=b2c89ef49cd1=ef30e0352b3d -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list

Re: [sqlite] 50% faster than 3.7.17

Interesting. From that code you might want to try something like this: SELECT uid, vcard, bdata FROM folder_id WHERE uid in ( select uid FROM email_list where value like 'p%' union select uid from folder_id where nickname LIKE 'p%' union

Re: [sqlite] Please fix the EBCDIC support

Dr. Hipp, I did the "port" (which was easy thanks to your excellent work with EBCDIC which is already in the base) which is being distributed via the CBTTape (http://cbttape.org) web site. I would be happy to test any fixes which might desired, if that is in accordance with your policy. I can

Re: [sqlite] Division accuracy

52 >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of RSmith >Sent: Wednesday, 24 September, 2014 10:49 >To: sqlite-users@sqlite.org >Subject: [sqlite] Division accuracy > >I'm trying to find what the limit is for dividing

Re: [sqlite] sqlite generate strange field name in union

On Wed, Sep 24, 2014 at 12:48 PM, 麦田观望者 wrote: > if we have to tables: > create table t1(f1 integer,f2 integer); > create table t2(f1 integer,f2 integer);‍ > > the fellowing sql generate a result set with a strange field name: > > select t1.f1 from t1 > union > select t2.f1 from

Re: [sqlite] Please fix the EBCDIC support

On Wed, Sep 24, 2014 at 10:27 AM, k wrote: > SQLite team, > compile of sqlite3 v3.8.6 amalgamation failed on zOS 2.1 with err CCN3045 > undeclared identifier sqlite3CtypeMap in function > sqlite3_compileoption_used. I notice that this was raised previously to the > mailing

Re: [sqlite] sqlite generate strange field name in union

On 2014/09/24 18:48, 麦田观望者 wrote: if we have to tables: create table t1(f1 integer,f2 integer); create table t2(f1 integer,f2 integer);‍ the fellowing sql generate a result set with a strange field name: select t1.f1 from t1 union select t2.f1 from t2 ‍ we expect a column named "f1" but we

[sqlite] sqlite generate strange field name in union

if we have to tables: create table t1(f1 integer,f2 integer); create table t2(f1 integer,f2 integer);‍ the fellowing sql generate a result set with a strange field name: select t1.f1 from t1 union select t2.f1 from t2 ‍ we expect a column named "f1" but we get "t1.f1" but "select t1.f1

[sqlite] Please fix the EBCDIC support

SQLite team, compile of sqlite3 v3.8.6 amalgamation failed on zOS 2.1 with err CCN3045 undeclared identifier sqlite3CtypeMap in function sqlite3_compileoption_used. I notice that this was raised previously to the mailing list by John McKown. Please consider adding the necessary fixes to the

Re: [sqlite] Division accuracy

On Wed, 24 Sep 2014 20:51:38 +0200 RSmith wrote: > I was thinking in stead of maybe having a prev and next column, to > just have a next column which points to an ID. ... > ID | Next | Data > 1 | 4 | 'First Row' > 2 | 3 | 'Eventual Fourth Row' > 3 | 1 | 'Last

Re: [sqlite] Crash when binding default column value

At 21:53 24/09/2014, you wrote: > If the default cannot be represented exactly, its rounding will be the > least of the difficulties. Not always: in scientific applications I've had column values default to plenty of irrational numbers: fractions of pi, sqrt(2.0) etc. My main gripe is when

Re: [sqlite] Division accuracy

RSmith wrote: > On 2014/09/24 22:24, Clemens Ladisch wrote: >> RSmith wrote: >>> I'm liking the link list but did not go with it due to an expensive insert >>> function >> >> Yes, you have to update two references (with prev/next), but how is that >> worse than the update of all SortOrder values?

Re: [sqlite] Crash when binding default column value

On Wed, 24 Sep 2014 20:53:32 +0100 Nathaniel Trellice wrote: > > > You are doing CREATE TABLE statements based on text from an > > > untrusted user? Really? > > > My reaction exactly. > > > I'm writing a library so the safety of the input is out of my hands > and in

Re: [sqlite] Crash when binding default column value

On 24 Sep 2014, at 8:53pm, Nathaniel Trellice wrote: > Imagine an application writer who's instructed my library to use pi (say) as > a column's default. When reading the value out of the database, they may want > to test if the value equals the default Sorry, but you

Re: [sqlite] Division accuracy

No problem! Let us know which solution you picked! Alessandro 2014-09-24 22:50 GMT+02:00 RSmith : > Thanks Alessandro, this will work, it's just... ugly... and for other > reasons I'd prefer the sort order to be numeric. If however it doesn't work > out, this

Re: [sqlite] Division accuracy

Thanks Alessandro, this will work, it's just... ugly... and for other reasons I'd prefer the sort order to be numeric. If however it doesn't work out, this might just be the best solution, ugly or not. :) Thanks! Ryan On 2014/09/24 21:33, Alessandro Marzocchi wrote: Which language are you

Re: [sqlite] Division accuracy

On 2014/09/24 22:24, Clemens Ladisch wrote: RSmith wrote: Clemens I'm liking the link list but did not go with it due to an expensive insert function Yes, you have to update two references (with prev/next), but how is that worse than the update of all SortOrder values? Well the insert runs

Re: [sqlite] Division accuracy

RSmith wrote: > Clemens I'm liking the link list but did not go with it due to an expensive > insert function Yes, you have to update two references (with prev/next), but how is that worse than the update of all SortOrder values? > how would I get a normal SQL query ORDER BY clause to use that?

Re: [sqlite] Crash when binding default column value

Hi Richard, James, > > > * SQL injection attacks; > > > > > > > You are doing CREATE TABLE statements based on text from an untrusted > > user? Really? > My reaction exactly. I'm writing a library so the safety of the input is out of my hands and in that of the application writers who

Re: [sqlite] Division accuracy

Which language are you using? Andres ideas could give some nice solutions using blob, even without a specific arbitrary precision library. eg: Splitting data with an encoding like (In-order id) (Out-of-order id) In-order insert are encoded with an encoding that guarantees lexicografical ordering

Re: [sqlite] Division accuracy

Thanks all for the responses. Thanks Scott for the calcs, I had somehow imagined using a set of values might yield more iterations, but of course that is just wishful thinking, the bits are the bits. The idea from Alessandro is great if I could control or guess where the next inserts will be,

Re: [sqlite] Division accuracy

Is the approach of 'just try it and if it goes badly fix it' doable? mid = (lo + hi) / 2; if ((mid <= lo) || (mid >= hi)) { Fix it } John Hascall IT Services Iowa State Univ. > On Sep 24, 2014, at 11:49 AM, RSmith wrote: > > I'm trying to find what the limit is for

Re: [sqlite] Division accuracy

RSmith wrote: > 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" [...] > reassign SortOrders simply in Integer steps: 1, 2, 3 etc. > > ID | SortOrder | Data > 1 | 1 | 'First Row' > 2 |

Re: [sqlite] Division accuracy

RSmith wrote: > how can I accurately establish how many total-then-divide-by-2's a set > of co-values in 64-bit FP guise can withstand before the difference is > too small to make sense to the sorter in SQLite? Internally, SQLite uses 64-bit IEEE floating-point numbers, which is the same as

Re: [sqlite] 50% faster than 3.7.17

On 24/09/14 06:19, Simon Slavin wrote: > How much max is max ? Not giving up ACID. But for example stat4 is better than the default stat1. Memory mapping (especially on 64 bit) is great. So is WAL. All are off by default. If you want to give up ACID then you should really be on your own to

Re: [sqlite] Division accuracy

Interesting trick. I remember having seen something similar for Trees ... Found it ... http://www.tclcommunityassociation.org/wub/proceedings/Proceedings-2011/StephenHuntley/Huntley_Tcl2011.pdf Well, he uses bignums. Maybe this could be degenerated into a linear list, which is what you are

Re: [sqlite] Division accuracy

Hello, Why don't you try the same approach with 64 bit integers? You could insert consecutive items at 0x10 intervals, in this way you would be allowed about 4G items to me inserted in any order you like. You could also change the interval to change the maximum consecutive items

Re: [sqlite] Division accuracy

On Wed, Sep 24, 2014 at 10:49 AM, RSmith 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 >

[sqlite] Division accuracy

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

Re: [sqlite] Crash when binding default column value

On Wed, 24 Sep 2014 09:49:36 -0400 Richard Hipp wrote: > > * SQL injection attacks; > > > > You are doing CREATE TABLE statements based on text from an untrusted > user? Really? My reaction exactly. > > * floating point value rounding in conversion to/from text; If the

Re: [sqlite] Results of Joins in sqlite

On Wed, 24 Sep 2014 11:03:53 +0530 Prakash Premkumar wrote: > Let's say an output of the join is: > > r11,r21,r31 > r11,r21,r32 > r11,r21,r33 > > where r1i is the i th row in T1, r2i is the i th row in T2 and r3i is > the ith row in T3: > > sqlite produces 3 result

Re: [sqlite] Non-optimal query plan

On 2014/09/24 16:52, Rob Golsteijn wrote: Hi List, I was looking at the query plan of a rather simple query, but I don't understand why sqlite would choose this query plan. ...I was surprised that sqlite came up with the inferior query plan... Note: After an "analyze aaa" (on a decently

Re: [sqlite] Non-optimal query plan

On Wed, Sep 24, 2014 at 10:52 AM, Rob Golsteijn wrote: > > Sqlite decides to create an AUTOMATIC INDEX (time complexity O(n log n)) > which it then uses to iterate table aaa1. This index is not re-used for > anything else (it can't be re-used since 'type' is not used

[sqlite] Non-optimal query plan

Hi List, I was looking at the query plan of a rather simple query, but I don't understand why sqlite would choose this query plan. For the following example: create table aaa(id INTEGER, name_id INTEGER, type CHAR); create table bbb(name_id INTEGER, name CHAR); create index ix_aaa ON aaa(id);

Re: [sqlite] Crash when binding default column value

On Wed, Sep 24, 2014 at 7:36 AM, Nathaniel Trellice wrote: > 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

Re: [sqlite] Results of Joins in sqlite

It does sound like that, like an Object Relational Mapping. And maybe someone has already done this. Googling "ORM sqlite c++" Yields a lot of existing ORM projects that support sqlite, so maybe Prakash doesn't need to work so hard on implementing that part of his project himself. -dave >

Re: [sqlite] sqlite db file have collapsed

On Wed, Sep 24, 2014 at 9:22 AM, sqlitekyounoii wrote: > Hi, I'm Takashi > > I use sqlite in Android. > I have access to SQLite using the API of Android. > Today, my sqlite's db file have collapsed. > Takashi: This sentence did not translate well: "my db file have

Re: [sqlite] Results of Joins in sqlite

On 2014/09/24 15:06, Prakash Premkumar wrote: Thanks a lot , Simon and Hick, What I am looking for is , instead of iterating through the result which sqlite provides and then form the respective objects and setting pointers, is it possible to hack sqlite to fill in the objects this way. I

Re: [sqlite] Results of Joins in sqlite

This is a really terrible idea. It is dependent on the internals of sqlite which makes it extremely fragile. Have you profiled your code to show that this is your bottleneck? If so, it's still a terrible idea, but at least has some basis for considering the idea. John On Wed, Sep 24, 2014 at

Re: [sqlite] Results of Joins in sqlite

Sounds like you want to "hack up" where the resultrow is returned for each table row visited as the query is processed, but only once for each table row satisfying the query. You can certainly do this -- the source code is freely available. I am sure you could "hack that up" if you want, but

Re: [sqlite] 50% faster than 3.7.17

On Wed, 2014-09-24 at 06:13 -0600, Keith Medcalf wrote: > > Would it not be more efficient to skip the join altogether since all > you want is the list of uid's, and assuming that you have maintained > the referential integrity of your database mail_list(list_uid) > references main(uid)? > >

Re: [sqlite] 50% faster than 3.7.17

On 24 Sep 2014, at 2:13pm, jose isaias cabrera wrote: > This would be a nice set of options. On my case, I would set all connections > to our project to be" max_performance", as it is what we need. Just thinking > out loud. How much max is max ? Are you willing to

Re: [sqlite] 50% faster than 3.7.17

"Roger Binns" wrote... On 22/09/14 10:48, Richard Hipp wrote: But if you have any new ideas on how we can further reduce the I/O, we'd love to hear from you. The single biggest problem for me is defaults. SQLite supports memory mapped i/o which has many advantages. The stat4 analyze does

Re: [sqlite] Results of Joins in sqlite

Thanks a lot , Simon and Hick, What I am looking for is , instead of iterating through the result which sqlite provides and then form the respective objects and setting pointers, is it possible to hack sqlite to fill in the objects this way. I would like to prevent the extra iteration through the

Re: [sqlite] Crash when binding default column value

On 24 Sep 2014, at 1:33pm, Nathaniel Trellice wrote: > Without the INSERT call, my test code runs through without crashing. > Attempting the INSERT call in the shell tool crashes it too. Following your > advice, the output of the .schema command looks dodgy: > > CREATE

Re: [sqlite] Crash when binding default column value

I think that the diagrams on http://www.sqlite.org/lang_createtable.html are the generic parsing and construction rules for "expr" everywhere an "expr" can be used. The text description further down the page describes restrictions and usage particular to the CREATE TABLE statement itself.

Re: [sqlite] Crash when binding default column value

On Wed, Sep 24, 2014 at 8:33 AM, Nathaniel Trellice wrote: > Attempting the INSERT call in the shell tool crashes it too. Following > your advice, the output of the .schema command looks dodgy: > > CREATE TABLE test_table (name TEXT DEFAULT ('joe'), interest TEXT DEFAULT >

Re: [sqlite] Crash when binding default column value

Hi Simon, Thanks for the quick reply. Without the INSERT call, my test code runs through without crashing. Attempting the INSERT call in the shell tool crashes it too. Following your advice, the output of the .schema command looks dodgy: CREATE TABLE test_table (name TEXT DEFAULT ('joe'),

Re: [sqlite] 50% faster than 3.7.17

Would it not be more efficient to skip the join altogether since all you want is the list of uid's, and assuming that you have maintained the referential integrity of your database mail_list(list_uid) references main(uid)? SELECT list_uid FROM mail_list WHERE email LIKE 'al%' UNION SELECT

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

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

Re: [sqlite] Crash when binding default column value

On 24 Sep 2014, at 12:36pm, Nathaniel Trellice wrote: > 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

Re: [sqlite] Results of Joins in sqlite

On 24 Sep 2014, at 11:53am, Prakash Premkumar wrote: > 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

[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

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

Re: [sqlite] 50% faster than 3.7.17

On Fri, 2014-09-19 at 21:14 -0400, Richard Hipp wrote: > The 50% faster number above is not about better query plans. Speaking of better query plans, though... here's a query which takes about 1700ms on my data set, followed by a couple of optimisations which seem like they might be generically

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

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

Re: [sqlite] Results of Joins in sqlite

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"

Re: [sqlite] Results of Joins in sqlite

To further clarify, the result of a join forms a row that has a new schema . (the new schema is derived from the schemas of the tables participating in the joins.) I would like to retain the old schema in the join result as well, so there is a split between which column belongs / is coming from