X-Git-Url: http://git.sourceforge.jp/view?p=amulettoolsmh4%2Fmain.git;a=blobdiff_plain;f=model%2Fdb_generator.py;h=55fe442dda7d3140b2c4677b48561eb225fe43d8;hp=2c925e7fb4a8d7f1073d52b45a8d35f52b654d79;hb=2c086b0aad62482e9cd905eb0a2c64b484ce55de;hpb=2ff5f0a846a7d2958bb179794a45751a89e73b53

diff --git a/model/db_generator.py b/model/db_generator.py
index 2c925e7..55fe442 100644
--- a/model/db_generator.py
+++ b/model/db_generator.py
@@ -13,6 +13,16 @@ import zipfile
 import cStringIO
 
 import db_supports
+import mh4constnumbers
+import skilltable
+import amulettable
+import skillminmaxtable
+import seed2skill2table
+import seed2tablenumbertable
+import seed2thresholdtable
+import seed2inishietable
+import sufficienttable
+import seed1tenuntable
 
 class DataBaseGenerator(object):
     u""" this is generate class of database """
@@ -44,11 +54,10 @@ class DataBaseGenerator(object):
         # db generation
         self._create_skill_table()
         self._create_amulet_table()
+        self._create_min_max_table()
         self._create_seed2_skill2_table()
-        self._create_seed2_threshold1_table()
-        self._create_seed2_threshold2_table()
+        self._create_seed2_threshold_table()
         self._create_seed2_inishie_table()
-        self._create_min_max_table()
         self._create_sufficient_value_table()
         self._create_seed1_tenun_table()
 
@@ -115,38 +124,25 @@ class DataBaseGenerator(object):
     def _create_skill_table(self):
         u"""スキルとIDの組み合わせテーブルを作成する"""
         print "create skill table"
-        self._cursor.execute(db_supports.SKILL_TABLE_CREATE_SQL)
+        generator = skilltable.SkillTableGenerator()
 
         # read from zip file with StringIO wrapper
         f = cStringIO.StringIO(self._zipfile.read(db_supports.SKILL_FILE_NAME, "r"))
-        reader = csv.reader(f)  # (skillName)
-
-        reader.next()   # skip header row
-        for row in reader:
-            try:
-                self._cursor.execute(db_supports.SKILL_TABLE_INSERT_SQL, (row[0].strip(),))
-            except sqlite3.IntegrityError, e:
-                print "not unique:", row[0].decode("utf-8")
-                raise e
+        reader = csv.reader(f)  # (id, skillName)
+        generator.insert_data(self._cursor, reader)
+
         self._connect.commit()
         f.close()
 
     def _create_amulet_table(self):
         u"""お守り名とIDの組み合わせテーブルを作成する"""
         print "create amulet table"
-        self._cursor.execute(db_supports.AMULET_TABLE_CREATE_SQL)
-
+        generator = amulettable.AmuletTableGenerator()
         # read from zip file with StringIO wrapper
         f = cStringIO.StringIO(self._zipfile.read(db_supports.AMULET_FILE_NAME, "r"))
         reader = csv.reader(f)  # (amuleteName)
+        generator.insert_data(self._cursor, reader)
 
-        reader.next()   # skip header row
-        for row in reader:
-            try:
-                self._cursor.execute(db_supports.AMULET_TABLE_INSERT_SQL, (row[0].strip(),))
-            except sqlite3.IntegrityError, e:
-                print "not unique:", row[0].decode("utf-8")
-                raise e
         self._connect.commit()
         f.close()
 
