Class: QuickBase::TextData

Inherits:
Object
  • Object
show all
Defined in:
lib/QuickBaseTextData.rb

Overview

Class to read and write human-editable text files of data that can be sent to QuickBase. The file format can also be used as a simple intermediate format for getting data into QuickBase programmatically from other sources. This format is better than CSV for human-readability and allows fields to be skipped and to appear in any sequence.

The format is like yaml, probably isn’t a subset of it, but is simpler.

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(username, password) ⇒ TextData

Returns a new instance of TextData.



72
73
74
 
# File 'lib/QuickBaseTextData.rb', line 72

def initialize(username,password)
   @username,@password=username,password
end

Instance Attribute Details

#dbid ⇒ Object (readonly)

Returns the value of attribute dbid.



70
71
72
 
# File 'lib/QuickBaseTextData.rb', line 70

def dbid
  @dbid
end

Class Method Details

.downloadData(username, password, dbid) ⇒ Object 



526
527
528
529
 
# File 'lib/QuickBaseTextData.rb', line 526

def TextData.downloadData(username,password,dbid)
   td = QuickBase::TextData.new(username,password)
   td.writeDataFromQuickBase(dbid,"downloadedTextData.txt","textDataDownloadErrors.txt")
end

.synchDataFile(username, password, file) ⇒ Object

Sends data from file to QuickBase, then overwrites the same file with data from the last referenced table. It’s best to use this for synchronizing just one table’s data.



534
535
536
537
538
 
# File 'lib/QuickBaseTextData.rb', line 534

def TextData.synchDataFile(username,password,file)
   td = QuickBase::TextData.new(username,password)
   td.sendDataToQuickBase(file,"textDataUploadErrors.txt")
   td.writeDataFromQuickBase(td.dbid,file,"textDataDownloadErrors.txt")
end

.uploadData(username, password, file) ⇒ Object 



521
522
523
524
 
# File 'lib/QuickBaseTextData.rb', line 521

def TextData.uploadData(username,password,file)
   td = QuickBase::TextData.new(username,password)
   td.sendDataToQuickBase(file,"textDataUploadErrors.txt")
end

Instance Method Details

#addField(field, type, properties) ⇒ Object 



310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
 
# File 'lib/QuickBaseTextData.rb', line 310

def addField(field,type,properties)
   newFieldID, newfieldLabel = @qbc._addField(field, type)
   if newFieldID
      if properties
         fieldPropertiesToSet = Hash.new
         properties.each{|property|
            property.each{|propertyName,propertyValue| fieldPropertiesToSet[propertyName] = propertyValue }
         }
         @qbc._setFieldProperties(fieldPropertiesToSet,newFieldID)
         @qbc._getSchema
      end
   else
      raise "Error creating new field #{field}"
   end
end

#addFieldChoices ⇒ Object 



459
460
461
462
463
464
465
466
467
468
469
470
471
472
 
# File 'lib/QuickBaseTextData.rb', line 459

def addFieldChoices
  if @fieldIDChoicesToSet
    @fieldIDChoicesToSet.each{ |f,choices|
       @qbc._fieldAddChoices(f,choices) 
    }
    @fieldIDChoicesToSet = nil
  end
  if @fieldChoicesToSet
    @fieldChoicesToSet.each{ |f,choices|
       @qbc._fieldNameAddChoices(f,choices) 
    }
    @fieldChoicesToSet = nil
  end
end

#addFieldValuePairs ⇒ Object 



366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
 
# File 'lib/QuickBaseTextData.rb', line 366

def addFieldValuePairs
   @qbc.clearFieldValuePairList
   if @fieldValues
      @fieldValues.each{ |f,v|
         if f and v and @fieldProperties and @fieldProperties[f] and @fieldProperties[f]["fieldAllowsNewChoices"]
            @fieldChoicesToSet = Hash.new if @fieldChoicesToSet.nil?
            @fieldChoicesToSet[f] = Array.new if @fieldChoicesToSet[f].nil?
            @fieldChoicesToSet[f] << v 
         end
         if f and v and  @fieldProperties and @fieldProperties[f] and @fieldProperties[f]["fieldIsValidFileAttachment"]
            @qbc.addFieldValuePair(f.dup,nil,v.dup,nil)
         else
            @qbc.addFieldValuePair(f.dup,nil,nil,v.dup)
         end
      }
      @fieldValues = nil
   end
   if @fieldIDValues
      @fieldIDValues.each{ |f,v|
         if @fieldIDProperties and @fieldIDProperties[f] and @fieldIDProperties[f]["fieldAllowsNewChoices"]
            @fieldIDChoicesToSet = Hash.new if @fieldChoicesToSet.nil?
            @fieldIDChoicesToSet[f] << v 
         end
         if @fieldIDProperties and @fieldIDProperties[f] and @fieldIDProperties[f]["fieldIsValidFileAttachment"]
            @qbc.addFieldValuePair(nil,f.dup,v.dup,nil)
         else
            @qbc.addFieldValuePair(nil,f.dup,nil,v.dup)
         end
      }
      @fieldIDValues = nil
   end
   @qbc.fvlist
