Re: How do you version your database?

Amit Aronovitch <[email protected]> Mon, 09 May 2011 00:14:50 +0300
Newsgroups	gmane.culture.hackers.israel
Message-ID	<[email protected]>
--------------030409000006060902000800
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 08/05/2011 14:50, Arik Baratz wrote:

>
> On Sat, May 7, 2011 at 8:30 PM, Amit Aronovitch <[email protected] 
> <mailto:[email protected]>> wrote:
>
>
>     OK for the schema, but what about the actual data?
>
>
> Our script loaded the data as well :) If you start from a new database 
> and run the script you would end up with the up-to-date schema loaded 
> with all of the default values etc. All this was maintained by our DBA.
>
> Maybe it is not the most efficient way to do it but it worked for us.
>
>     You need a script for upgrading the data, at least from each
>     production release to the next one. And given a db-dump, you
>     should be able to know which version it belongs to, so you know
>     which script to run.
>     I think it would be best to store some date/version stamp in some
>     table, so you'd never lose track of which schema/code is relevant
>     for the data (once you have that, you can automate and have the
>     upgrade-scripts use this stamp).
>
>
>
> I think what you are talking about is different - a web application or 
> something similar that holds production data.
>

Right. I was thinking of SAS where the database holds the customers' 
valuable data.
But this is also relevant for installable software, where you want the 
user to have an easy way to upgrade the software without losing the data 
he already fed into the system and without having to manually select 
scripts to run according to version.
I guess if you have a backup feature that packages other stuff along 
with the db dump, the version info could be stored outside the dump 
(still it seems safer to keep the version in the db itself, to avoid 
dealing with file formats etc.)

> What I was referring to was a product that was using a database and 
> the programming environments that we created for that. We had no need 
> to version live data on our database.
>

I see. It remains to see which case the OP had ment. :-)

AA


--------------030409000006060902000800
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit




<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
</head>







<body style="background-color: #fff;">
<span style="display:none">&nbsp;</span>

<!--~-|**|PrettyHtmlStartT|**|-~-->
<div id="ygrp-mlmsg" style="position:relative;">
  <div id="ygrp-msg" style="z-index: 1;">
<!--~-|**|PrettyHtmlEndT|**|-~-->

    <div id="ygrp-text" >
      
      
      <p><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

  
  
    <p>On 08/05/2011 14:50,
      Arik Baratz wrote:<br>
    </p>
    <blockquote cite="mid:BANLkTinN5MqA9YJL=hfjoAXWw3EgAiE8cQ-JsoAwUIsXosN+BqQ9rBEUg@public.gmane.org" type="cite">
      
      <!-- |**|begin egp html banner|**| -->
      <br>
      <div dir="ltr">
        <div class="gmail_quote">On Sat, May 7, 2011 at 8:30 PM, Amit
          Aronovitch <span dir="ltr">&lt;<a moz="true" href="mailto:[email protected]">[email protected]</a>&gt;</span>
          wrote:<br>
          <blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204);">
            <div>
              <div dir="ltr">
                <div class="gmail_quote">
                  <div> <br>
                    OK for the schema, but what about the actual data?<br>
                    <br>
                  </div>
                </div>
              </div>
            </div>
          </blockquote>
          <div><br>
          </div>
          <div>Our script loaded the data as well :) If you start from a
            new database and run the script you would end up with the
            up-to-date schema loaded with all of the default values etc.
            All this was maintained by our DBA.</div>
          <div><br>
          </div>
          <div>Maybe it is not the most efficient way to do it but it
            worked for us.</div>
          <div> </div>
          <blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204);">
            <div>
              <div dir="ltr">
                <div class="gmail_quote">
                  <div>You need a script for upgrading the data, at
                    least from each production release to the next one.
                    And given a db-dump, you should be able to know
                    which version it belongs to, so you know which
                    script to run.<br>
                    I think it would be best to store some date/version
                    stamp in some table, so you'd never lose track of
                    which schema/code is relevant for the data (once you
                    have that, you can automate and have the
                    upgrade-scripts use this stamp).<br>
                  </div>
                </div>
              </div>
            </div>
          </blockquote>
          <div><br>
          </div>
          <div><br>
          </div>
          <div>I think what you are talking about is different - a web
            application or something similar that holds production data.</div>
          <div><br>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    Right. I was thinking of SAS where the database holds the customers'
    valuable data.<br>
    But this is also relevant for installable software, where you want
    the user to have an easy way to upgrade the software without losing
    the data he already fed into the system and without having to
    manually select scripts to run according to version. <br>
    I guess if you have a backup feature that packages other stuff along
    with the db dump, the version info could be stored outside the dump
    (still it seems safer to keep the version in the db itself, to avoid
    dealing with file formats etc.)<br>
    <br>
    <blockquote cite="mid:BANLkTinN5MqA9YJL=hfjoAXWw3EgAiE8cQ-JsoAwUIsXosN+BqQ9rBEUg@public.gmane.org" type="cite">
      <div dir="ltr">
        <div class="gmail_quote">
          <div>What I was referring to was a product that was using a
            database and the programming environments that we created
            for that. We had no need to version live data on our
            database.</div>
          <div><br>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    I see. It remains to see which case the OP had ment. :-)<br>
    <br>
    AA<br>
    <br>
  

