How to use test_started_time method in autotest

Best Python code snippet using autotest_python

model.py

Source:model.py

1"""2"""3import lightgbm as lgb4import numpy as np5import pandas as pd6pd.options.mode.chained_assignment = None7from sklearn.linear_model import ElasticNet8from sklearn.linear_model import LinearRegression as lr9import itertools as it10import matplotlib.pyplot as plt11import matplotlib.ticker as mtick12from lib.feature_engineering import FeatureEngineering13from lib.config import metadata_process_info14class ModelMain(FeatureEngineering):15    def __init__(self, data_list,16                     label_columns,17                     num_columns,18                     target_col = metadata_process_info['target_col']19                ):20        FeatureEngineering.__init__(self, data_list, label_columns, num_columns, target_col)21        # set parameter_tuned option22        self.parameter_tuned = False23        self.importance_flag = False24        self.optimized_params = None25    def cross_prediction(self, 26                         model_name_list, 27                         params_dict, 28                         percent_data_process_info, 29                         nfold = 6, 30                         back_consideration_date = 180):        31        # reset the flag32        self.performance_bootstrapped_flag = False33    34        35        # step 1: do time split36        self.timesplit(percent_data_process_info, nfold, back_consideration_date)37        38        # step 2: initialize the params39        self._cross_prediction_init()40        41        # step 3: cross prediction42        fold_n = len(self.train_fold_index) 43        for fold in range(0, fold_n):44            # train and test45            train_index = self.train_fold_index[fold]46            test_index  = self.test_fold_index[fold]47            x_train, x_test = self.X_base.loc[train_index,:], self.X_base.loc[test_index,:]48            y_train, y_test = self.y_base[train_index], self.y_base[test_index]49            ### adhoc50            #x_train = x_train.drop(columns = ['program_type', 'platform_name'])51            #x_test = x_test.drop(columns = ['program_type', 'platform_name'])52            match_id_test = self.target_copy['match_id'][test_index]53            54            # loop over the selected models55            for model_name in model_name_list:56                # train 57                model = self.model_train(model_name, 58                        params_dict, 59                        x_train, y_train, 60                        percent_data_process_info)61                self.trained_model[model_name] = model62                63                # predict 64                y_predict = self.model_predict(model_name, x_test, percent_data_process_info)65                66                # output transformation67                y_predict = self.output_transformation(y_predict, x_test, percent_data_process_info)68                69                # recording70                self.output.loc[test_index, 'cross_predict_' + model_name] = y_predict71                72            y_test = self.output_transformation(y_test, x_test, percent_data_process_info)73            # record the target, fold, and match_id 74            self.output.loc[test_index, 'target'] = y_test   75            self.output.loc[test_index, 'fold'] = fold76            self.output.loc[test_index, 'match_id'] = match_id_test77        78        # processing output and calculate evaluation metrics79        self._output_and_metrics_processing(model_name_list)80        81        self.output_flag = True82        83    def model_train(self, model_name, params_dict, 84                    x_train, y_train, percent_data_process_info):85        86        if model_name == 'lgb':87            model = self._lgb_model_train(params_dict, x_train, y_train, percent_data_process_info)88        elif model_name =='lr':89            model = self._lr_model_train(x_train, y_train, percent_data_process_info)90        elif model_name =='enet':91            model = self._enet_model_train(params_dict, x_train, y_train, percent_data_process_info)92        else:93            print('model name not valid')94            95        return model96    def model_predict(self, model_name, x_test, percent_data_process_info):97        if model_name in ['lr','enet','lgb']:98            if model_name in ['lr','enet']:99                x_test  = x_test[self.columns_used]100            y_predict = self.trained_model[model_name].predict(x_test)101        102        else:103            return 0104            print('the model name is not valid')105            106        if model_name in ['enet']:107            y_predict = y_predict.flatten()108        ### chagned, correct to 0 of the prediction is negative109        if percent_data_process_info['target_log_transformation']==False:110            y_predict[y_predict<0] = 0111                       112        return y_predict113        114        115    def _lgb_model_train(self, params_dict, x_train, y_train, percent_data_process_info):116        # initilization117        params = params_dict['lgb']118        self.params_record = params119        120        ### new set to monotone constraint ###121        if percent_data_process_info['max_num_day'] < 1:122            constraint_list = []123            for col in x_train.columns:124                if col in percent_data_process_info['prelaucn_monotonic_features']:125                    constraint_list.append(1)126                else:127                    constraint_list.append(0)128                129            params['monotone_constraint'] = constraint_list130            params['objective'] = 'rmse'131        else:132            params['monotone_constraint'] = None133        # set training data134        train_data = lgb.Dataset(x_train, label=y_train)135        #The test data should not be the predicting fold, should be the 136        #   lastest days in the trian fold137        #test_data = lgb.Dataset(x_test, label=y_test, categorical_feature=self.categorical_features)138        139        # lightgbm model140        model = lgb.train(params,141                               train_data,142                               verbose_eval = False143                         )144        145        # feature importance recording146        if self.importance_flag:147            self.importance += model.feature_importance() 148        else:149            self.importance = model.feature_importance() 150            self.importance_flag = True151            152        return model153        154    def _enet_model_train(self, params_dict, x_train, y_train, percent_data_process_info):155        # initialization156        params = params_dict['enet']157        # benchmark prediction158        if percent_data_process_info['max_num_day']<=1:159            self.columns_used = self.prelaunch_processed_columns160            self.columns_used.extend(['platform_name', 'program_type'])161        else:162            self.columns_used = list(self.day_column_list_no_last_season)163            self.columns_used.extend(x_train.columns[x_train.columns.str.contains\164                                            ('dayofweek_earliest_date')])165        166        167        model = ElasticNet(168                        alpha = params_dict['enet']['alpha'],169                        l1_ratio = params_dict['enet']['l1_ratio'],170                        max_iter = 10000, 171                        normalize = False).fit(x_train[self.columns_used].values, 172                                             y_train.values.reshape(-1,1))                               173        return model174    175    def _lr_model_train(self, x_train, y_train, percent_data_process_info):176        # benchmark prediction177        if percent_data_process_info['max_num_day']<=1:178            self.columns_used = self.prelaunch_processed_columns179            self.columns_used.extend(['platform_name', 'program_type'])180        else:181            self.columns_used = list(self.day_column_list_no_last_season)182            self.columns_used.extend(x_train.columns[x_train.columns.str.contains\183                                            ('dayofweek_earliest_date')])184        185        model = lr().fit(x_train[self.columns_used].values, y_train.values.reshape(-1,1))186                                               187        return model188    189    def _output_and_metrics_processing(self, model_name_list):190        # drop the for-training-only data rows (since they have no rolling cross predicted values)191        self.output = self.output.loc[self.output.drop(columns = ['program_type']).dropna(how = 'all').index,:]192        193        # calculate evaluation metrics194        for model_name in model_name_list:195            self.output['smape_' + model_name] = abs(self.output['cross_predict_' + model_name] - self.output['target'])/\196                                    ((self.output['cross_predict_' + model_name] + self.output['target'])/2)197            self.output['mae_' + model_name] = abs(self.output['cross_predict_' + model_name] - self.output['target'])198        # merge basic info199        self.output = self.output.merge(self.base_copy[['title_name','match_id_platform']], left_index = True, right_index = True)200        201    def timesplit(self, percent_data_process_info, nfold = 10, back_consideration_date = 180):202        # Use this line to get the lastest date in the data recorded203        test_started_time = self.title_offered_ts[self.X_base['platform_name'] == 1].max()204        if percent_data_process_info['max_num_day'] > 0:205            test_started_time = test_started_time - pd.Timedelta(days = back_consideration_date)206        else:207            # give longer period for the trailer to get values, roughly two months208            test_started_time = test_started_time - pd.Timedelta(days = back_consideration_date)209        test_folds_ind = self.title_offered_ts[self.title_offered_ts>= test_started_time]210        211        # find the date boundaries of the folds212        nfold = int(nfold)213        fold_bound = test_folds_ind.quantile(np.linspace(0,1,nfold+1)).reset_index(drop=True)214        215        # generate train and test index        216        self.train_fold_index, self.test_fold_index = self._generate_test_and_train_index(fold_bound, self.title_offered_ts)217                218    def _generate_test_and_train_index(self, fold_bound, title_offered_ts):219        # generate folds220        test_fold = []221        train_fold = []222        for cnt in range(len(fold_bound)-1):223            test_lower_bound = fold_bound[cnt]224            # to exclude the upperbound boundary225            test_upper_bound = fold_bound[cnt + 1] - pd.Timedelta(seconds = 1)226            # working on test227            test_ind = title_offered_ts[title_offered_ts.between(test_lower_bound, test_upper_bound)].index228            if len(test_ind)>0:229                test_fold.append(test_ind)230                # working on train231                train_upper_bound = title_offered_ts[test_ind].min() - pd.Timedelta(days = 28)232                train_ind = title_offered_ts[title_offered_ts < train_upper_bound].index233                train_fold.append(train_ind)234                235            else:236                print('Warning: one test fold has no elements, did not include in the test_fold output')237                238        return train_fold, test_fold  239    240    def _cross_prediction_init(self):241        # set output_format242        self.output = pd.DataFrame(self.y_base).copy()243        self.output['program_type'] = self.X_base['program_type']244        self.output.columns = ['target', 'program_type']245        self.output['target'] = np.nan246        247        self.trained_model = {}248        249        self.importance = None250        self.importance_flag = False251        self.output_flag = False252    253        254    def _param_tunning_flag_init(self, model_name):255        # initialize parameter tunning flags256        self.parameter_tuning_stats = {}257        self.parameter_tuning_stats[model_name] = {}258        self.parameter_tuning_stats[model_name]['min_smape_original_flag'] = False259        self.parameter_tuning_stats[model_name]['min_smape_all_flag'] = False     260    261    def predict_new_titles(self, 262                         model_name_list, 263                         params_dict, 264                         percent_data_process_info):  265        266        # step 1: initialize the params267        self._predict_new_title_init()268        269        # step 2: train then predict270        for model_name in model_name_list:271            # train 272            model = self.model_train(model_name, 273                    params_dict, 274                    self.X_base, self.y_base, 275                    percent_data_process_info)276            277            self.trained_model[model_name] = model278            279            # predict 280            y_predict = self.model_predict(model_name, self.X_pred, percent_data_process_info)281            282            # output transformation283            y_predict = self.output_transformation(y_predict, self.X_pred, percent_data_process_info)284            285            # recording286            self.new_title_output['day_' + str(percent_data_process_info['max_num_day']) + '_' + model_name] = y_predict287        288    289    def _predict_new_title_init(self):290        # set output_format291        self.new_title_output = pd.DataFrame(self.y_pred).copy()292        self.new_title_output['program_type'] = self.X_pred['program_type']293        self.new_title_output.columns = ['target', 'program_type']294        self.new_title_output['target'] = np.nan295        self.new_title_output = self.base_copy[['title_name','match_id_platform','match_id','platform_name']].\296            merge(self.new_title_output, 297                  left_index = True, 298                  right_index = True)299        300        self.trained_model = {}301        302        self.importance = None303        self.importance_flag = False304        self.output_flag = False305    306    def output_transformation(self, y, x_test, percent_data_process_info):307    # result processing308        if y.ndim > 1:309            y = y.flatten()310        if percent_data_process_info['target_log_transformation']:311            y = np.exp(y)312            if percent_data_process_info['log_ratio_transformation']:313                y = y*np.exp(x_test['log_day001_percent_viewed'])314                315        return y316    317    def print_performance(self):318        if self.output_flag:319            print('Overall: lgb smape is {}, benchmark smape is {}'.format(self.output['smape_lgb'].mean(), self.output['smape_benchmark'].mean()))320            print('Original: lgb smape is {}, benchmark smape is {}'.format(self.output.loc[self.output['program_type']==1, 'smape_lgb'].mean(), 321                                                                            self.output.loc[self.output['program_type']==1, 'smape_benchmark'].mean()))322        else:323            print('run the cross prediction first')324            325    def feature_importance(self, importance, columns, n_features = 20, filename = 'feature_importance'):326        fig = plt.figure(figsize = (12,6)) 327    # just show one model's result, but should be representative enough328        importance_percent = importance/importance.sum()*100329        importance_percent = pd.DataFrame({'importance':importance_percent, 'feature':columns})330        importance_percent = importance_percent.sort_values('importance', ascending = False).head(n_features)331        plot = importance_percent.plot.bar(x = 'feature', y= 'importance', 332                                           rot = 90, figsize = (8,4))333        334        plt.xlabel('Feature names', fontsize=16)335        plt.ylabel('Frequency [%]', fontsize=16)336        plot.get_figure().savefig('{}.png'.format(filename))337    338    def parameter_tuning(self, model_name, 339                          params_tunning_dict, 340                          percent_data_process_info,341                          nfold = 6,342                          back_consideration_date = 180):343        if model_name!= 'lr':344            self._param_tunning_flag_init(model_name)345            346            model_name_list = [model_name]347            self.param_combinations, allNames = self._parameter_tuning_init(params_tunning_dict, model_name)348            349            ct= 1350            for com in self.param_combinations:351                params_dict_input = self._set_params_dict(model_name, allNames, com)352                353                print('parameter combination {}'.format(ct)) 354                ct+=1355                self.cross_prediction(model_name_list, params_dict_input, 356                                           percent_data_process_info,357                                           nfold,358                                           back_consideration_date)359                360                smape_mean = self.output['smape_lgb'].mean()361                smape_original_mean = self.output.loc[self.output['program_type']==1, 'smape_lgb'].mean()362                363                self._output_updating([smape_mean, smape_original_mean], params_dict_input)364        else:365            print('lr model does not need tunning')366    367    def _output_updating(self, smape_list, params_dict_input):368        smape_type = [['min_smape_all', 'maximum_smape_all'], 369                                        ['min_smape_original', 'maximum_smape_original']]370        smape_flag = ['min_smape_all_flag','min_smape_original_flag']371        for smape, min_max_name, flag in zip(smape_list, smape_type, smape_flag):            372            if smape < self.parameter_tuning_stats[min_max_name[0]]:373                self.parameter_tuning_stats[min_max_name[0]]  = smape374                self.min_smape_param[min_max_name[0]] = params_dict_input375                self.parameter_tuning_stats[flag] = True376                377            if smape > self.parameter_tuning_stats[min_max_name[1]]:378                self.parameter_tuning_stats[min_max_name[1]]  = smape379                380    def _parameter_tuning_init(self, params_tunning_dict, model_name):381        self.parameter_tuning_stats = {}382        self.parameter_tuning_stats['min_smape_all'] = np.inf383        self.parameter_tuning_stats['maximum_smape_all'] = -np.inf384        self.parameter_tuning_stats['min_smape_original'] = np.inf385        self.parameter_tuning_stats['maximum_smape_original'] = -np.inf386        self.parameter_tuning_stats['min_smape_all_flag'] = False387        self.parameter_tuning_stats['min_smape_original_flag'] = False388        self.min_smape_param = {}389        390        return self._gen_combinations(params_tunning_dict, model_name)391        392    393    def _gen_combinations(self, params_tunning_dict, model_name):394        params_dict = params_tunning_dict[model_name]395        allNames = params_dict.keys()396        combinations = list(it.product(*(params_dict[Name] for Name in allNames)))397        return combinations, allNames398    def _set_params_dict(self, model_name, allNames, com):399        params_dict = {}400        params_dict[model_name] = {}401        for key, cnt in zip(allNames, range(len(allNames))):402            params_dict[model_name][key] = com[cnt]403            404        return params_dict405        406        407    408    ...

