Flourish PHP Unframework
This is an archived copy of the forum for reference purposes

Support for NoSQL databases

posted by audvare 8 years ago

Possibility in the future even with Flourish SQL as the basis to create tables (at minimum)?

I'm not exactly sure what you are looking for here. There have been a number of posts on the forum about NoSQL support, and I always ask the same question. What kind of functionality are you looking for on top of your NoSQL database?

I have heard people talk about creating an ORM for NoSQL databases, however there are NoSQL solutions that are just objects, so there isn't really the need to map relations to objects, you just need to pick the right NoSQL database for the job. In terms of creating a consistent querying interface for the various databases, most of them are significantly different in terms of how they store data and the language they user for querying.

The reason there can be a useful ORM for PostgreSQL, MySQL, SQLite, Oracle, DB2 and SQL Server is because they all build off of SQL. Granted there are slight differences, but they are close enough that it is possible to build a solution that can target all of them and achieve the majority of functionality people are looking for.

There is ticket #568 in which Matt laid out some ideas for an object document mapper, but even then we are talking about targeting only CouchDB and MongoDB. Also, at a certain point I think you need to step back and make sure that using a NoSQL solution is really better than using a good SQL solution, such as PostgreSQL. If you spend a lot of time trying to make your NoSQL database act like you SQL database, that is probably a good indicator.

posted by wbond 8 years ago

After this week fixing all my CREATE TABLE SQL files to be Flourish SQL and in EXACT order (meaning INT DEFAULT 0 NOT NULL not INT NOT NULL DEFAULT 0 (latter seems to translate incorrectly)), I used translatedExecute() with all of the SQL creating tables with a SQLite database instead of MySQL. Then finally (with a hack for lighttpd's bad url rewriting where it lacks checking for file existence), I got my site to run with lighttpd + PHP 5.2 (luckily I backported all my code to not use namespaces; main development machine is PHP 5.3) + SQLite 3.x on my iPhone (jailbroken of course). It's much slower than my 3.4 GHz machine but it does work quite nicely and the best thing is that I know whatever I make for MySQL now works on SQLite without question (generally speaking). Besides a few DELETE statements and a few SELECTs with JOIN, most of my code uses fActiveRecord/fRecordSet to do CRUD operations.

So the reason I mention NoSQL (and really any other type of database) is because I like this level of flexibility (and of course it's neat to run a whole site on an iPhone locally).

Lastly, does fRecordSet cause growth in memory usage with time? It seems that doing large limits and page offsets with tables that have a very large amount of rows (1000 as limit, 100000+ records), multiple tables, etc etc causes this sort of issue and then PHP just quits because it's out of memory. I have my CLI limit set to 1 GB and I can create 100,000 users in my basic users table with random data (fCryptography::random() and fCryptography::randomString()) before PHP kills itself. Surprisingly, the php process uses the CPU and memory, not MySQL. I can show some code I'm dealing with that is purely for testing but does show a memory increase with time in many different cases.

posted by audvare 8 years ago

Right, so it sounds like you want something simpler to deploy/manipulate for your situation, but not necessarily are you looking for Flourish to add anything on top of something like the MongoDB extension. In other words, you are frustrated with your experience of working with SQLite and MySQL, right?

In terms of fRecordSet performance/memory usage, you are not seeing anything unexpected. Taking all of the different databases and making PHP extensions return consistent data and providing hooks for ORM plugins does take its toll on performance.

fActiveRecord uses a few different arrays for each record to allow for advanced functionality and to provide caching. Unfortunately PHP uses a lot of memory for storing variables in array (68 bytes per entry minimum, plus the size of the value), so memory use can jump very quickly when loading lots of records. I've tried creating a 3000x3000 multi-dimensional array before and caused PHP to exhaust 128MB of ram. So loading 100,000 records into memory is definitely going to use a whole ton of ram.

If you are just using one record at a time and then discarding it, you may also be running into the "feature" of the identity map in fActiveRecord. This stores references to all records in a central place to ensure that two different copies of a record are not loaded into different scopes. In the big code drop I am doing soon there is going to be a way to clear the identity map, which should help with situations likes yours.

I don't know what you code looks like, or what problem you are trying to solve, but loading 1000+ active records into memory sounds like a lot. When I need to load a lot of records, I usually fall back to raw database queries as opposed to relying on all of the convenience of fActiveRecord. It often requires a little extra code, but it ends up being much more efficient.

This is probably all over the place, but I hope it helps some.

posted by wbond 8 years ago

One example:

// Create 100,000 new users with mostly unique identification information (preset schemes like age,gender which are in a different table and have an ID to the possibilities)
$time = time();
$hasher = new PasswordHasher();
$hash = $hasher->HashPassword('abc123');
for ($i = 0; $i < 100000; $i++) {
  try {
    // Pick a random identity ID out of 1000 records with a random offset
    $page = fCryptography::random(1, 10);
    $identities = fRecordSet::build('User_Identity', array(), array(), 1000, $page);
    $id = $identities[fCryptography::random(1, $identities->count())]->getIdentityId();

    $user = new User;
  catch (fValidationException $e) {}

I know it seems crazy and it's somewhat unrealistic but I guess I was sort of hoping that with the end of the loop, $user and $identities no longer exist and and the memory gets freed. Apparently setting to null at end can help but not a guarantee, and unset() doesn't usually do anything immediately in terms of memory. PHP's garbage collector just doesn't seem to be doing much to help here.

posted by audvare 8 years ago


Here is an ORM that works with Sqlite https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

posted by asavasamuel 6 years ago

I'm perfectly happy with the whole experience so far with both MySQL and SQLite. SQLite is the obvious choice for a 'web server on the iPhone' and MySQL for actual development. Maybe Postgres in production? I have never tried it but hear good things.

posted by audvare 8 years ago