github.com/shoshinnikita/budget-manager@v0.7.1-0.20220131195411-8c46ff1c6778/internal/db/sqlite/migrations/1_init.go (about)

     1  package migrations
     2  
     3  import "database/sql"
     4  
     5  func initMigration(tx *sql.Tx) error {
     6  	_, err := tx.Exec(`
     7  		CREATE TABLE IF NOT EXISTS months (
     8  			id           INTEGER PRIMARY KEY,
     9  			year         INTEGER NOT NULL,
    10  			month        INTEGER NOT NULL,
    11  			daily_budget INTEGER NOT NULL DEFAULT 0,
    12  			total_income INTEGER NOT NULL DEFAULT 0,
    13  			total_spend  INTEGER NOT NULL DEFAULT 0,
    14  			result       INTEGER NOT NULL DEFAULT 0
    15  		);
    16  
    17  		CREATE TABLE IF NOT EXISTS days (
    18  			id       INTEGER PRIMARY KEY,
    19  			month_id INTEGER NOT NULL,
    20  			day      INTEGER NOT NULL,
    21  			saldo    INTEGER NOT NULL DEFAULT 0,
    22  
    23  			FOREIGN KEY (month_id) REFERENCES months(id)
    24  		);
    25  
    26  		CREATE TABLE IF NOT EXISTS incomes (
    27  			id       INTEGER PRIMARY KEY,
    28  			month_id INTEGER NOT NULL,
    29  			title    TEXT NOT NULL,
    30  			notes    TEXT,
    31  			income   INTEGER NOT NULL,
    32  
    33  			FOREIGN KEY (month_id) REFERENCES months(id)
    34  		);
    35  
    36  		CREATE TABLE IF NOT EXISTS monthly_payments (
    37  			id       INTEGER PRIMARY KEY,
    38  			month_id INTEGER NOT NULL,
    39  			title    TEXT NOT NULL,
    40  			type_id  INTEGER,
    41  			notes    TEXT,
    42  			cost     INTEGER NOT NULL,
    43  
    44  			FOREIGN KEY (month_id) REFERENCES months(id),
    45  			FOREIGN KEY (type_id) REFERENCES spend_types(id)
    46  		);
    47  
    48  		CREATE TABLE IF NOT EXISTS spends (
    49  			id      INTEGER PRIMARY KEY,
    50  			day_id  INTEGER NOT NULL,
    51  			title   TEXT NOT NULL,
    52  			type_id INTEGER,
    53  			notes   TEXT,
    54  			cost    INTEGER NOT NULL,
    55  
    56  			FOREIGN KEY (day_id) REFERENCES days(id),
    57  			FOREIGN KEY (type_id) REFERENCES spend_types(id)
    58  		);
    59  
    60  		CREATE TABLE IF NOT EXISTS spend_types (
    61  			id        INTEGER PRIMARY KEY,
    62  			name      TEXT NOT NULL,
    63  			parent_id INTEGER,
    64  
    65  			FOREIGN KEY (parent_id) REFERENCES spend_types(id)
    66  		);`,
    67  	)
    68  	return err
    69  }