Submitted by Jan on
Requirement: - sql server management studio - mdb viewer plus / MS access - mdb2sqlite - java opensdk-jdk 1. download mdb viewer plus open mdb view plus , create a blank mdb file (test.mdb) without password. 2. open sql server management, cmd > ssms.exe 3. right click on database you want to export > tasks > export data . 4. select data source > sql server native client 11.0 > fill in auth information and make sure connection is working. 5. click next > select destination > Microsoft access (microsoft jet database engine) > select test.mdb 6. select all tables and all data will be exported to test.mdb. 7. download https://code.google.com/p/mdb-sqlite/ > wget https://mdb-sqlite.googlecode.com/files/mdb-sqlite-1.0.2.tar.bz2 tar -zxvf mdb-sqlite-1.0.2.tar.bz2 cd mdb-sqlite-1.0.2/ 8. install opensdk > apt-get install openjdk-6-jdk 9. upload your test.mdb to mdb-sqlite-1.0.2 directory. 10. java -jar dist/mdb-sqlite.jar test.mdb test.sqlite 11. we will use test.sqlite for drupal migration umbraco database structure : http://blog.hendyracher.co.uk/wp-content/uploads/Umbraco-Schema-with-Constraints-v2.gif Example : article content type migration: <? /** * @file * test_migrate.module */ /** * Sqlite db connection helper. */ function _test_migrate_sqlite_connect() { $other_database = array( 'database' => '/home/test/test.sqlite', 'host' => 'localhost', 'driver' => 'sqlite', ); $key = md5('testmigrate'); Database::addConnectionInfo($key, 'default', $other_database); return $key; } /** * Implements hook_menu(). */ function test_migrate_menu() { $items = array(); $items['admin/test_migrate'] = array( 'title' => 'test data migration', 'page callback' => 'test_migrate_page_test', 'access arguments' => array('administer site configuration'), 'type' => MENU_NORMAL_ITEM, ); /** * Migrate entry. * Get all node data from umbraco */ function test_migrate_page_test() { $key = _test_migrate_sqlite_connect(); db_set_active(); db_set_active($key); // Dsm all data. $sql = db_query(' select * from cmsPropertyData as cpd inner join (select * from cmsDataType as dt left join cmsPropertyType as cpt on cpt.dataTypeId = dt.nodeId) as data on data.id = cpd.propertytypeid left join cmsDocument as cd on cd.versionId = cpd.versionId left join cmsContentVersion as ccv on ccv.versionId = cd.versionId left join umbracoNode as ubn on ubn.id = ccv.ContentId inner join cmsContentXml as ccx on ccx.nodeId = ubn.id left join cmsContent as cc on cc.nodeId = ubn.id left join cmsContentType as cct on cct.nodeId = cc.contentType left join cmsDocumentType as cdt on cdt.contentTypeNodeId = cct.nodeId left join cmsTemplate as ct on ct.nodeId = cdt.templateNodeId where cd.newest = -1 and order by cpd.contentNodeId asc limit 0, 4000'); db_set_active(); foreach ($sql as $k => $v) { dsm($v); // dsm all whole record. $nids[$sql_v->nid]['xml'] = $sql_v->xml; // XML node from umbraco. } foreach($nids as $nid_k => $nid_v) { debug = 1; // Debug mode. // Actually we can grab all node data from xml $doc = new DOMDocument(); $doc->recover = TRUE; $xml = $nid_v['xml']; $doc->loadXML($xml); $xpath = new DOMXPath($doc); $article = $xpath->query("//Article"); $creatorId = $article->item(0)->getAttribute('creatorId'); $sortOrder = $article->item(0)->getAttribute('sortOrder'); $createDate = $article->item(0)->getAttribute('createDate'); $updateDate = $article->item(0)->getAttribute('updateDate'); $nodeName = $article->item(0)->getAttribute('nodeName'); $writerName = $article->item(0)->getAttribute('writerName'); $pageTitle = $xpath->query("//Article/pageTitle"); $metaDescription = $xpath->query("//Article/metaDescription"); $metaKeywords = $xpath->query("//Article/metaKeywords"); $articleTitle = $xpath->query("//Article/articleTitle"); $articleImage = $xpath->query("//Article/articleImage"); $bodyText = $xpath->query("//Article/bodyText"); $articleDate = $xpath->query("//Article/articleDate"); $author = $xpath->query("//Article/author"); $articleSynopsis = $xpath->query("//Article/articleSynopsis"); $articleTags = $xpath->query("//Article/articleTags"); $issueNumber = $xpath->query("//Article/issueNumber"); $node->title = $articleTitle->item(0)->nodeValue; $node->type = 'article'; $node->uid = 1; $node->status = 1; $body = $bodyText->item(0)->nodeValue; $node->body['und'][0]['value'] = $body; $node->body['und'][0]['format'] = 'full_html'; $imagepath = $articleImage->item(0)->nodeValue; $img = _test_migrate_create_img($imagepath, $debug); if (!empty($img)) { $node->field_image[LANGUAGE_NONE][] = (array) $img; } if (!$debug) { node_save($node); } else { dsm($node); } } return ''; } /** * Create image by file path. * we are using file entity module here. * * @Return file obj. */ function _test_migrate_create_img($imgpath, $debug) { if (empty($imgpath)) { return 0; } $filename = str_replace('\\', '/', $imgpath); $file->uri = 'private:/' . $imgpath; $target_dir = 'public://articles' . dirname($filename); if (!file_exists(drupal_realpath($file->uri))) { return 0; } file_prepare_directory($target_dir, FILE_CREATE_DIRECTORY); $file->filemime = file_get_mimetype($file->uri); $file->status = 1; $file->uid = 1; $file->type = 'image'; $file->display = 1; if (!$debug) { $file = file_copy($file, $target_dir, FILE_EXISTS_REPLACE); return $file; } else { dsm($file); return 0; } }
- Log in to post comments