[sqlite] SQLITE_DEFAULT_FOREIGN_KEYS support please

[Kyle McKay]

Currently the code base supports SQLITE_DEFAULT_RECURSIVE_TRIGGERS  
which controls the default setting for PRAGMA recursive_triggers.

Can someone please add similar support for a  
SQLITE_DEFAULT_FOREIGN_KEYS compiler define that allows the default  
setting of PRAGMA foreign_keys to be controlled at compile time?

Just a couple of lines need to be added around line 343 of sqliteInt.h  
and just a couple more around line 1621 of main.c (the flag needed  
there is SQLITE_ForeignKeys).

It's very trivial to make these changes (and I can easily do it  
locally), but it would sure be nice if support for enabling foreign  
keys by default could make it into the official distribution.

Then you could just add -DSQLITE_DEFAULT_FOREIGN_KEYS=1 to the  
compiler options to have foreign keys always enabled by default.

Anyone else interested in having support for this compiler option?

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

Re: [sqlite] Problem with sqlite providing different programs different data

[Felipe Aramburu]

Hey thanks for the advice after further testing I found this to be a
windows file system issue. windows made a boo boo and had discrepancy
between what could be accessed on the command line and what was shown
in windows

On Thu, Apr 1, 2010 at 3:11 PM, Tim Romano  wrote:
> I should add that when you create a SQLite database outside of Adobe and use
> INT PK, in Adobe's implementation joins will return perfectly plausible yet
> often completely inaccurate results, grabbing rows from the joined table
> with the RowID not the actual PK.
>
> http://forums.adobe.com/message/2365982#2365982
>
> Tim Romano
> ___
> 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] Berkeley DB adds SQL using SQLite API !!

[Florian Weimer]

* sub sk:

> Somehow no one seems to  have mentioned it on this mailing list so far!?
> Here is the scoop...
>
> On March 23, Oracle announced the latest release of Oracle® Berkeley
> DB - 11g Release 2 -  which introduces a new SQL API, based on lo and
> behold, SQLite v3 API. What this means is that all tools that work
> with SQLite will also work with Oracle Berkeley DB.

How do we resolve the symbol clashes?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with sqlite providing different programs different data

[Tim Romano]

I should add that when you create a SQLite database outside of Adobe and use
INT PK, in Adobe's implementation joins will return perfectly plausible yet
often completely inaccurate results, grabbing rows from the joined table
with the RowID not the actual PK.

http://forums.adobe.com/message/2365982#2365982

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

Re: [sqlite] Problem with sqlite providing different programs different data

[Tim Romano]

I don't know what your specific problem may be but check to make sure that
you are using INTEGER [exactly I-N-T-E-G-E-R] primary keys (not INT or any
other variant form). I documented on Adobe's bug database and in their AIR
discussion forum a problem with the Adobe implementation of SQLite --Adobe
is treating INT and INTEGER primary keys as if they were the same, taking
both forms as an alias for the RowId, which in SQLite they are not.

Regards
Tim Romano

On Thu, Apr 1, 2010 at 1:10 PM, Felipe Aramburu  wrote:

> I have some code that is using flex sdk 3.2 and I am updating and
> inserting into a database with the following queries:
>
> stmt.text = 'INSERT INTO Question( hotlist, name, label, datatypeid,
> advanced, multivalue, measurementtypeid) VALUES (@hotlist,
> @name,@label,@datatypeid,@advanced,@multivalue,@measurementtypeid );';
>
>
> stmt.text = 'UPDATE Question SET hotlist = @hotlist, name = @name,
> label = @label, measurementtypeid = @measurementtypeid, datatypeid =
> @datatypeid, advanced = @advanced, multivalue = @multivalue WHERE id =
> @id;';
>
> I don't think those are very interesting to the problem but its better
> to provide more detail than less.
> The program executes these queries without a problem, the view
> displays the information as expected and a quick query using the
> sqlite command line tool will confirm that such queries executed
> successfully and without a problem..
>
> The problem arises when I check the database in two different tools
>
> Lita
> Sqlite Expert Professional (SEP)
>
> Both of these tools show a version of the database that is different
> from what i see in the command line and they are equivalent in their
> discrepancies (they are different from the command line but the same
> as each other).
>
> So heres the basica scenario:
>
> 1. i update my database with the code i wrote
> 2. those changes are reflected in the command line
> 3. those changes are not reflected in Lita/SEP
>
> 1. I update information in Lita/SEP on that same database file
> 2. those changes are reflected in Lita/SEP
> 3. those changes are not reflected in the command line
>
>
> This seems so bizarre to me. How can two different programs make
> changes to one database file and the information i see is different in
> these two programs, but correct according to the changes i made using
> that program.
>
>
> I appreciate any help.
> Felipe
> ___
> 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] Performance problem with count(*) calculation

