Re: [sqlite] unique with icu
Thanks a million Simon :-) On Sun, Oct 26, 2014 at 8:11 PM, Simon Slavin wrote: > > On 26 Oct 2014, at 6:00am, dd wrote: > > > Application using sqlite database without icu extension. I am planning to > > add icu extension. for schema, add new column and index with lower. > > > > Is it safe to add icu for existing db's? > > Yes. But once you've added it and used it there will be a problem if you > ever try to use the database without it. > > > Will it lead to any corruptions? > > No. > > 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] unique with icu
Thanks Richard/Joseph.
Application using sqlite database without icu extension. I am planning to
add icu extension. for schema, add new column and index with lower.
Is it safe to add icu for existing db's? Will it lead to any corruptions?
On Sun, Oct 26, 2014 at 1:22 AM, Joseph R. Justice
wrote:
> On Sat, Oct 25, 2014 at 3:44 PM, Richard Hipp wrote:
>
> > On Sat, Oct 25, 2014 at 7:09 AM, dd wrote:
> >
>
>
> > > Hi,
> > >
> > > icu enabled for sqlite. I didn't do any custom collations/like
> > operator.
> > >
> > > CREATE TABLE test(id integer primary key autoincrement, t text collate
> > > nocase, unique(t));
> > >
> > > Case 1: When I try to insert 'd' and 'D', throwing constraint
> violation.
> > > (SUCCESS)
> > > Case 2: When I try to insert 'ö' and 'Ö', sqlite inserted both
> > > sucessfully. (FAILED)
> > >
> > > I expect case 2 should throw constraint violation, but not. Am I
> > missing
> > > anything here?
> >
> > Please read https://www.sqlite.org/src/artifact/d9fbbad0c2f and
> especially
> > the part about ICU collating sequences. "NOCASE" is still the standard
> > ASCII-only collating sequence, even if you enable ICU.
> >
>
> I'm looking at that page now. (I'm not especially interested in this issue
> -- it's just a whim.)
>
>
>
> When I look at the following text from section 1.1:
>
> To utilise "general" case mapping, the upper() or lower() scalar
> functions are invoked with one argument:
>
> upper('ABC') -> 'abc'
> lower('abc') -> 'ABC'
>
> I wonder if the examples shown are reversed, and it should be
> upper('abc') -> 'ABC', et al.
>
>
>
> W.r.t. the original poster's question, given what DRH has said here, I
> wonder if they might have to introduce a third column to the table, say
> t_unique, where t_unique is defined to be the value of t after it has been
> passed through the upper() and/or lower() functions (as they are defined by
> 1.1), and in the definition of the table instead of unique(t) have
> unique(t_unique). Obviously this would bloat the size of the table in any
> real usage (since t and t_unique could potentially be quite long). And I
> recognize that such bloat may be unacceptable to the original poster.
>
> Perhaps a "good enough" solution would be to define t_unique as being a
> hash of the value of upper(t) (or lower(t)), and again have
> unique(t_unique). I recognize this could potentially lead to a false
> positive (two unrelated strings hashing to the same value and therefore
> colliding), but hopefully the chance of that occurring in real life would
> be acceptably small. Alternatively, define t_unique to be a concatenation
> of two or three hash values of upper(t), where each hash value is generated
> using a different hash function -- the chance of two unrelated strings
> hashing to the same value with two or three unrelated hash functions should
> be far, far smaller even than that of them hashing to the same value under
> only one function. (Pulling numbers out of thin air, if we say the chance
> of two unrelated strings hashing to the same value with any reasonable hash
> function is no more likely than 1x10^-10, then concatenating the value of
> three unrelated hash values should result in a collision no more often at
> worst than 1x10^-30 (assuming my math is correct), which is 20 orders of
> magnitude (or more!) smaller than the chance using just one hash function.)
> Of course, here we are probably going to use far more CPU, since we will
> be hashing strings at least once (and possibly two or three times,
> depending on the chosen implementation) and this is not necessarily a cheap
> thing to compute.
>
> Alternatively, and this would require programming / API changes to SQLite,
> perhaps a "nocase_icu" could be defined / created which, in the absence of
> ICU being enabled for sqlite, works identically to how "nocase" works now,
> but which when ICU is enabled works in the fashion expected by the original
> poster as to how they thought plain "nocase" would work with ICU enabled.
> (I assume that changing how "nocase" works when ICU is enabled is
> unacceptable since that would be a backwards-incompatible change, and it's
> possible there are programs existing which depend on the current behavior.)
>
>
>
> Hope this is of some use, interest. Thanks for your time.
>
>
>
> Joseph
> ___
> 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] unique with icu
any inputs. On Sat, Oct 25, 2014 at 3:09 PM, dd wrote: > Hi, > > icu enabled for sqlite. I didn't do any custom collations/like operator. > > CREATE TABLE test(id integer primary key autoincrement, t text collate > nocase, unique(t)); > > Case 1: When I try to insert 'd' and 'D', throwing constraint violation. > (SUCCESS) > Case 2: When I try to insert 'ö' and 'Ö', sqlite inserted both > sucessfully. (FAILED) > > I expect case 2 should throw constraint violation, but not. Am I missing > anything here? > > Thanks. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] unique with icu
Hi, icu enabled for sqlite. I didn't do any custom collations/like operator. CREATE TABLE test(id integer primary key autoincrement, t text collate nocase, unique(t)); Case 1: When I try to insert 'd' and 'D', throwing constraint violation. (SUCCESS) Case 2: When I try to insert 'ö' and 'Ö', sqlite inserted both sucessfully. (FAILED) I expect case 2 should throw constraint violation, but not. Am I missing anything here? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to enable icu for sqlite shell
Hi, I need to verify unicode stuff with shell. Is it possible? Tried with .load icu, thrown "icu.so cannot open...". Where can I find icu.so on sqlite.org? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unicode case insensitive
typo:
*I am summarizing options to support unicode* case-insensitive*:
On Sat, Oct 25, 2014 at 10:34 AM, dd wrote:
> I am summarizing options to support unicode case-sensitive:
>
> 1. Richard Hipp: icu ext
> 2. Aleksey Tulinov: https://bitbucket.org/alekseyt/nunicode#markdown-
> header-sqlite3-extension
> 3. Grey's suggestion: custom collation
>
> Please add in options list if I missed.
>
> I don't have experience in custom collations and icu extension. Needs
> to support Unicode case insensitive (collate nocase with uniquely indexed
> column) for database for all unicode charsets without storing locale.
>
> I would like to know what are the disadvantages/pitfalls with icu
> extension?
>
> Can somebody point which option is suitable for my requirement.
>
>
> On Fri, Oct 24, 2014 at 11:51 PM, James K. Lowden <
> jklow...@schemamania.org> wrote:
>
>> On Fri, 24 Oct 2014 21:44:50 +0400
>> dd wrote:
>>
>> > >>Convert everything to upper (or lower) case brute force.
>> >Sorry. I am not clear. Can you please elaborate this.
>>
>> The standard function tolower(3) is locale-dependent. If your locale is
>> set to match the data's single-byte encoding,
>>
>> tolower('Ö') == tolower('ö') .
>>
>> If you are using Unicode, you have towlower(3) as defined by C99. If
>> you're using utf-8, you'll want to call ivonv(3) first to convert the
>> string to an array of wint_t.
>>
>> Plug that into the xCompare function of a custom collation, and you
>> have your own case-insensitive matching capability.
>>
>> --jkl
>> ___
>> 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] unicode case insensitive
I am summarizing options to support unicode case-sensitive:
1. Richard Hipp: icu ext
2. Aleksey Tulinov: https://bitbucket.org/alekseyt/nunicode#markdown-
header-sqlite3-extension
3. Grey's suggestion: custom collation
Please add in options list if I missed.
I don't have experience in custom collations and icu extension. Needs
to support Unicode case insensitive (collate nocase with uniquely indexed
column) for database for all unicode charsets without storing locale.
I would like to know what are the disadvantages/pitfalls with icu
extension?
Can somebody point which option is suitable for my requirement.
On Fri, Oct 24, 2014 at 11:51 PM, James K. Lowden
wrote:
> On Fri, 24 Oct 2014 21:44:50 +0400
> dd wrote:
>
> > >>Convert everything to upper (or lower) case brute force.
> >Sorry. I am not clear. Can you please elaborate this.
>
> The standard function tolower(3) is locale-dependent. If your locale is
> set to match the data's single-byte encoding,
>
> tolower('Ö') == tolower('ö') .
>
> If you are using Unicode, you have towlower(3) as defined by C99. If
> you're using utf-8, you'll want to call ivonv(3) first to convert the
> string to an array of wint_t.
>
> Plug that into the xCompare function of a custom collation, and you
> have your own case-insensitive matching capability.
>
> --jkl
> ___
> 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] how to store latin strings with no casae
Thank you. On Fri, Oct 24, 2014 at 11:24 PM, Constantine Yannakopoulos < alfasud...@gmail.com> wrote: > On Fri, Oct 24, 2014 at 9:40 AM, dd wrote: > > > > > >>The SQLite source code includes an "ICU" extension that does these > > overloads. > > > > Will it solve for all charsets if ICU enabled? > > > > > Probably but I cannot tell for sure because I haven't used it. When my > company started using SQLite we didn't know about ICU extensions so we > developed our own. > ___ > 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] unicode case insensitive
Hi,
Any sample/open source avail to custom collation. Will it work for like
queries. Any performance degradation?
>>Convert everything to upper (or lower) case brute force.
Sorry. I am not clear. Can you please elaborate this.
Thanks.
On Fri, Oct 24, 2014 at 9:16 PM, Gerry Snyder
wrote:
> In a vaguely similar situation I wrote a custom collation that converted
> accented letters to their non-accented cousins. Since the conversion is on
> a case-by-case basis I also had to do a pre-screening that would show any
> non-ascii characters that I wasn't converting, so that I could add them to
> my collation.
>
> This is not quite what you want, since (I think) you want O and Ö and Ó to
> be distinct, but the same sort of technique should work for you. Convert
> everything to upper (or lower) case brute force. Perhaps tedious to set up,
> but straightforward.
>
> Gerry
>
>
>
> On 10/24/2014 9:54 AM, dd wrote:
>
>> Hi,
>>
>> ö and Ö same character but case different. I dont want to allow to insert
>> two entries for same data with different case. It works well with ascii
>> set. How to handle this? any inputs welcome.
>>
>> $./sqlite3 '/home//sqlite/test/a.db'
>> SQLite version 3.8.7 2014-10-17 11:24:17
>> Enter ".help" for usage hints.
>> sqlite> .fullschema
>> CREATE TABLE test(id integer primary key autoincrement, t text collate
>> nocase, unique(t));
>> /* No STAT tables available */
>> sqlite> insert into test(t) values('a');
>> sqlite> insert into test(t) values('A');
>> Error: UNIQUE constraint failed: test.t
>> sqlite> .headers on
>> sqlite> select * from test;
>> id|t
>> 1|a
>> sqlite> insert into test(t) values('ö');
>> sqlite> insert into test(t) values('Ö');//issue: allowed to insert.
>> Expects
>> constraint failed err. But, not.
>> sqlite> select * from test;
>> id|t
>> 1|a
>> 2|ö
>> 3|Ö
>> sqlite> .q
>>
>> Thanks,
>> dd
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] unicode case insensitive
Hi,
ö and Ö same character but case different. I dont want to allow to insert
two entries for same data with different case. It works well with ascii
set. How to handle this? any inputs welcome.
$./sqlite3 '/home//sqlite/test/a.db'
SQLite version 3.8.7 2014-10-17 11:24:17
Enter ".help" for usage hints.
sqlite> .fullschema
CREATE TABLE test(id integer primary key autoincrement, t text collate
nocase, unique(t));
/* No STAT tables available */
sqlite> insert into test(t) values('a');
sqlite> insert into test(t) values('A');
Error: UNIQUE constraint failed: test.t
sqlite> .headers on
sqlite> select * from test;
id|t
1|a
sqlite> insert into test(t) values('ö');
sqlite> insert into test(t) values('Ö');//issue: allowed to insert. Expects
constraint failed err. But, not.
sqlite> select * from test;
id|t
1|a
2|ö
3|Ö
sqlite> .q
Thanks,
dd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to store latin strings with no casae
Thanks for your response. https://www.sqlite.org/faq.html#q18 >>The SQLite source code includes an "ICU" extension that does these overloads. Will it solve for all charsets if ICU enabled? On Thu, Oct 23, 2014 at 6:28 PM, Constantine Yannakopoulos < alfasud...@gmail.com> wrote: > On Thu, Oct 23, 2014 at 2:47 PM, dd wrote: > > > Hi, > > > > database schema defined with collate nocase. It supports only for ascii > > (upper and lower). If I want to support db for other characters with > > nocase, what steps I need to consider during schema design. > > > > for ex: > > > > *À Á Â Ã Ä Å Æ = * > > > > * à á â ã ä å æ * > > > > you can write your own collations > <https://www.sqlite.org/c3ref/create_collation.html> to support custom > comparing and sorting of strings or any other data and add them to SQLite > <https://www.sqlite.org/c3ref/collation_needed.html>. > > There is a pitfall though: The LIKE operator will not honour your > collation > <http://www.sqlite.org/lang_expr.html#like>. You may choose to override it > <http://www.sqlite.org/lang_corefunc.html#like> by using the > sqlite3_create_function() api so that it does, but if you do you will lose > the LIKE optimization <http://www.sqlite.org/optoverview.html#like_opt>. > > -- > Constantine > ___ > 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] how to store latin strings with no casae
Hi, database schema defined with collate nocase. It supports only for ascii (upper and lower). If I want to support db for other characters with nocase, what steps I need to consider during schema design. for ex: *À Á Â Ã Ä Å Æ = * * à á â ã ä å æ * Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will collate binary supports like query
Thank you :-) On Tue, Sep 30, 2014 at 3:44 PM, Simon Slavin wrote: > > On 30 Sep 2014, at 12:32pm, dd wrote: > > > My db already there in production. I have to do it programmatically. > > > > Can it be done with any sqlite apis/pragmas. > > Sure. But you do need to drop the table at one point so nothing should be > trying to use it. > > Depending on how your indexes, triggers and foreign keys are defined you > can do something like > > CREATE TABLE newVersion ( here>) > INSERT INTO newVersion SELECT * FROM originalName > DROP originalName > ALTER TABLE newVersion RENAME TO originalName > > You may then have to recreate any indexes triggers and foreign keys. > > Again, don't forget to take a backup before messing with anything. > > 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] Will collate binary supports like query
My db already there in production. I have to do it programmatically. Can it be done with any sqlite apis/pragmas. On Tue, Sep 30, 2014 at 3:19 PM, Simon Slavin wrote: > > On 30 Sep 2014, at 12:07pm, dd wrote: > > > Hi Simon, > > > >>> However, frequent use of COLLATE often suggests that the original > schema > > was badly chosen. > > Any performance degradation? > > Having SQLite analyse the command, work out what 'COLLATE BINARY' means, > and making the change means that it will take a little longer to execute > the command. But that should just be a few milliseconds, not a long time. > > > Is it possible to change "collate nocase" to "collate binary" in schema > for > > already existing db? Any alternative solution for this? > > If you need to change your TABLE definitions a little but keep your data, > a good way to do it is to use the SQLite shell tool: > > <https://sqlite.org/cli.html> > > You can load up your database, then use the '.dump' command to dump it as > a series of SQL commands to a text file. Then you can use a text processor > to go into the text file and edit any of the commands. Then you can use > the shell tool to create a new database file and '.read' the new SQL > commands into it. > > Don't forget to take a backup before you start, just in case you mess up > and type the wrong filename. Like I've done once or twice. > > 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] Will collate binary supports like query
Hi Simon, >>However, frequent use of COLLATE often suggests that the original schema was badly chosen. Any performance degradation? Is it possible to change "collate nocase" to "collate binary" in schema for already existing db? Any alternative solution for this? Thanks. On Tue, Sep 30, 2014 at 2:09 PM, Simon Slavin wrote: > > On 30 Sep 2014, at 7:03am, dd wrote: > > > Needs to support case-sensitive for case-insensitive sqlite > > database field . To find exact case, COLLATE BINARY, works very well. > > > > > > > > What is the work around for like queries. Needs to case-sensitive for > > like queries > > <http://www.sqlite.org/lang_expr.html> > > "The LIKE operator can be made case sensitive using the > case_sensitive_like pragma." > > > and lessthan, greaterthan operators. > > <http://www.sqlite.org/lang_corefunc.html> > > "upper(X)", "lower(X)", but also see COLLATE operator as discussed below. > > > OR > > > > Is there anyway to change sqlite column's collating from NOCASE to > BINARY? > > <http://www.sqlite.org/datatype3.html#collation> > > The three collations available are BINARY, NOCASE and RTRIM. (When NOCASE > used with the higher Unicode characters you may have to think quite hard to > figure out what's going on.) > > You can define collation for each column when you define a table. This is > almost always all that's necessary: most of the time data in a database > seems to have an inherent collation that suits it. You can define > collation for each index element when you define an index (though you > probably shouldn't: you should get it right when you define the table). > You cannot change either of these without dropping and recreating the table > or index. > > You can use a COLLATE operator for each operand in any expression. For > instance ... > > SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x COLLATE RTRIM > > However, frequent use of COLLATE often suggests that the original schema > was badly chosen. The only justified use of a lot of COLLATEs I ever saw > was in a database which was designed to process a lot of text for someone > who was doing research into words and how they are used. > > 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] Will collate binary supports like query
@RSmith, yes. nice. Is it possible to change "collate nocase" to "collate binary" in schema for already existing db? On Tue, Sep 30, 2014 at 1:44 PM, RSmith wrote: > > On 2014/09/30 09:03, dd wrote: > >> I just got below pragma: >> pragma case_sensitive_like = true >> >> As an aside... The beauty of it being a Pragma is that you are not stuck > with a single method - you can switch it on and off at a whim... Maybe even > provide a GUI interface to switch modes. > ___ > 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] Will collate binary supports like query
I just got below pragma: pragma case_sensitive_like = true On Tue, Sep 30, 2014 at 10:03 AM, dd wrote: > Hi, > > Needs to support case-sensitive for case-insensitive sqlite > database field . To find exact case, COLLATE BINARY, works very well. > > > > What is the work around for like queries. Needs to case-sensitive for > like queries and lessthan, greaterthan operators. > > OR > > Is there anyway to change sqlite column's collating from NOCASE to > BINARY? > > > > sqlite> .schema > CREATE TABLE t (Id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT COLLATE > NOCASE); > > sqlite> select * from t; > Id|data > 1|abcd > 2|defg > 3|ABCD > 4|AbCd > 5|aBCD > 6|Abcd > 7|abcd > > sqlite> select * from t where (data = 'abcd' collate binary); > Id|data > 1|abcd > 7|abcd > > // Is it right way to use collate binary for '<' and '>' operations > sqlite> select * from t where data < 'a' collate binary; > Id|data > 3|ABCD > 4|AbCd > 6|Abcd > > sqlite> select * from t where data > 'a' collate binary; > Id|data > 1|abcd > 2|defg > 5|aBCD > 7|abcd > > > sqlite> select * from t where data like 'a%' collate binary; //not working > as expected. any alternative. > Id|data > 1|abcd > 3|ABCD > 4|AbCd > 5|aBCD > 6|Abcd > 7|abcd > > Thanks. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Will collate binary supports like query
Hi, Needs to support case-sensitive for case-insensitive sqlite database field . To find exact case, COLLATE BINARY, works very well. What is the work around for like queries. Needs to case-sensitive for like queries and lessthan, greaterthan operators. OR Is there anyway to change sqlite column's collating from NOCASE to BINARY? sqlite> .schema CREATE TABLE t (Id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT COLLATE NOCASE); sqlite> select * from t; Id|data 1|abcd 2|defg 3|ABCD 4|AbCd 5|aBCD 6|Abcd 7|abcd sqlite> select * from t where (data = 'abcd' collate binary); Id|data 1|abcd 7|abcd // Is it right way to use collate binary for '<' and '>' operations sqlite> select * from t where data < 'a' collate binary; Id|data 3|ABCD 4|AbCd 6|Abcd sqlite> select * from t where data > 'a' collate binary; Id|data 1|abcd 2|defg 5|aBCD 7|abcd sqlite> select * from t where data like 'a%' collate binary; //not working as expected. any alternative. Id|data 1|abcd 3|ABCD 4|AbCd 5|aBCD 6|Abcd 7|abcd Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to control cpu usage while transaction in progress
Hi all, Executing like query in a transaction. Query works with multiple tables and table has 1 million records on desktop clients. CPU goes high when transaction in progress. Is there any way to control the CPU without adding sleep statements? Is there any alternative solution for like queries? (for ex: delete * from emp where empname like "%a") Thanks, dd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Understanding Sqlite
Dear Author/All, There are many things needs to understand from sqlite apart from RDBMS concepts. For example, pragmas, database connection in different scenarios, IPC, virtual tables..etc. Do sqlite team has any plan to start online university for training and issuing certificates? If already there, please let me know. OR What is/are the best practice(s) to become master in sqlite in short period of time for new developers (i mean, new to sqlite not for programming)? Regards, dd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] detach failed with error code 1
I have set busy time out for 5 seconds and given different names for every attach and detaching database. still, this error is not getting fixed. prepare, step and finalize are not throwing any errors. What is the alternative solution for attach and detaching database? Thanks in advance. On Sun, Apr 20, 2014 at 9:16 PM, Simon Slavin wrote: > > On 20 Apr 2014, at 12:58pm, dd wrote: > > > Given different database name for in-memory database for every > > iteration(looped for 1000 times). Still, it's throwing Database Locked at > > least once on Windows, not on Mac/Linux. Is there any way to track this > > issue? I am using 3.7.11. Any ideas? > > Technically this should not solve the problem, but I'm curious to know. > > Have you set a timeout value ? If not, please set one to at least five > seconds using either of these: > > <http://www.sqlite.org/c3ref/busy_timeout.html> > <http://www.sqlite.org/draft/pragma.html#pragma_busy_timeout> > > I'm curious to know whether (a) this solves the problem or (b) it makes > your loop of 1000 take longer. > > However, we do seem to still think that the error is in your code > somewhere rather than in the SQLite library. Are you checking the result > returned by all calls for errors, rather than checking just the result > returned by your DETACH ? Check the result returned by every single > _prepare(), _step() and _finalize() and other sqlite_ call in your code. > > 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] detach failed with error code 1
Given different database name for in-memory database for every iteration(looped for 1000 times). Still, it's throwing Database Locked at least once on Windows, not on Mac/Linux. Is there any way to track this issue? I am using 3.7.11. Any ideas? On Thu, Apr 17, 2014 at 5:11 PM, Richard Hipp wrote: > On Thu, Apr 17, 2014 at 9:08 AM, dd wrote: > > > Dear Richard, > > > > This is great api. > > > > output: > > > > (1) statement aborts at 5: [DETACH my_in_memory_db;] database > > my_in_memory_db is locked > > > > The DETACH failed because you have unfinalized statements using the > attached database and you cannot detach a database file out from under a > statement that is using that database file. Because the DETACH failed, > subsequent ATTACH statements cannot succeed because there would be a name > conflict. > > > > > (1) statement aborts at 5: [ATTACH DATABASE '/full/path/info.db' AS > > my_in_memory_db;] database my_in_memory_db is > > already in use > > (1) statement aborts at 5: [ATTACH DATABASE '/full/path/info.db' AS > > my_in_memory_db;] database my_in_memory_db is already in use > > (1) statement aborts at 5: [ATTACH DATABASE '/full/path/info.db' AS > > my_in_memory_db;] database my_in_memory_db is already in use > > (1) statement aborts at 5: [ATTACH DATABASE '/full/path/info.db' AS > > my_in_memory_db;] database my_in_memory_db is already in use > > > > there is a detach for every attach. What could be the solution for > this? > > > > Thanks, > > dd > > > > > > > > > > On Thu, Apr 17, 2014 at 2:58 PM, Richard Hipp wrote: > > > > > Please turn on error logging (http://www.sqlite.org/errlog.html) and > > > report > > > back what error messages you are seeing. > > > > > > > > > On Thu, Apr 17, 2014 at 6:47 AM, dd wrote: > > > > > > > Hi All, > > > > > > > > 1. prepare, step, then finalize: Attach DATABASE '/full/path' as > > > > 'my_in_memory_db'; > > > > 2. prepare, step, then finalize: Delete from > my_in_memory_db.table_1 > > > > where primary_key = 'value'; > > > > 3. prepare, step, then finalize: DETACH my_in_memory_db; > > > > > > > > Executed above three queries in loop for 100 times for empty > database > > > > (/full/path). It's throwing sqlite error 1 at some random iteration. > Is > > > it > > > > correct way to implement attach and detach dbs? > > > > > > > > Thanks, > > > > dd. > > > > ___ > > > > sqlite-users mailing list > > > > sqlite-users@sqlite.org > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > > > > > -- > > > D. Richard Hipp > > > d...@sqlite.org > > > ___ > > > 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 > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] detach failed with error code 1
Dear Richard, This is great api. output: (1) statement aborts at 5: [DETACH my_in_memory_db;] database my_in_memory_db is locked (1) statement aborts at 5: [ATTACH DATABASE '/full/path/info.db' AS my_in_memory_db;] database my_in_memory_db is already in use (1) statement aborts at 5: [ATTACH DATABASE '/full/path/info.db' AS my_in_memory_db;] database my_in_memory_db is already in use (1) statement aborts at 5: [ATTACH DATABASE '/full/path/info.db' AS my_in_memory_db;] database my_in_memory_db is already in use (1) statement aborts at 5: [ATTACH DATABASE '/full/path/info.db' AS my_in_memory_db;] database my_in_memory_db is already in use there is a detach for every attach. What could be the solution for this? Thanks, dd On Thu, Apr 17, 2014 at 2:58 PM, Richard Hipp wrote: > Please turn on error logging (http://www.sqlite.org/errlog.html) and > report > back what error messages you are seeing. > > > On Thu, Apr 17, 2014 at 6:47 AM, dd wrote: > > > Hi All, > > > > 1. prepare, step, then finalize: Attach DATABASE '/full/path' as > > 'my_in_memory_db'; > > 2. prepare, step, then finalize: Delete from my_in_memory_db.table_1 > > where primary_key = 'value'; > > 3. prepare, step, then finalize: DETACH my_in_memory_db; > > > > Executed above three queries in loop for 100 times for empty database > > (/full/path). It's throwing sqlite error 1 at some random iteration. Is > it > > correct way to implement attach and detach dbs? > > > > Thanks, > > dd. > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] detach failed with error code 1
Hi All, 1. prepare, step, then finalize: Attach DATABASE '/full/path' as 'my_in_memory_db'; 2. prepare, step, then finalize: Delete from my_in_memory_db.table_1 where primary_key = 'value'; 3. prepare, step, then finalize: DETACH my_in_memory_db; Executed above three queries in loop for 100 times for empty database (/full/path). It's throwing sqlite error 1 at some random iteration. Is it correct way to implement attach and detach dbs? Thanks, dd. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improving Bulk Insert Speed (C/C++)
Did you see any improvement if application runs with high priority(nice)? (CPU and I/O) On Thu, Apr 3, 2014 at 12:56 AM, Kevin Xu wrote: > The app seems to use between 60-80% CPU while it is running (from Activity > Monitor) while disk use (using sudo iotop -P on OSX) seem to suggest about > 20%-30% I/O use. (spikes to over 30million% every 10s or so, might be when > sqlite decides to page out) > > Clearly my computations on the data are not complex enough to max out the > processor (the bulk inserts, at least, is single threaded) while iotop > results suggests I/O isn't maxed out either, which is why I suspect > performance improvements are still possible. > > On the other hand, it is difficult for anyone to suggest more ideas > without seeing the actual source code (professor wants it private for now), > so I will try coding up the virtual table, and pure binary file dump (as a > baseline) to see if I discover any more issues. > > Kevin Xu > > > On Apr 2, 2014, at 1:28 PM, Keith Medcalf wrote: > > > On Tue, 1 Apr 2014 20:58:14 -0700 > > Kevin Xu wrote: > > > >> I am trying to see if it is possible to achieve even higher > >> throughput - my professor was adamant that he managed > >> to insert 3 million rows in 7 seconds (which works out to > >> over 420K inserts per second) though he could not find > >> his code that did it or records of his insertions. > > > >> When I profiled the application (using Instruments), and after > >> inverting the call tree, the time spent within the program is > >> broken down as follows: > > > >> 2170ms 15.1% - sqlite3VdbeExec -> sqlite3_step -> insert function > >> 2142ms 14.9% - pwrite -> unixWrite -> pager_write/pager_write_pagelist > >> 1925ms 14.9% - std::string::insert -> boost::spirit > >> 539ms3.7% - pack (my compression function) > > > > A question that nobody seems to have bothered to ask, and that will > > entirely direct your solution search: > > > > Is the limit I/O or CPU? > > > > That is, which has hit 100% usage, CPU or I/O? If is is I/O then you > > might want to look for consumers of I/O. The best way to make I/O go > > faster is not to do it. > > > > If the limit is CPU, then you need to devise a way to consume less CPU > > (or get a faster CPU) or to get more processors and parallelize your > > processing. > > > > Secondly, what is the headroom you have available? For example if you > > are using 100% CPU and 97% I/O, then you are damn close to balanced and > > the additional couple of K per second you can gain in I/O is unlikely > > to make any significant difference, and will likely end up being the > > bottleneck even if you spend a couple of million dollars on CPU and > > MEMORY (to drive CPU usage down to 0.0001% but peg I/O to 100%). > > > > If the problem is CPU, then the first place to attack is boost:spirit > > (or use multiprocessing) -- after checking to ensure that you I/O > > system is properly offloaded from the main CPU and not consuming spin > > cycles while doing I/O, of course. > > > > If neither I/O nor CPU is pegged at 100% then you have a crappy > > scheduler or simply insufficient overlap between I/O and compute, and > > you need to fix this first. > > ___ > > 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Noticed crash on windows
Thank you all for your inputs. Let me check. On Wed, Mar 26, 2014 at 2:05 PM, Oliver Schneider wrote: > On 2014-03-25 18:52, Larry Brasfield wrote: >> Going in, it is best to not read too much into your code running >> "pretty well" on a Unix platform. Have you run your code with >> Valgrind (or equivalent) on that platform? If not, doing so may help >> you uncover a bug which affects behavior more badly on Windows with >> your present build. > DrMemory also exists for Windows and can be used there, it uses the > DynamoRIO instrumentation framework. I am not sure about the state of > affairs of AddressSanitizer with GCC on Windows, but that could be > another option with a smaller performance penalty. > > // Oliver > ___ > 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] Noticed crash on windows
Hi all, We have an application on unix platform with sqlite database which is running from last 2 years. pretty well. We recently ported on Windows too. Crashed at inserting record in a small database(less than 20 records, record has 10 columns). It's not reproducible. call stack: ntdll!RtlEnterCriticalSection+0x12 !winMutexEnter+0x13 !sqlite3_mutex_enter+0x19 !sqlite3_step+0x5e Is it known issue with 3.7.11 on Windows. Any idea? Thanks, dd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integrity check
Applied encryption on top of sqlite. Now, I suspect on encryption. Thanks for prompt response. On Thu, Jan 9, 2014 at 5:38 PM, Richard Hipp wrote: > On Thu, Jan 9, 2014 at 8:29 AM, dd wrote: > >> Hi all, >> >> Executed integrity check for database before application starts. >> Sometimes, it takes 1 minute. Other times, it finishes within 2 >> seconds. How integrity check works? can somebody explain why it takes >> less time. >> > > PRAGMA integrity_check is suppose to do exactly the same thing every time > it is run. I don't know why you are seeing a 30x timing difference. Can > you provide us with a test case? > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] integrity check
Hi all, Executed integrity check for database before application starts. Sometimes, it takes 1 minute. Other times, it finishes within 2 seconds. How integrity check works? can somebody explain why it takes less time. Thanks, dd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] executing queries on normalized database
Got it. Can I conclude this way: Foreign keys works pretty well when application deals with parent keys only. But, application may need to execute more queries when dealing with child key/tables. Is it? Thanks, dd On Mon, Nov 11, 2013 at 5:35 PM, Simon Slavin wrote: > > On 11 Nov 2013, at 12:38pm, dd wrote: > >> Scenario: >> >> sqlite> pragma foreign_keys = on; >> sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY >> AUTOINCREMENT, artistname TEXT); >> sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY >> AUTOINCREMENT, trackname TEXT, trackartist INTEGER REFERENCES >> artist(artistid) ON UPDATE CASCADE ON DELETE CASCADE); >> >> for ex: Five tracks are pointing to one artist. it needs to delete >> track by track. For first track to four tracks, it should not delete >> artist information from artist table. For fifth track, it should >> delete track information along with artist information. Is it possible >> to do with single query? > > You can't do this with foreign keys. You could do it with triggers: when you > delete a track, check to see whether that artist still has any tracks, and if > not, delete the artist. > > But you probably shouldn't. For example, in my own database an artist has > both tracks and albums. Even if I delete the last track by an artist, they > may still have albums under their name. Think of, for example, the Jools > Holland album where every track is a collaboration between him and someone > else. And also, even if I don't have any music by a particular artist, the > artists still exists. > > So I would do clearing up of artists in a separate part of the system, > perhaps a monthly maintenance task, rather than having it happen > automatically. > > 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] executing queries on normalized database
I understand parent table to child table modifications with ON UPDATE/ON DELETE CASCADS. It's very good. Now, I stuck with child to parent tables query optimization after normalization. Scenario: sqlite> pragma foreign_keys = on; sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY AUTOINCREMENT, artistname TEXT); sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY AUTOINCREMENT, trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE ON DELETE CASCADE); for ex: Five tracks are pointing to one artist. it needs to delete track by track. For first track to four tracks, it should not delete artist information from artist table. For fifth track, it should delete track information along with artist information. Is it possible to do with single query? On Mon, Nov 11, 2013 at 3:08 PM, dd wrote: > Hi, > > Thanks Simon. > > sqlite> pragma foreign_keys = on; > sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY > AUTOINCREMENT, artistname TEXT); > sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY > AUTOINCREMENT, trackname TEXT, trackartist INTEGER REFERENCES > artist(artistid) ON UPDATE CASCADE ON DELETE CASCADE); > > How do I insert trackname as "That's Amore" and artistname as "Dean > Martin" with single query in artist and track tables? > > dd > > > > > On Sun, Nov 10, 2013 at 6:45 PM, Simon Slavin wrote: >> >> On 10 Nov 2013, at 10:54am, dd wrote: >> >>> I have two tables in my database. >>> >>> After applying normalization, there are twelve tables with foreign >>> key support. >>> >>> For insert/delete operations, it has to execute twelve queries >>> instead of two. Is it recommended way? >> >> You should not have to write twelve queries in your own programming. If you >> have set up your FOREIGN KEYs correctly, SQLite should be doing that for >> you. The foreign keys facility should have allowed you to move some of your >> business model from your own programming into SQLite, which should be >> dealing with it in one place in a consistent way. >> >>> In delete case, do always need to check in parent table whether >>> child key refers to any other rows in parent table? >> >> Again, if you have set up your foreign keys correctly, SQLite should be >> doing the checking or deleting for you. Whether SQLite will prevent you >> from deleting, or delete rows from other tables, depends which of these >> actions you have set up: >> >> <http://www.sqlite.org/foreignkeys.html#fk_actions> >> >> 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] executing queries on normalized database
Hi, Thanks Simon. sqlite> pragma foreign_keys = on; sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY AUTOINCREMENT, artistname TEXT); sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY AUTOINCREMENT, trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE ON DELETE CASCADE); How do I insert trackname as "That's Amore" and artistname as "Dean Martin" with single query in artist and track tables? dd On Sun, Nov 10, 2013 at 6:45 PM, Simon Slavin wrote: > > On 10 Nov 2013, at 10:54am, dd wrote: > >> I have two tables in my database. >> >> After applying normalization, there are twelve tables with foreign >> key support. >> >> For insert/delete operations, it has to execute twelve queries >> instead of two. Is it recommended way? > > You should not have to write twelve queries in your own programming. If you > have set up your FOREIGN KEYs correctly, SQLite should be doing that for you. > The foreign keys facility should have allowed you to move some of your > business model from your own programming into SQLite, which should be dealing > with it in one place in a consistent way. > >> In delete case, do always need to check in parent table whether >> child key refers to any other rows in parent table? > > Again, if you have set up your foreign keys correctly, SQLite should be doing > the checking or deleting for you. Whether SQLite will prevent you from > deleting, or delete rows from other tables, depends which of these actions > you have set up: > > <http://www.sqlite.org/foreignkeys.html#fk_actions> > > 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
[sqlite] executing queries on normalized database
Hi, I have two tables in my database. After applying normalization, there are twelve tables with foreign key support. For insert/delete operations, it has to execute twelve queries instead of two. Is it recommended way? In delete case, do always need to check in parent table whether child key refers to any other rows in parent table? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need suggestion for database scheama
Hi James K. Lowden, Sorry. My backup application pulls tracks from albums and upload to server. Here, I need to store these Albums and Tracks information into database. From my application point of view, Every Track is a file.File may be track. When same track avail with different albums, then database can have single file reference. For ex: Track 'abc' avail in two albums. I can avoid duplicate row for the same file in AllFiles (Table). Any suggestions? I considered 'employee' table reference. Thats why, I made singular. It makes sense for me to remove 'Tbl' and add plural for track/album. Thanks for suggestions. dd On Fri, Nov 8, 2013 at 7:47 AM, James K. Lowden wrote: > On Thu, 7 Nov 2013 14:50:44 +0400 > dd wrote: > >> I am working on sqlite database schema for Music/Track files. I am >> posting few tables schema here. >> >> CREATE TABLE if not exists AllFilesTbl (Id INTEGER PRIMARY KEY >> AUTOINCREMENT, file_path TEXT NOT NULL, file_type INTEGER NOT NULL, >> UNIQUE(file_path)); >> >> CREATE TABLE if not exists AlbumTbl (AId INTEGER PRIMARY KEY >> AUTOINCREMENT, AlbumId TEXT NOT NULL, AlbumName TEXT NOT NULL, >> UNIQUE(AlbumId)); >> >> CREATE TABLE if not exists TrackTbl (TId INTEGER PRIMARY KEY >> AUTOINCREMENT, TrackId TEXT NOT NULL, AlbumId INTEGER NOT NULL, Title >> TEXT NOT NULL, UNIQUE(TrackId, AlbumId), FOREIGN KEY(AlbumId) >> REFERENCES Album(AId)); >> >> CREATE TABLE if not exists FileTrackTbl (FId INTEGER NOT NULL, TId >> INTEGER NOT NULL, UNIQUE(FId, TId), FOREIGN KEY(FId) REFERENCES >> AllFiles(Id), FOREIGN KEY(TId) REFERENCES Track(TId)); > > You might guess from my email domain name that I take an interest in > posts like yours. And it's pretty good first cut, no pun intended. ;-) > > As I read the above, we have > > Tracks <-N--1- Albums > Files <-N--M-> Tracks > > That is, > > An Album has many Tracks > A File may hold many Tracks, and >a Track may be part of many Files > > The latter statement looks specious. I would expect the relationship > to be > > Files -1--N-> Tracks <-M--1- Albums > > meaning that every Track would have a foreign key to its File and Album. > I would also guess the Track needs some further information -- offset or > index number or something -- in order to locate the Track in the File. > Similarly you might want to capture the Track's ordinal position on the > Album. ISTM likely that at least sometimes the order of the music in > the file will differ from the "official" information you'd find on the > cover, back when albums had covers. > > I recommend you use the natural keys unless they present a problem. I > might use an ID for a Files and Albums, because the names are long > and might not be definitive: you might want to change the filename > and/or the Album name, and there's no particular reason you'd want to > update Tracks to match. But the Tracks table doesn't need a surrogate > key: a Track's file and intrafile location specify it uniquely. > > Not that you asked, but I also suggest you consider dropping the "Tbl" > from the table names. Noting that at table is a table in its name is > like calling every file "data". It makes it harder to read and conveys > no information. I myself prefer plurals for table names (files, > tracks, albums) because each *row* represents the singular -- a file, a > track, an album -- and tables as we know are made of rows. But some > people find that pedantic, and they're not wrong. > > --jkl > > ___ > 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] tools list from sqlite.org
Hi, I used to include sqlite3.h/c files (from amalgamation) in application. I would like to know about tools from sqlite.org. What are all the tools available from sqlite.org? Where can I find the list of tools/utilities? any document? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need suggestion for database scheama
It has to work with multiple devices in future. This is valid point for me. Thanks. On Thu, Nov 7, 2013 at 3:42 PM, Stephan Beal wrote: > On Thu, Nov 7, 2013 at 12:15 PM, dd wrote: > >> Thanks for pointing multimedia id, Stephan Beal. I missed it. I will >> ad this to my schema. >> > > If you're only storing the list for local use on one machine, adding the > media ID is almost certainly overkill, but i found it useful for my > lots-of-external-drives collection, and it works regardless of the type of > media. e.g. i also had self-burned DVDs in the collection. Instead of > writing down what was on each DVD, i simply wrote its label/ID on the front > of it. Back in the 80's and early 90's applications for indexing floppy > disk collections using a similar approach was relatively common. > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ > http://gplus.to/sgbeal > "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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need suggestion for database scheama
Thanks for pointing multimedia id, Stephan Beal. I missed it. I will ad this to my schema. On Thu, Nov 7, 2013 at 3:07 PM, Stephan Beal wrote: > On Thu, Nov 7, 2013 at 11:50 AM, dd wrote: > >> CREATE TABLE if not exists AllFilesTbl (Id INTEGER PRIMARY KEY >> AUTOINCREMENT, file_path TEXT NOT NULL, file_type INTEGER NOT NULL, >> UNIQUE(file_path)); >> > > Some years ago i worked on something similar to keep track of my multimedia > collection and i added one layer of abstraction to this table: instead of > storing just a file path, i stored a media ID and a path, with the media ID > effectively defining the root path. e.g. let's say i've got 5 external hard > drives, each of them with a unique label. Mine were called > MM--, e.g. MM-500-00, MM-500-01, etc. > (MM==MultiMedia). The file path is then relative to wherever that media > (device) is mounted. In my case i stored the media id as a simple string, > but arguably more correct would be to store it as an integer and then have > a separate lookup table mapping id==>name. A view could then easily > translate that into a more human-readable form. > > Was it worth the effort? In my case it was because my media was scattered > around many external drives and this allowed me to quickly determine which > drive. But for simple use cases it very possibly isn't worth the extra > effort. Unfortunately, i have long since lost all that code (it was > implemented in JS, using a SpiderMonkey binding for sqlite), so i don't > have it to share with you, but the idea is simple enough that it doesn't > really need a demonstration. > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ > http://gplus.to/sgbeal > "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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Need suggestion for database scheama
Hi All, I am working on sqlite database schema for Music/Track files. I am posting few tables schema here. CREATE TABLE if not exists AllFilesTbl (Id INTEGER PRIMARY KEY AUTOINCREMENT, file_path TEXT NOT NULL, file_type INTEGER NOT NULL, UNIQUE(file_path)); CREATE TABLE if not exists AlbumTbl (AId INTEGER PRIMARY KEY AUTOINCREMENT, AlbumId TEXT NOT NULL, AlbumName TEXT NOT NULL, UNIQUE(AlbumId)); CREATE TABLE if not exists TrackTbl (TId INTEGER PRIMARY KEY AUTOINCREMENT, TrackId TEXT NOT NULL, AlbumId INTEGER NOT NULL, Title TEXT NOT NULL, UNIQUE(TrackId, AlbumId), FOREIGN KEY(AlbumId) REFERENCES Album(AId)); CREATE TABLE if not exists FileTrackTbl (FId INTEGER NOT NULL, TId INTEGER NOT NULL, UNIQUE(FId, TId), FOREIGN KEY(FId) REFERENCES AllFiles(Id), FOREIGN KEY(TId) REFERENCES Track(TId)); Can I achieve normalization by using above 4 tables to store music files and albums in database? Any suggestions/improvements are welcome. Thanks in advance. dd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] int stored as a string
Thanks Igor. It's text. I ran below to query: SELECT typeof(emp_id), typeof(emp_mngr_id) FROM employee; typeof(emp_id) | typeof(emp_mngr_id) text text text text ... text text On Thu, Oct 24, 2013 at 5:55 PM, Igor Tandetnik wrote: > On 10/24/2013 9:46 AM, dd wrote: >> >> Sorry Igor. >> >> For ex, Employee has below columns: >> >> column details: >> 1. seq_id as integer prmary key, >> 2. emp_id as STRING >> 3. emp_mngr_id as STRING >> 4. emp_id is UNIQUE >> >> Programmatically, application converts integer to string for emp_id >> and emp_mngr_id columns to insert into Employee table. >> >> I would like to know about how sqlite maintains emp_id column. It >> always contains integers in string format. > > > The type "STRING" has no special meaning to SQLite, therefore, the columns > have NUMERIC affinity, which is the default. This means that, upon > insertion, the value is converted to a number if it looks like one. Thus, I > predict that the values are actually stored as integers. For details, see > http://sqlite.org/datatype3.html > > But again, you don't need to guess. Run the query I suggested earlier, and > you'll know for sure. > > -- > Igor Tandetnik > > ___ > 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] int stored as a string
Sorry Igor. For ex, Employee has below columns: column details: 1. seq_id as integer prmary key, 2. emp_id as STRING 3. emp_mngr_id as STRING 4. emp_id is UNIQUE Programmatically, application converts integer to string for emp_id and emp_mngr_id columns to insert into Employee table. I would like to know about how sqlite maintains emp_id column. It always contains integers in string format. On Thu, Oct 24, 2013 at 4:49 PM, Igor Tandetnik wrote: > On 10/24/2013 8:39 AM, dd wrote: >> >>One of the column stores 'long long int' as string in database in >> production. Internally, sqlite maintains it as a integer or text? > > > I'm not sure I understand the question. Didn't you just say "stored as > string"? What do you mean by that, if not "maintained as text"? What do you > believe is the difference between the two? > > Anyway, run this query and see for yourself: > > select typeof(ColumnYouWantToKnowAbout), count(*) from > TableYouWantToKnowAbout group by 1; > > >> If Text, is there any performance overhead if I write a query based >> on this column? > > > If it requires text to integer conversion, then most likely there is some. > > >> Can I get better performance If I write a query based on integer >> column(with new schema or new column) rather than existing column? > > > Quite possibly. Why don't you try that? > -- > Igor Tandetnik > > ___ > 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] int stored as a string
Hi, One of the column stores 'long long int' as string in database in production. Internally, sqlite maintains it as a integer or text? If Text, is there any performance overhead if I write a query based on this column? Can I get better performance If I write a query based on integer column(with new schema or new column) rather than existing column? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Any tool to create erd from sqlite database?
I am looking for tool which generates er diagrams from existing database. Any suggetions? Thanks in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to encrypt sqlite db
Thank you for your quick response. I am looking for freeware. If freeware not available, I have to implement encryption support for sqlite on winrt. What is the procedure to implement encryption support on winrt? Thanks, dd On Sat, Aug 31, 2013 at 6:34 PM, Stephan Beal wrote: > On Sat, Aug 31, 2013 at 2:59 PM, Mohit Sindhwani wrote: > > > Adding on to Paolo's answer, see this: http://www.hwaci.com/sw/** > > sqlite/prosupport.html <http://www.hwaci.com/sw/sqlite/prosupport.html> > > See SEE and CEROD on that page. > > > > @devs: minor typo on that page: > > "The SQLite software free and it works great." > > missing "is" > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ > http://gplus.to/sgbeal > ___ > 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] to encrypt sqlite db
Hi All, I have to encrypt sqlite database on winrt. What are all the necessary steps to do to encrypt sqlite database? Thanks in advance. Regards, dd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database design question
I got it. Thank you RSmith and Simon Slavin. On Wed, Jun 12, 2013 at 2:54 PM, Simon Slavin wrote: > > On 12 Jun 2013, at 11:14am, dd wrote: > > > Yes Simon. I am looking for it. ON DELETE RESTRICT. > > > > I got the answer. > > > > Should I enable foreign key support to use on delete restrict?( > > http://www.sqlite.org/foreignkeys.html) > > I think that that fits with your earlier description: it will make SQLite > refuse to delete an author if they have books in the database. > > > I have x databases without enabling foreign key support. Can I enable > > foreign key support for x+1 database only? > > Turning on FOREIGN KEY support on a database that has no foreign keys does > no harm. It might slow some things down a millisecond perhaps. > > However, if you have separate applications for separate databases then you > can turn on FOREIGN KEY support just in your 'books’ application. In that > application, just execute the SQL command > > PRAGMA foreign_keys = ON > > before you open the database file. Then everything will work as expected. > > You must, of course, be using SQLite version 3.6.19 or later. > > 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] database design question
Yes Simon. I am looking for it. ON DELETE RESTRICT. I got the answer. Should I enable foreign key support to use on delete restrict?( http://www.sqlite.org/foreignkeys.html) I have x databases without enabling foreign key support. Can I enable foreign key support for x+1 database only? On Wed, Jun 12, 2013 at 2:01 PM, Simon Slavin wrote: > > On 12 Jun 2013, at 9:49am, dd wrote: > > >>> Book titles are not unique. > > I agree. 2. Books : columns(BookId_primarykey, > > Book_id_in_string_format_like_guid) > > I’m not sure why you would need a GUID. You can store > > 2. Books : columns(BookId_primarykey, Title) > > Just don’t put a restriction on it that the title must be unique. The > BookId is unique and that is enough. > > > > >>> Why is there a problem when there is an author without any books? > > No problem. But, my app need to delete author record from author > > table when author doesn't have any books. How to handle this? (I can > verify > > in author table, whether this author belongs to any other book when book > > deletion. If no, delete from author table. I am not happy with this > > solution ) > > The normal way to do this in a SQLite database would be to use a FOREIGN > KEY relationship: > > <http://www.sqlite.org/foreignkeys.html> > > You would have something like this: > > 1. Authors: columns(AuthorId, Name, SSN) > 2. Books : columns(BookId, Title, AuthorId, > FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId)) > > You could set the database up in two ways: > > (A) ON DELETE CASCADE would allow you to delete an author with books, and > would delete their books too. > (B) ON DELETE RESTRICT would make SQLite issue an error message if you > tried to delete an author who still had books in the Books table. > > SQLite will do this itself automatically. You do not need to write your > own code to look at the other table. > > 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] database design question
Thanks for your response. It will delete from Author_Books when book deleted. I am trying minimize queries on Authors table. For every deletion of record from Books, it needs to check in Authors table. One extra query execution is there. I am trying minimize that. On Wed, Jun 12, 2013 at 1:00 PM, Clemens Ladisch wrote: > dd wrote: > > my app need to delete author record from author table when author > > doesn't have any books. How to handle this? (I can verify in author > > table, whether this author belongs to any other book when book > > deletion. > > You mean the Author_Books table. > > > If no, delete from author table. > > That's how it's done. > > > I am not happy with this solution > > Why not? > > > Regards, > Clemens > ___ > 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] database design question
>>Book titles are not unique. I agree. 2. Books : columns(BookId_primarykey, Book_id_in_string_format_like_guid) >>Why is there a problem when there is an author without any books? No problem. But, my app need to delete author record from author table when author doesn't have any books. How to handle this? (I can verify in author table, whether this author belongs to any other book when book deletion. If no, delete from author table. I am not happy with this solution ) On Wed, Jun 12, 2013 at 12:37 PM, Clemens Ladisch wrote: > dd wrote: > > I am working on sample database application. I want to store book names > > and authors. > > > > 1. Authors: columns(AuthorId_primarykey, Name, SSN) > > 2. Books : columns(BookId_primarykey, Title)//Title is unique > > Book titles are not unique. > > > 3. Author_Books: columns(AuthorId_primarykey, BookId_primarykey) > > > > I am facing an issue with deletion of records from Books table. One > > author may belongs to morethan one book. How to handle this scenario? Is > > there technique like shared_ptr. > > Why is there a problem when there is an author without any books? > > > Regards, > Clemens > ___ > 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] database design question
Hi All, I am working on sample database application. I want to store book names and authors. Tables: 1. Authors: columns(AuthorId_primarykey, Name, SSN) 2. Books : columns(BookId_primarykey, Title)//Title is unique 3. Author_Books: columns(AuthorId_primarykey, BookId_primarykey) Here, I am able to avoid rendandent data (author names). I am facing an issue with deletion of records from Books table. One author may belongs to morethan one book. How to handle this scenario? Is there technique like shared_ptr. Thanks in advance. Regards, d ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] hide sqlite database
I have backup of existing database. I just want to hide this backup database. On Thu, Mar 7, 2013 at 5:49 PM, Clemens Ladisch wrote: > dd wrote: > > I want to hide sqlite database file. > > If you hide it, SQLite will not be able to open it. > It would be easier to just delete the file. > > What do you actually want to achieve? > > > Regards, > Clemens > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] hide sqlite database
Hi all, Is there any flag to create sqlite database in hidden mode? (chmod, setfileattributes are os specific). I am looking for os independent. It runs on 3 major desktop oss. Thanks, dd. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Announcement of Copy - A cross platform syncing app, that uses SQLite as its backend
>>The integrity check just takes too long so we don't. pragma quick_check; doesn't take much tme. http://www.sqlite.org/pragma.html#pragma_quick_check On Sun, Mar 3, 2013 at 8:00 AM, Jason Dictos wrote: > >> . > > > > Thanks for the details. What programming language do you use? (Dropbox > > uses Python.) > > > > C++ > > >> Early on we figured out quite quickly not leverage the read/write > >> locking models of SQLite > > > > Do you use WAL mode? > > > > I played with it early on but I didn't know how stable the feature was and > didn't need any of its features at the time, so not at this time. > > > I'm also curious roughly what you do with your schema. It looks like the > > Dropbox on Android schema has elements of implementing a tree with > > everything in one big table and no triggers. > > > > We have a file table which is our representation of what the cloud > currently thinks of things. Then we diff what the filesystem thinks and > describe the differences to the cloud. Once the cloud accepts the event we > update our file table. We are careful with triggers and foreign keys since > SQLite slows to a crawl when we have those and decide to delete a few > thousand rows. > > > Do you use any of SQLite's extensibilty such as adding your own > collations > > or user defined functions. > > > > We did have some stored procedures early on but we soon found ways to use > the sql language a bit better, which is always a faster option and > guarantees use of indices in some cases. We still may have a couple I think > but they are not used very much. > > >> As it stands now, our customers periodically have random I/O errors > >> ... > > > > As you have large deployments, random stuff does happen. We have a web > > service with several requests per second from browsers all over the world > > and sadly have to use HTTP (cough *IE* cough) rather than the SSL we > > normally use. Even though TCP/IP is checksummed there are sporadic > > corruptions that come through (typically bit flips here and there). > > > > 8 years ago there were lots of CPU random errors: > > > > http://blogs.msdn.com/b/oldnewthing/archive/2005/04/12/407562.aspx > > > > Do you run an integrity check at startup on the database? I did so with > > BitPim, but we didn't have analytics so there was no idea how often > > corruption happened. > > > > The integrity check just takes too long so we don't. We are currently > brainstorming ways of detecting corruption quickly. > > > There has been an open feature request for a while to have data checksums > > to deal with the case that what SQLite thinks it wrote is not what is > > later returned: > > > > http://www.sqlite.org/src/tktview?name=72b01a982a > > > > Thanks for the info Roger > > Jason > > > -BEGIN PGP SIGNATURE- > > Version: GnuPG v1.4.11 (GNU/Linux) > > > > iEYEARECAAYFAlExZhYACgkQmOOfHg372QTLBgCgsbidy6oQfmAeS4OWq4OBSmFI > > zxEAn04lneghgvr+ww76AQWzycZ3x+Q0 > > =eya6 > > -END PGP SIGNATURE- > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > Copy, by Barracuda, helps you store, protect, and share all your amazing > things. Start today: www.copy.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
Re: [sqlite] like query
Igor/Clemen Ladisch, >>SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/z" I want to replace z with 10 character. But, it's failed. what is the correct decimal value for that? On Tue, Feb 26, 2013 at 6:18 PM, dd wrote: > 10 decimal value is 1114111. But, some chinese characters are greater > than this value. Is it correct character(10) to replace with z? > > Please correct me if I am doing wrong. > > > On Tue, Feb 26, 2013 at 5:58 PM, Igor Tandetnik wrote: > >> On 2/26/2013 8:31 AM, Clemens Ladisch wrote: >> >>> Igor Tandetnik wrote:> On 2/26/2013 2:39 AM, dd wrote: >>> >>>> SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND >>>>> "somedata/zzz" >>>>> >>>>> This database has unicode strings(chinese/japanese/...**etc strings). >>>>> can >>>>> you tell me which is the correct character to replace with z? >>>>> >>>> >>>> U+, of course. >>>> >>> >>> Unicode characters can have more than 16 bits, of course. >>> >> >> ... but SQLite orders them with simple memcmp (absent a custom >> collation), so 0x will still compare greater than any surrogate pair. >> >> If the database file uses UTF-8 encoding, and contains supplemental >> characters, then yes, a UTF-8 representation of U+10 would be prudent. >> -- >> Igor Tandetnik >> >> >> __**_ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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] like query
10 decimal value is 1114111. But, some chinese characters are greater than this value. Is it correct character(10) to replace with z? Please correct me if I am doing wrong. On Tue, Feb 26, 2013 at 5:58 PM, Igor Tandetnik wrote: > On 2/26/2013 8:31 AM, Clemens Ladisch wrote: > >> Igor Tandetnik wrote:> On 2/26/2013 2:39 AM, dd wrote: >> >>> SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND >>>> "somedata/zzz" >>>> >>>> This database has unicode strings(chinese/japanese/...**etc strings). >>>> can >>>> you tell me which is the correct character to replace with z? >>>> >>> >>> U+, of course. >>> >> >> Unicode characters can have more than 16 bits, of course. >> > > ... but SQLite orders them with simple memcmp (absent a custom collation), > so 0x will still compare greater than any surrogate pair. > > If the database file uses UTF-8 encoding, and contains supplemental > characters, then yes, a UTF-8 representation of U+10 would be prudent. > -- > Igor Tandetnik > > > __**_ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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] like query
>>SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/zzz" This database has unicode strings(chinese/japanese/...etc strings). can you tell me which is the correct character to replace with z? On Mon, Feb 25, 2013 at 8:13 PM, Simon Slavin wrote: > > On 25 Feb 2013, at 2:46pm, dd wrote: > > > Table has string data type column. format of strings: > > somedata1/somedata2/somedata3 > > > > I have written query to search : select * from emp where column_test > like > > "somedata/%"; > > > > It gives perfomance as per articles in internet. Is it? If yes, what is > > alternate query for this? > > If the format of your 'LIKE' clause is always that you have fixed text at > the beginning, then you can speed up your search a lot. Create an index on > the 'column_test' field, and use this query: > > SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/zzz" > > replace 'zzz' with '~~~' or something similar if you're being really fussy. > > 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] like query
Thanks Richard. On Mon, Feb 25, 2013 at 6:54 PM, Richard Hipp wrote: > On Mon, Feb 25, 2013 at 9:46 AM, dd wrote: > > > Hi, > > > > Table has string data type column. format of strings: > > somedata1/somedata2/somedata3 > > > > I have written query to search : select * from emp where column_test > like > > "somedata/%"; > > > > It gives perfomance as per articles in internet. Is it? If yes, what is > > alternate query for this? > > > > The query might go faster if you do: > > CREATE INDEX emp_idx1 ON emp(column_test COLLATE nocase); > > Or, if you really intended to do a case-sensitive search, you could say: > > SELECT * FROM emp WHERE column_test GLOB 'somedata/*'; > > > > > > > > > Thanks in advance. > > > > Best Regards, > > dd. > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] like query
Hi, Table has string data type column. format of strings: somedata1/somedata2/somedata3 I have written query to search : select * from emp where column_test like "somedata/%"; It gives perfomance as per articles in internet. Is it? If yes, what is alternate query for this? Thanks in advance. Best Regards, dd. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] table backup
I am using SQLite version 3.7.14.1. On Wed, Dec 12, 2012 at 2:56 PM, dd wrote: > Hi Michael > > I am using journal mode truncate. Explicitly, I am not running any pragma > for synchronous. (i think, it's default value is 2 - Full). > > Regards, > d > > > On Tue, Dec 11, 2012 at 5:08 PM, Michael Black wrote: > >> I don't see in the thread where you say what journal mode you're running >> in >> or your synchronous setting. >> That may explain your problem and be easily fixable. >> >> The other thing is to compile your system with stack protection and see if >> that can trap the problem. >> >> If it's just stack corruption you should be able to trigger the error >> locally much more quickly with stack protection on then in a random >> system. >> >> >> >> >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dd >> Sent: Tuesday, December 11, 2012 1:00 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] table backup >> >> Hi Roger, >> >> I don't have any clue. Two databases are corrupted. First one, while >> inserting 20,000 records suddenly sqlite thrown disk io error at 4,000 >> record. No clue. >> >> Second database corrupted when my application crashed. But that time, >> second database was not opened. >> >> So, I am planning to choose backup solution instead of investigating >> corruption. >> >> I discussed this issue in my previous post. >> >> Any sample application for virtual table option. >> >> Best Regards, >> d >> >> >> On Mon, Dec 10, 2012 at 9:09 PM, Roger Binns >> wrote: >> >> > -BEGIN PGP SIGNED MESSAGE- >> > Hash: SHA1 >> > >> > On 09/12/12 21:44, dd wrote: >> > > Sometimes, sqlite databse corrupts. >> > >> > That is the problem you need to fix. If you have a system that is >> > unreliable then it will also corrupt your backups. >> > >> > http://www.sqlite.org/lockingv3.html#how_to_corrupt >> > http://www.sqlite.org/howtocorrupt.html >> > >> > > So, I want to take online backup of specific table. Not entire >> > > database. >> > >> > Do you need to take a backup on every change, as part of the change or >> is >> > it acceptable to make backups periodically and possibly lose >> intermediate >> > versions of the data? >> > >> > For a periodic backup you can iterate over the table contents and output >> > them in a convenient format for you, such as CSV or SQL statements. >> > >> > For saving all data you can use triggers to save historical values in a >> > second table and then do a periodic backup. >> > >> > If it must be immediate then the only choice available is to use a >> virtual >> > table and do the backup during writes/sync. >> > >> > This is all considerably more work than figuring out why you are getting >> > corruption in the first place. >> > >> > Roger >> > -BEGIN PGP SIGNATURE- >> > Version: GnuPG v1.4.11 (GNU/Linux) >> > >> > iEYEARECAAYFAlDGF2IACgkQmOOfHg372QTo9gCfSQQwreSvsa9lrV/wj0YC2Fvj >> > LT0AmwdZSaNvVJJuic3gLYmQfn9YX6x3 >> > =Gx1r >> > -END PGP SIGNATURE- >> > ___ >> > 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-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] table backup
Hi Michael I am using journal mode truncate. Explicitly, I am not running any pragma for synchronous. (i think, it's default value is 2 - Full). Regards, d On Tue, Dec 11, 2012 at 5:08 PM, Michael Black wrote: > I don't see in the thread where you say what journal mode you're running in > or your synchronous setting. > That may explain your problem and be easily fixable. > > The other thing is to compile your system with stack protection and see if > that can trap the problem. > > If it's just stack corruption you should be able to trigger the error > locally much more quickly with stack protection on then in a random system. > > > > > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dd > Sent: Tuesday, December 11, 2012 1:00 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] table backup > > Hi Roger, > > I don't have any clue. Two databases are corrupted. First one, while > inserting 20,000 records suddenly sqlite thrown disk io error at 4,000 > record. No clue. > > Second database corrupted when my application crashed. But that time, > second database was not opened. > > So, I am planning to choose backup solution instead of investigating > corruption. > > I discussed this issue in my previous post. > > Any sample application for virtual table option. > > Best Regards, > d > > > On Mon, Dec 10, 2012 at 9:09 PM, Roger Binns > wrote: > > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > On 09/12/12 21:44, dd wrote: > > > Sometimes, sqlite databse corrupts. > > > > That is the problem you need to fix. If you have a system that is > > unreliable then it will also corrupt your backups. > > > > http://www.sqlite.org/lockingv3.html#how_to_corrupt > > http://www.sqlite.org/howtocorrupt.html > > > > > So, I want to take online backup of specific table. Not entire > > > database. > > > > Do you need to take a backup on every change, as part of the change or is > > it acceptable to make backups periodically and possibly lose intermediate > > versions of the data? > > > > For a periodic backup you can iterate over the table contents and output > > them in a convenient format for you, such as CSV or SQL statements. > > > > For saving all data you can use triggers to save historical values in a > > second table and then do a periodic backup. > > > > If it must be immediate then the only choice available is to use a > virtual > > table and do the backup during writes/sync. > > > > This is all considerably more work than figuring out why you are getting > > corruption in the first place. > > > > Roger > > -BEGIN PGP SIGNATURE- > > Version: GnuPG v1.4.11 (GNU/Linux) > > > > iEYEARECAAYFAlDGF2IACgkQmOOfHg372QTo9gCfSQQwreSvsa9lrV/wj0YC2Fvj > > LT0AmwdZSaNvVJJuic3gLYmQfn9YX6x3 > > =Gx1r > > -END PGP SIGNATURE- > > ___ > > 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-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] table backup
I tested with millions of records. Sqlite is stable and reliable. It is suitable for my application. So, I have chosen. But in customers place, I am getting corruptions. Within 6 months I got 4 corruptions. I do integrity check when app launch. But, critical data is there with corrupted database. How can I recover it without backup. bit more details: 1. Application installing in users machine. 2. Configuration maintains in database in local computer. 3. Application related data stored in database when running in local computer. 4. Users related data(critical) stored in database in local computer. Database integrity is very very fine when app launches. In runtime, due to hardware or my logic error(using smart pointers boost/std), sqlite database got corrupted. Now, backup is mandatory for me. this is integiry_check result for corrupted db: sqlite> PRAGMA integrity_check; *** in database main *** Page 255: btreeInitPage() returns error code 11 On tree page 970 cell 5: Child page depth differs Page 252: unable to get the page. error code=1 Page 249: unable to get the page. error code=1 Page 246: unable to get the page. error code=1 Page 244: unable to get the page. error code=1 Page 239: unable to get the page. error code=1 Page 236: unable to get the page. error code=1 Page 233: unable to get the page. error code=1 Page 180: unable to get the page. error code=1 Page 229: unable to get the page. error code=1 Page 226: unable to get the page. error code=1 Page 223: unable to get the page. error code=1 Page 220: unable to get the page. error code=1 Page 218: unable to get the page. error code=1 Page 214: unable to get the page. error code=1 Page 211: unable to get the page. error code=1 Page 208: unable to get the page. error code=1 Page 205: unable to get the page. error code=1 Page 203: unable to get the page. error code=1 Page 194: unable to get the page. error code=1 Page 197: unable to get the page. error code=1 Page 200: unable to get the page. error code=1 Page 112: unable to get the page. error code=1 On tree page 1832 cell 11: Child page depth differs Page 1101: unable to get the page. error code=1 On tree page 1832 cell 12: Child page depth differs Page 1181: unable to get the page. error code=1 Page 1331: unable to get the page. error code=1 Page 1377: unable to get the page. error code=1 Page 1426: unable to get the page. error code=1 Page 2475: unable to get the page. error code=1 Page 2759: unable to get the page. error code=1 Page 2890: unable to get the page. error code=1 Page 2927: unable to get the page. error code=1 Page 2709: unable to get the page. error code=1 Page 2375: unable to get the page. error code=1 Page 2129: unable to get the page. error code=1 Page 1199: unable to get the page. error code=1 Page 1833: unable to get the page. error code=1 Page 7: unable to get the page. error code=1 Page 8: unable to get the page. error code=1 Page 5: unable to get the page. error code=1 Page 6: unable to get the page. error code=1 Page 4: unable to get the page. error code=1 Page 2: unable to get the page. error code=1 Page 3: unable to get the page. error code=1 Page 1: unable to get the page. error code=1 Page 9 is never used Page 10 is never used Page 16 is never used Page 21 is never used Page 22 is never used Page 23 is never used Page 24 is never used Page 25 is never used Page 26 is never used Page 28 is never used Page 30 is never used Page 31 is never used Page 33 is never used Page 34 is never used Page 35 is never used Page 36 is never used Page 37 is never used Page 38 is never used Page 39 is never used Page 40 is never used Page 41 is never used Page 42 is never used Page 43 is never used Page 44 is never used Page 46 is never used Page 47 is never used Page 49 is never used Page 51 is never used Page 54 is never used Page 55 is never used Page 56 is never used Page 57 is never used Page 58 is never used Page 59 is never used Page 60 is never used Page 61 is never used Page 62 is never used Page 63 is never used Page 64 is never used Page 65 is never used Page 66 is never used Page 67 is never used Page 68 is never used Page 69 is never used Page 70 is never used Page 71 is never used Page 72 is never used Page 73 is never used Page 74 is never used Page 75 is never used Page 76 is never used Page 77 is never used Any inputs? On Tue, Dec 11, 2012 at 2:16 PM, Simon Slavin wrote: > > On 11 Dec 2012, at 7:00am, dd wrote: > > > I don't have any clue. Two databases are corrupted. First one, while > > inserting 20,000 records suddenly sqlite thrown disk io error at 4,000 > > record. No clue. > > > > Second database corrupted when my application crashed. But that time, > > second database was not opened. > > > > So, I am planning to choose backup solution instead of investigating > > corruption. > > That seems silly. Unless you run an integrity-check just before you do a > backup you will be
Re: [sqlite] table backup
Hi Roger, I don't have any clue. Two databases are corrupted. First one, while inserting 20,000 records suddenly sqlite thrown disk io error at 4,000 record. No clue. Second database corrupted when my application crashed. But that time, second database was not opened. So, I am planning to choose backup solution instead of investigating corruption. I discussed this issue in my previous post. Any sample application for virtual table option. Best Regards, d On Mon, Dec 10, 2012 at 9:09 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 09/12/12 21:44, dd wrote: > > Sometimes, sqlite databse corrupts. > > That is the problem you need to fix. If you have a system that is > unreliable then it will also corrupt your backups. > > http://www.sqlite.org/lockingv3.html#how_to_corrupt > http://www.sqlite.org/howtocorrupt.html > > > So, I want to take online backup of specific table. Not entire > > database. > > Do you need to take a backup on every change, as part of the change or is > it acceptable to make backups periodically and possibly lose intermediate > versions of the data? > > For a periodic backup you can iterate over the table contents and output > them in a convenient format for you, such as CSV or SQL statements. > > For saving all data you can use triggers to save historical values in a > second table and then do a periodic backup. > > If it must be immediate then the only choice available is to use a virtual > table and do the backup during writes/sync. > > This is all considerably more work than figuring out why you are getting > corruption in the first place. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.11 (GNU/Linux) > > iEYEARECAAYFAlDGF2IACgkQmOOfHg372QTo9gCfSQQwreSvsa9lrV/wj0YC2Fvj > LT0AmwdZSaNvVJJuic3gLYmQfn9YX6x3 > =Gx1r > -END PGP SIGNATURE- > ___ > 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] table backup
Sometimes, sqlite databse corrupts. So, I want to take online backup of specific table. Not entire database. Any best solution? On Sun, Dec 9, 2012 at 11:29 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 09/12/12 10:35, dd wrote: > > i mean, for every write operation in a table. > > You should specify some of your other constraints and exactly what it is > you are trying to achieve (*not* how you are trying to achieve it). > > The solutions range from implementing undo by using triggers to save old > values, through using a virtual table that backs up data somewhere else. > > The SQLite backup API only works on the whole database. > > Roger > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.11 (GNU/Linux) > > iEYEARECAAYFAlDE5poACgkQmOOfHg372QQI8gCeP6GfezJUWQ7M3pdfvvgc2FCp > J1QAoMSM/tbpxlicrrDpTbgjHAsWln1d > =mPMA > -END PGP SIGNATURE- > ___ > 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] table backup
i mean, for every write operation in a table. On Sun, Dec 9, 2012 at 11:55 PM, dd wrote: > Hi All, > > What is the best way to do online backup for a particular table for > every write operation in a sqlite db. Not entire database. > > Best Regards, > d > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] table backup
Hi All, What is the best way to do online backup for a particular table for every write operation in a sqlite db. Not entire database. Best Regards, d ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] disk image malformed
I do Integrity check every often. On Sun, Dec 9, 2012 at 5:07 PM, Simon Slavin wrote: > > On 9 Dec 2012, at 12:44pm, dd wrote: > > > Can I read the database when sqlite throws disk io or image malformed or > > other critical errors first time. I cannot simulate these issues on my > > machine. So, I need your suggestion. > > You should not continue to work with the database in your own application > on customer computers. > > I would recommend that you copy the database to your own computer and open > it with the sqlite3 shell tool: > > <http://www.sqlite.org/sqlite.html> > > you will find a copy on every Mac in '/usr/bin/sqlite3'. > > You can immediately run > > <http://www.sqlite.org/pragma.html#pragma_integrity_check> > > and find out whether the error means that the file itself is corrupt. If > the file fails the integrity check then the file is corrupt. if it > doesn't, it means that something went wrong (software or hardware) on your > customer's computer but that this didn't cause anything bad to be written > to the database file. > > 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] disk image malformed
Can I read the database when sqlite throws disk io or image malformed or other critical errors first time. I cannot simulate these issues on my machine. So, I need your suggestion. On Sun, Dec 9, 2012 at 4:40 PM, dd wrote: > Thank you Simon Slavin. > > > On Sun, Dec 9, 2012 at 4:27 PM, Simon Slavin wrote: > >> >> On 9 Dec 2012, at 12:10pm, dd wrote: >> >> > I have code in C++. Right now, I got this issue on Mac. >> >> Oh. Then you don't have to worry about Windows codepages. All your text >> is already in Unicode. >> >> >>> you still have faulty hardware or software >> > >> > faulty software means? (OS or sqlite version) >> >> The software you wrote. Your C++ code. You may be writing to memory >> that SQLite is using. You may not be allocating or disposing of memory >> correctly. The C programming language is very bad at allowing you to do >> all sorts of dangerous things. Every C programmer makes mistakes like this >> from time to time. >> >> Make sure that you are checking the return values of all your calls to >> the SQLite API, reporting any unexpected values and immediately halting >> your application. That is the easiest way to detect problems as soon as >> they happen. If you don't do this then your application may continue to >> use the database, writing corrupted memory to the database file. >> >> 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] disk image malformed
Thank you Simon Slavin. On Sun, Dec 9, 2012 at 4:27 PM, Simon Slavin wrote: > > On 9 Dec 2012, at 12:10pm, dd wrote: > > > I have code in C++. Right now, I got this issue on Mac. > > Oh. Then you don't have to worry about Windows codepages. All your text > is already in Unicode. > > >>> you still have faulty hardware or software > > > > faulty software means? (OS or sqlite version) > > The software you wrote. Your C++ code. You may be writing to memory that > SQLite is using. You may not be allocating or disposing of memory > correctly. The C programming language is very bad at allowing you to do > all sorts of dangerous things. Every C programmer makes mistakes like this > from time to time. > > Make sure that you are checking the return values of all your calls to the > SQLite API, reporting any unexpected values and immediately halting your > application. That is the easiest way to detect problems as soon as they > happen. If you don't do this then your application may continue to use the > database, writing corrupted memory to the database file. > > 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] disk image malformed
I have code in C++. Right now, I got this issue on Mac. >>you still have faulty hardware or software faulty software means? (OS or sqlite version) On Sun, Dec 9, 2012 at 3:37 PM, Simon Slavin wrote: > > On 9 Dec 2012, at 7:34am, dd wrote: > > > I have 10 databases. One database got disk io error, later it's used by > > application without proper handling, so it's corrupted. > > > > After some days, one more database also corrupted without io error. > Sqlite > > returned error code 11. It's fully corrupted. Are these corruptions > depend > > on other database integrity? > > Corruption may spread from one database to another if you have a corrupt > database open at the same time as an uncorrupt database. It is rare but it > can happen. If you are no longer opening corrupt database files and are > still getting more corruption you still have faulty hardware or software. > > > I am using this database for "schengen countries" customers. Can sqlite > > handle these characters(special)? > > SQLite handles text in UTF-8 or UTF-16 format. If you are storing data in > a database relying on other Windows codepages then you will get strange > results. If you are allowing non-Roman characters and rely on your user's > codepage when handling text, use a routine like 'WideCharToMultiByte': > > <http://msdn.microsoft.com/en-us/library/dd374130(v=vs.85).aspx> > > to convert the data to UTF-8 or UTF-16 before storing it in the SQLite > database. Actually it's easier to convert your text to Unicode immediately > you receive it from your user. You do not have to use the above routine, I > provide it just as an example. > > I can't help more without knowing what OS and programming language you're > using but maybe someone else can. > > 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] disk image malformed
I have 10 databases. One database got disk io error, later it's used by application without proper handling, so it's corrupted. After some days, one more database also corrupted without io error. Sqlite returned error code 11. It's fully corrupted. Are these corruptions depend on other database integrity? I am using this database for "schengen countries" customers. Can sqlite handle these characters(special)? On Fri, Dec 7, 2012 at 5:51 PM, Simon Slavin wrote: > > On 7 Dec 2012, at 1:36pm, dd wrote: > > > Thank you Simon. > > > > So I have to stop using database when error between 1 to 26. > > You have to stop when you get any code you do not have specific logic to > handle. Here is the list of result codes: > > <http://www.sqlite.org/c3ref/c_abort.html> > > You can expect 0 from anything, and 100 and 101 from _step() calls. You > may have written your own software which will cause or handle various > errors (e.g. 4, 9, 19). Anything you haven't written your own code to > handle is a sign that your program needs to immediately stop until you have > figured out what caused the error. > > You might find it useful to look at the extended result codes if you don't > understand what's causing an error: > > <http://www.sqlite.org/c3ref/extended_result_codes.html> > <http://www.sqlite.org/c3ref/c_abort_rollback.html> > > > If I use new database connection (sqlite3*) for every query, will it > cause > > disk io error? > > It will not create them or prevent them, it will just make your software > slower. That result code is what happens when your operating system > reports an error, which it may do for anything from broken hardware, a full > hard disk, or trying to edit a file without high enough permissions. I > don't know what's causing the SQLITE_IOERR that you have seen. > > > I am encrypting database with secret key. I changed page size also. Can > > sqlite handle encrypted database and different page sizes? > > Each database has only one page size. You can have as many databases open > at the same time as you want. They do not all have to have the same page > size. > > 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] just a test
Yes. Igor Tandetnik mails marked as a spam nowadays. I marked it as a NOT SPAM. On Sun, Dec 9, 2012 at 9:33 AM, Gabor Grothendieck wrote: > I am still having problems with Igor's gmail messages being marked as > spam in gmail but after the upteenth time declaring them not to be > spam google finally asked me if I wanted to report it to their gmail > team so hopefully they will fix it soon. > > On Mon, Dec 3, 2012 at 11:59 PM, Clive Hayward > wrote: > > Igor's messages sometimes get marked as spam by gmail. > > > > -- > > Clive Hayward > > > > > > On 2012-12-03, at 7:57 AM, e-mail mgbg25171 > wrote: > > > >> I've posted a couple of mails lately...I'm not getting them via the > list or > >> any responses. > >> Admin says Igor responded to one of them...Thanks Igor! > >> This is just a test to see if the mail is coming to me (as a member of > the > >> list). > >> Therefore please just ignore this. > >> ___ > >> 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 > > > > -- > Statistics & Software Consulting > GKX Group, GKX Associates Inc. > tel: 1-877-GKX-GROUP > email: ggrothendieck at gmail.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
Re: [sqlite] disk image malformed
Thank you Simon. So I have to stop using database when error between 1 to 26. If I use new database connection (sqlite3*) for every query, will it cause disk io error? I am encrypting database with secret key. I changed page size also. Can sqlite handle encrypted database and different page sizes? On Fri, Dec 7, 2012 at 2:06 AM, Simon Slavin wrote: > > On 6 Dec 2012, at 7:57pm, dd wrote: > > > It's checking with OK, BUSY, 101 and some other errors. But not disk io, > > image malformed errors. How to handle these errors in run time? these > > databases have critical information about customers. We cannot stop > > application. Any inputs? > > Proceeding after anything but SQLITE_OK, and the special result codes > expected when you do sqlite3_step() will only make more problems. The > first time you get an error like DISK IO you have to stop. Anything > written to the database after that will probably fail, but if it doesn't > fail it'll probably just corrupt the database file. > > But it's extremely unusual for users of SQLite to see errors 10 or 11. > They almost always indicates faulty hardware or that some part of your > operating system has become corrupt (which might itself be the result of > faulty hardware). > > 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] disk image malformed
Databases integrity fine when application starts. These critical errors are introduced in run time. I cannot take backup of these databases also. space(memory) problem. On Fri, Dec 7, 2012 at 1:27 AM, dd wrote: > It's checking with OK, BUSY, 101 and some other errors. But not disk io, > image malformed errors. How to handle these errors in run time? these > databases have critical information about customers. We cannot stop > application. Any inputs? > > > On Fri, Dec 7, 2012 at 1:20 AM, Simon Slavin wrote: > >> >> On 6 Dec 2012, at 7:47pm, dd wrote: >> >> > No. I replaced with fresh database. I want to prevent these errors in >> our >> > customers place for future purpose. >> >> Run the integrity check every so often and see if the problem occurs >> again. Make sure that your program checks the result returned by every >> SQLite API call to make sure it is SQLITE_OK. >> >> 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] disk image malformed
It's checking with OK, BUSY, 101 and some other errors. But not disk io, image malformed errors. How to handle these errors in run time? these databases have critical information about customers. We cannot stop application. Any inputs? On Fri, Dec 7, 2012 at 1:20 AM, Simon Slavin wrote: > > On 6 Dec 2012, at 7:47pm, dd wrote: > > > No. I replaced with fresh database. I want to prevent these errors in our > > customers place for future purpose. > > Run the integrity check every so often and see if the problem occurs > again. Make sure that your program checks the result returned by every > SQLite API call to make sure it is SQLITE_OK. > > 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] disk image malformed
No. I replaced with fresh database. I want to prevent these errors in our customers place for future purpose. On Fri, Dec 7, 2012 at 1:09 AM, Simon Slavin wrote: > > On 6 Dec 2012, at 7:30pm, dd wrote: > > > My multithreaded application do all read/write operations. For each > query, > > it opens database connection and execute query then close connection. > This > > application executes plenty of records within a second. In this scenario, > > Application got disk io error within couple of hours with new database. > > Are you still using the database which the shell tool reports as corrupt ? > > 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] disk image malformed
I understand your points Simon. Let me explain scenario where I got this disk io error then my database got corrupted. My multithreaded application do all read/write operations. For each query, it opens database connection and execute query then close connection. This application executes plenty of records within a second. In this scenario, Application got disk io error within couple of hours with new database. (even space available on that machine). Application didn't handle this error. So application continued execution. After that, disk image malformed errors are thrown by sqlite for some records only. There was no other errors before disk io error. I suspect, after disk io error only these errors are introduced. Any ideas? On Fri, Dec 7, 2012 at 12:39 AM, dd wrote: > No Robert. It's on same machine -- multiple threads within process, > TRUNCATE mode. > > > On Fri, Dec 7, 2012 at 12:38 AM, Robert Myers wrote: > >> One thing I haven't seen anyone ask yet - are you putting this on a >> network drive? >> On 12/6/2012 10:52 AM, Durga D wrote: >> > Hi, >> > >> > Is it possible to corrupt a single table among 10 tables in a >> database? >> > >> > Is it possible to corrupt some records among millions of records in a >> > table? >> > >> > Best Regards, >> > ___ >> > 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] disk image malformed
No Robert. It's on same machine -- multiple threads within process, TRUNCATE mode. On Fri, Dec 7, 2012 at 12:38 AM, Robert Myers wrote: > One thing I haven't seen anyone ask yet - are you putting this on a > network drive? > On 12/6/2012 10:52 AM, Durga D wrote: > > Hi, > > > > Is it possible to corrupt a single table among 10 tables in a database? > > > > Is it possible to corrupt some records among millions of records in a > > table? > > > > Best Regards, > > ___ > > 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
