Re: [sqlite] Case-sensitivity, performance and LIKE

[Jay A. Kreibich]

On Tue, Mar 30, 2010 at 10:59:49PM -0400, Tom Holden scratched on the wall:
>
> - Original Message - From: "Jay A. Kreibich" 
> To: "General Discussion of SQLite Database" 
> Sent: Tuesday, March 30, 2010 9:26 PM
> Subject: Re: [sqlite] Case-sensitivity, performance and LIKE
>
>
>> On Tue, Mar 30, 2010 at 08:41:09PM -0400, Tom Holden scratched on the  
>> wall:
>>>
>>> - Original Message - From: "Simon Slavin" 
>>> 
>>
>>> > columnName TEXT COLLATE NOCASE
>>> >
>>> > then all sorting and SELECT queries on it will ignore case.
>>>
>>> You don't even have to change the defined collation as you can impose the
>>> NOCASE collation in the SELECT statement as:
>>>
>>> SELECT * from Customers WHERE LastName = 'Shaw' COLLATE NOCASE AND  
>>> FirstName
>>> = 'Gioia' COLLATE NOCASE;

>>  If either/both of these columns has/have indexes, it is best to change
>>  it in the table definition.  Otherwise you also need to define it in the
>>  index definition, as well as everywhere you expect to use the index.
>>  This is all automatic if everything is built with the collation in
>>  the table definition.

> Maybe it works OK because the custom collation is a superset of the 
> integral NOCASE collation and my data does not lie outside the latter.

  Sorry... I didn't mean to imply this was wrong.  Using a specific
  collation in a query is perfectly OK, and should always result in the
  correct answer.  The issue is that a query will only use an index if
  the query collation and the index collation match.  Given the OP's
  performance motivations, it seemed important.
  
  Hence, for general situations where performance is a concern, it is
  best to just declare the whole column with whatever collation you
  want to use.  Otherwise, it is all too easy to miss something
  somewhere and end up with a much slower query.

  For one-off specific instances when you want to use a specific 
  collation (or, as in your situation when you cannot re-define the
  table) it is perfectly valid to simply add the collation to the query.
  However, that will result in a full table-scan, even if an index is
  otherwise available.

   -j

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

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

Re: [sqlite] Case-sensitivity, performance and LIKE

[Tom Holden]

- Original Message - 
From: "Jay A. Kreibich" 
To: "General Discussion of SQLite Database" 
Sent: Tuesday, March 30, 2010 9:26 PM
Subject: Re: [sqlite] Case-sensitivity, performance and LIKE


> On Tue, Mar 30, 2010 at 08:41:09PM -0400, Tom Holden scratched on the 
> wall:
>>
>> - Original Message - 
>> From: "Simon Slavin" 
>
>> > columnName TEXT COLLATE NOCASE
>> >
>> > then all sorting and SELECT queries on it will ignore case.
>>
>> You don't even have to change the defined collation as you can impose the
>> NOCASE collation in the SELECT statement as:
>>
>> SELECT * from Customers WHERE LastName = 'Shaw' COLLATE NOCASE AND 
>> FirstName
>> = 'Gioia' COLLATE NOCASE;
>
>  If either/both of these columns has/have indexes, it is best to change
>  it in the table definition.  Otherwise you also need to define it in the
>  index definition, as well as everywhere you expect to use the index.
>  This is all automatic if everything is built with the collation in
>  the table definition.

I have used this technique to query a database of a commercial application 
with a proprietary, inaccessible nocase collation sequence and over whose 
structure I have no control. Seems to work alright but I have no way of 
assessing what the cost penalty might be over the 'best way'. Besides, it 
was not for a 'life or death' application (albeit one that tracks lives and 
deaths). Thought I would throw it in as an option.

Maybe it works OK because the custom collation is a superset of the integral 
NOCASE collation and my data does not lie outside the latter. If some data 
did lie outside the intersection of the two collations, I suppose there 
would be some sorting errors if the indexes are left on the superset and the 
query uses the override with the subset collation.

But then there could be data that lies outside the superset that would not 
be correctly sorted even with the 'best way'. Isn't that the conundrum 
caused by so many different character sets among the world's many languages? 
The more universal the collation sequence, the more expensive the index?

Tom 

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