@@ -156,82 +152,34 @@ class DataBaseGenerator(object):
         """
         print "load min & max of skill" 
         # create master table of skill min max
-        self._cursor.execute(db_supports.MIN_MAX_MASTER_TABLE_CREATE_SQL)
+        generator = skillminmaxtable.SkillMinMaxTableGenerator()
         # read from zip file with StringIO wrapper
         f = cStringIO.StringIO(self._zipfile.read(db_supports.MIN_MAX_FILE_NAME, "r"))
         reader = csv.reader(f)  # (name, filename of minmax1, filename of minmax2)
 
-        reader.next()   # skip header row
-        cnt = 0
-        for row in reader:
-            table_name = db_supports.MIN_MAX_TABLE_NAME.format(id=cnt)
-            insert_sql = db_supports.MIN_MAX_MASTER_TABLE_INSERT_SQL
-            amu_id = self._get_amulet_id(row[0].strip())
-            if amu_id is None:
-                print "amulet name:", row[0].decode("utf-8")
-            self._cursor.execute(insert_sql, (amu_id, table_name))
-
-            create_sql = db_supports.MIN_MAX_TABLE_CREATE_SQL.format(table_name=table_name)
-            self._cursor.execute(create_sql) # create minmax table for each omamori
-
-            skill_dict = {} # dict for skill1, skill2
-
-            # get min max of skill1
-            fname = row[1].strip()
-            if fname:
-                # not empty
-                if fname in self._zipfile.namelist():
-                    # read from zip file with StringIO wrapper
-                    file_minmax = cStringIO.StringIO(self._zipfile.read(fname, "r"))
-                    reader_minmax = csv.reader(file_minmax)  # (name of skill, min1, max1)
-
-                    reader_minmax.next()  # skip header
-                    for row_minmax in reader_minmax:
-                        min_val, max_val = int(row_minmax[1]), int(row_minmax[2])
-                        skill_id = self._get_skill_id(row_minmax[0].strip())
-                        if skill_id is None:
-                            print "name:", row_minmax[0].decode("utf-8"), ", skill_id", skill_id
-                        skill_dict[skill_id] = (min_val, max_val, 0, 0)
-
-                    file_minmax.close()
-                else:
-                    print "file1 ", fname, " doesn't exist!"
+        generator.insert_master_data(self._cursor, reader)
+        filenames_dict = generator.get_skill_filenames()
+        reader_dict, filelist = {}, []
+        for key, value in filenames_dict.items():
+            fname1, fname2 = value
+            if fname1 in self._zipfile.namelist():
+                file_minmax1 = cStringIO.StringIO(self._zipfile.read(fname1, "r"))
+                reader1 = csv.reader(file_minmax1)  # (name of skill, min1, max1)
+                filelist.append(file_minmax1)
             else:
-                print "file1 ", fname, " doesn't exist!"
-
-            # get min max of skill2
-            fname = row[2].strip()
-            if fname:
-                # not empty
-                if fname in self._zipfile.namelist():
-                    # read from zip file with StringIO wrapper
-                    file_minmax = cStringIO.StringIO(self._zipfile.read(fname, "r"))
-                    reader_minmax = csv.reader(file_minmax)  # (name of skill, min2, max)
-
-                    reader_minmax.next()  # skip header
-                    for row_minmax in reader_minmax:
-                        min_val, max_val = int(row_minmax[1]), int(row_minmax[2])
-                        skill_id = self._get_skill_id(row_minmax[0].strip())
-                        if skill_id is None:
-                            print "name:", row_minmax[0].decode("utf-8"), ", skill_id", skill_id
-                        elif skill_id in skill_dict:
-                            val = skill_dict[skill_id]
-                            skill_dict[skill_id] = (val[0], val[1], min_val, max_val)
-                        else:
-                            skill_dict[skill_id] = (0, 0, min_val, max_val)
-
-                    file_minmax.close()
-                else:
-                    print "file2 ", fname, " doesn't exist, all of the skill2 become zero"
-            else:
-                print "file2 ", fname, " doesn't exist, all of the skill2 become zero"
+                reader1 = None
 
-            # insert to minmax table
-            insert_sql = db_supports.MIN_MAX_TABLE_INSERT_SQL.format(table_name=table_name)
-            for k, v in skill_dict.items():
-                self._cursor.execute(insert_sql, (k,) + v)
+            if fname2 in self._zipfile.namelist():
+                file_minmax2 = cStringIO.StringIO(self._zipfile.read(fname2, "r"))
+                reader2 = csv.reader(file_minmax2)  # (name of skill, min1, max1)
+                filelist.append(file_minmax2)
+            else:
+                reader2 = None
+            reader_dict[key] = (reader1, reader2)
+        generator.insert_data(self._cursor, reader_dict)
 
-            cnt += 1
+        for _file in filelist:
+            _file.close()
 
         self._connect.commit()
         f.close()
@@ -241,130 +189,73 @@ class DataBaseGenerator(object):
         """
         print "load Second skill"
         # create master table of seed2 to skill2
