query_romsize.py 3.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130
  1. import sqlite3
  2. import os
  3. import sys
  4. import getpass
  5. import socket
  6. import traceback
  7. import paramiko
  8. def distance(a, b):
  9. c = {}
  10. n = len(a)
  11. m = len(b)
  12. for i in range(0, n + 1):
  13. c[i, 0] = i
  14. for j in range(0, m + 1):
  15. c[0, j] = j
  16. for i in range(1, n + 1):
  17. for j in range(1, m + 1):
  18. x = c[i - 1, j] + 1
  19. y = c[i, j - 1] + 1
  20. if a[i - 1] == b[j - 1]:
  21. z = c[i - 1, j - 1]
  22. else:
  23. z = c[i - 1, j - 1] + 1
  24. c[i, j] = min(x, y, z)
  25. return c[n, m]
  26. paramiko.util.log_to_file('rom_sftp.log')
  27. if socket.gethostname() == 'slap':
  28. path = "/home/david/Devel/arch/avr/code/snesram/roms/"
  29. username = "david"
  30. hostname = "slap"
  31. elif socket.gethostname() == 'box':
  32. path = "/Users/david/Devel/arch/avr/code/snesram/roms/"
  33. username = "david"
  34. hostname = "burst"
  35. else:
  36. sys.exit()
  37. def shellquote(s):
  38. return "'" + s.replace("'", "'\\''") + "'"
  39. def main():
  40. port = 22
  41. password = getpass.getpass('Password for %s@%s: ' % (username, hostname))
  42. hostkeytype = None
  43. hostkey = None
  44. host_keys = paramiko.util.load_host_keys(
  45. os.path.expanduser('~/.ssh/known_hosts'))
  46. if host_keys.has_key(hostname):
  47. hostkeytype = host_keys[hostname].keys()[0]
  48. hostkey = host_keys[hostname][hostkeytype]
  49. print 'Using host key of type %s' % hostkeytype
  50. try:
  51. print "Connect %s:%s" % (hostname, port)
  52. t = paramiko.Transport((hostname, port))
  53. t.connect(username=username, password=password, hostkey=hostkey)
  54. sftp = paramiko.SFTPClient.from_transport(t)
  55. # dirlist on remote host
  56. dirlist = sftp.listdir('.')
  57. print "Dirlist:", dirlist
  58. conn = sqlite3.connect('roms_cleanup.sqlite3')
  59. c = conn.cursor()
  60. for i in [(1,), (2,), (4,), (8,), (16,)]:
  61. dirname = os.path.join(path, "%02i" % i)
  62. if not os.path.isdir(dirname):
  63. os.mkdir(dirname)
  64. print "#" * 60
  65. print "%i MBit" % i
  66. print "#" * 60
  67. c.execute('''SELECT
  68. rom_name,
  69. rom_mb,
  70. file_name
  71. FROM
  72. roms
  73. WHERE
  74. rom_mb = ?
  75. AND
  76. rom_hirom = 0
  77. AND
  78. rom_sram = 0
  79. AND
  80. rom_type = 0
  81. AND
  82. rom_region like "Europe%"
  83. ORDER BY file_name
  84. ''', i)
  85. last_name = str()
  86. for row in c:
  87. name, size, filename = row
  88. d = distance(
  89. os.path.basename(filename), os.path.basename(last_name))
  90. if d < 7:
  91. print "Skip ", filename
  92. continue
  93. filename_dst = os.path.join(
  94. dirname, os.path.basename(filename))
  95. print "Remote: %s -> %s" % (filename, filename_dst)
  96. last_name = filename
  97. try:
  98. sftp.get(filename, filename_dst)
  99. except Exception, e:
  100. print '*** Caught exception: %s: %s' % (e.__class__, e)
  101. traceback.print_exc()
  102. print "Done"
  103. except Exception, e:
  104. print '*** Caught exception: %s: %s' % (e.__class__, e)
  105. traceback.print_exc()
  106. try:
  107. t.close()
  108. except:
  109. pass
  110. sys.exit(1)
  111. t.close()
  112. if __name__ == '__main__':
  113. main()