SQLiteBoy

1. Simple Web SQLite Manager/Form/Report Application Noprianto nop@tedut.com http://tedut.com SQLiteBoy

2. What is SQLiteBoy? • Web SQLite management tool • User-defined function • Simple data entry solution (Form/Subform) • Simple reporting solution • Tools: Files (+sharing), Notes, Page, Calculator • Free / open source software (GPL) • Commercial support (training, integration) by tedut.com • More: http://www.sqliteboy.com

3. What is SQLite? • Reliable, self-contained, serverless, zero-configuration, transactional, SQL Database Engine • Single file database • The code for SQLite is in the public domain • More: http://www.sqlite.org

4. Inside SQLiteBoy • Single python file: sqliteboy.py • Version 1.50: > 11500 lines of code • External dependency (pure python): web.py (http://webpy.org) • Default port: 11738

6. SQLiteBoy on Windows • Standalone / portable / run from USB Flash Disk • Can be run by standard user • No need to install python / web.py • Single file / self-contained exe (+/- 6 MB) • Run from Windows Explorer (double-click), no command line needed • Included: documentation and source code • Download: http://tedut.com/sqliteboy.exe

7. Basic Features (1) • Browse table (sort, BLOB, multiple selection/action, paginate) • Create table (primary keys, type, default) • Free form SQL Query (+CSV Export) • Insert into table • Edit/update table • Add column (type, default) • Rename table • Empty/drop table

8. Basic Features (2) • User-defined function (prefix: sqliteboy_) • number to words (multi language), number format, table lookup, hash, base64, random, additional date/time, additional string, regular expression, utility • Custom template • Human readable database size (GB,MB,KB,B) • Complete documentation • CSV Export/Import • Schema • Copy table • Vacuum • SSL Support

9. Extended Feature: Form (1) • Syntax: JSON • Subform support • Auto detect field type • Field: readonly, required, reference (SQL Query, Python List), default value (function, static, SQL Query), constraint, onsave event • Optional, additional SQL Query statement(s), to perform additional task(s) (before/after) • Insert/update/delete/etc • Simple security setting • Python Handler: integration with external system

10. Extended Feature: Form (2)Sample code: add medical record { "title" : "Add Medical Record", "data" : [ { "table" : "records", "column" : "patient_id", "label" : "Patient", "required" : 1, "reference" : "select id as a, id || ' - ' || first_name || ' ' || last_name || ' - ' || date_of_birth as b from patients order by first_name asc" }, { "table" : "records", "column" : "record", "label" : "Record", "required" : 1 }, { "table" : "records", "column" : "user", "label" : "User", "required" : 1, "readonly" : 1, "default" : ["sqliteboy_x_user"] }, { "table" : "records", "column" : "date_time", "label" : "Date/Time", "required" : 1, "readonly" : 1, "default" : ["sqliteboy_time3a"] } ], "security" : { "run" : "" } }

11. Extended Feature: Form (3)Sample shot: add medical record

12. Extended Feature: Form (4)Sample shot

13. Extended Feature: Report (1) • Syntax: JSON • Field: readonly, reference (SQL Query, Python List), default value (function, static, SQL Query), constraint • Custom SQL Query • Header order • Headers/footers support • Report format: PDF, HTML, HTML (printer friendly), CSV • Simple security setting • Python Handler: integration with external system

14. Extended Feature: Report (2)Sample code: patient medical record { "title" : "Patient Medical Record", "header": ["date_time", "user", "record"], "sql" : "select date_time, user, record from records where patient_id=$patient_id order by date_time asc", "data" : [ { "key" : "patient_id", "label" : "Patient", "reference" : "select id as a, id || ' - ' || first_name || ' ' || last_name || ' - ' || date_of_birth as b from patients order by first_name asc" } ], "security" : { "run" : "" } }

15. Extended Feature: Report (3)Sample shot: patient medical record

16. Extended Feature: Report (4)Sample shot: patient medical record

17. Extended Feature: Report (5)Sample shot: patient medical record

18. Extended Feature: Report (6)Sample shot

19. Extended Feature: Users • Multi user system • Type: admin (full access), standard (limited or configurable form/report access) • Simple user management

20. Extended Feature: Profile • System profile • style • first_name • last_name • email • website • User-defined profile • custom field(s) in user profile • useful in multi-company environment • syntax: JSON

21. Extended Feature: Files • System configuration: maximum number of files per user, maximum file size (admin: unlimited) • Multiple file upload • Action: view, download • Simple file sharing • Human readable file size

22. Extended Feature: Notes • Simple Notes • Content as SQL Query (admin), calculator

23. Extended Feature: Calculator • Simple Calculator • Valid characters: 0123456789.-+*/() • Maximum length: 36

24. Extended Feature: Page • Static page per user (home page) • URL: /page/<user> • Simple page code • *text* : text • ~text~: text • _text_: text • [text|url]:text

25. Extended Feature: Hosts • Configurable hosts allowed • Type: local (default), all, custom

26. Extended Feature: Scripts • 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

27. Extended Feature: Etc • Database backup (admin) • System configuration (admin) • Shortcut: form, report

28. Custom Template • Basic Feature • sqliteboy.html, if found in current directory • For template example: T_BASE variable

29. Thank You • :)