Re: [sqlite] pragma vs select for introspection
On 11 Dec 2010, at 2:28pm, BareFeetWare wrote: > On 11/12/2010, at 12:58 PM, Petite Abeille wrote: > >> I'm in the opinion that a comprehensive data dictionary, accessible directly >> from SQL, is the way to go. > > Yes, yes, yes :-) > > I think there's an SQL standard for introspective queries, isn't there? Is it > something like MySQL's "INFORMATION_SCHEMA Tables", as per?: > http://dev.mysql.com/doc/refman/5.0/en/information-schema.html Section 21 of the (SQL92) standard. It's absolutely horrible. Let's try to avoid that if we can. Fortunately SQLite has no user model, so most of it would be pointless anyway. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
On 12/12/2010, at 1:48 AM, Simon Slavin wrote: > On 11 Dec 2010, at 2:28pm, BareFeetWare wrote: > >> I think there's an SQL standard for introspective queries, isn't there? Is >> it something like MySQL's "INFORMATION_SCHEMA Tables", as per?: >> http://dev.mysql.com/doc/refman/5.0/en/information-schema.html > > Section 21 of the (SQL92) standard. > It's absolutely horrible. > Let's try to avoid that if we can. > > Fortunately SQLite has no user model, so most of it would be pointless anyway. Yes, I expect that at least half of what MySQL uses wouldn't be relevant to SQLite. But don't dismiss the concept due to what may be a flawed or wider implementation. I suspect part of the SQL standard for introspection (or "metadata") is relevant and worth using. But even if none is, please still move away from pragmas to some form of selectable query. 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] pragma vs select for introspection
On Dec 11, 2010, at 3:48 PM, Simon Slavin wrote: > Section 21 of the (SQL92) standard. Yes, the notorious information schema: http://en.wikipedia.org/wiki/Information_schema > It's absolutely horrible. Des goûts et des couleurs on ne discute point. > Let's try to avoid that if we can. Well, it has the merit of existing and being in use across various databases... so the cost/benefit of designing a slightly better, but different, information schema seem not worthwhile the trouble. In the same way as one could question some of the design choices of SQL itself, one is usually better off sticking to SQL nonetheless. Ditto for that information schema. No point in re-inventing a slightly squared wheel. > Fortunately SQLite has no user model, > so most of it would be > pointless anyway. Not sure what specifically you are referring to, but if it's the concept of schemata, then main, temp and attached databases fit nicely with the notion of schema. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
Petite Abeille wrote: > On Dec 11, 2010, at 3:48 PM, Simon Slavin wrote: > >> Section 21 of the (SQL92) standard. > > Yes, the notorious information schema: Nonsense. An information schema is a *good* thing, and is generally the *best* tool for introspecting a database. It lets you use all the power features you have when querying data, anything a SELECT can do, and you can query the database structure likewise. This is the way a relational database is supposed to work. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
On Dec 12, 2010, at 1:29 AM, Darren Duncan wrote: >> On Dec 11, 2010, at 3:48 PM, Simon Slavin wrote: >> >>> Section 21 of the (SQL92) standard. >> >> Yes, the notorious information schema: > > Nonsense. An information schema is a *good* thing, and is generally the > *best* > tool for introspecting a database. It lets you use all the power features > you > have when querying data, anything a SELECT can do, and you can query the > database structure likewise. This is the way a relational database is > supposed > to work. Fully agree :) "Notorious" was referring to Simon's "absolutely horrible" qualification of Section 21 of the SQL92 standard, which describes information_schema. This was a weak attempt to humor Simon :)) All in all, I'm all for a pragmatic implementation of Section 21 in SQLite. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
On 12 Dec 2010, at 10:38am, Petite Abeille wrote: > All in all, I'm all for a pragmatic implementation of Section 21 in SQLite. I see what you did there. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
On 12/12/10 00:29, Darren Duncan wrote: > Petite Abeille wrote: >> On Dec 11, 2010, at 3:48 PM, Simon Slavin wrote: >> >>> Section 21 of the (SQL92) standard. >> Yes, the notorious information schema: > Nonsense. An information schema is a *good* thing, and is generally the > *best* > tool for introspecting a database. It lets you use all the power features > you > have when querying data, anything a SELECT can do, and you can query the > database structure likewise. This is the way a relational database is > supposed > to work. -- Darren Duncan > ___ Okay, I'm not describing a relational database ... But one of the very nice features of Pick is it is self-describing. The top level is a pick "table" called MD. This is described by a Pick "table" called the dictionary. Everything in Pick is a FILE (the Pick name for a table), from the top down. Even indices. Dunno how well that approach translates into a relational engine, because Pick has several very non-relational quirks (every "row" MUST have a primary key, the dictionary DEscribes, not PREscribes the FILE, etc etc). But it means you can use your standard query tools to query EVERYTHING. Cheers, Wol ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
Wols Lists wrote: > On 12/12/10 00:29, Darren Duncan wrote: >> Nonsense. An information schema is a *good* thing, and is generally the >> *best* >> tool for introspecting a database. It lets you use all the power features >> you >> have when querying data, anything a SELECT can do, and you can query the >> database structure likewise. This is the way a relational database is >> supposed >> to work. -- Darren Duncan >> ___ > Okay, I'm not describing a relational database ... > > But one of the very nice features of Pick is it is self-describing. The > top level is a pick "table" called MD. This is described by a Pick > "table" called the dictionary. Everything in Pick is a FILE (the Pick > name for a table), from the top down. Even indices. > > Dunno how well that approach translates into a relational engine, > because Pick has several very non-relational quirks (every "row" MUST > have a primary key, the dictionary DEscribes, not PREscribes the FILE, > etc etc). Can you say more about this last paragraph. These last couple items don't necessarily mean that Pick is non-relational given how they can be interpreted. (I don't know anything about Pick.) > But it means you can use your standard query tools to query EVERYTHING. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
Darren Duncan wrote: > Wols Lists wrote: >> Dunno how well that approach translates into a relational engine, >> because Pick has several very non-relational quirks (every "row" MUST >> have a primary key, the dictionary DEscribes, not PREscribes the FILE, >> etc etc). > > Can you say more about this last paragraph. These last couple items don't > necessarily mean that Pick is non-relational given how they can be > interpreted. > (I don't know anything about Pick.) Actually, nevermind. Google is your friend. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
On 13/12/10 01:38, Darren Duncan wrote: > Darren Duncan wrote: >> Wols Lists wrote: >>> Dunno how well that approach translates into a relational engine, >>> because Pick has several very non-relational quirks (every "row" MUST >>> have a primary key, the dictionary DEscribes, not PREscribes the FILE, >>> etc etc). >> Can you say more about this last paragraph. These last couple items don't >> necessarily mean that Pick is non-relational given how they can be >> interpreted. >> (I don't know anything about Pick.) > Actually, nevermind. Google is your friend. -- Darren Duncan Pick is a jack-of-all-trades database - I describe it as being a bit like C - it gives you all the rope you need to shoot yourself in the foot :-) But it's best if used as an object-relational database. Pick has FILEs and RECORDs instead of TABLEs and ROWs, and you can store lists in a cell :-) Personally, I believe relational *technology* is fatally flawed by design - there's nothing wrong with the maths, but you can't do astronomy with classical physics and you can't do large information stores with set theory :-) I know that's flame-bait, but let's quickly explain ... I would say that a well designed Pick database uses the object-relational paradigm. Each file is a class, each record is an instance, and each record is a FULLY NORMALISED N-DIMENSIONAL ARRAY. (Just not first normal form.) So my datastore is heavily influenced by the real world. And I can reason about real world performance. All stuff that's forbidden in a "real" relational database. And actually, I can prove that my default performance is pretty close to a real relational database's theoretical best. But all of that depends on a close tying between the logical structure, the physical structure, and the real world. And all of that is totally antithetical to the basis behind relational database theory. And building on that, I would actually conclude that, just as in the real world parallel lines DO meet (Euclid's statement to the contrary notwithstanding), I would also conclude that in the real world data does NOT come just as rows and columns in sets (C&D's statement to the contrary notwithstanding), but it also comes in lists, bags, and jumbles. I'm quite happy to carry on discussing this, either privately or on the list, but there's a very good chance the list wouldn't welcome it ... Cheers, Wol ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
Wols Lists wrote: > On 13/12/10 01:38, Darren Duncan wrote: >> Darren Duncan wrote: >>> Wols Lists wrote: Dunno how well that approach translates into a relational engine, because Pick has several very non-relational quirks (every "row" MUST have a primary key, the dictionary DEscribes, not PREscribes the FILE, etc etc). >>> Can you say more about this last paragraph. These last couple items don't >>> necessarily mean that Pick is non-relational given how they can be >>> interpreted. >>>(I don't know anything about Pick.) >> Actually, nevermind. Google is your friend. -- Darren Duncan > > Pick is a jack-of-all-trades database - I describe it as being a bit > like C - it gives you all the rope you need to shoot yourself in the > foot :-) But it's best if used as an object-relational database. Pick > has FILEs and RECORDs instead of TABLEs and ROWs, and you can store > lists in a cell :-) > > Personally, I believe relational *technology* is fatally flawed by > design - there's nothing wrong with the maths, but you can't do > astronomy with classical physics and you can't do large information > stores with set theory :-) > > I know that's flame-bait, but let's quickly explain ... > > I would say that a well designed Pick database uses the > object-relational paradigm. Each file is a class, each record is an > instance, and each record is a FULLY NORMALISED N-DIMENSIONAL ARRAY. > (Just not first normal form.) > > So my datastore is heavily influenced by the real world. And I can > reason about real world performance. All stuff that's forbidden in a > "real" relational database. And actually, I can prove that my default > performance is pretty close to a real relational database's theoretical > best. > > But all of that depends on a close tying between the logical structure, > the physical structure, and the real world. And all of that is totally > antithetical to the basis behind relational database theory. > > And building on that, I would actually conclude that, just as in the > real world parallel lines DO meet (Euclid's statement to the contrary > notwithstanding), I would also conclude that in the real world data does > NOT come just as rows and columns in sets (C&D's statement to the > contrary notwithstanding), but it also comes in lists, bags, and jumbles. > > I'm quite happy to carry on discussing this, either privately or on the > list, but there's a very good chance the list wouldn't welcome it ... > I am interested in reading more about this. Why don't you write up a blog post or an article, put it on your web site. You do have a web site, no? Hopefully, powered by an object-relational, non-Euclidean, file-and-record database, the pick of the litter ;-) Seriously, I would love to read more about this as I am interested in storage technologies for gridded data (think cells in a remote sensing image). For now, all I have is the image of Dick Pick hanging upside down in his anti-gravity shoes burned in my brain. -- Puneet Kishor http://punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Fellow http://creativecommons.org/about/people/fellows#puneetkishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
On Mon, Dec 13, 2010 at 1:27 PM, Puneet Kishor wrote: > Wols Lists wrote: >> On 13/12/10 01:38, Darren Duncan wrote: >>> Darren Duncan wrote: Wols Lists wrote: > Dunno how well that approach translates into a relational engine, > because Pick has several very non-relational quirks (every "row" MUST > have a primary key, the dictionary DEscribes, not PREscribes the FILE, > etc etc). Can you say more about this last paragraph. These last couple items don't necessarily mean that Pick is non-relational given how they can be interpreted. (I don't know anything about Pick.) >>> Actually, nevermind. Google is your friend. -- Darren Duncan >> >> Pick is a jack-of-all-trades database - I describe it as being a bit >> like C - it gives you all the rope you need to shoot yourself in the >> foot :-) But it's best if used as an object-relational database. Pick >> has FILEs and RECORDs instead of TABLEs and ROWs, and you can store >> lists in a cell :-) >> >> Personally, I believe relational *technology* is fatally flawed by >> design - there's nothing wrong with the maths, but you can't do >> astronomy with classical physics and you can't do large information >> stores with set theory :-) >> >> I know that's flame-bait, but let's quickly explain ... >> >> I would say that a well designed Pick database uses the >> object-relational paradigm. Each file is a class, each record is an >> instance, and each record is a FULLY NORMALISED N-DIMENSIONAL ARRAY. >> (Just not first normal form.) >> >> So my datastore is heavily influenced by the real world. And I can >> reason about real world performance. All stuff that's forbidden in a >> "real" relational database. And actually, I can prove that my default >> performance is pretty close to a real relational database's theoretical >> best. >> >> But all of that depends on a close tying between the logical structure, >> the physical structure, and the real world. And all of that is totally >> antithetical to the basis behind relational database theory. >> >> And building on that, I would actually conclude that, just as in the >> real world parallel lines DO meet (Euclid's statement to the contrary >> notwithstanding), I would also conclude that in the real world data does >> NOT come just as rows and columns in sets (C&D's statement to the >> contrary notwithstanding), but it also comes in lists, bags, and jumbles. >> >> I'm quite happy to carry on discussing this, either privately or on the >> list, but there's a very good chance the list wouldn't welcome it ... >> > > > I am interested in reading more about this. Why don't you write up a > blog post or an article, put it on your web site. You do have a web > site, no? Hopefully, powered by an object-relational, non-Euclidean, > file-and-record database, the pick of the litter ;-) > > Seriously, I would love to read more about this as I am interested in > storage technologies for gridded data (think cells in a remote sensing > image). For now, all I have is the image of Dick Pick hanging upside > down in his anti-gravity shoes burned in my brain. Pick has been around for a very long time, use those interwebs: http://en.wikipedia.org/wiki/Pick_operating_system -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
Scott Hess wrote: > On Mon, Dec 13, 2010 at 1:27 PM, Puneet Kishor wrote: >> Wols Lists wrote: >>> On 13/12/10 01:38, Darren Duncan wrote: Darren Duncan wrote: > Wols Lists wrote: >> Dunno how well that approach translates into a relational engine, >> because Pick has several very non-relational quirks (every "row" MUST >> have a primary key, the dictionary DEscribes, not PREscribes the FILE, >> etc etc). > Can you say more about this last paragraph. These last couple items don't > necessarily mean that Pick is non-relational given how they can be > interpreted. > (I don't know anything about Pick.) Actually, nevermind. Google is your friend. -- Darren Duncan >>> Pick is a jack-of-all-trades database - I describe it as being a bit >>> like C - it gives you all the rope you need to shoot yourself in the >>> foot :-) But it's best if used as an object-relational database. Pick >>> has FILEs and RECORDs instead of TABLEs and ROWs, and you can store >>> lists in a cell :-) >>> >>> Personally, I believe relational *technology* is fatally flawed by >>> design - there's nothing wrong with the maths, but you can't do >>> astronomy with classical physics and you can't do large information >>> stores with set theory :-) >>> >>> I know that's flame-bait, but let's quickly explain ... >>> >>> I would say that a well designed Pick database uses the >>> object-relational paradigm. Each file is a class, each record is an >>> instance, and each record is a FULLY NORMALISED N-DIMENSIONAL ARRAY. >>> (Just not first normal form.) >>> >>> So my datastore is heavily influenced by the real world. And I can >>> reason about real world performance. All stuff that's forbidden in a >>> "real" relational database. And actually, I can prove that my default >>> performance is pretty close to a real relational database's theoretical >>> best. >>> >>> But all of that depends on a close tying between the logical structure, >>> the physical structure, and the real world. And all of that is totally >>> antithetical to the basis behind relational database theory. >>> >>> And building on that, I would actually conclude that, just as in the >>> real world parallel lines DO meet (Euclid's statement to the contrary >>> notwithstanding), I would also conclude that in the real world data does >>> NOT come just as rows and columns in sets (C&D's statement to the >>> contrary notwithstanding), but it also comes in lists, bags, and jumbles. >>> >>> I'm quite happy to carry on discussing this, either privately or on the >>> list, but there's a very good chance the list wouldn't welcome it ... >>> >> >> I am interested in reading more about this. Why don't you write up a >> blog post or an article, put it on your web site. You do have a web >> site, no? Hopefully, powered by an object-relational, non-Euclidean, >> file-and-record database, the pick of the litter ;-) >> >> Seriously, I would love to read more about this as I am interested in >> storage technologies for gridded data (think cells in a remote sensing >> image). For now, all I have is the image of Dick Pick hanging upside >> down in his anti-gravity shoes burned in my brain. > > Pick has been around for a very long time, use those interwebs: >http://en.wikipedia.org/wiki/Pick_operating_system > Yes, that is what lead me to see the image of Dick Pick hanging upside down. I am more interested in a focused take on rdbms vs. non-rdbms. It is a topic that I find rather fascinating, kinda like Republicans vs. Democrats. > -scott > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Fellow http://creativecommons.org/about/people/fellows#puneetkishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
Wols Lists wrote: > Personally, I believe relational *technology* is fatally flawed by > design - there's nothing wrong with the maths, but you can't do > astronomy with classical physics and you can't do large information > stores with set theory :-) > > I know that's flame-bait, but let's quickly explain ... > > I would say that a well designed Pick database uses the > object-relational paradigm. Each file is a class, each record is an > instance, and each record is a FULLY NORMALISED N-DIMENSIONAL ARRAY. > (Just not first normal form.) > > So my datastore is heavily influenced by the real world. And I can > reason about real world performance. All stuff that's forbidden in a > "real" relational database. And actually, I can prove that my default > performance is pretty close to a real relational database's theoretical > best. > > But all of that depends on a close tying between the logical structure, > the physical structure, and the real world. And all of that is totally > antithetical to the basis behind relational database theory. > > And building on that, I would actually conclude that, just as in the > real world parallel lines DO meet (Euclid's statement to the contrary > notwithstanding), I would also conclude that in the real world data does > NOT come just as rows and columns in sets (C&D's statement to the > contrary notwithstanding), but it also comes in lists, bags, and jumbles. > > I'm quite happy to carry on discussing this, either privately or on the > list, but there's a very good chance the list wouldn't welcome it ... I am also very interested in these subjects. I believe that the relational model can accurately model anything in the real world, and that this can be implemented in efficient ways, with physical structure taking hints from logical structure. Also, that you can model any data structure simply over tuples and relations, including arrays and bags, and likewise implement such tuples and relations with physical arrays behind the scenes. Ordered lists and bags can be logically binary relations with index+value or value+count attributes. (That is also the canonical way to do it in Muldis D.) It is perfectly valid to nest tuples and relations inside each other (these *are* valid 1NF), and so likewise you can have record field values that are sets or arrays or tables or whatever. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
On 13/12/10 22:44, Darren Duncan wrote: > I am also very interested in these subjects. > > I believe that the relational model can accurately model anything in > the real world, and that this can be implemented in efficient ways, > with physical structure taking hints from logical structure. But can you STORE it? A challenge I throw out - please STORE a list in the relational model. Oh - I'll just add a couple of sensible constraints. (1) as seen by the application, there mustn't be any duplicate data (I believe the relational model says you mustn't duplicate data, yes?). And (2) - again as seen by the application - you mustn't mix data and metadata in the same table. Worded a bit differently, don't get your cardinal and ordinal numbers mixed up :-) > > Also, that you can model any data structure simply over tuples and > relations, including arrays and bags, and likewise implement such > tuples and relations with physical arrays behind the scenes. Again, you use the word *model*. Isn't this pushing all this complexity back out into the app - where it DOESN'T belong? > > Ordered lists and bags can be logically binary relations with > index+value or value+count attributes. (That is also the canonical > way to do it in Muldis D.) > I think this is what I said above you mustn't do - mixing up your ordinals and cardinals? (And mixing your data and metadata.) > It is perfectly valid to nest tuples and relations inside each other > (these *are* valid 1NF), and so likewise you can have record field > values that are sets or arrays or tables or whatever. > Which is where Pick scores, this is easy to do and flows naturally from the data model. But it does it the other way round - the logical structure takes hints from the real-world-physical structure (and the database designer makes sure the physical database structure mimics the real-world physical structure). > -- Darren Duncan Cheers, Wol ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
Wols Lists wrote:
> On 13/12/10 22:44, Darren Duncan wrote:
>> I am also very interested in these subjects.
>>
>> I believe that the relational model can accurately model anything in
>> the real world, and that this can be implemented in efficient ways,
>> with physical structure taking hints from logical structure.
>
> But can you STORE it?
Yes. Anything that a computer can represent, a computer can store.
> A challenge I throw out - please STORE a list in the relational model.
> Oh - I'll just add a couple of sensible constraints. (1) as seen by the
> application, there mustn't be any duplicate data (I believe the
> relational model says you mustn't duplicate data, yes?). And (2) - again
> as seen by the application - you mustn't mix data and metadata in the
> same table. Worded a bit differently, don't get your cardinal and
> ordinal numbers mixed up :-)
Okay, I'll take this up. I will model an array of elements, where each element
has arbitrary/possibly-user-defined type Foo, in terms of a relation.
I also argue that the array index is necessary in any model or implementation,
relational or not; the user explicitly cares about this because they want an
order associated with a list of Foo that isn't intrinsic to the Foo values
themselves.
Sure, the language can provide syntax for operations such that users don't
actually have to use the index numbers, but they still have to be there, and
users have explicitly said they must by declaring their list is ordered. And
indexes must exist somehow; every implementation of an array must have some way
of referring to and finding an element regardless of language.
>> Also, that you can model any data structure simply over tuples and
>> relations, including arrays and bags, and likewise implement such
>> tuples and relations with physical arrays behind the scenes.
>
> Again, you use the word *model*. Isn't this pushing all this complexity
> back out into the app - where it DOESN'T belong?
No. All the complexity is stored and enforced by the database, right where it
belongs, and not with the app.
Since the format I describe of using a binary relation to represent an array is
canonical for Muldis D, it has related shorthands, and by using those, users
don't see the index numbers which conceptually exist.
Muldis D short-hand for declaring the array type:
material Foo_Array ::= array_of.Foo
Muldis D longer-hand for same:
material Foo_Array ::= relation-type {
over tuple-type {
attr index : subset-type of Int where { topic >= 0 }
attr value : Foo
}
primary-key { index }
where { #topic > max(topic{value}) }
}
One way to declare a database relvar of that type:
relvar list_of_foo : nlx.lib.Foo_Array
Or Foo_Array can be the declared type of a lexical variable, or a function
parameter, or another relation attribute, or whatever.
Here is a SQL analogy:
CREATE TABLE "list_of_foo" (
"index" INTEGER CHECK "index" >= 0,
"value" FOO,
PRIMARY KEY ("index")
);
CONSTRAINT CHECK (SELECT COUNT(*) FROM "list_of_foo")
> (SELECT MAX("index") FROM "list_of_foo");
Now a relational DBMS can see the above definition and realize that it means a
simple ordered array, and so can implement and store it as such physically if
it
chooses to; but the implementation choice is left to the DBMS.
For an example of assigning to said array variable, lets assume for simplicity
that Foo is actually just Text (if not, then replace the string literal for a
Foo value literal) ...
In Muldis D, the short-hand:
list_of_foo := ['cherry', 'apple', 'orange']
Muldis D longer-hand for the same:
list_of_foo := %:[index, value]:{
[0, 'cherry'],
[1, 'apple'],
[2, 'orange'],
}
And the SQL analogy (assume within a transaction):
SET CONSTRAINTS ALL DEFERRED;
TRUNCATE "list_of_foo";
INSERT INTO "list_of_foo" ("index", "value") VALUES
(0, 'cherry'),
(1, 'apple'),
(2, 'orange')
);
SET CONSTRAINTS ALL IMMEDIATE;
You will see that in all the short-hands, there is no array index metadata to
be
seen. The DBMS API for the application can map these arrays-as-relations to
its
native arrays fairly simply.
>> Ordered lists and bags can be logically binary relations with
>> index+value or value+count attributes. (That is also the canonical
>> way to do it in Muldis D.)
>
> I think this is what I said above you mustn't do - mixing up your
> ordinals and cardinals? (And mixing your data and metadata.)
The index is an ordinal, the count is a cardinal and both are just data. Lots
of actual user data is the same. If we're storing a count of apples, or
quantity of grain, in our inventory, that count is a cardinal number. If we're
listing the starting lineup for ball players, their order to go out is an
ordinal number.
If you want the DBMS to enforce that they are not mixed up, as you could do
with
plain integers, you can declare wrapp
Re: [sqlite] pragma vs select for introspection
Hi Duncan,
Exactly the sort of answer I was afraid of ... but I think my answer is
going to horrify you as much as yours horrified me.
Let's explain. In antiquity, Euclid said "parallel lines never meet". He
used that and logic to build a model of geometry (which other people
then built on further to create things like Classical Physics). The
point is, it's all a self-consistent *Mathematical* *Model*.
Then Einstein went to the south seas, observed the 1917 eclipse and said
"Hey, we have two straight lines here, that have crossed in two distinct
places. Sorry Euclid, your maths does not describe my reality".
You're talking maths, I'm talking science, we have the potential of a
major conflagration here ...
On 14/12/10 01:29, Darren Duncan wrote:
> Wols Lists wrote:
>> On 13/12/10 22:44, Darren Duncan wrote:
>>> I am also very interested in these subjects.
>>>
>>> I believe that the relational model can accurately model anything in
>>> the real world, and that this can be implemented in efficient ways,
>>> with physical structure taking hints from logical structure.
>>
>> But can you STORE it?
>
> Yes. Anything that a computer can represent, a computer can store.
>
And? Did you really mean to say that? We were talking about relational
databases, not computers. Of course a computer can store a list - it can
run Pick which stores lists ... (or it can run emacs !!! :-)
>> A challenge I throw out - please STORE a list in the relational
>> model. Oh - I'll just add a couple of sensible constraints. (1) as
>> seen by the application, there mustn't be any duplicate data (I
>> believe the relational model says you mustn't duplicate data, yes?).
>> And (2) - again as seen by the application - you mustn't mix data and
>> metadata in the same table. Worded a bit differently, don't get your
>> cardinal and ordinal numbers mixed up :-)
>
> Okay, I'll take this up. I will model an array of elements, where
> each element has arbitrary/possibly-user-defined type Foo, in terms of
> a relation.
>
> I also argue that the array index is necessary in any model or
> implementation, relational or not; the user explicitly cares about
> this because they want an order associated with a list of Foo that
> isn't intrinsic to the Foo values themselves.
"proof by arrogance" :-) "because I need it, so do you". Let me give you
a list. "Walter, Walter, John, Anthony". True, there's no inherent order
in the values themselves, but there IS an inherent order in the MEANING
of those values. It's a paternal lineage. But the ONLY valid
ordinal/cardinal conclusion you can come to is that Anthony is last,
because I have no sons. You can't assign an ordinal of four to me
because Walter Sr had a father (I happen to know his name was Walter,
too). In fact, with no more effort than checking another list on my
computer, I could change my *apparent* ordinality from 4 to about 10.
I'm quite happy with your argument "ordinals have meaning in relational
theory, because relational theory assigns them a meaning", but it's a
circular argument. As indeed, at fundamental, are ALL mathematical
arguments.
But to argue that ordinals have meaning as a matter of course is just
bullshit. I've just given you a scientific "the exception proves the
rule" proof, and in maths the basic proofs of number theory rely on the
fact they're meaningless.
>
> Sure, the language can provide syntax for operations such that users
> don't actually have to use the index numbers, but they still have to
> be there, and users have explicitly said they must by declaring their
> list is ordered. And indexes must exist somehow; every implementation
> of an array must have some way of referring to and finding an element
> regardless of language.
>
>>> Also, that you can model any data structure simply over tuples and
>>> relations, including arrays and bags, and likewise implement such
>>> tuples and relations with physical arrays behind the scenes.
>>
>> Again, you use the word *model*. Isn't this pushing all this
>> complexity back out into the app - where it DOESN'T belong?
>
> No. All the complexity is stored and enforced by the database, right
> where it belongs, and not with the app.
I'm beginning to see where the fault lines are developing. You've got a
middle layer I don't have. I'd leave all that complexity to the DBMS,
but you've got a database layer between the DBMS and the app ...
>
> Since the format I describe of using a binary relation to represent an
> array is canonical for Muldis D, it has related shorthands, and by
> using those, users don't see the index numbers which conceptually exist.
>
> Muldis D short-hand for declaring the array type:
>
> material Foo_Array ::= array_of.Foo
>
> Muldis D longer-hand for same:
>
> material Foo_Array ::= relation-type {
> over tuple-type {
> attr index : subset-type of Int where { topic >= 0 }
> attr value : Foo
> }
> primary-key { index }
> where { #topic > max(topic{value}) }
> }
>
Re: [sqlite] pragma vs select for introspection
On Dec 14, 2010, at 5:26 PM, Wols Lists wrote: > Exactly the sort of answer I was afraid of ... but I think my answer is > going to horrify you as much as yours horrified me. "... he began yawning and looking at his watch..." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
Wols, I'm just going to say a few things right now for brevity rather than individually responding to all your points. I think the point of a relational database is to provide an effective and flexible tool for users to describe any reality, and it is the job of the database to enforce any logically-defined business rules the user gives them. Perhaps in my explanation I may have been mixing up capabilities of the relational model itself with capabilities of programming languages that natively support the relational model and also do things that aren't part of that model itself but are complementary. But said programming languages in this case, such as SQL or Muldis D, *are* what the DBMS does. Wols Lists wrote: I believe that the relational model can accurately model anything in the real world, and that this can be implemented in efficient ways, with physical structure taking hints from logical structure. >>> But can you STORE it? >> Yes. Anything that a computer can represent, a computer can store. > > And? Did you really mean to say that? We were talking about relational > databases, not computers. Of course a computer can store a list - it can > run Pick which stores lists ... (or it can run emacs !!! :-) I think that part of the problem here is that you didn't define what STORE means. So please clarify with examples as what you see qualifies as "STORE a list" and what doesn't. For that matter, you didn't define what "list" means. I took it to mean "ordered array" given the context since you didn't specify. > "proof by arrogance" :-) "because I need it, so do you". Let me give you > a list. "Walter, Walter, John, Anthony". True, there's no inherent order > in the values themselves, but there IS an inherent order in the MEANING > of those values. It's a paternal lineage. But the ONLY valid > ordinal/cardinal conclusion you can come to is that Anthony is last, > because I have no sons. You can't assign an ordinal of four to me > because Walter Sr had a father (I happen to know his name was Walter, > too). In fact, with no more effort than checking another list on my > computer, I could change my *apparent* ordinality from 4 to about 10. There is more than one way to represent particular information, and meaning relates to context. A computer or model only says what users tell it to. If I made some assumptions I shouldn't have, maybe I was working from incomplete information and was just providing an example. >>> Again, you use the word *model*. Isn't this pushing all this >>> complexity back out into the app - where it DOESN'T belong? >> No. All the complexity is stored and enforced by the database, right >> where it belongs, and not with the app. > > I'm beginning to see where the fault lines are developing. You've got a > middle layer I don't have. I'd leave all that complexity to the DBMS, > but you've got a database layer between the DBMS and the app ... Maybe you should qualify what you mean by "the DBMS" and "the app" then. I define "the DBMS" as being the virtual machine in which the programming language runs which manages the storage of user data and the enforcement of user-defined business rules/constraints for that data. Typically the programming language is SQL but it could be Muldis D or something else instead. I define "the app" in this case as being user code that uses the DBMS and typically lives external to it. Both of these go along with conventional usage I think. How much the DBMS can do and what interface it provides to the app depends largely on what programming language it runs inside. As I understand it, Pick is a DBMS by this definition whose language isn't SQL. > Hang on a second - you've just said that "the DBMS can see this" ... in > other words, in MY reality, this is part of the application! And "index" > is visible! Game over, I've won :-) > > And hang on a third - you've just MODELLED a list, I challenged you to > STORE it. Game over, I've won :-) Once again, define what STORE means with examples. I had interpreted STORE conventionally as being how a programming language or DBMS or computer represents a list or persists it. > Okay, in your reality I think you'd say it's part of the > application/database api, to which I merely reply "and it's another > layer of complexity asking for trouble" :-) What is considered distinct layers is all about abstraction or representation or implementation. I see what the DBMS does as one layer and what app code outside the DBMS as the other layer. Why don't you define the layers you expect to have with examples which aren't unnecessarily complex? > Ouch. My fault for not being clear, I suppose, but you appear to have > one table per list. I was thinking along the lines of eg a recipe book - > a load of recipes with lists of ingredients. At one table per recipe, > that's going to be a right pig to manage in an RDBMS :-) That was just the simplified example. You will
Re: [sqlite] pragma vs select for introspection
On 14/12/10 19:49, Darren Duncan wrote:
> Wols,
>
> I'm just going to say a few things right now for brevity rather than
> individually responding to all your points.
>
> I think the point of a relational database is to provide an effective
> and flexible tool for users to describe any reality, and it is the job
> of the database to enforce any logically-defined business rules the
> user gives them.
M ... delete the word "relational" from that sentence and then
you're a lot closer to the truth. At which point I'd turn round and say
that a RDBMS does not fit that description :-)
>
> Perhaps in my explanation I may have been mixing up capabilities of
> the relational model itself with capabilities of programming languages
> that natively support the relational model and also do things that
> aren't part of that model itself but are complementary. But said
> programming languages in this case, such as SQL or Muldis D, *are*
> what the DBMS does.
At which point we diverge from the Pick model. SQL and Muldis-D have no
equivalent in the Pick world. (Or they *can* do. Like so much in Pick,
using the database query language is *optional*.)
>
> Wols Lists wrote:
> I believe that the relational model can accurately model anything in
> the real world, and that this can be implemented in efficient ways,
> with physical structure taking hints from logical structure.
But can you STORE it?
>>> Yes. Anything that a computer can represent, a computer can store.
>>
>> And? Did you really mean to say that? We were talking about relational
>> databases, not computers. Of course a computer can store a list - it can
>> run Pick which stores lists ... (or it can run emacs !!! :-)
>
> I think that part of the problem here is that you didn't define what
> STORE means. So please clarify with examples as what you see
> qualifies as "STORE a list" and what doesn't.
>
As opposed to "model". To store something is to put it into the database
unchanged. To model it is to alter it before you store it.
> For that matter, you didn't define what "list" means. I took it to
> mean "ordered array" given the context since you didn't specify.
>
And you would be correct.
>> "proof by arrogance" :-) "because I need it, so do you". Let me give you
>> a list. "Walter, Walter, John, Anthony". True, there's no inherent order
>> in the values themselves, but there IS an inherent order in the MEANING
>> of those values. It's a paternal lineage. But the ONLY valid
>> ordinal/cardinal conclusion you can come to is that Anthony is last,
>> because I have no sons. You can't assign an ordinal of four to me
>> because Walter Sr had a father (I happen to know his name was Walter,
>> too). In fact, with no more effort than checking another list on my
>> computer, I could change my *apparent* ordinality from 4 to about 10.
>
> There is more than one way to represent particular information, and
> meaning relates to context. A computer or model only says what users
> tell it to. If I made some assumptions I shouldn't have, maybe I was
> working from incomplete information and was just providing an example.
Isn't that what typically happens in a relational database :-) I'm being
sarky, but I think there's rather more than a grain of truth there.
But this is the perfect example of the difference between "model" and
"store". I'll use my list of names, but it applies equally to your list
of fruits. You *correctly* assumed that a list and and an ordered array
are equivalent. But in an ordered array, the index is metadata and the
value is data. So why did you store the index along with the value? I
did say "don't mix data and metadata", didn't I :-)
My app passes { Walter, Walter, John, Anthony } to Pick. Pick writes {
Walter, Walter, John, Anthony } to disk.
Your app passes { Walter, Walter, John, Anthony } to the
application/database transformation layer. SQL transforms it into { {0,
Walter} {1, Walter} {2, John} {3, Anthony} } and passes it to the RDBMS.
The RDBMS splits it up into the tuples, and saves them to disk.
(Now when the next programmer comes along, or you come back six months
later, how do you know that the 0,1,2,3 are, in fact, totally
meaningless and only there to be sorted on to make sure the names are
passed back in the correct order?)
>
Again, you use the word *model*. Isn't this pushing all this
complexity back out into the app - where it DOESN'T belong?
>>> No. All the complexity is stored and enforced by the database, right
>>> where it belongs, and not with the app.
>>
>> I'm beginning to see where the fault lines are developing. You've got a
>> middle layer I don't have. I'd leave all that complexity to the DBMS,
>> but you've got a database layer between the DBMS and the app ...
>
> Maybe you should qualify what you mean by "the DBMS" and "the app" then.
>
> I define "the DBMS" as being the virtual machine in which the
> programming language runs which manages the storage of user data and
> the
Re: [sqlite] pragma vs select for introspection
Wols Lists wrote:
>> I think that part of the problem here is that you didn't define what
>> STORE means. So please clarify with examples as what you see
>> qualifies as "STORE a list" and what doesn't.
>
> As opposed to "model". To store something is to put it into the database
> unchanged. To model it is to alter it before you store it.
>
>
> My app passes { Walter, Walter, John, Anthony } to Pick. Pick writes {
> Walter, Walter, John, Anthony } to disk.
>
> Your app passes { Walter, Walter, John, Anthony } to the
> application/database transformation layer. SQL transforms it into { {0,
> Walter} {1, Walter} {2, John} {3, Anthony} } and passes it to the RDBMS.
> The RDBMS splits it up into the tuples, and saves them to disk.
From your description here, it sounds like Pick is fairly low level and just
deals with bit strings; eg, each key and value it deals with is just a bit
string.
That is the only way I see it would make sense to be able to say that to
"store"
is to put into the database unchanged. Because as bit strings is how the
computer natively represents things, so the DBMS and app would use this in
common, and anything else is an abstraction.
If Pick has any understanding of the data itself which is higher level, other
than external metadata which is also bit strings, then it would be doing
modeling in order to do this, such as to treat text in text-specific ways.
When you go above bit strings, different programming languages and applications
and APIs assume different things about what bit pattern particular character
strings or numbers or arrays or whatever have.
> (Now when the next programmer comes along, or you come back six months
> later, how do you know that the 0,1,2,3 are, in fact, totally
> meaningless and only there to be sorted on to make sure the names are
> passed back in the correct order?)
Documentation, either in the form of a descriptive schema or otherwise.
> You misunderstand me - seriously so! In Pick, that operation is atomic
> inside the database, right through until the data passes out of the
> database into the hard disk queue! ANY and ALL RDBMS are unable to
> provide that guarantee, by simple virtue of being relational databases!
>
> That's why Pickies have such a hard time understanding all this fuss
> over ACIDity :-) If an application write fails sufficiently badly to
> worry about atomicity, it's not a matter of corrupt data, it's a corrupt
> hard disk you're worrying about!
>
> I don't want to broaden the discussion unnecessarily, but this is where
> Pick really gets away from the RDBMS model - by nesting tightly related
> tables you can write (or read) all this stuff in a single atomic hit.
> Which is how I can prove that Pick will ALWAYS outperform relational for
> speed - Pick allows me know about the underlying storage, and because I
> know about it I can reason about it and make predictions about
> performance. All totally forbidden by relational theory, of course :-)
Atomicity is just an abstraction for certain kinds of error detection and
correction. Pick can't be truly atomic, but only provide an illusion of such,
and so can other DBMSs, including relational ones, as the implementations
provide. (And even then, operating systems are known to lie about whether data
has been physically written to disk when you fsync.)
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma vs select for introspection
On 15/12/10 00:18, Darren Duncan wrote:
> Wols Lists wrote:
>>> I think that part of the problem here is that you didn't define what
>>> STORE means. So please clarify with examples as what you see
>>> qualifies as "STORE a list" and what doesn't.
>>
>> As opposed to "model". To store something is to put it into the database
>> unchanged. To model it is to alter it before you store it.
>>
>
>>
>> My app passes { Walter, Walter, John, Anthony } to Pick. Pick writes {
>> Walter, Walter, John, Anthony } to disk.
>>
>> Your app passes { Walter, Walter, John, Anthony } to the
>> application/database transformation layer. SQL transforms it into { {0,
>> Walter} {1, Walter} {2, John} {3, Anthony} } and passes it to the RDBMS.
>> The RDBMS splits it up into the tuples, and saves them to disk.
>
> From your description here, it sounds like Pick is fairly low level
> and just deals with bit strings; eg, each key and value it deals with
> is just a bit string.
Correct. Except your "value" is actually "bunch of attributes" if I
understand "key" in the typical sense ...
>
> That is the only way I see it would make sense to be able to say that
> to "store" is to put into the database unchanged. Because as bit
> strings is how the computer natively represents things, so the DBMS
> and app would use this in common, and anything else is an abstraction.
ALL is an abstraction :-) At least, once you've moved away from the real
pizza physically on your plate :-)
The point I'm making is that a list doesn't contain any ordering *data*
- it's inherent in the fact of a list. A list is an abstract concept. In
Pick, I can store a data structure that IS an abstract list. In an rdbms
I can't.
Put another way, in Pick the function "storelistindatabase()" and
"getlistfromdatabase()" are, at a fundamental level, direct inverses -
there's a one-to-one mapping.
In an rdbms, the function "storelistindatabase()" has an inverse
"getdatafromdatabase()" which returns something completely different
from what went in.
>
> If Pick has any understanding of the data itself which is higher
> level, other than external metadata which is also bit strings, then it
> would be doing modeling in order to do this, such as to treat text in
> text-specific ways.
Here again, we come to a fundamental mis-match between the relational
view of things, and the Pick view. In the relational view, if the table
does not have a column definition, there is no column. The definition,
by definition, defines the column :-)
In Pick, the DICTionary de*scribes* the column. If there's no
definition, the column can still exist. You just don't know what's in it
:-) Pick uses the description to understand the data, relational uses
the definition to define the data.
Without a definition, you can't model. So Pick doesn't. It understands,
instead.
>
> When you go above bit strings, different programming languages and
> applications and APIs assume different things about what bit pattern
> particular character strings or numbers or arrays or whatever have.
>
>> (Now when the next programmer comes along, or you come back six months
>> later, how do you know that the 0,1,2,3 are, in fact, totally
>> meaningless and only there to be sorted on to make sure the names are
>> passed back in the correct order?)
>
> Documentation, either in the form of a descriptive schema or otherwise.
And if the programmer didn't bother to document? Or he got it wrong? Or
he misunderstood? Point is, I'm not relying on some guy storing yet more
metadata in some other datastore somewhere else. My data inside my
database is logically identical to my reality outside. There's a
one-to-one bidirectional mapping.
You've basically just admitted that your database is not complete or
accurate - you need more information stored elsewhere.
>
>
>> You misunderstand me - seriously so! In Pick, that operation is atomic
>> inside the database, right through until the data passes out of the
>> database into the hard disk queue! ANY and ALL RDBMS are unable to
>> provide that guarantee, by simple virtue of being relational databases!
>>
>> That's why Pickies have such a hard time understanding all this fuss
>> over ACIDity :-) If an application write fails sufficiently badly to
>> worry about atomicity, it's not a matter of corrupt data, it's a corrupt
>> hard disk you're worrying about!
>>
>> I don't want to broaden the discussion unnecessarily, but this is where
>> Pick really gets away from the RDBMS model - by nesting tightly related
>> tables you can write (or read) all this stuff in a single atomic hit.
>> Which is how I can prove that Pick will ALWAYS outperform relational for
>> speed - Pick allows me know about the underlying storage, and because I
>> know about it I can reason about it and make predictions about
>> performance. All totally forbidden by relational theory, of course :-)
>
> Atomicity is just an abstraction for certain kinds of error detection
> and correction. Pick can't be truly at
Re: [sqlite] pragma vs select for introspection
Wols Lists wrote:
> On 15/12/10 00:18, Darren Duncan wrote:
> The point I'm making is that a list doesn't contain any ordering *data*
> - it's inherent in the fact of a list. A list is an abstract concept. In
> Pick, I can store a data structure that IS an abstract list. In an rdbms
> I can't.
>
> Put another way, in Pick the function "storelistindatabase()" and
> "getlistfromdatabase()" are, at a fundamental level, direct inverses -
> there's a one-to-one mapping.
>
> In an rdbms, the function "storelistindatabase()" has an inverse
> "getdatafromdatabase()" which returns something completely different
> from what went in.
I would expect that any RDBMS which has a "storelistindatabase()" would also
have a "getlistfromdatabase()". Sure, it may fail if you call the latter for
something which isn't a list, but then I would expect the same in Pick, unless
everything in Pick is a list.
>> If Pick has any understanding of the data itself which is higher
>> level, other than external metadata which is also bit strings, then it
>> would be doing modeling in order to do this, such as to treat text in
>> text-specific ways.
>
> Here again, we come to a fundamental mis-match between the relational
> view of things, and the Pick view. In the relational view, if the table
> does not have a column definition, there is no column. The definition,
> by definition, defines the column :-)
>
> In Pick, the DICTionary de*scribes* the column. If there's no
> definition, the column can still exist. You just don't know what's in it
> :-) Pick uses the description to understand the data, relational uses
> the definition to define the data.
>
> Without a definition, you can't model. So Pick doesn't. It understands,
> instead.
From my perspective at least, a relational database works more like a Pick
database than you think; and this is reflected in Muldis D. I recognize that
some other people see things in a way that are more different, and SQL reflects
this.
A primary difference as I see it is that tuple + relation + scalar values are
conceptually the basic building blocks of a relational database while Pick uses
other things. Obviously, if what you want to store is exactly like a basic
building block, then doing so will be simpler.
In Muldis D, you can work with any arbitrarily complex value, a relation or
otherwise, without first declaring a type for it. The *only* purpose of
declaring a type in Muldis D is for defining a constraint on a variable or a
parameter; it also helps with optimization since the DBMS can then better
predict what is going to be used where.
For example, you can simply say:
@:{ { pizza_name => 'Hawaiian', toppings => { 'ham', 'pineapple' } } }
... without declaring anything first, and what you have there is a binary
relation value literal consisting of a single tuple of 2 attributes, and one of
those attributes' values is a set of 2 elements.
You could also take any value and introspect it, whereby you can be given back
a
type definition that *describes* the value.
"the database" in Muldis D is in the general case simply a non-lexical variable
whose type is, loosely, "any tuple whose attribute values are relations". You
can declare that the type of "the database" is more specific, such as with
specific columns and such, but that is optional (though commonly done).
So in Muldis D, you can simply say "store this X" and it will, without you
having to define columns or whatever first. And I consider this to be
completely valid for a relational database.
This sounds like how you describe Pick.
Now SQL can't do this on the other hand, but that's a limitation of SQL.
(As a tangent that is more on-topic, the Muldis D approach is more in common
with SQLite than by many other SQL DBMSs in that a SQLite row column value can
be of any (scalar) type, and you don't have to declare a column to be of a
particular type in order to store a value there; if you do then that is just a
local constraint rather than a fundamental limitation.)
>> Atomicity is just an abstraction for certain kinds of error detection
>> and correction. Pick can't be truly atomic, but only provide an
>> illusion of such, and so can other DBMSs, including relational ones,
>> as the implementations provide. (And even then, operating systems are
>> known to lie about whether data has been physically written to disk
>> when you fsync.)
>>
> You're wrong there. Pick IS truly atomic. Yep, OSes can lie, and if Pick
> accepts that lie then carnage will occur, but the word "atom" is greek
> for "indivisible". Let's take my pizza for example. "Hawaiian = ham,
> pineapple". That is an atom. Take away any part of it, and it's no
> longer a hawaiian pizza. And as far as Pick is concerned (if properly
> programmed :-) that will remain, for ever and always, an atom. It comes
> in as an atom. It passes through as an atom. And it's fed out to the OS
> to put on disk as an atom. Pick is truly atomic
Is this mea
Re: [sqlite] pragma vs select for introspection
On 15/12/10 02:47, Darren Duncan wrote: > Wols Lists wrote: >> On 15/12/10 00:18, Darren Duncan wrote: >> The point I'm making is that a list doesn't contain any ordering *data* >> - it's inherent in the fact of a list. A list is an abstract concept. In >> Pick, I can store a data structure that IS an abstract list. In an rdbms >> I can't. >> >> Put another way, in Pick the function "storelistindatabase()" and >> "getlistfromdatabase()" are, at a fundamental level, direct inverses - >> there's a one-to-one mapping. >> >> In an rdbms, the function "storelistindatabase()" has an inverse >> "getdatafromdatabase()" which returns something completely different >> from what went in. > > I would expect that any RDBMS which has a "storelistindatabase()" > would also have a "getlistfromdatabase()". Sure, it may fail if you > call the latter for something which isn't a list, but then I would > expect the same in Pick, unless everything in Pick is a list. Hold onto that thought! I think I botched my wording - In Pick, getlistfromdatabase() and getdatafromdatabase() would be the same function. In an RDBMS, because the index is data, they're not. But back to that thought, you're almost spot on :-) The database structure consists of FILEs (tables in relational terminology) which consist of - to use a mac term - two "forks". The DATA fork and the DICTionary fork. These are structurally identical, so much so that the master dictionary only has one physical fork, which is logically both forks, and is therefore self-describing :-) Each RECORD (relational row) in a fork consists of a key-list pair - those in the DICTionary describing the FIELDs (columns), and those in the DATA instancing the cells described in the columns. So, at this level, each fork is a set - we have a bunch of items all with a unique primary key, and a database-defined order that is pseudo-random. (Going back to the real world, this pseudo-random order is why Pick guarantees to retrieve the sought-after data from disk at a 99% first-attempt success rate :-) Now if the column is the x-axis, and the row is the y-axis, each cell can itself be a list in the z-axis! And so on. (Yes, some people do complain Pick has its rows and columns the wrong way round from sensible :-) In *practice* all Pick implementations effectively stop at the next axis, the t-axis. But there's no theoretical reason why they should. It's just that, at this point, the programmer's brain explodes trying to cope with the all the dimensions. (And don't say an rdbms is easier to cope with - it's actually more complicated, because the programmer has to remember which tables are nested, rather than the database being "in your face" about it.) And pretty much every Pick database actually has three more dimensions available after this, they're just not used because of exactly that reason :-) > >>> If Pick has any understanding of the data itself which is higher >>> level, other than external metadata which is also bit strings, then it >>> would be doing modeling in order to do this, such as to treat text in >>> text-specific ways. >> >> Here again, we come to a fundamental mis-match between the relational >> view of things, and the Pick view. In the relational view, if the table >> does not have a column definition, there is no column. The definition, >> by definition, defines the column :-) >> >> In Pick, the DICTionary de*scribes* the column. If there's no >> definition, the column can still exist. You just don't know what's in it >> :-) Pick uses the description to understand the data, relational uses >> the definition to define the data. >> >> Without a definition, you can't model. So Pick doesn't. It understands, >> instead. > > From my perspective at least, a relational database works more like a > Pick database than you think; and this is reflected in Muldis D. I > recognize that some other people see things in a way that are more > different, and SQL reflects this. But I personally focus on the guarantees that Pick gives about response times, I can calculate that "in a perfect world it cannot be less than x seconds, in the real world it will be about y seconds" (and x and y are usually about the same). Relational merely says "I can guarantee that there is answer, and I that I will find it eventually". > > A primary difference as I see it is that tuple + relation + scalar > values are conceptually the basic building blocks of a relational > database while Pick uses other things. Obviously, if what you want to > store is exactly like a basic building block, then doing so will be > simpler. As I said, the idea of enforcing good design is totally alien to Pick :-) but ... The basic building block should be the (real world) atom. Let's say I'm represented by my NI number. That's my primary key. Without that there is no name, no age/d-o-b, no residence, no nothing. And it naturally belongs in a set. So we stick it in a FILE. Along with *all* the associated, tightly bound, attri
Re: [sqlite] pragma vs select for introspection
Wols, I'm just acknowledging that I've read this message, but don't feel the need to say anything more in response, as we appear to have reached a point of clear-enough mutual understanding. I suggest that if you want to further discuss anything related that you start a new message, off of the SQLite list. Or, if further discussion can be something that can help the evolution of Muldis D (so it lacks nothing good that Pick/etc has), you are welcome to post it as a new thread to the muldis-db-users email list instead (which also has public archives for anyone who cares) where it would be more on topic. -- Darren Duncan Wols Lists wrote: > On 15/12/10 02:47, Darren Duncan wrote: >> Wols Lists wrote: >>> On 15/12/10 00:18, Darren Duncan wrote: >>> The point I'm making is that a list doesn't contain any ordering *data* >>> - it's inherent in the fact of a list. A list is an abstract concept. In >>> Pick, I can store a data structure that IS an abstract list. In an rdbms >>> I can't. >>> >>> Put another way, in Pick the function "storelistindatabase()" and >>> "getlistfromdatabase()" are, at a fundamental level, direct inverses - >>> there's a one-to-one mapping. >>> >>> In an rdbms, the function "storelistindatabase()" has an inverse >>> "getdatafromdatabase()" which returns something completely different >>> from what went in. >> I would expect that any RDBMS which has a "storelistindatabase()" >> would also have a "getlistfromdatabase()". Sure, it may fail if you >> call the latter for something which isn't a list, but then I would >> expect the same in Pick, unless everything in Pick is a list. > > Hold onto that thought! > > I think I botched my wording - In Pick, getlistfromdatabase() and > getdatafromdatabase() would be the same function. In an RDBMS, because > the index is data, they're not. > > But back to that thought, you're almost spot on :-) The database > structure consists of FILEs (tables in relational terminology) which > consist of - to use a mac term - two "forks". The DATA fork and the > DICTionary fork. These are structurally identical, so much so that the > master dictionary only has one physical fork, which is logically both > forks, and is therefore self-describing :-) Each RECORD (relational row) > in a fork consists of a key-list pair - those in the DICTionary > describing the FIELDs (columns), and those in the DATA instancing the > cells described in the columns. So, at this level, each fork is a set - > we have a bunch of items all with a unique primary key, and a > database-defined order that is pseudo-random. (Going back to the real > world, this pseudo-random order is why Pick guarantees to retrieve the > sought-after data from disk at a 99% first-attempt success rate :-) > > Now if the column is the x-axis, and the row is the y-axis, each cell > can itself be a list in the z-axis! And so on. (Yes, some people do > complain Pick has its rows and columns the wrong way round from sensible :-) > > In *practice* all Pick implementations effectively stop at the next > axis, the t-axis. But there's no theoretical reason why they should. > It's just that, at this point, the programmer's brain explodes trying to > cope with the all the dimensions. (And don't say an rdbms is easier to > cope with - it's actually more complicated, because the programmer has > to remember which tables are nested, rather than the database being "in > your face" about it.) > > And pretty much every Pick database actually has three more dimensions > available after this, they're just not used because of exactly that > reason :-) > If Pick has any understanding of the data itself which is higher level, other than external metadata which is also bit strings, then it would be doing modeling in order to do this, such as to treat text in text-specific ways. >>> Here again, we come to a fundamental mis-match between the relational >>> view of things, and the Pick view. In the relational view, if the table >>> does not have a column definition, there is no column. The definition, >>> by definition, defines the column :-) >>> >>> In Pick, the DICTionary de*scribes* the column. If there's no >>> definition, the column can still exist. You just don't know what's in it >>> :-) Pick uses the description to understand the data, relational uses >>> the definition to define the data. >>> >>> Without a definition, you can't model. So Pick doesn't. It understands, >>> instead. >> From my perspective at least, a relational database works more like a >> Pick database than you think; and this is reflected in Muldis D. I >> recognize that some other people see things in a way that are more >> different, and SQL reflects this. > > But I personally focus on the guarantees that Pick gives about response > times, I can calculate that "in a perfect world it cannot be less than x > seconds, in the real world it will be about y seconds" (and x and y are > usually about the same). Relational merely
