<commit_before># -------------------------------------------------------------------------- #
# Copyright 2002-2012, OpenNebula Project Leads (OpenNebula.org)             #
#                                                                            #
# Licensed under the Apache License, Version 2.0 (the "License"); you may    #
# not use this file except in compliance with the License. You may obtain    #
# a copy of the License at                                                   #
#                                                                            #
# http://www.apache.org/licenses/LICENSE-2.0                                 #
#                                                                            #
# Unless required by applicable law or agreed to in writing, software        #
# distributed under the License is distributed on an "AS IS" BASIS,          #
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.   #
# See the License for the specific language governing permissions and        #
# limitations under the License.                                             #
#--------------------------------------------------------------------------- #

require "rexml/document"
include REXML
require 'ipaddr'

module OneDBFsck
    def db_version
        "3.8.0"
    end

    def one_version
        "OpenNebula 3.8.0"
    end

    def fsck

        ########################################################################
        # Acl
        ########################################################################

        ########################################################################
        # pool_control
        ########################################################################

        ########################################################################
        # Clusters
        #
        # CLUSTERS/HOSTS/ID
        # CLUSTERS/DATASTORES/ID
        # CLUSTERS/VNETS/ID
        ########################################################################

        ########################################################################
        # Groups
        #
        # GROUP/USERS/ID
        ########################################################################

        ########################################################################
        # Users
        #
        # USER/GID
        # USER/GNAME
        ########################################################################

        ########################################################################
        # Datastore
        #
        # DATASTORE/UID
        # DATASTORE/UNAME
        # DATASTORE/GID
        # DATASTORE/GNAME
        # DATASTORE/SYSTEM ??
        # DATASTORE/CLUSTER_ID
        # DATASTORE/CLUSTER
        # DATASTORE/IMAGES/ID
        ########################################################################

        ########################################################################
        # VM Template
        #
        # VMTEMPLATE/UID
        # VMTEMPLATE/UNAME
        # VMTEMPLATE/GID
        # VMTEMPLATE/GNAME
        ########################################################################

        ########################################################################
        # Documents
        #
        # DOCUMENT/UID
        # DOCUMENT/UNAME
        # DOCUMENT/GID
        # DOCUMENT/GNAME
        ########################################################################

        ########################################################################
        # VM
        #
        # VM/UID
        # VM/UNAME
        # VM/GID
        # VM/GNAME
        #
        # VM/STATE        <--- Check transitioning states?
        # VM/LCM_STATE    <---- Check consistency state/lcm_state ?
        ########################################################################

        ########################################################################
        # Image
        #
        # IMAGE/UID
        # IMAGE/UNAME
        # IMAGE/GID
        # IMAGE/GNAME
        # IMAGE/CLONING_OPS
        # IMAGE/CLONING_ID
        # IMAGE/DATASTORE_ID
        # IMAGE/DATASTORE
        ########################################################################

        ########################################################################
        # VNet
        #
        # VNET/UID
        # VNET/UNAME
        # VNET/GID
        # VNET/GNAME
        # 
        # VNET/CLUSTER_ID
        # VNET/CLUSTER
        ########################################################################

        ########################################################################
        # Hosts
        #
        # HOST/CLUSTER_ID
        # HOST/CLUSTER
        ########################################################################


        @errors = 0
        puts

        counters = {}
        counters[:host]  = {}
        counters[:image] = {}
        counters[:vnet]  = {}

        # Initialize all the host counters to 0
        @db.fetch("SELECT oid FROM host_pool") do |row|
            counters[:host][row[:oid]] = {
                :memory => 0,
                :cpu    => 0,
                :rvms   => 0
            }
        end

        # Init image counters
        @db.fetch("SELECT oid FROM image_pool") do |row|
            counters[:image][row[:oid]] = {
                :rvms   => 0
            }
        end

        # Init vnet counters
        @db.fetch("SELECT oid,body FROM network_pool") do |row|
            doc = Document.new(row[:body])

            counters[:vnet][row[:oid]] = {
                :type           => doc.root.get_text('TYPE').to_s.to_i,
                :total_leases   => 0,
                :leases         => {}
            }
        end

        # Aggregate information of the RUNNING vms
        @db.fetch("SELECT body FROM vm_pool WHERE state<>6") do |row|
            vm_doc = Document.new(row[:body])

            state     = vm_doc.root.get_text('STATE').to_s.to_i
            lcm_state = vm_doc.root.get_text('LCM_STATE').to_s.to_i


            # Images used by this VM
            vm_doc.root.each_element("TEMPLATE/DISK/IMAGE_ID") do |e|
                counters[:image][e.text.to_i][:rvms] += 1
            end

            # VNets used by this VM
            vm_doc.root.each_element("TEMPLATE/NIC") do |e|
                net_id = nil
                e.each_element("NETWORK_ID") do |nid|
                    net_id = nid.text.to_i
                end

                if !net_id.nil?
                    counters[:vnet][net_id][:leases][e.get_text('IP').to_s] =
                        [
                            e.get_text('MAC').to_s,                 # MAC
                            "1",                                    # USED
                            vm_doc.root.get_text('ID').to_s.to_i    # VID
                        ]
                end
            end

            # Host resources

            # Only states that add to Host resources consumption are
            # ACTIVE, SUSPENDED, POWEROFF
            next if !([3,5,8].include? state)

            # Get memory (integer)
            memory = 0
            vm_doc.root.each_element("TEMPLATE/MEMORY") { |e|
                memory = e.text.to_i
            }

            # Get CPU (float)
            cpu = 0
            vm_doc.root.each_element("TEMPLATE/CPU") { |e|
                cpu = e.text.to_f
            }

            # Get hostid
            hid = -1
            vm_doc.root.each_element("HISTORY_RECORDS/HISTORY[last()]/HID") { |e|
                hid = e.text.to_i
            }

            counters[:host][hid][:memory] += memory
            counters[:host][hid][:cpu]    += cpu
            counters[:host][hid][:rvms]   += 1
        end



        ########################################################################
        # Hosts
        #
        # HOST/HOST_SHARE/MEM_USAGE
        # HOST/HOST_SHARE/CPU_USAGE
        # HOST/HOST_SHARE/RUNNING_VMS
        ########################################################################

        # Create a new empty table where we will store the new calculated values
        @db.run "CREATE TABLE host_pool_new (oid INTEGER PRIMARY KEY, " <<
                "name VARCHAR(128), body TEXT, state INTEGER, " <<
                "last_mon_time INTEGER, uid INTEGER, gid INTEGER, " <<
                "owner_u INTEGER, group_u INTEGER, other_u INTEGER, " <<
                "UNIQUE(name));"

        # Calculate the host's xml and write them to host_pool_new
        @db[:host_pool].each do |row|
            host_doc = Document.new(row[:body])

            hid = row[:oid]

            rvms        = counters[:host][hid][:rvms]
            cpu_usage   = (counters[:host][hid][:cpu]*100).to_i
            mem_usage   = counters[:host][hid][:memory]*1024

            # rewrite running_vms
            host_doc.root.each_element("HOST_SHARE/RUNNING_VMS") {|e|
                if e.text != rvms.to_s
                    log_error("Host #{hid} RUNNING_VMS has #{e.text} \tis\t#{rvms}")
                    e.text = rvms
                end
            }

            # rewrite cpu
            host_doc.root.each_element("HOST_SHARE/CPU_USAGE") {|e|
                if e.text != cpu_usage.to_s
                    log_error("Host #{hid} CPU_USAGE has #{e.text} \tis\t#{cpu_usage}")
                    e.text = cpu_usage
                end
            }

            # rewrite memory
            host_doc.root.each_element("HOST_SHARE/MEM_USAGE") {|e|
                if e.text != mem_usage.to_s
                    log_error("Host #{hid} MEM_USAGE has #{e.text} \tis\t#{mem_usage}")
                    e.text = mem_usage
                end
            }

            row[:body] = host_doc.to_s

            # commit
            @db[:host_pool_new].insert(row)
        end

        # Rename table
        @db.run("DROP TABLE host_pool")
        @db.run("ALTER TABLE host_pool_new RENAME TO host_pool")


        ########################################################################
        # Image
        #
        # IMAGE/RUNNING_VMS
        ########################################################################


        # Create a new empty table where we will store the new calculated values
        @db.run "CREATE TABLE image_pool_new (oid INTEGER PRIMARY KEY, name VARCHAR(128), body TEXT, uid INTEGER, gid INTEGER, owner_u INTEGER, group_u INTEGER, other_u INTEGER, UNIQUE(name,uid) );"

        # Calculate the host's xml and write them to host_pool_new
        @db[:image_pool].each do |row|
            doc = Document.new(row[:body])

            oid = row[:oid]

            rvms = counters[:image][oid][:rvms]

            # rewrite running_vms
            doc.root.each_element("RUNNING_VMS") {|e|
                if e.text != rvms.to_s
                    log_error("Image #{oid} RUNNING_VMS has #{e.text} \tis\t#{rvms}")
                    e.text = rvms
                end
            }

            row[:body] = doc.to_s

            # commit
            @db[:image_pool_new].insert(row)
        end

        # Rename table
        @db.run("DROP TABLE image_pool")
        @db.run("ALTER TABLE image_pool_new RENAME TO image_pool")


        ########################################################################
        # VNet
        #
        # LEASES
        ########################################################################

        @db.run "CREATE TABLE leases_new (oid INTEGER, ip BIGINT, body TEXT, PRIMARY KEY(oid,ip));"

        @db[:leases].each do |row|
            doc = Document.new(row[:body])

            used = (doc.root.get_text('USED') == "1")
            vid  = doc.root.get_text('VID').to_s.to_i

            ip_str = IPAddr.new(row[:ip], Socket::AF_INET).to_s

            vnet_structure = counters[:vnet][row[:oid]]

            ranged = vnet_structure[:type] == 0

            counter_mac, counter_used, counter_vid =
                vnet_structure[:leases][ip_str]

            vnet_structure[:leases].delete(ip_str)

            insert = true

            if used && (vid != -1) # Lease used by a VM
                if counter_mac.nil?
                    log_error("VNet #{row[:oid]} has used lease #{ip_str} (VM #{vid}) \tbut it is free")

                    if ranged
                        insert = false
                    end

                    doc.root.each_element("USED") { |e|
                        e.text = "0"
                    }

                    doc.root.each_element("VID") {|e|
                        e.text = "-1"
                    }

                    row[:body] = doc.to_s

                elsif vid != counter_vid
                    log_error("VNet #{row[:oid]} has used lease #{ip_str} (VM #{vid}) \tbut it used by VM #{counter_vid}")

                    doc.root.each_element("VID") {|e|
                        e.text = counter_vid.to_s
                    }

                    row[:body] = doc.to_s
                end
            else # Lease is free or on hold (used=1, vid=-1)
                if !counter_mac.nil?
                    if used
                        log_error("VNet #{row[:oid]} has lease on hold #{ip_str} \tbut it is used by VM #{counter_vid}")
                    else
                        log_error("VNet #{row[:oid]} has free lease #{ip_str} \tbut it is used by VM #{counter_vid}")
                    end

                    doc.root.each_element("USED") { |e|
                        e.text = "1"
                    }

                    doc.root.each_element("VID") {|e|
                        e.text = counter_vid.to_s
                    }

                    row[:body] = doc.to_s
                end
            end

            if (doc.root.get_text('USED') == "1")
                vnet_structure[:total_leases] += 1
            end

            # commit
            @db[:leases_new].insert(row) if insert
        end

        # Now insert all the leases left in the hash, i.e. used by a VM in
        # vm_pool, but not in the leases table. This will only happen in
        # ranged networks

        counters[:vnet].each do |net_id,vnet_structure|
            vnet_structure[:leases].each do |ip,array|
                mac,used,vid = array

                ip_i = IPAddr.new(ip, Socket::AF_INET).to_i

                # TODO: MAC_PREFIX is now hardcoded to "02:00"
                body = "<LEASE><IP>#{ip_i}</IP><MAC_PREFIX>512</MAC_PREFIX><MAC_SUFFIX>#{ip_i}</MAC_SUFFIX><USED>#{used}</USED><VID>#{vid}</VID></LEASE>"

                log_error("VNet #{net_id} has free lease #{ip} \tbut it is used by VM #{vid}")

                vnet_structure[:total_leases] += 1

                @db[:leases_new].insert(
                    :oid        => net_id,
                    :ip         => ip_i,
                    :body       => body)
            end
        end


        # Rename table
        @db.run("DROP TABLE leases")
        @db.run("ALTER TABLE leases_new RENAME TO leases")


        ########################################################################
        # VNet
        #
        # VNET/TOTAL_LEASES
        ########################################################################

        # Create a new empty table where we will store the new calculated values
        @db.run "CREATE TABLE network_pool_new (oid INTEGER PRIMARY KEY, name VARCHAR(128), body TEXT, uid INTEGER, gid INTEGER, owner_u INTEGER, group_u INTEGER, other_u INTEGER, UNIQUE(name,uid));"

        @db[:network_pool].each do |row|
            doc = Document.new(row[:body])

            oid = row[:oid]

            total_leases = counters[:vnet][oid][:total_leases]

            # rewrite running_vms
            doc.root.each_element("TOTAL_LEASES") {|e|
                if e.text != total_leases.to_s
                    log_error("VNet #{oid} TOTAL_LEASES has #{e.text} \tis\t#{total_leases}")
                    e.text = total_leases
                end
            }

            row[:body] = doc.to_s

            # commit
            @db[:network_pool_new].insert(row)
        end

        # Rename table
        @db.run("DROP TABLE network_pool")
        @db.run("ALTER TABLE network_pool_new RENAME TO network_pool")


        ########################################################################
        # Users
        #
        # USER QUOTAS
        ########################################################################

        @db.run "ALTER TABLE user_pool RENAME TO old_user_pool;"
        @db.run "CREATE TABLE user_pool (oid INTEGER PRIMARY KEY, name VARCHAR(128), body TEXT, uid INTEGER, gid INTEGER, owner_u INTEGER, group_u INTEGER, other_u INTEGER, UNIQUE(name));"

        # oneadmin does not have quotas
        @db.fetch("SELECT * FROM old_user_pool WHERE oid=0") do |row|
            @db[:user_pool].insert(row)
        end

        @db.fetch("SELECT * FROM old_user_pool WHERE oid>0") do |row|
            doc = Document.new(row[:body])

            calculate_quotas(doc, "uid=#{row[:oid]}", "User")

            @db[:user_pool].insert(
                :oid        => row[:oid],
                :name       => row[:name],
                :body       => doc.root.to_s,
                :uid        => row[:oid],
                :gid        => row[:gid],
                :owner_u    => row[:owner_u],
                :group_u    => row[:group_u],
                :other_u    => row[:other_u])
        end

        @db.run "DROP TABLE old_user_pool;"


        ########################################################################
        # Groups
        #
        # GROUP QUOTAS
        ########################################################################

        @db.run "ALTER TABLE group_pool RENAME TO old_group_pool;"
        @db.run "CREATE TABLE group_pool (oid INTEGER PRIMARY KEY, name VARCHAR(128), body TEXT, uid INTEGER, gid INTEGER, owner_u INTEGER, group_u INTEGER, other_u INTEGER, UNIQUE(name));"

        # oneadmin group does not have quotas
        @db.fetch("SELECT * FROM old_group_pool WHERE oid=0") do |row|
            @db[:group_pool].insert(row)
        end

        @db.fetch("SELECT * FROM old_group_pool WHERE oid>0") do |row|
            doc = Document.new(row[:body])

            calculate_quotas(doc, "gid=#{row[:oid]}", "Group")

            @db[:group_pool].insert(
                :oid        => row[:oid],
                :name       => row[:name],
                :body       => doc.root.to_s,
                :uid        => row[:oid],
                :gid        => row[:gid],
                :owner_u    => row[:owner_u],
                :group_u    => row[:group_u],
                :other_u    => row[:other_u])
        end

        @db.run "DROP TABLE old_group_pool;"

        log_total_errors()

        return true
    end

    def log_error(message)
        @errors += 1
        puts message
    end

    def log_total_errors()
        puts
        puts "Total errors found: #{@errors}"
    end



    def calculate_quotas(doc, where_filter, resource)

        oid = doc.root.get_text("ID").to_s.to_i

        # VM quotas
        cpu_used = 0.0
        mem_used = 0
        vms_used = 0

        # VNet quotas
        vnet_usage = {}

        # Image quotas
        img_usage = {}

        @db.fetch("SELECT body FROM vm_pool WHERE #{where_filter} AND state<>6") do |vm_row|
            vmdoc = Document.new(vm_row[:body])

            # VM quotas
            vmdoc.root.each_element("TEMPLATE/CPU") { |e|
                # truncate to 2 decimals
                cpu = (e.text.to_f * 100).to_i / 100.0
                cpu_used += cpu
            }

            vmdoc.root.each_element("TEMPLATE/MEMORY") { |e|
                mem_used += e.text.to_i
            }

            vms_used += 1

            # VNet quotas
            vmdoc.root.each_element("TEMPLATE/NIC/NETWORK_ID") { |e|
                vnet_usage[e.text] = 0 if vnet_usage[e.text].nil?
                vnet_usage[e.text] += 1
            }

            # Image quotas
            vmdoc.root.each_element("TEMPLATE/DISK/IMAGE_ID") { |e|
                img_usage[e.text] = 0 if img_usage[e.text].nil?
                img_usage[e.text] += 1
            }
        end


        # VM quotas

        vm_elem = nil
        doc.root.each_element("VM_QUOTA/VM") { |e| vm_elem = e }

        if vm_elem.nil?
            vm_quota  = doc.root.add_element("VM_QUOTA")
            vm_elem   = vm_quota.add_element("VM")

            vm_elem.add_element("CPU").text         = "0"
            vm_elem.add_element("CPU_USED").text    = "0"

            vm_elem.add_element("MEMORY").text      = "0"
            vm_elem.add_element("MEMORY_USED").text = "0"

            vm_elem.add_element("VMS").text         = "0"
            vm_elem.add_element("VMS_USED").text    = "0"
        end


        vm_elem.each_element("CPU_USED") { |e|
            if e.text.to_f != cpu_used
                log_error("#{resource} #{oid} CPU_USED has #{e.text} \tis\t#{cpu_used}")
                e.text = cpu_used.to_s
            end
        }

        vm_elem.each_element("MEMORY_USED") { |e|
            if e.text.to_i != mem_used
                log_error("#{resource} #{oid} MEMORY_USED has #{e.text} \tis\t#{mem_used}")
                e.text = mem_used.to_s
            end
        }

        vm_elem.each_element("VMS_USED") { |e|
            if e.text.to_i != vms_used
                log_error("#{resource} #{oid} VMS_USED has #{e.text} \tis\t#{vms_used}")
                e.text = vms_used.to_s
            end
        }


        # VNet quotas

        net_quota = nil
        doc.root.each_element("NETWORK_QUOTA") { |e| net_quota = e }

        if net_quota.nil?
            net_quota = doc.root.add_element("NETWORK_QUOTA")
        end

        net_quota.each_element("NETWORK") { |net_elem|
            vnet_id = net_elem.get_text("ID").to_s

            leases_used = vnet_usage.delete(vnet_id)

            leases_used = 0 if leases_used.nil?

            net_elem.each_element("LEASES_USED") { |e|
                if e.text.to_i != leases_used
                    log_error("#{resource} #{oid} VNet #{vnet_id}\tLEASES_USED has #{e.text.to_i} \tis\t#{leases_used}")
                    e.text = leases_used.to_s
                end
            }
        }

        vnet_usage.each { |vnet_id, leases_used|
            log_error("#{resource} #{oid} VNet #{vnet_id}\tLEASES_USED has 0 \tis\t#{leases_used}")

            new_elem = net_quota.add_element("NETWORK")

            new_elem.add_element("ID").text = vnet_id
            new_elem.add_element("LEASES").text = "0"
            new_elem.add_element("LEASES_USED").text = leases_used.to_s
        }


        # Image quotas

        img_quota = nil
        doc.root.each_element("IMAGE_QUOTA") { |e| img_quota = e }

        if img_quota.nil?
            img_quota = doc.root.add_element("IMAGE_QUOTA")
        end

        img_quota.each_element("IMAGE") { |img_elem|
            img_id = img_elem.get_text("ID").to_s

            rvms = img_usage.delete(img_id)

            rvms = 0 if rvms.nil?

            img_elem.each_element("RVMS_USED") { |e|
                if e.text.to_i != rvms
                    log_error("#{resource} #{oid} Image #{img_id}\tRVMS has #{e.text.to_i} \tis\t#{rvms}")
                    e.text = rvms.to_s
                end
            }
        }

        img_usage.each { |img_id, rvms|
            log_error("#{resource} #{oid} Image #{img_id}\tRVMS has 0 \tis\t#{rvms}")

            new_elem = img_quota.add_element("IMAGE")

            new_elem.add_element("ID").text = img_id
            new_elem.add_element("RVMS").text = "0"
            new_elem.add_element("RVMS_USED").text = rvms.to_s
        }


        # Datastore quotas

        ds_usage = {}

        @db.fetch("SELECT body FROM image_pool WHERE #{where_filter}") do |img_row|
            img_doc = Document.new(img_row[:body])

            img_doc.root.each_element("DATASTORE_ID") { |e|
                ds_usage[e.text] = [0,0] if ds_usage[e.text].nil?
                ds_usage[e.text][0] += 1

                img_doc.root.each_element("SIZE") { |size|
                    ds_usage[e.text][1] += size.text.to_i
                }
            }
        end

        ds_quota = nil
        doc.root.each_element("DATASTORE_QUOTA") { |e| ds_quota = e }

        if ds_quota.nil?
            ds_quota = doc.root.add_element("DATASTORE_QUOTA")
        end

        ds_quota.each_element("DATASTORE") { |ds_elem|
            ds_id = ds_elem.get_text("ID").to_s

            images_used,size_used = ds_usage.delete(ds_id)

            images_used = 0 if images_used.nil?
            size_used   = 0 if size_used.nil?

            ds_elem.each_element("IMAGES_USED") { |e|
                if e.text.to_i != images_used
                    log_error("#{resource} #{oid} Datastore #{ds_id}\tIMAGES_USED has #{e.text.to_i} \tis\t#{images_used}")
                    e.text = images_used.to_s
                end
            }

            ds_elem.each_element("SIZE_USED") { |e|
                if e.text.to_i != size_used
                    log_error("#{resource} #{oid} Datastore #{ds_id}\tSIZE_USED has #{e.text.to_i} \tis\t#{size_used}")
                    e.text = size_used.to_s
                end
            }
        }

        ds_usage.each { |ds_id, array|
            images_used,size_used = array

            log_error("#{resource} #{oid} Datastore #{ds_id}\tIMAGES_USED has 0 \tis\t#{images_used}")
            log_error("#{resource} #{oid} Datastore #{ds_id}\tSIZE_USED has 0 \tis\t#{size_used}")

            new_elem = ds_quota.add_element("DATASTORE")

            new_elem.add_element("ID").text = ds_id

            new_elem.add_element("IMAGES").text = "0"
            new_elem.add_element("IMAGES_USED").text = images_used.to_s

            new_elem.add_element("SIZE").text = "0"
            new_elem.add_element("SIZE_USED").text = size_used.to_s
        }
    end
