Vyššie na [Platon] / web-apps / finance-manager / sql

Create tables.

--
-- These lines are needed, because there is an TRANSACTION database !
--
DROP TABLE IF EXISTS Transaction;
DROP TABLE IF EXISTS Event;
DROP TABLE IF EXISTS Source;
DROP TABLE IF EXISTS event_type;

-- MySQL dump 9.09
--
-- Host: localhost    Database: finance
-- ------------------------------------------------------
-- Server version    4.0.16-log

--
-- Table structure for table `Source`
--

DROP TABLE IF EXISTS Source;
CREATE TABLE Source (
  sourceid int(10) unsigned NOT NULL auto_increment,
  active smallint(3) unsigned NOT NULL default '0',
  name varchar(255) default NULL,
  description varchar(255) default NULL,
  note text,
  created datetime NOT NULL default '0000-00-00 00:00:00',
  changed timestamp(14) NOT NULL,
  last_transaction datetime NOT NULL default '0000-00-00 00:00:00',
  start_money float NOT NULL default '0',
  stop_money float NOT NULL default '0',
  money float NOT NULL default '0',
  low_money float default '3000',
  medium_money float default '10000',
  high_money float default '15000',
  PRIMARY KEY  (sourceid),
  UNIQUE KEY name (name)
) TYPE=InnoDB;


--
-- Table structure for table `event_type`
--

DROP TABLE IF EXISTS event_type;
CREATE TABLE event_type (
  event_type int(10) unsigned NOT NULL auto_increment,
  name varchar(20) default NULL,
  description text,
  PRIMARY KEY  (event_type)
) TYPE=InnoDB;


--
-- Table structure for table `Event`
--

DROP TABLE IF EXISTS Event;
CREATE TABLE Event (
  event_type int(10) unsigned default NULL,
  from_source int(10) unsigned default NULL,
  to_source int(10) unsigned default NULL,
  eventid int(10) unsigned NOT NULL auto_increment,
  active smallint(3) unsigned NOT NULL default '1',
  name varchar(255) default NULL,
  description varchar(255) default NULL,
  note text,
  created datetime NOT NULL default '0000-00-00 00:00:00',
  changed timestamp(14) NOT NULL,
  last_transaction datetime NOT NULL default '0000-00-00 00:00:00',
  money float NOT NULL default '0',
  period int(10) unsigned default NULL,
  start_time datetime default NULL,
  stop_time datetime default NULL,
  PRIMARY KEY  (eventid),
  UNIQUE KEY event_type (event_type,eventid),
  UNIQUE KEY from_source (from_source,eventid),
  UNIQUE KEY to_source (to_source,eventid),
  UNIQUE KEY event_type_2 (event_type,from_source,to_source),
  UNIQUE KEY event_type_3 (event_type,to_source),
  UNIQUE KEY from_source_2 (from_source,to_source),
  UNIQUE KEY name (name),
  CONSTRAINT `Event_ibfk_1` FOREIGN KEY (`event_type`) REFERENCES `event_type` (`event_type`) ON DELETE SET NULL,
  CONSTRAINT `Event_ibfk_2` FOREIGN KEY (`from_source`) REFERENCES `Source` (`sourceid`) ON DELETE SET NULL,
  CONSTRAINT `Event_ibfk_3` FOREIGN KEY (`to_source`) REFERENCES `Source` (`sourceid`) ON DELETE SET NULL
) TYPE=InnoDB;


--
-- Table structure for table `Transaction`
--

DROP TABLE IF EXISTS Transaction;
CREATE TABLE Transaction (
  eventid int(10) unsigned default NULL,
  from_source int(10) unsigned default NULL,
  to_source int(10) unsigned default NULL,
  transid int(11) NOT NULL auto_increment,
  created timestamp(14) NOT NULL,
  changed datetime default NULL,
  money float NOT NULL default '0',
  from_source_money float NOT NULL default '0',
  to_source_money float NOT NULL default '0',
  PRIMARY KEY  (transid),
  UNIQUE KEY eventid (eventid,transid),
  UNIQUE KEY from_source (from_source,transid),
  UNIQUE KEY to_source (to_source,transid),
  UNIQUE KEY eventid_2 (eventid,from_source,to_source),
  UNIQUE KEY eventid_3 (eventid,to_source),
  UNIQUE KEY from_source_2 (from_source,to_source),
  CONSTRAINT `Transaction_ibfk_1` FOREIGN KEY (`eventid`) REFERENCES `Event` (`eventid`) ON DELETE SET NULL,
  CONSTRAINT `Transaction_ibfk_2` FOREIGN KEY (`from_source`) REFERENCES `Source` (`sourceid`) ON DELETE SET NULL,
  CONSTRAINT `Transaction_ibfk_3` FOREIGN KEY (`to_source`) REFERENCES `Source` (`sourceid`) ON DELETE SET NULL
) TYPE=InnoDB;

--
-- Dumping data for table `Transaction`
--


--
-- Table structure for table `SourceLog`
--

DROP TABLE IF EXISTS SourceLog;
CREATE TABLE SourceLog (
  sourcelogid int(11) NOT NULL auto_increment,
  time timestamp(14) NOT NULL,
  action_type enum('added','changed_from','changed_to','removed') default NULL,
  sourceid int(10) unsigned NOT NULL default '0',
  active smallint(3) unsigned NOT NULL default '0',
  name varchar(255) default NULL,
  description varchar(255) default NULL,
  note text,
  created datetime default NULL,
  changed datetime default NULL,
  last_transaction datetime default NULL,
  start_money float NOT NULL default '0',
  stop_money float NOT NULL default '0',
  money float NOT NULL default '0',
  low_money float default '3000',
  medium_money float default '10000',
  high_money float default '15000',
  PRIMARY KEY  (sourcelogid),
  KEY name (name),
  KEY time (time)
) TYPE=InnoDB;


--
-- Table structure for table `EventLog`
--

DROP TABLE IF EXISTS EventLog;
CREATE TABLE EventLog (
  eventlogid int(11) NOT NULL auto_increment,
  time timestamp(14) NOT NULL,
  action_type enum('added','changed_from','changed_to','removed') default NULL,
  eventid int(10) unsigned NOT NULL default '0',
  event_type int(10) unsigned NOT NULL default '0',
  active smallint(3) unsigned NOT NULL default '1',
  name varchar(255) default NULL,
  description varchar(255) default NULL,
  note text,
  created datetime default NULL,
  changed datetime default NULL,
  last_transaction datetime default NULL,
  money float NOT NULL default '0',
  from_source int(10) unsigned NOT NULL default '0',
  to_source int(10) unsigned NOT NULL default '0',
  period int(10) unsigned default NULL,
  start_time datetime default NULL,
  stop_time datetime default NULL,
  PRIMARY KEY  (eventlogid),
  KEY name (name),
  KEY time (time)
) TYPE=InnoDB;