Re: [sqlite] Changing Date Format

2010-10-26 Thread Max Vlasov 
On Wed, Oct 27, 2010 at 8:09 AM, jose isaias cabrera
wrote:

>
> What I would like to do is a call that can fix the dates to the correct
> format, ie. -MM-DD, so that the final data looks like this,
>
>
How about

UPDATE Table1 Set d1=Replace(Replace(d1, "-", "-0"), "-00", "-0")

Max Vlasov
maxerist.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changing Date Format

2010-10-26 Thread BareFeetWare 
On 27/10/2010, at 3:09 PM, jose isaias cabrera wrote:

> I know I can do a bunch of sets, such as this one,
> 
> UPDATE table1 set d1 = '2010-01-01'
>   where
>   d1 = '2010-1-1';
> 
> but that is a lot of coding.


Perhaps something like:

create table table1
(   id integer primary key
,   st text
,   ca text
,   d1 date
,   d2 date
)
;
insert into table1 (st, ca, d1, d2)
values ('AA','BB','2010-1-1','2010-2-9')
;
insert into table1 (st, ca, d1, d2)
values ('BB','BB','2010-1-1', '2010-3-29')
;
insert into table1 (st, ca, d1, d2)
values ('CC','BB','2010-10-4','2010-5-13')
;
insert into table1 (st, ca, d1, d2)
values ('DD', 'BB','2010-1-10','2010-02-01')
;

update table1
set d1 = substr(d1, 1, 5) ||
case when substr(d1, 7, 1) = '-' then '0' || substr(d1, 6, 2) else 
substr(d1, 6, 3) end ||
case when substr(d1, -2, 1) = '-' then '0' || substr(d1, -1, 1) else 
substr(d1, -2, 2) end
,   d2 = substr(d2, 1, 5) ||
case when substr(d2, 7, 1) = '-' then '0' || substr(d2, 6, 2) else 
substr(d2, 6, 3) end ||
case when substr(d2, -2, 1) = '-' then '0' || substr(d2, -1, 1) else 
substr(d2, -2, 2) end
;

select * from table1;

which gives:

1,AA,BB,2010-01-01,2010-02-09
2,BB,BB,2010-01-01,2010-03-29
3,CC,BB,2010-10-04,2010-05-13
4,DD,BB,2010-01-10,2010-02-01

> I thought that perhaps there would be an easier regular expression call 
> within the DB engine.

I wish there was some regex functionality built into SQLite, but alas there is 
not.

Thanks,
Tom
BareFeetWare

--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Changing Date Format

2010-10-26 Thread jose isaias cabrera 

Greetings and salutations.

I have this data entry problem, that I have placed a fix for the users, but 
I have entries in the DB that have the wrong date format.  There are dates 
entered in this format, 2010-1-1 instead of 2010-01-01.  Say that I had this 
table,

table1.
id,st,ca,d1,d2
1,AA,BB,2010-1-1,2010-2-9
1,BB,BB,2010-1-1,2010-3-29
1,CC,BB,2010-10-4,2010-5-13
1,DD,BB,2010-1-10,2010-02-01

What I would like to do is a call that can fix the dates to the correct 
format, ie. -MM-DD, so that the final data looks like this,

table1.
id,st,ca,d1,d2
1,AA,BB,2010-01-01,2010-02-09
1,BB,BB,2010-01-01,2010-03-29
1,CC,BB,2010-10-04,2010-05-13
1,DD,BB,2010-01-10,2010-02-01

I know I can do a bunch of sets, such as this one,

UPDATE table1 set d1 = '2010-01-01'
where
d1 = '2010-1-1';

but that is a lot of coding.  I thought that perhaps there would be an 
easier regular expression call within the DB engine.

Any help would be greatly appreciated.

thanks,

josé 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple databases vs. Multiple tables.

2010-10-26 Thread Dariusz Matkowski 
On boot up the device will discover the servers and enumerate what they have, 
then when it is done it will just wait for updates from the servers. I do not 
know how big the servers are, each server/media library may be different.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Rich Shepard
Sent: Tuesday, October 26, 2010 10:59 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Multiple databases vs. Multiple tables.

