Re: [sqlite] How to get started SQLite Encryption Extensions(SEE) in SQLite

[Andi Suhandi]

Hi George,


Thanks for the link
(http://www.codeproject.com/KB/database/CppSQLite.aspx) that you gave
me, it really help me to start progamming in C++.

Ok, read the code in http://www.sqlite.org/quickstart.html, but it
looks like no SEE implementation.

#include 
#include 

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; i wrote:
> Simon Slavin writes:
>  > On 4 Oct 2009, at 3:08am, Andi Suhandi wrote:
>  >
>  > > Since SQLite support C++, I have to ask these questions
>  >
>  > You could ask somewhere else.
>  >
>  > SQLite supports C.  It does not support C++, it just doesn't violate
>  > it.  There is nothing in SQLite that takes any advantage of anything
>  > the '++' adds: SQLite does not use C++ internally, and it has no
>  > special interface for C++ apart from the one for C.
>  >
>  > If people want to invent languages which are a superset of C, that's
>  > fine, but we can't be expected to be familiar with every language
>  > that's a superset of C.  There are too many of them.
>  >
>  >  From your earlier post:
>  >
>  > > Are there anyone can give simple sample code in Visual C++
>  >
>  >
>  > 74,100 web pages have something to say about it, according to Google.
>  > Start with one of those.  If you have sample code that isn't working,
>  > post a minimal extract here, tell us how it fails, and we'll try to
>  > figure out what's wrong.
>
> I'm new to the sqlite list, not sure what the tone is supposed to be.
> I thought I'd make a helpful comment.
>
> I don't know the wiki well, there are at least some minimal examples
> here:
>
>   http://www.sqlite.org/quickstart.html
>
> If you look around the wiki you might find more.
>
> If you google 'sqlite c sample' it'll get you a bunch of links that
> you might find helpful.  Here are two that I found on the first page
> of hits that didn't seem insane.
>
>   http://freshmeat.net/articles/sqlite-tutorial
>   http://www.codeproject.com/KB/database/CppSQLite.aspx
>
> I think that you'll generally find the Internet more helpful if you
> ask specific questions and do your homework first.  Otherwise people
> will just flame at you.
>
> g.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Andi Suhandi
Software Engineer
PT. Informatika Lintasnusa
www.informatika.co.id
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I get context in collation function?

[Dan Kennedy]

> BTW while wandering in the 3.6.18 amalgamation source looking at how
> allocation failures are handled, I found a number of sqlite3_malloc
> whose return values are used (written to) head first without prior
> checking against 0.

We know there are still some of these cases in the fts3 code. If you
find any in any other modules, please point them out.

Dan.

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

Re: [sqlite] Phoronix SQLite benchmark Improvements - [was Re: SQLite behaviour on FreeBSD and KVM]

[Simon Slavin]

On 4 Oct 2009, at 3:26am, Roger Binns wrote:

> Matthew Tippett wrote:
>> Any takers?
>
> It isn't clear what you want.  It mostly appears to be people to fix  
> the
> Phoronix test suite.  That is really their problem!
>
> SQLite already includes various speed tests.

I like this as a response.  The SQLite people have already collected  
some tests  Read about them here:



The TCL tests are open and freely available as part of the source tree  
for SQLite.  They're open.  Pick whichever subtests you want and  
modify them and implement them however you want.  If you want, run the  
whole thing.  If you have a specific question, someone here can  
probably help you.

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

Re: [sqlite] How to get started SQLite Encryption Extensions(SEE) in SQLite

[George Hartzell]

Simon Slavin writes:
 > On 4 Oct 2009, at 3:08am, Andi Suhandi wrote:
 > 
 > > Since SQLite support C++, I have to ask these questions
 > 
 > You could ask somewhere else.
 > 
 > SQLite supports C.  It does not support C++, it just doesn't violate  
 > it.  There is nothing in SQLite that takes any advantage of anything  
 > the '++' adds: SQLite does not use C++ internally, and it has no  
 > special interface for C++ apart from the one for C.
 > 
 > If people want to invent languages which are a superset of C, that's  
 > fine, but we can't be expected to be familiar with every language  
 > that's a superset of C.  There are too many of them.
 > 
 >  From your earlier post:
 > 
 > > Are there anyone can give simple sample code in Visual C++
 > 
 > 
 > 74,100 web pages have something to say about it, according to Google.   
 > Start with one of those.  If you have sample code that isn't working,  
 > post a minimal extract here, tell us how it fails, and we'll try to  
 > figure out what's wrong.

I'm new to the sqlite list, not sure what the tone is supposed to be.
I thought I'd make a helpful comment.

I don't know the wiki well, there are at least some minimal examples
here:

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

If you look around the wiki you might find more.

If you google 'sqlite c sample' it'll get you a bunch of links that
you might find helpful.  Here are two that I found on the first page
of hits that didn't seem insane.

  http://freshmeat.net/articles/sqlite-tutorial
  http://www.codeproject.com/KB/database/CppSQLite.aspx

I think that you'll generally find the Internet more helpful if you
ask specific questions and do your homework first.  Otherwise people
will just flame at you.

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

Re: [sqlite] How to get started SQLite Encryption Extensions(SEE) in SQLite

[Simon Slavin]

On 4 Oct 2009, at 3:08am, Andi Suhandi wrote:

> Since SQLite support C++, I have to ask these questions

You could ask somewhere else.

SQLite supports C.  It does not support C++, it just doesn't violate  
it.  There is nothing in SQLite that takes any advantage of anything  
the '++' adds: SQLite does not use C++ internally, and it has no  
special interface for C++ apart from the one for C.

If people want to invent languages which are a superset of C, that's  
fine, but we can't be expected to be familiar with every language  
that's a superset of C.  There are too many of them.

 From your earlier post:

> Are there anyone can give simple sample code in Visual C++


74,100 web pages have something to say about it, according to Google.   
Start with one of those.  If you have sample code that isn't working,  
post a minimal extract here, tell us how it fails, and we'll try to  
figure out what's wrong.

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

Re: [sqlite] Phoronix SQLite benchmark Improvements - [was Re: SQLite behaviour on FreeBSD and KVM]

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Matthew Tippett wrote:
> Any takers?

It isn't clear what you want.  It mostly appears to be people to fix the
Phoronix test suite.  That is really their problem!

SQLite already includes various speed tests.  For other people the only
benchmark that is relevant is their own.  Some think 10MB is a large
database while others are in the tens of gigabytes.  Some strings are short
(eg names), some are longer (eg full pathnames and others very long (eg
genetic sequences).  Some use SQLite to store 3D data while others are plain
old tables that even Excel could handle.  Some transactions are small,
others are large as percentages of the tables.  Quite simply there is no way
someone else's benchmark is going to be representative of what you do.

If Phoronix wants to be relevant then they need to decide what it is they
are benchmarking.  You can make SQLite use lots of CPU by doing sorts on
large data sets.  You can make it do lots of I/O by making a query access
far more data than fits in the SQLite cache and the operating system cache
(the latter is largely a function of spare RAM).  You can make up synthetic
scenarios such as "pet shop", "gene splicer", "web log analyzer" etc and
code up to them.  A critique of those is easy providing the SQL executed and
a .dump of the database are provided so they can be reproduced by the shell.

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

iEYEARECAAYFAkrIB+oACgkQmOOfHg372QTrbgCfTDFr7109qXh0U7RIqtWapBzw
2bgAn1K5PwA9NKOLSLHa1UUx9cr80Y6z
=f+ot
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get started SQLite Encryption Extensions(SEE) in SQLite

[Andi Suhandi]

Well,

Since SQLite support C++, I have to ask these questions


Regards,


Andi

On 10/4/09, Simon Slavin  wrote:
>
> On 4 Oct 2009, at 2:12am, Andi Suhandi wrote:
>
>> Are there anyone can give simple sample code in Visual C++,
>> implementation SQLite with and without SEE ? SO I can compare it ?
>> Starting from :
>> 1. how to connect to database
>> 2. query
>> 3. close the database
>
> Which is it you are: someone who knows how to use SQLite but not
> Visual C++, or someone who knows how to use Visual C++ but not
> SQLite ?  I think you're better off asking in a Visual C++ place.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Andi Suhandi
Software Engineer
PT. Informatika Lintasnusa
www.informatika.co.id
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get started SQLite Encryption Extensions(SEE) in SQLite

[Simon Slavin]

On 4 Oct 2009, at 2:12am, Andi Suhandi wrote:

> Are there anyone can give simple sample code in Visual C++,
> implementation SQLite with and without SEE ? SO I can compare it ?
> Starting from :
> 1. how to connect to database
> 2. query
> 3. close the database

Which is it you are: someone who knows how to use SQLite but not  
Visual C++, or someone who knows how to use Visual C++ but not  
SQLite ?  I think you're better off asking in a Visual C++ place.

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

[sqlite] How to get started SQLite Encryption Extensions(SEE) in SQLite

[Andi Suhandi]

Dear All,

Thank you to the member for fast reply
Are there anyone can give simple sample code in Visual C++,
implementation SQLite with and without SEE ? SO I can compare it ?
Starting from :
1. how to connect to database
2. query
3. close the database

Regards,


Andi
===
Software Engineer Support
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Phoronix SQLite benchmark Improvements - [was Re: SQLite behaviour on FreeBSD and KVM]

[Matthew Tippett]

Any takers?

Matt

 Original Message  
Subject: [sqlite] Phoronix SQLite benchmark Improvements - [was Re: 
SQLite behaviour on FreeBSD and KVM]
From: Matthew Tippett 
To: General Discussion of SQLite Database 
Date: 09/29/2009 10:00 PM

> Relabling to provide focus on this thread the KVM/FreeBSD specific 
> issues - I'll deal with that the main thread.
> 
> So, if I could put forward to you a few suggestions.
> 
>1) Review the existing tests (which you have done somewhat already)
>2) Define a clear and relevant intent for benchmarking SQLite
>3) Work with Michael (from Phoronix) and myself to codify this intent 
>   into a set of test cases and test suites
> 
> I'll let this fork of the thread settle for a bit before taking it off list.
> 
> Regards,
> 
> Matthew
> 
> 
>  Original Message  
> Subject: Re: [sqlite] SQLite behaviour on FreeBSD and KVM
> From: Simon Slavin 
> To: General Discussion of SQLite Database 
> Date: 09/29/2009 08:40 PM
> 
>> On 29 Sep 2009, at 10:29pm, Matthew Tippett wrote:
>>
>>> If there is anyone who is interested in assisting in improving the
>>> quality/value/functional interest of the benchmarks, then please
>>> advise.
>> In SQLite, when you know you are making many changes and don't need to  
>> consult the data until you're finished, you surround the changes with  
>> BEGIN TRANSACTION and END TRANSACTION.  This makes them into one big  
>> update rather than lots of little ones, and it means that disk gets  
>> updated just once (handwave here) rather than after each command.   
>> Naturally, this is hugely faster.  No way should 2500 inserts in  
>> SQLite take 14 minutes.
>>
>> So much faster that, as Pavel noted upthread, it's suspiciously like  
>> what you're seeing in the result for KVM.  This suggests that KVM is  
>> not really writing results to disk immediately.  Putting those INSERTs  
>> into one transaction could make Ubuntu 9.10 (not KVM) as fast or  
>> faster than the result you're getting for KVM.  Sorry, I have no Linux  
>> to test it on.
>>
>> You could modify the sqlite test to reflect this.  You could perhaps  
>> turn sqlite-2500-insertions.txt into 50 transactions, with BEGIN  
>> TRANSACTION and END TRANSACTION around each 50 INSERT commands.  Or  
>> you could have two tests: make two copies of sqlite-2500- 
>> insertions.txt, leave one as it is and put BEGIN TRANSACTION and END  
>> TRANSACTION at the beginning and end of the other.  This would test  
>> both 2500 individual INSERT commands and one transaction of 2500  
>> INSERTs, testing SQLite as both kinds of application would need to use  
>> it.
>>
>> I know nothing about KVM but I assume that it's operating correctly  
>> here: since the entire machine is virtualised it doesn't matter that  
>> it's not really writing to real disk.
>>
>> 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

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

