[sqlite] .read command

[FrankLane]

Can I pass parameters to the .read command? Like "select * from test where
data=whatever" and then pass the value of whatever to the .read file
somehow?

Thanks - FL
-- 
View this message in context: 
http://old.nabble.com/.read-command-tp26587826p26587826.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Views across attached databases

[WClark]

Alexey Pechnikov wrote on 30/11/2009 19:58:15:

> This feature was disabled becouse can produce inconsistent database 
schema.
> If you want to enable this feature then the patch is simple:
>
> --- sqlite3-3.6.20.orig/src/attach.c
> +++ sqlite3-3.6.20/src/attach.c
> @@ -447,10 +447,11 @@
> if( pItem->zDatabase==0 ){
>   pItem->zDatabase = sqlite3DbStrDup(pFix->pParse->db, zDb);
> }else if( sqlite3StrICmp(pItem->zDatabase,zDb)!=0 ){
>-  sqlite3ErrorMsg(pFix->pParse,
>+/*  sqlite3ErrorMsg(pFix->pParse,
>  "%s %T cannot reference objects in database %s",
>  pFix->zType, pFix->pName, pItem->zDatabase);
>-  return 1;
>+  return 1;*/
>+  return 0;
> }
> #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
> if( sqlite3FixSelect(pFix, pItem->pSelect) ) return 1;


Thank you for the patch.  This was certainly the area I was playing with. 
However, I had actually commented out the entire "else if" clause.  Surely 
it should continue onto the next part of the sqlite3FixSrcList function 
rather than exit immediately, or am I this wrong?

Also, is it better to only disable this check for views (pFix->zType can 
be used to determine whether view, trigger or index)?  I assume the error 
would still be necessary for indexes, even though triggers seem to ignore 
it anyway.

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

Re: [sqlite] Error message from sqlite3_tokenizer_module.xCreate?

[Ralf Junker]

On 30.11.2009 20:33, Grzegorz Wierzchowski wrote:
> Monday 30 of November 2009 12:29:10 Ralf Junker napisał(a):
>> I am passing various arguments to sqlite3_tokenizer_module.xCreate. In case
>> they are invalid, I would like to return an explaining error message in
>> addition to SQLITE_ERROR. I did not find a way to do this. Is it at all
>> possible?
>>
>> Thanks, Ralf
>
> The last argument of xCreate() is  char **pzErr.
> It is exactly designed for the purpose you describe.
> See also description in doc-zip: sqlite-3_6_18-docs/vtab.html, or
> http://www.sqlite.org/vtab.html

Thank you for your answer! I believe you are mixing up the virtual table 
sqlite3_module.xCreate() in sqlite3.h and 
sqlite3_tokenizer_module.xCreate() in fts3_tokenizer.h.

The latter does not have the pzErr argument:

struct sqlite3_tokenizer_module {



int (*xCreate)(
 int argc,   /* Size of argv array */
 const char *const*argv, /* Tokenizer argument strings */
 sqlite3_tokenizer **ppTokenizer /* OUT: Created tokenizer */
   );

So I believe my question remains unanswered. Any suggestion, anyone?

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

Re: [sqlite] WHERE CLAUSE in UNICODE

[Tim Romano]

Aha. Got it.  After

PRAGMA case_sensitive_like =1

the optimizer might use the index with BINARY collation.

I was under the mistaken impression that LIKE() adapted itself to the 
column's collation. But I see that it is not possible to define one 
column to  use BINARY collation and another column to use NOCASE 
collation and have the LIKE operator be case-sensitive when used with 
the BINARY column and case-insensitive when used with the NOCASE 
column-- at least not without a PRAGMA change.

Thanks.
Tim Romano

Igor Tandetnik wrote:
> ... LIKE is case insensitive by default, while BINARY collation is case 
> sensitive.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WHERE CLAUSE in UNICODE

[Jay A. Kreibich]

On Mon, Nov 30, 2009 at 08:28:12PM -0500, Tim Romano scratched on the wall:
 
> On my system, an index is used with the query above only when collation 
> is "collation nocase". 

  In other words, when LIKE and collation agree on what "equal" is.

> I /believe/ case_sensitive_like is OFF. I've done nothing to change 
> turn it on. How can I be sure?

  SELECT 'A' LIKE 'a';

  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WHERE CLAUSE in UNICODE

[Igor Tandetnik]

Tim Romano  wrote:
> A revised question (sorry for leaving out an important fact on the
> first go): 
> 
> When the default binary collation is being used, why does the
> optimizer not attempt to use an index when the WHERE 
> clause uses the LIKE operator with a text column, as in:
> 
>  ... where myTextColumn like 'M%'

Because LIKE is case insensitive by default, while BINARY collation is case 
sensitive.

> I /believe/ case_sensitive_like is OFF.

Precisely.

Igor Tandetnik


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

Re: [sqlite] WHERE CLAUSE in UNICODE

[Tim Romano]

A revised question (sorry for leaving out an important fact on the first go):

When the default binary collation is being used, why does the optimizer not 
attempt to use an index when the WHERE
 clause uses the LIKE operator with a text column, as in:

  ... where myTextColumn like 'M%'


On my system, an index is used with the query above only when collation is 
"collation nocase". When the default binary collation is used, the identical 
query with LIKE operator in the WHERE clause does a full table scan. In other 
words, the only thing that is different is the collation of the column.

I /believe/ case_sensitive_like is OFF. I've done nothing to change turn it on. 
How can I be sure?

Thanks
Tim Romano

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

Re: [sqlite] Feature Request: More descriptive error message to replace "Constraint Failed."

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Shaun Seckman (Firaxis) wrote:
> It would be extremely useful if this
> error message could contain more information such as which constraint
> failed and even perhaps the data which caused the constraint to fail.
> Even just knowing the column names would be a huge help to debugging
> such errors.

A known issue with a ticket since January 2006:

  http://www.sqlite.org/src/tktview?name=23b2128201

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAksUa0kACgkQmOOfHg372QRicwCeLIt6Jgb9oGfcrsHhybbjIntr
rGAAn2PUV8+kvZMIaNhLlhtQpR6efGAz
=R4YZ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Feature Request: More descriptive error message to replace "Constraint Failed."

[Shaun Seckman (Firaxis)]

Hello everyone,

I've recently stumbled across a plethora of "Constraint
Failed" errors in my code due to a schema change that modified various
columns of tables to be unique.  It would be extremely useful if this
error message could contain more information such as which constraint
failed and even perhaps the data which caused the constraint to fail.
Even just knowing the column names would be a huge help to debugging
such errors.

 

What do you guys think?

 

-Shaun

 

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

Re: [sqlite] WHERE CLAUSE in UNICODE

[Nicolas Williams]

On Mon, Nov 30, 2009 at 08:56:48PM +, Simon Slavin wrote:
> On 30 Nov 2009, at 6:49pm, Nicolas Williams wrote:
> > See my previous message: it would make no sense to have a column with
> > data-dependent collations.  But perhaps I'm missing something.  Can you
> > describe the semantics of data-dependent collations?
> 
> Suppose I am preparing a sales system.  It can be used by users in
> many countries, but a user in each country sees only products sold in
> that country, and they see those products sorted into alphabetic order
> -- however their own country sorts its alphabet.  So in one table I
> have
> 
> Product IDMarket  Product name
> ----  
> 1 US  Box
> 2 US  Bible
> 3 US  Barrel
> 4 France  Boîte
> 5 France  Bible
> [...]
> 
> So in the same column I have words in three different languages.  If
> German had a different alphabet to English, depending on which
> customer I am SELECTing for I will need to use a different collation
> to sort the results.  Of course, I expect this is relatively rare, and
> can be perhaps be handled using VIEWs or some other trick, but I
> wanted to demonstrate that it is sometimes needed.

This falls into case (b): localization.  Here it'd be nice if SQLite3
allowed collations to be named via parameters, so you could compile a
query once.  But it does not, so you need to compile a query for each
language.  But that's as far as it goes: you're still specifying the
collation rather than letting the data specify a collation.

I.e., it'd be nice if you could compile

SELECT product_name FROM products
WHERE market = :country ORDER BY product_name COLLATE :lang ASC;

bind in the country and language, and then execute, but as it is SQLite3
doesn't let you parametrize collations.  So you have to mprintf and
compile a separate query for each collation.

Oh, I suppose one might want to allow a collation to be the result of
another query, so that you could lookup collations by country code, or
what have you

SELECT product_name FROM products
WHERE market = :country
ORDER BY (SELECT lang FROM country_to_lang WHERE country = :country)

But that's still not a collation that is derived from the data being
sorted, but from other data.

This:

SELECT product_name FROM products
WHERE market = :country ORDER BY lang_of(product_name);

makes no sense!  That's because nothing guarantees that
lang_of(product_name) will be singular here.  (Also, not only does it
make no sense, it doesn't help you since SQLite3 will not compile such a
query.)

> >>> Note too that Unicode has codepoints for specifying the language that
> >>> the subsequent text is written in.
> > 
> > http://unicode.org/unicode/faq/languagetagging.html#2
> > http://www.unicode.org/versions/Unicode5.0.0/ch16.pdf#G17521
> > (section 16.9)
> 
> Many thanks.  I did not know this and it saves me from having to
> invent my own system in something else I am doing.  I do understand
> why it should not normally be used and I will not over-use it.

I don't think you should use Unicode language tags though.  See above
and previous messages.

> On 30 Nov 2009, at 7:16pm, Nuno Lucas wrote:
> > Note that some countries have different collations depending on the 
> > objective of the output. For example, dictionary order may be different 
> > from phone-book order and different from other general listing order.
> 
> Arg !

Yes, it all sucks.  At least some language academies are now working to
reduce such problems.  For example, it used to be that case folding is
Spanish did preserve accents in the to-upper-case direction, and Spanish
used to have special sort orders for 'ch' and 'll' (which were as though
a single character).

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

Re: [sqlite] WHERE CLAUSE in UNICODE

[Igor Tandetnik]

Tim Romano  wrote:
> Why does the optimizer not attempt to use an index when the WHERE
> clause uses the LIKE operator with a text column, as in:
> 
>  ... where myTextColumn like 'M%'

It does, when certain conditions are met. See 
http://sqlite.org/optoverview.html , section 4 "The LIKE optimization".

Igor Tandetnik

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

Re: [sqlite] WHERE CLAUSE in UNICODE

[Tim Romano]

Why does the optimizer not attempt to use an index when the WHERE clause 
uses the LIKE operator with a text column, as in:

  ... where myTextColumn like 'M%'


My question ultimately concerns Unicode and indexing, and since these 
subjects are being discussed *passim* in this thread, I hope you don't 
mind my appending the question rather than creating a new thread.


Thanks


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

Re: [sqlite] WHERE CLAUSE in UNICODE

[Simon Slavin]

On 30 Nov 2009, at 6:49pm, Nicolas Williams wrote:

> On Mon, Nov 30, 2009 at 06:37:11PM +, Simon Slavin wrote:
>> 
> 
>> The column 'language'
>> could perhaps be absolute, or perhaps be used as a default if the
>> individual values did not declare a language.  On the other hand, it
>> might perhaps not be necessary to declare the language for each
>> column: it's likely that all columns for any database would want to
>> use the same language for collation.
> 
> See my previous message: it would make no sense to have a column with
> data-dependent collations.  But perhaps I'm missing something.  Can you
> describe the semantics of data-dependent collations?

Suppose I am preparing a sales system.  It can be used by users in many 
countries, but a user in each country sees only products sold in that country, 
and they see those products sorted into alphabetic order -- however their own 
country sorts its alphabet.  So in one table I have

Product ID  Market  Product name
--  --  
1   US  Box
2   US  Bible
3   US  Barrel
4   France  Boîte
5   France  Bible
6   France  Baril
7   Germany Kasten
8   Germany Bibel
9   Germany Faß

So in the same column I have words in three different languages.  If German had 
a different alphabet to English, depending on which customer I am SELECTing for 
I will need to use a different collation to sort the results.  Of course, I 
expect this is relatively rare, and can be perhaps be handled using VIEWs or 
some other trick, but I wanted to demonstrate that it is sometimes needed.

>>> Note too that Unicode has codepoints for specifying the language that
>>> the subsequent text is written in.
> 
> http://unicode.org/unicode/faq/languagetagging.html#2
> http://www.unicode.org/versions/Unicode5.0.0/ch16.pdf#G17521
> (section 16.9)

Many thanks.  I did not know this and it saves me from having to invent my own 
system in something else I am doing.  I do understand why it should not 
normally be used and I will not over-use it.

On 30 Nov 2009, at 7:16pm, Nuno Lucas wrote:

> Note that some countries have different collations depending on the 
> objective of the output. For example, dictionary order may be different 
> from phone-book order and different from other general listing order.

Arg !

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

Re: [sqlite] Getting up and running

[P Kishor]

On Mon, Nov 30, 2009 at 2:18 PM,   wrote:
>
> Hi - thanks for the help. Eventually I will want to get more involved, but 
> this lets me learn sql on my own database which is really helpful. One 
> problem I am having is that I have a tab-delimited text file which I load 
> using the commands:
>
>> CREATE TABLE test(a, b, c, d);
>> .mode tabs
>> .import "tab_delimited_data.txt" test
>> .mode column
>> SELECT d FROM test where d LIKE '%hello%'
>
> the resulting strings are just the first 16 characters. Can you tell me how 
> to get the whole string?
>

If using .mode col, see
sqlite> .width NUM

also

sqlite> .help

Finally, please reply/ask questions on the list instead of directly,
so others also have a chance to answer/learn.

Good luck with sqlite.



>
>
>
>
>
> P Kishor-3 wrote:
>>
>> On Mon, Nov 30, 2009 at 8:09 AM, FrankLane  wrote:
>>>
>>> Hi - I downloaded sqlite-amalgamation-3_6_20.zip and now I have a folder
>>> with
>>> three files: sqlite3.c, sqlite3.h, and sqlite3ext.h. I have no idea what
>>> to
>>> do next. I have a Mac 2.4 GHz Intel Core 2 Duo running Mac OS X 10.6.1.
>>> Can
>>> anyone guide me to a page that steps me through an installation process?
>>
>>
>> What you should do next depends on what you want to do next. Do you
>> want to just use the sqlite database from the command line as is? In
>> that case, do what Simon told you in a different email, that is, use
>> the sqlite version that comes with the operating system. You can find
>> it at /usr/bin/sqlite3 and its header files under /usr/include
>>
>> If you want to tinker with the sqlite source code, tweak the compile
>> time settings, or just want the latest version, then, make sure you
>> have the developer tools installed (Xcode), then go into your folder
>> that you downloaded above and type
>>
>> sqlite-src % ./configure
>> sqlite-src % make
>> sqlite-src % sudo make install
>>
>> The above three commands will build a new version of sqlite and
>> install it under /usr/local/
>>
>> Then, make sure that /usr/local/bin comes before /usr/bin in your
>> search path and fire up /usr/local/bin/sqlite3 and have fun.
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> Sent from Madison, WI, United States
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
> Quoted from:
> http://old.nabble.com/Getting-up-and-running-tp26575335p26579871.html
>
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views across attached databases

[Alexey Pechnikov]

Hello!

On Monday 30 November 2009 20:22:43 wcl...@gfs-hofheim.de wrote:
> Is there a technical reason why triggers work, but views don't, or is this 
> just a feature that's not currently supported in views?  I know that I can 
> create a temporary view and this works.  Is this something I can "hack" 
> easily and has anyone any experience?  I'm not afraid of getting my 
> fingers dirty!

This feature was disabled becouse can produce inconsistent database schema.
If you want to enable this feature then the patch is simple:

--- sqlite3-3.6.20.orig/src/attach.c
+++ sqlite3-3.6.20/src/attach.c
@@ -447,10 +447,11 @@
 if( pItem->zDatabase==0 ){
   pItem->zDatabase = sqlite3DbStrDup(pFix->pParse->db, zDb);
 }else if( sqlite3StrICmp(pItem->zDatabase,zDb)!=0 ){
-  sqlite3ErrorMsg(pFix->pParse,
+/*  sqlite3ErrorMsg(pFix->pParse,
  "%s %T cannot reference objects in database %s",
  pFix->zType, pFix->pName, pItem->zDatabase);
-  return 1;
+  return 1;*/
+  return 0;
 }
 #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
 if( sqlite3FixSelect(pFix, pItem->pSelect) ) return 1;


Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WHERE CLAUSE in UNICODE

[Jay A. Kreibich]

On Mon, Nov 30, 2009 at 06:37:11PM +, Simon Slavin scratched on the wall:
> 
> On 30 Nov 2009, at 5:51pm, Nicolas Williams wrote:
> 
> > Consider a column that contains a person's last name.  Q: do proper
> > names have a language?  A: No, since people can be from all over and
> > even within a single country may have last names of various radically
> > different origins.
> 
> But what is the purpose of collating a column ?  Why, to allow
> it to be indexed, of course.

  That's ONE reason.  You can give a column a specific collation
  without having to index it.  It provides the "natural" order for an
  ORDER BY.  You can also give ORDER BY an explicit collation to use
  for that specific query.

-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error message from sqlite3_tokenizer_module.xCreate?

[Grzegorz Wierzchowski]

Monday 30 of November 2009 12:29:10 Ralf Junker napisał(a):
> I am passing various arguments to sqlite3_tokenizer_module.xCreate. In case
> they are invalid, I would like to return an explaining error message in
> addition to SQLITE_ERROR. I did not find a way to do this. Is it at all
> possible?
>
> Thanks, Ralf

The last argument of xCreate() is  char **pzErr.
It is exactly designed for the purpose you describe.
See also description in doc-zip: sqlite-3_6_18-docs/vtab.html, or 
http://www.sqlite.org/vtab.html

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

Re: [sqlite] WHERE CLAUSE in UNICODE

[Nuno Lucas]

Simon Slavin wrote:
> But what is the purpose of collating a column ?  Why, to allow it to be 
> indexed, of course.  And for it to be indexed every value in the column must 
> be comparable to every other value.  So it might be sufficient to simply 
> declare the column as having a language:
> 
> ALTER TABLE ADD COLUMN familyname UNICODE LANG Deutsche
> 
> Actually, we'd probably use ISO 639-3:
> 
> ALTER TABLE ADD COLUMN familyname UNICODE LANG deu
> 
> That would be sufficient to allow the standard SQL functions like indexing 
> and comparison to be implemented.  The column 'language' could perhaps be 
> absolute, or perhaps be used as a default if the individual values did not 
> declare a language.  On the other hand, it might perhaps not be necessary to 
> declare the language for each column: it's likely that all columns for any 
> database would want to use the same language for collation.
> 

Note that some countries have different collations depending on the 
objective of the output. For example, dictionary order may be different 
from phone-book order and different from other general listing order.

But your idea can still be useful as an optimization for the general 
case (specially for locales where there is no difference).

For me, collation is at the same level as the presentation level. At the 
same time we decide how we format the numbers (number of decimal 
places), dates (-mm-dd, mm-dd-yyy, dd-mm-), etc, is when we 
decide the order of the items to show (the order the user viewing the 
data expects).


Regards,
~Nuno Lucas
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WHERE CLAUSE in UNICODE

[Nicolas Williams]

On Mon, Nov 30, 2009 at 06:37:11PM +, Simon Slavin wrote:
> 
> On 30 Nov 2009, at 5:51pm, Nicolas Williams wrote:
> 
> > Consider a column that contains a person's last name.  Q: do proper
> > names have a language?  A: No, since people can be from all over and
> > even within a single country may have last names of various radically
> > different origins.
> 
> But what is the purpose of collating a column ?  Why, to allow it to
> be indexed, of course.  And for it to be indexed every value in the
> column must be comparable to every other value.  So it might be
> sufficient to simply declare the column as having a language:
> 
> ALTER TABLE ADD COLUMN familyname UNICODE LANG Deutsche
> 
> Actually, we'd probably use ISO 639-3:
> 
> ALTER TABLE ADD COLUMN familyname UNICODE LANG deu

There's already a COLLATE column-constraint.  Given the use of Unicode
'collation' is approximately the same as 'language'.  One use of it is
to ensure that an index can be used to optimize ORDER BY clauses where
the ORDER BY clause's collation is defaulted or the same as in the
index, but it also affects comparisons, even equality comparisons.

> That would be sufficient to allow the standard SQL functions like
> indexing and comparison to be implemented.  The column 'language'
> could perhaps be absolute, or perhaps be used as a default if the
> individual values did not declare a language.  On the other hand, it
> might perhaps not be necessary to declare the language for each
> column: it's likely that all columns for any database would want to
> use the same language for collation.

See my previous message: it would make no sense to have a column with
data-dependent collations.  But perhaps I'm missing something.  Can you
describe the semantics of data-dependent collations?

> > Note too that Unicode has codepoints for specifying the language that
> > the subsequent text is written in.
> 
> I did not know this !  This makes things simpler.  Are you talking
> about
> 
> http://unicode.org/reports/tr35/
> 
> This appears to be a way of specifying a language outside of the text
> stream, not inside it.

No, I meant this:

http://unicode.org/unicode/faq/languagetagging.html#2
http://www.unicode.org/versions/Unicode5.0.0/ch16.pdf#G17521
(section 16.9)

Note that use of Unicode language tags is discouraged.  The same reasons
apply here, IMO.

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

Re: [sqlite] Getting up and running

[P Kishor]

On Mon, Nov 30, 2009 at 8:09 AM, FrankLane  wrote:
>
> Hi - I downloaded sqlite-amalgamation-3_6_20.zip and now I have a folder with
> three files: sqlite3.c, sqlite3.h, and sqlite3ext.h. I have no idea what to
> do next. I have a Mac 2.4 GHz Intel Core 2 Duo running Mac OS X 10.6.1. Can
> anyone guide me to a page that steps me through an installation process?


What you should do next depends on what you want to do next. Do you
want to just use the sqlite database from the command line as is? In
that case, do what Simon told you in a different email, that is, use
the sqlite version that comes with the operating system. You can find
it at /usr/bin/sqlite3 and its header files under /usr/include

If you want to tinker with the sqlite source code, tweak the compile
time settings, or just want the latest version, then, make sure you
have the developer tools installed (Xcode), then go into your folder
that you downloaded above and type

sqlite-src % ./configure
sqlite-src % make
sqlite-src % sudo make install

The above three commands will build a new version of sqlite and
install it under /usr/local/

Then, make sure that /usr/local/bin comes before /usr/bin in your
search path and fire up /usr/local/bin/sqlite3 and have fun.



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WHERE CLAUSE in UNICODE

[Nicolas Williams]

On Mon, Nov 30, 2009 at 01:21:08PM -0500, Igor Tandetnik wrote:
> Nicolas Williams  wrote:
> > IMO you'll have two types of text to sort: a) generic text (e.g.,
> > proper names), b) localized text (e.g., message catalogs).  For (a)
> > you'll want 
> > to pick a collation, _any_ collation.
> 
> Actually, you may want to choose a collation familiar to your
> application's user. After all, she's the one looking at the list of
> names, the one you have to convince the list is in fact sorted.

Indeed, that's one way to pick a collation.  But you might not always be
able to do even that!  Suppose you need to print a checklist and post it
on a wall/door/whatever, and have various people update it.

> E.g. Windows has the concept of default locale and sort order, chosen
> by the user (Control Panel | Regional and Language Options). I imagine
> other operating systems provide something similar. You wouldn't
> normally want to build indexes using this collation though, as it can
> change at any time (in fact, with multiple users sharing the same
> database, you may end up sorting the same data in two different ways
> at the same time). It's only good for sorting on the fly.

Right.  For indexes you need a collation.  You might keep multiple
indexes built with different collations, but that sounds like a waste of
resources (unless you have very static data).

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

Re: [sqlite] WHERE CLAUSE in UNICODE

[Simon Slavin]

On 30 Nov 2009, at 5:51pm, Nicolas Williams wrote:

> Consider a column that contains a person's last name.  Q: do proper
> names have a language?  A: No, since people can be from all over and
> even within a single country may have last names of various radically
> different origins.

But what is the purpose of collating a column ?  Why, to allow it to be 
indexed, of course.  And for it to be indexed every value in the column must be 
comparable to every other value.  So it might be sufficient to simply declare 
the column as having a language:

ALTER TABLE ADD COLUMN familyname UNICODE LANG Deutsche

Actually, we'd probably use ISO 639-3:

ALTER TABLE ADD COLUMN familyname UNICODE LANG deu

That would be sufficient to allow the standard SQL functions like indexing and 
comparison to be implemented.  The column 'language' could perhaps be absolute, 
or perhaps be used as a default if the individual values did not declare a 
language.  On the other hand, it might perhaps not be necessary to declare the 
language for each column: it's likely that all columns for any database would 
want to use the same language for collation.

> Note too that Unicode has codepoints for specifying the language that
> the subsequent text is written in.

I did not know this !  This makes things simpler.  Are you talking about

http://unicode.org/reports/tr35/

This appears to be a way of specifying a language outside of the text stream, 
not inside it.

> Such codepoints could be used for
> deriving a collation from some text.  But again, I don't think this will
> prove useful, both, for the reasons given above (SELECT ...  ORDER BY
> ... COLLATE lang_of(...) makes no sense) and also because users won't
> know how to ensure that such language tags are embedded in the text that
> they write.

You could provide a function in SQLite that converts two text fields into the 
Unicode form:

TEXTINLANG(theText, theLanguage)

and this would be sufficient

I agree that it cannot be done in an elegant manner without at least one slight 
modification to the SQLite syntax parser.

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

Re: [sqlite] WHERE CLAUSE in UNICODE

[Igor Tandetnik]

Nicolas Williams  wrote:
> IMO you'll have two types of text to sort: a) generic text (e.g.,
> proper names), b) localized text (e.g., message catalogs).  For (a)
> you'll want 
> to pick a collation, _any_ collation.

