The company I am volunteering for outside of work has been using a flat-file database I will call Gold Digger for fun and because it’s a play on the title of the software. Just within the past half hour I was able to export data from one of their main modules in Gold Digger as a CSV file and was able to import this into the PostgreSQL database, Egret. My strategy was to build a migration table in Egret that would match the column names in the CSV. Because the CSV is all text, trying to import “-80.50” into a numeric field just blows the frick up, so I decided I would make the migration table columns all text. I will have to explore more to find a quicker method to perform these migrations, but the next step will be to insert into the Inventory table a selection from the migration table and get fancy with the data format functions (currently unknown to me) in order to match the datatypes properly.
Three hours Tuesday night taught me some major differences between Oracle and PostgreSQL. I think I would struggle to be a DBA because I always have to know why, and so much about databases seems to be ad-hoc. In Oracle, a user is typically a schema, but in Postgres users are now Roles (with login privileges). Which is really interesting, because you can also have multiple databases in one “database”, but the users are separate from those databases. It’s weird. Now, what’s going to be really fun is building the user functionality and learning how to establish encryption over an intranet. Well, I won’t worry about that yet. A journey of a thousand miles begins with a single step.
“I…have made fire!”
By this I mean to say that I’ve connected to a Postgresql database with C# and printed some content to the console, marking the third time I have used the connected layer of ADO to retrieve database information.
You have to realize, at my last job, all of this was abstracted from us. ADO is the beast I’ve been learning on my own for the past several weeks. I took a guided, 200-page tour, and now I’ve been doing more hands-on, which is so important. Most tutorials use Sql Server, but last week I had some down time to connect to one of our development Oracle databases, and now I’ve connected to my Postgres database. So that’s the power of using interfaces to achieve similar functionality across providers.
To be fair, I’ve had to reference my C# book for each occasion, as I keep forgetting that damn DataReader object, but I’m pretty stoked I got this to work. It’s scary to think how much more there still is to learn about ADO and Entity Framework, which leads me to my next thought.
Entity Framework still confuses me. I was thinking about doing the Egret database code-first, but I’m so new to EF that my brain starts to freak out when I think about the database structure in C# code. I’ve done so much with SQL over the years, it just feels better. Not having had experience on the job to teach me which is better, I may just build out the rest of the database in Postgres for expediency and peace of mind, then pull in the classes from the database. Unbeknownst to me at the time, the original data I received for the database was not the complete copy, and I only received the complete copy last month, which changes everything in my models, but I am desperate for visible progress. I can’t take the tutorials anymore: it’s sink or swim now, and some good people are relying on me to swim.
This is probably the most study I ever done over these past 6 months. The pace feels painfully slow, but 700 pages of brute C# is not to be dismissed, and I’ve dramatically surpassed the knowledge I had last year. But I’m going to count this victory and call it a night. I’ll push onward tomorrow.