Re: [sqlite] How do I get context in collation function?

[Jean-Christophe Deschamps]

Igor,

´¯¯¯
>It seems fairly easy to me to
>implement the kind of collation you describe using only a fixed amount
>of extra memory.
`---

I didn't say it was impossible. Just that in this case, the code gets 
slowed down in convoluted loops everywhere.  Also I feel that clear, 
straightforward code is much easier to write, debug and get confident 
with and often much faster.



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

Re: [sqlite] How do I get context in collation function?

[Jean-Christophe Deschamps]

Roger,

´¯¯¯
>There is now a ticket for this issue:
`---

Thanks, not high priority but useful someday.



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

Re: [sqlite] SQLite Encryption Extensions(SEE) for SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Robert Simpson wrote:
> I'm pretty sure the license 

Rather than speculating, the SEE usage and license is documented at
http://www.hwaci.com/sw/sqlite/see.html

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

iEYEARECAAYFAkrH3uIACgkQmOOfHg372QSi/wCfUaSLDt9B557eJDZaVAUYrU6k
u+EAnRRxUgQN0MpkEpR+niSS3skEa4F2
=IhtD
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I get context in collation function?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jean-Christophe Deschamps wrote:
> Really, SQLite doesn't have emergency brakes?

Not for collations, just user defined functions.  It is a bit of a pain to
deal with.  I have the same issue in APSW but at least I can still report a
Python level error which SQLite happily continues sorting that will be ignore.

