Current section
2 Versions
Jump to
Current section
2 Versions
Compare versions
5
files changed
+164
additions
-43
deletions
| @@ -0,0 +1,22 @@ | |
| 1 | + # Changelog |
| 2 | + |
| 3 | + ## v0.2.0 (2018-04-05) |
| 4 | + |
| 5 | + * Enhancements |
| 6 | + * support Redshift specific features in migrations including `:diststyle`, `:distkey`, and `:sortkey` table options and `:identity`, `:encode`, and `:unique` column options |
| 7 | + |
| 8 | + ## v0.1.0 (2018-04-04) |
| 9 | + |
| 10 | + Initial release |
| 11 | + |
| 12 | + * Adapt the builtin Postgres adapter of Ecto to Redshift's limitations |
| 13 | + * no array type |
| 14 | + * maps are stored as json in `varchar(max)` columns |
| 15 | + * the `:binary_id` and `:uuid` Ecto types are stored in `char(36)` and generated as text |
| 16 | + * no binary type and literal support |
| 17 | + * no aliases in `UPDATE` and `DELETE FROM` statements |
| 18 | + * no `RETURNING` |
| 19 | + * no support for `on_conflict` (except for the default `:raise`) |
| 20 | + * no support for `on_delete` and `on_update` on foreign key definitions |
| 21 | + * no support for `ALTER COLUMN` |
| 22 | + * no support for `CHECK` and `EXCLUDE` constraints |
| @@ -5,7 +5,7 @@ | |
| 5 5 | {<<"files">>, |
| 6 6 | [<<"lib">>,<<"lib/redshift_ecto">>,<<"lib/redshift_ecto.ex">>, |
| 7 7 | <<"lib/redshift_ecto/connection.ex">>,<<".formatter.exs">>,<<"mix.exs">>, |
| 8 | - <<"README.md">>,<<"LICENSE.md">>]}. |
| 8 | + <<"README.md">>,<<"LICENSE.md">>,<<"CHANGELOG.md">>]}. |
| 9 9 | {<<"licenses">>,[<<"Apache 2.0">>]}. |
| 10 10 | {<<"links">>, |
| 11 11 | [{<<"GitHub">>,<<"https://github.com/100Starlings/redshift_ecto">>}]}. |
| @@ -32,4 +32,4 @@ | |
| 32 32 | {<<"optional">>,false}, |
| 33 33 | {<<"repository">>,<<"hexpm">>}, |
| 34 34 | {<<"requirement">>,<<"~> 1.0.0">>}]]}. |
| 35 | - {<<"version">>,<<"0.1.0">>}. |
| 35 | + {<<"version">>,<<"0.2.0">>}. |
| @@ -1,9 +1,9 @@ | |
| 1 1 | defmodule RedshiftEcto do |
| 2 2 | @moduledoc """ |
| 3 | - Adapter module for Redshift. |
| 3 | + Ecto adapter for [AWS Redshift](https://aws.amazon.com/redshift/). |
| 4 4 | |
| 5 | - It uses `postgrex` for communicating to the database and a connection pool, |
| 6 | - such as `poolboy`. |
| 5 | + It uses `Postgrex` for communicating to the database and a connection pool, |
| 6 | + such as `DBConnection.Poolboy`. |
| 7 7 | |
| 8 8 | This adapter is based on Ecto's builtin `Ecto.Adapters.Postgres` adapter. It |
| 9 9 | delegates some functions to it but changes the implementation of most that |
| @@ -28,6 +28,70 @@ defmodule RedshiftEcto do | |
| 28 28 | * no support for `CHECK` and `EXCLUDE` constraints |
| 29 29 | * since Redshift doesn't enforce uniqueness and foreign key constraints the |
| 30 30 | adapter can't report violations |
| 31 | + |
| 32 | + ## Migrations |
| 33 | + |
| 34 | + RedshiftEcto supports migrations with the exceptions of features that are not |
| 35 | + supported by Redshift (see above). There are also some extra features in |
| 36 | + migrations to help specify table attributes and column options available in |
| 37 | + Redshift. |
| 38 | + |
| 39 | + We highly recommend reading the |
| 40 | + [Designing Tables](https://docs.aws.amazon.com/redshift/latest/dg/t_Creating_tables.html) |
| 41 | + section from the AWS Redshift documentation. |
| 42 | + |
| 43 | + ### Table options |
| 44 | + |
| 45 | + While similarly to other adapters RedshiftEcto accepts table options as an |
| 46 | + opaque string, it also supports a keyword list with the following options: |
| 47 | + |
| 48 | + * `:diststyle`: data distribution style, possible values: `:even`, `:key`, `:all` |
| 49 | + * `:distkey`: specify the column to be used as the distribution key |
| 50 | + * `:sortkey`: specify one or more sort keys, the value can be a single column |
| 51 | + name, a list of columns, or a 2-tuple where the first element is a sort |
| 52 | + style specifier (`:compound` or `:interleaved`) and the second is a single |
| 53 | + column name or a list of columns |
| 54 | + |
| 55 | + #### Examples |
| 56 | + |
| 57 | + create table("posts", options: [distkey: :id, sortkey: :title]) |
| 58 | + create table("categories", options: [diststyle: :all, sortkey: {:interleaved, [:name, :parent_id]}]) |
| 59 | + create table("reports", options: [diststyle: :even, sortkey: [:department, :year, :month]]) |
| 60 | + |
| 61 | + ### Column options |
| 62 | + |
| 63 | + In addition to the column options accepted by `Ecto.Migration.add/3` |
| 64 | + RedshiftEcto also accepts the following Redshift specific column options: |
| 65 | + |
| 66 | + * `:identity`: specifies that the column is an identity column. The value must |
| 67 | + be a tuple of two integers where the first is the `seed` and the second the |
| 68 | + `step`. For example, `identity: {0, 1}` specifies that the values start from |
| 69 | + `0` and increments by `1`. It's worth noting that identity columns may |
| 70 | + behave differently in Redshift that one might be used to. See the [AWS |
| 71 | + Redshift docs][identity] for more details. |
| 72 | + * `:encode`: compression encoding for the column, possible values are lower |
| 73 | + case atom version of the compression encodings supported by Redshift. Some |
| 74 | + common values: `:zstd`, `:lzo`, `:delta`, `:bytedict`, `:raw`. See the [AWS |
| 75 | + Redshift docs][encodings] for more. |
| 76 | + * `:distkey`: specify the column as the distribution key (value must be `true`) |
| 77 | + * `:sortkey`: specify the column as the single (compound) sort key of the table |
| 78 | + (value must be `true`) |
| 79 | + * `:unique`: specify that the column can contain only unique values. Note that |
| 80 | + Redshift won't enforce uniqueness. |
| 81 | + |
| 82 | + [identity]: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html#identity-clause |
| 83 | + [encodings]: https://docs.aws.amazon.com/redshift/latest/dg/c_Compression_encodings.html |
| 84 | + |
| 85 | + #### Examples |
| 86 | + |
| 87 | + create table("posts") do |
| 88 | + add :id, :serial, primary_key: true, distkey: true, encode: :delta |
| 89 | + add :title, :string, size: 765, null: false, unique: true, sortkey: true, encode: :lzo |
| 90 | + add :counter, :serial, identity: {0, 1}, encode: :delta, |
| 91 | + add :views, :smallint, default: 0, encode: :mostly8, |
| 92 | + add :author, :string, default: "anonymous", encode: :text255, |
| 93 | + add :created_at, :naive_datetime, encode: :zstd |
| 94 | + end |
| 31 95 | """ |
| 32 96 | |
| 33 97 | # Inherit all behaviour from Ecto.Adapters.SQL |
| @@ -520,10 +520,16 @@ if Code.ensure_loaded?(Postgrex) do | |
| 520 520 | {quoted, quoted, schema} |
| 521 521 | |
| 522 522 | {:fragment, _, _} -> |
| 523 | - error!(nil, "Redshift doesn't support fragment sources in DELETE statements") |
| 523 | + error!( |
| 524 | + nil, |
| 525 | + "Redshift doesn't support fragment sources in UPDATE and DELETE statements" |
| 526 | + ) |
| 524 527 | |
| 525 528 | %Ecto.SubQuery{} -> |
| 526 | - error!(nil, "Redshift doesn't support subquery sources in DELETE statements") |
| 529 | + error!( |
| 530 | + nil, |
| 531 | + "Redshift doesn't support subquery sources in UPDATE and DELETE statements" |
| 532 | + ) |
| 527 533 | end |
| 528 534 | |
| 529 535 | [current | sources_unaliased(prefix, sources, pos + 1, limit)] |
| @@ -645,11 +651,8 @@ if Code.ensure_loaded?(Postgrex) do | |
| 645 651 | ] |
| 646 652 | end |
| 647 653 | |
| 648 | - def execute_ddl({:create, %Constraint{} = constraint}) do |
| 649 | - table_name = quote_table(constraint.prefix, constraint.table) |
| 650 | - queries = [["ALTER TABLE ", table_name, " ADD ", new_constraint_expr(constraint)]] |
| 651 | - |
| 652 | - queries ++ comments_on("CONSTRAINT", constraint.name, constraint.comment, table_name) |
| 654 | + def execute_ddl({:create, %Constraint{}}) do |
| 655 | + error!(nil, "CHECK and EXCLUDE constraints are not supported by Redshift") |
| 653 656 | end |
| 654 657 | |
| 655 658 | def execute_ddl({:drop, %Constraint{} = constraint}) do |
| @@ -683,23 +686,6 @@ if Code.ensure_loaded?(Postgrex) do | |
| 683 686 | [["COMMENT ON ", object, ?\s, name, " IS ", single_quote(comment)]] |
| 684 687 | end |
| 685 688 | |
| 686 | - defp comments_on(_object, _name, nil, _table_name), do: [] |
| 687 | - |
| 688 | - defp comments_on(object, name, comment, table_name) do |
| 689 | - [ |
| 690 | - [ |
| 691 | - "COMMENT ON ", |
| 692 | - object, |
| 693 | - ?\s, |
| 694 | - quote_name(name), |
| 695 | - " ON ", |
| 696 | - table_name, |
| 697 | - " IS ", |
| 698 | - single_quote(comment) |
| 699 | - ] |
| 700 | - ] |
| 701 | - end |
| 702 | - |
| 703 689 | defp comments_for_columns(table_name, columns) do |
| 704 690 | Enum.flat_map(columns, fn |
| 705 691 | {_operation, column_name, _column_type, opts} -> |
| @@ -760,20 +746,44 @@ if Code.ensure_loaded?(Postgrex) do | |
| 760 746 | |
| 761 747 | defp column_options(type, opts) do |
| 762 748 | default = Keyword.fetch(opts, :default) |
| 763 | - null = Keyword.get(opts, :null) |
| 764 | - [default_expr(default, type), null_expr(null)] |
| 749 | + |
| 750 | + {opts, _rest} = |
| 751 | + Keyword.split(opts, [:identity, :encode, :distkey, :sortkey, :null, :unique]) |
| 752 | + |
| 753 | + [default_expr(default, type), Enum.map(opts, &column_option/1)] |
| 765 754 | end |
| 766 755 | |
| 767 | - defp null_expr(false), do: " NOT NULL" |
| 768 | - defp null_expr(true), do: " NULL" |
| 769 | - defp null_expr(_), do: [] |
| 756 | + @compression_encodings [ |
| 757 | + :raw, |
| 758 | + :bytedict, |
| 759 | + :delta, |
| 760 | + :delta32k, |
| 761 | + :lzo, |
| 762 | + :mostly8, |
| 763 | + :mostly16, |
| 764 | + :mostly32, |
| 765 | + :runlength, |
| 766 | + :text255, |
| 767 | + :text32k, |
| 768 | + :zstd |
| 769 | + ] |
| 770 770 | |
| 771 | - defp new_constraint_expr(%Constraint{check: check}) when is_binary(check) do |
| 772 | - error!(nil, "CHECK constraints are not supported by Redshift") |
| 771 | + defp column_option({:encode, encoding}) when encoding in @compression_encodings do |
| 772 | + [" ENCODE ", to_string(encoding)] |
| 773 773 | end |
| 774 774 | |
| 775 | - defp new_constraint_expr(%Constraint{exclude: exclude}) when is_binary(exclude) do |
| 776 | - error!(nil, "EXCLUDE constraints are not supported by Redshift") |
| 775 | + defp column_option({:identity, {seed, step}}) when is_integer(seed) and is_integer(step) do |
| 776 | + [" IDENTITY(", to_string(seed), ?,, to_string(step), ?)] |
| 777 | + end |
| 778 | + |
| 779 | + defp column_option({:distkey, true}), do: " DISTKEY" |
| 780 | + defp column_option({:sortkey, true}), do: " SORTKEY" |
| 781 | + defp column_option({:null, false}), do: " NOT NULL" |
| 782 | + defp column_option({:null, true}), do: " NULL" |
| 783 | + defp column_option({:unique, true}), do: " UNIQUE" |
| 784 | + |
| 785 | + defp column_option(expr) do |
| 786 | + error!(nil, "unrecognized column option `#{inspect(expr)}`") |
| 777 787 | end |
| 778 788 | |
| 779 789 | defp default_expr({:ok, nil}, _type), do: " DEFAULT NULL" |
| @@ -802,12 +812,37 @@ if Code.ensure_loaded?(Postgrex) do | |
| 802 812 | defp default_expr(:error, _), do: [] |
| 803 813 | |
| 804 814 | defp options_expr(nil), do: [] |
| 805 | - |
| 806 | - defp options_expr(keyword) when is_list(keyword), |
| 807 | - do: error!(nil, "PostgreSQL adapter does not support keyword lists in :options") |
| 808 | - |
| 815 | + defp options_expr(keyword) when is_list(keyword), do: Enum.map(keyword, &table_attribute/1) |
| 809 816 | defp options_expr(options), do: [?\s, options] |
| 810 817 | |
| 818 | + defp table_attribute({:diststyle, :even}), do: " DISTSTYLE EVEN" |
| 819 | + defp table_attribute({:diststyle, :all}), do: " DISTSTYLE ALL" |
| 820 | + defp table_attribute({:diststyle, :key}), do: " DISTSTYLE KEY" |
| 821 | + |
| 822 | + defp table_attribute({:distkey, key}) when is_atom(key) or is_binary(key) do |
| 823 | + [" DISTKEY (", quote_name(key), ?)] |
| 824 | + end |
| 825 | + |
| 826 | + defp table_attribute({:sortkey, key}) when is_atom(key) or is_binary(key) do |
| 827 | + [" SORTKEY (", quote_name(key), ?)] |
| 828 | + end |
| 829 | + |
| 830 | + defp table_attribute({:sortkey, keys}) when is_list(keys) do |
| 831 | + [" SORTKEY (", intersperse_map(keys, ", ", "e_name/1), ?)] |
| 832 | + end |
| 833 | + |
| 834 | + defp table_attribute({:sortkey, {:compound, keys}}) do |
| 835 | + [" COMPOUND", table_attribute({:sortkey, keys})] |
| 836 | + end |
| 837 | + |
| 838 | + defp table_attribute({:sortkey, {:interleaved, keys}}) do |
| 839 | + [" INTERLEAVED", table_attribute({:sortkey, keys})] |
| 840 | + end |
| 841 | + |
| 842 | + defp table_attribute(table_attribute) do |
| 843 | + error!(nil, "unrecognized table attribute `#{inspect(table_attribute)}`") |
| 844 | + end |
| 845 | + |
| 811 846 | defp column_type(type, opts) do |
| 812 847 | size = Keyword.get(opts, :size) |
| 813 848 | precision = Keyword.get(opts, :precision) |
| @@ -1,7 +1,7 @@ | |
| 1 1 | defmodule RedshiftEcto.MixProject do |
| 2 2 | use Mix.Project |
| 3 3 | |
| 4 | - @version "0.1.0" |
| 4 | + @version "0.2.0" |
| 5 5 | |
| 6 6 | def project do |
| 7 7 | [ |