github.com/bcampbell/scrapeomat@v0.0.0-20220820232205-23e64141c89e/store/sqlstore/schema.go (about)

     1  package sqlstore
     2  
     3  import (
     4  	//	"database/sql"
     5  	"fmt"
     6  )
     7  
     8  func (ss *SQLStore) checkSchema() error {
     9  
    10  	ver, err := ss.schemaVersion()
    11  	if err != nil {
    12  		return err
    13  	}
    14  	ss.DebugLog.Printf("Existing schema version: %d\n", ver)
    15  	if ver == 7 {
    16  		return nil // up to date.
    17  	}
    18  
    19  	// auto schema management currently only for sqlite.
    20  	if ss.driverName != "sqlite3" {
    21  		return fmt.Errorf("Missing Schema.")
    22  	}
    23  
    24  	if ver != 0 {
    25  		return fmt.Errorf("No Schema upgrade path (from ver %d)", ver)
    26  	}
    27  
    28  	// TODO: handle schema upgrades for data-in-the-wild!
    29  
    30  	stmts := []string{
    31  		`CREATE TABLE publication (
    32  			id INTEGER PRIMARY KEY,
    33  			code TEXT NOT NULL,
    34  			name TEXT NOT NULL DEFAULT '',
    35  			domain TEXT NOT NULL DEFAULT '')`,
    36  
    37  		`CREATE TABLE article (
    38  	        id INTEGER PRIMARY KEY,
    39  			added TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    40  		    canonical_url TEXT NOT NULL,
    41  			headline TEXT NOT NULL,
    42  	        content TEXT NOT NULL DEFAULT '',
    43  		    published TIMESTAMP DEFAULT NULL,
    44  			updated TIMESTAMP DEFAULT NULL,
    45  	        publication_id INTEGER NOT NULL,
    46  	        section TEXT NOT NULL DEFAULT '',
    47  	        extra TEXT NOT NULL DEFAULT '',
    48  			FOREIGN KEY(publication_id) REFERENCES publication(id) )`,
    49  
    50  		`CREATE TABLE author (
    51  		    id INTEGER PRIMARY KEY,
    52  		    name TEXT NOT NULL,
    53  		    rel_link TEXT NOT NULL DEFAULT '',
    54  		    email TEXT NOT NULL DEFAULT '',
    55  		    twitter TEXT NOT NULL DEFAULT '' )`,
    56  
    57  		`CREATE TABLE author_attr (
    58  		    id INTEGER PRIMARY KEY,
    59  		    author_id INT NOT NULL,
    60  		    article_id INT NOT NULL,
    61  			FOREIGN KEY(author_id) REFERENCES author(id) ON DELETE CASCADE,
    62  			FOREIGN KEY(article_id) REFERENCES article(id) ON DELETE CASCADE )`,
    63  		`CREATE INDEX author_attr_artid ON author_attr(article_id)`,
    64  		`CREATE INDEX author_attr_authorid ON author_attr(author_id)`,
    65  
    66  		`CREATE TABLE article_tag (
    67  			id INTEGER PRIMARY KEY,
    68  			article_id INTEGER NOT NULL,
    69  			tag TEXT NOT NULL,
    70  			FOREIGN KEY(article_id) REFERENCES article(id) ON DELETE CASCADE )`,
    71  		`CREATE INDEX article_tag_artid ON article_tag(article_id)`,
    72  
    73  		`CREATE TABLE article_url (
    74  			id INTEGER PRIMARY KEY,
    75  			article_id INTEGER NOT NULL,
    76  			url TEXT NOT NULL,
    77  			FOREIGN KEY(article_id) REFERENCES article(id) ON DELETE CASCADE )`,
    78  		`CREATE INDEX article_url_artid ON article_url(article_id)`,
    79  		`CREATE INDEX article_url_url ON article_url(url)`,
    80  
    81  		`CREATE TABLE article_keyword (
    82  			id INTEGER PRIMARY KEY,
    83  			article_id INTEGER NOT NULL,
    84  			name TEXT NOT NULL,
    85  			url TEXT NOT NULL,
    86  			FOREIGN KEY(article_id) REFERENCES article(id) ON DELETE CASCADE )`,
    87  		`CREATE INDEX article_keyword_artid ON article_keyword(article_id)`,
    88  
    89  		`CREATE TABLE version (ver INTEGER NOT NULL)`,
    90  		`CREATE TABLE settings (n TEXT, v TEXT NOT NULL)`,
    91  
    92  		`INSERT INTO version (ver) VALUES (7)`,
    93  	}
    94  
    95  	for _, stmt := range stmts {
    96  		_, err := ss.db.Exec(stmt)
    97  		//ss.DebugLog.Printf("%s (%v)\n", stmt, err)
    98  		if err != nil {
    99  			return err
   100  		}
   101  
   102  	}
   103  
   104  	return nil
   105  }
   106  
   107  func (ss *SQLStore) schemaVersion() (int, error) {
   108  	var v int
   109  	err := ss.db.QueryRow(`SELECT MAX(ver) FROM version`).Scan(&v)
   110  	if err != nil {
   111  		// should distinguish between missing version table and other errors,
   112  		// but hey.
   113  		return 0, nil
   114  	}
   115  	return v, nil
   116  }