[ddj] GUI database tools for newsrooms

Thu May 28 08:24:09 UTC 2015

Hi Sam,

if you want to stay in the SQL world, you can use a relational db on the
back-end (postgresql, but also mysql),
queried by a simple server-side application (for example using php
language).

The key part of this simple stack is on the front-end: you can expose to
your users a graphical query builder
to write the WHERE clause of an arbitrary sql statement simply pointing and
clicking: http://mistic100.github.io/jQuery-QueryBuilder/.

Users can build their own query (only WHERE clause, server-side validation
is required here to avoid sql injection),
send it to the server and read (and even download) the response (a table).

I think the query builder doesn't support joining, but you can prepare
virtual table in your db with presetted join and expose
these ones to your users.

Best :)

2015-05-26 9:13 GMT+02:00 Alex Salkever <alex at silk.co>:

> I second Elasticsearch. It's amazing.
>
> On Mon, May 25, 2015 at 9:27 PM, Justin Seitz <justin at automatingosint.com>
> wrote:
>
>>  One thing that I have used previously is Elasticsearch and Kibana. It
>> is a NoSQL database as well, but it allows you to hook up a "river". Rivers
>> allow you to attach Elasticsearch to a SQL database so that you can have,
>> for example, a Postgres database that you can query traditional SQL against
>> but you can have a friendly full text search in the form of Elasticsearch,
>> exposed over a nice interface like Kibana.
>>
>> This seems to fit well with your use case, and I would be more than happy
>> to help if you had questions setting it up.
>>
>>
>> https://github.com/jprante/elasticsearch-jdbc/wiki/Step-by-step-recipe-for-setting-up-the-river-with-PostgreSQL
>>
>> Justin
>>
>> On 2015-05-25 10:19 PM, Alex Salkever wrote:
>>
>> I'd be cautious about MongoDB. It's NoSQL and doesn't handle nested data
>> very well. It's easy to use if you are only doing things with document
>> format but it's not really a SQL tool.
>>
>> I think the problem is the GUI part. There are loads of hosted solutions
>> for DBs but most don't have GUIs that would allow Excel users to work with
>> data. That's why I see a lot of people using Google Sheets - because it's
>> an Excel flavor but does synch across users and has versioning that can
>> easily be rolled back, as well as roles.
>>
>>
>>
>>
>>
>> On Sat, Apr 18, 2015 at 2:18 AM, Michael Saunby <mike at saunby.net> <mike at saunby.net> wrote:
>>
>>
>>  How about using CartoDB?  It's an open source project, so you can host it
>> yourself - https://github.com/CartoDB/cartodb
>> but there's also free to use and enterprise services http://cartodb.com/
>>
>> Files in Excel and many other formats can be imported easily.
>>
>> Michael
>>
>> On 17 April 2015 at 18:45, M. Edward (Ed) Borasky <znmeb at znmeb.net> <znmeb at znmeb.net> wrote:
>>
>>
>>  On Fri, Apr 17, 2015 at 12:49 AM, Sam Leon <sam.leon at okfn.org> <sam.leon at okfn.org> wrote:
>>
>>  I'm looking for a secure hosted database service ideally running
>>
>>  Postgres in
>>
>>  the backend which could be queried and updated using SQL commands but
>>
>>  also
>>
>>  had a graphical interface for users not familiar with SQL who can
>>
>>  easily run
>>
>>  queries and export to CSV. It's for journalists I'm working with who
>>
>>  have
>>
>>  various datasets currently in Excel which is a nightmare for
>>
>>  simultaneous
>>
>>  work and is exceptionally brittle.
>>
>> I'm aware of the the PANDA project, wondered if anyone else had any
>> tooling/service tips?
>>
>> Sam
>>
>>  Some combination of Excel, ODBC, PgAdmin3 and PostgreSQL is probably
>> the easiest path out of chaos for you and your users.  A hosted
>> PostgreSQL isn't going to be cheap, though - I'd start with free
>> desktop installs to get the workflow stabilized.
>> http://www.postgresql.org/download/windows/
>>
>> If you do go the hosted route, you'll need to hire a strong PostgreSQL
>> database administrator (DBA) to handle all the backup and security
>> stuff. Don't make that a "side task" for someone or you'll either lose
>> data or get hacked or both.
>>
>> The front ends are Excel and PgAdmin3. PgAdmin3 is a GUI tool for
>> managing the database but it also has a visual query builder similar
>> to the one in MS Access. Excel also has a query builder. ODBC is
>> "middleware" that will present a uniform database language to any
>> query tool.
>>
>> After you've got all that nailed down, it's just a small step to R,
>> RStudio and the bright world of analysis and visualization described
>> in the RStudio cheatsheets
>> (http://www.rstudio.com/resources/cheatsheets/). ;-)
>> _______________________________________________
>> data-driven-journalism mailing listdata-driven-journalism at lists.okfn.orghttps://lists.okfn.org/mailman/listinfo/data-driven-journalism
>> Unsubscribe:https://lists.okfn.org/mailman/options/data-driven-journalism
>>
>>  _______________________________________________
>> data-driven-journalism mailing listdata-driven-journalism at lists.okfn.orghttps://lists.okfn.org/mailman/listinfo/data-driven-journalism
>> Unsubscribe: https://lists.okfn.org/mailman/options/data-driven-journalism
>>
>>
>>
>> _______________________________________________
>> data-driven-journalism mailing listdata-driven-journalism at lists.okfn.orghttps://lists.okfn.org/mailman/listinfo/data-driven-journalism
>> Unsubscribe: https://lists.okfn.org/mailman/options/data-driven-journalism
>>
>>
>>
>> _______________________________________________
>> data-driven-journalism mailing list
>> data-driven-journalism at lists.okfn.org
>> https://lists.okfn.org/mailman/listinfo/data-driven-journalism
>> Unsubscribe:
>> https://lists.okfn.org/mailman/options/data-driven-journalism
>>
>>
>
>
> --
> Alex Salkever
> Growth/BD/Data Journalism
> 415-503-9035
> www.silk.co / @silkdotco <http://www.twitter.com/silkdotco> /
> @silkjournalism <http://www.twitter.com/silkjournalism>
>
>
> _______________________________________________
> data-driven-journalism mailing list
> data-driven-journalism at lists.okfn.org
> https://lists.okfn.org/mailman/listinfo/data-driven-journalism
> Unsubscribe: https://lists.okfn.org/mailman/options/data-driven-journalism
>
>

-- 
ALESSIO CIMARELLI
a.k.a. jenkin

Data scientist, web developer e giornalista scientifico free-lance

Blog: dataninja.it <http://www.dataninja.it/>
Mail: jenkin at dataninja.it
PGP pub key: 0x46bd7d12
<http://keyserver.ubuntu.com/pks/lookup?op=get&search=0xE9FD2D2B>
Twitter: @jenkin27 <https://twitter.com/jenkin27> | Skype: alessio.cimarelli

About.me <http://about.me/alessio.cimarelli>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.okfn.org/pipermail/data-driven-journalism/attachments/20150528/9b1e94da/attachment-0003.html>