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

[Charlie Reinl]

Am Sonntag, den 06.10.2013, 10:55 +1030 schrieb 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

Salut Bruce,

how do you look back to 'Lemon Liniment' history ?


-- 
Amicalement
Charlie