[mythtv] mythvideo - category, country and genre

MaxPower maxpower44 at tiscali.fr
Fri Dec 19 12:01:29 EST 2003


Forget my last patch it was wrong :-[
I send 3 files
patch.sql contain database change
metadata.h.diff and metada.cpp.diff allow to load category, genres and 
countries information but nothing in the interface can display or modify 
at this moment.
Just tested with the last cvs.
Thor,  can you apply this patch for me, please ?


-------------- next part --------------
USE mythconverg;
ALTER TABLE videometadata CHANGE plot plot TEXT;
ALTER TABLE videometadata ADD COLUMN category INT UNSIGNED NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS videocategory
(
	intid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	category VARCHAR(128) NOT NULL
);

CREATE TABLE IF NOT EXISTS videocountry
(
	intid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	country VARCHAR(128) NOT NULL
); 

CREATE TABLE IF NOT EXISTS videometadatacountry
(
	idvideo INT UNSIGNED NOT NULL,
	idcountry INT UNSIGNED NOT NULL
);

CREATE TABLE IF NOT EXISTS videogenre
(
	intid INT UNSIGNED AUTO_INCREMENT NOT NULL  PRIMARY KEY,
	genre VARCHAR(128) NOT NULL
);
CREATE TABLE IF NOT EXISTS videometadatagenre
(
	idvideo INT UNSIGNED NOT NULL,
	idgenre INT UNSIGNED NOT NULL
);

-------------- next part --------------
--- /usr/local/src/cvs/myth/mythvideo/mythvideo/metadata.h	2003-12-07 18:00:02.000000000 +0100
+++ mythvideo/metadata.h	2003-12-19 17:43:31.000000000 +0100
@@ -16,7 +16,10 @@
              QString ldirector = "", QString lplot = "", 
              float luserrating = 0.0, QString lrating = "", int llength = 0, 
              int lid = 0, int lshowlevel = 1, int lchildID = -1,
-             bool lbrowse = true, QString lplaycommand = "")
+             bool lbrowse = true, QString lplaycommand = "",
+	     QString lcategory = "",
+ 	     QStringList lgenres = QStringList(),
+	     QStringList lcountries = QStringList())
     {
         filename = lfilename;
         coverfile = lcoverfile;
@@ -33,6 +36,9 @@
         childID = lchildID;
         browse = lbrowse;
         playcommand = lplaycommand;
+	category = lcategory;
+	genres = lgenres;
+	countries = lcountries;
     }
 
     Metadata(const Metadata &other) 
@@ -52,6 +58,9 @@
         childID = other.childID;
         browse = other.browse;
         playcommand = other.playcommand;
+	category = other.category;
+	genres = other.genres;
+	countries = other.countries;
     }
 
    ~Metadata() {}
@@ -101,6 +110,14 @@
     QString CoverFile() const { return coverfile; }
     void setCoverFile(QString &lcoverfile) { coverfile = lcoverfile; }
 
+    QString Category() const { return category;}
+    void setCategory(QString &lcategory){category = lcategory;}
+    QStringList Genres() const { return genres; }
+    void setGenres(QStringList &lgenres){genres = lgenres;}
+
+    QStringList Countries() const { return countries;}
+    void setCountries(QStringList & lcountries){countries = lcountries;}
+
     void guessTitle();
     void setField(QString field, QString data);
     void dumpToDatabase(QSqlDatabase *db);
@@ -109,6 +126,13 @@
     void fillDataFromID(QSqlDatabase *db);
 
   private:
+    int getIdCategory(QSqlDatabase *db);
+    void fillCategory(QSqlDatabase *db);
+    void fillCountries(QSqlDatabase *db);
+    void updateCountries(QSqlDatabase *db);
+    void fillGenres(QSqlDatabase *db);
+    void updateGenres(QSqlDatabase *db);
+ 
     QString title;
     QString inetref;
     QString director;
@@ -121,6 +145,9 @@
     int showlevel;
     bool browse;
     QString playcommand;
+    QString category;
+    QStringList genres;
+    QStringList countries;
 
     unsigned int id;
     
-------------- next part --------------
--- /usr/local/src/cvs/myth/mythvideo/mythvideo/metadata.cpp	2003-10-27 18:12:04.000000000 +0100
+++ mythvideo/metadata.cpp	2003-12-19 17:43:30.000000000 +0100
@@ -67,6 +67,49 @@
     }
 }
 
