Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:50121
Mailing-List: contact internals-help@lists.php.net; run by ezmlm
Received-SPF: pass (pb1.pair.com: domain gmail.com designates 209.85.216.42 as permitted sender)
DomainKey-Status: bad
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :cc:content-type:content-transfer-encoding;
        b=HD0EVsMF9Xeoppz0IPLi3G9zmeQspQMF6lNTWOsX2NhMH6VdikaoofseQAng7dfnxB
         Hp8V4M/aaMUn8rn3I6yAkWvwCvHBu12gXkBCswoL/Chu0uWUU+pm9nS4GnRmP5vBoSig
         1xcJedaSL7j0qT4O/dcJYX5HwYPh55Yt5+z7M=
MIME-Version: 1.0
In-Reply-To: <4CD67264.1000808@lsces.co.uk>
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>
	<4CD67264.1000808@lsces.co.uk>
Date: Sun, 7 Nov 2010 11:07:05 -0700
Message-ID: <AANLkTimHzjB5xr-vMFSewbVcznH6q8M7DxZLV72YCPYC@mail.gmail.com>
To: Lester Caine <lester@lsces.co.uk>
Cc: PHP internals <internals@lists.php.net>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Subject: Re: [PHP-DEV] PDO_DBLIB Native PHP Type binding
From: ssufficool@gmail.com (Stanley Sufficool)

On Sun, Nov 7, 2010 at 2:33 AM, Lester Caine <lester@lsces.co.uk> wrote:
> Stanley Sufficool wrote:
>>
>> On Fri, Nov 5, 2010 at 1:28 AM, Lester Caine<lester@lsces.co.uk> =A0wrot=
e:
>>>
>>> Stanley Sufficool wrote:
>>>>>
>>>>> Rule Of Thumb:
>>>>>>
>>>>>> =A0If it's a BLOB, and you aren't using it in WHERE or ORDER BY, the=
n it
>>>>>> =A0doesn't belong in the DB.
>>>>>> =A0Put it in the custom highly-optimized specialized database engine
>>>>>> =A0commonly 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 ther=
e
>>> 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 lar=
ge
> 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!

Seems simple to me, with the option to restrict to a specific memory footpr=
int.
http://php.net/manual/en/function.stream-get-contents.php

>
>>> 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 ne=
ed
>>> 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 strip=
ped
> all of the 'server time' crap from the original tikiwiki some time ago
> making a working cross timezone calendar was actually possible ... up unt=
il
> then it was always wrong for half of the year in some areas :)

Unless there is an existing client application that uses the database
in it's current state. The example here is a supplemental application
that has to co-exist with another ancient application until all
functionality has been replicated. In this case, the applications data
only exists in one timezone and in one language/code page.

>
> But porting a project CURRENTLY to PDO is not a sensible way to tidy thin=
gs
> up :(
>
> --
> Lester Caine - G8HFL
> -----------------------------
> Contact - http://lsces.co.uk/wiki/?page=3Dcontact
> 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
>
> --
> PHP Internals - PHP Runtime Development Mailing List
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>