Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Nico Williams]

On Tue, Oct 18, 2011 at 7:41 AM, John Drescher  wrote:
> My biggest reason for wanting a mailing list versus a forum is that I
> subscribe to 20+ mailing lists that all go to my gmail account with
> gmail rules to organize the content. If these mailing lists all were
> forums I would not bother with most of them since that would be a lot
> of work to check their status. With that said I rarely participate on
> the sqlite mailing list. This is mainly because I have not had many
> problems with it.

+1e6

Plus e-mail can be searched (so much so that with a nice enough MUA
one does not need filters).  Web fora?  Much harder to search across
many of them.

I subscribe to an enormous number of lists.  I could never manage that
with web fora.  Just say no to web fora.  No, no, no, no, no, ...

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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Nico Williams]

On Tue, Oct 18, 2011 at 4:35 AM, Frank Missel  wrote:
> I think that the sqlite-users e-mail list has enough traffic to warrant a
> proper forum.
>
> Has this been considered?

I know of no better forum than a mailing list for this sort of thing.

Mailing lists have archives that can be downloaded and/or browsed
online.  How useful!

Mailing lists can be subscribed to -- or not.

Mailing lists are based on a standard: e-mail.

Web-based fora are non-standard and inconvenient.

More mailing lists might be OK though.

My $.02,

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

Re: [sqlite] Column headers include subselect alias if column is from a subselect

[Pavel Ivanov]

> I would expect a column header of a for all of these queries, and all> other 
> databases I've tested with use a column header of a.  Is this a> bug in 
> SQLite?
Column name in the result set is undefined unless you define alias in
the query. So SQLite can name it whatever it wants.


Pavel


On Sun, Oct 16, 2011 at 2:02 PM, Jeremy Evans  wrote:
> I'm not sure if this is expected/desired behavior, but I think it is 
> incorrect:
>
> $ sqlite3
> SQLite version 3.7.8 2011-09-19 14:49:19
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .headers ON
> sqlite> CREATE TABLE b (a integer);
> sqlite> INSERT INTO b VALUES (1);
> sqlite> SELECT * FROM b;
> a
> 1
> sqlite> SELECT b.a FROM b;
> a
> 1
> sqlite> SELECT c.a FROM b AS c;
> a
> 1
> sqlite> SELECT c.a FROM (SELECT * FROM b) AS c;
> c.a
> 1
> sqlite> SELECT c.a FROM b JOIN b AS c USING(a);
> a
> 1
> sqlite> SELECT c.a FROM b JOIN (SELECT * FROM b) AS c USING(a);
> c.a
> 1
>
> Note how if the column comes from a subselect, the subselect alias
> qualifier is included in the column header.
>
> I would expect a column header of a for all of these queries, and all
> other databases I've tested with use a column header of a.  Is this a
> bug in SQLite?
>
> Jeremy
> ___
> 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] Fast JOIN

[Fabian]

2011/10/19 Fabian 
>
>
> Maybe there is just no easy way to do what I want, I will take a good night
> sleep about it ;)
>
>
I think the correct query would be:

SELECT table1.data1, table2.data2
FROM
(
SELECT table1.rowid, table1.data1
WHERE table1.data1 = 10
) AS table1
JOIN table2
ON table1.rowid = table2.rowid
ORDER BY table2.data2 DESC
OFFSET 0 LIMIT 250

Which should make sure that only the rows matching WHERE are going to be
sorted (instead of all rows).

Since I moved the TEXT columns out of my main table to a FTS table, I have
all these complex and inefficient joins, because all data is divided between
two tables with identical rowids. I hope some day FTS supports INTEGER
columns, so I can merge the two back together, and get rid of the joins :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fast JOIN

[Woody]

you will need an inner select that selects everything and orders that, then an 
outside select that pages from the inner select.


Woody
Wizard, at large
"I'm in shape, round is a shape!"



 

--- On Tue, 10/18/11, Fabian  wrote:


From: Fabian 
Subject: Re: [sqlite] Fast JOIN
To: "General Discussion of SQLite Database" 
Date: Tuesday, October 18, 2011, 10:17 PM


>
>
> Assuming that is a typo, does the following not do what you want?
>
> SELECT table1.data1, table2.data2
> FROM
> (
> SELECT table1.rowid, table1.data1
> WHERE table1.data1 = 10
> ORDER BY table1.rowid DESC
> OFFSET 0 LIMIT 250
> ) AS table1
> JOIN table2
> ON table1.rowid = table2.rowid
> ORDER BY table2.rowid;
>
>
No, because it only sorts the current page. When the user scrolls down using
the scrollbar, and the second page is fetched (OFFSET 250 LIMIT 250), it
does not match up with the sorting of the previous page. Suppose it was
sorted by a TEXT column of table2, it would look like this:

Page 1:

aaa
abb
ddd

Page 2:

bba
cca
dda

Maybe there is just no easy way to do what I want, I will take a good night
sleep about it ;)
___
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] Fast JOIN

[Fabian]

>
>
> Assuming that is a typo, does the following not do what you want?
>
> SELECT table1.data1, table2.data2
> FROM
> (
> SELECT table1.rowid, table1.data1
> WHERE table1.data1 = 10
> ORDER BY table1.rowid DESC
> OFFSET 0 LIMIT 250
> ) AS table1
> JOIN table2
> ON table1.rowid = table2.rowid
> ORDER BY table2.rowid;
>
>
No, because it only sorts the current page. When the user scrolls down using
the scrollbar, and the second page is fetched (OFFSET 250 LIMIT 250), it
does not match up with the sorting of the previous page. Suppose it was
sorted by a TEXT column of table2, it would look like this:

Page 1:

aaa
abb
ddd

Page 2:

bba
cca
dda

Maybe there is just no easy way to do what I want, I will take a good night
sleep about it ;)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fast JOIN

[Simon Davies]

On 18 October 2011 22:09, Fabian  wrote:
> I'm working on a pagination system where two tables need to be joined.
.
.
.
>
> I got very good advice on this mailing-list, to change the query into this:
>
> SELECT table1.data1, table2.data2
> FROM
> (
>
> SELECT table1.data1
> WHERE table1.data1 = 10
> ORDER BY table1.rowid DESC
> OFFSET 0 LIMIT 250
>
> ) AS table1
> JOIN table2
> ON table1.rowid = table2.rowid
.
.
.
> JOIN. So far, so good.
>
> Then the second problem appeared: the user is free to sort on any column of
> the output. So you can have the situation where the WHERE is on table1 but
> ORDER BY is on table 2. At first I wanted to solve this by using the old
> (slow) JOIN for cases like this. But it turned out to be unworkable: if the
> query only returns 3 rows, it was still spending precious seconds sorting
> all the other rows in the table.
>
> Is there any solution for this? All things I tried completely break the
> pagination system, because it fully depends on having ORDER and OFFSET in
> the same sub-query.

Your queries above produce syntax errors, as there is no rowid from
the inner subquery on which to perform the join.

Assuming that is a typo, does the following not do what you want?

SELECT table1.data1, table2.data2
FROM
(
SELECT table1.rowid, table1.data1
WHERE table1.data1 = 10
ORDER BY table1.rowid DESC
OFFSET 0 LIMIT 250
) AS table1
JOIN table2
ON table1.rowid = table2.rowid
ORDER BY table2.rowid;

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

Re: [sqlite] UPDATE question

[Donald Griggs]

Hi, Barry,

Regarding:  "Could you please explain me why the indexing is so important?
(for
future reference)."

If you're asking just in *general* why indexes can speed up searching a
database table, you might want to look at most any sql tutorial, or
resources such as:
http://en.wikipedia.org/wiki/Index_%28database%29

You might then want to look at the sqlite document for the
EXPLAIN QUERY PLAN

If I've misunderstood your question and you're asking something much more
specific, I apologize in advance.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fast JOIN

[Petite Abeille]

On Oct 18, 2011, at 11:09 PM, Fabian wrote:

> Is there any solution for this? 

Perhaps this is not a technical issue, but rather a design one, as you seem to 
be, hmmm, tilting at windmills.  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fast JOIN

[Fabian]

I'm working on a pagination system where two tables need to be joined. At
first my query looked like this:

SELECT table1.data1, table2.data2
FROM table1
JOIN table2 ON table1.rowid = table2.rowid
WHERE table1.data1 = 10
ORDER BY table1.rowid DESC
OFFSET 0 LIMIT 250

I got very good advice on this mailing-list, to change the query into this:

SELECT table1.data1, table2.data2
FROM
(

SELECT table1.data1
WHERE table1.data1 = 10
ORDER BY table1.rowid DESC
OFFSET 0 LIMIT 250

) AS table1
JOIN table2
ON table1.rowid = table2.rowid

This returns exactly the same results, but the performance is much better
because it only has to JOIN the rows that match the WHERE clause.

Soon, the first problem appeared: in some cases the WHERE clause was on a
column from table2, for example: WHERE table2.data2 = 10, for which the
above query wouldn't work. I changed my code so that when it detects the
WHERE is on table2, it uses this query:

SELECT table1.data1, table2.data2
FROM
(

SELECT table2.data2
WHERE table2.data2 = 10
ORDER BY table2.rowid DESC
OFFSET 0 LIMIT 250

) AS table2
JOIN table1
ON table2.rowid = table1.rowid

