Re: [sqlite] threads and transactions

[Igor Tandetnik]

David M. Cotter  wrote:
> i may not have been clear
> 
> i want to begin transactions on different threads at once

Why, if you don't mind me asking? Your hard drive has only one write head. What 
makes you feel that writing to the same file from multiple threads would be any 
faster than doing it from one thread?

In any case, you can't do with SQLite what you think you want to do. 
Personally, I'd have one thread dedicated to SQLite work, then all the other 
threads would generate the data to be inserted, and feed it to the SQLite 
worker via a producer-consumer queue.

> but does inserting data during a transaction actually block too?

Yes.

> is inserting considered a "writing transaction" if there is a "begin" before 
> it?

Yes.

> cuz it's not actually writing to the DB proper,
> it's writing to it's journal file

Incorrect. SQLite writes previous, unmodified data into journal file, prior to 
overwriting parts of actual database file with new data. Committing a 
transaction consists simply of deleting the journal file. Rolling back means 
copying the data over from the journal file back to the database file.

> i'm not using "begin immediate", just using "begin"

This just means the transaction becomes a write transaction a split second 
later, when the first INSERT statement is executed.
-- 
Igor Tandetnik

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

Re: [sqlite] threads and transactions

[Igor Tandetnik]

David M. Cotter  wrote:
>> In SQLite every write is in a transaction whether you declare one with BEGIN 
>> or not.  If you don't declare a transaction, SQLite
>> invisibly surrounds each individual INSERT or UPDATE with a BEGIN and 
>> COMMIT. 
> sure, that's fine.  but if you do your own BEGIN, then any INSERT you do 
> after that, *before* you do the COMMIT isn't actually
> writing anything into the database, right? 
> 
> it's really only the COMMIT that actually writes to the database, right?

Wrong. There is a limited in-memory cache, but once the amount of data grows 
large enough, intermediate changes need to be spilled to disk and, indeed, 
written to the database file.

> but you're not addressing the case where i have manually started a BEGIN.  
> after i manually do BEGIN, does the next INSERT need
> to block, or is it the COMMIT that actually does the blocking? 

The first INSERT acquires a reserved lock on the database. No other connection 
will then be able to acquite a reserved lock, so there would be no other 
writer. For details, see http://www.sqlite.org/lockingv3.html

> "if each thread is collecting several rows that it will insert in one fell 
> swoop, why should the mere gathering of rows (without
> actually committing them yet) block another thread from gathering it's own 
> set of rows?

You are free to "gather" the data that would go into the database before 
starting the write transaction.

> it's only when all the rows are gathered
> that i actually commit the change.

There's no mechanism in SQLite to control when precisely intermediate data is 
written to the database file. It may happen before COMMIT.

> am i making sense?

Yes, but you are laboring under incorrect assumptions, and thus reaching 
incorrect conclusions.
-- 
Igor Tandetnik

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

Re: [sqlite] threads and transactions

[Simon Slavin]

On 6 Feb 2011, at 1:30am, David M. Cotter wrote:

>> In SQLite every write is in a transaction whether you declare one with BEGIN 
>> or not. If you don't declare a transaction, SQLite invisibly surrounds each 
>> individual INSERT or UPDATE with a BEGIN and COMMIT.
> sure, that's fine.  but if you do your own BEGIN, then any INSERT you do 
> after that, *before* you do the COMMIT isn't actually writing anything into 
> the database, right?
> 
> it's really only the COMMIT that actually writes to the database, right?

An alternative point of view is that the transactions hit the database 
immediately, and that either ROLLBACK or failure to do a COMMIT backs them out. 
 It depends what you think 'the database' is.  Remember that it's possible to 
run SQLite without any journaling at all: even uncommitted changes go to the 
database file.  If a journal file exists, then I feel 'the database' includes 
both the database file and the journal file.

>>> i understand that one commit will block the other
>>> 
>>> but does inserting data during a transaction actually block too?
>> 
>> It is each transaction which blocks each other transaction, but the default 
>> behaviour is not to lock the file until the first command that makes a 
>> change.  So once one thread has executed an INSERT, all other threads will 
>> be blocked at their own BEGINs and will never even get to do an INSERT until 
>> the first thread has done its COMMIT.
> 
> but you're not addressing the case where i have manually started a BEGIN.  
> after i manually do BEGIN, does the next INSERT need to block, or is it the 
> COMMIT that actually does the blocking?

