Re: [sqlite] extracting domain names from website addresses efficiently

Thank you for your help! On 2017-12-11 22:43, Igor Tandetnik wrote: On 12/11/2017 12:22 PM, Klaus Maas wrote: There is 1 item in the last where condition of which I do not understand the purpose: (instr(subdomain, '.') =

Re: [sqlite] extracting domain names from website addresses efficiently

Klaus. The CTE manual with good examples is at https://www.sqlite.org/lang_with.html IgorT posted some good stuff about your problem using CTE. FYI. TRIGGER is also recursive. Could be more efficient if you have to store them anyway: CREATE TABLE domain(d TEXT); CREATE TRIGGER domain_after_ins

Re: [sqlite] Retrieving constraint name

On Mon, Dec 11, 2017 at 4:48 PM, Cezary H. Noweta wrote: > Hello, > > On 2017-12-11 04:29, Igor Korot wrote: >> >> Thank you, but I need to keep the official SQLite code. > > Anyway, for the people who are interested in foreign key names: > http://sqlite.chncc.eu/fknames/. Certainly, all tests exp

Re: [sqlite] Retrieving constraint name

Hello, On 2017-12-11 04:29, Igor Korot wrote: Thank you, but I need to keep the official SQLite code. Anyway, for the people who are interested in foreign key names: http://sqlite.chncc.eu/fknames/. Certainly, all tests expecting original output of ``PRAGMA foreign_key_list'' will fail. -- b

Re: [sqlite] extracting domain names from website addresses efficiently

On 12/11/2017 12:22 PM, Klaus Maas wrote: There is 1 item in the last where condition of which I do not understand the purpose: (instr(subdomain, '.') = 0 and subdomain = long) That's a zero-dot case, when the original domain is simply 'com', say. Added for completeness. This means that any

Re: [sqlite] readfile/writefile extensioln, and UTF8 on Windows....

Certainly; the source file is short. I did successfully post it to the 'dev' list, however, which I guess is set to accept attachements. You might want to make edits if you do choose to encorporate it into the canonical codebase. E.g.: * I think there are some existing sqlite utility functions

Re: [sqlite] readfile/writefile extensioln, and UTF8 on Windows....

On 12/12/2017 12:42 AM, dave wrote: Folks; I recently had some trouble using the readfile() extension until I noticed it was due to there being filenames with Unicode in them, and that the existing implementation using fopen() doesn't do UTF8 on Windows (I think it uses the ambient code page).

Re: [sqlite] readfile/writefile extensioln, and UTF8 on Windows....

Welp; I guess attachements get stripped off. And maybe I should have sent this to the 'dev' list anyway, so I'll try there. > -Original Message- ... > To: 'SQLite mailing list' > Subject: [sqlite] readfile/writefile extensioln, and UTF8 on > Windows > > Folks; I recently had some t

[sqlite] readfile/writefile extensioln, and UTF8 on Windows....

Folks; I recently had some trouble using the readfile() extension until I noticed it was due to there being filenames with Unicode in them, and that the existing implementation using fopen() doesn't do UTF8 on Windows (I think it uses the ambient code page). I modified the extension code to supp

Re: [sqlite] extracting domain names from website addresses efficiently

Thank you, Igor, for your solution. I expected that I would be pointed to a recursive approach. Your solution is quite elegant in my opinion. My problem, however, is that I have not yet understood recursive CTE's. Could you give me some pointers for good resources to learn about them? Well, your

Re: [sqlite] Enabling MMAP in Android

On 12/11/17, advancenOO wrote: > > At the same time, Mmap() is only used for reading by default, not writing. > But why? If the database is memory-mapped writable, then a stray pointer or array overrun in the application (NB: not in SQLite - in the application that uses SQLite) could easily overw

Re: [sqlite] Enabling MMAP in Android

Have you actually got speed issues, or is this just a case of "this should be faster"? If you DO have speed issues, my experience from reading this list is that most can be improved with better SQL, better indices, or better interplay between readers and writers. Only if those have been exhauste

Re: [sqlite] extracting domain names from website addresses efficiently

This seems like a job for regular expressions. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Enabling MMAP in Android

I find MMAP is able to be used for db files(getPageMMap()) but is not enabled by default. I wonder if it is recommended to use MMAP in Android? As I think MMAP is one way to improve the speed performance(I use speedtest1.c to evaluate). At the same time, Mmap() is only used for reading by default,

Re: [sqlite] extracting domain names from website addresses efficiently

Something like this (could likely be simplified further, but this should give the idea): with recursive subdomains as ( SELECT substr(link, instr(link, '://')+3, instr(substr(link, instr(link, '://')+3), '/')-1) AS long, substr(link, instr(link, '://')+3, instr(substr(link, instr(lin

[sqlite] extracting domain names from website addresses efficiently

I hope someone can help me to improve my SQL. The solution I came up with becomes unreadable after 3 dots in a website address (at least for me). This is my approach: The domain/subdomain name of a website address is enclosed between '://' and the following '/'. So I trim any text before and

[sqlite] Inconsistent use of precompiler symbol SQLITE_HAS_CODEC

In most places in the SQLite source code the check #ifdef SQLITE_HAS_CODEC is used. However, at 4 places (1 in btree.c, 1 in ctime.c, and 2 in pager.c) the check #if SQLITE_HAS_CODEC is used. The latter can fail to be correctly evaluated, if one only defines the symbol, but doesn't set a va

Re: [sqlite] SQLite3 on WinCE platform

On 11 Dec 2017, at 8:59am, Ertan Küçükoğlu wrote: > Error I am getting when application runs is: EInOutError: Can not load > SQLite client library "sqlite3ce.dll". Check your installation. Given this page "EInOutError is ra

[sqlite] SQLite3 on WinCE platform

Hello, I am using SQLite3 on handheld barcode scanners. Development platform of my choice is Lazarus (free pascal). These handheld devices run WinCE operating system. I have found a sqlite3ce.dll which I have downloaded from Lazarus forums. That DLL is version 3.9.2.0 compiled for WinCE 5+ (armv4