diff options
Diffstat (limited to 'doc/development')
| -rw-r--r-- | doc/development/README.md | 11 | ||||
| -rw-r--r-- | doc/development/architecture.md | 2 | ||||
| -rw-r--r-- | doc/development/benchmarking.md | 69 | ||||
| -rw-r--r-- | doc/development/ci_setup.md | 2 | ||||
| -rw-r--r-- | doc/development/doc_styleguide.md | 270 | ||||
| -rw-r--r-- | doc/development/gotchas.md | 103 | ||||
| -rw-r--r-- | doc/development/migration_style_guide.md | 8 | ||||
| -rw-r--r-- | doc/development/scss_styleguide.md | 194 | ||||
| -rw-r--r-- | doc/development/sql.md | 219 |
9 files changed, 800 insertions, 78 deletions
diff --git a/doc/development/README.md b/doc/development/README.md index d5bf166ad32..1b281809afc 100644 --- a/doc/development/README.md +++ b/doc/development/README.md @@ -1,11 +1,12 @@ # Development - [Architecture](architecture.md) of GitLab -- [Shell commands](shell_commands.md) in the GitLab codebase -- [Rake tasks](rake_tasks.md) for development - [CI setup](ci_setup.md) for testing GitLab +- [Gotchas](gotchas.md) to avoid +- [How to dump production data to staging](db_dump.md) +- [Migration Style Guide](migration_style_guide.md) for creating safe migrations +- [Rake tasks](rake_tasks.md) for development +- [Shell commands](shell_commands.md) in the GitLab codebase - [Sidekiq debugging](sidekiq_debugging.md) +- [SQL guidelines](sql.md) for SQL guidelines - [UI guide](ui_guide.md) for building GitLab with existing css styles and elements -- [Migration Style Guide](migration_style_guide.md) for creating safe migrations -- [How to dump production data to staging](dump_db.md) -- [Benchmarking](benchmarking.md) diff --git a/doc/development/architecture.md b/doc/development/architecture.md index 6101a71a8de..12e33406cb6 100644 --- a/doc/development/architecture.md +++ b/doc/development/architecture.md @@ -42,7 +42,7 @@ Gitlab-shell communicates with Sidekiq via the “communication board” (Redis) ## System Layout -When referring to ~git in the pictures it means the home directory of the git user which is typically /home/git. +When referring to `~git` in the pictures it means the home directory of the git user which is typically /home/git. GitLab is primarily installed within the `/home/git` user home directory as `git` user. Within the home directory is where the gitlabhq server software resides as well as the repositories (though the repository location is configurable). diff --git a/doc/development/benchmarking.md b/doc/development/benchmarking.md deleted file mode 100644 index 88e18ee95f9..00000000000 --- a/doc/development/benchmarking.md +++ /dev/null @@ -1,69 +0,0 @@ -# Benchmarking - -GitLab CE comes with a set of benchmarks that are executed for every build. This -makes it easier to measure performance of certain components over time. - -Benchmarks are written as RSpec tests using a few extra helpers. To write a -benchmark, first tag the top-level `describe`: - -```ruby -describe MaruTheCat, benchmark: true do - -end -``` - -This ensures the benchmark is executed separately from other test collections. -It also exposes the various RSpec matchers used for writing benchmarks to the -test group. - -Next, lets write the actual benchmark: - -```ruby -describe MaruTheCat, benchmark: true do - let(:maru) { MaruTheChat.new } - - describe '#jump_in_box' do - benchmark_subject { maru.jump_in_box } - - it { is_expected.to iterate_per_second(9000) } - end -end -``` - -Here `benchmark_subject` is a small wrapper around RSpec's `subject` method that -makes it easier to specify the subject of a benchmark. Using RSpec's regular -`subject` would require us to write the following instead: - -```ruby -subject { -> { maru.jump_in_box } } -``` - -The `iterate_per_second` matcher defines the amount of times per second a -subject should be executed. The higher the amount of iterations the better. - -By default the allowed standard deviation is a maximum of 30%. This can be -adjusted by chaining the `with_maximum_stddev` on the `iterate_per_second` -matcher: - -```ruby -it { is_expected.to iterate_per_second(9000).with_maximum_stddev(50) } -``` - -This can be useful if the code in question depends on external resources of -which the performance can vary a lot (e.g. physical HDDs, network calls, etc). -However, in most cases 30% should be enough so only change this when really -needed. - -## Benchmarks Location - -Benchmarks should be stored in `spec/benchmarks` and should follow the regular -Rails specs structure. That is, model benchmarks go in `spec/benchmark/models`, -benchmarks for code in the `lib` directory go in `spec/benchmarks/lib`, etc. - -## Underlying Technology - -The benchmark setup uses [benchmark-ips][benchmark-ips] which takes care of the -heavy lifting such as warming up code, calculating iterations, standard -deviation, etc. - -[benchmark-ips]: https://github.com/evanphx/benchmark-ips diff --git a/doc/development/ci_setup.md b/doc/development/ci_setup.md index f9b48868182..6776d9b083f 100644 --- a/doc/development/ci_setup.md +++ b/doc/development/ci_setup.md @@ -26,7 +26,7 @@ We use [these build scripts](https://gitlab.com/gitlab-org/gitlab-ci/blob/master # Build configuration on [Semaphore](https://semaphoreapp.com/gitlabhq/gitlabhq/) for testing the [GitHub.com repo](https://github.com/gitlabhq/gitlabhq) - Language: Ruby -- Ruby version: 2.1.2 +- Ruby version: 2.1.8 - database.yml: pg Build commands diff --git a/doc/development/doc_styleguide.md b/doc/development/doc_styleguide.md new file mode 100644 index 00000000000..187ec9e7b75 --- /dev/null +++ b/doc/development/doc_styleguide.md @@ -0,0 +1,270 @@ +# Documentation styleguide + +This styleguide recommends best practices to improve documentation and to keep +it organized and easy to find. + +## Naming + +- When creating a new document and it has more than one word in its name, + make sure to use underscores instead of spaces or dashes (`-`). For example, + a proper naming would be `import_projects_from_github.md`. The same rule + applies to images. + +## Text + +- Split up long lines, this makes it much easier to review and edit. Only + double line breaks are shown as a full line break in [GitLab markdown][gfm]. + 80-100 characters is a good line length +- Make sure that the documentation is added in the correct directory and that + there's a link to it somewhere useful +- Do not duplicate information +- Be brief and clear +- Unless there's a logical reason not to, add documents in alphabetical order +- Write in US English +- Use [single spaces][] instead of double spaces + +## Formatting + +- Use dashes (`-`) for unordered lists instead of asterisks (`*`) +- Use the number one (`1`) for ordered lists +- Use underscores (`_`) to mark a word or text in italics +- Use double asterisks (`**`) to mark a word or text in bold +- When using lists, prefer not to end each item with a period. You can use + them if there are multiple sentences, just keep the last sentence without + a period + +## Headings + +- Add only one H1 title in each document, by adding `#` at the beginning of + it (when using markdown). For subheadings, use `##`, `###` and so on +- Avoid putting numbers in headings. Numbers shift, hence documentation anchor + links shift too, which eventually leads to dead links. If you think it is + compelling to add numbers in headings, make sure to at least discuss it with + someone in the Merge Request +- When introducing a new document, be careful for the headings to be + grammatically and syntactically correct. It is advised to mention one or all + of the following GitLab members for a review: `@axil`, `@rspeicher`, + `@dblessing`, `@ashleys`, `@nearlythere`. This is to ensure that no document + with wrong heading is going live without an audit, thus preventing dead links + and redirection issues when corrected +- Leave exactly one newline after a heading + +## Links + +- If a link makes the paragraph to span across multiple lines, do not use + the regular Markdown approach: `[Text](https://example.com)`. Instead use + `[Text][identifier]` and at the very bottom of the document add: + `[identifier]: https://example.com`. This is another way to create Markdown + links which keeps the document clear and concise. Bonus points if you also + add an alternative text: `[identifier]: https://example.com "Alternative text"` + that appears when hovering your mouse on a link + +## Images + +- Place images in a separate directory named `img/` in the same directory where + the `.md` document that you're working on is located. Always prepend their + names with the name of the document that they will be included in. For + example, if there is a document called `twitter.md`, then a valid image name + could be `twitter_login_screen.png`. +- Images should have a specific, non-generic name that will differentiate them. +- Keep all file names in lower case. +- Consider using PNG images instead of JPEG. + +Inside the document: + +- The Markdown way of using an image inside a document is: + `` +- Always use a proper description for what the image is about. That way, when a + browser fails to show the image, this text will be used as an alternative + description +- If there are consecutive images with little text between them, always add + three dashes (`---`) between the image and the text to create a horizontal + line for better clarity +- If a heading is placed right after an image, always add three dashes (`---`) + between the image and the heading + +## Notes + +- Notes should be quoted with the word `Note:` being bold. Use this form: + + ``` + >**Note:** + This is something to note. + ``` + + which renders to: + + >**Note:** + This is something to note. + + If the note spans across multiple lines it's OK to split the line. + +## New features + +- Every piece of documentation that comes with a new feature should declare the + GitLab version that feature got introduced. Right below the heading add a + note: `_**Note:** This feature was introduced in GitLab 8.3_` +- If possible every feature should have a link to the MR that introduced it. + The above note would be then transformed to: + `_**Note:** This feature was [introduced][ce-1242] in GitLab 8.3_`, where + the [link identifier](#links) is named after the repository (CE) and the MR + number +- If the feature is only in GitLab EE, don't forget to mention it, like: + `_**Note:** This feature was introduced in GitLab EE 8.3_`. Otherwise, leave + this mention out + +## References + +- **GitLab Restart:** + There are many cases that a restart/reconfigure of GitLab is required. To + avoid duplication, link to the special document that can be found in + [`doc/administration/restart_gitlab.md`][doc-restart]. Usually the text will + read like: + + ``` + Save the file and [reconfigure GitLab](../administration/restart_gitlab.md) + for the changes to take effect. + ``` + If the document you are editing resides in a place other than the GitLab CE/EE + `doc/` directory, instead of the relative link, use the full path: + `http://doc.gitlab.com/ce/administration/restart_gitlab.html`. + Replace `reconfigure` with `restart` where appropriate. + +## Installation guide + +- **Ruby:** + In [step 2 of the installation guide](../install/installation.md#2-ruby), + we install Ruby from source. Whenever there is a new version that needs to + be updated, remember to change it throughout the codeblock and also replace + the sha256sum (it can be found in the [downloads page][ruby-dl] of the Ruby + website). + +[ruby-dl]: https://www.ruby-lang.org/en/downloads/ "Ruby download website" + +## API + +Here is a list of must-have items. Use them in the exact order that appears +on this document. Further explanation is given below. + +- Every method must have the REST API request. For example: + + ``` + GET /projects/:id/repository/branches + ``` + +- Every method must have a detailed + [description of the parameters](#method-description). +- Every method must have a cURL example. +- Every method must have a response body (in JSON format). + +### Method description + +Use the following table headers to describe the methods. Attributes should +always be in code blocks using backticks (`). + +``` +| Attribute | Type | Required | Description | +| --------- | ---- | -------- | ----------- | +``` + +Rendered example: + +| Attribute | Type | Required | Description | +| --------- | ---- | -------- | ----------- | +| `user` | string | yes | The GitLab username | + +### cURL commands + +- Use `https://gitlab.example.com/api/v3/` as an endpoint. +- Wherever needed use this private token: `9koXpg98eAheJpvBs5tK`. +- Always put the request first. `GET` is the default so you don't have to + include it. +- Use double quotes to the URL when it includes additional parameters. +- Prefer to use examples using the private token and don't pass data of + username and password. + +| Methods | Description | +| ------- | ----------- | +| `-H "PRIVATE-TOKEN: 9koXpg98eAheJpvBs5tK"` | Use this method as is, whenever authentication needed | +| `-X POST` | Use this method when creating new objects | +| `-X PUT` | Use this method when updating existing objects | +| `-X DELETE` | Use this method when removing existing objects | + +### cURL Examples + +Below is a set of [cURL][] examples that you can use in the API documentation. + +#### Simple cURL command + +Get the details of a group: + +```bash +curl -H "PRIVATE-TOKEN: 9koXpg98eAheJpvBs5tK" https://gitlab.example.com/api/v3/groups/gitlab-org +``` + +#### cURL example with parameters passed in the URL + +Create a new project under the authenticated user's namespace: + +```bash +curl -X POST -H "PRIVATE-TOKEN: 9koXpg98eAheJpvBs5tK" "https://gitlab.example.com/api/v3/projects?name=foo" +``` + +#### Post data using cURL's --data + +Instead of using `-X POST` and appending the parameters to the URI, you can use +cURL's `--data` option. The example below will create a new project `foo` under +the authenticated user's namespace. + +```bash +curl --data "name=foo" -H "PRIVATE-TOKEN: 9koXpg98eAheJpvBs5tK" "https://gitlab.example.com/api/v3/projects" +``` + +#### Post data using JSON content + +_**Note:** In this example we create a new group. Watch carefully the single +and double quotes._ + +```bash +curl -X POST -H "PRIVATE-TOKEN: 9koXpg98eAheJpvBs5tK" -H "Content-Type: application/json" --data '{"path": "my-group", "name": "My group"}' https://gitlab.example.com/api/v3/groups +``` + +#### Post data using form-data + +Instead of using JSON or urlencode you can use multipart/form-data which +properly handles data encoding: + +```bash +curl -X POST -H "PRIVATE-TOKEN: 9koXpg98eAheJpvBs5tK" -F "title=ssh-key" -F "key=ssh-rsa AAAAB3NzaC1yc2EA..." https://gitlab.example.com/api/v3/users/25/keys +``` + +The above example is run by and administrator and will add an SSH public key +titled ssh-key to user's account which has an id of 25. + +#### Escape special characters + +Spaces or slashes (`/`) may sometimes result to errors, thus it is recommended +to escape them when possible. In the example below we create a new issue which +contains spaces in its title. Observe how spaces are escaped using the `%20` +ASCII code. + +```bash +curl -X POST -H "PRIVATE-TOKEN: 9koXpg98eAheJpvBs5tK" "https://gitlab.example.com/api/v3/projects/42/issues?title=Hello%20Dude" +``` + +Use `%2F` for slashes (`/`). + +#### Pass arrays to API calls + +The GitLab API sometimes accepts arrays of strings or integers. For example, to +restrict the sign-up e-mail domains of a GitLab instance to `*.example.com` and +`example.net`, you would do something like this: + +```bash +curl -X PUT -H "PRIVATE-TOKEN: 9koXpg98eAheJpvBs5tK" -d "restricted_signup_domains[]=*.example.com" -d "restricted_signup_domains[]=example.net" https://gitlab.example.com/api/v3/application/settings +``` + +[cURL]: http://curl.haxx.se/ "cURL website" +[single spaces]: http://www.slate.com/articles/technology/technology/2011/01/space_invaders.html +[gfm]: http://doc.gitlab.com/ce/markdown/markdown.html#newlines "GitLab flavored markdown documentation" +[doc-restart]: ../administration/restart_gitlab.md "GitLab restart documentation" diff --git a/doc/development/gotchas.md b/doc/development/gotchas.md new file mode 100644 index 00000000000..21078c8d6f9 --- /dev/null +++ b/doc/development/gotchas.md @@ -0,0 +1,103 @@ +# Gotchas + +The purpose of this guide is to document potential "gotchas" that contributors +might encounter or should avoid during development of GitLab CE and EE. + +## Don't `describe` symbols + +Consider the following model spec: + +```ruby +require 'rails_helper' + +describe User do + describe :to_param do + it 'converts the username to a param' do + user = described_class.new(username: 'John Smith') + + expect(user.to_param).to eq 'john-smith' + end + end +end +``` + +When run, this spec doesn't do what we might expect: + +```sh +spec/models/user_spec.rb|6 error| Failure/Error: u = described_class.new NoMethodError: undefined method `new' for :to_param:Symbol +``` + +### Solution + +Except for the top-level `describe` block, always provide a String argument to +`describe`. + +## Don't `rescue Exception` + +See ["Why is it bad style to `rescue Exception => e` in Ruby?"][Exception]. + +_**Note:** This rule is [enforced automatically by +Rubocop](https://gitlab.com/gitlab-org/gitlab-ce/blob/8-4-stable/.rubocop.yml#L911-914)._ + +[Exception]: http://stackoverflow.com/q/10048173/223897 + +## Don't use inline CoffeeScript in views + +Using the inline `:coffee` or `:coffeescript` Haml filters comes with a +performance overhead. + +_**Note:** We've [removed these two filters](https://gitlab.com/gitlab-org/gitlab-ce/blob/8-5-stable/config/initializers/haml.rb) +in an initializer._ + +### Further reading + +- Pull Request: [Replace CoffeeScript block into JavaScript in Views](https://git.io/vztMu) +- Stack Overflow: [Performance implications of using :coffescript filter inside HAML templates?](http://stackoverflow.com/a/17571242/223897) + +## ID-based CSS selectors need to be a bit more specific + +Normally, because HTML `id` attributes need to be unique to the page, it's +perfectly fine to write some JavaScript like the following: + +```javascript +$('#js-my-selector').hide(); +``` + +However, there's a feature of GitLab's Markdown processing that [automatically +adds anchors to header elements][ToC Processing], with the `id` attribute being +automatically generated based on the content of the header. + +Unfortunately, this feature makes it possible for user-generated content to +create a header element with the same `id` attribute we're using in our +selector, potentially breaking the JavaScript behavior. A user could break the +above example with the following Markdown: + +```markdown +## JS My Selector +``` + +Which gets converted to the following HTML: + +```html +<h2> + <a id="js-my-selector" class="anchor" href="#js-my-selector" aria-hidden="true"></a> + JS My Selector +</h2> +``` + +[ToC Processing]: https://gitlab.com/gitlab-org/gitlab-ce/blob/8-4-stable/lib/banzai/filter/table_of_contents_filter.rb#L31-37 + +### Solution + +The current recommended fix for this is to make our selectors slightly more +specific: + +```javascript +$('div#js-my-selector').hide(); +``` + +### Further reading + +- Issue: [Merge request ToC anchor conflicts with tabs](https://gitlab.com/gitlab-org/gitlab-ce/issues/3908) +- Merge Request: [Make tab target selectors less naive](https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/2023) +- Merge Request: [Make cross-project reference's clipboard target less naive](https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/2024) diff --git a/doc/development/migration_style_guide.md b/doc/development/migration_style_guide.md index 4fa1961fde9..28dedf3978c 100644 --- a/doc/development/migration_style_guide.md +++ b/doc/development/migration_style_guide.md @@ -8,12 +8,14 @@ In addition, having to take a server offline for a an upgrade small or big is a big burden for most organizations. For this reason it is important that your migrations are written carefully, can be applied online and adhere to the style guide below. +It's advised to have offline migrations only in major GitLab releases. + When writing your migrations, also consider that databases might have stale data or inconsistencies and guard for that. Try to make as little assumptions as possible about the state of the database. Please don't depend on GitLab specific code since it can change in future versions. -If needed copy-paste GitLab code into the migration to make make it forward compatible. +If needed copy-paste GitLab code into the migration to make it forward compatible. ## Comments in the migration @@ -33,6 +35,8 @@ It is always preferable to have a migration run online. If you expect the migrat to take particularly long (for instance, if it loops through all notes), this is valuable information to add. +If you don't provide the information it means that a migration is safe to run online. + ### Reversibility Your migration should be reversible. This is very important, as it should @@ -85,4 +89,4 @@ select_all("SELECT name, COUNT(id) as cnt FROM tags GROUP BY name HAVING COUNT(i execute("UPDATE taggings SET tag_id = #{origin_tag_id} WHERE tag_id IN(#{duplicate_ids.join(",")})") execute("DELETE FROM tags WHERE id IN(#{duplicate_ids.join(",")})") end -``` +```
\ No newline at end of file diff --git a/doc/development/scss_styleguide.md b/doc/development/scss_styleguide.md new file mode 100644 index 00000000000..6c48c25448b --- /dev/null +++ b/doc/development/scss_styleguide.md @@ -0,0 +1,194 @@ +# SCSS styleguide + +This style guide recommends best practices for SCSS to make styles easy to read, +easy to maintain, and performant for the end-user. + +## Rules + +### Naming + +CSS classes should use the `lowercase-hyphenated` format rather than +`snake_case` or `camelCase`. + +```scss +// Bad +.class_name { + color: #fff; +} + +// Bad +.className { + color: #fff; +} + +// Good +.class-name { + color: #fff; +} +``` + +### Formatting + +You should always use a space before a brace, braces should be on the same +line, each property should each get its own line, and there should be a space +between the property and its value. + +```scss +// Bad +.container-item { + width: 100px; height: 100px; + margin-top: 0; +} + +// Bad +.container-item +{ + width: 100px; + height: 100px; + margin-top: 0; +} + +// Bad +.container-item{ + width:100px; + height:100px; + margin-top:0; +} + +// Good +.container-item { + width: 100px; + height: 100px; + margin-top: 0; +} +``` + +Note that there is an exception for single-line rulesets, although these are +not typically recommended. + +```scss +p { margin: 0; padding: 0; } +``` + +### Colors + +HEX (hexadecimal) colors short-form should use shortform where possible, and +should use lower case letters to differenciate between letters and numbers, e. +g. `#E3E3E3` vs. `#e3e3e3`. + +```scss +// Bad +p { + color: #ffffff; +} + +// Bad +p { + color: #FFFFFF; +} + +// Good +p { + color: #fff; +} +``` + +### Indentation + +Indentation should always use two spaces for each indentation level. + +```scss +// Bad, four spaces +p { + color: #f00; +} + +// Good +p { + color: #f00; +} +``` + +### Semicolons + +Always include semicolons after every property. When the stylesheets are +minified, the semicolons will be removed automatically. + +```scss +// Bad +.container-item { + width: 100px; + height: 100px +} + +// Good +.container-item { + width: 100px; + height: 100px; +} +``` + +### Shorthand + +The shorthand form should be used for properties that support it. + +```scss +// Bad +margin: 10px 15px 10px 15px; +padding: 10px 10px 10px 10px; + +// Good +margin: 10px 15px; +padding: 10px; +``` + +### Zero Units + +Omit length units on zero values, they're unnecessary and not including them +is slightly more performant. + +```scss +// Bad +.item-with-padding { + padding: 0px; +} + +// Good +.item-with-padding { + padding: 0; +} +``` + +### Selectors with a `js-` Prefix +Do not use any selector prefixed with `js-` for styling purposes. These +selectors are intended for use only with JavaScript to allow for removal or +renaming without breaking styling. + +## Linting + +We use [SCSS Lint][scss-lint] to check for style guide conformity. It uses the +ruleset in `.scss-lint.yml`, which is located in the home directory of the +project. + +To check if any warnings will be produced by your changes, you can run `rake +scss_lint` in the GitLab directory. SCSS Lint will also run in GitLab CI to +catch any warnings. + +If the Rake task is throwing warnings you don't understand, SCSS Lint's +documentation includes [a full list of their linters][scss-lint-documentation]. + +### Fixing issues + +If you want to automate changing a large portion of the codebase to conform to +the SCSS style guide, you can use [CSSComb][csscomb]. First install +[Node][node] and [NPM][npm], then run `npm install csscomb -g` to install +CSSComb globally (system-wide). Run it in the GitLab directory with +`csscomb app/assets/stylesheets` to automatically fix issues with CSS/SCSS. + +Note that this won't fix every problem, but it should fix a majority. + +[csscomb]: https://github.com/csscomb/csscomb.js +[node]: https://github.com/nodejs/node +[npm]: https://www.npmjs.com/ +[scss-lint]: https://github.com/brigade/scss-lint +[scss-lint-documentation]: https://github.com/brigade/scss-lint/blob/master/lib/scss_lint/linter/README.md diff --git a/doc/development/sql.md b/doc/development/sql.md new file mode 100644 index 00000000000..23fd7604957 --- /dev/null +++ b/doc/development/sql.md @@ -0,0 +1,219 @@ +# SQL Query Guidelines + +This document describes various guidelines to follow when writing SQL queries, +either using ActiveRecord/Arel or raw SQL queries. + +## Using LIKE Statements + +The most common way to search for data is using the `LIKE` statement. For +example, to get all issues with a title starting with "WIP:" you'd write the +following query: + +```sql +SELECT * +FROM issues +WHERE title LIKE 'WIP:%'; +``` + +On PostgreSQL the `LIKE` statement is case-sensitive. On MySQL this depends on +the case-sensitivity of the collation, which is usually case-insensitive. To +perform a case-insensitive `LIKE` on PostgreSQL you have to use `ILIKE` instead. +This statement in turn isn't supported on MySQL. + +To work around this problem you should write `LIKE` queries using Arel instead +of raw SQL fragments as Arel automatically uses `ILIKE` on PostgreSQL and `LIKE` +on MySQL. This means that instead of this: + +```ruby +Issue.where('title LIKE ?', 'WIP:%') +``` + +You'd write this instead: + +```ruby +Issue.where(Issue.arel_table[:title].matches('WIP:%')) +``` + +Here `matches` generates the correct `LIKE` / `ILIKE` statement depending on the +database being used. + +If you need to chain multiple `OR` conditions you can also do this using Arel: + +```ruby +table = Issue.arel_table + +Issue.where(table[:title].matches('WIP:%').or(table[:foo].matches('WIP:%'))) +``` + +For PostgreSQL this produces: + +```sql +SELECT * +FROM issues +WHERE (title ILIKE 'WIP:%' OR foo ILIKE 'WIP:%') +``` + +In turn for MySQL this produces: + +```sql +SELECT * +FROM issues +WHERE (title LIKE 'WIP:%' OR foo LIKE 'WIP:%') +``` + +## LIKE & Indexes + +Neither PostgreSQL nor MySQL use any indexes when using `LIKE` / `ILIKE` with a +wildcard at the start. For example, this will not use any indexes: + +```sql +SELECT * +FROM issues +WHERE title ILIKE '%WIP:%'; +``` + +Because the value for `ILIKE` starts with a wildcard the database is not able to +use an index as it doesn't know where to start scanning the indexes. + +MySQL provides no known solution to this problem. Luckily PostgreSQL _does_ +provide a solution: trigram GIN indexes. These indexes can be created as +follows: + +```sql +CREATE INDEX [CONCURRENTLY] index_name_here +ON table_name +USING GIN(column_name gin_trgm_ops); +``` + +The key here is the `GIN(column_name gin_trgm_ops)` part. This creates a [GIN +index][gin-index] with the operator class set to `gin_trgm_ops`. These indexes +_can_ be used by `ILIKE` / `LIKE` and can lead to greatly improved performance. +One downside of these indexes is that they can easily get quite large (depending +on the amount of data indexed). + +To keep naming of these indexes consistent please use the following naming +pattern: + + index_TABLE_on_COLUMN_trigram + +For example, a GIN/trigram index for `issues.title` would be called +`index_issues_on_title_trigram`. + +Due to these indexes taking quite some time to be built they should be built +concurrently. This can be done by using `CREATE INDEX CONCURRENTLY` instead of +just `CREATE INDEX`. Concurrent indexes can _not_ be created inside a +transaction. Transactions for migrations can be disabled using the following +pattern: + +```ruby +class MigrationName < ActiveRecord::Migration + disable_ddl_transaction! +end +``` + +For example: + +```ruby +class AddUsersLowerUsernameEmailIndexes < ActiveRecord::Migration + disable_ddl_transaction! + + def up + return unless Gitlab::Database.postgresql? + + execute 'CREATE INDEX CONCURRENTLY index_on_users_lower_username ON users (LOWER(username));' + execute 'CREATE INDEX CONCURRENTLY index_on_users_lower_email ON users (LOWER(email));' + end + + def down + return unless Gitlab::Database.postgresql? + + remove_index :users, :index_on_users_lower_username + remove_index :users, :index_on_users_lower_email + end +end +``` + +## Plucking IDs + +This can't be stressed enough: **never** use ActiveRecord's `pluck` to pluck a +set of values into memory only to use them as an argument for another query. For +example, this will make the database **very** sad: + +```ruby +projects = Project.all.pluck(:id) + +MergeRequest.where(source_project_id: projects) +``` + +Instead you can just use sub-queries which perform far better: + +```ruby +MergeRequest.where(source_project_id: Project.all.select(:id)) +``` + +The _only_ time you should use `pluck` is when you actually need to operate on +the values in Ruby itself (e.g. write them to a file). In almost all other cases +you should ask yourself "Can I not just use a sub-query?". + +## Use UNIONs + +UNIONs aren't very commonly used in most Rails applications but they're very +powerful and useful. In most applications queries tend to use a lot of JOINs to +get related data or data based on certain criteria, but JOIN performance can +quickly deteriorate as the data involved grows. + +For example, if you want to get a list of projects where the name contains a +value _or_ the name of the namespace contains a value most people would write +the following query: + +```sql +SELECT * +FROM projects +JOIN namespaces ON namespaces.id = projects.namespace_id +WHERE projects.name ILIKE '%gitlab%' +OR namespaces.name ILIKE '%gitlab%'; +``` + +Using a large database this query can easily take around 800 milliseconds to +run. Using a UNION we'd write the following instead: + +```sql +SELECT projects.* +FROM projects +WHERE projects.name ILIKE '%gitlab%' + +UNION + +SELECT projects.* +FROM projects +JOIN namespaces ON namespaces.id = projects.namespace_id +WHERE namespaces.name ILIKE '%gitlab%'; +``` + +This query in turn only takes around 15 milliseconds to complete while returning +the exact same records. + +This doesn't mean you should start using UNIONs everywhere, but it's something +to keep in mind when using lots of JOINs in a query and filtering out records +based on the joined data. + +GitLab comes with a `Gitlab::SQL::Union` class that can be used to build a UNION +of multiple `ActiveRecord::Relation` objects. You can use this class as +follows: + +```ruby +union = Gitlab::SQL::Union.new([projects, more_projects, ...]) + +Project.from("(#{union.to_sql}) projects") +``` + +## Ordering by Creation Date + +When ordering records based on the time they were created you can simply order +by the `id` column instead of ordering by `created_at`. Because IDs are always +unique and incremented in the order that rows are created this will produce the +exact same results. This also means there's no need to add an index on +`created_at` to ensure consistent performance as `id` is already indexed by +default. + +[gin-index]: http://www.postgresql.org/docs/current/static/gin.html |