The first INSERT after the BEGIN starts the blocking.  Unless you specifically 
override the behaviour by doing a BEGIN IMMEDIATE (which you've previously said 
you weren't doing).

>>> is inserting considered a "writing transaction" if there is a "begin" 
>>> before it? cuz it's not actually writing to the DB proper, it's writing to 
>>> it's journal file, saving things up until the "commit" or "rollback".
>> 
>> The natural question you're asking is something like "Well, if each thread 
>> is just inserting new rows, why do they need to block each-other ?  They 
>> don't care what data the other threads are inserting."
> 
> well, i would phrase it this way:
> 
> "if each thread is collecting several rows that it will insert in one fell 
> swoop, why should the mere gathering of rows (without actually committing 
> them yet) block another thread from gathering it's own set of rows?  it's 
> only when all the rows are gathered that i actually commit the change. Since 
> the database isn't being written to during the gathering phase, they don't 
> care what other threads are gathering".

See my previous answer.  If a program tries to add a row to the database that 
conflicts with one that's already there, it needs to get an error immediately.  
Not later when it has forgotten the context of the row that's causing the 
problem and can no longer figure out how to handle the problem.  So a thread 
that's trying to do an INSERT must be blocked until all the changes that will 
be done before it have been completed.  Also there's the problem that some 
applications need to know the rowid of a freshly-inserted row.

If you don't need this behaviour because you're confident you'll never get a 
clash, then you could accumulate your INSERTs in memory, then blast through 
them when you would previously have just done the COMMIT.

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

Re: [sqlite] threads and transactions

[David M. Cotter]

forgive my not understanding this but i'm trying to be extremely clear and i am 
not sure from your answer whether you have understood my  question.

> In SQLite every write is in a transaction whether you declare one with BEGIN 
> or not.  If you don't declare a transaction, SQLite invisibly surrounds each 
> individual INSERT or UPDATE with a BEGIN and COMMIT.
sure, that's fine.  but if you do your own BEGIN, then any INSERT you do after 
that, *before* you do the COMMIT isn't actually writing anything into the 
database, right?

it's really only the COMMIT that actually writes to the database, right?

>> i understand that one commit will block the other
>> 
>> but does inserting data during a transaction actually block too?
> 
> It is each transaction which blocks each other transaction, but the default 
> behaviour is not to lock the file until the first command that makes a 
> change.  So once one thread has executed an INSERT, all other threads will be 
> blocked at their own BEGINs and will never even get to do an INSERT until the 
> first thread has done its COMMIT.

but you're not addressing the case where i have manually started a BEGIN.  
after i manually do BEGIN, does the next INSERT need to block, or is it the 
COMMIT that actually does the blocking?

do you see what i'm asking?

>> is inserting considered a "writing transaction" if there is a "begin" before 
>> it?  cuz it's not actually writing to the DB proper, it's writing to it's 
>> journal file, saving things up until the "commit" or "rollback".
> 
> The natural question you're asking is something like "Well, if each thread is 
> just inserting new rows, why do they need to block each-other ?  They don't 
> care what data the other threads are inserting."

well, i would phrase it this way:

"if each thread is collecting several rows that it will insert in one fell 
swoop, why should the mere gathering of rows (without actually committing them 
yet) block another thread from gathering it's own set of rows?  it's only when 
all the rows are gathered that i actually commit the change. Since the database 
isn't being written to during the gathering phase, they don't care what other 
threads are gathering".

am i making sense?

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

Re: [sqlite] threads and transactions

[Simon Slavin]

On 5 Feb 2011, at 11:00pm, David M. Cotter wrote:

> i may not have been clear
> 
> i want to begin transactions on different threads at once
> in each thread
>   begin a transaction
>   insert lots of data, this may take a long time
>   commit transaction

Okay, here's some background.

In SQLite every write is in a transaction whether you declare one with BEGIN or 
not.  If you don't declare a transaction, SQLite invisibly surrounds each 
individual INSERT or UPDATE with a BEGIN and COMMIT.

> i understand that one commit will block the other
> 
> but does inserting data during a transaction actually block too?

It is each transaction which blocks each other transaction, but the default 
behaviour is not to lock the file until the first command that makes a change.  
So once one thread has executed an INSERT, all other threads will be blocked at 
their own BEGINs and will never even get to do an INSERT until the first thread 
has done its COMMIT.

> is inserting considered a "writing transaction" if there is a "begin" before 
> it?  cuz it's not actually writing to the DB proper, it's writing to it's 
> journal file, saving things up until the "commit" or "rollback".

The natural question you're asking is something like "Well, if each thread is 
just inserting new rows, why do they need to block each-other ?  They don't 
care what data the other threads are inserting.".  The answer is that even the 
process of inserting new data needs to look at existing data.  There's the 
problem of dealing with columns marked as UNIQUE: each thread needs to be 
stopped if it tries to insert a new row that would violate database 
restrictions, and it has to be stopped on that instruction, not long after it 
has forgotten what it did that caused the problem.  And for the system to 
decide which thread should get the rejection it has to consider the operations 
in a particular order.  Which is why it only considers one transaction at a 
time.

For a fuller explanation of what causes and is blocked by locking see



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

Re: [sqlite] threads and transactions

[David M. Cotter]

i may not have been clear

i want to begin transactions on different threads at once
in each thread
begin a transaction
insert lots of data, this may take a long time
commit transaction

i understand that one commit will block the other

but does inserting data during a transaction actually block too?

is inserting considered a "writing transaction" if there is a "begin" before 
it?  cuz it's not actually writing to the DB proper, it's writing to it's 
journal file, saving things up until the "commit" or "rollback".

i'm not using "begin immediate", just using "begin"


On Feb 5, 2011, at 2:53 PM, Pavel Ivanov wrote:

>> i understand that one "commit" will block all other threads from doing a 
>> "commit", "rollback" or any atomic transaction, until it's done, but are you 
>> saying i can't even add data on another thread while one has an open 
>> transaction?
> 
> There can be several simultaneous read-only transactions. But as long
> as one connection started a writing transaction (by executing "begin
> immediate" or by executing insert/update/delete after "begin") no
> other connection can start a writing transaction (it still can do
> read-only transactions for a while).
> 
> If you need a different behavior you need to use some other DBMS.
> 
> 
> Pavel
> 
> On Sat, Feb 5, 2011 at 5:48 PM, David M. Cotter  wrote:
>>> Transactions are per-connection and have nothing to do
>>> with threads. If you want different transactions in each thread you
>>> need to make one connection for each thread. But those transactions
>>> won't be able to execute simultaneously.
>> 
>> so if i open a separate connection on each thread
>> then each thread begins a transaction
>> you're saying one thread will block?
>> 
>> i understand that one "commit" will block all other threads from doing a 
>> "commit", "rollback" or any atomic transaction, until it's done, but are you 
>> saying i can't even add data on another thread while one has an open 
>> transaction?
>> 

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

Re: [sqlite] threads and transactions

[Pavel Ivanov]

> i understand that one "commit" will block all other threads from doing a 
> "commit", "rollback" or any atomic transaction, until it's done, but are you 
> saying i can't even add data on another thread while one has an open 
> transaction?

There can be several simultaneous read-only transactions. But as long
as one connection started a writing transaction (by executing "begin
immediate" or by executing insert/update/delete after "begin") no
other connection can start a writing transaction (it still can do
read-only transactions for a while).

If you need a different behavior you need to use some other DBMS.


Pavel

On Sat, Feb 5, 2011 at 5:48 PM, David M. Cotter  wrote:
>> Transactions are per-connection and have nothing to do
>> with threads. If you want different transactions in each thread you
>> need to make one connection for each thread. But those transactions
>> won't be able to execute simultaneously.
>
> so if i open a separate connection on each thread
> then each thread begins a transaction
> you're saying one thread will block?
>
> i understand that one "commit" will block all other threads from doing a 
> "commit", "rollback" or any atomic transaction, until it's done, but are you 
> saying i can't even add data on another thread while one has an open 
> transaction?
>
> ___
> 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] threads and transactions

[David M. Cotter]

> Transactions are per-connection and have nothing to do
> with threads. If you want different transactions in each thread you
> need to make one connection for each thread. But those transactions
> won't be able to execute simultaneously.

so if i open a separate connection on each thread
then each thread begins a transaction
you're saying one thread will block?

i understand that one "commit" will block all other threads from doing a 
"commit", "rollback" or any atomic transaction, until it's done, but are you 
saying i can't even add data on another thread while one has an open 
transaction?

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

Re: [sqlite] threads and transactions

[Pavel Ivanov]

> presuming this timeline is chronological, may i assume that step 4 is 
> committed first in the database?

You mean as a third transaction? No.

> and that steps 5 and 6 operate independently?

No.

> even when threads 1 and 2 open their individual transactions, i see only ONE 
> journal file
> logically i guess i would have expected to see one journal file per open 
> transaction per thread?

You are wrong. Transactions are per-connection and have nothing to do
with threads. If you want different transactions in each thread you
need to make one connection for each thread. But those transactions
won't be able to execute simultaneously.

> does step five commit thread 6?  does thread 6's rollback only operate on the 
> "stuff" done in thread 6?

Step 5 commits everything in thread 1, thread 1's rollback does
nothing. And btw if you checked your return codes from SQLite you'd
notice that "begin transaction" fails either in thread 2 or thread 1
(whichever comes last).


Pavel

On Sat, Feb 5, 2011 at 5:27 PM, David M. Cotter  wrote:
> i'm sure this topic has been beaten to death but i just really want to make 
> sure.
>
> i'm using ONE database, and one handle to it on all threads
>
> here's a theoretical timeline
>
> --
> 1) thread 1
> begin transaction
> do bunches of stuff
>
> 2) thread 2
> begin transaction
> do bunches of stuff
>
> 3) thread 1
> do more stuff
>
> 4) thread 0 (main thread)
> do atomic operation
>
> 5) thread 2
> do more stuff
> commit transaction
>
> 6) thread 1
> rollback transaction
> --
>
> presuming this timeline is chronological, may i assume that step 4 is 
> committed first in the database?
>
> and that steps 5 and 6 operate independently?
>
> even when threads 1 and 2 open their individual transactions, i see only ONE 
> journal file
> logically i guess i would have expected to see one journal file per open 
> transaction per thread?
>
> does step five commit thread 6?  does thread 6's rollback only operate on the 
> "stuff" done in thread 6?
> ___
> 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] threads and transactions