Which is nothing more than the exact reverse of the previous query. When my
code detects that WHERE needs BOTH table1 and table2, it uses the old (slow)
JOIN. So far, so good.

Then the second problem appeared: the user is free to sort on any column of
the output. So you can have the situation where the WHERE is on table1 but
ORDER BY is on table 2. At first I wanted to solve this by using the old
(slow) JOIN for cases like this. But it turned out to be unworkable: if the
query only returns 3 rows, it was still spending precious seconds sorting
all the other rows in the table.

Is there any solution for this? All things I tried completely break the
pagination system, because it fully depends on having ORDER and OFFSET in
the same sub-query.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unexpected behavior when renaming a virtual table (rtree) in a UTF-16 database

[Peter Aronson]

This behavior seems to happen in both stock standard 3.7.7.1 on 32-bit Windows 
XP and my customized 3.7.8 on Solaris 9 (Sparc).  Here's the capture from 
Windows:

D:\peter\sqlite-shell-win32-x86-3070701>sqlite3 this_is_a_new_db.db
SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma encoding = "UTF-16be";
sqlite> create virtual table rt using rtree (i,x1,x2,y1,y2);
sqlite> pragma encoding;
UTF-16be
sqlite> .tables
rt rt_node    rt_parent  rt_rowid
sqlite> alter table rt rename to rx;
sqlite> .tables
_node    _parent  _rowid   rx
sqlite>

However, if I choose UTF-16le instead, it produces a different, although 
equally 
incorrect result:

D:\peter\sqlite-shell-win32-x86-3070701>sqlite3 this_is_a_new_db.db
SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma encoding = "UTF-16le";
sqlite> create virtual table rt using rtree (i,x1,x2,y1,y2);
sqlite> pragma encoding;
UTF-16le
sqlite> .tables
rt rt_node    rt_parent  rt_rowid
sqlite> alter table rt rename to rx;
sqlite> .tables
r_node    r_parent  r_rowid   rx
sqlite>

But it works correctly with UTF-8:

D:\peter\sqlite-shell-win32-x86-3070701>sqlite3 this_is_a_new_db.db
SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma encoding = "UTF-8";
sqlite> create virtual table rt using rtree (i,x1,x2,y1,y2);
sqlite> pragma encoding;
UTF-8
sqlite> .tables
rt rt_node    rt_parent  rt_rowid
sqlite> alter table rt rename to rx;
sqlite> .tables
rx rx_node    rx_parent  rx_rowid
sqlite>

I did a little debugging on Solaris, and it appeared that in the UTF-16be case, 
a zero-length string is being passed in as the 2nd argument (zNewName) for 
rtreeRename().  If I had to guess, given the difference in behavior between 
UTF-16le and UTF-16be, I'd say when the database is in UTF-16, that the virtual 
table interface is passing a UTF-16 string into the xRename function for the 
zNewName argument.  Given that zNewName is declared as a pointer to const char, 
I can see why rtreeRename isn't expecting this.

I suspect that someone familiar with the parser could probably figure out what 
the problem is in a few minutes.  In the meantime I can avoid this problem by 
creating my databases using UTF-8 (they're being created in UTF-16 right now 
because I'm calling sqlite3_open16(), which seemed natural as the level of code 
above mine was supplying the database name in UTF-16, but I can change that).

Best,

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

Re: [sqlite] UPDATE question

[Barry1337]

Igor Tandetnik wrote:
> 
> Barry1337  wrote:
>> So I need, for every record in STAYSPEC, to find another record in
>> STAYSPEC
>> that has the same STAYNUM field and an ORDER_SPEC field that is 1 more. I
>> want to replace the date_out from STAYSPEC with that date (in text
>> format).
>> 
>> If such a record does not exist (EXISTS) then it needs to take the
>> date_out
>> from the STAYHOSP table where the STAYNUM is the same.
> 
> Try this:
> 
> update STAYSPEC set date_out = coalesce(
>   (select date_in from STAYSPEC ss2
>where ss2.staynum = STAYSPEC.staynum and
>ss2.order_spec = STAYSPEC.order_spec + 1),
>   (select date_out from STAYHOSP sh where sh.staynum = STAYSPEC.staynum)
> );
> 
>> Whenever I execute the above query it doesn't give an error or something,
>> it's just keeps running without ever stopping !
> 
> Make sure you have an index on STAYSPEC(staynum, order_spec) (or at least
> on STAYSPEC(staynum) ), as well as one on STAYHOSP(staynum)
> 
> -- 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

Thanks! You made my day :) Apparantly the indexing stopped the infinite
querying. Could you please explain me why the indexing is so important? (for
future reference). 

-- 
View this message in context: 
http://old.nabble.com/UPDATE-question-tp32673794p32677141.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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Petite Abeille]

On Oct 18, 2011, at 9:21 PM, Jos Groot Lipman wrote:

>> Yes, go install your forum and leave us in peace :P
> 
> Who said mail-lists get less flame-wars ;-)

Flame warriors roster:

http://redwing.hutman.net/~mreed/

Take the personality test!

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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Jos Groot Lipman]

> Yes, go install your forum and leave us in peace :P

Who said mail-lists get less flame-wars ;-)

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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Kees Nuyt]

On Tue, 18 Oct 2011 12:21:50 +, Jean-Denis MUYS
 wrote:

>On 18 oct. 2011, at 11:47, Frank Missel wrote:
>
>> I see several advantages to having a forum:
>> 
>> 1. Several subject forums as mentioned
>> 
> Mail can have as many subjects as desired
>

>> 2. Better view of threads with several levels being immediately displayed
>> 
>
> My mail client threads far better than most forums (fora?)
>

>> 3. Preview of entries and  editing of them even after they are posted (by
>> the author)
>
> Which can be seen as a liability
>
 
>> 4. Formatted rather than plain text
>
> Which mail is capable of
>

>> 5. No need for e-mail-addresses to be exposed
>
> Couldn't a mailing list hide email addresses too?
>
> On the other hand, I find mailing lists much better:
> I can read them off line, I can also answer them
> off line (my client will send my answers as soon
> as it gets online), I can archive any and/or all
> posts that I find valuable…
>
> In fact, when fora (forums?) propose a mailing list
> interface (i.e. google groups), I prefer subscribing
> to them as a mailing list.

+1
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Petite Abeille]

On Oct 18, 2011, at 11:57 AM, Mark Schonewille wrote:

> However, if you really want a forum, install one on your web server and find 
> out how many people use it.

Yes, go install your forum and leave us in peace :P

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

Re: [sqlite] Limit COUNT

[Frank Missel]

> boun...@sqlite.org] On Behalf Of Bart Smissaert
> 
> That seems to be the answer and after some quick testing it looks it makes
it
> more efficient as well!
> 
> On Tue, Oct 18, 2011 at 1:36 PM, Kit  wrote:
> > 2011/10/16 Fabian :
> >> How can you limit a count-query? I tried:
> >> SELECT COUNT(*) FROM table LIMIT 5000
> >
> > SELECT min(COUNT(*),5000) FROM table;
> > --

My first impression was that the count(*) inside the min() would access all
the records anyway (perhaps not all the columns though) and thus in fact
still access more than 5000 records, even if it was hidden by the min
function.

To test this I took a database containing tables with millions of records
and executed the following statements:

1. select count(*) from table;

2. select min(count(*), 5000) from table;

3. select count(*) from (select null from table limit 5000);

Now to really test properly the computer would have to be restarted before
each test in order to avoid buffering of the disks, etc.
However, I could not be bothered in this case and just used three separate
tables with identical structure but a different number of records (between 4
and 5 million). As expected with the difference in execution times between
the three statements it did not matter that much.

1. Around 150 seconds

2. Around 14 seconds

3. Around 50 milliseconds !!!

Thus it seems that although variation two perhaps avoids fully reading the
records, it still have to touch them (or perhaps the primary key) which is
better than a normal full select count(*) on the table. Perhaps somebody
know the inner workings better and can explain the difference of factor 10
between variation 1 and 2.

To actually limit the count statement only variation 3 actually works (which
has been suggested earlier in the thread) and is of course several orders of
magnitude faster.

Somebody had also suggested: 
select count(*) from (select 1 from table limit 5000);

Notice the inner select of 1 instead of null. 
As predicted this is slightly slower at around 60 milliseconds. 

So selecting null is always better in sub queries when only the count or
existence of records are needed and not actual values from any of the
columns.


/Frank Missel

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

[sqlite] SQLIte compilation error on AIX 6.1 - Undefined symbol: .__gcc_qsub

[Grice, Lynton (L)]

Hi all,

I am running AIX 6.1, and GCC 4.2 and when I try compile SQLite (I think 
version 3.7.5) using the following commands:

gcc -maix64 -D_FILE_OFFSET_BITS=64 -c -fPIC -DHAVE_USLEEP ./sqlite/sqlite3.c -o 
./sqlite/sqlite3.o
gcc -maix64 -shared -L/usr/lib/threads -o ./sqlite/libsqlite3.so 
./sqlite/sqlite3.o -ldl -lpthread

I get the following errors:

ld: 0711-317 ERROR: Undefined symbol: .__gcc_qsub
ld: 0711-317 ERROR: Undefined symbol: .__gcc_qmul
ld: 0711-317 ERROR: Undefined symbol: .__gcc_qadd
ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information.

