Re: [sqlite] Question about a query

[James K. Lowden]

On Tue, 9 Oct 2018 10:22:12 -0700
Jens Alfke  wrote:

> You could implement a custom query function to do this (custom
> functions are quite simple, and there are examples online). 

http://www.schemamania.org/sql/sqlite/udf/

Been there, done that.  :-)

--jkl

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[James K. Lowden]

On Sat, 6 Oct 2018 21:21:38 +0100
Simon Slavin  wrote:

>  There is never any point in this process when a manager looks at
> what's being done with Excel and says "Okay we need to hire a
> programmer to turn this into a proper App.".

Hmm, there is such a point.  I used to do work like that, and there
were others in the firm who did, too.  Still are. afaik.  

Modeling and prototyping get done by analysts with numerical and
statistical tools: Excel, sure, but also SAS, Matlab, R, etc.  Not
infrequently, some sinister stew cooked up with a database or two
added (or taken from).  If the model/prototype proves useful, it will
be reimplemented as a production application in a "real language"
to keep better control of the computational result.  The more users,
the more control is needed,  because fools^Wusers are so inventive.  

It's actually a gargantuan, industry-wide, systemic waste of time.  If
the core of the model could be be extracted from its GUI and inserted
as a module in a production program, millions of man-hours could be
saved every year.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Simon Slavin]

On 9 Oct 2018, at 10:38pm, Keith Medcalf  wrote:

> And just what is NSFW spam?

Messages purporting to come from young women.  Some including images purporting 
to be them in various states of undress.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list

[Warren Young]

On Oct 9, 2018, at 3:05 PM, Simon Slavin  wrote:
> 
> On 9 Oct 2018, at 9:49pm, Warren Young  wrote:
> 
>> Also: This list may be an unusually juicy target, given the number of places 
>> SQLite is deployed.
> 
> The minute SQLite gains any sort of internet connectivity, a hundred thousand 
> man-hours of cracking attempts will be launched.  Which is why it's great the 
> way it is.  Back door access to every mobile phone's contact list ?  That's 
> monetised-hacker heaven.

Who said anything about putting a TCP listener into SQLite?

When — I see no reason to say “if” — this mailing list moves to Fossil forums, 
it will be as a *Fossil* forum, using the same code as currently powers 
https://fossil-scm.org/forum, not code that is part of SQLite.

> As Fossil gains TCP, sockets, streams, or anything else, it becomes a more 
> tempting target. 

Fossil v2.7 opens the same TCP listening sockets that v2.6 did, given equal 
conditions.

There’s a half-baked SMTP server feature in 2.7, but it’s not recommended for 
general use yet.  Presumably it will land in Fossil 2.8, but if you don’t 
enable the feature, it won’t listen.

I plan to continue delegating SMTP service to the battle-tested mainstream SMTP 
server that came with my Fossil server’s OS.

> The best protection is that all source code is public.  Anyone who thinks 
> there's vuln can raise it, and ten people will evaluate it while the USA is 
> still asleep.

A graph of Fossil forum traffic currently correlates pretty well with US 
daylight hours.  2 a.m. US time is a pretty lonely time on the forum.

So, we apparently need more European, African, Asian, and Oceanian Fossil 
users. :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Keith Medcalf]

>One of the problems we’ve been having on these lists is that every
>time someone posts to it, they get NSFW spam, presumably because by
>posting, you’ve just proven that your email address is valid.
>Everyone on the ML sees the poster’s email address.  My mailer
>included yours in the quoting structure above, and you quoted mine in
>the earlier quotes above.

And just what is NSFW spam?

Nazi Spam From Wankers?

Perhaps someone needs better spam filters or stop using freemail ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Warren Young]

On Oct 9, 2018, at 2:50 PM, Darren Duncan  wrote:
> 
> On 2018-10-09 12:56 PM, Eric wrote:
>> On Tue, 9 Oct 2018 11:19:13 -0600, Warren Young  wrote:
>>> Gmane is part of the problem that lead to the creation of the Fossil
>>> forum feature.  Viz., it enables spammers, by design:
>>> 
>>> http://gmane.org/about/
>> I don't see that page saying what you claim it says.
> 
> The only thing I saw along those lines was that gmane made it easy to harvest 
> the email addresses of all the forum posters, which could lead to external 
> spam, but I didn't see anything about it making things easier to send spam to 
> the lists themselves, unless that's implied by something else. -- Darren 
> Duncan

One of the problems we’ve been having on these lists is that every time someone 
posts to it, they get NSFW spam, presumably because by posting, you’ve just 
proven that your email address is valid.  Everyone on the ML sees the poster’s 
email address.  My mailer included yours in the quoting structure above, and 
you quoted mine in the earlier quotes above.

