[[:osapa:public_timestamp|<< Public Timestamp]]
=====Contents=====

  * [[https://www.linuxfoundation.org/#Server|1 Server]]
  * [[https://www.linuxfoundation.org/#Database|2 Database]]
    * [[https://www.linuxfoundation.org/#tables|2.1 tables]]
    * [[https://www.linuxfoundation.org/#ERD|2.2 ERD]]
    * [[https://www.linuxfoundation.org/#create_table|2.3 create table]]
      * [[https://www.linuxfoundation.org/#ptid|2.3.1 ptid]]
      * [[https://www.linuxfoundation.org/#ptid_info|2.3.2 ptid_info]]
      * [[https://www.linuxfoundation.org/#pt_options|2.3.3 pt_options]]
      * [[https://www.linuxfoundation.org/#ptid_transmit|2.3.4 ptid_transmit]]
      * [[https://www.linuxfoundation.org/#ptb|2.3.5 ptb]]

======  Server ======

  * **Platform**
  * LAMP - Linux/Apache/MySQL/Perl

  * **Interface**
  * see [[:osapa:pt_interface| PT Interface ]]

  * **Programming Language**
  * Perl

  * **Required Perl Modules**
  * CGI
  * DBI
  * XML::Twig
======  Database ======

Our data base consists of a relational database with the following [[https://www.linuxfoundation.org/collaborate/workgroups/osapa/pt_server#tables|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 =====
<code>
[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]</code>
=====  create table =====
====  ptid ====
<code>
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;</code>
====  ptid_info ====
<code>
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;</code>
====  pt_options ====
<code>
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');</code>

  * **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 ====
<code>
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;</code>
====  ptb ====
<code>
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');</code>

\\ [[:osapa:public_timestamp|<< Public Timestamp]]