Re: MV and SQL

x wrote:

> <michael_at_preece.net> wrote in message
> news:1137637589.943828.10850_at_z14g2000cwz.googlegroups.com...
> >
> > x wrote:
> >
> > > With SQL DBMSs one :
> > > - have the liberty to define any field to be a varchar no matter how bad
> the
> > > design is
> >
> > Well... with Pick you don't have to define it at all - as a varchar or
> > as anything else. You just start using it. Saying
> > "thisfield=thatfield+theotherfield" requires that both thatfield and
> > theotherfield be assigned a value prior to this code being executed
> > though - or you get a runtime warning and thatfield and/or
> > theotherfield are treated as being equal to zero. You can use it as a
> > "varchar" if you want - and/or as a boolean - and/or as a numeric...
> > anything. You can perform string operations on it, add it to another
> > field, then put it through code that says "if
> > thisfield+thatfield[startlen,forlen]-theotherfield then...". When you
> > perform enquiries on it using the query language then, again, how the
> > field is treated depends on its use - or the dictionary definition.
>
> > Total freedom. Of course this can be abused - and it often is. People
> > often experience difficulties when trying to extract data from Pick to
> > move to an SQL-relational DB because a single field, or column if you
> > prefer, can have a mixture of data types.
>
> 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.

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

What I'm dealing with in the application code is "fields" in attributes (or lower levels - multivalues and subvalues) in files. They are logical entities. An "attribute" in an "item" in Pick is similar, I think, to a column in a row in an SQL-relational table - except that it can contain >=0 "fields". It can be a scalar or a set (if I've got the terminology right this time). Or it can be both at the same time. You can simply start using a field that has always previously been used as a scalar as a set. It can be of infinite length and has no "type" as such. At the physical level it's simply a delimited part of a string containing an infinite number of delimited strings of infinite length, which can in turn contain an infinite number of delimited strings of infinite length. There's nothing to stop me getting down into the physical realm and using my own delimiters to go beyond the subvalue level - if I want. The fact that they are delimited strings can be useful - if you want to perform operations on a group of "fields" and treat them as a single string - but, generally, they are treated as variables in their own right - or as elements in a dimensioned or dynamic array. To me the purely physical level is all about frames and file control blocks and all that side of things. I couldn't care less about that most of the time. I just open a file, read an item, and go to work on it in any way I choose. I can also issue a command to get a single "field" (be it an attribute or multivalue or whatever (oconv('myitem','tmyfile;x1;;1)) without any file opens or reads. I generally prefer not to issue direct reads and writes though. I have a set of IO routines - or procedures - I like to use. That way I can build in any additional smarts I want to extend the DBMS's out-of-the-box capabilities. You don't have to go to this extent - it's just my preferred approach. The DBMS handles the physical side of things for me in any case.

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

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

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

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

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

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

Lock-handling and things like that are a function of the DBMS - and come as standard. I have done quite a bit of work in extending these capabilites btw (to handle maintenance of pessimistic locks for non-persistent web applications) - but again, this is just an "added extra" I've developed on my own because I can. A handful of others have developed similar capabilities in an effort to give their applications an edge. I'm pretty sure some of the Pick DBMS vendors - IBM in particular have this on their list of things to do. I also have a list of things to do - and that list is fed by new developments and capabilities I learn about in non-Pick DBMSs. Anything they can do...

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

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

I honestly do appreciate that SQL is very powerful, btw. If I've done nothing else in this post, though, I hope I have managed to show why I think Pick is so much more powerful - and liberating.

Regards,
Mike. Received on Thu Jan 19 2006 - 16:02:20 CET