~launchpad-pqm/launchpad/devel : revision 36

11

- re-evaluate the relationship table names, see if there isn't a better name for each of them

12

- add sample data for the schemas

13

- make sure names are only [a-z][0-9][-.+] and can only start with [a-z]

14

- set DEFAULT's for datestamps (now) and others

14

15

- present the database as a set of Interfaces

15

16

- add Series to products and projects

16

17

CHANGES

17

18

19

v0.95:

20

- move bug priority from CodereleaseBug to SourcepackageBug

21

- remove wontfix since it is now a bug priority ("wontfix")

22

- add name to bugattachment

23

- refactor bug attachments:

24

- don't have a relationship, each attachment on only one bug

25

- allow for revisions to attachments

26

- rename BugRef to BugExternalref and remove bugref field

27

- create a link ProjectBugsystem between Project's and BugSystem's

28

- remove BugMessageSighting, each BugMessage now belongs to one and only one bug

29

- add a nickname (optional unique name) to the Bug table

30

- change the "summary" field of Bug to "title" for consistency

31

- rename some tables:

32

- ReleaseBugStatus -> CodereleaseBug

33

- SourcepackageBugStatus -> SourcepackageBug

34

- ProductBugStatus -> ProductBug

35

- add a createdate to project and product

18

36

v0.94:

19

37

- rename soyuz.sql to launchpad.sql

20

38

- make Schema.extensible DEFAULT false (thanks spiv)

21

39

v0.93:

22

40

- add a manifest to Sourcepackage and Product, for the mutable HEAD manifest

23

- add a manifest to CodeRelease

41

- add a manifest to Coderelease

24

42

- rename includeas to entrytype in ManifestEntry

25

43

- remove "part" from ManifestEntry

26

44

- add hints in Manifest table so sourcerer knows how to name patch branches

37

55

- add iscurrent (boolean) field to the POTFiles table, current POTFiles

38

56

will be displayed in project summary pages.

39

57

- add ChangesetFile, ChangesetFilename and ChangesetFileHash tables

40

- rename Release to CodeRelease (and all dependent tables)

58

- rename Release to Coderelease (and all dependent tables)

41

59

- refactor Processor and ProcessorFamily:

42

60

- the distroarchrelease now has a processorfamily field

43

61

- the binarypackagebuild (deb) now records its processor

61

79

/*

62

80

DESTROY ALL TABLES

63

81

*/

82

DROP TABLE PersonBug_Relationship;

64

83

DROP TABLE SpokenIn CASCADE;

65

84

DROP TABLE Country CASCADE;

66

DROP TABLE BugMessageSighting CASCADE;

67

85

DROP TABLE TranslationEffort_POTFile_Relationship CASCADE;

68

86

DROP TABLE POComment CASCADE;

69

87

DROP TABLE Branch_Relationship CASCADE;

88

DROP TABLE ProjectBugsystem CASCADE;

70

89

DROP TABLE BugSystem CASCADE;

71

90

DROP TABLE BugWatch CASCADE;

72

91

DROP TABLE RosettaPOTranslationSighting CASCADE;

73

92

DROP TABLE BugAttachment CASCADE;

74

DROP TABLE BugAttachment_Relationship CASCADE;

93

DROP TABLE BugattachmentContent CASCADE;

75

94

DROP TABLE License CASCADE;

76

95

DROP TABLE Bug_Relationship CASCADE;

77

96

DROP TABLE BugMessage CASCADE;

78

97

DROP TABLE POTranslationSighting CASCADE;

79

DROP TABLE BugRef CASCADE;

98

DROP TABLE BugExternalref CASCADE;

80

99

DROP TABLE Bug CASCADE;

81

100

DROP TABLE Packaging CASCADE;

82

101

DROP TABLE SourcepackageReleaseFile CASCADE;

83

102

DROP TABLE Sourcepackage_Relationship CASCADE;

84

103

DROP TABLE SourcepackageRelease CASCADE;

85

DROP TABLE CodeReleaseBugStatus CASCADE;

104

DROP TABLE CodereleaseBug CASCADE;

86

105

DROP TABLE SourcepackageLabel CASCADE;

87

DROP TABLE SourcepackageBugStatus CASCADE;

106

DROP TABLE SourcepackageBug CASCADE;

88

107

DROP TABLE Bug_Sourcepackage_Relationship CASCADE;

89

108

DROP TABLE SourcepackageUpload CASCADE;

90

109

DROP TABLE Sourcepackage CASCADE;

108

127

DROP TABLE ArchArchive CASCADE;

109

128

DROP TABLE UpstreamReleaseFile CASCADE;

110

129

DROP TABLE UpstreamRelease CASCADE;

111

DROP TABLE CodeRelease CASCADE;

112

