|
Forum Index : Microcontroller and PC projects : mmsql 1.2 - SQLite manager for the CMM2
| Author | Message | ||||
| homa Guru Joined: 05/11/2021 Location: GermanyPosts: 777 |
mmsql 1.2 - SQLite manager for the CMM2 Hi all, a new release of mmsql, the interactive SQLite manager for the Colour Maximite 2 (G1, G2, G3). It needs MMBasic 8.00.00b1 or later, 8.00.00b2 is recommended. The big change: SQL is no longer squeezed into one line of 255 characters. New in 1.2 - A real editor at the prompt: statements over up to 20 lines with a status line - F3 new line, F2 run, Esc discard - Paste SQL from the PC: pasted lines are recognised and kept together - One rule for input: one line runs with Enter; several lines are a block, each statement ends with ; - Statements and queries over 255 characters, with results - at the prompt, in blocks and in scripts - History of statements of any length, also after a restart; PgUp/PgDn - Scripts as in sqlite3: .read takes statements over several lines, shows query results and knows dot commands - No more 20-column limit: .import without limits for columns and long fields, .insert up to 64 columns - Database in memory (:memory:) - mmsql starts without a file - New output: .mode markdown, .mode json, .width, .print - .dump keeps the real type of every value: BLOB or text, 2.0 stays REAL, very large integers stay exact - More: .restore, .version, search patterns for .tables/.schema/.indexes/.triggers, .set screen, .set width 0 - Shorter prompt [retro.db]>, and terminal and screen show exactly the same Note: .read now needs a ; at the end of every statement, as sqlite3 does. Old scripts with one statement per line without ; need a ; at the end of each line. Dumps from mmsql have always had it. A query over several lines looks like this (F3 after each line, F2 to run - or just paste it), here with computers.db from the firmware zip: SELECT maker, count(*) AS models, min(year) AS since FROM computer WHERE year < 1990 GROUP BY maker ORDER BY since; Three downloads - mmsql.zip - the program, help files (English and German), README, CHANGES, and the full manual as PDF: MANUAL_mmsql.pdf (English) and HANDBUCH_mmsql.pdf (German) - mmsql_tutorial.zip - TUTORIAL_mmsql.pdf with demo.sql: SQL and mmsql for beginners, step by step, using computers.db from the firmware zip - mmsql_testsuite.zip - an automatic test suite for the CMM2: 51 tests of SQLite, .read, .dump, .import and export, run from mmsql itself. Expected results are in README.txt. To start: RUN "mmsql", "A:/sql/computers.db" and then type .help Thanks A big thank you to Peter for 8.00.00b2: the history at the MMBasic prompt in the terminal and LGETSTR$ with 255 characters are both fixed. ![]() @Peter: two things from the work on 1.2 - OPTION CONSOLE in a program spoils characters arriving on the serial port. If a program switches OPTION CONSOLE while text is pasted into the terminal, characters get lost or arrive as rubbish - as if the port were set up again each time. mmsql 1.2 no longer switches while it reads keys, so it is not urgent for me. Small example in fw8_option_console.zip - still the same on 8.00.00b2. Is this expected, and is there another way to write to the terminal only, or the screen only? fw8_option_console.zip - A wish: SQL(TYPE n). SQL(n) cannot tell a BLOB from a TEXT, or REAL 2.0 from INTEGER 2. To dump correctly, mmsql adds a typeof() column for every column to the query. A function next to SQL(ISNULL n) - for example 1 = INTEGER, 2 = FLOAT, 3 = TEXT, 4 = BLOB, 5 = NULL, as sqlite3_column_type() - would make that easy for any query. Small example in fw8_sql_type.zip. fw8_sql_type.zip A question to you SQLite on the CMM2 is still quite new, and I would like to know how it is used out there: - Do you already use SQLite on the CMM2? What for - a collection, a logger, a game, something else? - Do you work with the SQL commands in your own programs, with mmsql, or both? - Is mmsql useful for you, or does something keep you from using it? - What is missing? A command, an export format, an example in the tutorial? A one-line answer is fine - it helps me decide what comes next. Bugs and anything unclear in the manual or tutorial are very welcome, too. Matthias mmsql.zip mmsql_tutorial.zip mmsql_testsuite.zip MANUAL_mmsql.pdf |
||||
| The Back Shed's forum code is written, and hosted, in Australia. | © JAQ Software 2026 |