[Gambas-user] [slightly OT] No Problem with Gambas or MySql!

[Bruce]

On Sun, 2013-10-06 at 00:58 +0200, Willy Raets wrote:
> On Sun, 2013-10-06 at 00:05 +0200, Caveat wrote:
> > > If you wanted to delete one row with values 347 200, then which row would it be?
> > 
> > It *doesn't matter*!  The rows are the same, so pick a row, ANY row!
> 
> That is exactly what I have been doing for the last 10 years.
> In VB code I just looked for the criteria in the table and deleted the
> first record that met the criteria, next update table and ready.
> 
> In Gambas I tried the same, now with the table being in MySql, and it
> didn't work.
> 
> It never, ever failed me in those 10 years, even without a primary key.
> Although most other tables do have primary keys and relations set up,
> there never was a need for this table to have a primary key.
> And I still do not see the need for a primary key.
> 
> > 
> > > Without the primary key, you're asking MySQL to guess, and it wont.
> > 
> > Yes it will, if you use LIMIT (see my previous two mails).  Otherwise, it'll happily delete all the rows that meet the criteria, whether they're duplicates or not.
> > 
> 
> And even in MySql there is NO need for the table to have a primary key
> as the 'SELECT ... limit 1' will do what I expect, delete only 1 record
> that meets the criteria.
> 
> > > >"duplicate rows are and always were a mistake in SQL", C.J.Date
> 
> Duplicate rows are a reality in real life data gathering.
> I have been working with databases since dbase somewhere in the '80's
> and I have had to deal with real life situations concerning data
> storage, not with theory for ideal models. Not saying I haven't read my
> share on relational database design and take it into account when
> designing a database.
> 
> > 
> > The guy has obviously never read William Kent's "Data and Reality". It's 
> > a book I'd still recommend to anyone working with data and particularly 
> > databases, even though it was written 35 years ago!
> > 
> > <RANT>
> > Why *must* every record be uniquely identifiable?  Explain why... don't 
> > just throw random snippets of relational database theory at me!  If I 
> > ask you to model the contents of your pockets on a database, are you 
> > going to attach some artificial key to each individual penny?  Why?  
> > They each buy the same number of penny chews (showing my age!), and if 
> > you take 3 pennies away, you've still got 3 pennies less than you had 
> > before, no matter how carefully you select those 3 pennies!
> > </RANT>
> > 
> > <HINT>
> > A tuple is, by definition, unique.  If I have 8 rows on my database 
> > representing 8 real-world penny objects and I can delete 3 of them... 
> > are the remaining 5 rows tuples?  How closely does the database model 
> > reality in this case?
> > </HINT>
> 
> I like your penny story ;)
> 
> 

[ASIDE!]

Interesting discussion, but someone recently asked me why I prefer
postgresql over mysql and this thread reminded me that I never answered.

One of those reasons is that this whole situation cannot occur in
postgresql.  It has been designed such that it cannot. Considering a
non-normalised table, i.e. one that has no natural (unique) primary key,
all rows are still capable of being operated on using the standard sql
commands. However, in that case, *all* rows that succeed the WHERE
filter clause will be acted on. Fullstop. 

Putting that another way, postgresql "doesn't really care" whether the
table has a primary key set or not. Nor does it care whether a delete or
an update query uses a unique row identifier.  That sounds pretty slack,
doesn't it?  Well, not at all really.  It relies on the WHERE clause to
identify the set of rows to be operated on and that is all.  If such a
clause identifies multiple rows then they will all be affected. So, to
continue the coin collection analogy and a theoretical sql command:
SELL COIN WHERE YEAR="1930" AND ALLOY="Cupronickel";
would sell all the coins that match.  The idea that you could write a
command like:
SELL THE FIRST COIN YOU FIND WHERE YEAR="1930" AND ALLOY="Cupronickel";
really, to me brings a sense of randomness into the game ... ??? What is
the dbms supposed to imagine "THE FIRST COIN YOU FIND" to mean?

Here in the thoroughbred racing industry, we have a somewhat similar
real world situation.  All thoroughbreds have a registered name and
there are strict controls over the reuse of a name.  However, horse
owners are allowed to change the registered name of the horse
occasionally and under certain conditions. All pretty good and you could
imagine that the name "Lemon Liniment" would uniquely identify a
specific horse from any of the other 61,000 we have on our database.
Further you might imagine that if the owners changed its' name to
"Cherry Zontop" then all we would have to do is, once the name change
has been registered, change that "unique" primary key.  Hah ha!  No such
simplicity exists on the planet.

First off, the horse is really and truly identified by its microchip ID.
This is what the register uses as the unique identifier.  However, they
don't publish that (and for good reasons if you think about it ...) and
secondly name change events are frequently if not often publicised some
time after the actual change has been registered.  This is a major,
major problem, (to us, logistically not technically).

Suppose Jack (the owner of Lemon Liniment) sells it to Jill (on
Wednesday) who re-registers it as Cherry Zontop.  Jill then races it
once on the next Saturday and we see a race entrance and result appear
in the official race records for Cherry Zontop.  We think that Cherry
Zontop is a "new untried" horse and add it as a new horse in the
database.  On the Monday (or later) the registry publicises the name
change...

Under MySQL merging the histories of Lemon Liniment into Cherry Zontop
was a technical nightmare that essentially required offloading and
reloading multiple sets of rows from multiple tables*.  Under postgresql
it is the execution of one SQL command:
UPDATE horse SET name='Cherry Zontop', namechanged=TRUE, oldname='Lemon
Liniment' WHERE name='Lemon Liniment';

* I have simplified the story a fair bit, there are around 18 underlying
tables that have the horse name as the foreign key or as part of the
foreign key. 

postgres, because it properly supports "ON UPDATE CASCADE" constraints
on those tables and their foreign key columns handles it all internally.

Anyway, as I said, [ASIDE] and just one of the reasons for preferring
postgresql.

regards
Bruce