subject:"\[Lazarus\] Lazarus, Jedi etc."

On 16 Jul 2013 20:39, "Mark Morgan Lloyd" 
wrote:
>
> Henry Vermaak wrote:
>>
>> On Tue, Jul 16, 2013 at 12:23:10PM +0200, Reinier Olislagers wrote:
>>>
>>> On 16-7-2013 11:53, Henry Vermaak wrote:

 Ranting from a position of ignorance seriously dilutes your credibility
 in my book.
>>>
>>> I'm just saying the rants may be justified if mysql cannot be configured
>>> to follow the RDBMS integrity norms.
>>> Obviously you don't know if that is possible either, so let's give the
>>> ranters the benefit of the doubt.
>>
>>
>> Sigh, I thought I was clear on that.  Using Graeme's example:
>>
>> mysql> set sql_mode = TRADITIONAL;
>> Query OK, 0 rows affected (0.00 sec)
>>
>> mysql> create table a (b int not null, c int not null);
>> Query OK, 0 rows affected (0.10 sec)
>>
>> mysql> insert into a (b) values (1);
>> ERROR 1364 (HY000): Field 'c' doesn't have a default value
>> mysql>
>
>
> I certainly sympathise with your argument Henry, but I'm a bit concerned
if MySQL is prepared to accept a "not null" constraint that it does not
intend to enforce. So assuming that there is no sql_mode change between
these two commands (using the earlier example):
>
>
> mysql> create table a (b int not null, c int not null);
> Query OK, 0 rows affected (0.17 sec)
>
>
> mysql> insert into a (b) values (1);
> Query OK, 1 row affected, 1 warning (0.03 sec)
>
> one or other of them should at the very least raise a warning.

Not specifying a default is perfectly legitimate, that's why the insert
fails in strict mode (in my example).  You just have to enable the strict
mode, since the default mysql mode uses an implicit default.

Henry
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-16 Thread Mark Morgan Lloyd


Henry Vermaak wrote:

On Tue, Jul 16, 2013 at 12:23:10PM +0200, Reinier Olislagers wrote:

On 16-7-2013 11:53, Henry Vermaak wrote:

Ranting from a position of ignorance seriously dilutes your credibility
in my book.

I'm just saying the rants may be justified if mysql cannot be configured
to follow the RDBMS integrity norms.
Obviously you don't know if that is possible either, so let's give the
ranters the benefit of the doubt.


Sigh, I thought I was clear on that.  Using Graeme's example:

mysql> set sql_mode = TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)

mysql> create table a (b int not null, c int not null);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into a (b) values (1);
ERROR 1364 (HY000): Field 'c' doesn't have a default value
mysql> 


I certainly sympathise with your argument Henry, but I'm a bit concerned 
if MySQL is prepared to accept a "not null" constraint that it does not 
intend to enforce. So assuming that there is no sql_mode change between 
these two commands (using the earlier example):


mysql> create table a (b int not null, c int not null);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into a (b) values (1);
Query OK, 1 row affected, 1 warning (0.03 sec)

one or other of them should at the very least raise a warning.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On Tue, Jul 16, 2013 at 12:23:10PM +0200, Reinier Olislagers wrote:
> On 16-7-2013 11:53, Henry Vermaak wrote:
> > Ranting from a position of ignorance seriously dilutes your credibility
> > in my book.
> 
> I'm just saying the rants may be justified if mysql cannot be configured
> to follow the RDBMS integrity norms.
> Obviously you don't know if that is possible either, so let's give the
> ranters the benefit of the doubt.

Sigh, I thought I was clear on that.  Using Graeme's example:

mysql> set sql_mode = TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)

mysql> create table a (b int not null, c int not null);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into a (b) values (1);
ERROR 1364 (HY000): Field 'c' doesn't have a default value
mysql> 

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On 16-7-2013 11:53, Henry Vermaak wrote:
> On Tue, Jul 16, 2013 at 11:22:44AM +0200, Reinier Olislagers wrote:
>> On 16-7-2013 11:17, Henry Vermaak wrote:
>>> On Tue, Jul 16, 2013 at 08:33:45AM +, Mark Morgan Lloyd wrote:
 If it really is so bad, why have so many developers- in particularly
 web developers- clasped it to their bosom?
>>>
>>> Probably not, no, given that it's used by wikipedia, google, twitter,
>>> etc.  It just seems like Graeme and Michael are complaining that it
>>> doesn't default to strict mode (i.e. traditional mode in mysql
>>> parlance).  Lots of credibility lost if people rant about something that
>>> they can actually configure the way they like.
>>
>> Would you happen to know how those people programmed their backends?
>> Using the crummy code mentioned in the thread or code that expects RDBMS
>> behaviour?
> 
> Sorry, my crystal ball is in for repairs, you'll have to spell out your
> point for me.

