[sqlite] upstreaming Chromium patches for file handle passing support

[Paweł Hajdan , Jr .]

Please take a look at chromium_sqlite3 functions in
http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/src/src/os_unix.c?view=markup

They are needed because in Chrome the browser process will pass a file
descriptor to the child renderer process instead of a file path. Here's the
code that handles it on the renderer side:
http://trac.webkit.org/browser/trunk/WebCore/platform/sql/chromium/SQLiteFileSystemChromiumPosix.cpp

Currently those chromium_sqlite3 functions live in patched os_unix.c file
because they access slite internals that are only visible in os_unix.c.

Could you suggest a way to do the same thing in a way that wouldn't require
custom patches on the Chromium project side? If we can get a similar
interface exposed in sqlite that'd be great. Another possible solution would
be to allow those chromium_sqlite3 functions to be implemented outside of
os_unix.c file (that will probably require exposing more internals from that
file).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get the original rows after 'group by'?

[Peng Yu]

Hi,

Without using sqlite, I can sort the table test ('sort' is from
coreutils, sort on the 1st column then the 2nd column), then I use awk
to parse the sorted file to get what I need. Should this approach be
faster than the sqlite query (in terms of runtime, not in term of
programming time)?

On Thu, Aug 26, 2010 at 3:59 PM, David Bicking  wrote:
> you can use:
>
> select col1, col2 from test where col1 in (select col1 from test group by 
> col1 having count(*)<=2);
>
> David
>
> --- On Thu, 8/26/10, Peng Yu  wrote:
>
>> From: Peng Yu 
>> Subject: [sqlite] How to get the original rows after 'group by'?
>> To: "General Discussion of SQLite Database" 
>> Date: Thursday, August 26, 2010, 4:53 PM
>> Hi,
>>
>> In the following sql query, I want to get all the lines
>> which
>> satisfies that the first column appears less or equal to 2
>> times. Is
>> there a way to do it other than using inner join of the
>> original table
>> and the query in main.sql?
>>
>> $ cat main.sql
>> #!/usr/bin/env bash
>>
>> #sqlite3 foods.db <>
>> rm -rf main.db
>> sqlite3 main.db <>
>> create table test (col1 text, col2 text);
>> insert into test values('a1', 'b1');
>> insert into test values('a1', 'b2');
>> insert into test values('a1', 'b3');
>> insert into test values('a1', 'b4');
>> insert into test values('a2', 'b5');
>> insert into test values('a2', 'b6');
>> insert into test values('a3', 'b8');
>>
>> .mode column
>> .headers on
>> .echo on
>> select col1, col2 from test group by col1 having count(*)
>> <= 2;
>>
>> EOF
>>
>> $ ./main.sql
>> select col1, col2 from test group by col1 having count(*)
>> <= 2;
>> col1        col2
>> --  --
>> a2          b6
>> a3          b8
>>
>> --
>> Regards,
>> Peng
>> ___
>> 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
>



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

[sqlite] Error message in RAISE(ABORT,...)

[David Bicking]

Can the Error message returned by a trigger be an expression? My testing seems 
to indicate that you can only put a string literal in the Raise function.

What I am trying to do is return the data that the trigger was looking at when 
it raised the error. 

My application can insert one to four records at a time. I do an "INSERT ... 
SELECT ... WHERE BATCH=?" The select is a complicated join of 4 tables, so 
there is no rowid that I could select against.

I would like to know which of the inserted records caused the error. What I 
would ideally like to return is the concatenation of the text in two column 
along with the problem encountered. 

Is there another way I can I determine the bad record? (Other than selecting 
each source record individually then binding and inserting them individually.)

Thanks,
David

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

Re: [sqlite] How to get the original rows after 'group by'?

[David Bicking]

you can use:

select col1, col2 from test where col1 in (select col1 from test group by col1 
having count(*)<=2);

David

--- On Thu, 8/26/10, Peng Yu  wrote:

> From: Peng Yu 
> Subject: [sqlite] How to get the original rows after 'group by'?
> To: "General Discussion of SQLite Database" 
> Date: Thursday, August 26, 2010, 4:53 PM
> Hi,
> 
> In the following sql query, I want to get all the lines
> which
> satisfies that the first column appears less or equal to 2
> times. Is
> there a way to do it other than using inner join of the
> original table
> and the query in main.sql?
> 
> $ cat main.sql
> #!/usr/bin/env bash
> 
> #sqlite3 foods.db < 
> rm -rf main.db
> sqlite3 main.db < 
> create table test (col1 text, col2 text);
> insert into test values('a1', 'b1');
> insert into test values('a1', 'b2');
> insert into test values('a1', 'b3');
> insert into test values('a1', 'b4');
> insert into test values('a2', 'b5');
> insert into test values('a2', 'b6');
> insert into test values('a3', 'b8');
> 
> .mode column
> .headers on
> .echo on
> select col1, col2 from test group by col1 having count(*)
> <= 2;
> 
> EOF
> 
> $ ./main.sql
> select col1, col2 from test group by col1 having count(*)
> <= 2;
> col1        col2
> --  --
> a2          b6
> a3          b8
> 
> -- 
> Regards,
> Peng
> ___
> 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] How to get the original rows after 'group by'?

[Peng Yu]

Hi,

In the following sql query, I want to get all the lines which
satisfies that the first column appears less or equal to 2 times. Is
there a way to do it other than using inner join of the original table
and the query in main.sql?

$ cat main.sql
#!/usr/bin/env bash

#sqlite3 foods.db 

Re: [sqlite] Parameters in views preparation

[Simon Slavin]

On 26 Aug 2010, at 8:29pm, Alan Chandler wrote:

> On 26/08/10 17:38, Simon Slavin wrote:
> 
>> So someone can check it out.  Try it with a VIEW that definitely doesn't 
>> exist, or use
>> 
>> CREATE VIEW IF NOT EXISTS ...
>> 
> 
> As far as I can work it out, the statement then prepares OK - but seems 
> then to execute as a no op.

Which makes some kind of sense.  So the thing to do seems to be to use

DROP VIEW IF EXISTS ...

and then either

CREATE VIEW ...orCREATE VIEW IF NOT EXISTS ...

Both of those should always both prepare and execute.

Like you, I am not enough of an expert on the intentions behind SQLite design 
to tell whether it should be possible to prepare a statement which will 
currently fail.

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

Re: [sqlite] Parameters in views preparation

[Alan Chandler]

On 26/08/10 20:29, Alan Chandler wrote:
> On 26/08/10 17:38, Simon Slavin wrote:
>
>> So someone can check it out.  Try it with a VIEW that definitely doesn't 
>> exist, or use
>>
>> CREATE VIEW IF NOT EXISTS ...
>>
>
> As far as I can work it out, the statement then prepares OK - but seems
> then to execute as a no op.  Since having completed that script and then
> checking the schema with the command line sqlite3 utility, the view no
> longer exists.
>
> This seems completely wrong.  Surely it should do this check at
> *execute* time not at *prepare* time.
>


Life is just too short.  The benefit of a prepared statement is lost 
when you can't have parameters and you are only using it once anyway, so 
whilst there is a slight lengthening of the time when the database is 
Locked, its just easier to move the whole thing into a PDO::exec 
function (which in SQLITE terms I think prepares and then executes 
immediately).

I did this, and my code now works fine.

There might be a discussion about the difference between semantic and 
syntactic validation of prepared statements, but I am not expert enough 
in SQL to know what the perceived wisdom in this area is.

-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Parameters in views preparation

[Alan Chandler]

On 26/08/10 17:38, Simon Slavin wrote:

> So someone can check it out.  Try it with a VIEW that definitely doesn't 
> exist, or use
>
> CREATE VIEW IF NOT EXISTS ...
>

As far as I can work it out, the statement then prepares OK - but seems 
then to execute as a no op.  Since having completed that script and then 
checking the schema with the command line sqlite3 utility, the view no 
longer exists.

This seems completely wrong.  Surely it should do this check at 
*execute* time not at *prepare* time.

-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Fw: Re: SQlite JDBC driver - need help

[Israel Lins Albuquerque]

I want to finish that confusion! 

1st point: 
JSP means Java Server Pages and runs on an Apache TomCat server. 
The java code runs ON server and NOT in client! 

2nd: 
Then the db files don't need be directed accessed by the web client, this is 
unsafe. 

Exemple: 
DriverManager.getConnection("jdbc:sqlite:[DB_PATH]"); 

Where DB_PATH is the path ON the server like "jdbc:sqlite:./test.db", and 
the data will be created if doesn't exists in the same path of current jsp 
page, 
if you don't want this you can put somethink like 
"jdbc:sqlite:C:/www/[my_site]/data/test.db", 
you need ensure that directory exists the sqlite only create the database! 


- "Pavel Ivanov"  escreveu: 
> Dev, 
> 
> Do you understand that SQLite is not a server application and it has 
> no access control facilities? It is just some library code that helps 
> accessing files that have some well-defined internal format. So your 
> application will be able to access any database accessible by the user 
> running your executable. In case of JSP page it will be whatever user 
> account your IIS server is running under. And database path like 
> '//ipaddress/spiceworks/db/spiceworks_prod.db' most probably means 
> that you try to access a file located in the network at a shared 
> folder spiceworks of the server ipaddress. So is that path accessible 
> to the user running IIS? I'm not sure though what "out of memory" 
> errors mean. Probably just badly written jdbc driver. 
> 
> 
> Pavel 
> 
> On Wed, Aug 25, 2010 at 3:14 PM, Dev Movva  wrote: 
> > Hello, 
> > 
> > Can some one please help me how do I connect to SQLite from a JSP page with 
> > windows authentication mode? 
> > 
> > Thank you, 
> > 
> > Dev 
> > 
> > --- On Wed, 8/25/10, Richard Hipp  wrote: 
> > 
> > 
> > From: Richard Hipp  
> > Subject: Re: SQlite JDBC driver - need help 
> > To: "Dev Movva"  
> > Date: Wednesday, August 25, 2010, 3:02 PM 
> > 
> > 
> > jdbc is maintained separately by Chris Werner. I don't know anything about 
> > it. If you post on sqlite-users@sqlite.org you might be able to get 
> > somebody there to help you. 
> > 
> > 
> > On Wed, Aug 25, 2010 at 2:47 PM, Dev Movva  wrote: 
> > 
> > 
> > 
> > 
> > 
> > 
> > Dear Richard, 
> > 
> > I am trying to connect to SQLite from my JSP page and not sure where I am 
> > not doing it right but no luck with it. I posted it on the google forums at 
> > http://groups.google.com/group/sqlitejdbc/browse_thread/thread/841538d7b3679678#
> >  
> > 
> > I am wondering if you can shed some light on my issue and help me wiht 
> > this. 
> > 
> > All I need is how do I connect to SQLite from a JSP page using windows 
> > authentication (like integratedSecurity=true while connecting to a SQL 
> > server). All our users should be able to see view the data with their 
> > windows authentication. Any help is greately appreciated. 
> > 
> > Thank you in advance, 
> > 
> > Dev 
> > 
> > 
> > 
> > -- 
> > D. Richard Hipp 
> > d...@sqlite.org 
> > 
> > 
> > 
> > 
> > ___ 
> > 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 
> 

-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
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] SQL script help.

[Israel Lins Albuquerque]

Or you can do: 
SELECT * FROM myTable WHERE _rowid_ IN (100, 101, 102) 

depending what you want 


- "Simon Slavin"  escreveu: 
> 
> On 26 Aug 2010, at 3:39pm, Kirk Clemons wrote: 
> 
> > SELECT * FROM myTable WHERE _rowid_ = 100; SELECT * FROM myTable WHERE 
> > _rowid_ = 101; SELECT * FROM myTable WHERE _rowid_ = 102; 
> 
> SELECT * FROM myTable WHERE _rowid_ BETWEEN 100 AND 102 
> 
> or 
> 
> SELECT * FROM myTable WHERE _rowid_ >= 100 AND _rowid_ <= 102 
> 
> will give identical results. Technically speaking you might want to add 
> 'ORDER BY _rowid_' to the end of those if the order matters. 
> 
> Simon. 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 

-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
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] Parameters in views preparation

[Simon Slavin]

On 26 Aug 2010, at 4:59pm, Igor Tandetnik wrote:

> Jay A. Kreibich  wrote:
>> On Thu, Aug 26, 2010 at 04:32:11PM +0100, Simon Slavin scratched on the wall:
>>> 
>>> On 26 Aug 2010, at 3:36pm, Alan Chandler wrote:
>>> 
 On 26/08/10 13:38, Simon Slavin wrote:
>>> 
>>> So you are trying to create a VIEW which does already exist.  In that
>>> case, there's no mystery about why you're getting an error message.
>> 
>> No, he's trying the *PREPARE* a CREATE VIEW *statement* for a view
>> that already exists.  Prepare != execute.
> 
> It's quite reasonable that the schema is checked at prepare time. For 
> example, you would expect an error on preparing, rather than executing, a 
> SELECT statement that mentions a non-existent table.

So someone can check it out.  Try it with a VIEW that definitely doesn't exist, 
or use 

CREATE VIEW IF NOT EXISTS ...

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

Re: [sqlite] Parameters in views preparation

[Jay A. Kreibich]

On Thu, Aug 26, 2010 at 11:59:03AM -0400, Igor Tandetnik scratched on the wall:
> Jay A. Kreibich  wrote:

> >> So you are trying to create a VIEW which does already exist.  In that
> >> case, there's no mystery about why you're getting an error message.
> > 
> >  No, he's trying the *PREPARE* a CREATE VIEW *statement* for a view
> >  that already exists.  Prepare != execute.
> 
> It's quite reasonable that the schema is checked at prepare time.
> For example, you would expect an error on preparing, rather than
> executing, a SELECT statement that mentions a non-existent table.

  I suppose it is reasonable, but I can see both sides.  Doing it at
  prepare makes a lot of code simpler, but it also makes some
  techniques (like pre-preparing big sets of statements) more
  difficult.

  Given that SQLite DDL commands are transactional (a somewhat unique
  feature), there is also a fixed, "known state" for executing.  Part
  of me thinks it odd to couple preparing with executing, since that's
  different from how the data operations work.

  On the other hand, most simple things would be much more complex
  if errors were only returned on execution.

  It's too bad these can't be some kind of warning, rather than an
  all-out error.

   -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] Parameters in views preparation

[Igor Tandetnik]

Jay A. Kreibich  wrote:
> On Thu, Aug 26, 2010 at 04:32:11PM +0100, Simon Slavin scratched on the wall:
>> 
>> On 26 Aug 2010, at 3:36pm, Alan Chandler wrote:
>> 
>>> On 26/08/10 13:38, Simon Slavin wrote:
 
 On 26 Aug 2010, at 12:12pm, Alan Chandler wrote:
 
> This time it reported that the view it would have created failed because
> the table (view) already existed.
 
 I'm sorry to ask this, but can you check for us whether a VIEW by that 
 name really does exist ?  Don't forget, VIEWs get saved
 in the file, they're not part of the attachment. 
>>> 
>>> Yes it does
>> 
>> So you are trying to create a VIEW which does already exist.  In that
>> case, there's no mystery about why you're getting an error message.
> 
>  No, he's trying the *PREPARE* a CREATE VIEW *statement* for a view
>  that already exists.  Prepare != execute.

It's quite reasonable that the schema is checked at prepare time. For example, 
you would expect an error on preparing, rather than executing, a SELECT 
statement that mentions a non-existent table.
-- 
Igor Tandetnik


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

Re: [sqlite] SQL script help.

[Simon Slavin]

On 26 Aug 2010, at 3:39pm, Kirk Clemons wrote:

> SELECT * FROM myTable WHERE _rowid_ = 100; SELECT * FROM myTable WHERE 
> _rowid_ = 101; SELECT * FROM myTable WHERE _rowid_ = 102;

SELECT * FROM myTable WHERE _rowid_ BETWEEN 100 AND 102

or

SELECT * FROM myTable WHERE _rowid_ >= 100 AND _rowid_ <= 102

will give identical results.  Technically speaking you might want to add 'ORDER 
BY _rowid_' to the end of those if the order matters.

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

[sqlite] SQL script help.

[Kirk Clemons]

I have heard about SQL scripts that can automate some processes. Is it possible 
to write a script that performs the following on an entire database?

SELECT * FROM myTable WHERE _rowid_ = 100; SELECT * FROM myTable WHERE _rowid_ 
= 101; SELECT * FROM myTable WHERE _rowid_ = 102;

Regards,
Kirk Clemons
Technical Support Analyst
Chief Architect(r)
6500 N. Mineral Dr.
Coeur d'Alene, Idaho 83815
Phone: (800)482-4433
   (208)664-4204

