Module: D3::Database

Defined in:: lib/d3/database.rb

Constant Summary collapse

TRUE_VAL = Booleans are stored as 1’s and 0’s in the db.

FALSE_VAL =

SCHEMA_TABLE = This table has info about the JSS schema

'db_schema_information'.freeze

MIN_SCHEMA_VERSION = the minimum JSS schema version allowed

'9.4'.freeze

MAX_SCHEMA_VERSION = the max JSS schema version allowed

"10.999.0"

EPOCH_TO_TIME = Ruby Time objects are stored as JSS epochs (unix epoch plus milliseconds)

proc { |v| v.nil? || v.to_s.empty? ? nil : JSS.epoch_to_time(v) }

TIME_TO_EPOCH = JSS epochs (unix epoch plus milliseconds) as used as Ruby Time objects

proc { |v| v.nil? || v.to_s.empty? ? nil : v.to_jss_epoch }

STRING_TO_INT = Integers come from the database as strings, but empty ones should be nil, not zero, as #to_i would do

proc { |v| v.nil? || v.to_s.empty? ? nil : v.to_i }

COMMA_STRING_TO_ARRAY = Some values are stored as comma-separated strings, but used as Arrays

proc { |v| JSS.to_s_and_a(v)[:arrayform] }

COMMA_STRING_TO_ARRAY_OF_PATHNAMES = Some values are stored as comma-separated strings, but used as Arrays of Pathnames

proc { |v| JSS.to_s_and_a(v)[:arrayform].map { |p| Pathname.new(p) } }

ARRAY_OF_PATHNAMES_TO_COMMA_STRING =

proc { |v| v.is_a?(Array) ? v.join(', ') : '' }

ARRAY_TO_COMMA_STRING = Some values are used as Arrays but stored as comma-separated strings

proc { |v| JSS.to_s_and_a(v)[:stringform] }

RUBY_TO_YAML = Some values are stored in the DB as YAML dumps

proc { |v| YAML.dump v }

YAML_TO_RUBY =

proc { |v| YAML.load v.to_s }

BOOL_TO_INT = Booleans are stored as zero and one

proc { |v| v == true ? TRUE_VAL : FALSE_VAL }

INT_TO_BOOL = Booleans are stored as zero and one

proc { |v| v.to_i == FALSE_VAL ? false : true }

STRING_TO_REGEXP = Regexps are stored as strings

proc { |v| v.to_s.empty? ? nil : Regexp.new(v.to_s) }

REGEXP_TO_STRING = Regexps are stored as strings

proc { |v| v.to_s }

STATUS_TO_STRING = Status values are stored as strings, but used as symbols

proc { |v| v.to_s }

STRING_TO_STATUS =

proc { |v| v.to_sym }

STRING_TO_PATHNAME = Expiration paths are stored as strings, but used as Pathnames

proc { |v| Pathname.new v.to_s }

PATHNAME_TO_STRING =

proc { |v| v.to_s }

