From ceb13e3ca252c3996b97393f6934a77cc78928f8 Mon Sep 17 00:00:00 2001 From: Jamie McCracken Date: Tue, 2 May 2006 20:37:43 +0000 Subject: Updates for 0.0.4 --- data/Makefile.am | 3 +- data/mysql-tracker.sql | 420 ++++++++++++++++++++------------------------ data/tracker-introspect.xml | 315 +++++++++++++++++++++++++++------ 3 files changed, 448 insertions(+), 290 deletions(-) (limited to 'data') diff --git a/data/Makefile.am b/data/Makefile.am index 072b045b6..872c67514 100644 --- a/data/Makefile.am +++ b/data/Makefile.am @@ -2,7 +2,7 @@ SUBDIRS = english configdir = $(datadir)/tracker -config_DATA = mysql-tracker.sql tracker.cfg tracker-stop-words.txt +config_DATA = tracker-introspect.xml mysql-tracker.sql mysql-stored-procs.sql mysql-system.sql tracker.cfg tracker-stop-words.txt servicedir = $(prefix)/share/dbus-1/services service_in_files = tracker.service.in @@ -12,4 +12,3 @@ service_DATA = tracker.service @sed -e "s|\@bindir\@|$(bindir)|" $< > $@ -EXTRA_DIST = $(config_DATA) tracker-introspect.xml diff --git a/data/mysql-tracker.sql b/data/mysql-tracker.sql index 156988415..6a8ee91bb 100644 --- a/data/mysql-tracker.sql +++ b/data/mysql-tracker.sql @@ -1,116 +1,226 @@ -/* WatchType Values : */ -/* 0 = top level watched folder */ -/* 1 = watched subfolder */ -/* 2 = watched special folder (like say /usr/share/applications for .Desktop files) */ -/* 3 = watched special file */ -/* 4 = no index folder */ -/* 5 = other */ - -/* basic file info for a file */ -create table if not exists Files + +create table if not exists Options +( + OptionKey varchar(50) not null, + OptionValue varchar(50), + Primary Key (OptionValue) +); + +insert into Options (OptionKey, OptionValue) values +('DBVersion', '1'); + + +create procedure GetVersion() SELECT OptionValue FROM Options WHERE OptionKey = 'DBVersion'; + +create table if not exists ServiceTypes +( + TypeID tinyint unsigned not null, + TypeName varchar (16), + MetadataClass varchar (16), + TableName varchar (16), + + Primary Key (TypeID) +); + +insert into ServiceTypes (TypeID, TypeName, MetadataClass) values +(0, 'Files', 'File'), +(1, 'Documents', 'Doc'), +(2, 'Images', 'Image'), +(3, 'Music', 'Audio'), +(4, 'Videos', 'File'), +(5, 'VFSFiles', 'File'), +(6, 'VFSDocuments', 'Doc'), +(7, 'VFSImages', 'Image'), +(8, 'VFSMusic', 'Audio'), +(9, 'VFSVideos', 'File'), +(10, 'Conversations', 'File'), +(11, 'Playlists', 'PlayList'), +(12, 'Applications', 'App'), +(13, 'Contacts', 'Contact'), +(14, 'Emails', 'Email'), +(15, 'EmailAttachments', 'File'), +(16, 'Notes', 'Note'), +(17, 'Appointments', 'Appointment'), +(18, 'Tasks', 'Task'), +(19, 'Bookmarks', 'Bookmark'), +(20, 'History', 'History'), +(21, 'Projects', 'Project'); + + +CREATE TABLE sequence (id int unsigned NOT NULL); +INSERT INTO sequence VALUES (0); + + +/* basic file info for a file or service object */ +create table if not exists Services ( - ID int unsigned auto_increment not null, - Path varchar (200) character set utf8 not null, /* non-local files can prefix uri type here */ - FileName varchar (128) character set utf8 not null, - FileTypeID tinyint unsigned default 0, - IsVFS bool default 0, + ID int unsigned not null, + ServiceTypeID tinyint unsigned default 0, /* see ServiceTypes table above for ID values */ + Path varchar (200) character set utf8 not null, /* non-file objects should use service name here */ + Name varchar (128) character set utf8, /* name of file or object - the combination path and name must be unique for all objects */ + IsServiceSource bool default 0, IsDirectory bool default 0, - IsLink bool default 0, - IsWatched bool default 0, - WatchType tinyint unsigned default 5, - IndexTime int unsigned, /* should equal st_mtime for file if up-to-date */ + IsWatchedDirectory bool default 0, + IsLink bool default 0, + Misc varchar(255), + MiscInt int, + MiscDate DateTime, + IndexTime int unsigned, /* should equal st_mtime for file if up-to-date */ Offset int unsigned, /* last used disk offset for indexable files that always grow (like chat logs) */ primary key (ID), - unique key (Path, FileName), - key (WatchType) + unique key (Path, Name), + key (ServiceTypeID) ); +/* provides links from one service entity to another */ +create table if not exists ServiceLinks +( + ServiceID int unsigned not null, + LinkID int unsigned not null, + LinkTypeID tinyint unsigned not null, /* see ServiceLinkTypes table */ + + primary key (ServiceID, LinkID, LinkTypeID) + +); + +create table if not exists ServiceLinkTypes +( + ID tinyint unsigned auto_increment not null, + Type varchar(32), + + primary key (ID) + +); + +insert into ServiceLinkTypes (Type) Values ('PlayListItem'); + + +/* store all keywords here. */ +create table if not exists ServiceKeywords +( + ServiceID int unsigned not null, + Keyword varchar (32) character set utf8 not null, + + Primary Key (ServiceID, Keyword), + Key (Keyword) +); + + + /* store all metadata here. */ -create table if not exists FileMetaData +create table if not exists ServiceMetaData ( - FileID int unsigned not null, + ServiceID int unsigned not null, MetaDataID smallint unsigned not null, MetaDataValue Text character set utf8, MetaDataIndexValue MediumText character set utf8, - MetaDataIntegerValue int unsigned, + MetaDataNumericValue double, - Primary Key (FileID, MetaDataID), - key IValue (MetaDataID, MetaDataValue (20)), - key IIntValue (MetaDataID, MetaDataIntegerValue), + Primary Key (ServiceID, MetaDataID), + Key (MetaDataIndexValue (24)), + key INumericValue (MetaDataID, MetaDataNumericValue), FullText (MetaDataIndexValue) ); + + + /* describes the types of metadata */ create table if not exists MetaDataTypes ( ID smallint unsigned auto_increment not null, MetaName varchar (128) not null, - DataTypeID tinyint unsigned, /* 0=string, 1=int, 2=datetime (as string) */ - Indexable bool, /* if the metadata uses a full text index*/ - Writeable bool, /* embedded metadata is not writable */ + DataTypeID tinyint unsigned, /* 0=full text indexable string, 1=string, 2=numeric, 3=datetime (as string) */ + Embedded bool, /* if the metadata is embedded in the file */ + Writeable bool, /* is metadata writable */ Primary Key (ID), Unique (MetaName) ); + /* built in metadata types */ -insert into MetaDataTypes (MetaName, DatatypeID, Indexable, Writeable) values +insert into MetaDataTypes (MetaName, DatatypeID, Embedded, Writeable) values ('File.Name', 0, 1, 0), ('File.Path', 0, 1, 0), -('File.Link', 0, 1, 0), +('File.Link', 1, 1, 0), ('File.Format', 0, 1, 0 ), -('File.Size', 1, 0, 0), -('File.Permissions', 0, 0, 0), +('File.Size', 2, 1, 0), +('File.Permissions', 1, 1, 0), ('File.Content', 0, 1, 0), -('File.Description', 0, 1, 1), -('File.Keywords', 0, 1, 1), -('File.Rank', 1, 0, 1 ), -('File.IconPath', 0, 0, 1 ), -('File.PreviewThumbnailPath', 0, 0, 1), -('File.Modified', 2, 0, 0), -('File.Accessed', 2, 0, 0 ), +('File.Description', 0, 0, 1), +('File.Keywords', 0, 0, 1), +('File.Rank', 2, 0, 1), +('File.IconPath', 1, 0, 1 ), +('File.SmallThumbnailPath', 1, 0, 1), +('File.LargeThumbnailPath', 1, 0, 1), +('File.Modified', 3, 1, 0), +('File.Accessed', 3, 1, 0 ), ('File.Other', 0, 1, 0 ), -('Audio.Title', 0, 1, 0), -('Audio.Artist', 0, 1, 0), -('Audio.Album', 0, 1, 0), -('Audio.Year', 1, 0, 0), -('Audio.Comment', 0, 1, 0), -('Audio.Genre', 0, 1, 0), -('Audio.Codec', 0, 1, 0), -('Audio.Samplerate', 1, 0, 0), -('Audio.Bitrate', 1, 0, 0), -('Audio.Channels', 1, 0, 0), +('Audio.Title', 0, 1, 1), +('Audio.Artist', 0, 1, 1), +('Audio.Album', 0, 1, 1), +('Audio.AlbumArtist', 0, 1, 1), +('Audio.AlbumTrackCount', 2, 1, 1), +('Audio.TrackNo', 2, 1, 1), +('Audio.DiscNo', 2, 1, 1), +('Audio.Performer', 0, 1, 1), +('Audio.TrackGain', 2, 1, 1), +('Audio.TrackPeakGain', 2, 1, 1), +('Audio.AlbumGain', 2, 1, 1), +('Audio.AlbumPeakGain', 2, 1, 1), +('Audio.Duration', 2, 1, 0), +('Audio.ReleaseDate', 3, 1, 1), +('Audio.Comment', 0, 1, 1), +('Audio.Genre', 0, 1, 1), +('Audio.Codec', 0, 1, 1), +('Audio.CodecVersion', 1, 1, 1), +('Audio.Samplerate', 2, 1, 1), +('Audio.Bitrate', 2, 1, 1), +('Audio.Channels', 2, 1, 1), +('Audio.LastPlay', 3, 0, 1), +('Audio.PlayCount', 2, 0, 1), +('Audio.IsNew', 2, 0, 1), +('Audio.MBAlbumID', 1, 0, 1), +('Audio.MBArtistID', 1, 0, 1), +('Audio.MBAlbumArtistID', 1, 0, 1), +('Audio.MBTrackID', 1, 0, 1), +('Audio.Lyrics', 0, 0, 1), +('Audio.CoverAlbumThumbnailPath', 1, 0, 1), ('Doc.Title', 0, 1, 0), ('Doc.Subject', 0, 1, 0), ('Doc.Author', 0, 1, 0), ('Doc.Keywords', 0, 1, 0), ('Doc.Comments', 0, 1, 0), -('Doc.PageCount', 1, 0, 0), -('Doc.WordCount', 1, 0, 0), -('Doc.Created', 2, 0, 0), -('Image.Height', 1, 0, 0), -('Image.Width', 1, 0, 0), +('Doc.PageCount', 2, 1, 0), +('Doc.WordCount', 2, 1, 0), +('Doc.Created', 3, 1, 0), +('Image.Height', 2, 1, 0), +('Image.Width', 2, 1, 0), ('Image.Title', 0, 1, 0), -('Image.Date', 2, 0, 0), +('Image.Date', 3, 1, 0), ('Image.Keywords', 0, 1, 0), ('Image.Creator', 0, 1, 0), ('Image.Comments', 0, 1, 0), ('Image.Description', 0, 1, 0), ('Image.Software', 0, 1, 0), ('Image.CameraMake', 0, 1, 0), -('Image.CameraModel', 0, 1, 0); - +('Image.CameraModel', 0, 1, 0), +('PlayList.DateCreated', 3, 0, 1), +('PlayList.LastPlay', 3, 0, 1), +('PlayList.PlayCount', 2, 0, 1), +('PlayList.Description', 0, 0, 1); /* optional contextual file data - gives a nice audit trail for a file */ create table if not exists FileContexts ( - FileID int unsigned not null, - ContextActionID tinyint unsigned not null, /* 0=created, 1=edited, 2=attached, 3=embedded */ + FileID int unsigned not null, + ContextActionID tinyint unsigned not null, ContextDate DateTime not null, ContextApp varchar (128), ContextFileID int unsigned, /* file if linked/attached/embedded */ @@ -120,39 +230,16 @@ create table if not exists FileContexts key (ContextFileID) ); - -/* allow aliasing of VFolders with nice names */ -create table if not exists VFolders +create table if not exists FileContextActions ( - Path varchar (200) not null, - Name varchar (128) not null, - Query text not null, - RDF text, - UserDefined bool, - - primary key (Path, Name) + ID tinyint unsigned auto_increment not null, + ContextAction varchar (30) not null, + primary key (ID) ); -/* determines whether a file is more than just a file */ -create table if not exists FileTypes -( - ID tinyint unsigned not null, - TypeName varchar(20) not null, +insert into FileContextActions (ContextAction) values ('Created'),('Edited'),('Attached'),('Embedded'),('Downloaded'); - primary key (ID), - unique (TypeName) -); - -insert into FileTypes values -(0,'File'), -(1,'Desktop'), -(2,'Bookmarks'), -(3,'SmartBookmarks'), -(4,'WebHistory'), -(5,'Emails'), -(6,'Conversations'), -(7,'Contacts'); /* table for files waiting to be processed */ @@ -167,163 +254,36 @@ create table if not exists FilePending IsDir tinyint default 0, primary key (ID), - key (FileID), + key (FileID, Action), key (Counter) ); -/* freedesktop .desktop files */ -create table if not exists DesktopFiles -( - AppName varchar (128) not null, - LocaleName varchar (128), - Comment varchar (255), - LocaleComment varchar (255), - Categories varchar (255), - Executable varchar (255), - - primary key (AppName), - FullText (AppName, LocaleName, Comment, LocaleComment, Categories) - -); - -/* any type of bookmark (web/filesystem) */ -create table if not exists Bookmarks -( - ID int auto_increment not null, - Type smallint not null, /*0=web, 1=Filesystem */ - Title varchar (255) not null, - URL varchar (255) not null, - - primary key (ID), - FullText (Title, URL) - -); - -/* web history */ -create table if not exists History -( - ID int auto_increment not null, - HistoryDate datetime, - Title varchar (255) not null, - URL varchar (255) not null, - - primary key (ID), - key (HistoryDate), - FullText (Title, URL) -); - - -/* index emails */ -create table if not exists Emails -( - EmailID int auto_increment not null, - MessageID varchar (255) not null, - InReplyTo varchar (255), - Subject varchar (255), - Body LongText, - - primary key (EmailID), - key (MessageID), - key (InReplyTo), - FullText (Subject, Body) - -); - -create table if not exists EmailAttachments -( - EmailID int not null, - AttachmentID int not null, - MimeVersion varchar (30), - ContentType varchar (255), - ContentBoundary varchar (255), - ContentCharset varchar (128), - ContentEncoding varchar (255), - ContentName varchar (255), - ContentDisposition varchar (255), - ContentFileName varchar (255), - Contents LongText, - AsText LongText, - - primary key (EmailID, AttachmentID), - FullText (ContentFileName, AsText) -); - -create table if not exists EmailAddresses +create table if not exists FileWatches ( - EmailAddressID int auto_increment not null, - EmailName varchar (255), - Address varchar (255) not null, - - primary key (EmailAddressID), - FullText (EmailName, Address) -); + WatchID int not null, + URI varchar(255) not null, -/* used for any field that can contain multiple emails */ -create table if not exists EmailReferences -( - EmailID int not null, - EmailAddressID int not null, - ReferenceType smallint not null, /* 0 = Sent to, 1=CC, 2=BCC, 3=Reference, 4=sender */ - counter int not null, - - primary key (EmailID, EmailAddressID, ReferenceType, counter), - key (EmailAddressID) -); - -/* index chat logs line by line */ -create table if not exists Conversations -( - FileID int not null, - LineID int not null, - LogDate datetime not null, - SpeakerNick varchar (64) not null, - Content varchar (255) not null, - - primary key (FileID, LineID), - FullText (SpeakerNick, Content) + primary key (WatchID), + unique (URI) ); -create table if not exists Contacts -( - ContactID int auto_increment not null, - ContactName varchar (255) not null, - Nick varchar (30), - HomeTel varchar(30), - BusTel varchar(30), - Mobile varchar(30), - Fax varchar(30), - HomePage varchar(255), - ChatAddress varchar (64), - ChatAddress2 varchar (64), - - primary key (ContactID), - FullText (ContactName, Nick) -); -/* Multiple email accounts for a contact */ -create table if not exists ContactEmails +/* allow aliasing of VFolders with nice names */ +create table if not exists VFolders ( - ContactID int not null, - EmailID int not null, - AdressType smallint not null, /* 0=unknown, 1=home/personal, 2=work */ + Path varchar (200) not null, + Name varchar (128) not null, + Query text not null, + RDF text, - primary key (ContactID, EmailID, AdressType ), - key (EmailID) - + primary key (Path, Name) ); -/* Multiple IM accounts for a contact */ -create table if not exists ContactChats -( - ContactID int not null, - ServerID smallint not null, /* 0 = aim, 1=jabber, 2=yahoo, 3=MSN, 4=ICQ, 5=Groupwise */ - Account varchar (255) not null, - primary key (ContactID, ServerID) -); +select Null diff --git a/data/tracker-introspect.xml b/data/tracker-introspect.xml index 66b2b08b0..e8c8b9ba2 100644 --- a/data/tracker-introspect.xml +++ b/data/tracker-introspect.xml @@ -1,20 +1,23 @@ - - - - + + + + + + + - - - - - - - - - - - + + + + + - + + + + + + + - + - + + + - + + - + - + + + + - + + + - + + + + + + + + + + + + + + + + + + - - + + + + + + + + - + + + - + + + + + + + + + + + + + + + - + + - - + - + + + + @@ -115,74 +190,198 @@ + + + + + + + + + + + + + - + + + + + + + + + + + - - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + - + + + + + + + + + + + - + - + - + - - - - - - - - - - + + + + - + + + + + + + + + + + + + + + + + + + - - + + - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + -- cgit v1.2.1