-        self._cursor.execute(db_supports.SEED2_SKILL2_MASTER_TABLE_CREATE_SQL)
+        generator = seed2skill2table.Seed2Skill2TableGenerator()
         # read from zip file with StringIO wrapper
         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_SKILL2_FILE_NAME, "r"))
         reader = csv.reader(f)  # (omamori_name, filename of Second_skill)
 
-        reader.next()   # skip header row
-        cnt = 0
-        for row in reader:
-            table_name = db_supports.SEED2_SKILL2_TABLE_NAME.format(id=cnt)
-            create_sql = db_supports.SEED2_SKILL2_TABLE_CREATE_SQL.format(table_name=table_name)
-            insert_sql = db_supports.SEED2_SKILL2_MASTER_TABLE_INSERT_SQL
-            amu_id = self._get_amulet_id(row[0].strip())
-            if amu_id is None:
-                print "amulet name:", row[0].decode("utf-8")
-            self._cursor.execute(insert_sql, (amu_id, table_name))
-            self._cursor.execute(create_sql) # create skill table for each omamori
-
-            fname_skill = row[1].strip()
-            if fname_skill in self._zipfile.namelist(): 
-                # read from zip file with StringIO wrapper
-                file_skill = cStringIO.StringIO(self._zipfile.read(fname_skill, "r"))
-                reader_skill = csv.reader(file_skill)  # (seed2, skill1, skil2, ..., skill7)
-
-                reader_skill.next()  # skip header
-                insert_sql = db_supports.SEED2_SKILL2_TABLE_INSERT_SQL.format(table_name=table_name)
-                for row1 in reader_skill:
-                    seed = row1[0]
-                    vals = [seed]
-                    for name in row1[1:]:
-                        skill_id = self._get_skill_id(name.strip())
-                        if skill_id is None:
-                            print "name:", name.decode("utf-8"), ", skill_id", skill_id
-                        else:
-                            vals.append(skill_id)
-                    self._cursor.execute(insert_sql, tuple(vals))
-                file_skill.close()
-            else:
-                print "file1 ", fname_skill, " doesn't exist!"
+        generator.insert_master_data(self._cursor, reader)
+        filenames_dict = generator.get_skill_filenames()
+        reader_dict, filelist = {}, []
+        for key, fname in filenames_dict.items():
+            if fname in self._zipfile.namelist():
+                file_skill2 = cStringIO.StringIO(self._zipfile.read(fname, "r"))
+                reader = csv.reader(file_skill2)  # (seed2, skillname1,..., skillname7)
+                filelist.append(file_skill2)
+                reader_dict[key] = reader
+        generator.insert_data(self._cursor, reader_dict)
 
-            cnt += 1
+        for _file in filelist:
+            _file.close()
+
+        self._connect.commit()
         f.close()
+
         self._create_seed2_table_no_table()
         self._connect.commit()
 
     def _create_seed2_table_no_table(self):
         u""" Seed2に対応するテーブル値を格納するテーブルを作成する """
-        self._cursor.execute(db_supports.SEED2_TABLE_NO_TABLE_CREATE_SQL)
+        generator = seed2tablenumbertable.Seed2TableNumberTableGenerator()
 
         # read from zip file with StringIO wrapper
         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_TABLE_NO_FILE_NAME, "r"))
         reader = csv.reader(f)  # (seed2, table_no, no)
-
-        reader.next()   # skip header row
-        for row in reader:
-            vals = [int(r.strip()) for r in row]
-            seed, table_no, no = vals[0], vals[1], vals[2]
-            self._cursor.execute(db_supports.SEED2_TABLE_NO_TABLE_INSERT_SQL, (no, seed, table_no))
+        generator.insert_data(self._cursor, reader)
         self._connect.commit()
         f.close()
 
     def _create_seed2_inishie_table(self):
         u""" いにしえの錬金の第2Seed対応テーブルを作成する """
-        self._cursor.execute(db_supports.SEED2_INISHIE_TABLE_CREATE_SQL)
+        generator = seed2inishietable.Seed2InishieTableGenerator()
 
         # read from zip file with StringIO wrapper
         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_INISHIE_FILE_NAME, "r"))
         reader = csv.reader(f)  # (seed2, skill_name, threshold1, threshold2)
