Re: [sqlite] SQLite file security

I think the question by the original poster was about data security and not file security. He probably does not mind if the file is copied, but the data should be decipherable only by his application. -Shibu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf

[sqlite] Counter function

Is there a reason for a very useful (for analytics) function like "counter(X)" to live in test_func.c ? I stumbled on it while trying to write a counter function of my own. Please if possible make it visible. It could ease the pain of not having analytics functions in sqlite (lead, lag, median

Re: [sqlite] About "select * from table ....." with Sqlite

yoky wrote: > Hi all, > I create a table with 200 columns, then use sqlite3_prepare() and > sqlite3_step()to get a record by this: "select * from tbl where ID =.. ". > Test in my > embedded system,The total time is about 65ms,it is too long for the > performance > requirement, I found

[sqlite] About "select * from table ....." with Sqlite

Hi all, I create a table with 200 columns, then use sqlite3_prepare() and sqlite3_step()to get a record by this: "select * from tbl where ID =.. ". Test in my embedded system,The total time is about 65ms,it is too long for the performance requirement, I found sqlite3_prepare() time is about

Re: [sqlite] Destruction callback for sqlite3_aggregate_context

* Dan: > On Nov 19, 2008, at 9:59 PM, Florian Weimer wrote: > >> In order to avoid a resource leak, I think I need some sort of >> callback when the memory allocated by sqlite3_aggregate_context is >> freed by SQLite. >> >> As far as I can see, it is not guaranteed that the xFinal function >>

Re: [sqlite] Signaling errors from collation functions

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Florian Weimer wrote: > I'm working on something which is supposed to be a foolproof interface > to SQLite. During that, I discovered that SQLite lacks a way to > return errors from a user-defined collation function. Is there > something I've

Re: [sqlite] SQLite file security

On Wed, 19 Nov 2008 12:31:22 +0530, "Satish" <[EMAIL PROTECTED]> wrote in General Discussion of SQLite Database : >Hi! > > > >I am basically a windows application developer. I am developing an >application for desktop which uses a database. I choose SQLite as my

Re: [sqlite] Sqlite 3.6.4 performance improvement

On Tue, 18 Nov 2008 22:57:50 +0530, "roshan sullad" <[EMAIL PROTECTED]> wrote in General Discussion of SQLite Database : >I have totally 70 queries with me - I have to provide maximum performance >improvement of queries from Sqlite 3.6.4 than Sqlite 3.2.5 version. So what

Re: [sqlite] about encrypting

On Tue, 18 Nov 2008 11:34:15 -0200, "perperino pomulo" <[EMAIL PROTECTED]> wrote in General Discussion of SQLite Database : >Hello. >First I want to apologise because my english is very poor. > >I have an aplication that contains an SQLite 2.1 database. This application

Re: [sqlite] Another set of questions

On 11/19/08, Christophe Leske <[EMAIL PROTECTED]> wrote: > > > > Why don't you just do a > > > > DELETE FROM table; > > > > and start inserting new results? (end result is the same as dropping > > and then recreating the table, but you wouldn't know). > > > > > I am worried about creeping memory

Re: [sqlite] Another set of questions - subject line - will do ! :-)

> Any chance that you could hint at the subject matter of the questions > in the Subject line ? In this case it would appear to be Performance ... > Helps to get your questions answered ! > ;-) > Yes, will do so. My apologies. Christophe Leske www.multimedial.de - [EMAIL PROTECTED]

Re: [sqlite] Another set of questions

> Why don't you just do a > > DELETE FROM table; > > and start inserting new results? (end result is the same as dropping > and then recreating the table, but you wouldn't know). > I am worried about creeping memory useage, as this is in-memory. The timing in the app is critical, not on my dev

Re: [sqlite] Terrible performance for one of our tables

Jens Miltner wrote: > > Any ideas why there would be such a _huge_ performance hit after > deleting and re-inserting records for a while? Without deletes/inserts the reads are sequential, and the OS and/or the drive/controller are reading ahead for you, hiding much of the disk read and seek

