On Sun, Nov 7, 2010 at 2:33 AM, Lester Caine <les...@lsces.co.uk> wrote: > 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!
Seems simple to me, with the option to restrict to a specific memory footprint. 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 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 :) 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 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 > > -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