Actually, you may want to choose a collation familiar to your application's 
user. After all, she's the one looking at the list of names, the one you have 
to convince the list is in fact sorted.

E.g. Windows has the concept of default locale and sort order, chosen by the 
user (Control Panel | Regional and Language Options). I imagine other operating 
systems provide something similar. You wouldn't normally want to build indexes 
using this collation though, as it can change at any time (in fact, with 
multiple users sharing the same database, you may end up sorting the same data 
in two different ways at the same time). It's only good for sorting on the fly.

Igor Tandetnik

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

Re: [sqlite] WHERE CLAUSE in UNICODE

[Nicolas Williams]

On Mon, Nov 30, 2009 at 08:15:58AM +, Simon Slavin wrote:
> On 30 Nov 2009, at 1:58am, Igor Tandetnik wrote:
> > Note that Unicode collation is not as simple as you might think. Did
> > you know that in Estonian, 'y' sorts between 'i' and 'j'? Or that in
> > German phonebook sort, 'oe' sorts as if it were a single letter
> > between 'o' and 'p'? Basically, your simplistic approach would only
> > work for plain unaccented Latin letters and English collation rules.
> 
> I spent a lot of time annoyed about this, and ended up deciding that
> the only way to do Unicode sorting correctly is to store the language
> (or collation method) with each piece of Unicode text.  Of course,
> this still gives you the problem of working out which order two pieces
> of text go in if they are in two different languages.  Perhaps you
> also need a 'default language' marker for the entire column.

