ownCloud Planet

Welcome to ownCloud News, our contributor blog roll. ownCloud contributors should ask to get added!

Opinions are the responsibility of those who express them. See our privacy policy.

If you'd like to stay up to date with ownCloud news you could also subscribe to our newsletter!

Evert Pot
Writing SQL that works on PostgreSQL, MySQL and SQLite
April 28, 2016

I am one of those crazy people who attempts to write SQL that works on SQlite, MySQL and PostgreSQL. First I should explain why:

This is all for my project sabre/dav. sabre/dav is a server for CalDAV, CardDAV and WebDAV. One of the big design goals is that it this project has to be a library first, and should be easily integratable into existing applications.

To do this effectively, it’s important that it’s largely agnostic to the host platform, and one of the best ways (in my opinion) to achieve that is to have as little dependencies as possible. Adding dependencies such as Doctrine is a great idea for applications or more opinionated frameworks, but for sabre/dav lightweight is key, and I need people to be able to understand the extension points fairly easily, without requiring them to familiarize them with the details of the dependency graph.

So while you are completely free to choose to add Doctrine or Propel yourself, the core adapters (wich function both as a default implementation and as samples for writing your own), all only depend on an instance of PDO.

The nice thing is that ORMs such as Doctrine and Propel, you can get access to the underlying PDO connection object and pass that, thus reusing your existing configuration.

For the longest time we only supported SQLite and MySQL, but I’m now working on adding PostgreSQL support. So I figured, I might as well write down my notes.

But how feasable is it to write SQL that works everywhere?

Well, it turns out that this is actually not super easy. There is such as thing as Standard SQL, but all of these databases have many of their own extensions and deviations.

The most important thing is that this will likely only work well for you if you have a very simple schema and simple queries.

Well, this blog post is not intended as a full guide, I’m just listing the particular things I’ve ran into. If you have your own, you can edit this blog post on github, or leave a comment.

My approach

  • I try to keep my queries as simple as possible.
  • If I can rewrite a query to work on every database, that query will have the preference.
  • I avoid stored procedures, triggers, functions, views. I’m really just dealing with tables and indexes.
  • Even if that means that it’s not the most optimal query. So I’m ok with sarcrificing some performance, if that means my queries can stay generic, within reason.
  • If there’s no possible way to do things in a generic way, I fall back on something like this:
<?php

if ($pdo->getAttribute(PDO::ATTR_DRIVER_NAME) === 'pgsql') {

    $query = "...";

} else {

    $query = "...';

}

$stmt = $pdo->prepare($query);


?>

DDL

First there is the “Data Definition Language” and “Data Manipulation Language” the former is used for queries starting with CREATE, ALTER, DROP, etc, and the latter SELECT, UPDATE, DELETE, INSERT.

There really is no sane way to generalize your CREATE TABLE queries, as the types and syntax are vastly different.

So for those we have a set of .sql files for every server.

Quoting

In MySQL and SQlite you can use either quotes ' or double quotes " to wrap a string.

In PostgreSQL, you always have to use single quotes '.

In MySQL and SQLite you use backticks for identifiers. PostgreSQL uses single quotes. SQlite can also use single quotes here if the result is unambigious, but I would strongly suggest to avoid that.

This means that this MySQL query:

SELECT * FROM `foo` WHERE `a` = "b"

is equivalent to this PostgreSQL query:

SELECT * FROM "foo" WHERE "a" = 'b'

Luckily you can often just write this query, which works for all databases:

SELECT * FROM foo WHERE a = 'b'

But keep in mind that when you create your tables, using double quotes will cause PostgreSQL to retain the upper/lower case characters. If you do not use quotes, it will normalize everything to lower case.

For compatibility I would therefore suggest to make sure that all your table and column names are in lower case.

REPLACE INTO

The REPLACE INTO is a useful extension that is supported by both SQLite and MySQL. The syntax is identical INSERT INTO, except that if it runs into a key conflict, it will overwrite the existing record instead of inserting a new one.

