Duplicate Index Names

I have what appears to be a pretty simple relation mapping, but it keeps failing during sync. This is due to:

{ [Error: SQLITE_ERROR: index storyboard_id_index already exists] errno: 1, code: 'SQLITE_ERROR' }

It seems that a model can only ever be part of one many to many relationship! Is there any way I can give custom names to the indices for each relationship? Or some other way I can avoid this error? Code below:

database.js

var orm = require('orm'),
    modts = require('orm-timestamps'),
    when = require('when');

var db;

module.exports = when.promise(function(resolve, reject, notify) {
    if (db) {
        resolve(db);
    }

    db = orm.connect('sqlite://test.db');

    db.on('connect', function(err) {
        var Medium,
            Storyboard,
            User,
            Panel;

        if (err) {
            throw err;
        }

        db.use(modts, {
            createdProperty: 'createdAt',
            modifiedProperty: 'modifiedAt',
            dbtype: { type: 'date', time: true },
            now: function() { return new Date(); },
            persist: true
        });

        // Medium
        Medium = db.define('medium', {
            name     : String,
            type     : ['video', 'image'],
            duration : Number,
            width    : { type: 'integer' },
            height   : { type: 'integer' },
            uri      : String
        }, {
            methods: {
                self: function() {
                    return '/api/medium/' + this.id;
                }
            },
            timestamp: true
        });

        // Panel
        Panel = db.define('panel', {
            start  : Number,
            end    : Number,
            height : { type: 'integer' }
        }, {
            methods: {
                self: function() {
                    return '/api/panel/' + this.id;
                },
                getThumbnail: function(callback) {
                    this.getMedium(function(err, medium) {
                        if (err) {
                            callback(err);
                        } else if (!medium) {
                            // return default thumbnail
                        } else {
                            // return thumbnail of first panel
                            medium.getThumbnail(callback);
                        }
                    });
                }
            },
            timestamp: true
        });

        // Storyboard
        Storyboard = db.define('storyboard', {
            name: String
        }, {
            methods: {
                self: function() {
                    return '/api/storyboard/' + this.id;
                },
                getThumbnail: function(callback) {
                    this.getPanels(function(err, panels) {
                        if (err) {
                            callback(err);
                        } else if (!panels.length) {
                            // return default thumbnail
                        } else {
                            // return thumbnail of first panel
                            panels[0].getThumbnail(callback);
                        }
                    });
                }
            },
            timestamp: true
        });

        // User
        User = db.define('user', {
            name: String
        }, {
            methods: {
                self: function() {
                    return '/api/user/' + this.id;
                }
            },
            timestamp: true
        });

        // Define relationships.
        Medium.hasOne('thumbnail', Medium);

        Panel.hasOne('storyboard', Storyboard, { reverse: 'panels' });
        Panel.hasOne('medium', Medium, {}, { reverse: 'panels' });
        Panel.hasOne('selectedBy', User, {}, { reverse: 'selectedPanels' });

        Storyboard.hasMany('media', Medium, {}, { reverse: 'storyboards' });

        User.hasMany('media', Medium, {
            relationship: ['owner', 'collaborator', 'viewer']
        }, { reverse: 'users' });
        User.hasMany('storyboards', {
            relationship: ['owner', 'collaborator', 'viewer']
        }, { reverse: 'users' });

        resolve(db);
    });
});

bootstrap.js

var when = require('when'),
    keys = require('when/keys'),

    database = require('./server/database');

function dropModels(db) {
    console.log('Dropping models...');

    return keys.map(db.models, function(model) {
        var deferred = when.defer();

        model.drop(function (err) {
            if (err) {
                deferred.rejct('err');
            } else {
                deferred.resolve(true);
            }
        });

        return deferred.promise;
    });
}

