Project

dbx

0.0
No commit activity in last 3 years
No release in over 3 years
dbxddrscott/dbxHomepageDocumentationSource CodeBug TrackerWiki
Database utilities
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
 Popularity
Downloads
10,615
Stars
0
Forks
0
Watchers
3
 Releases
Current version
0.2.2
Total releases
6
First release
2018-08-22
Latest release
2018-08-24
 Development
Primary Language
Ruby
Licenses
MIT
Average date of last 50 commits
2018-08-24
Reverse Dependencies
0

 Dependencies

Development

bundler
~> 1.16
rake
~> 10.0
rspec
~> 3.0

Runtime

activerecord
~> 4.0
activesupport
~> 4.0
thor
>= 0
 Project Readme

DBX

Database eXtras for working with CSV files in a Postgres database.

We currently only support Postgres database, but others will be supported soon.

Usage Examples

Import CSV file into database

Column type detection is performed based on column contents. By default the new table name will be the file name minus its extension, indexes are added to column matching ^(\w+_id|id)$.

dbx import path/to/data.csv --name data_v1 --db postgres://localhost/scratch --column-patterns _ref$:string
# --db   : Used to define where to put the table.
# --column-patterns : [] Override the detected column type.
# --name : [data] Optional override to default table name of file's base name without extension.
# --sample : [100] Number of rows to sample during column detection.
# --force : [false] Drops the destination table if it exists.
# --auto-index-pattern : [^(\w+_id|id)$] Creates indexes for columns matching the pattern

Create diff table of two tables in a database

  • The new table will be named diff_data_v1_data_v2.
  • It will contains column_a, column_b, column_diff. Where column is every column_ from data_v1 and column_diff is a simple difference representation of columns _a and _b.
    • Columns _a and _b can be omitted with --no-a-b 
dbx diff data_v1 data_v2 --db postgres://localhost/scratch --using id
# --db   : Used to define where to put the table.
# --using : Space delimited list of join columns.
# --no-a-b: [false] Omit the `_a` and `_b` columns showing the source data.
# --force : [false] Drops the destination diff table if it exists.

Import and diff two CSV files

Do the import and diff all at once!!!

dbx import_diff /path/to/data_v1.csv /path/to/data_v2.csv --db postgres://localhost/scratch --using id
# --db   : Used to define where to put the table.
# --column-patterns : [] Override the detected column type.
# --sample : [100] Number of rows to sample during column detection.
# --auto-index-pattern : [^(\w+_id|id)$] Creates indexes for columns matching the pattern
# --using : Space delimited list of join columns.
# --no-a-b: [false] Omit the `_a` and `_b` columns showing the source data.
# --force : [false] Drops the destination diff table if it exists.

List of Commands dbx help 

Commands:
  dbx create SRC               # Create a table with types from SRC CSV file
  dbx diff TABLE_A TABLE_B     # Create diff table between TABLE_A and TABLE_B.
  dbx help [COMMAND]           # Describe available commands or one specific command
  dbx import SRC               # Import SRC CSV into table
  dbx import_diff SRC_A SRC_B  # Import then diff between SRC_A CSV and SRC_B CSV files.
  dbx types SRC                # Detect column types give a SRC CSV file

Options:
  [--db=Database URL: adapter://user:pass@host:port/db_name]
  [--column-patterns=List of column patterns to override type info]
  [--sample=Number of rows to sample for type detection]
                                                                     # Default: 100
  [--auto-index-pattern=Add index when column matches pattern]
                                                                     # Default: ^(\w+_id|id)$

Configuration

If the current path contains a dbx.yml file, it will be read first. Settings in the config file can still be overridden by command line arguments.

# Database receiving the imported and diff data.
db: postgres://localhost/scratch

# Column patterns are used to override column detection based on the a matched pattern in the name.
# This is useful for things like phone numbers and zip codes where they look like numbers, but should be strings.
column_patterns:
  phone_number: :string
  zipcode:      :string
  zip_code:     :string
  external_ref: :string

# Number of rows to sample for type detection
sample: 100

# Add index if column matches this pattern.
auto_index_pattern: _id$

Installation

Add this line to your application's Gemfile:

gem 'dbx'

And then execute:

$ bundle

Or install it yourself as:

$ gem install dbx

Development

After checking out the repo, run bin/setup to install dependencies. Then, run rake spec to run the tests. You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and tags, and push the .gem file to rubygems.org.

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/ddrscott/dbx.

License

The gem is available as open source under the terms of the MIT License.