Popularity does not necessarily correspond with the best solution. See
thread up to now.

>> There's lots of whacky code out there that still is used in production,
>> and patched endlessly whenever the next error pops up. Doesn't mean it's
>> a good idea to go down that path.
> 
> A universal truth, but I can't see how it's unique to mysql?

As indicated in the thread, mysql does not enforce RDBMS concepts so
writing incorrect, loose code is easier/more inviting in comparison with
writing similar code for PostgreSQL, Firebird etc.
Similar to the bad reputation Microsoft Access got because it was so
easy to slap together some VBA/macros and call it an application.

>> That said, I agree about the configuring+ranting part, but I'm not sure
>> to what level of strictness you can actually configure MySQL. And I'm
>> not interested enough to find out - Firebird suits me just fine.
> 
> Ranting from a position of ignorance seriously dilutes your credibility
> in my book.

I'm just saying the rants may be justified if mysql cannot be configured
to follow the RDBMS integrity norms.
Obviously you don't know if that is possible either, so let's give the
ranters the benefit of the doubt.

In case you were referring to me as a ranter: I wasn't complaining about
the integrity stuff; my issues are more with the ridiculous client
library version matching thing as well as the signs that Oracle are
making it as difficult as possible for others to fix bugs/get insight in
the code.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-16 Thread Chavoux Luyt

Hi Henry

On 16 July 2013 10:28,  wrote:

>  From: Henry Vermaak 
>  

> If it really is so bad, why have so many developers- in particularly
> > web developers- clasped it to their bosom?
>
> Probably not, no, given that it's used by wikipedia, google, twitter,
> etc.  It just seems like Graeme and Michael are complaining that it
> doesn't default to strict mode (i.e. traditional mode in mysql
> parlance).  Lots of credibility lost if people rant about something that
>  they can actually configure the way they like.

Of the three examples updates happen relatively infrequently on wikipedia,
so transactional integrity is not really important at all.
And in both the other examples speed, rather than data integrity, is the
priority (i.e. if google misses a search result or two, does it really
matter? or if I miss a tweet now and then, does it really matter?). Speed
is important, however for these applications. This is where mySQL shines.

Michael Schnell 
> I seem to find out that this discussion is about experts making fun on
> each other, just wasting some bandwidth :-) .
I hope not! It really depends on what you want from the DB. If speed,
rather than data integrity is your priority, then mySQL is a good choice
(plus part of LAMP : although here I would advise you to use Python rather
than PHP). But if you are working with financial data, it is a really bad
idea and you need a proper RDMS.

Cheers
Chaovux
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On Tue, Jul 16, 2013 at 11:28:38AM +0200, Michael Schnell wrote:
> On 07/16/2013 11:17 AM, Henry Vermaak wrote:
> >Probably not, no, given that it's used by ...
> 
> I seem to find out that this discussion is about experts making fun
> on each other, just wasting some bandwidth :-) .

They're just paying you back in kind :)

Henry

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-16 Thread Marc Weustink


Graeme Geldenhuys wrote:

On 2013-07-15 17:40, Ludo Brands wrote:

Throw anything at it and MySQL will swallow. Your data are in good hands ;)



OK, I changed my mind about MySQL I'll not touch it with a 50 foot pole!

That "database server" (I cringed calling it that) is an absolute joke.
Please do all developers a favour and spread the word.


I'm already doing that since the early days of MySQL. I somewhere read a 
statement of the early core devels that he didn't like constraints. His 
statement (from what I recall): "Thats something what should be checked 
in the client, the DB shouldn't enforce that"


OK, exit MySQL as DB

Marc




--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On Tue, Jul 16, 2013 at 11:22:44AM +0200, Reinier Olislagers wrote:
> On 16-7-2013 11:17, Henry Vermaak wrote:
> > On Tue, Jul 16, 2013 at 08:33:45AM +, Mark Morgan Lloyd wrote:
> >> If it really is so bad, why have so many developers- in particularly
> >> web developers- clasped it to their bosom?
> > 
> > Probably not, no, given that it's used by wikipedia, google, twitter,
> > etc.  It just seems like Graeme and Michael are complaining that it
> > doesn't default to strict mode (i.e. traditional mode in mysql
> > parlance).  Lots of credibility lost if people rant about something that
> > they can actually configure the way they like.
> 
> Would you happen to know how those people programmed their backends?
> Using the crummy code mentioned in the thread or code that expects RDBMS
> behaviour?

Sorry, my crystal ball is in for repairs, you'll have to spell out your
point for me.

> There's lots of whacky code out there that still is used in production,
> and patched endlessly whenever the next error pops up. Doesn't mean it's
> a good idea to go down that path.

A universal truth, but I can't see how it's unique to mysql?

> That said, I agree about the configuring+ranting part, but I'm not sure
> to what level of strictness you can actually configure MySQL. And I'm
> not interested enough to find out - Firebird suits me just fine.

