sheetdb

package module
v0.2.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Mar 24, 2024 License: MIT Imports: 11 Imported by: 0

README

sheetdb

MIT License

A golang package for using Google spreadsheets as a database instead of the actual database management system.

!!! Caution !!!

  • This fork of github.com/takuoki/sheetdb is not intended for your use. It will not behave like you expect it to. Thar be dragonbugs here, and they won't be fixed. Go back to the original and use that instead.
  • Currently we are not measuring performance. It is intended for use in small applications where performance is not an issue.
  • The Google Sheets API has a usage limit. Do not use this package for applications that require access beyond this usage limit.

Features

  • Load sheet data into cache
  • Apply cache update information to sheet asynchronously
  • Exclusive control when updating cache and inserting a row to a sheet
  • Automatic generation of CRUD functions based on model (structure definition)
  • Automatic numbering and initial value setting of ID
  • Unique and non-null constraints
  • Cascade delete child data when deleting parent data
  • Notification mechanism on asynchronous update error

The following features are not included.

  • SQL query
  • Transaction control (commit and rollback)
  • Read Lock for Update

Installation

go get github.com/takuoki/sheetdb

Requirement

This package uses Google OAuth2.0. So before executing tool, you have to prepare credentials.json. See Go Quickstart, or blog post (Japanese) for the details.

Usage

1. Write models

Write the structure of the model as follows. Please refer to the sheetdb-modeler tool documentation for details.

//go:generate sheetdb-modeler -type=User -children=Foo,FooChild,Bar -initial=10001

// User is a struct of user.
type User struct {
  UserID   int           `json:"user_id" db:"primarykey"`
  Name     string        `json:"name"`
  Email    string        `json:"email" db:"unique"`
  Sex      Sex           `json:"sex"`
  Birthday *sheetdb.Date `json:"birthday"`
}

//go:generate sheetdb-modeler -type=Foo -parent=User -children=FooChild

// Foo is a struct of foo which is a child of user.
type Foo struct {
  UserID int     `json:"user_id" db:"primarykey"`
  FooID  int     `json:"foo_id" db:"primarykey"`
  Value  float32 `json:"value"`
  Note   string  `json:"note" db:"allowempty"`
}

//go:generate sheetdb-modeler -type=FooChild -parent=Foo

// FooChild is a struct of foo child.
type FooChild struct {
  UserID  int    `json:"user_id" db:"primarykey"`
  FooID   int    `json:"foo_id" db:"primarykey"`
  ChildID int    `json:"child_id" db:"primarykey"`
  Value   string `json:"value" db:"unique"`
}

//go:generate sheetdb-modeler -type=Bar -parent=User

// Bar is a struct of bar which is a child of user.
type Bar struct {
  UserID   int              `json:"user_id" db:"primarykey"`
  Datetime sheetdb.Datetime `json:"datetime" db:"primarykey"`
  Value    float32          `json:"value"`
  Note     string           `json:"note" db:"allowempty"`
}
2. Generate codes from models

You can generate in bulk with the go generate command by putting //go:generate sheetdb-modeler comments in the code of the target package. Please refer to the sheetdb-modeler tool documentation for details.

go generate ./sample
3. Set up Google spreadsheet

Prepare a spreadsheet according to the header comments of each generated file.

// Code generated by "sheetdb-modeler"; DO NOT EDIT.
// Create a Spreadsheet (sheet name: "users") as data storage.
// The spreadsheet header is as follows:
//   user_id | name | email | sex | birthday | updated_at | deleted_at
// Please copy and paste this header on the first line of the sheet.
4. Initialize models
Set up nortificaltion

By setting Logger, you can customize log output freely. If you want to send an alert to Slack, you can use SlackLogger in this package. Configuration of Logger is package scope. Please note that it is not client scope.

sheetdb.SetLogger(sheetdb.NewSlackLogger(
  "project name",
  "service name",
  "icon_emoji",
  "https://hooks.slack.com/services/zzz/zzz/zzzzz",
  sheetdb.LevelError,
))
Create new client

Create a new client using the New function. Set the created client in the package global variable. The name of the variable is the name specified with the -client option (the default is dbClient).

var dbClient *sheetdb.Client

// Initialize initializes this package.
func Initialize(ctx context.Context) error {
  client, err := sheetdb.New(
    ctx,
    `{"installed":{"client_id":"..."}`, // Google API credentials
    `{"access_token":"..."`,            // Google API token
    "xxxxx",                            // Google spreadsheet ID
  )
  // ...
  dbClient = client
  return nil
}
Load sheet data
err := client.LoadData(ctx)
5. Use CRUD functions