end

#addOrEditRecord ⇒ Object 



436
437
438
439
440
441
442
 
# File 'lib/QuickBaseTextData.rb', line 436

def addOrEditRecord
   if @activeRecordNumber
      editRecord
   elsif isValidRecord?(true)
      addRecord
   end
end

#addRecord ⇒ Object 



444
445
446
447
448
449
 
# File 'lib/QuickBaseTextData.rb', line 444

def addRecord
   if addFieldValuePairs
      addFieldChoices
      @qbc.addRecord(@qbc.dbid, @qbc.fvlist)
   end
end

#appendFieldValue(value) ⇒ Object 



352
353
354
355
356
357
358
359
360
361
362
363
364
 
# File 'lib/QuickBaseTextData.rb', line 352

def appendFieldValue(value)
   if value and value.length  > 0
      if @activeField and @fieldProperties[@activeField]["fieldIsMultiLineText"]
         @fieldValues = Hash.new if @fieldValues.nil?
         @fieldValues[@activeField] << "\n"
         @fieldValues[@activeField] << value.dup
      elsif @activeFieldID and @fieldIDProperties[@activeFieldID]["fieldIsMultiLineText"]
         @fieldIDValues = Hash.new if @fieldIDValues.nil?
         @fieldIDValues[@activeFieldID] << "\n"
         @fieldIDValues[@activeFieldID] << value.dup
      end
   end
end

#checkFieldNameAndValue(fieldName, fieldValue) ⇒ Object 



206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
 
# File 'lib/QuickBaseTextData.rb', line 206

def checkFieldNameAndValue(fieldName,fieldValue)

   @fieldNameIsFieldID = false
   @fieldAllowsNewChoices = false
   @fieldIsMultiLineText = false
   @fieldIsValidFileAttachment = false
   @fieldType = nil
   @fieldProperties = nil

   fieldElement = @qbc.lookupField( @qbc.lookupFieldIDByName(fieldName) )
   if !fieldElement and fieldName.match(/[0-9]+/) # if it's a number, see if it's a valid field id
      fieldElement = @qbc.lookupField(fieldName,fieldValue)
      @fieldNameIsFieldID = true if fieldElement
   end
   
   if !fieldElement # field doesn't exist. the field can be created if the value is actually a valid field type
      if fieldValue and fieldValue.length > 0
         fieldValue.strip!
         if fieldValue.length > 0
            fieldTypeAndProperties = fieldValue.split(/ /)
            type = fieldTypeAndProperties[0]
            if @qbc.isValidFieldType?(type)
               @fieldType = Hash.new if @fieldType.nil?
               @fieldType[fieldName] = type.dup
               fieldTypeAndProperties.shift # anything after the field type must be valid field properties in the form property="value"
               fieldTypeAndProperties.each { |property|
                  propertyType = property[0..(property.index("=")-1)]
                  raise "Invalid field property type #{propertyType}" if !@qbc.isValidFieldProperty?(propertyType) 
                  propertyValue = property[(property.index("=")+1)..(property.rindex("\"")-1)]
                  raise "Missing value for field property #{propertyType}" if propertyValue.nil? or propertyValue.length == 0
                  @fieldProperties = Hash.new if @fieldProperties.nil?
                  propertyAndValuePair = Hash.new
                  propertyAndValuePair[propertyType]=propertyValue
                  @fieldProperties[fieldName] << propertyAndValuePair
               }
            else
               raise "Invalid field type #{type}."
            end
         else
            raise "Invalid field name #{fieldName}."
         end
      end
   else 
      # do basic data type check
      type = fieldElement.attributes["field_type"].dup
      raise "Unable to determine data type for #{fieldName} field" if type.nil?
      if fieldValue.length > 0 #any field can be blanked out
         case type
            when "checkbox"
               if !(fieldValue == "1" or fieldValue == "0")
                  raise "Invalid data '#{fieldValue}' for checkbox field #{fieldName}"
               end
            when "date"
               fieldValue.gsub!("/","-")
               if !fieldValue.match(/[0-9][0-9]\-[0-9][0-9]\-[0-9][0-9][0-9][0-9]/)
                  raise "Invalid data '#{fieldValue}' for date field #{fieldName}"
               end
            when "duration", "float", "currency", "rating"
               fieldValue.gsub!(",","")
               if !fieldValue.match(/[0-9]*\.?[0-9]*/)
                  raise "Invalid data '#{fieldValue}' for field #{fieldName}"
               end
            when "phone"
               if !fieldValue.match(/[0-9|\.|x]+/)
                  raise "Invalid data '#{fieldValue}' for phone field #{fieldName}"
               end
            when "file"
               if FileTest.exist?(fieldValue)
                  @fieldIsValidFileAttachment = true
               else
                  raise "Invalid file name '#{fieldValue}' for file attachment field #{fieldName}"
               end
         end
      end
      # check whether field allows user to add to a choicelist.  
      @fieldAllowsNewChoices = fieldAllowsNewChoices?(fieldElement)
      # check whether field allows mutliple lines.  
      @fieldIsMultiLineText = fieldIsMultiLineText?(fieldElement)
   end
   fieldElement