There is now a ticket for this issue:

  http://www.sqlite.org/src/info/1a1790ca1a

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

iEYEARECAAYFAkrH3ckACgkQmOOfHg372QRYpACg5YxnegH3niVtteVguVXddnPp
P8kAn19Vcfq7Xvz1bKRtHox/6OkyPDs/
=1hsr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I get context in collation function?

[Igor Tandetnik]

Jean-Christophe Deschamps wrote:
>> Can't you preallocate sufficient memory at the time the collation is
>> created?
>
> This is for getting unaccented copies of input strings, which
> sometimes involves expansion of a single character into 2 to 5 or 6
> simpler characters.  I use this for offering unaccented collation as
> well as LIKE, friends and a fuzzy search.

Well, custom functions (such as one behind LIKE operator) do have a way 
to report errors. Only collations don't. It seems fairly easy to me to 
implement the kind of collation you describe using only a fixed amount 
of extra memory.

Igor Tandetnik 



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

Re: [sqlite] How do I get context in collation function?

[Jean-Christophe Deschamps]

Igor,


>Can't you preallocate sufficient memory at the time the collation is
>created?

Unfornately I can't do that: it would mean I place a maximum size on a 
work space allocated at creation for manipulating user strings, which 
is taboo in my view.

This is for getting unaccented copies of input strings, which sometimes 
involves expansion of a single character into 2 to 5 or 6 simpler 
characters.  I use this for offering unaccented collation as well as 
LIKE, friends and a fuzzy search.  Working on "live" strings (without 
dup) would make the code such a mess (and slow) that it's unreasonable.


