[Opensim-dev] moving assets from sqlite to mysql

[Bowman, Mic]

Rather than using the binary dump format, I used the ".dump" command
from the sqlite3 command line. That will create a file of SQL statements
(which are much easier to edit). I needed to make a few changes to the
file before running it to recreate the assets in mysql (working with the
ascii SQL statements is similar to what Melanie suggests... there are a
number of good online refs for doing SQL data conversion...). 

--mic


-----Original Message-----
From: opensim-dev-bounces at lists.berlios.de
[mailto:opensim-dev-bounces at lists.berlios.de] On Behalf Of Melanie
Sent: Thursday, January 17, 2008 8:18 AM
To: opensim-dev at lists.berlios.de
Subject: Re: [Opensim-dev] moving assets from sqlite to mysql

Hi,

no. There is no cut-and-dry solution.

Especially with the binary format key, there is no easy conversion, 
also, the blabs are a problem.
I have successfully migrated prim storage from MySQL to SQLite once, 
but it was a complex process.

The binary dump formats of MySQL and SQLite are sufficiently 
different to cause headaches.

MySQL uses an unquoted string preceded by 0x, e.f. 
0xFABE0345BAADFOOD for a binary value. SQLite uses 
X'FABE0345BAADFOOD to do the same thing.

So, since a UUID is a hexadecimal string representing a binary 
value, it can be done like this:

"0x`sed -e \"s/'/0x/\" -e \"s/-//g\" -e \"s/'//\"`"

This will convert one UUID to it's MySQL binary representation.

Without the second term, it could be used for a blob field as well. 
It is your responsibility to cut apart and put back together the SQL 
inserts.

This is for Linux/Cygwin, I don't know a pure Windows way, short of 
writing a C(++/#) program.

Melanie


Charles Krinke wrote:
> Please bear with me as I need to understand this completely to ensure
I get it right.
> 
> The issue is how to move one or more assets from a regions
AssetStorage.db in sqlite to import into a grids mysql database. Let me
take the tables first. Here is the sqlite table for assets.
> 
> $ sqlite3 AssetStorage.db
> sqlite> .tables
> assets
> sqlite> describe assets;
> SQL error: near "describe": syntax error
> sqlite> .schema assets
> CREATE TABLE assets(UUID varchar(255) primary key,
> Name varchar(255),
> Description varchar(255),
> Type integer,
> InvType integer,
> Local integer,
> Temporary integer,
> Data blob);
> sqlite>    
> 
> mysql> describe assets;
> +-------------+-------------+------+-----+---------+-------+
> | Field       | Type        | Null | Key | Default | Extra |
> +-------------+-------------+------+-----+---------+-------+
> | id          | binary(16)  | NO   | PRI |         |       |
> | name        | varchar(64) | NO   |     |         |       |
> | description| varchar(64) | NO   |     |         |       |
> | assetType  | tinyint(4)  | NO   |     |         |       |
> | invType    | tinyint(4)  | NO   |     |         |       |
> | local      | tinyint(1)  | NO   |     |         |       |
> | temporary  | tinyint(1)  | NO   |     |         |       |
> | data        | longblob    | NO   |     |         |       |
> +-------------+-------------+------+-----+---------+-------+
> 8 rows in set (0.00 sec)
> mysql>   
> 
> So, we have names that are slightly different (capitalization mainly)
and types that are somewhat different.
> 
> Can one expect to do something like
> 
> 1. $ sqlite3 AssetStorage > assetFile
> 2. $ scp assetFile computerRunningGrid
> 3. $ ssh computerRunningGrid
> 4. $ mysql 
> 5. $ import from assetFile to assets;
> 
> And expect all the details to be worked out?
> 
> Pardon my naivety a bit as I work through something I have not done
before, but I would like to understand it completely first before
fubarring anything. Also, I suspect a recipe describing this would be
helpful on wiki. If I can get a recipe to work, then I will be delighted
to add it to the wiki.
> 
> Charles
> 
> 
>
------------------------------------------------------------------------
> 
> _______________________________________________
> Opensim-dev mailing list
> Opensim-dev at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-dev
_______________________________________________
Opensim-dev mailing list
Opensim-dev at lists.berlios.de
https://lists.berlios.de/mailman/listinfo/opensim-dev