Do you have any idea what this could be caused from?

Any help would be hugely appreciated ;-)

Lynton


NOTICE: Please note that this eMail, and the contents thereof, 
is subject to the standard Sasol eMail legal notice which may be found at: 
http://www.sasol.com/legalnotices   
   

If you cannot access the legal notice through the URL attached and you wish 
to receive a copy thereof please send an eMail to 
legalnot...@sasol.com

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

Re: [sqlite] UPDATE question

[Marshall Cline]

Because your query was fairly complex, to help everyone debug it, I
simplified it using simple names (tables are a, b, c; fields are m, n, x, y,
z) and much simpler use of whitespace / indentation:

UPDATE a
SET x = CASE
WHEN EXISTS 
(
SELECT * 
FROM a
AS b
WHERE b.n = a.n
AND b.m = a.m + 1
)
THEN 
(
SELECT y
FROM a
AS c
WHERE c.n = a.n
AND c.m = a.m + 1
)
ELSE 
(
SELECT d.z
FROM d 
WHERE d.n = a.n
)
END;

Note that the name of field 'z' (from table 'd') happens to be the same as
the name of field 'x' (from table 'a'), but since they were from different
tables, I chose different names above - obviously you'll need to translate
everything back to your names before any advice can be useful.

So that's it - I did this to help others debug your problem. However I will
offer an observation which may (or may not) help: You might want to try
using a qualified name in the SELECT within the THEN clause ("SELECT y"
rather than "SELECT c.y"). I'm not sure if a qualified name would help in
the SET clause, but you could also try that.

Marshall


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Barry1337
Sent: Tuesday, October 18, 2011 6:04 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] UPDATE question


I have the following query:

Code:

UPDATE STAYSPEC SET date_out =
CASE WHEN EXISTS 
 (SELECT * 
  FROM STAYSPEC AS STAYSPEC2 
  WHERE (STAYSPEC2.STAYNUM = STAYSPEC.STAYNUM) AND 
 (STAYSPEC2.ORDER_SPEC = STAYSPEC.ORDER_SPEC+1)) THEN 
 (SELECT date_in FROM STAYSPEC AS STAYSPEC3 
  WHERE (STAYSPEC3.STAYNUM = STAYSPEC.STAYNUM) 
  AND (STAYSPEC3.ORDER_SPEC = STAYSPEC.ORDER_SPEC+1)) ELSE 
  (SELECT STAYHOSP.date_out FROM STAYHOSP 
  WHERE (STAYHOSP.STAYNUM = STAYSPEC.STAYNUM)) END;

date_in and date_out have both the type TEXT.

So I need, for every record in STAYSPEC, to find another record in STAYSPEC
that has the same STAYNUM field and an ORDER_SPEC field that is 1 more. I
want to replace the date_out from STAYSPEC with that date (in text format).

If such a record does not exist (EXISTS) then it needs to take the date_out
from the STAYHOSP table where the STAYNUM is the same.

Whenever I execute the above query it doesn't give an error or something,
it's just keeps running without ever stopping ! Since it's quite hard to
debug this I sincerely hope someone can help me with this.
--
View this message in context:
http://old.nabble.com/UPDATE-question-tp32673794p32673794.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

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

Re: [sqlite] How about a proper forum rather than an e-mail list

[ajm]

Definitely I would like to have the SQLite mail list in a fashion as 
StackOverlow.


>
>  Original Message 
> From: epank...@comcast.net
> Sent:  Tue, 18 Oct 2011 16:23:11 +0200
>
> 
>
>I'll say one thing - a forum would have saved me the wasted time of having to 
>download and then delete all of this discussion about forums :) 
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Bart Smissaert]

That seems to be the answer and after some quick testing it looks it
makes it more efficient as well!

RBS


On Tue, Oct 18, 2011 at 1:36 PM, Kit  wrote:
> 2011/10/16 Fabian :
>> How can you limit a count-query? I tried:
>> SELECT COUNT(*) FROM table LIMIT 5000
>
> SELECT min(COUNT(*),5000) FROM table;
> --
> Kit
> ___
> 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] Changing the data type of an existing table

[Frank Missel]

Hi Marshall,

A good suggestion.

The reason I chose to "transform" the schema instead was that there are a
number of databases with similar structure on different sites which has the
same declared data types. Also there will be several more in the future.
This is supported by a framework of some classes that I use to handle
SQLite. The frame work has been updated so only those declared data types
that work with Excel (i.e. Excel recognizes the data as the correct type and
use sum and other numerical functions). Thus future databases would have
only the "correct" declared data types. 

Rather than have some databases which need special handling with views I
prefer to handle the situation once and for all in existing and future
databases and not have to worry about having special views for some tables
in some databases at certain sites.

/Frank

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Marshall Cline
> Sent: 18 October 2011 22:30
> To: General Discussion of SQLite Database
> Cc: General Discussion of SQLite Database
> Subject: Re: [sqlite] Changing the data type of an existing table
> 
> I think you've already found a good solution to your problem by directly
> modifying the master table, so this is mostly an academic curiosity, but
would
> it be a viable alternative to use a VIEW? In other words, rather than
messing
> with the TABLE that holds the data, could you simply create a VIEW that
> exposes the information in the type you want (via a cast?), then your
excel
> can access the data via the VIEW rather than the TABLE?
> 
> To reiterate, I'm merely asking about viability here.
> 
> Marshall
> 
> Sent from my iPhone
> 

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

Re: [sqlite] How about a proper forum rather than an e-mail list

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 18/10/11 05:04, Igor Tandetnik wrote:
> Frank Missel  wrote:
>> I think that the sqlite-users e-mail list has enough traffic to
>> warrant a proper forum.
> 
> For what it's worth, I'm using GMane (http://gmane.org/),

It should be noted that Gmane can also give the list a forum like
presentation and a blog like interface in addition to NNTP.  The former
two are substantially similar to how many forum systems present messages:

  http://dir.gmane.org/gmane.comp.db.sqlite.general

They also provide handy search functionality.  There are also RSS feeds
you can use in the viewer of your choice.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6dlRAACgkQmOOfHg372QRyDQCfSsFeDK2EaZZcWT2g6ZXkSaUg
7mQAn2okUjyt8/5AGGzmt8sqFS9xAqhC
=lyNR
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Arbol One]

Sorry folks, but it does not matter who is right or wrong, if you want to
open a forum... go ahead, you can even announce it here; those interested
will follow your idea, but for the time being you are taking a lot of
bandwidth, time and creating distraction. Please, I encourage you to open
the forum and to announce the existence of it here, but stop posting the
plusses and minuses of it. 
Thanks in advance!!

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Frank Missel
Sent: Tuesday, October 18, 2011 10:10 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] FW: How about a proper forum rather than an e-mail
list

Hi Mark,

Well, perhaps you are right. 

But I am not going to take on that. 
I am a user of SQLite and can perhaps help with some posts and suggestions
now and then.
So I am just going to throw in the idea and state the pros vs. cons as I see
them .

Best regards,

Frank


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Mark Schonewille
> Sent: 18 October 2011 21:54
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] FW: How about a proper forum rather than an e-mail
list
> 
> Hi,
> 
> I know from experience that there really is no point whatsoever in
discussing
> whether a forum is better than mail or not. People are just not going to
> agree. Just set up your forum and find out whether you're going to have
> subscribers.
> 
> --
> Best regards,
> 
> Mark Schonewille
> 
> Economy-x-Talk Consulting and Software Engineering
> Homepage: http://economy-x-talk.com
> Twitter: http://twitter.com/xtalkprogrammer
> KvK: 50277553
> 
> See what you get with only a small contribution. All our LiveCode
downloads
> are listed at http://qery.us/zr
> 
> On 18 okt 2011, at 15:49, Frank Missel wrote:
> 
> >> boun...@sqlite.org] On Behalf Of Jean-Denis MUYS
> >>
> >>> 1. Several subject forums as mentioned
> >>>
> >> Mail can have as many subjects as desired
> >
> > Well, so can forum posts. The point is that the forum is divided into
> > main categories above the subject of the post
> >
> >
> >>> 2. Better view of threads with several levels being immediately
> >>> displayed
> >>>
> >> My mail client threads far better than most forums (fora?)
> >
> > I seriously doubt that :-).
> > I mean how much better than a totally ordered hierarchy with several
> > levels and forks can it be.
> >
> > But perhaps your mail client is very good. Which one do you use?
> >
> > Also, in a forum, you can see posts from a selected author sorted by
date.
> >
> >
> >>> 3. Preview of entries and  editing of them even after they are
> >>> posted (by the author)
> >>
> >> Which can be seen as a liability
> >
> > To be sure.
> > But then again until anyone has posted a reply. It can be used to
> > correct a typo or add info (where it makes sense rather than add a new
> post).
> >
> >
> >>> 4. Formatted rather than plain text
> >>
> >> Which mail is capable of
> >
> > Any formatting is stripped. At least it is so on the sqlite-users
mail-list.
> >
> >
> >>> 5. No need for e-mail-addresses to be exposed
> >>>
> >> Couldn't a mailing list hide email addresses too?
> >
> > Perhaps it could.
> > I find, however, that e-mails are spread all over the place on the
> > sqlite-users mailing list. So it is not being done here.
> > That is one of my big objections as it invariable leads to spamming of
> > the members.
> >
> >
> >> On the other hand, I find mailing lists much better: I can read them
> >> off
> > line, I
> >> can also answer them off line (my client will send my answers as soon
> >> as
> > it
> >> gets online), I can archive any and/or all posts that I find valuable.
> >
> > I get messages from others when they are on their way to work or home
> > sitting in trains and busses.
> > Mostly people are just online through wireless or mobile networks. It
> > is considered "in" to be able to access the Internet at all times in
> > all places, so I find that point a bit moot nowadays.
> >
> >
> >> In fact, when fora (forums?) propose a mailing list interface (i.e.
> >> google groups), I prefer subscribing to them as a mailing list.
> >>
> >> Even better than mailing lists: newsgroups. Except my company only
> >> lets us use port 80 in addition to the mail gateway, so I can't use
> newsgroups.
> >
> > Who am I tell someone what to prefer.
> > I just find, however, it is a bit religious like choice of OS or
> > gadgets. I can see that e-mail lists can work and in the past could
> > have a lot of advantages. Nowadays, I find not as many good reasons to
> > prefer them over a proper forum which have some really nice features.
> >
> >
> > /Frank Missel
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Frank Missel]

