Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:50109
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=rp96obQ3VgeF3gLrOFvhD26fwdz218IDG6R+DlnOfyj9rZt6aSbxaIxxEjYP9h7oQB
         GpHvE2haOunSC8nDIXqZ5+ke1urbyU2xaQeZiwzAZ3caAImD3fE/Bh5U18WJcvAxXCHI
         wGFW6ABxKXpm+poyygpF6D0pZLi620K5ixvZM=
MIME-Version: 1.0
In-Reply-To: <4CD3C012.5020807@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>
Date: Fri, 5 Nov 2010 19:09:35 -0700
Message-ID: <AANLkTi=XJHJpNYgO+iV5_WPVa256jDfqtd_5yAHkd+Dc@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 Fri, Nov 5, 2010 at 1:28 AM, Lester Caine <lester@lsces.co.uk> wrote:
> Stanley Sufficool wrote:
>>>
>>> Rule Of Thumb:
>>> > =A0If it's a BLOB, and you aren't using it in WHERE or ORDER BY, then=
 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 sim=
ply
> 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 o=
n
> 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 sea=
rch
> 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.

>
> Of cause the character set used for that data is then the next problem an=
d
> 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/Japan=
ese
> 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.

>
> I still can't simply switch between the generic firebird driver and the P=
DO
> one in ADOdb so using PDO is academic at the moment. ADOdb does a GOOD jo=
b
> of managing the data types and I can switch between most db's with very
> little problem ... data wise ... it's always going to be the SQL that is =
the
> real problem, but with care that can be made reasonably generic as well a=
s
> long as one bothers to think about that using PDO. Most 'conversions' sim=
ply
> break what was good cross db sql to 'simplify' things for PDO and then
> actually LOOSE access to most other db's :(
>
> --
> 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
>
>