Server wiki in Go cu suport MS SQL

Categorii: Programare

07-Apr-2015 17:24 - 699 vizionari

Go este un limbaj minunat si odata ce te obisnuiesti cu sintaxa, programarea este o placere.
Chiar daca aproape jumatate din programele go testeaza daca apare o eroare si o raporteaza, este ok: acesta este stilul de programare go.

In continuare este prezentat un exemplu complet functional de server de web, care serveste pagini de tip wiki dintr-o baza de date de tip Microsoft SQL.

Paginile wiki (in dialectul implementat de mine aici) sunt pagini text in care linkurile sunt referite intre paranteze drepte: [link1] sau [link1:Acesta este link 1]
Linkurile care nu exista (nu sunt gasite ca inregistrari in tabela SQL) sunt afisate taiate de o linie.
La pornirea aplicatiei sunt create automat tabelele si sunt populate cu cateva pagini wiki.
Modificarea unei pagini nu suprascrie versiunea anterioara, ci se creaza o noua intrare in tabela si se actualizeaza versiunea paginii.
Programul nu este optimizat si serveste ca exemplu de start.
Posibile imbunatatiri (folosite de mine in alte sisteme in Python): implementarea unui sistem de cautare in pagini, afisarea listei cu versiunilor anterioare ale paginilor si selectia lor pentru vizualizare, afisarea datei cand a fost creata pagina si cand a fost modificata, implementarea unor sabloane utile la crearea unei pagini, import si export de pagini in format zip, etc.

Codul sursa este formatat de aplicatia highlight.js (scrisa de Ivan Sagalaev) de la https://highlightjs.org/

In folderul aplicatiei, in subfolderul templates se creaza patru fisiere-template:

 

header.tmpl


{{define "header"}}
<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="utf-8">
	<meta name="description" content="Project in Go using gin">
	<meta name="keywords" content="go, golang, microframework, mvc">
	<title>{{ .title }}</title>
	<base href="/" />

<style type="text/css">
<!-- 
a:link { text-decoration: none; }
a:visited { text-decoration: none; }
a:hover { text-decoration: underline; }
a:active { text-decoration: underline; }
-->
</style>

</head>

<body>

<h1>
    {{ .title }}
</h1>
Pagina: {{.pagename}} - <a href="{{.pagename}}/edit">edit</a> - <a href="/">Home</a>
<hr>

{{end}}

 

footer.tmpl


{{define "footer"}}
<hr>
	<div id="footer" align="right">
Server de test
	</div>

  </body>
</html>
{{end}}

 

view.tmpl


{{define "view"}}
{{template "header" .}}

<!-- body -->
{{ .body }}
<!-- /body -->

{{template "footer" .}}
{{end}}

 

edit.tmpl


{{define "edit"}}
{{template "header" .}}

<form method="POST" action="/">
<input type="hidden" name="pagename" value="{{.pagename}}" />
Title: <input type="text" name="title" value="{{.title}}" />
<input type="submit" value="Save" /><br>
<textarea cols="80" rows="15" name="body">{{ .body }}</textarea>
</form>

{{template "footer" .}}
{{end}}

 

Comenzile SQL sunt pastrate intr-un fisier inspirat de aplicatia dotsql -  https://github.com/gchaincl/dotsql, dar intr-un mod mult mai simplu (o singura functie de citire a fisierului) si numit

wiki-mssql.sql


-- name: test-wikipages
SELECT 1 FROM wikipages

-- name: test-wikiversions
SELECT 1 FROM wikiversions

-- name: drop-wikipages
DROP TABLE wikipages

-- name: drop-wikiversions
DROP TABLE wikiversions

-- name: create-wikipages
CREATE TABLE wikipages (
	id INTEGER IDENTITY(1,1) PRIMARY KEY,
	pagename nvarchar(64),
	version_id INTEGER
)

-- name: create-wikiversions
CREATE TABLE wikiversions (
	id INTEGER IDENTITY(1,1) PRIMARY KEY,
	page_id INTEGER,
	title nvarchar(64),
	body nvarchar(4000),
	creation_time DATETIME DEFAULT CURRENT_TIMESTAMP,
	modification_time DATETIME DEFAULT CURRENT_TIMESTAMP
)

-- name: insert-wikipages
INSERT INTO wikipages (pagename, version_id) VALUES ($1, $2)

-- name: exist-wikipage
SELECT id FROM wikipages WHERE pagename = $1

-- name: get-page
SELECT wikiversions.title, wikiversions.body FROM wikipages JOIN wikiversions ON wikipages.version_id = wikiversions.id WHERE pagename = $1;

