Database driven images not showing in non-english systems

I'd like to share with the community the results of 3 days of testings and
headaches. I finally found the reason for the problem and a way to solve it:
As stated in
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/migratereports.mspx
dealing with 'How to Migrate Access Reports to Reporting Services', when
talking about database-stored images, the writer (Adam Cogan) says:
[...]
3. Replace the default value in the image Value field with the following
expression as shown in Figure 16:
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),105))
[...]
However this piece of code does not work under certain conditions. I have
seen several forum pages of people posting they are having the same problem
(see
http://forums.devarticles.com/archive/t-11844/displaying-image-fields-in-reporting-services )
. The former code takes the assumption that the images stored in the
database were saved by a English/American localised application. If you are
using an English version of MS Access, do copy a bitmap file and paste it
into an image field of SQL table (using ADP Access project), MS Access adds
an OLE header and trailer to the image you are storing. In the English case,
the header is 78 bytes and trailer 38 bytes. However, this header is not of
that size if you are using another language. In my case, we use spanish
applications and the OLE header for bitmap files is 88 bytes instead of 78
(the trailer is also 38 bytes but you can forget about it).
If you use the former piece of code in your reports and you are not using an
English OS and applications you will see red crosses on your reports (this
is an assumption of my own because at least in the Spanish case the header
size differs from English; I suppose the same might happen in German,
French, etc.).
The reason of this behaviour is because when converting those 78 bytes of
english header into a Base64 string, it turns to be exactly 104 Base64
chars:
78 bytes * 8 bits each = 624 bits
If you convert that stream of 624 bits using Base64 (6 bits each char):
624 bits / 6 bits = 104 Base64 chars
So the next Base64 char (105) belongs completely to the bitmap file data and
you don't experience any problem if you use the Mid function to cut the
string by 105th char and then FromBase64String to convert the Base64 string
back to a byte() (byte array).
However if you use Spanish applications, the OLE header is 88 bytes (704
bits). If you try to convert that stream of bits into a Base64 string:
740 bits / 6 bits each Base64 char = 123,333... chars
What does it mean? It means that it will use 123 chars and 2 bits of the
next char. The other 4 bits of the 124th Base64 char will have data
belonging to the bitmap file. And here is the tricky part: You CANNOT remove
the OLE header without turning the whole string into rubbish because of
this. Thats why you always see red crosses on your reports no matter the
number you use to remove the OLE header from the Base64 string. No matter if
you use 105, 104, 103, or 123, 124, etc. It will always break the Base64
string and when you convert it back to get the image you will get just
rubish.
THE SOLUTION:
Instead of using Base64 strings to cut the OLE header and try to rebuild the
image, you should use this piece of code in your Image RS components:
=System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),XXX))
Where XXX is the size of the localised OLE header + 1.
For Spanish use XXX = 89
For English use XXX = 79
For other languages you will have to guess (you can do some trial/error
until you find out).
Why is there this difference of sizes in the OLE header? The header contains
the type description of the file (the type column when you see the file
using explorer) using your local language. In Spanish, the default value for
a bitmap file is 'Imagen de mapa de bits' (22 bytes). However in English the
type description for that kind of files (I suppose) is 'Bitmap image' (12
bytes). Hence:
22 - 12 = 10
The OLE header is 10 bytes larger for spanish applications (in the case of
.bmp files). For other type of files the size probably differs (I have not
tested it).
Instead of trial/error to find the XXX value for your local languae you can
try to do this little calculation:
78 + ( length(bitmap type description for your language) - 12) + 1
That's all. Just use:
=System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),XXX))
being XXX the value you have just calculated and forget about the original
Adam's code.
I hope this could help to people experiencing this problem and save their
valuable time. I also hope that Adam Cogan revises his article to replace
his version of the code and explains the restrictions of this alternative
also. People tend to consider documentation under microsoft.com domain to be
fully trustable, but in this case the information was totally wrong for
users that are not using English localised applications.
Regards.Thanks for sharing your findings!
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
news:uyB2AeiVFHA.228@.TK2MSFTNGP12.phx.gbl...
> I'd like to share with the community the results of 3 days of testings and
> headaches. I finally found the reason for the problem and a way to solve
> it:
> As stated in
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/migratereports.mspx
> dealing with 'How to Migrate Access Reports to Reporting Services', when
> talking about database-stored images, the writer (Adam Cogan) says:
> [...]
> 3. Replace the default value in the image Value field with the following
> expression as shown in Figure 16:
> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),105))
> [...]
> However this piece of code does not work under certain conditions. I have
> seen several forum pages of people posting they are having the same
> problem (see
> http://forums.devarticles.com/archive/t-11844/displaying-image-fields-in-reporting-services )
> . The former code takes the assumption that the images stored in the
> database were saved by a English/American localised application. If you
> are using an English version of MS Access, do copy a bitmap file and paste
> it into an image field of SQL table (using ADP Access project), MS Access
> adds an OLE header and trailer to the image you are storing. In the
> English case, the header is 78 bytes and trailer 38 bytes. However, this
> header is not of that size if you are using another language. In my case,
> we use spanish applications and the OLE header for bitmap files is 88
> bytes instead of 78 (the trailer is also 38 bytes but you can forget about
> it).
> If you use the former piece of code in your reports and you are not using
> an English OS and applications you will see red crosses on your reports
> (this is an assumption of my own because at least in the Spanish case the
> header size differs from English; I suppose the same might happen in
> German, French, etc.).
> The reason of this behaviour is because when converting those 78 bytes of
> english header into a Base64 string, it turns to be exactly 104 Base64
> chars:
> 78 bytes * 8 bits each = 624 bits
> If you convert that stream of 624 bits using Base64 (6 bits each char):
> 624 bits / 6 bits = 104 Base64 chars
> So the next Base64 char (105) belongs completely to the bitmap file data
> and you don't experience any problem if you use the Mid function to cut
> the string by 105th char and then FromBase64String to convert the Base64
> string back to a byte() (byte array).
> However if you use Spanish applications, the OLE header is 88 bytes (704
> bits). If you try to convert that stream of bits into a Base64 string:
> 740 bits / 6 bits each Base64 char = 123,333... chars
> What does it mean? It means that it will use 123 chars and 2 bits of the
> next char. The other 4 bits of the 124th Base64 char will have data
> belonging to the bitmap file. And here is the tricky part: You CANNOT
> remove the OLE header without turning the whole string into rubbish
> because of this. Thats why you always see red crosses on your reports no
> matter the number you use to remove the OLE header from the Base64 string.
> No matter if you use 105, 104, 103, or 123, 124, etc. It will always break
> the Base64 string and when you convert it back to get the image you will
> get just rubish.
>
> THE SOLUTION:
> Instead of using Base64 strings to cut the OLE header and try to rebuild
> the image, you should use this piece of code in your Image RS components:
> =System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),XXX))
> Where XXX is the size of the localised OLE header + 1.
> For Spanish use XXX = 89
> For English use XXX = 79
> For other languages you will have to guess (you can do some trial/error
> until you find out).
> Why is there this difference of sizes in the OLE header? The header
> contains the type description of the file (the type column when you see
> the file using explorer) using your local language. In Spanish, the
> default value for a bitmap file is 'Imagen de mapa de bits' (22 bytes).
> However in English the type description for that kind of files (I suppose)
> is 'Bitmap image' (12 bytes). Hence:
> 22 - 12 = 10
> The OLE header is 10 bytes larger for spanish applications (in the case of
> .bmp files). For other type of files the size probably differs (I have not
> tested it).
> Instead of trial/error to find the XXX value for your local languae you
> can try to do this little calculation:
> 78 + ( length(bitmap type description for your language) - 12) + 1
> That's all. Just use:
> =System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),XXX))
> being XXX the value you have just calculated and forget about the original
> Adam's code.
> I hope this could help to people experiencing this problem and save their
> valuable time. I also hope that Adam Cogan revises his article to replace
> his version of the code and explains the restrictions of this alternative
> also. People tend to consider documentation under microsoft.com domain to
> be fully trustable, but in this case the information was totally wrong for
> users that are not using English localised applications.
> Regards.
>