+void Metadata::fillCategory(QSqlDatabase *db)
+{
+	QString thequery;
+	thequery.sprintf("SELECT category FROM videometadata INNER JOIN videocategory ON videometadata.category = videocategory.intid WHERE videometadata.intid = %d",id);
+	QSqlQuery query(thequery,db);
+	if (query.isActive() && query.numRowsAffected()>0){
+		category = query.value(0).toString();
+	}
+
+}
+void Metadata::fillGenres(QSqlDatabase *db)
+{
+	QString thequery;
+	thequery.sprintf("SELECT genre"
+			" FROM videometadatagenre INNER JOIN videogenre"
+			" ON videometadatagenre.idgenre = videogenre.intid"
+			" WHERE idvideo=%d",id);
+	QSqlQuery query(thequery,db);
+	genres.clear();
+	if (query.isActive() && query.numRowsAffected()>1){
+	    while(query.next()){
+		genres.append(query.value(0).toString());
+	    }
+	}
+}
+
+void Metadata::fillCountries(QSqlDatabase *db)
+{
+	QString thequery;
+	thequery.sprintf("SELECT country" 
+			" FROM videometadatacountry INNER JOIN videocountry"
+			" ON videometadatacountry.idcountry = videocountry.intid"
+			" WHERE idvideo=%d",id);
+	QSqlQuery query(thequery,db);
+	countries.clear();
+	if (query.isActive() && query.numRowsAffected()>1){
+	    while(query.next()){
+		genres.append(query.value(0).toString());
+	    }
+	}
+
+}
+
 void Metadata::fillData(QSqlDatabase *db)
 {
     if (title == "")
@@ -139,6 +182,15 @@
         childID = query.value(11).toUInt();
         browse = query.value(12).toBool();
         playcommand = query.value(13).toString();
+
+	// Category
+	fillCategory(db);
+	
+	// Genres
+	fillGenres(db);
+
+	//Countries
+	fillCountries(db);
     }
 }
 
@@ -190,6 +242,9 @@
 
     db->exec(thequery);
 
+    updateGenres(db);
+    updateCountries(db);
+
     // easiest way to ensure we've got 'id' filled.
     fillData(db);
 }
@@ -231,17 +286,20 @@
     QString sqlcoverfile = coverfile;
     sqlcoverfile.replace(QRegExp("\""), QString("\\\""));
 
+    int idCategory = getIdCategory(db);
+
     QString thequery;
     thequery.sprintf("UPDATE videometadata SET title=\"%s\",director=\"%s\","
                      "plot=\"%s\",rating=\"%s\",year=%d,userrating=%f,"
                      "length=%d,filename=\"%s\",showlevel=%d,coverfile=\"%s\","
-                     "inetref=\"%s\",browse=%d,playcommand=\"%s\",childid=%d"
+                     "inetref=\"%s\",browse=%d,playcommand=\"%s\",childid=%d,"
+		     "category=%d"
                      " WHERE intid=%d",
                      title.utf8().data(), director.utf8().data(),
                      plot.utf8().data(), rating.utf8().data(), year,
                      userrating, length, sqlfilename.utf8().data(), showlevel,
                      sqlcoverfile.utf8().data(), inetref.utf8().data(), browse,
-                     playcommand.utf8().data(), childID, id);
+                     playcommand.utf8().data(), childID, idCategory, id);
 
     QSqlQuery a_query(thequery, db);
     if(!a_query.isActive())
@@ -249,5 +307,125 @@
         
         cerr << "metadata.o: The following metadata update failed: " << thequery << endl;
     }
