search

pardom-zz / ActiveAndroid

Active record style SQLite persistence for Android
http://www.activeandroid.com
4.7k stars 1.03k forks source link

foregin key mismatch !! #188

Open yazeed44 opened 10 years ago

yazeed44 commented 10 years ago

Hi

i have been trying to add items to the category but i keep getting the foregin key mismatch exception

that's the log cat

02-21 00:33:41.012: D/dalvikvm(1456): GC_FOR_ALLOC freed 346K, 13% free 2841K/3252K, paused 43ms, total 44ms 02-21 00:33:41.572: E/SQLiteLog(1456): (1) foreign key mismatch 02-21 00:33:41.612: D/dalvikvm(1456): GC_FOR_ALLOC freed 453K, 16% free 2902K/3420K, paused 32ms, total 33ms 02-21 00:33:41.622: E/SQLiteDatabase(1456): Error inserting Name=google Category=12 02-21 00:33:41.622: E/SQLiteDatabase(1456): android.database.sqlite.SQLiteException: foreign key mismatch (code 1): , while compiling: INSERT INTO Items(Name,Category) VALUES (?,?) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at com.activeandroid.Model.save(Unknown Source) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at com.example.activeandoridtest.MainActivity.onCreate(MainActivity.java:30) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.app.Activity.performCreate(Activity.java:5231) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2159) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2245) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.app.ActivityThread.access$800(ActivityThread.java:135) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.os.Handler.dispatchMessage(Handler.java:102) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.os.Looper.loop(Looper.java:136) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at android.app.ActivityThread.main(ActivityThread.java:5017) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at java.lang.reflect.Method.invokeNative(Native Method) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at java.lang.reflect.Method.invoke(Method.java:515) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595) 02-21 00:33:41.622: E/SQLiteDatabase(1456): at dalvik.system.NativeStart.main(Native Method) 02-21 00:33:41.632: E/SQLiteLog(1456): (1) foreign key mismatch 02-21 00:33:41.642: E/SQLiteDatabase(1456): Error inserting Name=github Category=12 02-21 00:33:41.642: E/SQLiteDatabase(1456): android.database.sqlite.SQLiteException: foreign key mismatch (code 1): , while compiling: INSERT INTO Items(Name,Category) VALUES (?,?) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at com.activeandroid.Model.save(Unknown Source) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at com.example.activeandoridtest.MainActivity.onCreate(MainActivity.java:33) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.app.Activity.performCreate(Activity.java:5231) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2159) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2245) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.app.ActivityThread.access$800(ActivityThread.java:135) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.os.Handler.dispatchMessage(Handler.java:102) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.os.Looper.loop(Looper.java:136) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at android.app.ActivityThread.main(ActivityThread.java:5017) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at java.lang.reflect.Method.invokeNative(Native Method) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at java.lang.reflect.Method.invoke(Method.java:515) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595) 02-21 00:33:41.642: E/SQLiteDatabase(1456): at dalvik.system.NativeStart.main(Native Method) 02-21 00:33:41.642: E/SQLiteLog(1456): (1) foreign key mismatch 02-21 00:33:41.652: E/SQLiteDatabase(1456): Error inserting Name=yahoo Category=12 02-21 00:33:41.652: E/SQLiteDatabase(1456): android.database.sqlite.SQLiteException: foreign key mismatch (code 1): , while compiling: INSERT INTO Items(Name,Category) VALUES (?,?) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at com.activeandroid.Model.save(Unknown Source) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at com.example.activeandoridtest.MainActivity.onCreate(MainActivity.java:36) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.app.Activity.performCreate(Activity.java:5231) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2159) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2245) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.app.ActivityThread.access$800(ActivityThread.java:135) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.os.Handler.dispatchMessage(Handler.java:102) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.os.Looper.loop(Looper.java:136) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at android.app.ActivityThread.main(ActivityThread.java:5017) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at java.lang.reflect.Method.invokeNative(Native Method) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at java.lang.reflect.Method.invoke(Method.java:515) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595) 02-21 00:33:41.652: E/SQLiteDatabase(1456): at dalvik.system.NativeStart.main(Native Method) 02-21 00:33:41.952: D/gralloc_goldfish(1456): Emulator without GPU emulation detected. 02-21 00:33:48.222: I/Choreographer(1456): Skipped 69 frames! The application may be doing too much work on its main thread.

and that's the code for the main activity

@Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState);

    setContentView(R.layout.activity_main);

    final Category sites = new Category("sites");
    sites.save();

    Item google = new Item("google",sites);
    google.save();

    Item gitHub = new Item("github",sites);
    gitHub.save();

    Item yahoo = new Item("yahoo",sites);
    yahoo.save();

    Button showToast = (Button)findViewById(R.id.showToast);

    showToast.setOnClickListener(new OnClickListener(){

        @Override
        public void onClick(View arg0) {
            List<Item> idRes = sites.items();

            if (idRes != null){
                Toast.makeText(getApplicationContext(), idRes.size() + "", Toast.LENGTH_SHORT).show();}
                //getRandom().save();

        }

    });

}

