MySQL for persistence for SharePoint (WSS)

MySQLsharepointwss

This question has been asked many times in many forums but I haven't seen a confident conclusive answer. So I'll try my luck again.

I want to know whether and how is it possible to use MySQL as the persistence node (configuration and content) for SharePoint. As of now, I am only interested to know about WSS (I guess same would apply to MOSS as well).

Basically I should be able to define lists, document libraries and all content that lives in MySQL.

My front end will be pure ASP.Net and will use WSS APIs.

Best Answer

The answer is most likely to be "NO". When you install WSS, it will ask for a Microsoft SQL server and will not allow you to choose other kinds of SQL servers.

WSS/MOSS depends on hundreds of inter-related stored procedures living in Microsoft SQL Server, these are quite technology specific and use Microsoft SQL- only solutions in there.

However, if you do not want to pay for MSSQL Server, you can install WSS/MOSS on Microsoft SQL Express edition, this works fine, except you have size limit of 4GB.

Related Solutions

Mysql – How to set a default value for a MySQL Datetime column

IMPORTANT EDIT: It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other post below...

Previous versions can't do that with DATETIME...

But you can do it with TIMESTAMP:

mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type        | Null | Key | Default           | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str   | varchar(32) | YES  |     | NULL              |       | 
| ts    | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
+-------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| str  | ts                  |
+------+---------------------+
| demo | 2008-10-03 22:59:52 | 
+------+---------------------+
1 row in set (0.00 sec)

mysql>

CAVEAT: IF you define a column with CURRENT_TIMESTAMP ON as default, you will need to ALWAYS specify a value for this column or the value will automatically reset itself to "now()" on update. This means that if you do not want the value to change, your UPDATE statement must contain "[your column name] = [your column name]" (or some other value) or the value will become "now()". Weird, but true. I am using 5.5.56-MariaDB

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Best Answer

Related Solutions

Mysql – How to set a default value for a MySQL Datetime column

Mysql – concatenate multiple MySQL rows into one field

Related Topic