On Tue, Sep 15, 2009 at 7:04 PM, Tito Ciuro <tci...@mac.com> wrote:
> On Sep 15, 2009, at 6:59 PM, P Kishor wrote:
>
>> On Tue, Sep 15, 2009 at 8:50 PM, Tito Ciuro <tci...@mac.com> wrote:
>>> On Sep 15, 2009, at 6:42 PM, P Kishor wrote:
>>>
>>>> Could it be that those seemingly identical multiple rows actually
>>>> have
>>>> trailing spaces or some other non-visible character? Check for their
>>>> length.
>>>
>>> Here we go:
>>>
>>>> sqlite> SELECT clientName, entityName, length(entityName) FROM
>>>> MyDBState
>>>> ORDER BY entityName;
>>>> com.apple.AddressBook|com.apple.contacts.CalendarURI|30
>>>> com.apple.AddressBook|com.apple.contacts.Contact|26
>>>> com.apple.AddressBook|com.apple.contacts.Date|23
>>>> com.apple.AddressBook|com.apple.contacts.Email Address|32
>>>> com.apple.AddressBook|com.apple.contacts.Group|24
>>>> com.apple.AddressBook|com.apple.contacts.IM|21
>>>> com.apple.AddressBook|com.apple.contacts.ImageTransformationInfo|42
>>>> com.apple.AddressBook|com.apple.contacts.ImageTransformationInfo|42
>>>> com.apple.AddressBook|com.apple.contacts.Phone Number|31
>>>> com.apple.AddressBook|com.apple.contacts.Phone Number|31
>>>> com.apple.AddressBook|com.apple.contacts.Related Name|31
>>>> com.apple.AddressBook|com.apple.contacts.Related Name|31
>>>> com.apple.AddressBook|com.apple.contacts.SmartGroup|29
>>>> com.apple.AddressBook|com.apple.contacts.SmartGroup|29
>>>> sqlite>
>>>
>>
>> punk...@lucknow ~/Desktop/stuff$sqlite3
>> SQLite version 3.6.11
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> CREATE TABLE MyDBState (clientName TEXT, entityName
>> TEXT,propertyNames BLOB, PRIMARY KEY (clientName, entityName));
>> sqlite> INSERT INTO MyDBState (clientName, entityName) VALUES
>> ('com.apple.AddressBook', 'com.apple.contacts.SmartGroup');
>> sqlite> INSERT INTO MyDBState (clientName, entityName) VALUES
>> ('com.apple.AddressBook', 'com.apple.contacts.SmartGroup');
>> SQL error: columns clientName, entityName are not unique
>>
>> SQLite should stop you from inserting duplicate PK right away, like
>> above.
>>
>>
>> By the way, in your original post, you gave a different name for your
>> table... see below --
>>
>>> CREATE TABLE MyDBState (clientName TEXT, entityName TEXT,
>>> propertyNames BLOB, PRIMARY KEY (clientName, entityName))
>>>
>>> If I output the data using sqlite3, I get:
>>>
>>> sqlite> SELECT clientName, entityName FROM SyncState ORDER BY
>>> entityName;
>>
>> Where did SyncState come from? Is that different from MyDBState? Or,
>> was that just a typo?
>>
>>
>> Maybe some of the folks knowing more about the innards of the software
>> can help. Which version are you using?
>
> It was a typo. I'm using Mac OS X 10.6.1, so the version seems to be
> 3.6.12
>
> Yeah, it's puzzling how this can be happening. We really don't know
> why... we're looking.Do you have any reason to believe that your database had exactly a single corruption? My experience is that once you've found one bit of corrupt data, it's highly likely that you'll find others. Earlier, you said your index was corrupt. The index is how SQLite enforces things like primary key uniqueness, so if your index is corrupt, you can lose that uniqueness guarantee. -scott _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
