DB structure
Data model
Tables
Package:
- PK name=(distro,pkgname,epoch,version,release) (eg:. slc4X,grep,0,2.5.1,31)
- source_repo - srpm name + md5sum of srpm file - md5sum not filled yet !
Possible target repositories:
- [slc-base] eg.
/afs/cern.ch/project/linux/cern/slc4X/i386/yum/os
- [slc-test] eg.
/afs/cern.ch/project/linux/cern/slc4X/i386/yum/testing
- [slc-extra] eg.
/afs/cern.ch/project/linux/cern/slc4X/i386/yum/extras
- [slc-update] eg.
/afs/cern.ch/project/linux/cern/slc4X/i386/yum/updates
- [slc-cernonly] eg. = eg.
/afs/cern.ch/project/linux/onlycern/slc4X/i386/yum/cernonly
Possible source repositories (base dirs for SRPMs):
- [slc-base] eg.
/afs/cern.ch/project/linux/cern/slc4X/SRPMS
- [slc-test] eg.
/afs/cern.ch/project/linux/cern/slc4X/updates/testing/SRPMS
- [slc-extra] eg.
/afs/cern.ch/project/linux/cern/slc4X/extras/SRPMS
- [slc-update] eg.
/afs/cern.ch/project/linux/cern/slc4X/updates/SRPMS
- [slc-cernonly] eg.
/afs/cern.ch/project/linux/onlycern/slc4X/SRPMS
'CREATE TABLE packages (' \
'distro VARCHAR(10), ' \
'name VARCHAR(200), ' \
'epoch VARCHAR(10), ' \
'ver VARCHAR(10), ' \
'rel VARCHAR(10), ' \
'srpm_name VARCHAR(200), ' \
'srpm_md5sum VARCHAR(200), ' \
'PRIMARY KEY (distro,name,epoch,ver,rel)' \
')'
Target repositories:
- PK name (eg. base, test, extra, update, cernonly)
- FK (Package)dnevr
- yum directory (eg.
/afs/cern.ch/project/linux/cern/slc4X/i386/yum/os
)
- yum name (eg [slc-base])
'CREATE TABLE target_repositories (' \
'id INTEGER PRIMARY KEY AUTOINCREMENT, ' \
'name VARCHAR(200), ' \
'pkg_distro VARCHAR(10), ' \
'pkg_name VARCHAR(200), ' \
'pkg_epoch VARCHAR(10), ' \
'pkg_ver VARCHAR(10), ' \
'pkg_rel VARCHAR(10), ' \
'yum_directory VARCHAR(200), ' \
'yum_name VARCHAR(50) ' \
')'
Advisories (more info on why a certain Package was added):
- FK (Package)dnevr
- type (bugfix|security|addition)
- severity (minor|important|critical)
- link to actual advisory
- link(s) to external info (CVE, BugTraq, Bugzilla, IssueTracker..)
State (current job states and historical ones):
- PK timestamp
- FK (Package)dnevr
- state (incoming,...)
'CREATE TABLE state_history (' \
'timestamp BIGINT PRIMARY KEY, ' \
'pkg_distro VARCHAR(10), ' \
'pkg_name VARCHAR(200), ' \
'pkg_epoch VARCHAR(10), ' \
'pkg_ver VARCHAR(10), ' \
'pkg_rel VARCHAR(10), ' \
'state VARCHAR(50), ' \
')'
Options (pattern matching by package name - not clear whether this can be in a table or in scripts):
- PK pattern (eg:. \w* , kernel\w* )
- build targets (eg:. "i386"|"x86_64"|"ia64"|"i586"|"amd64"|"i386,x86_64,ia64" ...)
- builder (eg:. "mock"|"rpmbuild")
- builder_options (eg:. "%packager=..."|"%vendor=...")
- chroot (eg:. "/build/mock/slc-4-core")
- who should be notified
- who can change the state of the package
- update policy (keep on hold until manual OK, move to test automatically, move to production automatically, ..)
Arch_job_state (current arch_job(per-architecture job) state):
- PK id
- FK (Package)dnevr
- State (eg:. waiting|downloading|prep|building|uploading|done|deploy)
Job_details (all: historical and current arch_jobs, builder-specific data):
- PK job_id
- FK (Arch_job_state)id
- timestamps (start & stop)
- build_options (needed for building and requeing)
- username (who is running building process)
Filling DB
There is script
/afs/cern.ch/project/linux/dev/newbuildsys/fill.py
, which can fill DB with information based on content od RPM repositories. It took list of packages in base,updates,extras and cernonly repos and fill tables
packages and
target_repositories.
Command:
python fill.py prefill 1.db
will fill sqlite DB in file 1.db
If you want to fetch data also from testing repositories, you can type:
python fill.py prefill 2.db test
You can select list of packages with highest version with commands:
python fill.py list 1.db slc4X
python fill.py list 1.db slc30X
List is printed in format
"%s-%s-%s-%s" % (name,epoch,version,release)
. If no epoch in RPM is provided, then 0 value is printed.
If you want to compare list of packages given by that script with list of packages installed on some machine, you can type:
rpm -qa --qf "%{NAME}-%{EPOCH}-%{VERSION}-%{RELEASE}\n" | sed 's/(none)/0/g' > /tmp/tmp.dat
sort /tmp/tmp.dat > /tmp/rpm.dat
python fill.py list 1.db slc4X > /tmp/tmp.dat
sort /tmp/tmp.dat > /tmp/fill.dat
diff /tmp/rpm.dat /tmp/fill.dat
--
LeszekGrzanka - 09 May 2007