function bootstrapModels(db) {
    console.log('Bootstrapping models...');

    return keys.map(db.models, function(model) {
        var deferred = when.defer();

        model.sync(function (err) {
            if (err) {
                deferred.reject(err);
            } else {
                deferred.resolve(true);
            }
        });

        return deferred.promise;
    });
}


database.then(function(db) {
    dropModels(db).then(function() {
        console.log('Successfully dropped models.');

        bootstrapModels(db).then(function() {
            console.log('Successfully bootstrapped models.');
        }, function(err) {
            console.log('Failed to bootsrap models:', err);
        });
    }, function(err) {
        console.log('Failed to drop models:', err);
    });
});

It appears that models can't be synced in parallel. You need to do it in series:

var sequence = require('when/sequence');

function bootstrapModels(db) {
  console.log('Bootstrapping models...');

  var createTable = function (model) {
    var deferred = when.defer();

    model.sync(function (err) {
      if (err) deferred.reject(err);
      else deferred.resolve(true);
    });
    return deferred.promise;
  }

  var jobs = Object.keys(db.models).map(function (k) {
    return createTable.bind(null, db.models[k]);
  });

  return sequence(jobs);
}

If you change your connect call to: orm.connect('sqlite://test.db?debug=true'); the reason will become apparent.

Thanks for the help! I didn't know about ?debug=true on the connector, very useful.

Changing from asynchronous to synchronous didn't solve my problem though. It still errors on attempting to create a duplicate index. I have created a simplified test case to exhibit this behavior:

There exists As, Bs and Cs.
A has a many to many relationship with B.
A has a many to many relationship with C.

This setup results in:

SQLITE_ERROR: index a_id_index already exists

The code:

var orm = require('orm'),
    when = require('when'),
    sequence = require('when/sequence'),

    db = orm.connect('sqlite://test.db?debug=true');

function dropModel(model) {
    var deferred = when.defer();

    model.drop(function(err) {
        if (err) {
            deferred.reject(err);
        } else {
            deferred.resolve(true);
        }
    });

    return deferred.promise;
}

function dropModels() {
    var tasks = Object.keys(db.models).map(function(key) {
        return dropModel.bind(null, db.models[key]);
    });

    return sequence(tasks);
}


function bootstrapModel(model) {
    var deferred = when.defer();

    model.sync(function(err) {
        if (err) {
            deferred.reject(err);
        } else {
            deferred.resolve(true);
        }
    });

    return deferred.promise;
}

function bootstrapModels() {
    var tasks = Object.keys(db.models).map(function(key) {
        return bootstrapModel.bind(null, db.models[key]);
    });

    return sequence(tasks);
}

db.on('connect', function(err) {
    var A,
        B,
        C;

    A = db.define('a', { name: String });

    B = db.define('b', { name: String });

    C = db.define('c', { name: String });

    A.hasMany('bees', B, {}, { reverse: 'eighs' });
    A.hasMany('cees', C, {}, { reverse: 'eighs' });

    dropModels(db).then(function() {
        console.log('Successfully dropped models.');

        console.log('Bootstrapping models...');

        bootstrapModels(db).then(function() {
            console.log('Successfully bootstrapped models.');
        }, function(err) {
            console.log('Failed to bootsrap models:', err);
        });
    }, function(err) {
        console.log('Failed to drop models:', err);
    });
});

When I tested I simplified the code such that I only had 1 association and I hit the sync problem.
It appears the index thing is separate all together. Only happens in sqlite. Taking a look..

Please install the latest version (2.1.12). The issue should be resolved.

I forgot to commit the test case along with the release. Added it in a subsequent commit.
Thanks for the sample code.

That worked great! Thank you for the quick fix.

Navigation Menu

Repository navigation

Duplicate Index Names #499

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Duplicate Index Names #499

Description

Activity

dxg commented on May 19, 2014

nathanjd commented on May 20, 2014

dxg commented on May 20, 2014

dxg commented on May 20, 2014

nathanjd commented on May 20, 2014

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions