<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;">Hi.<br><br>Thanks a lot for these quite clear answer.<br>I'll try this detailled procedure next week, and I'll give my feedback.<br>It's seems too easy ;). I'm sorry, the database structure was not very clear for me, I'll have to do some homework to completly understand it.<br><br>Does the database structure is supposed to be changed again ?<br>It's a bit off-topic, but I'll like to have a Perl module for Geniustrader, for accessing to the shim database. Today I'm extracting text files from the Shim database for beeing after that backtested by Geniustrader, it's not the best solution...<br><br>Thanks again.<br><br>Sam.<br><br><br>--- En date de : <b>Sam 23.8.08, Bill Pippin <i><pippin@owlriver.net></i></b> a écrit :<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;">De: Bill Pippin
<pippin@owlriver.net><br>Objet: Re: version update/database migration<br>À: "sam" <sam_backtester@yahoo.fr>, ts-general@trading-shim.org<br>Date: Samedi 23 Août 2008, 2h31<br><br><pre>Sam (<sam_backtester@yahoo.fr>) asks about upgrading the database:<br><br>> I'm using the shim release 080616 ... for getting intraday<br>> historical data. ... [what happens] if I upgrade the Shim?<br><br>As long as you dump your data in the appropriate format, and then<br>are able to restore it, you're fine.<br><br>Three basic points, given that you used the default setup and create<br>scripts:<br><br> 1. There are two databases, testing and trading, and you probably<br> want to consolidate all your good data into one, say trading,<br> duplicate it in testing, and unload it from testing using the<br> load script near the end of this post. Once the data has<br> been consolidated and duplicated you can easily
recover from<br> mistakes.<br><br> 2. Next, dump from testing to a file, about which more in the<br> sequel. This dump step gives another layer of safety.<br><br> 3. After that you can run create.sql on testing, giving yourself<br> two databases, one still holding all your data, and that you<br> plan to leave alone for now, and the other with only the<br> initial symbol load, where you can experiment to your heart's<br> content.<br><br>Throughout the rest of these two recipes, I'll expect that you've<br>used the approach of step (1) to reduce the save/restore problem<br>from two databases to one.<br><br>Though for your database all you care about is HistoryBar, your<br>goal in general would be to save eight tables:<br><br> HistoryBar AccountCode<br> CreateEvent ChangeOrder<br> OrderStatus ActiveOrder<br> OrderResult OrderReport<br><br>The overall approach is to dump the tables to be saved,
recreate<br>the database, and then load the data back in. There are two<br>cases A and B as there have been significant changes to the structure<br>of any of the tables to be saved or not.<br><br>First, for the simple case where the tables' attribute names and<br>index relationships are consistent from the old version to the new,<br>save/restore boils down to:<br><br> A.1 use the dump script below to save<br><br> A.2 use create.sql to build a new, fresh database<br><br> A.3 use the load script below to load<br><br>After using this approach, and running the dump script again to<br>save to a new file name, diff tells me that the only difference<br>is in the datestamps:<br><br>< -- Dump completed on 2008-08-22 22:31:42<br>---<br>> -- Dump completed on 2008-08-22 22:38:03<br><br>Unfortunately, from June until now there have been signficant<br>changes to the database design, including in particular to<br>HistoryBar, so that the tables'
attribute names and index<br>relationships are *not* consistent from the old version to the<br>new, and case (B) applies.<br><br>In what follows I'll concentrate on HistoryBar, since that is<br>the example at hand; the generalization to all eight tables<br>is bulky but straight-forward.<br><br>The 080616 system HistoryBar create table statement defines the<br>contract minimally, though with a dependence on foreign key<br>stability:<br><br>> sym int unsigned not null references Product(uid),<br>> ecn int unsigned not null references Exchange(uid),<br><br>More recently, we've denormalized the data slightly, to add<br>redundancy and protect against product renumbering as symbols are<br>added or deleted, to help ensure that the history data and the<br>journal have stable, consistent meanings over time. For HistoryBar,<br>this meant adding the IB contract id as an attribute.<br><br>> ibc int unsigned not
null references Product(ibc),<br>> sym int unsigned not null references Product(uid),<br>> ecn int unsigned not null references Exchange(uid),<br><br>For the second table design, and since Product has a one-to-one<br>unique key relationship between the IB contract id, ibc, and the<br>Product record uid, here the foreign key "sym", as long as IB<br>doesn't change their contract ids, saved data can be recovered<br>even when symbols are added and deleted.<br> <br>The general approach given such non-trivial table changes is to:<br><br> B.1 Provide new table names via the alter table<br> statement, for HistoryBar say to HistoryTmp.<br><br> B.2 Ensure that HistoryTmp can be added to your new<br> database by leaving the --no-create-info<br> option *out* of the dump script below, so that<br> the dump'd file has a complete create table<br> definition inside;<br><br>
B.3 Dump the data from the table with the altered name,<br> here HistoryTmp.<br><br> B.4 Run create.sql on testing to get a fresh database.<br><br> B.5 Obtain a newly created table HistoryTmp loaded<br> with all your old 1-minute bars by loading the<br> dump file.<br><br> B.6 Insert the data into the new, empty HistoryBar table<br> via a statement similar to the following:<br><br> insert into HistoryBar(ibc, sym, ecn, bid, time, open,<br> high, low, close, vol, wap, has_gaps)<br><br> select Product.ibc,<br> Product.uid, ecn, bid, time, open,<br> high, low, close, vol, wap, has_gaps<br><br> from HistoryTmp, Product<br> where HistoryTmp.ibc = Product.ibc;<br><br>Note that from the first to the second recipes, steps A.1, A.2,<br>and A.3 correspond loosely to B.3,
B.4, and B.5. The history table<br>name has changed, and the load script has been modified by the<br>deletion of the --no-create-info option.<br><br>The dump and load scripts are themselves simple to the point of<br>being trivial, although as explained above you will need to make:<br>major changes if database version changes have been sufficiently<br>extensive; and in any case, minor changes depending on your setup.<br>Note in particular that the single-dash options, that is -h, -u, -p<br>and the like, are likely to change.<br><br>I've include these scripts in the newest release;<br>herewith sql/bin/dump <br> and sql/bin/load:<br><br>-------- cut-here --------<br>#!/bin/bash<br><br># ::::::::::::::<br># load<br># ::::::::::::::<br>#<br># Import journal and other accumulated shim data.<br># Adapt as needed for database version changes, and<br># in particular, correct the Load and Host
values.<br><br>Load=journal.080822.sql<br>Host=xps400<br><br>mysql -v -p -h $(Host) -u root testing < $Load<br><br>-------- cut-here --------<br>#!/bin/bash<br><br># ::::::::::::::<br># dump<br># ::::::::::::::<br>#<br># Export journal and other accumulated shim data.<br># Adapt as needed for database version changes, and<br># in particular, change the Host value to suit.<br><br>Host=xps400<br>Save=$(date +%y%m%d)<br>Opts="--skip-opt --add-locks --allow-keywords \<br>--create-options --extended-insert --single-transaction \<br>--no-create-info --tz-utc"<br><br>mysqldump -v -p -h $(Host) -u root $Opts testing \<br> HistoryBar AccountCode \<br> CreateEvent ChangeOrder \<br> OrderStatus ActiveOrder \<br> OrderResult OrderReport > journal.${Save}.sql <br><br>echo saved: journal.${Save}.sql <br>-------- cut-here --------<br><br>Cheers,<br><br>Bill<br></pre></blockquote></td></tr></table><br>
<hr size="1">
Envoyé avec <a href="http://us.rd.yahoo.com/mailuk/taglines/isp/control/*http://us.rd.yahoo.com/evt=52423/*http://fr.docs.yahoo.com/mail/overview/index.html">Yahoo! Mail</a>.<br>Une boite mail plus intelligente. </a>