github.com/k0marov/go-socnet@v0.0.0-20220715154813-90d07867c782/features/comments/store/sql_db/sql_db.go (about)

     1  package sql_db
     2  
     3  import (
     4  	"fmt"
     5  	"github.com/jmoiron/sqlx"
     6  	"github.com/k0marov/go-socnet/core/abstract/table_name"
     7  	"github.com/k0marov/go-socnet/core/general/core_err"
     8  	"time"
     9  
    10  	"github.com/k0marov/go-socnet/features/comments/domain/models"
    11  	"github.com/k0marov/go-socnet/features/comments/domain/values"
    12  	post_values "github.com/k0marov/go-socnet/features/posts/domain/values"
    13  )
    14  
    15  type SqlDB struct {
    16  	sql       *sqlx.DB
    17  	TableName table_name.TableName
    18  }
    19  
    20  func NewSqlDB(db *sqlx.DB) (*SqlDB, error) {
    21  	err := initSQL(db)
    22  	if err != nil {
    23  		return nil, core_err.Rethrow("initializing sql for comments", err)
    24  	}
    25  	return &SqlDB{db, table_name.NewTableName("Comment")}, nil
    26  }
    27  
    28  func initSQL(db *sqlx.DB) error {
    29  	_, err := db.Exec(`
    30  		CREATE TABLE IF NOT EXISTS Comment(
    31  		    id INTEGER PRIMARY KEY, 
    32  		   	post_id INT NOT NULL, 
    33  		   	owner_id INT NOT NULL, 
    34  		   	textContent TEXT NOT NULL, 
    35  		   	createdAt INT NOT NULL, 
    36  		   	FOREIGN KEY(post_id) REFERENCES Post(id) ON DELETE CASCADE, 
    37  		   	FOREIGN KEY(owner_id) REFERENCES Profile(id) ON DELETE CASCADE
    38  		)
    39     `)
    40  	if err != nil {
    41  		return core_err.Rethrow("creating Comment table", err)
    42  	}
    43  	return nil
    44  }
    45  
    46  func (db *SqlDB) GetComments(post post_values.PostId) ([]models.CommentModel, error) {
    47  	var comments []models.CommentModel
    48  	err := db.sql.Select(&comments, `
    49  		SELECT id, owner_id, textContent, createdAt
    50  		FROM Comment 
    51  		WHERE post_id = ?
    52  		ORDER BY createdAt DESC
    53      `, post)
    54  	if err != nil {
    55  		return []models.CommentModel{}, core_err.Rethrow("SELECTing post comments", err)
    56  	}
    57  	return comments, nil
    58  }
    59  
    60  func (db *SqlDB) Create(newComment values.NewCommentValue, createdAt time.Time) (values.CommentId, error) {
    61  	res, err := db.sql.Exec(`
    62  		INSERT INTO Comment(post_id, owner_id, textContent, createdAt) 
    63  		VALUES (?, ?, ?, ?)
    64      `, newComment.Post, newComment.Author, newComment.Text, createdAt.Unix())
    65  	if err != nil {
    66  		return "", core_err.Rethrow("INSERTing a new comment", err)
    67  	}
    68  	newId, err := res.LastInsertId()
    69  	if err != nil {
    70  		return "", core_err.Rethrow("getting the ID of newly inserted comment", err)
    71  	}
    72  	return fmt.Sprintf("%d", newId), nil
    73  }