rdb.go

package db

import (
	"fmt"
	"io/ioutil"
	"os"

	"github.com/cheggaaa/pb"
	"github.com/jinzhu/gorm"
	"github.com/knqyf263/go-cpe/common"
	"github.com/knqyf263/go-cpe/naming"
	c "github.com/kotakanbe/go-cve-dictionary/config"
	log "github.com/kotakanbe/go-cve-dictionary/log"
	"github.com/kotakanbe/go-cve-dictionary/models"
	sqlite3 "github.com/mattn/go-sqlite3"

	// Required MySQL.  See http://jinzhu.me/gorm/database.html#connecting-to-a-database
	_ "github.com/jinzhu/gorm/dialects/mysql"
	_ "github.com/jinzhu/gorm/dialects/postgres"

	// Required SQLite3.
	_ "github.com/jinzhu/gorm/dialects/sqlite"
)

// Supported DB dialects.
const (
	dialectSqlite3    = "sqlite3"
	dialectMysql      = "mysql"
	dialectPostgreSQL = "postgres"
)

// RDBDriver is Driver for RDB
type RDBDriver struct {
	name string
	conn *gorm.DB
}

// Name return db name
func (r *RDBDriver) Name() string {
	return r.name
}

// NewRDB return RDB driver
func NewRDB(dbType, dbpath string, debugSQL bool) (driver *RDBDriver, locked bool, err error) {
	driver = &RDBDriver{
		name: dbType,
	}

	log.Debugf("Opening DB (%s).", driver.Name())
	if locked, err = driver.OpenDB(dbType, dbpath, debugSQL); err != nil {
		return nil, locked, err
	}

	log.Debugf("Migrating DB (%s).", driver.Name())
	if err := driver.MigrateDB(); err != nil {
		return nil, false, err
	}
	return driver, false, nil
}

// OpenDB opens Database
func (r *RDBDriver) OpenDB(dbType, dbPath string, debugSQL bool) (locked bool, err error) {
	r.conn, err = gorm.Open(dbType, dbPath)
	if err != nil {
		if r.name == dialectSqlite3 {
			switch err.(sqlite3.Error).Code {
			case sqlite3.ErrLocked, sqlite3.ErrBusy:
				return true, err
			}
		}
		return false, fmt.Errorf("Failed to open DB. dbtype: %s, dbpath: %s, err: %s",
			dbType, dbPath, err)
	}
	if err := r.conn.LogMode(debugSQL).Error; err != nil {
		return false, err
	}
	r.conn.LogMode(debugSQL)
	return false, nil
}

