[mythtv] Database helper functions and pgsql support

Fernando Vilas fvilas at iname.com
Tue Aug 16 02:30:37 UTC 2005

On Sunday August 14 2005 18:38, Nezar Nielsen wrote:
> On 8/13/05, Robert Johnston <anaerin at gmail.com> wrote:
> > Is it not possible, during searches, to do "UPPER()"'s on both Search
> > terms and the field they're searching on, to make them case
> > insensitive again. So, for example:
> > 
> > SELECT program_name, description WHERE (UPPER(program_name) LIKE
> > UPPER('%Search String%')) OR (UPPER(description) LIKE UPPER('%Search
> > String%'))
> If you want to do a case-insensitive LIKE in pgsql, you just go:
> SELECT field FROM table WHERE column ilike '%sEaRcH%'
> Also, if you want to do bulk-inserts, use:
> COPY table (id, column) FROM stdin;
> 1	firstrow
> 2	secondrow
> 3	thirdrow
> \.

I have been trying to keep the code as-is, with as little modification as possible.  Since ilike is not portable and lower() is, I will probably implement it that way, thus keeping the same statement, regardless of DBMS.  It is not a small task, since it involves tracking down every use of like or = on a string type, which is scattered through several joins and wheres throughout the code.  I will get to it at some point, unless someone else wants to give it a shot, but I wanted to get a patch out with the DB helper functions before my copy diverged too greatly from svn.  

That's a good point about the bulk inserts, but the code as-is works something along the lines of "insert into <table> (columns) select <columns> from <really long join> where <somewhat shorter where clause>".  However, aside from mythfilldatabase, most of the bulk inserts are in the scheduler.  I have been investigating ways to make the tables used by the scheduler, like recordmatch, into views, hopefully making life simpler.  The snag I have hit on that one is the numeric constants for the record types are stored in the compiled code and not in the DB.  I have not found a satisfactory solution to this, but I am open to suggestions.

I hope these responses aren't coming off as too argumentative.  I do appreciate everyone's comments and am working to make support for different DBMSs as easy as possible.  To that end, I am trying to make the DB backend as DB agnostic as possible.  One of the goals is that to add another database to the supported DB list, a developer would only have to modify libs/libmythtv/dbhelper.cpp to provide support, then add it to the list for the user to select it.

Fernando Vilas
fvilas at iname.com

More information about the mythtv-dev mailing list