Re: [sqlite] Re: Why it does not work properly?

[Joe Wilson]

--- Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> woj <[EMAIL PROTECTED]> wrote:
> > Now, when I run a query:
> > SELECT Mieszalnia.IdMat, Mieszalnia.Partia, Mieszalnia.Kont,
> > Mieszalnia.Uk, Max(Mieszalnia.Data) FROM Mieszalnia;
> > I always get:
> > IdMat   Partia  KontUk  Data
> > 6 3 3 sl1 1172135769
> >
> > In this result there is indeed max from Data field but rest of the
> > fields fit not...
> 
> When a SELECT statement involves aggregate functions, all column 
> references in the SELECT must be either parameters to some aggregate 
> functions, or else be also mentioned in GROUP BY clause. SQLite allows, 
> as an extension, departure from this rule, but the row from which values 
> for columns that are neither aggregated nor grouped by are taken is 
> random and unpredictable.
> 
> Specifically, in the query you show, there's no guarantee that values 
> for IdMat, Partia and so on would be taken from the same row from which 
> Max(Data) comes. Even if SQLite really wanted to help you out here, it 
> is impossible in general. Consider:
> 
> SELECT IdMat, Max(Data), Min(Data) from Mieszalnia;
> 
> Which value of IdMat would you expect to see in response to such a 
> query? Should it come from the row with the largest value of Data, with 
> the smallest, or some other?

This thread describes how the non-aggregate values in a group by are 
selected by SQLite:

 http://www.mail-archive.com/sqlite-users@sqlite.org/msg17770.html



 

Yahoo! Music Unlimited
Access over 1 million songs.
http://music.yahoo.com/unlimited

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] question regarding Check-in [3671]: Changes to the btree and pager that reduce the amount of I/O when dealing with the freelist.

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> What sort of SQL statements will benefit most from this change?

SQL statements that run on embedded platforms that lack a
OS filesystem cache.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Why it does not work properly?-now written correctly...

[woj]

Hi all,
sorry previous mail was not really readable...
I have a database containing a following data:
Table Mieszalnia:
CREATE TABLE [Mieszalnia11]
(
[IdWpis] integer NOT NULL  PRIMARY KEY AUTOINCREMENT ,
[IdMat] integer,
[Data] integer,
[Partia] varchar (10),
[Kontener] varchar (10),
[Uklad] varchar (5),
[Opis] varchar (256)
);
with data:

IdW  IdMat  Partia  Kont Uk  Data
12   1  1179760 136832 F16 1171951258
12   2  1176865 315060 F21 1171915832
13   3  1177504 318272 F9  1171538132
15   4  1153571 277827 F15 1171437632
16   5  1153437 062022 F16 1171368032
17   6  1185543 323063 SL1 1171978232
18   7  1156166 077690 F3  1169082032
19   8  1156269 075825 F8  116832
20   9  1166581 101109 F11 1168014032
21   10 1099211 081868 F17 1166497232
22   11 1084774 054445 F20 1169530232
23   12 1143641 157155 F24 1171485032
24   13 1180275 179329 SF3 1169521232
25   14 1102698 150010 SF4 1171494000
26   15 1130267 266647 SF5 1170282600
27   6  11  11 sl1 1172135769
28   6  3   3  sl1 1172054927

Now, when I run a query:
SELECT Mieszalnia.IdMat, Mieszalnia.Partia, Mieszalnia.Kont,  
Mieszalnia.Uk, Max(Mieszalnia.Data) FROM Mieszalnia;

I always get:
IdMat  Partia  KontUk  Data
6  3   3   sl1 1172135769

In this result there is indeed max from Data field but rest of the fields  
fit not...I think result should look like:

IdMat  Partia  KontUk  Data
6  11  11  sl1 1172135769
--
Używam klienta poczty Opera Mail: http://www.opera.com/mail/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Why it does not work properly?

[Igor Tandetnik]

woj <[EMAIL PROTECTED]> wrote:

Now, when I run a query:
SELECT Mieszalnia.IdMat, Mieszalnia.Partia, Mieszalnia.Kont,
Mieszalnia.Uk, Max(Mieszalnia.Data) FROM Mieszalnia;
I always get:
IdMat   Partia  KontUk  Data
6 3 3 sl1 1172135769

In this result there is indeed max from Data field but rest of the
fields fit not...