// MigrateDB migrates Database
func (r *RDBDriver) MigrateDB() error {
	if err := r.conn.AutoMigrate(
		&models.FeedMeta{},
		&models.CveDetail{},
		&models.NvdXML{},
		&models.NvdJSON{},
		&models.Jvn{},
		&models.Reference{},
		&models.Cpe{},
		&models.EnvCpe{},
		&models.Cwe{},
		&models.Affect{},
		&models.Cvss3{},
		&models.Cvss2{},
		&models.Cvss2Extra{},
		&models.Description{},
	).Error; err != nil {
		return fmt.Errorf("Failed to migrate. err: %s", err)
	}

	errs := []error{}

	// CveID
	errs = append(errs, r.conn.Model(&models.CveDetail{}).
		AddIndex("idx_cve_detail_cveid", "cve_id").Error)
	errs = append(errs, r.conn.Model(&models.NvdXML{}).
		AddIndex("idx_nvd_xmls_cveid", "cve_id").Error)
	errs = append(errs, r.conn.Model(&models.NvdJSON{}).
		AddIndex("idx_nvd_jsons_cveid", "cve_id").Error)
	errs = append(errs, r.conn.Model(&models.Jvn{}).
		AddIndex("idx_jvns_cveid", "cve_id").Error)

	// CveDetailID
	errs = append(errs, r.conn.Model(&models.NvdXML{}).
		AddIndex("idx_nvd_xmls_cve_detail_id", "cve_detail_id").Error)
	errs = append(errs, r.conn.Model(&models.NvdJSON{}).
		AddIndex("idx_nvd_jsons_cve_detail_id", "cve_detail_id").Error)
	errs = append(errs, r.conn.Model(&models.Jvn{}).
		AddIndex("idx_jvns_cve_detail_id", "cve_detail_id").Error)

	// references
	errs = append(errs, r.conn.Model(&models.Reference{}).
		AddIndex("idx_references_nvd_xml_id", "nvd_xml_id").Error)
	errs = append(errs, r.conn.Model(&models.Reference{}).
		AddIndex("idx_references_jvn_id", "jvn_id").Error)
	errs = append(errs, r.conn.Model(&models.Reference{}).
		AddIndex("idx_references_nvd_json_id", "nvd_json_id").Error)

	// cpes
	errs = append(errs, r.conn.Model(&models.Cpe{}).
		AddIndex("idx_cpes_jvn_id", "jvn_id").Error)
	errs = append(errs, r.conn.Model(&models.Cpe{}).
		AddIndex("idx_cpes_nvd_xml_id", "nvd_xml_id").Error)
	errs = append(errs, r.conn.Model(&models.Cpe{}).
		AddIndex("idx_cpes_nvd_json_id", "nvd_json_id").Error)
	errs = append(errs, r.conn.Model(&models.Cpe{}).
		AddIndex("idx_cpes_uri", "uri").Error)
	errs = append(errs, r.conn.Model(&models.Cpe{}).
		AddIndex("idx_cpes_formatted_string", "formatted_string").Error)
	errs = append(errs, r.conn.Model(&models.Cpe{}).
		AddIndex("idx_cpes_part", "part").Error)
	errs = append(errs, r.conn.Model(&models.Cpe{}).
		AddIndex("idx_cpes_vendor", "vendor").Error)
	errs = append(errs, r.conn.Model(&models.Cpe{}).
		AddIndex("idx_cpes_product", "product").Error)

	// envcpes
	errs = append(errs, r.conn.Model(&models.EnvCpe{}).
		AddIndex("idx_envcpes_cpe_id", "cpe_id").Error)
	errs = append(errs, r.conn.Model(&models.EnvCpe{}).
		AddIndex("idx_envcpes_uri", "uri").Error)
	errs = append(errs, r.conn.Model(&models.EnvCpe{}).
		AddIndex("idx_envcpes_formatted_string", "formatted_string").Error)

	// Cwes
	errs = append(errs, r.conn.Model(&models.Cwe{}).
		AddIndex("idx_cwes_nvd_xml_id", "nvd_xml_id").Error)
	errs = append(errs, r.conn.Model(&models.Cwe{}).
		AddIndex("idx_cwes_jvn_id", "jvn_id").Error)
	errs = append(errs, r.conn.Model(&models.Cwe{}).
		AddIndex("idx_cwes_nvd_json_id", "nvd_json_id").Error)

	// Affects
	//TODO add index to vendor, product if needed
	errs = append(errs, r.conn.Model(&models.Affect{}).
		AddIndex("idx_affects_nvd_json_id", "nvd_json_id").Error)

	// Cvss3
	errs = append(errs, r.conn.Model(&models.Cvss3{}).
		AddIndex("idx_cvss3_nvd_json_id", "nvd_json_id").Error)
	errs = append(errs, r.conn.Model(&models.Cvss3{}).
		AddIndex("idx_cvss3_jvn_id", "jvn_id").Error)

	// Cvss2
	errs = append(errs, r.conn.Model(&models.Cvss2{}).
		AddIndex("idx_cvsss2_nvd_xml_id", "nvd_xml_id").Error)
	errs = append(errs, r.conn.Model(&models.Cvss2{}).
		AddIndex("idx_cvss2_jvn_id", "jvn_id").Error)

	// Cvss2Extra
	errs = append(errs, r.conn.Model(&models.Cvss2Extra{}).
		AddIndex("idx_cvsss2_extra_nvd_json_id", "nvd_json_id").Error)

	// Description
	errs = append(errs, r.conn.Model(&models.Description{}).
		AddIndex("idx_descriptions_nvd_json_id", "nvd_json_id").Error)

	for _, e := range errs {
		if e != nil {
			return fmt.Errorf("Failed to create index. err: %s", e)
		}
	}

	return nil
}

