Re: [sqlite] BACK API Questions

I did try using batch of transactions with synchronous=OFF PRAGMA set. The performance improved slightly. But as db file started to grow larger and larger in size, the performance degraded considerably. Is it expected? Or Is there a work around for this? Thanks Raghu -Original

Re: [sqlite] BACK API Questions

Max, Thanks.. Looks like I will have to optimize using transactions. I will try that -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Max Vlasov Sent: Tuesday, December 15, 2009 3:56 PM To: General Discussion of SQLite

Re: [sqlite] Gauging interest in patches...

Hello! On Tuesday 15 December 2009 19:05:31 D. Richard Hipp wrote: > Maintaining a fork of SQLite using Fossil is not difficult. The are some problems of usage: 1. Then I use reverse proxy-server Fossil does not check the existence of X-Forwarded-For header and does not ask login/password. Yes,

Re: [sqlite] Gauging interest in patches...

Hello! On Tuesday 15 December 2009 19:05:31 D. Richard Hipp wrote: > Maintaining a fork of SQLite using Fossil is not difficult. A sketch > of one solution can be found at http://www.sqlite.org/ > privatebranch.html and there is an updated version of that document > at

Re: [sqlite] Bug when uUsing Parameters with views

Cariotoglou Mike wrote: > Just an idea : > > Parameters bound via sql_bind... *should* have affinity, since they > are manifestly typed. Affinity is determined and necessary conversions are encoded into the virtual machine by sqlite_prepare, before any parameters are bound.

Re: [sqlite] Bug when uUsing Parameters with views

Just an idea : Parameters bound via sql_bind... *should* have affinity, since they are manifestly typed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BACK API Questions

-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: Tuesday, December 15, 2009 2:48 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] BACK API Questions On Tue, Dec 15, 2009 at 2:44 PM,

Re: [sqlite] BACK API Questions

On Tue, Dec 15, 2009 at 2:44 PM, Raghavendra Thodime wrote: > Hi Martin, >   Thanks for your reply. > >   You mean to say that I need to modify sqlite3 code to choose which pages > that need to be copied and copy them myself? You don't need to modify sqlite3 code, but you

Re: [sqlite] BACK API Questions

Hi Martin, Thanks for your reply. You mean to say that I need to modify sqlite3 code to choose which pages that need to be copied and copy them myself? Or is there some other API that I can use for this purpose? My main problem is I will have huge influx of real time data which I

Re: [sqlite] BUG: the rowid column in view is automa tically named as id

Hello! On Monday 14 December 2009 11:33:32 Jens Miltner wrote: > ... You did not understand me. The manually named columns may not be renamed automatically. But in the test the column "id" was renamed to "id:1": sqlite> create table test (id INTEGER PRIMARY KEY); sqlite> insert into test

Re: [sqlite] Gauging interest in patches...

Hello! On Tuesday 15 December 2009 18:35:18 wcl...@gfs-hofheim.de wrote: > I have made you a patch file for just the READONLY and ENFORCE table > constraints. Please send me the original patch. All features are interesting and I want to test them. > I think if I post the patch here it will get

Re: [sqlite] Gauging interest in patches...

Hello! On Tuesday 15 December 2009 19:17:31 wcl...@gfs-hofheim.de wrote: > Trying to attach a non-existent database as "readonly" will return the > error "unable to open database file". This message is not helpful becouse is used in other situations. Is it possible to change it? Best

Re: [sqlite] BACK API Questions

Hello Raghu, it seems to mee that you misunderstand the backup api. This api does not "know" or care for your schema and tables, but backups the database block for block. In this sense, the api does not know "old" contents and can not add "new" content. The backup api can not help you in your

Re: [sqlite] BACKUP API Questions

Any idea -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Raghavendra Thodime Sent: Tuesday, December 15, 2009 10:15 AM To: sqlite-users@sqlite.org Subject: [sqlite] BACK API Questions Hi, I am using in-memory db

Re: [sqlite] pragma database_list

On Tue, Dec 15, 2009 at 06:58:03PM +0100, Angelo scratched on the wall: > Hi to everybody, > > I'm using sqlite 3.6.16 both shell and C api. > I need to make automatic check (from C code) of the opened database files > (not > connections) . With sqlite3_column_database_name I get just main,

Re: [sqlite] pragma database_list

pragma database_list returns record set as any select statement. So you can capture this record set from C code as you do with any other select statement and based on that make your checking as you need. Pavel On Tue, Dec 15, 2009 at 12:58 PM, Angelo wrote: > Hi to

Re: [sqlite] Gauging interest in patches...

D. Richard Hipp wrote on 15/12/2009 17:05:31: > (1) Have your patches been fully documented and have you generated > automated test cases that provide 100% MC/DC and branch test > coverage? I'm guessing not and yet those are requirements for new > features in the core. I believe they are

Re: [sqlite] Gauging interest in patches...

