support for custom indexes

[wanderer]

It would be nice to be able to specify custom indexes. so for example index: "customIndex" the string customIndex would get passed along to the adapter.

If the index was a bool then the current behavior would hold.

[particlebanana]

I'm cool with that. Any thoughts on how we can do composite indexes? I struggled with that in the initial build and couldn't think of a nice way to express it in a collection so I left it out.

[wanderer]

yeah composites are tricky. I would be happy a new option indexes which could be an array of objects. Maybe like so..

indexes:[
//a composite index
{
   attributes:  ['attribute1', 'attribute2' ...]
   unique: boolean
},

But then what if we needed a composite index With custom indexes?

indexes:[
//a composite index
{
   attributes:  [
        {
            name: 'attribute1',
            type: '2dsphere'
        },
        {
            name: 'attribute1',
            type: 'hash'
        }
   ]
   unique: boolean
}
]

A bit messy but i think anyway you do its going to get a bit ugly. What do you think?

[sschepens]

what about having support for concurrent indexes (postgres) or background (mongo), etc?

[CWyrtzen]

Contribution Guide | Stackoverflow | Google Group | Trello | IRC | Build Status

[RWOverdijk]

What happened with this? It's been closed but I have no idea if this got implemented or not.

Update: If not, I'd like to propose allowing unique to be a string. The string will be used for the index key, making it possible to group them. This would apply to both index and unique.

[CWyrtzen]

It will eventually be pushed to Trello as a FR.

[0xgeert]

I've seen multiple issues closed like this the past days. I realize getting a low 'open issue count' is something to strive for, but closing without any back-and-forth really isn't the solution.

May I suggest to at least leave a note as to why an issue is going to be closed? Moreover, if the issue is moved to Trello as a FR, how about linking to the Trello Card of the FR before closing, so the issue isn't lost in void?

On Tue, Jun 17, 2014 at 9:45 PM, Courtney Wyrtzen notifications@github.com wrote:

It will eventually be pushed to Trello as a FR.

— Reply to this email directly or view it on GitHub https://github.com/balderdashy/waterline/issues/109#issuecomment-46355686 .

[CWyrtzen]

@gebrits we understand your concern and possible frustration. Thanks for your participation in the support community! This particular issue did have some discussion but, as you see was referenced in January and then trailed off. As you probably already know, closed issues are still searchable. Also, you are welcome to reopen ANY issue and restart a discussion if you feel we have closed it prematurely. That gets the conversation going again. On other issues you have seen, we do our best to reach out to the OP before we close up but that doesn't mean it can't be reopened.

https://trello.com/c/4ApPbu5C

Contribution Guide | Stackoverflow | Google Group | Trello | IRC | Build Status

[CWyrtzen]

Also, the FR milestone stays intact so they can be searched that way, too. (If you find one where this is not the case it was just a clerical error)~

[tjwebb]

When I google for waterline issues or features, I never see Trello in the search results. I always see github. It's annoying and confusing to have two places to discuss and report issues and features.

Morever, the link you posted is returning 404 for me: https://trello.com/c/4ApPbu5C

[RWOverdijk]

The link isn't working indeed, verified. It's probably a private board, or it has been moved by now.

@tjwebb Cards on Trello can be moved / renamed etc. So it doesn't make sense to show up in google. I do agree that it's annoying to keep track of these things in two different places.

[CWyrtzen]

@tjwebb and @RWOverdijk YES, good catch. We recently stopped using the Trello board and my new signature as of a few days ago removes this as an option. We are still honing the best way to respond to the needs of the community. Right now IRC Channel and Stack for support questions and GitHub for FR and Bugs are our main forums.

I know it can be frustrating when you need immediate help. I can tell you that we work as diligently as possible to respond in a timely manner. The needs of the community are important to us~

Sails v0.10 is here: Upgrade | Contribution Guide | Stackoverflow | Google Group | IRC | Build Status

[RWOverdijk]

To anyone googling and ending up here, I recommend IRC. I'm active at least 10 hours a day (work week, weekends a lot, too) and there are others that are really actively there as well.

I've decided to start blogging about FAQ in the channel as an archive.

[tjwebb]

IRC communication is ephemeral, which has the effect of making an ostensibly "public" conversation inaccessible to everyone who doesn't happen to be right there on that IRC channel at that time. This is especially problematic for a global community spanning many time zones. It leaves people out of the conversation.

This issue right here is certainly a feature request. Continuing the discussion here ensures that Googlers who end up here end up in the discussion, which is probably what they wanted anyway.

[CWyrtzen]

@tjwebb this is marked as a FR for all posterity (truly, I'm not just being snarky). Closed issues show up in search results. Once a member of the core team has marked it as a FR, we close it so that we can see that someone has addressed and discussed it (see @particlebanana comments above, plus he marked it as a FR so that helps me know what to do with it). The discussion can always continue.

We are working out a permanent system to log FR. In fact, some of them may have been implemented in the new release so we now have that as part of our plan, to see what's been implemented and what lies ahead.

The FR system is in place but we are smoothing out the kinks and eventually we will ask people to use it to log their own FR.

Is there anything else I can do for you? That's what I am here for.

[tjwebb]

Nope, I'm just following this issue. Thanks

[CWyrtzen]

Just FYI, this Trello card was in the archive - it may have been moved and that's why the link was broken but we did have it logged at one point (that system didn't develop - we're working on logging them another way)

just curious to see if you can access this: https://trello.com/c/lLXdNXtm (pretty sure it wasn't made public yet or it has been made private since it was created)

[tjwebb]

Yea, in postgres at least, there's no possible way to re-create this:

  CONSTRAINT mytable_pkey PRIMARY KEY (mytable_id, mytable_foo, mytable_bar),

It's kind of a problem.

[radkodinev]

Guys, is there any progress on this?

[CWyrtzen]

The current system for handling FR is here: https://github.com/balderdashy/sails/blob/master/ROADMAP.md

[leejt489]

+1, would like to see this opened back up.

If it were implemented for primary keys as well as any index, how should the model be referenced in an association?

[ArdentZeal]

+1 for composite keys

[lebarde]

To add my word, I currently want to inherit a model. I assume it is not possible for the moment, but raw SQL in the model definition would do it, see this post on stackoverflow.

I am therefore following the evolution of this thread!

[m-bodmer]

+1

[devinivy]

+1

[vinaymayar]

+1

[CWyrtzen]

Noted~

[CWyrtzen]

I did some hunting for you all and this is the word: for now that is something you can't model in the model definition. You can always add it manually or in your bootstrap. We would accept a pull request for it though.

[devinivy]

Thanks, @CWyrtzen . As part of the API change for this, perhaps the dominant option should have a particular effect; pivot tables typically ought to have a compound index (in the case of a SQL table) where one side is considered dominant. The first column in a compound key governs which join direction is more performant. Maybe this should go in sails-mysql. I guess the main point is that these compound indices should be implemented as ordered lists of columns rather than unordered sets of columns.

[arcseldon]

+1 Workarounds and hacks aside, there is a sizeable gap when implementing non-trivial domain models with waterline without this feature. Using sails-mysql adapter, and must have guarantees around combinations of attributes being unique composite keys. Don't wish to debate where the responsibility for this feature lies, only that it is being implemented. @CWyrtzen - Thanks for the great work to date on waterline, mostly a pleasure to work with. Please can you clarify your last comment - a solution is still definitely being provided? The community seems pretty adamant this a must have feature from a requirements perspective. Are you suggesting a technical issue preventing its provision?

[albertpeiro]

+1 I'd love to see this in waterline and use it from sails. Thank you for outstanding work.

[MarijnMensinga]

+1

[ghost]

+1

Is there a bounty for this? I'd like for composite key support to be made a priority.

[albertpeiro]

@arcseldon agree with the size of the gap. A simple constraint on a key pair turns into a series of checks in the shape of promises throughout the entire logic of the api in the best case. The only other way I could think of working around this would be: add the ability to run additional low-level/wrapped database commands after sails magic "alters" the database on lift. This approach could be connected too with how we solve data migrations for production - which by the way I think it's the biggest off-putter when you're new to sails.

[dzcpy]

+1 for composite keys

[dmarcelino]

+1 for composite keys and I'm OK with @wanderer's suggestion above.

[calidion]

+1 for composite keys

[franc23]

+1 for composite keys

[byCedric]

+1 for the ability to use composite keys

[cyberwookiee]

Data integrity should be maintained at the lowest possible level

+1 for composite keys

[franc23]

@particlebanana or any collaborator: I have hacked into the mysql adapter (sails v 0.11.0) to get composite keys to work with get/put calls (post not being an issue). I make use of the unique attribute to modify the adapter.js methods and a few other methods in 6 files total. Maybe totally wrong in my approach, but thought I'd share this with the team to see if it helps. Would be happy to help as much as I can if someone can guide me on any waterline standards, etc.

Uploaded the files with appropriate folder paths here -http://www.megafileupload.com/K1/node_modules.zip

[albertkim]

I've hacked around this issue by having big beforeCreate functions in my models involving composite keys. Thankfully none of my tables require more than 2 composites, but if there are any more I don't think this method will hold up. Here's a +1 from me.

I look forward to seeing how this gets implemented.

[elexx]

I am also struggling with not having composite keys available. +1 from me.

[Salakar]

I created a sails hook to give advanced indexing options for models that use the sails-mongo adapter.

Supports all mongo indexing options. Just have to add an 'indexes' property to your model!

https://www.npmjs.com/package/sails-hook-mongoat

[rafacapa]

+1

[BLamy]

+1 This is really throwing a huge wrench in my database design.

[devinivy]

@BLamy you can always manually create indexes or use migration tools to do so.

[BLamy]

@devinivy It's not just the indexes. It's the primary keys. Is it possible to create a composite primary key using migration tools or do it behind the scenes and not break sails-mysql?

[devinivy]

Yes– if migrate is set to safe or create then you should be fine. alter and drop are not suitable for production anyway :+1:

[sailsbot]

Thanks for posting, @wanderer. I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

• review our contribution guide to make sure this submission meets our criteria (only verified bugs with documented features, please; no questions, commentary, or bug reports about undocumented features or unofficial plugins)
• create a new issue with the latest information, including updated version details with error messages, failing tests, and a link back to [the original issue](). This allows GitHub to automatically create a back-reference for future visitors arriving from search engines.

Thanks so much for your help!