date:20130719

OK. Thank you.


2013/7/19 Richard Hipp 

> On Fri, Jul 19, 2013 at 9:20 AM, Sqlite Dog  wrote:
>
> > >
> > > There is no way to detect which encryption algorithm is used.  Indeed,
> > the
> > > encryption is so thorough that there is no way to tell whether or not
> the
> > > file you are trying to open is an encrypted database file or just a
> file
> > of
> > > white noise.
> > >
> >
> >
> > > The default algorithm is the fastest algorithm (AES-128).  I suggest
> you
> > > stick to that one algorithm unless you have a compelling reason to use
> > > another.  That way, you never need to worry which algorithm is being
> > used.
> > >
> >
> >
> > Suppose there are two databases, one is RC-4 encrypted and the other is
> > AES-256 encrypted.
> > What happens on open? SEE will use default algorithm and fail? Or it will
> > try all algorithms in cycle?
> >
>
> It will use the default algorithm and succeed.  But then later when you try
> to query the database you'll get back an SQLITE_CORRUPT error.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
SqliteDog support team
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query on sqlit3_malloc()

2013-07-19 Thread Ashok Pitambar

Thanks Simon and Igor for the reply.


On Fri, Jul 19, 2013 at 11:26 PM, Simon Slavin  wrote:

>
> On 19 Jul 2013, at 6:50pm, Ashok Pitambar  wrote:
>
> >  I am using SQLite in my application, Is sqlite mandates or
> is
> > it necessary
> > to use Sqlite provided memory allocation(sqlit3_malloc()) functions
> instead
> > for native allocators(malloc())in applications? If yes why it is
> necessary?
> > Is there any harm for not using them.
>
> The sqlite3_malloc() function is intended just for use in SQLite's own
> functions.  Don't worry about it unless you're modifying SQLite, or writing
> a SQLite extension.
>
> 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] Query on sqlit3_malloc()

2013-07-19 Thread Ashok Pitambar

Hi All,

  I am using SQLite in my application, Is sqlite mandates or is
it necessary
to use Sqlite provided memory allocation(sqlit3_malloc()) functions instead
for native allocators(malloc())in applications? If yes why it is necessary?
Is there any harm for not using them.

Ex: Here I am using malloc for sqlQuery allocation ,

   sqlQuery = (KN_CHAR*)malloc(DB_QUERY_LEN);
if(NULL != sqlQuery)
{
snprintf(sqlQuery,KN_DB_QUERY_LEN,"update %s set %s='%d' where
%s='%s';",GT_TBL_NAME,GT_STATUS_PTR->colName,//
pGroupStatus,GT_ID_PTR->colName,pGroupId);
}
else
return MEMORY_ALLOCATION_FAILED;

if(KN_Sqlite_DB_ExecQueryInSequence(sqlQuery) != KN_SUCCESS){
 free(sqlQuery);
return SQLITE_QUERY_FAILED;
}

free(sqlQuery);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query on sqlit3_malloc()

2013-07-19 Thread Igor Tandetnik


On 7/19/2013 1:50 PM, Ashok Pitambar wrote:

   I am using SQLite in my application, Is sqlite mandates or is
it necessary
to use Sqlite provided memory allocation(sqlit3_malloc()) functions instead
for native allocators(malloc())in applications? If yes why it is necessary?
Is there any harm for not using them.


No, SQLite doesn't mandate it. It is not necessary. There is no harm in 
using any memory allocator of your choice.


Sometimes, SQLite itself allocates memory which you are responsible for 
deallocating. In these cases, the documentation specifies exactly how to 
do so. For example, sqlite3_mprintf returns a pointer to memory 
allocated with sqlite3_malloc, which the caller should eventually 
deallocate with sqlite3_free. sqlite3_get_table allocates memory for the 
resulting table, which the caller should deallocate with sqlite3_free_table.

--
Igor Tandetnik

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

Re: [sqlite] Query on sqlit3_malloc()

On 19 Jul 2013, at 6:50pm, Ashok Pitambar  wrote:

>  I am using SQLite in my application, Is sqlite mandates or is
> it necessary
> to use Sqlite provided memory allocation(sqlit3_malloc()) functions instead
> for native allocators(malloc())in applications? If yes why it is necessary?
> Is there any harm for not using them.