-- name: get-wikiversion
SELECT title, body FROM wikiversions WHERE id = $1

-- name: insert-page
DECLARE @id_page INTEGER, @id_version INTEGER;
INSERT INTO wikipages (pagename, version_id) VALUES ($1, 0);
SET @id_page = @@IDENTITY;
INSERT INTO wikiversions (page_id, title, body) VALUES (@id_page, $2, $3);
SET @id_version = @@IDENTITY;
UPDATE wikipages SET version_id = @id_version WHERE id = @id_page;

-- name: update-page
DECLARE @id_page INTEGER, @id_version INTEGER;
IF NOT EXISTS(SELECT id FROM wikipages WHERE pagename = $1)
BEGIN
INSERT INTO wikipages (pagename, version_id) VALUES ($1, 0);
END
SELECT @id_page = id FROM wikipages WHERE pagename = $1;
INSERT INTO wikiversions (page_id, title, body) VALUES (@id_page, $2, $3);
SET @id_version = @@IDENTITY;
UPDATE wikipages SET version_id = @id_version WHERE id = @id_page;

 

 

Codul serverului, scris in go este:


/*

Wiki server cu gin si MS SQL

*/

package main

import (
	"bufio"
	"database/sql"
	"fmt"
	_ "github.com/denisenkom/go-mssqldb"
	"github.com/gin-gonic/gin"
	"html/template"
	"net/http"
	"os"
	"regexp"
	"strings"
)

type dict map[string]string

//variabile globale
var (
	r       = gin.Default() //router sau engine
	conn    *sql.DB
	sqlFunc = dict{}

	//ms sql
	server   = "localhost"
	user     = "utilizator_MS_SQL"
	password = "parola_utilizator_MS_SQL"
	port     = 1433
	database = "wiki"
)

func index(c *gin.Context) {
	var action = "view"
	var pagename = c.Params.ByName("page")
	var title, body string
	if pagename == "" || pagename == "/" {
		c.Redirect(http.StatusMovedPermanently, "/FrontPage")
		return
	}
	var L = strings.Split(pagename, "/")
	if L[len(L)-1] == "edit" {
		action = "edit"
		pagename = strings.Join(L[:len(L)-1], "/")
	}
	row := conn.QueryRow(sqlFunc["get-page"], pagename)
	err := row.Scan(&title, &body)
	if err != nil {
		title = "not found"
		body = "not found"
	}
	var data gin.H
	if action == "edit" {
		data = gin.H{
			"pagename": pagename,
			"title":    title,
			"body":     body}
	} else {
		data = gin.H{
			"pagename": pagename,
			"title":    title,
			"body":     template.HTML(processWikiPages(body))}
	}
	c.HTML(http.StatusOK, action, data)
}

func save(c *gin.Context) {
	c.Request.ParseForm()
	pagename := c.Request.Form.Get("pagename")
	title := c.Request.Form.Get("title")
	body := c.Request.Form.Get("body")
	_, err := conn.Exec(sqlFunc["update-page"], pagename, title, body)
	TestError("UPDATE page", err)
	c.Redirect(http.StatusMovedPermanently, pagename)
}

func main() {
	fmt.Println("Start wiki server")
	var err error
	connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s", server, user, password, port, database)
	fmt.Printf(" connString:%s\n", connString)
	conn, err = sql.Open("mssql", connString)
	TestError("connect", err)
	defer conn.Close()
	err = conn.Ping()
	TestError("ping", err)
	sqlFunc = ReadMap("wiki-mssql.sql") //aici citesc fisierul de comenzi SQL
	if false {
		//nu conteaza daca nu exista tabelele, eroarea este ignorata
		conn.Exec(sqlFunc["drop-wikipages"])
		conn.Exec(sqlFunc["drop-wikiversions"])
	}
	_, err = conn.Exec(sqlFunc["test-wikipages"])
	if err != nil {
		fmt.Println("CREATE TABLE wiki pages")
		_, err = conn.Exec(sqlFunc["create-wikipages"])
		TestError("create table wikipages", err)

		//DROP wikiversions pentru ca nu exista wikipages
		conn.Exec(sqlFunc["drop-wikiversions"])
		fmt.Println("CREATE TABLE wiki versions")
		_, err = conn.Exec(sqlFunc["create-wikiversions"])
		TestError("create table wikiversions", err)

		//insert pagini
		_, err = conn.Exec(sqlFunc["insert-page"], "/FrontPage", "Pagina de start", "[pagina1] - [pagina2] - [test/p1] - [test/p2]")
		TestError("INSERT page", err)
		_, err = conn.Exec(sqlFunc["insert-page"], "/pagina1", "Pagina unu", "1111 \n[pagina1] - [pagina2] - [test/p1] - [test/p2]")
		TestError("INSERT page", err)
		_, err = conn.Exec(sqlFunc["insert-page"], "/pagina2", "Pagina doi", "2222 \n[pagina1] - [pagina2] - [test/p1] - [test/p2]")
		TestError("INSERT page", err)
	}
	r.LoadHTMLGlob("templates/*")
	r.GET("/*page", index)
	r.POST("/", save)
	// Listen and server on 0.0.0.0:8080
	//err = r.Run("localhost:8080")
	err = r.RunTLS("localhost:443", "server.pem", "server.key")
	TestError("server de web", err)
}