So REPLACE INTO basically either updates or inserts a new record.

This works on both SQLite and MySQL, but not PostgreSQL. Since version 9.5 PostgreSQL gained a new feature that allows you to achieve the same effect.

This statement from MySQL or SQLite:

REPLACE INTO blog (uuid, title) VALUES (:uuid, :title)

then might become something like this in PostgreSQL:

INSERT INTO blog (uuid, title) VALUES (:uuid, :title)
ON CONFLICT (uuid) DO UPDATE SET title = :title

So the major difference here is with PostgreSQL we specifically have to tell it which key conflict we’re handling (uuid) and what to do in that case (UPDATE).

In addition to REPLACE INTO, MySQL also has this syntax to do the same thing:

INSERT INTO blog (uuid, title) VALUES (:uuid, :title)
ON DUPLICATE KEY UPDATE title = :title

But as far as I know SQLite does not have a direct equivalent.

BLOB

SQLite and MySQL have a BLOB type. This type is used for storing data as-is. Whatever (binary) string you store, you will retrieve again and no conversion is attempted for different character sets.

PostgreSQL has two types that have a similar purpose: Large Objects and the bytea type.

The best way to describe large objects, is that they are stored ‘separate’ from the table, and instead of inserting the object itself, you store a reference to the object (in the form of an id).

bytea is more similar to BLOB, so I opted to use that. But there are some differences.

First, if you do a select such as this:

<?php

$stmt = $pdo->prepare('SELECT myblob FROM binaries WHERE id = :id');
$stmt->execute(['id' => $id]);

echo $stmt->fetchColumn();

?>

On MySQL and Sqlite this will just work. The myblob field is represented as a string.

On PostgreSQL, byta is represented as a PHP stream. So you might have to rewrite that last statement as:

<?php

echo stream_get_contents($stmt->fetchColumn());

?>

Or:

<?php

stream_copy_to_stream($stmt->fetchColumn(), STDOUT);

?>

Luckily in sabre/dav we pretty much support streams where we also support strings, so we were already agnositic to this, but some unittests had to be adjusted.

Inserting bytea is also a bit different. I’m not a fan of of using PDOStatement::bindValue and PDOStatement::bindParam, instead I prefer to just send all my bound parameters at once using execute:

<?php

$stmt = $pdo->prepare('INSERT INTO binaries (myblob) (:myblob)');
$stmt->execute([
    'myblob' => $blob
]);

?>

While that works for PostgreSQL for some strings, it will throw errors when you give it data that’s invalid in the current character set. It’s also dangerous, as PostgreSQL might try to transcode the data into a different character set.

If you truly need to store binary data (like I do) you must do this:

<?php

$stmt = $pdo->prepare('INSERT INTO binaries (myblob) (:myblob)');
$stmt->bindParam('myblob', $blob, PDO::PARAM_LOB);
$stmt->execute();

?>

Luckily this also just works in SQlite and MySQL.

String concatenation

Standard SQL has a string concatenation operator. It works like this:

SELECT 'foo' || 'bar'
// Output: foobar

This works in PostgreSQL and Sqlite. MySQL has a function for this:

SELECT CONCAT('foo', 'bar')

PostgreSQL also has this function, but SQLite does not. You can enable Standard SQL concatenation in MySQL by enabling it:

SET SESSION sql_mode = 'PIPES_AS_CONCAT'

I’m not sure why this isn’t the default.

Last insert ID

The PDO object has a lastInsertId() function. For SQLite and MySQL you can just call it as such:

<?php

$id = $pdo->lastInsertId();

?>

However, PostgreSQL requires an explicit sequence identifier. By default this follows the format tablename_idfield_seq, so we might specifiy as this:

<?php

$id = $pdo->lastInsertId('articles_id_seq');

?>

Luckily the parameter gets ignored by SQLite and MySQL, so we can just specify it all the time.

Type casting

If you have an INT field (or similar) and you access it in this way:

<?php

$result = $pdo->query('SELECT id FROM articles');
$id = $result->fetchColumn();

?>

With PostgreSQL $id will actually have the type php type integer. If you use MySQL or SQlite, everything gets cast to a php string, which is unfortunate.

The sane thing to do is to cast everything to int after the fact, so you can correctly do PHP 7 strict typing with these in the future.

Testing

I unittest my database code. Yep, you read that right! I’m one of those people. It’s been tremendously useful.

Since adding PostgreSQL I was able to come up with a nice structure. Every unittest that does something with PDO now generally looks like this:

<?php

abstract PDOTest extends \PHPUnit_Framework_TestCase {

    abstract function getPDO();

    /** all the unittests go here **/

}

?>

Then I create one subclass for PostgreSQL, Sqlite and MySQL that each only implement the getPDO() function.

This way all my tests are repeated for each driver.

I’ve also rigged up Travis CI to have a MySQL and a PostgreSQL database server running, so everything automatically gets checked every time.

If a developer is testing locally, we detect if a database server is running, and automatically just skip the tests if this was not the case. In most cases this means only the Sqlite tests get hit, which is fine.

Conclusions

  1. Created a monster.
  2. PostgreSQL is by far the sanest database, and I would recommend everyone to move from MySQL towards it.

read more



ownCloud
Frank Leaving ownCloud, Inc.
April 27, 2016

ownCloud Logo and slogan
ownCloud, Inc. just shared that ownCloud community founder Frank has left the company to pursue other opportunities. In the last 6 years, ownCloud has grown from a project of a handful of people to a community of thousands of contributors and millions of users. Frank was a huge, driving force behind this. By co-founding ownCloud, Inc. he provided an opportunity for ownCloud to become a safe home for sensitive enterprise data as well as the photos, music and documents that home users like myself enjoy. ownCloud is relied upon in businesses and homes across the globe.

It is never great to see a colleague leave and I’ve known Frank for over a decade so I will miss him. The good news is that he has helped build a community with many exciting things going on. I’m proud to be involved with projects like Western Digital and the ownCloud Pi, real-time communication with Spreed or that with Collabora on LibreOffice Online integration. These projects (and others like it) will continue to move forward. Here’s to more greatness. And now it’s time for me to go bed.

See you tomorrow in ownCloud!

read more



Frank Karlitschek
big changes: I am leaving ownCloud, Inc. today
April 27, 2016

Recently I had to make one of my hardest decisions so far. Because this has an impact beyond myself I want to share this here: I am leaving ownCloud, Inc. today. But, the journey of ownCloud and Frank is not over!

6 years ago until now
I founded the ownCloud project a little over 6 years ago with the goal to enable home users, companies, universities and big enterprises to host their own cloud services and files. In a world with growing threats around security, surveillance and espionage, this idea is becoming more important every day.

4.5 years ago I co-founded ownCloud Inc, a company that supports enterprise use of ownCloud and drives the development of ownCloud forward.

Accomplishments
The product development worked our very well. With ownCloud 9.0 we released a huge milestone and an amazing product developed in close collaboration between the ownCloud company and the ownCloud community.

The community has grown tremendously, with contributions by almost 1000 people over the last 6 years, over 80 every single month. Nothing is perfect, the company could have done a better job recognizing the achievements of the community. It sometimes has a tendency to control the work too closely and discus things internally. But overall, the balance was not too bad.

I’m extremely impressed by the immense interest we see from our user community and company customers and prospects. We have built something people like and want to use and buy.

But there were also other experiences. I won’t go into the details there but these have put us in the very difficult situation.
The future
I thought a lot about this situation. Without sharing too much, there are some moral questions popping up for me. Who owns the community? Who owns ownCloud itself? And what matters more, short term money or long term responsibility and growth? Is ownCloud just another company or do we also have to answer to the hundreds of volunteers who contribute and make it what it is today?
These questions brought me to the very tough decisions: I have decided to leave my own company today. Yes, I handed in my resignation and will no longer work for ownCloud, Inc.

Let me make this very clear: this is NOT bad news for ownCloud. Change, yes, bad, no. And secondly, I am the ownCloud project lead until the community says otherwise. My email changes from owncloud to frank at karlitschek.de – that is all.

You wonder what this means for Frank and ownCloud? Besides what I’ve said, I can’t answer that today. But I can say that, of course, the journey is not over yet. There is tremendous potential in ownCloud and it is an open source product protected by the AGPL license.

If you have questions about this and what it means for you, as community member or employee, please don’t hesitate to contact me.

Stay tuned, as more news is coming!

read more



Efstathios Iosifidis
What happened @FOSSCOMM2016, April 16-17 2016 @ University of Pireaus
April 22, 2016


As I previously wrote, I went to Athens to attend the FOSSCOMM annual conference.

I applied to present something related to openSUSE (I was at the booth) and also to ownCloud.

openSUSE
Kick off the conference, my presentation was about my favourite version, openSUSE Tumbleweed. I used the structure of Richard's article.
The audience was aproximately 20 people. Actually it was the first presentation of the day, right after keynote (at the other room). The good thing was that more came at the booth and they were asking about Tumbleweed and Leap.
We had limited amount of swag to give away.

Here you can find the presentation file. Feel free to use it.

Picture:

ownCloud
Presentation was pretty interesting. I had the main room and the audience was about 50 people. I showed what is cloud, why we use it, what is good and what is bad cloud service and what's cool with ownCloud. I showed what's new on version 9.0. Right after the presentation I was asked about encryption (files and mails). Is it on server side or on sender side?
I had some leaflets and stickers with me. I left them at the registration desk and some at other booths, so if anyone was insterested on ownCloud, he/she could find me at the openSUSE booth.

Here is the presentation file (it's the same that we all use + conference promo).

Some pictures:



For more pictures you can check:
1. openSUSE album
2. ELLAK report
3. FOSSCOMM facebook event
4. FOSSCOMM instagram

read more



ownCloud
ownCloud Android Client 2.0 comes with upload view, federated sharing and more
April 21, 2016

Andriod-2.0-08_UPLOADS_VIEW__device-2016-04-18-115521
The new ownCloud Android app has been made available in the Google Play store, delivering significant enhancements and new features. The UI has been refined, users can engage in Federated sharing and probably most importantly the team finally integrated the the new uploading code with the upload dialog. This means uploads can be tracked, canceled and restarted automatically! Read on to learn more.

Upload view

Started by LukeOwncloud, the major new feature in the 2.0 release is the ability to manage uploads in the Transfers View. You see current files uploading or in the queue as well as files which might have failed to upload and those finished.

This new view allows you not only to keep an eye on what is happening but when uploads failed you can restart them. ownCloud will automatically handle this for you when possible, restarting uploads which failed for reasons it can recover from (like a time-out or a network error). On the other hand, if uploads failed due to wrong credentials you can get this fixed in the upload view.

Upload handling has been improved in other ways, too. Uploading will now be canceled if “only upload on wifi” is enabled and the wifi network disappears and the upload dialog now shows existing files in your ownCloud.

The new FAB
The new FAB

UI work and Federation

There have been several smaller and larger improvements to the UI. A big one is the introduction of the ‘FAB’, a Floating Action Button, better known as that round button bottom-right in my app. It’s big, blue plus, allowing you to upload new files, create new folders or add content from other apps to your ownCloud.

The share dialog was also reworked. It is now possible to set and edit permissions on shared files and folders and a shared-by-link folder can now be made editable.

A bigger new feature is the support for Federated Cloud Sharing in the Android client. It is now possible to directly share links with users on other ownCloud servers. Actually, as you might be aware, this extends beyond ownCloud as the Federated Cloud Sharing API is also supported by Pydio!

Thank you!

We want to give a big shout-out to the many contributors to this release! This was real teamwork. In alphabetical order, these people contributed to this release:

read more