>It seems to me that, rather than tricking SQLite into crashing, it would
>be more straightforward to just call abort().

Well, I might abort the process.  If someone gets into the situation of 
such low memory that I can't malloc or realloc 20 long int (u32) then 
it's likely there is a much bigger problem elsewhere.  OTOH if a fuzzy 
search is launched on a column containing strings big enough to cause 
memory suffocation, then it _is_ a big mistake.  Committing suicide in 
such conditions is understandable, even if I don't like it.

Really, SQLite doesn't have emergency brakes?




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

Re: [sqlite] How do I get context in collation function?

[Igor Tandetnik]

Jean-Christophe Deschamps wrote:
>> It would be best, of course, to write your collation function in
>> such a way that it can't fail.
>
> Certainly, in a perfect world, but I'm afraid I forcably need small
> chunks of memory, whose allocation could possibly fail "ungracefully"
> (?).

Can't you preallocate sufficient memory at the time the collation is 
created?

> Of course what I've done for now is pass a null pointer to companion
> routines, which ignore null context (don't invoke
> sqlite3_result_error_nomem(context) in case context is 0).  Most
> probably this won't hurt much just because you never need to sort 1Gib
> strings with complex collation.  But if you do it by mistake, then
> it's an unavoidable crash with possible consequences.

It seems to me that, rather than tricking SQLite into crashing, it would 
be more straightforward to just call abort().

Igor Tandetnik 



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

