Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
13306 lines (12796 sloc) 381 KB
#!/usr/bin/env python
#
#
# sqliteboy.py
# Simple web-based management tool for SQLite database
# (with form, report, website, and many other features)
# (c) Noprianto <nop@noprianto.com>
# 2012-2019
# License: GPL
#
# SQLiteBoy is an independent product, developed separately from the
# SQLite core library, which is maintained by SQLite.org.
# Neither SQLiteBoy.com nor SQLite.org take any responsibility for the
# work of the other.
#
#
# Please read README.rst
#
# I apologize for:
# - the lack of documentation in source code
# - obsolete codes
# - non-descriptive variable/function names
# - using python builtins as variable (input, type, ...) :(
# - bare except:
# - PEP8 violations :)
#
#
#----------------------------------------------------------------------#
# APPLICATION #
#----------------------------------------------------------------------#
NAME = 'sqliteboy'
APP_DESC = 'Simple web-based management tool for SQLite database (with form, report, website, and many other features)'
VERSION = '1.74'
WSITE = 'http://sqliteboy.com'
TITLE = NAME + ' ' + VERSION
TITLE_DEFAULT = NAME
DBN = 'sqlite'
CHECK_SAME_THREAD = False
FORM_TBL = '_sqliteboy_'
FORM_URL_INIT = '/sqliteboy/init'
FORM_FIELDS = ['a', 'b', 'c', 'd', 'e', 'f', 'g']
FORM_FIELDS_R1 = ['rowid', 'rowtime']
FORM_FIELD_TYPE = 'text'
FORM_SPLIT = '.'
FORM_VALID = None
URL_VALID = None
PATH_VALID = None
PRECISION = 4
SORT = ('asc', 'desc')
VSORT = ('&#9650;', '&#9660;')
ROWID = 'ROWID'
DEFAULT_PORT = 11738
DEFAULT_ADDR = '0.0.0.0'
DEFAULT_LANG = 'default'
DEFAULT_TABLE = 'sqlite_master'
DEFAULT_LIMIT = 50
DEFAULT_T_BASE = '%s.html' %(NAME)
DEFAULT_T_BASE_HEADER = '$def with (data, content)'
DEFAULT_PY_HANDLER = '%s_user' %(NAME)
DEFAULT_SSL_CERTIFICATE = '%s.cert' %(NAME)
DEFAULT_SSL_PRIVATE_KEY = '%s.key' %(NAME)
DEFAULT_PY_FORM = 'form_'
DEFAULT_PY_REPORT = 'report_'
DEFAULT_PY_WEB = 'web_'
DEFAULT_PY_WEB_POST = 'post_'
DEFAULT_WEBSITE_POST = ''
DEFAULT_ADMIN_USER = 'admin'
DEFAULT_ADMIN_PASSWORD = DEFAULT_ADMIN_USER
DEFAULT_HOSTS_ALLOWED = ['127.0.0.1']
DEFAULT_TEXTAREA_COLS = 40
DEFAULT_TEXTAREA_ROWS = 15
DEFAULT_ERROR_INT = -1
DEFAULT_ERROR_STR = ''
DEFAULT_WIN_EXE = '%s.exe' %(NAME)
DEFAULT_WIN_EXE_VERSION = '%s.version' %(DEFAULT_WIN_EXE)
DEFAULT_WIN_MD5 = '%s.md5' %(DEFAULT_WIN_EXE)
DEFAULT_FAVICON = '%s.ico' %(NAME)
DEFAULT_SPEC = '%s.spec' %(NAME)
DEFAULT_VERSION = '%s.version' %(NAME)
DEFAULT_WEBPY_STATIC = ['static']
DEFAULT_QUERY_EXPORT = 'query.csv'
DEFAULT_VAR_MAX = 3
DEFAULT_INDEX = '/index'
DEFAULT_HOME = '/'
DEFAULT_CUSTOM = '/(.*)'
APPLICATION_TITLE_MAX = 32
BROWSE_LIMIT_ALL = [10, 25, DEFAULT_LIMIT, 100, 250, 500, 1000]
SEQUENCE_TABLE = 'sqlite_sequence'
HOST_LOCAL = '0'
HOST_ALL = '1'
HOST_CUSTOM = '2'
DFLT_VALUE = 'dflt_value'
MARK_CURRENT = '&#9658' #unused
NAME_SELECT = 'select'
EXCLUDE_TABLE = []
SIZE_KB = 1024
SIZE_MB = 1024 * SIZE_KB
SIZE_GB = 1024 * SIZE_MB
BLOB_TYPE = ['blob']
TEXT_TYPE = ['text', 'clob']
NOQUOTE_TYPE = ['integer', 'real']
BLOB_VAR = 'rowid'
BLOB_COLUMN = 'column'
BLOB_CTYPE = 'application/octet-stream'
MODE_INSERT = 'insert'
SKT_ROWID = 'rowid'
SKT_M_INSERT = 'insert'
SKT_M_EDIT = 'edit'
SKT_P_EDIT = 'position'
SKT_M_COLUMN = 'column'
SKT_M_RENAME = 'rename'
SKT_M_DROP = 'drop'
SKT_M_COPY = 'copy'
SKT_M_EMPTY = 'empty'
SKT_M_IMPORT = 'import'
SKQ = 'query'
SKV = 'vacuum'
SK_CREATE = 'create'
SK_LOGIN = 'login'
SK_PASSWORD = 'password'
SK_NOTES = 'notes'
SK_FILES = 'files'
SK_PAGES = 'pages'
SK_CALCULATOR = 'calculator'
SK_USERS = 'users'
SK_HOSTS = 'hosts'
SK_SYSTEM = 'system'
SK_SCRIPTS = 'scripts'
SK_SCRIPT = 'script'
SK_PROFILE = 'profile'
SK_SCHEMA = 'schema'
SK_WEBSITE = 'website'
SKF_CREATE = 'form.create'
SKF_RUN = 'form.run'
SKR_CREATE = 'report.create'
SKR_RUN = 'report.run'
COLUMN_TYPES = (
('integer primary key', 0),
('integer primary key autoincrement', 0),
('integer', 1),
('real', 1),
('char', 1),
('varchar', 1),
('text', 1),
('blob', 1),
('null', 1),
) #type, used in add column
COLUMN_CONVERT = {
'integer': 'sqliteboy_as_integer',
'real': 'sqliteboy_as_float',
'char': 'sqliteboy_strs',
'varchar': 'sqliteboy_strs',
'text': 'sqliteboy_strs',
'blob': 'sqliteboy_strs',
}
COLUMN_CONVERT_DEFAULT = 'sqliteboy_strs'
MAX_COLUMN_ADD = 3
CUSTOM_RT = {
'query': 4,
'calculator': 3,
} #command, rt
PK_SYM = '*'
URL_README = ('/sqliteboy/readme', 'sqliteboy_readme', 'README.rst')
URL_SOURCE = ('/sqliteboy/source', 'sqliteboy_source', 'sqliteboy.py')
PROTECTED_USERS = ['admin']
FORM_ALL = ''
FORM_KEY_TITLE = 'title'
FORM_KEY_INFO = 'info'
FORM_KEY_DATA = 'data'
FORM_KEY_DATA_TABLE = 'table'
FORM_KEY_DATA_COLUMN = 'column'
FORM_KEY_DATA_LABEL = 'label'
FORM_KEY_DATA_REFERENCE = 'reference'
FORM_KEY_DATA_DEFAULT = 'default'
FORM_KEY_DATA_REQUIRED = 'required'
FORM_KEY_DATA_READONLY = 'readonly'
FORM_KEY_DATA_CONSTRAINT = 'constraint'
FORM_KEY_SECURITY = 'security'
FORM_KEY_SECURITY_RUN = 'run'
FORM_KEY_DATA_ONSAVE = 'onsave'
FORM_KEY_SUB = 'sub'
FORM_KEY_MESSAGE = 'message'
FORM_KEY_SQL2 = 'sql2'
FORM_KEY_SQL0 = 'sql0'
FORM_KEY_INSERT = 'insert'
FORM_KEY_CONFIRM = 'confirm'
FORM_KEY_FOCUS = 'focus'
FORM_KEY_LINK = 'link'
FORM_KEY_RESULT = 'result'
FORM_REQ = (FORM_KEY_DATA,)
FORM_REQ_X = (2,) #parsed index
FORM_REQ_DATA = (FORM_KEY_DATA_TABLE,
FORM_KEY_DATA_COLUMN,
)
FORM_REFERENCE_SQL_0 = 'a'
FORM_REFERENCE_SQL_1 = 'b'
FORM_ONSAVE_SQL_VALUE = 'value'
FORM_ONSAVE_SQL_RET = 'onsave'
FORM_SUB_ROWS_DEFAULT = [5, 1]#rows, required rows
FORM_MESSAGE_LEN = 3
FORM_MESSAGE_VAR_RESULT = 'result'
FORM_MESSAGE_VAR_PYTHON_HANDLER = 'python_handler'
FORM_MESSAGE_RESULT_LEN = 2
FORM_INSERT_DEFAULT = 1
FORM_DEFAULT_SQL_RET = 'a'
#
REPORT_KEY_DATA_TYPES = ['integer']
REPORT_ALL = FORM_ALL
REPORT_KEY_TITLE = FORM_KEY_TITLE
REPORT_KEY_INFO = FORM_KEY_INFO
REPORT_KEY_DATA = FORM_KEY_DATA
REPORT_KEY_DATA_KEY = 'key'
REPORT_KEY_DATA_LABEL = FORM_KEY_DATA_LABEL
REPORT_KEY_DATA_REFERENCE = FORM_KEY_DATA_REFERENCE
REPORT_KEY_DATA_DEFAULT = FORM_KEY_DATA_DEFAULT
REPORT_KEY_DATA_REQUIRED = FORM_KEY_DATA_REQUIRED
REPORT_KEY_DATA_READONLY = FORM_KEY_DATA_READONLY
REPORT_KEY_DATA_CONSTRAINT = FORM_KEY_DATA_CONSTRAINT
REPORT_KEY_DATA_TYPE = 'type'
REPORT_KEY_ALIGN = 'align'
REPORT_KEY_MESSAGE = 'message'
REPORT_KEY_SECURITY = FORM_KEY_SECURITY
REPORT_KEY_SECURITY_RUN = FORM_KEY_SECURITY_RUN
REPORT_KEY_SQL = 'sql'
REPORT_KEY_HEADER = 'header'
REPORT_KEY_HEADERS = 'headers'
REPORT_KEY_FOOTERS = 'footers'
REPORT_KEY_PAPER = 'paper'
REPORT_KEY_MARGINS = 'margins'
REPORT_KEY_CONFIRM = 'confirm'
REPORT_KEY_FOCUS = 'focus'
REPORT_KEY_LINK = 'link'
REPORT_REQ = (REPORT_KEY_DATA,
REPORT_KEY_SQL,
)
REPORT_REQ_X = (3,) #parsed index
REPORT_REQ_DATA = (REPORT_KEY_DATA_KEY,)
REPORT_REFERENCE_SQL_0 = 'a'
REPORT_REFERENCE_SQL_1 = 'b'
REPORT_MESSAGE_LEN = 3
REPORT_MESSAGE_VAR_RESULT = 'result'
REPORT_HEADERS_CELL_LEN = 3
REPORT_FOOTERS_CELL_LEN = 3
REPORT_HEADERS_CELL_TYPES = [
(str, unicode, ),
(str, unicode, int, ),
(dict, ),
]
REPORT_FOOTERS_CELL_TYPES = REPORT_HEADERS_CELL_TYPES
REPORT_CELL_TYPE_TEXT = ''
REPORT_CELL_TYPE_FILES_IMAGE = 'files.image'
REPORT_CELL_TYPE_SQL = 'sql'
REPORT_CELL_TYPE_SQL_RESULT = REPORT_REFERENCE_SQL_0
REPORT_RESULT_ROW_COUNT = 'result_row_count'
REPORT_RESULT_MESSAGE = 'result_message'
REPORT_FORMAT_DEFAULT = ''
REPORT_FORMAT_CSV = 'csv'
REPORT_FORMAT_PDF = 'pdf'
REPORT_FORMAT_ALL = [
REPORT_FORMAT_DEFAULT,
REPORT_FORMAT_CSV,
REPORT_FORMAT_PDF,
]
REPORT_ALIGN_ALL = [
0, #left
1, #center
2, #right
3, #justify
]
REPORT_ALIGN_DEFAULT = 0
REFERENCE_FLAG_PASSWORD = 2
FAVICON_WIDTH = 16
FAVICON_HEIGHT = 16
PYTIME_FORMAT = '%Y-%m-%d %H:%M:%S'
PYTIME_DATE_FORMAT = '%Y-%m-%d'
PYTIME_TIME_FORMAT = '%H:%M:%S'
PYTIME_FORMAT_BACKUP = '%Y-%m-%d_%H-%M-%S'
REGEX_EMAIL = r'^[\w\.\+-]+@[\w\.-]+\.[a-zA-Z]+$'
DAYS_IN_YEAR = 365.2425
DAYS_IN_MONTH_AVERAGE = round( (DAYS_IN_YEAR / float(12)), 2)
DAYS_IN_MONTH_30 = 30
DAYS_IN_MONTH_31 = 31
CSV_SUFFIX = '.csv'
CSV_CTYPE = 'text/csv'
BACKUP_BUFFER = 10 * SIZE_KB
FILES_MAX_NUMBER = 10
FILES_MAX_SIZE = 1 * SIZE_MB
SCRIPTS_MAX_SIZE = 32 * SIZE_KB
SYSTEM_CONFIG_MAXSPLIT = 3
SYSTEM_CONFIG = (
(
'x_application',
'x_application_title',
'application.title.',
'application.title..%s' %(''),
'',
'striphtml',
0,
),
(
'x_files',
'x_max_files_number',
'files.max_number.',
'files.max_number..%s' %(FILES_MAX_NUMBER),
FILES_MAX_NUMBER,
int,
0,
),
(
'x_files',
'x_max_file_size',
'files.max_size.',
'files.max_size..%s' %(FILES_MAX_SIZE),
FILES_MAX_SIZE,
int,
0,
),
(
'x_scripts',
'x_max_script_size',
'scripts.max_size.',
'scripts.max_size..%s' %(SCRIPTS_MAX_SIZE),
SCRIPTS_MAX_SIZE,
int,
0,
),
(
'x_log',
'x_log_access',
'log.access.',
'log.access..%s' %(''),
'',
'log_init',
0,
),
(
'x_log',
'x_log_override',
'log.override.',
'log.override..%s' %(''),
'',
str,
1,
),
(
'x_users',
'x_user_defined_profile_ref',
'users.profile.',
'users.profile..%s' %(''),
'',
str,
1,
),
(
'x_messages',
'x_messages_all',
'messages.all.',
'messages.all..%s' %(''),
'',
'striphtml',
1,
),
(
'x_link',
'x_link_login',
'link.login.',
'link.login..%s' %(''),
'',
str,
1,
),
(
'x_website',
'x_website_notfound',
'website.notfound.',
'website.notfound..%s' %(''),
'',
'notfound_url_check',
0,
),
)
NOTFOUND_CHECK = [
'/fs',
]
REGEX_PAGE = (
(
r'~([^~]+)~',
r'<em>\1</em>',
True,
'd_page_emphasis',
'',
),
(
r'\*([^\*]+)\*',
r'<strong>\1</strong>',
True,
'd_page_strong',
'',
),
(
r'_([^_]+)_',
r'<u>\1</u>',
True,
'd_page_underline',
'',
),
(
r'\[([^\|]+)\|(\S+)\]',
r'<a href="\2">\1</a>',
True,
'd_page_link',
'',
),
(
r'\[form:(\S+)\]',
r'<a href="/form/run/\1">\1</a>',
True,
'd_page_form',
'canformrun',
),
(
r'\[FORM:(\S+)\]',
r'<a href="/form/run/\1">\1</a><br>',
True,
'd_page_form_new_line',
'canformrun',
),
(
r'\[report:(\S+)\]',
r'<a href="/report/run/\1">\1</a>',
True,
'd_page_report',
'canreportrun',
),
(
r'\[REPORT:(\S+)\]',
r'<a href="/report/run/\1">\1</a><br>',
True,
'd_page_report_new_line',
'canreportrun',
),
(
r'\[-\]',
r'<hr>',
True,
'd_page_hr',
'',
),
)
SAMPLE_PAGE = [x[3] for x in REGEX_PAGE]
CALCULATOR_MAX_INPUT = 36
CALCULATOR_ALLOWED = ''
URL_MAX_INPUT = CALCULATOR_MAX_INPUT
PATH_MAX_INPUT = 128
PLAIN_CTYPE = 'text/plain'
SCRIPT_KEY_NAME = 'name'
SCRIPT_KEY_INFO = 'info'
SCRIPT_KEY_AUTHOR = 'author'
SCRIPT_KEY_LICENSE = 'license'
SCRIPT_KEY_TABLES = 'tables'
SCRIPT_KEY_FORMS = 'forms'
SCRIPT_KEY_REPORTS = 'reports'
SCRIPT_KEY_PROFILES = 'profiles'
SCRIPT_REQ = (
SCRIPT_KEY_NAME,
SCRIPT_KEY_TABLES,
SCRIPT_KEY_FORMS,
SCRIPT_KEY_REPORTS,
)
SCRIPT_TABLE_COLUMN_LEN = 3
SCRIPT_TABLE_ERROR = -1
SCRIPT_TABLE_COLUMN_CONFLICT = -2
SCRIPT_TABLE_OK = 0
SCRIPT_TABLE_EXISTS = 1
SCRIPT_FORM_ERROR = -1
SCRIPT_FORM_OK = 0
SCRIPT_FORM_EXISTS = -2
SCRIPT_REPORT_ERROR = -1
SCRIPT_REPORT_OK = 0
SCRIPT_REPORT_EXISTS = -2
SCRIPT_VALID_COLUMN_FLAG = (
([], 0, ''), #none
([], 1, ''), #primary key
(['integer'], 2, 'primary key autoincrement'),
)
JSON_INDENT = 4
COPY_TARGET_EXCLUDE = [
FORM_TBL,
DEFAULT_TABLE,
SEQUENCE_TABLE,
]
EMPTY_EXCLUDE = [
FORM_TBL,
DEFAULT_TABLE,
SEQUENCE_TABLE,
]
IMPORT_EXCLUDE = [
FORM_TBL,
DEFAULT_TABLE,
SEQUENCE_TABLE,
]
TEMPLATE_SELECT_EXCLUDE = [
FORM_TBL,
DEFAULT_TABLE,
SEQUENCE_TABLE,
]
PRAGMA_FREELIST_COUNT = 'freelist_count'
SERVER_COMMAND_SEPARATOR = '-'
SERVER_COMMAND_ALL = {
'generate_favicon': 'scmd_favicon',
'generate_pyinstaller': 'scmd_pyinstaller',
'generate_build': 'scmd_build',
'generate_version': 'scmd_version',
'enable_extended': 'scmd_extended',
'enable_extended_allow_all': 'scmd_extended_allow_all',
'reset_admin_password': 'scmd_reset_admin_password',
'disable_log': 'scmd_disable_log',
'restore_log': 'scmd_restore_log',
}
SHORTCUT_TYPE_FORM = 'form'
SHORTCUT_TYPE_REPORT = 'report'
SHORTCUT_ALL = [
SHORTCUT_TYPE_FORM,
SHORTCUT_TYPE_REPORT,
]
PRINT_DATA_KEY = 'output_printer'
PRINT_DATA_VALUE = 1
IMPORT_ERROR_CODE = 255
GENERAL_ERROR_CODE = 254
PYINSTALLER_SPEC = '''
# $title $command
# $datetime
a = Analysis([r'$source_path'])
a.datas += [
('$source', r'$source_path', 'DATA'),
('$readme', r'$readme_path', 'DATA'),
]
pyz = PYZ(a.pure)
exe = EXE(
pyz,
a.scripts,
a.binaries,
a.datas,
name=r'$output',
icon=r'$icon',
console=True,
debug=False
)
#
import sys
import os
try:
from hashlib import md5
except ImportError:
from md5 import md5
#
content_version = """
$title $command
$datetime
"""
file_version = open(r'$output_version', 'w')
file_version.write(content_version)
file_version.close()
#
content = open(r'$output', 'rb').read()
content_md5 = md5(content).hexdigest()
content_lines = [
'%s %s' %(content_md5, r'$output'),
]
file_md5 = open(r'$output_md5', 'w')
for i in content_lines:
line = '%s' %(i)
file_md5.write(line)
file_md5.close()
#
try:
content_check = open(r'$output_md5', 'r').readlines()
content_check = [x.strip() for x in content_check if not x.startswith('#')]
content_check = [x for x in content_check if x]
content_check_md5 = content_check[0].split()[0].strip()
if content_check_md5 == content_md5:
sys.stdout.write('OK' + os.linesep)
except:
pass
'''
VERSION_SPEC = '''
$title $command
$datetime
'''
PROFILE_STYLE_ADD_ALIGN = '''
.pre
{
white-space : pre-wrap;
white-space : -moz-pre-wrap;
word-wrap : break-word;
}
.left
{
text-align : left;
}
.center
{
text-align : center;
}
.right
{
text-align : right;
}
.justify
{
text-align : justify;
}
'''
PROFILE_STYLE_PRINT = '''
*
{
font-family : Courier;
font-size : 12pt;
}
table
{
border-collapse : collapse;
width : 100%;
}
td
{
border : 1px solid #000000;
padding : 2px;
}
th
{
border : 1px solid #000000;
padding : 2px;
}
select
{
width : 95%;
}
.main_menu
{
display : none;
}
.messages
{
display : none;
}
''' + PROFILE_STYLE_ADD_ALIGN
PROFILE_ITEM_STYLE = [
[
PROFILE_STYLE_PRINT,
'''
*
{
font-family : Courier;
font-size : 12pt;
}
table
{
border-collapse : collapse;
width : 100%;
}
td
{
border : 1px solid #808080;
padding : 2px;
}
th
{
background-color: #406080;
border : 1px solid #808080;
padding : 2px;
color : #ffffff;
}
th a
{
color : #ffffff;
text-decoration : none;
}
tr:nth-child(odd)
{
background-color: #cccccc;
}
tr:nth-child(even)
{
background-color: #ffffff;
}
select
{
width : 95%;
}
.main_menu
{
}
a
{
color : #406080;
}
.messages
{
padding : 2px;
background-color: #cccccc;
border : 1px solid #808080;
}
table:not(.nohover) tr:hover
{
background-color: #ffffe0;
}
''' + PROFILE_STYLE_ADD_ALIGN
],
[
PROFILE_STYLE_PRINT,
'''
*
{
font-family : Courier;
font-size : 12pt;
}
table
{
border-collapse : collapse;
width : 100%;
}
td
{
border : 1px solid #ffcc66;
padding : 2px;
}
th
{
background-color: #996600;
border : 1px solid #ffcc66;
padding : 2px;
color : #ffffff;
}
th a
{
color : #ffffff;
text-decoration : none;
}
tr:nth-child(odd)
{
background-color: #f3e3c3;
}
tr:nth-child(even)
{
background-color: #ffffff;
}
select
{
width : 95%;
}
.main_menu
{
}
input, select, textarea
{
background-color: #ffffff;
color : #996600;
border : 1px solid #ffcc66;
margin : 2px;
}
a
{
color : #996600;
}
.messages
{
padding : 2px;
background-color: #f3e3c3;
border : 1px solid #ffcc66;
}
table:not(.nohover) tr:hover
{
background-color: #ffffe0;
}
''' + PROFILE_STYLE_ADD_ALIGN
],
[
PROFILE_STYLE_PRINT,
'''
*
{
font-family : Courier;
font-size : 12pt;
}
table
{
border-collapse : collapse;
width : 100%;
}
td
{
border : 1px solid #4169e1;
padding : 2px;
}
th
{
background-color: #00008b;
border : 1px solid #4169e1;
padding : 2px;
color : #ffffff;
}
th a
{
color : #ffffff;
text-decoration : none;
}
tr:nth-child(odd)
{
background-color: #b0e0e6;
}
tr:nth-child(even)
{
background-color: #ffffff;
}
select
{
width : 95%;
}
.main_menu
{
}
a
{
color : #00008b;
}
.messages
{
padding : 2px;
background-color: #b0e0e6;
border : 1px solid #4169e1;
}
table:not(.nohover) tr:hover
{
background-color: #ffffe0;
}
''' + PROFILE_STYLE_ADD_ALIGN
],
]
PROFILE_ALL = [
[
'style',
'x_style',
[
[0, 'A'],
[1, 'B'],
[2, 'C'],
],
2,
'pr_style',
int,
0,
],
[
'first_name',
'x_first_name',
[
],
'',
'pr_user',
str,
0,
],
[
'last_name',
'x_last_name',
[
],
'',
'pr_user',
str,
0,
],
[
'email',
'x_email',
[
],
'',
'pr_user',
str,
0,
],
[
'website',
'x_website',
[
],
'',
'pr_user',
str,
0,
],
]
PROFILE_USER_DEFINED_LEN = 4
PROFILE_USER_DEFINED_HANDLER = 'pr_user'
PROFILE_USER_DEFINED_TYPE = str
PROFILE_USER_DEFINED_LEVEL = 1
ENV_VAR_MAX = DEFAULT_VAR_MAX
QUERY_STRING_MAX = 1 * SIZE_KB
PDF_CTYPE = 'application/pdf'
PDF_SUFFIX = '.pdf'
RANDOM_SIMPLE_MIN = 10
RANDOM_SIMPLE_MAX = 100
LOG_TABLE = '%s_log' %(NAME)
LINKS_LOGIN_MAIN = 'login.main'
LINKS_LOGIN_EXTRA = 'login.extra'
HEADER_CONTENT = 'Content-Type'
DEFAULT_CONTENT = 'text/html'
PREFIX_REDIR_HTTP = 'http://'
PREFIX_REDIR_HTTPS = 'https://'
PREFIX_BLOG = 'blog:'
BLOG_SEPARATOR = ':'
#----------------------------------------------------------------------#
# MODULE #
#----------------------------------------------------------------------#
import sys
import os
if getattr(sys, 'frozen', None):
try:
CURDIR = sys._MEIPASS
except:
CURDIR = os.getcwd()
CWDIR = os.getcwd()
SCURDIR = CWDIR
else:
CURDIR = os.path.dirname(__file__)
CWDIR = CURDIR
SCURDIR = os.getcwd()
for i in [CWDIR, SCURDIR]:
if not i in sys.path:
sys.path.append(i)
sys.stdout = os.fdopen(sys.stdout.fileno(), 'w', 0)
sys.stderr = os.fdopen(sys.stderr.fileno(), 'w', 0)
import time
import decimal
import random
import string
FORM_VALID = [x for x in string.ascii_lowercase] + [x for x in string.digits]
FORM_VALID.append('_')
CALCULATOR_ALLOWED = string.digits + '.-+*/()'
URL_VALID = [x for x in string.ascii_lowercase]
PATH_VALID = [x for x in string.ascii_lowercase] + [x for x in string.digits]
PATH_VALID.append('_')
PATH_VALID.append('.')
PATH_VALID.append('/')
PATH_VALID.append('-')
import socket
try:
DEFAULT_HOSTS_ALLOWED.append(socket.gethostbyname(socket.gethostname()))
except:
pass
try:
from hashlib import md5
except ImportError:
from md5 import md5
import urllib
import hashlib
import base64
import platform
import struct
import re
import csv
try:
import cStringIO
except ImportError:
import StringIO as cStringIO
from HTMLParser import HTMLParser
import calendar
import copy
import traceback
try:
import reportlab
from reportlab.lib.enums import TA_LEFT as PDF_TA_LEFT
from reportlab.lib.enums import TA_CENTER as PDF_TA_CENTER
from reportlab.lib.enums import TA_RIGHT as PDF_TA_RIGHT
from reportlab.lib.enums import TA_JUSTIFY as PDF_TA_JUSTIFY
from reportlab.lib.colors import black as PDF_DEFAULT_BORDER_COLOR
from reportlab.lib.styles import getSampleStyleSheet as PDF_STYLE_SHEET
from reportlab.platypus import SimpleDocTemplate as PDF_TEMPLATE
from reportlab.platypus import Table as PDF_TABLE
from reportlab.platypus import Image as PDF_IMAGE
from reportlab.platypus import Spacer as PDF_SPACER
from reportlab.platypus import Paragraph as PDF_PARAGRAPH
from reportlab.lib.pagesizes import A4 as PDF_DEFAULT_PAGE_SIZE
from reportlab.lib.units import inch as PDF_UNIT_INCH
#
PDF_DEFAULT_BORDER_STYLE = [
(
'GRID',
(0, 0),
(-1, -1),
1,
PDF_DEFAULT_BORDER_COLOR,
),
]
PDF_DEFAULT_SPACER_WIDTH = 1
PDF_DEFAULT_SPACER_HEIGHT = 36
PDF_DEFAULT_PARAGRAPH_STYLE = PDF_STYLE_SHEET()['BodyText']
except ImportError:
PDF_TA_LEFT = 0
PDF_TA_CENTER = 0
PDF_TA_RIGHT = 0
PDF_TA_JUSTIFY = 0
PDF_TEMPLATE = None
PDF_TABLE = None
PDF_IMAGE = None
PDF_SPACER = None
PDF_PARAGRAPH = None
PDF_DEFAULT_PAGE_SIZE = ()
PDF_UNIT_INCH = 0
reportlab = None
if reportlab:
try:
from reportlab.pdfbase import _fontdata_enc_winansi
from reportlab.pdfbase import _fontdata_enc_macroman
from reportlab.pdfbase import _fontdata_enc_standard
from reportlab.pdfbase import _fontdata_enc_symbol
from reportlab.pdfbase import _fontdata_enc_zapfdingbats
from reportlab.pdfbase import _fontdata_enc_pdfdoc
from reportlab.pdfbase import _fontdata_enc_macexpert
from reportlab.pdfbase import _fontdata_widths_courier
from reportlab.pdfbase import _fontdata_widths_courierbold
from reportlab.pdfbase import _fontdata_widths_courieroblique
from reportlab.pdfbase import _fontdata_widths_courierboldoblique
from reportlab.pdfbase import _fontdata_widths_helvetica
from reportlab.pdfbase import _fontdata_widths_helveticabold
from reportlab.pdfbase import _fontdata_widths_helveticaoblique
from reportlab.pdfbase import _fontdata_widths_helveticaboldoblique
from reportlab.pdfbase import _fontdata_widths_timesroman
from reportlab.pdfbase import _fontdata_widths_timesbold
from reportlab.pdfbase import _fontdata_widths_timesitalic
from reportlab.pdfbase import _fontdata_widths_timesbolditalic
from reportlab.pdfbase import _fontdata_widths_symbol
from reportlab.pdfbase import _fontdata_widths_zapfdingbats
except:
pass
try:
import sqlite3
import json
import web
web.config.debug = False
ssl_cert = os.path.join(SCURDIR, DEFAULT_SSL_CERTIFICATE)
ssl_pkey = os.path.join(SCURDIR, DEFAULT_SSL_PRIVATE_KEY)
ssl_cert = os.path.abspath(ssl_cert)
ssl_pkey = os.path.abspath(ssl_pkey)
if os.path.exists(ssl_cert) and os.path.exists(ssl_pkey):
import OpenSSL
from web.wsgiserver import CherryPyWSGIServer
CherryPyWSGIServer.ssl_certificate = ssl_cert
CherryPyWSGIServer.ssl_private_key = ssl_pkey
except Exception, e:
lsep = os.linesep
emsg = '%s%s%s%s%s%s%s' %(
TITLE,
lsep,
APP_DESC,
lsep,
lsep,
str(e),
lsep
)
sys.stderr.write(emsg)
sys.exit(IMPORT_ERROR_CODE)
#----------------------------------------------------------------------#
# WEB #
#----------------------------------------------------------------------#
URLS = (
DEFAULT_HOME, 'home',
DEFAULT_INDEX, 'index',
'/favicon.ico', 'favicon_ico',
'/table/action', 'table_action',
'/table/browse/(.*)', 'table_browse',
'/table/column', 'table_column',
'/table/rename', 'table_rename',
'/table/empty', 'table_empty',
'/table/drop', 'table_drop',
'/table/export/csv', 'table_export_csv',
'/table/import/csv', 'table_import_csv',
'/table/schema', 'table_schema',
'/table/copy', 'table_copy',
'/table/create', 'table_create',
'/query', 'query',
'/vacuum', 'vacuum',
'/table/row/(.*)', 'table_row',
'/table/blob/(.*)', 'table_blob',
'/table/save', 'table_save',
FORM_URL_INIT, 'sqliteboy_init',
URL_README[0], URL_README[1],
URL_SOURCE[0], URL_SOURCE[1],
'/login', 'login',
'/logout', 'logout',
'/password', 'password',
'/admin/users', 'admin_users',
'/admin/hosts', 'admin_hosts',
'/admin/system', 'admin_system',
'/admin/backup', 'admin_backup',
'/admin/website', 'admin_website',
'/form/action', 'form_action',
'/form/run/(.*)', 'form_run',
'/form/shortcut/(.*)', 'form_shortcut',
'/form/edit', 'form_edit',
'/report/action', 'report_action',
'/report/run/(.*)', 'report_run',
'/report/shortcut/(.*)', 'report_shortcut',
'/report/edit', 'report_edit',
'/notes', 'notes',
'/files', 'files',
'/fs', 'fs',
'/pages', 'pages',
'/page/(.*)', 'page',
'/calculator', 'calculator',
'/admin/scripts', 'admin_scripts',
'/admin/script/(.*)', 'admin_script',
'/profile', 'profile',
'/info', 'info',
DEFAULT_CUSTOM, 'website',
)
URLS_RESERVED_DEFAULT = (
DEFAULT_HOME,
DEFAULT_CUSTOM,
)
URLS_RESERVED_REAL = [x for x in URLS if x.startswith('/') and x not in URLS_RESERVED_DEFAULT]
URLS_RESERVED = [x.replace('/(.*)', '') for x in URLS_RESERVED_REAL]
URLS_RESERVED.sort()
app = None
db = None
dbfile = ''
dbfile0 = ''
rendertime = [0, 0]
rowid = '_%s___%s___%s___%s_' %(
NAME,
ROWID,
random.randrange(0, 999999999),
int(time.time()),
)
#
sess = None
sess_init = {
'var': {},
'table': {},
'user': '',
'admin': 0,
'login_redirect': '',
}
#
style_align_default = {
0: ' class="left"',
1: ' class="center"',
2: ' class="right"',
3: ' class="justify"',
}
style_align_pdf = {
0: PDF_TA_LEFT,
1: PDF_TA_CENTER,
2: PDF_TA_RIGHT,
3: PDF_TA_JUSTIFY,
}
#----------------------------------------------------------------------#
# STRIPHTMLPARSER #
#----------------------------------------------------------------------#
class StripHTMLParser(HTMLParser):
def __init__(self):
self.reset()
self.text = []
def handle_data(self, data):
self.text.append(data)
#----------------------------------------------------------------------#
# MEMSESSION #
#----------------------------------------------------------------------#
class MemSession(web.session.Store):
data = {}
def __init__(self):
self.data = {}
def __len__(self):
return len(self.data.keys())
def __contains__(self, key):
return self.data.has_key(key)
def __getitem__(self, key):
if not self.data.has_key(key):
raise KeyError, key
#
v = self.data[key]
v[0] = time.time()
self.data[key] = v
return v[1]
def __setitem__(self, key, value):
v = [time.time(), value]
self.data[key] = v
def __delitem__(self, key):
if self.data.has_key(key):
del self.data[key]
def cleanup(self, timeout):
kdel = []
now = time.time()
#
for k in self.data.keys():
v = self.data[k]
if now - v[0] > timeout:
kdel.append(k)
#
for k in kdel:
if self.data.has_key(k):
del self.data[k]
#----------------------------------------------------------------------#
# NUMBER TO WORDS #
#----------------------------------------------------------------------#
NUMBER_TO_WORDS = {}
class NumberToWords:
def __init__(self):
self.word = {}
self.name1 = {}
self.name2 = {}
self.sign = {}
self.replace = {}
self.style = {}
self.data = {}
#
self.chunk_size = 3
def maxlength(self):
ret = 0
#
ln1 = len(self.name1.keys())
#
if ln1:
ret = ( ( 2 * ln1 ) - 1) * self.chunk_size
#
return ret
def separator(self):
return self.style.get('separator', ' ')
def decimal_separator(self):
return self.style.get('decimal_separator', ' ')
def is_number(self, s, check_length=True):
s = str(s)
#
ret = False
#
if check_length:
if len(s) > self.maxlength():
return False
#
try:
test = float(s)
ret = True
except:
pass
#
return ret
def is_negative(self, s):
ret = False
#
try:
test = float(s)
if test < 0:
ret = True
except:
pass
#
return ret
def split(self, s):
s = str(s).lower()
#
ret = ()
#
if not self.is_number(s, False):
return ret
#
if 'e' in s:
return ret
#
P1 = ''
P2 = ''
if '.' in s:
P1, P2 = s.split('.')
else:
P1 = s
P1 = P1.strip()
P2 = P2.strip()
#
P1 = P1.replace('+', '')
P1 = P1.replace('-', '')
#
if not self.is_number(P1):
return ret
#
ret = (P1, P2)
return ret
def chunk(self, s):
s = str(s)
#
n = self.chunk_size
#
mod = len(s) % n
if mod:
ln = len(s) + (n - mod)
s = s.rjust(ln, ' ')
#
ret = [s[i:i+n] for i in range(0, len(s), n)]
ret = [s.strip() for s in ret]
#
return ret
def get_single(self, s):
s = str(s)
#
res = []
#
for i in s:
x = self.word.get(i, '')
if x and i == '0':
x = x[0]
res.append(x)
#
ret = self.separator().join(res)
return ret
def get_1d(self, s):
s = str(s)
#
ret = ''
#
if not len(s) == 1:
return ret
#
r = self.word.get(s, '')
if r and s == '0':
r = r[1]
#
ret = r
return ret
def get_2d(self, s):
'''
override this
'''
s = str(s)
#
ret = ''
#
if not len(s) == 2:
return ret
#
return ret
def get_3d(self, s):
'''
override this
'''
s = str(s)
#
ret = ''
#
if not len(s) == 3:
return ret
#
return ret
def get_d(self, s):
s = str(s)
#
ls = len(s)
#
ret = ''
#
if ls == 1:
ret = self.get_1d(s)
elif ls == 2:
ret = self.get_2d(s)
elif ls == 3:
ret = self.get_3d(s)
#
return ret
def get_x1(self, c, s, separator):
'''
override this if needed
'''
s = str(s)
separator = str(separator)
#
ret = (s, separator)
return ret
def get_p1(self, p):
p = str(p)
#
nk = self.name1.keys()
mx = max(nk)
mxt = ''
if self.is_number(p) and long(p) > 0:
mxt = self.name1.get(mx, '')
#
lp1 = self.chunk(p)
if len(lp1) > mx:
ret = [
lp1[:-mx],
mxt,
lp1[-mx:],
]
else:
ret = [
lp1,
]
#
return ret
def words_p1(self, p):
ret = []
#
if not isinstance(p, list):
p = []
#
p = reversed(p)
for rp in p:
if isinstance(rp, str):
ret.append(rp)
continue
#
rp = reversed(rp)
c = 0
for i in rp:
rsep = self.separator()
#
r = self.get_d(i).strip()
r, rsep = self.get_x1(c, r, rsep)
#
x = self.name1.get(c, '')
if not int(i) == 0:
if not r or not x:
rsep = ''
z = rsep.join([r, x])
ret.append(z)
c += 1
#
return ret
def words_p2(self, p):
p = str(p)
#
return self.get_single(p)
def get_words(self, s):
ret = ''
#
neg = self.is_negative(s)
#
parts = self.split(s)
if not parts and len(parts) != 2:
return ret
#
p1 = parts[0]
p2 = parts[1]
#
c1 = self.get_p1(p1)
w1 = self.words_p1(c1)
w2 = self.words_p2(p2)
#
r1 = reversed(w1)
ss = self.sign.get(neg, '')
sr = self.separator().join(r1)
if not sr.strip():
sr = self.get_single('0')
#
z1 = self.separator().join([ss, sr])
z1 = z1.strip()
#
if w2:
ret = self.decimal_separator().join([z1, w2])
else:
ret = z1
#
return ret
class NumberToWordsId(NumberToWords):
def __init__(self):
NumberToWords.__init__(self)
#
self.word = {
'0': ('nol', ''),
'1': 'satu',
'2': 'dua',
'3': 'tiga',
'4': 'empat',
'5': 'lima',
'6': 'enam',
'7': 'tujuh',
'8': 'delapan',
'9': 'sembilan',
'10': 'sepuluh',
'11': 'sebelas',
}
#
self.name1 = {
0: '',
1: 'ribu',
2: 'juta',
3: 'milyar',
4: 'triliun',
}
#
self.name2 = {
1: '',
2: ('belas', 'puluh'),
3: ('ratus', 'seratus'),
}
#
self.sign = {
True: 'min',
False: '',
}
#
self.replace = {
1: ('satu', 'se'),
}
#
self.style = {
'separator': ' ',
'decimal_separator': ' koma ',
}
#
def get_2d(self, s):
s = str(s)
#
ret = ''
if not len(s) == 2:
return ret
#
res = []
rn = ''
r = self.word.get(s, '')
#
if r:
res = [r]
else:
res2 = []
rx = self.name2.get(2, ())
if s[0] == '1':
rn = rx[0]
r = self.get_1d(s[1])
res2 = [r, '']
elif s[0] == '0':
rn = ''
r = self.get_1d(s[1])
res2 = [r, '']
else:
rn = rx[1]
for i in s:
r = self.get_1d(i)
res2.append(r)
res = []
res.append(res2[0])
res.append(rn)
res.append(res2[1])
#
ret = self.separator().join(res)
return ret
def get_3d(self, s):
s = str(s)
#
ret = ''
if not len(s) == 3:
return ret
#
res = []
rn = ''
r = self.word.get(s, '')
#
if r:
res = [r]
else:
res2 = []
rx = self.name2.get(3, ())
if s[0] == '1':
rn = rx[1]
r = self.get_2d(s[1:])
res2 = [rn , r]
elif s[0] == '0':
rn = ''
r = self.get_2d(s[1:])
res2 = [r, rn]
else:
rn = rx[0]
r1 = self.get_1d(s[0])
r2 = self.get_2d(s[1:])
res2 = [r1, rn, r2]
res = [self.separator().join(res2)]
#
ret = self.separator().join(res)
return ret
def get_x1(self, c, s, separator):
for k in self.replace.keys():
if c == k:
xk = self.replace.get(k)
if s == xk[0]:
s = xk[1]
separator = ''
break
#
return [s, separator]
class NumberToWordsEn1(NumberToWords):
def __init__(self):
NumberToWords.__init__(self)
#
self.word = {
'0': ('zero', ''),
'1': 'one',
'2': 'two',
'3': 'three',
'4': 'four',
'5': 'five',
'6': 'six',
'7': 'seven',
'8': 'eight',
'9': 'nine',
'10': 'ten',
'11': 'eleven',
'12': 'twelve',
'13': 'thirteen',
'15': 'fifteen',
'18': 'eighteen',
'20': 'twenty',
'30': 'thirty',
'40': 'forty',
'50': 'fifty',
'60': 'sixty',
'70': 'seventy',
'80': 'eighty',
'90': 'ninety',
}
#
self.name1 = {
0: '',
1: 'thousand',
2: 'million',
3: 'billion',
4: 'trillion',
}
#
self.name2 = {
1: '',
2: ('teen', 'ty'),
3: 'hundred',
}
#
self.sign = {
True: 'minus',
False: '',
}
#
self.replace = {}
#
self.style = {
'separator': ' ',
'decimal_separator': ' point ',
'dash_separator': '-',
}
#
def get_2d(self, s):
s = str(s)
#
ret = ''
if not len(s) == 2:
return ret
#
res = []
rn = ''
r = self.word.get(s, '')
#
if r:
res = [r]
else:
rx = self.name2.get(2, ())
if s[0] == '1':
rn = rx[0]
r = self.get_1d(s[1])
res = [r, rn]
ret = ''.join(res)
elif s[0] == '0':
rn = ''
r = self.get_1d(s[1])
res = [r, '']
else:
rn = self.word.get(s[0] + '0')
r = self.get_1d(s[1])
res = []
res.append(rn)
res.append(self.style.get('dash_separator', ''))
res.append(r)
ret = ''.join(res)
#
if not ret:
ret = self.separator().join(res)
return ret
def get_3d(self, s):
s = str(s)
#
ret = ''
if not len(s) == 3:
return ret
#
res = []
rn = ''
r = self.word.get(s, '')
#
if r:
res = [r]
else:
res2 = []
if s[0] == '0':
rn = ''
r = self.get_2d(s[1:])
res2 = [r, rn]
else:
rn = self.name2.get(3, '')
r1 = self.get_1d(s[0])
r2 = self.get_2d(s[1:])
res2 = [r1, rn, r2]
res = [self.separator().join(res2)]
#
ret = self.separator().join(res)
return ret
NUMBER_TO_WORDS['id'] = NumberToWordsId
NUMBER_TO_WORDS['en'] = NumberToWordsEn1
NUMBER_TO_WORDS['en1'] = NumberToWordsEn1
#----------------------------------------------------------------------#
# PANGSIT DOMAIN-SPECIFIC PROGRAMMING LANGUAGE #
# * in development * #
#----------------------------------------------------------------------#
def pangsit(url_id, url, content, param, file_id,
file_name, file_type, file_content, detail):
def get_allowed_db_operation():
return ['select', 'insert', 'update', 'delete']
def get_height(user_interface):
temp = []
total = 0
#
for u in user_interface:
symbol = u[0]
extra = u[1]
#
if not symbol in temp:
temp.append(symbol)
total += extra
#
ret = 100 / total
return ret
def get_height_percent(height, n):
return n * height
def read_file():
lines = []
#
try:
f = cStringIO.StringIO(file_content)
#
reader = csv.reader(f)
for row in reader:
lines.append(row)
except:
pass
return lines
def get_user_interface_size(lines, start=0):
ret = [0, 0]
#
r = 0
for row in lines:
r += 1
if r <= start:
continue
#
c = 0
for col in row:
c += 1
if col.strip() == '.':
return [r - 1, c - 1]
#
return ret
def clean_up_user_interface(lines, width, height):
ret = []
#
for row in lines[:height]:
symbol = ''
for col in row[:width]:
if col.strip():
symbol = col
break
ret.append(symbol)
#
return ret
def get_user_interface_merge(lines, height):
ret = []
#
pos = []
c = 0
for symbol in lines:
if symbol.strip():
pos.append(c)
c += 1
#
length = len(pos)
for i in range(length):
p = pos[i]
#
temp = []
temp.append(lines[p])
#
if i < length - 1:
extra = pos[i+1] - p
else:
extra = height - p
temp.append(extra)
#
ret.append(temp)
#
return ret
def read_action(symbol, lines, row, column):
ret = []
#
for c in lines[row]:
if c:
ret.append(c)
#
if len(ret) > 1:
ret = ret[1:]
#
return ret
def read_symbol(symbol, lines, width, height):
ret = None
#
for r in range(len(lines)):
row = lines[r]
for c in range(len(row)):
if r > height or c > width:
col = row[c]
if col.lower() == symbol.lower():
ret = read_action(symbol, lines, r, c)
break
#
return ret
def get_symbols(lines, user_interface, width, height):
ret = {}
#
for temp in user_interface:
symbol = temp[0]
if not ret.has_key(symbol):
ret[symbol] = read_symbol(symbol, lines, width, height)
#
return ret
def interpret_var(symbol, value, symbols):
ret = value[0]
#
return ret
def interpret_if(symbol, value, symbols):
ret = ''
#
param1 = value[0]
param2 = value[1]
#
return ret
def interpret_command(symbol, value, symbols):
ret = ''
#
param1 = value[0]
param2 = value[1]
param3 = value[2]
#
if param2 == '1': #HTML: img
ret = '<img src="%s" alt="%s">' %(param1, param1)
elif param2 == '21': #HTML: h1
ret = '<h1>%s</h1>' %(param1)
elif param2 == '22': #HTML: h2
ret = '<h2>%s</h2>' %(param1)
elif param2 == '23': #HTML: h3
ret = '<h3>%s</h3>' %(param1)
#
return ret
def interpret_loop(symbol, value, symbols):
ret = ''
#
param1 = value[0]
param2 = value[1]
param3 = value[2]
param4 = value[3]
#
return ret
def interpret_db(symbol, value, symbols):
ret = ''
#
param1 = value[0]
param2 = value[1]
param3 = value[2]
param4 = value[3]
param5 = value[4]
param6 = value[5]
#
param2 = param2.lower()
if not param2 in get_allowed_db_operation():
return ret
#
if param2 == 'select':
try:
r = db.select(param1,
what=param5, where=param3, order=param4)
if r:
r = list(r)
r = r[0]
if param6 == '1': #header/text
ret = '<h3>%s</h3><p>%s</p>' %(r['a'], r['b'])
elif param6 == '2': #link
ret = '<a href="%s">%s</a>' %(r['a'], r['b'])
return ret
except:
return ret
#
return ret
def interpret_action(symbol, symbols):
ret = ''
#
if not symbol in symbols.keys():
return ret
#
val = symbols.get(symbol, [])
if not isinstance(val, list):
return ret
#
if len(val) == 1:
ret = interpret_var(symbol, val, symbols)
elif len(val) == 2:
ret = interpret_if(symbol, val, symbols)
elif len(val) == 3:
ret = interpret_command(symbol, val, symbols)
elif len(val) == 4:
ret = interpret_loop(symbol, val, symbols)
elif len(val) == 6:
ret = interpret_db(symbol, val, symbols)
#
return ret
def interpret_code(user_interface, symbols):
height = get_height(user_interface)
#
ret = '''<!DOCTYPE html>
<html>
<head>
<title></title>
<style>
html, body {
height : 100%;
margin : 0;
}
'''
#
for u in user_interface:
ret += '''
.%s {
width : 100%%;
height : %s%%;
margin : 4px;
padding : 4px;
}
''' %(u[0], get_height_percent(height, u[1]))
#
ret += '''
</style>
</head>
<body>
'''
#
for u in user_interface:
ret += '''
<div class="%s">
%s
</div>
''' %(u[0], interpret_action(u[0], symbols))
#
ret += '''
</body>
</html>
'''
#
return ret
#
#
lines = read_file()
#
width = get_user_interface_size(lines)[1]
height = get_user_interface_size(lines, width)[0]
lines_clean = clean_up_user_interface(lines, width, height)
#
user_interface = get_user_interface_merge(lines_clean, height)
if not user_interface:
raise Exception
#
symbols = get_symbols(lines, user_interface, width, height)
#
return interpret_code(user_interface, symbols)
#----------------------------------------------------------------------#
# SIMPLEDROPDOWN #
#----------------------------------------------------------------------#
class SimpleDropdown(web.form.Dropdown):
def __init__(self, name, args, *validators, **attrs):
self.args = args
super(SimpleDropdown, self).__init__(name, args, *validators, **attrs)
def _render_option(self, arg, indent=' '):
if isinstance(arg, (tuple, list)):
value, desc= arg
else:
value, desc = arg, arg
#lines below are modified by sqliteboy author:
#- convert to str
#- web.net
#- web.utils.safestr
#- ref: webpy pull request #279
value = web.utils.safestr(value)
if isinstance(self.value, (tuple, list)):
s_value = [web.utils.safestr(x) for x in self.value]
else:
s_value = web.utils.safestr(self.value)
if s_value == value or (isinstance(s_value, list) and value in s_value):
select_p = ' selected="selected"'
else:
select_p = ''
return indent + '<option%s value="%s">%s</option>\n' % (select_p, web.net.websafe(value), web.net.websafe(desc))
#----------------------------------------------------------------------#
# LANG #
#----------------------------------------------------------------------#
LANGS = {
'default':
{
'usage': '<database_file> [port]',
's_field_simple': '; ',
'pf_b' : 'B',
'pf_kb': 'KB',
'pf_mb': 'MB',
'pf_gb': 'GB',
'a_local': 'local',
'a_all': 'all',
'a_custom': 'custom',
'x_id': 'id',
'x_welcome': 'welcome',
'x_welcome2': 'welcome to',
'x_allow': 'allow',
'x_table': 'table',
'x_second': 'second(s)',
'x_row' : 'row(s)',
'x_limit': 'limit',
'x_page': 'page',
'x_next': 'next',
'x_previous': 'previous',
'x_unlimited': 'unlimited',
'x_selected': 'selected',
'x_default': 'default',
'x_name': 'name',
'x_type': 'type',
'x_primary_key': 'primary key',
'x_optional': 'optional',
'x_rename': 'rename to',
'x_empty': 'empty',
'x_column_number': 'number of column',
'x_column': 'column(s)',
'x_table_name': 'table name',
'x_yes': 'yes',
'x_no': 'no',
'x_enabled': 'enabled',
'x_required': 'required',
'x_not_enabled': 'not enabled',
'x_version': 'version',
'x_sqlite_version': 'SQLite version',
'x_web_version': 'web.py version',
'x_python_version': 'Python version',
'x_reportlab_version': 'ReportLab version',
'x_extended_features': 'extended features',
'x_user': 'user',
'x_users': 'users',
'x_delete': 'delete',
'x_password': 'password',
'x_admin': 'admin',
'x_note': 'note',
'x_password_old': 'old password',
'x_password_new': 'new password',
'x_password_new_repeat': 'repeat new password',
'x_added': 'added',
'x_deleted': 'deleted',
'x_exists': 'exists',
'x_invalid': 'invalid',
'x_reserved': 'reserved',
'x_password_changed': 'password changed',
'x_admin_changed': 'admin changed',
'x_not_applicable': 'not applicable',
'x_form': 'form',
'x_code': 'code',
'x_form_name': 'form name',
'x_report': 'report',
'x_report_name': 'report name',
'x_title': 'title',
'x_content': 'content',
'x_action': 'action',
'x_key': 'key',
'x_value': 'value',
'x_section': 'section',
'x_files': 'files',
'x_max_files_number': 'maximum number of files per user',
'x_max_files_number_error': 'maximum number of files per user exceeded',
'x_max_file_size': 'maximum file size',
'x_max_file_size_error': 'maximum file size exceeded',
'x_file_name': 'file name',
'x_file_size': 'size',
'x_database_size': 'size of database file',
'x_unused_pages': 'number of unused pages',
'x_shared': 'shared',
'x_preview': 'preview',
'x_expression_too_long': 'expression too long',
'x_expression_invalid': 'invalid expression',
'x_info': 'info',
'x_author': 'author',
'x_license': 'license',
'x_run_time': 'run (time)',
'x_scripts': 'scripts',
'x_max_script_size': 'maximum script size',
'x_detail': 'detail',
'x_system_check': 'system check',
'x_table_exists': 'table already exists, however, additional column(s) will be added',
'x_script_not_runnable': 'could not run this script because nothing is defined, or error(s) found, or has been run before',
'x_copy_to': 'to',
'x_copy_from': 'from',
'x_copy_columns_none': 'no identical column found',
'x_sqliteboy_x_update': 'updating %s table, please wait...' %(FORM_TBL),
'x_sqliteboy_x_update_files': 'updating %s table (files), please wait...' %(FORM_TBL),
'x_please_wait': 'please wait...',
'x_server_command_mode': 'server command mode',
'x_style': 'style',
'x_first_name': 'first name',
'x_last_name': 'last name',
'x_email': 'email',
'x_website': 'website',
'x_website_notfound': 'custom not found URL (not logged in only, valid URL according to Website and Custom URL Reference)',
'x_session': 'session(s)',
'x_user_defined_profile': 'user-defined profile',
'x_user_defined_profile_ref': 'user-defined profile (please read User-defined Profile Reference)',
'x_profile': 'profile',
'x_create_table_schema': 'create table based on this schema',
'x_messages': 'messages',
'x_messages_all': 'welcome messages or menu for all users (please read Page Code Reference)',
'x_application': 'application',
'x_application_title': 'title (maximum %s characters, please read Title Reference)' %(APPLICATION_TITLE_MAX),
'x_not_avail_pdf': 'not available, PDF output will be disabled',
'x_log': 'logs',
'x_log_access': 'access log path (absolute, forward slash / for separator, will be verified on save or empty string if verification failed, use current database might impact the database)',
'x_log_override': 'log only specific URLs (please start each URL with /, separate URLs with whitespace, will discard everything else)',
'x_link': 'links',
'x_link_login': 'additional/custom links at login page (please read Link Code Reference)',
'x_url': 'url',
'tt_info': 'info',
'tt_insert': 'insert',
'tt_edit': 'edit',
'tt_column': 'column',
'tt_rename': 'rename',
'tt_empty': 'empty',
'tt_drop': 'drop',
'tt_query': 'query',
'tt_create': 'create',
'tt_readme': 'readme',
'tt_source': 'source',
'tt_login': 'login',
'tt_password': 'password',
'tt_users': 'users',
'tt_hosts': 'hosts',
'tt_system': 'system',
'tt_form_run': 'form run',
'tt_form_edit': 'form edit',
'tt_form_create': 'form create',
'tt_report_run': 'report run',
'tt_report_edit': 'report edit',
'tt_report_create': 'report create',
'tt_report_run_result': 'report run (result)',
'tt_notes': 'notes',
'tt_files': 'files',
'tt_pages': 'page',
'tt_calculator': 'calculator',
'tt_scripts': 'scripts',
'tt_script': 'script',
'tt_import_csv': 'import',
'th_error': 'ERROR',
'th_ok': 'OK',
'tt_copy': 'copy',
'tt_vacuum': 'vacuum',
'tt_profile': 'profile',
'tt_schema': 'schema',
'tt_website': 'website',
'cmd_browse': 'browse',
'cmd_insert': 'insert',
'cmd_column': 'column',
'cmd_rename': 'rename',
'cmd_table_empty': 'empty',
'cmd_table_drop': 'drop',
'cmd_export_csv': 'export',
'cmd_import_csv': 'import',
'cmd_copy': 'copy',
'cmd_table_create': 'create',
'cmd_query': 'query',
'cmd_query_export_csv': 'query (export)',
'cmd_query_src': 'query',
'cmd_delete_selected': 'delete selected',
'cmd_edit_selected': 'edit selected',
'cmd_clear_selected': 'clear selected',
'cmd_download': 'download',
'cmd_edit': 'edit',
'cmd_add': 'add',
'cmd_next': 'next',
'cmd_enable_sqliteboy': 'create %s table and enable extended features' %(FORM_TBL),
'cmd_readme': 'readme',
'cmd_source': 'source',
'cmd_website': 'website',
'cmd_website_admin': 'website',
'cmd_login': 'login',
'cmd_logout': 'logout',
'cmd_password': 'password',
'cmd_notes': 'notes',
'cmd_files': 'files',
'cmd_pages': 'page',
'cmd_info': 'info',
'cmd_home': 'home',
'cmd_calculator': 'calculator',
'cmd_calculate': 'calculate',
'cmd_users': 'users',
'cmd_hosts': 'hosts',
'cmd_system': 'system',
'cmd_backup': 'backup',
'cmd_save': 'save',
'cmd_run': 'run',
'cmd_form_create': 'create',
'cmd_report_create': 'create',
'cmd_report': 'report',
'cmd_view': 'view',
'cmd_use_result': 'use the result',
'cmd_scripts': 'scripts',
'cmd_vacuum': 'vacuum',
'cmd_go': 'go',
'cmd_go_print': 'go (print)',
'cmd_csv': 'csv',
'cmd_pdf': 'pdf',
'cmd_shortcut': 'shortcut',
'cmd_profile': 'profile',
'cmd_schema': 'schema',
'cmd_hide': 'hide',
'cmd_show': 'show',
'cf_delete_selected': 'are you sure you want to delete selected row(s)?',
'cf_drop': 'confirm drop table',
'cf_empty': 'confirm empty table',
'cf_vacuum': 'confirm vacuum database',
'd_page_emphasis': '~text~ -> <em>text</em>',
'd_page_strong': '*text* -> <strong>text</strong>',
'd_page_underline': '_text_ -> <u>text</u>',
'd_page_link': '[text|url] -> <a href="url">text</a>',
'd_page_form': '[form:name] -> link to run form (or empty string if the form is not available)',
'd_page_form_new_line': '[FORM:name] -> link to run form, followed by a line break (or empty string if the form is not available)',
'd_page_report': '[report:name] -> link to run report (or empty string if the report is not available)',
'd_page_report_new_line': '[REPORT:name] -> link to run report, followed by a line break (or empty string if the report is not available)',
'd_page_hr': '[-] -> <hr>',
'e_db_static': 'ERROR: database file must not be placed in static directory',
'e_notfound': 'ERROR 404: the page you are looking for is not found',
'e_internalerror': 'ERROR 500: internal server error',
'e_access_forbidden': 'access forbidden',
'e_connect': 'ERROR: unable to connect to',
'e_insert': 'ERROR: insert into table',
'e_edit': 'ERROR: update table',
'e_rename': 'ERROR: alter table (rename)',
'e_empty': 'ERROR: empty table',
'e_drop': 'ERROR: drop table',
'e_table_exists': 'ERROR: table already exists',
'e_open_file': 'ERROR: open file',
'e_login': 'ERROR: unknown user or incorrect password',
'e_password_general': 'ERROR: could not change password',
'e_password_auth': 'ERROR: authentication failed',
'e_password_mismatch': 'ERROR: passwords mismatch',
'e_password_blank': 'ERROR: please enter new password',
'e_hosts': 'ERROR: could not update hosts',
'e_system': 'ERROR: could not update system configuration',
'e_form_edit_whitespace': 'ERROR: could not handle form with whitespace in name',
'e_form_edit_exists': 'ERROR: form already exists',
'e_form_edit_syntax' : 'ERROR: form code error',
'e_form_edit_name': 'ERROR: invalid form name',
'e_form_run_syntax_or_required': 'ERROR: form code error or required keys are not set',
'e_form_run_required': 'ERROR: required',
'e_form_run_constraint': 'ERROR: constraint',
'e_form_run_onsave': 'ERROR: onsave',
'e_form_run_subform': 'ERROR: subform',
'e_form_insert_general': 'ERROR: processing form',
'e_report_edit_whitespace': 'ERROR: could not handle report with whitespace in name',
'e_report_edit_exists': 'ERROR: report already exists',
'e_report_edit_syntax' : 'ERROR: report code error',
'e_report_edit_name': 'ERROR: invalid report name',
'e_report_run_syntax_or_required': 'ERROR: report code error or required keys are not set',
'e_report_run_required': 'ERROR: required',
'e_report_run_constraint': 'ERROR: constraint',
'e_report_select_general': 'ERROR: processing report',
'e_scripts_max_size': 'ERROR: maximum script size exceeded',
'e_scripts_syntax_or_required' : 'ERROR: script code error or required keys are not set',
'e_scripts_name': 'ERROR: invalid script name',
'e_script_column_conflict': 'ERROR: table already exists and conflicted column(s) found',
'e_script': 'ERROR: script run',
'e_copy': 'ERROR: copy table',
'e_import_csv': 'ERROR: import csv',
'e_profile': 'ERROR: could not update profile',
'o_insert': 'OK: insert into table',
'o_edit': 'OK: update table',
'o_column': 'OK: alter table (column)',
'o_rename': 'OK: alter table (rename)',
'o_password': 'OK: password changed',
'o_hosts': 'OK: hosts updated',
'o_system': 'OK: system configuration updated',
'o_form_run': 'OK: form run',
'o_form_create': 'OK: create form',
'o_report_create': 'OK: create report',
'o_notes': 'OK: notes updated',
'o_files': 'OK: files updated',
'o_pages': 'OK: page updated',
'o_scripts': 'OK: scripts updated',
'o_script': 'OK: script run',
'o_table_create': 'OK: create table',
'o_drop': 'OK: drop table',
'o_copy': 'OK: copy table',
'o_empty': 'OK: empty table',
'o_vacuum': 'OK: vacuum database',
'o_import_csv': 'OK: import csv',
'o_profile': 'OK: profile updated',
'o_profile_set': 'OK: profile set',
'o_website': 'OK: website updated',
'h_insert': 'hint: leave blank to use default value (if any)',
'h_edit': 'hint: for blob field, leave blank = do not update',
'h_column': 'hint: only add column is supported in SQLite. Primary key/unique is not allowed in column addition. Default value(s) must be constant.',
'h_rename': '',
'h_empty': '',
'h_drop': '',
'h_query': 'hint: only one statement at a time is supported',
'h_create': 'hint: please do not put whitespace in table name',
'h_create2': 'hint: for multiple primary keys, do not select type contains "primary key", use primary key column instead. For date/time type, please use integer. If date/time default is needed, please use current_time, current_date or current_timestamp. To use non-constant literally, please surround with quote(\'), for example \'current_time\'.',
'h_users': 'hint: only valid value(s) will be updated. You could not delete yourself or update your admin level. New username must be unique, must not contain whitespace and will be lowercased.',
'h_hosts': 'hint: for custom hosts, please use whitespace separated format',
'h_system': '',
'h_form_create': 'hint: please do not put whitespace in form name. Form name must be alphanumeric/underscore and will be converted to lowercase. Form code in JSON format. Please read <a href="%s">README</a> for form code reference.' %(URL_README[0]),
'h_form_run': '',
'h_report_create': 'hint: please do not put whitespace in report name. Report name must be alphanumeric/underscore and will be converted to lowercase. Report code in JSON format. Please read <a href="%s">README</a> for report code reference.' %(URL_README[0]),
'h_report_run': '',
'h_notes': '',
'h_files': '',
'h_pages': 'hint: HTML tags will be stripped on page save. Please read <a href="%s">README</a> for page code reference. For example: ' %(URL_README[0]),
'h_calculator': 'hint: valid characters: %s. Maximum length: %s.' %(CALCULATOR_ALLOWED, CALCULATOR_MAX_INPUT),
'h_scripts': 'hint: script code in JSON format. Please read <a href="%s">README</a> for script code reference.' %(URL_README[0]),
'h_script': 'hint: only valid value(s) will be read. Script could not be run if there is error. Backup before running a script is recommended.',
'h_copy': 'hint: copy content of source table to existing destination table (insert), only for identical column(s) (name and type)',
'h_vacuum': 'hint: vacuum command will rebuild the entire database and may reduce the size of database file. Please make sure there is enough free space, at least twice the size of the original database file. This command may change the rowids of rows in any tables that do not have an explicit integer primary key column.',
'h_import_csv': 'hint: import CSV file (Excel dialect) into table (insert). First row will be read as column(s).',
'h_profile': '',
'h_website': 'hint: Please read <a href="%s">README</a> for Website and custom URL reference. Only valid values are saved (id and url are checked on save). Id must be alphabetic only (maximum length: %s) and will be converted to lowercase. URL must be alphanumeric/underscore/dot/slash/dash (maximum length: %s) and will be converted to lowercase. Please start url with / (but do not end it with /), and use / for home page. Content is interpreted and handled based on value (HTML, template, files, redirect, python handler; as documented). Custom not found URL can be set at <a href="/admin/system">system configuration</a>. Reserved URLs (%s, subject to change): %s' %(URL_README[0], URL_MAX_INPUT, PATH_MAX_INPUT, len(URLS_RESERVED), ', '.join(URLS_RESERVED)),
'z_table_whitespace': 'could not handle table with whitespace in name',
'z_view_blob': '[blob data]',
'z_edit_blob_column': 'could not edit this row: blob data in non-blob column',
},
}
def res(all, type, default=DEFAULT_LANG):
if not all.has_key(type):
return all[default]
#
ret = all[type]
for k in all[default].keys():
if not ret.has_key(k):
ret[k] = all[default][k]
#
return ret
_ = res(LANGS, DEFAULT_LANG)
#----------------------------------------------------------------------#
# SQLITE UDF #
#----------------------------------------------------------------------#
SQLITE_UDF = []
def sqliteboy_strs(s):
vt = [type(''), type(u'')]
if not type(s) in vt:
s = str(s)
#
return s
SQLITE_UDF.append(('sqliteboy_strs', 1, sqliteboy_strs))
def sqliteboy_as_integer(s):
s = str(s)
#
ret = 0
#
try:
ret = int(s)
if abs(ret) > sys.maxint:
ret = 0
else:
ret = int(ret)
except:
pass
#
return ret
SQLITE_UDF.append(('sqliteboy_as_integer', 1, sqliteboy_as_integer))
def sqliteboy_as_float(s):
s = str(s)
#
ret = 0
#
try:
ret = float(s)
except:
pass
#
return ret
SQLITE_UDF.append(('sqliteboy_as_float', 1, sqliteboy_as_float))
def sqliteboy_len(s):
return len(str(s))
SQLITE_UDF.append(('sqliteboy_len', 1, sqliteboy_len))
def sqliteboy_md5(s):
return md5(str(s)).hexdigest()
SQLITE_UDF.append(('sqliteboy_md5', 1, sqliteboy_md5))
def sqliteboy_sha1(s):
return hashlib.sha1(str(s)).hexdigest()
SQLITE_UDF.append(('sqliteboy_sha1', 1, sqliteboy_sha1))
def sqliteboy_sha224(s):
return hashlib.sha224(str(s)).hexdigest()
SQLITE_UDF.append(('sqliteboy_sha224', 1, sqliteboy_sha224))
def sqliteboy_sha256(s):
return hashlib.sha256(str(s)).hexdigest()
SQLITE_UDF.append(('sqliteboy_sha256', 1, sqliteboy_sha256))
def sqliteboy_sha384(s):
return hashlib.sha384(str(s)).hexdigest()
SQLITE_UDF.append(('sqliteboy_sha384', 1, sqliteboy_sha384))
def sqliteboy_sha512(s):
return hashlib.sha512(str(s)).hexdigest()
SQLITE_UDF.append(('sqliteboy_sha512', 1, sqliteboy_sha512))
def sqliteboy_b64encode(s):
return base64.b64encode(str(s))
SQLITE_UDF.append(('sqliteboy_b64encode', 1, sqliteboy_b64encode))
def sqliteboy_b64decode(s):
return base64.b64decode(str(s))
SQLITE_UDF.append(('sqliteboy_b64decode', 1, sqliteboy_b64decode))
def sqliteboy_randrange(a, b):
vt = [type(1), type(1L)]
if not type(a) in vt or not type(b) in vt: return 0
if a == b: return a
#
return random.randrange(a, b)
SQLITE_UDF.append(('sqliteboy_randrange', 2, sqliteboy_randrange))
def sqliteboy_randstr(s, a, b):
ret = ''
#
s = str(s)
if not isinstance(a, int) or not isinstance(b, int):
return ret
if a<=0 or b<=0:
return ret
if b<a:
return ret
#
if b==a:
length = a
else:
length_choice = xrange(a, b)
length = random.choice(length_choice)
#
for i in xrange(length):
r = random.choice(s)
ret += r
#
return ret
SQLITE_UDF.append(('sqliteboy_randstr', 3, sqliteboy_randstr))
def sqliteboy_randstr2(a, b):
s = string.letters + string.digits + string.punctuation
return sqliteboy_randstr(s, a, b)
SQLITE_UDF.append(('sqliteboy_randstr2', 2, sqliteboy_randstr2))
def sqliteboy_randstr3(a, b):
s = string.letters + string.digits
return sqliteboy_randstr(s, a, b)
SQLITE_UDF.append(('sqliteboy_randstr3', 2, sqliteboy_randstr3))
def sqliteboy_randstr_simple():
return sqliteboy_randstr3(RANDOM_SIMPLE_MIN, RANDOM_SIMPLE_MAX)
SQLITE_UDF.append(('sqliteboy_randstr_simple', 0, sqliteboy_randstr_simple))
def sqliteboy_is_datetime_format(s, fmt):
ret = 0
#
try:
s = s.strip()
p = time.strptime(s, fmt)
ret = 1
except:
pass
#
return ret
SQLITE_UDF.append(('sqliteboy_is_datetime_format', 2, sqliteboy_is_datetime_format))
def sqliteboy_is_datetime(s):
return sqliteboy_is_datetime_format(s, PYTIME_FORMAT)
SQLITE_UDF.append(('sqliteboy_is_datetime', 1, sqliteboy_is_datetime))
def sqliteboy_is_date(s):
return sqliteboy_is_datetime_format(s, PYTIME_DATE_FORMAT)
SQLITE_UDF.append(('sqliteboy_is_date', 1, sqliteboy_is_date))
def sqliteboy_is_time(s):
return sqliteboy_is_datetime_format(s, PYTIME_TIME_FORMAT)
SQLITE_UDF.append(('sqliteboy_is_time', 1, sqliteboy_is_time))
def sqliteboy_time():
return time.time()
SQLITE_UDF.append(('sqliteboy_time', 0, sqliteboy_time))
def sqliteboy_time2(s):
try:
s = s.strip()
return time.mktime(time.strptime(s, PYTIME_FORMAT))
except:
return 0
SQLITE_UDF.append(('sqliteboy_time2', 1, sqliteboy_time2))
def sqliteboy_time3(f):
try:
return time.strftime(PYTIME_FORMAT, time.localtime(f))
except:
return ''
SQLITE_UDF.append(('sqliteboy_time3', 1, sqliteboy_time3))
def sqliteboy_time3a():
return sqliteboy_time3(sqliteboy_time())
SQLITE_UDF.append(('sqliteboy_time3a', 0, sqliteboy_time3a))
def sqliteboy_time4(f):
try:
return time.strftime(PYTIME_FORMAT, time.gmtime(f))
except:
return ''
SQLITE_UDF.append(('sqliteboy_time4', 1, sqliteboy_time4))
def sqliteboy_time4a():
return sqliteboy_time4(sqliteboy_time())
SQLITE_UDF.append(('sqliteboy_time4a', 0, sqliteboy_time4a))
def sqliteboy_time5(s1, s2, mode):
s1 = str(s1)
s2 = str(s2)
if not sqliteboy_is_integer(mode):
mode = 0
#
tnow = sqliteboy_time()
if not s1.strip() or not sqliteboy_is_datetime(s1):
s1 = sqliteboy_time3(tnow)
if not s2.strip() or not sqliteboy_is_datetime(s2):
s2 = sqliteboy_time3(tnow)
#
t1 = sqliteboy_time2(s1)
t2 = sqliteboy_time2(s2)
#
d = t2 - t1
d = float(d)
#
ret = 0
#
if mode == 1: #second
ret = d
elif mode == 2: #minute
ret = d / (60)
elif mode == 3: #hour
ret = d / (60 * 60)
elif mode == 4: #day
ret = d / (60 * 60 * 24)
elif mode == 5: #year
ret = d / (60 * 60 * 24 * DAYS_IN_YEAR)
#
return ret
SQLITE_UDF.append(('sqliteboy_time5', 3, sqliteboy_time5))
def sqliteboy_time6(f, year, month, day, mode):
ret = ''
#
try:
f = float(f)
except:
return ret
#
year = str(year)
month = str(month)
day = str(day)
#
if not sqliteboy_is_integer(mode) or mode < 0:
mode = 0
#
if 'e' in str(f).lower():
return ret
#
f1, f2 = str(f).split('.')
try:
y = int(f1)
except:
return ret
#
leftm = f - y
fm = float(leftm * 12)
m1, m2 = str(fm).split('.')
try:
m = int(round(float(m1), 0))
except:
return ret
#
leftd = fm - m
d = int(round(leftd * DAYS_IN_MONTH_AVERAGE, 0))
d30 = int(round(leftd * DAYS_IN_MONTH_30, 0))
d31 = int(round(leftd * DAYS_IN_MONTH_31, 0))
#
dd = d
if mode == 1:
dd = d30
elif mode == 2:
dd = d31
#
ret = '%s%s%s%s%s%s' %(
y,
year,
m,
month,
dd,
day
)
#
return ret
SQLITE_UDF.append(('sqliteboy_time6', 5, sqliteboy_time6))
def sqliteboy_is_leap(n):
ret = 0
#
if sqliteboy_is_integer(n):
ret = calendar.isleap(n)
#
return ret
SQLITE_UDF.append(('sqliteboy_is_leap', 1, sqliteboy_is_leap))
def sqliteboy_lower(s):
s = sqliteboy_strs(s)
return s.lower()
SQLITE_UDF.append(('sqliteboy_lower', 1, sqliteboy_lower))
def sqliteboy_upper(s):
s = sqliteboy_strs(s)
return s.upper()
SQLITE_UDF.append(('sqliteboy_upper', 1, sqliteboy_upper))
def sqliteboy_swapcase(s):
s = str(s)
return s.swapcase()
SQLITE_UDF.append(('sqliteboy_swapcase', 1, sqliteboy_swapcase))
def sqliteboy_capitalize(s, what):
s = str(s)
if not sqliteboy_is_integer(what) or what < 0:
what = 0
#
if what == 0: #first
return s.capitalize()
else:
return string.capwords(s)
SQLITE_UDF.append(('sqliteboy_capitalize', 2, sqliteboy_capitalize))
def sqliteboy_justify(s, justify, length, padding):
s = str(s)
if not sqliteboy_is_integer(justify) or justify < 0:
justify = 0
#
padding = str(padding)
if padding:
padding = padding[0]
else:
padding = ' '
#
ls = len(s)
if not sqliteboy_is_integer(length) or length < 0 or length < ls:
length = ls
#
ret = s
#
if justify == 0: #left
ret = s.ljust(length, padding)
elif justify == 1: #right
ret = s.rjust(length, padding)
elif justify == 2: #center
ret = s.center(length, padding)
#
return ret
SQLITE_UDF.append(('sqliteboy_justify', 4, sqliteboy_justify))
def sqliteboy_find(s, sub, position, case):
s = str(s)
sub = str(sub)
if not sqliteboy_is_integer(position) or position < 0:
position = 0
#
if not sqliteboy_is_integer(case) or case < 0:
case = 0
if not case: #ignore case
s = s.lower()
sub = sub.lower()
#
ret = -1
#
if position == 0: #left
ret = s.find(sub)
elif position == 1: #right
ret = s.rfind(sub)
#
return ret
SQLITE_UDF.append(('sqliteboy_find', 4, sqliteboy_find))
def sqliteboy_reverse(s):
s = str(s)
#
return s[::-1]
SQLITE_UDF.append(('sqliteboy_reverse', 1, sqliteboy_reverse))
def sqliteboy_repeat(s, n):
s = str(s)
#
if not sqliteboy_is_integer(n) or n < 1:
n = 1
#
n = abs(n)
ret = s * n
return ret
SQLITE_UDF.append(('sqliteboy_repeat', 2, sqliteboy_repeat))
def sqliteboy_count(s, sub, case):
s = str(s)
sub = str(sub)
#
if not sqliteboy_is_integer(case) or case < 0:
case = 0
if not case: #ignore case
s = s.lower()
sub = sub.lower()
#
ret = s.count(sub)
return ret
SQLITE_UDF.append(('sqliteboy_count', 3, sqliteboy_count))
def sqliteboy_is_valid_email(s):
s = sqliteboy_strs(s)
#
if re.match(REGEX_EMAIL, s):
return int(True)
#
return int(False)
SQLITE_UDF.append(('sqliteboy_is_valid_email', 1, sqliteboy_is_valid_email))
def sqliteboy_match(s1, s2):
s1 = sqliteboy_strs(s1)
s2 = sqliteboy_strs(s2)
#
if re.match(s1, s2):
return int(True)
#
return int(False)
SQLITE_UDF.append(('sqliteboy_match', 2, sqliteboy_match))
def sqliteboy_is_number(n):
if isinstance(n, (float, int, long)):
return int(True)
#
try:
test = float(n)
except:
pass
else:
return int(True)
#
try:
test = long(n)
except:
pass
else:
return int(True)
#
try:
test = int(n)
except:
pass
else:
return int(True)
#
return int(False)
SQLITE_UDF.append(('sqliteboy_is_number', 1, sqliteboy_is_number))
def sqliteboy_is_float(n):
if isinstance(n, float):
return int(True)
#
return int(False)
SQLITE_UDF.append(('sqliteboy_is_float', 1, sqliteboy_is_float))
def sqliteboy_is_integer(n):
if isinstance(n, int):
return int(True)
#
return int(False)
SQLITE_UDF.append(('sqliteboy_is_integer', 1, sqliteboy_is_integer))
def sqliteboy_normalize_separator(s, separator, remove_space, unique):
field = sqliteboy_strs(s)
#
strs = field.strip()
splitted = strs.split(separator)
splitted2 = [x.strip() for x in splitted]
if remove_space:
splitted3 = [x.replace(' ', '') for x in splitted2]
else:
splitted3 = splitted2
if unique:
splitted4 = []
for i in splitted3:
if i not in splitted4:
splitted4.append(i)
else:
splitte4 = splitted3
#
splitted5 = splitted4
newlist = []
for part in splitted5:
if part:
newlist.append(part)
#
ret = separator.join(newlist)
#
return ret
SQLITE_UDF.append(('sqliteboy_normalize_separator', 4, sqliteboy_normalize_separator))
def sqliteboy_split0(s, separator, index):
ret = ''
#
s = str(s)
separator = str(separator)
#
if not sqliteboy_is_integer(index):
return ret
#
if not s.strip():
return ret
#
if separator:
data = s.split(separator)
else:
data = s.split()
#
try:
ret = data[index]
except:
pass
#
return ret
SQLITE_UDF.append(('sqliteboy_split0', 3, sqliteboy_split0))
def sqliteboy_chunk(s, n, separator, justify, padding):
s = str(s)
separator = str(separator)
padding = str(padding)
#
if (not n) or (not s) or (n < 1):
return s
#
if padding:
pad = padding[0]
else:
pad = ' '
#
if not justify:
justify = 0
#
mod = len(s) % n
if mod:
ln = len(s) + (n - mod)
if justify == 0: #left
s = s.ljust(ln, pad)
else: #right
s = s.rjust(ln, pad)
#
res = [s[i:i+n] for i in range(0, len(s), n)]
ret = separator.join(res)
#
return ret
SQLITE_UDF.append(('sqliteboy_chunk', 5, sqliteboy_chunk))
def sqliteboy_number_format(n, decimals, decimal_point, thousands_separator):
n = str(n)
decimal_point = str(decimal_point)
thousands_sep = str(thousands_separator)
#
neg = False
try:
f = float(n)
if f < 0:
neg = True
except:
return n
#
if 'e' in n.lower():
efmt = '%%.%sf' %len(n)
n = efmt %float(n)
#
dec = decimals
if not sqliteboy_is_integer(dec) or dec < 0:
dec = 0
#
nn = ''
dd = ''
if '.' in n: #float
nn, dd = n.split('.')
else:
nn = n
nn = nn.replace('-', '')
nn = nn.replace('+', '')
nn = nn.strip()
dd = dd.strip()
#
if dd:
if dec <= len(dd):
dd = dd[:dec]
else:
dd = dd.ljust(dec, '0')
#
nn = sqliteboy_chunk(nn, 3, thousands_sep, 1, '').strip()
dd = dd.strip()
#
if neg:
nn = '-' + nn
#
if dd:
ret = nn + decimal_point + dd
else:
ret = nn
#
return ret
SQLITE_UDF.append(('sqliteboy_number_format', 4, sqliteboy_number_format))
def sqliteboy_number_to_words(s, language):
ret = ''
#
s = str(s)
language = str(language).lower()
#
if not language in NUMBER_TO_WORDS.keys():
return ret
#
oc = NUMBER_TO_WORDS.get(language)
try:
oo = oc()
ret = oo.get_words(s)
except:
pass
#
return ret
SQLITE_UDF.append(('sqliteboy_number_to_words', 2, sqliteboy_number_to_words))
def sqliteboy_lookup1(table, field, field1, value1, function, distinct):
ret = ''
#
function_all = [
'avg',
'count',
'group_concat',
'max',
'min',
'sum',
'total'
]
#
table = str(table).lower()
field = str(field).lower()
field1 = str(field1).lower()
function = str(function).lower()
#
if not table in tables():
return ret
cols = columns(table, True)
if not field1 in cols or not field in cols:
return ret
if not function in function_all:
return ret
if not sqliteboy_is_number(distinct) or distinct < 0:
distinct = 0
#
where = [
'%s=$%s' %(field1, field1),
]
var = {field1: value1}
#
sdistinct = ''
if distinct:
sdistinct = ' distinct '
#
what_field = '%s(%s %s)' %(function, sdistinct, field)
#
try:
r = db.select(
table,
what=what_field,
where=' and '.join(where),
vars=var).list()
ret = r[0][what_field]
except:
pass
#
if ret:
ret = str(ret)
#
return ret
SQLITE_UDF.append(('sqliteboy_lookup1', 6, sqliteboy_lookup1))
def sqliteboy_lookup2(table, field, field1, value1, order, default):
table = str(table).lower()
field = str(field).lower()
field1 = str(field1).lower()
if not sqliteboy_is_number(order) or order < 0:
order = 0
#
if not table in tables():
return default
cols = columns(table, True)
if not field1 in cols or not field in cols:
return default
#
sorder = ' rowid asc'
if order > 0:
sorder = ' rowid desc'
#
ret = default
#
where = [
'%s=$%s' %(field1, field1),
]
var = {field1: value1}
#
try:
r = db.select(
table,
where=' and '.join(where),
order=sorder,
vars=var).list()
r = r[0]
ret = r[field]
except:
pass
#
return ret
SQLITE_UDF.append(('sqliteboy_lookup2', 6, sqliteboy_lookup2))
def sqliteboy_lookup3(table, field, field1, value1, field2, value2, order, default):
table = str(table).lower()
field = str(field).lower()
field1 = str(field1).lower()
field2 = str(field2).lower()
if not sqliteboy_is_number(order) or order < 0:
order = 0
#
if not table in tables():
return default
cols = columns(table, True)
if not field1 in cols or not field2 in cols or not field in cols:
return default
#
sorder = ' rowid asc'
if order > 0:
sorder = ' rowid desc'
#
ret = default
#
where = [
'%s=$%s' %(field1, field1),
'%s=$%s' %(field2, field2),
]
var = {field1: value1, field2: value2}
#
try:
r = db.select(
table,
where=' and '.join(where),
order=sorder,
vars=var).list()
r = r[0]
ret = r[field]
except:
pass
#
return ret
SQLITE_UDF.append(('sqliteboy_lookup3', 8, sqliteboy_lookup3))
def sqliteboy_split1(s, separator, table, column, convert):
ret = 0
#
s = str(s)
separator = str(separator)
table = str(table).strip().lower()
column = str(column).strip().lower()
if not sqliteboy_is_integer(convert) or convert < 0:
convert = 0
#
if not s.strip():
return ret
#
if not table in tables():
return ret
#
if not column in columns(table, True):
return ret
#
if separator:
data = s.split(separator)
else:
data = s.split()
if not data:
return ret
#
if hasws(table) or hasws(column):
return ret
#
cols = columnst(table)
colt = cols.get(column)
f = COLUMN_CONVERT.get(colt)
if not f:
f = COLUMN_CONVERT_DEFAULT
#
func = globals().get(f)
if not callable(func):
return ret
#
count = 0
t = db.transaction()
try:
for d in data:
if convert:
x = func(d)
else:
x = d
#
r = db.query(
'''
insert into $table ($column) values($data)
''',
vars = {
'table': web.sqlliteral(table),
'column': web.sqlliteral(column),
'data': x,
}
)
if r:
count += 1
except:
return ret
else:
t.commit()
ret = count
#
return ret
SQLITE_UDF.append(('sqliteboy_split1', 5, sqliteboy_split1))
def sqliteboy_list_datetime1(s, n, interval, table, column, local):
ret = 0
#
s = str(s).strip()
table = str(table).strip().lower()
column = str(column).strip().lower()
#
if not sqliteboy_is_integer(n) or n < 1:
return ret
#
if not sqliteboy_is_integer(interval) or interval == 0:
return ret
#
if not sqliteboy_is_integer(local) or local < 0:
local = 0
#
if not table in tables():
return ret
#
if not column in columns(table, True):
return ret
#
tnow = sqliteboy_time()
if not s:
s = sqliteboy_time3(tnow)
#
if not sqliteboy_is_datetime(s):
return ret
#
t2 = sqliteboy_time2(s)
#
count = 0
t = db.transaction()
try:
for i in range(n):
if local:
t3 = sqliteboy_time3(t2)
else:
t3 = sqliteboy_time4(t2)
#
if not t3:
continue
#
r = db.query(
'''
insert into $table ($column) values($data)
''',
vars={
'table': web.sqlliteral(table),
'column': web.sqlliteral(column),
'data': t3,
}
)
if r:
count += 1
#
t2 += interval
except:
return ret
else:
t.commit()
ret = count
#
return ret
SQLITE_UDF.append(('sqliteboy_list_datetime1', 6, sqliteboy_list_datetime1))
def sqliteboy_if(s, a, b):
ret = ''
#
s = str(s).strip()
if not s:
return ret
#
try:
r = db.query(s)[0].get('if')
if r is not None:
if r:
ret = a
else:
ret = b
except:
pass
return ret
SQLITE_UDF.append(('sqliteboy_if', 3, sqliteboy_if))
def sqliteboy_http_remote_addr():
return web.ctx.ip
SQLITE_UDF.append(('sqliteboy_http_remote_addr', 0, sqliteboy_http_remote_addr))
def sqliteboy_http_user_agent():
return web.ctx.env.get('HTTP_USER_AGENT', '')
SQLITE_UDF.append(('sqliteboy_http_user_agent', 0, sqliteboy_http_user_agent))
def sqliteboy_app_title():
return TITLE
SQLITE_UDF.append(('sqliteboy_app_title', 0, sqliteboy_app_title))
def sqliteboy_var_set(name, value):
name = str(name)
return v_set(name, value)
SQLITE_UDF.append(('sqliteboy_var_set', 2, sqliteboy_var_set))
def sqliteboy_var_get(name):
name = str(name)
return v_get(name)
SQLITE_UDF.append(('sqliteboy_var_get', 1, sqliteboy_var_get))
def sqliteboy_var_del(name):
name = str(name)
return v_del(name)
SQLITE_UDF.append(('sqliteboy_var_del', 1, sqliteboy_var_del))
def sqliteboy_strip_html(s):
s = str(s)
return striphtml(s)
SQLITE_UDF.append(('sqliteboy_strip_html', 1, sqliteboy_strip_html))
#----------------------------------------------------------------------#
# FUNCTION #