Rocksolid Light

Welcome to RetroBBS

mail  files  register  newsreader  groups  login

Message-ID:  

Dare to be naive. -- R. Buckminster Fuller


rocksolid / Rocksolid Nodes / Re: sqllite, huh ?

SubjectAuthor
* sql in rslightRetro Guy
+- NoneAnonymous
+- sql in rslightRetro Guy
+* sqllite, huh ?Anonymous
|+* sqllite, huh ?Retro Guy
||`* sqllite, huh ?Retro Guy
|| `- sqllite, huh ?Retro Guy
|`* sqllite, huh ?Retro Guy
| `* sqllite, huh ?AnonUser
|  `* sqllite, huh ?Retro Guy
|   `* sqllite, huh ?trw
|    +- sqllite, huh ?Retro Guy
|    `* sqllite, huh ?Retro Guy
|     `* sqllite, huh ?Retro Guy
|      +- sqllite, huh ?Retro Guy
|      `* sqllite, huh ?Retro Guy
|       `* sqllite, huh ?Retro Guy
|        `- sqllite, huh ?Retro Guy
+* sqllite, huh ?Anonymous
|`- sqllite, huh ?Retro Guy
+* sqlite, huh ?Anonymous
|`- sqlite, huh ?Retro Guy
`* sql in rslightAnonymous
 `- sql in rslightRetro Guy

1
sql in rslight

<20201211185438.0facd5ad@desktop14.dt>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: retro_guy@retrobbs.rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: sql in rslight
Date: Fri, 11 Dec 2020 18:54:38 -0700
Organization: RetroBBS
Message-ID: <20201211185438.0facd5ad@desktop14.dt>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: novabbs.org; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="17699"; mail-complaints-to="usenet@novabbs.org"
X-Newsreader: Claws Mail 3.11.1 (GTK+ 2.24.25; x86_64-pc-linux-gnu)
 by: Retro Guy - Sat, 12 Dec 2020 01:54 UTC

I've avoided using any database in Rocksolid Light to this point, but
finally have decided to do so.

I'm using sqlite, so no dealing with the database as an admin is
necessary, rslight handles the whole thing.

This comes about as there is one major issue in newsportal that is a
memory hog. It is the one thing I've been fighting with for quite a
while, and have decided a db is what is required to handle this.
newsportal loads the headers of an entire group into ram to thread the
messages, then repeatedly iterates through the array. In large
groups (50,000+), it exhausts default memory allowed in default php
settings. Plus, it's slow.

To get comfortable with sqlite, I've started with the search function.
Now on rslight.i2p (only this install), search is handled with a sqlite
database and it's working fine. I now plan to do the same with
overboard (which is almost done). I want to work with my own code
before jumping into a major change in newsportal code.

Anyway, rslight will use sqlite in future releases. The upgrade process
will automatically create the databases from existing flat files (I
already have these functions written), then go from there. It should be
pretty seamless for an admin (if there are any other than myself, lol).

So I guess I have to remove 'Database not required' from the README :)

Retro Guy

None

<n.1266.1yuknt@anon.com>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid2!def5!.POSTED.bogusentry!not-for-mail
From: poster@anon.com (Anonymous)
Newsgroups: rocksolid.nodes
Subject: None
Date: Sat, 12 Dec 2020 04:51:51 -0800
Organization: def5
Message-ID: <n.1266.1yuknt@anon.com>
References: <20201211185438.0facd5ad@desktop14.dt>
Content-Type: text/plain; charset=UTF-8
Injection-Info: def5.org; posting-host="bogusentry:192.168.1.189";
logging-data="4029"; mail-complaints-to="usenet@def5.org"
 by: Anonymous - Sat, 12 Dec 2020 12:51 UTC

The filesystem is a database.

--
Posted on def2

Re: sql in rslight

<20201215022937.1e07341b@desktop14.dt>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!i2pn.org!rocksolid2!.POSTED.localhost!not-for-mail
From: retro_guy@retrobbs.rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sql in rslight
Date: Tue, 15 Dec 2020 02:29:37 -0700
Organization: RetroBBS
Message-ID: <20201215022937.1e07341b@desktop14.dt>
References: <20201211185438.0facd5ad@desktop14.dt>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: novabbs.org; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="30975"; mail-complaints-to="usenet@novabbs.org"
X-Newsreader: Claws Mail 3.11.1 (GTK+ 2.24.25; x86_64-pc-linux-gnu)
 by: Retro Guy - Tue, 15 Dec 2020 09:29 UTC

On Fri, 11 Dec 2020 18:54:38 -0700
Retro Guy <retro_guy@retrobbs.rocksolidbbs.com> wrote:

> I've avoided using any database in Rocksolid Light to this point, but
> finally have decided to do so.

This is working well so far. rslight.i2p runs the test code using the db

It's just sqlite, so only requires sqlite support in php which is no
different than adding support for all sorts of other things. There is
no db software installation required. No external setup. rslight just
creates the .db3 files as necessary, creates the tables, everything is
just done for you. There is no extra work.

I've found that search is much faster now, and overboard works quite
well and with way less disk access.

I have no plans to place the articles themselves in a database, only
the support files (overview, etc. which I've now done) I want to keep
the spool the same as an inn spool.

Retro Guy

sqllite, huh ?

<n.1268.460pfw@anon.com>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid2!.POSTED.127.117.190.215!not-for-mail
From: poster@anon.com (Anonymous)
Newsgroups: rocksolid.nodes
Subject: sqllite, huh ?
Date: Tue, 15 Dec 2020 09:14:02 -0800
Organization: rocksolid2 (novabbs.org)
Message-ID: <n.1268.460pfw@anon.com>
References: <20201211185438.0facd5ad@desktop14.dt>
Content-Type: text/plain; charset=UTF-8
Injection-Info: novabbs.org; posting-account="def.i2p"; posting-host="127.117.190.215";
logging-data="27437"; mail-complaints-to="usenet@novabbs.org"
 by: Anonymous - Tue, 15 Dec 2020 17:14 UTC

>>d279998bf9073666e2
>rslight just creates the .db3 files as necessary, creates >the tables, everything is just done for you. There is no >extra work.

that sounds just lovely. Correct database setup is one more unpleasant parts, although I have to say it was very easy with vichan (not so much with fudforum). But if it is all handled inside your code, that should reduce this problem to zero.

cheers

trw

--
Posted on def2

Re: sqllite, huh ?

<53b1591460be38a137f320dc1c265eff$1@rslight.i2p>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: retro.guy@rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Wed, 16 Dec 2020 09:21:04 +0000
Organization: Rocksolid Light
Message-ID: <53b1591460be38a137f320dc1c265eff$1@rslight.i2p>
References: <20201211185438.0facd5ad@desktop14.dt> <n.1268.460pfw@anon.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Info: novabbs.org; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="11413"; mail-complaints-to="usenet@novabbs.org"
User-Agent: Rocksolid Light (news.novabbs.com/getrslight)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs.org
X-Rslight-Site: $2y$10$kG5AxKbtr7Y0rTck/DRUkeH8jl/vjHW.gcY2SnAJIq2qbW6WAL.mK
 by: Retro Guy - Wed, 16 Dec 2020 09:21 UTC

Anonymous wrote:

>>>d279998bf9073666e2
>>rslight just creates the .db3 files as necessary, creates >the tables, everything is just done for you. There is no >extra work.

> that sounds just lovely. Correct database setup is one more unpleasant parts, although I have to say it was very easy with vichan (not so much with fudforum). But if it is all handled inside your code, that should reduce this problem to zero.

sqlite is great for the purpose I'm using it. If you were storing critical or sensitive info, use another method that is protected as this is not, and it's not meant to be. The data I'm handling is not private, it's simply header data for messages, etc. It makes searching header data across messages very fast and easy.

Creating a db is as simple as creating a file (a db is in a file). If someone installed rslight and set it up, the only real thing you'd notice that shows it uses a db is that the instructions tell you phpX.x-sqlite3 is required. You don't need to take any action beyond installing that package.

I'm still learning so I may not be doing everything as best it can be done, but it's kinda fun and it's working great. Once I feel a bit more comfortable with it, I'll move on to some newsportal mods and work to reduce memory usage for big groups.

Retro Guy

--
Posted on Rocksolid Light
rslight.i2p

Re: sqllite, huh ?

<n.1270.3ui7zt@anon.com>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!.POSTED!not-for-mail
From: poster@anon.com (Anonymous)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Wed, 16 Dec 2020 04:44:57 -0800
Organization: i2pn2 (i2pn.org)
Message-ID: <n.1270.3ui7zt@anon.com>
References: <20201211185438.0facd5ad@desktop14.dt>
Content-Type: text/plain; charset=UTF-8
Injection-Info: i2pn2.org; posting-account="def2";
logging-data="31470"; mail-complaints-to="usenet@i2pn2.org"
 by: Anonymous - Wed, 16 Dec 2020 12:44 UTC

>>805247d43ca8bf30d0
> the only real thing you'd notice that shows it uses a db is that the instructions tell you phpX.x-sqlite3 is required. You don't need to take any action beyond installing that package.

How do you control the sql statements ? Are they all fully predefined or are you taking parameters from GET or POST ?

--
Posted on def2

Re: sqllite, huh ?

<20201216161452.5f1fb78a@desktop14.dt>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: retro_guy@retrobbs.rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Wed, 16 Dec 2020 16:14:52 -0700
Organization: RetroBBS
Message-ID: <20201216161452.5f1fb78a@desktop14.dt>
References: <20201211185438.0facd5ad@desktop14.dt>
<n.1270.3ui7zt@anon.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: novabbs.org; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="10175"; mail-complaints-to="usenet@novabbs.org"
X-Newsreader: Claws Mail 3.11.1 (GTK+ 2.24.25; x86_64-pc-linux-gnu)
 by: Retro Guy - Wed, 16 Dec 2020 23:14 UTC

On Wed, 16 Dec 2020 04:44:57 -0800
Anonymous <poster@anon.com> wrote:

> >>805247d43ca8bf30d0
> > the only real thing you'd notice that shows it uses a db is that
> > the instructions tell you phpX.x-sqlite3 is required. You don't
> > need to take any action beyond installing that package.
>
> How do you control the sql statements ? Are they all fully predefined
> or are you taking parameters from GET or POST ?

The sql statements use variables (after they're sanitized). They can be
from POST, but never used directly in the statements.

It's the same in vichan, that's where you see statements like:

$query = prepare("INSERT INTO ``search_queries`` VALUES
(:ip, :time, :query)");

Then the variables are inserted into the placeholders with bindvalue.
data should be sanitized for use using this step.

Both vichan and rslight use PDO so the statements look very similar.
Only that vichan uses mysql and rslight uses sqlite.

Re: sqlite, huh ?

<n.1272.3j5yok@anon.com>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid2!.POSTED.127.117.190.215!not-for-mail
From: poster@anon.com (Anonymous)
Newsgroups: rocksolid.nodes
Subject: Re: sqlite, huh ?
Date: Wed, 16 Dec 2020 15:46:58 -0800
Organization: rocksolid2 (novabbs.org)
Message-ID: <n.1272.3j5yok@anon.com>
References: <20201211185438.0facd5ad@desktop14.dt>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary=4c8a2493c821824186c5655f32a24f1ef5c36697
Injection-Info: novabbs.org; posting-account="def.i2p"; posting-host="127.117.190.215";
logging-data="30576"; mail-complaints-to="usenet@novabbs.org"
 by: Anonymous - Wed, 16 Dec 2020 23:46 UTC
Attachments: sqlite2.jpeg (image/jpeg)

>>ae4424233377cd30fc
>Both vichan and rslight use PDO so the statements look very similar.
Only that vichan uses mysql and rslight uses sqlite.

yeah, i took a look at that when i played around with vichan, then was happy that i did not have to touch it :-). I just don't know enough about databases (I mean I get their purpose and all, and I can install them basically, use them and in some cases repair them, but I found the whole topic always quite boreing).
The reason I ask is just that this is still one of the most often overlooked "features" for potential attackers. For both fudforum and vichan I did some research also on this, but could not find anything that would suggest that unsafe procedures were used.

So if the code is very similar between rslight and vichan, maybe this could be interesting to look at (I mean, maybe I can switch vichan to sqlite, using parts of your code. Maybe later...).

cheers

trw

Attachments: sqlite2.jpeg 
Re: sqlite, huh ?

<20201216222706.5d13e4af@desktop14.dt>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: retro_guy@retrobbs.rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sqlite, huh ?
Date: Wed, 16 Dec 2020 22:27:06 -0700
Organization: RetroBBS
Message-ID: <20201216222706.5d13e4af@desktop14.dt>
References: <20201211185438.0facd5ad@desktop14.dt>
<n.1272.3j5yok@anon.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: novabbs.org; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="1654"; mail-complaints-to="usenet@novabbs.org"
X-Newsreader: Claws Mail 3.11.1 (GTK+ 2.24.25; x86_64-pc-linux-gnu)
 by: Retro Guy - Thu, 17 Dec 2020 05:27 UTC

On Wed, 16 Dec 2020 15:46:58 -0800
Anonymous <poster@anon.com> wrote:

> So if the code is very similar between rslight and vichan, maybe this
> could be interesting to look at (I mean, maybe I can switch vichan to
> sqlite, using parts of your code. Maybe later...).

PDO may make this easier as that's the point of it:

"provides a data-access abstraction layer for working with databases in
PHP. It defines consistent API for working with various database
systems."

The idea is that the requests are the same, independent of what database
you are using.

I chose sqlite so that the process of needing to install mysql, create
users, create databases (in mysql), set permissions etc. is not
necessary at all.

Retro Guy

Re: sqllite, huh ?

<20201220035018.1e6671e9@desktop14.dt>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: retro_guy@retrobbs.rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Sun, 20 Dec 2020 03:50:18 -0700
Organization: RetroBBS
Message-ID: <20201220035018.1e6671e9@desktop14.dt>
References: <20201211185438.0facd5ad@desktop14.dt>
<n.1268.460pfw@anon.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: novabbs.org; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="30447"; mail-complaints-to="usenet@novabbs.org"
X-Newsreader: Claws Mail 3.11.1 (GTK+ 2.24.25; x86_64-pc-linux-gnu)
 by: Retro Guy - Sun, 20 Dec 2020 10:50 UTC

On Tue, 15 Dec 2020 09:14:02 -0800
Anonymous <poster@anon.com> wrote:

> >>d279998bf9073666e2
> >rslight just creates the .db3 files as necessary, creates >the
> >tables, everything is just done for you. There is no >extra work.
>
> that sounds just lovely. Correct database setup is one more
> unpleasant parts, although I have to say it was very easy with vichan
> (not so much with fudforum). But if it is all handled inside your
> code, that should reduce this problem to zero.

So far this is working great. Search is much better than before, and
easier to manage. Overboard will seem the same, but it's now much
easier to work with (the code) and should handle much larger installs.

Right now rslight.i2p (novabbs.org) contains 900,345 messages, and it
works reasonably quickly. With the db, growth should be less of a
challenge. With more articles, the flat files become a waste of time
and ram to search through.

This has also made expire work great. You can now expire all articles
by a config setting (defaults to never), and you can override any group
you wish with a different value. I currently expire alt.test at 30 days
and that's working fine.

This is all up and running on rslight right now, not my other sites.
I'm listing as 0.6.7 and it's a dev branch in git which I have not
pushed to github yet. I'll probably merge and then push after more
testing.

Retro Guy

Re: sqllite, huh ?

<77b72bfc4921ab9c88938fc2fb499bcf$1@www.rocksolidbbs.com>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid3!.POSTED.localhost!not-for-mail
From: anonuser@rocksolidbbs.com.remove-zhc-this (AnonUser)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Sun, 20 Dec 2020 21:22:07 +0000
Organization: RetroBBS
Message-ID: <77b72bfc4921ab9c88938fc2fb499bcf$1@www.rocksolidbbs.com>
References: <20201220035018.1e6671e9@desktop14.dt>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Info: rocksolidbbs.com; posting-host="localhost:127.0.0.1";
logging-data="24064"; mail-complaints-to="usenet@rocksolidbbs.com"
User-Agent: Rocksolid Light (news.novabbs.com/getrslight)
To: Retro Guy
X-Comment-To: Retro Guy
In-Reply-To: <20201220035018.1e6671e9@desktop14.dt>
X-FTN-PID: Synchronet 3.17a-Linux Dec 29 2018 GCC 6.3.0
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on rocksolidbbs.com
X-Rslight-Site: $2y$10$PwVnESpbf2qg5WQAXozZOuxVbbkjXemIMbnvD/72POEW3aUlRXmxe
X-Gateway: rocksolidbbs.com [Synchronet 3.17a-Linux NewsLink 1.110]
 by: AnonUser - Sun, 20 Dec 2020 21:22 UTC

To: Retro Guy
The search is working well, and is very fast, too.
--
Posted on RetroBBS
www.rocksolidbbs.com

Re: sqllite, huh ?

<ab8c5fa4037a3595e0f30ad1a526fc54$1@news.novabbs.org>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: retro.guy@rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Wed, 23 Dec 2020 11:32:15 +0000
Organization: Rocksolid Light
Message-ID: <ab8c5fa4037a3595e0f30ad1a526fc54$1@news.novabbs.org>
References: <20201220035018.1e6671e9@desktop14.dt> <77b72bfc4921ab9c88938fc2fb499bcf$1@www.rocksolidbbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Info: novabbs.org; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="19514"; mail-complaints-to="usenet@novabbs.org"
User-Agent: Rocksolid Light (news.novabbs.com/getrslight)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs.org
X-Rslight-Site: $2y$10$pgWJpS0Vt0/vTDJl4EEznuc8ReF6/SVcQY1NGz2PsWwMi/VSV8jp.
 by: Retro Guy - Wed, 23 Dec 2020 11:32 UTC

AnonUser wrote:

> To: Retro Guy
> The search is working well, and is very fast, too.

Thank you for that feedback :)

I just made a change to handle multibyte (names, subject) better. Seems ok but I'll keep monitoring.

Next is to combine search across 'sections'. This is part of a change that will make it simple to move a group from one section to another. Currently, that's not easy, lol. Once the change is done (testing is already working), groups will no longer be tied to sections, only for display purposes. Moving them will make no difference in how they operate.

--
Posted on Rocksolid Light
news.novabbs.org

Re: sqllite, huh ?

<5c54a78607c9145ca4bf48d69ffc72a9@def4>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid2!.POSTED.127.117.190.215!POSTED.localhost!not-for-mail
From: trw@anon.org (trw)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Thu, 24 Dec 2020 00:06:10+0000
Organization: def5
Message-ID: <5c54a78607c9145ca4bf48d69ffc72a9@def4>
References: <ab8c5fa4037a3595e0f30ad1a526fc54$1@news.novabbs.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Info: novabbs.org; posting-account="def.i2p"; posting-host="127.117.190.215";
logging-data="31773"; mail-complaints-to="usenet@novabbs.org"
In-Reply-To: <ab8c5fa4037a3595e0f30ad1a526fc54$1@news.novabbs.org>
 by: trw - Thu, 24 Dec 2020 00:06 UTC

If I had one feature request, it would be fulltext search on a group or section (so: doing the search also on the bodies).
Not sure if this is doable in a sane time window though if messages are not in a db.

--
Posted on def4

Re: sqllite, huh ?

<20201223171656.3a8b8d45@desktop14.dt>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: retro_guy@retrobbs.rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Wed, 23 Dec 2020 17:16:56 -0700
Organization: RetroBBS
Message-ID: <20201223171656.3a8b8d45@desktop14.dt>
References: <ab8c5fa4037a3595e0f30ad1a526fc54$1@news.novabbs.org>
<5c54a78607c9145ca4bf48d69ffc72a9@def4>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: novabbs.org; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="5288"; mail-complaints-to="usenet@novabbs.org"
X-Newsreader: Claws Mail 3.11.1 (GTK+ 2.24.25; x86_64-pc-linux-gnu)
 by: Retro Guy - Thu, 24 Dec 2020 00:16 UTC

On Thu, 24 Dec 2020 00:06:10+0000
trw <trw@anon.org> wrote:

>
> If I had one feature request, it would be fulltext search on a group
> or section (so: doing the search also on the bodies). Not sure if
> this is doable in a sane time window though if messages are not in a
> db.
>

That is something I've given some thought to and I agree, a full text
search on text files is very slow and keeps the server pretty busy.
It's not an idea I've dropped, just that it will take some more
consideration.

But I agree, it would be nice.

Re: sqllite, huh ?

<20201230231301.7f789621@desktop14.dt>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: retro_guy@retrobbs.rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Wed, 30 Dec 2020 23:13:01 -0700
Organization: RetroBBS
Message-ID: <20201230231301.7f789621@desktop14.dt>
References: <20201211185438.0facd5ad@desktop14.dt>
<n.1268.460pfw@anon.com>
<53b1591460be38a137f320dc1c265eff$1@rslight.i2p>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: novabbs.org; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="10198"; mail-complaints-to="usenet@novabbs.org"
X-Newsreader: Claws Mail 3.11.1 (GTK+ 2.24.25; x86_64-pc-linux-gnu)
 by: Retro Guy - Thu, 31 Dec 2020 06:13 UTC

On Wed, 16 Dec 2020 09:21:04 +0000
retro.guy@rocksolidbbs.com (Retro Guy) wrote:

> Anonymous wrote:
>
> >>>d279998bf9073666e2
> >>rslight just creates the .db3 files as necessary, creates >the
> >>tables, everything is just done for you. There is no >extra work.
>
> > that sounds just lovely. Correct database setup is one more
> > unpleasant parts, although I have to say it was very easy with
> > vichan (not so much with fudforum). But if it is all handled inside
> > your code, that should reduce this problem to zero.
>
> ...
>
> I'm still learning so I may not be doing everything as best it can be
> done, but it's kinda fun and it's working great. Once I feel a bit
> more comfortable with it, I'll move on to some newsportal mods and
> work to reduce memory usage for big groups.

I finally have newsportal using a sqlite database for thread data. I
have not done anything past that, just changed the way it stores the
data from a big 'serialized' file to a .db3. It seems to be working
fine. Right now this is only running on a test site.

Next step is to start working directly with the data in newsportal,
instead of just pulling it all into an array to work with. I just want
to do this slowly as it's a lot more work for me to make major changes
to newsportal code than it is to rslight code.

Re: sqllite, huh ?

<20210101032739.13d25186@desktop14.dt>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: retro_guy@retrobbs.rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Fri, 1 Jan 2021 03:27:39 -0700
Organization: RetroBBS
Message-ID: <20210101032739.13d25186@desktop14.dt>
References: <20201211185438.0facd5ad@desktop14.dt>
<n.1268.460pfw@anon.com>
<53b1591460be38a137f320dc1c265eff$1@rslight.i2p>
<20201230231301.7f789621@desktop14.dt>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: novabbs.org; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="26271"; mail-complaints-to="usenet@novabbs.org"
X-Newsreader: Claws Mail 3.11.1 (GTK+ 2.24.25; x86_64-pc-linux-gnu)
 by: Retro Guy - Fri, 1 Jan 2021 10:27 UTC

On Wed, 30 Dec 2020 23:13:01 -0700
Retro Guy <retro_guy@retrobbs.rocksolidbbs.com> wrote:

> On Wed, 16 Dec 2020 09:21:04 +0000
> retro.guy@rocksolidbbs.com (Retro Guy) wrote:
>
> I finally have newsportal using a sqlite database for thread data. I
> have not done anything past that, just changed the way it stores the
> data from a big 'serialized' file to a .db3. It seems to be working
> fine. Right now this is only running on a test site.

One issue I had with the way np handled it is that the entire group is
serialized into a file, and it (np) would just delete the file and
recreate it (takes a long time) for any changes (like the server
expired an article, etc.).

Now that it's in a db, I can access the data much easier and I've
started with dealing with expired articles. I simple delete them from
the database, not rebuild the entire thing. I eventually would like np
to thread directly reading/writing to the db, but not there yet.

Retro Guy

Re: sqllite, huh ?

<19fddbdc3def82d96a129c7b7eab383a$1@news.novabbs.org>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!i2pn.org!rocksolid2!.POSTED.localhost!not-for-mail
From: retro.guy@rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Fri, 8 Jan 2021 07:04:18 +0000
Organization: Rocksolid Light
Message-ID: <19fddbdc3def82d96a129c7b7eab383a$1@news.novabbs.org>
References: <ab8c5fa4037a3595e0f30ad1a526fc54$1@news.novabbs.org> <5c54a78607c9145ca4bf48d69ffc72a9@def4>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Info: novabbs.org; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="4549"; mail-complaints-to="usenet@novabbs.org"
User-Agent: Rocksolid Light (news.novabbs.com/getrslight)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs.org
X-Rslight-Site: $2y$10$/y79oWUWnM5NJZHhcnvXyuQKLFI64YP1/NDjRd.6gl7VdV8Bamhwq
 by: Retro Guy - Fri, 8 Jan 2021 07:04 UTC

trw wrote:

> If I had one feature request, it would be fulltext search on a group or section (so: doing the search also on the bodies).
> Not sure if this is doable in a sane time window though if messages are not in a db.

I've been working on the np "bug" for a while now and I think I have it working much better. This is the one where it rebuilds the entire group every chance it gets, which takes processing time. Now the groups are pruned if necessary, and I have plans to improve it some more, but it's working well now.

Back to search: I have been messing with this and have found one way to search through much less data to do a full text search. I'm using the code I wrote to display a snippet of a post in overboard, which does not include the quoted data. Many times the quoted data is most of the post. I'm using only the content from the actual poster in my testing (no quoted data), and it is not trimmed as it is for overboard, so it's the entire actual post without quote.

This is much less data to search through, and I am storing it in a database, which isn't using much space at all. I really wish I still had the code I wrote several years ago when I wrote a search engine for a tor site. It was quite nice, included search suggestions, similar words and even words that "sound" the same. If I ever get full text search working in rslight, I plan to slowly rewrite these features as it was quite fun :)

Retro Guy

--
Posted on Rocksolid Light
news.novabbs.org

Re: sqllite, huh ?

<94952dda02df0c1d6ebd7d1c4b852e46$1@www.novabbs.com>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!.POSTED!not-for-mail
From: retro.guy@rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Sat, 16 Jan 2021 06:34:57 +0000
Organization: novaBBS
Message-ID: <94952dda02df0c1d6ebd7d1c4b852e46$1@www.novabbs.com>
References: <ab8c5fa4037a3595e0f30ad1a526fc54$1@news.novabbs.org> <5c54a78607c9145ca4bf48d69ffc72a9@def4> <19fddbdc3def82d96a129c7b7eab383a$1@news.novabbs.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Info: i2pn2.org; posting-account="retrobbs1";
logging-data="24343"; mail-complaints-to="usenet@i2pn2.org"
User-Agent: Rocksolid Light (news.novabbs.com/getrslight)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs
X-Rslight-Site: $2y$10$DqiEz9eYOVZ8sDYS75./fOOxk0oo0pocghDAj8E/10FjPA.sD2gAW
 by: Retro Guy - Sat, 16 Jan 2021 06:34 UTC

I'm currently upgrading rslight.i2p (novabbs.org) to use the new articles database. It's a test and I really hope it doesn't have too many issues. Yes, I have a full backup.

Using a database for articles is a simple config option/toggle. Either tradspool or database.

This may remove the inodes problem with a lot of articles. For example, rslight.i2p has about 910,000 articles currently in 289 groups. Moving to a database means not having 910,000 individual files, only 289 individual files. There is one .db3 per group.

Testing has shown this to be pretty fast, but I want to run it on a real site to see if that's accurate as the test site is only rocksolid.*

One reason I want it to work is that I have about 12 hours into it and I'd hate to think it was a waste of time, lol :)

Retro Guy

--
Posted on novaBBS
www.novabbs.com

Re: sqllite, huh ?

<dc308803d8e3d730d3dd5718e624c9cb$1@www.novabbs.com>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!.POSTED!not-for-mail
From: retro.guy@rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Mon, 18 Jan 2021 11:42:55 +0000
Organization: novaBBS
Message-ID: <dc308803d8e3d730d3dd5718e624c9cb$1@www.novabbs.com>
References: <ab8c5fa4037a3595e0f30ad1a526fc54$1@news.novabbs.org> <5c54a78607c9145ca4bf48d69ffc72a9@def4> <19fddbdc3def82d96a129c7b7eab383a$1@news.novabbs.org> <94952dda02df0c1d6ebd7d1c4b852e46$1@www.novabbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Info: i2pn2.org; posting-account="retrobbs1";
logging-data="21433"; mail-complaints-to="usenet@i2pn2.org"
User-Agent: Rocksolid Light (news.novabbs.com/getrslight)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs
X-Rslight-Site: $2y$10$nfPojOY/kGoPKaIkh8dH8O/EHq0n59kIEnt.LlZn48CmDUqtr5mvi
 by: Retro Guy - Mon, 18 Jan 2021 11:42 UTC

Retro Guy wrote:

> I'm currently upgrading rslight.i2p (novabbs.org) to use the new articles database. It's a test and I really hope it doesn't have too many issues. Yes, I have a full backup.

> Testing has shown this to be pretty fast, but I want to run it on a real site to see if that's accurate as the test site is only rocksolid.*

So far this seems fine. I also zipped the spool on one site and extracted it to another and ran it. All works fine. This only copies the spool, not the config settings, look of the site etc. Just wanted to make sure the dbs are portable and apparently they are :) Yes, I'm still learning about databases.

The sites seem quite a bit faster where the db should show improvement. Mostly in overboard and 'latest' for a group. Also, no concern about inodes.

I'll continue to run two sites on a database and two on tradspool, just to make sure the code doesn't break on one as development continues.

Retro Guy
--
Posted on novaBBS
www.novabbs.com

Re: sql in rslight

<n.1289.52hzf@anon.com>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!rocksolid2!def5!.POSTED.bogusentry!not-for-mail
From: poster@anon.com (Anonymous)
Newsgroups: rocksolid.nodes
Subject: Re: sql in rslight
Date: Tue, 19 Jan 2021 01:20:45 -0800
Organization: def5
Message-ID: <n.1289.52hzf@anon.com>
References: <20201211185438.0facd5ad@desktop14.dt>
Content-Type: text/plain; charset=UTF-8
Injection-Info: def5.org; posting-host="bogusentry:192.168.1.189";
logging-data="31429"; mail-complaints-to="usenet@def5.org"
 by: Anonymous - Tue, 19 Jan 2021 09:20 UTC

>>8b9571283cccfde6e7
>I also zipped the spool on one site and extracted it to another and ran it. All works fine.

So on a new installation you can just unzip the sqlite files, and start from the status when you created the archives ?

--
Posted on def2

Re: sql in rslight

<20210119024657.6674f160@desktop14.dt>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!.POSTED!not-for-mail
From: retro_guy@novabbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sql in rslight
Date: Tue, 19 Jan 2021 02:46:57 -0700
Organization: novaBBS
Message-ID: <20210119024657.6674f160@desktop14.dt>
References: <20201211185438.0facd5ad@desktop14.dt>
<n.1289.52hzf@anon.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: i2pn2.org; posting-account="retrobbs1";
logging-data="27651"; mail-complaints-to="usenet@i2pn2.org"
X-Newsreader: Claws Mail 3.11.1 (GTK+ 2.24.25; x86_64-pc-linux-gnu)
 by: Retro Guy - Tue, 19 Jan 2021 09:46 UTC

On Tue, 19 Jan 2021 01:20:45 -0800
Anonymous <poster@anon.com> wrote:

> >>8b9571283cccfde6e7
> >I also zipped the spool on one site and extracted it to another and
> >ran it. All works fine.
>
> So on a new installation you can just unzip the sqlite files, and
> start from the status when you created the archives ?
>

Exactly. novabbs.com is now a clone of rslight.i2p (the spool is a
clone). I just zipped /var/spool/rslight and extracted it on
novabbs.com and it works fine.

Re: sqllite, huh ?

<a465b0fcaa94544425eacf6b2ea13e06$1@www.novabbs.com>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!.POSTED!not-for-mail
From: retro.guy@rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Tue, 19 Jan 2021 10:53:29 +0000
Organization: novaBBS
Message-ID: <a465b0fcaa94544425eacf6b2ea13e06$1@www.novabbs.com>
References: <ab8c5fa4037a3595e0f30ad1a526fc54$1@news.novabbs.org> <5c54a78607c9145ca4bf48d69ffc72a9@def4> <19fddbdc3def82d96a129c7b7eab383a$1@news.novabbs.org> <94952dda02df0c1d6ebd7d1c4b852e46$1@www.novabbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Info: i2pn2.org; posting-account="retrobbs1";
logging-data="5380"; mail-complaints-to="usenet@i2pn2.org"
User-Agent: Rocksolid Light (news.novabbs.com/getrslight)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs
X-Rslight-Site: $2y$10$A6.1DH.B/I3zPAmrhhrpJe653e6imIdatjCAsXwyx2DaN4TzO6R.q
 by: Retro Guy - Tue, 19 Jan 2021 10:53 UTC

Retro Guy wrote:

> I'm currently upgrading rslight.i2p (novabbs.org) to use the new articles database. It's a test and I really hope it doesn't have too many issues. Yes, I have a full backup.

> This may remove the inodes problem with a lot of articles. For example, rslight.i2p has about 910,000 articles currently in 289 groups. Moving to a database means not having 910,000 individual files, only 289 individual files. There is one .db3 per group.

I just checked on one server with 932,000 articles. Moving from tradspool to database has freed 913,000 inodes.

> Testing has shown this to be pretty fast, but I want to run it on a real site to see if that's accurate as the test site is only rocksolid.*

It seems to be quite a bit faster. The dbs are using indices that are created when the db is created. No user(admin) intervention, just run the site and everything is created and configured for you.

> One reason I want it to work is that I have about 12 hours into it and I'd hate to think it was a waste of time, lol :)

More than 12 hours now as I'm making sure expire and nocem are working properly with the new setup, and making a few changes in the newsportal code to clean itself up when things change.

Once I'm comfortable with it, I'll merge my dev branch and push to github. I'm going to run it a little longer first.

Retro Guy
--
Posted on novaBBS
www.novabbs.com

Re: sqllite, huh ?

<c259587ebd619fbf9dae6729222d65cc$1@news.novabbs.org>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!.POSTED!not-for-mail
From: retro.guy@rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Fri, 22 Jan 2021 01:41:59 +0000
Organization: Rocksolid Light
Message-ID: <c259587ebd619fbf9dae6729222d65cc$1@news.novabbs.org>
References: <ab8c5fa4037a3595e0f30ad1a526fc54$1@news.novabbs.org> <5c54a78607c9145ca4bf48d69ffc72a9@def4> <19fddbdc3def82d96a129c7b7eab383a$1@news.novabbs.org> <94952dda02df0c1d6ebd7d1c4b852e46$1@www.novabbs.com> <a465b0fcaa94544425eacf6b2ea13e06$1@www.novabbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Info: i2pn2.org; posting-account="retrobbs1";
logging-data="5457"; mail-complaints-to="usenet@i2pn2.org"
User-Agent: Rocksolid Light (news.novabbs.com/getrslight)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs.org
X-Rslight-Site: $2y$10$iCLMudlykrFRKEqbTH4i5u8nUXN5OX2FR5oY0XQKC222.21aPtMAu
 by: Retro Guy - Fri, 22 Jan 2021 01:41 UTC

Retro Guy wrote:

> More than 12 hours now as I'm making sure expire and nocem are working properly with the new setup, and making a few changes in the newsportal code to clean itself up when things change.

> Once I'm comfortable with it, I'll merge my dev branch and push to github. I'm going to run it a little longer first.

This seems to be working well. I have one test site using tradspool and one using sqlite and both are working properly. rslight.i2p and www.novabbs.com both now use a database, while retrobbs and i2pn2 both use tradspool.

I've pushed to github and fuwafuwa the changes.
--
Posted on Rocksolid Light
news.novabbs.org

Re: sqllite, huh ?

<48cb47fb7ada4c0de345f293ed0724e5$1@www.novabbs.com>

 copy mid   Newsgroups: rocksolid.nodes
Path: i2pn2.org!.POSTED!not-for-mail
From: retro.guy@rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes
Subject: Re: sqllite, huh ?
Date: Sun, 24 Jan 2021 12:04:51 +0000
Organization: novaBBS
Message-ID: <48cb47fb7ada4c0de345f293ed0724e5$1@www.novabbs.com>
References: <ab8c5fa4037a3595e0f30ad1a526fc54$1@news.novabbs.org> <5c54a78607c9145ca4bf48d69ffc72a9@def4> <19fddbdc3def82d96a129c7b7eab383a$1@news.novabbs.org> <94952dda02df0c1d6ebd7d1c4b852e46$1@www.novabbs.com> <a465b0fcaa94544425eacf6b2ea13e06$1@www.novabbs.com> <c259587ebd619fbf9dae6729222d65cc$1@news.novabbs.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Info: i2pn2.org; posting-account="retrobbs1";
logging-data="11193"; mail-complaints-to="usenet@i2pn2.org"
User-Agent: Rocksolid Light (news.novabbs.com/getrslight)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs
X-Rslight-Site: $2y$10$2ZHY8QnmGy16HowTZMmdLOo158NbXZ/6dTZfb5L9qRmRRGBTwwwOG
 by: Retro Guy - Sun, 24 Jan 2021 12:04 UTC

Retro Guy wrote:

> This seems to be working well. I have one test site using tradspool and one using sqlite and both are working properly. rslight.i2p and www.novabbs.com both now use a database, while retrobbs and i2pn2 both use tradspool.

I find it's easy to count articles now (since using a db).

novabbs.com currently has 1,192,102 articles and is using 7.4G space. Total inodes used for the system is 94K, which is much less than with tradspool.

I originally had difficulty serving groups of 50K+ messages, but that seems no problem at all now. I'm a bit curious how much it can handle in one group, but I don't think too many are going to exceed that number.

Retro Guy
--
Posted on novaBBS
www.novabbs.com

1
server_pubkey.txt

rocksolid light 0.9.7
clearnet tor