Re: [sqlite] Sqlite3 lock issue with NFS and read-only queries

Thanks Richard, According to the source code, it's an experimental feature : /* The following is VERY experimental */ { "writable_schema", SQLITE_WriteSchema }, { "omit_readlock",SQLITE_NoReadlock}, We have tested today and it does seem to work. The

Re: [sqlite] Another set of questions - subject line

Christophe Leske <[EMAIL PROTECTED]> writes: > > > Any chance that you could hint at the subject matter of the questions in the Subject line ? In this case it would appear to be Performance ... Helps to get your questions answered ! ;-) Regards, MikeW

Re: [sqlite] Terrible performance for one of our tables

Hello! В сообщении от Wednesday 19 November 2008 15:05:01 D. Richard Hipp написал(а): > That's because "SELECT count(*) FROM t" has to read and count every   > row in the table - all 1.2 million rows.  And this involves reading   > all 1.2 million rows from disk.  If each row is 100 bytes in

Re: [sqlite] Destruction callback for sqlite3_aggregate_context

Florian Weimer <[EMAIL PROTECTED]> wrote: > As far as I can see, it is not guaranteed that the xFinal function > specified in sqlite3_create_function will be called in all cases http://sqlite.org/c3ref/create_function.html "For an aggregate SQL function created using

Re: [sqlite] Destruction callback for sqlite3_aggregate_context

On Nov 19, 2008, at 9:59 PM, Florian Weimer wrote: > In order to avoid a resource leak, I think I need some sort of > callback when the memory allocated by sqlite3_aggregate_context is > freed by SQLite. > > As far as I can see, it is not guaranteed that the xFinal function > specified in

Re: [sqlite] Two questions

Christophe Leske wrote: >>> Can I spare some bytes in my DB by defining the ID field of the >>> standard >>> table as being a foreign key of the rtree table? In other words, when >>> defining a foreign key, is the coloumn referencing the ID field of the >>> foreign table and thus NOT replicating

Re: [sqlite] Terrible performance for one of our tables

Jens Miltner wrote: > Am 19.11.2008 um 13:05 schrieb D. Richard Hipp: > >> On Nov 19, 2008, at 3:08 AM, Jens Miltner wrote: >> >>> Hi, >>> >>> we're seeing terrible performance problems when fetching data from >>> one >>> of our tables: >>> The table contains roughly 1.2 Million rows and a

Re: [sqlite] Dropping and creating indexes

On Wed, 19 Nov 2008 06:59 + (GMT Standard Time), Hugh Gibson <[EMAIL PROTECTED]> wrote: > > An index is actually indeed associated with a table, and within that > > table with one or more columns. > > > > Hence, dropping an index doesn't require a table name. > > > I can easily write SQL

Re: [sqlite] Another set of questions

On 11/19/08, Christophe Leske <[EMAIL PROTECTED]> wrote: > > > I don't know. Have you run an experiment to see for yourself? > > > > Yes, but my results are inconclusive. would you like to share your results? If you timed two tasks, comparing their timing would be inconclusive *only* if there

Re: [sqlite] Two questions

On 11/19/08, Christophe Leske <[EMAIL PROTECTED]> wrote: > > >> Can I spare some bytes in my DB by defining the ID field of the > >> standard > >> table as being a foreign key of the rtree table? In other words, when > >> defining a foreign key, is the coloumn referencing the ID field of the >

Re: [sqlite] Problem selecting the empty string in a column

> I am *sure* that I am overlooking the obvious... > > I have a need to identify blank columns in my tables, and have been unable > to find a suitable query, eg > > SELECT * from table WHERE column = ''; > > What am I doing wrong here? It depends on what "blank" means? Does it mean an empty

[sqlite] Problem selecting the empty string in a column

Hi Folks, I am *sure* that I am overlooking the obvious... I have a need to identify blank columns in my tables, and have been unable to find a suitable query, eg SELECT * from table WHERE column = ''; What am I doing wrong here? TY, Steve ___

