vlambda博客
学习文章列表

go mod init github.com/unrotten/hello-world-web

CREATE TYPE article_state as ENUM ('unaudited','online','offline','deleted')  CREATE TABLE public.article ( id int8 NOT NULL, -- 主键 sn varchar(32) NOT NULL, -- 文章序号 title varchar(255) NOT NULL, -- 文章标题 uid int8 NOT NULL, -- 作者id cover varchar(255) NULL, -- 封面 "content" text NOT NULL, -- 内容，markdown格式 tags _varchar NULL, -- 文章标签 state article_state NOT NULL DEFAULT 'unaudited’::article_state, -- 状态:'unaudited'-未审核,'online'-已上线,'offline'-已下线,'deleted'-已删除 created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间 updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 更新时间 deleted_at timestamp NOT NULL, -- 删除时间 CONSTRAINT article_pkey PRIMARY KEY (id), CONSTRAINT sn UNIQUE (sn) );  COMMENT ON COLUMN public.article.id IS '主键'; COMMENT ON COLUMN public.article.sn IS '文章序号'; COMMENT ON COLUMN public.article.title IS '文章标题'; COMMENT ON COLUMN public.article.uid IS '作者id'; COMMENT ON COLUMN public.article.cover IS '封面'; COMMENT ON COLUMN public.article."content" IS '内容，markdown格式'; COMMENT ON COLUMN public.article.tags IS '文章标签'; COMMENT ON COLUMN public.article.state IS '状态:''unaudited''-未审核,''online''-已上线,''offline''-已下线,''deleted''-已删除'; COMMENT ON COLUMN public.article.created_at IS '创建时间'; COMMENT ON COLUMN public.article.updated_at IS '更新时间'; COMMENT ON COLUMN public.article.deleted_at IS '删除时间';

CREATE TABLE "public"."article_ex" ( "aid" int8 NOT NULL, "view_num" int4 NOT NULL DEFAULT 0, "cmt_num" int4 NOT NULL DEFAULT 0, "zan_num" int4 NOT NULL DEFAULT 0, "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, "deleted_at" timestamp(6) NOT NULL, PRIMARY KEY ("aid") ) ;  COMMENT ON COLUMN "public"."article_ex"."aid" IS '文章ID';  COMMENT ON COLUMN "public"."article_ex"."view_num" IS '浏览数';  COMMENT ON COLUMN "public"."article_ex"."cmt_num" IS '评论数';  COMMENT ON COLUMN "public"."article_ex"."zan_num" IS '点赞数';  COMMENT ON COLUMN "public"."article_ex"."created_at" IS '创建时间';  COMMENT ON COLUMN "public"."article_ex"."updated_at" IS '更新时间';  COMMENT ON COLUMN "public"."article_ex"."deleted_at" IS '删除时间';  COMMENT ON TABLE "public"."article_ex" IS '文章扩展表';

CREATE TYPE user_state as ENUM (‘unsign’,’normal,’forbidden’,’freeze’) CREATE TYPE gender as ENUM (‘man’,’woman’,’unknown')  CREATE TABLE "public"."user" ( "id" int8 NOT NULL, "username" varchar(32) COLLATE "pg_catalog"."default" NOT NULL, "email" varchar(32) COLLATE "pg_catalog"."default" NOT NULL, "password" varchar(255) COLLATE "pg_catalog"."default" NOT NULL, "avatar" varchar(127) COLLATE "pg_catalog"."default" NOT NULL, "gender" "public"."gender" NOT NULL DEFAULT 'unknown'::gender, "introduce" text COLLATE "pg_catalog"."default", "state" "public"."user_state" NOT NULL DEFAULT 'unsign'::user_state, "root" bool NOT NULL DEFAULT false, "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, "deleted_at" timestamp(6) NOT NULL, PRIMARY KEY ("id") ) ;  COMMENT ON COLUMN "public"."user"."id" IS 'ID';  COMMENT ON COLUMN "public"."user"."username" IS '用户名';  COMMENT ON COLUMN "public"."user"."email" IS '注册邮箱';  COMMENT ON COLUMN "public"."user"."password" IS '密码';  COMMENT ON COLUMN "public"."user"."avatar" IS '头像';  COMMENT ON COLUMN "public"."user"."gender" IS '性别：''man''-男,''woman''-女,''unknown''-保密';  COMMENT ON COLUMN "public"."user"."introduce" IS '个人简介';  COMMENT ON COLUMN "public"."user"."state" IS '状态:''unsign''-未认证,''normal''-正常,''forbidden''-禁止发言,''freeze''-冻结';  COMMENT ON COLUMN "public"."user"."root" IS '是否管理员';  COMMENT ON COLUMN "public"."user"."created_at" IS '创建时间';  COMMENT ON COLUMN "public"."user"."updated_at" IS '更新时间';  COMMENT ON COLUMN "public"."user"."deleted_at" IS '删除时间';

CREATE TABLE "public"."user_count" ( "uid" int8 NOT NULL, "fans_num" int4 NOT NULL DEFAULT 0, "follow_num" int4 NOT NULL DEFAULT 0, "article_num" int4 NOT NULL DEFAULT 0, "words" int4 NOT NULL DEFAULT 0, "zan_num" int4 NOT NULL DEFAULT 0, "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, "deleted_at" timestamp(6) NOT NULL, PRIMARY KEY ("uid") ) ;  COMMENT ON COLUMN "public"."user_count"."uid" IS '用户ID';  COMMENT ON COLUMN "public"."user_count"."fans_num" IS '粉丝数';  COMMENT ON COLUMN "public"."user_count"."follow_num" IS '关注数（关注其他用户）';  COMMENT ON COLUMN "public"."user_count"."article_num" IS '文章数';  COMMENT ON COLUMN "public"."user_count"."words" IS '字数';  COMMENT ON COLUMN "public"."user_count"."zan_num" IS '被赞数';  COMMENT ON COLUMN "public"."user_count"."created_at" IS '创建时间';  COMMENT ON COLUMN "public"."user_count"."updated_at" IS '更新时间';  COMMENT ON COLUMN "public"."user_count"."deleted_at" IS '删除时间';

CREATE TABLE "public"."user_follow" ( "id" int8 NOT NULL, "uid" int8 NOT NULL, "fuid" int8 NOT NULL, "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, "deleted_at" timestamp(6) NOT NULL, PRIMARY KEY ("id"), CONSTRAINT "uq_uid_fuid" UNIQUE ("uid", "fuid") ) ;  COMMENT ON COLUMN "public"."user_follow"."id" IS 'ID';  COMMENT ON COLUMN "public"."user_follow"."uid" IS '用户ID';  COMMENT ON COLUMN "public"."user_follow"."fuid" IS '粉丝ID';  COMMENT ON COLUMN "public"."user_follow"."created_at" IS '创建时间';  COMMENT ON COLUMN "public"."user_follow"."updated_at" IS '更新时间';  COMMENT ON COLUMN "public"."user_follow"."deleted_at" IS '删除时间';  COMMENT ON TABLE "public"."user_follow" IS '用户关注表';

CREATE TABLE "public"."comment" ( "id" int8 NOT NULL, "aid" int8 NOT NULL, "uid" int8 NOT NULL, "content" text NOT NULL, "zan_num" int4 NOT NULL DEFAULT 0, "floor" int4 NOT NULL DEFAULT 1, "state" "public"."article_state" NOT NULL DEFAULT 'unaudited', "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, "deleted_at" timestamp(6) NOT NULL, PRIMARY KEY ("id"), CONSTRAINT "uq_aidfloor" UNIQUE ("aid", "floor") ) ;  COMMENT ON COLUMN "public"."comment"."id" IS 'id';  COMMENT ON COLUMN "public"."comment"."aid" IS '文章ID';  COMMENT ON COLUMN "public"."comment"."uid" IS '评论用户id';  COMMENT ON COLUMN "public"."comment"."content" IS '评论内容';  COMMENT ON COLUMN "public"."comment"."zan_num" IS '被赞数';  COMMENT ON COLUMN "public"."comment"."floor" IS '第几楼';  COMMENT ON COLUMN "public"."comment"."state" IS '状态:''unaudited''-未审核,''online''-已上线,''offline''-已下线,''deleted''-已删除';  COMMENT ON COLUMN "public"."comment"."created_at" IS '创建时间';  COMMENT ON COLUMN "public"."comment"."updated_at" IS '更新时间';  COMMENT ON COLUMN "public"."comment"."deleted_at" IS '删除时间';  COMMENT ON TABLE "public"."comment" IS '评论表';

CREATE TABLE "public"."comment_reply" ( "id" int8 NOT NULL, "cid" int8 NOT NULL, "uid" int8 NOT NULL, "content" text NOT NULL, "state" "public"."article_state" NOT NULL DEFAULT 'unaudited'::article_state, "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, "deleted_at" timestamp(6) NOT NULL, PRIMARY KEY ("id") ) ;  CREATE INDEX "idx_cid" ON "public"."comment_reply" ( "cid" );  COMMENT ON COLUMN "public"."comment_reply"."id" IS 'id';  COMMENT ON COLUMN "public"."comment_reply"."cid" IS '评论id';  COMMENT ON COLUMN "public"."comment_reply"."uid" IS '回复人id';  COMMENT ON COLUMN "public"."comment_reply"."content" IS '回复内容';  COMMENT ON COLUMN "public"."comment_reply"."state" IS '状态:''unaudited''-未审核,''online''-已上线,''offline''-已下线,''deleted''-已删除';  COMMENT ON COLUMN "public"."comment_reply"."created_at" IS '创建时间';  COMMENT ON COLUMN "public"."comment_reply"."updated_at" IS '更新时间';  COMMENT ON COLUMN "public"."comment_reply"."deleted_at" IS '删除时间';  COMMENT ON TABLE "public"."comment_reply" IS '评论回复表';

create type zan_type as ENUM ('article','comment','reply')  CREATE TABLE "public"."zan" ( "id" int8 NOT NULL, "uid" int8 NOT NULL, "objtype" "public"."zan_type" NOT NULL DEFAULT 'article', "objid" int8 NOT NULL, "created_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, "deleted_at" timestamp(6) NOT NULL, PRIMARY KEY ("id"), CONSTRAINT "uq_u_obj" UNIQUE ("uid", "objtype", "objid") ) ;  COMMENT ON COLUMN "public"."zan"."id" IS 'id';  COMMENT ON COLUMN "public"."zan"."uid" IS '点赞用户id';  COMMENT ON COLUMN "public"."zan"."objtype" IS '被点赞对象:';  COMMENT ON COLUMN "public"."zan"."objid" IS '被赞对象id';  COMMENT ON COLUMN "public"."zan"."created_at" IS '创建时间';  COMMENT ON COLUMN "public"."zan"."updated_at" IS '更新时间';  COMMENT ON COLUMN "public"."zan"."deleted_at" IS '删除时间';  COMMENT ON TABLE "public"."zan" IS '赞表';

go get github.com/spf13/viper

#debug or release run_mode = "debug"  [app] jwt_secret = "20144481"  # 定义 HTTP 监听端口 [http] port = "8008"  # 存储配置,使用Postgres [storage] user = "admin" password = "admin" host = "localhost" port = 5432 dbname = "postgres"  # 日志设置 [logger] file_path= "/Users/yan/GolandProjects/log/hello-world-web/" # 使用zerolog包配置，0-debug,1-info,3-warn,4-error,5-fatal,6-panic,7-nolevel,8-disable, -1-> trace level= 0  # mail配置 [mail] host="smtp.gmail.com" port=465 email="[email protected]" password="******"

package setting  import ( "fmt" "github.com/spf13/viper" )  var ( RunMode string  HttpPort string  MailHost string MailPort int MailAddr string MailPwd string  JwtSecret string )  func init() { viper.AddConfigPath("config") err := viper.ReadInConfig() if err != nil { panic(fmt.Errorf("读取配置文件失败: %s \n", err)) }  // 设置默认配置 viper.SetDefault("run_mode", "0")  viper.SetDefault("http.port", "8008")  viper.SetDefault("logger.level", "debug")  viper.SetDefault("storage.user", "admin") viper.SetDefault("storage.password", "admin") viper.SetDefault("storage.host", "localhost") viper.SetDefault("storage.port", 5432) viper.SetDefault("storage.dbname", "postgres")  // 获取配置信息 RunMode = viper.GetString("run_mode")  HttpPort = viper.GetString("http.port")  MailHost = viper.GetString("mail.host") MailPort = viper.GetInt("mail.port") MailAddr = viper.GetString("mail.email") MailPwd = viper.GetString("mail.password")  JwtSecret = viper.GetString("app.jwt_secret") }

viper.SetConfigName("config") // name of config file (without extension) viper.SetConfigType("yaml") // REQUIRED if the config file does not have the extension in the name viper.AddConfigPath("/etc/appname/") // path to look for the config file in viper.AddConfigPath("$HOME/.appname") // call multiple times to add many search paths viper.AddConfigPath(".") // optionally look for config in the working directory err := viper.ReadInConfig() // Find and read the config file if err != nil { // Handle errors reading the config file panic(fmt.Errorf("Fatal error config file: %s \n", err)) }

viper.WatchConfig() viper.OnConfigChange(func(e fsnotify.Event) { fmt.Println("Config file changed:", e.Name) })

logger := zerolog.New(os.Stderr).With().Timestamp().Logger()

package util  import ( "fmt" "github.com/gin-gonic/gin" "github.com/rs/zerolog" "github.com/spf13/viper" "io" "os" "strings" "sync" "time" )  var logOutPut zerolog.ConsoleWriter  var ( pool sync.Pool )  func init() { loggerFile := viper.GetString("logger.file_path") loggerlevel := viper.GetInt("logger.level") // 初始化日志配置 zerolog.SetGlobalLevel(zerolog.Level(loggerlevel)) if loggerFile == "" { logOutPut = zerolog.ConsoleWriter{Out: os.Stdout, TimeFormat: time.RFC3339} } else { file, err := os.Create(loggerFile) if err != nil { panic(fmt.Errorf("打开日志文件[%s]失败 \n", loggerFile)) } gin.DefaultWriter = io.MultiWriter(file, os.Stdout) logOutPut = zerolog.ConsoleWriter{Out: io.MultiWriter(file, os.Stdout), TimeFormat: time.RFC3339} } logOutPut.FormatLevel = func(i interface{}) string { return strings.ToUpper(fmt.Sprintf("| %-6s|", i)) } logOutPut.FormatMessage = func(i interface{}) string { if i != nil { return fmt.Sprintf("***%s****", i) } return "" } logOutPut.FormatFieldName = func(i interface{}) string { return fmt.Sprintf("%s:", i) } logOutPut.FormatFieldValue = func(i interface{}) string { return strings.ToUpper(fmt.Sprintf("%s", i)) }  pool = sync.Pool{New: func() interface{} { return zerolog.New(logOutPut).With().Timestamp().Logger() }} }  func NewLogger() zerolog.Logger { return pool.Get().(zerolog.Logger) }  func PutLogger(logger zerolog.Logger) { pool.Put(logger) }

 go get -u github.com/jmoiron/sqlx go get -u github.com/unrotten/sqlex

 package model  import ( "fmt" "github.com/jmoiron/sqlx" _ "github.com/lib/pq" "github.com/sony/sonyflake" "github.com/spf13/viper" "github.com/unrotten/sqlex" "log" "time" )  var ( DB *sqlx.DB PSql sqlex.StatementBuilderType IdFetcher *sonyflake.Sonyflake )  // 初始化数据库连接 func init() { // 获取数据库配置信息 user := viper.Get("storage.user") password := viper.Get("storage.password") host := viper.Get("storage.host") port := viper.Get("storage.port") dbname := viper.Get("storage.dbname")  // 连接数据库 psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, dbname) DB = sqlx.MustOpen("postgres", psqlInfo) if err := DB.Ping(); err != nil { log.Fatalf("连接数据库失败:%s", err) }  // 初始化sql构建器，指定format形式 PSql = sqlex.StatementBuilder.PlaceholderFormat(sqlex.Dollar)  // 初始化sonyflake st := sonyflake.Settings{ StartTime: time.Date(2020, 1, 1, 0, 0, 0, 0, time.Local), } IdFetcher = sonyflake.NewSonyflake(st) }

PSql = sqlex.StatementBuilder.PlaceholderFormat(sqlex.Dollar)

go get -u github.com/graphql-go/graphql

package controller  import ( "context" "github.com/gin-gonic/gin" "github.com/graphql-go/graphql" "github.com/graphql-go/handler" "net/http" )  var ( schema graphql.Schema queryType *graphql.Object mutationType *graphql.Object subscriptType *graphql.Object )  type RequestOptions struct { Query string `json:"query" url:"query" schema:"query"` Variables map[string]interface{} `json:"variables" url:"variables" schema:"variables"` OperationName string `json:"operationName" url:"operationName" schema:"operationName"` }  func Register(e *gin.Engine) { queryType = graphql.NewObject(graphql.ObjectConfig{Name: "Query", Fields: graphql.Fields{ "test": { Name: "test", Type: graphql.String, Resolve: func(p graphql.ResolveParams) (interface{}, error) { return "test", nil }, Description: "test", }, }}) mutationType = graphql.NewObject(graphql.ObjectConfig{Name: "Mutation", Fields: graphql.Fields{ "test": { Name: "test", Type: graphql.String, Resolve: func(p graphql.ResolveParams) (interface{}, error) { return "test", nil }, Description: "test", }, }}) subscriptType = graphql.NewObject(graphql.ObjectConfig{Name: "Subscription", Fields: graphql.Fields{ "test": { Name: "test", Type: graphql.String, Resolve: func(p graphql.ResolveParams) (interface{}, error) { return "test", nil }, Description: "test", }, }}) schemaConfig := graphql.SchemaConfig{ Query: queryType, Mutation: mutationType, Subscription: subscriptType, } var err error schema, err = graphql.NewSchema(schemaConfig) if err != nil { panic(err) }  h := handler.New(&handler.Config{ Schema: &schema, Pretty: true, GraphiQL: true, Playground: false, }) router := func(ctx *gin.Context) { h.ContextHandler(context.Background(), ctx.Writer, ctx.Request) } // graphql的web界面，只有admin才能进入 e.GET("/graphql", router) e.POST("/graphql", router) e.OPTIONS("/graphql", router)  e.GET("/query", query) e.OPTIONS("/query", query) e.POST("/query", query)  }  func query(ctx *gin.Context) { requestOption := &RequestOptions{} _ = ctx.Bind(requestOption) ctx.Set("operationName", requestOption.OperationName) result := graphql.Do(graphql.Params{ Schema: schema, RequestString: requestOption.Query, VariableValues: requestOption.Variables, OperationName: requestOption.OperationName, })  ctx.JSON(http.StatusOK, result) }

package main  import ( "context" "fmt" "github.com/gin-gonic/gin" "github.com/unrotten/hello-world-web/setting" "github.com/unrotten/hello-world-web/util" "net/http" "os" "os/signal" "time" )  func main() { gin.SetMode(setting.RunMode) engine := gin.New() controller.Register(engine)  addr := ":" + setting.HttpPort server := &http.Server{ Addr: addr, Handler: engine, MaxHeaderBytes: 1 << 20, }  logger := util.NewLogger()  go func() { logger.Info().Msg(fmt.Sprintf("server run on:%s", addr)) if err := server.ListenAndServe(); err != nil { logger.Fatal().Caller().Err(err).Msg("server err") } }()  quit := make(chan os.Signal) signal.Notify(quit, os.Interrupt) <-quit logger.Info().Msg("Shutdown Server ...") ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second) defer cancel() if err := server.Shutdown(ctx); err != nil { logger.Fatal().Caller().Err(err).Msg("Server Shutdown") } logger.Info().Msg("Server exiting") }