DROP TABLE CodeRelease_Relationship CASCADE;

130

DROP TABLE Coderelease CASCADE;

131

DROP TABLE Coderelease_Relationship CASCADE;

113

132

DROP TABLE POTInheritance CASCADE;

114

133

DROP TABLE POTMsgIDSighting CASCADE;

115

134

DROP TABLE Manifest CASCADE;

538

557

owner integer NOT NULL REFERENCES Person,

539

558

name text NOT NULL UNIQUE,

540

559

title text NOT NULL,

541

description text,

560

description text NOT NULL,

561

createdate timestamp NOT NULL,

542

562

homepage text

543

563

);

544

564

573

593

name text NOT NULL,

574

594

title text NOT NULL,

575

595

description text NOT NULL,

596

createdate timestamp NOT NULL,

576

597

homepage text,

577

598

manifest integer REFERENCES Manifest,

578

599

UNIQUE ( project, name )

757

778

SoyuzFile

758

779

The Soyuz system keeps copies of all the files that are used to make

759

780

up a distribution, such as deb's and tarballs and .dsc files and .spec

760

files and CodeRelease files... these are represented by this table.

781

files and Coderelease files... these are represented by this table.

761

782

*/

762

783

CREATE TABLE SoyuzFile (

763

784

soyuzfile serial PRIMARY KEY,

789

810

790

811

/*

791

812

UpstreamReleaseFile

792

A file from an Upstream CodeRelease. Usually this would be a tarball.

813

A file from an Upstream Coderelease. Usually this would be a tarball.

793

814

*/

794

815

CREATE TABLE UpstreamReleaseFile (

795

816

upstreamrelease integer NOT NULL REFERENCES UpstreamRelease,

951

972

gpgsigningkey integer REFERENCES GPGKey,

952

973

component integer REFERENCES Label,

953

974

section integer REFERENCES Label,

954

priority integer REFERENCES Label,

955

975

shlibdeps text,

956

976

depends text,

957

977

recommends text,

998

1018

This section is devoted to data that tracks upstream and distribution

999

1019

SOURCE PACKAGE releases. So, for example, Apache 2.0.48 is an

1000

1020

UpstreamRelease. Apache 2.0.48-3 is a Debian SourcepackageRelease.

1001

We have data tables for both of those, and the CodeRelease table is

1002

the data that is common to any kind of CodeRelease. This subsystem also

1003

keeps track of the actual files associated with CodeReleases, such as

1021

We have data tables for both of those, and the Coderelease table is

1022

the data that is common to any kind of Coderelease. This subsystem also

1023

keeps track of the actual files associated with Codereleases, such as

1004

1024

tarballs and deb's and .dsc files and changelog files...

1005

1025

*/

1006

1026

1007

1027

1008

1028

1009

1029

/*

1010

CodeRelease

1030

Coderelease

1011

1031

A release of software. Could be an Upstream release or

1012

1032

a SourcepackageRelease.

1013

1033

*/

1014

CREATE TABLE CodeRelease (

1034

CREATE TABLE Coderelease (

1015

1035

coderelease serial PRIMARY KEY,

1016

1036

upstreamrelease integer REFERENCES UpstreamRelease,

1017

1037

sourcepackagerelease integer REFERENCES SourcepackageRelease,

1023

1043

1024

1044

1025

1045

/*

1026

CodeRelease_Relationship

1046

Coderelease_Relationship

1027

1047

Maps the relationships between releases (upstream and

1028

1048

sourcepackage).

1029

1049

*/

1030

CREATE TABLE CodeRelease_Relationship (

1031

src integer NOT NULL REFERENCES CodeRelease,

1032

dst integer NOT NULL REFERENCES CodeRelease,

1050

CREATE TABLE Coderelease_Relationship (

1051

src integer NOT NULL REFERENCES Coderelease,

1052

dst integer NOT NULL REFERENCES Coderelease,

1033

1053

label integer NOT NULL REFERENCES Label,

1034

1054

PRIMARY KEY ( src, dst )

1035

1055

);

1397

1417

CREATE TABLE Bug (

1398

1418

bug serial PRIMARY KEY,

1399

1419

datecreated timestamp NOT NULL,

1400

summary text NOT NULL,

1420

nickname text UNIQUE,

1421

title text NOT NULL,

1401

1422

description text NOT NULL,

1402

1423

owner integer NOT NULL,

1403

1424

duplicateof integer REFERENCES Bug,

1412

1433

1413

1434

1414

1435

/*

1415

CodeReleaseBugStatus

1436

PersonBug_Relationship

1437

The relationship between a person and a bug.

1438

*/

1439

CREATE TABLE PersonBug_Relationship (

1440

person integer NOT NULL REFERENCES Person,

1441

bug integer NOT NULL REFERENCES Bug,

1442

label integer NOT NULL REFERENCES Label

1443

);

1444

1445

1446

1447

/*

1448

CodereleaseBug

1416

1449

This is a bug status scorecard. It's not a global status for the

1417

1450

bug, this is usually attached to a release, or a sourcepackage in

1418

1451

a distro. So these tell you the status of a bug SOMEWHERE. The

1419

1452

pointer to this tells you which bug, and on what thing (the

1420

1453

SOMEWHERE) the status is being described.

1421

1454

*/

1422

CREATE TABLE CodeReleaseBugStatus (

1455

CREATE TABLE CodereleaseBug (

1423

1456

bug integer NOT NULL REFERENCES Bug,

1424

coderelease integer NOT NULL REFERENCES CodeRelease,

1457

coderelease integer NOT NULL REFERENCES Coderelease,

1425

1458

explicit boolean NOT NULL,

1426

bugstatus integer NOT NULL REFERENCES Label,

1459

affected integer NOT NULL REFERENCES Label,

1427

1460

priority integer NOT NULL REFERENCES Label,

1428

1461

severity integer NOT NULL REFERENCES Label,

1429

1462

reportedby integer NOT NULL REFERENCES Person,

1435

1468

1436

1469

1437

1470

/*

1438

SourcepackageBugStatus

1471

SourcepackageBug

1439

1472

The status of a bug with regard to a source package. This is different

1440

1473

to the status on a specific release, because it includes the concept

1441

1474

of workflow or prognosis ("what we intend to do with this bug") while

1442

1475

the release bug status is static ("is the bug present or not").

1443

1476

*/

1444

CREATE TABLE SourcepackageBugStatus (

1477

CREATE TABLE SourcepackageBug (

1445

1478

bug integer NOT NULL REFERENCES Bug,

1446

1479

sourcepackage integer NOT NULL REFERENCES Sourcepackage,

1447

1480

bugstatus integer NOT NULL REFERENCES Label,

1481

priority integer NOT NULL REFERENCES Label,

1448

1482

binarypackagename text,

1449

wontfix boolean NOT NULL,

1450

1483

PRIMARY KEY ( bug, sourcepackage )

1451

1484

);

1452

1485

1455

1488

/*

1456

1489

Bug_Sourcepackage_Relationship

1457

1490

This is a mapping of the relationship between a bug and a source

1458

package. Note that there is another similar table, the SourcepackageBugStatus,

1491

package. Note that there is another similar table, the SourcepackageBug,

1459

1492

that is dedicated to the status of a bug in a source package. This one is

1460

1493

a bit more subtle. For example, you might put an intry in this table to

1461

1494

indicate that a bug "victimises" a source package. In other words, the

1481

1514

bug integer NOT NULL REFERENCES Bug,

1482

1515

product integer NOT NULL REFERENCES Sourcepackage,

1483

1516

bugstatus integer NOT NULL REFERENCES Label,

1484

binarypackagename text,

1485

wontfix boolean NOT NULL,

1517

priority integer NOT NULL REFERENCES Label,

1486

1518

PRIMARY KEY ( bug, product )

1487

1519

);

1488

1520

1508

1540

1509

1541

1510

1542

/*

1511

BugRef

1543

BugExternalref

1512

1544

A table of external references for a bug, that are NOT remote

1513

1545

bug system references, except where the remote bug system is

1514

1546

not supported by the BugWatch table.

1547

XXX can we set the default timestamp to "now"

1515

1548

*/

1516

CREATE TABLE BugRef (

1517

bugref serial PRIMARY KEY,

1549

CREATE TABLE BugExternalref (

1518

1550

bug integer NOT NULL REFERENCES Bug,

1519

1551

bugreftype integer NOT NULL REFERENCES Label,

1520

1552

data text NOT NULL,

1521

1553

description text NOT NULL,

1554

createdate timestamp NOT NULL,

1522

1555

owner integer NOT NULL REFERENCES Person

1523

1556

);

1524

1557

1533

1566

*/

1534

1567

CREATE TABLE BugSystemType (

1535

1568

bugsystemtype serial PRIMARY KEY,

1569

name text NOT NULL UNIQUE,

1536

1570

title text NOT NULL,

1537

1571

description text NOT NULL,

1538

1572

homepage text,

1539

product integer REFERENCES Product, -- A Soyuz Product if it exists

1540

1573

owner integer NOT NULL REFERENCES Person -- who knows most about these

1541

1574

);

1542

/*

1543

INSERT INTO BugSystemType VALUES ( 1, "Bugzilla", "Dave Miller's Labour of Love, the Godfather of Open Source project issue tracking.", "http://www.bugzilla.org", NULL, 2 );

1544

INSERT INTO BugSystemType VALUES ( 2, "DebBugs", "The Debian bug tracking system, ugly as sin but fast and productive as a rabbit in high heels.", "http://bugs.debian.org", NULL, 3 );

1545

INSERT INTO BugSystemType VALUES ( 3, "RoundUp", "Python-based open source bug tracking system with an elegant design and reputation for cleanliness and usability.", NULL, 4 );

1546

*/

1575

INSERT INTO BugSystemType VALUES ( 1, 'bugzilla', 'BugZilla', 'Dave Miller\'s Labour of Love, the Godfather of Open Source project issue tracking.', 'http://www.bugzilla.org/', 2 );

1576

INSERT INTO BugSystemType VALUES ( 2, 'debbugs', 'DebBugs', 'The Debian bug tracking system, ugly as sin but fast and productive as a rabbit in high heels.', 'http://bugs.debian.org/', 3 );

1577

INSERT INTO BugSystemType VALUES ( 3, 'roundup', 'Round-Up', 'Python-based open source bug tracking system with an elegant design and reputation for cleanliness and usability.', 'http://www.roundup.org/', 4 );

1547

1578

1548

1579

1549

1580

/*

1557

1588

bugsystem serial PRIMARY KEY,

1558

1589

bugsystemtype integer NOT NULL REFERENCES BugSystemType,

1559

1590

name text NOT NULL,

1591

title text NOT NULL,

1560

1592

description text NOT NULL,

1561

1593

baseurl text NOT NULL,

1562

1594

owner integer NOT NULL REFERENCES Person

1584

1616

1585

1617

1586

1618

/*

1619

ProjectBugsystem

1620

A link between the Project table and the Bugsystem table. This allows

1621

us to setup a bug system and then easily create watches once a bug

1622

has been assigned to an upstream product.

1623

*/

1624

CREATE TABLE ProjectBugsystem (

1625

project integer NOT NULL REFERENCES Project,

1626

bugsystem integer NOT NULL REFERENCES BugSystem,

1627

PRIMARY KEY ( project, bugsystem )

1628

);

1629

1630

1631

1632

/*

1587

1633

BugAttachment

1588

1634

A table of attachments to bugs. These are typically patches, screenshots,

1589

1635

mockups, or other documents.

1590

1636

*/

1591

1637

CREATE TABLE BugAttachment (

1592

1638

bugattachment serial PRIMARY KEY,

1593

owner integer NOT NULL REFERENCES Person,

1594

content text NOT NULL,

1639

bug integer NOT NULL REFERENCES Bug,

1640

name text NOT NULL,

1595

1641

title text NOT NULL,

1596

description text NOT NULL,

1597

mimetype text,

1598

replaces integer REFERENCES BugAttachment

1642

description text NOT NULL

1599

1643

);

1600

1644

1601

1645

1602

1646

1603

1647

/*

1604

BugAttachment_Relationship

1605

A link between bug and attachment that carries some type

1606

of relationship.

1648

BugattachmentRevision

1649

The actual content of a bug attachment. There can be multiple

1650

uploads over time, each revision gets a changecomment.

1607

1651

*/

1608

CREATE TABLE BugAttachment_Relationship (

1609

bug integer NOT NULL REFERENCES Bug,

1652

CREATE TABLE BugattachmentContent (

1610

1653

bugattachment integer NOT NULL REFERENCES BugAttachment,

1611

label integer NOT NULL REFERENCES Label,

1612

PRIMARY KEY ( bug, bugattachment )

1654

revisiondate timestamp NOT NULL,

1655

changecomment text NOT NULL,

1656

content bytea NOT NULL,

1657

owner integer REFERENCES Person,

1658

mimetype text,

1659

PRIMARY KEY ( bugattachment, revisiondate )

1613

1660

);

1614

1661

1615

1662

1648

1695

*/

1649

1696

CREATE TABLE BugMessage (

1650

1697

bugmessage serial PRIMARY KEY,

1698

bug integer NOT NULL REFERENCES Bug,

1651

1699

msgdate timestamp NOT NULL,

1652

message text NOT NULL, -- the message or full email with headers

1700

title text NOT NULL, -- short title of comment

1701

contents text NOT NULL, -- the message or full email with headers

1653

1702

person int REFERENCES Person, -- NULL if we don't know it

1654

senderemail text, -- NULL if this came through the web

1655

1703

parent int REFERENCES BugMessage, -- gives us threading

1656

1704

distribution int REFERENCES Distribution,

1657

1705

rfc822msgid text

1659

1707

1660

1708

1661

1709

1662

/*

1663

BugMessageSighting

1664

*/

1665

CREATE TABLE BugMessageSighting (

1666

bug integer NOT NULL REFERENCES Bug,

1667

bugmessage integer NOT NULL REFERENCES BugMessage

1668

);

1669

1670

1671

1672

1710