Difference between revisions of "Metrics/Data Catalog"

mysql> desc simple;
+-----------------+--------------+------+-----+---------------------+----------------+
| Field           | Type         | Null | Key | Default             | Extra          |
+-----------------+--------------+------+-----+---------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL                | auto_increment |
| license_uri     | varchar(255) | NO   |     |                     |                |
| search_engine   | varchar(255) | NO   |     |                     |                |
| count           | int(11)      | NO   |     | 0                   |                |
| timestamp       | datetime     | NO   | MUL | 0000-00-00 00:00:00 |                |
| jurisdiction    | varchar(255) | YES  |     | NULL                |                |
| license_type    | varchar(16)  | YES  |     | NULL                |                |
| license_version | varchar(16)  | YES  |     | NULL                |                |
+-----------------+--------------+------+-----+---------------------+----------------+

CREATE TABLE `simple` (
  `id` int(11) NOT NULL auto_increment,
  `license_uri` varchar(255) NOT NULL default '',
  `search_engine` varchar(255) NOT NULL default '',
  `count` int(11) NOT NULL default '0',
  `timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
  `jurisdiction` varchar(255) default NULL,
  `license_type` varchar(16) default NULL,
  `license_version` varchar(16) default NULL,
  PRIMARY KEY  (`id`),
  KEY `timestamp_index` (`timestamp`)
);


INSERT INTO `simple` VALUES (750584,'http://creativecommons.org/licenses/by-sa/2.0/jp/','MSN',3955,'2006-07-11 16:04:56','jp','by-sa','2.0');

mysql> desc complex;
+-------------------+--------------+------+-----+---------------------+----------------+
| Field             | Type         | Null | Key | Default             | Extra          |
+-------------------+--------------+------+-----+---------------------+----------------+
| id                | int(11)      | NO   | PRI | NULL                | auto_increment |
| license_specifier | varchar(255) | NO   |     |                     |                |
| search_engine     | varchar(255) | NO   |     |                     |                |
| count             | int(11)      | NO   |     | 0                   |                |
| query             | varchar(255) | YES  |     | NULL                |                |
| timestamp         | datetime     | NO   | MUL | 0000-00-00 00:00:00 |                |
| country           | varchar(255) | YES  |     | NULL                |                |
| language          | varchar(255) | YES  |     | NULL                |                |
+-------------------+--------------+------+-----+---------------------+----------------+

CREATE TABLE `complex` (
  `id` int(11) NOT NULL auto_increment,
  `license_specifier` varchar(255) NOT NULL default '',
  `search_engine` varchar(255) NOT NULL default '',
  `count` int(11) NOT NULL default '0',
  `query` varchar(255) default NULL,
  `timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
  `country` varchar(255) default NULL,
  `language` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `complex_timestamp_index` (`timestamp`)
);

INSERT INTO `complex` VALUES (32,'cc_any','Yahoo',3,'license','2006-07-11 16:24:42','Norway','portuguese');

mysql> desc site_specific;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| license_uri    | varchar(255) | NO   |     | NULL    |                |
| site           | varchar(255) | NO   |     | NULL    |                |
| count          | int(11)      | NO   |     | NULL    |                |
| utc_time_stamp | datetime     | NO   | MUL | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

CREATE TABLE `site_specific` (
  `id` int(11) NOT NULL auto_increment,
  `license_uri` varchar(255) NOT NULL,
  `site` varchar(255) NOT NULL,
  `count` int(11) NOT NULL,
  `utc_time_stamp` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `utc_time_stamp` (`utc_time_stamp`,`license_uri`,`site`),
  KEY `site_specific_timestamp_index` (`utc_time_stamp`)
);

INSERT INTO `site_specific` VALUES (1,'http://creativecommons.org/licenses/by-nd/2.0/','http://www.flickr.com/',317345,'2006-03-17 12:00:00');