-
-        reader.next()   # skip header row
-        for row in reader:
-            try:
-                skill_id = self._get_skill_id(row[1].strip())
-                if skill_id is None:
-                    print "name:", row[1].strip().decode("utf-8"), ", skill_id", skill_id
-
-                seed, th1, th2 = row[0].strip(), row[2].strip(), row[3].strip()
-                self._cursor.execute(db_supports.SEED2_INISHIE_TABLE_INSERT_SQL, 
-                        (seed, skill_id, th1, th2))
-            except sqlite3.IntegrityError, e:
-                print "not unique:", row[0].decode("utf-8")
-                raise e
+        generator.insert_data(self._cursor, reader)
         self._connect.commit()
         f.close()
 
-    def _create_seed2_threshold1_table(self):
-        u""" csvファイルよりSEED2から判定値1へのテーブルを作成する
+    def _create_seed2_threshold_table(self):
+        u""" csvファイルよりSEED2から判定値1,2へのテーブルを作成する
         """
-        print "load Threshold1"
-        # for threshold1 table
-        # create threshold1 table from seed2
-        self._cursor.execute(db_supports.SEED2_THRESHOLD1_TABLE_CREATE_SQL)    
+        print "load Threshold1,2"
+        generator = seed2thresholdtable.Seed2ThresholdTableGenerator()
 
+        # for threshold1 table
         # read from zip file with StringIO wrapper
         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_THRESHOLD1_FILE_NAME, "r"))
         reader = csv.reader(f)  # (seed2, threshold1_1, threshold1_2, ..., threshold1_7)
-
-        reader.next()   # skip header row
-        insert_sql = db_supports.SEED2_THRESHOLD1_TABLE_INSERT_SQL
-        for row in reader:
-            val_tup = tuple([int(x.strip()) for x in row])
-            self._cursor.execute(insert_sql, val_tup)
-
+        generator.insert_data(self._cursor, reader, 1)
         f.close()
-        self._connect.commit()
 
-    def _create_seed2_threshold2_table(self):
-        u""" csvファイルよりSEED2から判定値2へのテーブルを作成する
-        """
-        print "load Threshold2"
         # for threshold2 table
-        # create threshold2 table from seed2
-        self._cursor.execute(db_supports.SEED2_THRESHOLD2_TABLE_CREATE_SQL)
-
         # read from zip file with StringIO wrapper
         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_THRESHOLD2_FILE_NAME, "r"))
         reader = csv.reader(f)  # (seed2, threshold2_1, threshold2_2, ..., threshold2_7)
-
-        reader.next()   # skip header row
-        insert_sql = db_supports.SEED2_THRESHOLD2_TABLE_INSERT_SQL
-        for row in reader:
-            val_tup = tuple([int(x.strip()) for x in row])
-            self._cursor.execute(insert_sql, val_tup)
-
+        generator.insert_data(self._cursor, reader, 2)
         f.close()
+
         self._connect.commit()
 
     def _create_sufficient_value_table(self):
@@ -372,41 +263,27 @@ class DataBaseGenerator(object):
         """
         print "load Sufficient Value"
         # create master table of sufficient value
-        self._cursor.execute(db_supports.SUFFICIENT_MASTER_TABLE_CREATE_SQL)
+        generator = sufficienttable.SufficientTableGenerator()
         # read from zip file with StringIO wrapper
         f = cStringIO.StringIO(self._zipfile.read(db_supports.SUFFICIENT_FILE_NAME, "r"))
         reader = csv.reader(f)  # (omamori_name, filename of sufficient values)
 
