Re: [sqlite] Encoding question

[Richard Hipp]

On 2/1/15, Peter Haworth  wrote:
> I'm new to the unicode world so this question may not make sense.
>
> The "PRAGMA encoding" statement tells me the encoding of a database. Can I
> rely on all data in the database having that encoding? For example, if the
> encoding is UTF8 and a row is inserted containing UTF16 encoded data, will
> it still end up as UTF8 data in the database?
>

If the database encoding is UTF8 and you insert data using
sqlite3_bind_text16() or a similar function, then SQLite automatically
converts UTF16 to UTF8.  Likewise, if you extract data using
sqlite3_column_text16() or a similar routine, then the UTF8 to UTF16
conversion is automatic.

If you run an INSERT statement that has the data as part of the INSERT
statement itself (not normally recommended due to the risk of SQL
injection if you fail to properly escape the data) then any necessary
conversions are also automatic.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encoding question

[Stephan Beal]

On Mon, Feb 2, 2015 at 12:49 AM, RSmith  wrote:

> guarantee that /IF/ you put valid UTF-8 data in there, it will be handled
> and returned correctly).
>

For a given definition of "correct." A relatively common topic on this list
is the handling of locale-specific collations (a topic i'm not qualified to
comment on), with "ICU extension" being a common part of any answers.

Google says:

http://www.sqlite.org/src/info/d9fbbad0c2f647c3fdf715fc9fd64af53aedfc43

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encoding question

[RSmith]

On 2015/02/02 01:12, Peter Haworth wrote:

I'm new to the unicode world so this question may not make sense.

The "PRAGMA encoding" statement tells me the encoding of a database. Can I
rely on all data in the database having that encoding? For example, if the
encoding is UTF8 and a row is inserted containing UTF16 encoded data, will
it still end up as UTF8 data in the database?


Not exactly sure if I understand what you are asking, so this answer may make 
even less sense!

If you are asking whether or not you can rely on the data which you do not control, inside a DB set to UTF-8, to always be UTF-8, 
then the simple answer is: Obviously not, it holds whatever the user (or program using the DB) stores in there however he/she/it 
stores it. What the encoding means simply is that the database engine (SQLite in this case) will treat the data in the database as 
if it is UTF-8 data. It will store, compare, collate, order, extract and do all the other things with the expectation that the data 
will conform to the UTF-8 standard and as such return to you (the user) valid UTF-8 based answers.


None of this prevents you from sticking a BLOB or some UTF-16 or indeed any other Unicode text in there but you might find the 
storing and returning of the values and query answers are not exactly what you expected with the format being somewhat off.


In short, the UTF-8 Pragma settings /allows/ your data to be interpreted as such. It doesn't /force/ it, nor magically /converts/ 
the data into UTF-8, and it most certainly does not under any circumstances *guarantee* the UTF-8-ness of data. (Though it does 
guarantee that /IF/ you put valid UTF-8 data in there, it will be handled and returned correctly).



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

[sqlite] Encoding question

[Peter Haworth]

I'm new to the unicode world so this question may not make sense.

The "PRAGMA encoding" statement tells me the encoding of a database. Can I
rely on all data in the database having that encoding? For example, if the
encoding is UTF8 and a row is inserted containing UTF16 encoded data, will
it still end up as UTF8 data in the database?

Thanks,
Pete
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible to get list of terms in the Full Text Index?

[Rael Bauer]

Hi,

Is it at all possible to get a listing of all terms in the Full Text 
Index and which rowid's they were found in?


E.g.

row1: one, two, three, three
row2: one
row3: three

Listing:
one [1,2]
two [1]
three [1,3]

And with a score count would be even better..

Is such information available?

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

Re: [sqlite] System.Data.SQLite for Pocket PC & RTREE

[Green Fields]

 

Hi

 

I have investigated this problem further and it appears that RTree is
definitely enabled, most queries work fine, but a query to the index fails.

I have tried a straight query to the index

SELECT * FROM idxNetwork WHERE ROWID = 1, which fails crashing the app at
command.ExecuteReader() with no exception thrown

As does SELECT * FROM idxNetwork

 

A query to sqlite_master confirms that the index tables exist.

 

I realise that Windows Mobile 6 is not well supported any more, but can
anyone help with some insight of what may be causing this to occur on the
Mobile and not the desktop?

 

Thanks

 

Duncan 

 

 

From: Green Fields [mailto:defn...@gmail.com] 
Sent: Sunday, 18 January 2015 3:46 PM
To: 'sqlite-users@sqlite.org'
Subject: System.Data.SQLite for Pocket PC & RTREE

 

Hi

I hope this message doesn't double up but the last one did not appear to
make it.

 

I'm new to the list and newish to SQLite and would appreciate some tips.

I'm attempting to create an application that requires a spatial rtree query,
and this works extremely well using the x86 version of the
System.Data.SQLite library
(sqlite-netFx40-static-binary-bundle-Win32-2010-1.0.94.0.zip). However, when
I attempt to run the same query using
(sqlite-netFx35-binary-PocketPC-ARM-2008-1.0.94.0.zip) in a pocketPC port,
the app crashes, and I have been unable to get any feedback from the debug
because the connection to the device is broken. Standard SQL queries and
sqlite_version() work fine.

 

I'm pretty sure rtree would be enabled in the cf binary looking at the
source config, but I don't know of a way to check for this in the compiled
binary short of running an rtree query.

 

I am new to debugging on mobile devices so there may be a way to log the
error that I'm not aware of, but so far all attempts to trap the error have
failed.

 

Does anyone have any suggestions where the problem might lie?

 

Thanks for any help

 

Duncan

 

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

Re: [sqlite] Custom functions, variable parameters, and SELECT triggers

[David Barrett]

Thank you both for the thoughtful responses.  Comments:

Re: NEW.* -- Thanks Igor for pointing out that my assumptions were totally
off base.  Assumptions are tricky things!

Re: "Why can't the trigger call myFunc(new.colA, new.colB)" -- that would
definitely work, but I'd like to make the trigger independent from the
table schema, such that I could add this trigger to a table without even
knowing its schema.  The ultimate goal is to just automatically add it to
every table in the database, and thereby monitor all changes to all rows on
all tables -- without needing to hand-roll a bunch of triggers for each.

PRAGMA table_info() - Hm, that's a very clever technique.  My original goal
was to create a reusable trigger query that can be applied verbatim to any
table, that will pass all columns to a function for each row change.  It's
sounding like this isn't possible, unfortunately.  But with your trick, I
could at least programmatically generate the appropriate trigger query as
follow:

1) Get a list of all tables
2) For each table, get a list of all columns with PRAGMA table_info()
3) Assemble and execute the appropriate query for each table to create a
comprehensive trigger.

Cool, thanks for the helpful tips!

-david

On Fri, Jan 30, 2015 at 9:31 PM, Tristan Van Berkom <
tris...@upstairslabs.com> wrote:

> On Sat, 2015-01-31 at 00:04 -0500, Igor Tandetnik wrote:
> > On 1/30/2015 10:44 PM, David Barrett wrote:
> > > Is it possible to create a trigger that calls a custom function and
> passes
> > > in NEW.*?
> >
> > Not literally NEW.* . You'll have to spell out individual columns as
> > parameters.
> >
> > > 2) I'm *assuming* if you pass a "*" into that function, it'll just call
> > > that function with however many columns are available.
> >
> > Your assumption is incorrect. If I recall correctly, the syntax
> > "myFunc(*)" is accepted, and is equivalent to "myFunc()" - that is, no
> > parameters are passed. Pretty much the only reason to allow this syntax
> > is to accept "count(*)".
> >
> > > 3) It seems that there is a way to create a custom trigger that has no
> > > byproduct but to call SELECT.  The only reason I can imagine you'd
> want to
> > > do that is to call a custom function.  But can I call that function
> with
> > > all columns using a *?  (I can't quite figure this out from the docs
> alone.)
> >
> > Well, you could have tested it, and discovered it didn't work. You don't
> > even need a custom function, you could have used a built-in one.
> >
> > >  SELECT myFunc( NEW.* );
> >
> > That would produce a syntax error.
> >
> > > Are these assumptions correct, and should the above generally work?
> >
> > No, and no.
> >
> > > My
> > > goal is to execute myFunc() every time there's an INSERT/UPDATE/DELETE
> on a
> > > given table -- but I want myFunc() to be reusable and not need to know
> the
> > > structure of the table it's being called on.
> >
> > I'm not sure how the necessity of myFunc(NEW.*) syntax follows from
> > this. Why can't the trigger call myFunc(new.colA, new.colB)?
> >
> > You can write a variadic custom function (one that can be called with an
> > arbitrary number of arguments), if that's what you are asking.
>
> Additional note,
>
> In order to generate queries on tables for which you dont know their
> structure (I've found this particularly useful in dynamic schema
> upgrades), you might find this useful:
>
>   PRAGMA table_info ('table_name')
>
> This part should help you to generate a query which passes all the
> columns of a given table to myFunc()
>
> Cheers,
> -Tristan
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users