The sqlite3_malloc() function is intended just for use in SQLite's own 
functions.  Don't worry about it unless you're modifying SQLite, or writing a 
SQLite extension.

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

Re: [sqlite] A few questions about SQLite Encryption Extension

2013/7/19 RSmith 

> Being a Delphi Developer (mostly) and using SQLite quite often, I can tell
> you some things about it:
>
> You can easily wrap an SQLite DLL in Delphi, there are many free
> implementations of this, if you dont have one, send me a mail and I will
> supply one. SEE will work as easily and I don't know of any restriction
> about it being used as a DLL, excepting to disguise it for obvious reasons.
> (not least among which, the security of your own system).
>

We already have a wrapper on SQLite.dll (otherwise we won't be able to
develop a SQLite database manager :) It's a .pas file with function calls
declared like:

...
function sqlite3_open_v2(const Filename: PAnsiChar; var Base: PSQLite3;
Flags: Integer; const Vfs: PAnsiChar = nil): Integer; cdecl; external
'sqlite3.dll';
...

It just wasn't clear how can we use SEE in this case.


>
> Further to this, DISQLite works really well as a native implementation in
> specifically Delphi, and will work very well for your purpose. It is
> however commercial so there is a cost consideration. (Yes it has a
> free-for-personal-use option, but any form of commercial application needs
> the commercial version). The only possible disadvantage I can level at it
> is that to use new adaptations of the SQLite system requires a recompile
> and update as opposed to just shipping another dll - but then if the dll
> supports new API calls, much the same is true. If security is a concern and
> it is a wholly Delphi-integrated product, I would suggest going with
> DISQLite if feasible.
>

I'm unsure about advantages of using DISQLite vs calling directly
SQLite.dll.

The main advantage is statically linking but we'll try to manage it via
compiling amalgamation to .obj and linking.

As for performance. There can't be a significant difference between these
solutions, in my opinion. 99% of the time will be spent inside SQLite.

Thanks, anyway. We'll give it a try.



>
> Lastly, it seems that either you wish to use different forms of
> encryptions to further subterfuge or obscure database content, or that you
> expect to be needing to open datasets with hitherto unknown encryptions. if
> it is the former, I can honestly say it is not needed, just use whatever
> you like, most of the supported encryptions have ratings way above military
> specification when using proper keys etc. If it is the latter case, you
> can't open Databases of which you don't know exactly the encryption and
> keys... there is no way to do this, else the whole encryption racket would
> be rather useless.


I guess you're right.



>
>
>
>
> On 2013/07/19 15:52, Ralf Junker wrote:
>
>> On 19.07.2013 15:27, Sqlite Dog wrote:
>>
>>  * Statically link SQLite to your Delphi application. My DISQLite3
enables you to do just that and has numerous extensions: One is a
custom encryption algorithm. This is not compatible with SEE,
but if you like I can replace it with your original SEE code for you.


 http://www.yunqa.de/delphi/**doku.php/products/sqlite3/**index

>>> Is it a pascal wrapper around SQLite or something bigger?
>>>
>> DISQLite3 is the only Delphi product which includes the _complete_
>> SQLite API, AFAIK. Using register calling conventions and the Delphi
>> memory manager, DISQLite3 surprised many users to perform noticeably
>> faster than other implementations. Features include:
>>
> etc...
>
>
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
Regards,
SqliteDog support team
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

2013-07-19 Thread RSmith

Being a Delphi Developer (mostly) and using SQLite quite often, I can tell you
some things about it:

You can easily wrap an SQLite DLL in Delphi, there are many free implementations of this, if you dont have one, send me a mail and I
will supply one. SEE will work as easily and I don't know of any restriction about it being used as a DLL, excepting to disguise it
for obvious reasons. (not least among which, the security of your own system).

Further to this, DISQLite works really well as a native implementation in specifically Delphi, and will work very well for your
purpose. It is however commercial so there is a cost consideration. (Yes it has a free-for-personal-use option, but any form of
commercial application needs the commercial version). The only possible disadvantage I can level at it is that to use new
adaptations of the SQLite system requires a recompile and update as opposed to just shipping another dll - but then if the dll
supports new API calls, much the same is true. If security is a concern and it is a wholly Delphi-integrated product, I would
suggest going with DISQLite if feasible.

Lastly, it seems that either you wish to use different forms of encryptions to further subterfuge or obscure database content, or
that you expect to be needing to open datasets with hitherto unknown encryptions. if it is the former, I can honestly say it is not
needed, just use whatever you like, most of the supported encryptions have ratings way above military specification when using
proper keys etc. If it is the latter case, you can't open Databases of which you don't know exactly the encryption and keys... there
is no way to do this, else the whole encryption racket would be rather useless.

On 2013/07/19 15:52, Ralf Junker wrote:

On 19.07.2013 15:27, Sqlite Dog wrote:

* Statically link SQLite to your Delphi application. My DISQLite3
enables you to do just that and has numerous extensions: One is a
custom encryption algorithm. This is not compatible with SEE,
but if you like I can replace it with your original SEE code for you.

http://www.yunqa.de/delphi/doku.php/products/sqlite3/index

Is it a pascal wrapper around SQLite or something bigger?

DISQLite3 is the only Delphi product which includes the _complete_
SQLite API, AFAIK. Using register calling conventions and the Delphi
memory manager, DISQLite3 surprised many users to perform noticeably
faster than other implementations. Features include:

etc...

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

Re: [sqlite] A few questions about SQLite Encryption Extension

2013-07-19 Thread Ralf Junker

On 19.07.2013 15:27, Sqlite Dog wrote:

>> * Statically link SQLite to your Delphi application. My DISQLite3
>>   enables you to do just that and has numerous extensions: One is a
>>   custom encryption algorithm. This is not compatible with SEE,
>>   but if you like I can replace it with your original SEE code for you.
>> 
>>   http://www.yunqa.de/delphi/doku.php/products/sqlite3/index
> 
> Is it a pascal wrapper around SQLite or something bigger?

DISQLite3 is the only Delphi product which includes the _complete_
SQLite API, AFAIK. Using register calling conventions and the Delphi
memory manager, DISQLite3 surprised many users to perform noticeably
faster than other implementations. Features include:

* Complete SQLite API.

* Supports Win32 and Win64.

* Delphi class wrapper.

* TDataSet descendant.

* Delphi smart linking for smallest possible binaries.

* Full Text Search (FTS) with customizable tokenizer, prefix matching,
and optional word stemming for 15 languages.

* Custom encryption (not SEE compatible).

* Async IO Backend.

* SQLite virtual table extensions: rtree, spellfix, fuzzer, closure,
wholenumber, amatch.

* SQLite SQL function extensions: ieee754, nextchar.

* Extensive documentation and lots of demo projects.

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

Re: [sqlite] A few questions about SQLite Encryption Extension

On Fri, Jul 19, 2013 at 9:20 AM, Sqlite Dog  wrote:

> >
> > There is no way to detect which encryption algorithm is used.  Indeed,
> the
> > encryption is so thorough that there is no way to tell whether or not the
> > file you are trying to open is an encrypted database file or just a file
> of
> > white noise.
> >
>
>
> > The default algorithm is the fastest algorithm (AES-128).  I suggest you
> > stick to that one algorithm unless you have a compelling reason to use
> > another.  That way, you never need to worry which algorithm is being
> used.
> >
>
>
> Suppose there are two databases, one is RC-4 encrypted and the other is
> AES-256 encrypted.
> What happens on open? SEE will use default algorithm and fail? Or it will
> try all algorithms in cycle?
>

It will use the default algorithm and succeed.  But then later when you try
to query the database you'll get back an SQLITE_CORRUPT error.

-- 
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] A few questions about SQLite Encryption Extension

2013/7/19 Ralf Junker 

> On 19.07.2013 09:21, Sqlite Dog wrote:
>
> > Our database manager is developed using Delphi (Pascal). Thus it is
> > not possible to statically link SQLite library, SQLite.dll is used.
> > Is there some other way to support SEE in our project?
>
> You have two options:
>
> * Create your own sqlite.dll and compile SEE into it.
>

The question was - is it against the rules or not.


>
> * Statically link SQLite to your Delphi application. My DISQLite3
>   enables you to do just that and has numerous extensions: One is a
>   custom encryption algorithm. This is not compatible with SEE,
>   but if you like I can replace it with your original SEE code for you.
>
>   http://www.yunqa.de/delphi/doku.php/products/sqlite3/index


Is it a pascal wrapper around SQLite or something bigger?



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



-- 
Regards,
SqliteDog support team
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

2013/7/19 Richard Hipp 

> On Fri, Jul 19, 2013 at 3:21 AM, Sqlite Dog  wrote:
>
> >
> > Our database manager is developed using Delphi (Pascal). Thus it is not
> > possible to statically link SQLite library, SQLite.dll is used. Is there
> > some other way to support SEE in our project?
> >
>
> Yes.  You can ship it as a DLL and use a special pragma to enable the
> encryption feature.  We ask that you rename the DLL to something that does
> not contain the words "SQLite" or "SEE" or anything similar, to disguise
> its purpose.
>
>
OK


>
>
> >
> > SEE supports several various encryption algorithms. If a database is
> > already encrypted does it automatically choose needed algorithm? How to
> > specify algorithm for a new database if a version of SEE is used which
> > supports all of them? Or is it always using most "powerful" one
> (AES-256)?
> >
>
> There is no way to detect which encryption algorithm is used.  Indeed, the
> encryption is so thorough that there is no way to tell whether or not the
> file you are trying to open is an encrypted database file or just a file of
> white noise.
>


> The default algorithm is the fastest algorithm (AES-128).  I suggest you
> stick to that one algorithm unless you have a compelling reason to use
> another.  That way, you never need to worry which algorithm is being used.
>


Suppose there are two databases, one is RC-4 encrypted and the other is
AES-256 encrypted.
What happens on open? SEE will use default algorithm and fail? Or it will
try all algorithms in cycle?





> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
SqliteDog support team
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

2013-07-19 Thread kyan

On Fri, Jul 19, 2013 at 3:49 PM, Sqlite Dog  wrote:

> That's interesting. What about pas file with function declarations to
> SQLite API? Should it be modified somehow?
>
>
If you use static dll loading and linking, Instead of declaring API
functions as external 'sqlite3.dll' you declare them as plain external. The
linker will automatically link to their implementation from the object file
sqlite3.obj by name.

If you have dynamic dll loading then you must have declared them as
function prototypes and assign them by hand using GetProcAddress() after
calling LoadLibrary() for SqLite3.dll. In this case the sqlite header API
file will require more modifications. For instance this:

var
  sqlite3_open: function(filename: PUTF8Char; var DB: Pointer): integer;
cdecl;

will have to be changed to this:

function sqlite3_open(filename: PUTF8Char; var DB: Pointer): integer;
cdecl; external;

but if the names of the function pointer variables are as in the
amalgamation then every unit that uses the API unit and calls its functions
will not need any modifications. For example the following code:

  Rslt := sqlite3_open(FileName, db);

compiles with both declarations.

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

Re: [sqlite] A few questions about SQLite Encryption Extension

On Fri, Jul 19, 2013 at 8:49 AM, Sqlite Dog  wrote:

> That's interesting. What about pas file with function declarations to
> SQLite API? Should it be modified somehow?
>

No need to.  The SQLite Encryption Extension is controlled using PRAGMA
statements.

-- 
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] A few questions about SQLite Encryption Extension

That's interesting. What about pas file with function declarations to
SQLite API? Should it be modified somehow?


2013/7/19 kyan 

> On Fri, Jul 19, 2013 at 10:21 AM, Sqlite Dog  wrote:
>
> >
> > Our database manager is developed using Delphi (Pascal). Thus it is not
> > possible to statically link SQLite library, SQLite.dll is used. Is there
> > some other way to support SEE in our project?
> >
>
> It is possible to compile the SQLite amalgamation with Embarcadero CBuilder
> and statically link the object file in a Delphi program using the $L
> directive; I have
> successfully
> done this
> with Delphi XE
> . So
> -although I haven't tried it-
> I suppose it is possible to do the same with SEE since it is open-source.
>
> --
> Constantine Yannakopoulos
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
SqliteDog support team
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using in-memory DBs of the form: "file:memdb1?mode=memory&cache=shared" (via Perl, DBD::SQLite & DBI)

2013-07-19 Thread Niall O'Reilly


On 19 Jul 2013, at 09:36, sqlite.20.browse...@xoxy.net wrote:

> Anyone here using SQLite via Perl & DBI & DBD::SQLite?

Yes, but not with an in-memory database.

Niall O'Reilly

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

Re: [sqlite] Integer data type

2013-07-19 Thread Igor Tandetnik


On 7/19/2013 8:29 AM, Paolo Bolzoni wrote:

Interesting problem, can you add a new comparison
operator to sqlite3?


Yes, but only for strings, not for ints. 
http://sqlite.org/c3ref/create_collation.html

--
Igor Tandetnik

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

Re: [sqlite] Integer data type

Or just use a BLOB.

On Fri, Jul 19, 2013 at 2:29 PM, Simon Slavin  wrote:
>
> On 19 Jul 2013, at 1:20pm, Paolo Bolzoni  
> wrote:
>
>> True, I was thinking as follow up of what I
>> mentioned in the first message.
>> -11 is the result of the cast of 4294967285
>> from unsigned int to int in a machine
>> where int are 32 bits long.
>
> Then don't cast.  If you think your numbers are going to overflow 
> 9223372036854775807 and you need integer precision for numbers that big, I'm 
> afraid you're going to have to use another SQL engine.
>
> 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] Integer data type

On 19 Jul 2013, at 1:20pm, Paolo Bolzoni  wrote:

> True, I was thinking as follow up of what I
> mentioned in the first message.
> -11 is the result of the cast of 4294967285
> from unsigned int to int in a machine
> where int are 32 bits long.

Then don't cast.  If you think your numbers are going to overflow 
9223372036854775807 and you need integer precision for numbers that big, I'm 
afraid you're going to have to use another SQL engine.

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

Re: [sqlite] Integer data type

Interesting problem, can you add a new comparison
operator to sqlite3?

On Fri, Jul 19, 2013 at 2:19 PM, Hick Gunter  wrote:
> It might change the sort order...
>
> -Ursprüngliche Nachricht-
> Von: Paolo Bolzoni [mailto:paolo.bolzoni.br...@gmail.com]
> Gesendet: Freitag, 19. Juli 2013 14:11
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Integer data type
>
> After all do you really care if the unsigned int 4294967285 is stored as -11?
>
> On Fri, Jul 19, 2013 at 1:13 PM, Simon Slavin  wrote:
>>
>> On 19 Jul 2013, at 11:02am, techi eth  wrote:
>>
>>> Definition of integer data type will talk for signed integer. What
>>> about unsigned integer ?Are they also be part of same data type.
>>
>> Yes.  SQLite has no special type for an unsigned integer.  Just store them 
>> as integers.
>>
>> The page you were looking at lists everything.  If it's not on that page it 
>> doesn't exist.
>>
>> 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
>
>
> --
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> Klitschgasse 2 – 4, A - 1130 Vienna, Austria
> FN 157284 a, HG Wien
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This e-mail is confidential and may well also be legally privileged. If you 
> have received it in error, you are on notice as to its status and accordingly 
> please notify us immediately by reply e-mail and then delete this message 
> from your system. Please do not copy it or use it for any purposes, or 
> disclose its contents to any person as to do so could be a breach of 
> confidence. Thank you for your cooperation.
> ___
> 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 locking issue with ATTACH'ed databases

On 18 Jul 2013, at 8:26pm, Loren Keagle  wrote:

> It definitely seems to be related to having the same database attached 
> multiple times with different names.

Okay.  I have absolutely no idea how well SQLite copes with that.  I'm not 
saying it's bad, I just have no idea at all.  I hope one of the other readers 
of this list can help.

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

Re: [sqlite] Integer data type

True, I was thinking as follow up of what I
mentioned in the first message.
-11 is the result of the cast of 4294967285
from unsigned int to int in a machine
where int are 32 bits long.


On Fri, Jul 19, 2013 at 2:16 PM, Richard Hipp  wrote:
> On Fri, Jul 19, 2013 at 8:11 AM, Paolo Bolzoni <
> paolo.bolzoni.br...@gmail.com> wrote:
>
>> After all do you really care if the unsigned
>> int 4294967285 is stored as -11?
>>
>
> To be pedantic:  SQLite stores 4294967285 as 4294967285.  It is
> 18446744073709551605 that gets stored as -11.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Integer data type

2013-07-19 Thread Hick Gunter

It might change the sort order...

-Ursprüngliche Nachricht-
Von: Paolo Bolzoni [mailto:paolo.bolzoni.br...@gmail.com]
Gesendet: Freitag, 19. Juli 2013 14:11
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Integer data type

After all do you really care if the unsigned int 4294967285 is stored as -11?

On Fri, Jul 19, 2013 at 1:13 PM, Simon Slavin  wrote:
>
> On 19 Jul 2013, at 11:02am, techi eth  wrote:
>
>> Definition of integer data type will talk for signed integer. What
>> about unsigned integer ?Are they also be part of same data type.
>
> Yes.  SQLite has no special type for an unsigned integer.  Just store them as 
> integers.
>
> The page you were looking at lists everything.  If it's not on that page it 
> doesn't exist.
>
> 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

--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Integer data type

On Fri, Jul 19, 2013 at 8:11 AM, Paolo Bolzoni <
paolo.bolzoni.br...@gmail.com> wrote:

> After all do you really care if the unsigned
> int 4294967285 is stored as -11?
>

To be pedantic:  SQLite stores 4294967285 as 4294967285.  It is
18446744073709551605 that gets stored as -11.

-- 
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] Integer data type

After all do you really care if the unsigned
int 4294967285 is stored as -11?

On Fri, Jul 19, 2013 at 1:13 PM, Simon Slavin  wrote:
>
> On 19 Jul 2013, at 11:02am, techi eth  wrote:
>
>> Definition of integer data type will talk for signed integer. What about
>> unsigned integer ?Are they also be part of same data type.
>
> Yes.  SQLite has no special type for an unsigned integer.  Just store them as 
> integers.
>
> The page you were looking at lists everything.  If it's not on that page it 
> doesn't exist.
>
> 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] Using in-memory DBs of the form: "file:memdb1?mode=memory&cache=shared" (via Perl, DBD::SQLite & DBI)

2013-07-19 Thread sqlite . 20 . browseruk



Hi all,

Anyone here using SQLite via Perl & DBI & DBD::SQLite?

Thanks, buk.


FREE 3D EARTH SCREENSAVER - Watch the Earth right on your desktop!
Check it out at http://www.inbox.com/earth



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

Re: [sqlite] Sqlite locking issue with ATTACH'ed databases

2013-07-19 Thread Loren Keagle

>Date: Wed, 17 Jul 2013 17:21:15 +0100
>From: Simon Slavin 
>To: General Discussion of SQLite Database 
>Subject: Re: [sqlite] Sqlite locking issue with ATTACH'ed databases
>Message-ID: 
>Content-Type: text/plain; charset=us-ascii

>On 16 Jul 2013, at 11:24pm, Loren Keagle  wrote:

>> Begin EXCLUSIVE TRANSACTION;
>> insert several rows of data;
>> Commit transaction;
>>
>> Prepare query statement;
>> Iterate through one or more rows;
>> Reset statement;
>>
>> Attempt to begin transaction; <--- SQLITE_BUSY
>> Would like to write more here, but can't unless I close/open the
>> connection;

>I assume you're checking the result codes returned by all the API calls before 
>the second BEGIN to see that they all return SQLITE_OK.

>Please add a _finalize() after the _reset() just for testing purposes.  I know 
>you may not want it as part of your production code.

>Is the statement that gets a busy a BEGIN or BEGIN IMMEDIATE or BEGIN 
>EXCLUSIVE ?

>Simon.

Thanks for the reply.  I've written wrapper classes in C++ that automatically 
check all return codes for every sqlite API call I make.  The only return error 
is the SQLITE_BUSY from the transaction statement (It's EXCLUSIVE, btw, but it 
doesn't seem to matter in this context).

I've tried finalizing all statements.  It definitely seems to be related to 
having the same database attached multiple times with different names.  I've 
done this because my data is split up amongst multiple sub-databases, and I 
simply have a reader and writer object that can work independently.  Of course, 
they can both end up pointing at the same sub-database, but I never would have 
thought this was a problem.

I've written some sample code to illustrate my problem.  I've commented out the 
actions that don't seem to make any difference.  Simply the fact that I've 
attached the second database causes the failure.  As soon as I detach it, I can 
write on the first again:

// Open master database
sqlite3* db = NULL;
int ret = sqlite3_open_v2("Test.sqlite", &db, SQLITE_OPEN_FULLMUTEX | 
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr);
if(ret != SQLITE_OK)
{
exit(1);
}
sqlite3_extended_result_codes(db, TRUE);

// Create table on main.  This probably serves no purpose.
ret = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS MainTable (id 
INTEGER PRIMARY KEY, IntColumn INTEGER);", NULL, NULL, NULL);
if(ret != SQLITE_OK)
exit(3);

// Attach write database
ret = sqlite3_exec(db, "ATTACH DATABASE 'TestSub.sqlite' as write1;", 
NULL, NULL, NULL);
if(ret != SQLITE_OK)
exit(2);

// Create table on subdb
ret = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS write1.TestTable (id 
INTEGER PRIMARY KEY, IntColumn INTEGER);", NULL, NULL, NULL);
if(ret != SQLITE_OK)
exit(3);

// Insert some data in write table
sqlite3_stmt * insert = nullptr;
const char* tail = nullptr;
ret = sqlite3_prepare_v2(db, "INSERT INTO write1.TestTable (IntColumn) 
VALUES (?1);", -1, &insert, &tail);
if (ret != SQLITE_OK)
exit(4);

for (int i = 0; i < 10; ++i)
{
ret = sqlite3_bind_int(insert, 1, i);
if (ret != SQLITE_OK)
exit(5);

ret = sqlite3_step(insert);
if(ret != SQLITE_DONE)
exit(6);

ret = sqlite3_reset(insert);
if (ret != SQLITE_OK)
exit(7);
}
ret = sqlite3_reset(insert);
if (ret != SQLITE_OK)
exit(8);

// Attach read database
ret = sqlite3_exec(db, "ATTACH DATABASE 'TestSub.sqlite' as read1;", 
NULL, NULL, NULL);
if(ret != SQLITE_OK)
exit(9);

//sqlite3_stmt * readRow = nullptr;
//ret = sqlite3_prepare_v2(db, "SELECT * FROM read1.TestTable;", -1, 
&readRow, &tail);
//if (ret == SQLITE_BUSY || ret == SQLITE_LOCKED)
//  exit(10);

 Iterate through the inserted rows
//do
//{
//  ret = sqlite3_step(readRow);
//  if (ret == SQLITE_BUSY || ret == SQLITE_LOCKED)
//  exit(11);

//  int id = sqlite3_column_int(readRow, 0);
//  int val = sqlite3_column_int(readRow, 1);
//} while (ret != SQLITE_DONE);

//ret = sqlite3_reset(readRow);
//if (ret != SQLITE_OK)
//  exit(12);

 Finalize open read statement. Has no effect on the transaction, 
but is necessary for detaching?
//ret = sqlite3_finalize(readRow);
//if (ret != SQLITE_OK)
//  exit(13);

 Detach read db.  This will allow the transaction to succeed.
//ret = sqlite3_exec(db, "DETACH DATABASE read1;", NULL, NULL, NULL);
//if(ret != SQ

Re: [sqlite] Integer data type

On 19 Jul 2013, at 11:02am, techi eth  wrote:

> Definition of integer data type will talk for signed integer. What about
> unsigned integer ?Are they also be part of same data type.

Yes.  SQLite has no special type for an unsigned integer.  Just store them as 
integers.

The page you were looking at lists everything.  If it's not on that page it 
doesn't exist.

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

Re: [sqlite] A few questions about SQLite Encryption Extension

2013-07-19 Thread Ralf Junker

On 19.07.2013 09:21, Sqlite Dog wrote:

> Our database manager is developed using Delphi (Pascal). Thus it is 
> not possible to statically link SQLite library, SQLite.dll is used. 
> Is there some other way to support SEE in our project?

You have two options:

* Create your own sqlite.dll and compile SEE into it.

* Statically link SQLite to your Delphi application. My DISQLite3
  enables you to do just that and has numerous extensions: One is a
  custom encryption algorithm. This is not compatible with SEE,
  but if you like I can replace it with your original SEE code for you.

  http://www.yunqa.de/delphi/doku.php/products/sqlite3/index

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

Re: [sqlite] A few questions about SQLite Encryption Extension

On Fri, Jul 19, 2013 at 3:21 AM, Sqlite Dog  wrote:

>
> Our database manager is developed using Delphi (Pascal). Thus it is not
> possible to statically link SQLite library, SQLite.dll is used. Is there
> some other way to support SEE in our project?
>

Yes.  You can ship it as a DLL and use a special pragma to enable the
encryption feature.  We ask that you rename the DLL to something that does
not contain the words "SQLite" or "SEE" or anything similar, to disguise
its purpose.

>
> SEE supports several various encryption algorithms. If a database is
> already encrypted does it automatically choose needed algorithm? How to
> specify algorithm for a new database if a version of SEE is used which
> supports all of them? Or is it always using most "powerful" one (AES-256)?
>

There is no way to detect which encryption algorithm is used.  Indeed, the
encryption is so thorough that there is no way to tell whether or not the
file you are trying to open is an encrypted database file or just a file of
white noise.

The default algorithm is the fastest algorithm (AES-128).  I suggest you
stick to that one algorithm unless you have a compelling reason to use
another.  That way, you never need to worry which algorithm is being used.
-- 
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] Integer data type