-        reader.next()   # skip header row
-        cnt = 0
-        for row in reader:
-            table_name = db_supports.SUFFICIENT_TABLE_NAME.format(id=cnt)
-            create_sql = db_supports.SUFFICIENT_TABLE_CREATE_SQL.format(table_name=table_name)
-            insert_sql = db_supports.SUFFICIENT_MASTER_TABLE_INSERT_SQL
-            amu_id = self._get_amulet_id(row[0].strip())
-            if amu_id is None:
-                print "amulet name:", row[0].decode("utf-8")
-            self._cursor.execute(insert_sql, (amu_id, table_name))
-            self._cursor.execute(create_sql) # create skill table for each omamori
-
-            fname_threshold = row[1].strip()
-            if fname_threshold in self._zipfile.namelist():
-                # read from zip file with StringIO wrapper
-                file_threshold = cStringIO.StringIO(self._zipfile.read(fname_threshold, "r"))
-                reader_threshold = csv.reader(file_threshold)  # (sufficient value, slot1 threshold, ..., slot3 threshold)
-
-                reader_threshold.next()  # skip header
-                insert_sql = db_supports.SUFFICIENT_TABLE_INSERT_SQL.format(table_name=table_name)
-                for row_vals in reader_threshold:
-                    val_tup = tuple([int(x.strip()) for x in row_vals])
-                    self._cursor.execute(insert_sql, val_tup)
-                file_threshold.close()
-            else:
-                print "file1 ", fname_threshold, " doesn't exist!"
+        generator.insert_master_data(self._cursor, reader)
+        filenames_dict = generator.get_skill_filenames()
+        reader_dict, filelist = {}, []
+        for key, fname in filenames_dict.items():
+            if fname in self._zipfile.namelist():
+                file_suff = cStringIO.StringIO(self._zipfile.read(fname, "r"))
+                reader = csv.reader(file_suff)  # (seed2, skillname1,..., skillname7)
+                filelist.append(file_suff)
+                reader_dict[key] = reader
+        generator.insert_data(self._cursor, reader_dict)
+
+        for _file in filelist:
+            _file.close()
 
-            cnt += 1
-        f.close()
         self._connect.commit()
+        f.close()
 
     def _create_seed1_tenun_table(self):
         u""" csvファイルより天運の錬金結果からSEED1へのテーブルを作成する
@@ -414,49 +291,18 @@ class DataBaseGenerator(object):
         print "load seed1"
         # for seed1 table
         # tenun555
-        self._cursor.execute(db_supports.SEED1_TENUN555_TABLE_CREATE_SQL)
+        generator = seed1tenuntable.Seed1TenunTableGenerator()
         # read from zip file with StringIO wrapper
         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED1_TENUN555_FILE_NAME, "r"))
-        # (table_no, no, seed1, result_no, amu1, amu2, ...,, amu6, skill_id1, skill_id2, ..., skill_id6)
         reader = csv.reader(f)
-        reader.next()   # skip header row
-        reader.next()   # skip header row
-        insert_sql = db_supports.SEED1_TENUN555_TABLE_INSERT_SQL
-        for row in reader:
-            vals = [x.strip() for x in row]
-            table_no, no, seed1, result_no = vals[0], vals[1], vals[2],vals[3]
-            amu_ids = [self._get_amulet_id(x) for x in vals[4:10]]
-            if len(amu_ids) < 6:
-                amu_ids += [None] * (6-len(amu_ids))
-            skill_ids = [self._get_skill_id(x) for x in vals[10:16]]
-            if len(skill_ids) < 6:
-                skill_ids += [None] * (6-len(skill_ids))
-            vals = [no, table_no, seed1, result_no] + amu_ids + skill_ids
-            self._cursor.execute(insert_sql, 
-                    tuple([int(x) if x is not None else db_supports.NO_DATA for x in vals]))
+        generator.insert_data(self._cursor, reader, mh4constnumbers.KEY_TENUN555)
         f.close()
 
         # tenun888
-        self._cursor.execute(db_supports.SEED1_TENUN888_TABLE_CREATE_SQL)
         # read from zip file with StringIO wrapper
         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED1_TENUN888_FILE_NAME, "r"))
-        # (table_no, no, seed1, result_no, amu1, amu2, ...,, amu7, skill_id1, skill_id2, ..., skill_id7)
         reader = csv.reader(f)
-        reader.next()   # skip header row
-        reader.next()   # skip header row
-        insert_sql = db_supports.SEED1_TENUN888_TABLE_INSERT_SQL
-        for row in reader:
-            vals = [x.strip() for x in row]
-            table_no, no, seed1, result_no = vals[0], vals[1], vals[2], vals[3]
-            amu_ids = [self._get_amulet_id(x) for x in vals[4:11]]
-            if len(amu_ids) < 7:
-                amu_ids += [None] * (7-len(amu_ids))
-            skill_ids = [self._get_skill_id(x) for x in vals[11:18]]
-            if len(skill_ids) < 7:
-                skill_ids += [None] * (7-len(skill_ids))
-            vals = [no, table_no, seed1, result_no] + amu_ids + skill_ids
-            self._cursor.execute(insert_sql, 
-                    tuple([int(x) if x is not None else db_supports.NO_DATA for x in vals]))
+        generator.insert_data(self._cursor, reader, mh4constnumbers.KEY_TENUN888)
         f.close()
         self._connect.commit()