[mythtv] Database helper functions and pgsql support
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.
fvilas at iname.com
More information about the mythtv-dev