Professional Software
www.chiefarchitect.com

Consumer Software
www.HomeDesignerSoftware.com

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

Re: [sqlite] Parameters in views preparation

[Jay A. Kreibich]

On Thu, Aug 26, 2010 at 04:32:11PM +0100, Simon Slavin scratched on the wall:
> 
> On 26 Aug 2010, at 3:36pm, Alan Chandler wrote:
> 
> > On 26/08/10 13:38, Simon Slavin wrote:
> >> 
> >> On 26 Aug 2010, at 12:12pm, Alan Chandler wrote:
> >> 
> >>> This time it reported that the view it would have created failed because
> >>> the table (view) already existed.
> >> 
> >> I'm sorry to ask this, but can you check for us whether a VIEW by that 
> >> name really does exist ?  Don't forget, VIEWs get saved in the file, 
> >> they're not part of the attachment.
> > 
> > Yes it does
> 
> So you are trying to create a VIEW which does already exist.  In that
> case, there's no mystery about why you're getting an error message.

  No, he's trying the *PREPARE* a CREATE VIEW *statement* for a view
  that already exists.  Prepare != execute.

   -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] Parameters in views preparation

[Pavel Ivanov]

>>> I'm sorry to ask this, but can you check for us whether a VIEW by that name 
>>> really does exist ?  Don't forget, VIEWs get saved in the file, they're not 
>>> part of the attachment.
>>
>> Yes it does
>
> So you are trying to create a VIEW which does already exist.  In that case, 
> there's no mystery about why you're getting an error message.

Just to let conversation continue I'll quote Alan's earlier message in
this thread:

--
I replaced all the parameter placeholders with a quoted version of the
parameter and undertook the prepare statement again.

This time it reported that the view it would have created failed because
the table (view) already existed.
---

Note that he gets error when he prepares statement, not when executes them.


Pavel

On Thu, Aug 26, 2010 at 11:32 AM, Simon Slavin  wrote:
>
> On 26 Aug 2010, at 3:36pm, Alan Chandler wrote:
>
>> On 26/08/10 13:38, Simon Slavin wrote:
>>>
>>> On 26 Aug 2010, at 12:12pm, Alan Chandler wrote:
>>>
 This time it reported that the view it would have created failed because
 the table (view) already existed.
>>>
>>> I'm sorry to ask this, but can you check for us whether a VIEW by that name 
>>> really does exist ?  Don't forget, VIEWs get saved in the file, they're not 
>>> part of the attachment.
>>
>> Yes it does
>
> So you are trying to create a VIEW which does already exist.  In that case, 
> there's no mystery about why you're getting an error message.
>
> 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] Parameters in views preparation

[Simon Slavin]

On 26 Aug 2010, at 3:36pm, Alan Chandler wrote:

> On 26/08/10 13:38, Simon Slavin wrote:
>> 
>> On 26 Aug 2010, at 12:12pm, Alan Chandler wrote:
>> 
>>> This time it reported that the view it would have created failed because
>>> the table (view) already existed.
>> 
>> I'm sorry to ask this, but can you check for us whether a VIEW by that name 
>> really does exist ?  Don't forget, VIEWs get saved in the file, they're not 
>> part of the attachment.
> 
> Yes it does

So you are trying to create a VIEW which does already exist.  In that case, 
there's no mystery about why you're getting an error message.

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

Re: [sqlite] Parameters in views preparation

[Alan Chandler]

On 26/08/10 13:38, Simon Slavin wrote:
>
> On 26 Aug 2010, at 12:12pm, Alan Chandler wrote:
>
>> This time it reported that the view it would have created failed because
>> the table (view) already existed.
>
> I'm sorry to ask this, but can you check for us whether a VIEW by that name 
> really does exist ?  Don't forget, VIEWs get saved in the file, they're not 
> part of the attachment.

Yes it does - this VIEW outputs the transactions in the default currency 
(rather than the currency of the transaction) and the whole objective of 
my this particular php script is to update this view when the default 
currency changes.


a...@kanga:~/dev/money/db[master]$ sqlite3 money.db
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema dfxaction
CREATE VIEW dfxaction AS
 SELECT t.id,t.date,t.version, src, srccode, dst, 
dstcode,t.description, rno, repeat,
 CASE
 WHEN t.currency = 'GBP' THEN t.amount
 WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN 