Re: [sqlite] Case-sensitivity, performance and LIKE

[Jay A. Kreibich]

On Tue, Mar 30, 2010 at 08:41:09PM -0400, Tom Holden scratched on the wall:
> 
> - Original Message - 
> From: "Simon Slavin" 

> > columnName TEXT COLLATE NOCASE
> >
> > then all sorting and SELECT queries on it will ignore case.
>
> You don't even have to change the defined collation as you can impose the 
> NOCASE collation in the SELECT statement as:
> 
> SELECT * from Customers WHERE LastName = 'Shaw' COLLATE NOCASE AND FirstName 
> = 'Gioia' COLLATE NOCASE;

  If either/both of these columns has/have indexes, it is best to change
  it in the table definition.  Otherwise you also need to define it in the
  index definition, as well as everywhere you expect to use the index.
  This is all automatic if everything is built with the collation in
  the table definition.

   -j

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

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

Re: [sqlite] Case-sensitivity, performance and LIKE

[Tom Holden]

- Original Message - 
From: "Simon Slavin" 
To: "General Discussion of SQLite Database" 
Sent: Tuesday, March 30, 2010 8:12 PM
Subject: Re: [sqlite] Case-sensitivity, performance and LIKE


>
> On 31 Mar 2010, at 12:51am, Rashed Iqbal wrote:
>
>> Is there a way to make SQLite queries case-insensitive?
>>
>> For example my query is:
>>
>> SELECT * from Customers WHERE LastName = 'Shaw' AND FirstName = 'Gioia'
>>
>> and I want to be able to get same results no matter if the case is good
>> in the DB or not or in the query.
>
> You need a change from the standard collating system.  Take a look at 
> section 6.2 of
>
> http://www.sqlite.org/datatype3.html
>
> I haven't tried this myself but I think that if you define a column as
>
> columnName TEXT COLLATE NOCASE
>
> then all sorting and SELECT queries on it will ignore case.
>
You don't even have to change the defined collation as you can impose the 
NOCASE collation in the SELECT statement as:

SELECT * from Customers WHERE LastName = 'Shaw' COLLATE NOCASE AND FirstName 
= 'Gioia' COLLATE NOCASE;

Tom

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

Re: [sqlite] Case-sensitivity, performance and LIKE

[Simon Slavin]

On 31 Mar 2010, at 12:51am, Rashed Iqbal wrote:

> Is there a way to make SQLite queries case-insensitive? 
> 
> For example my query is: 
> 
> SELECT * from Customers WHERE LastName = 'Shaw' AND FirstName = 'Gioia'
> 
> and I want to be able to get same results no matter if the case is good
> in the DB or not or in the query.

You need a change from the standard collating system.  Take a look at section 
6.2 of

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

I haven't tried this myself but I think that if you define a column as

columnName TEXT COLLATE NOCASE

then all sorting and SELECT queries on it will ignore case.

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

[sqlite] Case-sensitivity, performance and LIKE

[Rashed Iqbal]

Hello, I need help with the following question:

 

Is there a way to make SQLite queries case-insensitive? 

 

For example my query is: 

 

SELECT * from Customers WHERE LastName = 'Shaw' AND FirstName = 'Gioia'

 

and I want to be able to get same results no matter if the case is good
in the DB or not or in the query. 

 

Currently, I am using LIKE as the following: 

 

SELECT * from Customers WHERE LastName like 'Shaw' AND FirstName like
'Gioia'

 

to take care of case-sensitivity, but I believe there is a performance
penalty associated with using LIKE in SQLite. Is this true? 

 

Cheers!

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

Re: [sqlite] Select * from tablename results in an exception

[Burnett, Joe]

Thank you all for the replies. The message was a bit of a red herring.
I had issues in my SQLite DB schema - VARCHAR(64) was not big enough.

Joe Burnett | Principal Software Engineer | FIDELITY INSTITUTIONAL
TECHNOLOGY
2 Contra Way
Merrimack, NH 03054
603.791.5113
cell: 603.289.0481


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Romano
Sent: Tuesday, March 30, 2010 5:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Select * from tablename results in an exception

http://social.msdn.microsoft.com/Forums/en-US/Vsexpressvb/thread/27aec61
2-5ca4-41ba-80d6-0204893fdcd1

