Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to organize SQL scripts to create database?

6 posts in General Discussion Last posting was on 2007-10-10 17:20:07.0Z
Matt Navarret Posted on 2007-10-04 21:56:46.0Z
Sender: 19ca.46f2e75b.1804289383@sybase.com
From: Matt Navarret
Newsgroups: ianywhere.public.general
Subject: How to organize SQL scripts to create database?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4705619e.57e5.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 Oct 2007 14:56:46 -0700
X-Trace: forums-1-dub 1191535006 10.22.241.41 (4 Oct 2007 14:56:46 -0700)
X-Original-Trace: 4 Oct 2007 14:56:46 -0700, 10.22.241.41
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6339
Article PK: 4710

Currently we have 5 files to recreate our database we keep
in our repository. One that creates all of our tables, one
that creates all of our indexes, one for triggers, one that
has stored procedures functions and events, and one for
views. I am not sure how much good it does to have these
files since in the event of disaster we would go to backups
before anything. Evening being able to recreate the
structure you would have no data. I see having these files
really as more than anything documentation. I was wondering
what is the suggested way to store stuff like this? Unload
that data and keep a copy of the reload.sql files that has
it all in one? Keep seperate files for each table
containing everything to do with the table ie indexes and
triggers? Any suggestions?


Andy Posted on 2007-10-09 12:32:33.0Z
From: "Andy" <retrama@lanetsaf.moc>
Newsgroups: ianywhere.public.general
References: <4705619e.57e5.1681692777@sybase.com>
Subject: Re: How to organize SQL scripts to create database?
Lines: 34
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: fn00.fastenal.com
X-Original-NNTP-Posting-Host: fn00.fastenal.com
Message-ID: <470b74e1$1@forums-1-dub>
Date: 9 Oct 2007 05:32:33 -0700
X-Trace: forums-1-dub 1191933153 205.243.112.50 (9 Oct 2007 05:32:33 -0700)
X-Original-Trace: 9 Oct 2007 05:32:33 -0700, fn00.fastenal.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6363
Article PK: 2854

We unload our remote databases every week and zip up the reload.sql and
*.dat files to store offsite as an extra backup along with nightly logs that
can be translated to sql and applied if necessary. The only reason we do
that instead of copying a regular nightly backup offsite is to save
space/bandwidth.

We also do have seperate table, fk, index, trigger, etc scripts in our
version control system for creating brand new databases since all of our
databases have the same schema. The only problem is that we have to
remember to update these scripts every time we send out any schema changes
to our remote databases. This gets worse the more developers we have and
the more remotes we add, but we have some sanity checks set up to catch
differences.

- Andy

<Matt Navarret> wrote in message news:4705619e.57e5.1681692777@sybase.com...
> Currently we have 5 files to recreate our database we keep
> in our repository. One that creates all of our tables, one
> that creates all of our indexes, one for triggers, one that
> has stored procedures functions and events, and one for
> views. I am not sure how much good it does to have these
> files since in the event of disaster we would go to backups
> before anything. Evening being able to recreate the
> structure you would have no data. I see having these files
> really as more than anything documentation. I was wondering
> what is the suggested way to store stuff like this? Unload
> that data and keep a copy of the reload.sql files that has
> it all in one? Keep seperate files for each table
> containing everything to do with the table ie indexes and
> triggers? Any suggestions?


Shao Chan Posted on 2007-10-09 13:39:48.0Z
From: "Shao Chan" <noemail@noemail.com>
Newsgroups: ianywhere.public.general
References: <4705619e.57e5.1681692777@sybase.com> <470b74e1$1@forums-1-dub>
Subject: Re: How to organize SQL scripts to create database?
Lines: 60
Organization: VT Software
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: i-83-67-79-206.freedom2surf.net
X-Original-NNTP-Posting-Host: i-83-67-79-206.freedom2surf.net
Message-ID: <470b84a4$1@forums-1-dub>
Date: 9 Oct 2007 06:39:48 -0700
X-Trace: forums-1-dub 1191937188 83.67.79.206 (9 Oct 2007 06:39:48 -0700)
X-Original-Trace: 9 Oct 2007 06:39:48 -0700, i-83-67-79-206.freedom2surf.net
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6366
Article PK: 2855

We keep a copy of the database and sql file.

Everytime a change is requested, it is made to the database.

Periodically, the database is frozen for a release and the sql file is
generated. A delta is created between the current database and the previous
time it was frozen. You can use tools such as Power Designer to do the
diffing. This is used to upgrade all customers to the current release. If
a customer is several versions behind, then it applies all the relevant
deltas. However, I guess you could do a straight diff between the two
versions.

Cheers,

Shao

