Re: [sqlite] Noticed crash on windows

[Oliver Schneider]

On 2014-03-25 18:52, Larry Brasfield wrote:
> Going in, it is best to not read too much into your code running
> "pretty well" on a Unix platform.  Have you run your code with
> Valgrind (or equivalent) on that platform?  If not, doing so may help
> you uncover a bug which affects behavior more badly on Windows with
> your present build.
DrMemory also exists for Windows and can be used there, it uses the
DynamoRIO instrumentation framework. I am not sure about the state of
affairs of AddressSanitizer with GCC on Windows, but that could be
another option with a smaller performance penalty.

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

Re: [sqlite] Error with new HTTPS configuration on sqlite.org (missing certificate chain up to CA)

[Oliver Schneider]

Hey folks,

the problem still exists (or rather again). Shortly after my initial
email someone decided to turn the redirect to HTTPS off again, but now
it's back, only the certificate chain hasn't been fixed.

OpenSSL gives me:

$ openssl s_client -connect www.sqlite.org:443
CONNECTED(0003)
write:errno=104

... whereas with other services that have a full certificate chain you
get to see that. E.g. with google.com:

$ openssl s_client -connect www.google.com:443
CONNECTED(0003)
depth=1 /C=US/O=Google Inc/CN=Google Internet Authority
verify error:num=20:unable to get local issuer certificate
verify return:0
---
Certificate chain
 0 s:/C=US/ST=California/L=Mountain View/O=Google Inc/CN=www.google.com
   i:/C=US/O=Google Inc/CN=Google Internet Authority
 1 s:/C=US/O=Google Inc/CN=Google Internet Authority
   i:/C=US/O=Equifax/OU=Equifax Secure Certificate Authority
---
Server certificate
-BEGIN CERTIFICATE-
MIIDgDCCAumgAwIBAgIKMbrKwgABAACPzDANBgkqhkiG9w0BAQUFADBGMQswCQYD

... well, you get my drift.

The reason this works with Firefox is, because it has the certificate
chain for GoDaddy built-in and therefore is able to "fill the blanks".

Google Chrome and Internet Explorer use - on Windows that is - the
certificate store which, at least on my Windows 7 installations,
contains the root certificate from GoDaddy.

rekonq on Kubuntu fails:

> The certificate authority's certificate is invalid
> The root certificate authority's certificate is not trusted for this purpose
> The certificate cannot be verified for internal reasons

... just like Opera:

> Secure connection: fatal error (40)
> 
> https://www.sqlite.org/
> 
> Failed to connect to server. The reason may be that the encryption methods 
> supported by the server are not enabled in the security preferences.

... and OpenSSL.

// Oliver

PS: you can also use instead of OpenSSL something like:
https://www.digicert.com/help/ ... result is:

> The certificate is not signed by a trusted authority (checking against 
> Mozilla's root store). If you bought the certificate from a trusted 
> authority, you probably just need to install one or more Intermediate 
> certificates. Contact your certificate provider for assistance doing this for 
> your server platform.

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

[sqlite] Error with new HTTPS configuration on sqlite.org (missing certificate chain up to CA)

[Oliver Schneider]

Hi,

using Opera I receive the following error:

> Could not connect to remote server
> 
> You tried to access the address https://sqlite.org/lang_attach.html, which is 
> currently unavailable. Please make sure that the web address (URL) is 
> correctly spelled and punctuated, then try reloading the page.
> 
> Secure connection: fatal error (40)
> 
> https://sqlite.org/lang_attach.html
> 
> Failed to connect to server. The reason may be that the encryption methods 
> supported by the server are not enabled in the security preferences.
> 
> Please note that some encryption methods are no longer supported, and that 
> access will not be possible until the website has been upgraded to use strong 
> encryption.
> Make sure your internet connection is active and check whether other 
> applications that rely on the same connection are working.
> Check that the setup of any internet security software is correct and does 
> not interfere with ordinary web browsing.
> If you are behind a firewall on a Local Area Network and think this may be 
> causing problems, talk to your systems administrator.
> Try pressing the F12 key on your keyboard and disabling proxy servers, unless 
> you know that you are required to use a proxy to connect to the internet. 
> Reload the page.

I don't with Firefox. So I decided to investigate a bit more with
Firefox. Turns out the latter uses a built-in version of the CA cert.
This suggests that your server doesn't hand out the complete certificate
chain.

Since your web server doesn't provide information about what's running.
I'm unable to suggest a solution other than reviewing the configuration.

However, you can find the GoDaddy CA bundles here:

  

// Oliver

PS: re-sending from the subscribed email address.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Suggestions for approximate date

[Oliver Schneider]

Hello,

I have a decision to make about how to store dates that may not be
entirely accurate inside an SQLite DB. There are two options I came up with:

 1. store "exact" date plus (in separate column) value for accuracy
 2. store date range corresponding to original accuracy

The accuracy can be exact date, only month and year, +/- 1 year, +/- 10
years, +/- 50 years.

I reckon for searching the second option could be better. Does anyone
here have any better ideas? I'd go for the Julian Day stored as REAL in
either case.


Thanks,

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

[sqlite] Fwd: Re: Licence

[Oliver Schneider]

Re-sending because the list still complains about PGP signatures.

 Original Message 
Subject: Re: [sqlite] Licence
Date: Tue, 22 May 2012 20:55:06 +

On 2012-05-22 20:48, Net Surfer wrote:
>> Just a quick question. To sell an application that uses SQLite, do i need
>> to include in the software any reference to public domain Licence?

As far as I understand Public Domain isn't a license but pretty much
waiving any rights to the code. Often accompanied by a disclaimer.

See 

So in this light it seems to be more a matter of courtesy than of
obligation.


Hope this helps,

// Oliver


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

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Oliver Schneider]