On Tue, 26 Oct 2010, Dariusz Matkowski wrote:

> Queries will be done across the servers to aggregate the content. I am
> concern about the locking mechanism, if I write to the single database and
> I represent the servers as tables I will have no access to read the other
> servers/tables, but if I distribute the servers across different DBs I can
> write into one and the other ones are open to read.

   How frequently are data written to the tables? How much data per write?

   It appears that you want to use separate databases for each server so you
might try that and see how well it works for you.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple databases vs. Multiple tables.

2010-10-26 Thread Rich Shepard 
On Tue, 26 Oct 2010, Dariusz Matkowski wrote:

> Queries will be done across the servers to aggregate the content. I am
> concern about the locking mechanism, if I write to the single database and
> I represent the servers as tables I will have no access to read the other
> servers/tables, but if I distribute the servers across different DBs I can
> write into one and the other ones are open to read.

   How frequently are data written to the tables? How much data per write?

   It appears that you want to use separate databases for each server so you
might try that and see how well it works for you.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite install error

2010-10-26 Thread Simon Slavin 

On 26 Oct 2010, at 11:59am, Dasa wrote:

> When i tried to install new version on sqlite in my mac, i got this
> error ...
> 
> dyld: Library not loaded: /usr/local/lib/libtcl8.6.dylib
>  Referenced from: /users/dasaanand/Downloads/sqlite3_analyzer
>  Reason: image not found

Please do not try to replace the SQLite files included with the operating 
system with later versions.  This can cause problems because some applications 
may depend on old behaviour.

SQLite doesn't really have an installation.  Any application that uses SQLite 
just includes the sqlite programming inside the application.  For instance, 
that sqlite3_analyzer program is a single stand-alone command-line application 
that requires no other files on your Mac: it contains all the programming it 
needs.

>From the name of the library you refer to, you're trying to do something with 
>TCL rather than SQLite.  I don't know anything about TCL so I'm sorry I can't 
>help you.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple databases vs. Multiple tables.

2010-10-26 Thread Dariusz Matkowski 
Queries will be done across the servers to aggregate the content. I am concern 
about the locking mechanism, if I write to the single database and I represent 
the servers as tables I will have no access to read the other servers/tables, 
but if I distribute the servers across different DBs I can write into one and 
the other ones are open to read.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Rich Shepard
Sent: Tuesday, October 26, 2010 9:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Multiple databases vs. Multiple tables.

On Tue, 26 Oct 2010, Dariusz Matkowski wrote:

> Only one user (the browser). The DB is on a device the same place where
> the user (Browser) is. A process will collect the information about the
> servers and their contents and story it to the DB at the same time the
> user may ask for the contents to display on the screen.

   Then the next question is wether queries are restricted to each server. If
so, you could have a separate database for each. Alternatively, you can
define a server table and associate each one with the other data you collect
and retrieve. The latter approach is more flexible and allows easier changes
if/when your information needs change.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple databases vs. Multiple tables.

2010-10-26 Thread Rich Shepard 
On Tue, 26 Oct 2010, Dariusz Matkowski wrote:

> Only one user (the browser). The DB is on a device the same place where
> the user (Browser) is. A process will collect the information about the
> servers and their contents and story it to the DB at the same time the
> user may ask for the contents to display on the screen.

   Then the next question is wether queries are restricted to each server. If
so, you could have a separate database for each. Alternatively, you can
define a server table and associate each one with the other data you collect
and retrieve. The latter approach is more flexible and allows easier changes
if/when your information needs change.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple databases vs. Multiple tables.

2010-10-26 Thread Dariusz Matkowski 
Only one user (the browser). The DB is on a device the same place where the 
user (Browser) is. A process will collect the information about the servers and 
their contents and story it to the DB at the same time the user may ask for the 
contents to display on the screen.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Rich Shepard
Sent: Tuesday, October 26, 2010 9:24 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Multiple databases vs. Multiple tables.

On Tue, 26 Oct 2010, Dariusz Matkowski wrote:

> in  your opinion is it better (performance, maintainability etc...) to
> have multiple databases or multiple tables. The problem I am facing is as
> follows. I have many media servers containing a large amount of images
> music and videos, let's assume 5. I would like to gather the information
> (metadata, thumbnails, location etc) and story it in the database. I will
> also have a GUI/Browser that will display that information. Now, the
> question is it better to use multiple databases, each server = one
> database or each server = one table.

Daiuusz,

   I suggest you're asking the wrong questions. How many simultaneous users
will access data in the database? Will the database be stored on one server
and accessed across the network?

   It might be that for your application SQLite is not the appropriate tool.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Doubt about SQLite and its UTF-8 encoding format.

2010-10-26 Thread Igor Tandetnik 
nhar...@gmail.com wrote:
> Say, suppose I write a SQLite query to insert Japanese text which is encoded
> in EUC-JP, in to a table in SQLite database (UTF-8 encoding).

Don't. SQLite doesn't know anything about EUC-JP. Any string you pass to SQLite 
must be in UTF-8 or UTF-16. SQLite will convert between these two if the 
encoding you pass doesn't match that of the database - that's the only kind of 
conversion SQLite knows how to do.

> Is it that, the encoding of the text will be automatically converted from
> EUC-JP to UTF-8 for storage by the SQLite engine?

No. The string will be misinterpreted as being UTF-8. At best, it will be 
stored and returned back to you as is. At worst, it will be mangled when SQLite 
tries to convert it to UTF-16.

> Does the user has to take care of the encoding conversion before inserting
> such a text into the database, making sure that data inserted is always in
> UTF-8 encoding?

Yes.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple databases vs. Multiple tables.

2010-10-26 Thread Rich Shepard 
On Tue, 26 Oct 2010, Dariusz Matkowski wrote:

> in  your opinion is it better (performance, maintainability etc...) to
> have multiple databases or multiple tables. The problem I am facing is as
> follows. I have many media servers containing a large amount of images
> music and videos, let's assume 5. I would like to gather the information
> (metadata, thumbnails, location etc) and story it in the database. I will
> also have a GUI/Browser that will display that information. Now, the
> question is it better to use multiple databases, each server = one
> database or each server = one table.

Daiuusz,

   I suggest you're asking the wrong questions. How many simultaneous users
will access data in the database? Will the database be stored on one server
and accessed across the network?

   It might be that for your application SQLite is not the appropriate tool.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple databases vs. Multiple tables.

2010-10-26 Thread Dariusz Matkowski 
Question,
in  your opinion is it better (performance, maintainability etc...) to have 
multiple databases or multiple tables. The problem I am facing is as follows. I 
have many media servers containing a large amount of images music and videos, 
let's assume 5. I would like to gather the information (metadata, thumbnails, 
location etc) and story it in the database. I will also have a GUI/Browser that 
will display that information. Now, the question is it better to use multiple 
databases, each server = one database or each server = one table.

Regards,

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Doubt about SQLite and its UTF-8 encoding format.

2010-10-26 Thread nhar...@gmail.com 
Hi All,

*Ignore my previous email, Please. I am sorry for that SPAM email.*

While referring the SQLite documentation, about the database encoding in the
pages -


http://www.sqlite.org/datatype3.html
http://www.sqlite.org/pragma.html#pragma_encoding

I learnt that an SQLite database uses either one of the encoding - UTF-8,
UTF-16, UTF-16le/be as its "database encoding". Consider the following:-

Say, suppose I write a SQLite query to insert Japanese text which is encoded
in EUC-JP, in to a table in SQLite database (UTF-8 encoding).

Is it that, the encoding of the text will be automatically converted from
EUC-JP to UTF-8 for storage by the SQLite engine? or
Does the user has to take care of the encoding conversion before inserting
such a text into the database, making sure that data inserted is always in
UTF-8 encoding?

Let me know, please.

Thanks,
Harsha

On Tue, Oct 26, 2010 at 6:31 PM, nhar...@gmail.com wrote:

> Hi All,
>
>
> While refering the SQLite documentation, about the database encoding in the
> pages -
>
>
> http://www.sqlite.org/datatype3.html
>
> --
> ¡Gracias!
> Harsha Reddy
>



-- 
¡Gracias!
Harsha Reddy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Doubt about SQLite and its UTF-8 encoding format.

2010-10-26 Thread Igor Tandetnik 
nhar...@gmail.com wrote:
> While refering the SQLite documentation, about the database encoding in the
> pages -
> 
> 
>http://www.sqlite.org/datatype3.html

So, what seems to be the problem? What exactly do you find unclear in this 
document?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Doubt about SQLite and its UTF-8 encoding format.

2010-10-26 Thread nhar...@gmail.com 
Hi All,


While refering the SQLite documentation, about the database encoding in the
pages -


http://www.sqlite.org/datatype3.html

-- 
¡Gracias!
Harsha Reddy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG

2010-10-26 Thread Alexey Pechnikov 
Yes, I was wrong. Phisical order of records is equal to the useless sort
condition in the view... Your examples can help me, thanks!

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query help

2010-10-26 Thread Paul Sanderson 
Thank You.

On 26 October 2010 13:14, Igor Tandetnik  wrote:
> Paul Sanderson  wrote:
>> I have two tables, table b is a subset of table a. both tables have
>> the same primary key
>>
>> I want to update the rows from table a with a single column from table
>> b, what sql command would be most efficient for this?
>
> update a set ColumnToUpdate = coalesce(
>    (select ColumnToUpdate from b where b.KeyColumn = a.KeyColumn),
>    ColumnToUpdate);
>
> -- or
>
> insert or replace into a(ColumnToUpdate, AllOtherColumns)
> select b1.ColumnToUpdate, a1.AllOtherColumns
> from b1 join a1 on b1.KeyColumn = a1.KeyColumn;
>
> The second statement could be faster if b is much smaller than a, but is more 
> verbose and has to be updated whenever schema changes. Time both on real 
> data, see which one works better for you.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Paul Sanderson
Sanderson Forensics
+44 (0)1325 572786
www.sandersonforensics.com
http://www.twitter.com/sandersonforens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query help

2010-10-26 Thread Igor Tandetnik 
Paul Sanderson  wrote:
> I have two tables, table b is a subset of table a. both tables have
> the same primary key
> 
> I want to update the rows from table a with a single column from table
> b, what sql command would be most efficient for this?

update a set ColumnToUpdate = coalesce(
(select ColumnToUpdate from b where b.KeyColumn = a.KeyColumn),
ColumnToUpdate);

-- or

insert or replace into a(ColumnToUpdate, AllOtherColumns)
select b1.ColumnToUpdate, a1.AllOtherColumns
from b1 join a1 on b1.KeyColumn = a1.KeyColumn;

The second statement could be faster if b is much smaller than a, but is more 
verbose and has to be updated whenever schema changes. Time both on real data, 
see which one works better for you.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG

2010-10-26 Thread Igor Tandetnik 
Alexey Pechnikov  wrote:
> 2010/10/26 Dan Kennedy 
> 
>> The sorting happens after the grouping. And it is while processing
>> the GROUP BY clause that SQLite is forced to select an arbitrary
>> record from the user_record table.
>> 
>> See here:
>> 
>>   http://www.sqlite.org/lang_select.html#resultset
>> 
>> Third paragraph under the third bullet point.
> 
> 
> No. See paragraph under bullet points: "The list of expressions between the
> SELECT and FROM keywords".

I don't see how this is relevant. ORDER BY doesn't care about the expressions 
you list between SELECT and FROM.

>Sort condition IS NOT the expression from the
> third bullet point.

These bullet points describe which rows are returned by the statement. 
Specifically, you start with "input data from the FROM clause", which is then 
"filtered by the WHERE clause", and finally, in the presence of GROUP BY, "each 
group of input dataset rows contributes a single row to the set of result rows".

ORDER BY describes the order in which those result rows are returned: "if a 
SELECT statement does have an ORDER BY clause, then the list of expressions 
attached to the ORDER BY determine the order in which rows are returned". Note 
again that it works on result rows, not on input rows. In other words, ORDER BY 
is applied *after* GROUP BY. You cannot use ORDER BY clause to influence which 
single result row is chosen as a representative for its group of input rows.

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite install error