Hi Mark,

I started this thread with a  suggestion of having a forum for SQLite rather
than a mailing list. That is the subject line.
All posts have been related to that. I wished to see what the consent or
lack thereof was in the community. Possibly it would then also be noted by
those that presently run the SQLite mailing list.
So how is that off topic exactly? 
Those that don't have anything to add to it, don't have to bother with it.

If you had started another thread about some technical aspect of SQLite and
I then started mailing to that thread inserting irrelevant posts about a
forum I would agree with you. But that is not quite the case is it. 
Your reply come over as somehow offended and I don't really see why.

Possibly you may feel that these post take up space in the general SQLite
mailing list, but that is one point of having a forum, that one can easily
overview and follow the threads which are of interest -- although I did not
start the thread to demonstrate it in that way :-).

I appreciate your suggestion about starting a forum myself. I do feel,
however, as stated earlier that it is not my business to take over the
running of the SQLite website, mailing list, forum, etc. That would belong
with the organisation, company, persons that take care of that now. By
having this thread with people replies and opinions it can then give an idea
of the pro and cons as well as an idea of how would be for and against it.


Best regards,

Frank


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Mark Schonewille
> Sent: 18 October 2011 22:19
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] FW: How about a proper forum rather than an e-mail
list
> 
> Hi Frank,
> 
> If you want to discuss the pros and cons of a forum, I think you should
ask
> your question on a mailing list or forum about mailing lists and forums.
Your
> question is very off-topic if you don't intend to start a SQLite forum
yourself,
> but I think you could ask the members of this SQLite mailing list "who
wants
> to start a forum?" without starting a lengthy off-topic discussion. I'm
out of
> this discussion now.
> 
> --
> Best regards,
> 
> Mark Schonewille
> 

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

Re: [sqlite] How about a proper forum rather than an e-mail list

[Simon Slavin]

On 18 Oct 2011, at 3:22pm, epank...@comcast.net wrote:

> I'll say one thing - a forum would have saved me the wasted time of having to 
> download and then delete all of this discussion about forums :)

'man killfile'

Or, since we're now in the 21st century, create a rule for your mail app.

The way to settle this is easy: leave the mailing list in place.  Create a web 
forum.  If people abandon the mailing list and start using the web forum 
instead, it worked.  If people stay with the mailing list, the mailing list is 
superior.

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

Re: [sqlite] How about a proper forum rather than an e-mail list

[Brad Stiles]

On Tue, Oct 18, 2011 at 8:04 AM, Igor Tandetnik  wrote:
> Frank Missel  wrote:
>> I think that the sqlite-users e-mail list has enough traffic to warrant a
>> proper forum.
>
> For what it's worth, I'm using GMane (http://gmane.org/), which is a mailing 
> list-to-NNTP
> gateway and happens to carry this list. I'm old-fashioned enough to believe 
> that an NNTP
> newsgroup *is* the proper forum. Can't stand modern Web-based forum 
> interfaces.

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

Re: [sqlite] Changing the data type of an existing table

[Marshall Cline]

I think you've already found a good solution to your problem by directly 
modifying the master table, so this is mostly an academic curiosity, but would 
it be a viable alternative to use a VIEW? In other words, rather than messing 
with the TABLE that holds the data, could you simply create a VIEW that exposes 
the information in the type you want (via a cast?), then your excel can access 
the data via the VIEW rather than the TABLE?

To reiterate, I'm merely asking about viability here. 

Marshall

Sent from my iPhone

On Oct 16, 2011, at 4:48 AM, "Frank Missel"  wrote:

> Hi Dan,
> 
>> The two values in table "t" are stored in integer form. Were you to
> magically
>> change the database schema without rebuilding the underlying
>> b-trees:
>> 
>>   CREATE TABLE t(a TEXT);
>> 
>> and then execute the same SELECT, it would not work. SQLite would search
>> the index for text value '1', not integer value 1 (since it assumes that
> all
>> values had the TEXT affinity applied to them when they were inserted). And
>> the query would return no rows.
> 
> You are right. However, in my case it is a bit special in that the values in
> the fields actually already are stored correctly as REAL numbers but because
> the declared data type is DECIMAL (which is used in some databases), Excel
> will not treat the real numbers as such. Changing the declared data type
> does the trick!
> 
> As for text to integer as in your example, I guess it could also be done.
> First the declared data type could be changed as I have described. After
> that an update statement could set the  = CAST( as INTEGER).
> But you are right that indexes in this case would have to be rebuilt with
> the REINDEX statement. However, that might still be better than having to
> recreate the whole table and build indexes.
> 
> 
> /Frank
> 
> ___
> 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] How about a proper forum rather than an e-mail list

[epankoke]

I'll say one thing - a forum would have saved me the wasted time of having to 
download and then delete all of this discussion about forums :) 




- Original Message -
From: "Danny Staten"  
To: "General Discussion of SQLite Database"  
Sent: Tuesday, October 18, 2011 9:59:09 AM 
Subject: Re: [sqlite] How about a proper forum rather than an e-mail list 

I for one would love a forum, and disagree about it being the same as an 
email list. I would love to not see 40+ emails in my inbox every day from 
this mailing list, but I do find the available resource handy to have when I 
need it. A forum would allow us to be more active because we can quickly 
and easily ask questions and follow the conversation rather than having to 
scan through a myriad of other emails, or try and follow the reply chain 
when a flood comes in. It would also allow us to keep our inboxes a lot 
cleaner and clutter free which would be very very nice. 

-Original Message- 
From: Frank Missel 
Sent: Tuesday, October 18, 2011 6:35 AM 
To: 'Teg' ; 'General Discussion of SQLite Database' 
Subject: Re: [sqlite] How about a proper forum rather than an e-mail list 

Hi Teg, 

> I love forums and consider them far superior to email if only because it's 
> easier to follow a topic with less quoting needed. The downside is that 
> someone has to manage the forum. I've managed a forum for the past 10 
> years and there's a daily spam cleanup process and constant attacks and 
> required upgrades. You have to set the tone and be pretty ruthless about 
> flaming too. 
> 
> I'd like to see a forum. I just wouldn't want to manage it. 

Okay, but if the posting is by members only would it not be the same as the 
e-mail-lists. 
I don't see a lot of spam in the e-mail-list, so either it also monitored by 
someone or the fact that it can only be accessed by members makes for a well 
behaved list which would be the same for the forum. 

/Frank 

___ 
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

Re: [sqlite] How about a proper forum rather than an e-mail list

[Eugene N]

People might think that nothing will change if we move to a forum; Yet there
is a great difference between a letter (though its email, not paper) and a
forum post; Letters are usually more formal, more tidy, without spam and
trolling;
   A forum post and a forum itself is completely different media, and people
have a different attitude to forum posts then they do to letters;
Letters resemble real-world communication more closely, in a way that they
provide a link between two people, not 'forum members', but real people;
   Its quite ingenious when you think about this: stationary phones are used
to connect buildings together (chain of command, etc), letters (and mobile
phones) are used to connect people, no matter where they are;
Internet and forums connect people, no matter who they are, even strangers
can talk, given that the subject is interesting to both parties;
   Mailing lists are the last defense of the web, where one can converse to
people he formally knows, about things they are formally involved in;

As for keeping email accounts clean and compact - i suggest one can write a
nice script to delete unneeded things;

2011/10/18 Danny Staten 

