The marathon run for importing Norwich LETS's msaccess-based database to LocalExchange's MySQL-based database has ended a while ago. It took almost 7-8 hours and pushed my SQL knowledge to its limits. Here's how I did it.
0. Open database in MSAccess and export all tables as CSV file.
1. Make a list of columns in each table and create identical tables in MySQL.
2. Import the tables from the CSV files into MySQL:
$ mysqlimport -u dbusername -p --local --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by='\r\n' norlets tablename.txt
Got this tip from http://www.kitebird.com/articles/access-migrate.html
3. Now that all tables have been brought into MySQL, it's time to push them into LocalExchange's database.
Here are the schema of the "categories" table from MSAccess:
description -> varchar(200)
And here's the schema of LocalExchange's "categories" table:
category_id int primary key
parent_id int
categories varchar(30)
Localexchange is residing in the "norlets" database and the imported tables are in "tmp". Copying was easy:
mysql> insert into norlets.categories select * from tmp.categories
4. Next I copied in the "wants" table from "tmp". Here's the schema of tmp.wants:
autono int
member_id int
member_no int
firstname varchar(30)
category varchar(50)
description varchar(50)
And here's the schema of the "listings" table of localexchange:
+-----------------+----------------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------------------+------+-----+-------------------+-------+
| title | varchar(60) | NO | PRI | | |
| description | text | YES | | NULL | |
| category_code | smallint(4) unsigned | NO | | 0 | |
| member_id | varchar(15) | NO | PRI | | |
| rate | varchar(30) | YES | | NULL | |
| status | char(1) | NO | | | |
| posting_date | timestamp | NO | | CURRENT_TIMESTAMP | |
| expire_date | date | YES | | NULL | |
| reactivate_date | date | YES | | NULL | |
| type | char(1) | NO | PRI | | |
+-----------------+----------
This "listing" table stores both the wants and offers.
The following SQL copied all the wants:
INSERT INTO listings(title, category_code, member_id, status, posting_date, type)
SELECT tmp.wants.description as title, category_id as category_code, member_no as member_id,
'A', now(), 'W'
FROM tmp.wants, norlets.categories
WHERE tmo.offers.category = norlets.categories.description;
A similar SQL copied everything from the offers table.
5. Now we have to copy tmp.member_detail into norlets.member and norlets.person.
First we copy into norlets.person using the following SQL:
INSERT INTO norlets.person(person_id, member_id, primary_member, directory_list, first_name,
last_name, address_street1, address_street2, address_city,
address_state_code, address_post_code, email, phone1_number,
phone2_number, address_country)
SELECT member_id as person_id, member_no as member_id, 'Y', 'Y', firstname as first_name,
surname as last_name, address1 as address_street1, address2 as address_street2,
address3 as address_city, county as address_state_code, concat(post, " ", code) as
address_post_code, email, phone as phone1_number, mobile as phone2_number, 'UK'
FROM tmp.member_detail;
6. Next we copy into norlets.member from tmp.member_detail with the following SQL:
INSERT INTO norlets.member
SELECT member_no, 'password hash', 0, null, null, 'A', null,
CONCAT(notes, "\nSubscription paid? ", IF(sub_paid=1, 'Y', 'N')),
DATE(STR_TO_DATE(join_date, "%d/%m/%Y %H:%i:%S")), null, null,
IF(jointp=0, 'S', 'J'), 0, SUM(lokes_total)
FROM tmp.member_detail GROUP BY member_no ORDER BY member_no;
Especially important to note here is the use of the date format string inside the str_to_date function.
Instead of using "%M" for minute as in php, it is using "%i". This is MySQL specific.
7. After this, some specific records were update with suitable values (e.g. primary membership) to match the needs of local exchange.
8. Next I added the "admin" account with an id of 999 into the norlets.member table and started to test Local Exchange with the newly created database.