// Get Select Cve information from DB.
func (r *RDBDriver) Get(cveID string) (*models.CveDetail, error) {
	c := models.CveDetail{}
	err := r.conn.Where(&models.CveDetail{CveID: cveID}).First(&c).Error
	if err != nil && err != gorm.ErrRecordNotFound {
		return nil, err
	}

	if c.ID == 0 {
		return &models.CveDetail{}, nil
	}

	// JVN
	jvn := models.Jvn{}
	err = r.conn.Model(&c).Related(&jvn, "Jvn").Error
	if err != nil && err != gorm.ErrRecordNotFound {
		return nil, err
	}
	if jvn.ID == 0 {
		c.Jvn = nil
	} else {
		c.Jvn = &jvn
	}
	if jvn.CveDetailID != 0 && jvn.ID != 0 {
		jvnRefs := []models.Reference{}
		err = r.conn.Model(&jvn).Related(&jvnRefs, "References").Error
		if err != nil && err != gorm.ErrRecordNotFound {
			return nil, err
		}
		c.Jvn.References = jvnRefs

		cvss2 := models.Cvss2{}
		err = r.conn.Model(&jvn).Related(&cvss2, "Cvss2").Error
		if err != nil && err != gorm.ErrRecordNotFound {
			return nil, err
		}
		c.Jvn.Cvss2 = cvss2

		cvss3 := models.Cvss3{}
		err = r.conn.Model(&jvn).Related(&cvss3, "Cvss3").Error
		if err != nil && err != gorm.ErrRecordNotFound {
			return nil, err
		}
		c.Jvn.Cvss3 = cvss3

		// TODO commentout because JSON response size will be big. so Uncomment if needed.
		//  jvnCpes := []models.Cpe{}
		//  conn.Model(&jvn).Related(&jvnCpes, "Cpes")
		//  c.Jvn.Cpes = jvnCpes
	}

	// NVD JSON
	json := models.NvdJSON{}
	err = r.conn.Model(&c).Related(&json, "NvdJSON").Error
	if err != nil && err != gorm.ErrRecordNotFound {
		return nil, err
	}
	if json.ID == 0 {
		c.NvdJSON = nil
	} else {
		c.NvdJSON = &json
	}
	if json.CveDetailID != 0 && json.ID != 0 {
		descs := []models.Description{}
		err = r.conn.Model(&json).Related(&descs, "Descriptions").Error
		if err != nil && err != gorm.ErrRecordNotFound {
			return nil, err
		}
		c.NvdJSON.Descriptions = descs

		cvss2 := models.Cvss2Extra{}
		err = r.conn.Model(&json).Related(&cvss2, "Cvss2").Error
		if err != nil && err != gorm.ErrRecordNotFound {
			return nil, err
		}
		c.NvdJSON.Cvss2 = cvss2

		cvss3 := models.Cvss3{}
		err = r.conn.Model(&json).Related(&cvss3, "Cvss3").Error
		if err != nil && err != gorm.ErrRecordNotFound {
			return nil, err
		}
		c.NvdJSON.Cvss3 = cvss3

		cwes := []models.Cwe{}
		err = r.conn.Model(&json).Related(&cwes, "Cwes").Error
		if err != nil && err != gorm.ErrRecordNotFound {
			return nil, err
		}
		c.NvdJSON.Cwes = cwes

		affects := []models.Affect{}
		err = r.conn.Model(&json).Related(&affects, "Affects").Error
		if err != nil && err != gorm.ErrRecordNotFound {
			return nil, err
		}
		c.NvdJSON.Affects = affects

		refs := []models.Reference{}
		err = r.conn.Model(&json).Related(&refs, "References").Error
		if err != nil && err != gorm.ErrRecordNotFound {
			return nil, err
		}
		c.NvdJSON.References = refs

		cpes := []models.Cpe{}
		err = r.conn.Model(&json).Related(&cpes, "Cpes").Error
		if err != nil && err != gorm.ErrRecordNotFound {
			return nil, err
		}
		for i, cpe := range cpes {
			envCpes := []models.EnvCpe{}
			err = r.conn.Model(&cpe).Related(&envCpes, "EnvCpes").Error
			if err != nil && err != gorm.ErrRecordNotFound {
				return nil, err
			}
			cpes[i].EnvCpes = envCpes
		}
		c.NvdJSON.Cpes = cpes
	}

	if json.ID != 0 {
		c.NvdXML = nil
		return &c, nil
	}

	// NVD
	nvd := models.NvdXML{}
	err = r.conn.Model(&c).Related(&nvd, "NvdXML").Error
	if err != nil && err != gorm.ErrRecordNotFound {
		return nil, err
	}
	if nvd.ID == 0 {
		c.NvdXML = nil
	} else {
		c.NvdXML = &nvd
	}
	if nvd.CveDetailID != 0 && nvd.ID != 0 {
		nvdRefs := []models.Reference{}
		err = r.conn.Model(&nvd).Related(&nvdRefs, "References").Error
		if err != nil && err != gorm.ErrRecordNotFound {
			return nil, err
		}
		c.NvdXML.References = nvdRefs

		cvss2 := models.Cvss2{}
		err = r.conn.Model(&nvd).Related(&cvss2, "Cvss2").Error
		if err != nil && err != gorm.ErrRecordNotFound {
			return nil, err
		}
		c.NvdXML.Cvss2 = cvss2

		cwes := []models.Cwe{}
		err = r.conn.Model(&nvd).Related(&cwes, "Cwes").Error
		if err != nil && err != gorm.ErrRecordNotFound {
			return nil, err
		}
		c.NvdXML.Cwes = cwes

		// TODO commentout because JSON response size will be big. so Uncomment if needed.
		//  nvdCpes := []models.Cpe{}
		//  conn.Model(&nvd).Related(&nvdCpes, "Cpes")
		//  c.Nvd.Cpes = nvdCpes
	}
	return &c, nil
}