[Alexey Pechnikov]

Hello!

On Thursday 01 April 2010 18:04:10 Adam DeVita wrote:
> How does
> $ time sqlite3 test32k.db "select count(1) from role_exist"
> perform?

Equal to count(*).

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with sqlite providing different programs differentdata

[Felipe Aramburu]

That's not the issue i am 100 percent certain i am pointing to the file

On Apr 1, 2010 1:17 PM, "Igor Tandetnik"  wrote:

Felipe Aramburu  wrote:
> So heres the basica scenario:
>
> 1. i update my data...
My guess would be, you actually have two different database files (maybe
with the same name but in different directories, maybe in the same directory
but with subtly different names). You are pointing the command line utility
at one of them, and those management tools at the other.
--
Igor Tandetnik


___
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] Problem with sqlite providing different programs different data

[Jean-Christophe Deschamps]

>Both of these tools show a version of the database that is different
>from what i see in the command line and they are equivalent in their
>discrepancies (they are different from the command line but the same
>as each other).
>
>So heres the basica scenario:
>
>1. i update my database with the code i wrote
>2. those changes are reflected in the command line
>3. those changes are not reflected in Lita/SEP
>
>1. I update information in Lita/SEP on that same database file
>2. those changes are reflected in Lita/SEP
>3. those changes are not reflected in the command line

I use SEP heavily and never had such issue.  I don't use Lita so I 
can't comment on it.
Sorry to ask the obvious: have you double-checked you are using the 
same physical file in the CLI and in SEP?

Only times when SEP doesn't reflect actual DB content is when you have 
the Data or View open and have other processes change the 
table/view.  Obviously you have to hit Refresh to see the changes 
appear.  Beside that, I don't see how that can happen.


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

Re: [sqlite] Problem with sqlite providing different programs differentdata

[Igor Tandetnik]

Felipe Aramburu  wrote:
> So heres the basica scenario:
> 
> 1. i update my database with the code i wrote
> 2. those changes are reflected in the command line
> 3. those changes are not reflected in Lita/SEP
> 
> 1. I update information in Lita/SEP on that same database file
> 2. those changes are reflected in Lita/SEP
> 3. those changes are not reflected in the command line

My guess would be, you actually have two different database files (maybe with 
the same name but in different directories, maybe in the same directory but 
with subtly different names). You are pointing the command line utility at one 
of them, and those management tools at the other.
-- 
Igor Tandetnik


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

[sqlite] Problem with sqlite providing different programs different data

[Felipe Aramburu]

I have some code that is using flex sdk 3.2 and I am updating and
inserting into a database with the following queries:

stmt.text = 'INSERT INTO Question( hotlist, name, label, datatypeid,
advanced, multivalue, measurementtypeid) VALUES (@hotlist,
@name,@label,@datatypeid,@advanced,@multivalue,@measurementtypeid );';


stmt.text = 'UPDATE Question SET hotlist = @hotlist, name = @name,
label = @label, measurementtypeid = @measurementtypeid, datatypeid =
@datatypeid, advanced = @advanced, multivalue = @multivalue WHERE id =
@id;';

I don't think those are very interesting to the problem but its better
to provide more detail than less.
The program executes these queries without a problem, the view
displays the information as expected and a quick query using the
sqlite command line tool will confirm that such queries executed
successfully and without a problem..

The problem arises when I check the database in two different tools

Lita
Sqlite Expert Professional (SEP)

Both of these tools show a version of the database that is different
from what i see in the command line and they are equivalent in their
discrepancies (they are different from the command line but the same
as each other).

So heres the basica scenario:

1. i update my database with the code i wrote
2. those changes are reflected in the command line
3. those changes are not reflected in Lita/SEP

1. I update information in Lita/SEP on that same database file
2. those changes are reflected in Lita/SEP
3. those changes are not reflected in the command line


