THE BOOK--Playing The Percentages In Baseball

Friday, August 28, 2009

MySQL views are terrible

By Tangotiger, 03:03 PM

I’ve been working with MySQL a bit lately, and that’s only because that’s the database offered by my webhost. So far, I’ve seen two huge limitations, and both involves views (my preferred process).

The first is doing a left join rather than an inner join. Even though the number of returned records were virtually identical, turning the outer into an inner changed the run time from 51 seconds to 0.3 seconds. It was a pretty basic view.

The second one is joining a table to a view. It seems that the view doesn’t use any of the underlying tables’ indexes. So, what I’m left with is merging a 100,000 row indexed table to a 3000 row view, and it’s doing the join without the use of nay index. Even MsAccess is faster.

Suggestions?

• Web Admin

#1 Tangotiger (see all posts) 2009/08/28 (Fri) @ 15:20

As I’m reading about MySQL, I should point out that one of the tables in the view is the same as the table it’s joining to. It looks like this:

create or replace view abc as
select ...
from a,b,c
...

select ...
from abc, a
...

It looks like I need to specify “algorithm”. Note that my view has a group by and a sum.

#2 Colin Wyers (see all posts) 2009/08/28 (Fri) @ 15:29

My best advice without knowing more about the data or the queries at hand is to use a temporary table - instead of CREATE VIEW use CREATE TABLE. Yeah, you then have the problem of updating the table whenever the tables its derived from change.

#3 Ryan JL (see all posts) 2009/08/28 (Fri) @ 15:35

If it’s not using the index, can you force it to use it using a hint?

#4 Tangotiger (see all posts) 2009/08/28 (Fri) @ 15:36

Right, the tables are constantly updating (scouting report votes).

#5 (see all posts) 2009/08/28 (Fri) @ 15:40

Sounds like your left join is not using the indexes.

try using “EXPLAIN” to find out.

http://dev.mysql.com/doc/refman/5.0/en/explain.html

i think, but i am not certain, that mysql version 4 was not so good with indexes and left joins, and that there are ways to force the right indexes to be used.

#6 Tangotiger (see all posts) 2009/08/28 (Fri) @ 15:41

I’m new to MySQL (though I have experience in others). I didn’t know MySQL had “hints”. I’ll read up on it…

#7 (see all posts) 2009/08/28 (Fri) @ 15:46

one more for you on outer join optimization

http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html

#8 David Pinto (see all posts) 2009/08/28 (Fri) @ 15:50

I use temporary tables. However, if you’re using the MYSql query browser, it doesn’t properly support transactions, that is, running multiple queries separated by ;.

#9 Ryan JL (see all posts) 2009/08/28 (Fri) @ 15:53

Tango, here’s a link that might help:

http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

#10 Tangotiger (see all posts) 2009/08/28 (Fri) @ 16:30

Ryan, that USE INDEX looks like it applies at the table level, not the view level. So, when I do the join, there is no index on the view for me to go after. I’m shocked that this is the case.

David, for testing purposes, so I can least move forward, I’m creating a temp table as noted by Colin. So, after I do my view, I do this:

DROP TABLE IF EXISTS TEMP_abc;
CREATE TABLE TEMP_abc as
select * from abc
;
ALTER TABLE TEMP_abc
ADD PRIMARY KEY (d,e,f)
;

And then I use TEMP_abc. If ever I can just use the view, I can delete the above, and do a change/replace of TEMP_abc to abc.

Unfortunately, I have a few of these I have to do. It’s certainly not the elegant real-time solution I was looking for.

#11 weskelton (see all posts) 2009/08/29 (Sat) @ 01:29

When defining your view, you may want to try the ALGORITHM = MERGE option. Some of the discussion I’ve seen seems to suggest that this enables the use of indexes on underlying tables.

EX.

CREATE ALGORITHM = MERGE VIEW view_name (c1, c2)
as
select ...