Ranting from a position of ignorance seriously dilutes your credibility
in my book.

Henry

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-16 Thread Michael Van Canneyt




On Tue, 16 Jul 2013, Michael Schnell wrote:


On 07/16/2013 11:17 AM, Henry Vermaak wrote:

Probably not, no, given that it's used by ...


I seem to find out that this discussion is about experts making fun on each 
other, just wasting some bandwidth :-) .


That's the best joke I've heard in a long time.

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-16 Thread Mark Morgan Lloyd


Reinier Olislagers wrote:


There's lots of whacky code out there that still is used in production,
and patched endlessly whenever the next error pops up. Doesn't mean it's
a good idea to go down that path.

That said, I agree about the configuring+ranting part, but I'm not sure
to what level of strictness you can actually configure MySQL. And I'm
not interested enough to find out - Firebird suits me just fine.


I think an important point is that MySQL was freely-available long 
before Firebird. I looked at it in around 2000 when working on a demo to 
show a corporate, and rejected it because (a) it didn't have 
transactions and (b) I didn't fancy telling their DP department, who ran 
either Oracle or DB2 on a mainframe, that I was using something with 
such a tacky name.


PostgreSQL has done fine for us since then, and my recent experiences 
getting Firebird to the state where a program could create/destroy users 
etc. makes me wonder how much of Postgres's reputation for being 
difficult to configure is undeserved.


Whatever, this isn't the place for a database war. The important point 
was that I was able to point out to the OP (elsewhere) that Lazarus/FPC 
had database support that his version/variant of Delphi lacked.


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-16 Thread Michael Schnell


On 07/16/2013 11:17 AM, Henry Vermaak wrote:

Probably not, no, given that it's used by ...


I seem to find out that this discussion is about experts making fun on 
each other, just wasting some bandwidth :-) .


-Michael

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On 16-7-2013 11:17, Henry Vermaak wrote:
> On Tue, Jul 16, 2013 at 08:33:45AM +, Mark Morgan Lloyd wrote:
>> If it really is so bad, why have so many developers- in particularly
>> web developers- clasped it to their bosom?
> 
> Probably not, no, given that it's used by wikipedia, google, twitter,
> etc.  It just seems like Graeme and Michael are complaining that it
> doesn't default to strict mode (i.e. traditional mode in mysql
> parlance).  Lots of credibility lost if people rant about something that
> they can actually configure the way they like.

Would you happen to know how those people programmed their backends?
Using the crummy code mentioned in the thread or code that expects RDBMS
behaviour?
There's lots of whacky code out there that still is used in production,
and patched endlessly whenever the next error pops up. Doesn't mean it's
a good idea to go down that path.

That said, I agree about the configuring+ranting part, but I'm not sure
to what level of strictness you can actually configure MySQL. And I'm
not interested enough to find out - Firebird suits me just fine.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On Tue, Jul 16, 2013 at 08:33:45AM +, Mark Morgan Lloyd wrote:
> Reinier Olislagers wrote:
> 
> >It's not a bug, it's a feature: MySQL accepts data other DBs wouldn't
> >touch with a 10 foot pole ;)
> >(Don't ask about it proceeding and mangling that data - that's beside
> >the point ;) )
> 
> If it really is so bad, why have so many developers- in particularly
> web developers- clasped it to their bosom?

Probably not, no, given that it's used by wikipedia, google, twitter,
etc.  It just seems like Graeme and Michael are complaining that it
doesn't default to strict mode (i.e. traditional mode in mysql
parlance).  Lots of credibility lost if people rant about something that
they can actually configure the way they like.

Henry

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On 16-7-2013 10:59, Michael Van Canneyt wrote:
> On Tue, 16 Jul 2013, Mark Morgan Lloyd wrote:
>> Reinier Olislagers wrote:
>>
>>> It's not a bug, it's a feature: MySQL accepts data other DBs wouldn't
>>> touch with a 10 foot pole ;)
>>> (Don't ask about it proceeding and mangling that data - that's beside
>>> the point ;) )
>>
>> If it really is so bad, why have so many developers- in particularly
>> web developers- clasped it to their bosom?
> 
> Simple: laziness.
True.

> I seriously doubt any bank would consider using MySQL for their
> financial data.
Much better to shell out the big bucks to the likes of IBM and Oracle so
you can always say to your PHB that you're covered by a support contract
and a gold-plated company ;)

> For the cookies of their website: no problem. But not the actual account
> information. Imagine their horror when the database decided to round all
> numbers up for no apparent reason :)

Well, it depends. As indicated in the thread, if you configure mysql
well, it starts to behave a bit more RDBMS-like.
If you are disciplined writing programs that access it (or let e.g. an
ORM like Hibernate do the discipline for you), you could use MySQL as well.
The % of applications living up to that are probably not that big but
they're probably there.

