[gentoo-commits] proj/tinderbox-cluster:master commit in: pym/tbc/, sql/

[Magnus Granberg]

commit:     cd2daee7c3a41d2e0dabceb63584b3fd98e25e94
Author:     Magnus Granberg <zorry <AT> gentoo <DOT> org>
AuthorDate: Sun Feb 21 20:45:10 2016 +0000
Commit:     Magnus Granberg <zorry <AT> gentoo <DOT> org>
CommitDate: Sun Feb 21 20:45:10 2016 +0000
URL:        https://gitweb.gentoo.org/proj/tinderbox-cluster.git/commit/?id=cd2daee7

Add new host job remove old cpv to remove old cpv in the db

 pym/tbc/db_mapping.py  |   2 +-
 pym/tbc/jobs.py        |   9 +++
 pym/tbc/old_cpv.py     | 154 ++++++++++++++++++++-----------------------------
 pym/tbc/sqlquerys.py   |  41 +++++++++++++
 sql/data_dump.sql      |   5 +-
 sql/structure_dump.sql |  60 ++++++++++++++++---
 6 files changed, 170 insertions(+), 101 deletions(-)

diff --git a/pym/tbc/db_mapping.py b/pym/tbc/db_mapping.py
index 9be0d0a..86884bf 100644
--- a/pym/tbc/db_mapping.py
+++ b/pym/tbc/db_mapping.py
@@ -36,7 +36,7 @@ class Logs(Base):
 
 class Jobs(Base):
 	JobId = Column('job_id', Integer, primary_key=True)
-	JobType = Column('job_type', Enum('updatedb', 'esync', 'gsync'))
+	JobType = Column('job_type', Enum('updatedb', 'esync', 'removeold_cpv'))
 	Status = Column('status', Enum('Runing', 'Done', 'Waiting'))
 	User = Column('user', String(50))
 	ConfigId = Column('config_id', Integer, ForeignKey('configs.config_id'))

diff --git a/pym/tbc/jobs.py b/pym/tbc/jobs.py
index 5c136d2..efe8623 100644
--- a/pym/tbc/jobs.py
+++ b/pym/tbc/jobs.py
@@ -5,6 +5,7 @@ from __future__ import print_function
 from tbc.sync import git_sync_main
 #from tbc.buildquerydb import add_buildquery_main, del_buildquery_main
 from tbc.updatedb import update_db_main
+from tbc.old_cpv import remove_old_cpv_main
 from tbc.sqlquerys import get_config_id, get_jobs, update_job_list
 from tbc.log import write_log
 
@@ -68,4 +69,12 @@ def jobs_main(session, config_id):
 				update_job_list(session, "Fail", job_id)
 				log_msg = "Job %s did fail." % (job_id,)
 				write_log(session, log_msg, "info", config_id, 'jobs_main')
+		elif job == "removeold_cpv":
+			update_job_list(session, "Runing", job_id)
+			log_msg = "Job %s is runing." % (job_id,)
+			write_log(session, log_msg, "info", config_id, 'jobs_main')
+			remove_old_cpv_main(session, config_id)
+			update_job_list(session, "Done", job_id)
+			log_msg = "Job %s is done.." % (job_id,)
+			write_log(session, log_msg, "info", config_id, 'jobs_main')
 	return

diff --git a/pym/tbc/old_cpv.py b/pym/tbc/old_cpv.py
index dbeba56..027f171 100644
--- a/pym/tbc/old_cpv.py
+++ b/pym/tbc/old_cpv.py
@@ -1,97 +1,69 @@
-# Copyright 1998-2015 Gentoo Foundation
+# Copyright 1998-2016 Gentoo Foundation
 # Distributed under the terms of the GNU General Public License v2
 
 from __future__ import print_function
