Showing posts with label schema. Show all posts
Showing posts with label schema. Show all posts

Friday, March 30, 2012

Mapping two schema definitions to one column

Hi,
I have a xml data file that looks like this
<CT_DATASET>
<DATE-INTERVENTIONDATE>
<YEARS>0</YEARS>
<MONTHS>0</MONTHS>
<DAYS>-1</DAYS>
</DATE-INTERVENTIONDATE>
<DESCTEXT>Preoperative CT Diagnosis</DESCTEXT>
<NUMBER_OF_SLICES>58</NUMBER_OF_SLICES>
<DICOM_TAGS>
<TAGNR>00080014</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080016</TAGNR>
<TAGVALUE>1.2.840.10008.5.1.4.1.1.4</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080018</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102.0.2187.8.1.0.7</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080070</TAGNR>
<TAGVALUE>Toshiba </TAGVALUE>
</DICOM_TAGS>
</CT_DATASET>
And I want to map the multiple tagnr and tagvalue to one column for the
patient.
My schema file looks liek this:
<xs:element name="CT_DATASET" sql:relation="CT_DATA"
sql:overflow-field="ADDITIONAL">
<xs:complexType>
<xs:sequence>
<xs:element ref="DATE-INTERVENTIONDATE" />
<xs:element ref="DESCTEXT" />
<xs:element ref="NUMBER_OF_SLICES" />
<xs:element minOccurs="0" maxOccurs="unbounded" ref="DICOM_TAGS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="NUMBER_OF_SLICES" type="xs:string" />
<xs:element name="DICOM_TAGS" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DATE-INTERVENTIONDATE" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="YEARS" />
<xs:element ref="MONTHS" />
<xs:element ref="DAYS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="YEARS" type="xs:string" sql:field="years"/>
<xs:element name="MONTHS" type="xs:string" sql:field="months" />
<xs:element name="DAYS" type="xs:string" sql:field="days"/>
<xs:element name="TAGNR" sql:field="TAG_NO:"/>
<xs:element name="TAGVALUE" sql:field="TAG VALUE"/>
</xs:schema>
I get an error which says:
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Does anybody know a way around. Any help would be appreciated. Please
do help!!!Hello Prakruthi,
Are you trying to use this as a validation schema in an XML Schema collectio
n
or a XDR for mapping queries?

> I get an error which says:
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Does anybody know a way around. Any help would be appreciated. Please
> do help!!!
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
Im trying to use this as a validation schema. And as you can see from
the xml data , each patient has multiple dicom tags and numbers. I want
to be able to map all these numbers into a single column. Is this
possible?|||Hi Kent,
I forgot to mention that Tag Numbers and Tag values are two separate
columns. But each column should be able to have multiple values.
Regards,
Prakruthi|||Hello Prakruthi,
Part of problem is that the schema you posted isn't a valid XSD schema:
a. The sql namespace (aliased by sql:) isn't defined by your schema.
b. The schema has some other problems, so I rewrote it -- sans the SQL parts
since I'm not sure what these are or do in your processor.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="q
ualified">
<xs:element name="CT_DATASET">
<xs:complexType>
<xs:sequence>
<xs:element name="DATE-INTERVENTIONDATE">
<xs:complexType>
<xs:sequence>
<xs:element name="YEARS" type="xs:string"/>
<xs:element name="MONTHS" type="xs:string"/>
<xs:element name="DAYS" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="NUMBER_OF_SLICES" type="xs:string"/>
<xs:element ref="DICOM_TAGS" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DICOM_TAGS">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="TAGNR" type="xs:string"/>
<xs:element name="TAGVALUE" type="xs:string"/>
</xs:schema>
I was able to catalog is into a SQL Sever 2005 XML Schema Collection succesf
ully.
Does any of that help?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Dear Kent,
Thanks for reorganising my schema in the right manner. But I dont think
you understood what I meant to say. Even when I used your modified
schema I could not map the three tagnr and tagvalues associated with
one one single patient into the tagnr and tagvalue column.I get an
error that says
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Is it not possible to map three different values to one column of the
same row? I hope you can help me out again this time.
Regards,
Prakruthi|||Hello Prakruthi,
That's why I asked if you were using this an mapping schema or as a validati
on
schema. You said "validation," so I fixed it for that. However, I don't beli
eve
you can target more than one value into a column using a single mapping.
Sadly, I don't have the time to work on this today before going on the road
for the next couple of w. However, it looks like Michael is back online,
so he might be able to help more.:)

