Db command tutorial

db command tutorial

The db command is unique to SME Server, and is the command line user interface to the db system. It is used to manipulate configuration setting of the various configuration databases which are then incorporated into the standard configuration files in /etc/.... via the template expansion process.

The db system comes with default values that can be manipulated by either manually through the CLI or fully automatic through the Server Manager. This tutorial explains manipulating the db system manually through the CLI.

To display the syntax issue the following command:

/sbin/e-smith/db

which will result in the db help output below, for we did not pass any parameters.

/sbin/e-smith/db dbfile keys
/sbin/e-smith/db dbfile print [key]
/sbin/e-smith/db dbfile show [key]
/sbin/e-smith/db dbfile get key
/sbin/e-smith/db dbfile set key type [prop1 val1] [prop2 val2] ...
/sbin/e-smith/db dbfile setdefault key type [prop1 val1] [prop2 val2] ...
/sbin/e-smith/db dbfile delete key
/sbin/e-smith/db dbfile printtype [key]
/sbin/e-smith/db dbfile gettype key
/sbin/e-smith/db dbfile settype key type
/sbin/e-smith/db dbfile printprop key [prop1] [prop2] [prop3] ...
/sbin/e-smith/db dbfile getprop key prop
/sbin/e-smith/db dbfile setprop key prop1 val1 [prop2 val2] [prop3 val3] ...
/sbin/e-smith/db dbfile delprop key prop1 [prop2] [prop3] ...

	Note:
	Note that on a properly setup SME Server (path wise) you do not need to include the /sbin/e-smith/ part, only use db or config since you are in the Path of your environment

Only for the configuration database

	Note:
	Use of 'config' is a shorthand version for 'db configuration' and therefore only works with the configuration database

db configuration

is

config

Note this only applies to this one often used database file, all others must be referred to using the format

db dbname command key [property value] [property2 value2]

All database files are in

/home/e-smith/db

Auto completion

Like in every Linux shell you can use the TAB key when you use the command line to auto complete or propose all available answers

Examples

Here are a few examples to demonstrate usage and syntax correlation

db dbname show

Using real db names - for the database in /home/e-smith/db/configuration

db configuration show
db configuration show |less
db configuration keys |less

• An example for the imap service

[root@sme8dev64 ~]# db configuration show imap

imap=service
   ConcurrencyLimit=400
   ConcurrencyLimitPerIP=12
   TCPPort=143
   access=private
   status=enabled

we can retrieve a value

[root@sme8dev64 ~]# db configuration getprop imap status

which gives something like

enabled

[root@sme8dev64 ~]# db configuration getprop dansguardian ConcurrencyLimitPerIP

which gives something like

12

Compare the above displayed actual element values of the db command with the syntax result from issuing the db command where each element or piece equates to:

Keys/Properties/values

All turn around A key with properties and values of the properties, no more, no less. You can have a lot of properties under a key which is unique

imap=service
  ConcurrencyLimit=400
  ConcurrencyLimitPerIP=12
  TCPPort=143
  access=private
  status=enabled

Database name	Unique key	Property name	Default value	Possible values	Description
configuration	imap		service		Unique key name
		Concurrenstyle	400	nnn	User defined value
		ConcurrencyLimitPerIP	12	nn	User defined value
		TCPPort	143	nnn	TCP port or port range [nnn] or [nnn-nnn]
		access	private	private / public	Specify LAN only or LAN and WAN access. The firewall will be adjusted accordingly
		status	enabled	enabled / disabled	Enable or disable this service. The firewall will be adjusted accordingly

Apply the above "reasoning" to any database and the the syntax presented by issuing db then makes sense.

Note that missing (or no) properties and values do not necessarily mean no value is in the /etc/file.conf system, as the template code can have a default value in the absence of a specific db value.

Note to determine what db settings are supported by sme code, or what the default values are, refer to (ie read) the code that is in the particular template or custom template associated with a /etc/file.conf

Look in /etc/e-smith/templates/... and /etc/e-smith/templates-custom/.... and subfolders and template fragments applicable to particular /etc/file.conf configuration files.

show all available databases

To show a list of existing databases do

ls -al /home/e-smith/db

which gives something like for official databases

total 304
-rw-r----- 1 root admin   1921 févr. 18  2014 accounts
-rw-r--r-- 1 root admin      0 févr.  2  2013 backups
-rw-r----- 1 root admin   8816 nov.  4 19:38 configuration
-rw-r----- 1 root admin    361 févr.  2  2013 domains
-rw-r----- 1 root admin    816 févr.  7  2013 hosts
drwxr-x--- 2 root ldap    4096 févr. 22  2013 ldap
-rw-r----- 1 root admin   3098 févr.  2  2013 mailpatterns
drwxr-x--- 2 root root    4096 avril 27  2014 mysql
drwxr-xr-x 2 root root    4096 nov.  3 21:42 navigation
-rw-r----- 1 root admin    299 févr.  2  2013 networks
-rw-r----- 1 root admin      0 mars 11  2010 portforward_tcp
-rw-r----- 1 root admin      0 mars 11  2010 portforward_udp
-rw-r----- 1 root admin    271 févr.  2  2013 spamassassin
-rw-r--r-- 1 root root  201371 nov.  4 04:04 yum_available
-rw-r--r-- 1 root root   37419 nov.  4 04:04 yum_installed
-rw-r----- 1 root admin   4459 févr. 18  2014 yum_repositories
-rw-r--r-- 1 root root      70 nov.  4 04:04 yum_updates