Alexey Pechnikov wrote on 15/12/2009 15:15:42: >> 3. ENFORCE constraint for table columns, for example: CREATE TABLE t(i >> enforce integer, j enforce text). This optional constraint enforces type >> checking so that an entry must match the column type (i.e. integer, real, >> numeric, text,

[sqlite] BACK API Questions

Hi, I am using in-memory db where I will execute only inserts. Periodically, I am trying to backup my in-memory db to file based db. But as soon as I backup, I want to clear up the in-memory db so that I will have enough memory for subsequent inserts. So next time when I back up I want to

Re: [sqlite] Bug when uUsing Parameters with views

On Dec 15, 2009, at 10:53 AM, Cariotoglou Mike wrote: > Quoting from the documentation on Type Affinity: > By coincidence, I was working on rewriting and clarifying that documentation just last night. The latest draft can be seen at http://www.sqlite.org/draft/datatype3.html My belief

Re: [sqlite] Gauging interest in patches...

On Dec 15, 2009, at 8:26 AM, wcl...@gfs-hofheim.de wrote: > > This is a quick email just to gauge interest in a number of patches > I've > created for Sqlite over the last year or so. Your best bet would probably be to clone the SQLite Fossil repository and publish your own private branch as

[sqlite] Pragmas and compile options ...

I was just wondering whether it might be possible in some future version of SQLite to expose the pragma and compile time options settings in the form of a table: select * from sqlite_pragma ; select * from sqlite_options ; This might also allow update to set the value of a

Re: [sqlite] Bug when uUsing Parameters with views

> AH, GOT IT. values do NOT have affinity. so I would > either need to cast the expression OR the value. > > is this correct ? Yes, that's correct, you have to cast either left or right part to assign affinity to it and to force conversion of another part. Pavel On Tue, Dec 15, 2009 at 10:53

Re: [sqlite] EPC Based Search

> Why not just make epc column "unique on conflict ignore"? > i.e. > CREATE TABLE readmode( epc text unique on conflict ignore, col2 text... ); > >> one EPC ( say:'e2003411b802010994095761' i.e. 24char ) out of 5000 records. >> and select query take 2-3 sec. to give result output. >> >> By any

Re: [sqlite] Bug when uUsing Parameters with views

Quoting from the documentation on Type Affinity: " SQLite may attempt to convert values between the numeric storage classes (INTEGER and REAL) and TEXT before performing a comparison. Whether or not any conversions are attempted before the comparison takes place depends on the nominal affinity

[sqlite] Antwort: Re: Gauging interest in patches...

John Brooks wrote on 15/12/2009 15:13:05: > What license do you put these under? Sorry, I forgot to say in my last email: I'm happy for these to end up in the public domain like Sqlite -- I don't think realistically I could do otherwise given the blessing-style licence that the main Sqlite

Re: [sqlite] Bug when uUsing Parameters with views

Apparently result of count() and probably all other aggregate functions (as well as result of any function at all) has no affinity. So when you compare it to anything having no affinity too you have no type transformation during comparison. And thus values have to be exactly the same including

Re: [sqlite] Gauging interest in patches...

John Brooks wrote on 15/12/2009 15:13:05: > I love the idea of READONLY and ENFORCE. I would certainly make use of > those in amalgamation form. What license do you put these under? > > You should really publish these all somewhere, there are some great > features. Good work. Thank you. I have

Re: [sqlite] Bug when uUsing Parameters with views

2009/12/15 D. Richard Hipp : > >> > > Because string '2' is not the same thing as integer 2. > > sqlite3> select 2='2'; > 0 > sqlite3> > Why sqlite> select cast( 2 as integer ) = '2'; 1 > > D. Richard Hipp > d...@hwaci.com > Simon ___

Re: [sqlite] Bug when uUsing Parameters with views

> > essence of problem: > > why is there a difference in output between the following selects? > > sqlite> select * from t1 where (select count(*) from t2 where > t2.id=t1.id) = '2'; > sqlite> select * from t1 where cast((select count(*) from t2 where > t2.id=t1.id) > as integer)= '2'; > 2 >

Re: [sqlite] Bug when uUsing Parameters with views

2009/12/15 Cariotoglou Mike : > Simon, thanks for the answer. your code helped track down the issue, > which I *still* believe to be a bug: > . . . > > select * from > (select *," > cast((select count(*) from ITEM_ARTIST where > ARTIST_id=artists.artist_id) as int) CNT > from

Re: [sqlite] SQL question

On Tue, Dec 15, 2009 at 6:16 AM, Florian Schricker wrote: > Hello Simon, > > >> From: Simon Slavin >> On 14 Dec 2009, at 8:52am, Florian Schricker wrote: >> > Primary keys are Oper, Product, Category, Name and CreateTS   There is only one

Re: [sqlite] SQLite version 3.6.21

Original Message Subject: Re: [sqlite] SQLite version 3.6.21 From: Andreas Schwab To: sqlite-users@sqlite.org Date: 2009-12-08 02:41 > $ ./sqlite3 :memory: 'create table test(integer)' > Segmentation fault We have the same problem and with this posted

Re: [sqlite] Gauging interest in patches...

Hello! On Tuesday 15 December 2009 16:26:00 wcl...@gfs-hofheim.de wrote: > 3. ENFORCE constraint for table columns, for example: CREATE TABLE t(i > enforce integer, j enforce text). This optional constraint enforces type > checking so that an entry must match the column type (i.e. integer,

Re: [sqlite] SQL question

Florian Schricker wrote: > Hello Simon, > SNIP > Please excuse me for being so blunt: > You have no idea what I'm supposed to do here nor do you have any idea > for whatever historic reason the database or table is designed "as is" > here nor (and finally) do you have no idea about my education

Re: [sqlite] Bug when uUsing Parameters with views

Simon, thanks for the answer. your code helped track down the issue, which I *still* believe to be a bug: The issue is not with parameters, after all, but with comparison of values, and something "magic" about count(), I suspect. It has to do with the fact that the layer I am using (PHP PDO)

Re: [sqlite] SQL question

On 12/15/09 13:16 , "Florian Schricker" wrote: > Please excuse me for being so > blunt: You have no idea what I'm supposed to do here nor do you have any > idea for whatever historic reason the database or table is designed "as > is" here nor (and finally) do you have no

[sqlite] Gauging interest in patches...

Hi all, This is a quick email just to gauge interest in a number of patches I've created for Sqlite over the last year or so. What I want to know is whether anyone would like me to post one or more of these patches here? I have thought about posting to http://www.sqlite.org/contrib but since

Re: [sqlite] SQL question

Hello Simon, > From: Simon Slavin > On 14 Dec 2009, at 8:52am, Florian Schricker wrote: > Primary keys are Oper, Product, Category, Name and CreateTS >>>   There is only one primary key per table. >> >> So to say the primary key is "Oper, Product, Category, Name,

[sqlite] BACKUP API Questions.

Hi, I am using in-memory db where I will execute only inserts. Periodically, I am trying to backup my in-memory db to file based db. But as soon as I backup, I want to clear up the in-memory db so that I will have enough memory for subsequent inserts. So next time when I back up I want to

Re: [sqlite] Bug when uUsing Parameters with views

2009/12/15 Cariotoglou Mike : > I checked in the bug database, and this does not seem to have been > reported, and IMHO it is definitely a bug. > workarounds exist,as pointed out by me and others. still, I would like > to hear from the core team whether this is > recognized as a

Re: [sqlite] Passing a $dbHandle to a new page

Hello F Aha! now I am starting to understand the utility of that data.sqlite file! F Thanks again, Simon, and I will look for a PHP forum. Do you know a good F one? I've find that the PHP manual: http://www.php.net/manual/en/ answers almost all my questions. Swithun.

Re: [sqlite] Passing a $dbHandle to a new page

Aha! now I am starting to understand the utility of that data.sqlite file! Thanks again, Simon, and I will look for a PHP forum. Do you know a good one? Frank L. Simon Slavin-3 wrote: > > > On 15 Dec 2009, at 8:24am, FrankLane wrote: > >> In page 1 I have defined: >> >> $dbHandle = new

[sqlite] Bug when uUsing Parameters with views

I checked in the bug database, and this does not seem to have been reported, and IMHO it is definitely a bug. workarounds exist,as pointed out by me and others. still, I would like to hear from the core team whether this is recognized as a bug, and will be dealt with at some point in time. btw, I

Re: [sqlite] EPC Based Search

2009/12/15 Ram Mandavkar : > > Hello All, > >      I am New to SQL-Lite DB, > I had one ASSET table having EPC as varchar(50) field and 5000 records in > it. > > If i want to search > ( >   CREATE TRIGGER "main"."TR_MULTIREAD_BEFORE" BEFORE INSERT ON READMODE >       BEGIN

Re: [sqlite] Passing a $dbHandle to a new page

On 15 Dec 2009, at 8:24am, FrankLane wrote: > In page 1 I have defined: > > $dbHandle = new PDO('sqlite:data.sqlite'); > > $var='hello'; > > I can pass a $var variable to page 2 using using the URL as in > > echo " page2.php?var=$var GO TO PAGE 2 "; Instead do this: echo "

[sqlite] EPC Based Search

Hello All, I am New to SQL-Lite DB, I had one ASSET table having EPC as varchar(50) field and 5000 records in it. If i want to search ( CREATE TRIGGER "main"."TR_MULTIREAD_BEFORE" BEFORE INSERT ON READMODE BEGIN SELECT CASE WHEN M.a = NEW.EPC THEN

[sqlite] Passing a $dbHandle to a new page

Hello - I have two pages. In page 1 I have defined: $dbHandle = new PDO('sqlite:data.sqlite'); $var='hello'; I can pass a $var variable to page 2 using using the URL as in echo " page2.php?var=$var GO TO PAGE 2 "; and I want to execute an sqlite/PHP command in page 2 like: