findmypast-oss / mssql_ecto Goto Github PK

View Code? Open in Web Editor NEW

50.0 11.0 19.0 240 KB

Ecto adapter for Mssqlex

License: Apache License 2.0

Elixir 98.90% Shell 1.10%

elixir ecto sql-server hex mssql ms-sql-server microsoft-odbc-driver ecto-adapter erlang-odbc-application

mssql_ecto's Introduction

MssqlEcto

Ecto Adapter for Mssqlex

Installation

Erlang ODBC Application

MssqlEcto requires the Erlang ODBC application to be installed. This might require the installation of an additional package depending on how you have installed Elixir/Erlang (e.g. on Ubuntu sudo apt-get install erlang-odbc).

Microsoft's ODBC Driver

MssqlEcto depends on Microsoft's ODBC Driver for SQL Server. You can find installation instructions for Linux or other platforms on the official site.

Hex

With Application Inference

If you are using application inference, i.e. application in your mix.exs looks something like this:

def application do
  [extra_applications: [:logger]]
end

Note, the lack of :applications key. Then, you just need to add the following dependencies:

def deps do
  [{:mssql_ecto, "~> 1.2.0"},
   {:mssqlex, "~> 1.1.0"}]
end

Without Application Inference

If you are explicitly calling out all running applications under application in your mix.exs, i.e. it looks something like this:

def application do
  [applications: [:logger, :plug, :postgrex]]
end

Then, you need to add mssql_ecto and mssqlex to both your deps and list of running applications:

def application do
  [applications: [:logger, :plug, :mssqlex, :mssql_ecto]]
end

def deps do
  [{:mssql_ecto, "~> 1.2.0"},
   {:mssqlex, "~> 1.1.0"}]
end

Configuration

Example configuration:

config :my_app, MyApp.Repo,
  adapter: MssqlEcto,
  database: "sql_server_db",
  username: "bob",
  password: "mySecurePa$$word",
  hostname: "localhost",
  instance_name: "MSSQLSERVER",
  port: "1433"

Example Project

An example project using mssql_ecto with Docker has kindly been created by Chase Pursłey. It can be viewed here.

Type Mappings

Ecto Type	SQL Server Type	Caveats
:id	int
:serial	int identity(1, 1)
:bigserial	bigint identity(1,1)	When a query is returning this value with the `returning` syntax and no schema is used, it will be returned as a string rather than an integer value
:binary_id	char(36)
:uuid	char(36)
:string	nvarchar
:binary	nvarchar(4000)	Limited size, not fully implemented
:integer	int
:boolean	bit
{:array, type}	list of type	Not Supported
:map	nvarchar(4000)	Not Supported
{:map, _}	nvarchar(4000)	Not Supported
:date	date
:time	time	Can write but can't read
:utc_datetime	datetime2
:naive_datetime	datetime2
:float	float
:decimal	decimal

Features not yet implemented

Table comments
Column comments
On conflict
Upserts

Contributing

Integration Test Setup

Running the integration tests requires an instance of SQL Server running on localhost and certain configuration variables set as environment variables:

MSSQL_DVR should be set to the ODBC driver to be used. Usually SQL Server Native Client 11.0 on Windows, ODBC Driver 17 for SQL Server on Linux.
MSSQL_UID should be set to the name of a login with sufficient permissions, e.g. sa
MSSQL_PWD should be set to the password for the above account

The tests will create a database named mssql_ecto_integration_test

Code of Conduct

This project had a Code of Conduct if you wish to contribute to this project, please abide by its rules.

mssql_ecto's People

Contributors

Stargazers

Watchers

Forkers

ghouli fpauser mfeckie drueck kaiatgithub donatoaz kickinespresso pasibonfire rzane zelixir nikneroz svan-jansson chrisortman whossname schultzer nerds-and-company xueenda

mssql_ecto's Issues

Allow custom ports for connections to SQL server DBs

Expected Behavior

That I can set any port for the connection in my config, like so:

config :my_app, MyApp.Repo,
  adapter: MssqlEcto,
  hostname: "localhost"
  port: "9494"