end
<commit_msg>Feature #1490: FSCK the pool_control table<commit_after># -------------------------------------------------------------------------- #
# Copyright 2002-2012, OpenNebula Project Leads (OpenNebula.org)             #
#                                                                            #
# Licensed under the Apache License, Version 2.0 (the "License"); you may    #
# not use this file except in compliance with the License. You may obtain    #
# a copy of the License at                                                   #
#                                                                            #
# http://www.apache.org/licenses/LICENSE-2.0                                 #
#                                                                            #
# Unless required by applicable law or agreed to in writing, software        #
# distributed under the License is distributed on an "AS IS" BASIS,          #
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.   #
# See the License for the specific language governing permissions and        #
# limitations under the License.                                             #
#--------------------------------------------------------------------------- #

require "rexml/document"
include REXML
require 'ipaddr'

module OneDBFsck
    def db_version
        "3.8.0"
    end

    def one_version
        "OpenNebula 3.8.0"
    end

    def fsck

        ########################################################################
        # Acl
        ########################################################################

        ########################################################################
        # Clusters
        #
        # CLUSTERS/HOSTS/ID
        # CLUSTERS/DATASTORES/ID
        # CLUSTERS/VNETS/ID
        ########################################################################

        ########################################################################
        # Groups
        #
        # GROUP/USERS/ID
        ########################################################################

        ########################################################################
        # Users
        #
        # USER/GID
        # USER/GNAME
        ########################################################################

        ########################################################################
        # Datastore
        #
        # DATASTORE/UID
        # DATASTORE/UNAME
        # DATASTORE/GID
        # DATASTORE/GNAME
        # DATASTORE/SYSTEM ??
        # DATASTORE/CLUSTER_ID
        # DATASTORE/CLUSTER
        # DATASTORE/IMAGES/ID
        ########################################################################

        ########################################################################
        # VM Template
        #
        # VMTEMPLATE/UID
        # VMTEMPLATE/UNAME
        # VMTEMPLATE/GID
        # VMTEMPLATE/GNAME
        ########################################################################

        ########################################################################
        # Documents
        #
        # DOCUMENT/UID
        # DOCUMENT/UNAME
        # DOCUMENT/GID
        # DOCUMENT/GNAME
        ########################################################################

        ########################################################################
        # VM
        #
        # VM/UID
        # VM/UNAME
        # VM/GID
        # VM/GNAME
        #
        # VM/STATE        <--- Check transitioning states?
        # VM/LCM_STATE    <---- Check consistency state/lcm_state ?
        ########################################################################

        ########################################################################
        # Image
        #
        # IMAGE/UID
        # IMAGE/UNAME
        # IMAGE/GID
        # IMAGE/GNAME
        # IMAGE/CLONING_OPS
        # IMAGE/CLONING_ID
        # IMAGE/DATASTORE_ID
        # IMAGE/DATASTORE
        ########################################################################

        ########################################################################
        # VNet
        #
        # VNET/UID
        # VNET/UNAME
        # VNET/GID
        # VNET/GNAME
        # 
        # VNET/CLUSTER_ID
        # VNET/CLUSTER
        ########################################################################

        ########################################################################
        # Hosts
        #
        # HOST/CLUSTER_ID
        # HOST/CLUSTER
        ########################################################################


        @errors = 0
        puts



        ########################################################################
        # pool_control
        ########################################################################

        tables = ["group_pool", "user_pool", "acl", "image_pool", "host_pool",
            "network_pool", "template_pool", "vm_pool", "cluster_pool",
            "datastore_pool", "document_pool"]

        tables.each do |table|
            max_oid = -1

            @db.fetch("SELECT MAX(oid) FROM #{table}") do |row|
                max_oid = row[:"MAX(oid)"].to_i
            end

            # max(oid) will return 0 if there is none,
            # or if the max is actually 0. Check this:
            if ( max_oid == 0 )
                max_oid = -1

                @db.fetch("SELECT oid FROM #{table} WHERE oid=0") do |row|
                    max_oid = 0
                end
            end

            control_oid = -1

            @db.fetch("SELECT last_oid FROM pool_control WHERE tablename='#{table}'") do |row|
                control_oid = row[:last_oid].to_i
            end

            if ( max_oid > control_oid )
                log_error("pool_control for table #{table} has last_oid #{control_oid}, but it is #{max_oid}")

                if control_oid != -1
                    @db.run("UPDATE pool_control SET last_oid=#{max_oid} WHERE tablename='#{table}'")
                else
                    @db[:pool_control].insert(
                        :tablename  => table,
                        :last_oid   => max_oid)
                end
            end
        end


        ########################################################################
        # VM Counters for host, image and vnet usage
        ########################################################################

        counters = {}
        counters[:host]  = {}
        counters[:image] = {}
        counters[:vnet]  = {}

        # Initialize all the host counters to 0
        @db.fetch("SELECT oid FROM host_pool") do |row|
            counters[:host][row[:oid]] = {
                :memory => 0,
                :cpu    => 0,
                :rvms   => 0
            }
        end

        # Init image counters
        @db.fetch("SELECT oid FROM image_pool") do |row|
            counters[:image][row[:oid]] = {
                :rvms   => 0
            }
        end

        # Init vnet counters
        @db.fetch("SELECT oid,body FROM network_pool") do |row|
            doc = Document.new(row[:body])

            counters[:vnet][row[:oid]] = {
                :type           => doc.root.get_text('TYPE').to_s.to_i,
                :total_leases   => 0,
                :leases         => {}
            }
        end

        # Aggregate information of the RUNNING vms
        @db.fetch("SELECT body FROM vm_pool WHERE state<>6") do |row|
            vm_doc = Document.new(row[:body])

            state     = vm_doc.root.get_text('STATE').to_s.to_i
            lcm_state = vm_doc.root.get_text('LCM_STATE').to_s.to_i


            # Images used by this VM
            vm_doc.root.each_element("TEMPLATE/DISK/IMAGE_ID") do |e|
                counters[:image][e.text.to_i][:rvms] += 1
            end

            # VNets used by this VM
            vm_doc.root.each_element("TEMPLATE/NIC") do |e|
                net_id = nil
                e.each_element("NETWORK_ID") do |nid|
                    net_id = nid.text.to_i
                end

                if !net_id.nil?
                    counters[:vnet][net_id][:leases][e.get_text('IP').to_s] =
                        [
                            e.get_text('MAC').to_s,                 # MAC
                            "1",                                    # USED
                            vm_doc.root.get_text('ID').to_s.to_i    # VID
                        ]
                end
            end

            # Host resources

            # Only states that add to Host resources consumption are
            # ACTIVE, SUSPENDED, POWEROFF
            next if !([3,5,8].include? state)

            # Get memory (integer)
            memory = 0
            vm_doc.root.each_element("TEMPLATE/MEMORY") { |e|
                memory = e.text.to_i
            }

            # Get CPU (float)
            cpu = 0
            vm_doc.root.each_element("TEMPLATE/CPU") { |e|
                cpu = e.text.to_f
            }

            # Get hostid
            hid = -1
            vm_doc.root.each_element("HISTORY_RECORDS/HISTORY[last()]/HID") { |e|
                hid = e.text.to_i
            }

            counters[:host][hid][:memory] += memory
            counters[:host][hid][:cpu]    += cpu
            counters[:host][hid][:rvms]   += 1
        end



        ########################################################################
        # Hosts
        #
        # HOST/HOST_SHARE/MEM_USAGE
        # HOST/HOST_SHARE/CPU_USAGE
        # HOST/HOST_SHARE/RUNNING_VMS
        ########################################################################

        # Create a new empty table where we will store the new calculated values
        @db.run "CREATE TABLE host_pool_new (oid INTEGER PRIMARY KEY, " <<
                "name VARCHAR(128), body TEXT, state INTEGER, " <<
                "last_mon_time INTEGER, uid INTEGER, gid INTEGER, " <<
                "owner_u INTEGER, group_u INTEGER, other_u INTEGER, " <<
                "UNIQUE(name));"

        # Calculate the host's xml and write them to host_pool_new
        @db[:host_pool].each do |row|
            host_doc = Document.new(row[:body])

            hid = row[:oid]

            rvms        = counters[:host][hid][:rvms]
            cpu_usage   = (counters[:host][hid][:cpu]*100).to_i
            mem_usage   = counters[:host][hid][:memory]*1024

            # rewrite running_vms
            host_doc.root.each_element("HOST_SHARE/RUNNING_VMS") {|e|
                if e.text != rvms.to_s
                    log_error("Host #{hid} RUNNING_VMS has #{e.text} \tis\t#{rvms}")
                    e.text = rvms
                end
            }

            # rewrite cpu
            host_doc.root.each_element("HOST_SHARE/CPU_USAGE") {|e|
                if e.text != cpu_usage.to_s
                    log_error("Host #{hid} CPU_USAGE has #{e.text} \tis\t#{cpu_usage}")
                    e.text = cpu_usage
                end
            }

            # rewrite memory
            host_doc.root.each_element("HOST_SHARE/MEM_USAGE") {|e|
                if e.text != mem_usage.to_s
                    log_error("Host #{hid} MEM_USAGE has #{e.text} \tis\t#{mem_usage}")
                    e.text = mem_usage
                end
            }

            row[:body] = host_doc.to_s

            # commit
            @db[:host_pool_new].insert(row)
        end

        # Rename table
        @db.run("DROP TABLE host_pool")
        @db.run("ALTER TABLE host_pool_new RENAME TO host_pool")


        ########################################################################
        # Image
        #
        # IMAGE/RUNNING_VMS
        ########################################################################


        # Create a new empty table where we will store the new calculated values
        @db.run "CREATE TABLE image_pool_new (oid INTEGER PRIMARY KEY, name VARCHAR(128), body TEXT, uid INTEGER, gid INTEGER, owner_u INTEGER, group_u INTEGER, other_u INTEGER, UNIQUE(name,uid) );"

        # Calculate the host's xml and write them to host_pool_new
        @db[:image_pool].each do |row|
            doc = Document.new(row[:body])

            oid = row[:oid]

            rvms = counters[:image][oid][:rvms]

            # rewrite running_vms
            doc.root.each_element("RUNNING_VMS") {|e|
                if e.text != rvms.to_s
                    log_error("Image #{oid} RUNNING_VMS has #{e.text} \tis\t#{rvms}")
                    e.text = rvms
                end
            }

            row[:body] = doc.to_s

            # commit
            @db[:image_pool_new].insert(row)
        end

        # Rename table
        @db.run("DROP TABLE image_pool")
        @db.run("ALTER TABLE image_pool_new RENAME TO image_pool")


        ########################################################################
        # VNet
        #
        # LEASES
        ########################################################################

        @db.run "CREATE TABLE leases_new (oid INTEGER, ip BIGINT, body TEXT, PRIMARY KEY(oid,ip));"

        @db[:leases].each do |row|
            doc = Document.new(row[:body])

            used = (doc.root.get_text('USED') == "1")
            vid  = doc.root.get_text('VID').to_s.to_i

            ip_str = IPAddr.new(row[:ip], Socket::AF_INET).to_s

            vnet_structure = counters[:vnet][row[:oid]]

            ranged = vnet_structure[:type] == 0

            counter_mac, counter_used, counter_vid =
                vnet_structure[:leases][ip_str]

            vnet_structure[:leases].delete(ip_str)

            insert = true

            if used && (vid != -1) # Lease used by a VM
                if counter_mac.nil?
                    log_error("VNet #{row[:oid]} has used lease #{ip_str} (VM #{vid}) \tbut it is free")

                    if ranged
                        insert = false
                    end

                    doc.root.each_element("USED") { |e|
                        e.text = "0"
                    }

                    doc.root.each_element("VID") {|e|
                        e.text = "-1"
                    }

                    row[:body] = doc.to_s

                elsif vid != counter_vid
                    log_error("VNet #{row[:oid]} has used lease #{ip_str} (VM #{vid}) \tbut it used by VM #{counter_vid}")

                    doc.root.each_element("VID") {|e|
                        e.text = counter_vid.to_s
                    }

                    row[:body] = doc.to_s
                end
            else # Lease is free or on hold (used=1, vid=-1)
                if !counter_mac.nil?
                    if used
                        log_error("VNet #{row[:oid]} has lease on hold #{ip_str} \tbut it is used by VM #{counter_vid}")
                    else
                        log_error("VNet #{row[:oid]} has free lease #{ip_str} \tbut it is used by VM #{counter_vid}")
                    end

                    doc.root.each_element("USED") { |e|
                        e.text = "1"
                    }

                    doc.root.each_element("VID") {|e|
                        e.text = counter_vid.to_s
                    }

                    row[:body] = doc.to_s
                end
            end

            if (doc.root.get_text('USED') == "1")
                vnet_structure[:total_leases] += 1
            end

            # commit
            @db[:leases_new].insert(row) if insert
        end

        # Now insert all the leases left in the hash, i.e. used by a VM in
        # vm_pool, but not in the leases table. This will only happen in
        # ranged networks

        counters[:vnet].each do |net_id,vnet_structure|
            vnet_structure[:leases].each do |ip,array|
                mac,used,vid = array

                ip_i = IPAddr.new(ip, Socket::AF_INET).to_i

                # TODO: MAC_PREFIX is now hardcoded to "02:00"
                body = "<LEASE><IP>#{ip_i}</IP><MAC_PREFIX>512</MAC_PREFIX><MAC_SUFFIX>#{ip_i}</MAC_SUFFIX><USED>#{used}</USED><VID>#{vid}</VID></LEASE>"

                log_error("VNet #{net_id} has free lease #{ip} \tbut it is used by VM #{vid}")

                vnet_structure[:total_leases] += 1

                @db[:leases_new].insert(
                    :oid        => net_id,
                    :ip         => ip_i,
                    :body       => body)
            end
        end


        # Rename table
        @db.run("DROP TABLE leases")
        @db.run("ALTER TABLE leases_new RENAME TO leases")


        ########################################################################
        # VNet
        #
        # VNET/TOTAL_LEASES
        ########################################################################

        # Create a new empty table where we will store the new calculated values
        @db.run "CREATE TABLE network_pool_new (oid INTEGER PRIMARY KEY, name VARCHAR(128), body TEXT, uid INTEGER, gid INTEGER, owner_u INTEGER, group_u INTEGER, other_u INTEGER, UNIQUE(name,uid));"

        @db[:network_pool].each do |row|
            doc = Document.new(row[:body])

            oid = row[:oid]

            total_leases = counters[:vnet][oid][:total_leases]

            # rewrite running_vms
            doc.root.each_element("TOTAL_LEASES") {|e|
                if e.text != total_leases.to_s
                    log_error("VNet #{oid} TOTAL_LEASES has #{e.text} \tis\t#{total_leases}")
                    e.text = total_leases
                end
            }

            row[:body] = doc.to_s

            # commit
            @db[:network_pool_new].insert(row)
        end

        # Rename table
        @db.run("DROP TABLE network_pool")
        @db.run("ALTER TABLE network_pool_new RENAME TO network_pool")


        ########################################################################
        # Users
        #
        # USER QUOTAS
        ########################################################################

        @db.run "ALTER TABLE user_pool RENAME TO old_user_pool;"
        @db.run "CREATE TABLE user_pool (oid INTEGER PRIMARY KEY, name VARCHAR(128), body TEXT, uid INTEGER, gid INTEGER, owner_u INTEGER, group_u INTEGER, other_u INTEGER, UNIQUE(name));"

        # oneadmin does not have quotas
        @db.fetch("SELECT * FROM old_user_pool WHERE oid=0") do |row|
            @db[:user_pool].insert(row)
        end

        @db.fetch("SELECT * FROM old_user_pool WHERE oid>0") do |row|
            doc = Document.new(row[:body])

            calculate_quotas(doc, "uid=#{row[:oid]}", "User")

            @db[:user_pool].insert(
                :oid        => row[:oid],
                :name       => row[:name],
                :body       => doc.root.to_s,
                :uid        => row[:oid],
                :gid        => row[:gid],
                :owner_u    => row[:owner_u],
                :group_u    => row[:group_u],
                :other_u    => row[:other_u])
        end

        @db.run "DROP TABLE old_user_pool;"


        ########################################################################
        # Groups
        #
        # GROUP QUOTAS
        ########################################################################

        @db.run "ALTER TABLE group_pool RENAME TO old_group_pool;"
        @db.run "CREATE TABLE group_pool (oid INTEGER PRIMARY KEY, name VARCHAR(128), body TEXT, uid INTEGER, gid INTEGER, owner_u INTEGER, group_u INTEGER, other_u INTEGER, UNIQUE(name));"

        # oneadmin group does not have quotas
        @db.fetch("SELECT * FROM old_group_pool WHERE oid=0") do |row|
            @db[:group_pool].insert(row)
        end

        @db.fetch("SELECT * FROM old_group_pool WHERE oid>0") do |row|
            doc = Document.new(row[:body])

            calculate_quotas(doc, "gid=#{row[:oid]}", "Group")

            @db[:group_pool].insert(
                :oid        => row[:oid],
                :name       => row[:name],
                :body       => doc.root.to_s,
                :uid        => row[:oid],
                :gid        => row[:gid],
                :owner_u    => row[:owner_u],
                :group_u    => row[:group_u],
                :other_u    => row[:other_u])
        end

        @db.run "DROP TABLE old_group_pool;"

        log_total_errors()

        return true
    end

    def log_error(message)
        @errors += 1
        puts message
    end

    def log_total_errors()
        puts
        puts "Total errors found: #{@errors}"
    end



    def calculate_quotas(doc, where_filter, resource)

        oid = doc.root.get_text("ID").to_s.to_i

        # VM quotas
        cpu_used = 0.0
        mem_used = 0
        vms_used = 0

        # VNet quotas
        vnet_usage = {}

        # Image quotas
        img_usage = {}

        @db.fetch("SELECT body FROM vm_pool WHERE #{where_filter} AND state<>6") do |vm_row|
            vmdoc = Document.new(vm_row[:body])

            # VM quotas
            vmdoc.root.each_element("TEMPLATE/CPU") { |e|
                # truncate to 2 decimals
                cpu = (e.text.to_f * 100).to_i / 100.0
                cpu_used += cpu
            }

            vmdoc.root.each_element("TEMPLATE/MEMORY") { |e|
                mem_used += e.text.to_i
            }

            vms_used += 1

            # VNet quotas
            vmdoc.root.each_element("TEMPLATE/NIC/NETWORK_ID") { |e|
                vnet_usage[e.text] = 0 if vnet_usage[e.text].nil?
                vnet_usage[e.text] += 1
            }

            # Image quotas
            vmdoc.root.each_element("TEMPLATE/DISK/IMAGE_ID") { |e|
                img_usage[e.text] = 0 if img_usage[e.text].nil?
                img_usage[e.text] += 1
            }
        end


        # VM quotas

        vm_elem = nil
        doc.root.each_element("VM_QUOTA/VM") { |e| vm_elem = e }

        if vm_elem.nil?
            vm_quota  = doc.root.add_element("VM_QUOTA")
            vm_elem   = vm_quota.add_element("VM")

            vm_elem.add_element("CPU").text         = "0"
            vm_elem.add_element("CPU_USED").text    = "0"

            vm_elem.add_element("MEMORY").text      = "0"
            vm_elem.add_element("MEMORY_USED").text = "0"

            vm_elem.add_element("VMS").text         = "0"
            vm_elem.add_element("VMS_USED").text    = "0"
        end


        vm_elem.each_element("CPU_USED") { |e|
            if e.text.to_f != cpu_used
                log_error("#{resource} #{oid} CPU_USED has #{e.text} \tis\t#{cpu_used}")
                e.text = cpu_used.to_s
            end
        }

        vm_elem.each_element("MEMORY_USED") { |e|
            if e.text.to_i != mem_used
                log_error("#{resource} #{oid} MEMORY_USED has #{e.text} \tis\t#{mem_used}")
                e.text = mem_used.to_s
            end
        }

        vm_elem.each_element("VMS_USED") { |e|
            if e.text.to_i != vms_used
                log_error("#{resource} #{oid} VMS_USED has #{e.text} \tis\t#{vms_used}")
                e.text = vms_used.to_s
            end
        }


        # VNet quotas

        net_quota = nil
        doc.root.each_element("NETWORK_QUOTA") { |e| net_quota = e }

        if net_quota.nil?
            net_quota = doc.root.add_element("NETWORK_QUOTA")
        end

        net_quota.each_element("NETWORK") { |net_elem|
            vnet_id = net_elem.get_text("ID").to_s

            leases_used = vnet_usage.delete(vnet_id)

            leases_used = 0 if leases_used.nil?

            net_elem.each_element("LEASES_USED") { |e|
                if e.text.to_i != leases_used
                    log_error("#{resource} #{oid} VNet #{vnet_id}\tLEASES_USED has #{e.text.to_i} \tis\t#{leases_used}")
                    e.text = leases_used.to_s
                end
            }
        }

        vnet_usage.each { |vnet_id, leases_used|
            log_error("#{resource} #{oid} VNet #{vnet_id}\tLEASES_USED has 0 \tis\t#{leases_used}")

            new_elem = net_quota.add_element("NETWORK")

            new_elem.add_element("ID").text = vnet_id
            new_elem.add_element("LEASES").text = "0"
            new_elem.add_element("LEASES_USED").text = leases_used.to_s
        }


        # Image quotas

        img_quota = nil
        doc.root.each_element("IMAGE_QUOTA") { |e| img_quota = e }

        if img_quota.nil?
            img_quota = doc.root.add_element("IMAGE_QUOTA")
        end

        img_quota.each_element("IMAGE") { |img_elem|
            img_id = img_elem.get_text("ID").to_s

            rvms = img_usage.delete(img_id)

            rvms = 0 if rvms.nil?

            img_elem.each_element("RVMS_USED") { |e|
                if e.text.to_i != rvms
                    log_error("#{resource} #{oid} Image #{img_id}\tRVMS has #{e.text.to_i} \tis\t#{rvms}")
                    e.text = rvms.to_s
                end
            }
        }

        img_usage.each { |img_id, rvms|
            log_error("#{resource} #{oid} Image #{img_id}\tRVMS has 0 \tis\t#{rvms}")

            new_elem = img_quota.add_element("IMAGE")

            new_elem.add_element("ID").text = img_id
            new_elem.add_element("RVMS").text = "0"
            new_elem.add_element("RVMS_USED").text = rvms.to_s
        }


        # Datastore quotas

        ds_usage = {}

        @db.fetch("SELECT body FROM image_pool WHERE #{where_filter}") do |img_row|
            img_doc = Document.new(img_row[:body])

            img_doc.root.each_element("DATASTORE_ID") { |e|
                ds_usage[e.text] = [0,0] if ds_usage[e.text].nil?
                ds_usage[e.text][0] += 1

                img_doc.root.each_element("SIZE") { |size|
                    ds_usage[e.text][1] += size.text.to_i
                }
            }
        end

        ds_quota = nil
        doc.root.each_element("DATASTORE_QUOTA") { |e| ds_quota = e }

        if ds_quota.nil?
            ds_quota = doc.root.add_element("DATASTORE_QUOTA")
        end

        ds_quota.each_element("DATASTORE") { |ds_elem|
            ds_id = ds_elem.get_text("ID").to_s

            images_used,size_used = ds_usage.delete(ds_id)

            images_used = 0 if images_used.nil?
            size_used   = 0 if size_used.nil?

            ds_elem.each_element("IMAGES_USED") { |e|
                if e.text.to_i != images_used
                    log_error("#{resource} #{oid} Datastore #{ds_id}\tIMAGES_USED has #{e.text.to_i} \tis\t#{images_used}")
                    e.text = images_used.to_s
                end
            }

            ds_elem.each_element("SIZE_USED") { |e|
                if e.text.to_i != size_used
                    log_error("#{resource} #{oid} Datastore #{ds_id}\tSIZE_USED has #{e.text.to_i} \tis\t#{size_used}")
                    e.text = size_used.to_s
                end
            }
        }

        ds_usage.each { |ds_id, array|
            images_used,size_used = array

            log_error("#{resource} #{oid} Datastore #{ds_id}\tIMAGES_USED has 0 \tis\t#{images_used}")
            log_error("#{resource} #{oid} Datastore #{ds_id}\tSIZE_USED has 0 \tis\t#{size_used}")

            new_elem = ds_quota.add_element("DATASTORE")

            new_elem.add_element("ID").text = ds_id

            new_elem.add_element("IMAGES").text = "0"
            new_elem.add_element("IMAGES_USED").text = images_used.to_s

            new_elem.add_element("SIZE").text = "0"
            new_elem.add_element("SIZE_USED").text = size_used.to_s
        }
    end
end

{'fraction_non_alphanumeric': 0.20629748249664828, 'fraction_numerical': 0.004748249664829436, 'mean_word_length': 1.5938179183772037, 'pattern_counts': {'":': 0, '<': 51, '<?xml version=': 0, '>': 90, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 6}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 30, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6577550', 'n_tokens_mistral': 16183, 'n_tokens_neox': 14887, 'n_words': 2984}