Consider a column that contains a person's last name.  Q: do proper
names have a language?  A: No, since people can be from all over and
even within a single country may have last names of various radically
different origins.

Now consider a column that contains a person's self-description.  Q:
what language will it be in?  A: If you don't specify it, it could be in
one (or more!) language(s) of the person's choice.  You might not need
to sort by such a column though...

IMO you'll have two types of text to sort: a) generic text (e.g., proper
names), b) localized text (e.g., message catalogs).  For (a) you'll want
to pick a collation, _any_ collation.  For (b) you'll know the language
of the relevant text and can then sort with a specific collation.  If
you think there is a third class of text then you'll be best off forcing
it into (a) or (b), IMO, as statements like this make no sense:

CREATE TABLE foo (txt, lang);
...
-- this makes no sense:
SELECT txt FROM foo ORDER BY txt COLLATE lang; -- where lang is a
   -- column of foo;

Unfortunately you can't parametrize collations in COLLATE clauses in
SQLite3!  I.e., this gives an error:

sqlite> CREATE TABLE toy(a);
sqlite> EXPLAIN SELECT a FROM toy ORDER BY a COLLATE :a ASC;
SQL error: near ":a": syntax error

Therefore for (b) you'll have to compile statements for each collation
that you want to use.  (Say you have per-language tables: you'll need
separate SELECT statements for each table.  Say you have a single table
with a text column and another column indicating language: you'll need
to compile a statement for each language since the collation cannot be
parametrized.)

