### Create MySQL user for TogetherDB (placeholder) Source: https://togetherdb.com/docs/quick-start-guide Placeholder SQL for creating a MySQL user with limited privileges. The statements are commented out and marked as TODO; they are not yet implemented. ```sql -- TODO: Determine from https://dev.mysql.com/doc/refman/8.0/en/grant.html#grant-table-privileges -- TODO: Test -- grant select, insert, update, delete on ru_users* to. 'togetherdb'@'35.157.231.179'; -- create user 'togetherdb'@'35.157.231.179' identified by 'secret'; ``` -------------------------------- ### SQL: Fetch database structure (information_schema queries) Source: https://togetherdb.com/docs/mysql-setup Executed when navigating to a database or hard-reloading a table view. Issues 4 queries to information_schema: lists databases, table names, references, and columns. ```sql -- lists accessible database names for this connection SHOW DATABASES; -- table names SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='' ORDER BY TABLE_NAME ASC; -- references SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE NOT REFERENCED_TABLE_SCHEMA IS NULL; -- columns SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA, PRIVILEGES, CHARACTER_MAXIMUM_LENGTH FROM information_schema.columns WHERE TABLE_SCHEMA!='information_schema' AND TABLE_SCHEMA!='performance_schema' AND TABLE_SCHEMA!='mysql' ORDER BY ORDINAL_POSITION ASC ``` -------------------------------- ### List MySQL users Source: https://togetherdb.com/docs/mysql-setup Lists all MySQL users and their hosts. Use this if you don't remember the exact user specification for the TogetherDB user. ```sql SELECT Host, User FROM mysql.user; ``` -------------------------------- ### SQL: View table rows and approximate row counts Source: https://togetherdb.com/docs/mysql-setup Executed when viewing a table. Fetches actual rows, referenced rows, and approximate row counts from information_schema. Exact row count is only determined when explicitly requested or when approximation is less than 10,000 rows. ```sql -- get actual rows and its references SELECT * FROM table_name LIMIT 50; SELECT * FROM referenced_table1_name WHERE `id` IN (); -- get approximate row counts from cached statistics. we avoid exact counting here for performance reasons SELECT TABLE_SCHEMA, TABLE_NAME, CASE WHEN avg_row_length=0 THEN 0 ELSE ROUND(data_length / avg_row_length) END AS approx_count_1, table_rows AS approx_count_2 FROM information_schema.TABLES WHERE TABLE_SCHEMA!='sys' AND TABLE_SCHEMA!='mysql' AND TABLE_SCHEMA!='information_schema' AND TABLE_SCHEMA!='performance_schema'; ``` -------------------------------- ### Create PostgreSQL user for TogetherDB Source: https://togetherdb.com/docs/quick-start-guide Creates a login role named 'togetherdb' with a password. Use this as the first step when setting up a restricted user for TogetherDB in PostgreSQL. ```sql CREATE ROLE togetherdb WITH LOGIN PASSWORD 'secret123'; ``` -------------------------------- ### SQL: Load more rows when scrolling Source: https://togetherdb.com/docs/mysql-setup Executed when scrolling down in the table to load more rows. Uses LIMIT with OFFSET to fetch the next batch of rows and their references. ```sql SELECT * FROM table_name LIMIT 50 OFFSET 100 SELECT * FROM referenced_table1_name WHERE `id` IN (); ``` -------------------------------- ### Create MySQL user for TogetherDB Source: https://togetherdb.com/docs/mysql-setup Creates a new MySQL user 'togetherdb' that can only connect from the TogetherDB worker server IP 159.69.45.33. The user has no permissions by default. ```sql CREATE USER 'togetherdb'@'159.69.45.33' IDENTIFIED BY 'secret'; ``` -------------------------------- ### SQL: Update a value and refresh rows Source: https://togetherdb.com/docs/mysql-setup Executed when updating a value. Updates by primary key column (always indexed) and then fetches the updated row and its references. ```sql -- updates by primary key column which are always indexed UPDATE table_name SET column_name= WHERE `id`=row_id LIMIT 1; -- get row values again SELECT * FROM table_name WHERE `id`=row_id LIMIT 1; SELECT * FROM referenced_table1_name WHERE `id` IN (); ``` -------------------------------- ### Grant access to specific databases Source: https://togetherdb.com/docs/mysql-setup Grants SELECT on mysql.* for database/table info and SELECT, INSERT, UPDATE, DELETE on specific databases (mydatabase1, mydatabase2). Use this to limit access to selected databases. ```sql -- to access database/table infos (recommended) GRANT SELECT ON mysql.* TO 'togetherdb'@'159.69.45.33'; -- grant access to your databases GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase1.* TO 'togetherdb'@'159.69.45.33'; GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase2.* TO 'togetherdb'@'159.69.45.33'; ... ``` -------------------------------- ### Grant full access to all databases Source: https://togetherdb.com/docs/mysql-setup Grants SELECT, INSERT, UPDATE, DELETE on all databases to the TogetherDB user. Recommended for viewing and editing data in all databases. ```sql GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'togetherdb'@'159.69.45.33'; ``` -------------------------------- ### Grant PostgreSQL privileges to TogetherDB user Source: https://togetherdb.com/docs/quick-start-guide Grants the 'togetherdb' role permissions to read and write data on all tables, sequences, and functions in the public schema, and sets default privileges for future objects. Run these statements for each database you want to manage with TogetherDB. ```sql GRANT CONNECT ON DATABASE TO togetherdb; GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO togetherdb; GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO togetherdb; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO togetherdb; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO togetherdb; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, UPDATE ON SEQUENCES TO togetherdb; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO togetherdb; -- TODO: Test ``` -------------------------------- ### Grant read-only access Source: https://togetherdb.com/docs/mysql-setup Grants SELECT only on all databases, enabling read-only mode for TogetherDB. ```sql GRANT SELECT ON *.* TO 'togetherdb'@'159.69.45.33'; ``` -------------------------------- ### Delete TogetherDB user Source: https://togetherdb.com/docs/mysql-setup Deletes the TogetherDB user from MySQL. Use this to leave TogetherDB. ```sql DROP USER 'togetherdb'@'159.69.45.33'; ``` === COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.