Maybe related.
Regards
Tim Romano
___
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] Select * from tablename results in an exception

[Tim Romano]

http://social.msdn.microsoft.com/Forums/en-US/Vsexpressvb/thread/27aec612-5ca4-41ba-80d6-0204893fdcd1

Maybe related.
Regards
Tim Romano
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Preserving column size

[Kevin M.]

> Sorry I misunderstood your requirement, Kevin. I was focusing on the

>singular "a table" and "the table" in your original post, versus the plural
> "many types of structs".  I thought you were looking for serialization
> approach.

> Regards
> Tim Romano


Ah, now I see from where the misunderstanding came.



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

Re: [sqlite] Select * from tablename results in an exception

[Simon Slavin]

On 30 Mar 2010, at 2:55pm, Burnett, Joe wrote:

> Your understanding is correct. However, I used the SQLite Admin tool and
> the select * query 
> was successful after the table was populated and the program exited. 
> 
> Isn't that the same thing?

Okay, if I understand your result, you have a specific SELECT query, and this 
query executes correctly in the sqlite3 command-line tool, but it fails when 
you execute it in your application, which uses a .NET framework.

Instead of that specific SELECT, can you try one on a different table ?  If 
that works, try one on that table which doesn't use all the columns.  If it 
fails, try one of the PRAGMA commands that returns a table as an answer (e.g. 
"PRAGMA compile_options;").

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

[sqlite] multithreading (through C++ wrapper)

[Aymeric . Moureau-EXT]

Hello,

I'm working on a C++ application under WinCE (file system is TexFAT), with 
SQLite (actually version 3.6.11, but phenomenon is reproduced with 3.6.23) 
with the following C++ wrapper : 
http://www.alhem.net/project/sqlite/index.html . There's only one database 
with three tables (file is around 40KB, not so many entries!).
The application has two same priority threads (and also the main thread of 
the application) , one is only reading the DB, the second one is reading 
or writing. (Because of some constraints, I cannot store many writings in 
order to do them at once using only one transaction, so I have to do each 
one of them as soon as possible).
The C++ wrapper handles concurrent accesses through a Mutex (both threads 
are naturally using the same Database instance) : each new Query object 
locks the Database instance (and unlocks it in destructor). Then, there 
can't be two Query instances at a time refering to the same Database (I 
checked with traces & asserts, everything is correct).

My problem is the following : some writings or readings fail : SQLite 
returns the SQLITE_BUSY error.

I found on SQLite website that locking mecanism is available with 
THREADSAFE preprocessor flag set at 1, but even then I can reproduce the 
issue.
Also, I've changed the db opening from sqlite3_open to sqlite_open_v2 with 
flags (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX) 
: still unsuccessful...

Do you have any suggestion, as I'm getting stuck ?

[Additionnal question : I've developped a test application in order to 
reproduce the above case. Once I stop this application, and thus the two 
threads accessing the db, I can see the journal file being created/deleted 
for seconds, depending on how long I kept the threads alive. As the C++ 
wrapper is fully synchronous, I don't understand why there are still 
accesses to the db! Is there any cache mecanism into SQLite ?]

Thanks in advance for any answer !
Best regards,
Aymerik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segfault and incorrect result in last_insert_rowid() function

[Dan Kennedy]

On Mar 30, 2010, at 8:53 PM, Pavel Ivanov wrote:

>> The segfault is now fixed here:
>>
>>   http://www.sqlite.org/src/info/ca359a3e80
>
> Does the comment to commit mean that in the same test case if view has
> no triggers then compilation of the statement will still segfault?

No.

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

Re: [sqlite] Select * from tablename results in an exception

[Burnett, Joe]

Hi Simon,

Your understanding is correct. However, I used the SQLite Admin tool and
the select * query 
was successful after the table was populated and the program exited. 

Isn't that the same thing?

Thanks for your reply.

Joe Burnett | Principal Software Engineer | FIDELITY INSTITUTIONAL
TECHNOLOGY
2 Contra Way
Merrimack, NH 03054
603.791.5113
cell: 603.289.0481


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Tuesday, March 30, 2010 9:33 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Select * from tablename results in an exception


On 30 Mar 2010, at 2:01pm, Burnett, Joe wrote:

> I am using SQLite in a C# .NET application. I am inserting into tables
> without any issues as confirmed by the SQLite Admin tool.
> However, later in the application, I want to query the table where I
> inserted the data, I get the following exception:
> 
> "Failed to enable constraints. One or more rows contain values
violating
> non-null, unique, or foreign-key constraints."

If I understand you correctly, your INSERT commands are working fine,
but you get the above error when you do your SELECT.

At the point where you get your error, quit your program (or allow it to
quit), then use the sqlite3 command-line tool to submit the same SELECT
query.  See if the sqlite3 command-line too yields the same error.

Make certain that the sqlite3 command-line tool is compiled using the
same version of sqlite3 that your .NET framework is using.

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] Segfault and incorrect result in last_insert_rowid() function

[Pavel Ivanov]

> The segfault is now fixed here:
>
>   http://www.sqlite.org/src/info/ca359a3e80

Does the comment to commit mean that in the same test case if view has
no triggers then compilation of the statement will still segfault?

Pavel

On Tue, Mar 30, 2010 at 9:44 AM, Dan Kennedy  wrote:
>
> On Mar 30, 2010, at 6:29 PM, Alexey Pechnikov wrote:
>
>> Hello!
>>
>> CREATE TABLE test (
>>  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
>> );
>> CREATE VIEW view_test as select * from test;
>> CREATE TRIGGER view_test_insert instead of insert on view_test
>> begin
>>  insert into test (id) values (NULL);
>> end;
>>
>> insert into view_test (id) values (NULL);
>> select last_insert_rowid();
>> 0
>> select max(rowid) from test;
>> 1
>>
>> insert into view_test default values;
>> Segmentation error
>
> The segfault is now fixed here:
>
>   http://www.sqlite.org/src/info/ca359a3e80
>
> Thanks for reporting it.
>
> ___
> 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] Segfault and incorrect result in last_insert_rowid() function

[Alexey Pechnikov]

Hello!

On Tuesday 30 March 2010 16:12:18 Pavel Ivanov wrote:
> From http://www.sqlite.org/c3ref/last_insert_rowid.html:
> 
> "If an INSERT occurs within a trigger, then the rowid of the inserted
> row is returned by this routine as long as the trigger is running. But
> once the trigger terminates, the value returned by this routine
> reverts to the last value inserted before the trigger fired."
> 
> Your insert definitely happens in the trigger, so no access to rowid outside.

Hm... yes, so it's not the problem, this is feature :-(

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

Re: [sqlite] Preserving column size

[Tim Romano]

Sorry I misunderstood your requirement, Kevin. I was focusing on the
singular "a table" and "the table" in your original post, versus the plural
"many types of structs".  I thought you were looking for serialization
approach.

Regards
Tim Romano



On Tue, Mar 30, 2010 at 8:48 AM, Kevin M.  wrote:

> Actually Tim that is not what I was asking at all.  This is converting an
> application from using MySQL to SQLite.  All the tables are normalized
> already.  But instead of rewriting a large chunk of the application, I'd
> rather use the implementation as is substituting only the code needed to use
> SQLite instead of MySQL.
>
> The various structs are not intermingled and in fact many do not share data
> at all.  My concern was being able to use existing code to iterate through
> the columns of each row and assign the values into a chunk of memory
> (representing a particular struct) while faithfully retaining the size of
> each variable in the struct.  As such, the suggestion to use
> sqlite3_column_decltype seems most appropriate to accomplish the task.
>  Because then I can check for SQLITE_INTEGER as the type and get the
> decltype to find out what size.  Thus, the function called remains generic
> to any given struct, but still assigns values that are the correct size &
> type to the chunk of memory.
>
>
>
>
> 
> From: Tim Romano 
> To: General Discussion of SQLite Database 
> Sent: Tue, March 30, 2010 7:28:16 AM
> Subject: Re: [sqlite] Preserving column size
>
> On Tue, Mar 30, 2010 at 4:30 AM, Jean-Denis Muys  >wrote:
>
> >
> > 
> > See http://www.boost.org and more specifically:
> > http://www.boost.org/doc/libs/1_42_0/libs/serialization/doc/index.html
> > 
> >
>
> The OP's question is another form of this question: What's the best way to
> violate 1NF in SQLite, so that instead of defining multiple tables, a
> single
> table will contain multiple actual or virtual column sets where these
> column
> sets have different numbers of columns and different combinations of
> datatypes?  And I must be able to query the store.
>
> We have three "conceptually similar" serialization approaches  (BLOB, JSON,
> BOOST). But the OP could also store in the following manner and not violate
> 1NF:
>
> Table: STRUCT
> id INTEGER PRIMARY KEY
> structname TEXT
> [...any other attributes that need to be tracked]
>
>
> Table: STRUCTMEMBERS
> id INTEGER PRIMARY KEY
> structid  INTEGER (FK references STRUCT)
> membername TEXT
> memberdatatype  TEXT
> membervalue TEXT
>
> casting 'membervalue' as appropriate during the reconstitution phase.
>
>
> Regards
> Tim Romano
> ___
> 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] Segfault and incorrect result in last_insert_rowid() function