Re: [sqlite] build db in memory then dump to a persistent file.

[Simon Slavin]

On 3 Oct 2009, at 9:50pm, George Hartzell wrote:

> One of the tables has almost 19 million rows.  This is the table on
> which I build my 2-D rtree index.
>
> I read the data from tab delimited mysql dumps into the basic tables,
> then run a query that pulls the data I want to rtree-index out of it's
> table, cleans up one of the columns, the inserts it into the rtree
> index.  Finally I add a set of indices to several columns in the big
> table.
>
> I tried using .import to move the data into the tables, but gave up
> after it ran well past the time it took to do the inserts.  That
> didn't seem to help with the index/rtree creation time either.
>
> I'm turning synchronous off and doing the inserts inside of a
> transaction.

One or the other will help a lot but you shouldn't need both.

> I was wondering if there was some way to populate the database inside
> a ':memory:' database and then dump/copy/... the results into a file.
> I've seen posts that suggest that I can select from the memory tables
> and insert into the persistent ones, but that seems like it'd take
> more work to get the indexes.
>
> I'd be interested in any suggestions for making my loading go faster.

I see you're already doing the important bit: using a transaction.   
Possibly the next biggest influence is INSERTing rows first then  
making the indices later.  I don't think using a memory table will  
help much.  I would probably write the INSERT commands to a text file,  
then top and tail it with the other commands, or use the command-line  
tool and type the other commands manually.  If you have two hard  
disks, you might get best results by putting the text file with the  
INSERTs on a different hard disk, or you might not: depends how your  
controllers work.  Try it with 100,000 records and see which is faster.

Simon.

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

Re: [sqlite] How do I get context in collation function?

[Jean-Christophe Deschamps]

Igor,


>I'm not sure where you are seeing this. The collation function doesn't
>have a sqlite3* parameter

No, of course you're right (surprised ? ;-) ): I was doing more things 
than my personal stack could hold. Sorry for that. Great age, slow 
multitask...


>SQLite doesn't seem prepared to handle the case of a collation function
>failing. When this happens, to maintain database integrity, any
>transaction in progress should be rolled back immediately. You should be
>able to do that with sqlite3_interrupt, or you could instruct the
>calling code in some way to do that.

But _this_ is the problem!  How can I safely signal anyhow the "client" 
program that something is turning in turmoil under the water?  I'm 
building a loadable extension and I have no clue which program will run 
over my head.  The only layer to which I can report is the SQLite core 
(itself as a .lib, most of the times).

 From what I've read from sqlite3_interrupt, it should be reserved to 
application code and used with great care.


>It would be best, of course, to write your collation function in such a
>way that it can't fail.

Certainly, in a perfect world, but I'm afraid I forcably need small 
chunks of memory, whose allocation could possibly fail "ungracefully" (?).

Of course what I've done for now is pass a null pointer to companion 
routines, which ignore null context (don't invoke 
sqlite3_result_error_nomem(context) in case context is 0).  Most 
probably this won't hurt much just because you never need to sort 1Gib 
strings with complex collation.  But if you do it by mistake, then it's 
an unavoidable crash with possible consequences.


BTW while wandering in the 3.6.18 amalgamation source looking at how 
allocation failures are handled, I found a number of sqlite3_malloc 
whose return values are used (written to) head first without prior 
checking against 0.  It may be safe to do so in certain situations but 
it doesn't sound like a good idea in general.  It's also quite possible 
that there's no provision in the relevant code to report malloc 
failures, just like when collation run.





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

[sqlite] build db in memory then dump to a persistent file.

[George Hartzell]

Hi all,