(I’ve been on mailing lists where they yelled at you if you didn’t strip such 
things from the email, never mind not having any evidence that this helped.)

The only people who can see email addresses on a Fossil forum are the Admin and 
Setup users, which are typically the same person, and there’s usually only one. 
 They aren’t visible to subscribers, and they aren’t included in forum 
repository clones.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Warren Young]

On Oct 9, 2018, at 1:56 PM, Eric  wrote:
> 
> I suppose I must be an "anti-forum type" even though I have never used
> Gmane, but it does rather sound as though you are applying a somewhat
> perjorative label here.

I make no value judgement.  If you are against web forums, then you are 
anti-forum.  That’s just grammar.

>> Gmane is part of the problem that lead to the creation of the Fossil
>> forum feature.  Viz., it enables spammers, by design:
>> 
>>http://gmane.org/about/
> 
> I don't see that page saying what you claim it says.

“Gmane makes it much easier for spam harvesters to gather these real, authentic 
mail addresses. Even though the Gmane web interface to the news spool 
obfuscates all addresses, a spam harvesting bot just has to point itself to the 
news interface to slurp down the entire spool. And there's not much I can do to 
stop that from happening.”

>> The arguments about mailing lists vs forums have all been had.
> 
> Not in my hearing :-)

How long have you been on this list?  The last such thread was about 4 months 
ago:

   http://sqlite.1065341.n5.nabble.com/Mailing-list-shutting-down-td102466.html

That event was the immediate spur to start this Fossil forum project, but if 
you search the archives, there are multiple threads.  Here’s one from about a 
year ago:


http://sqlite.1065341.n5.nabble.com/Many-ML-emails-going-to-GMail-s-SPAM-td98685.html

If you think that thread is about a Gmail-specific problem, you’ve 
misunderstood it.  The problem is happening because spam gets reflected off 
this list, so people click “This is Spam” in Gmail, which causes Gmail’s spam 
filters to treat all messages on the list as more spammish.  The more that 
happens, the less likely a given SQLite ML message is to get to a Gmail user.

That problem will affect any mail system with centralized spam detection.  
Gmail is just the biggest such provider, so it affects more people than any 
other.

Meanwhile, the Fossil forum has been up for 78 days now without even an 
*attempted* spam, as far as I’m aware, in part due to Fossil’s pre-existing 
anti-bot defenses:

https://www.fossil-scm.org/fossil/doc/trunk/www/antibot.wiki

If some spammer manages to get a posting past those defenses, it’ll be stopped 
by one of the moderators.

> if I wanted to reply in context (like this very email) I would have to
> construct it manually and paste it into the forum.

The need for message quoting is much reduced when you’ve got a 
properly-threaded web view.

As you can see from this message, I’m a big believer in brief, on-point quotes 
on mailing lists, but I only do it on the Fossil forum when there are multiple 
topics that need to be addressed separately, as in this message.  When my 
entire reply follows clearly from the prior post, I don’t bother quoting.

Keep in mind that Fossil forums default to Markdown formatting, so all it takes 
to quote something is to insert a right angle bracket and space at the start of 
a line, followed by a paste of the text you want to show as quoted in the reply.

…which is the standard for email, too, which means MUAs display it as expected 
as well.

> Even just reading, some of the forum emails are meaningless because
> there is no context. I know, I could follow the link to the forum to see
> the context - and then come back, and then go to the forum for a
> different context …

You say you’ve just joined, which means your mail reader doesn’t have any of 
the context locally, which is no different from a just-joined mailing list.

If, after several days you have the same problem with threads where the entire 
conversation has been sent to you, then the problem is in your mailer’s ability 
to reconstruct a thread.

I do believe Fossil’s email alerts system sends the proper headers to allow 
threaded viewing.  If you find it’s not working, let us know which mailer 
you’re using, and maybe we can add more headers to clue it into the proper 
threading.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list

[Simon Slavin]

On 9 Oct 2018, at 9:49pm, Warren Young  wrote:

> Also: This list may be an unusually juicy target, given the number of places 
> SQLite is deployed.

The minute SQLite gains any sort of internet connectivity, a hundred thousand 
man-hours of cracking attempts will be launched.  Which is why it's great the 
way it is.  Back door access to every mobile phone's contact list ?  That's 
monetised-hacker heaven.

As Fossil gains TCP, sockets, streams, or anything else, it becomes a more 
tempting target.  But the biggest dangers are more difficult, and take longer, 
to exploit.  And I'm not going to discuss them publicly.  Nevertheless, it's a 
less tempting target than SQLite.