-import logging
-from tbc.readconf import get_conf_settings
-reader=get_conf_settings()
-tbc_settings_dict=reader.read_tbc_settings_all()
-# make a CM
-from tbc.ConnectionManager import connectionManager
-CM=connectionManager(tbc_settings_dict)
-#selectively import the pgsql/mysql querys
-if CM.getName()=='pgsql':
-	from tbc.pgsql_querys import *
+import multiprocessing
+from datetime import datetime
+from tbc.log import write_log
+from sqlalchemy.orm import scoped_session, sessionmaker
+from tbc.ConnectionManager import NewConnection
+from tbc.readconf import  read_config_settings
+from tbc.sqlquerys import get_category_list_info, get_package_list_info, get_ebuild_list_info, \
+	get_build_job_all, del_old_build_jobs, del_old_ebuild, add_old_package,  del_old_package, \
+	add_old_category
 
-class tbc_old_cpv(object):
-	
-	def __init__(self, myportdb, mysettings):
-		self._mysettings = mysettings
-		self._myportdb = myportdb
+def remove_old_ebuilds(package_id, config_id, tbc_settings, cp):
+	today = datetime.utcnow()
+	session_factory = sessionmaker(bind=NewConnection(tbc_settings))
+	Session = scoped_session(session_factory)
+	session2 = Session()
+	EbuildsInfo = get_ebuild_list_info(session2, package_id)
+	for EbuildInfo in EbuildsInfo:
+		cpv = cp + '-' + EbuildInfo.Version
+		log_msg = "Checking: %s" % (cpv,)
+		write_log(session2, log_msg, "info", config_id, 'old_cpv.remove_old_ebuilds')
+		if not EbuildInfo.Active:
+			duration = today - EbuildInfo.TimeStamp
+			if duration.days > 30:
+				log_msg = "Removing: %s" % (cpv,)
+				write_log(session2, log_msg, "info", config_id, 'old_cpv.remove_old_ebuilds')
+				build_job_id_list = get_build_job_all(session2, EbuildInfo.EbuildId)
+				if build_job_id_list != []:
+					for build_job in build_job_id_list:
+						del_old_build_jobs(session2, build_job.BuildJobId)
+				del_old_ebuild(session2, EbuildInfo.EbuildId)
+	if not get_ebuild_list_info(session2, package_id):
+		add_old_package(session2, package_id)
+	session2.close
+	Session.remove()
 
-	def mark_old_ebuild_db(self, package_id):
-		conn=CM.getConnection()
-		# Get the ebuild list for cp
-		cp, repo = get_cp_repo_from_package_id(conn, package_id)
-		mytree = []
-		mytree.append(self._myportdb.getRepositoryPath(repo))
-		ebuild_list_tree = self._myportdb.cp_list(cp, use_cache=1, mytree=mytree)
-		# Get ebuild list on categories, package in the db
-		ebuild_list_db = cp_list_db(conn, package_id)
-		# Check if don't have the ebuild in the tree
-		# Add it to the no active list
-		old_ebuild_list = []
-		for ebuild_line in ebuild_list_db:
-			cpv_db = cp + "-" + ebuild_line[0]
-			if not cpv_db in ebuild_list_tree:
-				old_ebuild_list.append(ebuild_line)
-			# Set no active on ebuilds in the db that no longer in tree
-			if  old_ebuild_list != []:
-				for old_ebuild in old_ebuild_list:
-					logging.info("O %s/%s-%s", categories, package, old_ebuild[0])
-					add_old_ebuild(conn,package_id, old_ebuild_list)
-		# Check if we have older no activ ebuilds then 60 days
-		ebuild_old_list_db = cp_list_old_db(conn,package_id)
-		# Delete older ebuilds in the db
-		if ebuild_old_list_db != []:
-			for del_ebuild_old in ebuild_old_list_db:
-				logging.info("D %s/%s-%s", categories, package, del_ebuild_old[1])
-			del_old_ebuild(conn,ebuild_old_list_db)
-		CM.putConnection(conn)
+def remove_old_cpv_main(session, config_id):
+	today = datetime.datetime.utcnow
+	tbc_settings = read_config_settings()
+	# Use all cores when multiprocessing
+	#pool_cores = multiprocessing.cpu_count()
+	#pool = multiprocessing.Pool(processes = pool_cores)
 