[sqlite] Destruction callback for sqlite3_aggregate_context

In order to avoid a resource leak, I think I need some sort of callback when the memory allocated by sqlite3_aggregate_context is freed by SQLite. As far as I can see, it is not guaranteed that the xFinal function specified in sqlite3_create_function will be called in all cases, so this is not

[sqlite] Signaling errors from collation functions

I'm working on something which is supposed to be a foolproof interface to SQLite. During that, I discovered that SQLite lacks a way to return errors from a user-defined collation function. Is there something I've missed? Right now, I call sqlite3_interrupt() to signal at least something (after

Re: [sqlite] Two questions

>> Can I spare some bytes in my DB by defining the ID field of the >> standard >> table as being a foreign key of the rtree table? In other words, when >> defining a foreign key, is the coloumn referencing the ID field of the >> foreign table and thus NOT replicating them (using a smaller

Re: [sqlite] Terrible performance for one of our tables

Am 19.11.2008 um 13:05 schrieb D. Richard Hipp: > > On Nov 19, 2008, at 3:08 AM, Jens Miltner wrote: > >> Hi, >> >> we're seeing terrible performance problems when fetching data from >> one >> of our tables: >> The table contains roughly 1.2 Million rows and a plain "SELECT >> COUNT(*) FROM t"

Re: [sqlite] Terrible performance for one of our tables

>> Out of interest why must it completely READ each entire row? Is it >> because '*' has been used? > > The database reads a page at a time. A page is, by default, 1024 > bytes. A single page might contain multiple rows, or a single large > row might be spread across multiple pages. > When

Re: [sqlite] Dropping and creating indexes

> > SQLite will complain because of the duplicate index names, but in > > other database packages it will be accepted. You then have to > > specify the table name when deleting indexes. > > The only database I'm aware of that does this is SQL Server. > Don't overgeneralize. :) No worries - I

Re: [sqlite] changes in cache spill locking since 3.5.9?

Dan, Thank you for detailed explanation. I assume that once large transaction is over, the cache returns to pre-configured state? That is, pages are given back to other sessions' caches and excess memory is freed? Best regards, Igor   > -Original Message- > From: [EMAIL PROTECTED]

[sqlite] Sqlite 3.6.4 performance improvement

Hi Roger, I got your mail id from one of my friend - I am facing some issues while working with Sqlite 3.6.4, could you please clarify those things. Actually I am upgrading Sqlite for one product from Sqlite 3.2.5 to Sqlite 3.6.4 version. I have dowloaded* sqlite-amalgamation-3_6_4* from sqlite

Re: [sqlite] Terrible performance for one of our tables

On Nov 19, 2008, at 7:57 AM, Hardy, Andrew wrote: > Out of interest why must it completely READ each entire row? Is it > because '*' has been used? The database reads a page at a time. A page is, by default, 1024 bytes. A single page might contain multiple rows, or a single large row

Re: [sqlite] Terrible performance for one of our tables

Out of interest why must it completely READ each entire row? Is it because '*' has been used? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: 19 November 2008 12:05 To: General Discussion of SQLite Database Subject: Re: [sqlite]

Re: [sqlite] Dropping and creating indexes

> SQLite will complain because of the duplicate index names, but in other > database packages it will be accepted. You then have to specify the table > name when deleting indexes. The only database I'm aware of that does this is SQL Server. Don't overgeneralize. :) -T

Re: [sqlite] Sqlite3 lock issue with NFS and read-only queries

> PRAGMA omit_readlock=ON; > Hm, should be documented, no? I could use that as well... Just wondering if this has any speed advantages? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32

Re: [sqlite] Another set of questions

> I don't know. Have you run an experiment to see for yourself? > Yes, but my results are inconclusive. Currently, i am doing this: drop table idlookup;create temp table idlookup as select id from (select statement for temporary result set) Thus the statement is shorter than create temp

Re: [sqlite] Another set of questions

