##---- Lua##local sqlite3 = require("lsqlite3")####--[[local db1 = sqlite3.open('lua_mb_urls_not.db') -- Lua##local db2 = sqlite3.open('mb_urls_not.db') -- other####local newdb = sqlite3.open('mb_url_notadditive.db') -- for working space##local newdb1 = sqlite3.open('lua_mb_url_not1.db') -- additive & order by url##]]##---- Lua####----> Ruby >##db1 = SQLite3::Database.open "lua_mb_urls_not.db" # Lua##db2 = SQLite3::Database.open "mb_urls_not.db" # made from python code##newdb = SQLite3::Database.open "mb_url_notadditive.db" # additive work space##newdb1 = SQLite3::Database.open "mb_url_not1.db" # additive & order by url####----< Ruby <##---- Lua##local db1 = sqlite3.open('lua_mb_urls_new.db') -- Lua##local db2 = sqlite3.open('mb_urls_new.db') -- other####local newdb = sqlite3.open('mb_url_additive.db') -- for working space##local newdb1 = sqlite3.open('lua_mb_url_new1.db')####newdb:exec[[## CREATE TABLE IF NOT EXIST url (id INTEGER PRIMARY KEY, url text);##]]####newdb1:exec[[## CREATE TABLE IF NOT EXIST url (id INTEGER PRIMARY KEY, url text);##]]##---- Lua##----> Ruby >require'sqlite3'
db1 = SQLite3::Database.open "lua_mb_urls_new.db"# Lua
db2 = SQLite3::Database.open "mb_urls_new.db"# made from python code
newdb = SQLite3::Database.open "mb_url_additive.db"# additive work space
newdb1 = SQLite3::Database.open "mb_url_new1.db"# additive & order by urlSQL =<<EOScreate table IF NOT EXISTS url( id INTEGER PRIMARY KEY, url text );EOS
newdb.execute(SQL)
newdb1.execute(SQL)
##----< Ruby <##---- Lua##local smt1 = "SELECT id FROM url ORDER BY id DESC LIMIT 1 ;" ####local last1 = 0##local last2 = 0####for id in db1:urows(smt1) do## last1 = id##end####for id in db2:urows(smt1) do## last2 = id##end####print("db1 has "..last1.." URLs")##print("db2 has "..last2.." URLs")##---- Lua##----> Ruby >
last1 = db1.execute("select id from url order by id desc limit 1")
last2 = db2.execute("select id from url order by id desc limit 1")
k = last1[0].pop # offset
l = last2[0].pop
puts "db1 has #{k} URLs"
puts "db2 has #{l} URLs"
i = 0
newdb_last = newdb.execute("select id from url order by id desc limit 1")
if !newdb_last.empty? then
newcount = newdb_last[0].pop # offsetelse
newcount = 0end##----< Ruby <##---- Lua##local newcount = 0##for last_id in newdb:urows(smt1) do## newcount = last_id##end####local i = 0####for url_data in db2:urows("SELECT url FROM url ;") do##--[[##for url_data in db2:urows("SELECT url FROM url ORDER BY url ;") do##]]## i = i + 1## print("copy from db2 ")## print(i)## print("total "..last2)#### local existflag = 0#### for exist in newdb:urows("SELECT id FROM url WHERE url=\""..url_data.."\" ;") do## existflag = exist## print(url_data.." is aleady exist in DB / skip")## end#### if existflag == 0 then -- record## newcount = newcount + 1## print()## print("newdb : "..newcount.." "..url_data)## local stmt2 = newdb:prepare[[ INSERT INTO url VALUES (:id, :url) ]]## stmt2:bind_names{ id = newcount, url = url_data }## stmt2:step()## stmt2:reset()## stmt2:finalize()## end##end####db2:close()####i = 0 -- reset####for url_data in db1:urows("SELECT url FROM url ;") do##--[[##for url_data in db1:urows("SELECT url FROM url ORDER BY url ;") do##]]## i = i + 1## print("copy from db1 ")## print(i)## print("total "..last1)#### local existflag = 0## ## for exist in newdb:urows("SELECT id FROM url WHERE url=\""..url_data.."\" ;") do## existflag = exist## print(url_data.." is aleady exist in DB / skip")## end#### if existflag == 0 then -- record## newcount = newcount + 1## print()## print("newdb : "..newcount.." "..url_data)## local stmt2 = newdb:prepare[[ INSERT INTO url VALUES (:id, :url) ]]## stmt2:bind_names{ id = newcount, url = url_data }## stmt2:step()## stmt2:reset()## stmt2:finalize()## end##end####db1:close()##---- Lua##----> Ruby >
l.times do |index|
i += 1
res = db2.execute("select url from url where id='#{i}' ;")
if res.empty? then
puts "no data / skip"nextend
x = res[0][0] # url
puts "copy from db2"
p i,l
res2 = newdb.execute("select url from url where url='#{x}' ;")
if !res2.empty? then
p x
puts 'already exist in db / skip'nextend
puts
newcount += 1
url = x
#url.gsub!(/\'/,"\'\'")
puts "#{newcount}#{url}"
newdb.execute("insert into url (id, url) values( '#{newcount}','#{url}') ;")
end
db2.close
i = 0
k.times do |index|
i += 1
res = db1.execute("select url from url where id='#{i}' ;")
if res.empty? then
puts "no data / skip"nextend
x = res[0][0] # url
f = newdb.execute("select url from url where url='#{x}' ;")
puts "copy from db1"
p i,k
if !f.empty? then
p res[0]
puts 'already exist in db / skip'nextelse
newcount += 1
url = x
#url.gsub!(/\'/,"\'\'")
puts "#{newcount}#{url}"
newdb.execute("insert into url (id, url ) values( '#{newcount}','#{url}') ;")
endend
db1.close
##----< Ruby <##---- Lua##print("new DB last id : "..newcount)##---- Lua##----> Ruby >
puts "new DB last id:#{newcount}"##----< Ruby <##---- Lua##local newdb1_last = 0##for last_id in newdb1:urows(smt1) do## newdb1_last = last_id##end####local counter = newdb1_last -- first time ==> 0##---- Lua##----> Ruby >
newdb1_last = newdb1.execute("select id from url order by id desc limit 1")
if !newdb1_last.empty? then
counter = newdb1_last[0].pop # offsetelse
counter = 0end##----< Ruby <##---- Lua##i = 0 -- reset##---- Lua##----> Ruby >
i = 0##----< Ruby <##---- Lua##for url_data in newdb:urows("SELECT url FROM url ORDER BY url ;") do## i = i + 1## print("copy from newdb ")## print(i)## print("total "..newcount)#### local existflag = 0## ## for exist in newdb1:urows("SELECT id FROM url WHERE url=\""..url_data.."\" ;") do## existflag = exist## print(url_data.." is aleady exist in DB / skip")## end#### if existflag == 0 then -- record## counter = counter + 1## print()## print("newdb1 : "..counter.." "..url_data)## local stmt2 = newdb1:prepare[[ INSERT INTO url VALUES (:id, :url) ]]## stmt2:bind_names{ id = counter, url = url_data }## stmt2:step()## stmt2:reset()## stmt2:finalize()## end##end####--[[##newdb1:close() ##newdb:close()##]] -- end --##---- Lua##----> Ruby >
newdb.execute("select url from url order by url ;") do |row|
i += 1
url = row[0]
#url.gsub!(/\'/,"\'\'")
f = newdb1.execute("select url from url where url='#{url}' ;")
puts "copy from newdb"
p i,newcount
if !f.empty? then
p url
puts 'already exist in db / skip'nextelse
counter = counter + 1
puts "#{counter}#{url}"
newdb1.execute("insert into url (id, url ) values( '#{counter}','#{url}') ;")
endend
newdb1.close
newdb.close
##----< Ruby <
hy 1.0a3 using CPython(default)3.9.7 on Linux
=> (import requests)
=> (setv res (requests.get "https://mangabank.org/watch/?tour=/vol/" :allow_redirects False))
=> (type res)
<class 'requests.models.Response'>
=> (get res.headers "location")
pip install pycurl
import pycurl
c = pycurl.Curl()
#c.setopt(c.URL, 'https://mangabank.org/watch/?tour=/vol/')## Follow redirect.#c.setopt(c.FOLLOWLOCATION, 1) # True: 1 / False : 0#for x in range(1):# c.perform()# #print(c.getinfo(c.REDIRECT_URL))# print(c.getinfo(c.EFFECTIVE_URL))##c.close()#import requestsimport sqlite3
SQL = """create table url( id int primary key, url text );"""## first time only
db = sqlite3.connect('mb_urls_not.db')
db.execute(SQL)
db.close()
db = sqlite3.connect('mb_urls_new.db')
db.execute(SQL)
db.close()
con1 = sqlite3.connect('mb_urls_not.db')
cur1 = con1.cursor()
con = sqlite3.connect('mb_urls_new.db')
cur = con.cursor()
last_id1 = cur1.execute("select id from url order by id desc limit 1;")
index_num1 = last_id1.fetchone()
if index_num1 != None:
count1 = int(*index_num1)
else:
count1 = 0
last_id = cur.execute("select id from url order by id desc limit 1;")
index_num = last_id.fetchone()
if index_num != None:
count = int(*index_num)
else:
count = 0from urllib.parse import urlparse
import re
c.setopt(c.WRITEFUNCTION, lambdabytes: len(bytes))
for i inrange(10000):
# Follow redirect.
c.setopt(c.FOLLOWLOCATION, 0) # True: 1 / False : 0
c.setopt(c.URL, 'https://mangabank.org/watch/?tour=/vol/')
c.perform()
#res = c.getinfo(c.EFFECTIVE_URL)
res = c.getinfo(c.REDIRECT_URL)
address = res
number = re.sub(r'\/','',urlparse(res).path)
print()
print(" " + number)
## number = int(number) - 10## address = 'https://mangabank.org/'+str(number)+'/'defdbr(c,address,number,cur1,count1,cur,count,second):
gate = Falsefor x inrange(11):
print(x,end=' ')
again = False###### <1 check invalid db if x == 0:
flag1 = None
flag1 = cur1.execute("select id from url where url=? ;",[address])
flagcheck = flag1.fetchone()
if flagcheck != None:
print("flag 1: id=",*flagcheck,address)
cur1.execute("update url set url=? where id=? ;",["null",*flagcheck])
#cur1.commit()print(address,"is VALID / remove from NOT-VALID")
gate = Trueif second != True:
number = int(number) - 10
address = 'https://mangabank.org/'+str(number)+'/'###### 1>if x != 0:
number = int(number) + 1
address = 'https://mangabank.org/'+str(number)+'/'# print(address)###### <2
flag2 = None
flag2 = cur1.execute("select id from url where url=? ;",[address])
flagcheck = flag2.fetchone()
if flagcheck != None:
print()
print("flag 2: id= ",*flagcheck,address)
print(number,"NOT-VALID")
if gate != True:
continue###### 2>
c.setopt(c.URL,address)
c.setopt(c.FOLLOWLOCATION, 0) # True: 1 / False : 0
c.perform()
print(address,"responce code: ", c.getinfo(c.HTTP_CODE))
if c.getinfo(c.HTTP_CODE) != 200:
count1 += 1print("insert NOT-VALID-DB id: ",count1," -> ",address)
cur1.execute("insert into url (id,url) values(?,?) ;",[count1,address])
con1.commit()
if second == True:
breakcontinueelif c.getinfo(c.HTTP_CODE) == 200:
if flagcheck != None:
print()
print("flag 2-1: id=",*flagcheck,address)
cur1.execute("update url set url=? where id=? ;",["null",*flagcheck])
#cur1.commit()print(address,"is VALID / remove from NOT-VALID")
flag = None
flag = cur.execute("select id from url where url=? ;",[address])
flagcheck = flag.fetchone()
if flagcheck != None:
print()
print("flag 0: id= ",*flagcheck,address)
print("already exsist in DB")
print()
breakelse:
if x == 0 :
c.setopt(c.URL,address)
c.setopt(c.FOLLOWLOCATION, 0) # True: 1 / False : 0
c.perform()
print(address,"responce code: ", c.getinfo(c.HTTP_CODE))
if c.getinfo(c.HTTP_CODE) == 200:
count += 1print("insert id: ",count," -> ",address)
cur.execute("insert into url (id,url) values(?,?) ;",[count,address])
con.commit()
again = Truecontinue#break elif c.getinfo(c.HTTP_CODE) != 200:
count1 += 1print("insert NOT-VALID-DB id: ",count1," -> ",address)
cur1.execute("insert into url (id,url) values(?,?) ;",[count1,address])
con1.commit()
return count1,count,address,again
second = False
count1,count,address,again = dbr(c,address,number,cur1,count1,cur,count,second)
print()
limit = 5while again == True:
print(" ", str(limit))
number = re.sub(r'\/','',urlparse(address).path)
number = int(number) + 1
address = 'https://mangabank.org/'+str(number)+'/'
c.setopt(c.URL,address)
c.setopt(c.FOLLOWLOCATION, 0) # True: 1 / False : 0
c.perform()
print(address,"responce code: ", c.getinfo(c.HTTP_CODE))
if c.getinfo(c.HTTP_CODE) == 200:
second = True
count1,count,address,again = dbr(c,address,number,cur1,count1,cur,count,second)
limit = 5else:
limit -= 1print(" retry ", str(limit))
###### <3
flag1 = None
flag1 = cur1.execute("select id from url where url=? ;",[address])
flagcheck = flag1.fetchone()
if flagcheck != None:
print("flag 3: id=",*flagcheck,address)
print( number,"NOT-VALID")
if limit < 1:
again = Falsecontinue###### 3>
count1 += 1print("insert NOT-VALID-DB id: ",count1," -> ",address)
cur1.execute("insert into url (id,url) values(?,?) ;",[count1,address])
con1.commit()
if limit < 1:
again = False
c.close()
con1.close()
con.close()
local sqlite3 = require("lsqlite3")
local curl = require('cURL')
local db = sqlite3.open('lua_mb_urls_new.db')
local db1 = sqlite3.open('lua_mb_urls_not.db')
db:exec[[ CREATE TABLE url (id INTEGER PRIMARY KEY, url);]]
db1:exec[[ CREATE TABLE url (id INTEGER PRIMARY KEY, url);]]--[[ lsqlite3 usage ]]-- --for row in db:nrows("SELECT * FROM url") do-- print(row.id, row.url)--end--for row in db:rows("SELECT * FROM url") do-- print(row[1], row[2])--end--for id,url in db:urows("SELECT * FROM url") do-- print(id, url)--endlocal id_count = 0for bigloop=1,1000dolocal easy = curl.easy{
url = "https://mangabank.org/watch/?tour=/vol",
--followlocation = true, -- true: 1
followlocation = 0, -- false: 0
maxredirs = 2,
[curl.OPT_VERBOSE] = 0,
}local buffer = {}
easy:setopt_writefunction(table.insert, buffer)
-------------------------------------------------------------------[[ hettp request ]]--
easy:perform()
local res_code = easy:getinfo_response_code()
local res_location = easy:getinfo_redirect_url() -- 'URL'... redirect to print()
--print("code:",res_code) -- always 301 not 200print("redirect URL:",res_location)
local address_code = res_location:match("%d+") -- "[0-9]+" stringlocal address_number = tonumber(address_code)
--[[ example: 'https://mangabank.org/2457593/' --]]local smt1 = "SELECT id,url FROM url WHERE url=".."\""..res_location.."\""--print(smt1)local exist = false---- VALID DBfor id,url in db:urows(smt1) do-- print(id,url)
exist = trueendlocal gate = falselocal remove = falseif exist == truethenprint(res_location.." is aleady exist in VALID-DB")
else-- exist == false
gate = true---- INVALID DBfor id,url in db1:urows(smt1) do
remove = trueprint(res_location.." is VALID.remove from INVALID-DB")
local null_smt1 = "UPDATE url SET url='null' WHERE id="..id..";"assert(db1:exec(null_smt1))
endlocal id_count = 0for id in db:urows("SELECT id FROM url ORDER BY id DESC LIMIT 1;") do
id_count = id
end
id_count = id_count + 1print(res_location.." record in VALID-DB id:"..tostring(id_count))
local stmt2 = db:prepare[[ INSERT INTO url VALUES (:id, :url) ]]
stmt2:bind_names{ id = id_count, url = res_location }
stmt2:step()
stmt2:reset()
stmt2:finalize()
end-----------------------------------------------------------------if gate == truethen
address_number = address_number - 11local record = falsefor i=1,20doprint("VALID DB DATA size :"..id_count)
address_number = address_number + 1local next_url = "https://mangabank.org/"..tostring(address_number).."/"print()
print("test:",next_url)
smt1 = "SELECT id,url FROM url WHERE url=".."\""..next_url.."\""--print(smt1)local exist = false---- VALID DB == dbfor id,url in db:urows(smt1) do-- print(id,url)
exist = trueendif exist == truethenprint(next_url.." is aleady exist in VALID-DB")
else-- exist == false-- INVALID DB == db1for id,url in db1:urows(smt1) do
exist = trueif remove ~= truethenprint(next_url.." is aleady exist in INVALID-DB")
goto continue -- go to next loopendend-- http requestif nexe_url == res_location then
res_code = 200goto recording -- skip http requestend
easy:setopt(curl.OPT_URL,next_url)
easy:perform()
res_code = easy:getinfo_response_code()
::recording::if res_code == 200then
record = true--local id_count = 0for id in db:urows("SELECT id FROM url ORDER BY id DESC LIMIT 1;") do
id_count = id
end
id_count = id_count + 1print(res_code,next_url.." record in VALID-DB id:"..tostring(id_count))
print()
local stmt2 = db:prepare[[ INSERT INTO url VALUES (:id, :url) ]]
stmt2:bind_names{ id = id_count, url = next_url }
stmt2:step()
stmt2:reset()
stmt2:finalize()
elseprint(res_code,next_url.." is INVALID")
print()
record = false---- INVALID-DBif remove == truethenfor id,url in db1:urows(smt1) doprint(next_url.." is aleady exist in INVALID-DB")
goto continue -- go to next loopendendlocal id_count1 = 0for id in db1:urows("SELECT id FROM url ORDER BY id DESC LIMIT 1;") do
id_count1 = id
end
id_count1 = id_count1 + 1print(res_code,next_url.." record in INVALID-DB id:"..tostring(id_count1))
print()
local stmt2 = db1:prepare[[ INSERT INTO url VALUES (:id, :url) ]]
stmt2:bind_names{ id = id_count1, url = next_url }
stmt2:step()
stmt2:reset()
stmt2:finalize()
endend::continue::endwhile record == truedofor x = 1,6do
address_number = address_number + 1local next_url = "https://mangabank.org/"..tostring(address_number).."/"print()
print("test:",next_url)
smt1 = "SELECT id,url FROM url WHERE url=".."\""..next_url.."\""--print(smt1)local exist = false---- VALID DB == dbfor id,url in db:urows(smt1) do-- print(id,url)
exist = trueendif exist == truethenprint(next_url.." is aleady exist in VALID-DB")
else-- exist == false-- INVALID DB == db1for id,url in db1:urows(smt1) do
exist = trueprint(next_url.." is aleady exist in INVALID-DB")
goto continue2 -- go to next loopend-- http request
easy:setopt(curl.OPT_URL,next_url)
easy:perform()
res_code = easy:getinfo_response_code()
if res_code == 200then
record = true--local id_count = 0for id in db:urows("SELECT id FROM url ORDER BY id DESC LIMIT 1;") do
id_count = id
end
id_count = id_count + 1print(res_code,next_url.." record in VALID-DB id:"..tostring(id_count))
print()
local stmt2 = db:prepare[[ INSERT INTO url VALUES (:id, :url) ]]
stmt2:bind_names{ id = id_count, url = next_url }
stmt2:step()
stmt2:reset()
stmt2:finalize()
---- INVALID-DBif remove == truethenfor id,url in db1:urows(smt1) doprint(next_url.." is exist in INVALID-DB but now VAILD.")
local null_smt1 = "UPDATE url SET url='null' WHERE id="..id..";"assert(db1:exec(null_smt1))
goto continue2 -- go to next loopendendelseprint(res_code,next_url.." is INVALID")
print()
record = false---- INVALID-DBif remove == truethenfor id,url in db1:urows(smt1) doprint(next_url.." is aleady exist in INVALID-DB")
goto continue2 -- go to next loopendend--local id_count1 = 0for id in db1:urows("SELECT id FROM url ORDER BY id DESC LIMIT 1;") do
id_count1 = id
end
id_count1 = id_count1 + 1print(res_code,next_url.." record in INVALID-DB id:"..tostring(id_count1))
print()
local stmt2 = db1:prepare[[ INSERT INTO url VALUES (:id, :url) ]]
stmt2:bind_names{ id = id_count1, url = next_url }
stmt2:step()
stmt2:reset()
stmt2:finalize()
endend::continue2::endendend
easy:close()
end--[[big loop end]]--
db1:close()
db:close()
local sqlite3 = require("lsqlite3")
--[[local db1 = sqlite3.open('lua_mb_urls_not.db') -- Lualocal db2 = sqlite3.open('mb_urls_not.db') -- otherlocal newdb = sqlite3.open('mb_url_notadditive.db') -- for working spacelocal newdb1 = sqlite3.open('lua_mb_url_not1.db') -- additive & order by url]]----> Ruby >--db1 = SQLite3::Database.open "lua_mb_urls_not.db" # Lua--db2 = SQLite3::Database.open "mb_urls_not.db" # made from python code--newdb = SQLite3::Database.open "mb_url_notadditive.db" # additive work space--newdb1 = SQLite3::Database.open "mb_url_not1.db" # additive & order by url----< Ruby <local db1 = sqlite3.open('lua_mb_urls_new.db') -- Lualocal db2 = sqlite3.open('mb_urls_new.db') -- otherlocal newdb = sqlite3.open('mb_url_additive.db') -- for working spacelocal newdb1 = sqlite3.open('lua_mb_url_new1.db')
newdb:exec[[ CREATE TABLE IF NOT EXIST url (id INTEGER PRIMARY KEY, url text);]]
newdb1:exec[[ CREATE TABLE IF NOT EXIST url (id INTEGER PRIMARY KEY, url text);]]----> Ruby >--require 'sqlite3'--db1 = SQLite3::Database.open "lua_mb_urls_new.db" # Lua--db2 = SQLite3::Database.open "mb_urls_new.db" # made from python code--newdb = SQLite3::Database.open "mb_url_additive.db" # additive work space--newdb1 = SQLite3::Database.open "mb_url_new1.db" # additive & order by url----SQL =<<EOS--create table IF NOT EXISTS url(-- id INTEGER PRIMARY KEY,-- url text-- );--EOS----newdb.execute(SQL)--newdb1.execute(SQL)------< Ruby <local smt1 = "SELECT id FROM url ORDER BY id DESC LIMIT 1 ;"local last1 = 0local last2 = 0for id in db1:urows(smt1) do
last1 = id
endfor id in db2:urows(smt1) do
last2 = id
endprint("db1 has "..last1.." URLs")
print("db2 has "..last2.." URLs")
----> Ruby >--last1 = db1.execute("select id from url order by id desc limit 1")--last2 = db2.execute("select id from url order by id desc limit 1")----k = last1[0].pop # offset--l = last2[0].pop----puts "db1 has #{k} URLs"--puts "db2 has #{l} URLs"----i = 0----newdb_last = newdb.execute("select id from url order by id desc limit 1")--if !newdb_last.empty? then-- newcount = newdb_last[0].pop # offset--else-- newcount = 0--end----< Ruby <local newcount = 0for last_id in newdb:urows(smt1) do
newcount = last_id
endlocal i = 0for url_data in db2:urows("SELECT url FROM url ;") do--[[for url_data in db2:urows("SELECT url FROM url ORDER BY url ;") do]]
i = i + 1print("copy from db2 ")
print(i)
print("total "..last2)
local existflag = 0for exist in newdb:urows("SELECT id FROM url WHERE url=\""..url_data.."\" ;") do
existflag = exist
print(url_data.." is aleady exist in DB / skip")
endif existflag == 0then-- record
newcount = newcount + 1print()
print("newdb : "..newcount.." "..url_data)
local stmt2 = newdb:prepare[[ INSERT INTO url VALUES (:id, :url) ]]
stmt2:bind_names{ id = newcount, url = url_data }
stmt2:step()
stmt2:reset()
stmt2:finalize()
endend
db2:close()
i = 0-- resetfor url_data in db1:urows("SELECT url FROM url ;") do--[[for url_data in db1:urows("SELECT url FROM url ORDER BY url ;") do]]
i = i + 1print("copy from db1 ")
print(i)
print("total "..last1)
local existflag = 0for exist in newdb:urows("SELECT id FROM url WHERE url=\""..url_data.."\" ;") do
existflag = exist
print(url_data.." is aleady exist in DB / skip")
endif existflag == 0then-- record
newcount = newcount + 1print()
print("newdb : "..newcount.." "..url_data)
local stmt2 = newdb:prepare[[ INSERT INTO url VALUES (:id, :url) ]]
stmt2:bind_names{ id = newcount, url = url_data }
stmt2:step()
stmt2:reset()
stmt2:finalize()
endend
db1:close()
----> Ruby >--l.times do |index|-- i += 1-- res = db2.execute("select url from url where id='#{i}' ;")-- if res.empty? then-- puts "no data / skip"-- next-- end-- x = res[0][0] # url-- puts "copy from db2"-- p i,l---- res2 = newdb.execute("select url from url where url='#{x}' ;")-- if !res2.empty? then-- p x-- puts 'already exist in db / skip'-- next-- end-- puts-- newcount += 1-- url = x-- #url.gsub!(/\'/,"\'\'")-- puts "#{newcount} #{url}"-- newdb.execute("insert into url (id, url) values( '#{newcount}','#{url}') ;")--end----db2.close----i = 0--k.times do |index|-- i += 1-- res = db1.execute("select url from url where id='#{i}' ;")-- if res.empty? then-- puts "no data / skip"-- next-- end-- x = res[0][0] # url-- f = newdb.execute("select url from url where url='#{x}' ;")-- puts "copy from db1"-- p i,k---- if !f.empty? then-- p res[0]-- puts 'already exist in db / skip'-- next-- else-- newcount += 1-- url = x-- #url.gsub!(/\'/,"\'\'")-- puts "#{newcount} #{url}"-- newdb.execute("insert into url (id, url ) values( '#{newcount}','#{url}') ;")-- end--end----db1.close----< Ruby <print("new DB last id : "..newcount)
----> Ruby >--puts "new DB last id:#{newcount}"----< Ruby <local newdb1_last = 0for last_id in newdb1:urows(smt1) do
newdb1_last = last_id
endlocal counter = newdb1_last -- first time ==> 0----> Ruby > --newdb1_last = newdb1.execute("select id from url order by id desc limit 1")--if !newdb1_last.empty? then-- counter = newdb1_last[0].pop # offset--else-- counter = 0--end----< Ruby <
i = 0-- reset----> Ruby >--i = 0----< Ruby <for url_data in newdb:urows("SELECT url FROM url ORDER BY url ;") do
i = i + 1print("copy from newdb ")
print(i)
print("total "..newcount)
local existflag = 0for exist in newdb1:urows("SELECT id FROM url WHERE url=\""..url_data.."\" ;") do
existflag = exist
print(url_data.." is aleady exist in DB / skip")
endif existflag == 0then-- record
counter = counter + 1print()
print("newdb1 : "..counter.." "..url_data)
local stmt2 = newdb1:prepare[[ INSERT INTO url VALUES (:id, :url) ]]
stmt2:bind_names{ id = counter, url = url_data }
stmt2:step()
stmt2:reset()
stmt2:finalize()
endend--[[newdb1:close() newdb:close()]]-- end ------> Ruby >--newdb.execute("select url from url order by url ;") do |row|-- i += 1-- url = row[0]-- #url.gsub!(/\'/,"\'\'")-- f = newdb1.execute("select url from url where url='#{url}' ;")-- puts "copy from newdb"-- p i,newcount---- if !f.empty? then-- p url-- puts 'already exist in db / skip'-- next-- else-- counter = counter + 1-- puts "#{counter} #{url}"-- newdb1.execute("insert into url (id, url ) values( '#{counter}','#{url}') ;")-- end--end----newdb1.close--newdb.close----< Ruby <
書籍データは ISBN4 をともなったものが多いが、全てではないのと、電子書籍は書籍という枠には入れられていないために ISBN での管理を外れていても例外とは云えない。
おおかたの書籍検索システムは ISBN コードを主軸としているので、ISBN を使って書籍データを問い合わせる仕組みになっているが、これでは要件に適していないので、本のタイトルから書籍データを問い合わせることが可能なシステムの API を使い、これを実現する。
マンガサイト観測 2
マンガサイトにある 51717 タイトルの出版社情報を並べてみる。
マンガ 51717 タイトル list pdf
マンガサイト観測 1 で解説したように ISBN ではなく、サイト内で独自に割り振られたタイトルと著者名らしき文字列の情報から、タイトル名と著者名を抜き出して、NDL ( National Diet Library ) サーチの API を使いデータ照会できるように、仕様に合わせたクエリをつくり HTTPS でリクエストする。
つまり、 ISBN はわからないので、タイトルから ISBN をわりだすということが可能な API を使うということになる。 このような場合、国会図書館サーチか amazon の API かの選択になるが、今回は制限のほぼなさそうな国会図書館サーチを選ぶ。国立国会図書館サーチの使用法は仕様公開ページをよく読んでもどこか説明が足りないので、実際に使えるサンプルを探して試行錯誤する方がよいと思う。