That said, with Oracle's recent shenanigans, I'd think PostgreSQL
popularity should surely be rising.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-16 Thread Chavoux Luyt

Hi Mark

On 16 July 2013 09:47,  wrote:

> Send Lazarus mailing list submissions to
> lazarus@lists.lazarus.freepascal.org
>
>   From: Mark Morgan Lloyd 
>  

If it really is so bad, why have so many developers- in particularly web
> developers- clasped it to their bosom?
>
For two main reasons:
1. It is (used to be) fast compared to other DB's while still handling
fairly large amounts of data.
2. Data integrity was not important to them (or they wanted to do all their
data checking on the web interface side by hand and considered it as
overkill or an unneeded resource drag on the DB side).

I have heard enough horror stories of data corruption in mySQL to agree
that it is in many ways still a toy DB and not a real RDBMS. However, when
data integrity is not that important and DB speed is a bigger priority, it
still has a role IMHO... at least it is a better choice than using text
files for storing your data.

Cheers
Chavoux
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-16 Thread Michael Van Canneyt

On Tue, 16 Jul 2013, Mark Morgan Lloyd wrote:

Reinier Olislagers wrote:

It's not a bug, it's a feature: MySQL accepts data other DBs wouldn't
touch with a 10 foot pole ;)
(Don't ask about it proceeding and mangling that data - that's beside
the point ;) )

If it really is so bad, why have so many developers- in particularly web
developers- clasped it to their bosom?

Simple: laziness.

Because it comes bundled with the LAMP stack, in particular with PHP.

So it is set up. Ready to use. Easy to use. Just like PHP itself.
So why go to the trouble of installing a more reliable database ?

If the lamp stack had been bundled with Postgres from day 1, it would have the
DB of choice.
[ and it would have been called a LAPP stack ;) ]

There is a second reason: when using PHP, the things a normal Pascal or C or C# or Java
developer thinks are "bad", suddenly become features: '' = 0 = Nil = NULL = false to a
PHP programmer.

They literally throw anything at their MySQL DB. And it eats it. Easy !

When using a "real" programming language you do things differently (at least I hope you do),
and then the junk you get (which was thrown in by the PHP programmer) suddenly causes all
kinds of trouble, because the data does not obey the rules you take for granted.

I seriously doubt any bank would consider using MySQL for their financial data.
For the cookies of their website: no problem. But not the actual account information.
Imagine their horror when the database decided to round all numbers up for no apparent reason :)

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-16 Thread Mark Morgan Lloyd


Reinier Olislagers wrote:


It's not a bug, it's a feature: MySQL accepts data other DBs wouldn't
touch with a 10 foot pole ;)
(Don't ask about it proceeding and mangling that data - that's beside
the point ;) )


If it really is so bad, why have so many developers- in particularly web 
developers- clasped it to their bosom?


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On 15-7-2013 18:40, Ludo Brands wrote:
> On 07/15/2013 05:53 PM, Graeme Geldenhuys wrote:
> Need other default (mis)behaviors for MySQL?
> -insert "123test" in an integer field: no error, it inserts 123
> -insert "123test" in a datetime field: no error, it inserts -00-00
> 00:00:00
> -insert 123 in a datetime field: no error, it inserts 2000-01-23
> 00:00:00 . Yes you got that right, inserting integer 11231 translates in
> 2001-12-31 00:00:00. 11232 gives -00-00 00:00:00 again. Insert
> 11231.133 and you get 2001-12-31 00:00:00 again
> -now that you understand the integer to datatime mapping do an UPDATE
> table SET date=date+4, you get 2001-12-31 00:00:04. UPDATE table SET
> date=date+400 and you obtain 2001-12-31 00:04:04.
> -UPDATE table SET date=date*2 results in -00-00 00:00:00. No error.
> -more MySQL arithmetic: insert "2001-1+10+3" into a date field: You get
> 2001-01-10 03:00:00. "2001-1*10/4^2" results in 2001-01-10 04:02:00
> 
> Throw anything at it and MySQL will swallow. Your data are in good hands ;)
> 

It's not a bug, it's a feature: MySQL accepts data other DBs wouldn't
touch with a 10 foot pole ;)
(Don't ask about it proceeding and mangling that data - that's beside
the point ;) )


--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-15 Thread Flávio Etrusco