#12 (see all posts) 2009/08/29 (Sat) @ 01:33

I have a lot of experience with MySQL, but very little with views. Mostly because I’ve always been told not to use them.
Also, EXPLAIN is your best friend for optimizing queries in MySQL.

See:
http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/

#13 weskelton (see all posts) 2009/08/29 (Sat) @ 01:35

Sorry, I take it back. I now see where the GROUP BY prevents you from using the MERGE option in your view definition.

#14 Harry Pavlidis (see all posts) 2009/08/29 (Sat) @ 10:37

David - you can use the “script tab” to execute multiple statements.

#15 Bill (see all posts) 2009/08/30 (Sun) @ 15:13

Use a function baby, functions.

#16 Brian Cartwright (see all posts) 2009/08/30 (Sun) @ 16:14

Anyone got a link for a good tutorial on functions? I’m recoding Oliver into MySQL (with enhancements) and there’s a few places functions would help out.

#17 mookie (see all posts) 2009/08/31 (Mon) @ 03:00

Brian - I just started creating my own database in MySQL from retrosheet data files and then I stumbled upon this site and it sounds like you guys already have one put together. Instead of duplicating efforts can I join your project?
Anyway, here’s a quick introduction to MySQL functions, there is quite a bit of documentation out there on MySQL functions, nevertheless I spent a very good many hours pulling my hair out because there are a few syntax rules that you kind of have to learn by trial and error.
I created a useless example function to illustrate most of the “gotchas” that tripped me up in the beginning.

OK, my database is named “retro”, the function I’m making is “my_silly_little_function” which accepts the parameter “var_retro_id” and returns a VARCHAR(200) message.

Gotcha #1
-----------------------------------
DON’T use the @ symbol to identify parameter names in the parameter section. If you don’t know what I’m talking about, good, forget I ever mentioned it.
Certain syntaxes require parameters and variables to be prefaced by a special symbol, theres a name for it, I forget what the hell it is,
for example, the $ in PHP variables and the @ in T-SQL (SQL Server) parameters and variables,
in MySQL throw that concept right out the window. Oh, you’ll see the @ symbol, but it means something entirely different.

Cute Observation #1 - RE: “DELIMITER $$”
-----------------------------------
MySQL commands were designed to be entered at a command prompt, then some cheeky bastard came along and made a GUI, and quite frankly,
the MySQL syntax doesn’t make a whole lot of sense when you view it from the GUI perspective where you can type any number of lines and then submit them all at once
whereas at the command prompt its a one line at a time kind of deal. So at a command prompt you need some way to tell it that, “When I enter a delimiter, just store it as
part of the command, don’t actually execute the command”. So the MySQL solution was to introduce a DELIMITER declaration which says, “From now until I tell you otherwise the
the delimiter shall be $$” or whatever string you choose, but the default in the GUI is $$ and I’m going to go ahead and assume that in 2009 most folks are using the GUI.
If this doesn’t make sense, please don’t fret, all you have to do is just spend 173 consecutive dateless Saturday nights in your parent’s basement, sitting on the edge of
a cot and contemplating suicide while messing around with MySQL, and at that point, all this mumbo-jumbo will be crystal clear to you as well.

OK, enough diatribe from me, I was gonna explain the rest of this thing, but I think the Milwaukee’s Best is doing most of the talking for me at this point so I better sign off.

The database is “retro”, the table is “game”, the field I’m searching is “retro_id” for a valid retro_id.

Here’s an example fuction, and an example call to it, and the example result. I’ll explain the rest tommorrow, but this should get you started.

/*************************************************************************************************************************************/

/* START OF FUNCTION DECLARATION */

DELIMITER $$

DROP FUNCTION IF EXISTS `retro`.`my_silly_little_function`$$
CREATE FUNCTION `retro`.`my_silly_little_function` (var_retro_id VARCHAR(15)) RETURNS VARCHAR(200)
BEGIN

