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

[Caveat]

Thanks for the great horse story Bruce.  Willy asked me once whether I 
preferred MySQL or postgreSQL and I couldn't really come up with a very 
smart answer! Perhaps that helps :-)

The only thing I find odd is why you don't just use the microchip ID as 
the primary key... then a name change would just be a change of 
attribute.  You can still create a unique index on the name (so lookups 
aren't slower, duplicate names are avoided at the db level...).  Or does 
your system have to register horses before they're chipped?

And, of course, we all know my penny story is ultimately nonsense!  Deep 
down, we all realise that if the pennies are so indistinguishable, then 
all we need is one 'pennies' object and a count.  Putting aside the 
storage concerns, even in logical terms you'd quickly realise that you 
don't need 13 separate identical objects to represent 13 pennies!  Same 
for what Willy is doing, but I guess he's dealing with something that's 
been done that way historically for one reason or another.

Kind regards,
Caveat

On 06/10/13 02:25, Bruce wrote:
> 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
>
>
>
>
>
> ------------------------------------------------------------------------------
> October Webinars: Code for Performance
> Free Intel webinars can help you accelerate application performance.
> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
> the latest Intel processors and coprocessors. See abstracts and register >
> http://pubads.g.doubleclick.net/gampad/clk?id=60134791&iu=/4140/ostg.clktrk
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
> .
>