Fink:PackageDatabase:NewSchema

Possible new layout for a distribution/release db. It should replace the existing release table and also the releases.inc file. The code below will go into the schema.sql and releases.sql

create table if not exists distribution (
  dist_id int unsigned not null auto_increment,
  identifier varchar(16) not null,
  description varchar(64) not null default '',
  architecture enum('powerpc', 'i386') not null default 'powerpc',
  priority tinyint unsigned not null default '1',
  active boolean default 1,
  primary key (dist_id)
) collate ascii_general_ci;

create table if not exists distribution_releases (
  rel_id int unsigned not null auto_increment,
  dist_id int unsigned not null references distribution(dist_id),
  type enum('bindist', 'stable', 'unstable') not null,
  version varchar(16) not null,
  priority tinyint unsigned not null default '1',
  active boolean default 1,
  primary key (rel_id)
) collate ascii_general_ci;


insert into distribution (dist_id, identifier, description, architecture, priority, active) values (null, '10.1', '10.1', 'powerpc', 1, 1);
select @last_dist_id := last_insert_id();
insert into distribution_releases (rel_id, dist_id, type, version, priority, active) values (null, @last_dist_id, 'bindist',  '0.4.1', 1, 1);

insert into distribution (dist_id, identifier, description, architecture, priority, active) values (null, '10.2-gcc3.3', '10.2\n(gcc3.3 only)', 'powerpc', 2, 1);
select @last_dist_id := last_insert_id();
insert into distribution_releases (rel_id, dist_id, type, version, priority, active) values (null, @last_dist_id, 'unstable', 'current', 3, 1);
insert into distribution_releases (rel_id, dist_id, type, version, priority, active) values (null, @last_dist_id, 'stable',   'current', 2, 1);
insert into distribution_releases (rel_id, dist_id, type, version, priority, active) values (null, @last_dist_id, 'bindist',  '0.6.4', 1, 1);

insert into distribution (dist_id, identifier, description, architecture, priority, active) values (null, '10.3', '10.3', 'powerpc', 3, 1);
select @last_dist_id := last_insert_id();
insert into distribution_releases (rel_id, dist_id, type, version, priority, active) values (null, @last_dist_id, 'unstable', 'current', 3, 1);
insert into distribution_releases (rel_id, dist_id, type, version, priority, active) values (null, @last_dist_id, 'stable',   'current', 2, 1);
insert into distribution_releases (rel_id, dist_id, type, version, priority, active) values (null, @last_dist_id, 'bindist',  '0.7.2', 1, 1);

insert into distribution (dist_id, identifier, description, architecture, priority, active) values (null, '10.4', '10.4/powerpc', 'powerpc', 4, 1);
select @last_dist_id := last_insert_id();
insert into distribution_releases (rel_id, dist_id, type, version, priority, active) values (null, @last_dist_id, 'unstable', 'current', 3, 1);
insert into distribution_releases (rel_id, dist_id, type, version, priority, active) values (null, @last_dist_id, 'stable',   'current', 2, 1);
insert into distribution_releases (rel_id, dist_id, type, version, priority, active) values (null, @last_dist_id, 'bindist',  '0.8.1', 1, 1);

insert into distribution (dist_id, identifier, description, architecture, priority, active) values (null, '10.4', '10.4/intel', 'i386', 5, 1);
select @last_dist_id := last_insert_id();
insert into distribution_releases (rel_id, dist_id, type, version, priority, active) values (null, @last_dist_id, 'unstable', 'current', 3, 1);
insert into distribution_releases (rel_id, dist_id, type, version, priority, active) values (null, @last_dist_id, 'stable',   'current', 2, 1);
insert into distribution_releases (rel_id, dist_id, type, version, priority, active) values (null, @last_dist_id, 'bindist',  '0.8.1', 1, 1);

And the corresponding package schema:

create table if not exists package (
 pkg_id int unsigned not null auto_increment,
 rel_id int unsigned not null references distribution_releases(rel_id),
 name varchar(64) not null,
 parentname varchar(64),
 version varchar(64) not null,
 revision varchar (16) not null,
 epoch tinyint not null default '0',
 descshort varchar(80) not null default '',
 desclong text,
 descusage text,
 maintainer varchar(255),
 license varchar(64),
 homepage varchar(255),
 section varchar(32) not null,
 infofile varchar(255) not null default '',
 infofilechanged datetime,
 primary key (pkg_id),
 index (name),
 index (section),
 index (rel_id)
) collate ascii_general_ci;