Re: [sqlite] Documentation update

[Simon Slavin]

On 9 Apr 2018, at 1:08am, Don V Nielsen  wrote:

> "For the purposes of unique indices, all NULL values are considered [to]
> different from all other NULL values and are thus unique."
> 
> I think it should read "...NULL value are considered different..."
> 
> https://www.sqlite.org/lang_createindex.html

I agree with Mister Nielsen that the original can be better phrased, but  my 
English teacher would not forgive me if I did not suggest an even bigger change:

"For the purposes of unique indices, all NULL values are considered to 
different from all other NULL values and are thus unique."

should be

"In unique indices, each NULL value is considered different to every other NULL 
value.  Thus each NULL value is unique."

Now my teacher is at peace, you may do what what you want.  Since some edits 
are already being made to the page, one might as well suggest ...

-

"Or if no collating sequence is otherwise defined, the built-in BINARY 
collating sequence is used."

Should be

"If no collating sequence is defined for a text column, the built-in BINARY 
collating sequence is used."

-

"There are no arbitrary limits"

should be more like

"There is no arbitrary limit"

though perhaps you might want to leave out the word 'arbitrary'.  Or perhaps 
even leave out the entire sentence, since the spirit of SQLite is that limits 
to all entities are caused only by namespace exhaustion or memory/storage 
limits.

-

"If the optional IF NOT EXISTS clause is present and another index with the 
same name already exists"

Remove the word "optional" since the "if" makes it clear that the clause is 
optional.  Replace "another" with "an".

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Documentation update

[Don V Nielsen]

"to be", or not "to be"  :)

On Sun, Apr 8, 2018 at 7:08 PM, Don V Nielsen  wrote:

> "For the purposes of unique indices, all NULL values are considered [to]
> different from all other NULL values and are thus unique."
>
> I think it should read "...NULL value are considered different..."
>
> https://www.sqlite.org/lang_createindex.html
>
> dvn
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation update

[Don V Nielsen]

"For the purposes of unique indices, all NULL values are considered [to]
different from all other NULL values and are thus unique."

I think it should read "...NULL value are considered different..."

https://www.sqlite.org/lang_createindex.html

dvn
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[mucip . ilbuga]

Dear Simon,
OK. Thanks.
It seems that I need to make moore Google search about "Recompiling SQLite
with ICU"...

Regards,
Mucip:)

On Mon, Apr 9, 2018 at 12:56 AM, Simon Slavin  wrote:

> On 8 Apr 2018, at 10:51pm, mucip.ilb...@gmail.com wrote:
>
> > I develop project on QT/C++ and compile both Linux and Windows platforms.
> > In this case, should I recompile SQLite both of these platforms?! Should
> I
> > make some modifications on QT too?!
>
> I think you will need to do all these things.  I know nothing about QT.
>
> > By the way is there any good guidence to add ICU support to SQLite both
> > Linux and Windows platfoems?...
>
> I have reached the limits of my knowledge.  But other people on this list
> may be able to help you.
>
> There is another solution which does not involve full ICU support.  You
> could instead make your own external functions for UPPER_TR() and
> LOWER_TR() which do just the conversions for Turkish.  Presumably they
> could use tables to look up which characters to convert.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Kolay gelsin,
Mucip:)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Simon Slavin]

On 8 Apr 2018, at 10:51pm, mucip.ilb...@gmail.com wrote:

> I develop project on QT/C++ and compile both Linux and Windows platforms.
> In this case, should I recompile SQLite both of these platforms?! Should I
> make some modifications on QT too?!

I think you will need to do all these things.  I know nothing about QT.

> By the way is there any good guidence to add ICU support to SQLite both
> Linux and Windows platfoems?...

I have reached the limits of my knowledge.  But other people on this list may 
be able to help you.

There is another solution which does not involve full ICU support.  You could 
instead make your own external functions for UPPER_TR() and LOWER_TR() which do 
just the conversions for Turkish.  Presumably they could use tables to look up 
which characters to convert.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[mucip . ilbuga]

Dear Simon,
I develop project on QT/C++ and compile both Linux and Windows platforms.
In this case, should I recompile SQLite both of these platforms?! Should I
make some modifications on QT too?!

By the way is there any good guidence to add ICU support to SQLite both
Linux and Windows platfoems?...

On Mon, Apr 9, 2018 at 12:24 AM, Simon Slavin  wrote:

> On 8 Apr 2018, at 9:55pm, Dr. Mucibirahman İLBUĞA 
> wrote:
>
> > As you see above table it returns incorrect. Only i>>I is working like
> in English language?! When I use this code in PostgreSQL it works correct.
> But I got unexpected result in SQLite unfortunatelly.
>
> Thank you for your examples, which help me understand what you're asking
> for.
>
> Unfortunately, bare SQLite understands only ASCII characters.  It does not
> understand Unicode.  To build in full understanding of Unicode would make
> SQLite almost twice as long.
>
> However, you can add Unicode support to SQLite using ICU:
>
> 
>
> If you're compiling your own SQLite, use section 2 of the above document
> or the option
>
> SQLITE_ENABLE_ICU
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Kolay gelsin,
Mucip:)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Simon Slavin]

On 8 Apr 2018, at 9:55pm, Dr. Mucibirahman İLBUĞA  
wrote:

> As you see above table it returns incorrect. Only i>>I is working like in 
> English language?! When I use this code in PostgreSQL it works correct. But I 
> got unexpected result in SQLite unfortunatelly.

Thank you for your examples, which help me understand what you're asking for.

Unfortunately, bare SQLite understands only ASCII characters.  It does not 
understand Unicode.  To build in full understanding of Unicode would make 
SQLite almost twice as long.

However, you can add Unicode support to SQLite using ICU:



If you're compiling your own SQLite, use section 2 of the above document or the 
option

SQLITE_ENABLE_ICU



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Dr . Mucibirahman İLBUĞA]

08.04.2018 23:23 tarihinde Simon Slavin yazdı:

Can you paste into a reply to this message

some lower-case Turkish text,
what you expect to get,
and what you are getting instead

?  It would be useful to have an example of what is going wrong.


Dear Simon,

First of all thanks a lot for your kind interest.

In Turkish we have lover "i" and lover "ı". And the upper of these 
letters like below;


*Lover**
*   *Upper**
*
i
İ
ı
I
ü
Ü
ç
Ç
ö
Ö
ğ
Ğ
ş
Ş


For example;

"SELECT musteri from tartim;" returns; "ğüşiöçı".

But "SELECT UPPER(musteri) from tartim;" returns; "ğüşIöçı"


As you see above table it returns incorrect. Only i>>I is working like 
in English language?! When I use this code in PostgreSQL it works 
correct. But I got unexpected result in SQLite unfortunatelly.  :(


--
Kolay gelsin,
Mucip:)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Simon Slavin]

On 8 Apr 2018, at 12:50pm, Dr. Mucibirahman İLBUĞA  
wrote:

> The UPPER function is not working properly in Turkish language?! How can I 
> get this?
> 
> For example small "i" is equal to big "I" in Turkish language. But it's not 
> working properly?!

Can you paste into a reply to this message

some lower-case Turkish text,
what you expect to get,
and what you are getting instead

?  It would be useful to have an example of what is going wrong.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPPER function depends on Locale?

[Dr . Mucibirahman İLBUĞA]

Hi,

I am new in this list and this is my first post.

The UPPER function is not working properly in Turkish language?! How can 
I get this?


For example small "i" is equal to big "I" in Turkish language. But it's 
not working properly?!


Thanks in advance...

--
Regards,
Mucip:)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] To use or not to use single quotes with integers

[Simon Slavin]

On 8 Apr 2018, at 11:54am, Markos  wrote:

> CREATE TABLE foods(
>  id integer PRIMARY KEY,
>  type_id integer,
>  name text );
> 
> I can insert type_id without single quote:
> 
> INSERT INTO foods (name, type_id) VALUES ('Rice', 16);
> 
> And also with single quote:
> 
> INSERT INTO foods (name, type_id) VALUES ('Bean', '17');
> 
> select * FROM foods;
> 
> ...
> 423 16  Rice
> 424 17  Bean
> 
> What are the consequences of inserting values in fields of datatype integer 
> with single quotes?

Well done for testing what happens rather than just assuming things worked the 
way you thought.

When you created the table you specified the affinity of each column.  If you 
define a column as INTEGER but supply a string, SQLite checks to see whether 
that string represents an integer.  If it does, then it stores the integer, 
effectively doing the translation for you.  The actual value stored is integer, 
and there is no trace that it was originally specified as a string.

A similar thing happens when you specify that a column has affinity of REAL.  
In both cases, SQLite considers that the CREATE command knows better than 
whatever specifies the value, and does the conversion.  However for the number 
to be stored the conversion has to be reversible.  If SQLite reverses the 
conversion and doesn't get the original string back it stores the string 
instead.

You can test to see what SQLite did using

SELECT id, type_id, typeof(type_id), name FROM foods

See section 3 (more usefully, the entire page) of



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] To use or not to use single quotes with integers

[Markos]

Hi,

I'm a beginner with sqliteand used to insert values in the fields of 
datatype text with single quote and values of datatype integer without 
single quote.


But I realized that sqlite accepts inserting both data types (text and 
integer) with single quote without error.


For example the table:

CREATE TABLE foods(
  id integer PRIMARY KEY,
  type_id integer,
  name text );

I can insert type_id without single quote:

INSERT INTO foods (name, type_id) VALUES ('Rice', 16);

And also with single quote:

INSERT INTO foods (name, type_id) VALUES ('Bean', '17');

select * FROM foods;

...
423 16  Rice
424 17  Bean

What are the consequences of inserting values in fields of datatype 
integer with single quotes?


What are the effects on database size and performance?

Can I have problems in the future to make a query using logical 
operators in these fields?


I'm making this question because it is simpler to implement in my 
interface a routine to assemble a list of fields and values by inserting 
single quotation marks in all elements of the list of values.


Otherwise I would have to identify the datatype of each field to decide 
whether or not to include the quotes.


Thank you,
Markos

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Constraining FTS5 results based on offsets()

[miroslav.marango...@outlook.com]

Hi,
For my use case I need to index some texts, and I also need be able to assign 
attributes to sub-sequences/spans of tokens in the texts. I want to be able to 
search only for keywords/phrases that occur in spans with a certain attribute. 
As an example, imagine we have a set of rich text documents and we want to find 
the locations of the "SQLite rocks" phrase in that set, but not just any 
instance of it- only those that have an attribute "bold".

The general idea, I'm considering at the moment is as follows:
1) create a table 'spans' with columns (doc_id, attrib_id, start_tok, end_tok), 
where doc_id is equal to the corresponding rowid in the FTS table, and 
start_tok and end_tok are the 0-based offsets that delimit a span
2) issue a match (sub?)query against the FTS table and obtain a list of (rowid, 
list-of-offsets in that doc)
3) somehow convert the above results into (rowid, start_offset, end_offset) for 
each entry of the list-of-offsets
4) join the results from 3) with the "spans" table on "rowid=spans.doc_id" 
where "spans.attrib_id=X and start_offset >= spans.start_pos and end_offset <= 
spans.end_pos"

Do you think this general approach makes sense, and how would you approach the 
problem if not? I only have a cursory knowledge of both SQL and SQLite at this 
point, so it's quite possible I'm missing something obvious.

On the implementation side:
- as far as I understand FTS5 has some clear advantages over FTS3/4. Apparently 
there isn't readily available offsets() function in FTS5 yet, but the API 
should make writing one rather straightforward

- step 3) is what I wonder about the most... What would be a good way to 
convert a (doc_id, ) row into (doc_id, start_offset, 
end_offset) tuples for every entry in the list? I'd guess I will have to 
implement some sort of virtual table(a.k.a. "table-valued function")? Perhaps a 
table-valued function that takes a FTS query as its parameter that it then uses 
to obtain (doc_id, ), and expose each hit as (doc_id, 
start_offset, end_offset) rows in the virtual table?

Any thoughts and ideas would be greatly appreciated.

Thank you!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Missing keywords

[Clemens Ladisch]

Klaas Van B. wrote:
> Since 3.23 the words FALSE and TRUE should be added to SQLite Query Language: 
> SQLite Keywords.

But FALSE and TRUE are not keywords.

 says that
| keywords ... may not be used as the names of tables, indices, columns,
| ... or any other named object.

While  says that
| SQLite recognizes the *identifiers* "TRUE" and "FALSE" as boolean
| literals, if and only if those identifiers are not already used for
| some other meaning. If there already exists columns or tables or other
| objects named TRUE or FALSE, then for the sake of backwards
| compatibility, the TRUE and FALSE identifiers refer to those other
| objects, not to the boolean values.

In other words, TRUE and FALSE behave like predefined global variables.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users