Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:50117
Mailing-List: contact internals-help@lists.php.net; run by ezmlm
Received-SPF: error (pb1.pair.com: domain lsces.co.uk from 213.123.20.127 cause and error)
Message-ID: <4CD67264.1000808@lsces.co.uk>
Date: Sun, 07 Nov 2010 09:33:24 +0000
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.14) Gecko/20101013 SUSE/2.0.9-2.1 SeaMonkey/2.0.9
MIME-Version: 1.0
To: PHP internals <internals@lists.php.net>
References: <AANLkTikKybPrGWLj3rT3ydvf8H0N6O6mHP=MEmnL58Yh@mail.gmail.com>	<24c7fdf08ac80c097306908d0c4b5512.squirrel@www.l-i-e.com>	<AANLkTikifSrLNFu+Ui6ZtdkyOtmgbEF60=J-0UhRXEnJ@mail.gmail.com>	<ec385a4a428945f591e5748d6af8053f.squirrel@www.l-i-e.com>	<AANLkTinFk6TC5OmUHExE7rwLKFh6mo_DWpbwP6dc2Pjw@mail.gmail.com>	<4CD3C012.5020807@lsces.co.uk> <AANLkTi=XJHJpNYgO+iV5_WPVa256jDfqtd_5yAHkd+Dc@mail.gmail.com>
In-Reply-To: <AANLkTi=XJHJpNYgO+iV5_WPVa256jDfqtd_5yAHkd+Dc@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Subject: Re: [PHP-DEV] PDO_DBLIB Native PHP Type binding
From: lester@lsces.co.uk (Lester Caine)

Stanley Sufficool wrote:
> On Fri, Nov 5, 2010 at 1:28 AM, Lester Caine<lester@lsces.co.uk>  wrote:
>> Stanley Sufficool wrote:
>>>>
>>>> Rule Of Thumb:
>>>>>   If it's a BLOB, and you aren't using it in WHERE or ORDER BY, then it
>>>>>   doesn't belong in the DB.
>>>>>   Put it in the custom highly-optimized specialized database engine
>>>>>   commonly known as "the file system" where it belongs.:-)
>>>
>>> FWIW, large objects cannot be used in ORDER BY in MSSQL, and
>>> relational integrity is best achieved by having your data in a
>>> -relational- database.
>>>
>>> Think mugshots, digitized signatures, etc. I really do not wish to
>>> cycle through a record set to do a pass to delete the images on disk
>>> by filename reference and then run another SQL to delete the records.
>>
>> The first brick wall I hit when simply trying to use PDO was this one. On
>> firebird big text fields are BLOB ( oracle is the same - CLOB ) and I simply
>> want to read them as strings, but PDO 'optimizes' them as streams. Text
>> search is best handled IN the database and the content needs to be there to
>> search on or create indexes on. So while keeping all the binary content on
>> my websites in the file system is acceptable, doing that with the text is
>> simply wrong. ( Until perhaps a reach the point when an external text search
>> option WOULD be more efficient ;) )
>
> PDO "optimizes" as a stream because PHP may be limited in memory and a
> full fetch on a large object could be upwards of 2 GB. Regardless of
> preferences on filesystem vs database storage of LOBs, there are
> plenty of people using large objects in the database that are suitable
> for sequential stream access.

BUT at least having a switch to disable that action would be nice?
BOTH actions are required SELECTIVELY by field if one database uses a large text 
field while the other uses a BLOB for the same size field. Current ports of 
projects TO PDO fall flat because Firebird and Oracle can't then access the text 
fields!

>> Of cause the character set used for that data is then the next problem and
>> has to be unicode internally since even little things like addresses need to
>> be able to accept international users ... I don't have many Chinese/Japanese
>> customers, but the address book can at least handle them :)
>>
>> In the database
>> Rule one ... times are all UTC
>> Rule two ... text is all unicode
>
> Rule three ... expect that rules 1 and 2 won't be followed by whoever
> designed the database before you and everything will be local specific
> and varchar.

In that case the first step is to clean up the mess to sort the problems out 
that those bad practices have forced on the 'project'. As soon as I stripped all 
of the 'server time' crap from the original tikiwiki some time ago making a 
working cross timezone calendar was actually possible ... up until then it was 
always wrong for half of the year in some areas :)

But porting a project CURRENTLY to PDO is not a sensible way to tidy things up :(

-- 
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php