</p>

    </div>
     

    <!--~-|**|PrettyHtmlStart|**|-~-->
    <div style="color: #fff; height: 0;">__._,_.___</div>

        
  
   
    <div id="ygrp-actbar" style="clear: both; margin-bottom: 10px; white-space: nowrap; color: #666; padding-top: 15px;">
      <div>
        <a href="mailto:[email protected]?subject=Re%3A%20%5Bhackers-il%5D%20How%20do%20you%20version%20your%20database%3F" style="margin-right: 0; padding-right: 0;">
	  Reply to <span style="font-weight: 700;">sender</span></a> |
        <a href="mailto:[email protected]?subject=Re%3A%20%5Bhackers-il%5D%20How%20do%20you%20version%20your%20database%3F">
	  Reply to <span style="font-weight: 700;">group</span></a> |
        	  <a href="http://groups.yahoo.com/group/hackers-il/post;_ylc=X3oDMTJwN3AxNTdiBF9TAzk3MzU5NzE0BGdycElkAzE4NTczMzIEZ3Jwc3BJZAMxNzA1MDA2NzY0BG1zZ0lkAzUxNjAEc2VjA2Z0cgRzbGsDcnBseQRzdGltZQMxMzA0OTQ3MTc3?act=reply&messageNum=5160">Reply <span style="font-weight: 700;">via web post</span></a> |
            	<a href="http://groups.yahoo.com/group/hackers-il/post;_ylc=X3oDMTJlYmNvZnI2BF9TAzk3MzU5NzE0BGdycElkAzE4NTczMzIEZ3Jwc3BJZAMxNzA1MDA2NzY0BHNlYwNmdHIEc2xrA250cGMEc3RpbWUDMTMwNDk0NzE3Nw--" style="font-weight: 700;">Start a New Topic</a>
      </div>

                <a href="http://groups.yahoo.com/group/hackers-il/message/5156;_ylc=X3oDMTM0Nm5rMXE1BF9TAzk3MzU5NzE0BGdycElkAzE4NTczMzIEZ3Jwc3BJZAMxNzA1MDA2NzY0BG1zZ0lkAzUxNjAEc2VjA2Z0cgRzbGsDdnRwYwRzdGltZQMxMzA0OTQ3MTc3BHRwY0lkAzUxNTY-">Messages in this topic</a>
          (<span style="font-weight: 700;">5</span>)
          </div> 
<!------- Start Nav Bar ------>

<!-- |**|begin egp html banner|**| -->
<div id="ygrp-vital" style="background-color: #e0ecee; font-family: Verdana; font-size: 10px; margin-bottom: 10px; padding: 10px;">
      <span id="vithd" style="font-weight: bold; color: #333; text-transform: uppercase; ">Recent Activity:</span>

    <ul style="list-style-type: none; margin: 0; padding: 0; display: inline;">
            <li style="border-right: 1px solid #000; font-weight: 700; display: inline; padding: 0 5px; margin-left: 0;">
      <span class="cat"><a href="http://groups.yahoo.com/group/hackers-il/members;_ylc=X3oDMTJmMzRrNW00BF9TAzk3MzU5NzE0BGdycElkAzE4NTczMzIEZ3Jwc3BJZAMxNzA1MDA2NzY0BHNlYwN2dGwEc2xrA3ZtYnJzBHN0aW1lAzEzMDQ5NDcxNzc-?o=6" style="text-decoration: none;">New Members</a></span>
      <span class="ct" style="color: #ff7900;">1</span>
    </li>
                                              </ul>
    
  <div style="clear: both; padding-top: 2px; color: #1e66ae;">
    <a href="http://groups.yahoo.com/group/hackers-il;_ylc=X3oDMTJlOWRzdGdjBF9TAzk3MzU5NzE0BGdycElkAzE4NTczMzIEZ3Jwc3BJZAMxNzA1MDA2NzY0BHNlYwN2dGwEc2xrA3ZnaHAEc3RpbWUDMTMwNDk0NzE3Nw--" style="text-decoration: none;">Visit Your Group</a>
  </div>
