SQLiteBoy
Simple web-based management tool for SQLite database
(with form, report, website, and many other features)
(c) Noprianto <nopri.anto@icloud.com>
2012-2019
License: GPL
Version: 1.83

SQLiteBoy is an independent product, developed separately from the
SQLite core library, which is maintained by SQLite.org.
Neither noprianto.com nor SQLite.org take any responsibility for the
work of the other.

SQLiteBoy
Simple web-based management tool for SQLite database
(with form, report, website, and many other features)
(c) Noprianto <nopri.anto@icloud.com>
2012-2019
License: GPL
Version: 1.83

SQLiteBoy is an independent product, developed separately from the
SQLite core library, which is maintained by SQLite.org.
Neither noprianto.com nor SQLite.org take any responsibility for the
work of the other.

Contents

• What Is SQLiteBoy
• Links
• Development Notes
• Features
• Requirements
• Standalone Version
• Login
• SSL Support
• How To Run
• Custom Template
• Website and Custom URL Reference
• User-defined Function
• Form Code Reference
• Report Code Reference
• Page Code Reference
• Number To Words Reference
• Script Code Reference
• System Profile Reference
• User-defined Profile Reference
• Python Handler Reference
• Server Command Reference
• Link Code Reference
• Title Reference
• Logs
• Monkey Programming Language

What Is SQLiteBoy

Easy to use, python and web.py based, simple web-based management tool for SQLite database with user-defined functions and many extended features (Free/open source)

User-defined functions: number to words (multi language), number format, table lookup, hash, base64, random, additional date/time, additional string, regular expression, utility

If Extended feature is enabled:

Multi user, simple (yet flexible) form (data entry) and reporting can be created by admin (simple JSON syntax), and can be run by admin/user (configurable). Very simple subform is also supported.

Form field supports predefined values (options) from SQL Query or Python list. Also, default value can be result of function call, static value or SQL Query. Constraint is also supported, to check before save, to prevent saving invalid value (it’s possible to call function before comparison). Onsave event is also supported, to execute SQL Query (and use the result) just before the data is saved.

Reporting wizard also supports form field predefined values, default value and constraint (checking before reporting query is executed). Supported report format: PDF, HTML, HTML (printer friendly) and CSV.

Form and Report support python handler, which will be automatically called, if provided. Python handler eases the integration with external system (for example: ERP system). Python handler also could be useful in, for example, complex database operation, reading from/writing to external devices, etc.

User accounts, Notes, Files (with file sharing support), Page (home page), calculator, configurable hosts allowed, database backup, system configuration, Scripts, profile (with user-defined profile support) and others are available as extended features

SQLiteBoy script (simple JSON syntax, single file) can be used to automate the creation of tables (including addition of columns, for existing table), forms, reports or user-defined profiles

It is also possible to use SQLiteBoy to serve a website with custom URLs. URL can be handled by a python function, redirect, Files, template, or HTML. POST method is also supported and can be handled by python function.

Links

• screenshot, probably not up-to-date: https://github.com/nopri/sqliteboy/wiki
• Free Book: Form dan Report sederhana dengan SQLiteBoy (Bahasa Indonesia, available as PDF/ODT, 250+ pages, revision 2): https://github.com/nopri/publication/raw/master/form-dan-report-sederhana-dengan-sqliteboy.pdf
• Tutorial: simple medical record: https://github.com/nopri/sqliteboy/wiki/Tutorial-simple-medical-record
• Tutorial: install sqliteboy on ACRyan Playon!HD Mini2 ACRPV73800: https://github.com/nopri/sqliteboy/wiki/Tutorial-install-sqliteboy-on-ACRyan-Playon!HD-Mini2-ACRPV73800
• Tutorial: Using sqliteboy udf in python handler: https://github.com/nopri/sqliteboy/wiki/Tutorial-using-sqliteboy-udf-in-python-handler
• Tutorial: Create a new partner in OpenERP: https://github.com/nopri/sqliteboy/wiki/Tutorial-create-a-new-partner-in-openerp
• Tutorial: Search partners in OpenERP: https://github.com/nopri/sqliteboy/wiki/Tutorial-search-partners-in-openerp
• Tutorial: Hyperlink and Javascript in label: https://github.com/nopri/sqliteboy/wiki/Tutorial-hyperlink-and-javascript-in-label
• More: https://github.com/nopri/sqliteboy/wiki/_pages

Development Notes