-	def mark_old_package_db(self, package_id_list_tree):
-		conn=CM.getConnection()
-		# Get categories/package list from db
-		package_list_db = cp_all_db(conn)
-		old_package_id_list = []
-		# Check if don't have the categories/package in the tree
-		# Add it to the no active list
-		for package_line in package_list_db:
-			if not package_line in package_id_list_tree:
-				old_package_id_list.append(package_line)
-		# Set no active on categories/package and ebuilds in the db that no longer in tree
-		if old_package_id_list != []:
-			mark_old_list = add_old_package(conn,old_package_id_list)
-			if mark_old_list != []:
-				for x in mark_old_list:
-					element = get_cp_from_package_id(conn,x)
-					logging.info("O %s", element[0])
-		# Check if we have older no activ categories/package then 60 days
-		del_package_id_old_list = cp_all_old_db(conn,old_package_id_list)
-		# Delete older  categories/package and ebuilds in the db
-		if del_package_id_old_list != []:
-			for i in del_package_id_old_list:
-				element = get_cp_from_package_id(conn,i)
-				logging.info("D %s", element)
-			del_old_package(conn,del_package_id_old_list)
-		CM.putConnection(conn)
-		
-	def mark_old_categories_db(self):
-		conn=CM.getConnection()
-		# Get categories list from the tree and db
-		categories_list_tree = self._mysettings.categories
-		categories_list_db =get_categories_db(conn)
-		categories_old_list = []
-		# Check if don't have the categories in the tree
-		# Add it to the no active list
-		for categories_line in categories_list_db:
-			if not categories_line[0] in categories_list_tree:
-				old_c = get_old_categories(conn,categories_line[0])
-				if old_c is not None:
-					categories_old_list.append(categories_line)
-		# Delete older  categories in the db
-		if categories_old_list != []:
-			for real_old_categories in categories_old_list:
-				del_old_categories(conn,real_old_categoriess)
-				logging.info("D %s", real_old_categories)
-		CM.putConnection(conn)
\ No newline at end of file
+	CategorysInfo = get_category_list_info(session)
+	for CategoryInfo in CategorysInfo:
+		log_msg = "Checking: %s" % (CategoryInfo.Category,)
+		write_log(session2, log_msg, "info", config_id, 'old_cpv.remove_old_cpv_main')
+		PackagesInfo = get_package_list_info(session, CategoryInfo.CategoryId)
+		for PackageInfo in PackagesInfo:
+			cp = CategoryInfo.Category + '/' + PackageInfo.Package
+			if PackageInfo.Active:
+				# pool.apply_async( remove_old_ebuilds, (Package.PackageId, config_id, tbc_settings, cp,))
+				# use this when debuging
+			 	remove_old_ebuilds(PackageInfo.PackageId, config_id, tbc_settings, cp,)
+			else:
+				duration = today - PackageInfo.TimeStamp
+				if duration.days > 30:
+					log_msg = "Removing: %s" % (cp,)
+					write_log(session2, log_msg, "info", config_id, 'old_cpv.remove_old_cpv_main')
+					del_old_package(session, PackageInfo.PackageId)
+
+		#close and join the multiprocessing pools
+		# pool.close()
+		# pool.join()
+		if not get_package_list_info(session, CategoryId = CategoryInfo.CategoryId):
+			 add_old_category(session, category_id)

diff --git a/pym/tbc/sqlquerys.py b/pym/tbc/sqlquerys.py
index f9f14a2..75e3d30 100644
--- a/pym/tbc/sqlquerys.py
+++ b/pym/tbc/sqlquerys.py
@@ -583,3 +583,44 @@ def add_repoman_log(session, package_id, repoman_log, repoman_hash):
 			PackagesRepomanInfo.RepomanHash = repoman_hash
 			PackagesRepomanInfo.RepomanText = repoman_log
 			session.commit()