func TestError(msg string, err error) {
	if err != nil {
		fmt.Println(msg)
		panic(err)
	}
}

//
//regexp
//
func processWikiPages(s string) string {
	var ret string
	var re *regexp.Regexp
	var ss = strings.Replace(s, "\n", "\n<br>", -1)
	re = regexp.MustCompile(`\[([^]]+)\]`) //potrivire [xxxx]
	//re = regexp.MustCompile(`\[([^]]+):([^]]+)\]`) //potrivire [lllll:tttttt]
	ret = string(re.ReplaceAllFunc([]byte(ss), myFunc1))
	re = regexp.MustCompile(`(https?://.+)`)
	ret = string(re.ReplaceAllFunc([]byte(ret), myFunc2))
	return ret
}
func myFunc1(b []byte) []byte {
	//[link] si [link:title]
	var s = strings.Replace(strings.Replace(string(b), "[", "", -1), "]", "", -1)
	var l = strings.Split(s, ":")
	var ret, style string
	var id int64
	var pagename string
	if l[0][:1] == "/" {
		pagename = l[0]
	} else {
		pagename = "/" + l[0]
	}
	row := conn.QueryRow(sqlFunc["exist-wikipage"], pagename)
	err := row.Scan(&id)
	if err == nil {
		style = ""
	} else {
		style = ` style="text-decoration: line-through"`
	}
	if len(l) > 1 {
		ret = fmt.Sprintf(`<a href="%s" %s>%s</a>`, pagename, style, l[1])
	} else {
		ret = fmt.Sprintf(`<a href="%s" %s>%s</a>`, pagename, style, pagename)
	}
	return []byte(ret)
}
func myFunc2(b []byte) []byte {
	//
	var s = string(b)
	ret := fmt.Sprintf(`<a href="%s">%s</a>`, s, s)
	return []byte(ret)
}

//
//citire functii SQL
//
func ReadMap(filename string) dict {
	f, err := os.Open(filename)
	if err != nil {
		fmt.Println(err)
		return dict{}
	}
	defer f.Close()
	r := bufio.NewReaderSize(f, 2048)
	var sectionName, sectionBody string
	sectionName = "initial"
	sectionBody = ""
	ret := dict{}
	for {
		line, isPrefix, err := r.ReadLine()
		if isPrefix {
			fmt.Println("buffer size to small")
			return dict{}
		}
		if err != nil {
			//probabil eroare de EOF
			//fmt.Println("eroare:", err)
			break
		}
		s := string(line)
		if len(s) < 1 {
			continue
		}
		//determinare inceput de procedura SQL
		const tagNume = "-- name: "
		ix := strings.Index(s, tagNume)
		if ix >= 0 { //gasit tag
			//mai intai salveaza sectiunea anterioara
			ret[sectionName] = sectionBody
			sectionBody = ""
			sectionName = s[ix+len(tagNume):]
		} else {
			//adauga la Body
			if len(sectionBody) < 1 {
				sectionBody = s
			} else {
				sectionBody = sectionBody + "\n" + s
			}
		}
	}
	//salveaza sectiunea finala
	ret[sectionName] = sectionBody
	return ret
}

func (m dict) Keys() (keys []string) {
	for k := range m {
		keys = append(keys, k)
	}
	return keys
}
func (m dict) List(nume string) {
	fmt.Println(nume)
	for k, v := range m {
		fmt.Printf("[%s]\n%s\n", k, v)
	}
}


Ultimele pagini: RSS

Alte adrese de Internet

Categorii

Istoric



Contorizari incepand cu 9 iunie 2014:
Flag Counter

Atentie: Continutul acestui server reprezinta ideile mele si acestea pot fi gresite.