> Thanks for reorganising my schema in the right manner. But I dont
> think
> you understood what I meant to say. Even when I used your modified
> schema I could not map the three tagnr and tagvalues associated with
> one one single patient into the tagnr and tagvalue column.I get an
> error that says
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Is it not possible to map three different values to one column of the
> same row? I hope you can help me out again this time.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi|||I have forwarded it to our mapping schema experts and I hope they will get
back to you.
Ping me if that is not the case.
Michael
"Prakruthi" <prakruthi.rao@.gmail.com> wrote in message
news:1143210046.980577.250430@.e56g2000cwe.googlegroups.com...
> Hi Kent,
> I wanted to say im not using it as a validation schema. I apologise for
> the typo error. I hope micheal would help me out with this issue.
> Regards,
> Prakruthi
>

Mapping two schema definitions to one column

Hi,
I have a xml data file that looks like this
<CT_DATASET>
<DATE-INTERVENTIONDATE>
<YEARS>0</YEARS>
<MONTHS>0</MONTHS>
<DAYS>-1</DAYS>
</DATE-INTERVENTIONDATE>
<DESCTEXT>Preoperative CT Diagnosis</DESCTEXT>
<NUMBER_OF_SLICES>58</NUMBER_OF_SLICES>
<DICOM_TAGS>
<TAGNR>00080014</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080016</TAGNR>
<TAGVALUE>1.2.840.10008.5.1.4.1.1.4</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080018</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102.0.2187.8.1.0.7< /TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080070</TAGNR>
<TAGVALUE>Toshiba </TAGVALUE>
</DICOM_TAGS>
</CT_DATASET>
And I want to map the multiple tagnr and tagvalue to one column for the
patient.
My schema file looks liek this:
<xs:element name="CT_DATASET" sql:relation="CT_DATA"
sql:overflow-field="ADDITIONAL">
<xs:complexType>
<xs:sequence>
<xs:element ref="DATE-INTERVENTIONDATE" />
<xs:element ref="DESCTEXT" />
<xs:element ref="NUMBER_OF_SLICES" />
<xs:element minOccurs="0" maxOccurs="unbounded" ref="DICOM_TAGS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="NUMBER_OF_SLICES" type="xs:string" />
<xs:element name="DICOM_TAGS" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DATE-INTERVENTIONDATE" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="YEARS" />
<xs:element ref="MONTHS" />
<xs:element ref="DAYS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="YEARS" type="xs:string" sql:field="years"/>
<xs:element name="MONTHS" type="xs:string" sql:field="months" />
<xs:element name="DAYS" type="xs:string" sql:field="days"/>
<xs:element name="TAGNR" sql:field="TAG_NO:"/>
<xs:element name="TAGVALUE" sql:field="TAG VALUE"/>
</xs:schema>
I get an error which says:
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Does anybody know a way around. Any help would be appreciated. Please
do help!!!
Hello Prakruthi,
Are you trying to use this as a validation schema in an XML Schema collection
or a XDR for mapping queries?