+
+def get_category_list_info(session):
+	try:
+		CategorysInfo = session.query(Categories).all()
+	except NoResultFound as e:
+		return False
+	return CategorysInfo
+
+def get_package_list_info(session, category_id):
+	try:
+		PackagesInfo = session.query(Packages).filter_by(CategoryId = category_id).all()
+	except NoResultFound as e:
+		return False
+	return PackagesInfo
+
+def get_ebuild_list_info(session, package_id):
+	try:
+		EbuildsInfo = session.query(Ebuilds).filter_by(PackageId = package_id).all()
+	except NoResultFound as e:
+		return False
+	return EbuildsInfo
+
+def del_old_ebuild(session, ebuild_id):
+	session.query(EbuildsRestrictions).filter(EbuildsRestrictions.EbuildId == ebuild_id).delete()
+	session.query(EbuildsIUse).filter(EbuildsIUse.EbuildId == ebuild_id).delete()
+	session.query(EbuildsKeywords).filter(EbuildsKeywords.EbuildId == ebuild_id).delete()
+	session.query(EbuildsMetadata).filter(EbuildsMetadata.EbuildId == ebuild_id).delete()
+	session.query(Ebuilds).filter(Ebuilds.EbuildId == ebuild_id).delete()
+	session.commit()
+
+def del_old_package(session, package_id):
+	session.query(PackagesRepoman).filter(PackagesRepoman.PackageId == package_id).delete()
+	session.query(PackagesEmails).filter(PackagesEmails.PackageId== package_id).delete()
+	session.query(PackagesMetadata).filter(PackagesMetadata.PackageId == package_id).delete()
+	session.query(Packages).filter(Packages.PackageId == package_id).delete()
+	session.commit()
+
+def add_old_category(session, category_id):
+	CategorysInfo = session.query(Categories).filter_by(CategoryId = category_id).one()
+	CategorysInfo.Active = False
+	session.commit()