When a SELECT statement involves aggregate functions, all column 
references in the SELECT must be either parameters to some aggregate 
functions, or else be also mentioned in GROUP BY clause. SQLite allows, 
as an extension, departure from this rule, but the row from which values 
for columns that are neither aggregated nor grouped by are taken is 
random and unpredictable.


Specifically, in the query you show, there's no guarantee that values 
for IdMat, Partia and so on would be taken from the same row from which 
Max(Data) comes. Even if SQLite really wanted to help you out here, it 
is impossible in general. Consider:


SELECT IdMat, Max(Data), Min(Data) from Mieszalnia;

Which value of IdMat would you expect to see in response to such a 
query? Should it come from the row with the largest value of Data, with 
the smallest, or some other?



There are many ways to formulate the query you seem to want. E.g.

select IdMat, Data from Mieszalnia
order by Data desc limit 1;

select IdMat, Data from Mieszalnia
where Data = (select max(Data) from Mieszalnia);

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Why it does not work properly?

[woj]

Hi all,

I have a database containing a following data:
Table Mieszalnia:
CREATE TABLE [Mieszalnia11]
(
[IdWpis] integer NOT NULL  PRIMARY KEY AUTOINCREMENT ,
[IdMat] integer,
[Data] integer,
[Partia] varchar (10),
[Kontener] varchar (10),
[Uklad] varchar (5),
[Opis] varchar (256)
);
with data:

IdW IdMat   Partia  KontUk  Data
12  1   1179760 136832  F16 1171951258
14  2   1176865 315060  F21 1171915832
14  3   1177504 318272  F9  1171538132
15  4   1153571 277827  F15 1171437632
16  5   1153437 062022  F13 1171368032
17  6   1185543 323063  SL1 1171978232
18  7   1156166 077690  F3  1169082032
19  8   1156269 075825  F8  116832
20  9   1166581 101109  F11 1168014032
21  10  1099211 081868  F17 1166497232
22  11  1084774 054445  F20 1169530232
23  12  1143641 157155  F24 1171485032
24  13  1180275 179329  SF3 1169521232
25  14  1102698 150010  SF4 1171494000
26  15  1130267 266647  SF5 1170282600
27  6   11  11  sl1 1172135769
28  6   3   3   sl1 1172054927

Now, when I run a query:
SELECT Mieszalnia.IdMat, Mieszalnia.Partia, Mieszalnia.Kont,  
Mieszalnia.Uk, Max(Mieszalnia.Data) FROM Mieszalnia;

I always get:
IdMat   Partia  KontUk  Data
6   3   3   sl1 1172135769

In this result there is indeed max from Data field but rest of the fields  
fit not...I think result should look like:

IdMat   Partia  KontUk  Data
6   11  11  sl1 1172135769

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] I Need database for some test

[Stef Mientki]

lordphoenix wrote:

Le Sun, 04 Mar 2007 22:01:30 +0100,
Stef Mientki <[EMAIL PROTECTED]> a écrit :

  

lordphoenix wrote:


I'm developing an SQlite database administration tool (web site is
there : http://litemanager.tuxamily.org)
  

this link doesn't seem to work :-(


Sorry for mistake here is the good one :
http://litemanager.tuxfamily.org/
  

thanks "lordphoenix"  (and Gerry too, for the lucky guess)
looks promising,
I hope to see it in English one day,
because my French is much much worse than your English !!

I think good open source database managers are very welcome.
Tested about 7 free and open source database managers (for windows),
and none of them was satisfactory.
( So I'm too building an opensource database manager (probably windows 
only, or wine),

maybe it would be nice to exchange some ideas)

--
cheers,
Stef Mientki
http://pic.flappie.nl


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Database security

[DragonK]

Hi,

I'm wondering if there's any free addon for sqlite which would add a
transparent encryption layer to the database... can anyone provide any
information?

Thanks!

--
...it's only a matter of time...

Re: [sqlite] I Need database for some test

[Gerry Snyder]

Stef Mientki wrote:



lordphoenix wrote:

I'm developing an SQlite database administration tool (web site is
there : http://litemanager.tuxamily.org)

this link doesn't seem to work :-(


There seems to have been a typo.

Try: http://litemanager.tuxfamily.org/



Gerry (who just made a lucky guess at what the URL should have been)

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Question about regexp function

[Neil McLeod]

Thanks! That answers my question perfectly.
Best,
Neil

On 3/3/07, Griggs, Donald <[EMAIL PROTECTED]> wrote:


Hi Neil,

Re: " I would like to perform regular expression queries interactively,
i.e. from the sqlite> prompt"I

I'm not sure what operating system you're using, but there are sqlite GUI
utilities that register a REGEX function.   In windows, one such free one is
Sqlite3Explorer:
 http://www.singular.gr/sqlite/ 
which includes a perl case-insensitive version.

If your needs require that you use the command-line utility included with
the product, you'd want to link a regex function into your compile to
produce your own version of sqlite3.exe.






-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
Ubuntu Linux 6.06 (Dapper Drake)

Re: [sqlite] I Need database for some test

[lordphoenix]

Le Sun, 04 Mar 2007 22:01:30 +0100,
Stef Mientki <[EMAIL PROTECTED]> a écrit :

> 
> 
> lordphoenix wrote:
> > I'm developing an SQlite database administration tool (web site is
> > there : http://litemanager.tuxamily.org)
> this link doesn't seem to work :-(
Sorry for mistake here is the good one :
http://litemanager.tuxfamily.org/


signature.asc
Description: PGP signature

Re: [sqlite] I Need database for some test

[Stef Mientki]

lordphoenix wrote:

I'm developing an SQlite database administration tool (web site is
there : http://litemanager.tuxamily.org)

this link doesn't seem to work :-(

cheers,
Stef Mientki


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] I Need database for some test

[Al Danial]

Here are SQL statements that will generate a 24 MB database
of US baseball information:
http://danial.org/sqlite/lampsig/baseball.sql.bz2
The original data came from
http://baseball1.info/statistics/lahman52_csv.zip
and the schema is explained in http://baseball1.com/statistics/readme52.txt


On 3/4/07, lordphoenix <[EMAIL PROTECTED]> wrote:


I'm developing an SQlite database administration tool (web site is
there : http://litemanager.tuxamily.org) for Linux using Mono and GTK#.
If you want test it use svn version the last commit content all
features of next release (Available only in french for the moment if
someone can help me for translation he is welcome). But in order to do
some test I would need some databases.
So if you have database with a significant volume of data and tables
and you don't mind to send me a copy it would be very great full. I
precise I don't care about data in this database I won't keep any of
this database after my test and of course I will never send any content
to nobody.

So if someone can help me .


PS Sorry for my bad English I don't speak it very often :)


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

[sqlite] question regarding Check-in [3671]: Changes to the btree and pager that reduce the amount of I/O when dealing with the freelist.

[Joe Wilson]

What sort of SQL statements will benefit most from this change?
Large delete operations or vacuum? What about query speed?


 

Bored stiff? Loosen up... 
Download and play hundreds of games for free on Yahoo! Games.
http://games.yahoo.com/games/front

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] I Need database for some test

[lordphoenix]

I'm developing an SQlite database administration tool (web site is
there : http://litemanager.tuxamily.org) for Linux using Mono and GTK#.
If you want test it use svn version the last commit content all
features of next release (Available only in french for the moment if
someone can help me for translation he is welcome). But in order to do
some test I would need some databases.
So if you have database with a significant volume of data and tables
and you don't mind to send me a copy it would be very great full. I
precise I don't care about data in this database I won't keep any of
this database after my test and of course I will never send any content
to nobody.

So if someone can help me .


PS Sorry for my bad English I don't speak it very often :)

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] I Need database fot some test

[lordphoenix]

I'm developing an SQlite database administration tool (web site is
there : http://litemanager.tuxamily.org) for Linux using Mono and GTK#.
If you want test it use svn version the last commit content all
features of next release (Available only in french for the moment if
someone can help me for translation he is welcome). But in order to do
some test I would need some databases.
So if you have database with a significant volume of data and tables
and you don't mind to send me a copy it would be very great full. I
precise I don't care about data in this database I won't keep any of
this database after my test and of course I will never send any content
to nobody.

So if someone can help me .


PS Sorry for my bad English I don't speak it very often :)


signature.asc
Description: PGP signature

Re: [sqlite] Re: Newbie question about LIKE and ESCAPE

[A.J.Millan]