On 2012-05-02 14:06, peter korinis wrote:
> Thank you all. 
> Look like I'm stuck with the CLI though I have contacted Nucleon software
> support ... tried CLI yesterday but need more practice.
> Is there a good reference book you would recommend for SQLite?
Absolutely. The one by Mike Owens was a great read. The first two or
three chapters explain SQL as a whole. Even though I _had_ to work with
MySQL and stuff before, it was this book that actually "enlightened" me
w.r.t. to some of the cool features SQL (RDBMSs) give you.

Name: "The Definitive Guide to SQLite"

There is another one from O'Reilly whose author is also on this list. I
think I would have found this one harder to grasp without reading the
Owens book before, though.

// Oliver

PS: re-sending after the mailing lists chokes on my message with S/MIME
signature.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[Oliver Schneider]

On 2012-05-01 20:41, Baruch Burstein wrote:
> It is already wrapped in a transaction.
> I seem to remember seeing somewhere that the .import command doesn't
> understand escaping, e.g.
> 
> "one","two,three"
> 
> will get imported as
> 
> "one" | "two | three"
> 
> (the quotes are part of the data, and the second column was split into two
> by the comma)
> Just a point to be aware of.
That could be mitigated by writing a small script that "converts" the
CSV contents line-wise to SQL-statements, right?


// Oliver

PS: re-sending after the mailing lists chokes on my message with S/MIME
signature.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] VACUUM can actually increase the DB file?

[Oliver Schneider]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

Hi there,

just a few minutes ago I ran a VACUUM on a DB file and the size before
was 2089610240 and afterwards 2135066624. Is this normal?


Thanks,

// Oliver
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (MingW32)

iQEcBAEBCAAGBQJMk/T6AAoJEJm+Ui/QYLFPNP4IAIGnbFh2kcPjlZgGpuSCnELO
1YaXtl3vmyMHSXjOykLdMd7hVgSNY3BDvzk2Lp4hWXGmDEgU3Zu7GBqKoJVadRzx
E8Q1IMLEebvxnoN3CUg3m2+aATUNYkxVZGNzRdFq2H84xFb8iZ42tRfkE11bIQsj
KdPaFbQI/xyUvv2AzvemvGIaeHtWjgn1Qjk5dM2Z+BWaQ4rIC8+OaaHPif1rl7Pv
oRPyjN2+mdrfQ1GdpPz+4OOmbAGWBbZtChUpjx29NLlz1Fs9bz/EBs46rhIirfnN
w/QeMaqBCkrxkQ1lE99z5yXgRkLX09weHO4db0O+lGh+Xt/MXGpHwPZ87HjrBfU=
=J/4p
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Are triggers the best way to store the modification time?

[Oliver Schneider]

On 2010-09-09 00:15, Igor Tandetnik wrote:
> This updates all the rows in the table, not just the one for which the 
> trigger was fired. You want something along the lines of
> 
> UPDATE file SET modified=julianday('now')
> where file.rowid = new.rowid;
Ouch. Thanks for shedding some light on this mystery.

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

Re: [sqlite] Are triggers the best way to store the modification time?

[Oliver Schneider]

Thanks for your reply.

> Can't you just include the modified value within your UPDATE or
> INSERT statement?  Or is the modified column in a separate table?
Guess I'll do that. SQLite is just so convenient even from command line,
that I thought it'd be better to automate that part to figure out if the
data set was tampered with.

> It sounds like your triggering on ANY table update, not the specific
> table you're interested in.
Hmm, strange. These are the triggers I used:

CREATE TRIGGER IF NOT EXISTS file_change AFTER UPDATE OF hash,name,path
ON file
  BEGIN
UPDATE file SET modified=julianday('now');
  END;

