int
cmyth_mysql_delete_scheduled_recording(cmyth_database_t db, char * query)
{
int rows=0;
if(cmyth_db_check_connection(db) != 0)
{
cmyth_dbg(CMYTH_DBG_ERROR, "%s: cmyth_db_check_connection failed\n",
__FUNCTION__);
fprintf(stderr,"%s: cmyth_db_check_connection failed\n", __FUNCTION__);
return -1;
}
cmyth_dbg(CMYTH_DBG_ERROR, "mysql query :%s\n",query);
if(mysql_real_query(db->mysql,query,(unsigned int) strlen(query))) {
cmyth_dbg(CMYTH_DBG_ERROR, "%s: mysql_query() Failed: %s\n",
__FUNCTION__, mysql_error(db->mysql));
return -1;
}
rows=mysql_affected_rows(db->mysql);
if (rows <=0) {
cmyth_dbg(CMYTH_DBG_ERROR, "%s: mysql_query() Failed: %s\n",
__FUNCTION__, mysql_error(db->mysql));
}
return rows;
}
int
cmyth_mythtv_remove_previos_recorded(cmyth_database_t db,char *query)
{
MYSQL_RES *res=NULL;
char N_query[128];
int rows;
if(cmyth_db_check_connection(db) != 0)
{
cmyth_dbg(CMYTH_DBG_ERROR, "%s: cmyth_db_check_connection failed\n",
__FUNCTION__);
fprintf(stderr,"%s: cmyth_db_check_connection failed\n", __FUNCTION__);
return -1;
}
mysql_real_escape_string(db->mysql,N_query,query,strlen(query));
if(mysql_query(db->mysql,query)) {
cmyth_dbg(CMYTH_DBG_ERROR, "%s: mysql_query() Failed: %s\n",
__FUNCTION__, mysql_error(db->mysql));
return -1;
}
res = mysql_store_result(db->mysql);
rows=mysql_insert_id(db->mysql);
if (rows <=0) {
cmyth_dbg(CMYTH_DBG_ERROR, "%s: mysql_query() Failed: %s\n",
__FUNCTION__, mysql_error(db->mysql));
}
return rows;
}
char *
cmyth_get_recordid_mysql(cmyth_database_t db, int chanid, char *title, char *subtitle, char *description, char *seriesid, char *programid)
{
MYSQL_RES *res=NULL;
MYSQL_ROW row;
char query[1000];
int count;
if(cmyth_db_check_connection(db) != 0)
{
cmyth_dbg(CMYTH_DBG_ERROR, "%s: cmyth_db_check_connection failed\n", __FUNCTION__);
fprintf(stderr,"%s: cmyth_db_check_connection failed\n", __FUNCTION__);
return NULL;
}
sprintf (query,"SELECT recordid FROM record WHERE (chanid=%d AND title='%s' AND subtitle='%s' AND description='%s' AND seriesid='%s' AND programid='%s')",chanid,title,subtitle,description,seriesid,programid);
cmyth_dbg(CMYTH_DBG_ERROR, "%s : query=%s\n",__FUNCTION__, query);
if(mysql_query(db->mysql,query)) {
cmyth_dbg(CMYTH_DBG_ERROR, "%s: mysql_query() Failed: %s\n", __FUNCTION__, mysql_error(db->mysql));
return NULL;
}
res = mysql_store_result(db->mysql);
if ( (count = mysql_num_rows(res)) >0) {
row = mysql_fetch_row(res);
fprintf(stderr, "row grabbed done count=%d\n",count);
mysql_free_result(res);
return row[0];
}
else {
mysql_free_result(res);
return "NULL";
}
}
MYSQL *
cmyth_db_get_connection(cmyth_database_t db)
{
if(cmyth_db_check_connection(db) != 0)
{
cmyth_dbg(CMYTH_DBG_ERROR, "%s: cmyth_db_check_connection failed\n",
__FUNCTION__);
return NULL;
}
/*
* mythbackend stores any multi-byte characters using utf8 encoding within latin1 database
* columns. The MySQL connection needs to be told to use a utf8 character set when reading the
* database columns or any multi-byte characters will be treated as 2 or 3 subsequent latin1
* characters with nonsense values.
*
* http://www.mythtv.org/wiki/Fixing_Corrupt_Database_Encoding#Note_on_MythTV_0.21-fixes_and_below_character_encoding
* http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html
*/
if(mysql_query(db->mysql,"SET NAMES utf8;")) {
cmyth_dbg(CMYTH_DBG_ERROR, "%s: mysql_query() failed: %s\n", __FUNCTION__, mysql_error(db->mysql));
return NULL;
}
return db->mysql;
}
MYSQL *
cmyth_db_get_connection(cmyth_database_t db)
{
if(cmyth_db_check_connection(db) != 0)
{
cmyth_dbg(CMYTH_DBG_ERROR, "%s: cmyth_db_check_connection failed\n",
__FUNCTION__);
return NULL;
}
return db->mysql;
}
int
cmyth_mysql_get_prog_finder_char_title(cmyth_database_t db, cmyth_program_t **prog, time_t starttime, char *program_name)
{
MYSQL_RES *res=NULL;
MYSQL_ROW row;
char query[350];
int rows=0;
int n = 50;
if(cmyth_db_check_connection(db) != 0)
{
cmyth_dbg(CMYTH_DBG_ERROR, "%s: cmyth_db_check_connection failed\n",
__FUNCTION__);
fprintf(stderr,"%s: cmyth_db_check_connection failed\n", __FUNCTION__);
return -1;
}
if (strncmp(program_name, "@", 1)==0)
snprintf(query, 350, "SELECT DISTINCT title FROM program " \
"WHERE ( title NOT REGEXP '^[A-Z0-9]' AND " \
"title NOT REGEXP '^The [A-Z0-9]' AND " \
"title NOT REGEXP '^A [A-Z0-9]' AND " \
"starttime >= FROM_UNIXTIME(%d)) ORDER BY title", \
(int)starttime);
else
snprintf(query, 350, "SELECT DISTINCT title FROM program " \
"where starttime >= FROM_UNIXTIME(%d) and " \
"title like '%s%%' ORDER BY `title` ASC",
(int)starttime, program_name);
fprintf(stderr, "%s\n", query);
cmyth_dbg(CMYTH_DBG_ERROR, "%s: query= %s\n", __FUNCTION__, query);
if(mysql_query(db->mysql,query)) {
cmyth_dbg(CMYTH_DBG_ERROR, "%s: mysql_query() Failed: %s\n",
__FUNCTION__, mysql_error(db->mysql));
return -1;
}
res = mysql_store_result(db->mysql);
while((row = mysql_fetch_row(res))) {
if (rows == n) {
n++;
*prog=realloc(*prog,sizeof(**prog)*(n));
}
sizeof_strncpy ( (*prog)[rows].title, row[0]);
cmyth_dbg(CMYTH_DBG_ERROR, "prog[%d].title = %s\n",rows, (*prog)[rows].title);
rows++;
}
mysql_free_result(res);
cmyth_dbg(CMYTH_DBG_ERROR, "%s: rows= %d\n", __FUNCTION__, rows);
return rows;
}
int
cmyth_mysql_insert_into_record(cmyth_database_t db, char * query, char * query1, char * query2, char *title, char * subtitle, char * description, char * callsign)
{
int rows=0;
char *N_title;
char *N_subtitle;
char *N_description;
char *N_callsign;
char N_query[2570];
if(cmyth_db_check_connection(db) != 0)
{
cmyth_dbg(CMYTH_DBG_ERROR, "%s: cmyth_db_check_connection failed\n",
__FUNCTION__);
fprintf(stderr,"%s: cmyth_db_check_connection failed\n", __FUNCTION__);
return -1;
}
N_title = ref_alloc(strlen(title)*2+1);
mysql_real_escape_string(db->mysql,N_title,title,strlen(title));
N_subtitle = ref_alloc(strlen(subtitle)*2+1);
mysql_real_escape_string(db->mysql,N_subtitle,subtitle,strlen(subtitle));
N_description = ref_alloc(strlen(description)*2+1);
mysql_real_escape_string(db->mysql,N_description,description,strlen(description));
N_callsign = ref_alloc(strlen(callsign)*2+1);
mysql_real_escape_string(db->mysql,N_callsign,callsign,strlen(callsign));
snprintf(N_query,2500,"%s '%s','%s','%s' %s '%s' %s",query,N_title,N_subtitle,N_description,query1,N_callsign,query2);
ref_release(N_title);
ref_release(N_subtitle);
ref_release(N_callsign);
cmyth_dbg(CMYTH_DBG_ERROR, "mysql query :%s\n",N_query);
if(mysql_real_query(db->mysql,N_query,(unsigned int) strlen(N_query))) {
cmyth_dbg(CMYTH_DBG_ERROR, "%s: mysql_query() Failed: %s\n",
__FUNCTION__, mysql_error(db->mysql));
return -1;
}
rows=mysql_insert_id(db->mysql);
if (rows <=0) {
cmyth_dbg(CMYTH_DBG_ERROR, "%s: mysql_query() Failed: %s\n",
__FUNCTION__, mysql_error(db->mysql));
}
return rows;
}
int
cmyth_mysql_get_prev_recorded(cmyth_database_t db, cmyth_program_t **prog)
{
MYSQL_RES *res= NULL;
MYSQL_ROW row;
int n=0;
int rows=0;
const char *query = "SELECT oldrecorded.chanid, UNIX_TIMESTAMP(starttime), UNIX_TIMESTAMP(endtime), title, subtitle, description, category, seriesid, programid, channel.channum, channel.callsign, channel.name, findid, rectype, recstatus, recordid, duplicate FROM oldrecorded LEFT JOIN channel ON oldrecorded.chanid = channel.chanid ORDER BY `starttime` ASC";
if(cmyth_db_check_connection(db) != 0)
{
cmyth_dbg(CMYTH_DBG_ERROR, "%s: cmyth_db_check_connection failed\n", __FUNCTION__);
fprintf(stderr,"%s: cmyth_db_check_connection failed\n", __FUNCTION__);
return -1;
}
if(mysql_query(db->mysql,query)) {
cmyth_dbg(CMYTH_DBG_ERROR, "%s: mysql_query() Failed: %s\n",
__FUNCTION__, mysql_error(db->mysql));
return -1;
}
res = mysql_store_result(db->mysql);
while((row = mysql_fetch_row(res))) {
if (rows >= n) {
n+=10;
*prog=realloc(*prog,sizeof(**prog)*(n));
}
(*prog)[rows].chanid = safe_atoi(row[0]);
(*prog)[rows].recording=0;
(*prog)[rows].starttime=(time_t)safe_atol(row[1]);
(*prog)[rows].endtime= (time_t)safe_atol(row[2]);
sizeof_strncpy((*prog)[rows].title, row[3]);
sizeof_strncpy((*prog)[rows].subtitle, row[4]);
sizeof_strncpy((*prog)[rows].description, row[5]);
sizeof_strncpy((*prog)[rows].category, row[6]);
sizeof_strncpy((*prog)[rows].seriesid, row[7]);
sizeof_strncpy((*prog)[rows].programid, row[8]);
(*prog)[rows].channum = safe_atoi(row[9]);
sizeof_strncpy((*prog)[rows].callsign, row[10]);
sizeof_strncpy((*prog)[rows].name, row[11]);
//sizeof_strncpy((*prog)[rows].rec_status, row[14]);
(*prog)[rows].rec_status=safe_atoi(row[14]);
//fprintf(stderr, "row=%s val=%d\n",row[14],(*prog)[rows].rec_status);
rows++;
}
mysql_free_result(res);
cmyth_dbg(CMYTH_DBG_ERROR, "%s: rows= %d\n", __FUNCTION__, rows);
return rows;
}
char *
cmyth_mysql_escape_chars(cmyth_database_t db, char * string)
{
char *N_string;
size_t len;
if(cmyth_db_check_connection(db) != 0)
{
cmyth_dbg(CMYTH_DBG_ERROR, "%s: cmyth_db_check_connection failed\n",
__FUNCTION__);
fprintf(stderr,"%s: cmyth_db_check_connection failed\n", __FUNCTION__);
return NULL;
}
len = strlen(string);
N_string=ref_alloc(len*2+1);
mysql_real_escape_string(db->mysql,N_string,string,len);
return (N_string);
}
int
cmyth_mysql_get_recgroups(cmyth_database_t db, cmyth_recgroups_t **sqlrecgroups)
{
MYSQL_RES *res=NULL;
MYSQL_ROW row;
const char *query="SELECT DISTINCT recgroup FROM record";
int rows=0;
int n=0;
if(cmyth_db_check_connection(db) != 0)
{
cmyth_dbg(CMYTH_DBG_ERROR, "%s: cmyth_db_check_connection failed\n",
__FUNCTION__);
fprintf(stderr,"%s: cmyth_db_check_connection failed\n", __FUNCTION__);
return -1;
}
cmyth_dbg(CMYTH_DBG_ERROR, "%s: query= %s\n", __FUNCTION__, query);
if(mysql_query(db->mysql,query)) {
cmyth_dbg(CMYTH_DBG_ERROR, "%s: mysql_query() Failed: %s\n",
__FUNCTION__, mysql_error(db->mysql));
return -1;
}
res = mysql_store_result(db->mysql);
while((row = mysql_fetch_row(res))) {
if (rows == n ) {
n++;
*sqlrecgroups=realloc(*sqlrecgroups,sizeof(**sqlrecgroups)*(n));
}
sizeof_strncpy ( (*sqlrecgroups)[rows].recgroups, row[0]);
cmyth_dbg(CMYTH_DBG_ERROR, "(*sqlrecgroups)[%d].recgroups = %s\n",rows, (*sqlrecgroups)[rows].recgroups);
rows++;
}
mysql_free_result(res);
cmyth_dbg(CMYTH_DBG_ERROR, "%s: rows= %d\n", __FUNCTION__, rows);
return rows;
}
int
cmyth_mysql_get_prog_finder_time(cmyth_database_t db, cmyth_program_t **prog, time_t starttime, char *program_name)
{
MYSQL_RES *res=NULL;
MYSQL_ROW row;
char query[630];
char *N_title;
int rows=0;
int n = 50;
int ch;
if(cmyth_db_check_connection(db) != 0)
{
cmyth_dbg(CMYTH_DBG_ERROR, "%s: cmyth_db_check_connection failed\n",
__FUNCTION__);
fprintf(stderr,"%s: cmyth_db_check_connection failed\n", __FUNCTION__);
return -1;
}
N_title = ref_alloc(strlen(program_name)*2+1);
mysql_real_escape_string(db->mysql,N_title,program_name,strlen(program_name));
//sprintf(query, "SELECT chanid,starttime,endtime,title,description,subtitle,programid,seriesid,category FROM program WHERE starttime >= '%s' and title ='%s' ORDER BY `starttime` ASC ", starttime, N_title);
snprintf(query, 630, "SELECT program.chanid,UNIX_TIMESTAMP(program.starttime),UNIX_TIMESTAMP(program.endtime),program.title,program.description,program.subtitle,program.programid,program.seriesid,program.category, channel.channum, channel.callsign, channel.name, channel.sourceid FROM program LEFT JOIN channel on program.chanid=channel.chanid WHERE starttime >= FROM_UNIXTIME(%d) and title ='%s' ORDER BY `starttime` ASC ", (int)starttime, N_title);
ref_release(N_title);
fprintf(stderr, "%s\n", query);
cmyth_dbg(CMYTH_DBG_ERROR, "%s: query= %s\n", __FUNCTION__, query);
if(mysql_query(db->mysql,query)) {
cmyth_dbg(CMYTH_DBG_ERROR, "%s: mysql_query() Failed: %s\n",
__FUNCTION__, mysql_error(db->mysql));
return -1;
}
cmyth_dbg(CMYTH_DBG_ERROR, "n = %d\n",n);
res = mysql_store_result(db->mysql);
cmyth_dbg(CMYTH_DBG_ERROR, "n = %d\n",n);
while((row = mysql_fetch_row(res))) {
cmyth_dbg(CMYTH_DBG_ERROR, "n = %d\n",n);
if (rows == n) {
n++;
cmyth_dbg(CMYTH_DBG_ERROR, "realloc n = %d\n",n);
*prog=realloc(*prog,sizeof(**prog)*(n));
}
cmyth_dbg(CMYTH_DBG_ERROR, "rows = %d\nrow[0]=%d\n",rows, row[0]);
cmyth_dbg(CMYTH_DBG_ERROR, "row[1]=%d\n",row[1]);
ch = atoi(row[0]);
(*prog)[rows].chanid=ch;
cmyth_dbg(CMYTH_DBG_ERROR, "prog[%d].chanid = %d\n",rows, (*prog)[rows].chanid);
(*prog)[rows].recording=0;
(*prog)[rows].starttime = atoi(row[1]);
(*prog)[rows].endtime = atoi(row[2]);
sizeof_strncpy ((*prog)[rows].title, row[3]);
sizeof_strncpy ((*prog)[rows].description, row[4]);
sizeof_strncpy ((*prog)[rows].subtitle, row[5]);
sizeof_strncpy ((*prog)[rows].programid, row[6]);
sizeof_strncpy ((*prog)[rows].seriesid, row[7]);
sizeof_strncpy ((*prog)[rows].category, row[8]);
(*prog)[rows].channum = atoi (row[9]);
sizeof_strncpy ((*prog)[rows].callsign,row[10]);
sizeof_strncpy ((*prog)[rows].name,row[11]);
(*prog)[rows].sourceid = atoi (row[12]);
cmyth_dbg(CMYTH_DBG_ERROR, "prog[%d].chanid = %d\n",rows, (*prog)[rows].chanid);
cmyth_dbg(CMYTH_DBG_ERROR, "prog[%d].title = %s\n",rows, (*prog)[rows].title);
rows++;
}
mysql_free_result(res);
cmyth_dbg(CMYTH_DBG_ERROR, "%s: rows= %d\n", __FUNCTION__, rows);
return rows;
}