end

#editRecord ⇒ Object 



451
452
453
454
455
456
457
 
# File 'lib/QuickBaseTextData.rb', line 451

def editRecord
   if @activeRecordNumber and addFieldValuePairs
      addFieldChoices
      @qbc.editRecord(@qbc.dbid, @activeRecordNumber, @qbc.fvlist)
      @activeRecordNumber = nil
   end
end

#fieldAllowsNewChoices?(fieldElement) ⇒ Boolean

Returns:

  • (Boolean) 


288
289
290
291
292
293
294
295
296
297
 
# File 'lib/QuickBaseTextData.rb', line 288

def fieldAllowsNewChoices?(fieldElement)
   allowsUserChoices = false
   findPropertyBlock = proc { |element|
      if element.is_a?(REXML::Element) and element.name == "allow_new_choices" and element.has_text?
         allowsUserChoices = (element.text == "1")
      end
   }
   @qbc.processChildElements(fieldElement, true, findPropertyBlock)
   allowsUserChoices
end

#fieldIsMultiLineText?(fieldElement) ⇒ Boolean

Returns:

  • (Boolean) 


299
300
301
302
303
304
305
306
307
308
 
# File 'lib/QuickBaseTextData.rb', line 299

def fieldIsMultiLineText?(fieldElement)
   isMultiLineText = false
   findPropertyBlock = proc { |element|
      if element.is_a?(REXML::Element) and element.name == "num_lines" and element.has_text?
         isMultiLineText = (element.text != "1")
      end
   }
   @qbc.processChildElements(fieldElement, true, findPropertyBlock)
   isMultiLineText
end

#isValidRecord?(addingRecord) ⇒ Boolean

Returns:

  • (Boolean) 


400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
 
# File 'lib/QuickBaseTextData.rb', line 400

def isValidRecord?(addingRecord)
   valid = true
   if addingRecord and @qbc.fields
      # check that all required fields are present
      requiredFieldIDs = Hash.new
      requiredFields = Hash.new
      fieldID = ""
      fieldName = ""
      findRequiredFieldsBlock = proc { |element|
         if element.is_a?(REXML::Element) and element.name == "field"
            fieldID = element.attributes["id"]
         elsif element.is_a?(REXML::Element) and element.name == "label" and element.has_text?
            fieldName = element.text
         elsif element.is_a?(REXML::Element) and element.name == "required" and element.has_text?
            if element.text == "1"
               requiredFieldIDs[fieldID] = "1" 
               requiredFields[fieldName] = "1" 
            end
         end
      }
      @qbc.processChildElements(@qbc.fields, false, findRequiredFieldsBlock)
      
      if @fieldValues
         @fieldValues.each{ |f,v| requiredFields[f] = nil if requiredFields[f] }
      end
      if @fieldIDValues
         @fieldIDValues.each{ |f,v| requiredFieldIDs[f] = nil if requiredFieldIDs[f]  }
      end
      missingFields = ""
      requiredFields.each{ |f,v| missingFields << "#{f} " if v } if @fieldValues
      requiredFieldIDs.each{ |f,v| missingFields << "#{f} " if v } if @fieldIDValues
      raise "Required fields are missing: #{missingFields}" if missingFields.length > 0
   end
   valid
