Re: MV and SQL
Date: 19 Jan 2006 16:04:21 -0800
Message-ID: <1137715461.014286.193420_at_o13g2000cwo.googlegroups.com>
x wrote:
> <michael_at_preece.net> wrote in message
> news:1137679787.138095.286740_at_g44g2000cwa.googlegroups.com...
> >
> > x wrote:
> >
> > > <michael_at_preece.net> wrote in message
> > > news:1137637589.943828.10850_at_z14g2000cwz.googlegroups.com...
> > > >
>
> > >
> > > Are you sure that a column in Pick is equivalent to a column in an SQL
> table
> > > ?
> >
> > Well - not exactly equivalent obviously. Take, for instance, a
> > ProductNumber field. Without defining it as any particular data type,
> > you might have been using it for years to store numbers. They can be
> > pure numerics of a fixed length - or of variable length, it doesn't
> > matter. You can then start using that same field to store
> > ProductNumbers with a suffix - as in 12345 & 12345-01 & 12345-02. Then,
> > later, the company using this file/table might undergo some change - as
> > in a takeover or something, and have a need to include ProductNumbers
> > of a totally different "type". Say the new company has a similar
> > product range and you want to include references to those products
> > using the alternative codes. No problem - just start including product
> > "numbers" 1234ABC-XYZ-01 in the same "column". Pick - or the Pick
> > applications using the data - won't bat an eye.
>
> With an SQL DBMS you can also change the type from numeric to varchar and
> use that.
> You can also change the type from varchar to array if your SQL DBMS support
> (out of the box or by writing an extension) arrays.
>
> I don't think that a Pick application that assumed that the column contain
> numbers and performed numerical operations with the data from that column
> would continue to work after such changes.
>
> With an SQL DBMS one can use a language that suport the Variant data type
> for variables that store the data retrieved from the database.
> In PL/SQL one can declare a variable of being of the same data type as some
> attribute of a relation without saying what it is.
>
> > > Are you sure you are not confusing physical storage with logical
> relations ?
>
> > Yes. I never mentioned anything about physical storage. Why? Correct me
> > if I'm wrong, but I get the impression that the demarkation between the
> > logical and physical in an SQL-relational environment is that anything
> > that uses SQL is at the logical level and what the SQL engine does is
> > physical.
>
> > There is no such clearly defined demarkation in Pick. You can read and
> > write data and do absolutely anything with it you want to.
>
> Even if everything in a Pick database is a string, after you retrieve those
> strings and use them in some way, they are not just strings anymore. They
> become product numbers, phones, e-mail addresses, etc. This is the logical
> level.
>
Yes.
>
> > > If a column is called phones then it is expected to "store" phones not
> > > moneys and phones.
> > >
> >
> > Yes. If you had some bizarre need to do this in a Pick DBMS though, you
> > could go right ahead.
>
> So you don't have multiple types for one field afterall.
>
> > > > > - have the liberty to define stored procedures for implementing a
> domain
> > > > > specific language
> > > >
> > > > I guess you could say that everything in Pick is a kind of stored
> > > > procedure. Hell - what's an unstored one? Bear in mind here that we're
> > > > talking about what goes on within the DBMS. As I said in the post you
> > > > responded to, a Pick DBMS application covers any or all of the layers
> > > > from client user interface down to file IO. As such, a Pick procedure
> > > > can execute SQL commands or be called from dictionary items used
> within
> > > > an SQL command. It can also be called from a VB application to
> retrieve
> > > > data. It can also be used to fire off an executable or generate and
> > > > execute script to perform a function on a server/client/browser... I'm
> > > > struggling here... Ask me a specific question about what it can't be.
> > >
> > > I only said that an SQL DBMS can do this too with the exception that one
> > > cannot modify the DBMS.
> >
> > I'm confused. Could you please clarify this for me.
>
> Confused about what ?
The Twighlight Zone, that Bob Dylan song about Mr Jones... oh - and what you said an SQL DBMS can do and yet can't do.
>
> > > > > - have the liberty to choose the programming language and reporting
> > > tools
> > > > > used to access the database
> > > >
> > > > Ummm.. OK. Here's where I show my ignorance. I thought the only way to
> > > > get data into or out of a SQL DBMS is/was through SQL. I thought that
> > > > was the only way to access the database. If I'm wrong about that then
> > > > please feel free to educate me a little - and have a go at me too
> while
> > > > you're at it if you like.
> > >
> > > SQL is not the only language. It can be used with other languages like
> VB,
> > > JAVA, C#, proprietary/standard stored procedures languages, C, C++,
> COBOL,
> > > ADA, etc. Some of these can be used to write code running "inside" the
> > > DBMS. Some of these can be used to extend the base types/indices
> supported
> > > by the DBMS. Some of these can be used to write code running "outside"
> the
> > > DBMS.
> >
> > I could do with some clarification on this too. I'm quite familiar with
> > writing VB code to get data into and out of an SQL DBMS via SQL - but
> > that's the only way I've ever accessed an SQL DBMS - via SQL. As might
> > be clear to you and others, I am an SQL tenderfoot and a Pick veteran.
>
> SQL is always used with another language when writing an application.
> That language can be a language implemented in the SQL DBMS such as PL/SQL,
> Java, C#, etc.
> You do the same thing when you use DataBasic with the Pick query language
> and the other access functions.
>
> > > Besides the query language of Pick (search, list, etc.) what other ways
> do
> > > you have to access the data ?
> >
> > Although there might appear to be many, when it comes right down to it,
> > it boils down to just the query language (and aspects of it available
> > through other means - as in the oconv function I alluded to earlier),
> > the DataBasic programming language, C functions, SQL, and a handful of
> > other commands you can issue from the command-line, not strictly part
> > of the query language as such.
>
>
> > > These access methods are for Pick what SQL + proprietary interfaces are
> for
> > > an SQL DBMS.
> > >
> >
> > Yes. The tool-box is quite extensive though. That is why I was saying
> > it is a mistake for people to simply compare the query languages.
> > Pick's query language is very cool - but it's by no means the be-all
> > and end-all.
>
> It is also a mistake to compare a Pick system with SQL only.
>
Yes. I'd rather compare all of the tools available in an SQL-relational DBMS for getting data into and out of the database with all of the tools available in a Pick DBMS for getting data into and out of the database. On one side we have SQL - on the other we have SQL (rarely used) and a whole lot more.
> > > > Of course people use all kinds of programming languages and reporting
> > > > tools on top of Pick. I, personally, prefer the browser-web languages
> > > > but am also using VB - and a lot of people combine aspects of those
> two
> > > > by using ActiveX components. There are also a lot of Pickies out there
> > > > who prefer to use Java on top of Pick. There are also a lot of people
> > > > that use various reporting tools written in various languages, some of
> > > > them not just sitting on top of Pick, but actually written in Pick -
> > > > MITS and Datastage for example - which are often used as the sole Pick
> > > > application in an otherwise SQL DBMS environment (because they're
> > > > judged by their users to be better tools than those based on SQL).
> > >
> > > I was not saying that Pick cannot do the above. I was saying that one
> can do
> > > the same things using an SQL DBMS.
> >
> > You just can't though. You might be able to *use* SQL to do these
> > things, but you can't write an entire application in SQL like you can
> > in Pick - without any external add-ons. You can, of course, bolt
> > anything on top either.
>
> You also cannot write an entire application using only the Pick query
> language.
No.
> But one can write an entire application using SQL + PL/SQL + JAVA in the
> database.
>
I don't know about PL/SQL, but anything you use to develop an
application with that uses SQL to access an SQL-relational DBMS can
also be used to access a Pick DBMS. You can also choose not to use SQL
at all. You can also choose not to use *anything* outside of the Pick
DBMS itself and develop a full-blown application.
> > > > > - have the liberty to use the DBMS out of the box without any other
> > > tools
> > > >
> > > > If you ever get a DBMS out of the box that can't be used without any
> > > > other tools I suggest you send it straight back where it came from and
> > > > tell everyone about it.
> > >
> > > I was saying than one can describe the tables, enter the data and
> retrieve
> > > it whithout having to write various sorts of code like locking code, IO
> > > code, code for virtual fields.
> > >
>
> > All that work you - or your DBA - have to do in rigorously defining
> > every field in every column in every table... you can dispense with
> > entirely in Pick. I have been a Pick application developer for a very
> > long time and I have never yet come across a single Pick DBA.
>
> It is only an ilusion.
>
Have you been reading Jonathon Livingston Seagull or something? ;-)
> > >
> > > > > Using a C compiler one even have the liberty to define its own
> > > specialized
> > > > > DBMS for an application domain.
> > > >
> > > > Again - I plead ignorance. Sorry. I thought you had to use SQL with an
> > > > SQL DBMS.
> > >
> > > I was talking about a C compiler not about an SQL DBMS.
> > > Using a C compiler you have total freedom to develop whatever you like.
> You
> > > don't have to use SQL or an SQL DBMS or even Pick.
> >
> > Yes. DataBasic code can be compiled down to C btw. I am still curious
> > though - are there not "issues" concerning use of C to bypass SQL in an
> > SQL-relational DBMS?
>
> There is nothing to bypass when you are writing your own DBMS from scratch.
>
S'pose not. (?)
> > >
> > > > >
> > > > > So what is the advantage of MV systems ?
> > > >
> > > > Over SQL-relational? Well - because you can use SQL - if you want - or
> > > > you can go way beyond what SQL limits you to. I'll be keeping an eye
> on
> > > > Dawn's blog over the next year or so. I believe it'll go quite a long
> > > > way towards answering this question - much further than I can at the
> > > > moment.
> > >
> > > What SQL limits one to ?
> > > SQL is only a sublanguage always used together with other general
> purpose
> > > language.
> > > It's like saying that a Pick system is bad because the Pick query
> language
> > > is very limited (more limited than SQL in fact).
> > > If one have an SQL DBMS with unlimited field size and a general purpose
> > > language one can develop any database application using only one table
> with
> > > only one column and only one row. But that doesn't mean he should.
> >
> > Now you've lost me I'm afraid. Maybe you mean that everything inside
> > the Pick DBMS - or more specifically the VME - is seen from outside as
> > a single BLOb? This is true. That's the reason a lot of the Pick DBMS
> > "flavours" also make use of "external" file systems for "Pick files".
> > There's also a lot you can do yourself, as an application developer, to
> > use the host OS's files directly - bypassing the Pick DBMS in a way.
> > What I mean is - generally, when you access a Pick file, there is a lot
> > going on within Pick to do with managing that interaction between
> > application code and the physical storage - lock-handling etc., etc..
> > You can, if you want, bypass much of this and "reach out and touch" OS
> > files and sockets etc. from within your Pick application. There really
> > are a lot of options in Pick.
Cheers,
Mike.
Received on Fri Jan 20 2006 - 01:04:21 CET