github.com/ncruces/go-sqlite3@v0.15.1-0.20240520133447-53eef1510ff0/ext/csv/csv_test.go (about)

     1  package csv_test
     2  
     3  import (
     4  	"fmt"
     5  	"log"
     6  	"testing"
     7  
     8  	"github.com/ncruces/go-sqlite3"
     9  	_ "github.com/ncruces/go-sqlite3/embed"
    10  	"github.com/ncruces/go-sqlite3/ext/csv"
    11  	_ "github.com/ncruces/go-sqlite3/tests/testcfg"
    12  )
    13  
    14  func Example() {
    15  	db, err := sqlite3.Open(":memory:")
    16  	if err != nil {
    17  		log.Fatal(err)
    18  	}
    19  	defer db.Close()
    20  
    21  	csv.Register(db)
    22  
    23  	err = db.Exec(`
    24  		CREATE VIRTUAL TABLE eurofxref USING csv(
    25  			filename = 'testdata/eurofxref.csv',
    26  			header   = YES,
    27  			columns  = 42,
    28  		)`)
    29  	if err != nil {
    30  		log.Fatal(err)
    31  	}
    32  
    33  	stmt, _, err := db.Prepare(`SELECT USD FROM eurofxref WHERE Date = '2022-02-22'`)
    34  	if err != nil {
    35  		log.Fatal(err)
    36  	}
    37  	defer stmt.Close()
    38  
    39  	if stmt.Step() {
    40  		fmt.Printf("On Twosday, 1€ = $%g", stmt.ColumnFloat(0))
    41  	}
    42  	if err := stmt.Reset(); err != nil {
    43  		log.Fatal(err)
    44  	}
    45  
    46  	err = db.Exec(`DROP TABLE eurofxref`)
    47  	if err != nil {
    48  		log.Fatal(err)
    49  	}
    50  	// Output:
    51  	// On Twosday, 1€ = $1.1342
    52  }
    53  
    54  func TestRegister(t *testing.T) {
    55  	t.Parallel()
    56  
    57  	db, err := sqlite3.Open(":memory:")
    58  	if err != nil {
    59  		t.Fatal(err)
    60  	}
    61  	defer db.Close()
    62  
    63  	csv.Register(db)
    64  
    65  	const data = `
    66  "Rob"	"Pike"	rob
    67  "Ken"	Thompson	ken
    68  Robert	"Griesemer"	"gri"`
    69  	err = db.Exec(`
    70  		CREATE VIRTUAL TABLE temp.users USING csv(
    71  			data   = ` + sqlite3.Quote(data) + `,
    72  			schema = 'CREATE TABLE x(first_name, last_name, username)',
    73  			comma  = '\t'
    74  		)`)
    75  	if err != nil {
    76  		t.Fatal(err)
    77  	}
    78  
    79  	stmt, _, err := db.Prepare(`SELECT * FROM temp.users WHERE rowid = 1 ORDER BY username`)
    80  	if err != nil {
    81  		t.Fatal(err)
    82  	}
    83  	defer stmt.Close()
    84  
    85  	if !stmt.Step() {
    86  		t.Fatal("no rows")
    87  	}
    88  	if got := stmt.ColumnText(0); got != "Rob" {
    89  		t.Errorf("got %q want Rob", got)
    90  	}
    91  	if stmt.Step() {
    92  		t.Fatal("more rows")
    93  	}
    94  
    95  	err = db.Exec(`ALTER TABLE temp.users RENAME TO csv`)
    96  	if err != nil {
    97  		t.Fatal(err)
    98  	}
    99  
   100  	err = db.Exec(`PRAGMA integrity_check`)
   101  	if err != nil {
   102  		t.Error(err)
   103  	}
   104  
   105  	err = db.Exec(`PRAGMA quick_check`)
   106  	if err != nil {
   107  		t.Error(err)
   108  	}
   109  
   110  	err = db.Exec(`DROP TABLE temp.csv`)
   111  	if err != nil {
   112  		t.Error(err)
   113  	}
   114  }
   115  
   116  func TestRegister_errors(t *testing.T) {
   117  	t.Parallel()
   118  
   119  	db, err := sqlite3.Open(":memory:")
   120  	if err != nil {
   121  		t.Fatal(err)
   122  	}
   123  	defer db.Close()
   124  
   125  	csv.Register(db)
   126  
   127  	err = db.Exec(`CREATE VIRTUAL TABLE temp.users USING csv()`)
   128  	if err == nil {
   129  		t.Fatal("want error")
   130  	} else {
   131  		t.Log(err)
   132  	}
   133  
   134  	err = db.Exec(`CREATE VIRTUAL TABLE temp.users USING csv(data='abc', data='abc')`)
   135  	if err == nil {
   136  		t.Fatal("want error")
   137  	} else {
   138  		t.Log(err)
   139  	}
   140  
   141  	err = db.Exec(`CREATE VIRTUAL TABLE temp.users USING csv(data='abc', xpto='abc')`)
   142  	if err == nil {
   143  		t.Fatal("want error")
   144  	} else {
   145  		t.Log(err)
   146  	}
   147  
   148  	err = db.Exec(`CREATE VIRTUAL TABLE temp.users USING csv(data='abc', comma='"')`)
   149  	if err == nil {
   150  		t.Fatal("want error")
   151  	} else {
   152  		t.Log(err)
   153  	}
   154  
   155  	err = db.Exec(`CREATE VIRTUAL TABLE temp.users USING csv(data='abc', header=tru)`)
   156  	if err == nil {
   157  		t.Fatal("want error")
   158  	} else {
   159  		t.Log(err)
   160  	}
   161  }