On Mon, Jul 15, 2013 at 1:40 PM, Ludo Brands  wrote:
>
> Need other default (mis)behaviors for MySQL?
> -insert "123test" in an integer field: no error, it inserts 123
> -insert "123test" in a datetime field: no error, it inserts -00-00
> 00:00:00
> -insert 123 in a datetime field: no error, it inserts 2000-01-23
> 00:00:00 . Yes you got that right, inserting integer 11231 translates in
> 2001-12-31 00:00:00. 11232 gives -00-00 00:00:00 again. Insert
> 11231.133 and you get 2001-12-31 00:00:00 again
> -now that you understand the integer to datatime mapping do an UPDATE
> table SET date=date+4, you get 2001-12-31 00:00:04. UPDATE table SET
> date=date+400 and you obtain 2001-12-31 00:04:04.
> -UPDATE table SET date=date*2 results in -00-00 00:00:00. No error.
> -more MySQL arithmetic: insert "2001-1+10+3" into a date field: You get
> 2001-01-10 03:00:00. "2001-1*10/4^2" results in 2001-01-10 04:02:00
>
> Throw anything at it and MySQL will swallow. Your data are in good hands ;)
>
> Ludo
>
> --

- Decimals with greater precision than the column don't trigger error,
but are rounded instead;
- Check constraints are parsed but ignored;
- Char columns are not padded in the default configuration;
- Char and varchar values that are longer than the column size don't
trigger errors in the default configuration;
- Varchar values used to be always trimmed before save, not long ago.
- Changing a column to "not null" doesn't check existing values.

"Toy DB" is almost an overstatement :-/

-Flávio

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On 2013-07-15 17:40, Ludo Brands wrote:
> Throw anything at it and MySQL will swallow. Your data are in good hands ;)


OK, I changed my mind about MySQL I'll not touch it with a 50 foot pole!

That "database server" (I cringed calling it that) is an absolute joke.
Please do all developers a favour and spread the word.

Regards,
  - Graeme -



--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-15 Thread Luca Olivetti

Al 15/07/13 15:12, En/na Mark Morgan Lloyd ha escrit:

> There was also a requirement to interface to PLCs etc. (using, I'm told,
> the nodave library)

For the record, libnodave works wonderfully with fpc.

Bye
-- 
Luca Olivetti
Wetron Automation Technology http://www.wetron.es
Tel. +34 935883004  Fax +34 935883007

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-15 Thread Mark Morgan Lloyd


Graeme Geldenhuys wrote:


Then depending on which filesystem your MySQL database is, it affectsthe case 
sensitivity


Definitely a problem. However even on something like PostgreSQL there 
can be case-sensitivity issues with e.g. usernames/roles.


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On Mon, Jul 15, 2013 at 04:37:35PM +0200, Michael Van Canneyt wrote:
> On Mon, 15 Jul 2013, Henry Vermaak wrote:
> >Could you explain why the above is bad for those of us that aren't DB
> >experts?  To my untrained eye it looks fairly plausible, so I'm
> >obviously missing something.
> 
> Simple. The statement
> 
> create table a (b int not null, c int not null);
> 
> creates a table called 'a', in which the fields b and c are required.
> that is, they cannot be NULL.
> 
>  mysql> insert into a (b) values (1);
> 
> Attempts to insert a record with value NULL for C, which is
> explicitly forbidden by the definition of the table.

0 != NULL, it's inserting the default value for the field, which seems
to be 0 for an integer field.

Does e.g. postgres error on an insert statement like this?

Henry

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On Mon, Jul 15, 2013 at 03:44:42PM +0100, Graeme Geldenhuys wrote:
> On 2013-07-15 15:06, Henry Vermaak wrote:
> > Could you explain why the above is bad for those of us that aren't DB
> > experts?  To my untrained eye it looks fairly plausible, so I'm
> > obviously missing something.
> 
> I never inserted data into column C, so MySQL decided on its own that it
> should magically contain the value 0 — inventing data! Last time I
> checked 0 <> null in SQL terms either.

I seem to remember something about table columns having default values
in the definition, so perhaps column C defaults to 0?  0 != NULL, so it
didn't break your definition?

Henry

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-15 Thread Ludo Brands

On 07/15/2013 05:53 PM, Graeme Geldenhuys wrote:
> On 2013-07-15 16:40, Henry Vermaak wrote:
>> If you want an INSERT statement to generate an error unless you
>> explicitly specify values for all columns that do not have a default
>> value, you should use strict mode."
> 
> 
> And G*d only knows why they have such modes in the first place! You
> would think that if they do value data (what you put in is what you get
> out), there would only be 'strict mode', or at least make that the more
> sane and default mode. Like I said, I installed MySQL on FreeBSD from
> source code and didn't customise any settings.
> 
> The case sensitivity in the table names is also crazy, especially if the
> database gets moved. I had to modify quite a lot of SQL statements
> because of that. For MySQL support I had to look up the table name (as
> MySQL has it), then modify lots and lots of SQL in my application to
> match that case exactly. Crazy stuff!
> 
> Regards,
>   G.
> 