</div>

  
<div id="ft" style="font-family: Arial; font-size: 11px; margin-top: 5px; padding: 0 2px 0 0; clear: both;">
  <a href="http://groups.yahoo.com/;_ylc=X3oDMTJkZ2Z2ZjdvBF9TAzk3NDc2NTkwBGdycElkAzE4NTczMzIEZ3Jwc3BJZAMxNzA1MDA2NzY0BHNlYwNmdHIEc2xrA2dmcARzdGltZQMxMzA0OTQ3MTc3" style="float: left;"><img src="http://l.yimg.com/a/i/us/yg/logo/us.gif" height="15" width="137" alt="Yahoo! Groups" style="border: 0;"/></a>
  <div style="color: #747575; float: right;">Switch to: <a href="mailto:[email protected]?subject=Change Delivery Format: Traditional" style="text-decoration: none;">Text-Only</a>, <a href="mailto:[email protected]?subject=Email Delivery: Digest" class="margin-rt" style="text-decoration: none;">Daily Digest</a> &bull; <a href="mailto:[email protected]?subject=Unsubscribe" style="text-decoration: none;">Unsubscribe</a> &bull; <a href="http://docs.yahoo.com/info/terms/" style="text-decoration: none;">Terms of Use</a></div>
</div>

<!-- |**|end egp html banner|**| -->

  </div> <!-- ygrp-msg -->

  <!-- Sponsor -->
  <!-- |**|begin egp html banner|**| -->
  <div id="ygrp-sponsor" style="width:160px; float:right; clear:none; margin:0 0 25px 0; background: #fff;">

<!-- Start Recommendations -->
<div id="ygrp-reco">
     </div>
<!-- End Recommendations -->



  </div>   <!-- |**|end egp html banner|**| -->

  <div style="clear:both; color: #FFF; font-size:1px;">.</div>
</div>

  <img src="http://geo.yahoo.com/serv?s=97359714/grpId=1857332/grpspId=1705006764/msgId=5160/stime=1304947177/nc1=1/nc2=2/nc3=3" width="1" height="1"> <br>

<div style="color: #fff; height: 0;">__,_._,___</div>
<!--~-|**|PrettyHtmlEnd|**|-~-->

</body>

<!--~-|**|PrettyHtmlStart|**|-~-->
<head>
  <style type="text/css">
  <!--
  #ygrp-mkp {
  border: 1px solid #d8d8d8;
  font-family: Arial;
  margin: 10px 0;
  padding: 0 10px;
}

#ygrp-mkp hr {
  border: 1px solid #d8d8d8;
}

#ygrp-mkp #hd {
  color: #628c2a;
  font-size: 85%;
  font-weight: 700;
  line-height: 122%;
  margin: 10px 0;
}

#ygrp-mkp #ads {
  margin-bottom: 10px;
}

#ygrp-mkp .ad {
  padding: 0 0;
}

#ygrp-mkp .ad p {
  margin: 0;
}

#ygrp-mkp .ad a {
  color: #0000ff;
  text-decoration: none;
}
  #ygrp-sponsor #ygrp-lc {
  font-family: Arial;
}

#ygrp-sponsor #ygrp-lc #hd {
  margin: 10px 0px;
  font-weight: 700;
  font-size: 78%;
  line-height: 122%;
}

