Re: MV and SQL

From: <michael_at_preece.net>
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.
>

You do not have to attach any "type" to any field in Pick. You can go ahead and use any field in which ever way you want. If you at any time in future want to change the "type" of data you use a field for - you can go right ahead - while the file is in use, without any interuption at all to the running DBMS and/or application code. There simply is no data type to change - from varchar to array or anything else. I feel I am repeating myself - but it is something I see as illustrating a clear difference between the rigidity of SQL and the freedom of Pick.

Now - if you want to add one field to another then obviously they have to contain numeric data. This is one of the clever things about Pick. Although any field can contain any kind of data - what *is* significant is the way the data is used. If you want to do something silly - like add one ProductNumber to another then there is nothing at all to stop you doing that. The only objection Pick might have is to raise a runtime warning if one of the ProductNumbers contains non-numeric data - in which case it treats the non-numeric data as zero and continues (unless you have error trapping switched on, in which case it throws you into the debugger). This is generally a bad thing to have happen - and usually indicates either a coding error or data corruption. These things can and do happen in an imperfect world from time to time. You can minimise the chances of this sort of thing happening though by ensuring you have adequate data integrity checking and decent change-control. You make your own rules.

> 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.
>

No. None at all.

> > > > > - 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.
>

You're still limited to using the SQLanguage aren't you - if you want to actually use (read/write) data in an SQL-relational DBMS? In Pick we can use that too - if we want to. Most choose not to.

> > > 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

Original text of this message