sqlweb

README

sqlweb

sql web based on golang, currently only MySQL supported!

build

1. install golang 1.11+
2. git clone git@github.com:bingoohuang/sqlweb.git & cd sqlweb
3. build:
   • go build -o sqlweb
   • go install ./...
   • env GOOS=linux GOARCH=amd64 go build -o sqlweb

docker

docker run -d \
 --name go-sql-web \
 --restart=always \
 -p 8381:8381 \
 -v $(pwd)/appConfig.toml:/root/appConfig.toml:ro \
 registry.cn-hangzhou.aliyuncs.com/bingoo-docker/go-sql-web

center database tables for multiple-tenant databases

CREATE TABLE sqlweb (
  MERCHANT_ID bigint(20) NOT NULL COMMENT '商户ID',
  DB_NAME varchar(50) NOT NULL COMMENT '数据库名',
  DB_USERNAME varchar(50) NOT NULL COMMENT '用户名',
  DB_PASSWORD varchar(100) NOT NULL COMMENT '用户密码',
  PROXY_IP varchar(100) NOT NULL COMMENT '应用接入数据库的IP。',
  PROXY_PORT varchar(100) NOT NULL COMMENT '应用端口号',
  MERCHANT_NAME varchar(50)  NULL COMMENT '商户名称',
  MERCHANT_CODE varchar(20)  NULL COMMENT '商户code',
  HOME_AREA varchar(15)  NULL COMMENT '部署中心',
  CLASSIFIER varchar(45)  NULL COMMENT 'default',
  PRIMARY KEY (MERCHANT_ID),
  UNIQUE KEY MERCHANT_CODE (MERCHANT_CODE)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '数据库信息';

snapshots

Main page:

LinksTo & Fast Entries:

LinksTo & Fast Entries Config:

Show table column info（Right click on the cell）:

Table head context menu:

More Tools for single table (Left click on the ...) :

Sql Templates:

Copy Columns As TSV(Tab separated values, can be pasted in Excel very well):

others

如何在Mac上关闭后退和前进触控板手势

方法1：如果您使用Mac笔记本电脑进行冲浪，您可能已经注意到，在触控板上向左或向右轻轻滑动两个手指会导致网络浏览器向前和向后翻页。 对于一些人来说，这是一个伟大的。 对于其他人来说，它偶然发生的比意外发生更频繁，这可能是恼人的。

方法2: 执行命令defaults write com.google.Chrome AppleEnableSwipeNavigateWithScrolls -bool FALSE

UPX

Citing:If you’re concerned about the binary size of your new executable, check out UPX. After installing it on my laptop via brew install upx, I ran upx -9 on an executable created with the above instructions. While the executable was an overly simplistic example, upx compressed it down to 34% of the original size. Even if you don’t care about the size of the binary once it’s on the server, it at least made the scp go faster.

Some useful javascript snippet

date-fns

var script = document.createElement('script');
script.type = 'text/javascript';
script.src = 'https://cdn.bootcss.com/date-fns/1.30.1/date_fns.min.js';
document.head.appendChild(script);

dateFns.parse('20090131')
dateFns.startOfWeek(dateFns.parse('20090131'))

dateFns.format(dateFns.addHours(dateFns.parse('20090131'), 8), 'YYYY-MM-DD HH:mm:ss')

date and time datajs

var script = document.createElement('script');
script.type = 'text/javascript';
script.src = 'https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/datejs/date.js';
document.head.appendChild(script);

var jan312009 = new Date(2009, 1-1, 31);
var oneMonthFromJan312009 = new Date(jan312009).add(1).month();

// What date is next thursday?
Date.today().next().thursday();

// Add 3 days to Today
Date.today().add(3).days();

// Is today Friday?
Date.today().is().friday();

// Number fun
(3).days().ago();

// 6 months from now
var n = 6;
n.months().fromNow();

// Set to 8:30 AM on the 15th day of the month
Date.today().set({ day: 15, hour: 8, minute: 30 });

function yyyy_mm_dd (date) {
    var now = new Date(date)
    year = "" + now.getFullYear();
    month = "" + (now.getMonth() + 1); if (month.length == 1) { month = "0" + month; }
    day = "" + now.getDate(); if (day.length == 1) { day = "0" + day; }
    return year + "-" + month + "-" + day;
}

function yyyy_mm_dd_hh_mm_ss (date) {
    var now = new Date(date)
    hour = "" + now.getHours(); if (hour.length == 1) { hour = "0" + hour; }
    minute = "" + now.getMinutes(); if (minute.length == 1) { minute = "0" + minute; }
    second = "" + now.getSeconds(); if (second.length == 1) { second = "0" + second; }
    return yyyy_mm_dd(date) + " " + hour + ":" + minute + ":" + second;
}

yyyy_mm_dd_hh_mm_ss(new Date('2018-08-30 16:53:14').add(1).month())

Documentation

Index

• func AssetInfo(name string) (os.FileInfo, error)
• func AssetNames(ext string) (files []string)
• func CommandExist(command string) bool
• func Contains(s string, sub ...string) bool
• func DownloadColumn(w http.ResponseWriter, req *http.Request)
• func ExportDatabase(w http.ResponseWriter, r *http.Request)
• func FindDbName(dsn string) (string, error)
• func ImportDatabase(w http.ResponseWriter, r *http.Request)
• func InitCfgFile(configTplFileName, configFileName string) error
• func InitConf()
• func InitCtl(ctlTplName, ctlFilename string) error
• func MustAsset(name string) []byte
• func ParseUploadedFile(r *http.Request, w http.ResponseWriter) (string, string, error)
• func ServeAction(w http.ResponseWriter, r *http.Request)
• func ServeFont(prefix string) http.HandlerFunc
• func ServeHome(w http.ResponseWriter, r *http.Request)
• func ServeLoadLinksConfig(w http.ResponseWriter, r *http.Request)
• func ServeQuery(w http.ResponseWriter, req *http.Request)
• func ServeSaveLinksConfig(w http.ResponseWriter, r *http.Request)
• func ServeSearchDb(w http.ResponseWriter, req *http.Request)
• func ServeTablesByColumn(w http.ResponseWriter, req *http.Request)
• func ServeUpdate(w http.ResponseWriter, r *http.Request) (interface{}, error)
• func TenantsQuery(w http.ResponseWriter, r *http.Request)
• func TimeNow() string
• func WrapHandlerFunc(f func(http.ResponseWriter, *http.Request) (interface{}, error)) http.HandlerFunc
• func WriteExportedSqlFile(tcode string, w http.ResponseWriter, file multipart.File) (string, error)
• type Action
• type ActionProxy
• type AppConfig
  • func (c AppConfig) BasicAuthRequired() bool
  • func (c AppConfig) GetDSN(index int) string
• type CacheAction
  • func (t *CacheAction) Execute() ([]byte, error)
• type ContextKey
• type DSNS
• type ImportResult
• type LoginUser
• type Merchant
• type MerchantDb
• type QueryResult
• type UnknownAction
  • func (t *UnknownAction) Execute() ([]byte, error)
• type UpdateResult
• type UpdateResultRow

Functions

func AssetInfo

func AssetInfo(name string) (os.FileInfo, error)

func AssetNames

func AssetNames(ext string) (files []string)

func CommandExist

func CommandExist(command string) bool

func Contains

func Contains(s string, sub ...string) bool

func DownloadColumn

func DownloadColumn(w http.ResponseWriter, req *http.Request)

func ExportDatabase

func ExportDatabase(w http.ResponseWriter, r *http.Request)

func FindDbName

func FindDbName(dsn string) (string, error)

func ImportDatabase

func ImportDatabase(w http.ResponseWriter, r *http.Request)

func InitCfgFile

func InitCfgFile(configTplFileName, configFileName string) error

InitCfgFile initializes the cfg file.

func InitConf

func InitConf()

func InitCtl

func InitCtl(ctlTplName, ctlFilename string) error

InitCtl initializes the ctl file.

func MustAsset

func MustAsset(name string) []byte

func ParseUploadedFile

func ParseUploadedFile(r *http.Request, w http.ResponseWriter) (string, string, error)

func ServeAction

func ServeAction(w http.ResponseWriter, r *http.Request)

func ServeFont

func ServeFont(prefix string) http.HandlerFunc

func ServeHome

func ServeHome(w http.ResponseWriter, r *http.Request)

func ServeLoadLinksConfig

func ServeLoadLinksConfig(w http.ResponseWriter, r *http.Request)

func ServeQuery

func ServeQuery(w http.ResponseWriter, req *http.Request)

func ServeSaveLinksConfig

func ServeSaveLinksConfig(w http.ResponseWriter, r *http.Request)

func ServeSearchDb

func ServeSearchDb(w http.ResponseWriter, req *http.Request)

func ServeTablesByColumn

func ServeTablesByColumn(w http.ResponseWriter, req *http.Request)

func ServeUpdate

func ServeUpdate(w http.ResponseWriter, r *http.Request) (interface{}, error)

func TenantsQuery

func TenantsQuery(w http.ResponseWriter, r *http.Request)

func TimeNow

func TimeNow() string

func WrapHandlerFunc

func WrapHandlerFunc(f func(http.ResponseWriter, *http.Request) (interface{}, error)) http.HandlerFunc

func WriteExportedSqlFile

func WriteExportedSqlFile(tcode string, w http.ResponseWriter, file multipart.File) (string, error)

Types

type Action

type Action interface {
	Execute() ([]byte, error)
}

type ActionProxy

type ActionProxy struct {
	Proxy string
}

type AppConfig

type AppConfig struct {
	ContextPath  string
	ListenPort   int
	MaxQueryRows int
	DSN          string
	DefaultDB    string
	TrrHomeArea  string

	DevMode   bool // to disable css/js minify
	BasicAuth []string
	ImportDb  bool

	ActionProxy map[string]ActionProxy

	EncryptKey  string
	CookieName  string
	RedirectUri string
	LocalUrl    string
	ForceLogin  bool
	// 是否只显示sqlweb表定义的库，不自动补充show databases()的库列表
	OnlyShowSqlWebDatabases bool

	WriteAuthUserNames []string   // UserNames which has write auth
	DSNS               []DSNS     // 配置文件中的多数据源配置
	BasicAuthGroups    [][]string `toml:"-"`
}

var AppConf AppConfig

func (AppConfig) BasicAuthRequired

func (c AppConfig) BasicAuthRequired() bool

func (AppConfig) GetDSN

func (c AppConfig) GetDSN(index int) string

type CacheAction

type CacheAction struct {
	Tenant *Merchant
	Key    string
	Value  string
	Score  string
	Ttl    string
	Op     string // Set/Get/Clear
	Db     int
}

func (*CacheAction) Execute

func (t *CacheAction) Execute() ([]byte, error)

type ContextKey

type ContextKey int

const (
	LoginUserKey ContextKey = iota
)

type DSNS

type DSNS struct {
	DSN       string
	BasicAuth []string
	DefaultDB bool
}

type ImportResult

type ImportResult struct {
	Success bool   `json:"success"`
	Result  string `json:"result"`
}

type LoginUser

type LoginUser struct {
	Name            string
	DSNGroups       []int
	DefaultDB       int
	Limit2ConfigDSN bool
}

LoginUser ...

type Merchant

type Merchant struct {
	MerchantName string
	MerchantId   string
	MerchantCode string
	HomeArea     string
	Classifier   string
}

type MerchantDb

type MerchantDb struct {
	MerchantId string
	Username   string
	Password   string
	Host       string
	Port       string
	Database   string
}

type QueryResult

type QueryResult struct {
	Headers          []string
	Rows             [][]string
	TableColumns     map[string][]string
	Error            string
	ExecutionTime    string
	CostTime         string
	DatabaseName     string
	TableName        string
	PrimaryKeysIndex []int
	Msg              string
	Tid              string
	CreateSth        bool
}

type UnknownAction

type UnknownAction struct {
}

func (*UnknownAction) Execute

func (t *UnknownAction) Execute() ([]byte, error)

type UpdateResult

type UpdateResult struct {
	Ok         bool
	Message    string
	RowsResult []UpdateResultRow
}

type UpdateResultRow

type UpdateResultRow struct {
	Ok      bool
	Message string
}