[Dan Kennedy]

On Mar 30, 2010, at 6:29 PM, Alexey Pechnikov wrote:

> Hello!
>
> CREATE TABLE test (
>  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
> );
> CREATE VIEW view_test as select * from test;
> CREATE TRIGGER view_test_insert instead of insert on view_test
> begin
>  insert into test (id) values (NULL);
> end;
>
> insert into view_test (id) values (NULL);
> select last_insert_rowid();
> 0
> select max(rowid) from test;
> 1
>
> insert into view_test default values;
> Segmentation error

The segfault is now fixed here:

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

Thanks for reporting it.

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

Re: [sqlite] Select * from tablename results in an exception

[Simon Slavin]

On 30 Mar 2010, at 2:01pm, Burnett, Joe wrote:

> I am using SQLite in a C# .NET application. I am inserting into tables
> without any issues as confirmed by the SQLite Admin tool.
> However, later in the application, I want to query the table where I
> inserted the data, I get the following exception:
> 
> "Failed to enable constraints. One or more rows contain values violating
> non-null, unique, or foreign-key constraints."

If I understand you correctly, your INSERT commands are working fine, but you 
get the above error when you do your SELECT.

At the point where you get your error, quit your program (or allow it to quit), 
then use the sqlite3 command-line tool to submit the same SELECT query.  See if 
the sqlite3 command-line too yields the same error.

Make certain that the sqlite3 command-line tool is compiled using the same 
version of sqlite3 that your .NET framework is using.

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

Re: [sqlite] Select * from tablename results in an exception

[Burnett, Joe]

That isn't the real name of the table, something else is the real name.

Thanks,


Joe Burnett | Principal Software Engineer | FIDELITY INSTITUTIONAL
TECHNOLOGY
2 Contra Way
Merrimack, NH 03054
603.791.5113
cell: 603.289.0481


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Tuesday, March 30, 2010 9:23 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Select * from tablename results in an exception


On 30 Mar 2010, at 2:01pm, Burnett, Joe wrote:

>string sql = "select * from Table";

Can you use 'Table' as the name of a 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] Select * from tablename results in an exception

[Simon Slavin]

On 30 Mar 2010, at 2:01pm, Burnett, Joe wrote:

>string sql = "select * from Table";

Can you use 'Table' as the name of a table ?

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

[sqlite] Select * from tablename results in an exception

[Burnett, Joe]

Hi All,

I am using SQLite in a C# .NET application. I am inserting into tables
without any issues as confirmed by the SQLite Admin tool.
However, later in the application, I want to query the table where I
inserted the data, I get the following exception:

"Failed to enable constraints. One or more rows contain values violating
non-null, unique, or foreign-key constraints."

There are no keys in the table (I removed them to try to solve this
problem), nor NOT NULL, nor foreign keys. A simple select
shouldn't worry about that, anyway. Here is my code:

string sql = "select * from Table";
mycommand.CommandText = sql;
reader = mycommand.ExecuteReader();
dt.Load(reader);
reader.Close();

What am I missing?

Thanks in advance for your help.

Joseph Burnett | Principal Software Engineer | FIDELITY INSTITUTIONAL
TECHNOLOGY
2 Contra Way
Merrimack, NH 03054
603.791.5113
CELL: 603.289.0481

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

Re: [sqlite] Preserving column size

[Kevin M.]

