Re: [sqlite] SELECT question (computing day of week the usingstrftime() function)

2010-05-01 Thread Roger Andersson 
> I need to find out how many specific weekdays (e.g., how many 
> Sundays) I have in any given range of dates.
> My problem: How to use the COUNT function in combination with 
> the strftime() function.
> 
Maybe something like

sqlite3 test.db
SQLite version 3.6.23
sqlite> CREATE TABLE test (date TEXT, money INTEGER);
sqlite> INSERT INTO test VALUES('2007-07-20', 1000);
sqlite> INSERT INTO test VALUES('2007-07-21', 2100);
sqlite> INSERT INTO test VALUES('2007-07-22', 2200);
sqlite> INSERT INTO test VALUES('2007-07-27', 7000);
sqlite> INSERT INTO test VALUES('2007-07-28', 2800);
sqlite> INSERT INTO test VALUES('2007-07-29', 2900);
sqlite> INSERT INTO test VALUES('2007-07-22', 9200);
sqlite> SELECT strftime('%w', date) weekday, count(*) cnt FROM test GROUP BY
weekday;
weekday|cnt
0|3
5|2
6|2

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

Re: [sqlite] sqlite update with joins

2010-05-01 Thread Pavel Ivanov 
> But it is ambigous ...

It is not ambiguous when you know how SQL works and it's the only
standard way of doing such things.


Pavel

On Sat, May 1, 2010 at 1:31 PM,   wrote:
> Yes that works.
> But it is ambigous ...
>
> -Ursprüngliche Nachricht-
> Von: Igor Tandetnik 
> Gesendet: 01.05.2010 16:00:01
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] sqlite update with joins
>
> wdl...@web.de wrote:
>> The general problem with this issue is that it is not possible
>>
>> to set an alias for the table in an update statement.
>> That should be implemented because otherwise some necessary statements are 
>> not possible.
>>
>> e.g.
>>
>> update T x
>>  set x.col1 = ( select  sum(c) from T y where y.id = x.id )
>
> update T set col1 = (select sum(c) from T y where y.id = T.id);
>
> Try it, it works. Identifier T binds to the first mention of the table, which 
> happens to be the one in the outer UPDATE statement. Other instances of T may 
> be referred to with aliases.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> NEU: WEB.DE DSL für 19,99 EUR/mtl. und ohne Mindest-Laufzeit!
> http://produkte.web.de/go/02/
> ___
> 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] SELECT question (computing day of week the using strftime() function)

2010-05-01 Thread Jean-Christophe Deschamps 

>I need to find out how many specific weekdays (e.g., how many Sundays) 
>I have in any given range of dates.
>My problem: How to use the COUNT function in combination with the 
>strftime() function.
>
>$ sqlite3 test.db3
>SQLite version 3.6.20
>sqlite> create table test (date VARCHAR(20), money INTEGER);
>sqlite> INSERT INTO "test" VALUES('2007-07-20', 1000);
>sqlite> INSERT INTO "test" VALUES('2007-07-21', 2100);
>sqlite> INSERT INTO "test" VALUES('2007-07-22', 2200);

That will do:
select count(*) from test where date between '2007-07-20' and 
'2007-07-22' and strftime('%w', date) = '0';

This will select and count, not compute anything.

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

[sqlite] SELECT question (computing day of week the using strftime() function)

2010-05-01 Thread Bernie Reiter 
Dear List Members,

I do need your wisdom and experience. Thank you very much for sharing these 
with me !

I have a table into which I import data records from an external source. 
These imported data records are all containing a date field.
I can change the field definitions in this table without any problems.

I need to find out how many specific weekdays (e.g., how many Sundays) I have 
in any given range of dates.
My problem: How to use the COUNT function in combination with the strftime() 
function.

$ sqlite3 test.db3
SQLite version 3.6.20
sqlite> create table test (date VARCHAR(20), money INTEGER);
sqlite> INSERT INTO "test" VALUES('2007-07-20', 1000);
sqlite> INSERT INTO "test" VALUES('2007-07-21', 2100);
sqlite> INSERT INTO "test" VALUES('2007-07-22', 2200); 

sqlite> SELECT date from test
   ...> WHERE date BETWEEN '2007-07-20' AND '2007-07-22';
2007-07-20
2007-07-21
2007-07-22

