func evolve(db *sqlx.DB, startVersion string, appName string) {
files, _ := ioutil.ReadDir("./schema")
sort.Sort(ByName(files))
i := 0
if startVersion != "-1" {
for ; i < len(files); i++ {
if stripExt(files[i].Name()) == startVersion {
i++
break
}
}
files = files[i:]
}
for _, file := range files {
log.Println("running file:", "./schema/"+file.Name())
commands, err := ioutil.ReadFile("./schema/" + file.Name())
if err != nil {
log.Fatal(err)
}
_ = db.MustExec(string(commands))
if err != nil {
log.Fatal(err)
}
_ = db.MustExec(fmt.Sprintf("update %s_meta set version=$1", appName), stripExt(file.Name()))
if err != nil {
log.Fatal(err)
}
}
}
func initDB(db *sqlx.DB) {
db.MustExec(schema)
tx := db.MustBegin()
tx.MustExec("INSERT into event (date, title, description, font) values ($1, $2, $3, $4)", timeToString(time.Now()), "Do something fun!", "description goes here", "Helvetica")
tx.MustExec("INSERT into event (date, title, description, font) values ($1, $2, $3, $4)", timeToString(time.Now().Add(time.Hour*24)), "Do something Else!", "discription goes here", "Helvetica")
tx.Commit()
}
func MustCreate(d *sqlx.DB) *postgresStore {
// Set the timezone for the store. This is needed for the logic here.
// This obviously assumes that noone else will change this.
// TODO: make this more robust.
d.MustExec(setTimeZoneStr)
s := &postgresStore{db: d}
return s
}
func buildTables(db *sqlx.DB) {
userTable :=
`CREATE TABLE users (
id text NOT NULL,
name text,
email text,
jwt text,
passwordhash text);`
db.MustExec(userTable)
}
func insertNotices(conn *sqlx.DB, results []NoticeResult) {
sql := conn.Rebind(`
INSERT INTO notice(id, published)
SELECT ?, ?
WHERE NOT EXISTS (SELECT 1 FROM notice WHERE id = ?)`)
for _, result := range results {
conn.MustExec(
sql, result.DocumentNumber, result.Published,
result.DocumentNumber)
}
insertAgencies(conn, results)
}
func insertAgencies(conn *sqlx.DB, results []NoticeResult) {
delete := conn.Rebind("DELETE FROM notice_agency WHERE notice_id = ?")
insert := conn.Rebind(`
INSERT INTO notice_agency (notice_id, agency)
VALUES (?, ?)`)
for _, result := range results {
conn.MustExec(delete, result.DocumentNumber)
for _, agency := range result.Agencies {
conn.MustExec(insert, result.DocumentNumber, agency)
}
}
}
func CreateDBSchema(db *sqlx.DB) {
db.MustExec(`
CREATE TABLE IF NOT EXISTS "todo_items" (
"id" integer,
"title" varchar(255),
"description" varchar(255),
"done" bool,
"done_at" datetime,
"created_at" datetime,
"updated_at" datetime,
"group_id" integer ,
PRIMARY KEY ("id")
);
`)
db.MustExec(`
CREATE INDEX IF NOT EXISTS idx_todo_items_group_id ON "todo_items"("group_id");
`)
db.MustExec(`
CREATE TABLE IF NOT EXISTS "todo_groups" (
"id" integer,
"title" varchar(255),
"created_at" datetime,
"updated_at" datetime,
"list_id" integer ,
PRIMARY KEY ("id")
);
`)
db.MustExec(`
CREATE INDEX IF NOT EXISTS idx_todo_groups_list_id ON "todo_groups"("list_id");
`)
db.MustExec(`
CREATE TABLE IF NOT EXISTS "todo_lists" (
"id" integer,
"title" varchar(255),
"description" varchar(255),
"created_at" datetime,
"updated_at" datetime ,
PRIMARY KEY ("id")
);
`)
}
func Export(w *sync.WaitGroup, c chan string, db *sqlx.DB, format *string) {
w.Add(1)
defer w.Done()
helpers.DropAndCreateTable(schema, tableName, db)
var format2 string
format2 = *format
fileName, err2 := helpers.SearchFile(tableName, format2)
if err2 != nil {
fmt.Println("Error searching file:", err2)
return
}
pathToFile := format2 + "/" + fileName
// Подсчитываем, сколько элементов нужно обработать
//fmt.Println("Подсчет строк")
// _, err := helpers.CountElementsInXML(pathToFile, elementName)
// if err != nil {
// fmt.Println("Error counting elements in XML file:", err)
// return
// }
//fmt.Println("\nВ ", elementName, " содержится ", countedElements, " строк")
xmlFile, err := os.Open(pathToFile)
if err != nil {
fmt.Println("Error opening file:", err)
return
}
defer xmlFile.Close()
decoder := xml.NewDecoder(xmlFile)
total := 0
var inElement string
for {
// Read tokens from the XML document in a stream.
t, _ := decoder.Token()
if t == nil {
break
}
// Inspect the type of the token just read.
switch se := t.(type) {
case xml.StartElement:
// If we just read a StartElement token
inElement = se.Name.Local
if inElement == elementName {
total++
var item XmlObject
// decode a whole chunk of following XML into the
// variable item which is a ActualStatus (se above)
err = decoder.DecodeElement(&item, &se)
if err != nil {
fmt.Println("Error in decode element:", err)
return
}
query := "INSERT INTO " + tableName + " (center_st_id, name) VALUES ($1, $2)"
db.MustExec(query, item.CENTERSTID, item.NAME)
c <- helpers.PrintRowsAffected(elementName, total)
}
default:
}
}
//fmt.Printf("\nTotal processed items in "+elementName+": %d \n", total)
}
func Export(w *sync.WaitGroup, c chan string, db *sqlx.DB, format *string) {
w.Add(1)
defer w.Done()
helpers.DropAndCreateTable(schema, tableName, db)
var format2 string
format2 = *format
fileName, err2 := helpers.SearchFile(tableName+"_", format2)
if err2 != nil {
fmt.Println("Error searching file:", err2)
return
}
pathToFile := format2 + "/" + fileName
xmlFile, err := os.Open(pathToFile)
if err != nil {
fmt.Println("Error opening file:", err)
return
}
defer xmlFile.Close()
decoder := xml.NewDecoder(xmlFile)
total := 0
var inElement string
for {
// Read tokens from the XML document in a stream.
t, _ := decoder.Token()
if t == nil {
break
}
// Inspect the type of the token just read.
switch se := t.(type) {
case xml.StartElement:
// If we just read a StartElement token
inElement = se.Name.Local
if inElement == elementName {
total++
var item XmlObject
// decode a whole chunk of following XML into the
// variable item which is a ActualStatus (se above)
err = decoder.DecodeElement(&item, &se)
if err != nil {
fmt.Println("Error in decode element:", err)
return
}
query := `INSERT INTO ` + tableName + ` (house_guid,
postal_code,
ifns_fl,
terr_ifns_fl,
ifns_ul,
terr_ifns_ul,
okato,
oktmo,
update_date,
house_num,
est_status,
build_num,
struc_num,
str_status,
house_id,
ao_guid,
start_date,
end_date,
stat_status,
norm_doc,
counter
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10,
$11, $12, $13, $14, $15, $16, $17, $18, $19, $20,
$21)`
db.MustExec(query,
item.HOUSEGUID,
item.POSTALCODE,
item.IFNSFL,
item.TERRIFNSFL,
item.IFNSUL,
item.TERRIFNSUL,
item.OKATO,
item.OKTMO,
item.UPDATEDATE,
item.HOUSENUM,
item.ESTSTATUS,
item.BUILDNUM,
item.STRUCNUM,
item.STRSTATUS,
item.HOUSEID,
item.AOGUID,
item.STARTDATE,
item.ENDDATE,
item.STATSTATUS,
item.NORMDOC,
item.COUNTER)
c <- helpers.PrintRowsAffected(elementName, total)
}
default:
}
}
//fmt.Printf("Total processed items in AddressObjects: %d \n", total)
}
func (p *Post) Save(db *sqlx.DB) {
db.MustExec("UPDATE posts SET title=$1,shortmessage=$2, body=$3,modified=NOW(),created=$8,status=$4,tags=$5,slug=$6 WHERE id=$7",
p.Title, p.ShortMessage, p.Body, p.Status, p.Tags, p.Slug, p.Id, p.Created)
}
func DeletePost(db *sqlx.DB, post_id int) {
db.MustExec("DELETE FROM posts WHERE id=$1", post_id)
}
// NewSQLTodo returns a new sql todo manager
func NewSQLTodo(db *sqlx.DB) TodoDatabase {
db.MustExec(createSchema)
return &SQLTodo{db}
}