+    updateGenres(db);
+    updateCountries(db);
+}
+
+int Metadata::getIdCategory(QSqlDatabase *db)
+{
+	int idcategory = 0;
+	if (category != ""){
+		QString thequery;
+		thequery.sprintf("SELECT intid FROM videocategory"
+				" WHERE category=\"%s\");",category.utf8().data());
+		QSqlQuery a_query(thequery,db);
+		if (a_query.isActive() && a_query.numRowsAffected()>0){
+			a_query.next();
+			idcategory = a_query.value(0).toInt();
+		}
+	}
+	return idcategory;
+	
+}
+void Metadata::updateGenres(QSqlDatabase *db)
+{
+    QString thequery;
+    //remove genre for this video
+    thequery.sprintf("DELETE FROM videometadatagenre where idvideo=%d",id);
+    QSqlQuery a_query(thequery,db);
+    if (!a_query.isActive()){
+	cerr << "metadata.o: The follwing metadata update failed: " << thequery << endl;
+    }
+    QStringList::Iterator genre;
+    for (genre = genres.begin() ; genre != genres.end() ; ++genre){
+	//search idgenre
+	thequery.sprintf("SELECT intid FROM videogenre where genre like \"%s\"",(*genre).utf8().data());
+	a_query.exec(thequery);
+	int idgenre = 0;
+	if (a_query.isActive()){
+		if (a_query.numRowsAffected()>0){
+			idgenre = a_query.value(0).toInt();
+		}else{
+		// we must create a new genre
+			thequery.sprintf("INSERT INTO videogenre (genre) VALUES (\"%s\")",(*genre).utf8().data());
+			if (a_query.isActive()){
+				thequery.sprintf("SELECT intid FROM videogenre WHERE genre like \"%s\"",(*genre).utf8().data());
+				a_query.exec(thequery);
+				if (a_query.isActive()){
+					if (a_query.numRowsAffected()>0){
+						idgenre=a_query.value(0).toInt();
+					}
+				}else{
+					cerr << "metadata.o: The Following metadata search failed :" << thequery << endl;
+				}
+			}else{
+				cerr << "metadata.o: The following metadata insert failed:" << thequery << endl;
+			}
+		}
+	}else{
+		cerr << "metadata.o: The following metadata search failed : " << thequery << endl;
+	}
+	if (idgenre >0){
+		//Add one genre for this video
+		thequery.sprintf("INSERT INTO videometadatagenre (idvideo, idgenre) VALUES (%d,%d)",id,idgenre);
+		a_query.exec(thequery);
+		if(!a_query.isActive()){
+			cerr << "metadata.o: The following metadata update failed :" << thequery << endl;
+		}
+	}
+    }
+}
+
+void Metadata::updateCountries(QSqlDatabase *db)
+{
+    QString thequery;
+    //remove countries for this video
+    thequery.sprintf("DELETE FROM videometadatacountry where idvideo=%d",id);
+    QSqlQuery a_query(thequery,db);
+    if (!a_query.isActive()){
+	cerr << "metadata.o: The following metadata update failed :" << thequery << endl;
+    }
+    QStringList::Iterator country;
+    for (country = countries.begin() ; country != countries.end() ; ++country){
+	// Search id of country
+	thequery.sprintf("SELECT intid FROM videocountry where country like \"%s\";",(*country).utf8().data());
+	a_query.exec(thequery);
+	int idcountry=0;
+	if (a_query.isActive()){
+		if (a_query.numRowsAffected()>0){
+			a_query.next();
+			idcountry = a_query.value(0).toInt();
+		}else{
+		//We must add a new country
+			thequery.sprintf("INSERT INTO videocountry (country) VALUES (\"%s\");",(*country).utf8().data());
+			a_query.exec(thequery);
+			if (a_query.isActive()){
+				//search the new idcountry
+				thequery.sprintf("SELECT intid FROM videocountry WHERE country like \"%s\"",(*country).utf8().data());
+				a_query.exec(thequery);
+				if (a_query.isActive()){
+					if (a_query.numRowsAffected()>0){
+						a_query.next();
+						idcountry=a_query.value(0).toInt();
+					}
+				}else{
+					cerr << "metadata.o : The following search failed : " << thequery << endl;
+				}
+			}else{
+				cerr << "metadata.o : The Following insert failed" << thequery << endl;
+			}
+		}
+	}else{
+		cerr << "metadata.o : The Following search failed : "<< thequery << endl;
+	}
+	if (idcountry>0){
+		// add current country for this video
+		thequery.sprintf("INSERT INTO videometadatacountry (idvideo, idcountry) VALUES (%d,\"%d\")",id, idcountry);
+		a_query.exec(thequery);
+		if (!a_query.isActive()){
+		    cerr << "metadata.o: The following metadata update failed :" << thequery << endl;
+		}
+	}
+    }
 }
 


More information about the mythtv-dev mailing list