sqlite> SELECT COUNT(date) FROM test
   ...> WHERE date BETWEEN '2007-07-20' AND '2007-07-22';
3

sqlite> SELECT COUNT(date) FROM test
   ...> WHERE date = '2007-07-22';
1

sqlite> SELECT strftime('%w', date) FROM test
   ...> WHERE date BETWEEN '2007-07-20' AND '2007-07-22';
5
6
0

=>   BERNIE'S COMMENT: 2007-07-22 is a Sunday  <=
sqlite> SELECT strftime('%w', date) FROM test
   ...> WHERE date = '2007-07-22';
0

Perfect: I have 3 data records, only one of which is a Sunday.
I can see this in this easy example immediately, but does SQLite too?
I can import the result list of "SELECT strftime('%w', date) FROM test ..." 
into awk to do the counting there,
but maybe there is a nice SQL way...
I have tried to construct a SELECT statement to count how many Sundays I have 
in my data records.
I have tried many variantions of (sub)selects but I didn't get anywhere.
I don't want to "overflow" my posting by listing them all. I will show only 3 
as examples which I used to check my SELECT statements. Here in these 3 
examples I have not yet used the COUNT function...

sqlite> SELECT date AS XXX FROM test
   ...> WHERE date BETWEEN '2007-07-20' AND '2007-07-22'
   ...> AND (strftime('%w', XXX)) = 0;
sqlite> SELECT date AS XXX FROM test
   ...> WHERE XXX BETWEEN '2007-07-20' AND '2007-07-22'
   ...> AND (strftime('%w', date)) = 0;
sqlite> SELECT date AS XXX FROM test
   ...> WHERE XXX BETWEEN '2007-07-20' AND '2007-07-22'
   ...> AND date = (SELECT date FROM test WHERE (strftime('%w', date)) = 0);

Unfortunately, in all 3 examples the result of my SELECT statements is empty.

I have then enclosed the 0 for Sunday in single quotes:
sqlite> SELECT date AS XXX FROM test
   ...> WHERE XXX BETWEEN '2007-07-20' AND '2007-07-22'
   ...> AND date = (SELECT date FROM test WHERE (strftime('%w', date)) = '0');
2007-07-22

But my whole SELECT looks clumsy.

Any suggestions?

And I have no clue why I should enclose the integer result of a function (i.e., 
here 0) in quotes...

Thanks again for your help and do enjoy your weekend :-)

bernie


  

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

Re: [sqlite] scripting language interpreter

2010-05-01 Thread Gabor Grothendieck 
On Sat, May 1, 2010 at 12:15 PM, Richard Hipp  wrote:
> On Sat, May 1, 2010 at 9:25 AM, Tim Romano  wrote:
>
>> I am aware that SQLite supports
>> loadable extensions, but would the SQLite architecture also permit the
>> integration of an interpreted scripting language?   An integrated scripting
>> language makes an already powerful database engine orders of magnitude more
>> useful especially when one is solving ad hoc data problems requiring very
>> rapid turnaround.
>>
>
> See http://www.sqlite.org/tclsqlite.html for the original.  SQLite began
> life as a TCL extension.  In fact, we often think of SQLite as a TCL
> extension that escaped into the wild.
>
> The integration between TCL and SQLite is very tight.  If you know where to
> look, you will see that many features of SQLite were designed specifically
> to support integration with TCL.
>
> An example of TCL using SQLite:
>
>  db eval {SELECT name FROM people WHERE personid=$personid} {
>     puts name=$name
>  }

If I understand correctly what is being illustrated here then the
sqldf package in R (http://sqldf.googlecode.com) has a similar
facility.  For example. from the R command line:

> # installs everything needed into R
> install.packages("sqldf")
>
> # loads everything needed into R workspace
> library(sqldf)
>
> # create R data frame (similar concept to an SQL table)
> DF <- data.frame(a = 1:3, b = 4:6)
>
> # the next statement notices that DF is an R data frame,
> # it automatically creates an sqlite data base in memory,
> # sets up table definition for DF by issuing create table stmt,
> # loads DF into the sqlite data base,
> # performs the query returning a new data frame
> # and deletes the sqlite data base
>
> sqldf("select * from DF where a < 3")
  a b
1 1 4
2 2 5

The actual interfaces between R and sqlite is in the DBI and RSQLite R
packages and sqldf sits on top of those.  The RSQLite package also
includes a copy of sqlite.  Installing and loading sqldf automatically
installs and loads its dependencies.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite update with joins

2010-05-01 Thread wdlurd 
Yes that works.
But it is ambigous ...

-Ursprüngliche Nachricht-
Von: Igor Tandetnik 
Gesendet: 01.05.2010 16:00:01
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] sqlite update with joins

wdl...@web.de wrote:
> The general problem with this issue is that it is not possible
> 
> to set an alias for the table in an update statement.
> That should be implemented because otherwise some necessary statements are 
> not possible.
> 
> e.g.
> 
> update T x
>  set x.col1 = ( select  sum(c) from T y where y.id = x.id )

update T set col1 = (select sum(c) from T y where y.id = T.id);

Try it, it works. Identifier T binds to the first mention of the table, which 
happens to be the one in the outer UPDATE statement. Other instances of T may 
be referred to with aliases.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
NEU: WEB.DE DSL für 19,99 EUR/mtl. und ohne Mindest-Laufzeit!
http://produkte.web.de/go/02/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Modeling SQLite databases

2010-05-01 Thread M. Bashir Al-Noimi 
Hi folks,

Any one know if there is any open source software for modeling SQLite 
databases just like "SQLite Maestro 
"?


P.S.

Currently I'm using "SQLite Studio " it's 
nice editor but it doesn't support diagram database designer


-- 
Best Regards
Muhammad Bashir Al-Noimi
My Blog:http://mbnoimi.net

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

Re: [sqlite] scripting language interpreter

2010-05-01 Thread Richard Hipp 
On Sat, May 1, 2010 at 9:25 AM, Tim Romano  wrote:

> I am aware that SQLite supports
> loadable extensions, but would the SQLite architecture also permit the
> integration of an interpreted scripting language?   An integrated scripting
> language makes an already powerful database engine orders of magnitude more
> useful especially when one is solving ad hoc data problems requiring very
> rapid turnaround.
>

See http://www.sqlite.org/tclsqlite.html for the original.  SQLite began
life as a TCL extension.  In fact, we often think of SQLite as a TCL
extension that escaped into the wild.

The integration between TCL and SQLite is very tight.  If you know where to
look, you will see that many features of SQLite were designed specifically
to support integration with TCL.

An example of TCL using SQLite:

  db eval {SELECT name FROM people WHERE personid=$personid} {
 puts name=$name
  }

The "db" is an TCL object which represents an open SQLite database
connection.  "eval" is the "evaluation" method.  Within the SQL text, we see
a TCL variable $personid.  That is really implemented as an SQL parameter,
but the TCL wrapper knows to loop over all SQL parameters, look up the
corresponding TCL variables, and bind them.  The stuff that follows is a
script that runs for each row returned.  The columns of the SELECT statement
are stored in TCL variables making them very easy to access.

If you want to compile a TCL interpreter than includes SQLite, it is simple
to do.  Just obtain the tclsqlite3.c amalgamation (available on the download
page) and run this:

 cc -o tclsh -DTCLSH=1 tclsqlite3.c -ltcl -ldl -lpthread -lm

You might have to adjust the list of libraries at the end of the compiler
command-line, but that is basically all that is required.  The resulting
"tclsh" is a script interpreter that includes full SQLite integration.

Yes, SQLite can be bound to other scripting languages.  But it was designed
for TCL and the integration with TCL is very tight, seamless, and easy to
use.  In all other wrappers that I am aware of, the binding of parameters
and the looping over result rows are separate explicit steps, not built into
the language the way their are with TCL.  A tclsh with integrated SQLite
support is a very convenient tool to have at hand for many common tasks.

-- 
-
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] scripting language interpreter

2010-05-01 Thread Jay A. Kreibich 
On Sat, May 01, 2010 at 09:25:54AM -0400, Tim Romano scratched on the wall:

> In part, this is a very broad question but I hope it's not unacceptable.
> From a birdseye perspective, what is involved in integrating an interpreted
> scripting language with a database engine?

  A number of people have looked into adding some kind of PL/* language
  to SQLite so that you can store functions, etc., in the database
  itself.  There are third-party folks working on a PL/SQL extension
  that converts stuff to compiled code, but that's a bit different
  since it isn't tied directly to the database.

  It is extremely unlikely anything would ever end up in the core.
  The biggest issue is license.  I know of no public-domain scripting
  language implementations (that are worth using).  Language systems
  also tend to be big and tricky to test.  So you'd still need the
  correct extension, but you could use the same extension for every
  database.

  Adding support for functions, aggregates, and collations is easy
  enough.  Many of the scripting language API wrappers already include
  stubs that allow functions, etc., to be defined in the scripting
  language.  Triggers are a bit more difficult, unless you can can
  reduce the logic down to a function.  The issue is passing the row
  state in and out of the function.  There may be some clever way of
  doing it deeper in the library, but I'm not sure it is possible with
  the public API.



  I'd love to see a Lua extension.  I know that's been proposed. Lua is
  MIT licensed, small, fast, and reasonably mature.  It is also designed
  to be easily embeddable, making it a good fit. I realize many of you
  may not know about Lua, but it is heavily used in a number of specific
  areas, including the game industry, where it is *the* behavioral
  and AI language, mostly because it is easy to learn, easy to embed,
  and will compile on just about anything.  http://www.lua.org/

-j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] scripting language interpreter

2010-05-01 Thread P Kishor 
On Sat, May 1, 2010 at 9:53 AM, Tim Romano  wrote:
> Simon,
>
> It's not clear to me how this is a result of scripting language support:
>
> "Another problem with it is that sooner or later you need your inner
> language (your SQL engine) to have access to your outer environment, for
> example, to find out if you have lots of filespace free."
>
> Could you please elaborate?
>
> The ability to declare FOREIGN KEYS and TRANSACTIONS are not what I had in
> mind when I used the phrase "scripting language". I am thinking of
> full-blown procedural logic integrated with the RDBMS. The foremost example
> that comes to mind is VBA in MS-Access, though many other examples are
> available, e.g. Revelation, Borland Paradox, or even java in Oracle.
>


You do it the other way around... instead of embedding a scripting
language inside SQLite (which would convert it to
SQLVeryHeavyAndCumbersomeAndLikelyToBlowUp), you embed sqlite inside a
scripting language. See how elegantly this is done with Perl and
DBD::SQLite which embeds its own sqlite engine in the perl module.
Similar products exist for Tcl and Python and other languages.

..  ..


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] scripting language interpreter

2010-05-01 Thread Tim Romano 
Simon,

It's not clear to me how this is a result of scripting language support:

"Another problem with it is that sooner or later you need your inner
language (your SQL engine) to have access to your outer environment, for
example, to find out if you have lots of filespace free."

Could you please elaborate?

The ability to declare FOREIGN KEYS and TRANSACTIONS are not what I had in
mind when I used the phrase "scripting language". I am thinking of
full-blown procedural logic integrated with the RDBMS. The foremost example
that comes to mind is VBA in MS-Access, though many other examples are
available, e.g. Revelation, Borland Paradox, or even java in Oracle.

Now, comparing SQLite with those other databases is like comparing a giraffe
with an alligator and then complaining that the alligator cannot reach the
tall acacia fruits or that the giraffe cannot take down a water buffalo
because he cannot fully submerge and his teeth are too dull.  I am very
aware of SQLite's many strengths and how it is different from those other
databases.  But I'd still like SQLite to have an integrated interpreted
scripting language if that were possible, because I know from experience
that if the interpreted scripting language integration is well handled you
can end up with the best of both worlds: SQL set logic and procedural logic
with arrays/collections.  That is of course one of the main reasons for the
loadable extensions module in SQLite. An important difference is that the
scripting language can be "sandboxed" and prevented from referencing the
file system.  If support for an interprested scripting language were
available,  Google's and Adobe's and other similar implementations of SQLite
could have access to the sort of power that loadable extensions offer.

Regards
Tim Romano


On Sat, May 1, 2010 at 10:04 AM, Simon Slavin  wrote:

>
> On 1 May 2010, at 2:25pm, Tim Romano wrote:
>
> > In part, this is a very broad question but I hope it's not unacceptable.
> > From a birdseye perspective, what is involved in integrating an
> interpreted
> > scripting language with a database engine?  I am aware that SQLite
> supports
> > loadable extensions, but would the SQLite architecture also permit the
> > integration of an interpreted scripting language?   An integrated
> scripting
> > language makes an already powerful database engine orders of magnitude
> more
> > useful especially when one is solving ad hoc data problems requiring very
> > rapid turnaround.  SQlite with, say, an ActionScript interpreter (ala
> > MS-Access->VBA) would be an amazingly powerful desktop tool. Do you know
> of
> > any project pursuing such an integration?
>
> SQLite already contains three scripting elements.  The first is TRIGGERs,
> where you can supply a sequence of operations to be completed when certain
> things happen.  The second is FOREIGN KEY support, which is more restricted
> in terms of what can be done, but easier to understand.  And the third is
> the COMMIT/ROLLBACK system which can also be considered a method of
> pre-programming certain events.
>
> One problem with implementing scripting within a database language is that
> it turns into just another layer of complication.  If you migrate some of
> your application logic into the scripting language it gives you another
> layer of stuff to debug.  I already don't know how a value arrived in one of
> my fields: is it a default value for that column ?  Or did it arrive there
> via a TRIGGER ?  Or was it explicitly put in there by my application ?  If
> you add another layer to that you're complicating an already complicated set
> of possibilities.
>
> Another problem with it is that sooner or later you need your inner
> language (your SQL engine) to have access to your outer environment, for
> example, to find out if you have lots of filespace free.  At that point you
> have to start specifying things about your OS (for example, that it actually
> does have a file system and you're not just using memory).  Which means
> you're going to restrict yourself to only some implementations of SQLite.
>  Which then means SQLite either has to have forks or waste code in
> environments it's not suitable for.  That way madness lies.
>
> So the conventional way to handle it is to put your SQL engine near the
> bottom of the heap, just above the file system.  If you want it to act as if
> it has a scripting language, implement your own, and call SQLite only by
> your own calls rather than directly.
>
> Simon.
> ___
> 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] scripting language interpreter

2010-05-01 Thread Simon Slavin 

On 1 May 2010, at 2:25pm, Tim Romano wrote:

> In part, this is a very broad question but I hope it's not unacceptable.
> From a birdseye perspective, what is involved in integrating an interpreted
> scripting language with a database engine?  I am aware that SQLite supports
> loadable extensions, but would the SQLite architecture also permit the
> integration of an interpreted scripting language?   An integrated scripting
> language makes an already powerful database engine orders of magnitude more
> useful especially when one is solving ad hoc data problems requiring very
> rapid turnaround.  SQlite with, say, an ActionScript interpreter (ala
> MS-Access->VBA) would be an amazingly powerful desktop tool. Do you know of
> any project pursuing such an integration?

SQLite already contains three scripting elements.  The first is TRIGGERs, where 
you can supply a sequence of operations to be completed when certain things 
happen.  The second is FOREIGN KEY support, which is more restricted in terms 
of what can be done, but easier to understand.  And the third is the 
COMMIT/ROLLBACK system which can also be considered a method of pre-programming 
certain events.

One problem with implementing scripting within a database language is that it 
turns into just another layer of complication.  If you migrate some of your 
application logic into the scripting language it gives you another layer of 
stuff to debug.  I already don't know how a value arrived in one of my fields: 
is it a default value for that column ?  Or did it arrive there via a TRIGGER ? 
 Or was it explicitly put in there by my application ?  If you add another 
layer to that you're complicating an already complicated set of possibilities.

Another problem with it is that sooner or later you need your inner language 
(your SQL engine) to have access to your outer environment, for example, to 
find out if you have lots of filespace free.  At that point you have to start 
specifying things about your OS (for example, that it actually does have a file 
system and you're not just using memory).  Which means you're going to restrict 
yourself to only some implementations of SQLite.  Which then means SQLite 
either has to have forks or waste code in environments it's not suitable for.  
That way madness lies.

So the conventional way to handle it is to put your SQL engine near the bottom 
of the heap, just above the file system.  If you want it to act as if it has a 
scripting language, implement your own, and call SQLite only by your own calls 
rather than directly.

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

Re: [sqlite] sqlite update with joins

2010-05-01 Thread Igor Tandetnik 
wdl...@web.de wrote:
> The general problem with this issue is that it is not possible
> 
> to set an alias for the table in an update statement.
> That should be implemented because otherwise some necessary statements are 
> not possible.
> 
> e.g.
> 
> update T x
>  set x.col1 = ( select  sum(c) from T y where y.id = x.id )

update T set col1 = (select sum(c) from T y where y.id = T.id);

Try it, it works. Identifier T binds to the first mention of the table, which 
happens to be the one in the outer UPDATE statement. Other instances of T may 
be referred to with aliases.
-- 
Igor Tandetnik

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

[sqlite] scripting language interpreter

2010-05-01 Thread Tim Romano 
In part, this is a very broad question but I hope it's not unacceptable.
>From a birdseye perspective, what is involved in integrating an interpreted
scripting language with a database engine?  I am aware that SQLite supports
loadable extensions, but would the SQLite architecture also permit the
integration of an interpreted scripting language?   An integrated scripting
language makes an already powerful database engine orders of magnitude more
useful especially when one is solving ad hoc data problems requiring very
rapid turnaround.  SQlite with, say, an ActionScript interpreter (ala
MS-Access->VBA) would be an amazingly powerful desktop tool. Do you know of
any project pursuing such an integration?

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

Re: [sqlite] sqlite update with joins

2010-05-01 Thread Andreas Henningsson 
cant you do something like this?

update tTmp
set tTmp.fileName = hTmp.fileName
from t tTmp
inner join h hTmp on t.id = h.id


1 maj 2010 kl. 14.20 skrev wdl...@web.de:

> The general problem with this issue is that it is not possible
>
> to set an alias for the table in an update statement.
> That should be implemented because otherwise some necessary  
> statements are not possible.
>
> e.g.
>
> update T x
>  set x.col1 = ( select  sum(c) from T y where y.id = x.id )
>
> Table T has the alias x in the outer update an the alias y in the  
> inner select.
>
> This is not possible.
> Has someone an idea for a workaround ?
>
> Simon
>
>
> -Ursprüngliche Nachricht-
> Von: Pavel Ivanov 
> Gesendet: 30.04.2010 21:38:54
> An: General Discussion of SQLite Database 
> Betreff: Re: [sqlite] sqlite update with joins
>
> You could try
>
> UPDATE H SET filename = (select F.rowid from F where F.filename =  
> H.filename);
>
>
> Pavel
>
> On Fri, Apr 30, 2010 at 3:25 PM, David Lyon   
> wrote:
>> I know sqlite update with joins is not supported but I have heard  
>> work arounds without the need for scripting.
>>
>> eg:
>> 2 tables H and F both join on FILENAME
>> I want to update h.FILENAME so its the same as the rowid of table F
>>
>>
>> sqlite3 F  "update H h , F f set h.FILENAME=f.rowid where  
>> h.FILENAME=f.FILENAME "
>>
>> is there an easy work around before I embark on scripting this
>>
>>
>> Thanks Again
>>
>>
>>
>>
>> ___
>> 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
> ___
> GRATIS: Movie-Flat mit über 300 Top-Videos. Für WEB.DE Nutzer
> dauerhaft kostenlos! Jetzt freischalten unter http://movieflat.web.de
> ___
> 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] sqlite update with joins

2010-05-01 Thread wdlurd 
The general problem with this issue is that it is not possible 

to set an alias for the table in an update statement.
That should be implemented because otherwise some necessary statements are not 
possible.

e.g.

update T x 
  set x.col1 = ( select  sum(c) from T y where y.id = x.id )

Table T has the alias x in the outer update an the alias y in the inner select.

This is not possible.
Has someone an idea for a workaround ?

Simon


-Ursprüngliche Nachricht-
Von: Pavel Ivanov 
Gesendet: 30.04.2010 21:38:54
An: General Discussion of SQLite Database 
Betreff: Re: [sqlite] sqlite update with joins

You could try

UPDATE H SET filename = (select F.rowid from F where F.filename = H.filename);


Pavel

On Fri, Apr 30, 2010 at 3:25 PM, David Lyon  wrote:
> I know sqlite update with joins is not supported but I have heard work 
> arounds without the need for scripting.
>
> eg:
> 2 tables H and F both join on FILENAME
> I want to update h.FILENAME so its the same as the rowid of table F
>
>
> sqlite3 F  "update H h , F f set h.FILENAME=f.rowid where 
> h.FILENAME=f.FILENAME "
>
> is there an easy work around before I embark on scripting this
>
>
> Thanks Again
>
>
>
>
> ___
> 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
___
GRATIS: Movie-Flat mit über 300 Top-Videos. Für WEB.DE Nutzer
dauerhaft kostenlos! Jetzt freischalten unter http://movieflat.web.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users