As far as I know there is no unsigned integer in
sqlite3. If you need cast to a signed integer of the
same size before using sqlite3.

On Fri, Jul 19, 2013 at 12:02 PM, techi eth  wrote:
> Definition of integer data type will talk for signed integer. What about
> unsigned integer ?Are they also be part of same data type.
>
> *INTEGER*. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8
> bytes depending on the magnitude of the value.
> http://www.sqlite.org/datatype3.html
>
> Bye
> ___
> 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] SQLite3 : Corrupt/IO Error

By default sqlite3 is fairly safe, so when you change a pragma
ensure that you are not disabling a safety feature.

To get an idea, check this thread where I had the opposite need
(more speed, less safety)
http://sqlite.1065341.n5.nabble.com/What-pragma-to-use-to-get-maximum-speed-at-expense-of-safety-td68488.html

About backups, here is the documentation:
http://www.sqlite.org/backup.html
I personally always used the historical way. And copied the backups in a
remote server.

On Fri, Jul 19, 2013 at 12:00 PM, techi eth  wrote:
> Thanks for answer.
>
> I can do integrity check by PRAGMA integrity_check.Please correct me if i
> wrong.
> Here backup means copying database file OR we can create query to SQLite
> for backup or copy.
>
> Cheers-
> Techi
>
>
>
> On Wed, Jul 17, 2013 at 4:42 PM, Paolo Bolzoni <
> paolo.bolzoni.br...@gmail.com> wrote:
>
>> On Wed, Jul 17, 2013 at 12:55 PM, techi eth  wrote:
>> > 2)  How do we make database safe from these error Or What is
>> Possibility to
>> > access database after error.
>> After error, as Stephan said you are out of luck.
>>
>> You should avoid this errors in the first place:
>> - make backup often,
>> - keep the safety features of sqlite3 on,
>> - use a error resilient filesystem (e.g., zfs),
>> - use ecc memory.
>> ___
>> 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

[sqlite] Integer data type

2013-07-19 Thread techi eth

Definition of integer data type will talk for signed integer. What about
unsigned integer ?Are they also be part of same data type.

*INTEGER*. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8
bytes depending on the magnitude of the value.
http://www.sqlite.org/datatype3.html

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

Re: [sqlite] SQLite3 : Corrupt/IO Error

2013-07-19 Thread techi eth

Thanks for answer.

I can do integrity check by PRAGMA integrity_check.Please correct me if i
wrong.
Here backup means copying database file OR we can create query to SQLite
for backup or copy.

Cheers-
Techi



On Wed, Jul 17, 2013 at 4:42 PM, Paolo Bolzoni <
paolo.bolzoni.br...@gmail.com> wrote:

> On Wed, Jul 17, 2013 at 12:55 PM, techi eth  wrote:
> > 2)  How do we make database safe from these error Or What is
> Possibility to
> > access database after error.
> After error, as Stephan said you are out of luck.
>
> You should avoid this errors in the first place:
> - make backup often,
> - keep the safety features of sqlite3 on,
> - use a error resilient filesystem (e.g., zfs),
> - use ecc memory.
> ___
> 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] A few questions about SQLite Encryption Extension

2013-07-19 Thread kyan

On Fri, Jul 19, 2013 at 10:21 AM, Sqlite Dog  wrote:

>
> Our database manager is developed using Delphi (Pascal). Thus it is not
> possible to statically link SQLite library, SQLite.dll is used. Is there
> some other way to support SEE in our project?
>

It is possible to compile the SQLite amalgamation with Embarcadero CBuilder
and statically link the object file in a Delphi program using the $L
directive; I have
successfully
done this
with Delphi XE
. So
-although I haven't tried it-
I suppose it is possible to do the same with SEE since it is open-source.

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

[sqlite] A few questions about SQLite Encryption Extension