// 更新操作 bool COracleDB::Update( const char *stable, const CSqlObj *obj, const CSqlWhere *where ) { string supdate; // 构建更新操作 if ( ! BuildUpdate( ( OracleSqlObj* ) obj, supdate ) ) { return false; } string sql = "update "; sql += stable; sql += " set "; sql += supdate; string swhere; // 构建的条件 if ( BuildWhere( where, swhere ) ) { sql += " where "; sql += swhere; } // 执行更新操作 int ret = oracle_exec( sql.c_str(), true ) ; if ( ret != DB_ERR_SUCCESS ) { OUT_ERROR( NULL, 0, "Oracle", "Exceute sql: %s, result %d", sql.c_str() , ret ); return false; } return true; }
void OGRSQLiteTableLayer::SetSpatialFilter( OGRGeometry * poGeomIn ) { if( InstallFilter( poGeomIn ) ) { BuildWhere(); ResetReading(); } }
void OGRIngresTableLayer::SetSpatialFilter( OGRGeometry * poGeomIn ) { if( !InstallFilter( poGeomIn ) ) return; BuildWhere(); ResetReading(); }
void OGRGFTTableLayer::SetSpatialFilter( OGRGeometry * poGeomIn ) { GetLayerDefn(); if( InstallFilter( poGeomIn ) ) { BuildWhere(); ResetReading(); } }
OGRErr OGRSQLiteTableLayer::SetAttributeFilter( const char *pszQuery ) { if( pszQuery == NULL ) osQuery = ""; else osQuery = pszQuery; BuildWhere(); ResetReading(); return OGRERR_NONE; }
OGRErr OGRIngresTableLayer::SetAttributeFilter( const char *pszQuery ) { osQuery = ""; if( pszQuery != NULL ) osQuery = pszQuery; BuildWhere(); ResetReading(); return OGRERR_NONE; }
OGRErr OGRMySQLTableLayer::SetAttributeFilter( const char *pszQuery ) { CPLFree( this->pszQuery ); if( pszQuery == NULL || strlen(pszQuery) == 0 ) this->pszQuery = NULL; else this->pszQuery = CPLStrdup( pszQuery ); BuildWhere(); ResetReading(); return OGRERR_NONE; }
OGRErr OGRIngresTableLayer::SetAttributeFilter( const char *pszQuery ) { CPLFree(m_pszAttrQueryString); m_pszAttrQueryString = (pszQuery) ? CPLStrdup(pszQuery) : NULL; osQuery = ""; if( pszQuery != NULL ) osQuery = pszQuery; BuildWhere(); ResetReading(); return OGRERR_NONE; }
OGRErr OGRGFTTableLayer::SetAttributeFilter( const char *pszQuery ) { GetLayerDefn(); if( pszQuery == NULL ) osQuery = ""; else { osQuery = PatchSQL(pszQuery); } BuildWhere(); ResetReading(); return OGRERR_NONE; }
OGRErr OGRMySQLTableLayer::SetAttributeFilter( const char *pszQueryIn ) { CPLFree(m_pszAttrQueryString); m_pszAttrQueryString = pszQueryIn ? CPLStrdup(pszQueryIn) : nullptr; CPLFree( pszQuery ); if( pszQueryIn == nullptr || strlen(pszQueryIn) == 0 ) pszQuery = nullptr; else pszQuery = CPLStrdup( pszQueryIn ); BuildWhere(); ResetReading(); return OGRERR_NONE; }
// 带条件的删除操作 bool COracleDB::Delete( const char *stable, const CSqlWhere *where ) { string sql = "delete from "; sql += stable; string swhere; // 构建删除的条件 if ( BuildWhere( where, swhere ) ) { sql += " where "; sql += swhere; } // 执行删除操作 int ret = oracle_exec( sql.c_str(), true ) ; if ( ret != DB_ERR_SUCCESS ) { OUT_ERROR( NULL, 0, "Oracle", "Exceute sql: %s, result %d", sql.c_str() , ret ); return false; } return true; }
OGRErr OGRCARTODBTableLayer::SetAttributeFilter( const char *pszQuery ) { GetLayerDefn(); if( pszQuery == NULL ) osQuery = ""; else { osQuery = "("; osQuery += pszQuery; osQuery += ")"; } BuildWhere(); ResetReading(); return OGRERR_NONE; }
void OGRCARTODBTableLayer::SetSpatialFilter( int iGeomField, OGRGeometry * poGeomIn ) { if( iGeomField < 0 || iGeomField >= GetLayerDefn()->GetGeomFieldCount() || GetLayerDefn()->GetGeomFieldDefn(iGeomField)->GetType() == wkbNone ) { if( iGeomField != 0 ) { CPLError(CE_Failure, CPLE_AppDefined, "Invalid geometry field index : %d", iGeomField); } return; } m_iGeomFieldFilter = iGeomField; if( InstallFilter( poGeomIn ) ) { BuildWhere(); ResetReading(); } }
/** * @brief Generic SELECT query builder * * @param p_ml This media_library_t object * @param ppsz_query *ppsz_query will contain query * @param p_result_type see enum ml_result_type_e * @param selected_type the type of the element we're selecting * @param tree the find tree * @return VLC_SUCCESS or VLC_EGENERIC */ int BuildSelect( media_library_t *p_ml, char **ppsz_query, ml_result_type_e *p_result_type, const char *psz_selected_type_lvalue, ml_select_e selected_type, ml_ftree_t *tree ) { /* Basic verification */ if( !ppsz_query ) return VLC_EGENERIC; int i_ret = VLC_SUCCESS; char *psz_query = NULL; /* Building psz_query : psz_query = "SELECT psz_distinct psz_select FROM psz_from [JOIN psz_join ON psz_on] [JOIN psz_join2 ON psz_on2] [WHERE psz_where[i] [AND psz_where[j] ...]] [LIMIT psz_limit] [ORDER BY psz_select psz_sort] ;" */ char *psz_select = NULL; const char *psz_distinct = ""; /* "DISTINCT" or "" */ /* FROM */ char *psz_from = NULL; int i_from = 0; /* Main select table */ char **ppsz_frompersons = NULL; int i_num_frompersons = 0; char *psz_peoplerole = NULL; /* Person to get selected */ /* JOIN ... ON ... */ char *psz_join = NULL; char *psz_join2 = NULL; char *psz_on = NULL; char *psz_on2 = NULL; int i_join = 0; /* Tables that need to be joined */ /* String buffers */ char *psz_where = NULL; char *psz_sort = NULL; /* ASC or DESC or NULL */ char *psz_tmp = NULL; int i_limit = 0; /* Build the WHERE condition */ BuildWhere( p_ml, &psz_where, tree, &psz_sort, &i_limit, &psz_distinct, &ppsz_frompersons, &i_num_frompersons, &i_join ); PackFromPersons( &ppsz_frompersons, i_num_frompersons ); /* What is the result type? */ ml_result_type_e res_type = ML_TYPE_PSZ; /* SELECT, FROM */ /* Note that a DISTINCT select makes id of result non sense */ switch( selected_type ) { case ML_ALBUM: psz_select = ( !*psz_distinct ) ? strdup( "album.id, album.title AS album_title" ) : strdup( "album.title AS album_title" ); i_from = table_album; break; case ML_ALBUM_COVER: psz_select = ( !*psz_distinct ) ? strdup( "album.id, album.cover" ) : strdup( "album.cover" ); i_from = table_album; break; case ML_ALBUM_ID: psz_select = strdup( "album.id" ); psz_distinct = "DISTINCT"; i_from = table_album; res_type = ML_TYPE_INT; break; case ML_ARTIST: psz_select = ( !*psz_distinct ) ? strdup( "people_Artist.id, people_Artist.name" ) : strdup( "people_Artist.name" ); i_from = table_people; psz_peoplerole = strdup( ML_PERSON_ARTIST ); break; case ML_ARTIST_ID: psz_select = strdup( "people_Artist.id" ); psz_distinct = "DISTINCT"; i_from = table_people; res_type = ML_TYPE_INT; psz_peoplerole = strdup( ML_PERSON_ARTIST ); break; case ML_COVER: psz_select = ( !*psz_distinct ) ? strdup( "media.id, media.cover" ) : strdup( "media.cover" ); i_from = table_media; break; case ML_COMMENT: psz_select = ( !*psz_distinct ) ? strdup( "media.id, extra.comment" ) : strdup( "extra.comment" ); i_from = table_extra; break; case ML_GENRE: psz_select = ( !*psz_distinct ) ? strdup( "media.id, media.genre" ) : strdup( "media.genre" ); i_from = table_media; break; case ML_COUNT_MEDIA: psz_select = ( !*psz_distinct ) ? strdup( "COUNT()" ) : strdup( "COUNT( DISTINCT media.id )" ); i_from = table_media; res_type = ML_TYPE_INT; break; case ML_COUNT_ALBUM: psz_select = ( !*psz_distinct ) ? strdup( "COUNT()" ) : strdup( "COUNT( DISTINCT album.id )" ); i_from = table_album; res_type = ML_TYPE_INT; break; case ML_COUNT_PEOPLE: psz_select = ( !*psz_distinct ) ? strdup( "COUNT()" ) : strdup( "COUNT( DISTINCT people.id )" ); i_from = table_people; res_type = ML_TYPE_INT; break; case ML_FILESIZE: psz_select = strdup( "media.filesize" ); i_from = table_media; res_type = ML_TYPE_INT; break; case ML_ID: psz_select = strdup( "media.id" ); /* ID: must be distinct */ psz_distinct = "DISTINCT"; i_from = table_media; res_type = ML_TYPE_INT; break; case ML_LANGUAGE: psz_select = strdup( "extra.language" ); psz_distinct = "DISTINCT"; i_from = table_extra; break; case ML_MEDIA_SPARSE: i_ret = AppendStringFmt( p_ml, &psz_select, "media.id AS id," "media.uri AS uri," "media.type AS type," "media.title AS title," "media.duration AS duration," "media.original_title AS original_title," "media.album_id AS album_id," "media.cover AS cover," "media.preview AS preview," "media.disc AS disc," "media.track AS track," "media.year AS year," "media.genre AS genre," "media.played_count AS played_count," "media.last_played AS last_played," "media.first_played AS first_played," "media.import_time AS import_time," "media.skipped_count AS skipped_count," "media.last_skipped AS last_skipped," "media.vote AS vote," "media.score AS score," "media.comment AS comment," "media.filesize AS filesize," "album.title AS album_title," "album.cover AS album_cover," "(SELECT name FROM media_to_people JOIN people " "ON (people_id = id) WHERE media_id = media.id AND role = %Q LIMIT 1) AS people_%s", ML_PERSON_ARTIST, ML_PERSON_ARTIST ); if( i_ret != VLC_SUCCESS ) goto exit; i_from = table_media; i_join |= ( table_album | table_people ); psz_distinct = "DISTINCT"; res_type = ML_TYPE_MEDIA; break; case ML_MEDIA: /* Who said this was over-complicated ?? */ /* Yea right. */ psz_select = strdup( "media.id AS id," "media.uri AS uri," "media.type AS type," "media.title AS title," "media.duration AS duration," "media.original_title AS original_title," "media.album_id AS album_id," "media.cover AS cover," "media.preview AS preview," "media.disc as disc," "media.track AS track," "media.year AS year," "media.genre AS genre," "media.played_count AS played_count," "media.last_played AS last_played," "media.first_played AS first_played," "media.import_time AS import_time," "media.last_skipped AS last_skipped," "media.skipped_count AS skipped_count," "media.vote AS vote," "media.score AS score," "media.comment AS comment," "media.filesize AS filesize," "album.title AS album_title," "album.cover AS album_cover," "people.id AS people_id," "people.name AS people_name," "people.role AS people_role," "extra.language AS language," "extra.extra AS extra" ); i_from = table_media; i_join |= ( table_album | table_people | table_extra ); psz_distinct = "DISTINCT"; res_type = ML_TYPE_MEDIA; break; case ML_MEDIA_EXTRA: psz_select = strdup( "media.id AS id," "people.id AS people_id," "people.name AS people_name," "people.role AS people_role," "extra.extra AS extra," "extra.language AS language" ); i_from = table_media; i_join |= ( table_album | table_people | table_extra ); psz_distinct = "DISTINCT"; res_type = ML_TYPE_MEDIA; break; case ML_ORIGINAL_TITLE: psz_select = ( !*psz_distinct ) ? strdup( "media.id, media.original_title" ) : strdup( "media.original_title" ); i_from = table_media; break; /* For people, if lvalue = "", then we want ANY people. */ case ML_PEOPLE: assert( psz_selected_type_lvalue ); i_ret = AppendStringFmt( p_ml, &psz_select, "people%s%s.name", *psz_selected_type_lvalue ? "_" : "", *psz_selected_type_lvalue ? psz_selected_type_lvalue : "" ); if( i_ret != VLC_SUCCESS ) goto exit; if( *psz_distinct ) { i_ret = AppendStringFmt( p_ml, &psz_select, ", people%s%s.name", *psz_selected_type_lvalue ? "_" : "", *psz_selected_type_lvalue ? psz_selected_type_lvalue : "" ); if( i_ret != VLC_SUCCESS ) goto exit; } i_from = table_people; psz_peoplerole = strdup( psz_selected_type_lvalue ); break; case ML_PEOPLE_ID: assert( psz_selected_type_lvalue ); i_ret = AppendStringFmt( p_ml, &psz_select, "people%s%s.id", *psz_selected_type_lvalue ? "_" : "", *psz_selected_type_lvalue ? psz_selected_type_lvalue : "" ); if( i_ret != VLC_SUCCESS ) goto exit; if( *psz_distinct ) { i_ret = AppendStringFmt( p_ml, &psz_select, ", people%s%s.id", *psz_selected_type_lvalue ? "_" : "", *psz_selected_type_lvalue ? psz_selected_type_lvalue : "" ); if( i_ret != VLC_SUCCESS ) goto exit; } psz_distinct = "DISTINCT"; i_from = table_people; psz_peoplerole = strdup( psz_selected_type_lvalue ); res_type = ML_TYPE_INT; break; case ML_PEOPLE_ROLE: psz_select = strdup( "people.role" ); psz_distinct = "DISTINCT"; i_from = table_people; break; case ML_TITLE: psz_select = ( !*psz_distinct ) ? strdup( "media.id, media.title" ) : strdup( "media.title" ); i_from = table_media; break; case ML_TYPE: psz_select = ( !*psz_distinct ) ? strdup( "media.id, media.type" ): strdup( "media.type" ); i_from = table_media; res_type = ML_TYPE_INT; break; case ML_URI: psz_select = ( !*psz_distinct ) ? strdup( "media.id, media.uri" ) : strdup( "media.uri" ); i_from = table_media; break; case ML_VOTE: psz_select = ( !*psz_distinct ) ? strdup( "media.id, media.vote" ) : strdup( "media.vote" ); i_from = table_media; res_type = ML_TYPE_INT; break; case ML_YEAR: psz_select = ( !*psz_distinct ) ? strdup( "media.id, media.year" ) : strdup( "media.year" ); i_from = table_media; res_type = ML_TYPE_INT; break; case ML_LIMIT: case ML_SORT_DESC: case ML_SORT_ASC: case ML_END: default: msg_Dbg( p_ml, "unknown select (%d) in BuildSelect", selected_type ); return VLC_EGENERIC; } /* Let's build full psz_query ! */ i_ret = VLC_SUCCESS; /* Figure out select and join tables */ switch( i_from ) { case table_media: break; case table_album: switch( i_join ) { case 0: break; case 2: i_join = 0; break; case 1: case 3: i_from = table_media; i_join = table_album; break; case 4: case 5: case 6: case 7: i_from = table_media; i_join = table_album | table_people; break; case 8: case 9: case 10: case 11: i_from = table_media; i_join = table_extra | table_album; break; case 12: case 13: case 14: case 15: i_from = table_media; i_join = table_extra | table_album | table_people; break; default: break; } break; case table_people: switch( i_join ) { case 0: break; case 1: i_from = table_media; i_join = table_people; break; case 2: case 3: i_from = table_media; i_join = table_album | table_people; break; case 4: /* Determine if a join from media is required */ if( i_num_frompersons > 1 ) i_from = table_media; else i_join = 0; break; case 5: i_from = table_media; i_join = table_people; break; case 6: case 7: i_from = table_media; i_join = table_album | table_people; break; case 8: case 9: i_from = table_media; i_join = table_people | table_extra; break; case 10: case 11: i_from = table_media; i_join = table_people | table_album | table_extra; break; case 12: case 13: i_from = table_media; i_join = table_people | table_extra; break; case 14: case 15: i_from = table_media; i_join = table_people | table_album | table_extra; break; default: break; } break; case table_extra: switch( i_join ) { case 0: break; case 1: i_from = table_media; i_join = table_extra; break; case 2: case 3: i_from = table_media; i_join = table_extra | table_album; break; case 4: case 5: i_from = table_media; i_join = table_extra | table_people; break; case 6: case 7: i_from = table_media; i_join = table_extra | table_people | table_album; break; case 8: i_from = table_extra; i_join = 0; break; case 9: i_from = table_media; i_join = table_extra; break; case 10: case 11: i_from = table_media; i_join = table_extra | table_album; break; case 12: case 13: i_from = table_media; i_join = table_extra | table_people; break; case 14: case 15: i_from = table_media; i_join = table_extra | table_people | table_album; break; default: break; } break; default: msg_Warn( p_ml, "You can't be selecting from this table!!" ); i_ret = VLC_EGENERIC; goto exit; } assert( !( i_from & table_album && i_join & table_album ) ); assert( !( i_from & table_people && i_join & table_people ) ); assert( !( i_from & table_extra && i_join & table_extra ) ); /* Generate FROM - psz_from */ if( i_from == table_media ) i_ret = AppendStringFmt( p_ml, &psz_from, "media" ); else if( i_from == table_album ) i_ret = AppendStringFmt( p_ml, &psz_from, "album" ); else if( i_from == table_extra ) i_ret = AppendStringFmt( p_ml, &psz_from, "extra" ); else if( i_from == table_people ) { i_ret = AppendStringFmt( p_ml, &psz_from, "people AS people%s%s", psz_peoplerole ? "_" : "", psz_peoplerole ); if( i_ret < 0 ) goto exit; /* The ugly next statement is only required if persons are being * selected. Otherwise the joins will handle this */ if( psz_peoplerole && *psz_peoplerole ) { i_ret = AppendStringFmt( p_ml, &psz_where, "%s people_%s.role = %Q ", ( psz_where && *psz_where ) ? " AND" : "", psz_peoplerole, psz_peoplerole ); if( i_ret < 0 ) goto exit; } } if( i_ret < 0 ) goto exit; i_ret = AppendStringFmt( p_ml, &psz_query, "SELECT %s %s ", psz_distinct, psz_select ); if( i_ret < 0 ) goto exit; i_ret = AppendStringFmt( p_ml, &psz_query, "FROM %s ", psz_from ); if( i_ret < 0 ) goto exit; /* Create join conditions */ if( i_join & table_people ) { /* we can join psz_peoplerole safely because * if i_join = people, then i_from != people */ bool join = true; for( int i = 0; i < i_num_frompersons ; i++ ) { /* We assume ppsz_frompersons has unique entries and * if ppsz_frompersons[i] is empty(but not NULL), then it * means we accept any role */ if( ppsz_frompersons[i] && *ppsz_frompersons[i] ) { if( strcmp( psz_peoplerole, ppsz_frompersons[i] ) == 0 ) join = false; AppendStringFmt( p_ml, &psz_join, "%smedia_to_people AS people_%sx ", psz_join == NULL ? "" : ",", ppsz_frompersons[i] ); /* This is possible because from is usually the media table */ AppendStringFmt( p_ml, &psz_on, "%speople_%sx.media_id = media.id ", psz_on == NULL ? "" : " AND ", ppsz_frompersons[i] ); AppendStringFmt( p_ml, &psz_join2, "%speople AS people_%s ", psz_join2 == NULL ? "" : ",", ppsz_frompersons[i] ); AppendStringFmt( p_ml, &psz_on2, "%s ( people_%sx.people_id = people_%s.id AND " "people_%s.role = %Q )", psz_on2 == NULL ? "" : " AND ", ppsz_frompersons[i], ppsz_frompersons[i], ppsz_frompersons[i], ppsz_frompersons[i] ); } else if( ppsz_frompersons[i] ) { if( strcmp( psz_peoplerole, ppsz_frompersons[i] ) == 0 ) join = false; AppendStringFmt( p_ml, &psz_join, "%smedia_to_people AS peoplex ", psz_join == NULL ? "" : "," ); /* This is possible because from is usually the media table */ AppendStringFmt( p_ml, &psz_on, "%speoplex.media_id = media.id ", psz_on == NULL ? "" : " AND " ); AppendStringFmt( p_ml, &psz_join2, "%speople AS people ", psz_join2 == NULL ? "" : "," ); AppendStringFmt( p_ml, &psz_on2, "%s peoplex.people_id = people.id", psz_on2 == NULL ? "" : " AND " ); } } if( join ) { if( psz_peoplerole && *psz_peoplerole ) { AppendStringFmt( p_ml, &psz_join, "%smedia_to_people AS people_%sx ", psz_join == NULL ? "" : ",", psz_peoplerole ); /* This is possible because from is always the media table */ AppendStringFmt( p_ml, &psz_on, "%speople_%sx.media_id = media.id ", psz_on == NULL ? "" : " AND ", psz_peoplerole ); AppendStringFmt( p_ml, &psz_join2, "%speople AS people_%s ", psz_join2 == NULL ? "" : ",", psz_peoplerole ); AppendStringFmt( p_ml, &psz_on2, "%s ( people_%sx.people_id = people_%s.id AND " "people_%s.role = %Q )", psz_on2 == NULL ? "" : " AND ", psz_peoplerole, psz_peoplerole, psz_peoplerole, psz_peoplerole ); } else { AppendStringFmt( p_ml, &psz_join, "%smedia_to_people AS peoplex ", psz_join == NULL ? "" : "," ); /* This is possible because from is usually the media table */ AppendStringFmt( p_ml, &psz_on, "%speoplex.media_id = media.id ", psz_on == NULL ? "" : " AND " ); AppendStringFmt( p_ml, &psz_join2, "%speople ", psz_join2 == NULL ? "" : "," ); AppendStringFmt( p_ml, &psz_on2, "%s peoplex.people_id = people.id", psz_on2 == NULL ? "" : " AND " ); } } } if( i_join & table_album ) { AppendStringFmt( p_ml, &psz_join, "%salbum", psz_join == NULL ? "" : "," ); AppendStringFmt( p_ml, &psz_on, "%s album.id = media.album_id ", psz_on == NULL ? "" : " AND " ); } if( i_join & table_extra ) { AppendStringFmt( p_ml, &psz_join, "%sextra", psz_join == NULL ? "" : "," ); AppendStringFmt( p_ml, &psz_on, "%s extra.id = media.id ", psz_on == NULL ? "" : " AND " ); } /* Complete the join clauses */ if( psz_join ) { AppendStringFmt( p_ml, &psz_query, "JOIN %s ON %s ", psz_join, psz_on ); } if( psz_join2 ) { AppendStringFmt( p_ml, &psz_query, "JOIN %s ON %s ", psz_join2, psz_on2 ); } if( psz_where && *psz_where ) { AppendStringFmt( p_ml, &psz_query, "WHERE %s ", psz_where ); } /* TODO: FIXME: Limit on media objects doesn't work! */ if( i_limit ) { AppendStringFmt( p_ml, &psz_query, "LIMIT %d ", i_limit ); } if( psz_sort ) { AppendStringFmt( p_ml, &psz_query, "ORDER BY %s %s", psz_select, psz_sort ); } if( i_ret > 0 ) i_ret = VLC_SUCCESS; if( p_result_type ) *p_result_type = res_type; if( !psz_query ) i_ret = VLC_EGENERIC; else *ppsz_query = strdup( psz_query ); exit: free( psz_query ); free( psz_where ); free( psz_tmp ); free( psz_from ); free( psz_join ); free( psz_select ); free( psz_join2 ); free( psz_on ); free( psz_on2 ); free( psz_peoplerole ); free( ppsz_frompersons ); if( i_ret != VLC_SUCCESS ) msg_Warn( p_ml, "an unknown error occurred (%d)", i_ret ); return i_ret; }
static int BuildWhere( media_library_t* p_ml, char **ppsz_where, ml_ftree_t* tree, char** sort, int* limit, const char** distinct, char*** pppsz_frompersons, int* i_frompersons, int* join ) { assert( ppsz_where && sort && distinct ); if( !tree ) /* Base case */ { return VLC_SUCCESS; } int i_ret = VLC_EGENERIC; char* psz_left = NULL; char* psz_right = NULL; switch( tree->op ) { case ML_OP_AND: case ML_OP_OR: i_ret = BuildWhere( p_ml, &psz_left, tree->left, SLDPJ ); if( i_ret != VLC_SUCCESS ) goto parsefail; i_ret = BuildWhere( p_ml, &psz_right, tree->right, SLDPJ ); if( i_ret != VLC_SUCCESS ) goto parsefail; if( psz_left == NULL || psz_right == NULL ) { msg_Err( p_ml, "Parsing failed for AND/OR" ); i_ret = VLC_EGENERIC; goto parsefail; } if( asprintf( ppsz_where, "( %s %s %s )", psz_left, ( tree->op == ML_OP_AND ? "AND" : "OR" ), psz_right ) == -1 ) { i_ret = VLC_ENOMEM; goto parsefail; } break; case ML_OP_NOT: i_ret = BuildWhere( p_ml, &psz_left, tree->left, SLDPJ ); if( i_ret != VLC_SUCCESS ) goto parsefail; if( psz_left == NULL ) { msg_Err( p_ml, "Parsing failed at NOT" ); i_ret = VLC_EGENERIC; goto parsefail; } if( asprintf( ppsz_where, "( NOT %s )", psz_left ) == -1 ) { i_ret = VLC_ENOMEM; goto parsefail; } break; case ML_OP_SPECIAL: i_ret = BuildWhere( p_ml, &psz_right, tree->right, SLDPJ ); if( i_ret != VLC_SUCCESS ) goto parsefail; i_ret = BuildWhere( p_ml, &psz_left, tree->left, SLDPJ ); if( i_ret != VLC_SUCCESS ) goto parsefail; /* Ignore right parse tree as this is a special node */ *ppsz_where = strdup( psz_left ? psz_left : "" ); if( !*ppsz_where ) { i_ret = VLC_ENOMEM; goto parsefail; } break; case ML_OP_NONE: switch( tree->criteria ) { case ML_PEOPLE: assert( tree->comp == ML_COMP_HAS || tree->comp == ML_COMP_EQUAL || tree->comp == ML_COMP_STARTS_WITH || tree->comp == ML_COMP_ENDS_WITH ); *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "people%s%s.name LIKE '%s%q%s'", tree->lvalue.str ? "_" : "", tree->lvalue.str ? tree->lvalue.str : "", tree->comp == ML_COMP_HAS || tree->comp == ML_COMP_STARTS_WITH ? "%%" : "", tree->value.str, tree->comp == ML_COMP_HAS || tree->comp == ML_COMP_ENDS_WITH ? "%%" : "" ); if( *ppsz_where == NULL ) goto parsefail; *pppsz_frompersons = realloc( *pppsz_frompersons, ++*i_frompersons * sizeof( char* ) ); *pppsz_frompersons[ *i_frompersons - 1 ] = tree->lvalue.str; *join |= table_people; break; case ML_PEOPLE_ID: assert( tree->comp == ML_COMP_EQUAL ); *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "( people%s%s.id = %d )", tree->lvalue.str ? "_":"", tree->lvalue.str ? tree->lvalue.str:"", tree->value.i ); if( *ppsz_where == NULL ) goto parsefail; *pppsz_frompersons = realloc( *pppsz_frompersons, ++*i_frompersons * sizeof( char* ) ); *pppsz_frompersons[ *i_frompersons - 1 ] = tree->lvalue.str; *join |= table_people; break; case ML_PEOPLE_ROLE: assert( tree->comp == ML_COMP_HAS || tree->comp == ML_COMP_EQUAL || tree->comp == ML_COMP_STARTS_WITH || tree->comp == ML_COMP_ENDS_WITH ); *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "people%s%s.role LIKE '%s%q%s'", tree->lvalue.str ? "_" : "", tree->lvalue.str ? tree->lvalue.str : "", tree->comp == ML_COMP_HAS || tree->comp == ML_COMP_STARTS_WITH ? "%%" : "", tree->value.str, tree->comp == ML_COMP_HAS || tree->comp == ML_COMP_ENDS_WITH ? "%%" : "" ); if( *ppsz_where == NULL ) goto parsefail; *pppsz_frompersons = realloc( *pppsz_frompersons, ++*i_frompersons * sizeof( char* ) ); *pppsz_frompersons[ *i_frompersons - 1 ] = tree->lvalue.str; *join |= table_people; break; CASE_PSZ( ML_ALBUM, "album.title", table_album ); CASE_PSZ( ML_ALBUM_COVER, "album.cover", table_album ); case ML_ALBUM_ID: assert( tree->comp == ML_COMP_EQUAL ); *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "album.id = %d", tree->value.i ); if( *ppsz_where == NULL ) goto parsefail; *join |= table_album; break; CASE_PSZ( ML_COMMENT, "media.comment", table_media ); CASE_PSZ( ML_COVER, "media.cover", table_media ); CASE_INT( ML_DURATION, "media.duration", table_media ); CASE_PSZ( ML_EXTRA, "extra.extra", table_extra ); CASE_INT( ML_FILESIZE, "media.filesize", table_media ); CASE_PSZ( ML_GENRE, "media.genre", table_media ); case ML_ID: assert( tree->comp == ML_COMP_EQUAL ); *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "media.id = %d", tree->value.i ); if( *ppsz_where == NULL ) goto parsefail; *join |= table_media; break; CASE_PSZ( ML_LANGUAGE, "extra.language", table_extra ); CASE_INT( ML_LAST_PLAYED, "media.last_played", table_media ); CASE_PSZ( ML_ORIGINAL_TITLE, "media.original_title", table_media ); msg_Warn( p_ml, "Deprecated Played Count tags" ); CASE_INT( ML_PLAYED_COUNT, "media.played_count", table_media ); CASE_INT( ML_SCORE, "media.score", table_media ); CASE_PSZ( ML_TITLE, "media.title", table_media ); CASE_INT( ML_TRACK_NUMBER, "media.track", table_media); CASE_INT( ML_TYPE, "media.type", table_media ); CASE_PSZ( ML_URI, "media.uri", table_media ); CASE_INT( ML_VOTE, "media.vote", table_media ); CASE_INT( ML_YEAR, "media.year", table_media ); case ML_LIMIT: if( !*limit ) *limit = tree->value.i; else msg_Warn( p_ml, "Double LIMIT found" ); break; case ML_SORT_DESC: *sort = sql_Printf( p_ml->p_sys->p_sql, "%s%s%s DESC ", sort ? *sort : "", sort ? ", " : "", tree->value.str ); if( *sort == NULL ) goto parsefail; break; case ML_SORT_ASC: *sort = sql_Printf( p_ml->p_sys->p_sql, "%s%s%s ASC ", sort ? *sort : "", sort ? ", " : "", tree->value.str ); if( *sort == NULL ) goto parsefail; break; case ML_DISTINCT: if( !**distinct ) *distinct = "DISTINCT"; else msg_Warn( p_ml, "Double DISTINCT found!" ); break; default: msg_Err( p_ml, "Invalid select type or unsupported: %d", tree->criteria ); } break; default: msg_Err( p_ml, "Broken find tree!" ); i_ret = VLC_EGENERIC; goto parsefail; } i_ret = VLC_SUCCESS; parsefail: free( psz_left ); free( psz_right ); return i_ret; }
/* TODO: Build smarter updates by using IN () */ static int BuildWhere( media_library_t* p_ml, char **ppsz_where, ml_ftree_t *tree ) { assert( ppsz_where ); char* psz_left = NULL; char* psz_right = NULL; int i_ret = VLC_SUCCESS; switch( tree->op ) { case ML_OP_AND: case ML_OP_OR: i_ret = BuildWhere( p_ml, &psz_left, tree->left ); if( i_ret != VLC_SUCCESS ) goto quit_buildwhere; i_ret = BuildWhere( p_ml, &psz_right, tree->right ); if( i_ret != VLC_SUCCESS ) goto quit_buildwhere; if( psz_left == NULL || psz_right == NULL ) { msg_Err( p_ml, "Couldn't build AND/OR for Update statement" ); i_ret = VLC_EGENERIC; goto quit_buildwhere; } if( asprintf( ppsz_where, "( %s %s %s )", psz_left, ( tree->op == ML_OP_AND ? "AND" : "OR" ), psz_right ) == -1 ) { i_ret = VLC_ENOMEM; goto quit_buildwhere; } break; case ML_OP_NOT: i_ret = BuildWhere( p_ml, &psz_left, tree->left ); if( i_ret != VLC_SUCCESS ) goto quit_buildwhere; if( psz_left == NULL ) { msg_Err( p_ml, "Couldn't build NOT for Update statement" ); i_ret = VLC_EGENERIC; goto quit_buildwhere; } if( asprintf( ppsz_where, "( NOT %s )", psz_left ) == -1 ) { i_ret = VLC_ENOMEM; goto quit_buildwhere; } break; case ML_OP_SPECIAL: msg_Err( p_ml, "Couldn't build special for Update statement" ); break; case ML_OP_NONE: switch( tree->criteria ) { case ML_ID: assert( tree->comp == ML_COMP_EQUAL ); *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "media.id = %d", tree->value.i ); if( *ppsz_where == NULL ) goto quit_buildwhere; break; case ML_URI: assert( tree->comp == ML_COMP_EQUAL ); *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "media.uri = %q", tree->value.str ); if( *ppsz_where == NULL ) goto quit_buildwhere; break; default: msg_Err( p_ml, "Trying to update with unsupported condition" ); break; } } quit_buildwhere: return i_ret; }
/** * @brief Generic UPDATE query builder * * @param p_ml This media_library_t object * @param ppsz_query *ppsz_query will contain query for update * @param ppsz_id_query will contain query to get the ids of updated files * @param selected_type the type of the element we're selecting * @param where parse tree of where condition * @param changes list of changes to make in the entries * @return VLC_SUCCESS or VLC_EGENERIC */ int BuildUpdate( media_library_t *p_ml, char **ppsz_query, char **ppsz_id_query, const char *psz_lvalue, ml_select_e selected_type, ml_ftree_t *where, vlc_array_t *changes ) { assert( ppsz_query ); assert( ppsz_id_query ); *ppsz_query = NULL; int i_type; int i_index; int i_ret = VLC_ENOMEM; char *psz_table = NULL; /* TODO: Hack? */ char *psz_set[ ML_DIRECTORY + 1 ] = { NULL }; char *psz_fullset = NULL; char *psz_extra = NULL; /*<< For an update to extra table */ char *psz_where = NULL; char *psz_tmp = NULL; int *pi_padd_ids = NULL; int i_people_add = 0; int i_album_id = 0; char *psz_album = NULL; char *psz_cover = NULL; if( !where ) { msg_Warn( p_ml, "You're trying to update no rows." "Trying to guess update based on uri" ); } /* Create the id/uri lists for WHERE part of the query */ i_ret = BuildWhere( p_ml, &psz_where, where ); if( i_ret != VLC_SUCCESS ) goto quit_buildupdate; /** Firstly, choose the right table */ switch( selected_type ) { case ML_ALBUM: psz_table = strdup( "album" ); break; case ML_PEOPLE: psz_table = strdup( "people" ); break; case ML_MEDIA: psz_table = strdup( "media" ); break; default: msg_Err( p_ml, "Not a valid element to Update!" ); i_ret = VLC_EGENERIC; goto quit_buildupdate; break; } if( !psz_table ) return VLC_ENOMEM; /** Secondly, build the SET part of the query */ for( i_index = 0; i_index < vlc_array_count( changes ); i_index++ ) { ml_element_t *find = ( ml_element_t * ) vlc_array_item_at_index( changes, i_index ); i_type = find->criteria; switch( i_type ) { case ML_ALBUM: if( selected_type == ML_ALBUM ) { if( !psz_set[i_type] ) { psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql, "title = %Q", find->value.str ); if( !psz_set[i_type] ) goto quit_buildupdate; } } else if( selected_type == ML_MEDIA ) { if( !psz_album ) psz_album = find->value.str; } else assert( 0 ); break; case ML_ALBUM_ID: assert( selected_type != ML_ALBUM ); if( selected_type == ML_MEDIA ) { if( i_album_id <= 0 ) { i_album_id = find->value.i; if( !psz_set[i_type] ) { psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql, "album_id = '%d'", find->value.i ); if( !psz_set[i_type] ) goto quit_buildupdate; } } } break; case ML_PEOPLE: if( selected_type == ML_MEDIA ) { pi_padd_ids = (int*) realloc( pi_padd_ids , ( ++i_people_add * sizeof(int) ) ); pi_padd_ids[ i_people_add - 1 ] = ml_GetInt( p_ml, ML_PEOPLE_ID, find->lvalue.str, ML_PEOPLE, find->lvalue.str, find->value.str ); if( pi_padd_ids[ i_people_add - 1 ] <= 0 ) { AddPeople( p_ml, find->value.str, find->lvalue.str ); pi_padd_ids[ i_people_add - 1 ] = ml_GetInt( p_ml, ML_PEOPLE_ID, find->lvalue.str, ML_PEOPLE, find->lvalue.str, find->value.str ); } } else if( strcmp( psz_lvalue, find->lvalue.str ) ) { msg_Err( p_ml, "Trying to update a different person type" ); return VLC_EGENERIC; } else { if( !psz_set[i_type] ) psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql, "name = %Q", find->value.str ); } break; case ML_PEOPLE_ID: /* TODO: Implement smarter updates for this case? */ assert( selected_type == ML_MEDIA ); if( selected_type == ML_MEDIA ) { bool b_update = true; for( int i = 0; i < i_people_add; i++ ) { if( pi_padd_ids[ i ] == find->value.i ) { b_update = false; break; } } if( b_update ) { pi_padd_ids = (int *)realloc( pi_padd_ids, ( ++i_people_add * sizeof(int) ) ); pi_padd_ids[ i_people_add - 1 ] = find->value.i; } } break; case ML_PEOPLE_ROLE: msg_Dbg( p_ml, "Can't update role" ); break; case ML_COMMENT: assert( selected_type == ML_MEDIA ); SET_STR( "comment = %Q" ); case ML_COVER: assert( selected_type == ML_ALBUM || selected_type == ML_MEDIA ); psz_cover = find->value.str; SET_STR( "cover = %Q" ); case ML_DISC_NUMBER: assert( selected_type == ML_MEDIA ); SET_INT( "disc = '%d'" ); case ML_DURATION: assert( selected_type == ML_MEDIA ); SET_INT( "duration = '%d'" ); case ML_EXTRA: assert( selected_type == ML_MEDIA ); SET_STR( "extra = %Q" ); case ML_FIRST_PLAYED: assert( selected_type == ML_MEDIA ); SET_INT( "first_played =='%d'" ); case ML_GENRE: assert( selected_type == ML_MEDIA ); SET_STR( "genre = %Q" ); /* ID cannot be updated */ /* Import time can't be updated */ case ML_LAST_PLAYED: assert( selected_type == ML_MEDIA ); SET_INT( "last_played = '%d'" ); case ML_ORIGINAL_TITLE: assert( selected_type == ML_MEDIA ); SET_STR( "original_title = %Q" ); case ML_PLAYED_COUNT: assert( selected_type == ML_MEDIA ); SET_INT( "played_count = '%d'" ); case ML_PREVIEW: assert( selected_type == ML_MEDIA ); SET_STR( "preview = %Q" ); case ML_SKIPPED_COUNT: assert( selected_type == ML_MEDIA ); SET_INT( "skipped_count = '%d'" ); case ML_SCORE: assert( selected_type == ML_MEDIA ); SET_INT( "score = '%d'" ); case ML_TITLE: assert( selected_type == ML_MEDIA ); SET_STR( "title = %Q" ); case ML_TRACK_NUMBER: assert( selected_type == ML_MEDIA ); SET_INT( "track = '%d'" ); case ML_TYPE: assert( selected_type == ML_MEDIA ); if( !psz_set[i_type] ) psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql, "type = '%d'", find->value.i ); break; case ML_URI: assert( selected_type == ML_MEDIA ); if( !psz_set[i_type] ) { psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql, "uri = %Q", find->value.str ); } break; case ML_VOTE: assert( selected_type == ML_MEDIA ); SET_INT( "vote = '%d'" ); case ML_YEAR: assert( selected_type == ML_MEDIA ); SET_INT( "year = '%d'" ); case ML_END: goto exitfor; default: msg_Warn( p_ml, "Invalid type for update : %d", i_type ); } } exitfor: /* TODO: Album artist. Verify albumart */ if( i_album_id <= 0 && psz_album && *psz_album ) { i_ret = AddAlbum( p_ml, psz_album, psz_cover, 0 ); if( i_ret != VLC_SUCCESS ) goto quit_buildupdate; i_album_id = ml_GetAlbumId( p_ml, psz_album ); if( i_album_id <= 0 ) goto quit_buildupdate; psz_set[ML_ALBUM_ID] = sql_Printf( p_ml->p_sys->p_sql, "album_id = '%d'", i_album_id ); if( !psz_set[i_type] ) goto quit_buildupdate; } for( unsigned i = 0; i <= ML_DIRECTORY; i++ ) { if( psz_set[i] ) { if( i == ML_EXTRA || i == ML_LANGUAGE ) { free( psz_tmp ); if( asprintf( &psz_tmp, "%s%s%s", psz_extra ? psz_extra : "", psz_extra ? ", ": "", psz_set[i] ) == -1 ) goto quit_buildupdate; free( psz_extra ); psz_extra = strdup( psz_tmp ); } else { free( psz_tmp ); if( asprintf( &psz_tmp, "%s%s%s", psz_fullset ? psz_fullset : "", psz_fullset ? ", ": "", psz_set[i] ) == -1 ) goto quit_buildupdate; free( psz_fullset ); psz_fullset = strdup( psz_tmp ); } } } i_ret = VLC_SUCCESS; /** Now build the right WHERE condition */ assert( psz_where && *psz_where ); /** Finally build the full query */ /** Pass if we have some people to add - Indirect update*/ if( !psz_fullset && i_people_add == 0 ) { i_ret = VLC_EGENERIC; goto quit_buildupdate; } if( psz_fullset ){ if( asprintf( ppsz_query, "UPDATE %s SET %s WHERE %s", psz_table, psz_fullset, psz_where ) == -1 ) { goto quit_buildupdate; } } if( selected_type == ML_MEDIA ) { if( psz_extra ) { if( asprintf( &psz_tmp, "%s; UPDATE extra SET %s WHERE %s", *ppsz_query, psz_extra, psz_where ) == -1 ) goto quit_buildupdate; free( *ppsz_query ); *ppsz_query = psz_tmp; psz_tmp = NULL; } char* psz_idstring = NULL; if( i_people_add > 0 ) { for( int i = 0; i < i_people_add; i++ ) { if( asprintf( &psz_tmp, "%s%s%d", psz_idstring == NULL? "" : psz_idstring, psz_idstring == NULL ? "" : ",", pi_padd_ids[i] ) == -1 ) { free( psz_tmp ); free( psz_idstring ); goto quit_buildupdate; } free( psz_idstring ); psz_idstring = psz_tmp; psz_tmp = NULL; } /* Delete all connections with people whom we will update now! */ if( asprintf( &psz_tmp, "%s;DELETE FROM media_to_people WHERE EXISTS " "(SELECT media.id, people.id FROM media JOIN media_to_people " "AS temp ON media.id = temp.media_id " "JOIN people ON temp.people_id = people.id " "WHERE %s AND people.role IN " "(SELECT people.role FROM people WHERE people.id IN (%s)) " "AND people.id NOT IN (%s) " "AND temp.media_id = media_to_people.media_id AND " "temp.people_id = media_to_people.people_id )", *ppsz_query == NULL ? "": *ppsz_query, psz_where, psz_idstring, psz_idstring ) == -1 ) { free( psz_idstring ); goto quit_buildupdate; } free( *ppsz_query ); *ppsz_query = psz_tmp; psz_tmp = NULL; free( psz_idstring ); } for( int i = 0; i < i_people_add ; i++ ) { if( pi_padd_ids[i] > 0 ) { /* OR IGNORE will avoid errors from collisions from old media * Perhaps this hack can be fixed...FIXME */ if( asprintf( &psz_tmp, "%s;INSERT OR IGNORE into media_to_people " "(media_id,people_id) SELECT media.id, %d FROM media WHERE %s", *ppsz_query == NULL ? "" : *ppsz_query, pi_padd_ids[i], psz_where ) == -1 ) goto quit_buildupdate; FREENULL( *ppsz_query ); *ppsz_query = psz_tmp; psz_tmp = NULL; } } } if( asprintf( ppsz_id_query, "SELECT id AS %s_id FROM %s WHERE %s", psz_table, psz_table, psz_where ) == -1 ) { goto quit_buildupdate; } #ifndef NDEBUG msg_Dbg( p_ml, "updated media where %s", psz_where ); #endif quit_buildupdate: free( psz_tmp ); free( psz_table ); free( psz_fullset ); free( psz_extra ); free( pi_padd_ids ); for( int i = 0; i <= ML_DIRECTORY; i++ ) free( psz_set[ i ] ); return i_ret; }