// GetMulti Select Cves information from DB.
func (r *RDBDriver) GetMulti(cveIDs []string) (map[string]models.CveDetail, error) {
	cveDetails := map[string]models.CveDetail{}
	for _, cveID := range cveIDs {
		cve, err := r.Get(cveID)
		if err != nil {
			return nil, err
		}
		cveDetails[cveID] = *cve
	}
	return cveDetails, nil
}

// CloseDB close Database
func (r *RDBDriver) CloseDB() (err error) {
	if err = r.conn.Close(); err != nil {
		log.Errorf("Failed to close DB. Type: %s. err: %s", r.name, err)
		return
	}
	return
}

// GetByCpeURI Select Cve information from DB.
func (r *RDBDriver) GetByCpeURI(uri string) ([]models.CveDetail, error) {
	// parse wfn, get vendor, product
	specified, err := naming.UnbindURI(uri)
	if err != nil {
		return nil, err
	}
	// sleect from cpe by vendor, product
	cpes := []models.Cpe{}
	err = r.conn.Where(&models.Cpe{
		CpeBase: models.CpeBase{
			CpeWFN: models.CpeWFN{
				Vendor:  fmt.Sprintf("%s", specified.Get(common.AttributeVendor)),
				Product: fmt.Sprintf("%s", specified.Get(common.AttributeProduct)),
			},
		}}).Find(&cpes).Error
	if err != nil && err != gorm.ErrRecordNotFound {
		return nil, err
	}

	log.Debugf("specified: %s", uri)
	filtered := []models.Cpe{}
	for _, cpe := range cpes {
		match, err := match(specified, cpe)
		if err != nil {
			log.Warnf("Failed to compare the version:%s %s %#v",
				err, uri, cpe)
			// continue matching
			continue
		}
		if match {
			filtered = append(filtered, cpe)
		}
	}

	idDetail := map[uint]models.CveDetail{}
	for _, cpe := range filtered {
		var cveDetailID uint
		if cpe.JvnID != 0 {
			jvn := models.Jvn{}
			err = r.conn.Select("cve_detail_id").Where("ID = ?", cpe.JvnID).First(&jvn).Error
			if err != nil && err != gorm.ErrRecordNotFound {
				return nil, err
			}
			cveDetailID = jvn.CveDetailID
		} else if cpe.NvdXMLID != 0 {
			nvd := models.NvdXML{}
			err = r.conn.Select("cve_detail_id").Where("ID = ?", cpe.NvdXMLID).First(&nvd).Error
			if err != nil && err != gorm.ErrRecordNotFound {
				return nil, err
			}
			cveDetailID = nvd.CveDetailID
		} else if cpe.NvdJSONID != 0 {
			json := models.NvdJSON{}
			err = r.conn.Select("cve_detail_id").Where("ID = ?", cpe.NvdJSONID).First(&json).Error
			if err != nil && err != gorm.ErrRecordNotFound {
				return nil, err
			}
			cveDetailID = json.CveDetailID
		}

		if _, ok := idDetail[cveDetailID]; ok {
			continue
		}

		cveDetail := models.CveDetail{}
		err = r.conn.Select("cve_id").Where("ID = ?", cveDetailID).First(&cveDetail).Error
		if err != nil && err != gorm.ErrRecordNotFound {
			return nil, err
		}
		cveID := cveDetail.CveID
		detail, err := r.Get(cveID)
		if err != nil {
			return nil, err
		}
		idDetail[cveDetailID] = *detail
	}

	details := []models.CveDetail{}
	for _, d := range idDetail {
		details = append(details, d)
		log.Debugf("%s", d.CveID)
	}
	return details, nil
}