Current Behavior

Port config is ignored.

Possible Solution

Implement code change to allow it to happen, most likely will need changes to Mssqlex as well.

Context

Trying to use custom ports.

Your Environment

MssqlEcto version: 0.1
Ecto version: 2.1
Elixir version: 1.4
Erlang version: 19
Microsoft SQL Server version: 2016
Operating System and version: Ubuntu Linux 16.04
Link to your project: N/A

UUID ** (ArgumentError) invalid or unknown type :uuid for field :DemoCategoryKey

= Compilation error in file lib/plant.ex ==
** (ArgumentError) invalid or unknown type :uuid for field :DemoCategoryKey
    lib/ecto/schema.ex:1727: Ecto.Schema.check_type!/3
    lib/ecto/schema.ex:1431: Ecto.Schema.__field__/4
    lib/plant.ex:5: (module)
    (stdlib) erl_eval.erl:670: :erl_eval.do_apply/6

Error from code section:


defmodule MsSqlTest.DemoCategory do
  use Ecto.Schema

  @primary_key {:DemoCategoryKey, :uuid, autogenerate: false} 
  schema "DemoCategory" do
    #field :DemoCategoryKey, :uuid
    field :Name, :string     # Defaults to type :string
    field :CategoryNo, :integer
    field :Description, :string
  end
end

Your Environment

MssqlEcto version:
Ecto version:
Elixir version:
Erlang version:
Microsoft SQL Server version:
Operating System and version:
Link to your project:

App created with phx.new test --database mssql won't compile

I've created a new Phoenix 1.3 app with phx.new test --database mssql (which configures the app by default to use mssql_ecto and mssqlex), which I would expect to work out of the box or get me close to a working Phoenix app connected to mssql.

Right after creating the app, if I try to run mix phx.server, it attempts to compile the dependencies and it fails to compile with the following error:

== Compilation error in file lib/mssql_ecto/query_string.ex ==
** (CompileError) lib/mssql_ecto/query_string.ex:251: unknown key :fields for struct Ecto.SubQuery
    lib/mssql_ecto/query_string.ex:251: (module)

This is happening whether I try it in Mac OS or Windows, and regardless of the database configuration.

My goal is to get a test application running that successfully connects to a test SQL Server database on my local machine. I'm expecting to play around with the configuration for a bit but this compilation error is getting in the way of even starting to work with the config.

My environment

MssqlEcto version: 0.3.0
Ecto version: 2.2.1
Elixir version: 1.5.1
Erlang version: 20
Microsoft SQL Server version: 2017 RC1 (Development, latest version)
Operating System and version: Happens on Mac and Windows equally

Preloading associations with differing schemas fails

Expected Behavior

Prefixing one table with a schema_prefix should not affect any of its associated schemas.

Current Behavior

When prefixing a table with a non-default schema name, its associations inherit that schema_prefix.

Possible Solution

Steps to Reproduce (for bugs)

Context

Your Environment

MssqlEcto version: 1
Ecto version: 3
Elixir version: 1.6.1
Erlang version: 20.1
Microsoft SQL Server version: MSSQL13
Operating System and version: Linux RHEL7.5
Link to your project: closed source.

Support for named instances in MS SQL

This might be not an issue but I need help with connecting to SQL server. I am trying to create a Phoenix application with Ecto and MS SQL database. I want to connect to a custom named instance in SQL server. Using TDS driver, we can mention it in config by using the variable "instance". Is there an equivalent in this driver. Please let me know. This is the config I am using:

config :sqlapp, Sqlapp.Repo,
adapter: MssqlEcto, # Ecto.Adapters.SQL,
database: "mydb",
username: "username",
password: "pwd",
hostname: "devdb"
#instance: "instancename"

Your Environment

MssqlEcto version: 0.1
Ecto version: 2.0
Elixir version: 1.4.2
Erlang version:
Microsoft SQL Server version: 2014
Operating System and version: Windows 2007
Link to your project:

support for xml column type

