[ale] MySQL help

[DJ-Pfulio]

ORMs are double edged.

Some are buggy and create lots of opportunities for SQL-injection attacks.

Some are slow and suck RAM ... like Java, Outlook, iTunes.

Some will be faster to code, create better (faster/safer) SQL than an
intermediate developer, only call the DBMS when results are actually needed,
and protect against all the currently known attacks ... I'm talking about DBIx,
of course.  The ORM that all other languages wish they had. ;)

Oh - and more ORMs abstract the DBMS away - swap in whatever back-end DB you
like - mysql, postgres, sqlite, mariadb ... are usually each supported with
others often supported like Oracle, DB2, Informix, etc. Some ORMs might support
noSQL too, but using a specialized class is probably best.

I haven't written any direct SQL in webapps in about 7 yrs. It just hasn't been
worth my time. If there are fewer than 20K concurrent users, I wouldn't bother
writing SQL until specific profiling showed it was needed.

In the 1990s, my company did "migrations" for our DBs in a way similar to
Rails::Migrate ... Clearly, I like that method and wish more ORMs did it that way.

For internal corporate webapps, I'd always start with an ORM regardless of
language - perl, python, ruby.  These are each wonderful languages and folks
should understand how great it is to have choices like these today.

Perl + DBIx + Dancer + CPAN is an awesome toolkit for whipping out a webapp or
creating an enterprise web application developed for years.

Ruby + Rails/Sinatra + ActiveRecord (or one of 10 other Ruby ORMs) ... ruby-gems
is only slightly less so. Gems and ActiveRecord are beasts, but ruby is a
gorgeous language. Maintaining Gems takes too much RAM.  I've had to add a GB of
RAM to VMs to patch Gems, otherwise, the updates would never complete. Then take
the RAM away to keep monthly costs reasonable.

No experience with python - my personal issue with languages that care about
whitespace. Wish python were my first language instead of FORTRAN 66, so I
wouldn't have the issue.

Of course, this is just 1 person's opinion. Certainly, direct SQL written by an
expert can be great too, but getting to that level of competence is just not
necessary most of the time.


On 03/13/2015 03:00 PM, Charles Shapiro wrote:
> Heh. Hadda look that one up.   Could work great, depending on resources and
> purpose.
> 
> -- CHS
> 
> 
> On Fri, Mar 13, 2015 at 2:51 PM, DJ-Pfulio <DJPfulio at jdpfu.com> wrote:
> 
>> Or just use an ORM.
>>
>> On 03/13/2015 02:36 PM, Charles Shapiro wrote:
>>> Hmm. I'm not that familiar with MySQL, but in PostgreSQL the way to
>> determine
>>> what you want is
>>>
>>> select
>>>    animal.id <http://animal.id>
>>> from
>>>    animal
>>> left join
>>>    food
>>> on
>>>    animal.id <http://animal.id> = food.animalid
>>> where
>>>    food.id <http://food.id> is null
>>> ;
>>>
>>>
>>> Writing SQL queries takes some practice.  One handy tip I've found is to
>> write
>>> the "from..." part first, then go back and fill in what columns you wish
>> to select.
>>>
>>> Doing anything based on the numeric value of the "id" field is a Bad
>> Idea.  In
>>> most relational databases, that field just increments when you add a
>> record to
>>> your table.  It never decrements when you delete records.  That means
>> that the
>>> ID value itself is useless in determining what is or is not in a table.
>>  It's
>>> only useful when it's in another table -- hence the name, "Relational".
>>>
>>>
>>> Your current table setup also breaks down if you add -- say -- a goat
>> which
>>> wants to eat the same things as the giraffe. You're more likely to have
>> several
>>> animals linked to the same foods than several foods linked to the same
>> animal.
>>> Don't you really want a "FoodFk" in the "animal" table?  Then you could
>> have
>>> lots of animals which eat the same thing.  Of course, I reckon in
>> reality you'd
>>> need a jump table to link foods and animals:
>>>
>>> food_animal { integer animalid references animal(id), integer foodid
>> references
>>> food(id) }
>>>
>>> Then you could have lots of different foods and lots of different
>> animals all
>>> linked together.
>>>
>>>
>>> -- CHS
>>>
>>>
>>> On Fri, Mar 13, 2015 at 1:52 PM, David S Jackson <deepbsd.ale at gmail.com
>>> <mailto:deepbsd.ale at gmail.com>> wrote:
>>>
>>>     Hi,
>>>
>>>     I'm a MySQL newbie.  I'm just starting to use Python to talk to MySQL
>>>     databases, but first I need to understand the MySQL query language!
>>>
>>>
>>>     So, I have two tables:  (zoo) animal and food.
>>>
>>>     animal has the columns ID, NAME, FAMILY, WEIGHT
>>>
>>>     food has  ID, ANIMALID, FEED
>>>
>>>
>>>     So if I go: select id, name, family, feed from animal JOIN food ON
>> animal.id
>>>     <http://animal.id>=food.animalid;
>>>
>>>
>>>     I get something like:
>>>
>>>     ID   NAME        FAMILY      FEED
>>>     1    Seymore   Snake        mice, leaves
>>>     2    Gerard      Giraffe       leaves, grass
>>>     ...
>>>     etc
>>>
>>>     So, if I have a situation where I want to see whether an animal has
>> been
>>>     added the animal table but may have not been added to the food
>> table, how
>>>     would I compose that query?
>>>
>>>     That is, the animal could have been added to the animal table and
>> could get
>>>     an id, say 10, but the food table could have 20 foods already
>> entered and
>>>     the animal-id would be used several times.
>>>
>>>     I was thinking, is there a way I can ask, "does the highest
>> animal.id
>>>     <http://animal.id> number equal the highest food.animalid number"?
>> If not,
>>>     what animal is not getting fed?