DB structure

Data model

Tables

Source packages:

• PK name=(distro,pkgname,epoch,version,release) (eg:. slc4X,grep,0,2.5.1,31)
• filename - file name of SRPM
• directory - directory in which SRPM is stored
• yum_name - yum-like name of directory with SRPMs

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 source_packages ('                    \
'id INTEGER PRIMARY KEY AUTOINCREMENT, ' \
'distro VARCHAR(10), '                    \
'name VARCHAR(200), '                     \
'epoch VARCHAR(10), '                     \
'ver VARCHAR(10), '                       \
'rel VARCHAR(10), '                       \
'filename VARCHAR(200), '                \
'directory VARCHAR(200), '                \
'yum_name VARCHAR(20), '                \
'md5sum VARCHAR(200), '              \
'UNIQUE (distro,name,epoch,ver,rel)' \
')'

Target packages:

'CREATE TABLE target_packages ('                    \
'id INTEGER PRIMARY KEY AUTOINCREMENT, ' \
'name VARCHAR(200), '                     \
'arch VARCHAR(10), '                     \
'filename VARCHAR(10), '                       \
'fk_src_id INTEGER, '                    \
'UNIQUE (name,arch,filename,fk_src_id)' \
')'

Target repositories:

Possible target repositories (and where yum stores files):

• [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

• name (eg. base, test, extra, update, cernonly)
• distro (eg. SLC4X,SLC30X)
• arch (eg. i386,x86_64,ia64)
• yum directory (eg. /afs/cern.ch/project/linux/cern/slc4X/i386/yum/os)
• yum name (eg [slc-base])

'CREATE TABLE target_packages ('                    \
'id INTEGER PRIMARY KEY AUTOINCREMENT, ' \
'name VARCHAR(200), '                     \
'arch VARCHAR(10), '                     \
'filename VARCHAR(10), '                       \
'fk_src_id INTEGER, '                    \
'UNIQUE (name,arch,filename,fk_src_id)' \
')'

Target repositories - target packages matching:

Needed for noarch and multiarch packages:

'CREATE TABLE target_packages_repositories (' \
'fk_repo_id INTEGER, '                     \
'fk_pkg_id INTEGER, '                     \
'PRIMARY KEY (fk_repo_id,fk_pkg_id)' \
')'

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, '     \
'fk_pkg_id INTEGER, '                     \
'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

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 i386
python fill.py list 1.db slc30X x86_64

-- LeszekGrzanka - 29 May 2007