[sqlite] How to import TSV table with double quote in it without having to escape double quote
Hi, I got the following error. I don't want to escape the quote charaters in the input. Is there still a way to import quote characters into a sqlite3 table? $ cat my.sql3 create table mytab (var text); .separator "\t" .import /dev/stdin mytab $ cat main.sh #!/usr/bin/env bash # vim: set noexpandtab tabstop=2: set -v > outfile.sqlite3 sqlite3 outfile.sqlite3 '.read my.sql3' <<< '"abc"' sqlite3 -header -separator $'\t' outfile.sqlite3 "select * from mytab;" > outfile.sqlite3 sqlite3 outfile.sqlite3 '.read my.sql3' <<< '"abc"|xyz' sqlite3 -header -separator $'\t' outfile.sqlite3 "select * from mytab;" $ ./main.sh > outfile.sqlite3 sqlite3 outfile.sqlite3 '.read my.sql3' <<< '"abc"' sqlite3 -header -separator $'\t' outfile.sqlite3 "select * from mytab;" var abc > outfile.sqlite3 sqlite3 outfile.sqlite3 '.read my.sql3' <<< '"abc"|xyz' /dev/stdin:1: unescaped " character /dev/stdin:1: unterminated "-quoted field sqlite3 -header -separator $'\t' outfile.sqlite3 "select * from mytab;" var abc"|xyz -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why sqlite fts5 Unicode61 Tokenizer does not support CJK(Chinese Japanese Krean)?
Hi Hideaki,
Thanks for your reply which made me figure out why I said icu version does
"not" support Chinese: b/c in Chinese '中文' can be tokenize as either '中文' or
'中' or '文' so when query '中文' or '中*' I can get the result but no result when
query '文'. The same goes to '为什么', which can be be tokenize as either '为什么' or
'为' or '什么' so no result for when query '什么'
And sadly fts5+unicode 61 definitely does not support Chinese.
BTW, it also helps me realize that I had answered this question myself at 2014
here, https://stackoverflow.com/a/31396975/301513. So basically icu does the
same as iOS CFStringTokenizer
Qiulang
在 2018-09-22 22:49:24，"Hideaki Takahashi" 写道：
>Hello,
>
>full text search index can be used to see how the text is tokenized for
>both FTS4 and FTS5.
>for FTS4, fts3tokenize can be used too.
>
>sqlite> CREATE VIRTUAL TABLE icu_zh_cn USING fts3tokenize(icu, zh_CN);
>sqlite> SELECT token, start, end, position FROM icu_zh_cn WHERE
>INPUT='为什么不支持中文 fts5 does not seem to work for chinese';
>为什么|0|9|0
>不|9|12|1
>支持|12|18|2
>中文|18|24|3
>fts5|25|29|4
>does|30|34|5
>not|35|38|6
>seem|39|43|7
>to|44|46|8
>work|47|51|9
>for|52|55|10
>chinese|56|63|11
>
>based on the output above, the query below works and makes sense to me.
>sqlite> select * from zh_text where text match '中文';
>为什么不支持中文 icu does not seem to work for chinese
>
>
>FTS5 + unicode61
>sqlite> CREATE VIRTUAL TABLE ft5_test USING fts5(content, tokenize =
>'porter unicode61 remove_diacritics 1');
>sqlite> INSERT INTO ft5_test values('为什么不支持中文 fts5 does not seem to work
>for chinese');
>sqlite> CREATE VIRTUAL TABLE ft5_test_vocab_i USING fts5vocab(ft5_test,
>'instance');
>sqlite> SELECT term, doc, col, offset FROM ft5_test_vocab_i;
>(snip non-Chinese portion)
>为什么不支持中文|1|content|0
>
>FTS4 + ICU(zh_CN)
>sqlite> CREATE VIRTUAL TABLE zh_text USING fts4(text, tokenize=icu zh_CN);
>sqlite> INSERT INTO zh_text values('为什么不支持中文 icu does not seem to work for
>chinese');
>sqlite> CREATE VIRTUAL TABLE zh_terms USING fts4aux(zh_text);
>sqlite> SELECT term, col, documents FROM zh_terms;
>(snip non-Chinese portion)
>不|*|1
>不|0|1
>中文|*|1
>中文|0|1
>为什么|*|1
>为什么|0|1
>支持|*|1
>支持|0|1
>
>Thanks,
>Hideaki
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...
Folks, I cannot seem to find a means of filtering on negated operators, e.g. <>, not null, not like, etc., in the xBestIndex() method for virtual vables. As best as I can tell, I cannot, unless there is something I am missing, hence this inquiry. In a few virtual tables I have implemented, I have handled the SQLITE_INDEX_CONSTRAINT_EQ, and the SQLITE_INDEX_CONSTRAINT_LIKE (for example) in the xBestIndex and xFilter. These code paths are taken for queries of the form: select * from myvtab where mycol = 'xxx'; select * from myvtab where mycol like 'xxx'; but /not/ for queries of the form: select * from myvtab where mycol <> 'xxx'; select * from myvtab where mycol not like 'xxx'; I can work around these things for now with caveats in documentation, but it does sometimes cause confusion to users. For example, in one case I have extended the syntax of LIKE . That extension of syntax is invoked for a positive LIKE constraint, but is bypassed for a negated one. I can work around that with an extension function, but I won't get the hints at record enumeration time that could reduce the dataset from the underlying source. In other cases, I have some 'required' columns, which must be present in a EQ constraints (usually they wind up being parameters to a function call that generates the underlying data). I emit an error when such constraints are missing, but it can be confusing to users when: select * from myvtab where mycol <> 'xxx'; indicates that "you must have a constraint on 'mycol'" Lastly, some behavioural inconsistencies occur between these forms: select * from myvtab where mycol = null; select * from myvtab where mycol is null; Since the first comes in as a constraint to xBestIndex, whereas the second does not. Anyway, as I said, I can work around this for now, but I thought I would ask if: 1) is it true: xBestIndex doesn't get to see negated predicates, or is it just somewhere that I have not found? 2) if it's not possible, would it be worthwhile to consider extending the operator set in some way to present the negative clauses at some release in the future? Thanks for any info! -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] weekday time modifier
select strftime('%Y-%m-%d %H:%M', '2018-09-23 8:59', 'localtime','weekday
0');
Also gets a bit confusing. The "weekday" operates on the UTC time.
On Sun, Sep 23, 2018 at 11:42 PM, Kevin Martin wrote:
> Hi,
>
> Not sure if this is me misreading it, but the description of the weekday
> modifier in the documentation seems a bit ambiguous.
>
> It says:
>
> > The "weekday" modifier advances the date forward to the next date where
> the weekday number is N. Sunday is 0, Monday is 1, and so forth.
>
> It is not clear what happens when the date before the modifier is already
> the correct weekday. I interpreted this as it would advance by a full week,
> but it does't, it leaves the date untouched:
>
> SQLite version 3.8.10.2 2015-05-20 18:17:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> select strftime('%Y-%m-%d', '2018-09-23', 'weekday 0');
> 2018-09-23
> sqlite>
>
> I seem the same behaviour in 3.24.
>
> Thanks,
> Kev
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug report: Window functions in VIEWs broken in 3.25.1
Hi, Using the sqlite-tools-linux-x86-3250100 Linux binaries I find that Window functions in VIEWS behave differently from PostgreSQL 9.6 and from what I expect. DROP TABLE IF EXISTS example; CREATE TABLE example(t INT, total INT); INSERT INTO example VALUES(0,2); INSERT INTO example VALUES(5,1); INSERT INTO example VALUES(10,1); DROP VIEW IF EXISTS view_example; CREATE VIEW view_example AS SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM example ; SELECT * FROM view_example; In SQLite 3.25.1 I get 0, 0, 0, while PostgreSQL 9.6 gives 0, 1, 2. Thanks, -- https://bjoern.hoehrmann.de/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] More bug with sqlite3_declare_vtab; also comments about ALTER TABLE
There seems a bug with sqlite3_declare_vtab that if you specify both INTEGER PRIMARY KEY and WITHOUT ROWID then it segfaults. It is easily enough to work around, but it shouldn't segfault if the string pointer is a valid pointer to a null-terminated string and the database pointer is a valid one given to xCreate or xConnect. Also, thank you to add "PRAGMA legacy_alter_table"; otherwise some things can break (including the old documentation specifying different behaviour with no hint that it would change). One thing I wanted to have is to be able to use the ALTER TABLE command to rename views; it should not be too difficult to fix. I once fixed this myself actually so that ALTER TABLE could also be used to renae views, although perhaps it might not be thoroughly tested. Furthermore, a documentation problem with window functions is that the none of lang.html, lang_expr.html, and lang_select.html mention window functions at all except as part of the syntax diagram in lang_expr.html (although window definitions are also mentioned in lang_select.html, not window functions) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_get_table() failed with error "database is locked"
Hi, Multiple applications called sqlite3_get_table() to read data in a same db file at the same time, got error "database is locked"? Why read-only access is locked? How to resolve the issue? Thanks, Liu ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite 3.25.1 windows function. So it should be?
Hi! Maybe I do not understand something, but does it really have to work this way? DROP TABLE IF EXISTS t; CREATE TABLE t (v1,v2,v3,v4); INSERT INTO t (v1,v2,v3,v4) VALUES (1,1,1,0),(1,1,0,1),(2,1,0,10),(3,1,0,100),(3,1,0,1000); SELECT v1,v2,sum(v3+v4) OVER (PARTITION BY v2 ORDER BY v1) as val FROM t v1 v2 val 1 1 10001 1 1 10001 2 1 10011 3 1 1 3 1 1 SELECT v1,v2,sum(v3+v4) OVER (PARTITION BY v2 ORDER BY v1) as val FROM t GROUP BY v1,v2; v1 v2 val 1 1 1 2 1 10010 3 1 10110 Yes, I know that v1 is duplicated, but whether the result should disappear 10011? --- Anton Azanov -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] weekday time modifier
Hi,
Not sure if this is me misreading it, but the description of the weekday
modifier in the documentation seems a bit ambiguous.
It says:
> The "weekday" modifier advances the date forward to the next date where the
> weekday number is N. Sunday is 0, Monday is 1, and so forth.
It is not clear what happens when the date before the modifier is already the
correct weekday. I interpreted this as it would advance by a full week, but it
does't, it leaves the date untouched:
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select strftime('%Y-%m-%d', '2018-09-23', 'weekday 0');
2018-09-23
sqlite>
I seem the same behaviour in 3.24.
Thanks,
Kev
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
