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. > > 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. > > 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 > > -- > PHP Internals - PHP Runtime Development Mailing List > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
