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
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
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
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
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
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
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
On 24 Sep 2014, at 11:53am, Prakash Premkumar prakash.p...@gmail.com 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 planning
On 24 Sep 2014, at 12:36pm, Nathaniel Trellice napt...@yahoo.co.uk 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
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
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
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
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'),
On Wed, Sep 24, 2014 at 8:33 AM, Nathaniel Trellice napt...@yahoo.co.uk
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
(?1));
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.
Under
On 24 Sep 2014, at 1:33pm, Nathaniel Trellice napt...@yahoo.co.uk 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 TABLE
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
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 a
On 24 Sep 2014, at 2:13pm, jose isaias cabrera jic...@cinops.xerox.com 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 give up
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)?
SELECT
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
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
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
On Wed, Sep 24, 2014 at 9:22 AM, sqlitekyounoii sqlitekyoun...@yahoo.co.jp
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
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
On Wed, Sep 24, 2014 at 7:36 AM, Nathaniel Trellice napt...@yahoo.co.uk
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 to avoid
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);
On Wed, Sep 24, 2014 at 10:52 AM, Rob Golsteijn rob.golste...@mapscape.eu
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
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
On Wed, 24 Sep 2014 11:03:53 +0530
Prakash Premkumar prakash.p...@gmail.com 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 rows , but I
On Wed, 24 Sep 2014 09:49:36 -0400
Richard Hipp d...@sqlite.org 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 default
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
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
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
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
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
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 double
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 | 4 |
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 rsm...@rsweb.co.za wrote:
I'm trying to find what the limit is for dividing
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,
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
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 work 'above'
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?
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
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
No problem! Let us know which solution you picked!
Alessandro
2014-09-24 22:50 GMT+02:00 RSmith rsm...@rsweb.co.za:
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
On 24 Sep 2014, at 8:53pm, Nathaniel Trellice napt...@yahoo.co.uk 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 don't
On Wed, 24 Sep 2014 20:53:32 +0100
Nathaniel Trellice napt...@yahoo.co.uk 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 that of the
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?
Well the
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
On Wed, 24 Sep 2014 20:51:38 +0200
RSmith rsm...@rsweb.co.za 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 Row'
4
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
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 from t1
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 get
On Wed, Sep 24, 2014 at 10:27 AM, k kmccord0...@gmail.com 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 list
On Wed, Sep 24, 2014 at 12:48 PM, 麦田观望者 myk...@qq.com 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
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 in terms
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
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
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=b2c89ef49cd1to=ef30e0352b3d
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
60 matches
Mail list logo