[David M. Cotter]

i'm sure this topic has been beaten to death but i just really want to make 
sure.

i'm using ONE database, and one handle to it on all threads

here's a theoretical timeline

--
1) thread 1
begin transaction
do bunches of stuff

2) thread 2
begin transaction
do bunches of stuff

3) thread 1
do more stuff

4) thread 0 (main thread)
do atomic operation

5) thread 2
do more stuff
commit transaction

6) thread 1
rollback transaction
--

presuming this timeline is chronological, may i assume that step 4 is committed 
first in the database?

and that steps 5 and 6 operate independently?

even when threads 1 and 2 open their individual transactions, i see only ONE 
journal file
logically i guess i would have expected to see one journal file per open 
transaction per thread?

does step five commit thread 6?  does thread 6's rollback only operate on the 
"stuff" done in thread 6?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this SQLite implementation doing something stupid?

[Eric Smith]

Ian Hardingham wrote:

> I'm using an SQLite implementation that someone else made for my 
> high-level language of choice.
> 
> While looking through the imp, I've just found this function, which is 
> used as the callback argument to sqlite3_exec.  Does this look like an 
> ok useage?  It seems to me like this might be doing a lot of work for 
> some data I may never use.

If it's not buggy, and you don't need it to perform better, then leave
it alone.

