dicom-schema.sql 2.7 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677
  1. --
  2. -- A simple SQLITE3 database schema for modelling locally stored DICOM files
  3. --
  4. -- Note: the semicolon at the end is necessary for the simple parser to separate
  5. -- the statements since the SQlite driver does not handle multiple
  6. -- commands per QSqlQuery::exec call!
  7. -- ;
  8. DROP TABLE IF EXISTS 'SchemaInfo' ;
  9. DROP TABLE IF EXISTS 'Images' ;
  10. DROP TABLE IF EXISTS 'Patients' ;
  11. DROP TABLE IF EXISTS 'Series' ;
  12. DROP TABLE IF EXISTS 'Studies' ;
  13. DROP TABLE IF EXISTS 'Directories' ;
  14. DROP INDEX IF EXISTS 'ImagesFilenameIndex' ;
  15. DROP INDEX IF EXISTS 'ImagesSeriesIndex' ;
  16. DROP INDEX IF EXISTS 'SeriesStudyIndex' ;
  17. DROP INDEX IF EXISTS 'StudiesPatientIndex' ;
  18. CREATE TABLE 'SchemaInfo' ( 'Version' VARCHAR(1024) NOT NULL );
  19. INSERT INTO 'SchemaInfo' VALUES('0.5.2');
  20. CREATE TABLE 'Images' (
  21. 'SOPInstanceUID' VARCHAR(64) NOT NULL,
  22. 'Filename' VARCHAR(1024) NOT NULL ,
  23. 'SeriesInstanceUID' VARCHAR(64) NOT NULL ,
  24. 'InsertTimestamp' VARCHAR(20) NOT NULL ,
  25. PRIMARY KEY ('SOPInstanceUID') );
  26. CREATE TABLE 'Patients' (
  27. 'UID' INTEGER PRIMARY KEY AUTOINCREMENT,
  28. 'PatientsName' VARCHAR(255) NULL ,
  29. 'PatientID' VARCHAR(255) NULL ,
  30. 'PatientsBirthDate' DATE NULL ,
  31. 'PatientsBirthTime' TIME NULL ,
  32. 'PatientsSex' varchar(1) NULL ,
  33. 'PatientsAge' varchar(10) NULL ,
  34. 'PatientsComments' VARCHAR(255) NULL );
  35. CREATE TABLE 'Series' (
  36. 'SeriesInstanceUID' VARCHAR(64) NOT NULL ,
  37. 'StudyInstanceUID' VARCHAR(64) NOT NULL ,
  38. 'SeriesNumber' INT NULL ,
  39. 'SeriesDate' DATE NULL ,
  40. 'SeriesTime' VARCHAR(20) NULL ,
  41. 'SeriesDescription' VARCHAR(255) NULL ,
  42. 'Modality' VARCHAR(20) NULL ,
  43. 'BodyPartExamined' VARCHAR(255) NULL ,
  44. 'FrameOfReferenceUID' VARCHAR(64) NULL ,
  45. 'AcquisitionNumber' INT NULL ,
  46. 'ContrastAgent' VARCHAR(255) NULL ,
  47. 'ScanningSequence' VARCHAR(45) NULL ,
  48. 'EchoNumber' INT NULL ,
  49. 'TemporalPosition' INT NULL ,
  50. PRIMARY KEY ('SeriesInstanceUID') );
  51. CREATE TABLE 'Studies' (
  52. 'StudyInstanceUID' VARCHAR(64) NOT NULL ,
  53. 'PatientsUID' INT NOT NULL ,
  54. 'StudyID' VARCHAR(255) NULL ,
  55. 'StudyDate' DATE NULL ,
  56. 'StudyTime' VARCHAR(20) NULL ,
  57. 'AccessionNumber' VARCHAR(255) NULL ,
  58. 'ModalitiesInStudy' VARCHAR(255) NULL ,
  59. 'InstitutionName' VARCHAR(255) NULL ,
  60. 'ReferringPhysician' VARCHAR(255) NULL ,
  61. 'PerformingPhysiciansName' VARCHAR(255) NULL ,
  62. 'StudyDescription' VARCHAR(255) NULL ,
  63. PRIMARY KEY ('StudyInstanceUID') );
  64. CREATE UNIQUE INDEX IF NOT EXISTS 'ImagesFilenameIndex' ON 'Images' ('Filename');
  65. CREATE UNIQUE INDEX IF NOT EXISTS 'ImagesSeriesIndex' ON 'Images' ('SeriesInstanceUID');
  66. CREATE UNIQUE INDEX IF NOT EXISTS 'SeriesStudyIndex' ON 'Series' ('StudyInstanceUID');
  67. CREATE UNIQUE INDEX IF NOT EXISTS 'StudiesPatientIndex' ON 'Studies' ('PatientsUID');
  68. CREATE TABLE 'Directories' (
  69. 'Dirname' VARCHAR(1024) ,
  70. PRIMARY KEY ('Dirname') );