// InsertJvn inserts Cve Information into DB
func (r *RDBDriver) InsertJvn(cves []models.CveDetail) error {
	log.Infof("Inserting fetched CVEs...")
	bar := pb.New(len(cves))
	if c.Conf.Quiet {
		bar.SetWriter(ioutil.Discard)
	} else {
		bar.SetWriter(os.Stderr)
	}
	bar.Start()

	var refreshedJvns []string
	for chunked := range chunkSlice(cves, 10) {
		tx := r.conn.Begin()
		if tx.Error != nil {
			return tx.Error
		}
		for _, c := range chunked {
			bar.Increment()

			// select old record.
			old := models.CveDetail{}
			result := tx.Where(&models.CveDetail{CveID: c.CveID}).First(&old)
			if result.Error != nil && result.Error != gorm.ErrRecordNotFound {
				return rollback(tx, result.Error)
			}
			if result.RecordNotFound() || old.ID == 0 {
				if err := tx.Create(&c).Error; err != nil {
					return rollback(tx, err)
				}
				refreshedJvns = append(refreshedJvns, c.CveID)
				continue
			}

			if !result.RecordNotFound() {
				// select Jvn from db
				jvn := models.Jvn{}
				err := tx.Model(&old).Related(&jvn, "Jvn").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}

				if jvn.CveDetailID == 0 {
					c.Jvn.CveDetailID = old.ID
					if err := tx.Create(&c.Jvn).Error; err != nil {
						return rollback(tx, err)
					}
					refreshedJvns = append(refreshedJvns, c.CveID)
					continue
				}

				// Refresh JVN Record.

				// skip if the record has already been in DB and not modified.
				if jvn.LastModifiedDate.Equal(c.Jvn.LastModifiedDate) ||
					jvn.LastModifiedDate.After(c.Jvn.LastModifiedDate) {
					//  log.Debugf("Not modified. old: %s", old.CveID)
					continue
				} else {
					log.Debugf("Newer record found. CveID: %s, old: %s, new: %s",
						c.CveID, jvn.LastModifiedDate, c.Jvn.LastModifiedDate)
				}

				// Delte old References
				refs := []models.Reference{}
				err = tx.Model(&jvn).Related(&refs, "References").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}
				for _, r := range refs {
					if err := tx.Unscoped().Delete(r).Error; err != nil {
						return rollback(tx, err)
					}
				}

				// Delete Cvss2
				cvss2 := []models.Cvss2{}
				err = tx.Model(&jvn).Related(&cvss2, "Cvss2").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}
				for _, cvss := range cvss2 {
					if err := tx.Unscoped().Delete(cvss).Error; err != nil {
						return rollback(tx, err)
					}
				}

				// Delete Cvss3
				cvss3 := []models.Cvss3{}
				err = tx.Model(&jvn).Related(&cvss3, "Cvss3").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}
				for _, cvss := range cvss3 {
					if err := tx.Unscoped().Delete(cvss).Error; err != nil {
						return rollback(tx, err)
					}
				}

				// Delete old Cpes
				cpes := []models.Cpe{}
				err = tx.Model(&jvn).Related(&cpes, "Cpes").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}
				for _, cpe := range cpes {
					if err := tx.Unscoped().Delete(cpe).Error; err != nil {
						return rollback(tx, err)
					}
				}

				// Delete old Jvn
				if err := tx.Unscoped().Delete(&jvn).Error; err != nil {
					return rollback(tx, err)
				}

				// Insert Jvn
				c.Jvn.CveDetailID = old.ID
				if err := tx.Create(&c.Jvn).Error; err != nil {
					return rollback(tx, err)
				}
				refreshedJvns = append(refreshedJvns, c.CveID)
			}
		}
		if err := tx.Commit().Error; err != nil {
			return rollback(tx, err)
		}
	}
	bar.Finish()
	log.Infof("Refreshed %d Jvns.", len(refreshedJvns))
	log.Debugf("%v", refreshedJvns)
	return nil
}

func rollback(tx *gorm.DB, err error) error {
	if err := tx.Rollback().Error; err != nil {
		return err
	}
	return err
}

// CountNvd count nvd table
func (r *RDBDriver) CountNvd() (int, error) {
	var count int
	err := r.conn.Model(&models.NvdXML{}).Count(&count).Error
	if err != nil && err != gorm.ErrRecordNotFound {
		return 0, err
	} else if count > 0 {
		return count, nil
	}

	err = r.conn.Model(&models.NvdJSON{}).Count(&count).Error
	if err != nil && err != gorm.ErrRecordNotFound {
		return 0, err
	}
	return count, nil
}

// InsertNvdXML inserts CveInformation into DB
func (r *RDBDriver) InsertNvdXML(cves []models.CveDetail) error {
	log.Infof("Inserting CVEs...")
	bar := pb.New(len(cves))
	if c.Conf.Quiet {
		bar.SetWriter(ioutil.Discard)
	} else {
		bar.SetWriter(os.Stderr)
	}
	bar.Start()

	var refreshedNvds []string
	for chunked := range chunkSlice(cves, 10) {
		tx := r.conn.Begin()
		if tx.Error != nil {
			return tx.Error
		}
		for _, c := range chunked {
			bar.Increment()

			// select old record.
			old := models.CveDetail{}
			result := tx.Where(&models.CveDetail{CveID: c.CveID}).First(&old)
			if result.Error != nil && result.Error != gorm.ErrRecordNotFound {
				return rollback(tx, result.Error)
			}
			if result.RecordNotFound() || old.ID == 0 {
				if err := tx.Create(&c).Error; err != nil {
					return rollback(tx, err)
				}
				refreshedNvds = append(refreshedNvds, c.CveID)
				continue
			}

			if !result.RecordNotFound() {
				// select Nvd from db
				nvd := models.NvdXML{}
				err := tx.Model(&old).Related(&nvd, "Nvd").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}

				if nvd.CveDetailID == 0 {
					c.NvdXML.CveDetailID = old.ID
					if err := tx.Create(&c.NvdXML).Error; err != nil {
						return rollback(tx, err)
					}
					refreshedNvds = append(refreshedNvds, c.CveID)
					continue
				}

				// Refresh to new NVD Record.

				// skip if the record has already been in DB and not modified.
				if nvd.LastModifiedDate.Equal(c.NvdXML.LastModifiedDate) ||
					nvd.LastModifiedDate.After(c.NvdXML.LastModifiedDate) {
					//  log.Debugf("Not modified. old: %s", old.CveID)
					continue
				} else {
					log.Debugf("newer Record found. CveID: %s, old: %s, new: %s",
						c.CveID, nvd.LastModifiedDate, c.NvdXML.LastModifiedDate)
				}

				// Delte old References
				refs := []models.Reference{}
				err = tx.Model(&nvd).Related(&refs, "References").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}
				for _, r := range refs {
					if err := tx.Unscoped().Delete(r).Error; err != nil {
						return rollback(tx, err)
					}
				}

				// Delte old CWE
				cwes := []models.Cwe{}
				err = tx.Model(&nvd).Related(&cwes, "Cwes").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}
				for _, cwe := range cwes {
					if err := tx.Unscoped().Delete(cwe).Error; err != nil {
						return rollback(tx, err)
					}
				}

				// uncomment if you needed
				// Delete old Cpes
				// cpes := []models.Cpe{}
				// err = tx.Model(&nvd).Related(&cpes, "Cpes").Error
				// if err != nil && err != gorm.ErrRecordNotFound {
				// return rollback(tx, err)
				// }
				// for _, cpe := range cpes {
				// if err := tx.Unscoped().Delete(cpe).Error; err != nil {
				// return rollback(tx, err)
				// }
				// }

				// Delete Cvss2
				cvss2 := []models.Cvss2{}
				err = tx.Model(&nvd).Related(&cvss2, "Cvss2").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}
				for _, cvss := range cvss2 {
					if err := tx.Unscoped().Delete(cvss).Error; err != nil {
						return rollback(tx, err)
					}
				}

				// Delete old Nvd
				if err := tx.Unscoped().Delete(&nvd).Error; err != nil {
					return rollback(tx, err)
				}

				// Insert Nvd
				c.NvdXML.CveDetailID = old.ID
				if err := tx.Create(&c.NvdXML).Error; err != nil {
					return rollback(tx, err)
				}
				refreshedNvds = append(refreshedNvds, c.CveID)
			}
		}
		if err := tx.Commit().Error; err != nil {
			return err
		}
	}
	bar.Finish()

	log.Infof("Refreshed %d Nvds.", len(refreshedNvds))
	//  log.Debugf("%v", refreshedNvds)
	return nil
}