Need other default (mis)behaviors for MySQL?
-insert "123test" in an integer field: no error, it inserts 123
-insert "123test" in a datetime field: no error, it inserts -00-00
00:00:00
-insert 123 in a datetime field: no error, it inserts 2000-01-23
00:00:00 . Yes you got that right, inserting integer 11231 translates in
2001-12-31 00:00:00. 11232 gives -00-00 00:00:00 again. Insert
11231.133 and you get 2001-12-31 00:00:00 again
-now that you understand the integer to datatime mapping do an UPDATE
table SET date=date+4, you get 2001-12-31 00:00:04. UPDATE table SET
date=date+400 and you obtain 2001-12-31 00:04:04.
-UPDATE table SET date=date*2 results in -00-00 00:00:00. No error.
-more MySQL arithmetic: insert "2001-1+10+3" into a date field: You get
2001-01-10 03:00:00. "2001-1*10/4^2" results in 2001-01-10 04:02:00

Throw anything at it and MySQL will swallow. Your data are in good hands ;)

Ludo

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On Mon, Jul 15, 2013 at 04:53:25PM +0100, Graeme Geldenhuys wrote:
> The case sensitivity in the table names is also crazy, especially if
> the database gets moved. I had to modify quite a lot of SQL statements
> because of that. For MySQL support I had to look up the table name (as
> MySQL has it), then modify lots and lots of SQL in my application to
> match that case exactly. Crazy stuff!

I understand the case sensitivity problem, since tables are stored in
separate files for some engines (I think).  There's a simple solution,
though, use lower_case_table_names=1, in which case tables will be
stored in lower case and lookups become case insensitive.  No need to
change SQL statements.  You may have to set this before you import the
database on the new system.  Doesn't seem like such a big deal to me,
after a little bit of RTFM.

Henry

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On 2013-07-15 16:42, Michael Van Canneyt wrote:
>>
>> I seem to remember something about table columns having default values
>> in the definition, so perhaps column C defaults to 0?
> 
> It should only do that when specifically instructed.

Exactly... SQL's data definition language has specific support for
default values. It's up to the DDL field definitions, not the database
engine/server to decide on default values.

Regards,
  G.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On 2013-07-15 16:40, Henry Vermaak wrote:
> If you want an INSERT statement to generate an error unless you
> explicitly specify values for all columns that do not have a default
> value, you should use strict mode."

And G*d only knows why they have such modes in the first place! You
would think that if they do value data (what you put in is what you get
out), there would only be 'strict mode', or at least make that the more
sane and default mode. Like I said, I installed MySQL on FreeBSD from
source code and didn't customise any settings.

The case sensitivity in the table names is also crazy, especially if the
database gets moved. I had to modify quite a lot of SQL statements
because of that. For MySQL support I had to look up the table name (as
MySQL has it), then modify lots and lots of SQL in my application to
match that case exactly. Crazy stuff!

Regards,
  G.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On Mon, 15 Jul 2013, Henry Vermaak wrote:

On Mon, Jul 15, 2013 at 04:34:21PM +0100, Henry Vermaak wrote:

On Mon, Jul 15, 2013 at 03:44:42PM +0100, Graeme Geldenhuys wrote:
> On 2013-07-15 15:06, Henry Vermaak wrote:
> > Could you explain why the above is bad for those of us that aren't DB
> > experts?  To my untrained eye it looks fairly plausible, so I'm
> > obviously missing something.
> 
> I never inserted data into column C, so MySQL decided on its own that it

> should magically contain the value 0 — inventing data! Last time I
> checked 0 <> null in SQL terms either.

I seem to remember something about table columns having default values
in the definition, so perhaps column C defaults to 0?  0 != NULL, so it
didn't break your definition?

Just checked the docs:

"If you are not running in strict SQL mode, any column not explicitly
given a value is set to its default (explicit or implicit) value.
...
If you want an INSERT statement to generate an error unless you
explicitly specify values for all columns that do not have a default
value, you should use strict mode."

Seems fair enough to me?

That is not RDBMS compliant behaviour.

RDBMs is an established mathematical theory, not something that a bunch of 
IT-ers with their 'anything goes' attitude invented.
All RDBMSes are supposed to behave the same.

So the same statement is supposed to behave the same on all RDBMSes, 
given the same definition of the table. The statement given in the 
example is supposed to give an error, period.

You can't just say "oh well, we'll just ignore the rules for some time"

Like in: 
I just killed my neighbour, but hey, I changed the law for 5 seconds while I did it, 
so no problem !

Michael.--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.




On Mon, 15 Jul 2013, Henry Vermaak wrote:


On Mon, Jul 15, 2013 at 04:37:35PM +0200, Michael Van Canneyt wrote:

On Mon, 15 Jul 2013, Henry Vermaak wrote:

Could you explain why the above is bad for those of us that aren't DB
experts?  To my untrained eye it looks fairly plausible, so I'm
obviously missing something.


Simple. The statement

create table a (b int not null, c int not null);