• Standalone version is no longer provided, but codes for this are still in the source code
• Version 1.51 was released as a bug fix (reported in issue #1). In this commit, many lines (both in this file and the source code) had been added/deleted (due to text reformatting). Please, let me know if something was accidentally added or deleted.
• As of v1.64, index URL was changed to /index because / is used for public home page (new in v1.64, please read WEBSITE AND CUSTOM URL REFERENCE)

Features

Works with single SQLite database per instance

Single python file

Configurable port (default 11738 because it looks like sqliteboy). As of v1.75, it is possible to run SQLiteBoy as WSGI application, please read HOW TO RUN.

SSL Support

Basic/Extended Feature

Basic: Database management + User-defined function

Extended: Form, Report, User/Login, etc

Completely optional

Can be enabled in menu

If enabled, one table:

_sqliteboy_

will be created. You can delete this table and extended feature will be disabled

Form Support (Extended feature, new in v0.12)

• Simple data entry
• Simple syntax (JSON)
• Please read FORM CODE REFERENCE section (below)
• Readonly field
• Required field
• Predefined values (field options) from SQL Query or Python list
• Default value: function call or static value or SQL Query
• Constraint: check before save, prevent saving invalid value (possible to call function before comparison)
• Simple security setting
• As of v0.21, onsave event is also supported, to execute SQL Query (and use the result) just before the data is saved. The SQL Query can be very complex and involves nested function calls.
• As of v0.26, very simple subform is also supported. Subform can be used in one to many relationship. However, field in subform is limited, compared to form.
• As of v0.27, custom result message (based on SQL query result), is also supported.
• As of v0.27, optional, additional SQL query statement(s) can be provided, and each of them will be executed in order, if you need to perform additional task(s), after the form data is saved (for example, write to another table). Previously, one might use report if need to write to several tables. Last insert rowid value is provided.
• As of v0.75, insert into table can be disabled by setting insert key to zero/negative value. This is useful if you need to update/delete data in table(s), using additional SQL query statement(s). By default, form/subform save will insert new row(s) into table(s).
• As of v0.98, form supports python handler, which will be automatically called, if provided. Python handler eases the integration with external system (for example: ERP system). Please read PYTHON HANDLER REFERENCE section.
• As of v1.33, optional, (run before) additional SQL query statement(s) can be provided.
• As of v1.72, links can be added to form
• As of v1.73, form supports extended result message. This extends custom result message introduced in v0.27. Using extended result, it is possible to perform operations on form fields, and use the result. Like custom result message, it is based on SQL query result. But unlike custom result message, the result is not limited to integer (can be string or another types; probably returned from function call).

Report Support (Extended feature, new in v0.16)

• Simple reporting (and data entry)
• Simple syntax (JSON)
• Please read REPORT CODE REFERENCE section (below)
• Readonly field
• Predefined values (field options) from SQL Query or Python list
• Default value: function call or static value or SQL Query
• Constraint: check before query, (possible to call function before comparison)
• Flexible SQL query (and relation to wizard/user input) (free form query, You can use join, etc)
• Custom header order
• Simple security setting
• As of v0.18, report also can be used as form/data entry, using insert SQL query. Custom result message (based on SQL query result), is also supported.
• As of v0.60, headers and footers are supported. If not defined, a default one will be created. Plain text, SQL Query, and Image are supported.
• As of v0.85, printer friendly version of report result is supported in default output format (HTML)
• As of v1.00, report supports python handler, which will be automatically called, if provided. Python handler eases the integration with external system (for example: ERP system). Please read PYTHON HANDLER REFERENCE section.
• As of v1.17, CSV output is supported
• As of v1.28, PDF output (paper size, margins, image) is supported
• As of v1.36, text align is supported (HTML and PDF only)
• As of v1.72, links can be added to report

Files (Extended feature, new in v0.47)

• System configuration: maximum number of files per user (admin: unlimited)
• System configuration: maximum file size (admin: unlimited)
• Multiple file upload (content type, filename, etc are saved)
• Action: view
• Action: download (disposition attachment)
• Simple file sharing support
• Human readable file size
• URL: /fs
• HTTP 404 Error: file not found or not shared

Page (Extended feature, new in v0.48)

• Static page per user (home page)
• URL: /page/<user>
• Please read PAGE CODE REFERENCE section (below)

Scripts (Extended feature, new in v0.71)

• Simple script, to automate the creation of tables (including addition of columns, for existing table), forms, reports or user-defined profiles
• Solution can be deployed in form of script, that can be uploaded and run by admin
• Simple syntax (JSON) in single file
• Please read SCRIPT CODE REFERENCE section (below)

Profile (Extended feature, new in v0.91)

• User profile

• style

• User-defined profile is also supported. Using this feature, custom field(s) in user profile can be added. This is useful, for example, in multi-company environment.

• system configuration

• Simple syntax (JSON)

• Predefined values (field options) from SQL Query or Python list (as in form or report, is also supported)

• Please read USER-DEFINED PROFILE REFERENCE section (below)

Website (Extended feature, new in v1.64)

• Custom URLs
• URL can be handled by a python function, redirect, Files, template, or HTML
• As of v1.67, POST method handler is supported, using python function
• As of v1.68, custom not found URL is supported
• Please read WEBSITE AND CUSTOM URL REFERENCE

Browse table

• Sort (asc/desc)
• Download for BLOB type (if not NULL)
• Multiple selection
• Delete selected
• Edit selected
• Maintain last selected row(s)
• Limit rows
• Pagination

Insert into table

• Default value hint
• Work with default value(s)
• Upload for BLOB type

Edit/Update table

• Default value hint
• Work with default value(s)
• Download for BLOB type (if not NULL)
• Upload for BLOB type

Column

• Add column (with type and default value)
• Multiple column addition

Rename table

Empty table

Drop table

CSV export/import

Schema (view schema, create new table)

Copy table

Create table

• Support type, primary key, default value
• Single or multiple primary key
• Support for integer primary key autoincrement
• Default value can be non-constant (for example: current_time, current_timestamp)

Query

• Free form SQL Query
• Automatically view query output (as integer or table)
• Export query result to CSV (if applicable)
• User-defined variable is also supported (max per user: 3). Please use the following functions: sqliteboy_var_set, sqliteboy_var_get, sqliteboy_var_del.

Vacuum

User account (Extended feature)

• Type: admin (full access), standard (limited or configurable form/report access)
• Change password
• User management

Notes (Extended feature, new in v0.41)

• Simple notes
• Content as SQL Query (admin), calculator, source code
• Please read Interpreter below

Calculator (Extended feature, new in v0.50)

• Simple calculator
• Valid characters: 0123456789.-+*/()
• Maximum length: 36

Interpreter (Extended feature, new in v1.81)

• Please read MONKEY PROGRAMMING LANGUAGE

User-defined function

Prefix:

sqliteboy_

Can be used in Query or Form or Report

Please read USER-DEFINED FUNCTION below

Will be added regularly (or by your request)

Easy to translate

Configurable hosts allowed (default: local) (Extended feature)

Database backup (admin) (Extended feature)

System configuration (admin) (Extended feature, new in v0.43)

Shortcut (form, report) (Extended feature, new in v0.84)

Logs (Extended feature, new in v1.61)

Human readable database size (GB, MB, KB, B)

Load time

Custom Template

Minimum use of Javascript in default/builtin template (only for confirmation dialog and toggle select all)

Table name limitation: cannot handle table with whitespace in name

Requirements

• python
• web.py (http://webpy.org)
• SQLite module (included as sqlite3, in python 2.5+)
• JSON module (included as json, in python 2.6+)
• Optional: ReportLab (PDF output)
• Optional: pyOpenSSL (SSL support)

Standalone Version

Note: Standalone version is no longer provided

Login

• Default admin user and password: admin
• As of v1.63, additional/custom links at login page are supported. Links may be placed at multiple page sections (please read LINK CODE REFERENCE and TITLE REFERENCE).
• As of v1.66, it is possible to define redirect URL after logged in, using to=<URL> parameter. However, only valid URL is allowed, according to WEBSITE AND CUSTOM URL REFERENCE.
• As of v1.69, logout uses redirect URL set in login

SSL Support

To enable SSL support, please put the following files into current working directory:

• sqliteboy.cert (SSL certificate)
• sqliteboy.key (SSL private key)

If you need to create a self-signed test certificate, OpenSSL can be used:

openssl req -new -x509 -newkey rsa:1024 -keyout sqliteboy.key -out sqliteboy.cert -days 365 -nodes

How To Run

Command:

python sqliteboy.py <database_file> [port]

(if you are using source code)

or

python sqliteboy.py <database_file> [port] > LOGFILE 2>&1 &

(if you are using source code, sh compatible shell (with job control),
and want to run in the background. If applicable, You could use
/dev/null as LOGFILE if you don't care about the logs.)

then, using web browser, visit localhost:11738, or localhost:PORT, if PORT is specified

Please use https if SSL support is enabled

(Please also read SERVER COMMAND REFERENCE)

As of v1.62, it is possible to run multiple SQLiteBoy instances (single host, different ports / databases), as the HTTP cookie name is set based on database path.

As of v1.75, it is possible to run as WSGI application:

Please make sure that an SQLite database named data.db exists in current working directory (or, it can be an empty file)

If ‘application’ is expected:

from sqliteboy import wsgi_application as application

As of v1.76, disk-based session is supported. If a readable/writable directory named sqliteboy-session is found in current working directory, disk-based session will be used. Otherwise, default memory-based session will be used.

Custom Template

• sqliteboy.html, if found in current working directory
• For template example: T_BASE variable
• Please do not put ‘$def with (data, content)’ line in template

Website and Custom URL Reference

To manage a website, please visit /admin/website (as admin), or visit info -> website

Custom URLs, as long as the URLs are not used by SQLiteBoy (reserved). List of reserved URLs is shown in website management screen.

URL can be handled by a python function, redirect, Files, template, or HTML

Each URL is specified by:

• id: must be alphabetic only (maximum length: 36), converted to lowercase on save. This id is used in python handler.
• url: must be alphanumeric/underscore/dot/slash/dash (maximum length: 128), converted to lowercase on save. This is the URL. Please read the additional rules below.
• content: content, interpreted.

Additional URL rules:

• Please use / for home page. Without this URL, / will be redirected to /index (and then /login if the user is not logged in)
• Please start url with / (but do not end it with /)

Only valid values are saved (id and url are checked on save)

Content interpretation:

• If there is a python function named web_<id> (in sqliteboy_user.py, please also read PYTHON HANDLER REFERENCE):

• It will be called and the return value is used as dynamic contents (with custom HTTP headers)

• If there is an exception, redirection to /index will be performed. Please make sure that the python function is valid.

• If python handler for that URL is NOT available:

• If the content looks like a number:

• If the number is a valid file id:

• Contents of the file will be returned (along with saved headers)

• To set Content-Disposition as attachment, please set download parameter (for example: ?download=download or ?download=true)

• Default Content-Disposition is inline

• Valid file id:

• Exists in Files

• If user is logged in:

• File sharing status is checked. If a file is not shared (and logged in user is not the owner), it is considered invalid. This applies for all users, including admin users.

• If the number is NOT a valid file id:

• Content will be returned

• If it is NOT a number:

• If the content starts with http:// or https://, redirection is performed

• Otherwise, the content is interpreted as HTML with template

HTML template interpretation:

If there is an exception (or content is HTML), content will be returned as is

If this meant to be a template, please start the content with

$def with (id, url, content, param)

The following globals are available to template:

Note: most user-defined functions are also available. Please read USER-DEFINED FUNCTION. Unavailable functions are marked with: not available in HTML template interpretation.

size: a function, requires no argument, returns database size as string

user: a function, requires no argument, returns logged in user name as string (or an empty string)

table_browse: a function, to browse a table (excluding _sqliteboy_, sqlite_sequence, sqlite_master)

table_browse(table, what='*', where=None, order=None, group=None, limit=None, offset=None)

begin: a function, to begin an HTML document

begin(title='', style='', lang='en', charset='utf-8')

end: a function, to end an HTML document

redirect: a function, to redirect to a URL

redirect(url, title='', after=0, lang='en')

redirect_check: a function, to redirect to a URL, if a condition is false. Otherwise, begin an HTML document.

redirect_check(check, url, title_redirect='', after=0,
lang='en', title='', style='', charset='utf-8')

redirect_user: a function, to redirect to a URL, if user is not logged in. Otherwise, begin an HTML document.

redirect_user(url, title_redirect='', after=0,
lang='en', title='', style='', charset='utf-8')

Please read web.py template for more information

Example (begin, end):

$def with (id, url, content, param)
$ a = begin(title='Hello')
$ b = end()
$:a
Hello
$:b

Example (custom URL:/test, /test?hello=world):

$def with (id, url, content, param)
<!DOCTYPE html>
<html>
<head>
</head>
<body>
$ u = user()
$if u:
Hello, $u
<br>
$ data = table_browse('A', order='name')
$if data:
$for d in data:
$d['name']
<br>
URL: $url
<br>
Hello: $param.get('hello', '')
</body>
</html>

Python handler:

Required arguments:

• user: current user (str)
• db: database connection object (web.py database object)
• url_id: url id (str)
• url: url (str)
• content: content (str)
• param: parameter (web.input())
• data: additional data (helper functions, UDFs, templates, modules, etc) (dict)

Function must return a list of two members:

• headers, empty list OR list of [HTTP header name, HTTP header value]
• content (str)

Example (url id: test, url: /test, url: test?hello=world):

def web_test(user, db, url_id, url, content, param, data):
headers = [
['Content-Type', 'text/plain'],
]
content = 'hello %s' %(param.get('hello', ''))
return [headers, content]

POST method:

Can only be handled by a python function named post_<id> (in sqliteboy_user.py, please also read PYTHON HANDLER REFERENCE)

If there is an exception, or handler is not available, an empty string is returned

Example (url id: form, url: /form):

<!DOCTYPE html>
<html>
<head>
</head>
<body>
<form action="/form" method="post">
Hello <input type="text" name="name">
<input type="submit">
</form>
</body>
</html>

POST method handler:

def post_form(user, db, url_id, url, content, param, data):
headers = [
['Content-Type', 'text/plain'],
]
content = 'POST: hello %s' %(param.get('name', ''))
return [headers, content]

Custom not found URL:

• Can be set at System configuration
• Only applicable when not logged in
• Please set to existing custom URL

Note: it is probably a good idea to consider/use a reverse proxy

User-defined Function

Note: please use SQLite built-in functions whenever and wherever possible.

sqliteboy_strs(s)

sqliteboy_as_integer(s)

sqliteboy_as_float(s)

sqliteboy_len(s)

sqliteboy_md5(s)

sqliteboy_sha1(s)

sqliteboy_sha224(s)

sqliteboy_sha256(s)

sqliteboy_sha384(s)

sqliteboy_sha512(s)

sqliteboy_b64encode(s)

sqliteboy_b64decode(s)

sqliteboy_randrange(a, b)

sqliteboy_randstr(s, a, b)

random string
argument    :
s (set characters)
a (min length, > 0)
b (max length, > 0, >=a)

example     :
sqliteboy_randstr('abcdef123456', 3, 8)
-> 'e2e6'

tips        :
- fix length: a = b
- use sqliteboy_randstr2() or sqliteboy_randstr3() for predefined
set characters
- use sqliteboy_randstr_simple() for simple random string

sqliteboy_randstr2(a, b)

random string (predefined set characters, letters + digits + punctuation)
argument    :
a (min length, > 0)
b (max length, > 0, >=a)

example     :
sqliteboy_randstr2(3, 8)
-> '"Z\@Z'

sqliteboy_randstr3(a, b)

random string (predefined set characters, letters + digits)
argument    :
a (min length, > 0)
b (max length, > 0, >=a)

example     :
sqliteboy_randstr3(3, 8)
-> 'nItJ8'

sqliteboy_randstr_simple()

random string (simple)
example     :
sqliteboy_randstr_simple()
-> 'VUmDAQeJCpww9IjmiexrWRuRT6ZgpacKVdOA'

sqliteboy_is_datetime_format(s, fmt)

is date time according to format
argument    :
s (input string)
fmt (date time format string)

example     :
sqliteboy_is_datetime_format('2014', '%Y')
-> 1

sqliteboy_is_datetime_format('2014-01-01', '%Y-%m-%d')
-> 1

sqliteboy_is_datetime_format('2014-01-01', '%Y-%m-%d %H:%M:%S')
-> 0

sqliteboy_is_datetime_format('2014-01-01 01:02:03', '%Y-%m-%d %H:%M:%S')
-> 1

tips        :
- use sqliteboy_is_datetime(), sqliteboy_is_date() or sqliteboy_is_time()
for predefined date time format

sqliteboy_is_datetime(s)

sqliteboy_is_date(s)

sqliteboy_is_time(s)

sqliteboy_time()

sqliteboy_time2(s)

get time from string (YYYY-MM-DD HH:MM:SS)
argument    :
s (date/time string)

example     :
sqliteboy_time2('2012-03-28 19:20:21')
-> 1332937221.0

sqliteboy_time2_date(s)

sqliteboy_time2() using YYYY-MM-DD format

sqliteboy_time2_format(s, fmt)

sqliteboy_time2() using custom format

sqliteboy_time3(f)

get string (YYYY-MM-DD HH:MM:SS) from time (local time)
argument    :
f (time)

example     :
sqliteboy_time3(1)
-> 1970-01-01 07:00:01
-> timezone is UTC+7

sqliteboy_time3a()

alias for sqliteboy_time3(sqliteboy_time())

sqliteboy_time4(f)

get string (YYYY-MM-DD HH:MM:SS) from time (UTC)
argument    :
f (time)

example     :
sqliteboy_time4(1)
-> 1970-01-01 00:00:01

sqliteboy_time4a()

alias for sqliteboy_time4(sqliteboy_time())

sqliteboy_time5(s1, s2, mode)

calculate the difference between two dates in seconds, minutes, hours, days, or years
(1 year = 365.2425 days)
argument    :
s1 (YYYY-MM-DD HH:MM:SS)
s2 (YYYY-MM-DD HH:MM:SS)
mode (1=seconds, 2=minutes, 3=hours, 4=days, 5=years)

example     :
sqliteboy_time5('2010-11-12 13:14:15', '2011-12-13 14:15:16', 1)
-> 34218061.0

sqliteboy_time5('2010-11-12 13:14:15', '2011-12-13 14:15:16', 2)
-> 570301.016667

sqliteboy_time5('2010-11-12 13:14:15', '2011-12-13 14:15:16', 3)
-> 9505.01694444

sqliteboy_time5('2010-11-12 13:14:15', '2011-12-13 14:15:16', 4)
-> 396.042372685

sqliteboy_time5('2010-11-12 13:14:15', '2011-12-13 14:15:16', 5)
-> 1.08432718724

tips        :
empty/invalid s1 or s2: current date/time (localtime)
use sqliteboy_number_format() to format the result

sqliteboy_time6(f, year, month, day, mode)

format the difference between two dates in
y (years) m (months) d (days) format
argument    :
f (number, in year, use sqliteboy_time5 function (mode=5) )
year (year string)
month (month string)
day (day string)
mode (1=30.44 days/month, 1=30 days/month, 2=31 days/month)

example     :
sqliteboy_time6(sqliteboy_time5('2010-11-12 01:02:03', '2011-12-13 11:12:13', 5), ' years ', ' months ', ' days ', 0)
-> 1 years 1 months 1 days

sqliteboy_time6(sqliteboy_time5('2010-11-12 01:02:03', '2011-10-11 11:12:13', 5), ' years ', ' months ', ' days ', 0)
-> 0 years 10 months 29 days

sqliteboy_time6(sqliteboy_time5('2013-01-01 10:20:30', '2013-01-02 10:20:30', 5), ' years ', ' months ', ' days ', 0)
-> 0 years 0 months 1 days

sqliteboy_time6(sqliteboy_time5('2013-01-02 10:20:30', '2013-01-01 10:20:30', 5), ' years ', ' months ', ' days ', 0)
-> 0 years 0 months -1 days

sqliteboy_time6(1000, ' years ', ' months ', ' days ', 0)
-> 1000 years 0 months 0 days

sqliteboy_time6(1.5, ' years ', ' months ', ' days ', 0)
-> 1 years 6 months 0 days

sqliteboy_time6(1.24, ' years ', ' months ', ' days ', 0)
-> 1 years 2 months 27 days

sqliteboy_time6(1.24, ' years ', ' months ', ' days ', 1)
-> 1 years 2 months 26 days

sqliteboy_time6(1.24, ' years, ', ' months, ', ' days', 0)
-> 1 years, 2 months, 27 days

sqliteboy_time6(1.24, ' tahun ', ' bulan ', ' hari ', 0)
-> 1 tahun 2 bulan 27 hari

sqliteboy_is_leap(n)

is leap year
argument    :
n (year)

return value:
1 (leap year) or 0 (not leap year)

sqliteboy_datetime_format(fmt)

format date time
argument    :
fmt (format string)

sqliteboy_datetime_format_local(fmt)

format date time (local)
argument    :
fmt (format string)

sqliteboy_date()

get current date in YYYY-MM-DD format

sqliteboy_date_local()

get current date (local) in YYYY-MM-DD format

sqliteboy_date_delta_format(s, fmt, n)

get date delta (days) from date string, using custom format
argument    :
s (date string)
fmt (format string)
n (days)

sqliteboy_date_local_delta_format(s, fmt, n)

get date delta (days) from date string, using custom format
(local time)
argument    :
s (date string)
fmt (format string)
n (days)

sqliteboy_date_delta(s, n)

get date delta (days) from date string (YYYY-MM-DD)
argument    :
s (date string)
n (days)

sqliteboy_date_local_delta(s, n)

get date delta (days) from date string (YYYY-MM-DD)
(local time)
argument    :
s (date string)
n (days)

sqliteboy_today_local_delta(n)

get date delta (days) from current date (YYYY-MM-DD)
(local time)
argument    :
n (days)

sqliteboy_today_local_delta_hour(n)

get date delta (days) from current date/time (YYYY-MM-DD)
(local time)
argument    :
n (hours)

sqliteboy_lower(s)

sqliteboy_upper(s)

sqliteboy_swapcase(s)

sqliteboy_capitalize(s, what)

capitalize string
argument    :
s (input string)
what (0=first word, 1=all)

example     :
sqliteboy_capitalize('hello world', 0)
-> 'Hello world'

sqliteboy_capitalize('hello world', 1)
-> 'Hello World'

sqliteboy_justify(s, justify, length, padding)

left, right, center justify string
argument    :
s (input string)
justify (0=left, 1=right, 2=center)
length (length)
padding (single padding character)

example     :
sqliteboy_justify('hello', 0, 10, 'x')
-> 'helloxxxxx'

sqliteboy_justify('hello', 1, 10, 'x')
-> 'xxxxxhello'

sqliteboy_justify('hello', 2, 10, 'x')
-> 'xxhelloxxx'

sqliteboy_justify(12345, 1, 10, 0)
-> '0000012345'

sqliteboy_find(s, sub, position, case)

find index in s where substring sub is found
argument    :
s (input string)
sub (substring)
position (0=lowest index, 1=highest index)
case (0=ignore case, 1=case sensitive)

return value:
-1 (not found) or > -1 (found, starts from 0)

example     :
sqliteboy_find('hello sqliteboy', 'e', 0, 0)
-> 1

sqliteboy_find('hello sqliteboy', 'e', 1, 0)
-> 11

sqliteboy_find('hello sqlitEboy', 'e', 1, 0)
-> 11

sqliteboy_find('hello sqlitEboy', 'e', 1, 1)
-> 1

sqliteboy_reverse(s)

reverse string
argument    :
s (input string)

example     :
sqliteboy_reverse('hello world')
-> 'dlrow olleh'

sqliteboy_reverse(12345)
-> '54321'

sqliteboy_repeat(s, n)

repeat s (n times)
argument    :
s (input string)
n (n times)

example     :
sqliteboy_repeat('sqliteboy ', 5)
-> 'sqliteboy sqliteboy sqliteboy sqliteboy sqliteboy'

sqliteboy_repeat(1, 20)
-> '11111111111111111111'

sqliteboy_repeat('=', 10)
-> '=========='

sqliteboy_count(s, sub, case)

count substring sub in s
argument    :
s (input string)
sub (substring)
case (0=ignore case, 1=case sensitive)

return value:
0 (not found) or > 0 (found)

example     :
sqliteboy_count('hello sqliteboy', 'e', 0)
-> 2

sqliteboy_count('hello hello hello', 'Hello', 0)
-> 3

sqliteboy_count('hello hello hello', 'Hello', 1)
-> 0

sqliteboy_is_valid_email(s)

return value  :
1 (valid) or 0 (invalid)

sqliteboy_match(s1, s2)

regular expression match
argument    :
s1 (pattern string)
s2 (test string)

return value:
1 (match) or 0 (not match)

sqliteboy_is_number(n)

argument    :
n (number or string to test)

return value:
1 (number) or 0 (not number)

sqliteboy_is_float(n)

return value:
1 (float) or 0 (not float)

sqliteboy_is_integer(n)

return value:
1 (integer) or 0 (not integer)

sqliteboy_normalize_separator(s, separator, remove_space, unique)

argument    :
separator (separator string)
remove_space (remove space in s, 1 or 0)
unique (1 or 0)

example     :
sqliteboy_normalize_separator
(',,,,,1,1,,  2,  3,  4,,,,', ',', 1, 1)
-> '1,2,3,4'

sqliteboy_split0(s, separator, index)

split string s using separator as the delimiter string and
return index (in list)
argument    :
s (input string)
separator (separator string)
index (index)

return value:
index (in list) or ''

example     :
sqliteboy_split0('s.q.l.i.t.e.b.o.y', '.', 1)
-> 'q'

sqliteboy_split0('s.q.l.i.t.e.b.o.y', '', 1)
-> ''

sqliteboy_split0('s.q.l.i.t.e.b.o.y', '.', -3)
-> 'b'

sqliteboy_split0('h e l l o', '', 1)
-> 'e'

tips        :
empty separator: use whitespace

sqliteboy_chunk(s, n, separator, justify, padding)

split string into evenly sized chunks
argument    :
s (string)
n (length/size)
separator (separator string)
justify (0=left, 1=right)
padding (single padding character)

example     :
select sqliteboy_chunk('123456789', 3, '-', 1, 'x')
-> '123-456-789'

select sqliteboy_chunk('123456789', 2, '-', 0, 'x')
-> '12-34-56-78-9x'

select sqliteboy_chunk('123456789', 2, '-', 1, 'x')
-> 'x1-23-45-67-89'

select sqliteboy_chunk('123456789', 4, ',', 1, '*')
-> '***1,2345,6789'

sqliteboy_number_format(n, decimals, decimal_point, thousands_separator)

format a number (or number as string) with grouped thousands and decimals
(works with number in scientific notation (e))
argument    :
n (number or number as string), use string for very big number
decimals (number of decimal points)
decimal_point (separator for the decimal point)
thousands_separator (thousands separator)

example     :
sqliteboy_number_format(12345, 3, '.', ',')
-> '12,345'

sqliteboy_number_format(12345, 3, ',', '.')
-> '12.345'

sqliteboy_number_format(12345.1234, 3, ',', '.')
-> '12.345,123'

sqliteboy_number_format(12345.1234, 0, ',', '.')
-> '12.345'

sqliteboy_number_format(12345.1234, 10, ',', '.')
-> '12.345,1234000000'

sqliteboy_number_format(12345.1234, 2, ',', ' ')
-> '12 345,12'

sqliteboy_number_format('-12345678912345678912345678912345678912.123', 10, ',', '.')
-> '-12.345.678.912.345.678.912.345.678.912.345.678.912,1230000000'

sqliteboy_number_to_words(s, language)

number to words
Please read NUMBER TO WORDS REFERENCE section (below)

argument    :
s (number as string)
language (language code)

return value:
number to words or '' (error/unsupported)

example     :
language  : 'id'

sqliteboy_number_to_words('-0', 'id')
-> 'nol'

sqliteboy_number_to_words('11', 'id')
-> 'sebelas'

sqliteboy_number_to_words('1000', 'id')
-> 'seribu'

sqliteboy_number_to_words('1000000', 'id')
-> 'satu juta'

sqliteboy_number_to_words('-123456789123456789123456789.123456789', 'id')
-> 'min seratus dua puluh tiga triliun empat ratus lima puluh enam milyar tujuh ratus delapan puluh sembilan juta seratus dua puluh tiga ribu empat ratus lima puluh enam triliun tujuh ratus delapan puluh sembilan milyar seratus dua puluh tiga juta empat ratus lima puluh enam ribu tujuh ratus delapan puluh sembilan koma satu dua tiga empat lima enam tujuh delapan sembilan'

language  : 'en1'

sqliteboy_number_to_words('-0', 'en1')
-> 'zero'

sqliteboy_number_to_words('11', 'en1')
-> 'eleven'

sqliteboy_number_to_words('1000', 'en1')
-> 'one thousand'

sqliteboy_number_to_words('1000000', 'en1')
-> 'one million'

sqliteboy_number_to_words('-123456789123456789123456789.123456789', 'en1')
-> 'minus one hundred twenty-three trillion four hundred fifty-six billion seven hundred eighty-nine million one hundred twenty-three thousand four hundred fifty-six trillion seven hundred eighty-nine billion one hundred twenty-three million four hundred fifty-six thousand seven hundred eighty-nine point one two three four five six seven eight nine'

sqliteboy_lookup1(table, field, field1, value1, function, distinct)

(not available in HTML template interpretation)

SELECT <function>(<field>) FROM <table> WHERE <field1>=<value1>
and
return function result
argument    :
table (table name)
field (field name)
field1 (where field)
value1 (where field value)
function (avg, count, group_concat, max, min, sum, total)
distinct (0=non distinct, 1=distinct)

return value:
function result (as str) or '' (error)

example     :
data in 'lookup' table:
| a | b |
---------
|a  | 0 |
|a  | 1 |
|a1 | 2 |
|a2 | 3 |

sqliteboy_lookup1('lookup', 'b', 'a', 'a', 'avg', 0)
-> '0.5'

sqliteboy_lookup1('lookup', 'a', 'a', 'a', 'count', 0)
-> '2'

sqliteboy_lookup1('lookup', 'a', 'a', 'a', 'count', 1)
-> '1'

sqliteboy_lookup1('lookup', 'a', 'a', 'a', 'group_concat', 0)
-> 'a,a'

sqliteboy_lookup1('lookup', 'b', 'a', 'a', 'max', 0)
-> '1'

sqliteboy_lookup1('lookup', 'b', 'a', 'a', 'min', 0)
-> '0'

sqliteboy_lookup1('lookup', 'b', 'a', 'a', 'sum', 0)
-> '1'

sqliteboy_lookup1('lookup', 'b', 'a', 'a2', 'total', 0)
-> '3.0'

sqliteboy_lookup2(table, field, field1, value1, order, default)

(not available in HTML template interpretation)

lookup into table
SELECT <field> FROM <table> WHERE <field1>=<value1> ORDER BY rowid asc
or
SELECT <field> FROM <table> WHERE <field1>=<value1> ORDER BY rowid desc
and
return first row
argument    :
table (table name)
field (field name)
field1 (where field)
value1 (where field value)
order (0=asc, 1=desc)
default (default return value)

example     :
data in 'lookup' table:
| a | b | c |
-------------
|a1 |b1 |c1 |
|a2 |b2 |c2 |

sqliteboy_lookup2('lookup', 'c', 'a', 'a1', 0, ':(')
-> 'c1'

sqliteboy_lookup2('lookup', 'c_notfound', 'a', 'a1', 0, ':(')
-> ':('

sqliteboy_lookup2('lookup', 'b', 'a', 'a1', 0, ':(')
-> 'b1'

sqliteboy_lookup2(12345, 'b', 'a', 'a1', 0, ':(')
-> ':('

sqliteboy_lookup3(table, field, field1, value1, field2, value2, order, default)

(not available in HTML template interpretation)

lookup into table
SELECT <field> FROM <table> WHERE <field1>=<value1> and <field2>=<value2> ORDER BY rowid asc
or
SELECT <field> FROM <table> WHERE <field1>=<value1> and <field2>=<value2> ORDER BY rowid desc
and
return first row
argument    :
table (table name)
field (field name)
field1 (where field1)
value1 (where field1 value)
field2 (where field2)
value2 (where field2 value)
order (0=asc, 1=desc)
default (default return value)

example     :
data in 'lookup' table:
| a | b | c |
-------------
|a1 |b1 |c1 |
|a2 |b2 |c2 |

sqliteboy_lookup3('lookup', 'c', 'a', 'a1', 'b', 'b1', 0, ':(')
-> 'c1'

sqliteboy_lookup3('lookup', 'c', 'a', 'a1', 'b', 'b2', 0, ':(')
-> ':('

sqliteboy_lookup3(12345, 'c', 'a', 'a1', 'b', 'b1', 0, ':(')
-> ':('

sqliteboy_split1(s, separator, table, column, convert)

(not available in HTML template interpretation)

split string s using separator as the delimiter string and
insert into table (column) for each member in list
argument    :
s (input string)
separator (separator string)
table (table to insert)
column (column in table)
convert(0=no conversion, 1=convert to column type if applicable (or to string) )

return value:
number of row(s) inserted into table, or 0

example     :
sqliteboy_split1('h.e.l.l.o.w.o.r.l.d', '.', 'test_split', 'c', 1)
-> 10

sqliteboy_split1('hello', '', 'test_split', 'c', 0)
-> 1

tips        :
empty separator: use whitespace

sqliteboy_list_datetime1(s, n, interval, table, column, local)

(not available in HTML template interpretation)

generate list of datetime starting with s (inclusive),
as much as n, with interval,
and insert into table (column) for each member in list
argument    :
s (YYYY-MM-DD HH:MM:SS)
n (as much as, must be > 0)
interval (interval in seconds, must not zero)
table (table to insert)
column (column in table)
local (0=UTC, 1=local)

return value:
number of row(s) inserted into table, or 0

example     :
(local timezone is UTC+7)

sqliteboy_list_datetime1('', 5, 60*60*24, 'test_date', 'a', 1)
-> 5
(data in table)
2013-06-03 23:13:27
2013-06-04 23:13:27
2013-06-05 23:13:27
2013-06-06 23:13:27
2013-06-07 23:13:27

sqliteboy_list_datetime1('', 5, 60*60*24, 'test_date', 'a', 0)
-> 5
(data in table)
2013-06-03 16:14:09
2013-06-04 16:14:09
2013-06-05 16:14:09
2013-06-06 16:14:09
2013-06-07 16:14:09

sqliteboy_list_datetime1('', 5, -60*60*24, 'test_date', 'a', 1)
-> 5
(data in table)
2013-06-03 23:14:55
2013-06-02 23:14:55
2013-06-01 23:14:55
2013-05-31 23:14:55
2013-05-30 23:14:55

sqliteboy_list_datetime1('2013-01-01 00:00:00', 5, 60*60, 'test_date', 'a', 1)
-> 5
(data in table)
2013-01-01 00:00:00
2013-01-01 01:00:00
2013-01-01 02:00:00
2013-01-01 03:00:00
2013-01-01 04:00:00

tips        :
empty s: current date/time (localtime)

sqliteboy_if(s, a, b)

(not available in HTML template interpretation)

if s, return a, else return b
argument    :
s (SQL query, must return column alias named 'if')
a (return this, if 'if' column considered true)
b (return this, if 'if' column considered false)

return value:
a or b, or '' (error)

example     :
sqliteboy_if('select 1 as if' , 'True', 'False')
-> 'True'

sqliteboy_if('select -1 as if' , 'True', 'False')
-> 'True'

sqliteboy_if('select 0 as if' , 'True', 'False')
-> 'False'

sqliteboy_if('select -1 as if' , 1, -1)
-> 1

sqliteboy_if('select "" as if' , 'True', 'False')
-> 'False'

sqliteboy_if('select "sqliteboy" as if' , 'True', 'False')
-> 'True'

sqliteboy_if('select 1' , 'True', 'False')
-> ''

tips        :
for SQLite built-in command, please use CASE expression

sqliteboy_http_remote_addr()

return value  :
http remote address

sqliteboy_http_remote_addr_ext()

return value  :
http remote address (read HTTP_X_FORWARDED_FOR)

sqliteboy_http_user_agent()

return value  :
http user agent (for example: web browser)

sqliteboy_app_title()

return value:
application title

example     :
sqliteboy_app_title()
-> 'sqliteboy 1.10'

sqliteboy_var_set(name, value)

(not available in HTML template interpretation)

user-defined variable: set
(max per user apply)
argument    :
name (variable name, underscore and alphanumeric only, not case-sensitive)
value (value)

return value:
1 (ok) or 0

example     :
sqliteboy_var_set('a', 1000)
-> 1

sqliteboy_var_set('b', 'hello')
-> 1

tips        :
to free some space, please use sqliteboy_var_del function below,
setting to empty string or 0 does not delete the variable

sqliteboy_var_get(name)

(not available in HTML template interpretation)

user-defined variable: get
argument    :
name (variable name, underscore and alphanumeric only, not case-sensitive)

return value:
value of variable or ''

example     :
sqliteboy_var_get('a')
-> 1000

sqliteboy_var_get('b')
-> hello

sqliteboy_var_del(name)

(not available in HTML template interpretation)

user-defined variable: delete
argument    :
name (variable name, underscore and alphanumeric only, not case-sensitive)

return value:
1 (ok) or 0

example     :
sqliteboy_var_del('a')
-> 1

sqliteboy_var_del('b')
-> 1

sqliteboy_strip_html(s)

strip html
argument    :
s (input string)

example     :
sqliteboy_strip_html('<b>hello</b>')
-> 'hello'

sqliteboy_x_user()

(not available in HTML template interpretation)

return value  :
user name (if extended feature is enabled, or '')

sqliteboy_x_profile_all(u, field, system)

(not available in HTML template interpretation)

read user profile (both system and user-defined)

argument    :
u (user)
field (custom field)
system (0=user-defined, 1=system)

return value:
field value (if extended feature is enabled and field is set,
or '')

sqliteboy_x_profile(u, field)

(not available in HTML template interpretation)

read custom field in user-defined profile for user u
Please read USER-DEFINED PROFILE REFERENCE section (below)

argument    :
u (user)
field (custom field)

return value:
field value (if extended feature is enabled and field is set,
or '')

sqliteboy_x_profile_system(u, field)

(not available in HTML template interpretation)

read system profile for user u
Please read SYSTEM PROFILE REFERENCE section (below)

argument    :
u (user)
field (field)

return value:
field value (if extended feature is enabled and field is set,
or '')

sqliteboy_x_my(field)

(not available in HTML template interpretation)

alias for sqliteboy_x_profile(sqliteboy_x_user(), field)

sqliteboy_x_my_system(field)

(not available in HTML template interpretation)

alias for sqliteboy_x_profile_system(sqliteboy_x_user(), field)

Form Code Reference

• Must be valid JSON syntax (json.org)
• String (including keys below) must be double-quoted (between " and ")
• No trailling comma in dict or list
• Python dict (keys are case-sensitive)
• Only single table is supported. If you need to write to another table after form data is saved, you can use additional SQL query statement(s) (see below).
• Onsave event can be used to execute SQL Query (and use the result) just before the data is saved. The SQL Query can be very complex and involves nested function calls.
• Very simple subform is also supported. Subform can be used in one to many relationship. However, fields in subform is limited, compared to form (only reference and default are supported; all is required; none is readonly; column(s) can be selected). When saving data, transaction is used.
• Custom result message (based on SQL query result), is also supported.
• Extended result message is supported, as an alternative to custom result message. Using extended result, it is possible to perform operations on form fields, and use the result. Like custom result message, it is based on SQL query result. But unlike custom result message, the result is not limited to integer (can be string or another types; probably returned from function call).
• Optional, additional SQL query statement(s) can be provided, and each of them will be executed in order, if you need to perform additional task(s), after the form data is saved (for example, write to another table). Previously, one might use report if need to write to several tables. Last insert rowid value is provided.
• Insert into table can be disabled by setting insert key to zero/negative value. This is useful if you need to update/delete data in table(s), using additional SQL query statement(s). By default, form/subform save will insert new row(s) into table(s). Please note that setting insert key to zero/negative value will also set last insert rowid/query result to same value as insert value.
• Please also read PYTHON HANDLER REFERENCE section
• Keys:

Key	Description	Type	Status	Example
data	form data	list of dict	required	see: Keys (data)
security	form security	dict	required	see: Keys (security)
title	form title	str	optional	"My Form"
info	form information (html is allowed)	str	optional	"Form Information"
sub	subform+ must be list of five members: related table (str); related column in that table (str); list of [rows (int), required rows (int)]; list of list (column) [column (str), label (str), reference, default]; subform information (str) + see Keys (data) below for reference/default + return value of last_insert_rowid() will be written to related column (each row). Use ROWID column in master table to get the relation.	list	optional	+ ["table2", "a", [5,3], [["b", "Column B", [ ["0", "NO"], ["1", "YES"] ], "1"], ["c", "Column C", "select a, b from table1", ""]], "My Subform"]
message	custom result message+ not applicable to subform + must be list of three members (str) ["message res < 0", "message res = 0", "message res > 0"] + $result (in message) will be replaced by actual SQL Query result + $<column> will be replaced by user input value for that column + $last_insert_rowid will be replaced by last_insert_rowid() function call result (after insert to main table) + $python_handler will be replaced by return value of python handler (if provided, default: -1) (html is allowed)	list	optional	+ [ "unknown result", "zero result", "success: $result" ]
result	extended result message+ not applicable to subform + must be list of two members (str) ["template", "sql query"] + $result (in template) will be replaced by actual SQL Query result + $<column> will be replaced by user input value for that column + $last_insert_rowid will be replaced by last_insert_rowid() function call result (after insert to main table) + $python_handler will be replaced by return value of python handler (if provided, default: -1) (html is allowed)	list	optional	[ "$a + $b = $result", "select $a+$b as result" ] (Example 3)
sql0	additional sql query statement(s) (run before) (please see sql2)	list	optional
sql2	additional sql query statement(s) (run after)+ must be list of str + $<column> will be replaced by user input va