I was wondering if you have plans to support xml column type, even if it just returns it as a string. I get the following error when attempting to use this column in my schema as the :string type
** (Mssqlex.Error) Column type not supported | ODBC_CODE | SQL_SERVER_CODE 0

Encoding issues with nvarchar(max) columns?

Expected Behavior

I'm using SQL Server 2016 Express, set to SQL_Latin1_General_CP1_CI_AS collation. When I save data on columns set to nvarchar(max) on SQL Server's side, they're properly saved, but when I read them back, I get this:

Columns in question here are Analysis and Contents - they should contain "analysis" and "content" respectively.

Current Behavior

Using the scaffolding default views, I managed to save the data (and confirm it's correct in SQL Server Management Studio). Schema definition:

schema "politicaldata" do
    field :analysis, :binary
    field :available, :naive_datetime
    field :contents, :binary
    field :created, :naive_datetime
    field :heading, :string
    field :onhold, :integer
    field :typecode, :integer
    field :url, :string
    field :userid, :integer

    timestamps(inserted_at: :date, updated_at: false)
end

The form to save the data uses textarea as the HTML control.

Possible Solution

Is there a way to set the collation on the Repo settings?

Steps to Reproduce (for bugs)

Save data
Go to index view, see that the nvarchar(max) columns on the database are not set properly.

Context

Trying to get out of the database what I put in 😄

Your Environment

MssqlEcto version: 0.3.0
Ecto version: 2.1
Elixir version: 1.5.0
Erlang version: 20.0
Microsoft SQL Server version: 2016 Express (in a VM)
Operating System and version: macOS 10.12.6
Link to your project: (not public, sorry)

Ecto-3 support.

Are there any plans for Ecto-3 support ?

Mssqlex.Error odbc_not_started

Bug details

When I start my phoenix server locally, I get an error that odbc is not started:

[error] Mssqlex.Protocol (#PID<0.3246.0>) failed to connect: ** (Mssqlex.Error) odbc_not_started

I tried adding :mssql_ecto to applications as specified previously in the README, but had a different error instead:

[error] Mssqlex.Protocol (#PID<0.723.0>) failed to connect: ** (Mssqlex.Error) [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found Connection to database failed. | ODBC_CODE 01000 | SQL_SERVER_CODE 0

I have MSSQL running in Azure which I am trying to connect to. I have been able to connect & query using Node.

Steps to Reproduce

I followed the instructions in the README here:

Ensured I have ODBC application installed
Installed Microsoft's ODBC driver for macOS Sierra here
Added {:mssql_ecto, "~> 0.1"} and {:mssqlex, "~> 0.6"} to my deps.
Configured my app as below:

config :my_app, MyApp.Repo,
  adapter: MssqlEcto,
  username: "<my_username>",
  password: "<my_password>",
  database: "test",
  hostname: "<my_server>.database.windows.net", 
  pool_size: 10

My environment

MssqlEcto version: 0.1
Elixir version: 1.4.0
Erlang version: 19
Operating System and version: MacOS Sierra 10.12.5

Concurrent DB Tests Failing

Cannot run tests after swapping postgres to MS SQL.

Expected Behavior

Tests should run without issue.

Current Behavior

Tests are failing, saying:

18:42:34.953 [error] GenServer #PID<0.322.0> terminating
** (stop) exited in: :gen_server.call(#PID<0.252.0>, {:checkout, #Reference<0.3768785716.1265631238.47152>, true, :infinity}, 5000)
    ** (EXIT) time out
    (db_connection) lib/db_connection/poolboy.ex:112: DBConnection.Poolboy.checkout/3
    (ecto) lib/ecto/adapters/sql/sandbox.ex:386: Ecto.Adapters.SQL.Sandbox.Pool.checkout/2
    (db_connection) lib/db_connection/ownership/proxy.ex:108: DBConnection.Ownership.Proxy.handle_call/3
    (stdlib) gen_server.erl:636: :gen_server.try_handle_call/4
    (stdlib) gen_server.erl:665: :gen_server.handle_msg/6
    (stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3
Last message (from #PID<0.321.0>): {:init, 15000}

Possible Solution

Do not check out DB connection.

Steps to Reproduce (for bugs)

Clone https://github.com/HangingClowns/mssql_ecto_issue
Run docker-compose up to turn on MSSql DB
Run mix test from sample project:

Context

Just trying to run the tests.

Your Environment

MssqlEcto version: 1.1.0
Ecto version: 2.2.9
Elixir version: 1.6.4
Erlang version: 9.3 (OTP 20.3.2)
Microsoft SQL Server version: whatever is in docker image ab22b8353bbd, was tagged as latest 8 weeks ago
Operating System and version: Mac 10.13.4
Link to your project: https://github.com/HangingClowns/mssql_ecto_issue

DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Problem:

When a database table has a trigger attached to it, insert/1 will fail because the adapter is not utilizing an INTO clause.

This blog post from the SQL Server team explains the issue well: https://blogs.msdn.microsoft.com/sqlprogrammability/2008/07/11/update-with-output-clause-triggers-and-sqlmoreresults/

Error Example:

m=%{
  client_sport_id: 1, 
  user_id_entered: 1, 
  tracking_type: "TT-mon", 
  subject: "Incoming Call", 
  notes: "This is a note.", 
  tracking_date: "2017-09-14T11:21:39.592873Z",
  entry_date: "2017-09-14T11:21:39.592873Z"
}

cs=OpsMessagingService.ClientTracking.changeset(%OpsMessagingService.ClientTracking{},m)

OpsMessagingService.Repo.insert(cs)

result:

insert/1 error:
** (Mssqlex.Error) [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The target table 'client_tracking' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. | ODBC_CODE 42000 | SQL_SERVER_CODE 334
    (ecto) lib/ecto/adapters/sql.ex:571: Ecto.Adapters.SQL.struct/7
    (ecto) lib/ecto/repo/schema.ex:469: Ecto.Repo.Schema.apply/4
    (ecto) lib/ecto/repo/schema.ex:205: anonymous fn/13 in Ecto.Repo.Schema.do_insert/4

DB Trigger Example:

CREATE TRIGGER [dbo].[briu_client_tracking] on [dbo].[client_tracking]
  for INSERT,UPDATE
  as
begin
  declare  
           @icnt int,
           @dcnt int,
           @errno   int,
           @errmsg  varchar(255)
           
   begin
     select @icnt = count(*) from inserted;
     select @dcnt = count(*) from deleted;
     IF @icnt > 0 and @dcnt=0
       begin
         update client_tracking
         set entry_date=current_timestamp,
          mod_date=current_timestamp
          where client_tracking_id in (select client_tracking_id from inserted)
	 
       end
     IF @dcnt > 0 
        begin 
          update client_tracking
          set mod_date=current_timestamp
                where client_tracking_id in (select client_tracking_id from deleted)

        end
    end
return      

end

Temporary Workaround:

Even if you're only looking to insert one record, you can use insert_all/2 and pass in your one entry because it does not rely on a return value from the database.
https://hexdocs.pm/ecto/Ecto.Repo.html#c:insert_all/3

Environment:

MssqlEcto version: 0.1.4
Ecto version: 2.1.6
Elixir version: 1.4
Erlang/OTP version: 19
Microsoft SQL Server version: Microsoft SQL Server 2014 (SP2-CU5) - 12.0.5546.0 (X64)
Operating System and version: Ubuntu 16.04.3 LTS

(Mssqlex.Error) odbc_not_started

I'm getting the following error when I start up my application: Mssqlex.Protocol (#PID<0.19212.0>) failed to connect: ** (Mssqlex.Error) odbc_not_started.

I followed the instructions https://github.com/findmypast-oss/mssqlex to install erlang-odbc and Microsoft’s ODBC Driver for SQL Server.

I’m working from within a Docker containing running Debian 8.

Ecto version 2.1
Elixir version 1.3.4

I can confirm that i can telenet to the mssql server and have used a node cli client to query it.

Any ideas?

Limit statements do not work in SQL Server 2008

Expected Behavior

To be able to limit the number of returned values in a query

Current Behavior

The query will fail and throw a syntax error

Possible Solution

If we can detect the SQL Server version we can substitute in TOP statements instead of using FETCH OFFSET

Steps to Reproduce (for bugs)

Use limit in an ecto query e.g.

from(u in User, where: u.id == ^current_user, limit: 1)

Your Environment

MssqlEcto version: 0.1
Ecto version: 2.1
Elixir version: 1.4
Erlang version: 19
Microsoft SQL Server version: 2008 (Windows)
Operating System and version: Ubuntu 16.04

How to handle capital letters in database column

I have a database table with camel case column names.

 DemoCategoryKey
 Name
 CategoryNo
 Description

How do I handle the capital letters?

Here is what I used for a schema:


defmodule MsSqlTest.DemoCategory do
  use Ecto.Schema

  schema "DemoCategory" do
    field :name, :string     # Defaults to type :string
    field :category_no, :integer
    field :description, :string
  end
end

Expected Behavior

Query the record

Current Behavior

14:20:43.582 [debug] QUERY ERROR source="DemoCategory" db=6.3ms queue=0.1ms
SELECT D0."id", D0."name", D0."category_no", D0."description" FROM "DemoCategory" AS D0 []
** (Mssqlex.Error) [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'id'.[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'category_no'. | ODBC_CODE 42S22 | SQL_SERVER_CODE 207
    (ecto) lib/ecto/adapters/sql.ex:436: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto) lib/ecto/repo/queryable.ex:130: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:35: Ecto.Repo.Queryable.all/4

Your Environment

MssqlEcto version: 0.3.1
Ecto version: 2.1
Elixir version: 1.5
Erlang version:
Microsoft SQL Server version:
Operating System and version:
Link to your project:

Installing Erlang ODBC on Windows 2012 R2

I'm having trouble installing Erlang ODBC on Windows, and I suspect many Windows users struggle with the same issue.

There is very little documentation on the internet about how to install this Erlang library.
I found this (http://erlang.org/documentation/doc-5.1/lib/odbc-0.9.1/doc/html/OdbcCompileWindows.html) and tried to follow it.

I was able to find the erl8.2\lib\odbc-2.12\src folder on my C: drive. This is where the instruction seems to expect this “Makefile” to exist but I don’t see such file there. Instead there are these 4 files only.

odbc_app.erl
odbc.internal.hrl
odbc_sup.erl
odbc.erl

Your help will be greatly appreciated. Thank you.

Possible issue with query()

When trying to execute a parameterised query using Ecto.Adapters.SQL.query, I receive an error message that the number of parameters is incorrect (1 for 0), irrespective of how many are present in the string.

This block seems to be the cause

https://github.com/findmypast-oss/mssql_ecto/blob/master/lib/mssql_ecto/connection.ex#L73-L93

I'm not entirely sure I understand what the Regex is attempting to do, particularly as both capture groups capture the same section.

The second Regex which I assume is supposed to count the number of $x parameters doesn't seem to be working either.

I suspect I'm missing something, could someone help my understand where I'm going wrong?

Thanks

Compilation error in file lib/mssql_ecto/query_string.ex ==

Received this while setting up a "hello_world" app for the mssql_ecto and the MS SQL Server database to test connectivity and experiment/verify the initial configurations.

the "hello_world" project can be found here

== Compilation error in file lib/mssql_ecto/query_string.ex ==
** (CompileError) lib/mssql_ecto/query_string.ex:251: unknown key :fields for struct Ecto.SubQuery
    lib/mssql_ecto/query_string.ex:251: (module)
could not compile dependency :mssql_ecto, "mix compile" failed. You can recompile this dependency with "mix deps.compile mssql_ecto", update it with "mix deps.update mssql_ecto" or clean it with "mix deps.clean mssql_ecto"

Expected Behavior

For the project to compile with dependencies and run

Current Behavior

errors out on iex -S mix

Possible Solution

Steps to Reproduce (for bugs)

git clone https://github.com/kickinespresso/ms_sql_test
configure connection in config.ex
run in terminal iex -S mix

Context

👍 -- I cant get the library to work

Your Environment

MssqlEcto version: 0.3.0
mssqlex version: 0.8.0
Ecto version: 2.1
Elixir version: 1.5.0
Erlang version: 19
Microsoft SQL Server version: MS SQL Server 2017
Operating System and version: OS X 10.12.6
Link to your project: https://github.com/kickinespresso/ms_sql_test

Run tests using latest version of Elixir

We are running tests from this docker image: raniemi/elixir:1.4.0_19.2_ubuntu_xenial, raniemi is not keeping up with latest versions of Elixir, so will have to do some Docker magic to run the tests with latest version of Elixir on Ubuntu Xenial

Can't open lib

[unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2'

Expected Behavior

This worked before. Expected to fire right up.

Current Behavior

Example fails when running docker-compose mix setup

Possible Solution

Not sure, I've fiddled a bunch with the Elixir Dockerfile (tried setting msodbcsql, mssql-tools and unixodbc-dev versions).

Perhaps this an issue with the official MSSQL Docker image? I was not able to get running on macOS either.

Steps to Reproduce (for bugs)

$ docker-compose build
$ docker-compose up

In separate terminal:

$ docker-compose mix setup

Context

Silently failed on me with any changes to mssql_ecto version or Docker.

Your Environment

MssqlEcto version: 1.0.0
Ecto version: 2.1.0
Elixir version: 1.6
Erlang version: 20
Microsoft SQL Server version: microsoft/mssql-server-linux:latest
Operating System and version: macOS High Sierra 10.13.3
Link to your project: cpursley/mssql_ecto_friends#2

Variable insertion misaligned with using WHERE IN

I have a query like so which I'm passing an array of ids and a string

Repo.all(
        from p in Punchcard,
          join: sm in assoc(p, :sm),
          join: application in assoc(p, :application),
          join: profile in assoc(application, :profile),
          where: sm.state_id in ^state_ids,
          where: profile.country_code == ^country_code,
          select: p
        )

If I pass a single value array, it works:

Expected Behavior

 QUERY OK source="Punchcard" db=144.2ms
SELECT P0."sm_id", P0."application_id", P0."check_in", P0."check_out", P0."amt_to_talent", P0."amt_to_hyr" FROM "Punchcard" AS P0 INNER JOIN "Sm" AS S1 ON (S1."sm_id" = P0."sm_id") INNER JOIN "Application" AS A2 ON (A2."sm_id" = P0."application_id") INNER JOIN "Profile" AS P3 ON (P3."sm_id" = A2."profile_id") 
WHERE ((S1."state_id" IN (?)) AND (P3."country_code" = ?)) ORDER BY P0."sm_id" DESC [4202, "US"]

The last part is most relevant. However, as soon as I pass a second item to the array the variable insertion seems to misalign

Actual Behavior

QUERY ERROR source="Punchcard" db=90.2ms queue=20.2ms
SELECT P0."sm_id", P0."application_id", P0."check_in", P0."check_out", P0."amt_to_talent", P0."amt_to_hyr" FROM "Punchcard" AS P0 INNER JOIN "Sm" AS S1 ON (S1."sm_id" = P0."sm_id") INNER JOIN "Application" AS A2 ON (A2."sm_id" = P0."application_id") INNER JOIN "Profile" AS P3 ON (P3."sm_id" = A2."profile_id") 
WHERE ((S1."state_id" IN (?,?)) AND (P3."country_code" = ?)) [4202, 4204, "US"]

My database thinks that "US" is being passed to the "state_id" IN(?,?) clause.

Conversion failed when converting the nvarchar value 'US' to data type int. | ODBC_CODE 22018 | SQL_SERVER_CODE 245

Unless I'm missing something, it seems not to be behaving correctly.

Possible Solution

it works to reverse the where clauses

WHERE (P3."country_code" = ?) AND (S1."state_id" IN (?,?,?)) ["US", 4202, 4204, 4303]

Your Environment

latest

Text type restricted to maximum length of 4000

Expected Behavior

Text types defined in a migration should be able to store strings of a length greater than 4000.

Current Behavior

When data of greater than length 4000 is inserted the statement is terminated.

Possible Solution

Perhaps a look at the erlang ODBC driver?

Steps to Reproduce (for bugs)

test "insert lots" do
    string = 1..1200 |> Enum.reduce("", fn x, acc -> acc <> to_string(x) end)
    # :text is of unrestricted size type
    post = TestRepo.insert!(%Post{text: string})
    assert post.text == string
end

Your Environment

MssqlEcto version: 0.1
Ecto version: 2.1
Elixir version: 1.4
Erlang version: 19
Microsoft SQL Server version: 2016
Operating System and version: Ubuntu 16.04

Alter table remove column migration removes primary key

Expected Behavior

Removing a column should remove only the column.
If the removed column is primary key, only then the constraint should be deleted.

Current Behavior

Migration that removes a column (or rollback for add column) removes the primary key constraint from the table.

Possible Solution

Adding primary_key: false doesn't remove the constraint. If this is expected behaviour, there is no documentation about it.

Steps to Reproduce (for bugs)

Clone https://github.com/pasibonfire/mssql_ecto.git
Checkout branch fix_remove_column
Run docker-compose build
Run docker-compose run mssql_ecto mix test

Context

Your Environment

MssqlEcto version: 1.1.0
Ecto version: 2.2.8
Elixir version: 1.5.3
Erlang version: 20
Microsoft SQL Server version: microsoft/mssql-server-linux:2017-CU6
Operating System and version: macOS High Sierra 10.13.4
Link to your project: https://github.com/pasibonfire/mssql_ecto/tree/fix_remove_column

Column type not supported | ODBC_CODE | SQL_SERVER_CODE 0 using type uniqueidentifier

In my database i have a table with column pruebauuid uniqueidentifier and in my schema used type :binary_id or :uuid or Ecto.UUID but always have same problem

Expected Behavior

Hi
I have a problem to use type uniqueidentifier.
In my database i have a table with column pruebauuid uniqueidentifier and in my schema used type :binary_id or :uuid or Ecto.UUID but always have same problem

Column type not supported | ODBC_CODE | SQL_SERVER_CODE 0

lib/ecto/adapters/sql.ex

431 case sql_call(repo, :prepare_execute, [name, prepared], params, mapper, opts) do
432 {:ok, query, %{num_rows: num, rows: rows}} ->
433 update.({id, query})
434 {num, rows}
435 {:error, err} ->
436 raise err
437 end
438 end
439
440 defp execute_or_reset(repo, id, reset, cached, params, mapper, opts) do
441 case sql_call(repo, :execute, [cached], params, mapper, opts) do

Steps to Reproduce (for bugs)

mix.ex

def application do
    [
      mod: {PruebaBien.Application, []},
      extra_applications: [:logger, :runtime_tools, :mssqlex, :mssql_ecto]
    ]
  end
defp deps do
    [
      {:phoenix, "~> 1.3.0"},
      {:phoenix_pubsub, "~> 1.0"},
      {:phoenix_ecto, "~> 3.2"},
      {:mssql_ecto, "~> 0.3.1"},
      {:mssqlex, "~> 0.8.0"},
      {:phoenix_html, "~> 2.10"},
      {:phoenix_live_reload, "~> 1.0", only: :dev},
      {:gettext, "~> 0.11"},
      {:cowboy, "~> 1.0"},
      {:ex_doc, "~> 0.12"},
      { :uuid, "~> 1.1" }
    ]
  end

usuario.ex

defmodule Usuario do
    use Ecto.Schema
      import Ecto
      import Ecto.Changeset
      import Ecto.Query
    schema "usuario" do
        field :nombre, :string
        field :valor, :integer
        field :pruebauuid, Ecto.UUID
    end
    def changeset(struct, params) do
        struct
            |> cast(params, [:nombre, :valor])
            |> validate_required(:nombre)
    end
end

table sql

create table usuario(
 id int auto_increment,
nombre varchar(50),
valor int,
pruebauuid uniqueidentifier,
primary key(id)
)

mix phx.server

Context

All database tables have type uniqueidentifier how primary key or other attribute. I need use uniqueidentifier type

Your Environment

MssqlEcto version: 0.3.1
Ecto version: 2.1.6
Elixir version: 1.5.3
Erlang version: 9.0
Microsoft SQL Server version: SQL SERVER 2014
Operating System and version: Windows 10 Pro, 64-bit
Link to your project: https://github.com/ruben44bac/elixirmssql_test

Extra information

file_system 0.2.2
connection 1.0.4
gettext 0.14.0
ranch 1.3.2
poolboy 1.5.1
decimal 1.4.1
poison 3.1.0
earmark 1.2.4
ex_doc 0.18.1
db_connection 1.1.2
ecto 2.1.6
phoenix_pubsub 1.0.2
cowlib 1.0.2
cowboy 1.1.2
uuid 1.1.8
mime 1.2.0
plug 1.4.3
phoenix_html 2.10.5
phoenix 1.3.0
phoenix_live_reload 1.1.3
mssqlex 0.8.0
mssql_ecto 0.3.1
phoenix_ecto 3.3.0

Difficulty setting up

This looks like a very useful project; thanks for making it. But I'm having a difficult time setting it up. I'd love to see a simple reference Elixir application using this library that I could clone. Or as an alternative, expand the README to include how to use mssql_ecto along with a repo and simple schema.

Expected MssqlEcto to implement Ecto.Adapter.Storage in order to to create storage

In a Phoenix-project when I run mix test I get the following error:

(Mix) Expected MssqlEcto to implement Ecto.Adapter.Storage in order to to create storage for MyApp.Repo

The app works in development environment where I can connect to the MS SQL server.

Am I missing something in my configuration (see below) or is this an error?

Thank you for your help!

Expected Behavior

mix test should run because I didn't change anything except adding mssql_ecto as a dependency.

Current Behavior

mix test doesn't work as it did bevor I've added mssql_ecto as a dependency.

Steps to Reproduce (for bugs)

Create a new Phoenix App
add dependency mssql_ecto
add mssql_ecto repo to config/test
config :my_app, MyApp.Repo, adapter: MssqlEcto, database: "mydb_test", username: "user", password: "pwd", hostname: "localhost", odbc_driver: "{SQL Server Native Client 11.0}", pool: 10 # pool: Ecto.Adapters.SQL.Sandbox
run mix test

Context

Run mix test doesn't work

Your Environment

MssqlEcto version: ~> 0.1
Phoenix_ecto version: 3.2 (--> Ecto version: 2.1.4)
Elixir version: 1.4
Phoenix version: 1.3.0-rc
Erlang version: 7.2.1
Microsoft SQL Server version: 2014
Operating System and version: Windows 10

Support for uniqueidentifier / UUIDs

I would love to see support for the native MS-SQL uniqueidentifier datatype.

Expected Behavior

Mapping :binary_id to uniqueidentifier

Current Behavior

Currently data returned with a uniqueidentifier datatype raises an error:

** (Mssqlex.Error) Column type not supported | ODBC_CODE  | SQL_SERVER_CODE 0

Possible Solution

Something like
https://github.com/elixir-ecto/postgrex/blob/b1c0d7e4fd517b56fbd3bcdb7f2ee21e107c2665/lib/postgrex/extensions/uuid.ex##

Context

Using UUID is pretty standard behaviour I would assume.

Support Ecto 2.2

There are changes coming in Ecto 2.2 which will require changes to our adapter.

See elixir-ecto/ecto#2155 for details.

Associations do not work with non-standard primary keys

When using non-standard primary keys in a legacy database schema, mssql_ecto associations are building out incompatible foreign keys.

An example primary key source in the database is UserID while the ecto alias of that is user_id. Mssql creates a foreign key like user_id_id.