t.srcamount
 WHEN t.dstamount IS NOT NULL AND da .currency = 'GBP' THEN 
t.dstamount
 ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS 
INTEGER)
 END AS dfamount
 FROM
 xaction AS t
 LEFT JOIN account AS sa ON t.src = sa.name
 LEFT JOIN account AS da ON t.dst = da.name
 LEFT JOIN currency ON
 t.currency != 'GBP' AND
 (t.srcamount IS NULL OR sa.currency != 'GBP') AND
 (t.dstamount IS NULL OR da.currency != 'GBP') AND
 t.currency = currency.name;
sqlite>


-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Parameters in views preparation

[Alan Chandler]

On 26/08/10 12:20, Pavel Ivanov wrote:

> Yes, "validation" happens only at the time of execution. So you are
> apparently doing something wrong and you better show your code.
>

easiest is to provide links to a copy.  I've added a .txt extension to 
all the files to stop them being executed by the web server

The key php file I am talking about is this one

http://www.chandlerfamily.org.uk/sites/default/files/files/snippets/updatedefcur.php.txt

It is trying to update the view (dfxaction) which provides transaction 
amounts in the default currency because the default currency is changing..


You will see it requires db.inc which is here

http://www.chandlerfamily.org.uk/sites/default/files/files/snippets/db.inc.txt

This initializes the global variable $db by opening the sqlite database.

The whole database schema is loaded on initial initialization by reading 
this file

The dfxaction view is created almost at the end of this file.

http://www.chandlerfamily.org.uk/sites/default/files/files/snippets/database.sql.txt

(someone asked further down the thread whether the view really exists. 
Yes it does and its not temporary).



-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Parameters in views preparation

[Drake Wilson]

Quoth Simon Slavin , on 2010-08-26 13:38:36 +0100:
> I'm sorry to ask this, but can you check for us whether a VIEW by
> that name really does exist ?  Don't forget, VIEWs get saved in the
> file, they're not part of the attachment.

And to add to that: if you want them to merely be attached to the
database connection rather than part of the permanent schema, you can
use CREATE TEMPORARY VIEW instead.

> Simon.

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

[sqlite] ANN: SQLite PHP Generator 10.8 released

[SQL Maestro Group]

Hi!

SQL Maestro Group announces the release of SQLite PHP Generator 10.8, a GUI 
frontend that allows you to generate high-quality PHP scripts for the 
selected SQLite tables, views and queries for the further working with these 
objects through the web.
http://www.sqlmaestro.com/products/sqlite/phpgenerator/

Online demo:
http://demo.sqlmaestro.com/

SQLite PHP Generator comes in both Freeware and Professional editions. The 
feature matrix can be found at
http://www.sqlmaestro.com/products/sqlite/phpgenerator/feature_matrix/

Please note that before September 5 you can purchase Professional Edition of 
PHP Generator as well as all other our products and bundles with a 20% 
discount.

Top 10 new features
=

1.  Inline Insert and Inline Edit abilities.
2.  Support for RSS feeds.
3.  Environment variables (such as CURRENT_USER_ID) in events and templates.
4.  Page-level permissions.
5.  Client-side events including custom validators.
6.  Easy access to recently used projects.
7.  Changing number of records displayed at a page during app runtime.
8.  Customizable menus.
9.  Most-frequently used values for combo box editors.
10. Password encryption by the PHPass algorithm.

Full press-release (with explaining screenshots and code samples) is 
available at:
http://www.sqlmaestro.com/news/company/php_generators_updated_10_8/

Background information:
---
SQL Maestro Group offers complete database admin, development and management 
tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2, SQLite, SQL Anywhere, 
Firebird and MaxDB providing the highest performance, scalability and 
reliability to meet the requirements of today's database applications.

Sincerely yours,
The SQL Maestro Group Team
http://www.sqlmaestro.com 

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

Re: [sqlite] Parameters in views preparation

[Simon Slavin]

On 26 Aug 2010, at 12:12pm, Alan Chandler wrote:

> This time it reported that the view it would have created failed because 
> the table (view) already existed.

I'm sorry to ask this, but can you check for us whether a VIEW by that name 
really does exist ?  Don't forget, VIEWs get saved in the file, they're not 
part of the attachment.

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

Re: [sqlite] Parameters in views preparation

[Pavel Ivanov]

> In my code, I delete the view before attempting to recreate it by
> executing the prepared statement.  Isn't that the time to validate
> whether there are semantic problems with the statement?

Yes, "validation" happens only at the time of execution. So you are
apparently doing something wrong and you better show your code.

As to your initial question: the text of view is remembered by SQLite
as is for execution in future statements, so it would need to inline
your parameters anyway. And thus you can't use parameters in the view
creation.


Pavel

On Thu, Aug 26, 2010 at 7:12 AM, Alan Chandler
 wrote:
> On 26/08/10 10:22, Alan Chandler wrote:
>> I am using PHP PDO to access sqlite and have reached a peculiar error
>> situation.  Basically its saying I can't have parameters in a CREATE
>> VIEW sql statement when I am preparing it.
>
> The plot thickens
>
> I replaced all the parameter placeholders with a quoted version of the
> parameter and undertook the prepare statement again.
>
> This time it reported that the view it would have created failed because
> the table (view) already existed.
>
> Is it correct to undertake the semantic validation at prepare time?
>
> In my code, I delete the view before attempting to recreate it by
> executing the prepared statement.  Isn't that the time to validate
> whether there are semantic problems with the statement?
>
> The reason I was pre-preparing the statement was to limit the time I
> have to lock the database with a "BEGIN IMMEDIATE" (and the later
> "COMMIT").
> --
> Alan Chandler
> http://www.chandlerfamily.org.uk
>
> ___
> 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] Parameters in views preparation

[Alan Chandler]

On 26/08/10 10:22, Alan Chandler wrote:
> I am using PHP PDO to access sqlite and have reached a peculiar error
> situation.  Basically its saying I can't have parameters in a CREATE
> VIEW sql statement when I am preparing it.

The plot thickens

I replaced all the parameter placeholders with a quoted version of the 
parameter and undertook the prepare statement again.

This time it reported that the view it would have created failed because 
the table (view) already existed.

Is it correct to undertake the semantic validation at prepare time?

In my code, I delete the view before attempting to recreate it by 
executing the prepared statement.  Isn't that the time to validate 
whether there are semantic problems with the statement?

The reason I was pre-preparing the statement was to limit the time I 
have to lock the database with a "BEGIN IMMEDIATE" (and the later 
"COMMIT").
-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

[sqlite] Parameters in views preparation

[Alan Chandler]

I am using PHP PDO to access sqlite and have reached a peculiar error 
situation.  Basically its saying I can't have parameters in a CREATE 
VIEW sql statement when I am preparing it.

I can't find any reference to this restriction or the error message in 
the SQLite documentation, so I am wondering is it a SQLite restriction 
or something PDO is forcing upon me?

This is the prepare statement followed by a var dump to check the error 
code.

$vstmt = $db->prepare("
CREATE VIEW dfxaction AS
 SELECT t.id,t.date,t.version, src, srccode, dst, 
dstcode,t.description, rno, repeat,
 CASE
 WHEN t.currency = ? THEN t.amount
 WHEN t.srcamount IS NOT NULL AND sa.currency = ? THEN 
t.srcamount
 WHEN t.dstamount IS NOT NULL AND da .currency = ? THEN 
t.dstamount
 ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS 
INTEGER)
 END AS dfamount
 FROM
 xaction AS t
 LEFT JOIN account AS sa ON t.src = sa.name
 LEFT JOIN account AS da ON t.dst = da.name
 LEFT JOIN currency ON
 t.currency != ? AND
 (t.srcamount IS NULL OR sa.currency != ? ) AND
 (t.dstamount IS NULL OR da.currency != ?) AND
 t.currency = currency.name;
 ");
var_dump($db->errorInfo());

and this gives the following error info

array(3) {
   [0]=>
   string(5) "HY000"
   [1]=>
   int(1)
   [2]=>
   string(35) "parameters are not allowed in views"
}

All the parameters are to be bound to the same value, the 3 letter name 
of a default currency which will be passed in via a $_POST variable for 
this particular Ajax called routine.

I could just use PHP to merge in the variable into the string, but I 
have been using prepare and bindValue to prevent SQL injection attacks 
so I am a little nervous of breaking this rule.

Is there a particular reason for this restriction (if it is indeed an 
SQLite one).


-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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