[sqlite] calculate difference of two times and show the result as double value

[Sven]

Dear SQLite users,

I've created a Microsoft Access 2003 database with three complex queries 
written in pure SQL language. The function of the database is to 
document and calculate the daily work-time.

Today I want to convert this database to SQLite but I have problems with 
my written queries: How can I calculate the difference of two times and 
show the result as double value?

For more information I'll include one example: One table hold the two 
times (start_time, end_time) and the calculated result (shown in the 
table as 'difference'):

[code]
start_time | end_time | difference
08:00:00 | 16:30:00 | 8.5
[/code]

The result of the difference between the two times (start_time, 
end_time) shown in the table above as 'difference' will be calculated 
with the following SQL-query code within Microsoft Access 2003:

[code]
( [end_time] - [start_time] ) * 24
[/code]

and returns the following data '8.5'. While using the following query 
code within SQLite:

[code]
select time(end_time) - time(start_time)
[/code]

I only get the following data returned: '8' (but not '8.5' as calculated 
with SQL).

So currently I don't know what to do next to solve my problem and 
perhaps all the other users of this SQLite-users mailing list could help 
me out.

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

[sqlite] How to use customize Rules & specific locale simultaneously for Sorting using ICU

[ashish yadav]

Hi ,

For sorting , ICU use locale base sorting.

Locale can be given using ucol_open(const char* loc,UErrorCode* status)
function/API like :
collator = ucol_open(en_US, &status)

Now i want to customize collation rule also like :
const static char* rule = "& f < a " ;

Now, for this customize rule , i use function ucol_openRules ( ) ...like :
  collator = ucol_openRules(rlz, rlen, UCOL_OFF,UCOL_TERTIARY,NULL,
&status);

Both of these functions return
UCollator
.