To show the keys for the two main databases do:

db configuration show |less

That can be abbreviated to

config show |less

db accounts show |less

Note after using the |less switch, press Enter to scroll down a line at a time, or press Space to scroll down a page at a time. 'q' to quit.

Usage

	Note:
	Use of 'config' is a shorthand version for 'db configuration' and therefore only works with the configuration database

Create a key in a database

the generic command is :

db dbfile set key type [prop1 val1] [prop2 val2] ...

dbfile : choose the database in /home/e-smith/db
key : the name of key
type : generally 'service' or 'configuration' but it can be other things as webbapps or url or ...

If you want to create a keys called 'plop' in the configuration database with a type as 'configuration you can do like this

db configuration set plop configuration Name wordpress PublicAccess private status enabled DbName wordpress DbUser wordpress WpLang en

you can see the result

config show plop

or

db configuration show plop

 plop=configuration
   DbName=wordpress
   DbUser=wordpress
   Name=wordpress
   PublicAccess=private
   WpLang=en
   status=enabled

Change values of properties

The generic command line is

db dbfile setprop key prop1 val1 [prop2 val2] [prop3 val3] ...

dbfile : choose the database in /home/e-smith/db
key : the name of key

Then if you want to modify some values of our example above

db configuration setprop plop PublicAccess public WpLang fr

or

config setprop plop PublicAccess public WpLang fr

We want to verify

config show plop 

or

db configuration show plop

plop=configuration
   DbName=wordpress
   DbUser=wordpress
   Name=wordpress
   PublicAccess=public  <----------Here
   WpLang=fr            <----------Here
   status=enabled

Create a property under a key

Same as above, really not difficult

db configuration setprop plop PlopTheWorld YES             

or

config setprop plop PlopTheWorld YES             

and to display modification

db configuration show plop

or

config show plop

plop=configuration
   DbName=wordpress
   DbUser=wordpress
   Name=wordpress
   PlopTheWorld=YES    <----------Here
   PublicAccess=public
   WpLang=fr
   status=enabled

Setting db variables to default values

	Note:
	Use of 'config' is a shorthand version for 'db configuration' and therefore only works with the configuration database

Any db variable that has a default value can be reset to the default by deleting the variable entirely, then re-initializing the default database values as follows:

config delprop <key> <prop>
/etc/e-smith/events/actions/initialize-default-databases

for example

db configuration delprop plop WPlang
/etc/e-smith/events/actions/initialize-default-databases

or

config delprop plop WpLang
/etc/e-smith/events/actions/initialize-default-databases

Delete a property value

To delete the property

db dbfile delprop <key> <prop>

for example

db configuration delprop plop WpLang

or

config delprop plop WpLang

Delete a Key

To delete the Key

db dbfile delete <key>

for example

db configuration delete plop

or

config delete plop

Reset a property to an empty value

To reset to an empty value

db dbfile setprop <key> <prop> ''

for example

db configuration setprop plop WpLang ''

or

config setprop plop WpLang ''

Check if a key is used

To check if a key is used, grep templates

Eg :

grep -irn grapCutof /etc/e-smith/templates

grep -rn AutoBlock /etc/e-smith/templates

	Note:
	: via CLI so it is easy to make mistakes. Hence it is safer to user server manager when there is an entry to configure key.

	Warning:
	Database parameters are case sensitive so take great care when typing at the server shell because no error messages are given should you make a mistake.

Signal-event

Once you have adjusted/modified/created your keys/properties/values, it is not finished because you have to do a signal-event to read all templates and add the values you have set in all configuration files.

signal-event are called like this, the most known is

signal-event post-upgrade; signal-event reboot

This one reboot your server and reconfigure all templates and initialize all default db entries. It is an SME Server requirement that all database entries and configuration files must be correctly configured after a "reconfiguration reboot"

signal-event console-save

This one is useful when you don't want to restart your server, a lot of event are called with this command line but The console-save event is not a "reconfigure everything" event, and only changes items which can be configured from the text-mode console. It is convenient in this case as it performs database initialization and migration.

all specific events can be found at DB_Variables_Configuration and in the developer manual with more informations

References

See the Howto section for a couple of articles on db commands that should give useful info ie

http://wiki.contribs.org/DB_Variables_Configuration

and

http://wiki.contribs.org/Useful_Commands

and refer to the Developers Guide for technical information if needed

---