Find Special/Escape Characters in Sqlite

Find TAB

SELECT * FROM table WHERE column LIKE '%' || X'09' || '%';
SELECT * FROM table WHERE column LIKE '%' || CHAR(9) || '%';

Find CR LF

SELECT * FROM table WHERE column LIKE '%' || CHAR(13) || CHAR(10) || '%';
 

Python aiosqlite - async SQLITE

Install the aiosqlite package: aiosqlite is an async version of sqlite3 that works with Python's asyncio library. You can install it using pip:

 Import the necessary libraries: You need to import aiosqlite and asyncio for this task.

Create an async function to execute your query: This function will connect to a SQLite database, execute a query, and return the results. It uses the aiosqlite.connect() method to establish a connection to the database and the execute() method to run the SQL query.

Create tasks for each database: For each database you want to search, create a task using asyncio.create_task(). Each of these tasks will run the query_db function concurrently.

Execute the tasks: Use asyncio.gather() to run all the tasks concurrently and wait for all of them to finish. The results will be returned as a list of lists, with each sublist containing the results from one database.

Iterate through the results: You can now iterate through the results to process the data from each database.

import asyncio
import aiosqlite

async def query_db(database, query):
    async with aiosqlite.connect(database) as db:
        async with db.execute(query) as cursor:
            return await cursor.fetchall()

async def main():
    databases = ["database1.db", "database2.db", "database3.db"]
    query = "SELECT * FROM some_table"
    tasks = [asyncio.create_task(query_db(database, query)) for database in databases]

    results = await asyncio.gather(*tasks)

    for db_results in results:
        for row in db_results:
            # Process row
            pass

asyncio.run(main())
 

# This code will initiate connections to each of the specified databases concurrently and execute the same query on each. The results from each database are collected into a list of lists, which can then be processed as needed.Note: It's important to remember that although asyncio allows for concurrent execution of tasks, SQLite databases are stored on disk and therefore I/O bound. The actual speedup from using asyncio will depend on the speed of your disk and how large the databases are

Baza de date dexonline in format stardict (Dexonline Database in Stardict Format)

Acesta este un dictionar roman, bazat pe baza de date dexonline in format stardict. Formatul stardict este recunoscut de KoReader (singurul software open source pe care l-am gasit pentru e-reader-e).

Se descarcă baza de date de la dexonline si se transformă in format stardict.

TLDR

Ca sa obtineti un format stardict, trebuie mai intai obtinut un format gls din baza de date dexonline a datelor returnate de select-ul acesta:

select replace(l.form, "'", '') form, replace(d.htmlRep, "\n", "<br/>") html from Lexem l inner join EntryLexem el on l.id = el.lexemId
inner join Entry e on el.entryId = e.id
inner join EntryDefinition ed on e.id = ed.entryId
inner join Definition d on ed.definitionId = d.id where d.sourceId in (27, 21);

Formatul gls este destul de simplu. Pe prima linie se afla cuvantul cautat, apoi pe urmatoarea se afla definitia si dupa o linie libera. Deasupra datelor trebuie adaugat header-ul acesta (cu tot cu linii libere):

#bookname=dexonline.ro
#version=3.0.0
#stripmethod=keep
#sametypesequence=h
#date=2018-01-21
#description=Acesta este o adaptare a bazei de date DEX online sub licența GPL.

iar dupa aceea trebuie trecut prin stardict-editor pentru a obtine cele 3 fisiere stardict. Eu am folosit anumite software si anumite proceduri ca sa obtin formatul gls, dar evident, din moment ce se lucreaza cu soft-uri libere si formate text, puteti folosi orice software va e la indemana.

Cum sa-l folosesc

Cele 3 fisiere dex.* trebuie copiate in folderul de dictionare al koreader sau al oricarui alt cititor care cunoaste stardict. Daca doriti, puteti reincepe procesul pentru a obtine din nou baza de date si dictionarul.

Softuri folosite

• Artix Linux
• vim
• Sql Workbench/j
• StarDict editor
• dictd (din pachetele Artix Linux).
• MySql

Pasi

Instalati softurile necesare.

Atentie la stardict-tools. Pachetul este configurat sa depinda de libmysqlclient, care nu mai exista acum in Arch sau Artix Linux. Trebuie sa editati fisierul PKGBUILD si sa eliminati de acolo aceasta dependinta. Probabil libmysqlclient este folosit de alte softuri din pachet, stardict-editor (care ne trebuie noua) va merge.

Descarcati baza de date dexonline de

aici.   Instrucțiuni de instalare

Incarcati-o in mysql.

Un exemplu poate fi vazut aici

Folosind Sql Workbench/J, exportati datele in format CSV

In documentatia Sql Workbench/J sunt explicate anumite comenzi specifice, ca wbexport Le puteti gasi acolo. Eu am executat aceste comenzi:

wbexport -header=false -file=/tmp/rows.txt -delimiter=, -quoteChar="'" -quoteCharEscaping=none -quoteAlways=false -encoding=UTF-8;

select replace(l.form, "'", '') form, d.htmlRep html from Lexem l inner join EntryLexem el on l.id = el.lexemId
inner join Entry e on el.entryId = e.id
inner join EntryDefinition ed on e.id = ed.entryId
inner join Definition d on ed.definitionId = d.id

In urma acestei operatii, am obtinut datele in format text in fisierul /tmp/rows.txt.

Preparati fisierul text

Fisierul text obtinut trebuie transformat in format .gls. Puteti gasi informatii despre format aici.

Am editat fisierul cu vim si am facut urmatoarele schimbari:

• :%s/\v'$//g (am eliminat ' de la sfarsitul fiecarei linii unde exista)
• :%s/\v^([^,]+),'/\1, (am eliminat ' de dinaintea definitiilor)
• :%s/\v^([^,]+),(.*)$/\1^M\2^M (am separat lexemele de definitii si am adaugat o linie noua dupa fiecare definitie)

Atentie la caracterele speciale: au fost obtinute apasand Ctrl si V si apoi Enter. Am inlocuit in principiu textele gasite cu linii noi.

Acum datele sunt in format .gls.

Creati fisierul dex.gls cu urmatorul continut:

#bookname=dexonline.ro
#version=3.0.0
#stripmethod=keep
#sametypesequence=h
#date=2018-01-21
#description=Acesta este o adaptare a bazei de date DEX online sub licența GPL.

-continut-rows.txt-modificat-

Atentie la liniile libere. Prima linie din fisier trebuie sa fie o linie libera si dupa definitia fisierului (linniile care incep cu #) trebuie inserat continutul modificat al rows.txt.

Salvati fisierul si inchideti vim.

Creati dictionarul

Deschideti stardict-editor. In interfata, selectati jos in stanga formatul Babylon file (nu Tab file, care este selectat initial). Apoi selectati fisierul creat dex.gls cu optiunea Browse in partea din stanga sus. Apoi apasati Compile in partea din dreapta jos. Daca totul a mers ok, ar trebui sa obtineti un mesaj de genul acesta:

Building...
Over
wordcount: <n>
Done!

unde n este numarul de cuvinte din dictionar.

Acum ar trebui sa aveti langa fisierul dex.gls cele 3 fisiere stardict (dex.dict.dz, dex.idx, dex.ifo). Aceste 3 fisiere reprezinta dictionarul in format stardict. Trebuie sa le copiati in functie de e-reader-ul folosit in folderul indicat de documentatia KoReader

Sursa: https://github.com/cosminadrianpopescu/dexonline-stardict