Copy Link
Add to Bookmark
Report
NULL mag Issue 07 29 ANSI gallery database
With the following program written in Python3, we can create a SQLite3
database, that will contain all info about all ANSI files that we may
have in a specific directory.
The intention is to have a directory structure like:
+ Gallery
|
+ 1990
| |
| + packname
| |
| + files...
| + ...
...
+ 2019
|
+ packname
|
+ files...
each subdirectory will have the name of the package and inside, will be
all the ANSI files. You can remove files that are not viewable in a BBS,
like XBIN, JPG, tracks etc and keep only .ANS, .DIZ, .NFO and perhaps
some package specific.
The program will search all those files and extract data, that will put
inside an SQLite3 database file, of your choice.
To execute the program type: script.py database.sq
If you want more info, just ask in FSX or Zero networks or send me an
email at: xqtr@gmx.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
#!/usr/bin/python3
import os
import sqlite3
import sys
from sqlite3 import Error
from subprocess import PIPE, run
import struct
# This is the SQL string for creating our database file. If you need more
# fields, feel free to add them
ansitablesql = """CREATE TABLE IF NOT EXISTS gallery (id integer PRIMARY KEY,
file VARCHAR(255),
folder VARCHAR(255),
rate VARCHAR(3),
sha1 VARCHAR(100),
author VARCHAR(40),
date VARCHAR(20),
tags text,
stext text,
downloaded integer,
year VARCHAR(4),
pack VARCHAR(100),
title VARCHAR(255),
agroup VARCHAR(50),
size INT8,
UNIQUE (sha1));"""
# The SQL string to insert a record
insertrec = """insert OR IGNORE into gallery(year,pack,file,sha1,
folder,rate,agroup,title,author,date,stext,size,tags)
values (?,?,?,?,?,?,?,?,?,?,?,?,?)"""
# A valuable function on how to get an SHA1 string for a file
def getsha1(filename):
import hashlib
BLOCKSIZE = 65536
hasher = hashlib.sha1()
with open(filename, 'rb') as afile:
buf = afile.read(BLOCKSIZE)
while len(buf) > 0:
hasher.update(buf)
buf = afile.read(BLOCKSIZE)
return hasher.hexdigest()
# A simple function to get the SAUCE data. It doesn't support COMMENTS
# and only gets a few fields of it and not the whole data. You can easily
# extend it.
def getsauce(filename):
f = open(filename, 'rb')
f.seek(-128,2)
saucestr = "<5s2s35s20s20s8si32x"
sf = struct.calcsize(saucestr)
sauce = f.read(sf)
s = struct.unpack(saucestr,sauce)
f.close()
if ''.join(str(s[0]).strip("b'")) == "SAUCE":
return s
else:
return -1
# a function to create a byte-string to string
def byte2str(v):
s=''.join(str(v))
return s[2:-1]
def create_table(conn, create_table_sql):
""" create a table from the create_table_sql statement
:param conn: Connection object
:param create_table_sql: a CREATE TABLE statement
:return:
"""
try:
c = conn.cursor()
c.execute(create_table_sql)
except Error as e:
print(e)
def create_connection(db_file):
""" create a database connection to the SQLite database
specified by db_file
:param db_file: database file
:return: Connection object or None
"""
try:
conn = sqlite3.connect(db_file)
create_table(conn,ansitablesql)
return conn
except Error as e:
print(e)
return None
# get text between a specific character in a string
def wordget(Num,Str,Ch):
a = Str.split(Ch)
return a[Num-1]
# the main procedure to get all the files, extract the data and create the
# database.
def getfiles(path):
files = []
# r=root, d=directories, f = files
for r, d, f in os.walk(path):
for file in f:
filename, ext = os.path.splitext(file)
if ext.upper() == ".NFO" or ext.upper() == ".ANS" or \
ext.upper() == ".ASC" or ext.upper() == ".DIZ" \
or ext.upper() == ".TXT":
files.append(os.path.join(r, file))
cur = conn.cursor()
for f in files:
print(f)
line = f.split(os.sep)
if len(line) > 2:
fname = os.path.basename(f)
fdir = os.path.dirname(f)
isize = os.path.getsize(f)
print('Adding: '+fname+' : Sauce', end = '')
# if filesize is less than 128byte the the file, for sure it has
# no SAUCE data and if we try to read it with the above function
# it will crash
if isize > 127:
sauce = getsauce(f)
print(" ,SHA1", end = '')
sha1 = getsha1(f)
year = line[1]
if sauce != -1:
group = byte2str(sauce[4]).rstrip(" ")
artist = byte2str(sauce[3]).rstrip(" ")
title = byte2str(sauce[2]).rstrip(" ")
date = byte2str(sauce[5]).rstrip(" ")
if byte2str(sauce[6]) != '':
fsize = int(byte2str(sauce[6]))
else:
fsize = isize
else:
fsize = isize
group = ""
artist = ""
title = ""
pack = line[2]
# Below are two sets of commands that need external tools to work.
# If you don't want to add them, comment out the lines and also
# change the command in line 164 to:
#values = (year,pack,fname,sha1,fdir,"",group,title,artist,date, \
# "",fsize,"")
# for this command to work, you must have the script from issue
# x005, to convert an ANSI to Text
print(" ,Text", end = '')
command = ['/usr/bin/timeout','20','./ansi2text.sh', f]
sresult = run(command, stdout=PIPE, stderr=PIPE, \
universal_newlines=True)
# This command will add some tags to begin with. It needs the
# tool ANSIMAN, which you can find at BBSes
print(" ,Tags", end = '')
command = ['./ansichar',f,'-s']
aresult = run(command, stdout=PIPE, stderr=PIPE, \
universal_newlines=True)
values = (year,pack,fname,sha1,fdir,"",group,title,artist,date, \
sresult.stdout,fsize,aresult.stdout)
cur.execute(insertrec,values)
print(' OK!')
conn.commit()
conn = create_connection(sys.argv[1])
if conn == None:
print("Error creating the database.")
else:
print("Creating Database Data...")
getfiles(".")
conn.commit()
conn.close()
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
