What are /r/sqlite's favorite Products & Services?

From 3.5 billion Reddit comments

SQLite full-text search doesn't support the % wildcard syntax or the like operator - those work against normal TEXT columns.

The syntax supported by FTS is documented here: https://www.sqlite.org/fts5.html#full_text_query_syntax

I don't think SQLite FTS has a mechanism for matching terms that appear in the middle of the word - the index is designed to support only prefix matches.

I don't have a good solution for speeding up match-in-the-middle queries using SQLite yet. In PostgreSQL I've solved this problem before using trigram indexes, which are described about half way down this article: https://about.gitlab.com/blog/2016/03/18/fast-search-using-postgresql-trigram-indexes/

I'd love to find a way to do something like that in SQLite.

where is the `customers` table? Also, some sample data should be supplied rather than having to make some up. I guess we could make some assumptions that, at the very least, the customer table would look something like this:

CREATE TABLE customers (

id INTEGER NOT NULL,

name VARCHAR,

PRIMARY KEY (id)

)

However, more complete data would be useful.

As far as the tool, then you can use the built-in sqlite3 tool, download the DB Browser for SQLite, or one of the many other multi-db tools. My preference is DBeaver

https://www.amazon.com/SQL-Dummies-Allen-G-Taylor/dp/1118607961

No joke. I went from completely lost to being able to building databases for health care clinics mostly from reading this book one weekend.

Are you particular about SQLite? If you don't need to maintain your data in an RDBMS, there are lots of options.

I can think of two right of the top of my head - Firebase or AirTable

I don’t remember there ever being a EFcore project template in visual studio, but it’s easy enough to get started without.

First, I’d recommend this package instead of the system one:

https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.Sqlite

Then, go for a code first approach, meaning you’ll add and code entity classes to model tables and columns as c# properties.

Edit: pasted the wrong link to the package

SQLite doesn't have a "LONG_TEXT" datatype, you want TEXT

The default max length for a string is one billion, aka a gigabyte.

I'm going to go out on a limb and guess that SQLite's storage limits isn't your issue?

You'll need to get the amalgamation sources. It won't build a library for your system. Instead, it will be embedded directly in the sqlite3 command line program so there's no chance of linking the wrong one. The on-disk format is compatible with the sqlite3 installed by Raspbian, so there won't be any conflict that way either.

wget http://www.sqlite.org/2014/sqlite-amalgamation-3080403.zip unzip sqlite-amalgamation-3080403.zip cd sqlite-amalgamation-3080403/ cc -O3 -DSQLITE_THREADSAFE=0 sqlite3.c shell.c -ldl -o sqlite3

Then copy sqlite3 to somewhere in your PATH, or just run it directly.

Yes, I installed Raspbian. Didn't know it came with sqlite preinstalled.

Just to verify, here was the steps I took to install sqlite from scratch

wget http://www.sqlite.org/2014/sqlite-autoconf-3080403.tar.gz tar -xzvf sqlite-autoconf-3080403.tar.gz cd sqlite-autoconf-3080403/ ./configure make # This part takes a long time, so be patient! sudo make install cd ~ whereis sqlite # /usr/local/bin/sqlite3 sqlite3 # That error

So if I recompile that part of sqlite3, where should I do that? Would I need to redo ./configure && make && sudo make install or can I just do it now since I have everything installed? Also, should I do it at /usr/bin/(I don't know where) or back in the source folder ~/sqlite-autoconf-3080403/(somewhere)?

I guess we’re back to the simplicity topic again.

The main reasons that I’ve been investigating and exploring this topic is that I’m currently using Notion for my task management and I’m sick and tired of it’s performance on very very simple database queries not to mention no real offline mode. So again keeping things simple is key to me especially for maintenance. The main thing that I do love about Notion is it’s ear of use and how clean, simple and great the UI is.

Thanks for the links I’ll have a look :). I also came across baserow.io which looks cool. I need to dig into it a bit bcz it seems that you can’t bring your own database which I really like the idea of.

pgadmin and phpmyadmin are both web-based; if that's what you're going for, this thing is the only one I'm aware of: https://www.phpliteadmin.org/demo/

Personally I paid for datagrip, and have been very happy with it; I use it for all my database needs, not just sqlite.

Try DBeaver https://dbeaver.io very good cross platform, multi DB, open source, free IDE.

SQLite’s Propriety Encryption (by Dr. Hipp) https://www.sqlite.org/see/doc/release/www/index.wiki

There are some other open source SQLite encryption libraries in Github.

Try dbeaver instead. Can do cell based filtering, is cross platform, fast, can work with SQLite or most any other database, free, well maintained, and polished.

It is a hidden gem.

https://dbeaver.io