Linux Foundation Wiki

project collaboration site

User Tools

Site Tools


osapa:pt_server

<< Public Timestamp

Contents

Server

  • Platform
  • LAMP - Linux/Apache/MySQL/Perl
  • Programming Language
  • Perl
  • Required Perl Modules
  • CGI
  • DBI
  • XML::Twig

Database

Our data base consists of a relational database with the following tables and the PTBs!

The database is used for using the system, enabling searches and quick-verifications.

The PTBs are used for legal evidence. The Hash-Values of these PTBs are what is going to be published. PTBs are single files with names like 'PTB-123', where the 123 is the ID of the ptb table.

tables

  • ptb
  • Each Public Timestamp Block has one entry here incl. some meta-informations
  • ptb also contains 'first-ptid' and 'last-ptid' to tell which PTIDs are within this block
  • ptid
  • This are the actual timestamps. The ID of this table is the PTID for each timestamped file
  • ptid_info
  • contains the meta-informations on timestamped files, this is a separate table as the same file might be transmitted several times with different meta-infos (like different download urls, ..)
  • pt_options
  • just some options we need for configuration
  • ptid_transmit
  • when what was submitted to us, incl. IP etc.
  • This is primarily for statistical usage and abuse-detection

ERD

[ptid] 1-------n [ptid_info] 1-------n [ptid_transmit]

 1 1 1   
 | | |   (1) first-ptid
 | | |   (2) last-ptid
 | | |   (3) my-ptid
 n n n

[ptb] 1-------1 [_PTB_FILE_]


[pt_options]

create table

ptid

CREATE TABLE `ptid` (
  `id` bigint(20) NOT NULL auto_increment, 
  `filesize` bigint(20) NOT NULL, 
  `sum32` char(18) NOT NULL default '',
  `fcs16` char(14) NOT NULL default '',
  `elf32` char(18) NOT NULL default '',
  `md2sum` char(32) NOT NULL default '',
  `md4sum` char(32) NOT NULL default '',
  `crc64` char(26) NOT NULL default '',
  `whirlpool` char(128) NOT NULL default '',
  `ripemd128` char(32) NOT NULL default '',
  `ripemd160` char(40) NOT NULL default '',
  `crc16` char(14) NOT NULL default '',
  `haval` char(32) NOT NULL default '',
  `tiger` char(48) NOT NULL default '',
  `sha512` char(128) NOT NULL default '',
  `sha384` char(96) NOT NULL default '',
  `sha256` char(64) NOT NULL default '',
  `crc32mpeg2` char(18) NOT NULL default '',
  `adler32` char(18) NOT NULL default '',
  `cksum` char(18) NOT NULL default '',
  `md5sum` char(32) NOT NULL default '',
  `fcs32` char(18) NOT NULL default '',
  `sha160` char(40) NOT NULL default '',
  `sha0` char(40) NOT NULL default '',
  `sha224` char(56) NOT NULL default '',
  `tiger128` char(32) NOT NULL default '',
  `tiger160` char(40) NOT NULL default '',
  `tiger2` char(48) NOT NULL default '',
  `whirlpool0` char(128) NOT NULL default '',
  `whirlpool2` char(128) NOT NULL default '',
  `ripemd256` char(64) NOT NULL default '',
  `ripemd320` char(80) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ptid_info

CREATE TABLE `ptid_info` (
  `id` bigint(20) NOT NULL auto_increment,
  `ptid` bigint(20) NOT NULL default '0',
  `document_title` varchar(255) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `homepage` varchar(255) NOT NULL default '',
  `person` varchar(255) NOT NULL default '',
  `project_name` varchar(255) NOT NULL default '',
  `url` varchar(255) NOT NULL default '',
  `filename` varchar(255) NOT NULL default '',
  `documenttype` smallint(6) NOT NULL default '0',
  `reported` smallint(6) NOT NULL default '0',
  `info_show` char(1) NOT NULL default '-',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

pt_options

CREATE TABLE `pt_options` (
  `id` int(11) NOT NULL default '0',
  `description` varchar(255) NOT NULL default '',
  `value` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `pt_options` VALUES (0,'quick-stat_timestamps-today','0');
INSERT INTO `pt_options` VALUES (1,'quick-stat_timestamps-total','0');
  • quick-stat_%
  • Contains statistics that we show very often. Those stats will be updated regularly via cronjobs, so that we don't need to calculate them for every page-view

ptid_transmit

CREATE TABLE `ptid_transmit` (
  `id` bigint(20) NOT NULL auto_increment,
  `tstamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `ip` char(15) NOT NULL default '',
  `version` int(11) NOT NULL default '0',
  `subversion` int(11) NOT NULL default '0',
  `ptid_info` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ptb

CREATE TABLE `ptb` (
  `id` bigint(20) NOT NULL auto_increment,
  `first_ptid` bigint(20) NOT NULL,
  `last_ptid` bigint(20) NOT NULL,
  `ptid` bigint(20) NOT NULL,
  `tstamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `ptb` VALUES (0, 0, 0, 0, '0000-00-00');


<< Public Timestamp

osapa/pt_server.txt · Last modified: 2016/07/19 01:21 (external edit)