CREATE TRIGGER IF NOT EXISTS file_insert AFTER INSERT ON file
  BEGIN
UPDATE file SET modified=julianday('now');
  END;


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

[sqlite] Are triggers the best way to store the modification time?

[Oliver Schneider]

Hi,

I'm having a table of file names along with hashes and so on. In order
to make sure that we keep a record, I had put a trigger on UPDATE and
INSERT which would set the column 'modified' (REAL, Julian day) to the
time of the change. However, this slows down any INSERTs (even those
seemingly unrelated to this table) considerably.

Is there a better method to achieve the same, i.e. keep track of when a
row was last changed?


Thanks,

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

Re: [sqlite] Question concerning foreign keys across databases

[Oliver Schneider]

Hi Pavel,

I think I guess that I understand what your point is.

On 2010-08-18 21:18, Pavel Ivanov wrote:
> This is exactly the reason why it's not logical action: SQLite will
> check constraint only in those places where it knows that something is
> changed and constraint can be violated. And it shouldn't re-check it
> in million other places where nothing seems to be changed...
> To catch up with reason of "why" consider these scenarios:
> 1) You connected to main, attached texts, made foreign constraint and
> inserted some records. Then you created other connection to texts
> directly and deleted all referenced records. How should SQLite know
> that they cannot be deleted?
> 2) You made another connection to main and connected another database
> as "texts". How foreign keys should be enforced in this situation?
I see, so the guarantees made by constraints are somehow just don't fit
with the feature of using more than one database at a time.


Thanks for pointing it out,

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

Re: [sqlite] Question concerning foreign keys across databases

[Oliver Schneider]

Hello Pavel,

thanks for your reply.

On 2010-08-18 20:39, Pavel Ivanov wrote:
>> Summary: how can I use foreign keys across database boundaries? Is it at
>> all possible?
> 
> No. It's logically incorrect action, so it's impossible. If you want
> consistency of your tables to be automatically checked by database
> engine you need to allow that engine to see those tables at all times.
Sorry to disagree, concerning the logic. But I can't see why an error
about texts.text instead of main.text would make such a difference. Why
is one more logical than the other? In either case the insertion would
fail, since it can only be checked when it happens and it probably will
not be checked again afterwards.

If SQLite supports attaching multiple databases it would be natural to
support foreign tables across them.

The only possible catch I see is if the references are two-way (or
more). Then it could really get messy, although the principle of failing
if the table does not exist would still apply. I guess I'll have to
dedicate a night or two to read over the code to get an idea about the
"why".

> For SQLite it means that you need to keep those tables in one
> database. If you insist on keeping tables in different databases then
> your application should check consistency itself because it's the only
> one knowing how to keep track of different files.
I guess what I'll simply dump the data from the "static" DB (which will
only be modified manually) and import it right before creating the new
tables.


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

[sqlite] Question concerning foreign keys across databases

[Oliver Schneider]

Hello,

when trying to use foreign keys I'm running into a problem. It could be
that I hit some general limitation, but then again the error that I'm
seeing could also be issued if the database with the table of the
referenced foreign key is not attached.

Here's what I'm trying to do.

I have some meta-data that rarely changes (e.g. text strings with
accompanying IDs) but is rather big. Suppose we have:

-
pragma foreign_keys = on;

create table texts ( id integer primary key autoincrement, str text
unique not null on conflict fail );

/* With the following data */
insert into texts(str) values('foo');
insert into texts(str) values('bar');
insert into texts(str) values('baz');
-

I'll call this table {texts} from now on. It's contained in file1. Now
comes some more variable data where I would prefer to use one database
per "data set", so I create another database contained in file2 (which
becomes {main}):

-
pragma foreign_keys = on;

attach database file1 as text;

/* This fails right away:

create table main.result ( id integer primary key autoincrement, strid
integer, details text default null, foreign key(strid) references
text.texts(id) );

... so I use: */

create table main.result ( id integer primary key autoincrement, strid
integer, details text default null, foreign key(strid) references
texts(id) );

/* Which succeeds for the moment, until ... */

insert into result(strid,details) values (1,'some foo value');
-

This last line gives me "Error: no such table: main.texts", which, I
suppose, is due to the constraint check for the foreign key "strid".

The given error message obviously makes sense. However, since the
constraint check is done upon insertion referencing an attached database
shouldn't really pose a problem, right? At worst - e.g. if I hadn't
attached "file1 as text" - I would get the that error with a slight
variation: "Error: no such table: text.texts" ...

Summary: how can I use foreign keys across database boundaries? Is it at
all possible?


Thanks,

// Oliver

PS: I'm using SQLite version 3.6.22
PPS: Tried to send it with PGP/MIME signature, but it didn't get through
according to the list archive, so sending without signature.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users