Paste: ugly sql

Author: fooo
Mode: sql-loader
Date: Thu, 26 Aug 2010 16:36:12
Plain Text |
BEGIN TRANSACTION;

INSERT OR IGNORE INTO Types VALUES ( NULL, 'Book' );

INSERT OR IGNORE INTO Attributes VALUES ( NULL, 'author' );
INSERT OR IGNORE INTO Attributes VALUES ( NULL, 'title' );
INSERT OR IGNORE INTO Attributes VALUES ( NULL, 'year' );
INSERT OR IGNORE INTO Attributes VALUES ( NULL, 'publisher' );
INSERT OR IGNORE INTO Attributes VALUES ( NULL, 'isbn' );

INSERT INTO Records VALUES (
  NULL,
  (SELECT TypeID FROM Types WHERE Name = 'Book'),
  'eusterbrock92ininfix',
  NULL,
  NULL
);

INSERT INTO Entries VALUES (
  NULL,
  (SELECT MAX(RecordID) FROM Records),
  (SELECT AttributeID FROM Attributes WHERE Name = 'author'),
  'Jutta Eusterbrock'
);
INSERT INTO Entries VALUES (
  NULL,
  (SELECT MAX(RecordID) FROM Records),
  (SELECT AttributeID FROM Attributes WHERE Name = 'title'),
  'Wissensbasierte Verfahren zur Synthese mathematischer Beweise: Eine kombinatorische Anwendung.'
);
INSERT INTO Entries VALUES (
  NULL,
  (SELECT MAX(RecordID) FROM Records),
  (SELECT AttributeID FROM Attributes WHERE Name = 'year'),
  '1992'
);
INSERT INTO Entries VALUES (
  NULL,
  (SELECT MAX(RecordID) FROM Records),
  (SELECT AttributeID FROM Attributes WHERE Name = 'publisher'),
  'Infix Verlag, St. Augustin, Germany'
);
INSERT INTO Entries VALUES (
  NULL,
  (SELECT MAX(RecordID) FROM Records),
  (SELECT AttributeID FROM Attributes WHERE Name = 'isbn'),
  '3-929037-10-6'
);

COMMIT TRANSACTION;

Annotation: baaaar

Author: baaaar
Mode: sql-loader
Date: Thu, 26 Aug 2010 16:36:58
Plain Text |
BEGIN TRANSACTION;

CREATE TABLE Users (
  UserID    INTEGER UNIQUE,
  LoginName VARCHAR UNIQUE,
  Password  VARCHAR,
  IsAdmin   BOOLEAN
);

CREATE TABLE Types (
  TypeID INTEGER PRIMARY KEY,
  Name   VARCHAR UNIQUE
);

CREATE TABLE Attributes (
  AttributeID INTEGER PRIMARY KEY,
  Name        VARCHAR UNIQUE
);

CREATE TABLE Records (
  RecordID INTEGER PRIMARY KEY AUTOINCREMENT,
  TypeID   INTEGER,
  Key      VARCHAR,
  ParentID INTEGER,
  UserID   INTEGER CHECK (((ParentID IS NULL)     AND (UserID IS NULL)) OR
                          ((ParentID IS NOT NULL) AND (UserID IS NOT NULL))),

  FOREIGN KEY ( TypeID   ) REFERENCES Types   ( TypeID   ),
  FOREIGN KEY ( ParentID ) REFERENCES Records ( RecordID ),
  FOREIGN KEY ( UserID   ) REFERENCES Users   ( UserID   )
);

CREATE TABLE Entries (
  EntryID     INTEGER PRIMARY KEY,
  RecordID    INTEGER,
  AttributeID INTEGER,
  Content     VARCHAR,
  FOREIGN KEY ( RecordID  ) REFERENCES Records    ( RecordID    ),
  FOREIGN KEY ( AttributeID ) REFERENCES Attributes ( AttributeID )
);

COMMIT TRANSACTION;

New Annotation

Summary:
Author:
Mode:
Body: