Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:50106
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.132 cause and error)
Message-ID: <4CD3C012.5020807@lsces.co.uk>
Date: Fri, 05 Nov 2010 08:28:02 +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>
In-Reply-To: <AANLkTinFk6TC5OmUHExE7rwLKFh6mo_DWpbwP6dc2Pjw@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:
>> 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 ;) )

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

I still can't simply switch between the generic firebird driver and the PDO one 
in ADOdb so using PDO is academic at the moment. ADOdb does a GOOD job 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 as long as 
one bothers to think about that using PDO. Most 'conversions' simply 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=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