In Sample programs i seen that , both of these function are not use at same
time /simultaneously  , like :
if (opt_rules != 0) {
u_unescape(opt_rules, rules, 100);
collator = ucol_openRules(rules, -1, UCOL_OFF, UCOL_TERTIARY,
  NULL, &status);
}
else {
collator = ucol_open(opt_locale, &status);


So how to make sure that ICU use specific locale like Zh & customize
collation rules  simultaneously ?


Thanks & Regards
Ashish
"Be a Part of Solution"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ActiveX documentation

[stormtrooper]

Steve,

You might try installing this ODBC driver that allows you to query a Sqlite
db just like Access, Excel, etc with Adodb. You can use VB6, VBA, vbscript.

http://www.ch-werner.de/sqliteodbc/

The connection string is like this:
objConn.Open "DRIVER=SQLite3 ODBC Driver;Database=C:\sqlite\test-2.3.sqlite"

Keith


Steve Ely wrote:
> 
> My mistake. I had read that SQLite had an ActiveX, I guess they were
> refering to 3rd party vendors. Can anyone reomend a good one for VB?
> 
> --- On Wed, 1/5/11, Pavel Ivanov  wrote:
> 
> 
> From: Pavel Ivanov 
> Subject: Re: [sqlite] ActiveX documentation
> To: "General Discussion of SQLite Database" 
> Date: Wednesday, January 5, 2011, 4:38 PM
> 
> 
>> Can any one direct me to the documentation for the Windows ActiveX
>> control?
>> I found the zip on the download page but it only contains sqlite3.dll and
>> sqlite3.def.
> 
> If you are talking about sqlite3.dll from sqlite.org website then you
> are wrong: it's not an ActiveX control. It's just a library with a set
> of C functions, that's it. There's documentation on all of those
> functions on the same website sqlite.org.
> 
> 
> Pavel
> 
> On Wed, Jan 5, 2011 at 5:05 PM, Steve Ely  wrote:
>> Can any one direct me to the documentation for the Windows ActiveX
>> control?
>> I found the zip on the download page but it only contains sqlite3.dll and
>> sqlite3.def.
>>
>> Thanks,
>> Steve
>>
>>
>>
>> ___
>> 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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/ActiveX-documentation-tp30600564p30630735.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

Re: [sqlite] R: R: Crypto lib for Sqlite - suggest required

[Simon Slavin]

On 9 Jan 2011, at 5:29pm, Roger Binns wrote:

> I think you misunderstand how the SQLite encryption extension works.  The on
> disk storage format for SQLite is a series of fixed sized pages.  The
> extension transparently encrypts each page on writing to disk and decrypts
> on reading.  To use it you open/attach a database and then provide the
> password either via a C API or a pragma.  You just make regular SQLite API
> calls and everything just works.
> 
>  http://www.hwaci.com/sw/sqlite/see.html
> 
> The various other ones pointed out do something similar but since you go via
> their API layers they intersperse code to do encryption.  I found it very
> hard to work out what they did for encryption since things like the
> algorithm used, IV (the usual weakness for home grown implementations) etc
> do matter.  They also make other choices:

As far as I can work out, the two solutions he pointed to encrypt at the field 
level.  So if you understand the file structure of an SQLite database you can, 
for example, work out which records have the same values in either within a 
table or as across tables.  It also gives you a handy-dandy plain/crypt pair 
since you will know that certain fields definitely start with 'CREATE TABLE ' 
and such things.

On the other hand, these solutions are cheaper than the hwaci one.  As with 
most encryption it depends how much effort you think the enemy will devote to 
attacking your technique.

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

Re: [sqlite] More descriptive error

[Max Vlasov]

On Sun, Jan 9, 2011 at 7:21 PM, BareFeetWare wrote:

> On 9 Jan 2011, at 2:48pm, BareFeetWare wrote:
>
> >> When I run this via C, using sqlite3_errmsg(), I get the error message:
> >>
> >> constraint failed
> >>
> >> but when I run it via the sqlite3 command line I get a more descriptive:
> >>
> >> SQL error: columns First, Last are not unique
> >>
> >> How can I get the more descriptive error via sqlite3_*() routines?
>
> On 10/01/2011, at 3:01 AM, Simon Slavin wrote:
>
> > http://www.sqlite.org/c3ref/errcode.html
> >
> > I'm guessing your 'constraint failed' is just the numeric result 19.
>  Using sqlite3_errmsg() or sqlite3_errmsg16() should get you the long text
> version.
>
> No. sqlite3_errmsg() is what I'm using, which is giving me the string
> "constraint failed".
>
> Where does the sqlite3 command line tool get the more descriptive error?
>
>

Tom, it seems it's related to this issue:
http://www.sqlite.org/cvstrac/tktview?tn=1640. So you have to call
sqlite3_errmsg after sqlite3_finalize() or sqlite3_reset().

Occasionally I had to fix this in order for user functions to supply error
strings (without this it's just simple default error). I checked two recent
versions of my admin with this fix and without and they supply exactly two
different errors as you described

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

Re: [sqlite] fill blank fields

[Igor Tandetnik]

CDN Mark  wrote:
> what I need to do is replace blank fields in a specific row, sort of
> a double where where statement as in:
> 
> UPDATE Aircraft SET CN = '*' where CN = '' or CN is null where (primary key)
> is xyz

UPDATE Aircraft SET CN = '*' where (CN = '' or CN is null) and 
MyPrimaryKey='xyz';
-- 
Igor Tandetnik

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

Re: [sqlite] fill blank fields

[Jean-Christophe Deschamps]

>thanks Simon and Igor for your help on this a few weeks ago, but I 
>need an
>addition to
>
>UPDATE Aircraft SET CN = '*' where CN = '' or CN is null;
>
>what I need to do is replace blank fields in a specific row, sort of
>a double where where statement as in:
>
>UPDATE Aircraft SET CN = '*' where CN = '' or CN is null where 
>(primary key)
>is xyz
>
>What I'm trying to do is update a database, but the users also have an
>automated update system
>that apparently if it finds any blank fields in the row, updates the 
>whole
>row, thereby overwriting
>what I've replaced.  I thought if I could at least replace the blank 
>fields
>with something, it should
>prevent the overwriting.  I don't know which fields are blank, every user
>will be different, so the
>plan was to update the fields I want to, insert text/symbol into the 
>blank
>fields and leave the others
>alone.  I realize that because I don't know which fields are 
>empty/blank I'm
>going to have to create
>a statement for every column I'm not updating to, there are only 10 
>columns
>altogether and I'll
>typically be updating to at least 5 of them.

You should be able to acheive what you want (keep as many columns 
non-empty and non-blank) even while users cause rows update which don't 
follow your rules.

Create an AFTER UPDATE OF (colA, colB, ... ) WHEN new.colA = '' OR 
new.colA IS NULL OR new.colB = '' OR new.colB IS NULL OR ... trigger 
where you can force some value in the columns of your choice.  It will 
surely slightly slow down insert rate, but that doesn't seem to be a 
blocking reason in your case. 

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

Re: [sqlite] R: R: Crypto lib for Sqlite - suggest required

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/09/2011 02:20 AM, Marco Turco wrote:
> My app is an accounting system.
> This accounting system must support the exchange of db between users that
> work with the this application 
> so I think the only way is to use the same key for all users.

Note that SQLite lets you attach additional databases so you don't
necessarily need one single database with one password.  For example if some
of the data is sales and some is payroll and not every user needs to see
both then you can put them in separate databases with different passwords
attaching only what is needed.

> This generate
> a lack of security of course but anyway my executable is crypted itself
> using an anti-debug cipher.

And that still requires a key that is present on the same machine as the
program.  All you have done is made it take a little longer for a bad guy to
see the plain code, but it isn't *that* hard since it is running on a
machine totally under their control.

- From what you have said so far I would strongly recommend that you just use
the regular SQLite encryption extension from the SQLite team and ask the
users for the database password(s) as you open/attach the databases.

> I also need my app could read the same db crypted and also in the decrypted
> format because for some special situations I need to provide my app running
> with the Db decrypted.

I think you misunderstand how the SQLite encryption extension works.  The on
disk storage format for SQLite is a series of fixed sized pages.  The
extension transparently encrypts each page on writing to disk and decrypts
on reading.  To use it you open/attach a database and then provide the
password either via a C API or a pragma.  You just make regular SQLite API
calls and everything just works.

  http://www.hwaci.com/sw/sqlite/see.html

The various other ones pointed out do something similar but since you go via
their API layers they intersperse code to do encryption.  I found it very
hard to work out what they did for encryption since things like the
algorithm used, IV (the usual weakness for home grown implementations) etc
do matter.  They also make other choices:

  http://sqlite.phxsoftware.com/forums/p/2536/9856.aspx

> My doubt about the xor cryptation is that with this solution I can't manage
> a text search into the db using a select but I need to read all data ,
> decrypt it and then make the text search, for this reason a low level
> cryptation would be better in my case.

You misunderstand how this works.  Using a VFS merely changes how the data
is stored on disk.  The upper layers of SQLite do not know or care how data
is stored on disk and they always see the data correctly and in the clear
even if the VFS layer is doing encryption or obfuscation on reads and writes.

- From everything you have said I'd strongly recommend you use multiple
attached databases, prompt users for passwords (ie it is up to them to
ensure security) and use the SQLite SEE extension from the SQLite authors
since you know it works and gets the security right and will continue to do so.

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

iEYEARECAAYFAk0p8GEACgkQmOOfHg372QShCQCgr6BIWQMXJ9ZiJvLEYKe+Js43
oj4Ani/mX7/I4MXtQVRRXBe2WlQKNkZY
=bifl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] fill blank fields

[CDN Mark]

hi there,

thanks Simon and Igor for your help on this a few weeks ago, but I need an 
addition to

UPDATE Aircraft SET CN = '*' where CN = '' or CN is null;

what I need to do is replace blank fields in a specific row, sort of
a double where where statement as in:

UPDATE Aircraft SET CN = '*' where CN = '' or CN is null where (primary key) 
is xyz

What I'm trying to do is update a database, but the users also have an 
automated update system
that apparently if it finds any blank fields in the row, updates the whole 
row, thereby overwriting
what I've replaced.  I thought if I could at least replace the blank fields 
with something, it should
prevent the overwriting.  I don't know which fields are blank, every user 
will be different, so the
plan was to update the fields I want to, insert text/symbol into the blank 
fields and leave the others
alone.  I realize that because I don't know which fields are empty/blank I'm 
going to have to create
a statement for every column I'm not updating to, there are only 10 columns 
altogether and I'll
typically be updating to at least 5 of them.

hope this is clear and mtia
Mark

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

Re: [sqlite] A kind help-request.

[Alok Singh]

Hi Simon, Olaf , Garry,

Great news to you all,now i can able to insert 65k docs in second,
only i had done as per Simon and Garry suggestion
1. i freed the array as per suggestion
2. remove  'cmd.Parameters.Add(myparam) as per Garry suggestion.

*here are summary code :inserting 65k in 4-5 sec*
*
*
//Reading text file 1 in
yarray2= filetext.Split(Environment.NewLine)
//Reading text file 1 in
yarray1 = filetext1.Split(Environment.NewLine)

insert:
Dim tx = cons.BeginTransaction()
For y = j To x
Dim strval
strval = Replace(yarray(y) + vbTab + yarray1(y), "'", "''")
strval = Replace(strval, vbTab, "','")
myparam.Value = strval
cmd.CommandText = "INSERT into " & ticket & " VALUES('" & strval
& "')"
//' REMOVED cmd.Parameters.Add(myparam)
cmd.ExecuteNonQuery()

Next
tx.Commit()
   Array.Clear(yarray, 0, x) ' // FREE ARRAY 1
Array.Clear(yarray1, 0, x) ' // FREE ARRAY 1
tx.Dispose()
j = x + 1
x = x + 1
If x < Frow1 Then
GoTo insert
Else
tx = cons.BeginTransaction()
Dim m As Integer
m = x - 1 + 1
For y = m To Frow1

Dim strval
strval = Replace(yarray(y) + vbTab + yarray1(y), "'", "''")
strval = Replace(strval, vbTab, "','")
myparam.Value = strval
cmd.CommandText = "INSERT into " & ticket & " VALUES('" &
strval & "')"
'cmd.Parameters.Add(myparam)
cmd.ExecuteNonQuery()
Next
tx.Commit()
End If


Thanks you again guys, many thanks...

Regards,
Alok




On 6 January 2011 21:21, Olaf Schmidt  wrote:

>
> "Alok Singh"  schrieb
> im Newsbeitrag
> news:aanlktikhcyfsuybpjtv=+cd4asrddt-9+f7qx_qpq...@mail.gmail.com...
>
> > yeah that's correct Simon, its in 0.6 sec to insert
> > for 10.5K rows with 20 columns (2 files both
> > having 10.5k rows)
>
> That's the timing I would expect, if you'd have used
> Garrys recommendation (to read in the whole file
> into a String first, and then split the string into an
> Array "InMemory", finally followed by an Insert-
> Transaction, which makes use of this 2D-Array).
>
> That's memory-intensive - but "Ok" (and fast) for Testfiles
> with that RowCount (filesize of  your 10.5K-Rows
> testfiles around 4-6MB I'd guess).
>
> Are you sure, that your replies address the person
> you have in mind ... your previous reply was going to
> Garry - and your last reply here was going to me,
> and "both of us" are not Simon (who is a very helpful
> person on this list, no doubt about that... :-).
>
> Olaf
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] More descriptive error

[BareFeetWare]

On 9 Jan 2011, at 2:48pm, BareFeetWare wrote:

>> When I run this via C, using sqlite3_errmsg(), I get the error message:
>> 
>> constraint failed
>> 
>> but when I run it via the sqlite3 command line I get a more descriptive:
>> 
>> SQL error: columns First, Last are not unique
>> 
>> How can I get the more descriptive error via sqlite3_*() routines?

On 10/01/2011, at 3:01 AM, Simon Slavin wrote:

> http://www.sqlite.org/c3ref/errcode.html
> 
> I'm guessing your 'constraint failed' is just the numeric result 19.  Using 
> sqlite3_errmsg() or sqlite3_errmsg16() should get you the long text version.

No. sqlite3_errmsg() is what I'm using, which is giving me the string 
"constraint failed".

Where does the sqlite3 command line tool get the more descriptive error?

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml



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

Re: [sqlite] More descriptive error

[Simon Slavin]

On 9 Jan 2011, at 2:48pm, BareFeetWare wrote:

> How can I get the more descriptive error via sqlite3_*() routines?

http://www.sqlite.org/c3ref/errcode.html

I'm guessing your 'constraint failed' is just the numeric result 19.  Using 
sqlite3_errmsg() or sqlite3_errmsg16() should get you the long text version.

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

[sqlite] More descriptive error

[BareFeetWare]

Hi all,

I am trying to get as much detail on SQL errors generated by SQLite as possible.

Here's a test:

begin immediate;
create table "Names" (First, Last, unique(First, Last));
insert into "Names" values ('Mickey', 'Mouse');
insert into "Names" values ('Mickey', 'Mouse');
rollback;

When I run this via C, using sqlite3_errmsg(), I get the error message:

> constraint failed


but when I run it via the sqlite3 command line I get a more descriptive:

> SQL error: columns First, Last are not unique

How can I get the more descriptive error via sqlite3_*() routines?

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] Disabling foreign key cascading during re-insertion of existing data