If you need it to perform better, don't do any optimizing until you've 
measured where your hotspots really are by using, e.g., gprof and/or
whatever profiling tools exist for your language.

If you find that this interface is really your issue, scope out how
feasible/cost-effective it is to change it.  This will be 
determined by a great many variables -- size of the code base & number
of entry points to this interface, quality of client code, level of
abstraction client code provides from this interface to higher layers, 
availability of a covering test suite, amount of time you have to do 
the work, number of round-trips you can make with your QA team (if 
they exist) before delivery, how much new client code you think will
be written that eventually calls down to this interface over the next 
weeks/months/years, etc etc etc.

If so, and if it makes sense, consider creating a new interface instead
of editing the existing one.  This allows you to upgrade the client code 
a little bit at a time to use the new interface.  The drawback is that
now you're maintaining two interfaces.

Probable hot spots in the existing code: string scans (dStrlen), string 
copies (dStrcpy) and calls to 'new'.  The fewer of those you can do, the 
better off you'll be.  

I'd say a good reference as to the "proper" way to hook SQLite into a
high-level language is the SQLite devs' own implementation for Tcl.

You want to grab e.g. http://sqlite.org/sqlite-tea-3070500.tar.gz
and have a look at tclsqlite3.c, function DbEvalNextCmd on line 124807
and function dbEvalColumnValue on line 124743.

Things this gets right that your implementation doesn't, from my quick
glance:

1. It keeps a cache of prepared statements and re-uses statements
   that have been run recently.  You are re-preparing statements
   every time.

2. It only asks for columns that the client is asking for (only you
   can know whether higher layers are getting this right).

3. It only computes the column names once during the statement.  You
   are computing them on every row.

4. It only processes one result row at a time (synchronously calling up
   to the high-level language), keeping memory usage low.  You are 
   stuffing the whole result set into memory before returning it to the 
   client.  Fixing this might be the biggest hassle on this list,
   because it might impact many layers above. 

5. It does not force all values to strings.  You are (and, depending
   on which language you're using, you'll probably convert them back
   to native types higher up somewhere).

Shared drawback:

1. Text data is copied.  This can't be helped for Tcl because it's 
   enforced by the Tcl extention API.  Maybe your language lets you
   just point directly at a const char* and use copy-on-write semantics
   or some such.

Eric

> Any help much appreciated,
> Thanks,
> Ian
> 
> int Callback(void *pArg, int argc, char **argv, char **columnNames)
> {
> // basically this callback is called for each row in the SQL query 
> result.
> // for each row, argc indicates how many columns are returned.
> // columnNames[i] is the name of the column
> // argv[i] is the value of the column
> 
> sqlite_resultrow* pRow;
> sqlite_resultset* pResultSet;
> char* name;
> char* value;
> int i;
> 
> if (argc == 0)
>return 0;
> 
> pResultSet = (sqlite_resultset*)pArg;
> if (!pResultSet)
>return -1;
> 
> // create a new result row
> pRow = new sqlite_resultrow;
> pResultSet->iNumCols = argc;
> // loop through all the columns and stuff them into our row
> for (i = 0; i < argc; i++)
> {
>// DBEUG CODE
> //  Con::printf("%s = %s\n", columnNames[i], argv[i] ? argv[i] : 
> "NULL");
>name = new char[dStrlen(columnNames[i]) + 1];
>dStrcpy(name, columnNames[i]);
>pRow->vColumnNames.push_back(name);
>if (argv[i])
>{
>   value = new char[dStrlen(argv[i]) + 1];
>   dStrcpy(value, argv[i]);
>   pRow->vColumnValues.push_back(value);
>}
>else
>{
>   value = new char[10];
>   dStrcpy(value, "NULL");
>   pRow->vColumnValues.push_back(value);
>}
> }
> pResultSet->iNumRows++;
> pResultSet->vRows.push_back(pRow);
> 
> // return 0 or else the sqlexec will be aborted.
> return 0;
> }
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> 

Re: [sqlite] Sub-Select reports only row one value?

[Igor Tandetnik]

Jeff Hennick  wrote:
> I am having problems with Sub-Select apparently working on one row
> rather than the whole table.

When syntactically used as an expression (e.g. in SELECT clause), a subselect 
produces a single value. When used as a table (e.g. in FROM clause), it may 
produce multiple rows.

> registrations table:
> Class | Term | (Other student columns)
> CC123 | 101  | ...
> ...
> 
> Desired ultimate output counts by class and term, with totals and
> averages by row and by column:

SQL is not well suited for building pivot tables. Its resultset is a table with 
a fixed number of columns and a variable number of rows. I suggest you write a 
query that produces a table with three columns (Class, Term, SomeNumber), and 
process it into a pivot table in your application code.

> But when I use it as a Sub-Select (or in a Join), it gives, for ALL
> rows, the result from the FIRST row.
> 
> SELECT
> r.class,
> r.term,
> COUNT(*) AS c,
> 
> (SELECT COUNT(term)
> FROM (SELECT DISTINCT class, term
> FROM registrations AS r2
> GROUP BY r2.class, r2.term
> ORDER BY r2.class, r2.term)
> GROUP BY class) AS cnt
> 
> FROM registrations AS r
> GROUP BY r.class, r.term
> ORDER BY r.class
> 
> All rows get the same value for cnt.

The subselect has no dependencies on the current row from "registrations AS r" 
table. It could as well be calculated once, and the resulting value inserted 
into every row. In what way do you expect the value to vary?

What exactly was this subselect supposed to achieve? It makes no sense to me.
-- 
Igor Tandetnik

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

Re: [sqlite] Is this SQLite implementation doing something stupid?

[Igor Tandetnik]

Ian Hardingham  wrote:
> I'm using an SQLite implementation that someone else made for my
> high-level language of choice.
> 
> While looking through the imp, I've just found this function, which is
> used as the callback argument to sqlite3_exec.  Does this look like an
> ok useage?  It seems to me like this might be doing a lot of work for
> some data I may never use.

I imagine that, lacking prescience, the callback has no way to know which data 
you will eventually use and which you won't, and has no choice but to store it 
all.

If at all possible, I would suggest moving away from sqlite3_exec towards a 
forward cursor based on sqlite3_prepare / sqlite3_step.
-- 
Igor Tandetnik

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

[sqlite] Sub-Select reports only row one value?

[Jeff Hennick]

I am having problems with Sub-Select apparently working on one row 
rather than the whole table.

registrations table:
Class | Term | (Other student columns)
CC123 | 101  | ...
CC002 | 101
CC050 | 111
CC123 | 101
CC123 | 102
...

Desired ultimate output counts by class and term, with totals and 
averages by row and by column:

Class | 093 | 101 | 102 | 103 | 111 | Total | Average
CC001 |  10 |   8 |  12 |  7  |   8 |45 |9
CC002 |   6 |  11 | | 13  | |30 |   10
...   | | | | | |   |
CC278 | |   6 |   8 | | |14 |7
Totals: | ...


I want to be able to use Order By on any column, including Average.

The first columns aren't too difficult.  But Averages within a row (or 
even Counts to calculate an average) has me baffled.

I don't know if it is (in order of probability) 1. My ignorance, 2. 
SQLite bug, or 3. SQL problem.

When I use this not so pretty Select, I can get good counts of non-NULL 
terms:

SELECT COUNT(term)
FROM (SELECT DISTINCT class, term
 FROM registrations AS r2
 GROUP BY r2.class, r2.term
 ORDER BY r2.class, r2.term)
GROUP BY class

But when I use it as a Sub-Select (or in a Join), it gives, for ALL 
rows, the result from the FIRST row.

SELECT
 r.class,
 r.term,
 COUNT(*) AS c,

(SELECT COUNT(term)
FROM (SELECT DISTINCT class, term
 FROM registrations AS r2
 GROUP BY r2.class, r2.term
 ORDER BY r2.class, r2.term)
GROUP BY class) AS cnt

 FROM registrations AS r
 GROUP BY r.class, r.term
 ORDER BY r.class

All rows get the same value for cnt.  I can verify that it is getting 
the cnt from the first row by introducing a class "CC000" with just one 
student.  Using a self-Join rather than sub-Select has the same result.

Environments:  1. SQLite embedded in PHP5 called via PDO (Windows or 
FreeBSD),  2. SQLite Manager addon for Firefox.  Same results from each.

Apparently the optimization is going overboard.  I have, as you see, 
tried to help defeat it using aliases on the two uses of the table.