creates a table called 'a', in which the fields b and c are required.
that is, they cannot be NULL.

 mysql> insert into a (b) values (1);

Attempts to insert a record with value NULL for C, which is
explicitly forbidden by the definition of the table.


0 != NULL, it's inserting the default value for the field, which seems
to be 0 for an integer field.


There is no default value specified.

You MUST specify a default value if you want one. 
If you didn't specify one, the Database engine should not apply one on its own.



Does e.g. postgres error on an insert statement like this?


Yes. As does firebird.

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On Mon, 15 Jul 2013, Henry Vermaak wrote:

On Mon, Jul 15, 2013 at 03:44:42PM +0100, Graeme Geldenhuys wrote:

On 2013-07-15 15:06, Henry Vermaak wrote:
> Could you explain why the above is bad for those of us that aren't DB
> experts?  To my untrained eye it looks fairly plausible, so I'm
> obviously missing something.

I never inserted data into column C, so MySQL decided on its own that it
should magically contain the value 0 — inventing data! Last time I
checked 0 <> null in SQL terms either.

I seem to remember something about table columns having default values
in the definition, so perhaps column C defaults to 0?

It should only do that when specifically instructed.

Michael.--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On 2013-07-15 15:06, Henry Vermaak wrote:
> Could you explain why the above is bad for those of us that aren't DB
> experts?  To my untrained eye it looks fairly plausible, so I'm
> obviously missing something.

I never inserted data into column C, so MySQL decided on its own that it
should magically contain the value 0 — inventing data! Last time I
checked 0 <> null in SQL terms either.

Regards,
  G.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.




On Mon, 15 Jul 2013, Henry Vermaak wrote:


On Mon, Jul 15, 2013 at 02:49:06PM +0100, Graeme Geldenhuys wrote:

On 2013-07-15 13:43, leledumbo wrote:


Or if they insist on using MySQL (compatible alternative): MariaDB


Nope, still not! I'll not touch MySQL, MariaDB or anything based on
those with a 10 foot pole! If you value your data, stay away from those two.

Another developer enlightened me on how bad MySQL really is, and I
confirmed what he said on two of my servers (Linux and FreeBSD).

Here is the simple test to show how bad MySQL/MariaDB is...

-[ Forwarded message ]---
This test was done on a newly setup FreeBSD server - no tweaking to
MySQL was done - just the default install.

mysql> create table a (b int not null, c int not null);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into a (b) values (1);
Query OK, 1 row affected, 1 warning (0.03 sec)

As if the above was not miraculous enough, then you get:

mysql> select * from a;
+-+-+
| b | c |
+-+-+
| 1 | 0 |
+-+-+
1 row in set (0.00 sec)

Unbelievable!


Could you explain why the above is bad for those of us that aren't DB
experts?  To my untrained eye it looks fairly plausible, so I'm
obviously missing something.


Simple. The statement

create table a (b int not null, c int not null);

creates a table called 'a', in which the fields b and c are required.
that is, they cannot be NULL.

 mysql> insert into a (b) values (1);

Attempts to insert a record with value NULL for C, which is explicitly 
forbidden by the definition of the table.


The definition of a RDBMS states that it must enforce all database constraints.

The above violates that.

The last statement then shows that MySQL has silently inserted 0 as a value for 
the field C.
That's even worse. It invents data.

(I could name more examples)

sqlite has the same problem, and even worse. It does not even enforce the 
correct type.


Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On Mon, Jul 15, 2013 at 02:49:06PM +0100, Graeme Geldenhuys wrote:
> On 2013-07-15 13:43, leledumbo wrote:
> > 
> > Or if they insist on using MySQL (compatible alternative): MariaDB
> 
> Nope, still not! I'll not touch MySQL, MariaDB or anything based on
> those with a 10 foot pole! If you value your data, stay away from those two.
> 
> Another developer enlightened me on how bad MySQL really is, and I
> confirmed what he said on two of my servers (Linux and FreeBSD).
> 
> Here is the simple test to show how bad MySQL/MariaDB is...
> 
> -[ Forwarded message ]---
> This test was done on a newly setup FreeBSD server - no tweaking to
> MySQL was done - just the default install.
> 
> mysql> create table a (b int not null, c int not null);
> Query OK, 0 rows affected (0.17 sec)
> 
> mysql> insert into a (b) values (1);
> Query OK, 1 row affected, 1 warning (0.03 sec)
> 
> As if the above was not miraculous enough, then you get:
> 
> mysql> select * from a;
> +-+-+
> | b | c |
> +-+-+
> | 1 | 0 |
> +-+-+
> 1 row in set (0.00 sec)
> 
> Unbelievable!

Could you explain why the above is bad for those of us that aren't DB
experts?  To my untrained eye it looks fairly plausible, so I'm
obviously missing something.

Henry

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On 2013-07-15 14:38, Michael Van Canneyt wrote:
> People using MySQL should have their developers license revoked :)


