Re: [sqlite] ChangePassword method problem

[Farhan Husain]

Sorry, I should clarify, I meant the chances of the database corruption. You 
are right, in a properly designed system the access to the database would take 
into account a changed password, which would be the normal scenario. But, if 
there is even a small possibility that a database would be "corrupted" because 
of an extraneous connection (regardless of how well the system is designed), 
then it could be a problem. The database being unreadable due to a wrong 
password is good because it is functioning the right way like you stated. It 
shouldn't however become corrupt.
Again, I am quite sure it is simply something that I am doing wrong on my end 
so I need to revisit all the codepaths for this procedure in my code.
Thanks!

> From: slav...@bigfraud.org
> Date: Mon, 24 Oct 2011 04:45:24 +0100
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ChangePassword method problem
> 
> 
> On 24 Oct 2011, at 4:42am, Farhan Husain wrote:
> 
> > So, I was just wondering how you would deal with multiple processes 
> > accessing the database. You can't guarantee that all would be closing the 
> > connection properly. It would seem that if all connections needed to be 
> > closed before a proper changepassword call can take place, then the chances 
> > of database corruption would increase in a multi-process/multi-access 
> > environment.
> 
> How did you expect it to work when you designed the system ?  After all, 
> changing the password to a database when lots of other processes are reading 
> it would naturally cause problems for those processes.  They would all 
> suddenly start generating errors because they'd be unable to access their 
> data.
> 
> 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] ChangePassword method problem

[Simon Slavin]

On 24 Oct 2011, at 4:42am, Farhan Husain wrote:

> So, I was just wondering how you would deal with multiple processes accessing 
> the database. You can't guarantee that all would be closing the connection 
> properly. It would seem that if all connections needed to be closed before a 
> proper changepassword call can take place, then the chances of database 
> corruption would increase in a multi-process/multi-access environment.

How did you expect it to work when you designed the system ?  After all, 
changing the password to a database when lots of other processes are reading it 
would naturally cause problems for those processes.  They would all suddenly 
start generating errors because they'd be unable to access their data.

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

Re: [sqlite] ChangePassword method problem

[Farhan Husain]

So, I was just wondering how you would deal with multiple processes accessing 
the database. You can't guarantee that all would be closing the connection 
properly. It would seem that if all connections needed to be closed before a 
proper changepassword call can take place, then the chances of database 
corruption would increase in a multi-process/multi-access environment.
I haven't adjusted the code based on your reply yet, but as soon as I do I will 
post the results. Thanks!

> From: sql...@mistachkin.com
> To: sqlite-users@sqlite.org
> Date: Sat, 22 Oct 2011 23:33:05 -0700
> Subject: Re: [sqlite] ChangePassword method problem
> 
> 
> Farhan Husain wrote:
> >
> > Aah, ok. So, for all the methods that act on the database I should
> explicitly add
> > conn.Close() within the using conn scope?
> >
> 
> Well, I'm not familiar with your specific project; however, that does not
> sound like
> a bad idea.
> 
> --
> Joe Mistachkin
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 46, Issue 23

[Kees Nuyt]

On Sun, 23 Oct 2011 10:26:14 -0700, Pete 
wrote:

>Apologies, I omitted what is the real cause of the problem.  This simplified
>SELECT illustrates the error:
>
>SELECT sum( colc * cold ) as total from tst where total > 1000
>
>The error message is "misuse of aggregate: sum()".  No error if I remove the
>where clause.

A condition on an aggregate is expressed with a HAVING clause, not
a WHERE clause.