"Andy" <retrama@lanetsaf.moc> wrote in message
news:470b74e1$1@forums-1-dub...
> We unload our remote databases every week and zip up the reload.sql and
> *.dat files to store offsite as an extra backup along with nightly logs
> that can be translated to sql and applied if necessary. The only reason
> we do that instead of copying a regular nightly backup offsite is to save
> space/bandwidth.
>
> We also do have seperate table, fk, index, trigger, etc scripts in our
> version control system for creating brand new databases since all of our
> databases have the same schema. The only problem is that we have to
> remember to update these scripts every time we send out any schema changes
> to our remote databases. This gets worse the more developers we have and
> the more remotes we add, but we have some sanity checks set up to catch
> differences.
>
> - Andy
>
>
> <Matt Navarret> wrote in message
> news:4705619e.57e5.1681692777@sybase.com...
>> Currently we have 5 files to recreate our database we keep
>> in our repository. One that creates all of our tables, one
>> that creates all of our indexes, one for triggers, one that
>> has stored procedures functions and events, and one for
>> views. I am not sure how much good it does to have these
>> files since in the event of disaster we would go to backups
>> before anything. Evening being able to recreate the
>> structure you would have no data. I see having these files
>> really as more than anything documentation. I was wondering
>> what is the suggested way to store stuff like this? Unload
>> that data and keep a copy of the reload.sql files that has
>> it all in one? Keep seperate files for each table
>> containing everything to do with the table ie indexes and
>> triggers? Any suggestions?
>
>


Breck Carter [Team iAnywhere] Posted on 2007-10-05 13:31:21.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: How to organize SQL scripts to create database?
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <v5ecg357rlacmek4cpsi5rr1q2eqe28lki@4ax.com>
References: <4705619e.57e5.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 5 Oct 2007 06:31:21 -0700
X-Trace: forums-1-dub 1191591081 64.7.134.118 (5 Oct 2007 06:31:21 -0700)
X-Original-Trace: 5 Oct 2007 06:31:21 -0700, bcarter.sentex.ca
Lines: 75
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6342
Article PK: 4712

Some general observations...

The current database and each backup store everything that would be
contained in reload.sql file; in other words, you can always recreate
a reload.sql file from the database or a backup.

With this option setting...

SET OPTION PUBLIC.preserve_source_format = 'On';

...all comments embedded inside procedures and triggers will be
preserved in the database and would appear in a recreated reload.sql.

Comments explicitly stored with the COMMENT ON command are also stored
in the database.

Other comments that might appear in the original text files (e.g.,
file change history, comments near but not "inside" CREATE statements,
etc) are not stored in the database.

Without active maintenance, the original CREATE statements used to
create a database often become out of date as the real schema is
subject to ALTER commands. The reload.sql may not have all the
comments, but the schema is 100% accurate as far as reflecting what's
actually running in production.

=====

Some personal behavior...

Personally, I keep track of *all* scripts I have used to create and
alter an ongoing production database. These scripts are frozen upon
successful use so I have a complete audit trail of all changes.

For databases subject to *recreation* (such as embedded databases,
MobiLink remotes), if they cannot be recreated by some automated
process involving a reference database, I keep the master scripts up
to date and ready to use.

All of these scripts include simple dated "change history" comments,
which have more than once answered important questions during
maintenance.

If *any* doubts arise about the accuracy of the scripts (stuff
happens), they are checked against the real database schema.

Dbisql is used for all maintenance tasks, not Sybase Central.

Breck

On 4 Oct 2007 14:56:46 -0700, Matt Navarret wrote:

>Currently we have 5 files to recreate our database we keep
>in our repository. One that creates all of our tables, one
>that creates all of our indexes, one for triggers, one that
>has stored procedures functions and events, and one for
>views. I am not sure how much good it does to have these
>files since in the event of disaster we would go to backups
>before anything. Evening being able to recreate the
>structure you would have no data. I see having these files
>really as more than anything documentation. I was wondering
>what is the suggested way to store stuff like this? Unload
>that data and keep a copy of the reload.sql files that has
>it all in one? Keep seperate files for each table
>containing everything to do with the table ie indexes and
>triggers? Any suggestions?

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


Breck Carter [Team iAnywhere] Posted on 2007-10-10 17:20:07.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: How to organize SQL scripts to create database?
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <d12qg3pnbmmbi6atokkbf5l8v71p84k9qb@4ax.com>
References: <v5ecg357rlacmek4cpsi5rr1q2eqe28lki@4ax.com> <470bf334.7b25.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 10 Oct 2007 10:20:07 -0700
X-Trace: forums-1-dub 1192036807 64.7.134.118 (10 Oct 2007 10:20:07 -0700)
X-Original-Trace: 10 Oct 2007 10:20:07 -0700, bcarter.sentex.ca
Lines: 148
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6386
Article PK: 4731

