Database - SQLite migration to mySQL?

Are you having problems with using or developing a plugin? Let me know here.
Post Reply
Laban
Posts: 10
Joined: Thu Jan 26, 2017 1:34 pm

Database - SQLite migration to mySQL?

Post by Laban » Fri Jan 27, 2017 9:55 am

Hello,

Had a look but could not find this on the forum - so here it goes.

I'm wondering if anyone has an idea of how to migrate the SQLite database to something that can be used on a mySQL server? I've had a wiff at what the internet has to say and there are various options - but as my proficiency in this field is rather limited I can't figure out what would actually work on the BaseStation.sql file.

My attempt is to use the data that VRS generates and play around with it on a website, but first I obviously need importing a database that mySQL can accept.

Any ideas?

drencken
Posts: 18
Joined: Sun Feb 28, 2016 1:45 pm

Re: Database - SQLite migration to mySQL?

Post by drencken » Fri Jan 27, 2017 6:37 pm

If you're handy with scripting etc., take a look at my project on Github. https://github.com/drencken/VRS-flights-db

This borrows heavily from Andy Hill's work as published here : https://github.com/ProHill/VRS-flights-db

I have made this work all on a single Linux box where VRS and MySQL are co-resident. So I'm not substituting SQLite but importing data into MySQL where PHP scripts etc can be used to display historic flight data etc.

Laban
Posts: 10
Joined: Thu Jan 26, 2017 1:34 pm

Re: Database - SQLite migration to mySQL?

Post by Laban » Sat Jan 28, 2017 9:36 am

@drencken

Thanks! I'm not very good at scripting no, but will take a look at your project.

I'm running my VRS on a Windows machine while Tracker is on a Pi3.

Laban
Posts: 10
Joined: Thu Jan 26, 2017 1:34 pm

Re: Database - SQLite migration to mySQL?

Post by Laban » Mon Jan 30, 2017 8:48 am

Had a look at the above project(s) and those look really neat!... but quite frankly, way out of may league in terms of proficiency :)

I'm more looking for a way to convert/migrate a SQLite databse to mySQL.

drencken
Posts: 18
Joined: Sun Feb 28, 2016 1:45 pm

Re: Database - SQLite migration to mySQL?

Post by drencken » Mon Jan 30, 2017 6:25 pm

If you want to just create a copy of the SQLite data it's pretty easy. It comes down doing an export to a csv file from Sqlite and then importing that data into mySQL. I can send you more details if that approach seems feasible to you.

Laban
Posts: 10
Joined: Thu Jan 26, 2017 1:34 pm

Re: Database - SQLite migration to mySQL?

Post by Laban » Tue Jan 31, 2017 7:54 am

@drencken

If you could point me in that direction it would be a great start!

EDIT: I played around with SQLiteStudio - opened the SQB File and exported database to, what SQLiteStudio claims to be, a SQL file.

The export (excluding values):

Code: Select all

DROP TABLE IF EXISTS `Aircraft`;
CREATE TABLE [Aircraft]
(
    [AircraftID]        INTEGER PRIMARY KEY
   ,[FirstCreated]      DATETIME NOT NULL
   ,[LastModified]      DATETIME NOT NULL
   ,[ModeS]             VARCHAR(6) NOT NULL UNIQUE
   ,[ModeSCountry]      VARCHAR(24)
   ,[Country]           VARCHAR(24)
   ,[Registration]      VARCHAR(20)
   ,[CurrentRegDate]    VARCHAR(10)
   ,[PreviousID]        VARCHAR(10)
   ,[FirstRegDate]      VARCHAR(10)
   ,[Status]            VARCHAR(10)
   ,[DeRegDate]         VARCHAR(10)
   ,[Manufacturer]      VARCHAR(60)
   ,[ICAOTypeCode]      VARCHAR(10)
   ,[Type]              VARCHAR(40)
   ,[SerialNo]          VARCHAR(30)
   ,[PopularName]       VARCHAR(20)
   ,[GenericName]       VARCHAR(20)
   ,[AircraftClass]     VARCHAR(20)
   ,[Engines]           VARCHAR(40)
   ,[OwnershipStatus]   VARCHAR(10)
   ,[RegisteredOwners]  VARCHAR(100)
   ,[MTOW]              VARCHAR(10)
   ,[TotalHours]        VARCHAR(20)
   ,[YearBuilt]         VARCHAR(4)
   ,[CofACategory]      VARCHAR(30)
   ,[CofAExpiry]        VARCHAR(10)
   ,[UserNotes]         VARCHAR(300)
   ,[Interested]        BOOLEAN NOT NULL DEFAULT 0
   ,[UserTag]           VARCHAR(5)
   ,[InfoURL]           VARCHAR(150)
   ,[PictureURL1]       VARCHAR(150)
   ,[PictureURL2]       VARCHAR(150)
   ,[PictureURL3]       VARCHAR(150)
   ,[UserBool1]         BOOLEAN NOT NULL DEFAULT 0
   ,[UserBool2]         BOOLEAN NOT NULL DEFAULT 0
   ,[UserBool3]         BOOLEAN NOT NULL DEFAULT 0
   ,[UserBool4]         BOOLEAN NOT NULL DEFAULT 0
   ,[UserBool5]         BOOLEAN NOT NULL DEFAULT 0
   ,[UserString1]       VARCHAR(20)
   ,[UserString2]       VARCHAR(20)
   ,[UserString3]       VARCHAR(20)
   ,[UserString4]       VARCHAR(20)
   ,[UserString5]       VARCHAR(20)
   ,[UserInt1]          INTEGER DEFAULT 0
   ,[UserInt2]          INTEGER DEFAULT 0
   ,[UserInt3]          INTEGER DEFAULT 0
   ,[UserInt4]          INTEGER DEFAULT 0
   ,[UserInt5]          INTEGER DEFAULT 0
   ,[OperatorFlagCode]  VARCHAR(20)
);
It appears that there are still quite alot of crap that needs removing from above to make SQL like it :)

I've succesfully imported it to my mySQL database after removing all the [] and '....

Must be a slicker way

Post Reply