2010-10-26 Thread Dasa 
When i tried to install new version on sqlite in my mac, i got this
error ...

dyld: Library not loaded: /usr/local/lib/libtcl8.6.dylib
  Referenced from: /users/dasaanand/Downloads/sqlite3_analyzer
  Reason: image not found


what does it mean ??
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query help

2010-10-26 Thread Paul Sanderson 
I have two tables, table b is a subset of table a. both tables have
the same primary key

I want to update the rows from table a with a single column from table
b, what sql command would be most efficient for this?

Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG

2010-10-26 Thread Alexey Pechnikov 
Richard, with "PRAGMA reverse_unordered_selects = 1;". it's returns
count(*)=0. This is strange for me and not help for development.

2010/10/26 Richard Hipp 

> On Mon, Oct 25, 2010 at 4:06 PM, Alexey Pechnikov  >wrote:
>
> > > The result of the view above is undefined.  It will choose one of the
> > > user_record rows for each distinct user.id, but you don't know which
> > row.
> >
> > Yes! But it choose only single user_record row for two distinct user_id
> in
> > count(*) expression.
> >
> > Are you really think that count(*)=1 for _two_ rows is not the bug?
> >
>
> You miss my point.  "test" in this case doesn't have one row or two rows.
> It has an arbitrary number of rows due to indeterminacy in your view.
> Sometimes "test" will return one row.  Sometimes it will return two.  You
> can never predict which.  Both are "correct" in the sense that both are
> allowed interpretations of what SQLite ought to do.
>
> The above will never happen for a simple table named "test".  It only
> happens for things like:
>
> ... WHERE record_id IN (SELECT record_id FROM view_user WHERE name LIKE
> '%');
>
> where the record_id value returned from the view_user view is
> indeterminate.  The WHERE clause above might be equivalent to
>
> ... WHERE record_id IN (76,86)
>
> and in that case count(*) will return 2.  But the WHERE clause might also
> be
> equivalent to
>
> ... WHERE record_id IN (76,87)
>
> in which case count(*) will return 1.  SQLite is free to choose either
> interpretation for the subquery in your WHERE clause, and hence might get
> either 1 or 2 as the count(*) result.  Version 3.7.2 happened to get 2.
> Version 3.7.3 happens to get 1.  Who knows what 3.7.4 will get - both
> answers are correct
>
>
> > sqlite> select * from test;
> > 4|87|3|4|B
> > 11|76|8|11|A
> > sqlite> select count(*) from test;
> > 1
> > 
> > CREATE TABLE user
> > (
> >  id INTEGER PRIMARY KEY
> > );
> > INSERT INTO "user" VALUES(4);
> > INSERT INTO "user" VALUES(11);
> >
> > CREATE TABLE user_record
> > (
> >  record_id INTEGER PRIMARY KEY,
> >  record_version INTEGER,
> >  user_id INTEGER NOT NULL,
> >  name TEXT
> > );
> > INSERT INTO "user_record" VALUES(76,8,11,'A');
> > INSERT INTO "user_record" VALUES(86,11,4,'B');
> > INSERT INTO "user_record" VALUES(87,3,4,'B');
> >
> > CREATE VIEW view_user AS
> > SELECT user.id,user_record.*
> > FROM user, user_record
> > WHERE user.id=user_record.user_id
> > GROUP BY user.id;
> >
> > create temp view test as select * from main.view_user where record_id in
> > (select record_id from main.view_user where name like '%');
> >
> > select * from test;
> > select count(*) from test;
> > 
> >
> > --
> > Best regards, Alexey Pechnikov.
> > http://pechnikov.tel/
> > ___
> > 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
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG

2010-10-26 Thread Alexey Pechnikov 
2010/10/26 Dan Kennedy 

> The sorting happens after the grouping. And it is while processing
> the GROUP BY clause that SQLite is forced to select an arbitrary
> record from the user_record table.
>
> See here:
>
>   http://www.sqlite.org/lang_select.html#resultset
>
> Third paragraph under the third bullet point.


No. See paragraph under bullet points: "The list of expressions between the
SELECT and FROM keywords". Sort condition IS NOT the expression from the
third bullet point. For sorting we may to see the part "ORDER BY and
LIMIT/OFFSET Clauses".

In the original test script the problem was when two rows are equal for
sorting condition.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG

2010-10-26 Thread Dan Kennedy 

On Oct 26, 2010, at 2:22 PM, Alexey Pechnikov wrote:

>> But view_user statement makes no attempt to select the last  
>> version. It
> picks some arbitrary random version. You might want to consider  
> something
> like this:
>
> Why you wrote about "some arbitrary random version" when we have  
> sorting by
> "ts"?..

The sorting happens after the grouping. And it is while processing
the GROUP BY clause that SQLite is forced to select an arbitrary
record from the user_record table.

See here:

   http://www.sqlite.org/lang_select.html#resultset

Third paragraph under the third bullet point.



>
> CREATE TABLE user
> (
>  id INTEGER PRIMARY KEY
> );
> CREATE TABLE user_record
> (
>  record_id INTEGER PRIMARY KEY,
>  record_version INTEGER,
>  ts INTEGER NOT NULL DEFAULT (strftime('%s','now')),
>  user_id INTEGER NOT NULL,
>  name TEXT,
>  FOREIGN KEY(user_id) REFERENCES user
> );
>
> CREATE VIEW view_user AS
> SELECT user.id,user_record.*
> FROM user, user_record
> WHERE user.id=user_record.user_id
> GROUP BY user.id
> ORDER BY ts ASC;
>
> This view returns last by "ts" row for each unique "user_id". There  
> is used
> the SQLite hint with "group by" (non-grouped values returns too).
>
> And conflict with equal "ts" can be resolved by trigger as:
>
> CREATE TRIGGER view_user_update instead of update on view_user
> begin
> ...
>  SELECT RAISE(ABORT, 'User wait 1 second.')
>WHERE EXISTS(select 1 from user_record where user_id=OLD.user_id  
> and
> ts=strftime('%s','now'));
> ...
> end;
>
> -- 
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] BUG

2010-10-26 Thread Alexey Pechnikov 
> But view_user statement makes no attempt to select the last version. It
picks some arbitrary random version. You might want to consider something
like this:

Why you wrote about "some arbitrary random version" when we have sorting by
"ts"?..

CREATE TABLE user
(
  id INTEGER PRIMARY KEY
);
CREATE TABLE user_record
(
  record_id INTEGER PRIMARY KEY,
  record_version INTEGER,
  ts INTEGER NOT NULL DEFAULT (strftime('%s','now')),
  user_id INTEGER NOT NULL,
  name TEXT,
  FOREIGN KEY(user_id) REFERENCES user
);

CREATE VIEW view_user AS
SELECT user.id,user_record.*
FROM user, user_record
WHERE user.id=user_record.user_id
GROUP BY user.id
ORDER BY ts ASC;

This view returns last by "ts" row for each unique "user_id". There is used
the SQLite hint with "group by" (non-grouped values returns too).

And conflict with equal "ts" can be resolved by trigger as:

CREATE TRIGGER view_user_update instead of update on view_user
begin
...
  SELECT RAISE(ABORT, 'User wait 1 second.')
WHERE EXISTS(select 1 from user_record where user_id=OLD.user_id and
ts=strftime('%s','now'));
...
end;

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Resident Database

2010-10-26 Thread durgadevi 

Hai,
 I am using SQLite with c.To increase the performance I am using inmemory
Database.
How Can I view the contents stored in the inmemory database?
Please help me.

Regards,
B.Durgadevi

marcglennjamon wrote:
> 
> Hello guys,
> 
>  Is there an option in SQLite to make the database file reside in the
> memory during sql transactions for faster access? 
>  I am using the C# language under Mono.
> 
> Thanks in advance,
> Marc Glenn
> 

-- 
View this message in context: 
http://old.nabble.com/Memory-Resident-Database-tp19830584p30054585.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