#!/usr/bin/python2.6
#
# Copyright 2011 Google Inc.
#
# 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.

__author__ = 'flamingcow@google.com (Ian Gulliver)'


from pylib import app
from pylib import db
from pylib import flags

FLAGS = flags.FLAGS

flags.DEFINE_string('db', None, 'DB spec to scan')


def _ListStartsWith(superset, subset):
  """Returns true if superset starts with subset."""
  return superset[:len(subset)] == subset


def FindDuplicateIndexes(dbh):
  tables = dbh.ExecuteOrDie(
      'SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES')

  for table in tables:
    index_columns = dbh.ExecuteOrDie('SHOW INDEX FROM `%s`.`%s`' %
                                     (table['TABLE_SCHEMA'],
                                      table['TABLE_NAME']))
    indexes = {}
    for column in index_columns:
      index = indexes.setdefault(column['Key_name'], {})
      index['unique'] = not bool(column['Non_unique'])
      index.setdefault('columns', []).append(column['Column_name'])

    for key_name1, index1 in indexes.iteritems():
      if index1['unique']:
        # We never suggest removal of unique indexes.
        continue
      for key_name2, index2 in indexes.iteritems():
        if key_name1 == key_name2:
          continue
        if _ListStartsWith(index2['columns'], index1['columns']):
          print '`%s`.`%s`: Key %s is a prefix of %s' % (
              table['TABLE_SCHEMA'], table['TABLE_NAME'],
              key_name1, key_name2)


def main(argv):
  assert FLAGS.db, 'Please pass --db'

  with db.Connect(FLAGS.db) as dbh:
    FindDuplicateIndexes(dbh)


if __name__ == '__main__':
  app.run()

@@ -1972,16 +1972,853 @@
 name2)%0A%0A
+    if 'PRIMARY' in indexes and len(indexes%5B'PRIMARY'%5D%5B'columns'%5D) == 1:%0A      # Single-column primary index. Validate type.%0A      column_info = dbh.ExecuteOrDie(%0A          %22SHOW COLUMNS FROM %60%25s%60.%60%25s%60 LIKE %25%25(column)s%22 %25%0A          (table%5B'TABLE_SCHEMA'%5D,%0A           table%5B'TABLE_NAME'%5D),%0A          %7B'column': indexes%5B'PRIMARY'%5D%5B'columns'%5D%5B0%5D%7D)%0A      if column_info%5B0%5D%5B'Null'%5D == 'YES':%0A        print '%60%25s%60.%60%25s%60: ID column %25s is nullable' %25 (%0A            table%5B'TABLE_SCHEMA'%5D, table%5B'TABLE_NAME'%5D,%0A            column_info%5B0%5D%5B'Field'%5D)%0A      if column_info%5B0%5D%5B'Type'%5D not in ('bigint(20)',%0A                                        'bigint(20) unsigned'):%0A        print '%60%25s%60.%60%25s%60: ID column %25s has invalid type: %25s' %25 (%0A            table%5B'TABLE_SCHEMA'%5D, table%5B'TABLE_NAME'%5D,%0A            column_info%5B0%5D%5B'Field'%5D, column_info%5B0%5D%5B'Type'%5D)%0A%0A
 %0Adef mai

Check for 32-bit indexes.