[BareFeetWare]

On 01/07/2011 09:19 PM, BareFeetWare wrote:

>> pragma foreign_keys = no;

On 08/01/2011, at 1:26 AM, Dan Kennedy wrote:

> The command above should have disabled all foreign-key constraint checks 
> (including cascades etc.). Check it for typos perhaps.

I thought it would too, but when my particular test wasn't working, I thought I 
had misunderstood what this pragma does. Thanks for confirming that it is 
supposed to do what I originally thought.

Upon further investigation, my particular test failed for another reason. I was 
testing the result of sqlite_prepare for errors, but I didn't allow for some 
errors that don't occur until "runtime", so they don't occur until sqlite3_step 
or similar is called. On of these errors is:

SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */

which was the case here because I was adding a unique constraint to my table 
schema, then inserting the old data, which contains some non unique rows. 
Because I wasn't trapping this runtime error, the transaction proceeded 
through: dropped the old table, created a new table, didn't complain when it 
failed to insert the old data, thus no rows in the new table.

Once I trapped the runtime error and forced a rollback, all is good.

Anyway, problem solved. Thanks for the input.

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] Embarassed Newbie

[Peter]

james630...@aol.com wrote, On 08/01/11 13:45:
> Hi Peter,
>
> Thanks for your response.
>
> At the DOS command prompt I manually changed directory as suggested but to
> no avail.
>
> I originally downloaded all 3 downloads as instructed by the download page.
>   Whilst I was able to unzip the first two folders my attempts to unzip the
> last  results in a message "Cannot complete the compressed (zipped) folder
> extracting  wizard. The compressed zipped folder is empty"
>
> I note that this folder (shell-win32) indicates a size of 126kb.
>
> In addition to the foregoing when I unzipped the second  folder for a
> number of files (1 or 2 maybe) I was informed file name  already exists and 
> asked
> if I wished to overwrite. I said yes.
>
> I hope the above will assist.
> Jim.