// InsertNvdJSON Cve information from DB.
func (r *RDBDriver) InsertNvdJSON(cves []models.CveDetail) (err error) {
	log.Infof("Inserting fetched CVEs...")
	bar := pb.New(len(cves))
	if c.Conf.Quiet {
		bar.SetWriter(ioutil.Discard)
	} else {
		bar.SetWriter(os.Stderr)
	}
	bar.Start()

	var refreshedNvds []string
	for chunked := range chunkSlice(cves, 10) {
		tx := r.conn.Begin()
		if tx.Error != nil {
			return tx.Error
		}
		for _, c := range chunked {
			bar.Increment()

			// select old record.
			old := models.CveDetail{}
			result := tx.Where(&models.CveDetail{CveID: c.CveID}).First(&old)
			if result.Error != nil && result.Error != gorm.ErrRecordNotFound {
				return rollback(tx, result.Error)
			}
			if result.RecordNotFound() || old.ID == 0 {
				log.Debugf("CveDetail is not found: %s", c.CveID)
				if err := tx.Create(&c).Error; err != nil {
					return rollback(tx, err)
				}
				refreshedNvds = append(refreshedNvds, c.CveID)
				continue
			}

			if !result.RecordNotFound() {
				// select NvdJSON from db
				json := models.NvdJSON{}
				err = tx.Model(&old).Related(&json, "NvdJSON").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}

				// If the NVD JSON hasn't been inserted yet, insert the NVD JSON newly and assosiate it with existing CveDetail.
				if json.CveDetailID == 0 {
					log.Debugf("CveDetail found but no NVD JSON: %s", c.CveID)
					c.NvdJSON.CveDetailID = old.ID
					if err := tx.Create(&c.NvdJSON).Error; err != nil {
						return rollback(tx, err)
					}
					refreshedNvds = append(refreshedNvds, c.CveID)
					continue
				}

				// If the NVD JSON has already been inserted, Refresh to new NVD JSON Record.

				// skip if the record has already been in DB and not modified.
				if json.LastModifiedDate.Equal(c.NvdJSON.LastModifiedDate) ||
					json.LastModifiedDate.After(c.NvdJSON.LastModifiedDate) {
					log.Debugf("Not modified. old: %s", old.CveID)
					continue
				} else {
					log.Debugf("newer Record found. CveID: %s, old: %s, new: %s",
						c.CveID, json.LastModifiedDate, c.NvdJSON.LastModifiedDate)
				}

				// Delte old Descriptions
				descs := []models.Description{}
				err = tx.Model(&json).Related(&descs, "Descriptions").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}
				for _, desc := range descs {
					if err := tx.Unscoped().Delete(desc).Error; err != nil {
						return rollback(tx, err)
					}
				}

				// Delete Cvss2
				cvss2 := []models.Cvss2Extra{}
				err = tx.Model(&json).Related(&cvss2, "Cvss2").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}
				for _, cvss := range cvss2 {
					if err := tx.Unscoped().Delete(cvss).Error; err != nil {
						return rollback(tx, err)
					}
				}

				// Delete Cvss3
				cvss3 := []models.Cvss3{}
				err = tx.Model(&json).Related(&cvss3, "Cvss3").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}
				for _, cvss := range cvss3 {
					if err := tx.Unscoped().Delete(cvss).Error; err != nil {
						return rollback(tx, err)
					}
				}

				// Delte old CWE
				cwes := []models.Cwe{}
				err = tx.Model(&json).Related(&cwes, "Cwes").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}
				for _, cwe := range cwes {
					if err := tx.Unscoped().Delete(cwe).Error; err != nil {
						return rollback(tx, err)
					}
				}

				// Delete old Cpes and EnvEpes
				cpes := []models.Cpe{}
				err = tx.Model(&json).Related(&cpes, "Cpes").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}
				for _, cpe := range cpes {
					envs := []models.EnvCpe{}
					err = tx.Model(&cpe).Related(&envs, "EnvCpes").Error
					if err != nil && err != gorm.ErrRecordNotFound {
						return rollback(tx, err)
					}
					for _, env := range envs {
						if err := tx.Unscoped().Delete(env).Error; err != nil {
							return rollback(tx, err)
						}
					}

					if err := tx.Unscoped().Delete(cpe).Error; err != nil {
						return rollback(tx, err)
					}
				}

				// Delte old Affects
				affects := []models.Affect{}
				err = tx.Model(&json).Related(&affects, "Affects").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}
				for _, r := range affects {
					if err := tx.Unscoped().Delete(r).Error; err != nil {
						return rollback(tx, err)
					}
				}

				// Delte old References
				refs := []models.Reference{}
				err = tx.Model(&json).Related(&refs, "References").Error
				if err != nil && err != gorm.ErrRecordNotFound {
					return rollback(tx, err)
				}
				for _, r := range refs {
					if err := tx.Unscoped().Delete(r).Error; err != nil {
						return rollback(tx, err)
					}
				}

				// Delete old NvdJSON
				if err := tx.Unscoped().Delete(&json).Error; err != nil {
					return rollback(tx, err)
				}

				// Insert Nvd JSON
				c.NvdJSON.CveDetailID = old.ID
				if err := tx.Create(&c.NvdJSON).Error; err != nil {
					return rollback(tx, err)
				}
				refreshedNvds = append(refreshedNvds, c.CveID)
			}
		}
		if err := tx.Commit().Error; err != nil {
			return rollback(tx, err)
		}
	}
	bar.Finish()

	log.Infof("Refreshed %d NvdJSONs.", len(refreshedNvds))
	//  log.Debugf("%v", refreshedNvds)
	return nil
}