On Nov 19, 2008, at 7:08 AM, Christophe Leske wrote: > Hi, > > some more questions... > > I am using in-memory temporary tables with results sets created on > the fly. > > On each round, i would like to create a new table with those interims > results. Question is: > > - what is

Re: [sqlite] Sqlite3 lock issue with NFS and read-only queries

On Nov 19, 2008, at 5:07 AM, David Levy wrote: > Is there a way to tell Sqlite to not lock the database when we know > there > are only read-only queries ? > PRAGMA omit_readlock=ON; D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing

Re: [sqlite] Two questions

On Nov 19, 2008, at 4:36 AM, Christophe Leske wrote: > Hi, > > first i´d like to thank the people on this list that I have found to > be > very helpful in the past. This list is truly great and friendly. > > I got two questions today: > > I have two tables, a standard one and an rtree table,

[sqlite] Another set of questions

Hi, some more questions... I am using in-memory temporary tables with results sets created on the fly. On each round, i would like to create a new table with those interims results. Question is: - what is quicker/better? Dropping the temporary table on every time and recreate it from

Re: [sqlite] Terrible performance for one of our tables

On Nov 19, 2008, at 3:08 AM, Jens Miltner wrote: > Hi, > > we're seeing terrible performance problems when fetching data from one > of our tables: > The table contains roughly 1.2 Million rows and a plain "SELECT > COUNT(*) FROM t" query takes 8 minutes to finish. That's because "SELECT

[sqlite] How to use the test cases for sqlite-amalgamation repository

Hi, I have built and executed the self test cases for sqlite-3.6.4 repository but how to use these test cases with sqlite-amalgamation repository? Regards Avinash ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Sqlite3 lock issue with NFS and read-only queries

Hi all, I am experiencing a pretty annoying issue with my Sqlite3 databases. I am using PDO to access small ( approx. 300Kb ) databases stored on an NFS v3 mount. These queries are multithreaded as they come from a web application. However, there are only SELECT statements, not any

Re: [sqlite] Terrible performance for one of our tables

What page size do you use in your database? Maybe you should increase it? Assuming a table fully fragmented with a page size of 16KB then the I/O rate should be (if my calculations are correct) in the 2MB/s range (assuming a hard disk having 50MB/s bandwidth, 7msec seek time). For 32KB page

[sqlite] Two questions

Hi, first i´d like to thank the people on this list that I have found to be very helpful in the past. This list is truly great and friendly. I got two questions today: I have two tables, a standard one and an rtree table, which are both linked together logically by the ID field of the rtree

Re: [sqlite] SQLite file security

Satish <[EMAIL PROTECTED]> writes: > > Hi! > > I am basically a windows application developer. I am developing an > application for desktop which uses a database. I choose SQLite as my > database and my issue is if any one finds my application is using SQLite > database they can corrupt my

Re: [sqlite] SQLite file security

Hi, beside the other replys: If you design a new database application: You may simply change the sqlite fileformat header and recompile your sqlite library. Look into the code, it is mentioned there how to do this. This will avoid that other standard sqlite application will be able to read

Re: [sqlite] Terrible performance for one of our tables

Hi, do you have a autoincrement primary key in that table ? if not, try to add one giving sqlite the chance to query an internal index rather than the table itselve. I don't see why sqlite should read all the data from that table. I've read somewhere that count(*) may scan the hole table, if you

Re: [sqlite] SQLite file security

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Satish wrote: > Plz provide me best solution to provide security to my database without any > Data loss. You would have to store the database on another machine over the network that you control. The application would have to send you a request for

Re: [sqlite] SQLite file security

> Now my question is how I can provide security to my database for > example no one can access my database except my application .how can I > provide security o encrypt data by yourself o buy the properitary encryption add-on from hwaci.com o code your appl with .NET and use the

[sqlite] Terrible performance for one of our tables

Hi, we're seeing terrible performance problems when fetching data from one of our tables: The table contains roughly 1.2 Million rows and a plain "SELECT COUNT(*) FROM t" query takes 8 minutes to finish. The table contains 10 fields, but the records average to about 100 Bytes of data total.