I'm afraid I can't help with the wizard messages as I've never tried to 
use the built-in unzip - I've always installed 7-zip since it handles 
.gz and .bz2 files as well.

If it were me I'd delete the folder that you downloaded and unzipped the 
file to assuming there's nothing else in it ;) and start again.

It seems if all you want to do is play with the shell command line 
interface you only need the sqlite-shell-win32... file.


-- 
Peter Hardman

'For every complex problem there is a solution that is simple, 
straightforward - and wrong'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Hui assauts oïl kv

[Patkó Sándor]

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

Re: [sqlite] Read-only main database and ATTACH

[Simon Slavin]

On 9 Jan 2011, at 5:07am, Bill Zissimopoulos wrote:

> I am wondering then whether there are any particular gotchas related to 
> attaching read-only and read-write databases that I should be aware of. I am 
> looking at one of the following ATTACH scenarios:
>   1. Open the read-only database as main and ATTACH the read-write 
> database. This is my preferred solution.
>   2. Open the read-write database as main and ATTACH the read-only 
> database.

Just to clarify your question, are you doing something special to the read-only 
database ?  Your question makes it clear that intend not to make changes to it, 
but does it have special protection,  or is it stored on a read-only medium or 
something ?

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

[sqlite] Read-only main database and ATTACH

[Bill Zissimopoulos]

Hello, list:

I am looking to understand the implications of using ATTACH with databases with 
different read-write permissions.

I have a scenario where I need to access a large database (approx. 512MB) that 
resides in a read-only filesystem. There is also a small read-write database 
with the same schema that resides in a read-write filesystem. The read-only 
database provides the base data used in my scenario, with infrequent data 
updates stored in the read-write database.

Currently I open these two databases in separate connections and the code that 
maintains the connections is responsible for presenting a unified view of the 
data to its clients. For example, this means that the code has to merge query 
results from the read-only and read-write databases, etc. I realize that this 
setup is inelegant (and likely suboptimal) and have been looking to use the 
ATTACH command to create a unified view of the data in SQL rather than C++.

I am wondering then whether there are any particular gotchas related to 
attaching read-only and read-write databases that I should be aware of. I am 
looking at one of the following ATTACH scenarios:
1. Open the read-only database as main and ATTACH the read-write 
database. This is my preferred solution.
2. Open the read-write database as main and ATTACH the read-only 
database.
3. A third alternative?

A few google queries pointed to messages suggesting problems in scenario (1). 
Because I did not find a definitive answer and because my own testing using 
sqlite 3.6.13 did not reveal any problems, I am posting this question.

Thank you for any insights.