Actually Tim that is not what I was asking at all.  This is converting an 
application from using MySQL to SQLite.  All the tables are normalized already. 
 But instead of rewriting a large chunk of the application, I'd rather use the 
implementation as is substituting only the code needed to use SQLite instead of 
MySQL.

The various structs are not intermingled and in fact many do not share data at 
all.  My concern was being able to use existing code to iterate through the 
columns of each row and assign the values into a chunk of memory (representing 
a particular struct) while faithfully retaining the size of each variable in 
the struct.  As such, the suggestion to use sqlite3_column_decltype seems most 
appropriate to accomplish the task.  Because then I can check for 
SQLITE_INTEGER as the type and get the decltype to find out what size.  Thus, 
the function called remains generic to any given struct, but still assigns 
values that are the correct size & type to the chunk of memory.





From: Tim Romano 
To: General Discussion of SQLite Database 
Sent: Tue, March 30, 2010 7:28:16 AM
Subject: Re: [sqlite] Preserving column size

On Tue, Mar 30, 2010 at 4:30 AM, Jean-Denis Muys wrote:

>
> 
> See http://www.boost.org and more specifically:
> http://www.boost.org/doc/libs/1_42_0/libs/serialization/doc/index.html
> 
>

The OP's question is another form of this question: What's the best way to
violate 1NF in SQLite, so that instead of defining multiple tables, a single
table will contain multiple actual or virtual column sets where these column
sets have different numbers of columns and different combinations of
datatypes?  And I must be able to query the store.

We have three "conceptually similar" serialization approaches  (BLOB, JSON,
BOOST). But the OP could also store in the following manner and not violate
1NF:

Table: STRUCT
id INTEGER PRIMARY KEY
structname TEXT
[...any other attributes that need to be tracked]


Table: STRUCTMEMBERS
id INTEGER PRIMARY KEY
structid  INTEGER (FK references STRUCT)
membername TEXT
memberdatatype  TEXT
membervalue TEXT

casting 'membervalue' as appropriate during the reconstitution phase.


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



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

Re: [sqlite] Sqlite3 crashing on multithreaded program

[Dan Kennedy]

On Mar 30, 2010, at 7:37 PM, Periasamy, Karthikeyan wrote:

> Hi,
>
> Our application is a multithreaded program running on Powerpc e300  
> mpc8343 embedded board. There are 8 threads running which are  
> querying and updating DB parallelly  and continuously. We are using  
> libdbi-0.8.2 and sqlite-3.6.16 to interact with sqlite3 DB file. The  
> application crashes when it tries to free the dbi_result object.
>
> Here is the gdb backtrace which got from the core dump file,
>
> #1  0x0feb3418 in abort () from /lib/libc.so.6
> #2  0x0fee847c in __libc_message () from /lib/libc.so.6
> #3  0x0feef5ec in malloc_printerr () from /lib/libc.so.6
> #4  0x0fef0244 in free () from /lib/libc.so.6
> #5  0x0fb6d624 in sqlite3MemFree (pPrior=0x0) at sqlite3.c:12289
> #6  0x0fb6dba8 in sqlite3_free (p=0xffc524c) at sqlite3.c:15667
> #7  0x0fb9e238 in sqlite3_free_table (azResult=Cannot access memory  
> at address 0x3200c4c4) at sqlite3.c:79793
> #8  0x0fbf7b5c in dbd_free_query () from /usr/local/lib/dbd// 
> libdbdsqlite3.so
> #9  0x0fc23634 in _disjoin_from_conn () from /usr/local/lib/ 
> libdbi.so.0
> #10 0x0fc23960 in dbi_result_free () from /usr/local/lib/libdbi.so.0
> ...Our application code...
>
> The sqlite3 has been compiled without any configure options and the  
> same  for libdbi.
>
> Please suggest us if there is anything we can do in our application,  
> sqlite3 library or libdbi library to fix this problem?
>
> Note: The crash is happening at the same place "dbi_result_free" for  
> different DBquery every time.

Odds are the pointer being passed to sqlite3_free_table() is
invalid. Either because it was not returned by sqlite3_get_table(),
or it has already been freed by sqlite3_free_table().

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

[sqlite] Sqlite3 crashing on multithreaded program

[Periasamy, Karthikeyan]