> I for one would love a forum, and disagree about it being the same as an
> email list.  I would love to not see 40+ emails in my inbox every day from
> this mailing list, but I do find the available resource handy to have when I
> need it.  A forum would allow us to be more active because we can quickly
> and easily ask questions and follow the conversation rather than having to
> scan through a myriad of other emails, or try and follow the reply chain
> when a flood comes in.  It would also allow us to keep our inboxes a lot
> cleaner and clutter free which would be very very nice.
>
> -Original Message- From: Frank Missel
> Sent: Tuesday, October 18, 2011 6:35 AM
> To: 'Teg' ; 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] How about a proper forum rather than an e-mail list
>
>
> Hi Teg,
>
>  I love forums and consider them far superior to email if only because it's
>> easier to follow a topic with less quoting needed. The downside is that
>> someone  has  to  manage the forum. I've managed a forum for the past  10
>> years  and there's a daily spam cleanup process and constant attacks and
>> required upgrades.  You have to set the tone and be pretty ruthless about
>> flaming too.
>>
>> I'd like to see a forum. I just wouldn't want to manage it.
>>
>
> Okay, but if the posting is by members only would it not be the same as the
> e-mail-lists.
> I don't see a lot of spam in the e-mail-list, so either it also monitored
> by
> someone or the fact that it can only be accessed by members makes for a
> well
> behaved list which would be the same for the forum.
>
> /Frank
>
> __**_
> 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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Mark Schonewille]

Hi Frank,

If you want to discuss the pros and cons of a forum, I think you should ask 
your question on a mailing list or forum about mailing lists and forums. Your 
question is very off-topic if you don't intend to start a SQLite forum 
yourself, but I think you could ask the members of this SQLite mailing list 
"who wants to start a forum?" without starting a lengthy off-topic discussion. 
I'm out of this discussion now.

--
Best regards,

Mark Schonewille

Economy-x-Talk Consulting and Software Engineering
Homepage: http://economy-x-talk.com
Twitter: http://twitter.com/xtalkprogrammer
KvK: 50277553

See what you get with only a small contribution. All our LiveCode downloads are 
listed at http://qery.us/zr

On 18 okt 2011, at 16:10, Frank Missel wrote:

> Hi Mark,
> 
> Well, perhaps you are right. 
> 
> But I am not going to take on that. 
> I am a user of SQLite and can perhaps help with some posts and suggestions
> now and then.
> So I am just going to throw in the idea and state the pros vs. cons as I see
> them .
> 
> Best regards,
> 
> Frank

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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Frank Missel]

Hi Mark,

Well, perhaps you are right. 

But I am not going to take on that. 
I am a user of SQLite and can perhaps help with some posts and suggestions
now and then.
So I am just going to throw in the idea and state the pros vs. cons as I see
them .

Best regards,

Frank


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Mark Schonewille
> Sent: 18 October 2011 21:54
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] FW: How about a proper forum rather than an e-mail
list
> 
> Hi,
> 
> I know from experience that there really is no point whatsoever in
discussing
> whether a forum is better than mail or not. People are just not going to
> agree. Just set up your forum and find out whether you're going to have
> subscribers.
> 
> --
> Best regards,
> 
> Mark Schonewille
> 
> Economy-x-Talk Consulting and Software Engineering
> Homepage: http://economy-x-talk.com
> Twitter: http://twitter.com/xtalkprogrammer
> KvK: 50277553
> 
> See what you get with only a small contribution. All our LiveCode
downloads
> are listed at http://qery.us/zr
> 
> On 18 okt 2011, at 15:49, Frank Missel wrote:
> 
> >> boun...@sqlite.org] On Behalf Of Jean-Denis MUYS
> >>
> >>> 1. Several subject forums as mentioned
> >>>
> >> Mail can have as many subjects as desired
> >
> > Well, so can forum posts. The point is that the forum is divided into
> > main categories above the subject of the post
> >
> >
> >>> 2. Better view of threads with several levels being immediately
> >>> displayed
> >>>
> >> My mail client threads far better than most forums (fora?)
> >
> > I seriously doubt that :-).
> > I mean how much better than a totally ordered hierarchy with several
> > levels and forks can it be.
> >
> > But perhaps your mail client is very good. Which one do you use?
> >
> > Also, in a forum, you can see posts from a selected author sorted by
date.
> >
> >
> >>> 3. Preview of entries and  editing of them even after they are
> >>> posted (by the author)
> >>
> >> Which can be seen as a liability
> >
> > To be sure.
> > But then again until anyone has posted a reply. It can be used to
> > correct a typo or add info (where it makes sense rather than add a new
> post).
> >
> >
> >>> 4. Formatted rather than plain text
> >>
> >> Which mail is capable of
> >
> > Any formatting is stripped. At least it is so on the sqlite-users
mail-list.
> >
> >
> >>> 5. No need for e-mail-addresses to be exposed
> >>>
> >> Couldn't a mailing list hide email addresses too?
> >
> > Perhaps it could.
> > I find, however, that e-mails are spread all over the place on the
> > sqlite-users mailing list. So it is not being done here.
> > That is one of my big objections as it invariable leads to spamming of
> > the members.
> >
> >
> >> On the other hand, I find mailing lists much better: I can read them
> >> off
> > line, I
> >> can also answer them off line (my client will send my answers as soon
> >> as
> > it
> >> gets online), I can archive any and/or all posts that I find valuable.
> >
> > I get messages from others when they are on their way to work or home
> > sitting in trains and busses.
> > Mostly people are just online through wireless or mobile networks. It
> > is considered "in" to be able to access the Internet at all times in
> > all places, so I find that point a bit moot nowadays.
> >
> >
> >> In fact, when fora (forums?) propose a mailing list interface (i.e.
> >> google groups), I prefer subscribing to them as a mailing list.
> >>
> >> Even better than mailing lists: newsgroups. Except my company only
> >> lets us use port 80 in addition to the mail gateway, so I can't use
> newsgroups.
> >
> > Who am I tell someone what to prefer.
> > I just find, however, it is a bit religious like choice of OS or
> > gadgets. I can see that e-mail lists can work and in the past could
> > have a lot of advantages. Nowadays, I find not as many good reasons to
> > prefer them over a proper forum which have some really nice features.
> >
> >
> > /Frank Missel
> 
> ___
> 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 in using overloaded functions for virtual tables?

[nobre]

I experience the same situation on the following query:

select 
offsets(pasta_text),
snippet(pasta_text),
DataPasta
from pasta_text 
WHERE pasta_text match 'andamento*'
group by DataPasta
order by DataPasta

Which desired output would be all matches grouped by date (the full query is
joined from a child table that can return multiple results per parent, but
only one parent is desired in the output, along with an arbitrary child
snippet)

Regards
Rafael


fbuser wrote:
> 
>>> From: fbu...@web.de
>>> To: sqlite-users@sqlite.org
>>> Date: Tue, 30 Aug 2011 16:01:24 +0200 (CEST)
>>> Subject: Bug in using overloaded functions for virtual tables?
>>>
>>> SELECT DISTINCT offsets(pages) FROM pages;
>>> -or-
>>> SELECT offsets(pages),count(*) FROM pages GROUP BY 1
>>>
>>> => error message: unable to use function offsets in the requested
>>> context
>>>
>>> Is this is a bug or a restriction, which is not documented? If this is a
>>> restriction it should be considered as a feature request to allow using
>>> virtual table functions in queries with the DISTINCT keyword or the
>>> GROUP BY
>>> clause. Otherwise the bug should be fixed :-)
> 

-- 
View this message in context: 
http://old.nabble.com/Bug-in-using-overloaded-functions-for-virtual-tables--tp32364864p32674947.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

Re: [sqlite] How about a proper forum rather than an e-mail list

[Danny Staten]

I for one would love a forum, and disagree about it being the same as an 
email list.  I would love to not see 40+ emails in my inbox every day from 
this mailing list, but I do find the available resource handy to have when I 
need it.  A forum would allow us to be more active because we can quickly 
and easily ask questions and follow the conversation rather than having to 
scan through a myriad of other emails, or try and follow the reply chain 
when a flood comes in.  It would also allow us to keep our inboxes a lot 
cleaner and clutter free which would be very very nice.


-Original Message- 
From: Frank Missel

Sent: Tuesday, October 18, 2011 6:35 AM
To: 'Teg' ; 'General Discussion of SQLite Database'
Subject: Re: [sqlite] How about a proper forum rather than an e-mail list

Hi Teg,


I love forums and consider them far superior to email if only because it's
easier to follow a topic with less quoting needed. The downside is that
someone  has  to  manage the forum. I've managed a forum for the past  10
years  and there's a daily spam cleanup process and constant attacks and
required upgrades.  You have to set the tone and be pretty ruthless about
flaming too.

I'd like to see a forum. I just wouldn't want to manage it.


Okay, but if the posting is by members only would it not be the same as the
e-mail-lists.
I don't see a lot of spam in the e-mail-list, so either it also monitored by
someone or the fact that it can only be accessed by members makes for a well
behaved list which would be the same for the forum.

/Frank

___
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] FW: How about a proper forum rather than an e-mail list

[Frank Missel]

> boun...@sqlite.org] On Behalf Of Eugene N
> Mailing list is the one true way; Look what IT forums turn into
eventually...

This is a generality and not really an argument.
Which forums do you mean?

I have followed several forums which work exactly as one would hope for. Two
examples comes to mind: 
https://forums.virtualbox.org/
http://forums.codeblocks.org/


> The only way to maintain high level of responsibility and seriousness of
> discussion is, alas, a mailing list

I don't see why. The two examples above are very good forums with relevant
posts, etc.
I find that SQLite would fall in the same category: A technical expertise
forum covering a specific product, application, programming language, etc.
With a members only access and very dedicated users.


/Frank Missel

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

Re: [sqlite] What's the default busyTimeout ?