Making collation functions parametrizable might be a useful extension,
so you could then write:

sqlite> CREATE TABLE toy(txt, lang);
sqlite> explain SELECT txt FROM toy WHERE lang = :a ORDER BY txt COLLATE :b ASC;

where :a specifies some language and :b specifies a collation for that
language.

Note too that Unicode has codepoints for specifying the language that
the subsequent text is written in.  Such codepoints could be used for
deriving a collation from some text.  But again, I don't think this will
prove useful, both, for the reasons given above (SELECT ...  ORDER BY
... COLLATE lang_of(...) makes no sense) and also because users won't
know how to ensure that such language tags are embedded in the text that
they write.

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

[sqlite] Views across attached databases

[WClark]

Hello,

Playing around with attached databases in sqlite 3.6.17, I notice that it 
is possible to create triggers that operate across attached databases, but 
not views.  So something along the lines of:

attach database "other.db" as other;
create table other.a(a integer);
create table b(a integer);
create view v as select * from a union select * from b;

will fail with an error "no such table: main.a" and if explicitly linked 
to "other" then it fails with the error "cannot reference objects in 
database other".

However, the following will work:

create trigger t before insert on b
  when (select rowid from a where a=new.a) is null
  begin
select raise(abort,'need to insert into a first!');
end;

Is there a technical reason why triggers work, but views don't, or is this 
just a feature that's not currently supported in views?  I know that I can 
create a temporary view and this works.  Is this something I can "hack" 
easily and has anyone any experience?  I'm not afraid of getting my 
fingers dirty!

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

Re: [sqlite] sqlite3 bind and insert double values

[Michael Lippautz]

Indeed, I was mistaken by the fact the printf cuts a float/double
after 6 digits (iso c99) if no precision is given.

SQLite seems to store the whole value, despite the situations where
the last digits would be 0's. (I think I've copied some wrong values
in the examples; sorry for that)

And thx for the infos on gps, but I do correct them wich ntrip, which
"should" provide values with an accuracy of about 0.5m. The reason I
wanted to store them as REAL's in first place are queries on them.

--Michael

2009/11/30 Nick Shaw :
> Agreed - the difference in coordinates between the two values amounts to
> 3/10,000's of a second, which is about 9 millimeters.  Most GPS devices
> can't give accuracy to more than 5 meters!
>
> It's also probably nicer storing GPS coordinates as numeric instead of
> text, as then you can use some useful equations on your data set to work
> out such things as which GPS coordinates fall within a certain radius of
> a certain position (as many shop websites use on their "find your
> nearest store" page).  Google API's website has some example functions
> to do just this on SQL data stored as GPS floats.
>
> Nick.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: 30 November 2009 14:59
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3 bind and insert double values
>
>
> On 30 Nov 2009, at 2:05pm, Michael Lippautz wrote:
>
>> 47.824669 / 47.824669167
>
> Same number.  If you need better precision than that, declare the column
> type as TEXT and bind your data as text.
>
> But since you're using GPS coordinates I can tell you it's not
> necessary.  That seventh digit in a GPS coordinate gives you more
> precision than a GPS device can actually deliver.  No consumer GPS
> device is going to quote you 47.8246690 in one place and 47.8246691 to
> mean a different place.  So you don't need to worry about your rounding
> error.
>
> Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 bind and insert double values

[Nick Shaw]

Agreed - the difference in coordinates between the two values amounts to
3/10,000's of a second, which is about 9 millimeters.  Most GPS devices
can't give accuracy to more than 5 meters!

It's also probably nicer storing GPS coordinates as numeric instead of
text, as then you can use some useful equations on your data set to work
out such things as which GPS coordinates fall within a certain radius of
a certain position (as many shop websites use on their "find your
nearest store" page).  Google API's website has some example functions
to do just this on SQL data stored as GPS floats.

Nick.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: 30 November 2009 14:59
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite3 bind and insert double values


On 30 Nov 2009, at 2:05pm, Michael Lippautz wrote:

> 47.824669 / 47.824669167

Same number.  If you need better precision than that, declare the column
type as TEXT and bind your data as text.

But since you're using GPS coordinates I can tell you it's not
necessary.  That seventh digit in a GPS coordinate gives you more
precision than a GPS device can actually deliver.  No consumer GPS
device is going to quote you 47.8246690 in one place and 47.8246691 to
mean a different place.  So you don't need to worry about your rounding
error.

Simon.
___
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

Re: [sqlite] sqlite3 bind and insert double values

[Simon Slavin]

On 30 Nov 2009, at 2:05pm, Michael Lippautz wrote:

> 47.824669 / 47.824669167

Same number.  If you need better precision than that, declare the column type 
as TEXT and bind your data as text.

But since you're using GPS coordinates I can tell you it's not necessary.  That 
seventh digit in a GPS coordinate gives you more precision than a GPS device 
can actually deliver.  No consumer GPS device is going to quote you 47.8246690 
in one place and 47.8246691 to mean a different place.  So you don't need to 
worry about your rounding error.

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

Re: [sqlite] Getting up and running

[Simon Slavin]

On 30 Nov 2009, at 2:09pm, FrankLane wrote:

> Hi - I downloaded sqlite-amalgamation-3_6_20.zip and now I have a folder with
> three files: sqlite3.c, sqlite3.h, and sqlite3ext.h. I have no idea what to
> do next. I have a Mac 2.4 GHz Intel Core 2 Duo running Mac OS X 10.6.1. Can
> anyone guide me to a page that steps me through an installation process?

You have already a version of SQLite installed on your Mac.  Not the latest 
version, but one which works well enough to get started.  I would recommend you 
start off by typing 'sqlite3' at a command prompt and work through some of the 
ideas in

http://www.sqlite.org/sqlite.html

Just to let you explore how it all works.  Your next steps depend on which 
programming language you want to use.  You will find the .h file for the 
version included with OS X in

/usr/include/sqlite3.h

Or if you want the extra facilities included with your latest downloaded 
version use the .h file provided by that one, and compile the .c file with your 
application.  Here are two sample programs which show you how to use the .h 
file:

http://www.sqlite.org/quickstart.html

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

Re: [sqlite] sqlite3 bind and insert double values

[Igor Tandetnik]

Michael Lippautz wrote:
> Well, I compared a casual printf("%f\n",val) with the entry stored in
> the database (as REAL). The entry is inserted into the db via
> sqlite3_bind_double (prepare/reset/step)
> 
> Some examples:
> fprintf / database (looked up via .dump on the table)
> 47.824669 / 47.824669167
> 47.824676 / 47.824672667
> 47.824676 / 47.824676
> 
> I thought that sqlite would store them as text anyways

It won't, unless you declare your column as TEXT (or char, or varchar, or 
similar) in CREATE TABLE statement. Otherwise it would store a double as a 
double. Even if you force the value to be stored as text, nothing says that 
SQLite would internally use printf("%f") and not some other, perhaps more 
precise, format.

> Converting the double to a char* via snprintf and using
> sqlite3_bind_text works though.

But why would you want to do that?

Igor Tandetnik

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

[sqlite] Getting up and running

[FrankLane]

Hi - I downloaded sqlite-amalgamation-3_6_20.zip and now I have a folder with
three files: sqlite3.c, sqlite3.h, and sqlite3ext.h. I have no idea what to
do next. I have a Mac 2.4 GHz Intel Core 2 Duo running Mac OS X 10.6.1. Can
anyone guide me to a page that steps me through an installation process?
Thanks, FL.
-- 
View this message in context: 
http://old.nabble.com/Getting-up-and-running-tp26575335p26575335.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] sqlite3 bind and insert double values

[Michael Lippautz]

Thanks for helping (all)!

Well, I compared a casual printf("%f\n",val) with the entry stored in
the database (as REAL). The entry is inserted into the db via
sqlite3_bind_double (prepare/reset/step)

Some examples:
fprintf / database (looked up via .dump on the table)
47.824669 / 47.824669167
47.824676 / 47.824672667
47.824676 / 47.824676

I thought that sqlite would store them as text anyways, but would
allow me to operate with the binding using double values.

Converting the double to a char* via snprintf and using
sqlite3_bind_text works though. I think that I am wrong concerning the
purpose of sqlite3_bind_double here.

--Michael

2009/11/30 Igor Tandetnik :
> Michael Lippautz wrote:
>> I am using sqlite_bind_double on a prepared statement (C API). The
>> insert completes, however, the value stored in the sqlite table is
>> different from the output of a casual printf("%f",..)
>
> How do you determine this? Have you retrieved the value back from the 
> database as a double, printed it the exact same way you did the original, and 
> found the results different? I'll be surprised if the data you get back is 
> not bit-for-bit identical to the data you put in.
>
> Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 bind and insert double values

[Igor Tandetnik]

Michael Lippautz wrote:
> I am using sqlite_bind_double on a prepared statement (C API). The
> insert completes, however, the value stored in the sqlite table is
> different from the output of a casual printf("%f",..)

How do you determine this? Have you retrieved the value back from the database 
as a double, printed it the exact same way you did the original, and found the 
results different? I'll be surprised if the data you get back is not 
bit-for-bit identical to the data you put in.

Igor Tandetnik


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

Re: [sqlite] sqlite3 bind and insert double values

[Simon Slavin]

On 30 Nov 2009, at 1:50pm, Michael Lippautz wrote:

> I am using sqlite_bind_double on a prepared statement (C API). The
> insert completes, however, the value stored in the sqlite table is
> different from the output of a casual printf("%f",..)

By 'different' do you mean that it is obviously a different number, or do you 
mean that it's pretty-much the same number, just shown in the same way ?  Could 
you give a couple of examples ?

> Am I wrong when assuming that they should be the same. (double values
> are gathered by a gps and are in range of %1.6f up to %3.9f

You could gather the number as a %3.9f, but your value is stored purely as a 
number -- no formatting at all.  You could get a %2.2f out again, as long as 
the two numbers are equal to one-another (or very nearly so).

If you want the numbers stored with formatting, define the columns as TEXT, and 
bind the values to the INSERT instruction as string values, not numbers.

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

Re: [sqlite] sqlite3 bind and insert double values

[Nick Shaw]

How different are they?  Could this be [unavoidable] binary floating
point storage limitations?

Nick.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Lippautz
Sent: 30 November 2009 13:50
To: sqlite-users@sqlite.org
Subject: [sqlite] sqlite3 bind and insert double values

Hej,

I am using sqlite_bind_double on a prepared statement (C API). The
insert completes, however, the value stored in the sqlite table is
different from the output of a casual printf("%f",..)

Am I wrong when assuming that they should be the same. (double values
are gathered by a gps and are in range of %1.6f up to %3.9f

Thanks in advance!
___
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

[sqlite] sqlite3 bind and insert double values

[Michael Lippautz]

Hej,

I am using sqlite_bind_double on a prepared statement (C API). The
insert completes, however, the value stored in the sqlite table is
different from the output of a casual printf("%f",..)

Am I wrong when assuming that they should be the same. (double values
are gathered by a gps and are in range of %1.6f up to %3.9f

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

[sqlite] Error message from sqlite3_tokenizer_module.xCreate?

[Ralf Junker]

I am passing various arguments to sqlite3_tokenizer_module.xCreate. In case 
they are invalid, I would like to return an explaining error message in 
addition to SQLITE_ERROR. I did not find a way to do this. Is it at all 
possible?

Thanks, Ralf

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

Re: [sqlite] sqlite3 is blocked by transaction when wanting to close the sqlite3 *

[Pavel Ivanov]

> Because there are several process who use the database. I have another
> question:Could I close the database of other process in main process?

Just use your favorite IPC mechanism and write your application so
that main process sends message to other process and when other
process receives it then it closes its database connection...

Pavel

On Sun, Nov 29, 2009 at 7:46 PM, liubin liu <7101...@sina.com> wrote:
>
> Thank you!
>
> I'm sorry for not showing clearly the environment is embedded linux on arm
> board.
>
> Because there are several process who use the database. I have another
> question:Could I close the database of other process in main process?
>
>
> Nick Shaw-3 wrote:
>>
>> By "other process" do you mean a separate DLL or similar?  You can't
>> free memory allocated in a DLL from an application, even when that
>> application has the DLL loaded - Windows will complain.  This could be
>> what's happening.
>>
>> Could you instead write the database close call within this other
>> process, and call it from the main process when you shut down?
>>
>> Nick.
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of liubin liu
>> Sent: 26 November 2009 02:29
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] sqlite3 is blocked by transaction when wanting to
>> close the sqlite3 *
>>
>>
>> My application includes a main process and some other processes. I open
>> the
>> database in other process, but at end I will close the database in main
>> process.
>>
>> The problem happens while I close the database. The main process is
>> blocked.
>> And I could see the journal file is still there, so I guess there are
>> still
>> some transactions.
>>
>> How resolve the problem?
>>
>> Thanks in advance!
>> --
>> View this message in context:
>> http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-wanting-to-
>> close-the-sqlite3-*-tp26523551p26523551.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>>
>> ___
>> 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
>>
>>
>
> --
> View this message in context: 
> http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-closing-the-sqlite3-*-tp26523551p26568098.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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

[sqlite] Error message from sqlite3_tokenizer_module.xCreate?

[Ralf Junker]

I am passing various arguments to sqlite3_tokenizer_module.xCreate. In case 
they are invalid, I would like to return an explaining error message in 
addition to SQLITE_ERROR. I did not find a way to do this. Is it at all 
possible?

Thanks, Ralf

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

[sqlite] Trigger-based PostgreSQL to SQLite online replication

[Alexey Pechnikov]

Hello!

For some reasons may be useful online replication from PostgreSQL database 
to SQLite database or databases. I write this as set of pltclu procedures for 
my PostgreSQL database. The code of example procedure and results see here:
http://geomapx.blogspot.com/2009/11/postgresql-to-sqlite-replication.html

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WHERE CLAUSE in UNICODE

[Simon Slavin]

On 30 Nov 2009, at 1:58am, Igor Tandetnik wrote:

> Note that Unicode collation is not as simple as you might think. Did you know 
> that in Estonian, 'y' sorts between 'i' and 'j'? Or that in German phonebook 
> sort, 'oe' sorts as if it were a single letter between 'o' and 'p'? 
> Basically, your simplistic approach would only work for plain unaccented 
> Latin letters and English collation rules.

I spent a lot of time annoyed about this, and ended up deciding that the only 
way to do Unicode sorting correctly is to store the language (or collation 
method) with each piece of Unicode text.  Of course, this still gives you the 
problem of working out which order two pieces of text go in if they are in two 
different languages.  Perhaps you also need a 'default language' marker for the 
entire column.

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