[sqlite] foreign_key_check mystery
On 2016/05/19 12:51 AM, James K. Lowden wrote:
> On Wed, 18 May 2016 19:06:30 +0200
> R Smith wrote:
>
>>> I'm not convinced the requirement that the referenced columns be
>>> unique is justified
>> How do you see a parent-child relationship possible where the parent
>> is not Unique?
> I think I can convince you that uniqueness is a good rule of thumb, but
> that enforcing it ahead of RI is undesirable. But the price of making
> me think about it is reading a long answer.
Serves me right :)
Thank you for elaborating.
> The referenced table represents the domain of the foreign key
> relationship. When we say
>
> foreign key R(A) references S(B)
>
> we're saying every value in A appears in B. We're not saying anything
> about B: not its type, not its cardinality. The statement describes A
> only.
>
> We're also saying something logical, not enforced by the DBMS: that R
> is a part of S. S can exist without R, but R without S is
> meaningless. But that's a modelling question, and I can't think of
> another SQLite feature that enforces any aspect of database design.
> Why start here of all places? Was that even the intention?
>
> So lets's look at my table T and its FK
>
> , foreign key (SegName, DbdName) references
> Segm(Name, DbdName)
>
> as it happens, Segm was defined with
>
> , Nametext not NULL primary key
> , DbdName text not NULL
>
> One day, Segm may have a 2-column PK: primary key (Name, DbdName).
> But today, Name uniquely identifies it. (Note that therefore {Name,
> DbdName} also uniquely identifies it!) T extends Segm, and requires
> that its {SegName, DbdName} pair appear in Segm.
Ok, I'm convinced, but for the assumption that you've suggested
non-uniqueness before, which is of course not the case.
I suppose it boils down to suggesting that a DB accepts in a Foreign Key
relation, as a parent, any combination of references which is unique by
virtue of combined uniqueness, or if any one (or more) of the
constituent references in itself is unique. (Since if A is unique, it
follows that A|B|C|... is unique for any and all possible values of B,C,...)
I think SQLite has an additional difficulty in that it needs to have a
KEY Index to implement the mechanism of cascading changes, or at least,
testing whether the changes require cascading to children. This (if it
even is accurate) is a peculiarity or implementation detail though.
>
> Let's assert that's *correct*, even though Segm.Name is unique today.
> What is *wrong* with saying the FK relationship refers to more columns
> than are in the domain table's PK? After all, the above assertions are
> still true:
>
> 1. T{SegName, DbdName} must be in Segm{Name, DbdName}
> 2. T extends Segm
>
> Even more -- though not required IMO -- Segm{Name, DbdName}is unique
> (because Segm{Name} is unique).
>
> You could probably get me to agree that the relationship is anomalous.
> I suppose if Segm.Name is unique, the FK should refer only to it.
>
> In general, though, not every domain is manifested in a table. One
> might have these PKs:
>
> S {A, B}
> R {B}
>
> Now let me assert that R extends S: that is, for any S there could be
> an R. The rule: If an R exists for S, there is only one,
> regardless of A.
>
> If that's logically incoherent, I don't understand why.
>
> Remember, there could be a missing domain table, say, T {B}, and the
> real rule would be that for some T there must be an R. But T is
> missing because it has no non-key attributes, and S serves in its
> stead.
>
> That's where "not sure justified" comes from. foreign_key_check
> nudges the user in the right direction most of the time, and as a
> linter I have no argument with it. However, as implemented, "foreign
> key mismatch" prevents reporting of a genune error, namely "FOREIGN KEY
> constraint failed". By my lights that's putting a design
> recommendation before a data error, definitely cart before horse.
>
> I hope that one day FK enforcement becomes a property of the database,
> not of the connection. If that comes to pass, this issue needs careful
> consideration. As things stand, I think it might be better if "foreign
> key mismatch" were demoted to a warning.
Agreed.
Cheers,
Ryan
[sqlite] Sqlite incompatibility with Postgres
> Restriction is applied to the values of the tuple. The number of tuples
is
> not a value of the tuple.
No, I can't agree. Restriction is a membership test, a function on members:
should this tuple be included in the result set or not? Cardinality of a set
is a second order function on the members of the set, obtainable simply by
examining all the tuples in the set at the same time. There is no a priori
reason not to use cardinality in a membership functions.
> Neither of us is stupid, David. I've boiled this down to something very
> simple. If you look at it algebraically, I think you'll come to the same
> conclusion I have.
AS far as I'm concerned I have already done so, but it seems we reach
different conclusions. To settle that we either need new facts or a higher
authority.
> I wouldn't persist except that you're worth convincing. Andl holds
promise,
> and seeks higher ground than SQL holds. Insofar as possible, if I can I
want
> to help you get it right.
Much appreciated. Really.
> > For this query: calculate the average of that set of numbers after
> > excluding the 2 largest and 2 smallest values. Again, a pure set
> > operation.
> >
> > A reasonable solution would be to use two subqueries with ORDER BY
> > ASC/DESC and LIMIT 2, followed by an aggregation.
>
> Sadly, no. If we're talking about a "pure set operation", and the set is
{1,
> 1, 2}, the "two smallest" is {1, 2} but LIMIT 2 would yield {1, 1}.
No, that isn't a set, it's a multiset. A set has no duplicates.
> Here again, my putative nth() function *does* give the right answer,
simply
> because it's a function of the values, and not of the number of values.
>
> > > There's no debate about the relational operators.
>
> By which I meant: there's no debate about what they do.
>
> > You might be surprised to learn that there is considerable academic
> > uncertainty as to exactly which operators should be included.
>
> There's no uncertainty. Some operators are defined in terms of others.
No
> suprise: even under De Morgan you don't need OR if you have NOT and AND.
The
> redundancy makes the language more expressive.
Not what I meant: that only covers the conjunctive queries. To that you can
successively add negation, recursion, higher order functions and
fixpoint/while. Each of those allows operations that others do not, but
there is disagreement about which should be considered 'relational'. De
Morgan won't help you there.
> LIMIT doesn't belong in this part of the discussion, btw, because it is
not
> defined relationally.
Yes it is. But it does require a second order function.
> > For example, is CTE RECURSIVE relational, or not?
>
> http://wiki.epfl.ch/provenance2011/documents/foundations%20of%20databases-
> abiteboul-1995.pdf
>
> Cf. Chapter 14. Adding recursion changes the language. It adds power; if
> memory serves permits answering second-order queries.
Thank you for the reference -- I didn't have that one. I'm familiar with the
material.
No, recursion (similar to fixpoint/while) makes the language Turing
Complete. Second order functions are not enough.
> > What about LEAD and LAG in the windowing functions? What about string
> > concatenation as an aggregation operator?
>
> AFAIK there's no debate about those, either. They can be expressed in
terms
> of simpler operations, and exist for convenience, such as it is.
This is the same debate as for LIMIT, because they rely on ordering. Indeed
it's possible to construct one from the other with something like this:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases
) a WHERE row > 5 and row <= 10
You can't do SQL windowing without some kind of ordering comparison, but the
use of ordering in making a selection does not automatically make the query
non-relationally. The result is still just a set of tuples, no matter how
you choose them.
BTW this is one very useful extension for Sqlite, which is otherwise full
book on the relational hierarchy.
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
[sqlite] INTEGRITY_CHECK consumes entire system memory
Hi, please check if this helps: http://serverfault.com/questions/325277/windows-server-2008-r2-metafile-ram-usage The provided script (setfc.ps1) did help for us and thus we implemented the suggested SetSystemFileCacheSize calls in our application. Regards, dg. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Markus Ecker Sent: Tuesday, May 17, 2016 3:56 PM To: sqlite-users at mailinglists.sqlite.org Subject: [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-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WITHOUT ROWID
Hello, I wonder if there are any drawbacks/things to be aware of, etc. for tables created "WITHOUT ROWID". I am considering using such tables in another project. Thanks. Regards, Ertan K???ko?lu
[sqlite] WITHOUT ROWID
On 5/19/16, Ertan K???ko?lu wrote: > Hello, > > I wonder if there are any drawbacks/things to be aware of, etc. for tables > created "WITHOUT ROWID". I am considering using such tables in another > project. Experiment. Run your application with WITHOUT ROWID and measure performance (where "performance" is defined by whatever is important to you) then remove the WITHOUT ROWID and run the same experiment. Decide which works best for you. All of the application code should work the same either way, so other than removing the "WITHOUT ROWID" text from the "CREATE TABLE" no other changes are required. -- D. Richard Hipp drh at sqlite.org
[sqlite] Podcast with Dr Hipp: SQLite history, success and funding
This link is a presentation on 'we wish git had this' https://www.youtube.com/watch?v=ghtpJnrdgbo by DRH. This is the fossil page on the subject of comparing vs git. http://www.fossil-scm.org/xfer/doc/trunk/www/fossil-v-git.wiki That said, at my workplace we use git. How else can Windows developers get to practice text editing in vi? :) regards, Adam On Wed, May 18, 2016 at 9:42 PM, J Decker wrote: > On Wed, May 18, 2016 at 2:39 AM, Cecil Westerhof > wrote: > > I would be interested what you find wrong about Git and is better in your > > version control system. > > > > git blows; monotone forever! > > > -- > > Cecil Westerhof > > ___ > > 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 > -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1
[sqlite] Podcast with Dr Hipp: SQLite history, success and funding
To add to Tony's comment... Where would you find that the developers and the actual owner of the software would answer questions to newbies? I remember when I started using this software, a complete newbie to SQLite, I asked a bunch of questions and some of them, the first to respond was Dr. Hipp. I also believe that the support in this list is amazing, and also carries some weight as to why folks use SQLite. I have been using computers since 1982 and this is the best software that I have ever used, and the support I have ever received. Yes, I paid for some software also. So, thanks Dr. Hipp and the gang for the wonderful opportunities you, and the gang provide. As we say in Spanish, muchas gracias y que Dios los bendiga. jos? On 2016-05-15 10:05, Tony Papadimitriou wrote: > 1. Why SQLite is popular. > The answers to those question mentioned in the podcast may be good ones but I > think the main reason is that it's free. Completely, unmistakably, free. Necessary but not sufficient. It's free, and the license is as non-restrictive as it is possible to be. Certainly true! However, there are tons of free & liberally licensed software out there, many (most?) of which are failures in terms of public acceptance. So, even these two alone do not seem to be entirely sufficient. But SQLite has one greater attribute. It comes with a proven commitment of EXCELLENT support & maintenance. A true quality product. Bugs are killed practically instantly after being discovered, and new features added on a regular basis. You rarely get this kind of support even from paid software. (Many open source projects have bugs waiting for months or years for someone to be bothered to fix, often driving people away!) To sum it up, a big thanks to Richard and his team! ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WITHOUT ROWID
On 2016/05/19 2:50 PM, Richard Hipp wrote: > On 5/19/16, Ertan K???ko?lu wrote: >> Hello, >> >> I wonder if there are any drawbacks/things to be aware of, etc. for tables >> created "WITHOUT ROWID". I am considering using such tables in another >> project. > Experiment. Run your application with WITHOUT ROWID and measure > performance (where "performance" is defined by whatever is important > to you) then remove the WITHOUT ROWID and run the same experiment. > Decide which works best for you. > > All of the application code should work the same either way, so other > than removing the "WITHOUT ROWID" text from the "CREATE TABLE" no > other changes are required. > True, though be aware that some SQLite functionality requires tables to have rowids, such as AUTOINCREMENT. Also, SQlite engines prior to 3.8.2 will not work and will report a malformed database. Any table using WITHOUT ROWID must have a primary key (this is otherwise not a requirement). Primary keys cannot have NULL values in WITHOUT ROWID tables - this is perfectly possible otherwise, though most of us feel it should never be possible anyway. If you ever use the last_insert_id type of API's in SQLite, it won't work on a WITHOUT ROWID table. (But you shouldn't anyway...) On-Update callbacks from the API doesn't work right (or at all - not tested recently) in a WITHOUT ROWID table The new SESSION extension requires ROWID tables ^[needs citation]. (I think - not tested yet) I think the SQLDIFF might also, but now I'm just guessing. My point is, check out all the kinds of things you might do in SQLite, make sure they can be done with WITHOUT ROWID tables. If they can, the change is worth it - the WITHOUT ROWID optimization is quite faster on large key lookups and nice and lean in size (no need for rowid translation or added rowid key data per table). Everywhere I use it, it works a charm, and if ever I find I want to use some functionality on a table that requires a rowid, the changeover is quick and painless, any DB admin system out there would probably have a 1-click solution to do so. Best of luck, Ryan
[sqlite] Sqlite incompatibility with Postgres
On Thu, 19 May 2016 10:29:48 +1000 "dandl" wrote: > > Restriction is applied to the values of the tuple. The number of > > tuples is not a value of the tuple. > > No, I can't agree. Restriction is a membership test, a function on > members: should this tuple be included in the result set or not? > Cardinality of a set is a second order function on the members of the > set, obtainable simply by examining all the tuples in the set at the > same time. There is no a priori reason not to use cardinality in a > membership functions. That's an interesting perspective. If you're dealing with genuine sets, and you define your language in terms of second-order operations, then something like LIMIT could be included. Would have to be, I guess. But that's not what SQL is, or what LIMIT is. You were rather dismissive of my nth() function, but that approximates what LIMIT does (approximation is all that's possible) with a first-order operation. BTW, I still think you're agreeing with me. I'm insisting on using the "values of the tuple", implicitly restricted to first-order operations. Cardinality, as you say, as a second order *function*, hardly a "value". But at least I understand your argument now. > To that you can successively add negation, recursion, higher order > functions and fixpoint/while. Each of those allows operations that > others do not, but there is disagreement about which should be > considered 'relational'. OK, I see. It's fitting that the debate is about the definition of the set of relational operators. I'm conservative in that regard. I'm wary of the complexity that higher-order operations bring to the language. Each higher level brings (I suspect) more complexity than the prior, while solving fewer new problems. I think recursion is a good extension, and a good example. It permits the expression of hierarchies. It's indispensable ... for maybe 1% of queries. I guess you could convince me it makes SQL Turing Complete, but that's a very dense thicket. Recursive structures are useful. If they could be manipulated without making the language Turing Compiete, I'd consider that a plus. > Thank you for the reference -- I didn't have that one. I'm familiar > with the material. You're welcome, and it shows. I think we've managed to hash out some agreement: 1. Second order functions are "relational", or can be, depending on one's definition. We have support for them already in SQL. 2. Limit, as currently implemented, lies outside the theory because it doesn't operate on sets. Regards, --jkl
[sqlite] Sqlite incompatibility with Postgres
On 2016-05-19 9:17 PM, dandl wrote: > Every aggregation function is at least second order: a function that applies > a function to the set. So for MIN the function is 'less than', for SUM() the > function is 'plus' and so on. In Andl aggregation functions are provided by > fold(), which takes a function as an argument. Actually, MIN still is fundamentally a first-order itself. The dyadic function call "x min y" returns either x or y depending on how they compare. The list form is then repeated application of the binary min(). This is directly comparable to your example of list plus/sum which is repetition of the dyadic "x + y". List MIN is NOT a repeated application of "x less than y". -- Darren Duncan
[sqlite] Using LSM and Memory Mapped files -- Question about LSM_CONFIG_MMAP
I am interested in using the LSM (https://www.sqlite.org/src4/doc/trunk/www/lsmusr.wiki) The characteristics of my application is that I would like to enable the memory-mapped file feature so that I can offload data in memory to disk temporarily.I don't care about data-recover after my application shuts down or it crashes. In fact, the application will create a new database each time it starts up. The reason to do this is to be able to address hundreds of gigs of data in a key/value store method. We would it put it in RAM but that would require a lot of RAM.By putting the data on disk, we can work with very large sets of data. I care a lot about performance. If I had the RAM, all of this would be in RAM (non-persistent and transien) My question is, if I have LSM_CONFIG_MMAP enabled, and on a 64-bit system I believe it's enabled by default, what happens if I also turn off LSM_CONFIG_SAFETY and LSM_CONFIG_USE_LOG. Would that not make things a lot faster? I don't need data to be written to disk right away.. In fact, I would be okay if data were only written to disk when there the memory that is occupied by the data has to be reused - because persistence is not a factor. Anyone use LSM for this type of a use case? *** Confidentiality Notice: This e-mail, including any associated or attached files, is intended solely for the individual or entity to which it is addressed. This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice of its status. Please notify the sender 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 other person.