// GetFetchedFeedMeta selects fetchmeta of the year
func (r *RDBDriver) GetFetchedFeedMeta(url string) (*models.FeedMeta, error) {
	meta := models.FeedMeta{}
	m := &models.FeedMeta{
		URL: url,
	}
	err := r.conn.Where(m).First(&meta).Error
	if err != nil && err != gorm.ErrRecordNotFound {
		return nil, err
	}
	return &meta, nil
}

// UpsertFeedHash selects sha1 in metafile of the year
func (r *RDBDriver) UpsertFeedHash(mm models.FeedMeta) error {
	meta := models.FeedMeta{}
	m := &models.FeedMeta{
		URL: mm.URL,
	}
	err := r.conn.Where(m).First(&meta).Error
	if err != nil && err != gorm.ErrRecordNotFound {
		return err
	}

	tx := r.conn.Begin()
	if tx.Error != nil {
		return tx.Error
	}

	if err == gorm.ErrRecordNotFound {
		m.Hash = mm.Hash
		m.LastModifiedDate = mm.LastModifiedDate
		if err := tx.Create(m).Error; err != nil {
			return rollback(tx, err)
		}
	} else {
		meta.Hash = mm.Hash
		m.LastModifiedDate = mm.LastModifiedDate
		if err := tx.Save(&meta).Error; err != nil {
			return rollback(tx, err)
		}
	}

	if err := tx.Commit().Error; err != nil {
		return rollback(tx, err)
	}
	return nil
}

// GetFetchedFeedMetas selects a list of FeedMeta
func (r *RDBDriver) GetFetchedFeedMetas() ([]models.FeedMeta, error) {
	metas := []models.FeedMeta{}
	err := r.conn.Find(&metas).Error
	if err != nil && err != gorm.ErrRecordNotFound {
		return nil, err
	}
	return metas, nil
}