<commit_before>#! /usr/bin/env python
# s3_list_buckets.py
"""
Lists all keys in the specified bucket, along with each key's metadata.
Saves it all into a sqlite3 database.
"""

import sqlite3
import sys
from boto.s3.connection import S3Connection


def list_bucket(bucket_name):
    conn = sqlite3.connect('aptrust_s3.db')
    create_db_if_necessary(conn)
    s3 = S3Connection()
    bucket = s3.get_bucket(bucket_name)
    count = 0
    for key in bucket.list():
        full_key_with_metadata = bucket.get_key(key.name)
        pk = add_to_db(conn, full_key_with_metadata)
        print("{0:08d}  {1}".format(pk, key.name))
        count += 1
        if count > 10:
            return

def add_to_db(conn, key):
#    pk = 1
#    query = "select last_insert_rowid()"
#    c = conn.cursor()
#    c.execute(query)
#    row = c.fetchone()
#    if row and row[0]:
#        pk = row[0] + 1
    c = conn.cursor()
    statement = """insert into s3_keys
    (bucket, name, cache_control, content_type, etag,
    last_modified, storage_class, size)
    values (?,?,?,?,?,?,?,?)"""
    c.execute(statement, (key.bucket.name, key.name,
                          key.cache_control, key.content_type,
                          key.etag.replace('"', ''),
                          key.last_modified, key.storage_class,
                          key.size))
    conn.commit()
    pk = c.lastrowid
    for k,v in key.metadata.iteritems():
        statement = """insert into s3_meta (key_id, name, value)
        values (?,?,?)"""
        conn.execute(statement, (pk, k, v))
        conn.commit()
    c.close()
    return pk

def create_db_if_necessary(conn):
    query = """SELECT name FROM sqlite_master WHERE type='table'
    AND name='s3_keys'"""
    c = conn.cursor()
    c.execute(query)
    row = c.fetchone()
    if not row or len(row) < 1:
        print("Creating table s3_keys")
        statement = """create table s3_keys(
        id integer primary key autoincrement,
        bucket text, name text, cache_control text,
        content_type text,
        etag text, last_modified datetime,
        storage_class text, size int)"""
        conn.execute(statement)
        conn.commit()

        print("Creating table s3_meta")
        statement = "create table s3_meta(key_id, name, value)"
        conn.execute(statement)
        conn.commit()
    c.close()

if __name__ == "__main__":
    list_bucket('aptrust.preservation.storage')
    list_bucket('aptrust.preservation.oregon')<commit_msg>Handle existing items. Removed 10-item limit<commit_after>#! /usr/bin/env python
# s3_list_buckets.py
"""
Lists all keys in the specified bucket, along with each key's metadata.
Saves it all into a sqlite3 database.
"""

import sqlite3
import sys
from boto.s3.connection import S3Connection


def list_bucket(bucket_name):
    conn = sqlite3.connect('aptrust_s3.db')
    create_db_if_necessary(conn)
    s3 = S3Connection()
    bucket = s3.get_bucket(bucket_name)
    for key in bucket.list():
        full_key_with_metadata = bucket.get_key(key.name)
        pk, saved = add_to_db(conn, full_key_with_metadata)
        status = 'Inserted'
        if saved == False:
            status = 'Exists - Not Updated'
        print("{0:08d}  {1}  {2}".format(pk, key.name, status))

def add_to_db(conn, key):
    c = conn.cursor()
    exists = "select id from s3_keys where bucket=? and name=? and etag=?"
    c = conn.cursor()
    c.execute(exists, (key.bucket.name, key.name, key.etag.replace('"', '')))
    row = c.fetchone()
    if row and len(row) > 0:
        return row[0], False  # the row's id / primary key
    statement = """insert into s3_keys
    (bucket, name, cache_control, content_type, etag,
    last_modified, storage_class, size)
    values (?,?,?,?,?,?,?,?)"""
    c.execute(statement, (key.bucket.name, key.name,
                          key.cache_control, key.content_type,
                          key.etag.replace('"', ''),
                          key.last_modified, key.storage_class,
                          key.size))
    conn.commit()
    pk = c.lastrowid
    for k,v in key.metadata.iteritems():
        statement = """insert into s3_meta (key_id, name, value)
        values (?,?,?)"""
        conn.execute(statement, (pk, k, v))
        conn.commit()
    c.close()
    return pk, True

def create_db_if_necessary(conn):
    query = """SELECT name FROM sqlite_master WHERE type='table'
    AND name='s3_keys'"""
    c = conn.cursor()
    c.execute(query)
    row = c.fetchone()
    if not row or len(row) < 1:
        print("Creating table s3_keys")
        statement = """create table s3_keys(
        id integer primary key autoincrement,
        bucket text, name text, cache_control text,
        content_type text,
        etag text, last_modified datetime,
        storage_class text, size int)"""
        conn.execute(statement)
        conn.commit()

        print("Creating table s3_meta")
        statement = "create table s3_meta(key_id, name, value)"
        conn.execute(statement)
        conn.commit()
    c.close()

if __name__ == "__main__":
    list_bucket('aptrust.preservation.storage')
    list_bucket('aptrust.preservation.oregon')
{'fraction_non_alphanumeric': 0.12705472829239992, 'fraction_numerical': 0.011216399149100754, 'mean_word_length': 2.5546391752577318, 'pattern_counts': {'":': 2, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2453614', 'n_tokens_mistral': 1700, 'n_tokens_neox': 1656, 'n_words': 416}