Probably worth making the filename of the database configurable.

Great point. We can include a FileName field in the config.DatabaseCfg struct, and use something like writefreely.db as the default in the config.New() func.

Aye, that sounds perfect.

I started working on a proof of concept for this yesterday morning. In the process, I noticed a couple things.

1. Instantiating the database connection as a property of the app makes it difficult to atomically test database functions
2. There are no tests yet

Normally I'm not one for test-driven development. However, in this case, writing unit tests will help me make sure that SQLite integration works just as well as MySQL integration.

@matt thoughts on adding unit tests for database functions?

Nice! I'm all for adding unit tests for database funcs -- especially with different databases, I agree we should make sure they work consistently.

What's a better way to structure things so the connection can still be passed around in the app and testing is easy?

Actually, I retract my previous statement. I was thinking in terms of integration tests, not unit tests - the current pattern is fine.

I'm going to spend some time this week getting the SQLite integration functioning, and in the process, I'll work out a test process that we can use for CI.

Ok cool 👍 let me know if you need anything along the way. Excited to get this in!

The working branch is here: https://github.com/writeas/writefreely/tree/sqlite-support

This is not yet ready for a PR. I have to do testing and documentation yet at a minimum.

So as I've started testing this, I've discovered there is a vast chasm of differences between normal SQL and SQLite SQL. I'm working on the logic to handle both.

As an example, SQLite doesn't support the SQL function NOW(). It also only has five data types - NULL, INTEGER, REAL, TEXT, and BLOB. Yes, that means boolean values are stored as full INTEGERs.

Ah, gotcha. I thought there might be some differences when I was reading through this fork with sqlite support.

I'm thinking about what the setup process will look like with this.. I assume we'll need a different schema.sql for SQLite then? Maybe the --init-db option can do a search and replace on the queries?

I have a working variation of schema.sql (called, appropriately enough, sqlite.sql) for SQLite.

My current task is to go through every SQL statement in the code to create alternatives for SQLite. For example, several queries rely on the expired >= NOW() clause, which won't work in SQLite.

@matt OK, with the latest commit on the sqlite-support branch, SQLite support is working.

I still need to do more extensive testing before I feel comfortable merging it in, though. Right now I've only tested creating the initial user, viewing posts, creating a new post, and moving a post from draft to published (and viewing it after that). This was all done in single-user mode, and not in multi-user mode.

Awesome!! I'll give it a thorough testing this weekend in various configurations and let you know how it looks.

Some feedback from my initial multi-user testing: so far almost everything looks solid, this is really good work! Only issue so far is something weird going on with scheduled posts. They correctly show as "scheduled" and don't show up for non-auth'd users. But once the published time has passed, the "scheduled" badge correctly drops for auth'd users, but the post doesn't show up for non-auth'd users like it should.

Looking into that now and making a few minor adjustments.

Oh, and I believe that adding PostgreSQL support will be much easier than SQLite. It should be able to use schema.sql and all of the MySQL SQL commands unmodified. The only bit that it'll require is a new connection block in the initial setup.

Awesome. Plenty of people asking about that now, so that'll be good. If you want to take the charge on that one, please feel free!

As for SQLite support, do you have any experience with cross-compiling applications that use a C library like this? Reading through the README and various issues on the go-sqlite3 repo I'm a bit lost, and seems like it might be a pain.

After this has been merged in, I'll work on the PostgreSQL support.

I haven't done any cross-compilation work with C libraries, no. Running into issues?

Sounds great.

I haven't tried cross-compiling yet, I was just looking at what's involved and saw it wasn't straightforward. But I'll let you know when I start trying it out.

Okay, did some more refactoring and caught some other errors / differences between MySQL and SQLite. Also added the SQLite option in the config setup.

Updating the documentation and trying out cross-compiling now. But I think the code is in a pretty good spot, so I just opened a pull request for all these changes. If all is good, this'll be included in the v0.6 release.