Bill

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

Re: [sqlite] Embarassed Newbie

[James630165]

Hi Peter,
 
Thanks for your response.
 
At the DOS command prompt I manually changed directory as suggested but to  
no avail.
 
I originally downloaded all 3 downloads as instructed by the download page. 
 Whilst I was able to unzip the first two folders my attempts to unzip the 
last  results in a message "Cannot complete the compressed (zipped) folder 
extracting  wizard. The compressed zipped folder is empty"
 
I note that this folder (shell-win32) indicates a size of 126kb.
 
In addition to the foregoing when I unzipped the second  folder for a 
number of files (1 or 2 maybe) I was informed file name  already exists and 
asked 
if I wished to overwrite. I said yes.
 
I hope the above will assist.
Jim.
 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] R: R: Crypto lib for Sqlite - suggest required

[Marco Turco]

Hi, thank you for your suggests.

My app is an accounting system.
This accounting system must support the exchange of db between users that
work with the this application 
so I think the only way is to use the same key for all users. This generate
a lack of security of course but anyway my executable is crypted itself
using an anti-debug cipher.

I also need my app could read the same db crypted and also in the decrypted
format because for some special situations I need to provide my app running
with the Db decrypted.

My doubt about the xor cryptation is that with this solution I can't manage
a text search into the db using a select but I need to read all data ,
decrypt it and then make the text search, for this reason a low level
cryptation would be better in my case.

Marco



-Messaggio originale-
Da: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
Per conto di Roger Binns
Inviato: domenica 9 gennaio 2011 05:16
A: sqlite-users@sqlite.org
Oggetto: Re: [sqlite] R: Crypto lib for Sqlite - suggest required

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/08/2011 04:36 AM, Marco Turco wrote:
> Essentially I would avoid that my competitors can look inside the Db 
> structure and import data.

On any machine where the database is used the key will have to exist in
plain form no matter how convoluted the encryption going on.  Your
competitors will always be able to run it under a debugger.

You don't say if the database content is the same for everyone or if it is
different for each user.  If it is the same for everyone then all it takes
is one bad user and the DB contents will be publishable for all.  If it is
different per user then I don't see the problem.

The only way to be "secure" is to provide the data one value at a time via a
web service where you can audit each and every request and not provide
everything at once.  (And anyone receiving that data can still republish
it.)

In these situations it is usually enough just to obfuscate the database so
it isn't immediately apparent that SQLite is in use.  A simple way of doing
that is to write your own VFS that calls the normal VFS but xors all data
that is being read and written.  This is exactly that scheme using Python
and demonstrates how much code it is:

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

If you still want to go down the encryption route then be aware that getting
encryption right is very hard.  More accurately it is trivial to use
encryption, and even easier to use it wrongly.  People usually get it wrong.
 Some random subjects: IV, salt, key strengthening

The SQLite paid for encryption module is a bargain.  It is a one time fee
and you get it forever.  It will always work with SQLite as the versions
change.  It does security right and is always thoroughly tested alongside
SQLite development.  Work out the value of that and the value of your time.

Since you won't really be able to prevent copying the data, what you should
look for is ways of proving that someone has done so.  Obfuscating the
database is a good first start since no one could "accidentally" look at the
contents - they had to put in deliberate effort.  Then throw in some
mountweazels:

  http://en.wikipedia.org/wiki/Fictitious_entry#Motivations_for_creation
  http://en.wikipedia.org/wiki/Trap_street

This would give you enough evidence to sue a competitor.

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

iEYEARECAAYFAk0pNnIACgkQmOOfHg372QT8jwCg0DRP/QhGxrOWo2fWDsYNPZj4
tgUAoM0ReVOOJ9exG8rb9iz4cFqZJOWq
=w+gv
-END PGP SIGNATURE-
___
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