> I get an error which says:
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Does anybody know a way around. Any help would be appreciated. Please
> do help!!!
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hi Kent,
Im trying to use this as a validation schema. And as you can see from
the xml data , each patient has multiple dicom tags and numbers. I want
to be able to map all these numbers into a single column. Is this
possible?
|||Hi Kent,
I forgot to mention that Tag Numbers and Tag values are two separate
columns. But each column should be able to have multiple values.
Regards,
Prakruthi
|||Hello Prakruthi,
Part of problem is that the schema you posted isn't a valid XSD schema:
a. The sql namespace (aliased by sql isn't defined by your schema.
b. The schema has some other problems, so I rewrote it -- sans the SQL parts
since I'm not sure what these are or do in your processor.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xs:element name="CT_DATASET">
<xs:complexType>
<xs:sequence>
<xs:element name="DATE-INTERVENTIONDATE">
<xs:complexType>
<xs:sequence>
<xs:element name="YEARS" type="xs:string"/>
<xs:element name="MONTHS" type="xs:string"/>
<xs:element name="DAYS" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="NUMBER_OF_SLICES" type="xs:string"/>
<xs:element ref="DICOM_TAGS" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DICOM_TAGS">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="TAGNR" type="xs:string"/>
<xs:element name="TAGVALUE" type="xs:string"/>
</xs:schema>
I was able to catalog is into a SQL Sever 2005 XML Schema Collection succesfully.
Does any of that help?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Dear Kent,
Thanks for reorganising my schema in the right manner. But I dont think
you understood what I meant to say. Even when I used your modified
schema I could not map the three tagnr and tagvalues associated with
one one single patient into the tagnr and tagvalue column.I get an
error that says
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Is it not possible to map three different values to one column of the
same row? I hope you can help me out again this time.
Regards,
Prakruthi
|||Hello Prakruthi,
That's why I asked if you were using this an mapping schema or as a validation
schema. You said "validation," so I fixed it for that. However, I don't believe
you can target more than one value into a column using a single mapping.
Sadly, I don't have the time to work on this today before going on the road
for the next couple of week. However, it looks like Michael is back online,
so he might be able to help more.

> Thanks for reorganising my schema in the right manner. But I dont
> think
> you understood what I meant to say. Even when I used your modified
> schema I could not map the three tagnr and tagvalues associated with
> one one single patient into the tagnr and tagvalue column.I get an
> error that says
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Is it not possible to map three different values to one column of the
> same row? I hope you can help me out again this time.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi
|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi
|||I have forwarded it to our mapping schema experts and I hope they will get
back to you.
Ping me if that is not the case.
Michael
"Prakruthi" <prakruthi.rao@.gmail.com> wrote in message
news:1143210046.980577.250430@.e56g2000cwe.googlegr oups.com...
> Hi Kent,
> I wanted to say im not using it as a validation schema. I apologise for
> the typo error. I hope micheal would help me out with this issue.
> Regards,
> Prakruthi
>

Mapping schema for GoogleBase XSD

I want to load into a SQL Server table with SQLXMLBulkload an xml file that
is formatted with google base xsd (http://base.google.com/base/base.xsd). I
need a mapping schema for that, correct? To map the elements of the xml to
my table. Does anyone have a mapping schema for this?
Steve
Hi Steve
The mapping depends on the database tables to which you want to map it to.
Best regards
Michael
"Steve Mc" <stevemc@.zillow.com> wrote in message
news:OGEN6NOiHHA.5052@.TK2MSFTNGP05.phx.gbl...
>I want to load into a SQL Server table with SQLXMLBulkload an xml file that
>is formatted with google base xsd (http://base.google.com/base/base.xsd).
>I need a mapping schema for that, correct? To map the elements of the xml
>to my table. Does anyone have a mapping schema for this?
> Steve
>
|||Hello,
Please take a look at http://msdn2.microsoft.com/en-us/library/ms172649.aspx
to understand how annotations work.
Let me know if you need further assistance.
Regards,
Monica Frintu
"Steve Mc" wrote:

> I want to load into a SQL Server table with SQLXMLBulkload an xml file that
> is formatted with google base xsd (http://base.google.com/base/base.xsd). I
> need a mapping schema for that, correct? To map the elements of the xml to
> my table. Does anyone have a mapping schema for this?
> Steve
>
>

Mapping schema for GoogleBase XSD

I want to load into a SQL Server table with SQLXMLBulkload an xml file that
is formatted with google base xsd (http://base.google.com/base/base.xsd). I
need a mapping schema for that, correct? To map the elements of the xml to
my table. Does anyone have a mapping schema for this?
SteveHi Steve
The mapping depends on the database tables to which you want to map it to.
Best regards
Michael
"Steve Mc" <stevemc@.zillow.com> wrote in message
news:OGEN6NOiHHA.5052@.TK2MSFTNGP05.phx.gbl...
>I want to load into a SQL Server table with SQLXMLBulkload an xml file that
>is formatted with google base xsd (http://base.google.com/base/base.xsd).
>I need a mapping schema for that, correct? To map the elements of the xml
>to my table. Does anyone have a mapping schema for this?
> Steve
>|||Hello,
Please take a look at http://msdn2.microsoft.com/en-us/library/ms172649.aspx
to understand how annotations work.
Let me know if you need further assistance.
Regards,
--
Monica Frintu
"Steve Mc" wrote:

> I want to load into a SQL Server table with SQLXMLBulkload an xml file tha
t
> is formatted with google base xsd (http://base.google.com/base/base.xsd).
I
> need a mapping schema for that, correct? To map the elements of the xml t
o
> my table. Does anyone have a mapping schema for this?
> Steve
>
>sql

Friday, March 23, 2012

Many to Many Question

SQL2000
I have 3 tables
Customers
TBL_Categories (Master Table of Categories)
CUS_Categories (Many-to-Many Table)
My Schema is something like this:
TBL_Categories
SysID
CategoryName
CUS_Categories
SysID
CategoryID (Sysid in TBL_Categories)
CustomerID
I am looking for a query that will give me a list of all Category Names that
do not exists in CUS_Categories for a specific customer.
i.e. SELECT CategoryName FROM TBL_Categories WHERE (no categories exists for
customer 1)
TIA
Tim Morrison
On Fri, 10 Dec 2004 10:24:40 -0600, Tim Morrison wrote:

>SQL2000
>I have 3 tables
>Customers
>TBL_Categories (Master Table of Categories)
>CUS_Categories (Many-to-Many Table)
>My Schema is something like this:
>TBL_Categories
> SysID
> CategoryName
>CUS_Categories
> SysID
> CategoryID (Sysid in TBL_Categories)
> CustomerID
>I am looking for a query that will give me a list of all Category Names that
>do not exists in CUS_Categories for a specific customer.
>i.e. SELECT CategoryName FROM TBL_Categories WHERE (no categories exists for
>customer 1)
Hi Tim,
SELECT CategoryName
FROM TBL_Categories
WHERE NOT EXISTS
(SELECT *
FROM CUS_Categories
WHERE CUS_Categories.CategoryID = TBL_Categories.SysID
AND CUS_Categories.CustomerID = 1)
(untested)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, March 9, 2012

Managing subscribers from remote machine?

It looks like I can manage mostly everything within NS schema remotely, except subscribers and subscriptions.

The SubscriberEnumeration class wants an NSInstance reference, which only works running on local SQL server. As it reads and writes directly to the registry.

Is there something that I’m over looking to be able to enumerate the subscribers for an instance from a remote machine?

John

Install the client tools and then register the SSNS instance on the remote machine. That'll give you the API and registry entries you'll need.

HTH...

Joe|||

I have looked through the enumeration class. It only works on the local SQL server; I cannot use the class on my client machine and manage the subscriptions remotely with it. I have although, found that for every Instance's database, there are stored procedures that i can call to get a list of subscribers and devices and so fourth.

John

|||The SSNS API can be used from a remote machine. In fact, this is the most common deployment scenario - SSNS running on one server, the backend database running on a separate server, and the Subscription Management Application running on a web server.

To gain access to the remote instance of SSNS you'll need to install the client components and register the instance (pointing it to the remote server that hosts the instance, this will create the necessary registry entries, performance counters for you).

Here are a few links that may help.

http://msdn2.microsoft.com/en-us/library/ms171319.aspx
http://msdn2.microsoft.com/en-us/library/ms171263.aspx
http://msdn2.microsoft.com/en-us/library/ms143466.aspx
http://msdn2.microsoft.com/en-us/library/ms172627.aspx

HTH...

Joe

Saturday, February 25, 2012

Managing Database Schema Changes

I hope this is the correct news group to post this to, if not could someone
please advice me as to a better choice. We are currently releasing V1 of
our product which will include a SQL Server DB. After this point when we
want to provide Service Packs/Rollups/Upgrades/whatever to our clients we
are going to need to manage database changes in some fashion to ensure we
keep the database in sync with the App. I have seen people use source safe
for this, just keeping a script for each change and labeling them so they
get pulled out during the build process. This solution seemed to work well
enough, but I was wondering if anyone in this group had any other
suggestions. As always any advice that can be provided would be greatly
appreciated.
Thank you,
Prescott
"J Prescott Sanders" <nomorespam@.spam.com> schrieb im Newsbeitrag
news:uIemf2GVEHA.3656@.TK2MSFTNGP11.phx.gbl...
> I hope this is the correct news group to post this to, if not could
someone
> please advice me as to a better choice. We are currently releasing V1
of
> our product which will include a SQL Server DB. After this point when
we
> want to provide Service Packs/Rollups/Upgrades/whatever to our clients
we
> are going to need to manage database changes in some fashion to ensure
we
> keep the database in sync with the App. I have seen people use source
safe
> for this, just keeping a script for each change and labeling them so
they
> get pulled out during the build process. This solution seemed to work
well
> enough, but I was wondering if anyone in this group had any other
> suggestions. As always any advice that can be provided would be greatly
> appreciated.
This is a difficult topic: especially if you have to create or change
indexes on an existing database with significant amount of data. This can
easily take very long effectively stopping your application.
Since schema changes (DDL) do not happen within transaction it can be a
problem to deal with failure of schema changing statements. This is
especially true if you not only want to change the schema but also modify
data as part of a schema migration from one version to the next.
IMHO source code control is not sufficient since you'll have to take into
consideration that customers might be upgrading from different schema
versions to the current one. They might have skipped a product version
you released and the next release has to take care of both schema
migration steps.
The ideal solution would be a target schema description and some kind of
tool that compares the actual schema with this target schema and performs
all changes needed for them to be in synch again. Unfortunately I don't
know such a tool but if anybody does know such a tool I'd be glad to get
it to know.
The single golden rule I can give you: plan schema migration carefully and
try to imagine which kind of schema changes you might be doing in the
future. Since you're at the beginning of your migration history, you have
a good change to get it right. Otherwise the whole topic might easily get
out of hand. That of course depends on the nature of your application and
the expected schema changes.
Good luck!
Kind regards
robert
|||Hi Robert,
Try dbMaestro. It's a product that allows comparison, migration and archiving of database schema and data.
this tool can compare the actual schema with this target schema and performs
all changes needed for them to be in synch again.
You can find it here:
http://www.extreme.co.il
"Robert Klemme" wrote:

> "J Prescott Sanders" <nomorespam@.spam.com> schrieb im Newsbeitrag
> news:uIemf2GVEHA.3656@.TK2MSFTNGP11.phx.gbl...
> someone
> of
> we
> we
> we
> safe
> they
> well
> This is a difficult topic: especially if you have to create or change
> indexes on an existing database with significant amount of data. This can
> easily take very long effectively stopping your application.
> Since schema changes (DDL) do not happen within transaction it can be a
> problem to deal with failure of schema changing statements. This is
> especially true if you not only want to change the schema but also modify
> data as part of a schema migration from one version to the next.
> IMHO source code control is not sufficient since you'll have to take into
> consideration that customers might be upgrading from different schema
> versions to the current one. They might have skipped a product version
> you released and the next release has to take care of both schema
> migration steps.
> The ideal solution would be a target schema description and some kind of
> tool that compares the actual schema with this target schema and performs
> all changes needed for them to be in synch again. Unfortunately I don't
> know such a tool but if anybody does know such a tool I'd be glad to get
> it to know.
> The single golden rule I can give you: plan schema migration carefully and
> try to imagine which kind of schema changes you might be doing in the
> future. Since you're at the beginning of your migration history, you have
> a good change to get it right. Otherwise the whole topic might easily get
> out of hand. That of course depends on the nature of your application and
> the expected schema changes.
> Good luck!
> Kind regards
> robert
>
|||Robert,
Thanks for the quick response. It seems you have quite a bit of experience
on this subject. Can you comment at all on the processes you have in place
to manage database changes within your own system. Any information will
certainly be appreciated as I work on a solution for our system.
Thanks in advance.
Prescott
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:2jdkn2F10ja1kU1@.uni-berlin.de...
> "J Prescott Sanders" <nomorespam@.spam.com> schrieb im Newsbeitrag
> news:uIemf2GVEHA.3656@.TK2MSFTNGP11.phx.gbl...
> someone
> of
> we
> we
> we
> safe
> they
> well
> This is a difficult topic: especially if you have to create or change
> indexes on an existing database with significant amount of data. This can
> easily take very long effectively stopping your application.
> Since schema changes (DDL) do not happen within transaction it can be a
> problem to deal with failure of schema changing statements. This is
> especially true if you not only want to change the schema but also modify
> data as part of a schema migration from one version to the next.
> IMHO source code control is not sufficient since you'll have to take into
> consideration that customers might be upgrading from different schema
> versions to the current one. They might have skipped a product version
> you released and the next release has to take care of both schema
> migration steps.
> The ideal solution would be a target schema description and some kind of
> tool that compares the actual schema with this target schema and performs
> all changes needed for them to be in synch again. Unfortunately I don't
> know such a tool but if anybody does know such a tool I'd be glad to get
> it to know.
> The single golden rule I can give you: plan schema migration carefully and
> try to imagine which kind of schema changes you might be doing in the
> future. Since you're at the beginning of your migration history, you have
> a good change to get it right. Otherwise the whole topic might easily get
> out of hand. That of course depends on the nature of your application and
> the expected schema changes.
> Good luck!
> Kind regards
> robert
>
|||"J Prescott Sanders" <nomorespam@.spam.com> schrieb im Newsbeitrag
news:OmmcrXHVEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Robert,
> Thanks for the quick response. It seems you have quite a bit of
experience
> on this subject. Can you comment at all on the processes you have in
place
> to manage database changes within your own system. Any information will
> certainly be appreciated as I work on a solution for our system.
Well, what we do is basically store a numeric schema version number in
some table and check on startup, whether the schema version of the
software is more recent than the schema version found. If it is, we do
all single upgrade steps that we defined for each version hop.
Since a lot of legacy code is involved, things are a bit difficult
sometimes. I'd say a crucial thing to do is have several phases for
schema upgrade: a read only phase that determines whether preconditions
are satisfied (i.e. whether the schema at hand is in fact the one that it
claims to be). Only if the verification passes successfully, the next
phase is started, which modifies the dataabase. Alternatively you can do
a full backup and restore that if modifications fail.
The other factor that makes things hard for us is that we don't support a
single database.
And there's another gotcha: if you provide error fixing patches for older
versions of the software that included schema changes, then tracking of
schema versions and changes becomes really hard, because you need to
incorporate the change in the newest head version also but you have to
make sure it's not applied twice. Example:
1.0 version of software is released
2.0 version of software is released
2.0 version of software is released
1.1 version of software is released that contains a schema bug fix
Now you'll have to make sure that 3.0 checks on installation whether the
1.1 schema fix was incorporated into the db or not. You get the picture.
Kind regards
robert
[vbcol=seagreen]
> Thanks in advance.
> Prescott
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:2jdkn2F10ja1kU1@.uni-berlin.de...
V1[vbcol=seagreen]
when[vbcol=seagreen]
clients[vbcol=seagreen]
ensure[vbcol=seagreen]
source[vbcol=seagreen]
work[vbcol=seagreen]
greatly[vbcol=seagreen]
can[vbcol=seagreen]
a[vbcol=seagreen]
modify[vbcol=seagreen]
into[vbcol=seagreen]
version[vbcol=seagreen]
of[vbcol=seagreen]
performs[vbcol=seagreen]
don't[vbcol=seagreen]
get[vbcol=seagreen]
and[vbcol=seagreen]
have[vbcol=seagreen]
get[vbcol=seagreen]
and
>