Re: MV and SQL


x wrote:

> <michael@xxxxxxxxxx> wrote in message
> news:1137637589.943828.10850@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> >
> > 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.

.

Relevant Pages

  • Re: MV and SQL
    ... > be related to the language used with the DBMS. ... > or xsd have dynamic typing. ... The query language in Pick is just that - a query language. ... >>> The LIST statement mentioned in the SOFACards is analogous to the SQL ...
    (comp.databases.theory)
  • Re: MV and SQL
    ... >> perform enquiries on it using the query language then, again, how the ... > Are you sure that a column in Pick is equivalent to a column in an SQL table ... If you had some bizarre need to do this in a Pick DBMS though, ... > Besides the query language of Pick what other ways do ...
    (comp.databases.theory)
  • Re: MV and SQL
    ... >> perform enquiries on it using the query language then, again, how the ... > Are you sure that a column in Pick is equivalent to a column in an SQL table ... If you had some bizarre need to do this in a Pick DBMS though, ... > Besides the query language of Pick what other ways do ...
    (comp.databases.theory)
  • Re: MV and SQL
    ... >>> Are you sure that a column in Pick is equivalent to a column in an SQL ... > You can also change the type from varchar to array if your SQL DBMS support ... > for variables that store the data retrieved from the database. ... >>> SQL is not the only language. ...
    (comp.databases.theory)
  • Re: choices regarding where to place code - in the database or middle tier
    ... Sure, the DBMS is a good place for simple referential integrity constraints, ... to 4 separately-running-but-pipelined stored procedures, ... A typical user would enact a 100 or so business functions per day. ... own stored procedures' by storing the SQL for every business query in the DBMS ...
    (comp.lang.java.databases)