The best protection is that all source code is public.  Anyone who thinks 
there's vuln can raise it, and ten people will evaluate it while the USA is 
still asleep.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Darren Duncan]

On 2018-10-09 12:56 PM, Eric wrote:

On Tue, 9 Oct 2018 11:19:13 -0600, Warren Young  wrote:

Gmane is part of the problem that lead to the creation of the Fossil
forum feature.  Viz., it enables spammers, by design:

 http://gmane.org/about/


I don't see that page saying what you claim it says.


The only thing I saw along those lines was that gmane made it easy to harvest 
the email addresses of all the forum posters, which could lead to external spam, 
but I didn't see anything about it making things easier to send spam to the 
lists themselves, unless that's implied by something else. -- Darren Duncan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list

[Warren Young]

On Oct 9, 2018, at 1:20 PM, Ian Zimmerman  wrote:
> 
> There are many mailing lists which are not moderated by a human (to my
> knowledge) and do not suffer from this problem.  Why?  Maybe because
> someone took time to put a well tuned spam filter in place on the MTA
> level? 

Would you rather drh be writing SQLite and other software or managing MTAs and 
spam software?  At least if he spends time writing forum and SMTP features for 
Fossil, he’s adding value to the world, rather than simply fighting its decay.

If you say “hire it out,” where do those funds come from?

Moderation delegates the workload to people who volunteer to do it, which gives 
drh time to do the things he’d prefer to be doing, much of which overlaps with 
the things we’d rather he be doing, too.

Also: This list may be an unusually juicy target, given the number of places 
SQLite is deployed.  Are you comparing apples to apples?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Eric]

On Tue, 9 Oct 2018 11:19:13 -0600, Warren Young  wrote:
> On Oct 9, 2018, at 7:48 AM, Scott Robison  wrote:
>> 
>> Isn't Gmane a web forum style interface to email lists?
> 
> Gmane also provides NNTP access.  I suspect the anti-forum types are
> using Usenet news readers to follow such lists.

That's precisely what Gmane was always for.

I suppose I must be an "anti-forum type" even though I have never used
Gmane, but it does rather sound as though you are applying a somewhat
perjorative label here.
> 
> Gmane is part of the problem that lead to the creation of the Fossil
> forum feature.  Viz., it enables spammers, by design:
> 
> http://gmane.org/about/

I don't see that page saying what you claim it says.

> The arguments about mailing lists vs forums have all been had.

Not in my hearing :-)

> The software's been written ...

So I've joined the Fossil forum, and I get each post as an email and
they seem to thread properly, but of course I can't post by email, and
if I wanted to reply in context (like this very email) I would have to
construct it manually and paste it into the forum.

Even just reading, some of the forum emails are meaningless because
there is no context. I know, I could follow the link to the forum to see
the context - and then come back, and then go to the forum for a
different context ...

It's all too much trouble for someone with a lot of mailing list
subscriptions, there isn't enough time in the day, it's a lot easier to
keep a finger on the pulse without reading everything in mailing lists
than it is with forums.

I suppose all that amounts to a somewhat blurry feature request ;-)

Eric
-- 
ms fnd in a lbry
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list

[Ian Zimmerman]

On 2018-10-09 11:19, Warren Young wrote:

> Gmane is part of the problem that lead to the creation of the Fossil
> forum feature.  Viz., it enables spammers, by design:
> 
> http://gmane.org/about/
> 

[...]

> It is possible that Fossil will grow an email submission feature
> before that happens, so that this list *appears* to remain in place,
> even after it's moved to a Fossil forum on the back end.  If that
> happens, I expect such email will be moderated by default.  I'm not
> sure whether it will ever be safe to mark email submissions as not
> needing moderation, due to the ease of email From address forgery.

There are many mailing lists which are not moderated by a human (to my
knowledge) and do not suffer from this problem.  Why?  Maybe because
someone took time to put a well tuned spam filter in place on the MTA
level? 

-- 
Please don't Cc: me privately on mailing lists and Usenet,
if you also post the followup to the list or newsgroup.
To reply privately _only_ on Usenet and on broken lists
which rewrite From, fetch the TXT record for no-use.mooo.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about a query

[Jens Alfke]

> On Oct 9, 2018, at 6:47 AM, Leonardo Inácio de Freitas 
>  wrote:
> 
> Using SQLite, can you use masks (or regex) (like '% str%') inside
> instr / substr, to delimit the output of a select, instead of me
> determining the beginning and end of the substring?

You could implement a custom query function to do this (custom functions are 
quite simple, and there are examples online). 

Or you could just postprocess the string in your application code when it comes 
back from the query.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Warren Young]

On Oct 9, 2018, at 7:48 AM, Scott Robison  wrote:
> 
> Isn't Gmane a web forum style interface to email lists?

Gmane also provides NNTP access.  I suspect the anti-forum types are using 
Usenet news readers to follow such lists.

Gmane is part of the problem that lead to the creation of the Fossil forum 
feature.  Viz., it enables spammers, by design:

http://gmane.org/about/

The arguments about mailing lists vs forums have all been had.  The software’s 
been written and is now successfully being used, tested, and enhanced, both 
within the Fossil project and in third-party repositories.  (Two of my own 
public repos are now using Fossil forums, for example.)

I believe the only uncertainties left are how many more features and how much 
more battle testing drh will want before cutting this mailing list over.

It is possible that Fossil will grow an email submission feature before that 
happens, so that this list *appears* to remain in place, even after it’s moved 
to a Fossil forum on the back end.  If that happens, I expect such email will 
be moderated by default.  I’m not sure whether it will ever be safe to mark 
email submissions as not needing moderation, due to the ease of email From 
address forgery.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about a query

[Brian Curley]

well...

It's not quite that categorical "no", although Simon's more than correct.

There's extensions that allow for regexp(), such as you might find in
SQLite Studio that allow for some really handy cross-functionality when
paired with group_concat(), for example. (It's addictive to have it handy
during development, in fact.) I understand that it can be rolled into your
own local build, if you want to use it, but it complicates things in terms
of portability. If you craft your SQL around that...you need to bring that
build along with it, and any headaches that it might include.

Otherwise, you'd want to leverage your application's handling to emulate
the same behavior.

Regards.

Brian P Curley


On Tue, Oct 9, 2018 at 10:04 AM Simon Slavin  wrote:

> On 9 Oct 2018, at 2:47pm, Leonardo Inácio de Freitas <
> oldbrain...@gmail.com> wrote:
>
> > Using SQLite, can you use masks (or regex) (like '% str%') inside
> > instr / substr, to delimit the output of a select, instead of me
> > determining the beginning and end of the substring?
>
> No.  Sorry.  You have to use string core functions to isolate the piece
> you want:
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about a query

[Simon Slavin]

On 9 Oct 2018, at 2:47pm, Leonardo Inácio de Freitas  
wrote:

> Using SQLite, can you use masks (or regex) (like '% str%') inside
> instr / substr, to delimit the output of a select, instead of me
> determining the beginning and end of the substring?

No.  Sorry.  You have to use string core functions to isolate the piece you 
want:



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Scott Robison]

On Tue, Oct 9, 2018, 6:34 AM Will Parsons  wrote:

> On Sunday,  7 Oct 2018  5:25 PM -0400, Keith Medcalf wrote:
> >
> > Many people do not "do" web forums.  I am one of them.  If there is not
> a mailing list then it does not exist.
>
> I completely agree.  I read and post to the SQLite mailing via Gmane,
> and I used to do the same for Fossil.  Now that the Fossil mailing
> list has gone away, I can't use Gmane any more, so I no longer follow
> Fossil.  Please, let's *not* have the same thing happen with SQLite3!


Isn't Gmane a web forum style interface to email lists? That doesn't seem
much different than what fossil is now doing, though maybe my lack of
experience using Gmane is preventing me from seeing a difference.

As described by at least one other, I use email to track the fossil forum
just as I always have, and can log in there to post when I want. I can
appreciate why people who refuse to use a web interface might object to it,
but not Gmane.

There are potential cons to the forum, but there are pros as well.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about a query

[Leonardo Inácio de Freitas]

Hello!
Using SQLite, can you use masks (or regex) (like '% str%') inside
instr / substr, to delimit the output of a select, instead of me
determining the beginning and end of the substring?

What I need (example): SELECT (substr (my_field, like% abc,
until_next_spacechar_after (like% abc))) from my_table

That is, after getting the start of the substring using a like mask, I
want it from this point, the end of the substring to be the next
character space.

Thank you!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Will Parsons]

On Sunday,  7 Oct 2018  5:25 PM -0400, Keith Medcalf wrote:
>
> Many people do not "do" web forums.  I am one of them.  If there is not a 
> mailing list then it does not exist.

I completely agree.  I read and post to the SQLite mailing via Gmane,
and I used to do the same for Fossil.  Now that the Fossil mailing
list has gone away, I can't use Gmane any more, so I no longer follow
Fossil.  Please, let's *not* have the same thing happen with SQLite3!

-- 
Will

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users