Hi,

Our application is a multithreaded program running on Powerpc e300 mpc8343 
embedded board. There are 8 threads running which are querying and updating DB 
parallelly  and continuously. We are using libdbi-0.8.2 and sqlite-3.6.16 to 
interact with sqlite3 DB file. The application crashes when it tries to free 
the dbi_result object.

Here is the gdb backtrace which got from the core dump file,

#1  0x0feb3418 in abort () from /lib/libc.so.6
#2  0x0fee847c in __libc_message () from /lib/libc.so.6
#3  0x0feef5ec in malloc_printerr () from /lib/libc.so.6
#4  0x0fef0244 in free () from /lib/libc.so.6
#5  0x0fb6d624 in sqlite3MemFree (pPrior=0x0) at sqlite3.c:12289
#6  0x0fb6dba8 in sqlite3_free (p=0xffc524c) at sqlite3.c:15667
#7  0x0fb9e238 in sqlite3_free_table (azResult=Cannot access memory at address 
0x3200c4c4) at sqlite3.c:79793
#8  0x0fbf7b5c in dbd_free_query () from /usr/local/lib/dbd//libdbdsqlite3.so
#9  0x0fc23634 in _disjoin_from_conn () from /usr/local/lib/libdbi.so.0
#10 0x0fc23960 in dbi_result_free () from /usr/local/lib/libdbi.so.0
 ...Our application code...

The sqlite3 has been compiled without any configure options and the same  for 
libdbi.

Please suggest us if there is anything we can do in our application, sqlite3 
library or libdbi library to fix this problem?

Note: The crash is happening at the same place "dbi_result_free" for different 
DBquery every time.

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

Re: [sqlite] Preserving column size

[Tim Romano]

On Tue, Mar 30, 2010 at 4:30 AM, Jean-Denis Muys wrote:

>
> 
> See http://www.boost.org and more specifically:
> http://www.boost.org/doc/libs/1_42_0/libs/serialization/doc/index.html
> 
>

The OP's question is another form of this question: What's the best way to
violate 1NF in SQLite, so that instead of defining multiple tables, a single
table will contain multiple actual or virtual column sets where these column
sets have different numbers of columns and different combinations of
datatypes?  And I must be able to query the store.

We have three "conceptually similar" serialization approaches  (BLOB, JSON,
BOOST). But the OP could also store in the following manner and not violate
1NF:

Table: STRUCT
id INTEGER PRIMARY KEY
structname TEXT
[...any other attributes that need to be tracked]


Table: STRUCTMEMBERS
id INTEGER PRIMARY KEY
structid  INTEGER (FK references STRUCT)
membername TEXT
memberdatatype  TEXT
membervalue TEXT

casting 'membervalue' as appropriate during the reconstitution phase.


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

Re: [sqlite] Segfault and incorrect result in last_insert_rowid() function

[Pavel Ivanov]

From http://www.sqlite.org/c3ref/last_insert_rowid.html:

"If an INSERT occurs within a trigger, then the rowid of the inserted
row is returned by this routine as long as the trigger is running. But
once the trigger terminates, the value returned by this routine
reverts to the last value inserted before the trigger fired."

Your insert definitely happens in the trigger, so no access to rowid outside.


Pavel

On Tue, Mar 30, 2010 at 7:29 AM, Alexey Pechnikov
 wrote:
> Hello!
>
> CREATE TABLE test (
>  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
> );
> CREATE VIEW view_test as select * from test;
> CREATE TRIGGER view_test_insert instead of insert on view_test
> begin
>  insert into test (id) values (NULL);
> end;
>
> insert into view_test (id) values (NULL);
> select last_insert_rowid();
> 0
> select max(rowid) from test;
> 1
>
> insert into view_test default values;
> Segmentation error
>
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Segfault and incorrect result in last_insert_rowid() function

[Alexey Pechnikov]

Hello!

CREATE TABLE test (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
);
CREATE VIEW view_test as select * from test;
CREATE TRIGGER view_test_insert instead of insert on view_test
begin
  insert into test (id) values (NULL);
end;

insert into view_test (id) values (NULL);
select last_insert_rowid();
0
select max(rowid) from test;
1

insert into view_test default values;
Segmentation error


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

