On 05/21/10 18:00, Bryan Vyhmeister wrote:
On Fri, May 21, 2010 at 4:10 PM, Jerry<dovecot.user@seibercom.net> wrote:
If you can access the Microsoft SQL Server from the machine hosting Dovecot, there should be no problem at all. I have used Microsoft's SQL sever for for several projects and it is an extremely fast and robust piece of software.
Perhaps you could post a clearer picture of exactly what you are attempting to accomplish.
Sorry. I should have been more specific. I want to use SQL Server for the userdb and password database. I looked through the wiki and did a number of searches but could not find an answer. If I were to use sqlite, mysql, or pgsql, these would be listed under driver = in the dovecot-sql.conf (or whatever you want to name it). How would you go about using SQL Server? There is no sql-server driver as far as I know. I have and am currently using dovecot from passwd files and from sqlite databases so I am pretty familiar with how everything works. I am just not sure how to go about this for SQL Server. Thank you.
Bryan
You can specify arbitrary .NET DLL functions to be executed from triggers in Microsoft SQL Server, and there are .NET libraries to talk to Postgres, so just do it the other way around: run Dovecot off of a local Postgres install, and feed it data from Microsoft SQL every time something changes.
Here's an example assembly.
/* Begin C# */ using System; using System.Net;
public class StoredProcedures {
public static void InsertPostgres(string username, string password) { /* This part is your job. */ }
public static void UpdatePostgres(string username, string password) { /* This part is your job. */ }
public static void DeletePostgres(string username, string password) { /* This part is your job. */ }
} /* End C# */
Now,
Compile the assembly to a DLL. Call it e.g. PostgresStoredProcedures.
Enable CLR code to run within the database:
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
Make your database trustworthy. This is necessary because it will be accessing external resources.
ALTER DATABASE <exampledb> SET TRUSTWORTHY ON
Add the assembly to your database. This will fail if you haven't set TRUSTWORTHY ON in step 3. Note that this should be done *within* the database, not to SQL server in general (i.e. the master database).
CREATE Assembly PostgresStoredProcedures FROM '<dll_path>' WITH PERMISSION_SET = EXTERNAL_ACCESS
Create the stored procedures within the database. Their signatures should match those of the class methods.
CREATE PROCEDURE InsertPostgres(@username nvarchar(256), @password nvarchar(256)) AS EXTERNAL NAME PostgresStoredProcedures.StoredProcedures.InsertPostgres
CREATE PROCEDURE UpdatePostgres(@username nvarchar(256), @password nvarchar(256)) AS EXTERNAL NAME PostgresStoredProcedures.StoredProcedures.UpdatePostgres
CREATE PROCEDURE DeletePostgres(@username nvarchar(256), @password nvarchar(256)) AS EXTERNAL NAME PostgresStoredProcedures.StoredProcedures.DeletePostgres
You can now execute the stored procedures from within SQL:
exec InsertPostgres 'user@example.com' 'password'
At this point, you just need to define three triggers on your user database -- one for each of insert, update, and delete -- that will execute those stored procedures. This is left as an exercise (but really is trivial once you've made it that far).