github.com/k0marov/go-socnet@v0.0.0-20220715154813-90d07867c782/features/posts/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  	"github.com/k0marov/go-socnet/core/general/core_values"
     9  	"github.com/k0marov/go-socnet/features/posts/domain/models"
    10  	"github.com/k0marov/go-socnet/features/posts/domain/values"
    11  )
    12  
    13  type SqlDB struct {
    14  	sql       *sqlx.DB
    15  	TableName table_name.TableName
    16  }
    17  
    18  func NewSqlDB(sql *sqlx.DB) (*SqlDB, error) {
    19  	err := initSQL(sql)
    20  	if err != nil {
    21  		return nil, err
    22  	}
    23  	return &SqlDB{sql: sql, TableName: table_name.NewTableName("Post")}, nil
    24  }
    25  
    26  func initSQL(sql *sqlx.DB) error {
    27  	_, err := sql.Exec(`
    28  		CREATE TABLE IF NOT EXISTS Post(
    29  		    id INTEGER PRIMARY KEY, 
    30  			owner_id INT NOT NULL, 
    31  			textContent TEXT NOT NULL, 
    32  			createdAt INT NOT NULL, 
    33  			FOREIGN KEY(owner_id) REFERENCES Profile(id) ON DELETE CASCADE
    34  		)
    35  	`)
    36  	if err != nil {
    37  		return core_err.Rethrow("creating Post table", err)
    38  	}
    39  	_, err = sql.Exec(`
    40  		CREATE TABLE IF NOT EXISTS PostImage(
    41  		    post_id INT NOT NULL, 
    42  		    path VARCHAR(255), 
    43  		    ind INT,
    44  		    FOREIGN KEY(post_id) REFERENCES Post(id) ON DELETE CASCADE
    45  		)	
    46  	`)
    47  	if err != nil {
    48  		return core_err.Rethrow("creating PostImage table", err)
    49  	}
    50  	return nil
    51  }
    52  
    53  func (db *SqlDB) GetPosts(author core_values.UserId) (posts []models.PostModel, err error) {
    54  	rows, err := db.sql.Query(`
    55  		SELECT id, owner_id, textContent, createdAt
    56  		FROM Post 
    57  		WHERE owner_id = ?
    58  		ORDER BY createdAt DESC
    59  	`, author)
    60  	if err != nil {
    61  		return []models.PostModel{}, core_err.Rethrow("getting posts from db", err)
    62  	}
    63  	defer rows.Close()
    64  	for rows.Next() {
    65  		post := models.PostModel{}
    66  		err = rows.Scan(&post.Id, &post.AuthorId, &post.Text, &post.CreatedAt)
    67  		if err != nil {
    68  			return []models.PostModel{}, core_err.Rethrow("scanning a post", err)
    69  		}
    70  		post.Images, err = db.getImages(post.Id)
    71  		if err != nil {
    72  			return []models.PostModel{}, err
    73  		}
    74  		posts = append(posts, post)
    75  	}
    76  	return posts, nil
    77  }
    78  
    79  func (db *SqlDB) CreatePost(newPost models.PostToCreate) (values.PostId, error) {
    80  	res, err := db.sql.Exec(`
    81  		INSERT INTO Post(owner_id, textContent, createdAt) VALUES (?, ?, ?)
    82  	`, newPost.Author, newPost.Text, newPost.CreatedAt.Unix())
    83  	if err != nil {
    84  		return "", core_err.Rethrow("inserting a post", err)
    85  	}
    86  	id, err := res.LastInsertId()
    87  	if err != nil {
    88  		return "", core_err.Rethrow("getting the inserted post id", err)
    89  	}
    90  	return fmt.Sprintf("%d", id), nil
    91  }
    92  
    93  func (db *SqlDB) AddPostImages(post values.PostId, images []models.PostImageModel) error {
    94  	for _, image := range images {
    95  		err := db.addImage(post, image)
    96  		if err != nil {
    97  			return err
    98  		}
    99  	}
   100  	return nil
   101  }
   102  
   103  func (db *SqlDB) addImage(post values.PostId, image models.PostImageModel) error {
   104  	_, err := db.sql.Exec(`
   105  		INSERT INTO PostImage(post_id, path, ind) VALUES (?, ?, ?)
   106     `, post, image.Path, image.Index)
   107  	if err != nil {
   108  		return core_err.Rethrow("inserting a post image", err)
   109  	}
   110  	return nil
   111  }
   112  
   113  func (db *SqlDB) getImages(post values.PostId) (images []models.PostImageModel, err error) {
   114  	err = db.sql.Select(&images, `
   115  		SELECT path, ind FROM PostImage WHERE post_id = ?
   116      `, post)
   117  	if err != nil {
   118  		return []models.PostImageModel{}, core_err.Rethrow("SELECTing post images", err)
   119  	}
   120  	return images, nil
   121  }