This seems so bizarre to me. How can two different programs make
changes to one database file and the information i see is different in
these two programs, but correct according to the changes i made using
that program.


I appreciate any help.
Felipe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance problem with count(*) calculation

[Jay A. Kreibich]

On Thu, Apr 01, 2010 at 10:44:51AM -0400, Pavel Ivanov scratched on the wall:
> > So 58s for count of all records! The count(*) for all records may use
> > the counter from primary key b-tree, is't it?
> 
> What does this mean? I believe there's no any kind of counters in
> b-tree. If you meant counter from auto-increment key then how about
> gaps in the middle?

  If a statement is in the form "SELECT count(*) FROM " there is
  an optimization that short-cuts the standard aggregate functions and
  gets the leaf count directly from a BTree.

  However, I believe you are correct that the BTree does not keep a
  global leaf count in the header, so the whole tree must be loaded and
  walked.  The optimization will favor a low-column index BTree over the
  table root BTree, since this will typically require less I/O.  Time is
  also saved in short-cutting the column loading and aggregation functions
  (countStep() and countFinalize()).  But in the end, it looks like
  most of the tree is still loaded.

  See the function isSimpleCount() in the source for more specifics.

   -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] REQUEST: Implement APDB like PRAGMA for CHS access

[D. Richard Hipp]

On Apr 1, 2010, at 12:33 PM, Noah Hart wrote:

> Maybe SQLite could implement a PRAGMA to use the
>
> physical memory locator (CHS# - Cylinder, Head, Sector) as data-access
> keys as well
>
>
>
> Could even be faster than the INTEGER PRIMARY KEY now used.
>
>
>
> What do you think?
>

We don't do April fools jokes.  Nor do we find them amusing.

>
>
> http://thedailywtf.com/Articles/Announcing-APDB-The-Worlds-Fastest-Datab
> ase.aspx
>
>
>
> Regards,
>
>
>
> Noah
>
>
>
>
>
>
> CONFIDENTIALITY NOTICE:
> This message may contain confidential and/or privileged information.  
> If you are not the addressee or authorized to receive this for the  
> addressee, you must not use, copy, disclose, or take any action  
> based on this message or any information herein. If you have  
> received this message in error, please advise the sender immediately  
> by reply e-mail and delete this message. Thank you for your  
> cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

[sqlite] REQUEST: Implement APDB like PRAGMA for CHS access

[Noah Hart]

Maybe SQLite could implement a PRAGMA to use the 

physical memory locator (CHS# - Cylinder, Head, Sector) as data-access
keys as well

 

Could even be faster than the INTEGER PRIMARY KEY now used.

 

What do you think?

 

http://thedailywtf.com/Articles/Announcing-APDB-The-Worlds-Fastest-Datab
ase.aspx

 

Regards,

 

Noah

 




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


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

[sqlite] Loading sqlite extensions using QtSql in PyQt4

[Marcelo Reyes]

Hello,

how to load the spatialite extension from python using QtSql? Anyone has
code examples?note that I'm talking about QtSQl not pysql2 or sqlite3 or
apsw. Thank you

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

Re: [sqlite] Question about lemon

[Andy Gibbs]

Hi, sorry to bump this thread, but I thought I'd just try once more to see
whether anybody might have some insight on the problem I'm having with
Lemon, as detailed in the original post below.  I'm sure posts that languish
on this mailing list quickly get snowed under!  I appreciate its not an
Sqlite question, but I don't know where else to post questions about
Lemon.

Thanks!!!
Andy


- Original Message - 
From: "Andy Gibbs"
Sent: Wednesday, March 10, 2010 10:54 AM
Subject: Question about lemon

> Hello,
>
> Is it alright to ask a quick question about the lemon parser in this
> mailing list, or is there a dedicated one which I should post this to
> instead?
>
> I'm using lemon to create a parser for a simple c/basic-like grammar,
> and have among other rules, the following defined (I've trimmed it down
> to what I believe are the important bits for this email, so hopefully
> its all there):
>
> cmd   ::= WHILE expr DO cmd_list END.
> cmd   ::= CASE case_list END.
> cmd   ::= SET set_list.
> cmd_list  ::= cmd_list cmd SEMI.
> cmd_list  ::= cmd SEMI.
> case_list ::= case_list case_cond cmd_list.
> case_list ::= case_cond cmd_list.
> case_cond ::= WHEN expr THEN.
> case_cond ::= ELSE.
> set_list  ::= set_list COMMA set_item.
> set_list  ::= set_item.
> set_item  ::= ID ASSIGN expr.
>
> This in itself works correctly, parsing such code as...
>
> SET a := 0;
> WHILE a < 12 DO
>  some_other_statement;
>  CASE WHEN a = 5 THEN
>another_statement;
>yet_another_statement;
>  WHEN a = 11 THEN
>do_something;
>  END;
>  SET a := a+1;
> END;
>
> However, I would like to be able to add the rule:
>
> cmd ::= set_item.
>
> so that the "SET" keyword becomes optional in the above code (i.e. so
> that I can write "a := 0;" instead of "SET a := 0;").  When I do this
> however, the parser then fails to parse CASE constructs.
>
> I believe I know where the problem lies: I am using the %fallback
> directive which includes a number of keywords that fallback to "ID",
> and in this list is the "END" keyword.  Ideally I would like to leave
> the fallback list as it is, if possible, and in fact I'd much rather
> not add the additional rule than change this!
>
> What interests me is that the WHILE construct still parses correctly.
> An output from the parser trace gives the following:
>
> ...
> Stack: WHILE expr DO cmd SEMI
> Input END
> Reduce [cmd_list ::= cmd SEMI].
> Shift 8
> Stack: WHILE expr DO cmd_list END
> Input SEMI
> Reduce [cmd ::= WHILE expr DO cmd_list END].
> Shift 410
> Stack: cmd
> Shift 515
> Stack: cmd SEMI
> ...
>
> But with the CASE construct, I get:
>
> ...
> Stack: CASE case_list case_cond cmd SEMI
> Input END
> Reduce [cmd_list ::= cmd SEMI].
> Shift 10
> Stack: CASE case_list case_cond cmd_list
> FALLBACK END => ID
> Shift 713
> Stack: CASE case_list case_cond cmd_list END
> Input SEMI
> Syntax error!
>
> My understanding is that it simply isn't able to reduce "CASE case_list
> case_cond cmd_list END" to "CASE case_list END" and then "cmd" and so
> instead takes "END" to be the left-hand-side of the "set_item" rule as
> a continuation of "cmd_list", gets a semi-colon as the next character
> which according to that rule would be a syntax error.
>
> My question -- sorry this is such a long post -- is, what is it about
> the grammar for WHILE which allows it to work, and CASE that causes it
> to fail?  It is because there is an extra "list" level in the grammar
> ("case_list" -> "cmd_list", instead of just "cmd_list")?  Is there any
> way I can change the grammar to do what I hope to do, or is this a
> limitation of lemon?  I've tried adding a rule "cmd ::= CASE case_cond
> cmd_list END" but this causes a parsing conflict error message in lemon.
> I've also had a look into the lemon source code to see if I could find
> an answer there, but I'm afraid its engineering brilliance is beyond me!
>
> Thank you very much for any help that can be offered.
> Andy
> 

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

Re: [sqlite] Performance problem with count(*) calculation

[Pavel Ivanov]

> So 58s for count of all records! The count(*) for all records may use
> the counter from primary key b-tree, is't it?

What does this mean? I believe there's no any kind of counters in
b-tree. If you meant counter from auto-increment key then how about
gaps in the middle?


Pavel

On Thu, Apr 1, 2010 at 5:52 AM, Alexey Pechnikov  wrote:
> Hello!
>
> $ time sqlite3 test32k.db "select count(*) from role_exist"
> 1250
>
> real    0m58.908s
> user    0m0.056s
> sys     0m0.864s
>
> $ sqlite3 test32k.db
> SQLite version 3.6.23
> sqlite> .schema role_exist
> CREATE TABLE role_exist (
>  id INTEGER PRIMARY KEY,
>  uid BLOB NOT NULL DEFAULT (randomblob(16)) UNIQUE ON CONFLICT REPLACE
> );
>
> So 58s for count of all records! The count(*) for all records may use
> the counter from primary key b-tree, is't it?
> ==
> HARDWARE:
>
> $ grep CPU /proc/cpuinfo
> model name      : Intel(R) Core(TM)2 Quad CPU    Q6700  @ 2.66GHz
> model name      : Intel(R) Core(TM)2 Quad CPU    Q6700  @ 2.66GHz
> model name      : Intel(R) Core(TM)2 Quad CPU    Q6700  @ 2.66GHz
> model name      : Intel(R) Core(TM)2 Quad CPU    Q6700  @ 2.66GHz
>
> $ free
>             total       used       free     shared    buffers     cached
> Mem:       8310892    7552880     758012          0      29496    6667708
> -/+ buffers/cache:     855676    7455216
> Swap:      3903784     301240    3602544
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] Performance problem with count(*) calculation

[Adam DeVita]

How does
$ time sqlite3 test32k.db "select count(1) from role_exist"

perform?

On Thu, Apr 1, 2010 at 5:52 AM, Alexey Pechnikov wrote:

> Hello!
>
> $ time sqlite3 test32k.db "select count(*) from role_exist"
> 1250
>
> real0m58.908s
> user0m0.056s
> sys 0m0.864s
>
> $ sqlite3 test32k.db
> SQLite version 3.6.23
> sqlite> .schema role_exist
> CREATE TABLE role_exist (
>  id INTEGER PRIMARY KEY,
>  uid BLOB NOT NULL DEFAULT (randomblob(16)) UNIQUE ON CONFLICT REPLACE
> );
>
> So 58s for count of all records! The count(*) for all records may use
> the counter from primary key b-tree, is't it?
> ==
> HARDWARE:
>
> $ grep CPU /proc/cpuinfo
> model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
> model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
> model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
> model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
>
> $ free
> total   used   free sharedbuffers cached
> Mem:   83108927552880 758012  0  294966667708
> -/+ buffers/cache: 8556767455216
> Swap:  3903784 3012403602544
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ANN: SQLite PHP Generator 10.3 released

[SQL Maestro Group]

Hi!

SQL Maestro Group announces the release of SQLite PHP Generator 10.3, 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 April 11 you can purchase Professional Edition of 
SQLite PHP Generator as well as all other our products and bundles with a 
20% discount. Happy Easter!

Top 10 new features:


1. Autocomplete and WYSIWYG editors.
2. Nested master-detail presentations.
3. Key columns for views and updatable queries.
4. Support for right-to-left languages (such as Arabic and Hebrew).
5. The read-only attribute for the column properties.
6. Project-level data manipulation events.
7. Default ordering for page records.
8. Updated image viewer and calendar components.
9. Filters to restrict number of records returned.
10. Data printing for single records.

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

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

[sqlite] Performance problem with count(*) calculation

[Alexey Pechnikov]

Hello!

$ time sqlite3 test32k.db "select count(*) from role_exist"
1250

real0m58.908s
user0m0.056s
sys 0m0.864s

$ sqlite3 test32k.db
SQLite version 3.6.23
sqlite> .schema role_exist
CREATE TABLE role_exist (
  id INTEGER PRIMARY KEY,
  uid BLOB NOT NULL DEFAULT (randomblob(16)) UNIQUE ON CONFLICT REPLACE
);

So 58s for count of all records! The count(*) for all records may use
the counter from primary key b-tree, is't it?
==
HARDWARE:

$ grep CPU /proc/cpuinfo 
model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz

$ free
 total   used   free sharedbuffers cached
Mem:   83108927552880 758012  0  294966667708
-/+ buffers/cache: 8556767455216
Swap:  3903784 3012403602544

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table is locked error

[Dan Kennedy]

On Apr 1, 2010, at 2:39 PM, Radovan Antloga wrote:

> I did not find any info about my situation in page
> http://www.sqlite.org/lockingv3.html
>
> I would just like a confirmation if this is expected
> behaviour.
>
> I have locking mode = normal. Steps are:
>
> 1. open database
> 2. open statement (simple select from T1)
>and doing 1 step
> 3. execute statement (drop table T2)
>
> I get SQLite error 6 database table is locked.
>
> So while statements are in progress it is not possible
> to drop any tables.

That is correct.

It is not possible to do a DROP TABLE if the database handle
has any active statements (apart from the "DROP TABLE" statement
itself of course).

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

Re: [sqlite] Berkeley DB adds SQL using SQLite API !!

[Jean-Denis Muys]

On 4/1/10 4:12 , "Neville Franks"  wrote:

> Thursday, April 1, 2010, 12:16:13 PM, you wrote:
> 
> JJD> On Wed, Mar 31, 2010 at 8:50 AM, Wiktor Adamski
> JJD>  wrote:
 There were many problems with
 that approach:
>>> ...
 (3) Each table and index is in a
 separate file so your "database" was a directory full of files instead
 of a single file
>>> 
>>> This one is not a problem. Actually I don't see how 1 file is better
>>> than 1 directory. For example mac application is a directory not a
>>> file and no one complains. And with several files database would be
>>> faster (for example dropping a table is instant or fragmentation is
>>> handled by OS without need for vacuuming whole database). Also with
>>> current SQLite implementation only tables would be locked by a
>>> transation not a whole database (a few years ago there were even
>>> document on SQLite website listing  splittnig database to several
>>> files as one way to implement table level locks in SQLite).
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> 
> 
> JJD> Two reasons I prefer the single file approach:
> 
> JJD> 1. Simpler copy, tables and indexes don't get lost or mismatched.
> > 
> Also from the "end user" perspective it is so much easier for them to
> backup or copy a single file.
> 

These two answers are void in the context of the Wiktor's point who said
"For example mac application is a directory not a file and no one
complains".

The reason no one complains is that an app directory is seen by the user as
a single entity (a "bundle"). S/he doesn't have the option to copy/backup
parts of it. (except through geeky, explicit actions).

Of course, the other operating systems don't care much about such details,
nor about users in general, so your points are valid in general, just not in
Wiktor's context.

I think an interesting point can be made that splitting the database in
several files (or forks, such as provided by HFS or NTFS) really means
deferring parts of the DBMS's job to the file system.

It could even be argued that using files in the first place is already
deferring to the OS!

So at one end of the spectrum the DBMS defers at little as possible to the
OS, while at the other end of the spectrum, every table, or even maybe every
row is in its own file.

There are advantages to storing every row in its own file: the database
becomes compatible by system-wide search facilities for example. This is why
Apple went from a single-file mail database to a scheme where every mail is
in its own file. Now you can use spotlight to search for emails.

And to be clear, I agree that a single-file data format was the right choice
for SQLite :-)