[Simon Slavin]

On 18 Oct 2011, at 2:52pm, Richard Hipp wrote:

> On Tue, Oct 18, 2011 at 9:46 AM, Simon Slavin  wrote:
> 
>> If I don't call sqlite3_busy_timeout() what is it set to ?
> 
> zero.  The default is to not delay at all.

Thanks for the fast reply.  That makes sense given what I'm seeing.

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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Mark Schonewille]

Hi,

I know from experience that there really is no point whatsoever in discussing 
whether a forum is better than mail or not. People are just not going to agree. 
Just set up your forum and find out whether you're going to have subscribers.

--
Best regards,

Mark Schonewille

Economy-x-Talk Consulting and Software Engineering
Homepage: http://economy-x-talk.com
Twitter: http://twitter.com/xtalkprogrammer
KvK: 50277553

See what you get with only a small contribution. All our LiveCode downloads are 
listed at http://qery.us/zr

On 18 okt 2011, at 15:49, Frank Missel wrote:

>> boun...@sqlite.org] On Behalf Of Jean-Denis MUYS
>> 
>>> 1. Several subject forums as mentioned
>>> 
>> Mail can have as many subjects as desired
> 
> Well, so can forum posts. The point is that the forum is divided into main
> categories above the subject of the post 
> 
> 
>>> 2. Better view of threads with several levels being immediately
>>> displayed
>>> 
>> My mail client threads far better than most forums (fora?)
> 
> I seriously doubt that :-).
> I mean how much better than a totally ordered hierarchy with several levels
> and forks can it be. 
> 
> But perhaps your mail client is very good. Which one do you use?
> 
> Also, in a forum, you can see posts from a selected author sorted by date.
> 
> 
>>> 3. Preview of entries and  editing of them even after they are posted
>>> (by the author)
>> 
>> Which can be seen as a liability
> 
> To be sure. 
> But then again until anyone has posted a reply. It can be used to correct a
> typo or add info (where it makes sense rather than add a new post).
> 
> 
>>> 4. Formatted rather than plain text
>> 
>> Which mail is capable of
> 
> Any formatting is stripped. At least it is so on the sqlite-users mail-list.
> 
> 
>>> 5. No need for e-mail-addresses to be exposed
>>> 
>> Couldn't a mailing list hide email addresses too?
> 
> Perhaps it could. 
> I find, however, that e-mails are spread all over the place on the
> sqlite-users mailing list. So it is not being done here.
> That is one of my big objections as it invariable leads to spamming of the
> members.
> 
> 
>> On the other hand, I find mailing lists much better: I can read them off
> line, I
>> can also answer them off line (my client will send my answers as soon as
> it
>> gets online), I can archive any and/or all posts that I find valuable.
> 
> I get messages from others when they are on their way to work or home
> sitting in trains and busses.
> Mostly people are just online through wireless or mobile networks. It is
> considered "in" to be able to access the Internet at all times in all
> places, so I find that point a bit moot nowadays.
> 
> 
>> In fact, when fora (forums?) propose a mailing list interface (i.e. google
>> groups), I prefer subscribing to them as a mailing list.
>> 
>> Even better than mailing lists: newsgroups. Except my company only lets us
>> use port 80 in addition to the mail gateway, so I can't use newsgroups.
> 
> Who am I tell someone what to prefer.  
> I just find, however, it is a bit religious like choice of OS or gadgets. I
> can see that e-mail lists can work and in the past could have a lot of
> advantages. Nowadays, I find not as many good reasons to prefer them over a
> proper forum which have some really nice features.
> 
> 
> /Frank Missel

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

Re: [sqlite] What's the default busyTimeout ?

[Richard Hipp]

On Tue, Oct 18, 2011 at 9:46 AM, Simon Slavin  wrote:

> If I don't call sqlite3_busy_timeout() what is it set to ?
>

zero.  The default is to not delay at all.



>
> If it matters, I'm actually using the PHP SQLite3 interface calling SQLite
> 3.7.3.
>
> Simon.
> ___
> 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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Frank Missel]

> boun...@sqlite.org] On Behalf Of Jean-Denis MUYS
> 
> > 1. Several subject forums as mentioned
> >
> Mail can have as many subjects as desired

Well, so can forum posts. The point is that the forum is divided into main
categories above the subject of the post 

 
> > 2. Better view of threads with several levels being immediately
> > displayed
> >
> My mail client threads far better than most forums (fora?)

I seriously doubt that :-).
I mean how much better than a totally ordered hierarchy with several levels
and forks can it be. 

But perhaps your mail client is very good. Which one do you use?

Also, in a forum, you can see posts from a selected author sorted by date.


> > 3. Preview of entries and  editing of them even after they are posted
> > (by the author)
> 
> Which can be seen as a liability

To be sure. 
But then again until anyone has posted a reply. It can be used to correct a
typo or add info (where it makes sense rather than add a new post).


> > 4. Formatted rather than plain text
> 
> Which mail is capable of

Any formatting is stripped. At least it is so on the sqlite-users mail-list.


> > 5. No need for e-mail-addresses to be exposed
> >
> Couldn't a mailing list hide email addresses too?

Perhaps it could. 
I find, however, that e-mails are spread all over the place on the
sqlite-users mailing list. So it is not being done here.
That is one of my big objections as it invariable leads to spamming of the
members.


> On the other hand, I find mailing lists much better: I can read them off
line, I
> can also answer them off line (my client will send my answers as soon as
it
> gets online), I can archive any and/or all posts that I find valuable.

I get messages from others when they are on their way to work or home
sitting in trains and busses.
Mostly people are just online through wireless or mobile networks. It is
considered "in" to be able to access the Internet at all times in all
places, so I find that point a bit moot nowadays.


> In fact, when fora (forums?) propose a mailing list interface (i.e. google
> groups), I prefer subscribing to them as a mailing list.
> 
> Even better than mailing lists: newsgroups. Except my company only lets us
> use port 80 in addition to the mail gateway, so I can't use newsgroups.

Who am I tell someone what to prefer.  
I just find, however, it is a bit religious like choice of OS or gadgets. I
can see that e-mail lists can work and in the past could have a lot of
advantages. Nowadays, I find not as many good reasons to prefer them over a
proper forum which have some really nice features.


/Frank Missel

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

[sqlite] What's the default busyTimeout ?

[Simon Slavin]

If I don't call sqlite3_busy_timeout() what is it set to ?

If it matters, I'm actually using the PHP SQLite3 interface calling SQLite 
3.7.3.

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

Re: [sqlite] Very slow processing of some SELECT statements

[Simon Slavin]

On 18 Oct 2011, at 6:46am, Sylvain Pointeau wrote:

> Is it normal that fromuid of the table exits is STRING ?
> I think it should be TEXT to be surely processed as text and not float

If you're writing your database especially for SQLite you should specify 'TEXT' 
not 'STRING'.  However, SQLite has a process where it looks at your 'type' and 
guesses the affinity it has that's most useful: section 2.2 in

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

As you can see it does not specifically recognise 'STRING' as meaning 'TEXT'.  
'STRING' is not one of the types that was ever part of the SQL design so 
there's really no excuse for anyone using it.

Nevertheless, the delay of two seconds was obviously a bug and kudos to the 
fixers for fixing it.

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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Eugene N]

Mailing list is the one true way; Look what IT forums turn into
eventually...
The only way to maintain high level of responsibility and seriousness of
discussion is, alas, a mailing list

2011/10/18 John Drescher 

> My biggest reason for wanting a mailing list versus a forum is that I
> subscribe to 20+ mailing lists that all go to my gmail account with
> gmail rules to organize the content. If these mailing lists all were
> forums I would not bother with most of them since that would be a lot
> of work to check their status. With that said I rarely participate on
> the sqlite mailing list. This is mainly because I have not had many
> problems with it.
>
> John
> ___
> 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] How about a proper forum rather than an e-mail list

[Frank Missel]

> boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: 18 October 2011 20:05
> For what it's worth, I'm using GMane (http://gmane.org/), which is a
mailing
> list-to-NNTP gateway and happens to carry this list. I'm old-fashioned
enough
> to believe that an NNTP newsgroup *is* the proper forum. Can't stand
> modern Web-based forum interfaces.

I agree.
If not for http://www.mail-archive.com/sqlite-users@sqlite.org/

It would be quite bad. 
Using that gives the list at least a flavour of forum with some overview of
the threads.

But I can see where this is leading :-).  Many seem quite happy with the
list.


/Frank

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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[John Drescher]

My biggest reason for wanting a mailing list versus a forum is that I
subscribe to 20+ mailing lists that all go to my gmail account with
gmail rules to organize the content. If these mailing lists all were
forums I would not bother with most of them since that would be a lot
of work to check their status. With that said I rarely participate on
the sqlite mailing list. This is mainly because I have not had many
problems with it.

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

Re: [sqlite] Limit COUNT

[Kit]

2011/10/16 Fabian :
> How can you limit a count-query? I tried:
> SELECT COUNT(*) FROM table LIMIT 5000

SELECT min(COUNT(*),5000) FROM table;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How about a proper forum rather than an e-mail list

[Frank Missel]

Hi Teg,