The functions in this section are generated automatically by sheetdb-modeler.

Read (Get/Select)

GetModelName function returns an instance of that model by the primary key(s). If it can not be found, this function returns *sheetdb.NotFoundError.

user, err := GetUser(userID)
foo, err := GetFoo(userID, fooID)
fooChild, err := GetFooChild(userID, fooID, childID)
bar, err := GetBar(userID, datetime)

If the model is a child model of another model, GetModelName method is also added to the parent model.

foo, err := user.GetFoo(fooID)
fooChild, err := foo.GetFooChild(childID)
bar, err := user.GetBar(datetime)

For fields defined as unique, GetModelNameByFieldName function is also generated. For child models, this method is also added to the parent model.

user, err := GetUserByEmail(email)
fooChild, err := GetFooChildByValue(userID, fooID, value)

fooChild, err := foo.GetFooChildByValue(value)

GetModelNames function returns all instances of that model. If the model is a child model of another model, this function returns all instances that parent model has. For child models, this method is also added to the parent model.

users, err := GetUsers()
foos, err := GetFoos(userID)
fooChildren, err := GetFooChildren(userID, fooID)
bars, err := GetBars(userID)

foos, err := user.GetFoos()
fooChildren, err := foo.GetFooChildren()
bars, err := user.GetBars()

You can get filtered results using ModelFilter option.

users, err := GetUsers(sample.UserFilter(func(user *sample.User) bool {
  return user.Sex == sample.Male
}))

You can also get sorted results using ModelSort option. If the sort option is not specified, the order of results is random.

users, err := GetUsers(sample.UserSort(func(users []*sample.User) {
  sort.Slice(users, func(i, j int) bool {
    return users[i].UserID < users[j].UserID
  })
}))
Create (Add/Insert)

AddModel adds a new instance. If the primary key matches the automatic numbering rule, it will be automatically numbered. For child models, AddModel method is added to the parent model.

user, err := AddUser(name, email, sex, birthday)
foo, err := AddFoo(userID, value, note)
fooChild, err := AddFooChild(userID, fooID, value)
bar, err := AddBar(userID, datetime, value, note)

foo, err := user.AddFoo(value, note)
fooChild, err := foo.AddFooChild(value)
bar, err := user.AddBar(datetime, value, note)
Update

UpdateModel updates an existing instance. You can not change the primary key(s). For child models, UpdateModel method is added to the parent model.

user, err := UpdateUser(userID, name, email, sex, birthday)
foo, err := UpdateFoo(userID, fooID, value, note)
fooChild, err := UpdateFooChild(userID, fooID, childID, value)
bar, err := UpdateBar(userID, datetime, value, note)

foo, err := user.UpdateFoo(fooID, value, note)
fooChild, err := foo.UpdateFooChild(childID, value, note)
bar, err := user.UpdateBar(datetime, value, note)
Delete

DeleteModel deletes an existing instance. If the model has child models, this function deletes the instances of the child model in cascade. For child models, DeleteModel method is added to the parent model.

err = DeleteUser(userID)
err = DeleteFoo(userID, fooID)
err = DeleteFooChild(userID, fooID, childID)
err = DeleteBar(userID, datetime)

err = user.DeleteFoo(fooID)
err = foo.DeleteFooChild(childID)
err = user.DeleteBar(datetime)

Documentation

Overview

Package sheetdb is a package for using Google spreadsheets as a database instead of the actual database management system. This package is used with the code generated by the `sheetdb-modeler` tool. For details, please refer to usage of README.md.

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func ModelSetName 

func ModelSetName(modelSetName string) func(client *Client) *Client

ModelSetName is an option to change model set name using by client. If not used, the "default" model set is used.

func RegisterModel 

func RegisterModel(modelSetName, modelName, sheetName string, loadFunc func(data *gsheets.Sheet) error)

RegisterModel registers the model specified as an argument into a model set. This function is usually called from generated code.

func SetLogLevel 

func SetLogLevel(l LogLevel)

SetLogLevel sets a log level for this package. If you don't use the default logger or SlackLogger, log level are meaningless. The default log level is "INFO".

func SetLogger 

func SetLogger(l Logger)

SetLogger sets a logger for this package. By default, logs are output only to standard output and standard error output.

Types

type Client 

type Client struct {
	// contains filtered or unexported fields
}

