search

palewire / django-calaccess-campaign-browser

A Django app to refine, review and republish campaign finance data drawn from the California Secretary of State’s CAL-ACCESS database
http://django-calaccess-campaign-browser.californiacivicdata.org
MIT License
17 stars 12 forks source link

MySQL-specific SQL in management commands #231

Open dcloud opened 9 years ago

dcloud commented 9 years ago

MySQL-specific syntax and settings are used in loadcalaccesscampaignfilings and flushcalaccesscampaignbrowser management commands. The following settings variables are specific to MySQL and so throw a syntax error on other database systems.

The SQL queries should only be executed on MySQL.

Additionally, the TRUNCATE command in PostgreSQL would require the CASCADE option and double quotes rather than backticks.

So there are some MySQL dependency issues…

palewire commented 9 years ago

You've put your finger on the bleeding edge of our project. While the raw-data app has been massaged to support both MySQL and PostgreSQL, this app currently only works with the former.

The SQL queries you were inspecting are all incomplete. They are only beginnings of our effort to transform, clean and filter the source data into bulk data files the average hacker can grip and rip. It's all alpha and needs exactly the kind of work you're pointing out.

dcloud commented 9 years ago

I completely understand! I wanted to make note of this since I ran across it and it seemed like a group of issues around SQL copy commands could indicate some tooling for SQL operations might be in order.

I'm spending some time looking into how the various database drives (mysqlclient, psycopg2) might be used to ameliorate the situations where you want to perform interactions closer to the database than the ORM level.