Jean-Denis

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

Re: [sqlite] Maximum number of bytes in the text of an SQL statement

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Navaneeth Sen B wrote:
> Could somebody explain this to me :

Which bit in particular is confusing?

SQLITE_MAX_SQL_LENGTH is a C define so you can change it by adding
"-DSQLITE_MAX_SQL_LENGTH=999" to the compiler invocation for sqlite3.c.

The reason for the limit in the first place is because (for historical
reasons) SQLite uses 'int' as the type for sizes which is limited to signed
32 bits even on 64 bit platforms.  That gives a practical limit of 2GB but
1GB is the upper limit because UTF16 expansion could result in 2GB of text.

(Changing the type would break the ABI or require introducing new versions
of several of the public API functions.)

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

iEYEARECAAYFAku0Tl4ACgkQmOOfHg372QRGqwCfRwBPZFX1xv1rGlTUEdob9BO4
EpwAoNzml7x8bUe9IPPQMSYTZsNwq72a
=2IIN
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Table is locked error

[Radovan Antloga]

I did not find any info about my situation in page
http://www.sqlite.org/lockingv3.html

I would just like a confirmation if this is expected
behaviour.

I have locking mode = normal. Steps are:

1. open database
2. open statement (simple select from T1)
and doing 1 step
3. execute statement (drop table T2)

I get SQLite error 6 database table is locked.

So while statements are in progress it is not possible
to drop any tables.


Best Regards
Radovan Antloga


__ Informacija od ESET NOD32 Antivirus, zbirka virusnih definicij 4990 
(20100401) __

To sporočilo je preveril ESET NOD32 Antivirus.

http://www.eset.si


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

[sqlite] Maximum number of bytes in the text of an SQL statement

[Navaneeth Sen B]

Hi all,

Could somebody explain this to me :

"The maximum number of bytes in the text of an SQL statement is limited 
to SQLITE_MAX_SQL_LENGTH which defaults to 100. You can redefine 
*this limit to be as large as the smaller of SQLITE_MAX_LENGTH and 
1073741824*."

Source : http://www.sqlite.org/limits.html (3rd point)

Regards,
Sen
*
*


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