- Original Message - 
From: "Igor Tandetnik" <[EMAIL PROTECTED]>
To: "SQLite" 
Sent: Sunday, March 04, 2007 12:59 AM
Subject: [sqlite] Re: Newbie question about LIKE and ESCAPE


> For example, this query retrieves all rows where someField contains '%'
> character:
>
> SELECT * FROM someTable WHERE someField LIKE '%\%%' ESCAPE '\';
>
> > Can I use some like:
> >
> > SELECT someField IN someTable WHERE name LIKE 'xyzetc' ESCAPE
> > ;
>
> No. ESCAPE should be followed by a string consisting of exactly one
> character.
>

Leaving to a side the mentioned example (that is possibly a very old
comment), I had really misinterpreted the meaning and objective of the
ESCAPE clause. Now, thanks to yours comments and to Igor's example,
everything is clear and my querys works properly.

By the way, some simple examples next to each epigraph in the on-line manual
would be a lot of help.  Maybe a system of comments from the readers, as the
one in MySQL or PHP (I believe to remember).

Thaks to all.

A. J.Millan
ZATOR Systems


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: developers mailing list, ignored patches

[Adam Megacz]

[EMAIL PROTECTED] writes:
>> I also printed out and signed the copyright papers and mailed them in.

> Your copyright release and your patches arrived in today's post.  
> The postmark is smeared somewhat but it does appear to say 
> "2? DEC 2006" (where the ? is illegible.)

> So from Oakland, CA to Charlotte, NC in only 62 days.  And one
> wonders why the nobody sends letters anymore

Heh, not surprising.

If you do get a chance to look at the filesystem detection additions,
I would appreciate hearing back on that.  I can try to break it out
into a separate patch from the autoconf stuff.

  - a

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3 shared object question.

[aaron smith]

ok I got it figured out. I had to compile sqlite3>> ./configure
--disable-tcl



On 3/4/07, aaron smith <[EMAIL PROTECTED]> wrote:


Ultimately what I am trying to do is install the ruby wiki, Instiki. In
doing some research I found that rubys sqlite-lib wasn't compatible with
sqlite3. So i removed sqlite3 to get sqlite2. Later to find out that, oh.
sqlite3-ruby library is the one I needed to use. So now I have to figure out
how to get sqlite3 back on.

So far I have been compiling everything myself from source,
ruby,lighttpd,apache, etc.. Except sqlite. I was confused about this .so. As
on sqlites site there is a previous version binary of 2.8.xx. Which was a
binary that executed as expected. Is the solution just to recompile sqlite3
source myself? Any idea where I can find out information about RPM for
centOS?

thanks




On 3/3/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:
>
> On 3/4/07, aaron smith <[EMAIL PROTECTED]> wrote:
> > I'm somewhatenew to the linux world. I'm running a dedicated CenteOS
> box.
> > I'm trying to get sqlite3 installed. I've downloaded this binary
> > http://www.sqlite.org/sqlite-3.3.13.so.gz. gunzip it. I see an
> > sql.x.x.x.sofile. What do I do with that? if I try to execute it I get
> > s segmentation
> > fault.
>
> A .so file is similar to a windows DLL, so you can't execute it, only
> use it from your programs.
> What you need is either the sqlite binary or the sqlite source and
> compile it yourself.
>
> What you are trying to do? Your distribution should already have some
> pre-made package you can download to install it. If I'm not mistaken
> CentOS uses RPM as it's package manager, and if I remember correctly,
> yast uses SQLite internally, so maybe it is already installed?
>
>
> Regards,
> ~Nuno Lucas
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] Re: sqlite3_total_changes() and multiple connections

[Brownie]

It would be nice if sqlite stored a change count in the database that was 
easily accessible.


CREATE TABLE counter_table(n INTEGER);
INSERT INTO counter_table VALUES(0);

CREATE TRIGGER counter_trigger_insert
AFTER INSERT ON yourtable
FOR EACH ROW BEGIN
UPDATE counter_table SET n = n + 1;
END;

CREATE TRIGGER counter_trigger_update
AFTER UPDATE ON yourtable
FOR EACH ROW BEGIN
UPDATE counter_table SET n = n + 1;
END;

CREATE TRIGGER counter_trigger_delete
AFTER DELETE ON yourtable
FOR EACH ROW BEGIN
UPDATE counter_table SET n = n + 1;
END;

-
To unsubscribe, send email to [EMAIL PROTECTED]
-