I use an SQLite database w/ the rtree extension to hold information
about genetic polymorphism (snp's), based on UCSC's mapping work and
their mysql table dumps.  My database is write-once, read from then
on.

One of the tables has almost 19 million rows.  This is the table on
which I build my 2-D rtree index.

I read the data from tab delimited mysql dumps into the basic tables,
then run a query that pulls the data I want to rtree-index out of it's
table, cleans up one of the columns, the inserts it into the rtree
index.  Finally I add a set of indices to several columns in the big
table.

I tried using .import to move the data into the tables, but gave up
after it ran well past the time it took to do the inserts.  That
didn't seem to help with the index/rtree creation time either.

I'm turning synchronous off and doing the inserts inside of a
transaction.

I was wondering if there was some way to populate the database inside
a ':memory:' database and then dump/copy/... the results into a file.
I've seen posts that suggest that I can select from the memory tables
and insert into the persistent ones, but that seems like it'd take
more work to get the indexes.

I'd be interested in any suggestions for making my loading go faster.

Thanks,

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

Re: [sqlite] How do I get context in collation function?

[Igor Tandetnik]

Jean-Christophe Deschamps wrote:
> I need to have a context* for use inside a collation function (to
> report possible memory allocation errors for instance), but the spec
> for collation doesn't give me a context pointer, only an
> sqlite3*.

I'm not sure where you are seeing this. The collation function doesn't 
have a sqlite3* parameter - it takes a void*. sqlite3_create_collation 
also has a void* parameter, stores it, and passes it along to the 
collation function every time it's called. That's how you typically pass 
any context information to your collation function.

> Now when I encounter, say, a malloc error, what should I do before
> returning from the collation invokation?

SQLite doesn't seem prepared to handle the case of a collation function 
failing. When this happens, to maintain database integrity, any 
transaction in progress should be rolled back immediately. You should be 
able to do that with sqlite3_interrupt, or you could instruct the 
calling code in some way to do that.

It would be best, of course, to write your collation function in such a 
way that it can't fail.

Igor Tandetnik



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

Re: [sqlite] SQLite Encryption Extensions(SEE) for SQLite

[Robert Simpson]

If there were problems, then Dr. H wouldn't sell it ...

The SEE extensions add a couple new API calls, specifically sqlite_key() and
rekey() functions to specify the password (or change the password) for an
encrypted database.  Other than that, your code remains the same.

I'm pretty sure the license requires that you compile with extensions with
your app, or at the very least, if compiled in a library, your library wraps
your specific implementation.  In otherwords, no compiling a generic sqlite
library with SEE extensions and handing it out so everyone can use SEE for
free.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andi Suhandi
Sent: Saturday, October 03, 2009 11:52 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite Encryption Extensions(SEE) for SQLite

Dear All

Hi, I was using sqlite as a database for my program. My program is
based on C++ . Well, for my next project I want encryt the database,
and I read about SQLite Encryption Extensions(SEE).
My questions :
1. Are there problems if I add SEE ?
2. Do I have change a lot in my program ?
3. How to add SEE to my source code (including header, dll, etc that I
have to add and the function that I call sequencially)

Thank you
-- 
Andi Suhandi
Software Engineer Support
___
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] SQLite Encryption Extensions(SEE) for SQLite

[Andi Suhandi]

Dear All

Hi, I was using sqlite as a database for my program. My program is
based on C++ . Well, for my next project I want encryt the database,
and I read about SQLite Encryption Extensions(SEE).
My questions :
1. Are there problems if I add SEE ?
2. Do I have change a lot in my program ?
3. How to add SEE to my source code (including header, dll, etc that I
have to add and the function that I call sequencially)

Thank you
-- 
Andi Suhandi
Software Engineer Support
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help To Get Started with SQLITE

[jack]

Thanks. Obviously it's going to take awhile to remember my C.

Jack

- Original Message - 
From: "Dan Kennedy" 
To: "General Discussion of SQLite Database" 
Sent: Saturday, October 03, 2009 1:05 PM
Subject: Re: [sqlite] Need help To Get Started with SQLITE


