db_automate

DBAutomate

If you actually happened upon this, please note that this project is in its earliest stages and has not been developed much yet. The README found here is mainly for the purposes of Readme Driven Development so that I can try to get the API right first, and hopefully eliminate lots of churn along the way.

If you dare to read on, your suggestions are welcome if you believe you have an idea that would make the API and project as a whole better. Just drop me a line, or better yet fork the project and create a pull request showing your thoughts.

Let it be known that I have many ideas swarming on how to expand this project to do more, but I want to start small and get some basics right first. So for now, it is what it is, or more accurately, it will be what it is below.

Yes, I know what is below is quite messy at this stage, and there is a lot of stuff which will quickly become too much stuff and need to be moved to a wiki somewhere with dense digital vegetation. Bare with me!

Installation

Add this line to your application's Gemfile:

gem 'db_automate'

And then execute:

$ bundle

Or install it yourself as:

$ gem install db_automate

Usage

Initialize a query object with a sql statement and run the query against database 'my_db'

my_query = Query.new(:sql => 'select first_name, last_name, email from users')
my_query.run(:db => :my_db)

Give your query a name to make it more my_query.name = 'user info'

Most query attributes are able to be set in an initialization block

my_query = Query.new do |qry|
	qry.name = 'user info'
	qry.db = :my_db
	qry.sql = 'select first_name, last_name, email from users'
	qry.export 'my_query_results', :csv, :xlsx, :export_dir => 'Desktop/query_data'
end

The key passed to #db should be a top-level key in your YAML config file.

my_db:
	adapter: mysql
	server: localhost
	username: dave
	password: parties

Tell DBAutomate where to find your config file:

DBAutomate.config_file = 'my/db/configs.yml'

For more complex SQL, pass in the path (relative or absolute) to a file containing your sql

my_query.sql = 'sql/my_select_query.sql'

By default if only a file name is given and no path, then DBAutomate will search in the current directory and in a subdirectory named 'sql' if it exists. You may change the default search path of DBAutomate:

DBAutomate.sql_dir = 'relative_path/'
DBAutomate.sql_dir = '/Users/me/store/all/my/sql/here/absolutely'

And execute the query

my_query.run

Check for results and do stuff with them

if my_query.results?
	# do things. . .
end

results = my_query.results
puts 'query #{my_query.name} returned #{results.length} rows'
puts 'columns: #{results.headers.join(", ")}'

Export your results

my_query.export_csv
# => creates user_info.csv in the current directory

my_query.export_xlsx :export_dir => '/home/users/me'
# => create user_info.xlsx in /home/users/me

For a more configurable multi format export, you may like the #export method. Pass it a configuration hash. . .

my_query.export :format => [:csv, :xlsx], :file_name => 'user info query results', :export_dir => DESKTOP_PATH

. . . Or a block

my_query.export do |exp|
	exp.format = :csv, :xlsx
	exp.headers = false
	exp.file_name = 'user info query results'
	exp.export_dir = DESKTOP_PATH
end

Or configure a default export directory for DBAutomate

DBAutomate.export_dir = '/my/given/export/path'

Contributing

1. Fork it ( https://github.com/DRowan99/db_automate/fork )
2. Create your feature branch (git checkout -b my-new-feature)
3. Commit your changes (git commit -am 'Add some feature')
4. Push to the branch (git push origin my-new-feature)
5. Create a new Pull Request