PACKAGE_TABLE = The MySQL table that defines which JSS Packages are a part of d3 This complex Hash contains all the data needed to create and work with the d3 Packages table. The Hash contains these keys & values: :table_name [String] the name of the table in the database :other_indexes [Array<String>] SQL clauses for defining multi-field indexes in the CREATE TABLE statement. Single-field indexes are defined in the field definitions. :field_definitions [Hash<Hash>] The definitions of the fields in the table, used throughout the D3 module to refer to the data from the database. The keys are also used as the attribute names of DD3::Package objects Each field definition is a subHash with these keys: :field_name [String] the name of the field in the table :sql_type [String] The SQL data type clause and options for creating the field in the table :index [Boolean,Symbol] How should the field be indexed in the table? One of: true, :primary, :unique or nil/false :to_sql [Proc] the Proc to call with a Ruby object, to convert it to the storable format for the field. Integers and Strings don’t need conversion. Mysql.encode will be called on all values automatically. :to_ruby [Proc] The Proc to call with a MySQL return value, to convert it to the Ruby class used by this module. nil if the value should be a String in Ruby. The fields in the table, their Ruby classes, and their meanings are: :id [Integer] the JSS::Package id of this package :basename [String] the basename to which this pkg belongs :version [String] the version number for this basename, installed by this package :revision [Integer] the d3 pkg-revision number of this version. I.e. how many times has this basename-version been added to d3? :apple_receipt_data [Array<Hash>] the apple package data for this pkg and all it’s sub-pkgs Each Hash contains these keys for each pkg installed - :apple_pkg_id The identifier for the item, e.g. com.avid.edlmanager.pkg - :version The version installed, which might not match the version of the metapkg - :installed_kb The disk spaced used by this pkg when installed When .[m]pkgs are installed, the identifiers and metadata for each are recorded in the OS’s receipts database and are accessible via the pkgutil command. (e.g. pkgutil –pkg-info com.company.application). Storing the apple rcpt data in the DB allows us to do uninstalls and other client tasks without needing to index the pkg in jamf. This is stored in the DB as a YAML string :added_date [Time] when was this package was added to d3 :added_by [String,nil] the login name of the admin who added this packge to d3 :status [Integer] the status of this pkg, one of Basename::STATUSES :release_date [Time,nil] when was this package made live in d3 :released_by [String,nil] the login name of the admin who made it live :auto_groups [Array] a list of JSS::ComputerGroup names whose members get this package installed automatically. The special value :standard means all computers get this package automatically, except those in excluded groups. :excluded_groups [Array] a list of JSS::ComputerGroup names for whose members this package is not available without force :triggers_swu [Boolean] when installed, will this package trigger a GUI software update check, either immediately if there’s a console user, or at the next console login? :prohibiting_processes [Array<String>] An array of strings for matching to the output lines of ‘/bin/ps -A -c -o comm’. If there’s a matching line, this pkg won’t be installed :remove_first [Boolean] should any currently installed versions of this basename be uninstalled (if possible) before installing this package? :pre_install_id [Integer,nil] the JSS::Script id of the pre-install script, if any :post_install_id [Integer,nil] the JSS::Script id of the post-install script, if any :pre_remove_id [Integer,nil] the JSS::Script id of the pre-remove script, if any :post_remove_id [Integer,nil] the JSS::Script id of the post-remove script, if any See also the attributes of Package, which mostly mirror the

{
  table_name: 'd3_packages',

  field_definitions: {

    id: {
      field_name: 'package_id',
      sql_type: 'int(11) NOT NULL',
      index: :unique,
      to_sql: nil,
      to_ruby: STRING_TO_INT
    },

    basename: {
      field_name: 'basename',
      sql_type: 'varchar(60) NOT NULL',
      index: true,
      to_sql: nil,
      to_ruby: nil
    },

    version: {
      field_name: 'version',
      sql_type: 'varchar(30) NOT NULL',
      index: nil,
      to_sql: nil,
      to_ruby: nil
    },

    revision: {
      field_name: 'revision',
      sql_type: 'int(4) NOT NULL',
      index: nil,
      to_sql: nil,
      to_ruby: STRING_TO_INT
    },

    apple_receipt_data: {
      field_name: 'apple_receipt_data',
      sql_type: 'text',
      index: nil,
      to_sql: RUBY_TO_YAML,
      to_ruby: YAML_TO_RUBY
    },

    added_date: {
      field_name: 'added_date_epoch',
      sql_type: 'bigint(32) DEFAULT NULL',
      index: nil,
      to_sql: TIME_TO_EPOCH,
      to_ruby: EPOCH_TO_TIME
    },

    added_by: {
      field_name: 'added_by',
      sql_type: 'varchar(30)',
      index: nil,
      to_sql: nil,
      to_ruby: nil
    },

    status: {
      field_name: 'status',
      sql_type: "varchar(30) DEFAULT 'pilot'",
      index: nil,
      to_sql: STATUS_TO_STRING,
      to_ruby: STRING_TO_STATUS
    },

    release_date: {
      field_name: 'release_date_epoch',
      sql_type: 'bigint(32)  DEFAULT NULL',
      index: nil,
      to_sql: TIME_TO_EPOCH,
      to_ruby: EPOCH_TO_TIME
    },

    released_by: {
      field_name: 'released_by',
      sql_type: 'varchar(30)',
      index: nil,
      to_sql: nil,
      to_ruby: nil
    },

    auto_groups: {
      field_name: 'auto_install_groups',
      sql_type: 'text',
      index: nil,
      to_sql: ARRAY_TO_COMMA_STRING,
      to_ruby: COMMA_STRING_TO_ARRAY
    },

    excluded_groups: {
      field_name: 'excluded_groups',
      sql_type: 'text',
      index: nil,
      to_sql:  ARRAY_TO_COMMA_STRING,
      to_ruby: COMMA_STRING_TO_ARRAY
    },

    prohibiting_processes: {
      field_name: 'prohibiting_process',
      sql_type: 'varchar(100)',
      index: nil,
      to_sql: ARRAY_TO_COMMA_STRING,
      to_ruby: COMMA_STRING_TO_ARRAY
    },

    remove_first: {
      field_name: 'remove_first',
      sql_type: "tinyint(1) DEFAULT '0'",
      index: nil,
      to_sql: BOOL_TO_INT,
      to_ruby: INT_TO_BOOL
    },

    pre_install_script_id: {
      field_name: 'pre_install_id',
      sql_type: 'int(11)',
      index: nil,
      to_sql: nil,
      to_ruby: STRING_TO_INT
    },

    post_install_script_id: {
      field_name: 'post_install_id',
      sql_type: 'int(11)',
      index: nil,
      to_sql: nil,
      to_ruby: STRING_TO_INT
    },

    pre_remove_script_id: {
      field_name: 'pre_remove_id',
      sql_type: 'int(11)',
      index: nil,
      to_sql: nil,
      to_ruby: STRING_TO_INT
    },

    post_remove_script_id: {
      field_name: 'post_remove_id',
      sql_type: 'int(11)',
      index: nil,
      to_sql: nil,
      to_ruby: STRING_TO_INT
    },

    expiration: {
      field_name: 'expiration',
      sql_type: 'int(11)',
      index: nil,
      to_sql: nil,
      to_ruby: STRING_TO_INT
    },

    expiration_paths: {
      field_name: 'expiration_app_path',
      sql_type: 'varchar(300)',
      index: nil,
      to_sql: ARRAY_OF_PATHNAMES_TO_COMMA_STRING,
      to_ruby: COMMA_STRING_TO_ARRAY_OF_PATHNAMES
    }
  },

  other_indexes: [
    'UNIQUE KEY `edition` (`basename`,`version`,`revision`)'
  ]
}.freeze

