In this post, we'll explore how to take an existing sql table (currently in mysql) and get it into sqllite. I have a lot of questions - how to code it, how to load the data, should I start out on the device, the emulator, the Mac? Adopting a "logical" test-first approach, I'm going to reduce this to the simplest step I can, which is transferring the data from mySql (for which the table was created in the previous post) to sqlLite. That needs to pass the logical test of having the data in sqllite.
In fact, that will require me to set up an sql lite database on the mac. I know there's already one running on the android phone, and on the emulator as well. Since it's always better to test on a real device, I'll go with the device. But the question is - how to get the data into the sql lite database? Actually, I ran across a post on how to pre-package your data into an sqllite database for android - let's check it out. It's at http://tinyurl.com/mvtwqm.
Essentially, the code checks for an existing db, and if not, it copies the data from your res folder into your sql lite database folder, where it can then be accessed by your program. From all the comments, it looks as if this is a pretty standard requirement - and that a lot of people had issues with getting the code to run. But, there is also a mention of an SQLite Database Browser. Let's have a look at that, first, at http://sourceforge.net/projects/sqlitebrowser/
The description states "SQLite Database browser is a light GUI editor for SQLite databases, built on top of Qt. The main goal of the project is to allow non-technical users to create, modify and edit SQLite databases using a set of wizards and a spreadsheet-like interface."
That sounds good - does it set up the sql lite database itself? Let's download it and give it a try.
Ok, it looks pretty simple. Open files - presumably for sql files? You can run sql...but how do you identify the database it's talking to? Or is it wrapping it's own version? Let's try a create table. Oops - a crash. But I don't see how to connect to a running database. A google search shows there are actually a variety of database front ends for sqllite, both cross-platform and mac-specific. I'm going to just google SqlLite Mac. Right, sqllite comes with mac, is located in /usr/bin directory and called sqlite3, on a site that recommends razorsql. Shall I download that? I hate to learn tools I'm unlikely to purchase. Ah, ok, on the free database browser, the "new" button pop up bubble actually says "create a new database file". So let's create one in desktop/databasetest called "jlpt". I'm not going to worry about getting it onto the device yet.
Ok, I created the database. But I don't see any way to run an sql script. Well, I could copy and paste an sql file into the execute sql window. Well, ok, let's give it a quick try. First, let's export the database from phpAdmin and see if I can cut and paste it. Done. Ok, it seems to be running -and running - and running. Somehow, I don't think it's working.
Let's give the command line a try. sqllite3. It was /usr/bin/sqllite3. Yup, and there's the command from .help - .read FILENAME Execute SQL in FILENAME. Let's move the file to ~/test.sql. It can't read it. Ok, how about /Users/myname/test.sql. Wait, I think I need to open the database first - jlpt. Arggh. This is a hassle Ok, I give up. Let's download RazorSQL for the 30 day evaluation.
Ohhh, that's *much* better. I've already created a database after spending an hour spinning my wheels on the other options. So easy. The connect dialogue lets me specify a profile name, the database tool (slqlite3) and whether or not to create the database. Perfect. Ok, now, that I've created the database, what's next? I need to import the data. Uh-oh. I need to specify the separator and the file encoding. And the separator doesn't even give a default. I don't see one, so I'll say line break and pray. Ok, it doesn't like the "set" statement. Hmm...I wonder if I can just import the csv file instead? Well, that creates the table, but then says "no such column - level" when there plainly is one. What now? Well, I unchecked the "halt on error", and it's running, very slowly, through all 8k+ words. But - what will be there when it's done - if anything? I'm in it up to my knees, I tell you!
18% - it's going to be a while. Ah, I see - maybe the problem was the first insert had the column headings. Good this might actually work. 46%. Slooow. I might as well come back later and check. Anyway, it's time to watch a little bit of Madmen - season two finally is out on DVD!
Ok. I'm back from Madmen. It was another good one - that's an addictive show. Anyway - it looks like the insert worked. 8k+ word were inserted. One possible hiccup is if it stored the characters in the proper format - a select all of the records shows junk in the japanese character columns. But that might just be the display. I took the default on the storage format. We'll find out soon enough if it should've been something different. So, for now, this logical test of getting from mySql to sqlLite has succeeded. In the next step, we'll start looking at actually having the data set up on the Android.
No comments:
Post a Comment