the code for item class

@Table(name="Items",id = "items_id") public class Item extends Model {

 @Column(name = "Name", index = true)
    public String name;

    @Column(name = "Category",index = true)
    public Category category;

    public Item(){
        super();
}

    public Item(String name, Category category){
        super();
        this.name = name;
        this.category = category;
}

}

the code for category class

@Table(name = "Categories",id = "category_id") public class Category extends Model { @Column(name = "Name") public String name;

        public Category(){
            super();
        }

        public Category(String namePar){
        super();
        this.name = namePar;
        }

        public List<Item> items() {
            return getMany(Item.class, "Category");
        }
    }

Thank you

rusfearuth commented 10 years ago

Does anyone solution?

yazeed44 commented 10 years ago

you should just use SQL and forgot about active android

rusfearuth commented 10 years ago

@yazeed44, this is not solution I think. I think problem in id in Tablet annotation.

vonloxley commented 10 years ago

Could you post the schema of the created database? (".schema"-command in sqlite3.)

rusfearuth commented 10 years ago

@vonloxley I don't have access to db. How could I do that?

rusfearuth commented 10 years ago

I've found device and get access to database.

Let me introduce my problem. I have two entities. The first entity is day. Day has title and long for unix timestamp. Day may have photos. In this case we have one to many between day entity and day_photo entity.

@Table(name = "day_info", id = BaseColumns._ID)
public class Day extends Model
{
    @Column
    private String title;

    @Column(notNull = true)
    private Long date;

// here getters and setters
}

@Table(name = "day_info_photo")
public class DayPhoto extends Model
{
        @Column(notNull = true)
         private String photo;

        @Column(name = "Day", notNull = true, onDelete = Column.ForeignKeyAction.CASCADE)
        private Day day;

// here getters and setters
}

The day_info_photo schema has bug. Current schema looks like the next:

CREATE TABLE day_info_photo ( 
    Id    INTEGER PRIMARY KEY AUTOINCREMENT,
    Day   INTEGER NOT NULL ON CONFLICT FAIL
                  REFERENCES day_info ( Id ) ON DELETE CASCADE
                                               ON UPDATE NO ACTION,
    photo TEXT    NOT NULL ON CONFLICT FAIL 
);

but it should be:

CREATE TABLE day_info_photo ( 
    Id    INTEGER PRIMARY KEY AUTOINCREMENT,
    Day   INTEGER NOT NULL ON CONFLICT FAIL
                  REFERENCES day_info ( Id ) ON DELETE CASCADE
                                               ON UPDATE NO ACTION,
    photo TEXT    NOT NULL ON CONFLICT FAIL 
);

I mean, it should has references on day_info (_id) instread of day_info(Id)

vonloxley commented 10 years ago

Yep. You could try changing https://github.com/hube/ActiveAndroid/blob/master/src/com/activeandroid/util/SQLiteUtils.java#L309 from

definition.append("("+tableInfo.getIdName()+")");

to

definition.append("("+Cache.getTableInfo((Class<? extends Model>) type).getIdName()+")");

Prima vista, that’s the only thing that comes to mind.

rusfearuth commented 10 years ago

Thanks @vonloxley

I've added fix and sent pull request https://github.com/pardom/ActiveAndroid/pull/251

sailrish commented 9 years ago

In my app/build.gradle I have

compile 'com.michaelpardo:activeandroid:3.1.0-SNAPSHOT'

But looking at Model.java, it appears I don't have the fix for this issue (from pull request #251). How do I get the latest version of active android using gradle?

gustahrodrigues commented 9 years ago

@sailrish i'm looking for this too. Do you have some news about it?

sailrish commented 9 years ago

@gustahrodrigues it appears that this change was done in a forked repo, but was never actually incorporated. It does concern me that active android was last released in October 2014, and there's been no change since, and this particular PR #251 was never merged. Sounds like it is time to find an alternative to active android? (like Sugar ORM, or ORMLite). My first attempt is to try to get around this issue by avoiding custom ID fields. If I can't do that, I might try to merge this branch into my own fork of the repo and use that in my android project. But I don't really like the sound of that.

ImMathanR commented 8 years ago

@sailrish Have you figgered out any way around this issue.

sailrish commented 8 years ago

@ImMathanR Sorry its been a while, let me take a deeper look at how I worked around the problem. I'm still using the official 3.1.0-SNAPSHOT release of active android, no private or other forks.

ImMathanR commented 8 years ago

@sailrish Thanks. Please let me know.

gustahrodrigues commented 8 years ago

Thanks @sailrish . I go back to change normal id, instead of custom id.