Duplicate Index Names

[nathanjd]

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);
    });
});

[dxg]

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.

[nathanjd]

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);
    });
});

[dxg]

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..

[dxg]

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.

[nathanjd]

That worked great! Thank you for the quick fix.