Automated database creation?

aseques · June 20, 2012, 10:19am

Hi, following the path of integrating piwik into anther control panel (i-mscp) I am getting stuck at this point when creating the database.
Basically I accomplished so far:
[ul]
[li] Crafted config.ini.php with user, password, db connections
[/li][li] Create specific user for this database
[/li][li] Grant the proper privileges for this user to the database
[/li][li] Create the database
[/li][/ul]

What I would like to know is what would the appropiate process to create the database enries required, since I am getting stuck in:

[quote=“Mysqli prepare error: Table ‘ispcp_piwik.piwik_option’ doesn’t exist”][/quote]

Could I automate the creation/upgrade of piwik databases when needed?

Some info about the piwik workflow:

github.com/matomo-org/matomo

Setting up Piwik manually or headless install not supported

opened 06:31PM - 19 Sep 09 UTC

closed 09:27PM - 28 Dec 09 UTC

robocoder

Bug

We provide config/config.ini.sample.php, which implies that a user could copy an…d configure this manually as config.ini.php. - if the db user doesn't exist, Piwik reports: ``` SQLSTATE[28000] [1045] Access denied for user "%s" ``` (followed by a backtrace) - if db user exists but not the database, Piwik reports: ``` SQLSTATE[42000] [1049] Unknown database "%s" ``` (followed by a backtrace) - if db user and database exist, but not the tables, Piwik reports: ``` Critical Error during the update process: Database piwik is empty. You must edit or remove your Piwik configuration file. ``` - if user bypasses the Installation, things like the anonymous user and default site are not created Proposal: - for database user that doesn't exist, provide help - for missing database or tables, prompt to remove the config file and redirect to installer, using the settings to pre-fill the forms; (we don't want to remove the config file without user confirmation, in case Piwik simply lost its connection to the MySQL server)

There is this script which does some part of the job, but it only creates the database and grants the users, so it has no use for me:

Also, the headless install got pollute with typo3 configs, so don’t know how much work is left to be done:

github.com/matomo-org/matomo

Headless install / command line piwik remote install

opened 03:44PM - 14 Aug 10 UTC

closed 12:30PM - 13 Jan 14 UTC

robocoder

Enhancement

Use cases: - 3rd party apps seeking deeper integration - hosting providers (or s…cript providers) offering more seamless installs - puppet installation As we've seen (eg Typo3, Dreamhost, SimpleScript) bypass (in various degrees) the native installer which increasingly does more system checks and initialization (eg salt, .htaccess, robots.txt, favicon.ico, web.config). This sometimes results in misplaced support requests. A headless install would accept the configuration via get/post parameters, run thru the installation steps, and then either return success (200 Ok and any warnings) or (4xx/5xx and the warnings+error messages). We would also have to publish an integration guide to all the configuration parameters, defaults, and required/mandatory vs optional (eg overwrite existing tables).

aseques · June 21, 2012, 7:27am

Another thread about automation (without a solution): 301 Moved Permanently

matthieu · June 21, 2012, 8:03am

It’s important not to create the config/config.ini.php file - at first the db details are stored in the session.
Could you look at the simplescripts/softaculous scripts for inspiration maybe?

aseques · June 21, 2012, 12:15pm

I can’t see neither the source for simplescripts script neither scriptaculous without registering into their sites, and simplescripts requires me to set a hosting provider. Softaculous requires me to create an acount in control system I don’t have.
Could you point me to the source code of those scripts?

matthieu · June 21, 2012, 7:07pm

Sorry I don’t know if we can actually find the source codes, maybe they are private. But I know it’s possible to bypass the Installer. Maybe we need to patchPiwik to make it easier though?

aseques · June 22, 2012, 12:18pm

[quote=matt]
Sorry I don’t know if we can actually find the source codes, maybe they are private. But I know it’s possible to bypass the Installer. Maybe we need to patchPiwik to make it easier though?[/quote]

That would be great, I don’t considerer myself as a programmer, so I don’t know how would be the best way to deal with it, what I would need (I think) would be to call:


Piwik::getTablesCreateSql();

That’s the code that seems to create/upgrade the databases when needed, I tried to create a script that would handle that, but I didn’t succed
This is what I got so far.


<?php

define('PIWIK_DOCUMENT_ROOT', dirname(__FILE__)=='/'?'':dirname(__FILE__));
define('PIWIK_INCLUDE_PATH', PIWIK_DOCUMENT_ROOT);

//Required pear classes
//require "Event/Notification.php";

include('core/Piwik.php');
include('core/Db/Schema.php');
include('core/PluginsManager.php');
require('libs/Event/Dispatcher.php');
Piwik_Event_Notification:Piwik_AddAction
Piwik::getTablesCreateSql();
?>

matthieu · June 27, 2012, 6:28pm

Sorry I don’t have time to help with that advanced request unfortunately
but it’s interesting. If you need help please consider hiring pro services: http://piwik.org/consulting/

aseques · September 7, 2012, 8:07am

Finally worked out this part. It was my fault setting a wrong prefix on tables, after doing this, the login page just appeared.


-tables_prefix  = piwik_
+tables_prefix  = ""

nuxwin · October 25, 2012, 5:06pm

Hello ;

I’m developer at IMSCP. What we want do exactly it’s to allow our customers to use your software instead of awstats for their statistics. I’ve looked a lot at your code and I must say that it’s really hard to dump any database update from it since many of them are generated on the fly according current database state. Well, I would recommend to propose a kind of CLI api for both install/update db tasks. For integration, we are using incremental process and so, it’s more easy for us to trigger a CLI command to either install or update Piwik database than ask our users to connect to piwik to do the needed job after i-MSCP install/update.

You are providing a great alternative to the awstats dinosaur and it’s always a pleasure for us to integrate such software.

Note: Sorry for my bad English, I’m french.

[attachment 906 Capturedu2012-10-2518:09:05.png]

matthieu · October 26, 2012, 12:45am

Hello,

Thanks for your interest and integrating piwik in your software! Maybe we could improve the script, currently you can run the DB update from the CLI though, maybe that’s enough? see: Update Piwik - Analytics Platform - Matomo