> I love forums and consider them far superior to email if only because it's
> easier to follow a topic with less quoting needed. The downside is that
> someone  has  to  manage the forum. I've managed a forum for the past  10
> years  and there's a daily spam cleanup process and constant attacks and
> required upgrades.  You have to set the tone and be pretty ruthless about
> flaming too.
> 
> I'd like to see a forum. I just wouldn't want to manage it.

Okay, but if the posting is by members only would it not be the same as the
e-mail-lists. 
I don't see a lot of spam in the e-mail-list, so either it also monitored by
someone or the fact that it can only be accessed by members makes for a well
behaved list which would be the same for the forum.

/Frank

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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Jean-Denis MUYS]

On 18 oct. 2011, at 11:47, Frank Missel wrote:

> I see several advantages to having a forum:
> 
> 1. Several subject forums as mentioned
> 
Mail can have as many subjects as desired

> 2. Better view of threads with several levels being immediately displayed
> 

My mail client threads far better than most forums (fora?)

> 3. Preview of entries and  editing of them even after they are posted (by
> the author)

Which can be seen as a liability

> 
> 4. Formatted rather than plain text

Which mail is capable of

> 
> 5. No need for e-mail-addresses to be exposed
> 

Couldn't a mailing list hide email addresses too?

On the other hand, I find mailing lists much better: I can read them off line, 
I can also answer them off line (my client will send my answers as soon as it 
gets online), I can archive any and/or all posts that I find valuable…

In fact, when fora (forums?) propose a mailing list interface (i.e. google 
groups), I prefer subscribing to them as a mailing list.

Even better than mailing lists: newsgroups. Except my company only lets us use 
port 80 in addition to the mail gateway, so I can't use newsgroups.

JDM

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

Re: [sqlite] How about a proper forum rather than an e-mail list

[Teg]

Hello Frank,

I love forums and consider them far superior to email if only because
it's easier to follow a topic with less quoting needed. The downside is
that  someone  has  to  manage the forum. I've managed a forum for the
past  10  years  and there's a daily spam cleanup process and constant
attacks and required upgrades.  You have to set the tone and be pretty
ruthless about flaming too.

I'd like to see a forum. I just wouldn't want to manage it.

C


Tuesday, October 18, 2011, 5:32:46 AM, you wrote:

FM> I think that the sqlite-users e-mail list has enough traffic to warrant a
FM> proper forum. 

FM> Has this been considered?

FM>  

FM> A proper forum also can contain several subject forums, e.g.

FM> USER FORUMS:

FM> Announcements

FM> General

FM> Help with SQL

FM> News

FM> DEVELOPERS CORNER:

FM> News

FM> General

FM> OS specific

FM> . 

FM>  

FM> There is a quite widespread, free Forum software that could be used:

FM> http://www.simplemachines.org/

FM> http://en.wikipedia.org/wiki/Simple_Machines_Forum

FM>  

FM> They support MySQL, SQLite and PostgreSQL as the underlying database.

FM>  

FM> Well, just a thought.

FM>  

FM>  

FM> /Frank Missel

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




-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] How about a proper forum rather than an e-mail list

[Eduardo Morras]

At 14:04 18/10/2011, Igor Tandetnik wrote:

Frank Missel  wrote:
> I think that the sqlite-users e-mail list has enough traffic to warrant a
> proper forum.

For what it's worth, I'm using GMane (http://gmane.org/), which is a 
mailing list-to-NNTP gateway and happens to carry this list. I'm 
old-fashioned enough to believe that an NNTP newsgroup *is* the 
proper forum. Can't stand modern Web-based forum interfaces.


++


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

Re: [sqlite] How about a proper forum rather than an e-mail list

[Igor Tandetnik]

Frank Missel  wrote:
> I think that the sqlite-users e-mail list has enough traffic to warrant a
> proper forum.

For what it's worth, I'm using GMane (http://gmane.org/), which is a mailing 
list-to-NNTP gateway and happens to carry this list. I'm old-fashioned enough 
to believe that an NNTP newsgroup *is* the proper forum. Can't stand modern 
Web-based forum interfaces.
-- 
Igor Tandetnik

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

[sqlite] How to make SQLite run safely on CIFS mounted file system?

[Sune Ahlgren]

Hi,
I have mounted a CIFS share like this (cat /proc/mounts)://192.168.0.1/test2 
/var/spool/storage/Share cifs 
rw,relatime,unc=\\192.168.0.1\test2,username=sune,uid=123,forceuid,gid=123,forcegid,addr=192.168.0.1,file_mode=0770,dir_mode=0770,nobrl,rsize=16384,wsize=114688
 0 0
My CIFS options ( -o) are: 
username=sune,password=test,nounix,noserverino,uid=123,gid=123,nobrl,file_mode=0770,dir_mode=0770,port=1
 
1)I have a database file called data.db on the CIFS mounted share. I log on to 
sqlite3 from the client side and inserts a row into a table. I and this works 
fine. 
2)I then log on to the server side and remove the data.db file.
3)Again back on the client side I run an ls to make sure the file is really 
gone, and it is.
4)I run a similar insert as in step 1 and sqlite does not complain(!).
I have tried to add directio to my CIFS mount options but the behavior  remains 
the same.
If I run the tests on a filesystem other than CIFS, I get the expected:Error: 
disk I/O error
What can I do to make SQLite run safely on CIFS?
/Sune 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How about a proper forum rather than an e-mail list

[Frank Missel]

I think that the sqlite-users e-mail list has enough traffic to warrant a
proper forum. 

Has this been considered?

 

A proper forum also can contain several subject forums, e.g.

USER FORUMS:

Announcements

General

Help with SQL

News

DEVELOPERS CORNER:

News

General

OS specific

. 

 

There is a quite widespread, free Forum software that could be used:

http://www.simplemachines.org/

http://en.wikipedia.org/wiki/Simple_Machines_Forum

 

They support MySQL, SQLite and PostgreSQL as the underlying database.

 

Well, just a thought.

 

 

/Frank Missel

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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Frank Missel]

> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mark Schonewille
> I prefer mailing lists because I can read and write e-mails while I'm
online
> (.e.g while travelling) and I can easily flag important messages, copy
text and
> syntax in plain format or print (part of) an e-mail and I can also
organise the
> message in the wy I see fit. A true geek uses e-mail, no forums.

Well, if you are online you can open a browser as well.

You can copy text and print -- it is after all also just text in the posts
in the forum when you use ctrl-c.
When working posts in forum software there are very nice features for
commenting posts of others where author and date is automatically inserted.

The one thing that I find really nice in forums is the immediate overview of
longer threads with forks on several levels. Its easy to follow the
discussions.

I have tried both forum and e-mail-list. Although e-mail-list can work, the
forum is so much nicer in my opinion.

> A true geek uses e-mail, no forums.

Ah well, perhaps this is more the reason for not wanting to try it ;-).


> However, if you really want a forum, install one on your web server and
find
> out how many people use it.

Nah, for it to have any success it would have to be an official forum
endorsed and supported by the SQLite team.


/Frank Missel

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

Re: [sqlite] UPDATE question

[Igor Tandetnik]

Barry1337  wrote:
> So I need, for every record in STAYSPEC, to find another record in STAYSPEC
> that has the same STAYNUM field and an ORDER_SPEC field that is 1 more. I
> want to replace the date_out from STAYSPEC with that date (in text format).
> 
> If such a record does not exist (EXISTS) then it needs to take the date_out
> from the STAYHOSP table where the STAYNUM is the same.

Try this:

update STAYSPEC set date_out = coalesce(
  (select date_in from STAYSPEC ss2
   where ss2.staynum = STAYSPEC.staynum and
   ss2.order_spec = STAYSPEC.order_spec + 1),
  (select date_out from STAYHOSP sh where sh.staynum = STAYSPEC.staynum)
);

> Whenever I execute the above query it doesn't give an error or something,
> it's just keeps running without ever stopping !

Make sure you have an index on STAYSPEC(staynum, order_spec) (or at least on 
STAYSPEC(staynum) ), as well as one on STAYHOSP(staynum)

-- 
Igor Tandetnik

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

[sqlite] UPDATE question

[Barry1337]

I have the following query:

Code:

UPDATE STAYSPEC SET date_out = 
CASE WHEN EXISTS 
 (SELECT * 
  FROM STAYSPEC AS STAYSPEC2 
  WHERE (STAYSPEC2.STAYNUM = STAYSPEC.STAYNUM) AND 
 (STAYSPEC2.ORDER_SPEC = STAYSPEC.ORDER_SPEC+1)) 
THEN 
 (SELECT date_in FROM STAYSPEC AS STAYSPEC3 
  WHERE (STAYSPEC3.STAYNUM = STAYSPEC.STAYNUM) 
  AND (STAYSPEC3.ORDER_SPEC = STAYSPEC.ORDER_SPEC+1))
ELSE 
  (SELECT STAYHOSP.date_out FROM STAYHOSP 
  WHERE (STAYHOSP.STAYNUM = STAYSPEC.STAYNUM)) 
END;

date_in and date_out have both the type TEXT.

So I need, for every record in STAYSPEC, to find another record in STAYSPEC
that has the same STAYNUM field and an ORDER_SPEC field that is 1 more. I
want to replace the date_out from STAYSPEC with that date (in text format).

