Stanley Sufficool wrote:
On Fri, Nov 5, 2010 at 1:28 AM, Lester Caine<les...@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
--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
