[sqlite] Accessing an encyrpted Sqlite Database
Alrighty, thank you for the compatibility know-how between the two and for such a great product overall. V/r, Ryan -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Tuesday, May 17, 2016 3:20 PM To: SQLite mailing list Subject: Re: [sqlite] Accessing an encyrpted Sqlite Database On 5/17/16, Ryan Irwin wrote: > > --> But it does work with System.Data.SQLite. > Would that entail a mandatory reconstruction of the System.Data.SQLite > project to utilize SEE for compatibility on the C++ side as well? Yes, as it stands now, you have to recompile SDS using MSVC. And that is a lot for many C# developers to deal with as SDS is not especially easy to compile. But we are working on an easier way. -- D. Richard Hipp drh at sqlite.org ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Accessing an encyrpted Sqlite Database
--> But it does work with System.Data.SQLite. Would that entail a mandatory reconstruction of the System.Data.SQLite project to utilize SEE for compatibility on the C++ side as well? Adding to both would be a good idea as Mike N suggested, but for clarity to the superiors, I would like confirmation on the congruent encryption/decryption methods used on the publicly available System.Data.SQLite V/r, Ryan -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Tuesday, May 17, 2016 11:57 AM To: SQLite mailing list Subject: Re: [sqlite] Accessing an encyrpted Sqlite Database On 5/16/16, Ryan Irwin wrote: > > I am aware of the SEE but am lead to believe that it may be > proprietary and would not match the encryption method used by the > DotNet and SQLite.Interop. > SEE is indeed proprietary. (http://www.hwaci.com/sw/sqlite/see.html). But it does work with System.Data.SQLite. -- D. Richard Hipp drh at sqlite.org ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] foreign_key_check mystery
I seem to be getting a foreign key check anomaly. I've checked the constraint mentioned in the error message (and the other one, just in case). Am I overlooking something, or has this been fixed since 3.8.4.1? sqlite> pragma foreign_key_check; Error: foreign key mismatch - "Field" referencing "Segm" sqlite> pragma foreign_key_list(Field); id seq table fromto on_update on_delete match -- -- -- -- -- -- -- -- 0 0 SegmSegName NameNO ACTION NO ACTION NONE 0 1 SegmDbdName DbdName NO ACTION NO ACTION NONE 1 0 Datatypes DatatypeNameNO ACTION NO ACTION NONE sqlite> select count(*) from Field as f where not exists ( select 1 from Segm where Name = f.SegName and DbdName = f.DbdName); count(*) -- 0 In case it's interesting, the FK declaration in the CREATE TABLE for Field is , foreign key (SegName, DbdName) references Segm(Name, DbdName) --jkl
[sqlite] Podcast with Dr Hipp: SQLite history, success and funding
I am a big fan of SQLite and the elegance and simplicity of it and fossil. The documentation and support are excellent. Given that the team takes a long-term perspective, I would prefer if the s/w was structured in a more modular fashion so that various components were usable as libraries e.g. 1) virtual machine 2) lemon 3) fossil I don't need them to be distributed as separate libraries but I think it would make experimentation much easier. In any case, this is a very minor nitpick overall and I continue to be delighted at how useful SQLite continues to be on a day to day basis after almost 10 years of using it. pjjH
[sqlite] Accessing an encyrpted Sqlite Database
System.Data.SQLite I believe has its own SEE implementation embedded. Unsure if its compatible with SEE or not. In order to access an encrypted DB outside of System.Data.SQLite, the same encryption module would have to be available in your C++ implementation. If your project has this requirement, I expect it would be easier/safer to make use of SEE in both System.Data.SQLite and your C++ implementation to ensure the encryption modules are compatible. Thanks, Mike Nicolino -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Ryan Irwin Sent: Monday, May 16, 2016 1:00 PM To: sqlite-users at mailinglists.sqlite.org Subject: [sqlite] Accessing an encyrpted Sqlite Database Sqlite Support, If there is a particular database that was created using DotNet's System.Data.Sqlite, and that database was encrypted with a "Password" argument via the SqliteConnection string. Would it be possible to access that encrypted database by means of the native C++ language using Visual Studio, Windows, and the public version of SQLite? I am aware of the SEE but am lead to believe that it may be proprietary and would not match the encryption method used by the DotNet and SQLite.Interop. Thank you for any assistance provided, Ryan ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Accessing an encyrpted Sqlite Database
On 5/17/16, Ryan Irwin wrote: > > --> But it does work with System.Data.SQLite. > Would that entail a mandatory reconstruction of the System.Data.SQLite > project to utilize SEE for compatibility on the C++ side as well? Yes, as it stands now, you have to recompile SDS using MSVC. And that is a lot for many C# developers to deal with as SDS is not especially easy to compile. But we are working on an easier way. -- D. Richard Hipp drh at sqlite.org
[sqlite] INTEGRITY_CHECK consumes entire system memory
Hello together! I am struggling with a memory issue of SQLite and therefore searching for help. The database of our product has about 70GB of data. When I call the "PRAGMA integrity_check" method, the memory consumption of my system continuously increases until all of my 16GB of RAM are used. The interesting thing is that the memory is not consumed by the processes working on the SQLite itself. Rather it seems that the database file is mapped into memory as you can see from the output of RamMap. https://drive.google.com/open?id=0BwnV5z14WWRSYjZZOVlTWHREd00 https://drive.google.com/open?id=0BwnV5z14WWRSQVpneTZ3Q3I3NzA Interesting for me is that the system does not crash if the entire memory is used (and SQLite does not want to consume more memory). Nonetheless, the big issue is that for new applications no additional memory is available (errors occur when starting new applications) and the system itself also gets really slow!! To debug further into this issue I took the Northwind sample database and extended the Employees table by continuously duplicating the records. This should exclude any issues caused by my database setup (I am using with custom collations etc.). Unfortunately the issue is still there. Reading the documentation I ended up in using the following PRAGMA statements ? but without success. PRAGMA cache_size = 12800; PRAGMA temp_store = FILE; PRAGMA journal_mode = OFF; PRAGMA mmap_size=0; PRAGMA integrity_check; Can anyone please help me further with this problem. My OS is Windows 7 64Bit. I uploaded a compressed version of the database here (178MB): https://drive.google.com/open?id=0BwnV5z14WWRSYUhja0JWWFkxY3c Thanks!!! Best Regards Markus
[sqlite] Accessing an encyrpted Sqlite Database
On 5/16/16, Ryan Irwin wrote: > > I am aware of the SEE but am lead to believe that it may be proprietary and > would not match the encryption method used by the DotNet and > SQLite.Interop. > SEE is indeed proprietary. (http://www.hwaci.com/sw/sqlite/see.html). But it does work with System.Data.SQLite. -- D. Richard Hipp drh at sqlite.org
[sqlite] Sqlite incompatibility with Postgres
On Tue, 17 May 2016 11:09:53 +1000
"dandl" wrote:
> Any disagreement so far?
Full agreement; your description is perfectly sound.
I am quite certain nevertheless that LIMIT has no relational basis.
Nothing based on Order By could. And I'll try to clear up what I meant
by a cursor.
> So the "3" is a perfectly valid argument for a set-oriented theory:
> find a subset S of N tuples with the following test for set
> membership: that each member of S is greater than each member not in
> S when compared by certain attributes, for N = 3. Pure set logic with
> a membership function.
You lost me at "subset S of N tuples". Which relational operator takes
N as an argument?
You could be right vis a vis set theory. But strictly within
relational theory, I'll cede your point when you demonstrate it
relationally. The N in "N tuples" is not to be found in the relation's
extension. Even if we include aggregation, all relational functions
operate on the *values* of the set, or functions of the values of the
set (e.g. min() or avg()). N is not among them.
> > "Order by 1" is always valid.
>
> By analogy, not because they're the same. In order to apply LIMIT 3
> the query parser should require a test of set membership that is fully
> determined for every member. It can do that by either requiring all
> select list columns to appear in the ORDER BY, or by applying other
> constraints such as a unique key.
Unless your point is constrained to the LIMIT operator, you're making a
mistake here. Normally the Select list is a superset of the Order By
list. If they need to be equal for LIMIT to work, that's just one more
nail in LIMIT's coffin, a byproduct of ts nonrelationality.
Given a table T {A,B}, what's wrong with "select A from T
order by B" (even though that's not valid SQL)? The system has access
to the full table, can sort by B and project A. Nothing to do with
indexes or constraints. The query is invalid not because it couldn't
be executed, but because it's undefined: there's no "sort" operator to
apply before "project". Order By, not being a relational function, is
a filter applied to the relational product.
( Yes, "filter"; filters don't alway remove:
$ echo hello | rev
olleh
)
All members of the Order By list must appear in the Select list because
the Select list is input to Order By, see next.
> > > Order By just produces a cursor for convenient traversal of the
> > > results.
>
> Not so. In standard SQL ORDER BY establishes a comparison function
> between tuples and is part of the DECLARE CURSOR syntax, but the
> cursor exists regardless.
Regarding Order By and cursors, I'm referencing CJ Date. The reason
Order By cannot appear in a subquery is that its output is not a table,
but a cursor. Whether Order By "establishes a comparison function
between tuples" is irrelevant; so too does Where. It's the product
that's different.
If you accept that Order By can appear only in the outermost query,
it's clear that it has no relational role at all. It affects only the
order in which the rows are returned to the caller. Other than syntax,
how is that different from a cursor?
> The point of LIMIT is that it is a complete query; the rows can
> be returned in a single network round trip; the result set can be
> discarded.
So it's just a tiny communication optimization? After all, compared to
a network round trip, sorting the result (in order to apply LIMIT
rationally) is usually far more expensive. I bet no study has ever
shown LIMIT to improve performance measurably, not that that would
justify its existence.
> > LIMIT causes harm. Bad results come of bad math.
>
> Disagree. The problem (if there is one) is that it is not
> well-defined.
Then I think you mean you agree! Because LIMIT is nonrelational, it's
*undefined*. We have a long thread here that might be titled "what
should LIMIT do?" There's no debate about the relational operators.
It's only the ad hoc add-ons that present problems, precisely because
they lie outside the theory and provide (unwittingly) idiosyncratic
behavior. Like NULL, LIMIT is unspecified by the theory. Like NULL,
LIMIT is hard to get "right" because each implementation has to make
its own decision about what it means, instead of relying on the math.
--jkl
[sqlite] Sqlite incompatibility with Postgres
On Tue, 17 May 2016 11:09:53 +1000 "dandl" wrote: > > I'll invent here and now to replace LIMIT: nth(). > > The issue is find the "top N". This does not solve the problem. nth() does find "top N". For any query, nth(c, N) returns N rows. It also exposes the arbitrariness of LIMIT. To use nth() correctly for Simon's data select * from T as out where exists ( select 1 from T where out.first = first and out.second = second group by second having nth(second, 2) <= out.second ); produces 4 rows. Deterministically. Unambiguously. The "problem" is to produce 3 rows where, relationally, the only answers have 2 or 4 rows. There is no right answer to the problem because there is no answer to the problem. --jkl
[sqlite] Sqlite incompatibility with Postgres
> > > > first second > > > > - -- > > > > MarkSpark > > > > Emily Spark > > > > MarySoper > > > > Brian Soper > > > > > > > > SELECT first,second FROM members ORDER BY second LIMIT 3 > > First, hat tip to Simon for providing a motivating example. :-) > > The question illustrates what I mean when I say Limit is not "rooted in the > data": in this case, "3" is not in the data, and is not a function of the > data. Having introduced an extraneous arbitrary element, ambituity and > contradiction are inevitable. It's practically the definition of a hack, > right? Does the job, albeit incorrectly. Not so. First: a couple of facts to avoid misunderstanding. 1. Relational theory is a theory of set operations on tuples. Any query that can be expressed as a set operation is valid. 2. In order to perform the familiar operations of restriction (WHERE) and join, scalar operations are allowed on values of attributes (columns). Those operations include: a) compare equal (all types) b) compare greater/less than, if the value is of any ordered type c) expression evaluation, to construct new values of any type. Any attribute that can be compared greater/less for the purpose of restriction can also be used in a query that finds the largest (1 or N) or smallest (1 or N) of that attribute. This is pure relational theory, most of it already known to Codd back in 1972. Any disagreement so far? So the "3" is a perfectly valid argument for a set-oriented theory: find a subset S of N tuples with the following test for set membership: that each member of S is greater than each member not in S when compared by certain attributes, for N = 3. Pure set logic with a membership function. > > I would say that this is an invalid query. As already applies for > > DISTINCT and GROUP BY, the query parser should require that every > > column in the column list should appear in the ORDER BY list. If it > > does not, then the result is indeterminate. > > Order By does not requre Group By, and the Select list is a *superset* of the > Order By list. I'm not sure where you got the notion that the the Select and > Order By sets are equal. "Order by 1" is always valid. By analogy, not because they're the same. In order to apply LIMIT 3 the query parser should require a test of set membership that is fully determined for every member. It can do that by either requiring all select list columns to appear in the ORDER BY, or by applying other constraints such as a unique key. If it does not, then the results of the query depend on information that is not part of the query (ie not deterministic). > David, permit me to elaborate on my indictment of LIMIT. You said > earlier: > > > You can't sort the relation, but you can certainly apply an order when > > performing a query. How else would MIN() work? > > I'm not disputing that. Window functions even require multiple sorts in the > same query. > > Whether or not "LIMIT is perfectly relational", we do know relational algebra > has no Sort operator, and that Order By is never part of an input to a > relational operation (because of course relatational operands have no order). > Order By just produces a cursor for convenient traversal of the results. Not so. In standard SQL ORDER BY establishes a comparison function between tuples and is part of the DECLARE CURSOR syntax, but the cursor exists regardless. In a query retrieved by an external API there is no requirement for a cursor to ever exist (it's undefined, and not required by relational theory). > I'd be perfectly fine with a function I'll invent here and now to replace > LIMIT: nth(). It's a generalization of min(); the > construction nth(C, 1) is equivalent to min(C). You use it this way: > > SELECT first,second > FROM members > where second < nth(second, 2) > > That query is based in the data. It's unambiguous. Given Simon's input, it > produces 2 rows; with "< 3" it produces 4 rows. It can be used without Order > By (for the same reason min() can). While it > *implies* a sort, it doesn't require one (because indexes), as LIMIT does. > And, like min() and unlike Order By, it can be used in a subquery. The issue is find the "top N". This does not solve the problem. > LIMIT is a hack. It's an "obvious" extension to SQL, so simple it needn't > even be part of it, because the program reading the rows from the DBMS can > always stop wherever it wants. Simple things are always implemented freely - > - even if unnecessary or misbegotten, simply because they're easy to do and > understand -- and LIMIT was > no exception. I disagree. The point of LIMIT is that it is a complete query; the rows can be returned in a single network round trip; the result set can be discarded. Ironically, though, seemingly simple things are very > hard, sometimes impossible, to explain mathematically. In that way, LIMIT > shelters under the same roof as NULL and SQL's use of bags instead of sets.
[sqlite] Podcast with Dr Hipp: SQLite history, success and funding
Please take this to another thread!
> On 16 May 2016, at 23:16, Objective C wrote:
>
> Thank you for your answer,
> here is the code i used to backup my SQLite database :
>
> var source = new SQLiteConnection("Data Source = MyDB.db ; Version = 3;");
> var destination = new SQLiteConnection("Data Source = NewDBBackup.db;
> Version = 3;");
>
> source.Open();
> destination.Open();
>
> source.BackupDatabase(destination , "main", "main" , -1 , null , 0 );
> source.Close();
>
> 2016-05-16 22:05 GMT+00:00 Kees Nuyt :
>
>> On Mon, 16 May 2016 19:14:01 +, Objective C
>> wrote:
>>
>>> Hi sir,
>>> In fact, i have an issue with SQLite database Restore using c#
>>
>> That's not what this discussion thread is about, it would have
>> been better if you sterted a new thread, but anyway.
>>
>>> i'm coding a button which can restore an existing
>>> SQLite database with '*.db*' extension.
>>
>>> i wonder if you can guide me please,
>>
>> Can you describe what you already tried, what happened, and what
>> made you think you didn't succeed?
>>
>> There are (at least) two methods:
>> 1- use the SQLite backup API
>> See: www.sqlite.org/c3ref/backup_finish.html
>>
>> 2- copy the backup database over the database
>> using file system operations
>>
>> In the latter case you'll have to make sure
>> - the backup database is consistent and does not
>> have a 'hot' journal
>> - the database you are going to overwrite is not
>> used by any databse connection
>> - the journal of the database you are overwriting
>> (if any) is removed.
>>
>>> i'll ber very grateful
>>> Sincerely yours
>>> Hashim
>>
>> --
>> Regards,
>>
>> Kees Nuyt
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Podcast with Dr Hipp: SQLite history, success and funding
On Mon, 16 May 2016 19:14:01 +, Objective C wrote: > Hi sir, > In fact, i have an issue with SQLite database Restore using c# That's not what this discussion thread is about, it would have been better if you sterted a new thread, but anyway. > i'm coding a button which can restore an existing > SQLite database with '*.db*' extension. > i wonder if you can guide me please, Can you describe what you already tried, what happened, and what made you think you didn't succeed? There are (at least) two methods: 1- use the SQLite backup API See: www.sqlite.org/c3ref/backup_finish.html 2- copy the backup database over the database using file system operations In the latter case you'll have to make sure - the backup database is consistent and does not have a 'hot' journal - the database you are going to overwrite is not used by any databse connection - the journal of the database you are overwriting (if any) is removed. > i'll ber very grateful > Sincerely yours > Hashim -- Regards, Kees Nuyt
[sqlite] using cerod/sqlite with JDBC
Hi , yes, this is perfectly understood. but my quesion is around the CEROD extension. the sqlite driver does not support DBs which are created with CEROD based compression. thanks Tal > On May 16, 2016, at 10:53 PM, Klaas Van B. wrote: > > Did you read, understood and used all instructions you can find here? > > http://www.tutorialspoint.com/sqlite/sqlite_java.htm > > Tal Tabakman wrote Sun, 15 May 2016 09:41:39 -0700: > >> ... open an sqlite DB , compressed with CEROD, using java?s sqlite JDBC >> connection ? > > Kind regards | Vriendelijke groeten | Cordiali saluti, > Klaas `Z4us` van Buiten V, Experienced Freelance ICT-Guy > https://www.linkedin.com/in/klaas-van-buiten-0325b2102