If such a record does not exist (EXISTS) then it needs to take the date_out
from the STAYHOSP table where the STAYNUM is the same.

Whenever I execute the above query it doesn't give an error or something,
it's just keeps running without ever stopping ! Since it's quite hard to
debug this I sincerely hope someone can help me with this.
-- 
View this message in context: 
http://old.nabble.com/UPDATE-question-tp32673794p32673794.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

Re: [sqlite] Problem with FTS4 - Floating point error.

[Dan Kennedy]

On 10/18/2011 05:02 PM, Mohd Radzi Ibrahim wrote:

Hi,

This is my table schema:

CREATE VIRTUAL TABLE LocationFTS using FTS4
(
name text,
address text,
email text,
remark text,
telno text,
contact text,
isDeleted text
);

This select statement gives floating point error in both sqlite3.exe and also 
my program (both my Ubuntu and Windows versions failed) :

select docid, name, address, email, remark, telNo, contact
from locationFTS
where locationFTS match 'suai plantation sdn bhd, p.o.box 730 97008 bintulu 
swk, tel 2 07-6637460 ah leng   06-9766061 a3.0';

PharmacyServer.exe!GenericKedaiRuncitDB::GetLocationFTS(LocationRecord& 
 r)  Line 147 + 0x14 bytes  C++
PharmacyServer.exe!wxSQLite3Statement::ExecuteQuery(bool 
transferStatementOwnership)  Line 1486 + 0xc bytes C++
PharmacyServer.exe!sqlite3_step(sqlite3_stmt * pStmt)  Line 62029 + 0x9 
bytes   C
PharmacyServer.exe!sqlite3Step(Vdbe * p)  Line 61954 + 0x9 bytes
C
PharmacyServer.exe!sqlite3VdbeExec(Vdbe * p)  Line 3856 + 0x30 bytes
C
PharmacyServer.exe!fts3FilterMethod(sqlite3_vtab_cursor * pCursor, int 
idxNum, const char * idxStr, int nVal, Mem * * apVal)  Line 51378 + 0x9 bytes   
 C
PharmacyServer.exe!fts3EvalStart(Fts3Cursor * pCsr)  Line 52714 + 0x13 
bytesC
PharmacyServer.exe!fts3EvalSelectDeferred(Fts3Cursor * pCsr, Fts3Expr * 
pRoot, Fts3TokenAndCost * aTC, int nTC)  Line 52634 + 0x2a bytesC

Does anybody know what's going on here?


Can you send me the database by email? Not via the list,
as it will strip the attachment.

Dan Kennedy.


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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Arbol One]

yap, I agree with you, email better.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mark Schonewille
Sent: Tuesday, October 18, 2011 5:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] FW: How about a proper forum rather than an e-mail
list

Hi,

I prefer mailing lists because I can read and write e-mails while I'm online
(.e.g while travelling) and I can easily flag important messages, copy text
and syntax in plain format or print (part of) an e-mail and I can also
organise the message in the wy I see fit. A true geek uses e-mail, no
forums.

However, if you really want a forum, install one on your web server and find
out how many people use it.

--
Best regards,

Mark Schonewille

Economy-x-Talk Consulting and Software Engineering
Homepage: http://economy-x-talk.com
Twitter: http://twitter.com/xtalkprogrammer
KvK: 50277553

See what you get with only a small contribution. All our LiveCode downloads
are listed at http://qery.us/zr

On 18 okt 2011, at 11:35, Frank Missel wrote:

> I think that the sqlite-users e-mail list has enough traffic to warrant a
> proper forum. 
> 
> Has this been considered?
> 

___
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] Problem with FTS4 - Floating point error.

[Mohd Radzi Ibrahim]

Hi,

This is my table schema:

CREATE VIRTUAL TABLE LocationFTS using FTS4
(
name text, 
address text,
email text, 
remark text, 
telno text, 
contact text, 
isDeleted text
);

This select statement gives floating point error in both sqlite3.exe and also 
my program (both my Ubuntu and Windows versions failed) :

select docid, name, address, email, remark, telNo, contact 
from locationFTS 
where locationFTS match 'suai plantation sdn bhd, p.o.box 730 97008 bintulu 
swk, tel 2 07-6637460 ah leng   06-9766061 a3.0';


It runs OK without if nothing matches, but when I've populated the table with 
some 10,000 records, and this record is matching, the select statement failed. 
When I reduces the match string to 80, it's Ok. But it's not the length that 
causes it to fail, because there are other statement that has match string more 
that 100 characters.

On Windows (VS2010) I have this stack of calls that causes the error:

 
PharmacyServer.exe!GenericKedaiRuncitDB::GetLocationFTS(LocationRecord 
& r)  Line 147 + 0x14 bytes  C++
PharmacyServer.exe!wxSQLite3Statement::ExecuteQuery(bool 
transferStatementOwnership)  Line 1486 + 0xc bytes C++
PharmacyServer.exe!sqlite3_step(sqlite3_stmt * pStmt)  Line 62029 + 0x9 
bytes   C
PharmacyServer.exe!sqlite3Step(Vdbe * p)  Line 61954 + 0x9 bytes
C
PharmacyServer.exe!sqlite3VdbeExec(Vdbe * p)  Line 3856 + 0x30 bytes
C
PharmacyServer.exe!fts3FilterMethod(sqlite3_vtab_cursor * pCursor, int 
idxNum, const char * idxStr, int nVal, Mem * * apVal)  Line 51378 + 0x9 bytes   
 C
PharmacyServer.exe!fts3EvalStart(Fts3Cursor * pCsr)  Line 52714 + 0x13 
bytesC
PharmacyServer.exe!fts3EvalSelectDeferred(Fts3Cursor * pCsr, Fts3Expr * 
pRoot, Fts3TokenAndCost * aTC, int nTC)  Line 52634 + 0x2a bytesC

Does anybody know what's going on here?

Thanks.

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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Mark Schonewille]

Hi,

I prefer mailing lists because I can read and write e-mails while I'm online 
(.e.g while travelling) and I can easily flag important messages, copy text and 
syntax in plain format or print (part of) an e-mail and I can also organise the 
message in the wy I see fit. A true geek uses e-mail, no forums.

However, if you really want a forum, install one on your web server and find 
out how many people use it.

--
Best regards,

Mark Schonewille

Economy-x-Talk Consulting and Software Engineering
Homepage: http://economy-x-talk.com
Twitter: http://twitter.com/xtalkprogrammer
KvK: 50277553

See what you get with only a small contribution. All our LiveCode downloads are 
listed at http://qery.us/zr

On 18 okt 2011, at 11:35, Frank Missel wrote:

> I think that the sqlite-users e-mail list has enough traffic to warrant a
> proper forum. 
> 
> Has this been considered?
> 

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

Re: [sqlite] FW: How about a proper forum rather than an e-mail list

[Frank Missel]

I see several advantages to having a forum:

1. Several subject forums as mentioned

2. Better view of threads with several levels being immediately displayed

3. Preview of entries and  editing of them even after they are posted (by
the author)

4. Formatted rather than plain text

5. No need for e-mail-addresses to be exposed


/Frank Missel

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

[sqlite] FW: How about a proper forum rather than an e-mail list

[Frank Missel]

I think that the sqlite-users e-mail list has enough traffic to warrant a
proper forum. 

Has this been considered?

 

A proper forum also can contain several subject forums, e.g.

USER FORUMS:

Announcements

General

Help with SQL

News

DEVELOPERS CORNER:

News

General

OS specific

. 

 

There is a quite widespread, free Forum software that could be used:

http://www.simplemachines.org/

http://en.wikipedia.org/wiki/Simple_Machines_Forum

 

They support MySQL, SQLite and PostgreSQL as the underlying database.

 

Well, just a thought.

 

 

/Frank Missel

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

Re: [sqlite] Very slow processing of some SELECT statements

[Sylvain Pointeau]

On Tue, Oct 18, 2011 at 8:30 AM, Nick Gammon  wrote:

>
> On 18/10/2011, at 4:46 PM, Sylvain Pointeau wrote:
>
> > Is it normal that fromuid of the table exits is STRING ?
> > I think it should be TEXT to be surely processed as text and not float
>
> That was an error. However it shouldn't take SQLite 2.5 seconds to handle
> *any* numeric literal. Especially as it was quoted. For example, in C you
> don't expect:
>
> x = "123E45678942";
>
> ... to go through any sort of numeric conversion. Now I know this isn't C,
> but the "let's see if we can turn a string into a number, and take two to
> three seconds to do so" is not right, IMHO.
>
>
it has something to do with type affinity.
I don't think you would have had this slow down if your type was TEXT,
because (I think) sqlite would have not tried to convert it.
Anyway seems to be improved now.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Very slow processing of some SELECT statements

[Nick Gammon]

On 18/10/2011, at 4:46 PM, Sylvain Pointeau wrote:

> Is it normal that fromuid of the table exits is STRING ?
> I think it should be TEXT to be surely processed as text and not float

That was an error. However it shouldn't take SQLite 2.5 seconds to handle *any* 
numeric literal. Especially as it was quoted. For example, in C you don't 
expect:

x = "123E45678942";

... to go through any sort of numeric conversion. Now I know this isn't C, but 
the "let's see if we can turn a string into a number, and take two to three 
seconds to do so" is not right, IMHO.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users