Database Design. Need Advice. Thank You.

Hello,
I am creating a database where:
- I have a Blogs and Folders system.
- Use a common design so I can implement new systems in the future.
Users, Comments, Ratings, View, Tags and Categories are tables common
to all systems, i.e., used by Posts and Files in Blogs and Folders.
- One Tag or Category can be associated to many Posts or Files.
- One Comment, View or Rating should be only associated to one Post or
one File. I am missing this ... (1)
Relations between a File / Folder and Comments / Ratings / View /
Tags / Categories are done using FilesRatings, FoldersViews, etc.
I am using UniqueIdentifier as Primary Keys.
I checked ASP.NET Membership tables, a few articles and few features
in my project, such as renaming files with the GUID of their records.
I didn't decided yet for INT or UNIQUEIDENTIFIER.
I am looking for some feedback on the design of my database.
One thing I think need to improve is mentioned in (1)
But any advices to improve it would be great.
Thank You,
Miguel
My Database Script:
-- Users ...
create table dbo.Users
(
UserID uniqueidentifier not null
constraint PK_User primary key clustered,
[Name] nvarchar(200) not null,
Email nvarchar(200) null,
UpdatedDate datetime not null
)
-- Categories ...
create table dbo.Categories
(
CategoryID uniqueidentifier not null
constraint PK_Category primary key clustered,
[Name] nvarchar(100) not null
)
-- Comments ...
create table dbo.Comments
(
CommentID uniqueidentifier not null
constraint PK_Comment primary key clustered,
AuthorID uniqueidentifier not null,
Title nvarchar(400) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
constraint FK_Comments_Users
foreign key(AuthorID)
references dbo.Users(UserID)
)
-- Ratings ...
create table dbo.Ratings
(
RatingID uniqueidentifier not null
constraint PK_Rating primary key clustered,
AuthorID uniqueidentifier not null,
Value float not null,
constraint FK_Ratings_Users
foreign key(AuthorID)
references dbo.Users(UserID)
)
-- Tags ...
create table dbo.Tags
(
TagID uniqueidentifier not null
constraint PK_Tag primary key clustered,
[Name] nvarchar(100) not null
)
-- Views ...
create table dbo.Views
(
ViewID uniqueidentifier not null
constraint PK_View primary key clustered,
Ticket [datetime] not null
)
-- Blogs ...
create table dbo.Blogs
(
BlogID uniqueidentifier not null
constraint PK_Blog primary key clustered,
Title nvarchar(400) null,
Description nvarchar(2000) null,
CreatedDate datetime null
)
-- Posts ...
create table dbo.Posts
(
PostID uniqueidentifier not null
constraint PK_Post primary key clustered,
BlogID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nchar(1000) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
IsPublished bit not null,
constraint FK_Posts_Blogs
foreign key(BlogID)
references dbo.Blogs(BlogID)
on delete cascade,
constraint FK_Posts_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade
)
-- PostsCategories ...
create table dbo.PostsCategories
(
PostID uniqueidentifier not null,
CategoryID uniqueidentifier not null,
constraint PK_PostsCategories
primary key clustered (PostID, CategoryID),
constraint FK_PostsCategories_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsCategories_Categories
foreign key(CategoryID)
references dbo.Categories(CategoryID)
)
-- PostsComments ...
create table dbo.PostsComments
(
PostID uniqueidentifier not null,
CommentID uniqueidentifier not null,
constraint PK_PostsComments
primary key clustered (PostID, CommentID),
constraint FK_PostsComments_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsComments_Comments
foreign key(CommentID)
references dbo.Comments(CommentID)
on delete cascade
)
-- PostsRatings ...
create table dbo.PostsRatings
(
PostID uniqueidentifier not null,
RatingID uniqueidentifier not null,
constraint PK_PostsRatings
primary key clustered (PostID, RatingID),
constraint FK_PostsRatings_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsRatings_Ratings
foreign key(RatingID)
references dbo.Ratings(RatingID)
on delete cascade
)
-- PostsTags ...
create table dbo.PostsTags
(
PostID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_PostsTags
primary key clustered (PostID, TagID),
constraint FK_PostsTags_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
-- PostsViews ...
create table dbo.PostsViews
(
PostID uniqueidentifier not null,
ViewID uniqueidentifier not null,
constraint PK_PostsViews
primary key clustered (PostID, ViewID),
constraint FK_PostsViews_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsViews_Views
foreign key(ViewID)
references dbo.Views(ViewID)
on delete cascade
)
-- Folders ...
create table dbo.Folders
(
FolderID uniqueidentifier not null
constraint PK_Folder primary key clustered,
[Name] nvarchar(100) null,
Description nvarchar(2000) null,
CreatedDate datetime not null,
URL nvarchar(400) not null
)
-- Files ...
create table dbo.Files
(
FileID uniqueidentifier not null
constraint PK_File primary key clustered,
FolderID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nvarchar(400) null,
Description nvarchar(2000) null,
[Name] nvarchar(100) not null,
URL nvarchar(400) not null,
UpdatedDate datetime not null,
IsPublished bit not null,
Type nvarchar(50) null,
constraint FK_Files_Folders
foreign key(FolderID)
references dbo.Folders(FolderID)
on delete cascade,
constraint FK_Files_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade
)
-- FilesCategories ...
create table dbo.FilesCategories
(
FileID uniqueidentifier not null,
CategoryID uniqueidentifier not null,
constraint PK_FilesCategories
primary key clustered (FileID, CategoryID),
constraint FK_FilesCategories_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesCategories_Categories
foreign key(CategoryID)
references dbo.Categories(CategoryID)
)
-- FilesComments ...
create table dbo.FilesComments
(
FileID uniqueidentifier not null,
CommentID uniqueidentifier not null,
constraint PK_FilesComments
primary key clustered (FileID, CommentID),
constraint FK_FilesComments_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesComments_Comments
foreign key(CommentID)
references dbo.Comments(CommentID)
on delete cascade
)
-- FilesRatings ...
create table dbo.FilesRatings
(
FileID uniqueidentifier not null,
RatingID uniqueidentifier not null,
constraint PK_FilesRatings
primary key clustered (FileID, RatingID),
constraint FK_FilesRatings_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesRatings_Ratings
foreign key(RatingID)
references dbo.Ratings(RatingID)
on delete cascade
)
-- FilesTags ...
create table dbo.FilesTags
(
FileID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_FilesTags
primary key clustered (FileID, TagID),
constraint FK_FilesTags_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
-- FilesViews ...
create table dbo.FilesViews
(
FileID uniqueidentifier not null,
ViewID uniqueidentifier not null,
constraint PK_FilesViews
primary key clustered (FileID, ViewID),
constraint FK_FilesViews_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesViews_Views
foreign key(ViewID)
references dbo.Views(ViewID)
on delete cascade
)
-- Run script
goOn Oct 22, 12:21 am, shapper <mdmo...@.gmail.com> wrote:
> Hello,
> I am creating a database where:
> - I have a Blogs and Folders system.
> - Use a common design so I can implement new systems in the future.
> Users, Comments, Ratings, View, Tags and Categories are tables common
> to all systems, i.e., used by Posts and Files in Blogs and Folders.
> - One Tag or Category can be associated to many Posts or Files.
> - One Comment, View or Rating should be only associated to one Post or
> one File. I am missing this ... (1)
> Relations between a File / Folder and Comments / Ratings / View /
> Tags / Categories are done using FilesRatings, FoldersViews, etc.
> I am using UniqueIdentifier as Primary Keys.
> I checked ASP.NET Membership tables, a few articles and few features
> in my project, such as renaming files with the GUID of their records.
> I didn't decided yet for INT or UNIQUEIDENTIFIER.
> I am looking for some feedback on the design of my database.
> One thing I think need to improve is mentioned in (1)
> But any advices to improve it would be great.
> Thank You,
> Miguel
> My Database Script:
> -- Users ...
> create table dbo.Users
> (
> UserID uniqueidentifier not null
> constraint PK_User primary key clustered,
> [Name] nvarchar(200) not null,
> Email nvarchar(200) null,
> UpdatedDate datetime not null
> )
> -- Categories ...
> create table dbo.Categories
> (
> CategoryID uniqueidentifier not null
> constraint PK_Category primary key clustered,
> [Name] nvarchar(100) not null
> )
> -- Comments ...
> create table dbo.Comments
> (
> CommentID uniqueidentifier not null
> constraint PK_Comment primary key clustered,
> AuthorID uniqueidentifier not null,
> Title nvarchar(400) null,
> Body nvarchar(max) null,
> UpdatedDate datetime not null,
> constraint FK_Comments_Users
> foreign key(AuthorID)
> references dbo.Users(UserID)
> )
> -- Ratings ...
> create table dbo.Ratings
> (
> RatingID uniqueidentifier not null
> constraint PK_Rating primary key clustered,
> AuthorID uniqueidentifier not null,
> Value float not null,
> constraint FK_Ratings_Users
> foreign key(AuthorID)
> references dbo.Users(UserID)
> )
> -- Tags ...
> create table dbo.Tags
> (
> TagID uniqueidentifier not null
> constraint PK_Tag primary key clustered,
> [Name] nvarchar(100) not null
> )
> -- Views ...
> create table dbo.Views
> (
> ViewID uniqueidentifier not null
> constraint PK_View primary key clustered,
> Ticket [datetime] not null
> )
> -- Blogs ...
> create table dbo.Blogs
> (
> BlogID uniqueidentifier not null
> constraint PK_Blog primary key clustered,
> Title nvarchar(400) null,
> Description nvarchar(2000) null,
> CreatedDate datetime null
> )
> -- Posts ...
> create table dbo.Posts
> (
> PostID uniqueidentifier not null
> constraint PK_Post primary key clustered,
> BlogID uniqueidentifier not null,
> AuthorID uniqueidentifier not null,
> Title nchar(1000) null,
> Body nvarchar(max) null,
> UpdatedDate datetime not null,
> IsPublished bit not null,
> constraint FK_Posts_Blogs
> foreign key(BlogID)
> references dbo.Blogs(BlogID)
> on delete cascade,
> constraint FK_Posts_Users
> foreign key(AuthorID)
> references dbo.Users(UserID)
> on delete cascade
> )
> -- PostsCategories ...
> create table dbo.PostsCategories
> (
> PostID uniqueidentifier not null,
> CategoryID uniqueidentifier not null,
> constraint PK_PostsCategories
> primary key clustered (PostID, CategoryID),
> constraint FK_PostsCategories_Posts
> foreign key(PostID)
> references dbo.Posts(PostID)
> on delete cascade,
> constraint FK_PostsCategories_Categories
> foreign key(CategoryID)
> references dbo.Categories(CategoryID)
> )
> -- PostsComments ...
> create table dbo.PostsComments
> (
> PostID uniqueidentifier not null,
> CommentID uniqueidentifier not null,
> constraint PK_PostsComments
> primary key clustered (PostID, CommentID),
> constraint FK_PostsComments_Posts
> foreign key(PostID)
> references dbo.Posts(PostID)
> on delete cascade,
> constraint FK_PostsComments_Comments
> foreign key(CommentID)
> references dbo.Comments(CommentID)
> on delete cascade
> )
> -- PostsRatings ...
> create table dbo.PostsRatings
> (
> PostID uniqueidentifier not null,
> RatingID uniqueidentifier not null,
> constraint PK_PostsRatings
> primary key clustered (PostID, RatingID),
> constraint FK_PostsRatings_Posts
> foreign key(PostID)
> references dbo.Posts(PostID)
> on delete cascade,
> constraint FK_PostsRatings_Ratings
> foreign key(RatingID)
> references dbo.Ratings(RatingID)
> on delete cascade
> )
> -- PostsTags ...
> create table dbo.PostsTags
> (
> PostID uniqueidentifier not null,
> TagID uniqueidentifier not null,
> constraint PK_PostsTags
> primary key clustered (PostID, TagID),
> constraint FK_PostsTags_Posts
> foreign key(PostID)
> references dbo.Posts(PostID)
> on delete cascade,
> constraint FK_PostsTags_Tags
> foreign key(TagID)
> references dbo.Tags(TagID)
> )
> -- PostsViews ...
> create table dbo.PostsViews
> (
> PostID uniqueidentifier not null,
> ViewID uniqueidentifier not null,
> constraint PK_PostsViews
> primary key clustered (PostID, ViewID),
> constraint FK_PostsViews_Posts
> foreign key(PostID)
> references dbo.Posts(PostID)
> on delete cascade,
> constraint FK_PostsViews_Views
> foreign key(ViewID)
> references dbo.Views(ViewID)
> on delete cascade
> )
> -- Folders ...
> create table dbo.Folders
> (
> FolderID uniqueidentifier not null
> constraint PK_Folder primary key clustered,
> [Name] nvarchar(100) null,
> Description nvarchar(2000) null,
> CreatedDate datetime not null,
> URL nvarchar(400) not null
> )
> -- Files ...
> create table dbo.Files
> (
> FileID uniqueidentifier not null
> constraint PK_File primary key clustered,
> FolderID uniqueidentifier not null,
> AuthorID uniqueidentifier not null,
> Title nvarchar(400) null,
> Description nvarchar(2000) null,
> [Name] nvarchar(100) not null,
> URL nvarchar(400) not null,
> UpdatedDate datetime not null,
> IsPublished bit not null,
> Type nvarchar(50) null,
> constraint FK_Files_Folders
> foreign key(FolderID)
> references dbo.Folders(FolderID)
> on delete cascade,
> constraint FK_Files_Users
> foreign key(AuthorID)
> references dbo.Users(UserID)
> on delete cascade
> )
> -- FilesCategories ...
> create table dbo.FilesCategories
> (
> FileID uniqueidentifier not null,
> CategoryID uniqueidentifier not null,
> constraint PK_FilesCategories
> primary key clustered (FileID, CategoryID),
> constraint FK_FilesCategories_Files
> foreign key(FileID)
> references dbo.Files(FileID)
> on delete cascade,
> constraint FK_FilesCategories_Categories
> foreign key(CategoryID)
> references dbo.Categories(CategoryID)
> )
> -- FilesComments ...
> create table dbo.FilesComments
> (
> FileID uniqueidentifier not null,
> CommentID uniqueidentifier not null,
> constraint PK_FilesComments
> primary key clustered (FileID, CommentID),
> constraint FK_FilesComments_Files
> foreign key(FileID)
> references dbo.Files(FileID)
> on delete cascade,
> constraint FK_FilesComments_Comments
> foreign key(CommentID)
> references dbo.Comments(CommentID)
> on delete cascade
> )
> -- FilesRatings ...
> create table dbo.FilesRatings
> (
> FileID uniqueidentifier not null,
> RatingID uniqueidentifier not null,
> constraint PK_FilesRatings
> primary key clustered (FileID, RatingID),
> constraint FK_FilesRatings_Files
> foreign key(FileID)
> references dbo.Files(FileID)
> on delete cascade,
> constraint FK_FilesRatings_Ratings
> foreign key(RatingID)
> references dbo.Ratings(RatingID)
> on delete cascade
> )
> -- FilesTags ...
> create table dbo.FilesTags
> (
> FileID uniqueidentifier not null,
> TagID uniqueidentifier not null,
> constraint PK_FilesTags
> primary key clustered (FileID, TagID),
> constraint FK_FilesTags_Files
> foreign key(FileID)
> references dbo.Files(FileID)
> on delete cascade,
> constraint FK_FilesTags_Tags
> foreign key(TagID)
> references dbo.Tags(TagID)
> )
> -- FilesViews ...
> create table dbo.FilesViews
> (
> FileID uniqueidentifier not null,
> ViewID uniqueidentifier not null,
> constraint PK_FilesViews
> primary key clustered (FileID, ViewID),
> constraint FK_FilesViews_Files
> foreign key(FileID)
> references dbo.Files(FileID)
> on delete cascade,
> constraint FK_FilesViews_Views
> foreign key(ViewID)
> references dbo.Views(ViewID)
> on delete cascade
> )
> -- Run script
> go
Please, anyone?
Does someone sees a way to improve this?
Thanks,
Miguel