gKq4VV0z

1package main
2
3import (
4	"database/sql"
5	"flag"
6	"fmt"
7	"io/ioutil"
8	"log"
9	"os"
10	"path/filepath"
11	"strings"
12
13	"github.com/go-sql-driver/mysql"
14
15	"github.com/joho/sqltocsv"
16	_ "github.com/mattn/go-sqlite3"
17)
18
19func main() {
20	path := flag.String("path", "", "請提供火車安裝路徑")
21	flag.Parse()
22	dbPath := fmt.Sprintf("%s/Configuration/config.db3", *path)
23	fmt.Println(dbPath)
24	db, err := sql.Open("sqlite3", dbPath)
25	if err != nil {
26		panic(err)
27	}
28	defer db.Close()
29
30	rows, err := db.Query("SELECT Job.JobId,Job.JobName FROM Job INNER JOIN Site ON Site.SiteId=Job.SiteId WHERE Site.SiteName LIKE '%_database'")
31	if err != nil {
32		log.Fatal(err)
33	}
34	defer rows.Close()
35	for rows.Next() {
36		var JobId int
37		var JobName string
38		err = rows.Scan(&JobId, &JobName)
39		if err != nil {
40			log.Fatal(err)
41		}
42		fmt.Println(JobId, JobName)
43		tmpPath := fmt.Sprintf("%s/Data/%d/SpiderResult.db3", *path, JobId)
44		if _, err := os.Stat(tmpPath); os.IsNotExist(err) {
45			// path/to/whatever does not exist
46			fmt.Println(tmpPath, " not exist")
47			continue
48		}
49
50		dbsub, suberr := sql.Open("sqlite3", tmpPath)
51		if suberr != nil {
52			log.Fatal(suberr)
53		}
54
55		subrows, err := dbsub.Query("SELECT * FROM \"Content\" WHERE \"已采\" = '1'")
56
57		if err != nil {
58			log.Fatal(err)
59		}
60
61		Cols, err := subrows.Columns()
62		fmt.Println(Cols)
63		if err != nil {
64			log.Fatal(err)
65		}
66
67		csvfile, err := ioutil.TempFile(os.TempDir(), "prefix")
68
69		if err != nil {
70			log.Fatal(err)
71		}
72
73		sqls := genCreateSQL(Cols, csvfile.Name(), JobName)
74		csverr := sqltocsv.WriteFile(csvfile.Name(), subrows)
75		if csverr != nil {
76			log.Fatal(csverr)
77		}
78		mysqlP(sqls)
79		defer subrows.Close()
80		dbsub.Close()
81		err = os.Remove(csvfile.Name())
82		if err != nil {
83			fmt.Println(err)
84		}
85	}
86	db.Close()
87}
88
89func genCreateSQL(cols []string, tmpTblName string, realTbllName string) []string {
90	var sqls []string
91	var fields []string
92	tmpCsvPath := tmpTblName
93	tmpTblName = filepath.Base(tmpTblName)
94	sqls = append(sqls, fmt.Sprintf("DROP TABLE IF EXISTS `%s`", realTbllName))
95	tmp := "CREATE TABLE `%s` (%s)"
96	for _, fd := range cols {
97		if fd == "ID" {
98			fields = append(fields, fmt.Sprintf("`%s` INT", fd))
99		} else if fd == "已采" {
100			fields = append(fields, fmt.Sprintf("`%s` INT", fd))
101		} else if fd == "已发" {
102			fields = append(fields, fmt.Sprintf("`%s` INT", fd))
103		} else {
104			fields = append(fields, fmt.Sprintf("`%s` TEXT", fd))
105		}
106	}
107
108	sqls = append(sqls, fmt.Sprintf(tmp, tmpTblName, strings.Join(fields, ",")))
109	mysql.RegisterLocalFile(strings.Replace(tmpCsvPath, `\`, `/`, -1))
110	tmp = fmt.Sprintf("LOAD DATA LOCAL INFILE '%s' INTO TABLE `%s` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '%s' IGNORE 1 ROWS", strings.Replace(tmpCsvPath, `\`, `/`, -1), tmpTblName, `\n`)
111	sqls = append(sqls, tmp)
112	tmp = fmt.Sprintf("RENAME TABLE `%s` TO `%s`", tmpTblName, realTbllName)
113	sqls = append(sqls, tmp)
114	return sqls
115}
116
117func mysqlP(sqls []string) {
118  db, err := sql.Open("mysql", "user:pass@(127.0.0.1:3306)/DataBase?charset=utf8")
119	defer db.Close()
120	if err != nil {
121		panic(err.Error())
122	}
123
124	for _, sql := range sqls {
125		_, err := db.Exec(sql)
126		if err != nil {
127			fmt.Println(err, sql)
128		} else {
129			fmt.Println(sql)
130		}
131	}
132	db.Close()
133}