My workaround, when the Order By is the averages column, is to post 
process in PHP which involves a full array and sorting it.  Kind of 
defeats the use of SQL.

Any help or insights would be most welcomed. Thank you.


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

Re: [sqlite] Sqlite management

[mustafa]

2011/2/5 John Drescher 

> On Sat, Feb 5, 2011 at 6:36 AM, mustafa  wrote:
> > Hello,
> >
> > I am planning to write an open source software with wxWidgets
> > to simplify SQLite operations(select,insert etc)
> >
> > There are open source alternative softwares.
> > http://sourceforge.net/projects/sqlitemanager/
> >
> >
> > http://sqlitebrowser.sourceforge.net/index.html
> >
> >
> > http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
> >
> >
> > Writing new a management tool is wasting time or SQLite doesn't need a
> > software to simply SQLite operations.
> >
> > Could you please give a suggestion?
> >
>
> The big question is what will you offer that is not available on the
> currently available opensource or free tools?
>
> John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Program will not be web base. I will support software as long as I will.

If contributors want join to the project they are welcome.

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

Re: [sqlite] Sqlite management

[John Drescher]

On Sat, Feb 5, 2011 at 6:36 AM, mustafa  wrote:
> Hello,
>
> I am planning to write an open source software with wxWidgets
> to simplify SQLite operations(select,insert etc)
>
> There are open source alternative softwares.
> http://sourceforge.net/projects/sqlitemanager/
>
>
> http://sqlitebrowser.sourceforge.net/index.html
>
>
> http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
>
>
> Writing new a management tool is wasting time or SQLite doesn't need a
> software to simply SQLite operations.
>
> Could you please give a suggestion?
>

The big question is what will you offer that is not available on the
currently available opensource or free tools?

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

[sqlite] Sqlite management

[mustafa]

Hello,

I am planning to write an open source software with wxWidgets
to simplify SQLite operations(select,insert etc)

There are open source alternative softwares.
http://sourceforge.net/projects/sqlitemanager/


http://sqlitebrowser.sourceforge.net/index.html


http://www.sqlite.org/cvstrac/wiki?p=ManagementTools


Writing new a management tool is wasting time or SQLite doesn't need a
software to simply SQLite operations.

Could you please give a suggestion?

Mustafa Onur Aysan

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

[sqlite] Is this SQLite implementation doing something stupid?

[Ian Hardingham]

Hey guys.

I'm using an SQLite implementation that someone else made for my 
high-level language of choice.

While looking through the imp, I've just found this function, which is 
used as the callback argument to sqlite3_exec.  Does this look like an 
ok useage?  It seems to me like this might be doing a lot of work for 
some data I may never use.

Any help much appreciated,
Thanks,
Ian

int Callback(void *pArg, int argc, char **argv, char **columnNames)
{
// basically this callback is called for each row in the SQL query 
result.
// for each row, argc indicates how many columns are returned.
// columnNames[i] is the name of the column
// argv[i] is the value of the column

sqlite_resultrow* pRow;
sqlite_resultset* pResultSet;
char* name;
char* value;
int i;

if (argc == 0)
   return 0;

pResultSet = (sqlite_resultset*)pArg;
if (!pResultSet)
   return -1;

// create a new result row
pRow = new sqlite_resultrow;
pResultSet->iNumCols = argc;
// loop through all the columns and stuff them into our row
for (i = 0; i < argc; i++)
{
   // DBEUG CODE
//  Con::printf("%s = %s\n", columnNames[i], argv[i] ? argv[i] : 
"NULL");
   name = new char[dStrlen(columnNames[i]) + 1];
   dStrcpy(name, columnNames[i]);
   pRow->vColumnNames.push_back(name);
   if (argv[i])
   {
  value = new char[dStrlen(argv[i]) + 1];
  dStrcpy(value, argv[i]);
  pRow->vColumnValues.push_back(value);
   }
   else
   {
  value = new char[10];
  dStrcpy(value, "NULL");
  pRow->vColumnValues.push_back(value);
   }
}
pResultSet->iNumRows++;
pResultSet->vRows.push_back(pRow);

// return 0 or else the sqlexec will be aborted.
return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users