That is because WHERE and HAVING work on different stages of the
SELECT statement: WHERE decides which rows to include in the
aggregate, HAVING decides which results to present after
aggregation.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> When WHERE condition is constant, there are no need to evaluate and check it 
> for
> each row. It works, but only partially:
...
> [In fact, you can move out out loop not only *whole* constant WHERE, but also
> all constant AND terms of WHERE, like this:
> SELECT * FROM t WHERE const1 AND notconst AND const2 ->
> SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND const2
> I'll take a shot on that later.]

Here it goes.

Prerequisite: previous patch.
Passes quick regression test (make test).
Possible problem: short-circuits evaluation. Should not be problem, IMO, as only
constants references? Please verify.

The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy 

Index: sqlite3-3.7.8/src/where.c
===
--- sqlite3-3.7.8.orig/src/where.c  2011-10-23 20:41:44.0 +0400
+++ sqlite3-3.7.8/src/where.c   2011-10-23 20:48:33.0 +0400
@@ -4636,6 +4636,21 @@ WhereInfo *sqlite3WhereBegin(

   whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */

+  {
+/* Move const in "WHERE const AND notconst" out of internal loop */
+int j;
+for(j=i=0; inTerm; i++){
+  if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){
+sqlite3ExprIfFalse(pParse, pWC->a[i].pExpr, pWInfo->iBreak,
SQLITE_JUMPIFNULL);
+continue;
+  }
+  if( j!=i )
+pWC->a[j]=pWC->a[i];
+  j++;
+}
+pWC->nTerm -= i-j;
+  }
+
   /* Assign a bit from the bitmask to every term in the FROM clause.
   **
   ** When assigning bitmask values to FROM clause cursors, it must be


Part 2: Remove redundant sqlite3ExprIsConstantNotJoin call. Result should be
equivalent. Feel free to squash with above patch on apply.

Index: sqlite3-3.7.8/src/where.c
===
--- sqlite3-3.7.8.orig/src/where.c  2011-10-23 21:23:00.0 +0400
+++ sqlite3-3.7.8/src/where.c   2011-10-23 21:25:06.0 +0400
@@ -4626,18 +4626,12 @@ WhereInfo *sqlite3WhereBegin(
   whereClauseInit(pWC, pParse, pMaskSet);
   sqlite3ExprCodeConstants(pParse, pWhere);

-  /* Special case: a WHERE clause that is constant.  Evaluate the
-  ** expression and either jump over all of the code or fall thru.
-  */
-  if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
-sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);
-pWhere = 0;
-  }
-
   whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */

   {
-/* Move const in "WHERE const AND notconst" out of internal loop */
+/* Special case: AND subterm of WHERE clause that is constant. Evaluate the
+** expression and either jump over all of the code or fall thru.
+*/
 int j;
 for(j=i=0; inTerm; i++){
   if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){

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

Re: [sqlite] database like ms northwind

[Eugene N]

As i understand it, Northwind is simply an example of Ms access (populated
db). So, if you need an example of access, buy access and you get Northwind;
If, on the other hand, you dont buy it, you cant use access at all (unless
by broking the law);

If you are looking for something like Northwind for access, but not for
access, then this pops up:
http://stackoverflow.com/questions/2100982/alternatives-to-northwind

2011/10/23 saeed ahmed 

> i want to make a databse like Northwind of ms Access.from where i can get
> guidance or download something similar?
> ___
> 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] database like ms northwind

[saeed ahmed]

i want to make a databse like Northwind of ms Access.from where i can get
guidance or download something similar?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 46, Issue 23

[Pete]

Apologies, I omitted what is the real cause of the problem.  This simplified
SELECT illustrates the error:

SELECT sum( colc * cold ) as total from tst where total > 1000

The error message is "misuse of aggregate: sum()".  No error if I remove the
where clause.

Pete





>
> Message: 2
> Date: Sat, 22 Oct 2011 17:38:23 +0100
> From: Simon Davies 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] Question re use of column alias in SELECT
> Message-ID:
> >
> Content-Type: text/plain; charset=ISO-8859-1
>
> On 22 October 2011 17:28, Pete  wrote:
> > If I have a SELECT statement like:
> >
> > SELECT cola, colb, sum(tableb.colc * tableb.cold) AS Total FROM TableA
> GROUP
> > BY cola ORDER BY Total
>
> What is tableb?
>
> >
> > ...I get an error, I think because of referring to Total in the ORDER BY
> > clause. ?Is it not possible to refer to column aliases anywhere within a
> > SELECT statement other than in the AS clause? ?If not, is there any other
> > way to achieve this without repeating the sum expression?
>
> sqlite> create table tst( id integer primary key, cola integer, colb
> integer, colc integer, cold integer );
> sqlite> SELECT cola, colb, sum( colc * cold ) as total from tst group
> by cola order by total;
>
> works for me, so I don't think that the alias is your problem.
>
> >
> > Pete
>
> Regards,
> Simon
>
>
> 
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [patch] constant WHERE elimination (partially) ineffective

[Yuriy Kaminskiy]

When WHERE condition is constant, there are no need to evaluate and check it for
each row. It works, but only partially:
sqlite> explain SELECT * FROM t;
0|Trace|0|0|0||00|
1|Goto|0|17|0||00|
2|OpenRead|0|60|0|9|00|
3|Rewind|0|15|0||00|
4|Column|0|0|1||00|
5|Column|0|1|2||00|
6|Rowid|0|3|0||00|
7|Column|0|3|4||00|
8|Column|0|4|5||00|
9|Column|0|5|6||00|
10|Column|0|6|7||00|
11|Column|0|7|8||00|
12|Column|0|8|9|0|00|
13|ResultRow|1|9|0||00|
14|Next|0|4|0||01|
15|Close|0|0|0||00|
16|Halt|0|0|0||00|
...
sqlite> explain SELECT * FROM t;
0|Trace|0|0|0||00|
1|Integer|1|1|0||00|
2|IfNot|1|18|1||00|
3|Goto|0|20|0||00|
4|OpenRead|0|60|0|9|00|
5|Rewind|0|18|0||00|
6|IfNot|1|17|1||00|
7|Column|0|0|3||00|
8|Column|0|1|4||00|
9|Rowid|0|5|0||00|
10|Column|0|3|6||00|
11|Column|0|4|7||00|
12|Column|0|5|8||00|
13|Column|0|6|9||00|
14|Column|0|7|10||00|
15|Column|0|8|11|0|00|
16|ResultRow|3|9|0||00|
17|Next|0|6|0||01|
18|Close|0|0|0||00|
19|Halt|0|0|0||00|
[...]
Look at addr 6, there are completely unnecessary IfNot inside of loop: this
condition is already checked out-of-loop.

I've looked at code, and noticed strange thing:
src/where.c, line 4631:
  /* Special case: a WHERE clause that is constant.  Evaluate the
  ** expression and either jump over all of the code or fall thru.
  */
  if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);
pWhere = 0;
  }
But pWhere *is not used* in any code below this fragment, only *above* this
code. Patch below.