I like that! :)


Regards,
  G.



--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On 2013-07-15 13:43, leledumbo wrote:
> 
> Or if they insist on using MySQL (compatible alternative): MariaDB

Nope, still not! I'll not touch MySQL, MariaDB or anything based on
those with a 10 foot pole! If you value your data, stay away from those two.

Another developer enlightened me on how bad MySQL really is, and I
confirmed what he said on two of my servers (Linux and FreeBSD).

Here is the simple test to show how bad MySQL/MariaDB is...

-[ Forwarded message ]---
This test was done on a newly setup FreeBSD server - no tweaking to
MySQL was done - just the default install.

mysql> create table a (b int not null, c int not null);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into a (b) values (1);
Query OK, 1 row affected, 1 warning (0.03 sec)

As if the above was not miraculous enough, then you get:

mysql> select * from a;
+-+-+
| b | c |
+-+-+
| 1 | 0 |
+-+-+
1 row in set (0.00 sec)

Unbelievable!

Then depending on which filesystem your MySQL database is, it affects
the case sensitivity of the table names! Why, oh why, let a filesystem
dictate requirements in your database schema. eg: Moving a database from
Windows to Linux/FreeBSD/etc Under Windows the database schema will
not be case sensitive, and your applications work wonderful. Move that
database to a more secure Linux/FreeBSD system, and suddenly your
application is broken - because the filesystem under Linux/FreeBSD is
case sensitive and your SQL statements didn't know that. This is
apparently caused by MySQL because it uses a directory structure to
define tables - one directory per database table. Absolutely ridiculous!

Then there are cases where MySQL magically does Number-to-String and
vice-versa conversions.

MySQL is nothing but a toy database "server" (if you can call it that) -
you really must not place any value on your data. My clients value their
data a lot, hence we use a real database server...Firebird is a good fit
for most of my clients.
---[ end of message ]

Regards,
  - Graeme -

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.




On Mon, 15 Jul 2013, Mark Morgan Lloyd wrote:


leledumbo wrote:
Indeed! More people should be introduced [and educated] to the much better 
open source database servers out there, like Firebird and
PostgreSQL. 


Or if they insist on using MySQL (compatible alternative): MariaDB


The OP's backend was half constructed.

There was also a requirement to interface to PLCs etc. (using, I'm told, the 
nodave library), the specific problem he'd got was MySQL access which- 
according to him at least- is not well-supported by Delphi. In that context I 
risked mentioning Lazarus, but I think that trying to tell him to simply use 
a different database would have been counterproductive since (a) he could 
then choose one that suited his version/variant of Delphi and (b) it was in a 
private conferencing topic dedicated to Delphi, not to Life After Delphi.


So while I use Lazarus, PostgreSQL and (tentatively) FireBird, there are 
limits to how aggressively I'm prepared to push the combination. Particularly 
when the other party is more experienced than I am and has been known to wear 
a journalist's hat.



People using MySQL should have their developers license revoked :)

MySQL is not even worthy of the name database.

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-15 Thread Mark Morgan Lloyd


leledumbo wrote:
Indeed! More people should be introduced [and educated] to the much 
better open source database servers out there, like Firebird and
PostgreSQL. 


Or if they insist on using MySQL (compatible alternative): MariaDB


The OP's backend was half constructed.

There was also a requirement to interface to PLCs etc. (using, I'm told, 
the nodave library), the specific problem he'd got was MySQL access 
which- according to him at least- is not well-supported by Delphi. In 
that context I risked mentioning Lazarus, but I think that trying to 
tell him to simply use a different database would have been 
counterproductive since (a) he could then choose one that suited his 
version/variant of Delphi and (b) it was in a private conferencing topic 
dedicated to Delphi, not to Life After Delphi.


So while I use Lazarus, PostgreSQL and (tentatively) FireBird, there are 
limits to how aggressively I'm prepared to push the combination. 
Particularly when the other party is more experienced than I am and has 
been known to wear a journalist's hat.


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

2013-07-15 Thread leledumbo

> Indeed! More people should be introduced [and educated] to the much 
> better open source database servers out there, like Firebird and
> PostgreSQL. 

Or if they insist on using MySQL (compatible alternative): MariaDB



--
View this message in context: 
http://free-pascal-lazarus.989080.n3.nabble.com/Lazarus-Lazarus-Jedi-etc-tp4032338p4032397.html
Sent from the Free Pascal - Lazarus mailing list archive at Nabble.com.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.

On 2013-07-13 15:06, reinierolislag...@gmail.com wrote:
> The fact that they're connecting to
> the horror that is MySQL says enough

Indeed! More people should be introduced [and educated] to the much
better open source database servers out there, like Firebird and PostgreSQL.

Regards,
  G.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Lazarus, Jedi etc.