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

     1  package lines_test
     2  
     3  import (
     4  	"database/sql"
     5  	"errors"
     6  	"fmt"
     7  	"log"
     8  	"net/http"
     9  	"os"
    10  	"strings"
    11  	"testing"
    12  
    13  	"github.com/ncruces/go-sqlite3"
    14  	"github.com/ncruces/go-sqlite3/driver"
    15  	_ "github.com/ncruces/go-sqlite3/embed"
    16  	"github.com/ncruces/go-sqlite3/ext/lines"
    17  	_ "github.com/ncruces/go-sqlite3/tests/testcfg"
    18  )
    19  
    20  func Example() {
    21  	db, err := driver.Open(":memory:", func(c *sqlite3.Conn) error {
    22  		lines.Register(c)
    23  		return nil
    24  	})
    25  	if err != nil {
    26  		log.Fatal(err)
    27  	}
    28  	defer db.Close()
    29  
    30  	res, err := http.Get("https://storage.googleapis.com/quickdraw_dataset/full/simplified/calendar.ndjson")
    31  	if err != nil {
    32  		log.Fatal(err)
    33  	}
    34  	defer res.Body.Close()
    35  
    36  	rows, err := db.Query(`
    37  		SELECT
    38  			line ->> '$.countrycode' as countrycode,
    39  			COUNT(*)
    40    		FROM lines_read(?)
    41    		GROUP BY 1
    42    		ORDER BY 2 DESC
    43    		LIMIT 5`,
    44  		sqlite3.Pointer(res.Body))
    45  	if err != nil {
    46  		log.Fatal(err)
    47  	}
    48  	defer rows.Close()
    49  
    50  	var countrycode sql.RawBytes
    51  	var count int
    52  	for rows.Next() {
    53  		err := rows.Scan(&countrycode, &count)
    54  		if err != nil {
    55  			log.Fatal(err)
    56  		}
    57  		fmt.Printf("%s: %d\n", countrycode, count)
    58  	}
    59  	if err := rows.Err(); err != nil {
    60  		log.Fatal(err)
    61  	}
    62  	// Expected output:
    63  	// US: 141001
    64  	// GB: 22560
    65  	// CA: 11759
    66  	// RU: 9250
    67  	// DE: 8748
    68  }
    69  
    70  func Test_lines(t *testing.T) {
    71  	t.Parallel()
    72  
    73  	db, err := driver.Open(":memory:", func(c *sqlite3.Conn) error {
    74  		lines.Register(c)
    75  		return nil
    76  	})
    77  	if err != nil {
    78  		log.Fatal(err)
    79  	}
    80  	defer db.Close()
    81  
    82  	const data = "line 1\nline 2\r\nline 3\n"
    83  
    84  	rows, err := db.Query(`SELECT rowid, line FROM lines(?)`, data)
    85  	if err != nil {
    86  		t.Fatal(err)
    87  	}
    88  	defer rows.Close()
    89  
    90  	for rows.Next() {
    91  		var id int64
    92  		var line string
    93  		err := rows.Scan(&id, &line)
    94  		if err != nil {
    95  			t.Fatal(err)
    96  		}
    97  		if want := fmt.Sprintf("line %d", id); line != want {
    98  			t.Errorf("got %q, want %q", line, want)
    99  		}
   100  	}
   101  }
   102  
   103  func Test_lines_error(t *testing.T) {
   104  	t.Parallel()
   105  
   106  	db, err := driver.Open(":memory:", func(c *sqlite3.Conn) error {
   107  		lines.Register(c)
   108  		return nil
   109  	})
   110  	if err != nil {
   111  		log.Fatal(err)
   112  	}
   113  	defer db.Close()
   114  
   115  	_, err = db.Exec(`SELECT rowid, line FROM lines(?)`, nil)
   116  	if err == nil {
   117  		t.Fatal("want error")
   118  	} else {
   119  		t.Log(err)
   120  	}
   121  
   122  	_, err = db.Exec(`SELECT rowid, line FROM lines_read(?)`, "xpto")
   123  	if err == nil {
   124  		t.Fatal("want error")
   125  	} else {
   126  		t.Log(err)
   127  	}
   128  }
   129  
   130  func Test_lines_read(t *testing.T) {
   131  	t.Parallel()
   132  
   133  	db, err := driver.Open(":memory:", func(c *sqlite3.Conn) error {
   134  		lines.Register(c)
   135  		return nil
   136  	})
   137  	if err != nil {
   138  		log.Fatal(err)
   139  	}
   140  	defer db.Close()
   141  
   142  	const data = "line 1\nline 2\r\nline 3\n"
   143  
   144  	rows, err := db.Query(`SELECT rowid, line FROM lines_read(?)`,
   145  		sqlite3.Pointer(strings.NewReader(data)))
   146  	if err != nil {
   147  		t.Fatal(err)
   148  	}
   149  	defer rows.Close()
   150  
   151  	for rows.Next() {
   152  		var id int64
   153  		var line string
   154  		err := rows.Scan(&id, &line)
   155  		if err != nil {
   156  			t.Fatal(err)
   157  		}
   158  		if want := fmt.Sprintf("line %d", id); line != want {
   159  			t.Errorf("got %q, want %q", line, want)
   160  		}
   161  	}
   162  }
   163  
   164  func Test_lines_test(t *testing.T) {
   165  	t.Parallel()
   166  
   167  	db, err := driver.Open(":memory:", func(c *sqlite3.Conn) error {
   168  		lines.Register(c)
   169  		return nil
   170  	})
   171  	if err != nil {
   172  		log.Fatal(err)
   173  	}
   174  	defer db.Close()
   175  
   176  	rows, err := db.Query(`SELECT rowid, line FROM lines_read(?)`, "lines_test.go")
   177  	if errors.Is(err, os.ErrNotExist) {
   178  		t.Skip(err)
   179  	}
   180  	if err != nil {
   181  		t.Fatal(err)
   182  	}
   183  	defer rows.Close()
   184  
   185  	for rows.Next() {
   186  		var id int64
   187  		var line string
   188  		err := rows.Scan(&id, &line)
   189  		if err != nil {
   190  			t.Fatal(err)
   191  		}
   192  	}
   193  }