[Opensim-dev] moving assets from sqlite to mysql
Charles Krinke
cfk at pacbell.net
Fri Jan 18 00:18:05 UTC 2008
Thank you kindly, Mic. I have determined that I can do:
sqlite3 AssetStorage.db .dump > fileName
And that this file now contains the various fields in plain text ascii. The binary uuid's are ascii. In fact, it looks like this with 'head':
[grid at fc620887 bin]$ head testAssets
BEGIN TRANSACTION;
CREATE TABLE assets(UUID varchar(255) primary key,
Name varchar(255),
Description varchar(255),
Type integer,
InvType integer,
Local integer,
Temporary integer,
Data blob);
INSERT INTO "assets" VALUES('00000000000022223333000000000001', 'WelcomeNote', '', 7, 7, 0, 0, X'0A48656C6C6F20616E64207468616E6B20796F7520666F72207573696E67204F70656E5...A');
So, indeed, I will read the tutorial as I head towards understanding how to get some assets from sqlite to mysql to help out some of our users.
Charles
----- Original Message ----
From: "Bowman, Mic" <mic.bowman at intel.com>
To: opensim-dev at lists.berlios.de
Sent: Thursday, January 17, 2008 2:18:56 PM
Subject: Re: [Opensim-dev] moving assets from sqlite to mysql
Here's one of the refs for the data conversion. It has examples for
converting text to binary (hex) and for creating blobs...
http://www.dbmaker.com.tw/reference/manuals/tutorial/tutorial_07.html
--mic
-----Original Message-----
From: opensim-dev-bounces at lists.berlios.de
[mailto:opensim-dev-bounces at lists.berlios.de] On Behalf Of Bowman, Mic
Sent: Thursday, January 17, 2008 8:33 AM
To: opensim-dev at lists.berlios.de
Subject: Re: [Opensim-dev] moving assets from sqlite to mysql
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
_______________________________________________
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://opensimulator.org/pipermail/opensim-dev/attachments/20080117/23266a28/attachment-0001.html>
More information about the Opensim-dev
mailing list