Re: [sqlite] on delete no action

[Dan Kennedy]

On Jun 2, 2010, at 11:41 AM, Scott Frankel wrote:

>
> Hi all,
>
> I've been lurking for a few days while I explore SQLite and find
> myself now with a question.
>
> What's the correct usage for the "ON DELETE NO ACTION" statement?
>
> I'm converting a schema file to SQLite.  Executing a create table
> statement on the sqlite3 cmd-line, ON DELETE NO ACTION yields an
> error:  SQL error: near "ON": syntax error.  Yet according to the
> docs:  "NO ACTION" is a valid action.
>
> Is the absence of the ON DELETE rule itself (example 2 below)
> equivalent to specifying "ON DELETE NO ACTION?"  What concept am I
> missing from the docs?  ;)

Could be that you need SQLite version 3.6.19 or later.

Dan.

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

[sqlite] on delete no action

[Scott Frankel]

Hi all,

I've been lurking for a few days while I explore SQLite and find  
myself now with a question.

What's the correct usage for the "ON DELETE NO ACTION" statement?

I'm converting a schema file to SQLite.  Executing a create table  
statement on the sqlite3 cmd-line, ON DELETE NO ACTION yields an  
error:  SQL error: near "ON": syntax error.  Yet according to the  
docs:  "NO ACTION" is a valid action.

Is the absence of the ON DELETE rule itself (example 2 below)  
equivalent to specifying "ON DELETE NO ACTION?"  What concept am I  
missing from the docs?  ;)

Thanks
Scott



-- 1) doesn't work
CREATE TABLE foo (
foo_id  SERIAL  PRIMARY KEY,
bar_id  INTEGER NOT NULL REFERENCES bar(bar_id) ON DELETE NO ACTION,
created TIMESTAMP   DEFAULT CURRENT_TIMESTAMP);



-- 2) works!!!
CREATE TABLE foo (
foo_id  SERIAL  PRIMARY KEY,
bar_id  INTEGER NOT NULL REFERENCES bar(bar_id),
created TIMESTAMP   DEFAULT CURRENT_TIMESTAMP);



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

Re: [sqlite] MySQL vs. SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

> You might be interested in NoSQL, or in databases which have no schema: every 
> piece of information is a property of an object.

I do happen to use them pretty intensively, especially MongoDB, so here are
some things they do differently:

There is no such thing as a join and each object (aka document) is
completely independent of all others.  ie there is no way to reference one
document from another as a "query".  If you need that functionality then you
have to grab relevant documents to the client and do the matching yourself.
 Because of the independence it is trivial to shard and replicate the database.

They do have indices so that queries are quick, such when looking for fields
to contain a particular value.  As an example MongoDB supports a rich set of
query operators:

  http://www.mongodb.org/display/DOCS/Advanced+Queries

And the index information:

 http://www.mongodb.org/display/DOCS/Indexes

Additionally they also provide map/reduce as an index mechanism, and in the
case of CouchDB this is the only query/index mechanism where they call it a
view.  Conceptually a piece of code is called for each document and can
return what key goes into the index as well as what value.  This allows for
very complex index key/values.  A reduce is similar to aggregate functions.
 Map/reduce is done incrementally as documents are added.

  http://books.couchdb.org/relax/design-documents/views
  http://www.mongodb.org/display/DOCS/MapReduce

MongoDB does another interesting thing for query plans.  Rather than try to
compute the ultimate correct one, it runs multiple candidates in parallel
picking whichever finishes first.  More details:

  http://www.mongodb.org/display/DOCS/Query+Optimizer

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkwFwz4ACgkQmOOfHg372QRbCwCgjnKNXwwoZOS/86yIbbzVyGjq
wpMAnA3Uu5xZjlflt1p0Qpc8Jvvek3dv
=UjLm
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MySQL vs. SQLite

[Darren Duncan]

Simon Slavin wrote:
> On 2 Jun 2010, at 1:14am, Darren Duncan wrote:
> 
>> What are some examples of the proposed SQL replacements that do this?
> 
> You might be interested in NoSQL, or in databases which have no schema: every
> piece of information is a property of an object.  Please note: I am not
> recommending these systems, or saying that I use them; I'm just talking about
> database theory.