end

#login ⇒ Object 



132
133
134
135
136
137
138
139
 
# File 'lib/QuickBaseTextData.rb', line 132

def 
   if @username.nil? or @password.nil?
      writeError("Must set username and password") 
   elsif @qbc.nil?
      @qbc = QuickBase::Client.new(@username,@password)
   end
   ret = @qbc and @qbc.requestSucceeded
end

#logout ⇒ Object 



141
142
143
144
 
# File 'lib/QuickBaseTextData.rb', line 141

def logout
   @qbc.signOut if @qbc
   @qbc = nil
end

#resetTableVars ⇒ Object 



146
147
148
149
150
151
152
153
154
155
156
157
 
# File 'lib/QuickBaseTextData.rb', line 146

def resetTableVars
   @activeRecordNumber = nil
   @activeFieldID = nil
   @activeField = nil
   @fieldIDValues = nil
   @fieldValues = nil
   @fieldType = nil
   @fieldProperties = nil
   @fieldAllowsNewChoices = false
   @fieldIsMultiLineText = false
   @fieldIsValidFileAttachment = false
end

#sendDataToQuickBase(inputFilename, errorFilename) ⇒ Object

Read a file in the format described above and send the data to QuickBase.

Records are added or edited when tables, fields and values have been validated
and all the data for a record has been accumulated.
Any error in the input file will stop further processing of the file.



80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
# File 'lib/QuickBaseTextData.rb', line 80

def sendDataToQuickBase(inputFilename, errorFilename)
      @errorFilename = errorFilename
      if FileTest.exist?(inputFilename) 
         if 
            lineNumber = 1
            begin
               IO.foreach(inputFilename){|line|
                  if line.index( "application:") == 0
                     line.sub!("application:","")
                     line.strip!
                     setApplication(line)
                  elsif line.index( "table:") == 0
                     line.sub!("table:","")
                     line.strip!
                     setTable(line)
                  elsif line.index( "dbid:") == 0
                     line.sub!("dbid:","")
                     line.strip!
                     setDBID(line)
                  elsif line.index( "record:") == 0
                     line.sub!("record:","")
                     line.strip!
                     if line.match(/[0-9]+/)
                        setRecord(line)
                     else
                        setRecord(nil)
                     end
                  elsif line.match( /^([^:]+):(.*)$/)
                     setFieldValue($1, $2)
                  elsif line.match( /^([\s\t])(.*)$/)
                     appendFieldValue($2)
                  end
                  lineNumber = lineNumber + 1
               }
               addOrEditRecord
            rescue StandardError => error
               writeError("File #{inputFilename}, line #{lineNumber}: #{error}.")
            end
         else
            writeError("Error connecting to QuickBase.")
         end
         logout
      else   
         writeError("Input file #{inputFilename} does not exist.")
      end
end

#setApplication(appName) ⇒ Object 



159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
 
# File 'lib/QuickBaseTextData.rb', line 159

def setApplication(appName)
   addOrEditRecord
   resetTableVars
   @singleTableApplication = false
   @qbc.findDBByname(appName)
   if @qbc.dbid.nil?
      @qbc.createDatabase(appName,appName)
      raise "Error creating application '#{name}'" if @qbc.dbid.nil?
      raise "Unable to find table '#{@appName}'" if !@qbc.lookupChdbid(@appName)
      @singleTableApplication = true
   end
   raise "Error finding or creating application '#{name}'" if @qbc.dbid.nil?
   @appName = appName
   @qbc._getSchema
   @dbid = @qbc.dbid.dup
end

#setDBID(id) ⇒ Object 



186
187
188
189
190
191
192
193
 
# File 'lib/QuickBaseTextData.rb', line 186

def setDBID(id)
   addOrEditRecord
   resetTableVars
   @singleTableApplication = false
   @qbc.getSchema(id)
   raise "Unable to find table with the '#{id}' id" if @qbc.dbid.nil?
   @dbid = @qbc.dbid.dup
end

#setFieldValue(fieldName, value) ⇒ Object 



326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
 
# File 'lib/QuickBaseTextData.rb', line 326

