Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error
How to generate or modify GalleryServerProSchema.xml ?
Options
Previous Topic Next Topic
marty1101
#1 Posted : Friday, 1 May 2009 2:03:15 PM(UTC)
Rank: Advanced Member

Joined: 4/01/2009(UTC)
Posts: 37
Location: Taipei, Taiwan
I added a new table in MSSQL database called gs_ExternalThumbnail. It would be nice to backup/restore this table as well in GSP's admin backup/restore function.

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[gs_ExternalThumbnail](
    [ExternalThumbnailUid] [int] IDENTITY(1,1) NOT NULL,
    [MediaObjectId] [int] NOT NULL,
    [ThumbnailUrl] [nvarchar](max) NOT NULL,
    [ImageUrl] [nvarchar](max) NOT NULL CONSTRAINT [DF_gs_ExternalThumbnail_ImageUrl] DEFAULT (''),
    [LinkBackUrl] [nvarchar](max) NOT NULL CONSTRAINT [DF_gs_ExternalThumbnail_LinkBackUrl] DEFAULT (''),
    [host_uid] [int] NOT NULL CONSTRAINT [DF_gs_ExternalThumbnail_seq] DEFAULT ((0)),
    [CreatedBy] [nvarchar](256) NOT NULL CONSTRAINT [DF_gs_ExternalThumbnail_CreatedBy] DEFAULT (''),
    [DateAdded] [datetime] NOT NULL CONSTRAINT [DF_gs_ExternalThumbnail_DateAdded] DEFAULT (((1971)/(11))/(1)),
    [LastModifiedBy] [nvarchar](256) NOT NULL CONSTRAINT [DF_gs_ExternalThumbnail_LastModifiedBy] DEFAULT (''),
    [DateLastModified] [datetime] NOT NULL CONSTRAINT [DF_gs_ExternalThumbnail_DateLastModified] DEFAULT (((1971)/(11))/(1)),
CONSTRAINT [PK_gs_ExternalThumbnail] PRIMARY KEY CLUSTERED
(
    [ExternalThumbnailUid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[gs_ExternalThumbnail] WITH CHECK ADD CONSTRAINT [FK_gs_ExternalThumbnail_gs_MediaObject] FOREIGN KEY([MediaObjectId])
REFERENCES [dbo].[gs_MediaObject] ([MediaObjectId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[gs_ExternalThumbnail] CHECK CONSTRAINT [FK_gs_ExternalThumbnail_gs_MediaObject]


Tracing the code to asm.GetManifestResourceStream("GalleryServerPro.Data.SqlServer.GalleryServerProSchema.xml") in
TIS.GSP.Data.SqlServer\DataExporter.cs
Code:

internal static void ExportData(string filePath, bool exportMembershipData, bool exportGalleryData)
{
    DataSet ds = new DataSet("GalleryServerData");

    System.Reflection.Assembly asm = System.Reflection.Assembly.GetExecutingAssembly();
    using (System.IO.Stream stream = asm.GetManifestResourceStream("GalleryServerPro.Data.SqlServer.GalleryServerProSchema.xml"))
    {
        ds.ReadXmlSchema(stream);
    }


I think a section describing the gs_ExternalThumbnail needs to be in
TIS.GSP.Data.SqlServer\GalleryServerProSchema.xml

It looks like a generated file. I tried through VS2008 and googled with no significant answer. I tried Altova XML Spy's "Create XML Schema from DB Structure" and the generated xml is not exactly like GalleryServerProSchema.xml

A little pointing of direction is appreciated.
Back to top
Roger Martin
#2 Posted : Saturday, 2 May 2009 1:50:43 AM(UTC)
Roger Martin

Rank: Administration

Joined: 3/08/2007(UTC)
Posts: 3,298
Location: Fort Atkinson, WI
Yes, you need to update GalleryServerProSchema.xml and then recompile to add it as an embedded resource.

I generated the file by using the WriteXmlSchema method of the DataSet object. In the source code, open the DataExporter class in the TIS.GSP.Data.SqlServer project. Look at the Export Data method. Notice at the end there are these lines:

Code:
ds.WriteXml(filePath, XmlWriteMode.WriteSchema);
//ds.WriteXmlSchema(filePath);


To create the XML file, I temporarily switched these two, like this:

Code:
//ds.WriteXml(filePath, XmlWriteMode.WriteSchema);
ds.WriteXmlSchema(filePath);


In your case, before you do that, you should modify the earlier part of the method to include your new table, probably like this:

Code:
string[] gs_TableNames = new string[] { "gs_Album", "gs_Gallery", "gs_MediaObject", "gs_MediaObjectMetadata", "gs_Role", "gs_Role_Album", "gs_SchemaVersion", "gs_ExternalThumbnail" };


And update the stored procedure gs_ExportGalleryData to include the table.

After you made the change and generated a new schema, you can probably copy and paste the XML for the new table into GalleryServerProSchema.xml.

Or you can skip all these steps and try to generate the XML manually by copying another table in the schema and modifying as needed.

And of course you'll need to modify the import code, but that should be pretty straightforward.

Hope this helps,
Roger Martin
Creator and Lead Developer of Gallery Server Pro
Back to top
WWW
marty1101
#3 Posted : Saturday, 2 May 2009 1:42:37 PM(UTC)
Rank: Advanced Member

Joined: 4/01/2009(UTC)
Posts: 37
Location: Taipei, Taiwan
Thank you very much. I could never thought of generating the file by asp.net codes.

I did manually generate the XML by referencing the gs_MediaObject section and got it to work. However, the generated XML is slightly different from what I wrote:

By hand:
Code:

<xs:element name="gs_ExternalThumbnail">
<xs:complexType>
<xs:sequence>
<xs:element name="ExternalThumbnailUid" msdata:ReadOnly="true" msdata:AutoIncrement="true" type="xs:int" />
<xs:element name="MediaObjectId" type="xs:int" />
<xs:element name="ThumbnailUrl" type="xs:string" />
<xs:element name="ImageUrl" type="xs:string" />
<xs:element name="LinkBackUrl" type="xs:string" />
<xs:element name="host_uid" type="xs:int" />
<xs:element name="CreatedBy">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="256" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DateAdded" type="xs:dateTime" />
<xs:element name="LastModifiedBy">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="256" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DateLastModified" type="xs:dateTime" />
</xs:sequence>
</xs:complexType>
</xs:element>


Generated XML:
Code:

<xs:element name="gs_ExternalThumbnail">
<xs:complexType>
<xs:sequence>
<xs:element name="ExternalThumbnailUid" msdata:ReadOnly="true" msdata:AutoIncrement="true" type="xs:int" />
<xs:element name="MediaObjectId" type="xs:int" />
<xs:element name="ThumbnailUrl">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2147483647" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ImageUrl">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2147483647" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="LinkBackUrl">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="2147483647" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="host_uid" type="xs:int" />
<xs:element name="CreatedBy">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="256" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DateAdded" type="xs:dateTime" />
<xs:element name="LastModifiedBy">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="256" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DateLastModified" type="xs:dateTime" />
</xs:sequence>
</xs:complexType>
</xs:element>


The differences are the string descriptions. I set those strings to nvarchar(max) in the database table. This can't be found in you database design. I was wondering if not setting the restriction would make it to work, but it did anyway. It's still nice to know how to generate the XML for future uses and for my learning GSP. Human errors would be annoying to debug in such situation. and a more complicated (or long) table would make manual input more difficult.

Regarding the nvarchar(max), can you share why you don't set strings to max? Well, I set it max because it may never go beyond a set limit; and because I didn't have much experience of database design best practices.
Back to top
Roger Martin
#4 Posted : Sunday, 3 May 2009 1:13:29 AM(UTC)
Roger Martin

Rank: Administration

Joined: 3/08/2007(UTC)
Posts: 3,298
Location: Fort Atkinson, WI
The only reason I haven't used nvarchar(max) is because it is not supported in SQL Server 2000. However, for 2.3, I had to use it to define some of the columns in the new error log table because some items such as stack trace can easily be more than 4000 characters.
Roger Martin
Creator and Lead Developer of Gallery Server Pro
Back to top
WWW
marty1101
#5 Posted : Sunday, 3 May 2009 12:24:21 PM(UTC)
Rank: Advanced Member

Joined: 4/01/2009(UTC)
Posts: 37
Location: Taipei, Taiwan
I see ... SQL2005 was my first time... I'm jumping from no fixed-phone-line to mobile phones, again...
Thank you!
Back to top
Rss Feed  Atom Feed
Users browsing this topic
Guest
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Powered by YAF 1.9.5.5 | YAF © 2003-2011, Yet Another Forum.NET