Yes, I understand.  And similarly, a relational database doesn't have to have a 
schema, where "schema" means a pre-defined set of relvars/tables with specific 
attributes/columns that restricts what data the relational database may hold, 
though it is typically a good idea to use a schema.  With a sufficiently smart 
relational DBMS, users never have to explicitly define indexes and the DBMS can 
implicitly figure out for itself how to optimize the way the database is used 
or 
cached or indexed for best performance.  If users ever explicitly define 
indexes, this is more of a hint to a DBMS to assist it figuring out how to best 
performance, rather than being necessary to have good performance at all. -- 
Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MySQL vs. SQLite

[Simon Slavin]

On 2 Jun 2010, at 1:14am, Darren Duncan wrote:

> What are some examples of the proposed SQL replacements that do this?

You might be interested in NoSQL, or in databases which have no schema: every 
piece of information is a property of an object.  Please note: I am not 
recommending these systems, or saying that I use them; I'm just talking about 
database theory.

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

Re: [sqlite] MySQL vs. SQLite

[Darren Duncan]

Simon Slavin wrote:
> On 1 Jun 2010, at 11:59pm, Scott Hess wrote:
>> Well, really, what you want is "SQLite, for this table, I want to these
>> SELECT and UPDATE statements in this ratio, what indices would be ideal?"
> 
> That's often handled with smart caching.  The cache system notes down how
> often each item is hit, and uses that information to decide which items
> should be wiped from the cache when more memory is needed. (That's a massive
> simplification.).  Some of the proposed replacements for SQL involve smart
> systems like this: the programmer never creates any indices at all.  It's up
> to the database engine to decide how to do the searches most efficiently, the
> programmer just says how much memory it can use to do so.

What are some examples of the proposed SQL replacements that do this?

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

Re: [sqlite] MySQL vs. SQLite

[Simon Slavin]

On 1 Jun 2010, at 11:59pm, Scott Hess wrote:

> Well, really, what you want is "SQLite, for this table, I want to
> these SELECT and UPDATE statements in this ratio, what indices would
> be ideal?"

That's often handled with smart caching.  The cache system notes down how often 
each item is hit, and uses that information to decide which items should be 
wiped from the cache when more memory is needed. (That's a massive 
simplification.).  Some of the proposed replacements for SQL involve smart 
systems like this: the programmer never creates any indices at all.  It's up to 
the database engine to decide how to do the searches most efficiently, the 
programmer just says how much memory it can use to do so.

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

Re: [sqlite] MySQL vs. SQLite

[Scott Hess]

On Tue, Jun 1, 2010 at 2:37 PM, Simon Slavin  wrote:
> On 1 Jun 2010, at 7:11pm, Israel Lins Albuquerque wrote:
>> About that future release functionality. Will be possible to know whats 
>> temporary index are created?
>> Using that information will be easy to know what indexes we need create to 
>> increase perfomance,
>> don't giving chance to sqlite create that indexes!
>
> You can do something pretty good with EXPLAIN QUERY PLAN:
>
> http://www.sqlite.org/lang_explain.html
>
> I wonder how that could be enhanced to refer to any temporary index created.
>
> I understand what Israel wants, I think: CREATE your table with no indices, 
> then ask SQLite what index would be ideal for a SELECT or UPDATE statement.

Well, really, what you want is "SQLite, for this table, I want to
these SELECT and UPDATE statements in this ratio, what indices would
be ideal?"

-scott
[I was always annoyed by someone telling me that they needed some
index added to make their seldom-run query incrementally faster
without having figured out the downside hit to the updates which
generated the data they were querying.]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MySQL vs. SQLite

[Simon Slavin]

On 1 Jun 2010, at 7:11pm, Israel Lins Albuquerque wrote:

> About that future release functionality. Will be possible to know whats 
> temporary index are created? 
> Using that information will be easy to know what indexes we need create to 
> increase perfomance, 
> don't giving chance to sqlite create that indexes!

You can do something pretty good with EXPLAIN QUERY PLAN:

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

I wonder how that could be enhanced to refer to any temporary index created.

I understand what Israel wants, I think: CREATE your table with no indices, 
then ask SQLite what index would be ideal for a SELECT or UPDATE statement.

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

Re: [sqlite] Performance issue on view

[Israel Lins Albuquerque]

Create a new table to do this and add a trigger on op to make the sum. 

- Mensagem original - 
De: "Stéphane MANKOWSKI"  
Para: sqlite-users@sqlite.org 
Enviadas: Terça-feira, 1 de Junho de 2010 16:57:16 
Assunto: [sqlite] Performance issue on view 

Hi, 

In the this database file (http://skrooge.org/files/test.wrk), I created a 
table 
named "op" containing banking 
transactions. 
A transaction has: 
An unique id 
An account 
A date 
An amount 

I created a view named "v_op" with one more computed attribute named 
"balance". 
This attribute is the sum of all previous transactions (including current one) 
for the same account. 

My problem is that v_op is very slow. This is not usable. 

What can I do to improve performances ? 

PS: I don't want to compute "balance" attribute by code and save it in op 
balance due to the fact that I am using an undo/redo mechanism. 

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


-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

Re: [sqlite] Performance issue on view

[Pavel Ivanov]

> PS: I don't want to compute "balance" attribute by code and save it in op
> balance due to the fact that I am using an undo/redo mechanism.

>From my experience this is the only way to go - calculate the balance
in your application then store it in database along with transaction
as "balance after this transaction". Yes, you would have to update
many rows during any undo/redo operation but otherwise you will wait
too long to calculate balances on the fly. You can implement some
interim solution which will calculate balance say for the end of each
month. So to calculate current balance you'll have to take record for
the end of previous month and add all transactions for the current
month...


Pavel

2010/6/1 Stéphane MANKOWSKI :
> Hi,
>
> In the this database file (http://skrooge.org/files/test.wrk), I created a 
> table
> named "op" containing banking
> transactions.
> A transaction has:
>        An unique id
>        An account
>        A date
>        An amount
>
> I created a view named "v_op" with one more computed attribute named
> "balance".
> This attribute is the sum of all previous transactions (including current one)
> for the same account.
>
> My problem is that v_op is very slow. This is not usable.
>
> What can I do to improve performances ?
>
> PS: I don't want to compute "balance" attribute by code and save it in op
> balance due to the fact that I am using an undo/redo mechanism.
>
> Regards,
> Stephane
> ___
> 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] Different error message after sqlite3_reset() or sqlite3_finalize()

[Ralf Junker]

Related to constraint violations, an error message returned by 
sqlite3_errmsg() changes to the better after calling sqlite3_reset() or 
sqlite3_finalize().

Example code is below, here is the output:

sqlite3_step: 19 constraint failed
sqlite3_reset:19 t.c may not be NULL
sqlite3_finalize:  0 t.c may not be NULL

As can be seen, sqlite3_reset() "enhances" the error message returned by 
sqlite3_errmsg() by filling in the column name and reason why the 
constraint failed.

I would like to ask if this could be changed so that sqlite3_step() by 
itself generates the more meaningful error message, even without calling 
sqlite3_reset() or sqlite3_finalize()?

IIRC, having sqlite3_step() generate the "final" error codes and 
messages was one of the reasons that brought about sqlite3_prepare_v2(). 
I observe that the error message generated after sqlite3_prepare_v2() 
("constraint failed") is far better than that of sqlite3_prepare() ("SQL 
logic error or missing database"), but it is not as good as it gets 
after resetting the statement.

Ralf

 Example code:

int main(int argc, char* argv[])
{
   sqlite3* db;
   sqlite3_stmt* stmt;
   int e;

   sqlite3_open("test.db3", &db);

   sqlite3_exec(db, "create table if not exists t (c text not null)",
NULL, NULL, NULL);

   sqlite3_prepare_v2 (db, "insert into t values (null)", -1,
   &stmt, NULL);

   e = sqlite3_step(stmt);
   printf("sqlite3_step: %d %s\n", e, sqlite3_errmsg(db));

   e = sqlite3_reset(stmt);
   printf("sqlite3_reset:%d %s\n", e, sqlite3_errmsg(db));

   e = sqlite3_finalize(stmt);
   printf("sqlite3_finalize:  %d %s\n", e, sqlite3_errmsg(db));

   sqlite3_close (db);

   printf ("\nDone");
   scanf ("*%s");

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

[sqlite] Performance issue on view

[Stéphane MANKOWSKI]

Hi,

In the this database file (http://skrooge.org/files/test.wrk), I created a 
table 
named "op" containing banking 
transactions.
A transaction has:
An unique id
An account
A date
An amount

I created a view named "v_op" with one more computed attribute named 
"balance".
This attribute is the sum of all previous transactions (including current one) 
for the same account.

My problem is that v_op is very slow. This is not usable.

What can I do to improve performances ?

PS: I don't want to compute "balance" attribute by code and save it in op 
balance due to the fact that I am using an undo/redo mechanism.

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

Re: [sqlite] What languages can include SQLite statically?

[Gilles Ganault]

On Tue, 1 Jun 2010 15:13:57 -0400, Doug Currie
 wrote:
>You may find eLua interesting.  http://www.eluaproject.net/ 
>The supported platforms are heavily ARM based, but in the same performance 
>class as Blackfin.

Thanks very much for the link. I'll go check if it can be compiled for
the Blackfin.

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

Re: [sqlite] What languages can include SQLite statically?

[Doug Currie]

On Jun 1, 2010, at 2:24 PM, Gilles Ganault wrote:

> Actually, it's a Blackfin processor, and since it's an embedded
> environment, RAM and storage (NAND) are an issue.

You may find eLua interesting.  http://www.eluaproject.net/ 
The supported platforms are heavily ARM based, but in the same performance 
class as Blackfin.

e

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

Re: [sqlite] MySQL vs. SQLite

[Jay A. Kreibich]

On Tue, Jun 01, 2010 at 02:13:06PM -0400, Richard Hipp scratched on the wall:
> On Tue, Jun 1, 2010 at 2:09 PM, Israel Lins Albuquerque <
> israel...@polibrasnet.com.br> wrote:
> 
> > About that future release functionality. Will be possible to know whats
> > temporary index are created?
> > Using that information will be easy to know what indexes we need create to
> > increase perfomance,
> > don't giving chance to sqlite create that indexes!

> No.  Unfortunately we do not have any mechanism to tell you what indices
> were created.  There is a mechanism to alert the application to the fact
> that transient indices were creating for a particular query, as a hint to
> the developer to go back and think more about indices on that query.  But it
> does not provide any suggestions on what indices would be useful.

  I also assume the query optimizer may create an index on a transient
  set (or "materialized table" in some books) that might not exist as an
  actual table.  If that's true, there may not be any standard index that
  can be created.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What languages can include SQLite statically?

[Gilles Ganault]

On Tue, 1 Jun 2010 11:33:36 -0400 (EDT), Rob Sciuk
 wrote:
>As you may be aware, SQLite and Tcl/Tk have an affinity which was not 
>entirely accidental.  There are threads which discuss compiling Tcl for
>Arm/Linux:
>
>(http://objectmix.com/tcl/15449-how-cross-compile-tcl8-4-tk8-4-arm-linux.html)
>
>And you might find some binaries in various places:
>
>(http://www.evolane.com/)
>
>If your Arm platform is a full blown Linux with the development 
>environment, the problem is further reduced to a simple ./configure ; make 
>; make install (or two) rather than a cross compilation using a Canadian 
>Cross (tricky).

Actually, it's a Blackfin processor, and since it's an embedded
environment, RAM and storage (NAND) are an issue.

I'll check if it's possible to compile EvoWeb for that platform.

Thank you.

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

Re: [sqlite] MySQL vs. SQLite

[Richard Hipp]

On Tue, Jun 1, 2010 at 2:09 PM, Israel Lins Albuquerque <
israel...@polibrasnet.com.br> wrote:

>
>
> About that future release functionality. Will be possible to know whats
> temporary index are created?
> Using that information will be easy to know what indexes we need create to
> increase perfomance,
> don't giving chance to sqlite create that indexes!
> _
>


No.  Unfortunately we do not have any mechanism to tell you what indices
were created.  There is a mechanism to alert the application to the fact
that transient indices were creating for a particular query, as a hint to
the developer to go back and think more about indices on that query.  But it
does not provide any suggestions on what indices would be useful.


-- 
-
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] MySQL vs. SQLite

[Israel Lins Albuquerque]

About that future release functionality. Will be possible to know whats 
temporary index are created? 
Using that information will be easy to know what indexes we need create to 
increase perfomance, 
don't giving chance to sqlite create that indexes! 
-- 

Regards/Atenciosamente, 

Israel Lins Albuquerque 
Developer/Desenvolvimento 
Polibrás Brasil Software Ltda. 

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

Re: [sqlite] MySQL vs. SQLite

[Israel Lins Albuquerque]

- Mensagem original - 
De: "Richard Hipp"  
Para: "General Discussion of SQLite Database"  
Enviadas: Segunda-feira, 31 de Maio de 2010 20:56:33 
Assunto: Re: [sqlite] MySQL vs. SQLite 

On Mon, May 31, 2010 at 3:57 PM, Simon Slavin  wrote: 

> 
> On 31 May 2010, at 7:17pm, Michael Ash wrote: 
> 
> > (Curiously, this is only 
> > the case on the first run of the query; if I run the query again, the 
> > MySQL time drops to close to zero while sqlite appears to take the 
> > same time. Maybe MySQL leaves the database sorted or somehow caches 
> > that particular run?) 
> 
> SQLite finds the best index it can and uses it. If there are no indices, 
> it searches the entire table. 
> 
> MySQL has a server process which runs until you shut it down (usually when 
> you reboot the server). Every time you do anything that could use an index 
> (including the WHERE clause in an UPDATE) it looks for a perfect index. If 
> it finds one, it uses it. If it doesn't find one, it finds the best it can, 
> but constructs a temporary index specially designed for the operating you're 
> doing. These temporary indices are cached, on the assumption that if you've 
> executed a command once, you're probably going to do it again. 
> 
> This is an excellent part of MySQL and has lead many MySQL programmers to 
> completely ignore the CREATE INDEX command because once MySQL has executed 
> one of every command, everything executes quickly. However, it requires a 
> lot of memory to be used for caching, and a persistent server process. And 
> it would require a thorough rewrite of SQLite which would then no longer be 
> practical for small fast embedded devices. 
> 

Great explanation, Simon. Thanks! 

FWIW, the latest versions of SQLite in the source tree will also create a 
temporary index to help with a query, if SQLite estimates that the expense 
of creating and using index is less than doing a full-table scan. SQLite is 
unable to cache indices, though. So the entire cost of building the index 
must be recouped on a single query or SQLite will figure that creating the 
index is not worth the effort and just go ahead with a brute-force query. 
Hence, temporary indices are normally only created for multi-way joins or 
perhaps for subqueries. 

This automatic-indexing feature is new. It has only been in the source tree 
since early April and has not yet appeared in a released version of SQLite. 


> 
> MySQL and SQLite are both excellent examples of their craft, but they're 
> suitable for different situations. About the only thing they have in common 
> is that they both speak SQL. 
> 
> 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 


About that future release functionality. Will be possible to know whats 
temporary index are created? 
Using that information will be easy to know what indexes we need create to 
increase perfomance, 
don't giving chance to sqlite create that indexes! 


-- 

Regards/Atenciosamente, 

Israel Lins Albuquerque 
Developer/Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

Re: [sqlite] What languages can include SQLite statically?

[Gilles Ganault]

On Mon, 24 May 2010 12:28:14 +0400, Max Vlasov
 wrote:
>For Delphi I successfully used files from http://www.aducom.com to
>statically link sqlite files compiled with bcc (Borland command-line c
>compiler freely available now) with Delphi. Also the components of
>aducom.com will allow you to use all the power of Delphi database components
>with the sqlite without necessity to provide any additional dlls.

Thanks for the tip on Delphi + Aducom, but as I definitely need a
cross-platform solution, I guess that won't do it.

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

Re: [sqlite] What languages can include SQLite statically?

[Rob Sciuk]

From: Gilles Ganault 
> On Tue, 1 Jun 2010 08:40:15 -0300 (BRT), Israel Lins Albuquerque
>  wrote:
> >Look for C++ QT framework! http://qt.nokia.com/
> 
> Thanks, I'll check it out.

Gilles,

As you may be aware, SQLite and Tcl/Tk have an affinity which was not 
entirely accidental.  There are threads which discuss compiling Tcl for
Arm/Linux:

(http://objectmix.com/tcl/15449-how-cross-compile-tcl8-4-tk8-4-arm-linux.html)

And you might find some binaries in various places:

(http://www.evolane.com/)

If your Arm platform is a full blown Linux with the development 
environment, the problem is further reduced to a simple ./configure ; make 
; make install (or two) rather than a cross compilation using a Canadian 
Cross (tricky).

Elsewhere in this thread, someone mentioned PHP, and PHP can, in theory be 
run stand alone (without Apache).  Moving from a scripting language/SQLite 
solution to a C++/Qt/SQLite solution is quite a step, IMHO.  I'm not sure 
about the state of the Java VM on the ARM platforms, but that too might be 
a possibility ... but I'd certainly give the Tcl/Tk+SQLite a shot first. 
I've not used Lua, but I understand it to be highly portable ... but I 
have no knowledge of SQLite bindings for it ... shouldn't be hard, though.

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

[sqlite] Returning empty result set

[Max Vlasov]

Hi,

Tried to figured out the simplest query returning empty result set without
binding to any existing table.

The query
SELECT 1 WHERE 1=2

worked, but it looked a little strange ) and besides it didn't work in
mysql. Will it work in future versions of sqlite or I'd be better to use a
query working in both worlds:

SELECT * FROM (SELECT 1) AS TBL WHERE 1=2

?

Thanks

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

Re: [sqlite] What languages can include SQLite statically?

[Gilles Ganault]

On Tue, 1 Jun 2010 08:40:15 -0300 (BRT), Israel Lins Albuquerque
 wrote:
>Look for C++ QT framework! http://qt.nokia.com/ 

Thanks, I'll check it out.

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

Re: [sqlite] What languages can include SQLite statically?

[Israel Lins Albuquerque]

Look for C++ QT framework! http://qt.nokia.com/ 


- Mensagem original - 
De: "Gilles Ganault"  
Para: sqlite-users@sqlite.org 
Enviadas: Terça-feira, 1 de Junho de 2010 8:10:34 
Assunto: Re: [sqlite] What languages can include SQLite statically? 

On Tue, 1 Jun 2010 11:57:29 +0100, Simon Slavin 
 wrote: 
>The obvious solution is to use PHP, and have it use one of the three 
>avaialable sets of SQLite calls. 

Can I compile a PHP script + modules into something that will run on 
embedded devices? They don't have enough RAM to run eg. Apache + 
mod_php. 

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


-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

[sqlite] Bug and possible fix: Access violation in rtree.c:nodeGetRowid()

[Peter Kolbus]

I am getting an access violation in rtree.c::nodeGetRowid() using a
database image of questionable integrity (the application the database
was last updated on shut down abnormally, but executing PRAGMA
integrity_check returns "ok" and there is no journal file).  The
access violation can be reproduced by executing the following query in
the sqlite3 shell:

DELETE FROM history_rtree WHERE NOT EXISTS(SELECT _id FROM history
WHERE history._id = history_rtree._id);

Using http://sqlite.org/sqlite-source-3_6_23_1.zip (to get reasonable
line numbers), the call stack is roughly:
>   sqlite.exe!nodeGetRowid(Rtree * pRtree=0x00348340, RtreeNode * 
> pNode=0x, int iCell=0x)  Line 568 + 0x3 bytesC
sqlite.exe!nodeRowidIndex(Rtree * pRtree=0x00348340, RtreeNode *
pNode=0x, __int64 iRowid=0x002d)  Line 874 + 0x23
bytes   C
sqlite.exe!rtreeUpdate(sqlite3_vtab * pVtab=0x00348340, int
nData=0x0001, Mem * * azData=0x0034f908, __int64 *
pRowid=0x0012f100)  Line 2385 + 0x41 bytes  C
sqlite.exe!sqlite3VdbeExec(Vdbe * p=0x00348ff0)  Line 6125 + 0x29 bytes 
C
sqlite.exe!sqlite3Step(Vdbe * p=0x00348ff0)  Line 370 + 0x9 bytes   
C
sqlite.exe!sqlite3_step(sqlite3_stmt * pStmt=0x00348ff0)  Line 432 +
0x9 bytes   C
sqlite.exe!shell_exec(sqlite3 * db=0x00343210, const char *
zSql=0x0052dcc0, int (void *, int, char * *, char * *, int *)*
xCallback=0x00456400, callback_data * pArg=0x0012f940, char * *
pzErrMsg=0x0012f7b4)  Line 1012 + 0x9 bytes C

The call to findLeafNode() at rtree.c line 2379 returned SQLITE_OK but
set pLeaf to NULL, and I note that this postcondition is documented as
possible.

Adding a check for pLeaf to the if condition on line 2383 prevents the
access violation:

if( rc==SQLITE_OK && pLeaf ) {

Is this the correct fix?

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

[sqlite] OpenSQLCamp EU 2010 - Call for participation is now open

[Giuseppe Maxia]

OpenSQL Camp is a free conference of, by, and for the open-source database
community of users and developers. The OpenSQLCamp 2010, European Edition
(http://opensqlcamp.org/) will take part in parallel to the Free and Open
Source Conference 2010 (http://froscon.org/) on Saturday 21st and Sunday
22nd August in St. Augustin, Germany, which is located close to Bonn and
Cologne.

The goal of this event is to spread the word about the vibrant communities
and large ecosystems that exist around Open Source Databases and to educate
the attendees about possible alternatives to commercial databases. It is a
place where people come to learn, to participate and to contribute.

We would like to invite your project to participate in this event.

We've set up a call for participation
(http://opensqlcamp.org/Events/2010/Call_for_Participation) - the deadline
for submitting your proposal is July 11th.

We are seeking talks related to Open Source Databases of all kind, not just
relational databases! Submission about tools and technologies related to
OSS databases (e.g. connectors/APIs) are also welcome.

Some ideas and for submissions:

* A how to presentation, showing how to solve a common problem in the
database field.
* An introduction/overview about a certain database project/product or
  related tool
* Providing "best practices" information for administrators
* A deeply technical and developer-centric session about some project's
  internals or an API used to connect to a database

We look forward to your contribution! Please don't hesitate to contact us
via IRC (#opensqlcamp on FreeNode) or our Discussion Group
(http://groups.google.com/group/opensqlcamp)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What languages can include SQLite statically?

[Gilles Ganault]

On Tue, 1 Jun 2010 11:57:29 +0100, Simon Slavin
 wrote:
>The obvious solution is to use PHP, and have it use one of the three 
>avaialable sets of SQLite calls.

Can I compile a PHP script + modules into something that will run on
embedded devices? They don't have enough RAM to run eg. Apache +
mod_php.

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

Re: [sqlite] What languages can include SQLite statically?

[Gilles Ganault]

On Tue, 1 Jun 2010 05:49:27 -0500, "Black, Michael (IS)"
 wrote:
>About the only thing you'll find cross-platform + embedded is C/C++

Yes, it looks like it's pretty much it. I'll check Lua, though, to see
whether the modules I need are either in Lua, or are writte in C and
can be compiled to whatever CPU I need.

Thanks for the tip on Code::Blocks.

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

Re: [sqlite] Getting a "rowid" from a view

[Pavel Ivanov]

A bit of bitterness here: Jim, do you know that SELECT doesn't
guarantee rows to be returned in any particular order unless ORDER BY
clause is used? It's even not guaranteed that the same SELECT without
ORDER BY issued twice will return rows in the same order. So when you
do
SELECT * FROM phonebook ORDER BY last_name, first_name
you are guaranteed to get ordered rows. But when you do
SELECT * FROM phonebook_order
database engine is not obligated to give you rows in any particular order.

And one more thing: please don't mix rowid with row number. They are
completely different things. You want row number for some particular
row so you have to count rows in your application or to issue
appropriate select for that. Appropriate select would be like this:

SELECT COALESCE(COUNT(*), 0) + 1 from phonebook
WHERE last_name < 'Smith'
OR (last_name = 'Smith' AND first_name < 'John');

But here you have to think of how to assign row numbers to different
people named John Smith.


Pavel

On Fri, May 28, 2010 at 8:19 PM, Jim Terman  wrote:
> Say I have a table of phone numbers
>
> CREATE TABLE phonebook (first_name TEXT, last_name TEXT, phone_number TEXT);
>
> I want to sort this by name, so I create a view
>
> CREATE VIEW phonebook_order AS SELECT first_name, last_name,
> phone_number FROM phonebook ORDER BY last_name, first_name;
>
> Now on the table phonebook I can do a query:
>
> SELECT rowid FROM phonebook where last_name = "Smith" and first_name =
> "John";
>
> which will gave me the row number of John Smith.
>
> How do I do this for the view phonebook_order?
>
> Nearest I can determine would be to run the command
>
> SELECT COUNT(*) from phonebook_order WHERE last_name <= "Smith" AND
> first_name <= "John";
>
> Is there an easier way?
> ___
> 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] What languages can include SQLite statically?

[Simon Slavin]

On 1 Jun 2010, at 10:30am, Gilles Ganault wrote:

> The problem is that I'd like a cross-platform
> solution so that the HTTP + SQLite solution runs on Windows and Linux

The obvious solution is to use PHP, and have it use one of the three avaialable 
sets of SQLite calls.

> (and in the case of Linux, ideally, should also compile on platforms
> other than x86 such as embedded devices).

That's harder.  You could use Zend, I suppose.  Zend is the actual language PHP 
supports.  PHP is just the Zend programming language implemented as an Apache 
module.

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

Re: [sqlite] What languages can include SQLite statically?

[Black, Michael (IS)]

About the only thing you'll find cross-platform + embedded is C/C++
 
For an IDE use Code::Blocks as it is cross-platform Windows/Unix (no embedded 
though of course).  But does run gcc on both.  And since gcc is a popular 
choice for embedded that will maximize your code portability.
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Gilles Ganault
Sent: Tue 6/1/2010 4:30 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What languages can include SQLite statically?



On Mon, 24 May 2010 12:28:14 +0400, Max Vlasov
 wrote:
>For Delphi I successfully used files from http://www.aducom.com 
>  to
>statically link sqlite files compiled with bcc (Borland command-line c
>compiler freely available now) with Delphi. Also the components of
>aducom.com will allow you to use all the power of Delphi database components
>with the sqlite without necessity to provide any additional dlls.

Thanks for the tip. The problem is that I'd like a cross-platform
solution so that the HTTP + SQLite solution runs on Windows and Linux
(and in the case of Linux, ideally, should also compile on platforms
other than x86 such as embedded devices).

___
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] What languages can include SQLite statically?

[Gilles Ganault]

On Mon, 24 May 2010 12:28:14 +0400, Max Vlasov
 wrote:
>For Delphi I successfully used files from http://www.aducom.com to
>statically link sqlite files compiled with bcc (Borland command-line c
>compiler freely available now) with Delphi. Also the components of
>aducom.com will allow you to use all the power of Delphi database components
>with the sqlite without necessity to provide any additional dlls.

Thanks for the tip. The problem is that I'd like a cross-platform
solution so that the HTTP + SQLite solution runs on Windows and Linux
(and in the case of Linux, ideally, should also compile on platforms
other than x86 such as embedded devices).

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

[sqlite] check constraint error message

[Andrea Galeazzi]

Is it possible to have a custom check constraint error message?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Windows] Application to let end-users handle records?

[Gilles Ganault]

On Tue, 18 May 2010 17:03:17 +0100, Bart Smissaert
 wrote:
>This is a VB6 procedure (in an ActiveX dll) that handles this.
>Obviously there are a lot of secondary routines that you don't have,
>but I think you will get the general idea of what is going on here.

Thanks a lot for the source code.

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

Re: [sqlite] What languages can include SQLite statically?

[Gilles Ganault]

On Sat, 22 May 2010 14:09:18 +0200, Peter Rodwell
 wrote:
>Take a look at REALbasic (http://www.realsoftware.com/), now called
>REALStudio. It has SQLite support built in (and support for other
>dabases such as MySQL). The same source code can be compiled simultaneously
>for Windows (32-bit only for now), Linux and Mac.

Thanks for the tip. However, it's proprietary and a bit pricey, so I'd
rather investigate writing an HTTP + SQLite combo before choosing that
solution.

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