### Copyright 2016 Pixar ### ### Licensed under the Apache License, Version 2.0 (the "Apache License") ### with the following modification; you may not use this file except in ### compliance with the Apache License and the following modification to it: ### Section 6. Trademarks. is deleted and replaced with: ### ### 6. Trademarks. This License does not grant permission to use the trade ### names, trademarks, service marks, or product names of the Licensor ### and its affiliates, except as required to comply with Section 4(c) of ### the License and to reproduce the content of the NOTICE file. ### ### You may obtain a copy of the Apache License at ### ### http://www.apache.org/licenses/LICENSE-2.0 ### ### Unless required by applicable law or agreed to in writing, software ### distributed under the Apache License with the above modification is ### distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY ### KIND, either express or implied. See the Apache License for the specific ### language governing permissions and limitations under the Apache License. ### ### ### module D3 ### module Database ################# Module Constants ################# ### Booleans are stored as 1's and 0's in the db. TRUE_VAL = 1 FALSE_VAL = 0 # This table has info about the JSS schema SCHEMA_TABLE = 'db_schema_information'.freeze # the minimum JSS schema version allowed MIN_SCHEMA_VERSION = '9.4'.freeze # the max JSS schema version allowed MAX_SCHEMA_VERSION = "10.999.0" ### these Proc objects allow us to encapsulate and pass around various ### blocks of code more easily for converting data between their mysql ### representation and the Ruby classses we use internally. ### Ruby Time objects are stored as JSS epochs (unix epoch plus milliseconds) EPOCH_TO_TIME = proc { |v| v.nil? || v.to_s.empty? ? nil : JSS.epoch_to_time(v) } ### JSS epochs (unix epoch plus milliseconds) as used as Ruby Time objects TIME_TO_EPOCH = proc { |v| v.nil? || v.to_s.empty? ? nil : v.to_jss_epoch } ### Integers come from the database as strings, but empty ones should be nil, not zero, as #to_i would do STRING_TO_INT = proc { |v| v.nil? || v.to_s.empty? ? nil : v.to_i } ### Some values are stored as comma-separated strings, but used as Arrays COMMA_STRING_TO_ARRAY = proc { |v| JSS.to_s_and_a(v)[:arrayform] } ### Some values are stored as comma-separated strings, but used as Arrays of Pathnames COMMA_STRING_TO_ARRAY_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(', ') : '' } ### Some values are used as Arrays but stored as comma-separated strings ARRAY_TO_COMMA_STRING = proc { |v| JSS.to_s_and_a(v)[:stringform] } ### Some values are stored in the DB as YAML dumps RUBY_TO_YAML = proc { |v| YAML.dump v } YAML_TO_RUBY = proc { |v| YAML.load v.to_s } ### Booleans are stored as zero and one BOOL_TO_INT = proc { |v| v == true ? TRUE_VAL : FALSE_VAL } ### Booleans are stored as zero and one INT_TO_BOOL = proc { |v| v.to_i == FALSE_VAL ? false : true } ### Regexps are stored as strings STRING_TO_REGEXP = proc { |v| v.to_s.empty? ? nil : Regexp.new(v.to_s) } ### Regexps are stored as strings REGEXP_TO_STRING = proc { |v| v.to_s } ### Status values are stored as strings, but used as symbols STATUS_TO_STRING = proc { |v| v.to_s } STRING_TO_STATUS = proc { |v| v.to_sym } ### Expiration paths are stored as strings, but used as Pathnames STRING_TO_PATHNAME = proc { |v| Pathname.new v.to_s } PATHNAME_TO_STRING = proc { |v| v.to_s } ### 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] SQL clauses for defining multi-field indexes ### in the CREATE TABLE statement. Single-field indexes are defined in the field definitions. ### ### - :field_definitions [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] 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 {D3::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] 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 {D3::Package}, which mostly mirror the ### PACKAGE_TABLE = { 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 # end PACKAGE_TABLE ################# Module Methods ################# ### Retrieve all records for one of the tables defined in D3::Database ### ### This is generally used by the method {D3::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. ### ### @param table_def[Hash] one of the d3 mysql table definitions, currently only ### {D3::Database::PACKAGE_TABLE} ### ### @return [Array] the records from the desired table, with all values converted to ### appropriate Ruby classes as defined in the table_def ### ### 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 # self.table_records(table_def) ### Print the sql for creating the d3_packages table ### as defined in the PACKAGE_TABLE constant ### ### @return [void] ### def self.table_creation_sql puts self.create_table(:display) end ### Raise an exception if JSS schema is to old or too new 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 private ### Given a table constant defining a d3 table (PACKAGES_TABLE, at this point), ### create the table in the database. ### ### @param table_constant[Hash] one of the d3 table definition constants, currently only ### {D3::Database::PACKAGE_TABLE} ### ### @param print[Boolean] just print to stdout the SQL statement for creating the table, don't execute it. ### ### @return [void] ### def self.create_table(display = false) # as of now, only one table. table_constant = PACKAGE_TABLE sql = "CREATE TABLE `#{table_constant[:table_name]}` (" indexes = '' table_constant[:field_definitions].keys.sort.each do |key| field = table_constant[:field_definitions][key] sql += "\n `#{field[:field_name]}` #{field[:sql_type]}," indexes += case field[:index] when :primary "\n PRIMARY KEY (`#{field[:field_name]}`)," when :unique "\n UNIQUE KEY (`#{field[:field_name]}`)," when true "\n KEY (`#{field[:field_name]}`)," else '' end # indexes += case end # each do key sql += indexes table_constant[:other_indexes].each do |idx| sql += "\n #{idx}," end sql.chomp! ',' sql += "\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci" if display puts sql return end stmt = JSS::DB_CNX.db.prepare sql stmt.execute end # create d3 table ### @return [Array] A list of all d3-related tables in the database ### def self.tables res = JSS::DB_CNX.db.query 'show tables' d3_tables = [] res.each do |t| d3_tables << t[0] if t[0].start_with? 'd3_' end # res.each do |t| res.free d3_tables end end # module Database end # module D3