> 
> On Oct 4, 2009, at 12:01 AM, jack wrote:
> 
>> I just setting out to learn how to use sqlite3 (3.6.18).  Obviouly  
>> I'm missing some very important points.
>>
>> The very simple test app below is to  open (and create) an sql  
>> datbase then close it
>>
>> Using windows XP. Using a precompiled .LIB. I confirmed the version  
>> number from the command line.
>>
>> It bombs!
>>
>> #include 
>> #include "sqlite3.h"
>> int main()
>> {
>> using namespace std;
>> std::string dbName = "C:\\SQL DATABASES\\first_try.db";
>> const char * c_dbName = dbName.c_str();
>> sqlite3  **db;
>> int  rc;
>>
>> rc = sqlite3_open( "X", db );
> 
> The above causes SQLite to dereference pointer db, which is  
> uninitialized.
> You want something like:
> 
>   sqlite3 *db;
>   sqlite3_open("X", &db);
>   ...
>   sqlite3_close(db);
> 
> 
> 
> 
>> sqlite3_close( *db );
>> return 0;
>> }
>>
>>
>> I tracked down in sqlite3.c where it bombed.
>>
>> #if !SQLITE_OS_WINCE && !defined(__CYGWIN__)
>>  int nByte;
>>  void *zConverted;
>>  char *zOut;
>>  UNUSED_PARAMETER(nFull);
>>  zConverted = convertUtf8Filename(zRelative);
>>  if( isNT() ){
>>WCHAR *zTemp;
>>nByte = GetFullPathNameW((WCHAR*)zConverted, 0, 0, 0) + 3;
>>zTemp = malloc( nByte*sizeof(zTemp[0]) );
>>if( zTemp==0 ){
>>  free(zConverted);
>>  return SQLITE_NOMEM;   <*** GOT TO HERE THEN BOMBED
>>}
>>GetFullPathNameW((WCHAR*)zConverted, nByte, zTemp, 0);
>>free(zConverted);
>>zOut = unicodeToUtf8(zTemp);
>>free(zTemp);
>>
>>
>> Jack
>>
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] dump in-memory db to file in tcl

[Gerry Snyder]

Ned Fleming wrote:
> Is it possible to dump an in-memory sqlite database (or table?) to a
> file from within Tcl?
>
> I create it like so:
>
>   sqlite3 dbFireData :memory:
>
> and insert a bunch of records, and then commit.

Is there reason not to attach a file (old or new), and either

create table realfile.newtablecopy as select * from main.originaltable

or else create the new table with ~ the same create table statement used 
for the original table, and then

insert into realfile.newtablecopy select * from main.originaltable

?  The first method copies all the data, but will drop any special 
thingies from the column definitions.


HTH,


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

Re: [sqlite] Need help To Get Started with SQLITE

[Dan Kennedy]

On Oct 4, 2009, at 12:01 AM, jack wrote:

> I just setting out to learn how to use sqlite3 (3.6.18).  Obviouly  
> I'm missing some very important points.
>
> The very simple test app below is to  open (and create) an sql  
> datbase then close it
>
> Using windows XP. Using a precompiled .LIB. I confirmed the version  
> number from the command line.
>
> It bombs!
>
> #include 
> #include "sqlite3.h"
> int main()
> {
> using namespace std;
> std::string dbName = "C:\\SQL DATABASES\\first_try.db";
> const char * c_dbName = dbName.c_str();
> sqlite3  **db;
> int  rc;
>
> rc = sqlite3_open( "X", db );

The above causes SQLite to dereference pointer db, which is  
uninitialized.
You want something like:

   sqlite3 *db;
   sqlite3_open("X", &db);
   ...
   sqlite3_close(db);




> sqlite3_close( *db );
> return 0;
> }
>
>
> I tracked down in sqlite3.c where it bombed.
>
> #if !SQLITE_OS_WINCE && !defined(__CYGWIN__)
>  int nByte;
>  void *zConverted;
>  char *zOut;
>  UNUSED_PARAMETER(nFull);
>  zConverted = convertUtf8Filename(zRelative);
>  if( isNT() ){
>WCHAR *zTemp;
>nByte = GetFullPathNameW((WCHAR*)zConverted, 0, 0, 0) + 3;
>zTemp = malloc( nByte*sizeof(zTemp[0]) );
>if( zTemp==0 ){
>  free(zConverted);
>  return SQLITE_NOMEM;   <*** GOT TO HERE THEN BOMBED
>}
>GetFullPathNameW((WCHAR*)zConverted, nByte, zTemp, 0);
>free(zConverted);
>zOut = unicodeToUtf8(zTemp);
>free(zTemp);
>
>
> Jack
>
> ___
> 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] Need help To Get Started with SQLITE

[jack]

I just setting out to learn how to use sqlite3 (3.6.18).  Obviouly I'm missing 
some very important points.

The very simple test app below is to  open (and create) an sql datbase then 
close it

Using windows XP. Using a precompiled .LIB. I confirmed the version number from 
the command line.

It bombs!

