On 05/21/10 18:00, Bryan Vyhmeister wrote:
On Fri, May 21, 2010 at 4:10 PM, Jerry<dovecot.u...@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,
1. Compile the assembly to a DLL. Call it e.g. PostgresStoredProcedures.
2. Enable CLR code to run within the database:
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
3. Make your database trustworthy. This is necessary because it will
be accessing external resources.
ALTER DATABASE <exampledb> SET TRUSTWORTHY ON
4. 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
5. 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
6. You can now execute the stored procedures from within SQL:
exec InsertPostgres 'u...@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).