Class Method Summary collapse

.check_schema_version ⇒ Object

Raise an exception if JSS schema is to old or too new.
.table_creation_sql ⇒ void

Print the sql for creating the d3_packages table as defined in the PACKAGE_TABLE constant.
.table_records(table_def) ⇒ Array<Hash>

Retrieve all records for one of the tables defined in D3::Database.

Class Method Details

.check_schema_version ⇒ `Object`

Raise an exception if JSS schema is to old or too new

Raises:

(JSS::InvalidConnectionError)

# File 'lib/d3/database.rb', line 400

def self.check_schema_version
  raw = JSS::DB_CNX.db.query("SELECT version FROM #{SCHEMA_TABLE}").fetch[0]
  simmered = raw.split('.')[0..1].join('.')
  current = JSS.parse_jss_version(simmered)[:version]
  min = JSS.parse_jss_version(MIN_SCHEMA_VERSION)[:version]
  max = JSS.parse_jss_version(MAX_SCHEMA_VERSION)[:version]
  raise JSS::InvalidConnectionError, "Invalid JSS database schema version: #{raw}, min: #{MIN_SCHEMA_VERSION}, max: #{MAX_SCHEMA_VERSION}" if (current < min) || (current > max)
  true
end

.table_creation_sql ⇒ `void`

This method returns an undefined value.

Print the sql for creating the d3_packages table as defined in the PACKAGE_TABLE constant



395
396
397

# File 'lib/d3/database.rb', line 395

def self.table_creation_sql
  puts  self.create_table(:display)
end

.table_records(table_def) ⇒ `Array<Hash>`

Retrieve all records for one of the tables defined in D3::Database

This is generally used by the method Package.package_data,

Returns an Array of Hashes, one for each record in the desired table. The keys of each hash are the keys of the :field_definitions hash from the table definition.

Parameters:

table_def (Hash) —

one of the d3 mysql table definitions, currently only PACKAGE_TABLE

Returns:

(Array<Hash>) —

the records from the desired table, with all values converted to appropriate Ruby classes as defined in the table_def

# File 'lib/d3/database.rb', line 362

def self.table_records(table_def)
  recs = []

  result = JSS.db.query "SELECT * FROM #{table_def[:table_name]}"

  # parse each record into a hash
  result.each_hash do |record|
    rec = {}

    # go through each field in the record, adding it to the hash
    # converting it to its ruby data type if defined in field conversions
    table_def[:field_definitions].each_pair do |key, field_def|
      # do we convert the value from the DB to something else in ruby?
      if field_def[:to_ruby]
        rec[key] = field_def[:to_ruby].call record[field_def[:field_name]]

      # or do we use the value as it comes from the DB?
      else
        rec[key] = record[field_def[:field_name]]
      end # if
    end # do key, field_def

    recs << rec
  end # do record

  recs
end