IF EXISTS(SELECT 1 FROM game WHERE retro_id = var_retro_id) THEN
BEGIN
SET @team = (SELECT LEFT(retro_id,3) FROM game WHERE retro_id = var_retro_id);
SET @team_name = CASE WHEN @team = ‘NYA’ THEN ‘the Yankees.’
WHEN @team = ‘BOS’ THEN ‘the Red Sox.’
ELSE ‘irrelevant to the current state of humanity as we now know it.’ END;
SET @result = CONCAT(’Yes, its a valid retrosheet game_id, and the home team was ‘,@team_name);
RETURN @result;
END;
ELSE
BEGIN

SET @result = ‘Sorry, try again, its not a valid retrosheet game id.’;
RETURN @result;
END;
END IF;

END$$

DELIMITER ;

/* END OF FUNCTION DECALARATION */

/**************************************************************************************************************************************************/

/* START OF EXAMPLE CALL TO FUNCTION */

select my_silly_little_function(’NYA200804010’ as straightforward,
my_silly_little_function(’JXT131004050’ as constructive_criticism,
my_silly_little_function(’ANA200804050’ as factually_correct_as_always_however_now_the_response_arrives_with_a_tinge_of_sauciness;

/* END OF EXAMPLE CALL TO FUNCTION */

/*************************************************************************************************************************************************/

/* RESULT */

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 111
Server version: 5.1.31-1ubuntu2 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> use retro;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select my_silly_little_function(’NYA200804010’ as straightforward,
-> my_silly_little_function(’JXT131004050’ as constructive_criticism,
-> my_silly_little_function(’ANA200804050’ as factually_correct_as_always_however_now_the_response_arrives_with_a_tinge_of_sauciness;
+-------------------------------------------------------------------------+-------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------+
| straightforward | constructive_criticism | factually_correct_as_always_however_now_the_response_arrives_with_a_tinge_of_sauciness |
+-------------------------------------------------------------------------+-------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------+
| Yes, its a valid retrosheet game_id, and the home team was the Yankees. | Sorry, try again, its not a valid retrosheet game id. | Yes, its a valid retrosheet game_id, and the home team was irrelevant to the current state of humanity as we now know it. |
+-------------------------------------------------------------------------+-------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#18 mookie (see all posts) 2009/08/31 (Mon) @ 03:08

hey, I think your website is interpreting a single quote and close parentheses as some sort of winking emoticon. anywhere you see that winking fucker in my post its supposed to be a ) character

#19 Brian Cartwright (see all posts) 2009/08/31 (Mon) @ 09:51

What’s a GUI? lol - I’m lost without a dos prompt!

I’ve got user defined variables working, still need to experiment on expanded use - in MS Access, you can do an ‘AS col’ and then reference that column in the same query, but MySQL doesn’t allow that. User defined variables were the solution.

In this example, names preceded by ‘_’ are rates, then I have to reconstruct the chances for each to get the new counts.

SELECT
PA,
IBB,
@HP:=_HP*(PA-IBB) AS HP,
@BB:=_BB*(PA-IBB-@HP) AS BB,
@SO:=_SO*(PA-IBB-@HP) AS SO,
@SH:=_SH*(PA-IBB-@HP-@BB-@SO) AS SH,
@HR:=_HR*(PA-IBB-@HP-@BB-@SO-@SH) AS HR,
@BH:=_BH*(PA-IBB-@HP-@BB-@SO-@SH-@HR) AS BH
FROM Rates;

I’ll try coding my first function (log5) later today

#20 mookie (see all posts) 2009/09/01 (Tue) @ 05:07

this website is stripping out essential characters from the code so even if you look up what I’m talking about it won’t make sense, but here’s the server side of it. tommorrow I’ll create a sourceforge or google svn account to give you whole picture

/**************************************************************/
/* START OF MySQL PROCEDURES */

DELIMITER $$

DROP PROCEDURE IF EXISTS `retro`.`add_game`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_game`(var_retro_id VARCHAR(15))
BEGIN

IF NOT EXISTS(SELECT 1 FROM game WHERE retro_id = var_retro_id) THEN
BEGIN

    INSERT INTO game (retro_id) values (var_retro_id);
    SET @ID = LAST_INSERT_ID();
    SELECT @ID AS ID;
END;
ELSE
BEGIN
    SELECT 0 AS ID;
END;

END IF;

END$$

DELIMITER ;
 
/*****************************************/
DELIMITER $$

DROP PROCEDURE IF EXISTS `retro`.`log_state`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `log_state`(var_game_id int, var_play_id int, var_field_name VARCHAR(100), var_value VARCHAR(100))
BEGIN

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'state' AND COLUMN_NAME = var_field_name) THEN
BEGIN
    
    SET @sql = CONCAT('ALTER TABLE state ADD ', var_field_name , ' CHAR(100) ;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;

END;
END IF;

IF NOT EXISTS(SELECT 1 FROM state WHERE game_id = var_game_id AND seq = var_play_id) THEN
BEGIN

    SET @sql1 = CONCAT('INSERT INTO state (game_id,seq,' , var_field_name , ') VALUES (' ,CAST(var_game_id AS CHAR), ',' , CAST(var_play_id AS CHAR), ',', CHAR(39) , var_value , CHAR(39) , ');');
    PREPARE stmt1 FROM @sql1;
    EXECUTE stmt1 ;

END;
ELSE
BEGIN

    SET @sql2 = CONCAT('UPDATE state SET ' , var_field_name , ' = ' , CHAR(39) , var_value , CHAR(39) , ' WHERE game_id = ' , CAST(var_game_id AS CHAR) , ' AND seq = ', CAST(var_play_id AS CHAR),';');
    PREPARE stmt2 FROM @sql2;
    EXECUTE stmt2 ;

END;
END IF;

END$$

DELIMITER ;

/********************************************/
DELIMITER $$

DROP PROCEDURE IF EXISTS `retro`.`add_info`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_info`(var_game_id INT, var_field_name VARCHAR(100), var_value VARCHAR(200))
BEGIN

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'info' AND COLUMN_NAME = var_field_name) THEN
BEGIN
    
    SET @sql = CONCAT('ALTER TABLE info ADD ', var_field_name , ' CHAR(100) ;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;

END;
END IF;

IF NOT EXISTS(SELECT 1 FROM info WHERE game_id = var_game_id) THEN
BEGIN

    SET @sql1 = CONCAT('INSERT INTO info (game_id,' , var_field_name , ') VALUES (' ,CAST(var_game_id AS CHAR), ',' , CHAR(39) , var_value , CHAR(39) , ');');
    PREPARE stmt1 FROM @sql1;
    EXECUTE stmt1 ;

END;
ELSE
BEGIN

    SET @sql2 = CONCAT('UPDATE info SET ' , var_field_name , ' = ' , CHAR(39) , var_value , CHAR(39) , ' WHERE game_id = ' , CAST(var_game_id AS CHAR) , ';');
    PREPARE stmt2 FROM @sql2;
    EXECUTE stmt2 ;

END;
END IF;

END$$

DELIMITER ;
/**********************************************/

#21 mookie (see all posts) 2009/09/01 (Tue) @ 05:11

Brian -

/********************************************/
using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text.RegularExpressions;

namespace retro
{
    class MainClass
    {
        // main function
        public static void Main(string[] args)
        {
            Archiver a = new Archiver();
            a.Start("/home/u/retro/masternew.EVA");
            // GetAllPlays("/home/u/retro/master.EVX");
        }
        
        
        
        public static void GetAllPlays(string path)
        {
            StreamReader sr = new StreamReader(path);
            List<string> plays = new List<string>();
            int x=0;
            while(!sr.EndOfStream)
            {
                string line = sr.ReadLine().Trim();
                string[] s = line.Split(',');
                if(s.Length>0)
                {
                    if(s[0].Equals("play") && !plays.Contains(s[6].Trim()))
                    {
                        x++;
                        plays.Add(s[6].Trim());
                        string[] a = s[6].Split('.');
                        Console.Write(s[6].PadRight(25));
                        if(a.Length>0)
                        {
                            string[] play = SplitWithParens(a[0],'/');
                            foreach(string pl in play)
                            {
                                Console.Write(pl.PadRight(12));
                            }
                            
                        }
                        if(a.Length>1)
                        {
                            string[] runners = SplitWithParens(a[1],';');
                            foreach(string run in runners)
                            {
                                Console.Write(run.PadRight(12));
                            }
                        }
                        Console.WriteLine();
                        string slkdlskd = Console.ReadLine();
                    }
                }
            }
            plays.Sort();
            StreamWriter sw = new StreamWriter("/home/u/retro/plays.dat");
            foreach(string p in plays)
            {
                sw.WriteLine(p);
                Console.WriteLine(p);
            }
            sw.Flush();
            sw.Close();
        }        
        
        
        
        public static string[] SplitWithParens(string input,char splitChar)
        {
            char[] cstr = input.ToCharArray();
            List<string> res = new List<string>();
            int lvl=0;
            string temp="";    
            foreach(char c in cstr)
            {
                if(lvl==0)
                {
                    if(c=='(')
                    {
                        temp+=c;
                        lvl++;
                    }
                    else if(c==')')
                    {
                        temp+=c;
                        lvl--;
                    }
                    else if(c==splitChar)
                    {
                        res.Add(temp);
                        temp="";
                    }
                    else
                    {
                        temp+=c;
                    }
                }
                else
                {
                    temp+=c;
                    if(c=='(')
                    {
                        lvl++;
                    }
                    else if(c==')')
                    {
                        lvl--;
                    }
                }
            }
            
            if(temp!="")
            {
                res.Add(temp);
            }
            
            string[] result = res.ToArray();
            return result;
        }
                        
        
        
        
        // append all files to one big one
        public static void MakeFile(string path)
        {
            
            List<string> fnames = new List<string>();
            Stack<string> dirs = new Stack<string>();
            dirs.Push(path);
            Console.WriteLine("Made it here.");
            while(dirs.Count>0)
            {
                
                string currentPath = dirs.Pop();
                string[] fileList = Directory.GetFiles(currentPath);
                
                foreach(string f in fileList)
                {    
                    if(!fnames.Contains(f) && !f.Contains(".zip"))
                    {
                        fnames.Add(f);
                        StreamReader sr = new StreamReader(f);
                        string outFile = "master" + (f.Contains(".") ? f.Substring(f.LastIndexOf(".")) : "garbage");
                        StreamWriter sw = new StreamWriter(path + "/" + outFile,true);
                        while(!sr.EndOfStream)
                        {
                            string line = sr.ReadLine();
                            sw.WriteLine(line);
                        }
                        sw.Flush();
                        sw.Close();
                        sr.Close();
                        Console.WriteLine(f);
                    }
                }
                
                
                string[] dirList = Directory.GetDirectories(currentPath);
                foreach(string d in dirList)
                {
                    dirs.Push(d);
                }
            }

        }
        
        
    }
}
/********************************************/



using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text.RegularExpressions;
using MySql.Data.MySqlClient;


namespace retro
{
    
    
    public class Archiver
    {
        
        
        public Archiver()
        {
            
        }
        
        public void Start(string inFile)
        {
            Console.WriteLine(inFile);
            StreamReader sr = new StreamReader(inFile);
            int currentGame=0;
            MySqlConnection cnn = new MySqlConnection("server=localhost;database=retro;username=retro;pwd=retro;");
            cnn.Open();
            string line="";
            Console.WriteLine("Made it here.");
            Game g = new Game(0);
            while(!sr.EndOfStream)
            {
                line = sr.ReadLine();
                line = line.Replace("'","").Replace("\"","");
                string[] s = line.Split(',');
                if(s.Length > 0)
                {
                    if(s[0].Equals("id"))
                    {
                        MySqlCommand cmd = new MySqlCommand("call add_game('" + s[1] + "');",cnn);                        
                        MySqlDataReader mdr =  cmd.ExecuteReader();
                        if(mdr.Read())
                        {
                            // yep, brand new game in our db
                            if(mdr.GetInt32("ID")>0)
                            {
                                currentGame = mdr.GetInt32("ID");
                                g = new Game(currentGame);
                                Console.WriteLine(currentGame.ToString());
                            }
                        }
                        mdr.Close();
                    }
                    else if(s[0].Equals("version"))
                    {
                        
                    }
                    else if(s[0].Equals("info"))
                    {
                        MySqlCommand cmd = new MySqlCommand("call add_info(" + currentGame.ToString() + ",'" + s[1] + "','" + s[2] + "');",cnn);                        
                        cmd.ExecuteNonQuery();
                        
                    }
                    else if(s[0].Equals("start"))
                    {
                        g.ChangeState((s[3].Equals("0") ? "home_bat_" : "vis_bat_") + s[4],s[1]);
                        g.ChangeState((s[3].Equals("0") ? "home_field_" : "vis_field_") + s[5],s[1]);
                    }
                    else if(s[0].Equals("play"))
                    {
                        /* First time around this will log the the zero state (seq=0) which is essentially the starting lineups
                         * , every subsequent call we flush the state to the database, increment the seq, and then interpret the
                         * play.
                         */
                        g.LogState();
                        g.seq++;
                        g.ChangeState("inning",s[1]);
                        g.ChangeState("at_bat",s[2]);
                        g.ChangeState("base_0",s[3]);
                        string[] a = s[6].Split('.');
                        if(a.Length>1)
                        {
                            string[] runners = SplitWithParens(a[1],';');
                            foreach(string run in runners)
                            {
                                // runner advanced
                                if(run.Contains("-"))
                                {
                                    string[] b = run.Split('-');
                                    int from = (b[0].Substring(0,1)=="B" ? 0 : Convert.ToInt32(b[0].Substring(0,1)));
                                    int to = (b[1].Substring(0,1)=="H" ? 4 : Convert.ToInt32(b[1].Substring(0,1)));
                                    g.AdvanceRunner(from,to,true);
                                }
                                // runner was out
                                else if(run.Contains("X"))
                                {
                                    // string[] b = run.Split('X');
                                    // int from = (b[0].Substring(0,1)=="B" ? 0 : Convert.ToInt32(b[0].Substring(0,1)));
                                    // int to = (b[1].Substring(0,1)=="H" ? 4 : Convert.ToInt32(b[1].Substring(0,1)));
                                    // g.AdvanceRunner(from,to,1);
                                }
                            }
                        }
                        if(a.Length>0)
                        {
                            string[] play = SplitWithParens(a[0],'/');
                            foreach(string pl in play)
                            {
                                
                                
                            }
                        }
                        
                    }
                    else if(s[0].Equals("com"))
                    {
                        
                    }
                    else if(s[0].Equals("data"))
                    {
                        
                    }
                    else if(s[0].Equals("sub"))
                    {
                        
                    }
                    
                    
                    
                }
                
                
                
                
                
                
            }
            
            cnn.Close();
            sr.Close();
            
        }
        
        
        
        
        public static string[] SplitWithParens(string input,char splitChar)
        {
            char[] cstr = input.ToCharArray();
            List<string> res = new List<string>();
            int lvl=0;
            string temp="";    
            foreach(char c in cstr)
            {
                if(lvl==0)
                {
                    if(c=='(')
                    {
                        temp+=c;
                        lvl++;
                    }
                    else if(c==')')
                    {
                        temp+=c;
                        lvl--;
                    }
                    else if(c==splitChar)
                    {
                        res.Add(temp);
                        temp="";
                    }
                    else
                    {
                        temp+=c;
                    }
                }
                else
                {
                    temp+=c;
                    if(c=='(')
                    {
                        lvl++;
                    }
                    else if(c==')')
                    {
                        lvl--;
                    }
                }
            }
            
            if(temp!="")
            {
                res.Add(temp);
            }
            
            string[] result = res.ToArray();
            return result;
        }
        
        
        
            
        
        
        
        
        
        
    }
}

/*************************************************/


using System;
using System.IO;
using System.Data;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Collections.Specialized;
using MySql.Data.MySqlClient;


namespace retro
{
    
    
    public class Game
    {
        
        public int id;
        public int seq;
        Dictionary<string,string> state;
        
        public Game(int game_id)
        {
            id = game_id;
            seq=0;
            state =  new Dictionary<string, string>();
        }
        
        
        public void ChangeState(string element, string val)
        {
            if(state.ContainsKey(element))
            {
                state[element] = val;
            }
            else
            {
                state.Add(element,val);
            }
        }
        
        
        public string GetState(string element)
        {
            if(!state.ContainsKey(element))
            {
                state.Add(element,"");
            }
            return state[element];
        }
        
        
        public void AddToState(string element, int val)
        {
            int v = (GetState(element)=="" ? 0 : Convert.ToInt32(GetState(element)));
            val += v;
            ChangeState(element,val.ToString());
        }
        
        
        
                
    
        public void LogState()
        {
            /*
            StreamWriter sw = new StreamWriter("/home/u/game_state.dat",true);
            foreach(KeyValuePair<string,string> k in state)
            {
                sw.Write(k.Value + ",");
            }
            sw.Write("\n");
            sw.Flush();
            sw.Close();
            
            */
            MySqlConnection cnn = new MySqlConnection("server=localhost;database=retro;username=retro;pwd=retro;");
            cnn.Open();
            foreach(KeyValuePair<string,string> k in state)
            {
                MySqlCommand cmd = new MySqlCommand("call log_state(" + id.ToString() + "," + seq.ToString() + ",'" + k.Key + "','" + k.Value + "');",cnn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            cnn.Close();
            
        }
        
        
        public void AdvanceRunner(int from, int to, bool earned)
        {
            string runner = GetState("base_" + from.ToString());
            ChangeState("base_" + to.ToString(),runner);
            ChangeState("base_" + from.ToString(),"");
            if(to==4)
            {
                if(GetState("at_bat")=="0")
                {
                    AddToState("score_vis",1);
                    AddToState("total_bases_vis",to-from);
                    if(earned)
                    {
                        AddToState("earned_runs_vis",1);
                    }
                }
                else
                {
                    AddToState("score_home",1);
                    AddToState("total_bases_home",to-from);
                    if(earned)
                    {
                        AddToState("earned_runs_home",1);
                    }
                }
            }
        }
        
        
        
        
        
        
        
        
        
        
        
        public void EndOfGame()
        {
            // TODO: implement this
        }
        
        
    }
}

#22 Brian Cartwright (see all posts) 2009/09/01 (Tue) @ 06:56

I think I understand the function definging and calling syntax, I just actually have to sit down and create one.

Your 05:11 post goes beyond that, and I ahve not yet attepmted to decypher all of it.

I have been doing the programming in our office for 20 years (awk, batch, visual basic) and I’ve been doing Access for almost two years. I only got into MySQL about two months ago, and some of these things wait until I have a need for them until I sit down, learn it and try it.

#23 Tangotiger (see all posts) 2009/09/01 (Tue) @ 07:13

wrap your code with
{code}
{/code}

tags, but change { to [

Page 1 of 1 pages

<< Back to main

THE BOOK--Playing The Percentages In Baseball

Friday, August 28, 2009

MySQL views are terrible

Latest...