def setFieldValue(fieldName, value)
   if checkFieldNameAndValue(fieldName,value)
      if @fieldNameIsFieldID
         @activeFieldID = fieldName
         @activeField = nil
         @fieldIDValues = Hash.new if @fieldIDValues.nil?
         @fieldIDValues[@activeFieldID] = value.dup
         @fieldIDProperties = Hash.new if @fieldIDProperties.nil?
         @fieldIDProperties[@activeFieldID] = { "fieldAllowsNewChoices"=>@fieldAllowsNewChoices, "fieldIsMultiLineText"=>@fieldIsMultiLineText, "fieldIsValidFileAttachment"=>@fieldIsValidFileAttachment }
      else
         @activeField = fieldName
         @activeFieldID = nil
         @fieldValues = Hash.new if @fieldValues.nil?
         @fieldValues[@activeField] = value.dup
         @fieldProperties = Hash.new if @fieldProperties.nil?
         @fieldProperties[@activeField] = { "fieldAllowsNewChoices"=>@fieldAllowsNewChoices,"fieldIsMultiLineText"=>@fieldIsMultiLineText, "fieldIsValidFileAttachment"=>@fieldIsValidFileAttachment }
      end
   elsif @fieldType and @fieldType[fieldName]
      if @fieldProperties and @fieldProperties[fieldName]
         addField(fieldName,@fieldType[fieldName],@fieldProperties[fieldName])
      else
         addField(fieldName,@fieldType[fieldName],nil)
      end
   end
end

#setRecord(number) ⇒ Object 



195
196
197
198
199
200
201
202
203
204
 
# File 'lib/QuickBaseTextData.rb', line 195

def setRecord(number)
   addOrEditRecord
   @activeRecordNumber = number
   @activeRecordNumber = nil if @activeRecordNumber and @activeRecordNumber.length == 0
   if @activeRecordNumber
      @qbc._setActiveRecord(@activeRecordNumber)
   else   
      @qbc.resetrid
   end   
end

#setTable(name) ⇒ Object 



176
177
178
179
180
181
182
183
184
 
# File 'lib/QuickBaseTextData.rb', line 176

def setTable(name)
   addOrEditRecord
   resetTableVars
   if !@singleTableApplication
      raise "Unable to find table '#{name}'" if !@qbc.lookupChdbid(name)
      @qbc._getSchema
   end
   @dbid = @qbc.dbid.dup
end

#writeDataFromQuickBase(dbid, outputFilename, errorFilename) ⇒ Object

Writes all records and all fields from a table to the specified 

outputFilename, in the format specified above, sorted by record ID#.
Since they can't be sent back into QuickBase,  built-in fields are excluded.



477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
 
# File 'lib/QuickBaseTextData.rb', line 477

def writeDataFromQuickBase(dbid, outputFilename, errorFilename)
  @errorFilename = errorFilename
   if 
      @qbc.getSchema(dbid)
      if @qbc.dbid and @qbc.requestSucceeded
         @outputFile = File.new(outputFilename,"w")
         @outputFile.puts( "dbid:#{dbid}")
         @isBuitlInField = false
         recordIDs = @qbc.getAllValuesForFields(dbid,["Record ID#"],nil,nil,nil,"3","3")
         if recordIDs
            recordIDs["Record ID#"].each{ |recordID|
               @outputFile.puts( "record:#{recordID}")
               @qbc._getRecordInfo(recordID)
               processFieldDataBlock = proc { |element|
                  if element.is_a?(REXML::Element)
                     if element.name == "fid"
                        @isBuitlInField = element.text.to_i < 6
                     elsif element.name == "name" and !@isBuitlInField
                        @outputFile.print("#{element.text}:")
                     elsif element.name == "type"
                        @outputFieldType = element.text.dup.downcase!
                     elsif element.name == "value"  and !@isBuitlInField
                        outputFieldValue = ""
                        outputFieldValue = element.text.dup if element.has_text?
                        outputFieldValue.gsub!("<BR/>","\n ")
                        outputFieldValue = @qbc.formatFieldValue(outputFieldValue,@outputFieldType)
                        outputFieldValue = "" if outputFieldValue.nil?
                        @outputFile.puts(outputFieldValue)
                     end
                  end
               }
               @qbc.processChildElements(@qbc.field_data_list,true,processFieldDataBlock)
            }
            @outputFile.close
         end
      else
         writeError("Invalid dbid #{dbid}.")
      end
      logout
   else
      writeError("Error connecting to QuickBase.")
   end
end

#writeError(error) ⇒ Object 



127
128
129
130
 
# File 'lib/QuickBaseTextData.rb', line 127

def writeError(error)
   @errorFile = File.new(@errorFilename,"w") if @errorFilename and @errorFile.nil?
   @errorFile.puts(error) 
end