I have more or less completed this project, so I thought I would post the final script. The script is in ruby and accepts the name of an input file and the name of the database,
ruby loader.rb inputFilename databaseName The input file uses metadata rows to identify the database table that the column will go to, the data type, and the column name. The unique fields iK1, iK2, iK3, iK4 are how the data for the primary table is registered for each input record. If a record is inserted and the database already contains a record with the same value for these 4 ints, there will be an exception. The script will create the database if it doesn't exist, but can also be used to insert records to an existing database. The "structure" table is required with all 9 fields not null. The other satellite tables are optional. Additional tables can be added by adding new columns with the table name in the first row, the data type for the column in the second row, and the column name in the third row. All fields in the satellite tables are not null by default. The script is below, but I have also attached it with a sample input file. sample_files_sqlite_ruby_loader.zip <http://sqlite.1065341.n5.nabble.com/file/n69543/sample_files_sqlite_ruby_loader.zip> Thanks for the assistance. I am now working on a script to add/change data in the existing records in a database, as well as a simple query script to export data to a delimited text file. *LMHmedchem* # script loader.rb require 'rubygems' require 'sqlite3' require 'logger' class DataLoader attr_accessor :db_name, :current_row, :table_names, :field_names, :data_types, :line_number, :db, :ready, :structure_id UNIQUE_FIELDS = %w(iK1 iK2 iK3 iK4) def initialize(db_path) @db_name = db_path @table_names = [] @field_names = {} @data_types = {} @ready = false @log = Logger.new('sqlite_data_loader.log', 'weekly') db_connect end def db_connect @db = SQLite3::Database.open @db_name # Enabling foreign_keys features @db.execute('PRAGMA foreign_keys = ON') @ready = true end def load_data(tsv_file) @line_number = 1 File.open(tsv_file, 'r') do |f1| while line = f1.gets @current_row = line.strip.split("\t") next if @current_row.empty? if @line_number <= 3 collect_table_info # After processing third line, we can create tables with the information collected. create_tables if @line_number == 3 else collect_table_data end @line_number += 1 end end end def collect_table_info if @line_number == 1 @current_row.uniq.each do |name| @table_names << [name, @current_row.count(name)] end else offset = 0 @table_names.each do |table_name, fields_count| @data_types[table_name] = @current_row[offset..(offset + fields_count - 1)] if @line_number == 2 @field_names[table_name] = @current_row[offset..(offset + fields_count - 1)] if @line_number == 3 offset += fields_count end end end def create_tables @table_names.each do |table_name, arity| create_table table_name end end def create_table(table_name) query = "CREATE TABLE IF NOT EXISTS #{table_name}(#{fields_for table_name})" @log.debug query @db.execute query unless 'structure' == table_name @log.debug "Creating index on #{table_name}" @db.execute "CREATE INDEX IF NOT EXISTS idx_#{table_name}_structure_id ON #{table_name}(structure_id)" end rescue SQLite3::Exception => e @log.debug "Exception occured #{e.class}: #{e.message}" end def fields_for(table_name) list = [] if 'structure' == table_name list << 'id INTEGER PRIMARY KEY AUTOINCREMENT' else list << 'structure_id INTEGER NOT NULL' end fields = [] @field_names[table_name].each_with_index do |field, index| next if 'primary_key' == field next if fields.member?(field) list << "'#{field}' #{field_data_type(table_name, index)}" fields << field end list << add_unique_fields(table_name) list << add_foreign_key_constraint(table_name) list.compact.join(', ') end def field_data_type(table_name, index) kind = @data_types[table_name][index].upcase kind = 'TEXT' if kind.start_with?('STR') "#{kind}#{' NOT NULL' if 'structure' == table_name}" end def add_unique_fields(table_name) available_fields = (@field_names[table_name] & UNIQUE_FIELDS) unless available_fields.empty? "UNIQUE(#{available_fields.join(', ')}) ON CONFLICT ROLLBACK" end end def add_foreign_key_constraint(table_name) unless 'structure' == table_name 'FOREIGN KEY(structure_id) REFERENCES Structure(id)' end end def insert_into_fields(table_name, values) field_names = @field_names[table_name] values_list, attr_names = [], [] if 'structure' == table_name attr_names << 'id' values_list << nil elsif @structure_id && values attr_names << 'structure_id' values_list << @structure_id end values.each_with_index do |value, indx| field_name = field_names[indx] next if 'primary_key' == field_name next if attr_names.member?(field_name) attr_names << field_name if 'NULL' == value values_list << nil else values_list << value end end [attr_names, values_list] end def collect_table_data offset = 0 @structure_id = nil @table_names.each do |table_name, fields_count| values_array = @current_row[offset..(offset + fields_count - 1)] begin fields, values = insert_into_fields(table_name, values_array) next if fields.empty? if values_array && !values_array.empty? && ('structure' == table_name or @structure_id) @log.debug "Populating #{table_name}" @db.execute "INSERT INTO #{table_name} (#{fields.collect {|name| %{'#{name}'}}.join ','}) VALUES(#{(['?'] * values.size).join ','})", values @structure_id = @db.last_insert_row_id if 'structure' == table_name else @log.debug "Skipping population of `#{table_name}` with #{values.inspect} due to structure data existance." end rescue SQLite3::Exception => e @log.debug "Exception occured while importing data into `#{table_name}`: #{e.message}" return rescue Exception => e @log.debug "Exception occured while importing data into `#{table_name}`: #{e.class} #{e.message}" fail end offset += fields_count end end end if ARGV.empty? || ARGV.length < 2 puts "Usage: #{File.basename __FILE__} data.tsv database.sqlite3" else loader = DataLoader.new(ARGV[1]) if loader.ready loader.load_data ARGV[0] end end -- View this message in context: http://sqlite.1065341.n5.nabble.com/inserting-record-data-into-mutliple-tables-with-a-composite-primary-key-tp64874p69543.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