Client is a client of this package. Create a new client with the `New` function.

func New 

func New(ctx context.Context, spreadsheetID string, opts ...option.ClientOption) (*Client, error)

New creates and returns a new client.

func (*Client) AsyncUpdate 

func (c *Client) AsyncUpdate(data []gsheets.UpdateValue) error

AsyncUpdate applies updates to s spreadsheet asynchronously. This function is usually called from generated code. Even though it's called Async this function is a synchronous blocking call. Because the original implementation DISCARDS ERRORS, and I can't be having that 

in a database implementation. If I retain sheetsdb and perf becomes an issue
i'll look to plumb channels through here to communicate errors.

func (*Client) LoadData 

func (c *Client) LoadData(ctx context.Context) error

LoadData loads data from a spreadsheet into cache. This function calls the load functions of models registered in advance into the model set in order.

type ClientOption 

type ClientOption func(client *Client) *Client

ClientOption is an option to change the behavior of the client.

type Date 

type Date struct {
	time.Time
}

Date is date (YYYY-MM-DD).

func NewDate 

func NewDate(date string) (Date, error)

NewDate returns new Date. The argument "date" is parsed on local location.

func Today 

func Today() Date

Today returns new Date that represents today.

func (*Date) MarshalJSON 

func (d *Date) MarshalJSON() ([]byte, error)

MarshalJSON marshals date value.

func (*Date) String 

func (d *Date) String() string

String returns date string.

type Datetime 

type Datetime struct {
	time.Time
}

Datetime is datetime (YYYY-MM-DDTHH:mm:ssZ0700).

func NewDatetime 

func NewDatetime(datetime string) (Datetime, error)

NewDatetime returns new Datetime.

func Now 

func Now() Datetime

Now returns new Datetime that represents now.

func (*Datetime) MarshalJSON 

func (d *Datetime) MarshalJSON() ([]byte, error)

MarshalJSON marshals datetime value.

func (*Datetime) String 

func (d *Datetime) String() string

String returns datetime string.

type DuplicationError 

type DuplicationError struct {
	FieldName string
}

DuplicationError means that field value that should be unique is duplicated.

func (*DuplicationError) Error 

func (e *DuplicationError) Error() string

type EmptyStringError 

type EmptyStringError struct {
	FieldName string
}

EmptyStringError means that string field that does not allow empty is empty.

func (*EmptyStringError) Error 

func (e *EmptyStringError) Error() string

type InvalidFormatError 

type InvalidFormatError struct {
	Err error
}

InvalidFormatError means that the format is invalid.

func (*InvalidFormatError) Error 

func (e *InvalidFormatError) Error() string

type InvalidValueError 

type InvalidValueError struct {
	FieldName string
	Err       error
}

InvalidValueError means that field value is invalid.

func (*InvalidValueError) Error 

func (e *InvalidValueError) Error() string

type LogLevel 

type LogLevel int

LogLevel represents the level of logging when using the default logger. 

const (
	LevelDebug LogLevel = iota + 1
	LevelInfo
	LevelError
	LevelNoLogging
)

Levels of logging.

type Logger 

type Logger interface {
	Debugf(format string, v ...interface{})
	Infof(format string, v ...interface{})
	Errorf(format string, v ...interface{})
}

Logger is an interface for logging of this package.

type NotFoundError 

type NotFoundError struct {
	Model string
}

NotFoundError means that model is not found.

func (*NotFoundError) Error 

func (e *NotFoundError) Error() string

type SlackLogger 

type SlackLogger struct {
	// contains filtered or unexported fields
}

SlackLogger is a logget to output log to slack. Logs are also output to standard output and standard error output at the same time, and you can control each log level.

func NewSlackLogger 

func NewSlackLogger(projectName, serviceName, iconEmoji, errorURL string,
	slackLogLevel LogLevel) *SlackLogger

NewSlackLogger returns a new SlackLogger.

func (*SlackLogger) Debugf 

func (l *SlackLogger) Debugf(format string, v ...interface{})

Debugf outputs a log whose log level is Debug or higher.

func (*SlackLogger) Errorf 

func (l *SlackLogger) Errorf(format string, v ...interface{})

Errorf outputs a log whose log level is Error or higher.

func (*SlackLogger) Infof 

func (l *SlackLogger) Infof(format string, v ...interface{})

Infof outputs a log whose log level is Info or higher.

Source Files

View all Source files

Directories

Path Synopsis
tools
sheetdb-modeler

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.