#include 
#include "sqlite3.h"
int main()
{
 using namespace std;
 std::string dbName = "C:\\SQL DATABASES\\first_try.db";
 const char * c_dbName = dbName.c_str();
 sqlite3  **db;
 int  rc;
 
 rc = sqlite3_open( "X", db );
 sqlite3_close( *db );
 return 0;
}


I tracked down in sqlite3.c where it bombed.

#if !SQLITE_OS_WINCE && !defined(__CYGWIN__)
  int nByte;
  void *zConverted;
  char *zOut;
  UNUSED_PARAMETER(nFull);
  zConverted = convertUtf8Filename(zRelative);
  if( isNT() ){
WCHAR *zTemp;
nByte = GetFullPathNameW((WCHAR*)zConverted, 0, 0, 0) + 3;
zTemp = malloc( nByte*sizeof(zTemp[0]) );
if( zTemp==0 ){
  free(zConverted);
  return SQLITE_NOMEM;   <*** GOT TO HERE THEN BOMBED
}
GetFullPathNameW((WCHAR*)zConverted, nByte, zTemp, 0);
free(zConverted);
zOut = unicodeToUtf8(zTemp);
free(zTemp);


Jack

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

[sqlite] How do I get context in collation function?

[Jean-Christophe Deschamps]

Hi all,


I need to have a context* for use inside a collation function (to 
report possible memory allocation errors for instance), but the spec 
for collation doesn't give me a context pointer, only an 
sqlite3*.  This later guy is defined as an "opaque" structure, so I'm a 
little scared to misuse something.

It's a pity because several functions I call from there already rely on 
a context* for such case (used for SQL functions).

Now when I encounter, say, a malloc error, what should I do before 
returning from the collation invokation?

Thank you for your advices.



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

Re: [sqlite] How to use sqlite3_exec() to execute SQL command with Unicode text?

[bigboss97]

Thank you for the link.
Then I started solving the callback problem. After a bit research, I found
out that I could end up with a new exec() function. So I took the original
exec() and modified it. It's working, no guaratee  :-D

int sqlite3_exec16(
  sqlite3 *db,/* The database on which the SQL executes */
  const short *zSql,   /* The SQL(16) to be executed */
  sqlite3_callback16 xCallback, /* Invoke this callback routine */
  void *pArg, /* First argument to xCallback() */
  short **pzErrMsg /* Write error messages here */
){
  int rc = SQLITE_OK;
  const short *zLeftover;
  sqlite3_stmt *pStmt = 0;
  short **azCols = 0;

  int nRetry = 0;
  int nCallback;

  if( zSql==0 ) return SQLITE_OK;
  while( (rc==SQLITE_OK || (rc==SQLITE_SCHEMA && (++nRetry)<2)) && zSql[0]
){
int nCol;
short **azVals = 0;

pStmt = 0;
rc = sqlite3_prepare16_v2(db, zSql, -1, &pStmt, &zLeftover);
assert( rc==SQLITE_OK || pStmt==0 );
if( rc!=SQLITE_OK ){
  continue;
}
if( !pStmt ){
  /* this happens for a comment or white-space */
  zSql = zLeftover;
  continue;
}

nCallback = 0;

nCol = sqlite3_column_count(pStmt);
azCols = sqliteMalloc(2*nCol*sizeof(const short *) + 1);
if( azCols==0 ){
  goto exec_out;
}

while( 1 ){
  int i;
  rc = sqlite3_step(pStmt);

  /* Invoke the callback function if required */
  if( xCallback && (SQLITE_ROW==rc || 
  (SQLITE_DONE==rc && !nCallback && db->flags&SQLITE_NullCallback))
){
if( 0==nCallback ){
  for(i=0; ierrMask)==rc );
  return rc;
}



Kees Nuyt wrote:
> 
> 
> This page tells it all in a nutshell:
> http://www.sqlite.org/c3ref/stmt.html
> -- 
>   (  Kees Nuyt
> 


-

www.folksfun.com

-- 
View this message in context: 
http://www.nabble.com/How-to-use-sqlite3_exec%28%29-to-execute-SQL-command-with-Unicode-text--tp25663732p25728190.html
Sent from the SQLite mailing list archive at Nabble.com.

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