[In fact, you can move out out loop not only *whole* constant WHERE, but also
all constant AND terms of WHERE, like this:
SELECT * FROM t WHERE const1 AND notconst AND const2 ->
SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND const2
I'll take a shot on that later.]

The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy 

Index: sqlite3-3.7.8/src/where.c
===
--- sqlite3-3.7.8.orig/src/where.c  2011-10-23 20:04:58.0 +0400
+++ sqlite3-3.7.8/src/where.c   2011-10-23 20:06:30.0 +0400
@@ -4625,7 +4625,6 @@ WhereInfo *sqlite3WhereBegin(
   initMaskSet(pMaskSet);
   whereClauseInit(pWC, pParse, pMaskSet);
   sqlite3ExprCodeConstants(pParse, pWhere);
-  whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */

   /* Special case: a WHERE clause that is constant.  Evaluate the
   ** expression and either jump over all of the code or fall thru.
@@ -4635,6 +4634,8 @@ WhereInfo *sqlite3WhereBegin(
 pWhere = 0;
   }

+  whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */
+
   /* Assign a bit from the bitmask to every term in the FROM clause.
   **
   ** When assigning bitmask values to FROM clause cursors, it must be

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

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Simon Slavin]

On 23 Oct 2011, at 4:13pm, Bo Peng wrote:

> Other than using a SSD to speed up random access, I hope a VACUUM
> operation would copy tables one by one so content of the tables would
> not scatter around the whole database. If this is the case, disk
> caching should work much better after VACUUM... fingers crossed.

VACUUM will defragment, too.  Unless your free space is fragmented.  So yes, 
probably a good move.

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

Re: [sqlite] Database file structure

[Fabian]

2011/10/23 Simon Slavin 

>
> In that case, try defragging your file sometime.  May make a big
> difference.
>
>
If you mean Windows defrag, it would be pointless, since it doesn't change
the database structure? If you mean VACUUM, it will generate the exact same
structure as 'method 2', so I could better use that method in the first
place?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Bo Peng]

On Sun, Oct 23, 2011 at 8:57 AM, Simon Slavin  wrote:
> It seems that this was the first problem he found with the way he arranged 
> this database.  But our solution to it would be different depending on 
> whether he wanted to do this just the once, or it was a regular requirement. 
> With this structure I might use TRIGGERs to keep track of the number of rows 
> in each table.  But Bo might want more than count(*).  But TRIGGERs could be 
> used to keep track of a total too.

I will do this multiple times, with different conditions (e.g. SELECT
MAX(c) FROM TABLE_X WHRE b > 1.0) so maintaining number of rows would
not help. I intentionally avoided TRIGGERs because of the large amount
(billions) of data inserted.

Other than using a SSD to speed up random access, I hope a VACUUM
operation would copy tables one by one so content of the tables would
not scatter around the whole database. If this is the case, disk
caching should work much better after VACUUM... fingers crossed.

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

Re: [sqlite] UNIQUE constraint

[Fabian]

2011/10/23 Simon Slavin 

>
> In this example, the indexed column is a text column.  The text fields
> could have been very long, and checking long text fields for uniqueness can
> involve comparing every byte.  Nevertheless, I do not understand the results
> you quoted.  I wonder whether some use of transactions would have vastly
> reduced the problem.
>
>
In my case, the indexed column is a text column too, and the original author
already stated he batched the inserts into transactions, and made use of
PRAGMA statements. Only after pre-sorting the data, the problem disappeared,
but I'd rather avoid having to pre-sort everything (because SQLite's C code
should be able to do it faster, than my C# code).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database file structure

[Simon Slavin]

On 23 Oct 2011, at 4:03pm, Fabian wrote:

> It's Windows/NTFS, but I get the point.

In that case, try defragging your file sometime.  May make a big difference.

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

Re: [sqlite] UNIQUE constraint

[Simon Slavin]

On 23 Oct 2011, at 3:49pm, Fabian wrote:

> So the only overhead for UNIQUE is that extra check? [snip]

Right.  When doing an INSERT or UPDATE, it checks to see whether the value it's 
trying to add to the index already exists in the index.  If it does, the result 
is an error.  There is no difference in the file format used to store the index.

> The difference between 9 and 156 seconds is too large for me. The original
> author solved this by pre-sorting the data for the indexed column, which
> made the difference 9 to 12 seconds (including the pre-sort), which is very
> acceptable.
> 
> So why does SQLite not pre-sort the data itself, when doing such large batch
> inserts inside a transaction?

In this example, the indexed column is a text column.  The text fields could 
have been very long, and checking long text fields for uniqueness can involve 
comparing every byte.  Nevertheless, I do not understand the results you 
quoted.  I wonder whether some use of transactions would have vastly reduced 
the problem.

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

Re: [sqlite] Database file structure

[Fabian]

2011/10/23 Simon Slavin 

>
> My immediate question is why this is two rows in two separate tables rather
> than one row in one table.  After all, if tables always have the same rows
> in, they might as well be the same row in one table.


I would love to have those rows into a single table, because those joins
slow down the queries, but I have a mix of TEXT and INTEGER columns, and I
had to move the TEXT columns to a different table (FTS virtual table), and I
could not move the INTEGERs too, because FTS doesn't support them.


> So we could ask you for your OS and disk format.  But even then the answer
> will be useful only for your exact current setup.  The next time you get an
> OS update things might change.  Manufacturers tweak this stuff all the time.
>

It's Windows/NTFS, but I get the point.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database file structure

[Simon Slavin]

On 23 Oct 2011, at 3:41pm, Fabian wrote:

> I have two tables, both containing 1 million rows, which frequently need to
> be joined by rowid. Right now, the insert loop is like this:
> 
> For I = 1 to 1000
> INSERT INTO TABLE1 ...
> INSERT INTO TABLE2 ...
> Next [snip]

My immediate question is why this is two rows in two separate tables rather 
than one row in one table.  After all, if tables always have the same rows in, 
they might as well be the same row in one table.

> When I look at the structure of the created database-file, the rows for the
> two tables are in alternating pattern. At first I thought this was a good
> sign, because when the two rows needs to be joined, they are very close to
> eachother on disk.
> 
>  [snip]
> 
> Are there any significant performances differences to be expected when
> choosing the first method vs the second?

The answer changes surprisingly much depending on what OS you're using and what 
format the disk is in.  Windows, for example, suffers very badly when files are 
fragmented, and it does a great deal of pre-fetching, on the assumption that if 
you just asked for the sector S of the disk you are shortly going to want 
sector (S+1) of the disk.  Unix speeds do not degrade as much when files are 
fragmented, and disk drivers generally don't do pre-fetching.

So we could ask you for your OS and disk format.  But even then the answer will 
be useful only for your exact current setup.  The next time you get an OS 
update things might change.  Manufacturers tweak this stuff all the time.

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

Re: [sqlite] UNIQUE constraint

[Fabian]

>
>
> No, a UNIQUE index and a regular index are implemented the exact same way.
> It's just that, at INSERT and UPDATE time, after finding a proper place to
> insert the new value, an additional check is made that the place isn't
> already occupied.
>

So the only overhead for UNIQUE is that extra check?


> I suspect the way you are going to manually filter duplicates will involve
> inserting them into a moral equivalent of a UNIQUE index. The performace is
> probably going to be similar to that of just creating a UNIQUE index up
> front. But only measurements with your particular data can tell for sure.
>

I was planning using the HashList from the .Net framework, which should be
the fastest way to do it.

If SQLite only has to check if a certain position is occupied, it should
outperform creating a hash for each value.

What got me worried was the post "Slow insertion for Unique Text Column" to
this mailinglist, I quote:

---

I was creating a new table and populating it with 100,000 rows of data (as
a
test case; I really wanted to populate it with over a million rows).

[Insertion A] When a Text Column was NOT Unique it would take:
8875 ms = ~9 seconds

[Insertion B] When a Text Column was Unique it would take:
155781 ms = ~156 seconds

-

The difference between 9 and 156 seconds is too large for me. The original
author solved this by pre-sorting the data for the indexed column, which
made the difference 9 to 12 seconds (including the pre-sort), which is very
acceptable.

So why does SQLite not pre-sort the data itself, when doing such large batch
inserts inside a transaction?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database file structure

[Fabian]

I have two tables, both containing 1 million rows, which frequently need to
be joined by rowid. Right now, the insert loop is like this:

For I = 1 to 1000
 INSERT INTO TABLE1 ...
 INSERT INTO TABLE2 ...
Next

When I look at the structure of the created database-file, the rows for the
two tables are in alternating pattern. At first I thought this was a good
sign, because when the two rows needs to be joined, they are very close to
eachother on disk.

An other way to do the inserts would be:

For I = 1 to 1000
 INSERT INTO TABLE1 ...
Next

For I = 1 to 1000
 INSERT INTO TABLE2 ...
Next

Now, the actual data of the two rows, are not close to eachother on disk,
but the structure looks very clean and not fragmented.

Are there any significant performances differences to be expected when
choosing the first method vs the second? I guess that using the first method
JOINS will be faster (because the data is close), but SELECTs on a single
table will be slower (because the rows are scattered around the file), but I
don't know enough about the internals of SQLite to know if that's true.

So what is generally more preferable, or doesnt it make any difference?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint

[Igor Tandetnik]

Fabian  wrote:
> I have a column with a normal INDEX, and I would like to turn it into an
> UNIQUE index, but I'm a bit worried about the performance implications for
> inserts. Can someone give some insight into how UNIQUE is implemented in
> SQLite, does it create extra tables compared to a normale index, are there
> many extra checks?

No, a UNIQUE index and a regular index are implemented the exact same way. It's 
just that, at INSERT and UPDATE time, after finding a proper place to insert 
the new value, an additional check is made that the place isn't already 
occupied.

> And a related question: I need to insert 1 million rows, and currently I
> create the INDEX afterwards, because that should be faster. I cannot create
> the UNIQUE INDEX afterwards, because there's a slight possibility there will
> be 1 or 2 duplicates, which will make the creation fail. I now have the
> possibility to specify UNIQUE upfront (in the TABLE definition) or manually
> filter out any doubles before the insert (which will also take CPU time).
> Would there be any advantage doing this manually, or will SQLite do it just
> as efficiently?

I suspect the way you are going to manually filter duplicates will involve 
inserting them into a moral equivalent of a UNIQUE index. The performace is 
probably going to be similar to that of just creating a UNIQUE index up front. 
But only measurements with your particular data can tell for sure.
-- 
Igor Tandetnik

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

[sqlite] UNIQUE constraint

[Fabian]

I have a column with a normal INDEX, and I would like to turn it into an
UNIQUE index, but I'm a bit worried about the performance implications for
inserts. Can someone give some insight into how UNIQUE is implemented in
SQLite, does it create extra tables compared to a normale index, are there
many extra checks?

And a related question: I need to insert 1 million rows, and currently I
create the INDEX afterwards, because that should be faster. I cannot create
the UNIQUE INDEX afterwards, because there's a slight possibility there will
be 1 or 2 duplicates, which will make the creation fail. I now have the
possibility to specify UNIQUE upfront (in the TABLE definition) or manually
filter out any doubles before the insert (which will also take CPU time).
Would there be any advantage doing this manually, or will SQLite do it just
as efficiently?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] :Re: Concurrent readonly access to a large database.

[Black, Michael (IS)]

So you are definitely thrashing disk then.



An SSD might help as head seek time is constant for those.



But if your gronking 288G in 5m22s that is 894MB/sec (relative to database 
size).



With the default 2M cache_size your flushing cache 450 times per second.



What happens if you bump up your cache_size to the maximum you can?



And have you tried a different page_size?



I also wonder if you did your own count if it would be faster -- since your 
data is interleaved just 'select rowid" and walk through all the tables one row 
at  a time to count them.  That sounds like it would emulate the format on the 
disk and take max advantage of the cache.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Bo Peng [ben@gmail.com]
Sent: Sunday, October 23, 2011 8:47 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

On Sun, Oct 23, 2011 at 8:12 AM, Black, Michael (IS)
 wrote:
> #1 What's the size of your database?

288G, 5000 table, each with ~1.4 million records

> #2 What's your cache_size setting?

default

> #3 How are you loading the data?  Are your table inserts interleaved or by 
> table?  Your best bet would be by interleaving during insert so cache hits 
> would be better.

The tables were created all at once, with records inserted evenly, so
the content of each table is spread all over the 288G place. I believe
this is the reason why cache_size did not help.

> Looks to me like you're getting disk thrashing in test3 and test4 which 
> cache_size could affect also.

I am now thinking that if I vacuum the database so that all tables are
copied one by one. The performance could be increased dramatically
because the content of each table could be read to memory easier.

> And are you running your test twice to bypass the initial cache filling of 
> sqlite?

I ran all the tests on tables that have not been processed (cached).

Bo
___
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] How about a proper forum rather than an e-mail list

[Simon Slavin]

On 23 Oct 2011, at 3:00pm, John Drescher wrote:

>> If the mailing list was replaced by a forum, everybody would go to the forum.
> 
> The failure in this logic is that is not true. I already said I would
> not bother with the forum and I was not the only one.

I would bother with a web forum only if it had an RSS feed.  And even then I'd 
have to get in the habit of using an RSS reader, which I don't currently do 
with work-type stuff.  Part of the attraction of this list is that I don't have 
to think "Oh, I want to read a lot of SQLite-related stuff now !".

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

Re: [sqlite] Problem with binding parameters to LIKE

[Igor Tandetnik]

Navaneeth.K.N  wrote:
> I am trying to use parameters in a LIKE query. I have the following
> code which uses Sqlite C/C++ API.
> 
> const char *sql = "SELECT word FROM words WHERE word LIKE ?1 || '%'
> ORDER BY freq DESC LIMIT 10;";
> 
> int rc = sqlite3_prepare_v2 (db, sql, -1, , NULL);
> if ( rc != SQLITE_OK )
> return false;
> 
> sqlite3_bind_text ( stmt, 1, data , -1, NULL );
> 
> Unfortunaltly, this won't work.

It should. Check the value of "data" variable - you are probably passing 
something other than what you think you are. I don't think anything wrong with 
the code you've shown - the problem must lie in the code you haven't.

> When I execute
> the same statement after removing parameters it works perfectly.
> Something like,
> 
> const char *sql = "SELECT word FROM words WHERE word LIKE 'word'%'
> ORDER BY freq DESC LIMIT 10;";

That can't be right - there's an extra apostrophe before % sign.
-- 
Igor Tandetnik

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

Re: [sqlite] How about a proper forum rather than an e-mail list

[John Drescher]

> If the mailing list was replaced by a forum, everybody would go to the forum.
>
The failure in this logic is that is not true. I already said I would
not bother with the forum and I was not the only one.

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

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Simon Slavin]

On 23 Oct 2011, at 2:47pm, Bo Peng wrote:

> On Sun, Oct 23, 2011 at 8:12 AM, Black, Michael (IS)
>  wrote:
>> #1 What's the size of your database?
> 
> 288G, 5000 table, each with ~1.4 million records

Worth adding here Bo's original post:

On 22 Oct 2011, at 8:52pm, Bo Peng wrote:

> I needed to get some summary statistics of each table but
> find that it will take days to run 'SELECT count(*) FROM table_XX'
> (XX=1,...,5000) sequentially.

It seems that this was the first problem he found with the way he arranged this 
database.  But our solution to it would be different depending on whether he 
wanted to do this just the once, or it was a regular requirement.

With this structure I might use TRIGGERs to keep track of the number of rows in 
each table.  But Bo might want more than count(*).  But TRIGGERs could be used 
to keep track of a total too.

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

Re: [sqlite] Tables as ASCII - is it possible?

[Jay A. Kreibich]

On Sun, Oct 23, 2011 at 05:06:46AM +0100, Paul Linehan scratched on the wall:
> Hi all,
> 
> Is there a way of storing SQLite data (tables) as ASCII text rather
> than as binary data?
> 
> I want to be able to run scripts against my data as well as use SQLite.

  SQLite has drivers for most popular scripting languages.  Just access
  the database as it was meant to be accessed.

   -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] EXT :Re: Concurrent readonly access to a large database.

[Bo Peng]

On Sun, Oct 23, 2011 at 8:12 AM, Black, Michael (IS)
 wrote:
> #1 What's the size of your database?

288G, 5000 table, each with ~1.4 million records

> #2 What's your cache_size setting?

default

> #3 How are you loading the data?  Are your table inserts interleaved or by 
> table?  Your best bet would be by interleaving during insert so cache hits 
> would be better.

The tables were created all at once, with records inserted evenly, so
the content of each table is spread all over the 288G place. I believe
this is the reason why cache_size did not help.

> Looks to me like you're getting disk thrashing in test3 and test4 which 
> cache_size could affect also.

I am now thinking that if I vacuum the database so that all tables are
copied one by one. The performance could be increased dramatically
because the content of each table could be read to memory easier.

> And are you running your test twice to bypass the initial cache filling of 
> sqlite?

I ran all the tests on tables that have not been processed (cached).

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

Re: [sqlite] How about a proper forum rather than an e-mail list

[Yves Goergen]

On 18.10.2011 16:40 CE(S)T, Simon Slavin wrote:
> The way to settle this is easy: leave the mailing list in place.
> Create a web forum.  If people abandon the mailing list and start
> using the web forum instead, it worked.  If people stay with the
> mailing list, the mailing list is superior.

I don't think many would want to be in two places that serve the same
purpose. You can't make a forum a success if the old way still remains
active. A forum will only be successful if people are there to post. And
if nobody posts there, nobody will go there. If the mailing list was
replaced by a forum, everybody would go to the forum. But you definitely
should ask the mailing list users whether they would prefer moving over
to a forum, otherwise many might be angry.

I would also very much prefer a web-based forum. The whole concept has
many advantages over e-mail lists and only few disadvantages.

+ Easy access, no setup (subscription, rules etc.)
+ Easily searchable archive with modern and usable UI
+ Post editing and moderation support (if someone messed it up)
  -> Allows to correct thread hijacking or wrong subject
+ Solid threading support (not every MUA can do it well)
+ Following single threads, with notification
+ URL to any content, directly from the primary UI
+ Optional source code syntax highlighting
+ Less traffic in your mailbox, you only read what you want
+ No publicly published e-mail address (spam etc.)

o More "generation Facebook"-like (is this an advantage at all?)

- Web-based UI may be less efficient for some (married with their MUA;
  also greatly depends on the forum software being used!)

Further reading: http://halr9000.com/article/179

Having said that, unfortunately I don't have the time to set it up and
manage it, but I'm far too little into this community. I'm just a reader
and rare questioner... But you'd need a few moderators to operate a
forum anyway.

-- 
Yves Goergen "LonelyPixel" 
Visit my web laboratory at http://beta.unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Black, Michael (IS)]

#1 What's the size of your database?

#2 What's your cache_size setting?

#3 How are you loading the data?  Are your table inserts interleaved or by 
table?  Your best bet would be by interleaving during insert so cache hits 
would be better.



Looks to me like you're getting disk thrashing in test3 and test4 which 
cache_size could affect also.



And are you running your test twice to bypass the initial cache filling of 
sqlite?





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Bo Peng [ben@gmail.com]
Sent: Saturday, October 22, 2011 10:05 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Concurrent readonly access to a large database.

> It's not only speed in KB/sec that matters. It's also disk system
> usage as reported by iostat. If it's close to 100% then SQLite can't
> do any better.

A sad day.

I copied the database to a faster driver with RAID 0, made another
copy of the database (referred to as DB1), and ran another set of
tests:

test1: two sequential processes of sqlite count(*) table1 and table 2
in DB1 --> 7m15s

test2: two concurrent processes of sqlite count(*) table1 and table2
in DB1 --> 5m22s

test3: four concurrent processes of sqlite count(*) table 1, 2, 3, and
4 in DB1 --> 12m58s

test4: two concurrent processes of sqlite count(*) table1 in DB1, and
table1 in DB2 --> 9m51s.

Although running two or more processes can save some time, the
performance gain is not that big (tests 2 and 3), splitting the
database into several smaller ones would not help either (test 4).

Anyway, the iostat output of my system is

2011 Oct 22 21:16:36,  load: 0.03,  disk_r:   2676 KB,  disk_w:  0 KB

  UIDPID   PPID CMD  DEVICE  MAJ MIN DBYTES
0  0  0  ??   14   8  65536
  503732730 sqlite3  ??   14  14 R  1323008
  503731730 sqlite3  ??   14  14 R  1355776

If I understand correctly, the IO load is only 3% when two sqlite3
processes are running, so perhaps I can still tweak sqlite3 to run
faster. I will also copy the database around and see if other disks
(SSD?), operating system (linux?), and file systems can provide better
performance.

Thanks again for all the help,
Bo
___
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 binding parameters to LIKE

[Richard Hipp]

On Sat, Oct 22, 2011 at 11:53 PM, Navaneeth.K.N wrote:

>  I hooked up sqlite3_trace and
> sqlite3_profile and printed the SQL being executed. Unfortunatly,
> these routines won't give the SQL with values bound to it.
>

sqlite3_trace() does, since version 3.6.21 (2009-12-07).  What version of
SQLite did you say you were using?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [patch] shell.c: fix .schema failure after PRAGMA case_sensitive_like = ON

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Alternative 2: (partially tested)
> Explicitly use case-insensitive comparison for table/indexes, no matter what
> case_sensitive_like is.
> 
> Index: sqlite3-3.7.8/src/shell.c
> ===
> --- sqlite3-3.7.8.orig/src/shell.c2011-10-23 13:52:44.0 +0400
> +++ sqlite3-3.7.8/src/shell.c 2011-10-23 13:54:13.0 +0400
> @@ -1573,16 +1573,18 @@ static int do_meta_command(char *zLine,
>for(i=1; i +int j;
> +for(j=0; azArg[i][j]; i++) azArg[i][j] = (char)tolower(azArg[i][j]);

Doh :-( Fixed version:
Index: sqlite3-3.7.8/src/shell.c
===
--- sqlite3-3.7.8.orig/src/shell.c  2011-10-23 13:52:44.0 +0400
+++ sqlite3-3.7.8/src/shell.c   2011-10-23 13:54:13.0 +0400
@@ -1573,16 +1573,18 @@ static int do_meta_command(char *zLine,
 }else{
   int i;
   for(i=1; iout, p->db,
   "SELECT sql FROM sqlite_master "
   "WHERE sql NOT NULL"
   "  AND type IN ('index','trigger','view')"
-  "  AND tbl_name LIKE shellstatic()", 0
+  "  AND lower(tbl_name) LIKE shellstatic()", 0
 );
 zShellStatic = 0;
   }
@@ -1790,13 +1792,15 @@ static int do_meta_command(char *zLine,
 callback, , 
   );
 }else{
+  int j;
+  for(j=0; azArg[1][j]; j++) azArg[1][j] = (char)tolower(azArg[1][j]);
   zShellStatic = azArg[1];
   rc = sqlite3_exec(p->db,
 "SELECT name FROM sqlite_master "
-"WHERE type='index' AND tbl_name LIKE shellstatic() "
+"WHERE type='index' AND lower(tbl_name) LIKE shellstatic() "
 "UNION ALL "
 "SELECT name FROM sqlite_temp_master "
-"WHERE type='index' AND tbl_name LIKE shellstatic() "
+"WHERE type='index' AND lower(tbl_name) LIKE shellstatic() "
 "ORDER BY 1",
 callback, , 
   );
@@ -2055,7 +2059,7 @@ static int do_meta_command(char *zLine,
   "  (SELECT sql sql, type type, tbl_name tbl_name, name name"
   " FROM sqlite_master UNION ALL"
   "   SELECT sql, type, tbl_name, name FROM sqlite_temp_master) "
-  "WHERE tbl_name LIKE shellstatic() AND type!='meta' AND sql NOTNULL "
+  "WHERE lower(tbl_name) LIKE shellstatic() AND type!='meta' AND sql
NOTNULL "
   "ORDER BY substr(type,2,1), name",
   callback, , );
 zShellStatic = 0;
@@ -2130,13 +2134,15 @@ static int do_meta_command(char *zLine,
 , , 0, 
   );
 }else{
+  int j;
+  for(j=0; azArg[1][j]; j++) azArg[1][j] = (char)tolower(azArg[1][j]);
   zShellStatic = azArg[1];
   rc = sqlite3_get_table(p->db,
 "SELECT name FROM sqlite_master "
-"WHERE type IN ('table','view') AND name LIKE shellstatic() "
+"WHERE type IN ('table','view') AND lower(name) LIKE shellstatic() "
 "UNION ALL "
 "SELECT name FROM sqlite_temp_master "
-"WHERE type IN ('table','view') AND name LIKE shellstatic() "
+"WHERE type IN ('table','view') AND lower(name) LIKE shellstatic() "
 "ORDER BY 1",
 , , 0, 
   );

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

[sqlite] [patch] shell.c: fix .schema failure after PRAGMA case_sensitive_like = ON

[Yuriy Kaminskiy]

Two alternative patches, choose whichever you like.

Alternative 1: (IMO, preferred; tested)
Don't lowercase argument of .schema.
With PRAGMA case_sensitive_like = ON, you just need to use right case for table
names.

The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy 

Index: sqlite3-3.7.8/src/shell.c
===
--- sqlite3-3.7.8.orig/src/shell.c  2011-10-23 14:00:50.0 +0400
+++ sqlite3-3.7.8/src/shell.c   2011-10-23 14:01:14.0 +0400
@@ -2018,9 +2018,7 @@ static int do_meta_command(char *zLine,
 data.showHeader = 0;
 data.mode = MODE_Semi;
 if( nArg>1 ){
-  int i;
-  for(i=0; azArg[1][i]; i++) azArg[1][i] = (char)tolower(azArg[1][i]);
-  if( strcmp(azArg[1],"sqlite_master")==0 ){
+  if( sqlite3_strnicmp(azArg[1],"sqlite_master",13+1)==0 ){
 char *new_argv[2], *new_colv[2];
 new_argv[0] = "CREATE TABLE sqlite_master (\n"
   "  type text,\n"
@@ -2034,7 +2032,7 @@ static int do_meta_command(char *zLine,
 new_colv[1] = 0;
 callback(, 1, new_argv, new_colv);
 rc = SQLITE_OK;
-  }else if( strcmp(azArg[1],"sqlite_temp_master")==0 ){
+  }else if( sqlite3_strnicmp(azArg[1],"sqlite_temp_master",18+1)==0 ){
 char *new_argv[2], *new_colv[2];
 new_argv[0] = "CREATE TEMP TABLE sqlite_temp_master (\n"
   "  type text,\n"
=

Alternative 2: (partially tested)
Explicitly use case-insensitive comparison for table/indexes, no matter what
case_sensitive_like is.

Index: sqlite3-3.7.8/src/shell.c
===
--- sqlite3-3.7.8.orig/src/shell.c  2011-10-23 13:52:44.0 +0400
+++ sqlite3-3.7.8/src/shell.c   2011-10-23 13:54:13.0 +0400
@@ -1573,16 +1573,18 @@ static int do_meta_command(char *zLine,
 }else{
   int i;
   for(i=1; iout, p->db,
   "SELECT sql FROM sqlite_master "
   "WHERE sql NOT NULL"
   "  AND type IN ('index','trigger','view')"
-  "  AND tbl_name LIKE shellstatic()", 0
+  "  AND lower(tbl_name) LIKE shellstatic()", 0
 );
 zShellStatic = 0;
   }
@@ -1790,13 +1792,15 @@ static int do_meta_command(char *zLine,
 callback, , 
   );
 }else{
+  int j;
+  for(j=0; azArg[1][j]; i++) azArg[1][j] = (char)tolower(azArg[1][j]);
   zShellStatic = azArg[1];
   rc = sqlite3_exec(p->db,
 "SELECT name FROM sqlite_master "
-"WHERE type='index' AND tbl_name LIKE shellstatic() "
+"WHERE type='index' AND lower(tbl_name) LIKE shellstatic() "
 "UNION ALL "
 "SELECT name FROM sqlite_temp_master "
-"WHERE type='index' AND tbl_name LIKE shellstatic() "
+"WHERE type='index' AND lower(tbl_name) LIKE shellstatic() "
 "ORDER BY 1",
 callback, , 
   );
@@ -2055,7 +2059,7 @@ static int do_meta_command(char *zLine,
   "  (SELECT sql sql, type type, tbl_name tbl_name, name name"
   " FROM sqlite_master UNION ALL"
   "   SELECT sql, type, tbl_name, name FROM sqlite_temp_master) "
-  "WHERE tbl_name LIKE shellstatic() AND type!='meta' AND sql NOTNULL "
+  "WHERE lower(tbl_name) LIKE shellstatic() AND type!='meta' AND sql
NOTNULL "
   "ORDER BY substr(type,2,1), name",
   callback, , );
 zShellStatic = 0;
@@ -2130,13 +2134,15 @@ static int do_meta_command(char *zLine,
 , , 0, 
   );
 }else{
+  int j;
+  for(j=0; azArg[1][j]; i++) azArg[1][j] = (char)tolower(azArg[1][j]);
   zShellStatic = azArg[1];
   rc = sqlite3_get_table(p->db,
 "SELECT name FROM sqlite_master "
-"WHERE type IN ('table','view') AND name LIKE shellstatic() "
+"WHERE type IN ('table','view') AND lower(name) LIKE shellstatic() "
 "UNION ALL "
 "SELECT name FROM sqlite_temp_master "
-"WHERE type IN ('table','view') AND name LIKE shellstatic() "
+"WHERE type IN ('table','view') AND lower(name) LIKE shellstatic() 

Re: [sqlite] Tables as ASCII - is it possible?

[Petite Abeille]

On Oct 23, 2011, at 6:06 AM, Paul Linehan wrote:

> Is there a way of storing SQLite data (tables) as ASCII text rather
> than as binary data?

Perhaps you might be better off with something along the lines of KirbyBase or 
such.

http://www.netpromi.com/kirbybase_python.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with binding parameters to LIKE

[Baruch Burstein]

I have done something similar and it worked for me, but there is an issue
with indexes you should take into account, as discussed here:
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2011-July/031470.html
.
Out of curiosity (since this query and it's field names seem very similar to
one I am using), what are you using this for?

On Sun, Oct 23, 2011 at 5:53 AM, Navaneeth.K.N wrote:

> Hello,
>
> I am trying to use parameters in a LIKE query. I have the following
> code which uses Sqlite C/C++ API.
>
> const char *sql = "SELECT word FROM words WHERE word LIKE ?1 || '%'
> ORDER BY freq DESC LIMIT 10;";
>
> int rc = sqlite3_prepare_v2 (db, sql, -1, , NULL);
> if ( rc != SQLITE_OK )
> return false;
>
> sqlite3_bind_text ( stmt, 1, data , -1, NULL );
>
> Unfortunaltly, this won't work. Sqlite is executing the statement
> successfully, but I am not getting the expected result. When I execute
> the same statement after removing parameters it works perfectly.
> Something like,
>
> const char *sql = "SELECT word FROM words WHERE word LIKE 'word'%'
> ORDER BY freq DESC LIMIT 10;";
>
> It looks like concatentation with parameters is not working for some
> reason. To debug the issue, I hooked up sqlite3_trace and
> sqlite3_profile and printed the SQL being executed. Unfortunatly,
> these routines won't give the SQL with values bound to it.
>
> I am running out of ideas and any help would be great to address the
> problem.
>
> Thanks
> --
> -n
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ChangePassword method problem

[Joe Mistachkin]

Farhan Husain wrote:
>
> Aah, ok. So, for all the methods that act on the database I should
explicitly add
> conn.Close() within the using conn scope?
>

Well, I'm not familiar with your specific project; however, that does not
sound like
a bad idea.

--
Joe Mistachkin

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

Re: [sqlite] Tables as ASCII - is it possible?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 22/10/11 21:06, Paul Linehan wrote:
> Is there a way of storing SQLite data (tables) as ASCII text rather 
> than as binary data?
> 
> I want to be able to run scripts against my data as well as use
> SQLite.

Yes, and it is very easy.  SQLite has something called virtual tables
where you can register some code to handle tables.  That code can then
look in text files, make internet queries or whatever else meets your
needs.  You can then use regular SQL to make queries independent of what
data format is underlying them.

Virtual tables are documented here:

  http://www.sqlite.org/vtab.html

If you are happy using a higher level language like Python then my APSW
module lets you implement virtual tables easily.  Here is example code
that provides directory listings as a virtual table:

  http://apidoc.apsw.googlecode.com/hg/example.html#example-vtable

Reference documentation:

  http://apidoc.apsw.googlecode.com/hg/vtable.html

APSW includes a more complicated virtual table that lets you use SQLite
against a CouchDB backend:

  http://apidoc.apsw.googlecode.com/hg/couchdb.html

SQLite also provides some functionality using virtual tables such as full
text search and rtree:

  http://www.sqlite.org/fts3.html
  http://www.sqlite.org/rtree.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6jsQQACgkQmOOfHg372QQltgCffbPN2BgbF9MMkrVRs+AM587u
C3oAni6mPiFaZDGCt0WpKu2XfPsqhBAS
=E3QJ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users