Some responses below...

On 9 Oct 2007 14:31:32 -0700, Matt Navarret wrote:

>Our database is ran on a windows 2003 server machine. Our
>database is not subject to recreation. Our scripts that we
>have in our repository do not necessarily match our schema.
>Should I update the existing files to match our schema

That's a hard question... if it was me, and if the files were really
out of date, I'd probably start over, perhaps with reload.sql, and
then merge any "intellectual property" (comments, INSERT statements,
etc) that were present in the old script files.

It is not the schema that's important... you can get that from
reload.sql or a tool like PowerDesigner. It is the extra stuff that's
important... the comments, the change history, even the logical
grouping of items close together like indexes-with-tables,
triggers-with-tables, inserts-with-tables, that adds value to these
scripts. If you don't have need of that stuff, then don't bother at
all... the database itself is completely self-describing as far as
schema is concerned.

>and
>while doing so break up the 1 big file into 1 file for each
>table? In the future if I have a script to add 2 tables and
>modify a third should I create 2 script files to add the two
>tables and modify the existing script file that has the
>create table for the third? If I add 2 triggers and a
>procedure should I create them using separate files so that
>I can store them in the repository neatly (triggers in
>trigger folder, procedures in procedure folder)? Or should
>they just be kept in the same script and that script is
>stored in the repository in some generic SQL folder? My
>goal is to get our scripts up to date with our schema and in
>the process organize what we have. We have our code in our
>repository organized nicely so I figured we should do the
>same for our SQL scripts. I am trying to figure out what
>people do in practice. Is it wise to use separate a
>separate script for each table?

What I do, in practice, is maintain a small number of large files,
sometimes just one file for the entire database... definitely not one
file per table, etc.

>Should create index
>statements be stored with create table statements? Does
>this whole idea of organization really matter or do you just
>store whatever the script is that you run simply for an
>audit trail? What if in the script you insert data or
>delete data does that matter? Should you only keep scripts
>that deal with changing the schema?




>> Some general observations...
>>
>> The current database and each backup store everything that
>> would be contained in reload.sql file; in other words, you
>> can always recreate a reload.sql file from the database or
>> a backup.
>>
>> With this option setting...
>>
>> SET OPTION PUBLIC.preserve_source_format = 'On';
>>
>> ...all comments embedded inside procedures and triggers
>> will be preserved in the database and would appear in a
>> recreated reload.sql.
>>
>> Comments explicitly stored with the COMMENT ON command are
>> also stored in the database.
>>
>> Other comments that might appear in the original text
>> files (e.g., file change history, comments near but not
>> "inside" CREATE statements, etc) are not stored in the
>> database.
>>
>> Without active maintenance, the original CREATE statements
>> used to create a database often become out of date as the
>> real schema is subject to ALTER commands. The reload.sql
>> may not have all the comments, but the schema is 100%
>> accurate as far as reflecting what's actually running in
>> production.
>>
>> =====
>>
>> Some personal behavior...
>>
>> Personally, I keep track of *all* scripts I have used to
>> create and alter an ongoing production database. These
>> scripts are frozen upon successful use so I have a
>> complete audit trail of all changes.
>>
>> For databases subject to *recreation* (such as embedded
>> databases, MobiLink remotes), if they cannot be recreated
>> by some automated process involving a reference database,
>> I keep the master scripts up to date and ready to use.
>>
>> All of these scripts include simple dated "change history"
>> comments, which have more than once answered important
>> questions during maintenance.
>>
>> If *any* doubts arise about the accuracy of the scripts
>> (stuff happens), they are checked against the real
>> database schema.
>>
>> Dbisql is used for all maintenance tasks, not Sybase
>> Central.
>>
>> Breck
>>
>>
>>
>> On 4 Oct 2007 14:56:46 -0700, Matt Navarret wrote:
>>
>> >Currently we have 5 files to recreate our database we
>> keep >in our repository. One that creates all of our
>> tables, one >that creates all of our indexes, one for
>> triggers, one that >has stored procedures functions and
>> events, and one for >views. I am not sure how much good
>> it does to have these >files since in the event of
>> disaster we would go to backups >before anything. Evening
>> being able to recreate the >structure you would have no
>> data. I see having these files >really as more than
>> anything documentation. I was wondering >what is the
>> suggested way to store stuff like this? Unload >that data
>> and keep a copy of the reload.sql files that has >it all
>> in one? Keep seperate files for each table >containing
>> everything to do with the table ie indexes and >triggers?
>> Any suggestions?
>>
>> --
>> Breck Carter [Team iAnywhere]
>> RisingRoad SQL Anywhere and MobiLink Professional Services
>> www.risingroad.com
>> The book:
>>
>http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
>> breck.carter@risingroad.com

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com