diff --git a/sql/data_dump.sql b/sql/data_dump.sql
index c5e3a9c..c452356 100644
--- a/sql/data_dump.sql
+++ b/sql/data_dump.sql
@@ -168,8 +168,9 @@ INSERT INTO `hilight_css` (`hilight_css_id`, `hilight_css_name`, `hilight_css_co
 --
 
 INSERT INTO `jobs` (`job_id`, `job_type`, `status`, `user`, `config_id`, `run_config_id`, `time_stamp`) VALUES
-(1, 'updatedb', 'Done', 'cron', 1, 1, '2016-01-27 17:54:38'),
-(3, 'esync', 'Done', 'cron', 1, 1, '2016-02-13 14:35:58');
+(1, 'updatedb', 'Done', 'cron', 1, 1, '2016-02-20 14:13:32'),
+(2, 'esync', 'Done', 'cron', 1, 1, '2016-02-21 20:02:08'),
+(3, 'removeold_cpv', 'Done', 'cron', 1, 1, '2016-02-21 20:04:51');
 
 --
 -- Dumping data for table `setups`

diff --git a/sql/structure_dump.sql b/sql/structure_dump.sql
index 9c2f397..cf4bab7 100644
--- a/sql/structure_dump.sql
+++ b/sql/structure_dump.sql
@@ -3,9 +3,9 @@
 -- http://www.phpmyadmin.net
 --
 -- Host: localhost
--- Generation Time: Jan 31, 2016 at 06:07 PM
+-- Generation Time: Feb 21, 2016 at 08:08 PM
 -- Server version: 10.0.22-MariaDB-log
--- PHP Version: 5.6.16-pl0-gentoo
+-- PHP Version: 7.0.3-pl0-gentoo
 
 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
 SET time_zone = "+00:00";
@@ -31,9 +31,9 @@ BEGIN
   DECLARE in_job_id INT;
   SET in_config_id = (SELECT config_id
     FROM configs WHERE default_config = True);
-  SET in_job_id = (SELECT job_id FROM jobs
+  SET in_job_id = (SELECT job_id FROM jobs 
     WHERE job_type = 'esync'
-    AND config_id = in_config_id
+    AND config_id = in_config_id 
     AND status = 'Done'
     LIMIT 1);
   IF in_job_id >= 1 THEN
@@ -43,6 +43,25 @@ BEGIN
   END IF;
 END$$
 
+CREATE DEFINER=`tbc`@`localhost` PROCEDURE `add_jobs_removeold_cpv`()
+    MODIFIES SQL DATA
+BEGIN
+  DECLARE in_config_id INT;
+  DECLARE in_job_id INT;
+  SET in_config_id = (SELECT config_id
+    FROM configs WHERE default_config = True);
+  SET in_job_id = (SELECT job_id FROM jobs 
+    WHERE job_type = 'removeold_cpv'
+    AND config_id = in_config_id 
+    AND status = 'Done'
+    LIMIT 1);
+  IF in_job_id >= 1 THEN
+    UPDATE jobs SET user = 'cron', status = 'Waiting' WHERE job_type = 'removeold_cpv';
+  ELSE
+  	SET in_job_id = 0;
+  END IF;
+END$$
+
 DELIMITER ;
 
 -- --------------------------------------------------------
@@ -331,7 +350,7 @@ CREATE TABLE IF NOT EXISTS `ebuilds_metadata` (
 `id` int(11) NOT NULL,
   `ebuild_id` int(11) NOT NULL,
   `commit` varchar(100) NOT NULL COMMENT 'Git commit',
-  `New` tinyint(1) NOT NULL,
+  `new` tinyint(1) NOT NULL,
   `descriptions` varchar(200) NOT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
@@ -428,7 +447,7 @@ CREATE TABLE IF NOT EXISTS `hilight_css` (
 
 CREATE TABLE IF NOT EXISTS `jobs` (
 `job_id` int(11) NOT NULL,
-  `job_type` enum('esync','updatedb') NOT NULL,
+  `job_type` enum('esync','updatedb','removeold_cpv') NOT NULL,
   `status` enum('Runing','Done','Waiting') NOT NULL DEFAULT 'Waiting',
   `user` varchar(20) NOT NULL,
   `config_id` int(11) NOT NULL,
@@ -552,6 +571,18 @@ CREATE TABLE IF NOT EXISTS `setups` (
 -- --------------------------------------------------------
 
 --
+-- Table structure for table `tbc_config`
+--
+
+CREATE TABLE IF NOT EXISTS `tbc_config` (
+`id` int(11) NOT NULL,
+  `webinker` varchar(100) NOT NULL,
+  `webbug` varchar(100) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+-- --------------------------------------------------------
+
+--
 -- Table structure for table `uses`
 --
 
@@ -793,6 +824,12 @@ ALTER TABLE `setups`
  ADD PRIMARY KEY (`setup_id`), ADD UNIQUE KEY `setup_id` (`setup_id`);
 
 --
+-- Indexes for table `tbc_config`
+--
+ALTER TABLE `tbc_config`
+ ADD PRIMARY KEY (`id`);
+
+--
 -- Indexes for table `uses`
 --
 ALTER TABLE `uses`
@@ -993,6 +1030,11 @@ MODIFY `restriction_id` int(11) NOT NULL AUTO_INCREMENT;
 ALTER TABLE `setups`
 MODIFY `setup_id` int(11) NOT NULL AUTO_INCREMENT;
 --
+-- AUTO_INCREMENT for table `tbc_config`
+--
+ALTER TABLE `tbc_config`
+MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
+--
 -- AUTO_INCREMENT for table `uses`
 --
 ALTER TABLE `uses`
@@ -1001,10 +1043,14 @@ DELIMITER $$
 --
 -- Events
 --
-CREATE DEFINER=`tbc`@`localhost` EVENT `add_esync_jobs` ON SCHEDULE EVERY 30 MINUTE STARTS '2012-12-23 17:15:13' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
+CREATE DEFINER=`tbc`@`localhost` EVENT `add_esync_jobs` ON SCHEDULE EVERY 1 HOUR STARTS '2012-12-23 17:15:13' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
   CALL add_jobs_esync();
 END$$
 
+CREATE DEFINER=`tbc`@`localhost` EVENT `add_removeold_cpv_jobs` ON SCHEDULE EVERY 24 HOUR STARTS '2016-02-21 21:00:22' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
+  CALL add_jobs_removeold_cpv();
+END$$
+
 DELIMITER ;
 
 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;