On 10/04/13 00:12, Christophe Dupriez wrote: > Hi! > > Belgium PoisonCentre has decided to standardize on MySQL. PostgreSQL > was rejected because its level of integration with Microsoft Entity > Framework (used for the development of other PoisonCentre > applications) is far behind what MySQL achieves (MySQL seems to be > the only open-source DBMS well integrated with Entity Framework) > > For that reason, I was obliged to modify DSpace for MySQL support. > > Here some results that may be of interest to developers: > > 1) "canonicalize": don't add "`" around identifiers! the result of > canonicalize is used in SQL statements but also as keys for various > maps. > > 2) Sequences: new object ids (AUTO_INCREMENT in MySQL) are created > in a different way (insert with object key "0") than Oracle and > PostgreSQL (DatabaseManager class in DSpace 1.4) > > 3) DDL: Tables creation ask for a different DDL than PostgreSQL > > 4) MySQL does not support BOOLEAN SQL type, it uses TINYINT(1). > Converting from PostgreSQL to MySQL must take this into account. > TINYINT must be treated and converted to/from boolean in > DatabaseManager class (DSpace 1.4): look where BOOLEAN is managed. > > 5) Fields greated than 255 characters are TEXT (LONGVARCHAR) in MySQL > LONGVARCHAR must be treated and converted to/from String in > DatabaseManager class (DSpace 1.4): look where VARCHAR is managed. > > 6) Care must be taken with the AUTO_INCREMENT counter when > converting the data.
Hi Christophe, I haven't looked at the DSpace database code for a while but if you're using MySQL >= 5.0: 1. you should be able to use BOOLEAN and TRUE and FALSE in SQL, but they are aliases for TINYINT(1), 1, and 0 respectively, 2. the SERIAL type is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE and should work okay as a PK (but it's not really a separate sequence like in PostgreSQL), http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html 3. DDL shouldn't be too different as long as you're not using weird MySQL-isms, e.g. specifying indexes with the KEY keyword inside a CREATE TABLE statement. Luckily you can instead use standard CREATE TABLE DDL, with separate CREATE INDEX or ALTER TABLE ADD CONSTRAINT statements. Generally wherever there is a weird MySQL-only syntax, you can use the SQL standard instead, e.g. LIMIT x, y should be LIMIT y OFFSET x. I've had to do the opposite, converting a MySQL-only app to support PostgreSQL. The tragedy is that MySQL actually supports very similar syntax but many MySQL developers just assume that `backticks` and all sorts of other junk are standard SQL. I can't help but say that MySQL is cool as long as one: - only uses InnoDB, - sorts out the UTF-8 support properly, - doesn't mind DDL being non-transactional (CREATE/ALTER/DROP TABLE will stick after a ROLLBACK), and one - doesn't have to rely on its awful date handling. Cheers, Jonathan > As I forked from DSpace at version 1.4, the issues I state must be > reconciled with current version. > > The conversion work was done by Mr. Vehzdin Hamid who makes a three > months internship at PoisonCentre. > > I attach the DDL in SQL to create the DSpace 1.4 (without browsing > tables) in MySQL if it helps anyone wishing to add support to MySQL > in current DSpace version. > > Wishing you a very nice day! > > Christophe ------------------------------------------------------------------------------ Precog is a next-generation analytics platform capable of advanced analytics on semi-structured data. The platform includes APIs for building apps and a phenomenal toolset for data science. Developers can use our toolset for easy data analysis & visualization. Get a free account! http://www2.precog.com/precogplatform/slashdotnewsletter _______________________________________________ Dspace-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/dspace-devel