Re: [sqlite] sqlite performance with large and multiple attached databases

[Simon Slavin]

On 30 Mar 2010, at 9:02am, Akbar Syed wrote:

> Unfortunately, I could not think about any other option than to keep
> the devices attached all the time.

I hope you don't have to handle many attachable devices, because SQLite can't 
handle more than 30 simultaneous ATTACHes.  See section 11 of



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

[sqlite] sqlite performance with large and multiple attached databases

[Akbar Syed]

>>Make one big 'working' database by reading all the data from the databases on 
>>the independent devices and putting it into one big table.  Once you have 
>>sucked out the data you can disconnect from the device.  Then do all your 
>>enquiries on this big table.  There's a special convenient form of the INSERT 
>>command which gets its data directly from a SELECT command:

>>INSERT INTO bigTable SELECT * FROM attachedDB.myTable

>>So, in a simplified example, for each pluginnable device do this:

>>ATTACH "deviceFile.s3" AS attachedDB
>>INSERT INTO allDeviceTable SELECT * FROM attachedDB.myTable
>>DETACH attachedDB

Then just query allDeviceTable.

Thanks Simon for your suggestions. Unfortunately, I cannot detach the
devices in my application.
The data to be inserted in each database could be live i.e, on every
device where a database is
created, there could be new live data which needs to be inserted in
the database as and when added
to the device and made available for a single select. Your suggestions
would have worked if it was not live data.
Sorry I forgot to mention abt this in my previous mail.
Unfortunately, I could not think about any other option than to keep
the devices attached all the time.I am trying to
find some optimum cache values for each connection. I tried not to use
many connections. I just have a single connection
open for a device and its database and one attach connection. As of
now I am using 500K of default cache for each open connection
with 100K for attached connections and 500k default temp-cache and
also I have temp-store set to a file. It might hamper my
performance, yet I dont have any other option thou.

Regards,
-Akbar

On 26 Mar 2010, at 10:47am, Akbar Syed wrote:

>* Unfortunately, my application restricts me to use independent
*>* databases than to a single  database
*>* as each database exists on a different device and contains the info of
*>* that device in the database.
*>* Multiple devices are allowed to connect to my server app which instead
*>* gathers the records from the
*>* independent databases by attaching the databases together.
*
Make one big 'working' database by reading all the data from the
databases on the independent devices and putting it into one big
table.  Once you have sucked out the data you can disconnect from the
device.  Then do all your enquiries on this big table.  There's a
special convenient form of the INSERT command which gets its data
directly from a SELECT command:

INSERT INTO bigTable SELECT * FROM attachedDB.myTable

So, in a simplified example, for each pluginnable device do this:

ATTACH "deviceFile.s3" AS attachedDB
INSERT INTO allDeviceTable SELECT * FROM attachedDB.myTable
DETACH attachedDB

Then just query allDeviceTable.

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

Re: [sqlite] Preserving column size

[Jean-Denis Muys]

On 3/29/10 23:10 , "Tim Romano"  wrote:

> On 3/29/2010 4:19 PM, Kevin M. wrote:
>> I have a C/C++ application in which I want to store data from a struct into a
>> table (using SQLite 3.6.23) and later retrieve data from the table and store
>> it back in the struct.  But, I need a general interface for this as there are
>> many types of structs used.
>>
> 
> Couldn't you convert the structs to JSON format and store them in a TEXT
> field?
> There are a number of C++ JSON libraries listed here: http://www.json.org/
> 

Since it's C++, an another option (a good one IMHO) is to use the Boost
serialization facility.

See http://www.boost.org and more specifically:
http://www.boost.org/doc/libs/1_42_0/libs/serialization/doc/index.html

I quote:

Here, we use the term "serialization" to mean the reversible deconstruction
of an arbitrary set of C++ data structures to a sequence of bytes. Such a
system can be used to reconstitute an equivalent structure in another
program context. Depending on the context, this might used implement object
persistence, remote parameter passing or other facility. In this system we
use the term "archive" to refer to a specific rendering of this stream of
bytes. This could be a file of binary data, text data, XML, or some other
created by the user of this library.

<\quote>

"some other" could be a SQLite repository.

FWIW, Boost is a widely established C++ general library with enough
influence the next C++ standard library will borrow a lot from it.

JD

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