031_rename_tko_tables.py

Source:031_rename_tko_tables.py

1import common2from autotest_lib.database import db_utils3RECREATE_VIEWS_UP = """4CREATE VIEW tko_test_view AS5SELECT  tko_tests.test_idx,6        tko_tests.job_idx,7        tko_tests.test,8        tko_tests.subdir,9        tko_tests.kernel_idx,10        tko_tests.status,11        tko_tests.reason,12        tko_tests.machine_idx,13        tko_tests.started_time AS test_started_time,14        tko_tests.finished_time AS test_finished_time,15        tko_jobs.tag AS job_tag,16        tko_jobs.label AS job_label,17        tko_jobs.username AS job_username,18        tko_jobs.queued_time AS job_queued_time,19        tko_jobs.started_time AS job_started_time,20        tko_jobs.finished_time AS job_finished_time,21        tko_machines.hostname AS machine_hostname,22        tko_machines.machine_group,23        tko_machines.owner AS machine_owner,24        tko_kernels.kernel_hash,25        tko_kernels.base AS kernel_base,26        tko_kernels.printable AS kernel_printable,27        tko_status.word AS status_word28FROM tko_tests29INNER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx30INNER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx31INNER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx32INNER JOIN tko_status ON tko_status.status_idx = tko_tests.status;33CREATE VIEW tko_perf_view AS34SELECT  tko_tests.test_idx,35        tko_tests.job_idx,36        tko_tests.test,37        tko_tests.subdir,38        tko_tests.kernel_idx,39        tko_tests.status,40        tko_tests.reason,41        tko_tests.machine_idx,42        tko_tests.started_time AS test_started_time,43        tko_tests.finished_time AS test_finished_time,44        tko_jobs.tag AS job_tag,45        tko_jobs.label AS job_label,46        tko_jobs.username AS job_username,47        tko_jobs.queued_time AS job_queued_time,48        tko_jobs.started_time AS job_started_time,49        tko_jobs.finished_time AS job_finished_time,50        tko_machines.hostname AS machine_hostname,51        tko_machines.machine_group,52        tko_machines.owner AS machine_owner,53        tko_kernels.kernel_hash,54        tko_kernels.base AS kernel_base,55        tko_kernels.printable AS kernel_printable,56        tko_status.word AS status_word,57        tko_iteration_result.iteration,58        tko_iteration_result.attribute AS iteration_key,59        tko_iteration_result.value AS iteration_value60FROM tko_tests61INNER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx62INNER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx63INNER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx64INNER JOIN tko_status ON tko_status.status_idx = tko_tests.status65INNER JOIN tko_iteration_result ON66        tko_iteration_result.test_idx = tko_tests.test_idx;67CREATE VIEW tko_test_view_2 AS68SELECT  tko_tests.test_idx,69        tko_tests.job_idx,70        tko_tests.test AS test_name,71        tko_tests.subdir,72        tko_tests.kernel_idx,73        tko_tests.status AS status_idx,74        tko_tests.reason,75        tko_tests.machine_idx,76        tko_tests.started_time AS test_started_time,77        tko_tests.finished_time AS test_finished_time,78        tko_jobs.tag AS job_tag,79        tko_jobs.label AS job_name,80        tko_jobs.username AS job_owner,81        tko_jobs.queued_time AS job_queued_time,82        tko_jobs.started_time AS job_started_time,83        tko_jobs.finished_time AS job_finished_time,84        tko_jobs.afe_job_id AS afe_job_id,85        tko_machines.hostname AS hostname,86        tko_machines.machine_group AS platform,87        tko_machines.owner AS machine_owner,88        tko_kernels.kernel_hash,89        tko_kernels.base AS kernel_base,90        tko_kernels.printable AS kernel,91        tko_status.word AS status92FROM tko_tests93INNER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx94INNER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx95INNER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx96INNER JOIN tko_status ON tko_status.status_idx = tko_tests.status;97CREATE VIEW tko_test_view_outer_joins AS98SELECT  tko_tests.test_idx,99        tko_tests.job_idx,100        tko_tests.test AS test_name,101        tko_tests.subdir,102        tko_tests.kernel_idx,103        tko_tests.status AS status_idx,104        tko_tests.reason,105        tko_tests.machine_idx,106        tko_tests.started_time AS test_started_time,107        tko_tests.finished_time AS test_finished_time,108        tko_jobs.tag AS job_tag,109        tko_jobs.label AS job_name,110        tko_jobs.username AS job_owner,111        tko_jobs.queued_time AS job_queued_time,112        tko_jobs.started_time AS job_started_time,113        tko_jobs.finished_time AS job_finished_time,114        tko_machines.hostname AS hostname,115        tko_machines.machine_group AS platform,116        tko_machines.owner AS machine_owner,117        tko_kernels.kernel_hash,118        tko_kernels.base AS kernel_base,119        tko_kernels.printable AS kernel,120        tko_status.word AS status121FROM tko_tests122LEFT OUTER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx123LEFT OUTER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx124LEFT OUTER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx125LEFT OUTER JOIN tko_status ON tko_status.status_idx = tko_tests.status;126CREATE VIEW tko_perf_view_2 AS127SELECT  tko_tests.test_idx,128        tko_tests.job_idx,129        tko_tests.test AS test_name,130        tko_tests.subdir,131        tko_tests.kernel_idx,132        tko_tests.status AS status_idx,133        tko_tests.reason,134        tko_tests.machine_idx,135        tko_tests.started_time AS test_started_time,136        tko_tests.finished_time AS test_finished_time,137        tko_jobs.tag AS job_tag,138        tko_jobs.label AS job_name,139        tko_jobs.username AS job_owner,140        tko_jobs.queued_time AS job_queued_time,141        tko_jobs.started_time AS job_started_time,142        tko_jobs.finished_time AS job_finished_time,143        tko_machines.hostname AS hostname,144        tko_machines.machine_group AS platform,145        tko_machines.owner AS machine_owner,146        tko_kernels.kernel_hash,147        tko_kernels.base AS kernel_base,148        tko_kernels.printable AS kernel,149        tko_status.word AS status,150        tko_iteration_result.iteration,151        tko_iteration_result.attribute AS iteration_key,152        tko_iteration_result.value AS iteration_value153FROM tko_tests154LEFT OUTER JOIN tko_jobs ON tko_jobs.job_idx = tko_tests.job_idx155LEFT OUTER JOIN tko_machines ON tko_machines.machine_idx = tko_jobs.machine_idx156LEFT OUTER JOIN tko_kernels ON tko_kernels.kernel_idx = tko_tests.kernel_idx157LEFT OUTER JOIN tko_status ON tko_status.status_idx = tko_tests.status158LEFT OUTER JOIN tko_iteration_result ON159        tko_iteration_result.test_idx = tko_tests.test_idx;160"""161RECREATE_VIEWS_DOWN = """162CREATE VIEW test_view AS163SELECT  tests.test_idx,164        tests.job_idx,165        tests.test,166        tests.subdir,167        tests.kernel_idx,168        tests.status,169        tests.reason,170        tests.machine_idx,171        tests.started_time AS test_started_time,172        tests.finished_time AS test_finished_time,173        jobs.tag AS job_tag,174        jobs.label AS job_label,175        jobs.username AS job_username,176        jobs.queued_time AS job_queued_time,177        jobs.started_time AS job_started_time,178        jobs.finished_time AS job_finished_time,179        machines.hostname AS machine_hostname,180        machines.machine_group,181        machines.owner AS machine_owner,182        kernels.kernel_hash,183        kernels.base AS kernel_base,184        kernels.printable AS kernel_printable,185        status.word AS status_word186FROM tests187INNER JOIN jobs ON jobs.job_idx = tests.job_idx188INNER JOIN machines ON machines.machine_idx = jobs.machine_idx189INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx190INNER JOIN status ON status.status_idx = tests.status;191CREATE VIEW perf_view AS192SELECT  tests.test_idx,193        tests.job_idx,194        tests.test,195        tests.subdir,196        tests.kernel_idx,197        tests.status,198        tests.reason,199        tests.machine_idx,200        tests.started_time AS test_started_time,201        tests.finished_time AS test_finished_time,202        jobs.tag AS job_tag,203        jobs.label AS job_label,204        jobs.username AS job_username,205        jobs.queued_time AS job_queued_time,206        jobs.started_time AS job_started_time,207        jobs.finished_time AS job_finished_time,208        machines.hostname AS machine_hostname,209        machines.machine_group,210        machines.owner AS machine_owner,211        kernels.kernel_hash,212        kernels.base AS kernel_base,213        kernels.printable AS kernel_printable,214        status.word AS status_word,215        iteration_result.iteration,216        iteration_result.attribute AS iteration_key,217        iteration_result.value AS iteration_value218FROM tests219INNER JOIN jobs ON jobs.job_idx = tests.job_idx220INNER JOIN machines ON machines.machine_idx = jobs.machine_idx221INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx222INNER JOIN status ON status.status_idx = tests.status223INNER JOIN iteration_result ON iteration_result.test_idx = tests.test_idx;224CREATE VIEW test_view_outer_joins AS225SELECT  tests.test_idx,226        tests.job_idx,227        tests.test AS test_name,228        tests.subdir,229        tests.kernel_idx,230        tests.status AS status_idx,231        tests.reason,232        tests.machine_idx,233        tests.started_time AS test_started_time,234        tests.finished_time AS test_finished_time,235        jobs.tag AS job_tag,236        jobs.label AS job_name,237        jobs.username AS job_owner,238        jobs.queued_time AS job_queued_time,239        jobs.started_time AS job_started_time,240        jobs.finished_time AS job_finished_time,241        machines.hostname AS hostname,242        machines.machine_group AS platform,243        machines.owner AS machine_owner,244        kernels.kernel_hash,245        kernels.base AS kernel_base,246        kernels.printable AS kernel,247        status.word AS status248FROM tests249LEFT OUTER JOIN jobs ON jobs.job_idx = tests.job_idx250LEFT OUTER JOIN machines ON machines.machine_idx = jobs.machine_idx251LEFT OUTER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx252LEFT OUTER JOIN status ON status.status_idx = tests.status;253CREATE VIEW test_view_2 AS254SELECT  tests.test_idx,255        tests.job_idx,256        tests.test AS test_name,257        tests.subdir,258        tests.kernel_idx,259        tests.status AS status_idx,260        tests.reason,261        tests.machine_idx,262        tests.started_time AS test_started_time,263        tests.finished_time AS test_finished_time,264        jobs.tag AS job_tag,265        jobs.label AS job_name,266        jobs.username AS job_owner,267        jobs.queued_time AS job_queued_time,268        jobs.started_time AS job_started_time,269        jobs.finished_time AS job_finished_time,270        jobs.afe_job_id AS afe_job_id,271        machines.hostname AS hostname,272        machines.machine_group AS platform,273        machines.owner AS machine_owner,274        kernels.kernel_hash,275        kernels.base AS kernel_base,276        kernels.printable AS kernel,277        status.word AS status278FROM tests279INNER JOIN jobs ON jobs.job_idx = tests.job_idx280INNER JOIN machines ON machines.machine_idx = jobs.machine_idx281INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx282INNER JOIN status ON status.status_idx = tests.status;283CREATE VIEW perf_view_2 AS284SELECT  tests.test_idx,285        tests.job_idx,286        tests.test AS test_name,287        tests.subdir,288        tests.kernel_idx,289        tests.status AS status_idx,290        tests.reason,291        tests.machine_idx,292        tests.started_time AS test_started_time,293        tests.finished_time AS test_finished_time,294        jobs.tag AS job_tag,295        jobs.label AS job_name,296        jobs.username AS job_owner,297        jobs.queued_time AS job_queued_time,298        jobs.started_time AS job_started_time,299        jobs.finished_time AS job_finished_time,300        machines.hostname AS hostname,301        machines.machine_group AS platform,302        machines.owner AS machine_owner,303        kernels.kernel_hash,304        kernels.base AS kernel_base,305        kernels.printable AS kernel,306        status.word AS status,307        iteration_result.iteration,308        iteration_result.attribute AS iteration_key,309        iteration_result.value AS iteration_value310FROM tests311LEFT OUTER JOIN jobs ON jobs.job_idx = tests.job_idx312LEFT OUTER JOIN machines ON machines.machine_idx = jobs.machine_idx313LEFT OUTER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx314LEFT OUTER JOIN status ON status.status_idx = tests.status315LEFT OUTER JOIN iteration_result ON iteration_result.test_idx = tests.test_idx;316"""317ORIG_NAMES = (318        'embedded_graphing_queries',319        'iteration_attributes',320        'iteration_result',321        'jobs',322        'kernels',323        'machines',324        'patches',325        'query_history',326        'saved_queries',327        'status',328        'test_attributes',329        'test_labels',330        'test_labels_tests',331        'tests',332        )333RENAMES_UP = dict((name, 'tko_' + name) for name in ORIG_NAMES)334VIEWS_TO_DROP_UP = (335        'test_view',336        'test_view_2',337        'test_view_outer_joins',338        'perf_view',339        'perf_view_2',340        )341RENAMES_DOWN = dict((value, key) for key, value in RENAMES_UP.iteritems())342VIEWS_TO_DROP_DOWN = ['tko_' + view for view in VIEWS_TO_DROP_UP]343def migrate_up(manager):344    db_utils.drop_views(manager, VIEWS_TO_DROP_UP)345    db_utils.rename(manager, RENAMES_UP)346    manager.execute_script(RECREATE_VIEWS_UP)347def migrate_down(manager):348    db_utils.drop_views(manager, VIEWS_TO_DROP_DOWN)349    db_utils.rename(manager, RENAMES_DOWN)...

Automation Testing Tutorials

Learn to execute automation testing from scratch with LambdaTest Learning Hub. Right from setting up the prerequisites to run your first automation test, to following best practices and diving deeper into advanced test scenarios. LambdaTest Learning Hubs compile a list of step-by-step guides to help you be proficient with different test automation frameworks i.e. Selenium, Cypress, TestNG etc.