#ygrp-sponsor #ygrp-lc .ad {
  margin-bottom: 10px;
  padding: 0 0;
}

  a {
    color: #1e66ae;
  }

  #actions {
    font-family: Verdana;
    font-size: 11px;
    padding: 10px 0;
  }

  #activity {
    background-color: #e0ecee;
    float: left;
    font-family: Verdana;
    font-size: 10px;
    padding: 10px;
  }

  #activity span {
    font-weight: 700;
  }

  #activity span:first-child {
    text-transform: uppercase;
  }

  #activity span a {
    color: #5085b6;
    text-decoration: none;
  }

  #activity span span {
    color: #ff7900;
  }

  #activity span .underline {
    text-decoration: underline;
  }

  .attach {
    clear: both;
    display: table;
    font-family: Arial;
    font-size: 12px;
    padding: 10px 0;
    width: 400px;
  }

  .attach div a {
    text-decoration: none;
  }

  .attach img {
    border: none;
    padding-right: 5px;
  }

  .attach label {
    display: block;
    margin-bottom: 5px;
  }

  .attach label a {
    text-decoration: none;
  }
  
  blockquote {
    margin: 0 0 0 4px;
  }

  .bold {
    font-family: Arial;
    font-size: 13px;
    font-weight: 700;
  }

  .bold a {
    text-decoration: none;
  }

  dd.last p a {
    font-family: Verdana;
    font-weight: 700;
  }

  dd.last p span {
    margin-right: 10px;
    font-family: Verdana;
    font-weight: 700;
  }

  dd.last p span.yshortcuts {
    margin-right: 0;
  }

  div.attach-table div div a {
    text-decoration: none;
  }

  div.attach-table {
    width: 400px;
  }

  div.file-title a, div.file-title a:active, div.file-title a:hover, div.file-title a:visited {
    text-decoration: none;
  }

  div.photo-title a, div.photo-title a:active, div.photo-title a:hover, div.photo-title a:visited {
    text-decoration: none;
  }

  div#ygrp-mlmsg #ygrp-msg p a span.yshortcuts {
    font-family: Verdana;
    font-size: 10px;
    font-weight: normal;
  }

  .green {
    color: #628c2a;
  }

  .MsoNormal {
    margin: 0 0 0 0;
  }

  o {
    font-size: 0;
  }

  #photos div {
    float: left;
    width: 72px;
  }

  #photos div div {
    border: 1px solid #666666;
    height: 62px;
    overflow: hidden;
    width: 62px;
  }

  #photos div label {
    color: #666666;
    font-size: 10px;
    overflow: hidden;
    text-align: center;
    white-space: nowrap;
    width: 64px;
  }

  #reco-category {
    font-size: 77%;
  }

  #reco-desc {
    font-size: 77%;
  }

  .replbq {
    margin: 4px;
  }

  #ygrp-actbar div a:first-child {
   /* border-right: 0px solid #000;*/
    margin-right: 2px;
    padding-right: 5px;
  }

  #ygrp-mlmsg {
    font-size: 13px;
    font-family: Arial, helvetica,clean, sans-serif;
    *font-size: small;
    *font: x-small;
  }

  #ygrp-mlmsg table {
    font-size: inherit;
    font: 100%;
  }

  #ygrp-mlmsg select, input, textarea {
    font: 99% Arial, Helvetica, clean, sans-serif;
  }

  #ygrp-mlmsg pre, code {
    font:115% monospace;
    *font-size:100%;
  }

  #ygrp-mlmsg * {
    line-height: 1.22em;
  }

  #ygrp-mlmsg #logo {
    padding-bottom: 10px;
  }

  #ygrp-mlmsg a {
    color: #1E66AE;
  }

  #ygrp-msg p a {
    font-family: Verdana;
  }

  #ygrp-msg p#attach-count span {
    color: #1E66AE;
    font-weight: 700;
  }

  #ygrp-reco #reco-head {
    color: #ff7900;
    font-weight: 700;
  }

  #ygrp-reco {
    margin-bottom: 20px;
    padding: 0px;
  }

  #ygrp-sponsor #ov li a {
    font-size: 130%;
    text-decoration: none;
  }

  #ygrp-sponsor #ov li {
    font-size: 77%;
    list-style-type: square;
    padding: 6px 0;
  } 

  #ygrp-sponsor #ov ul {
    margin: 0;
    padding: 0 0 0 8px;
  }

  #ygrp-text {
    font-family: Georgia;
  }

  #ygrp-text p {
    margin: 0 0 1em 0;
  }

  #ygrp-text tt {
    font-size: 120%;
  }

  #ygrp-vital ul li:last-child {
    border-right: none !important; 
  } 
  -->
  </style>
</head>

<!--~-|**|PrettyHtmlEnd|**|-~-->
</html>
<!-- end group email -->


--------------030409000006060902000800--