Arql

中文

Arql is a simple instrumental gem that combines Rails ActiveRecord and Pry, and adds useful Pry commands. It can automatically define model classes based on information from database tables. If you’re a Ruby user, you can use this Arql as your database query tool.

• Ruby 2.6.0 or later
• For different types of databases, you need to install the appropriate database adapter or client binary library:
  • MySQL: Depending on your operating system, you may need to install: , libmariadb-dev libmysqlclient-dev mysql-devel , default-libmysqlclient-dev ; Refer to your distribution’s package guide to find your specific package, or refer to the mysql2 documentation
  • SQLite3: No need to install any additional libraries
  • PostgreSQL: gem install pg
  • Oracle: gem install activerecord-oracle_enhanced-adapter
  • SQL Server: gem install activerecord-sqlserver-adapter

Execute:

$ gem install arql

If you’re having problems with system permissions, try using sudo:

$ sudo gem install arql

Usage: arql [options] [ruby file]

  If neither [ruby file] nor -E option is specified, and STDIN is a tty, a Pry REPL will be started,
  otherwise the specified ruby file or -E option value or ruby code read from STDIN will be executed,
  and the REPL will not be started

    -c, --conf=CONFIG_FILE           Specify the configuration file, default is $HOME/.arql.yml or $HOME/.arql.d/init.yml
    -i, --initializer=INITIALIZER    Specify the initializer Ruby file, default is $HOME/.arql.rb or $HOME/.arql.d/init.rb
    -e, --env=ENVIRON                Specify the configuration environment
    -a, --db-adapter=DB_ADAPTER      Specify the database adapter, default is sqlite3
    -h, --db-host=DB_HOST            Specify the database host
    -p, --db-port=DB_PORT            Specify the database port
    -d, --db-name=DB_NAME            Specify the database name
    -u, --db-user=DB_USER            Specify the database user
    -P, --db-password=DB_PASSWORD    Specify the database password
    -n, --db-encoding=DB_ENCODING    Specify the database encoding, default is utf8
    -o, --db-pool=DB_POOL            Specify the database connection pool size, default is 5
    -H, --ssh-host=SSH_HOST          Specify the SSH host
    -O, --ssh-port=SSH_PORT          Specify the SSH port
    -U, --ssh-user=SSH_USER          Specify the SSH user
    -W, --ssh-password=SSH_PASSWORD  Specify the SSH password
    -L, --ssh-local-port=SSH_LOCAL_PORT  Specify the local SSH proxy port
    -E, --eval=CODE                  Evaluate code
    -S, --show-sql                   Print SQL on STDOUT
    -w, --write-sql=OUTPUT           Write SQL to OUTPUT file
    -A, --append-sql=OUTPUT          Append SQL to OUTPUT file
        --help                      Print this message

Specify the profile location, which defaults to $HOME/.arql.yml or $HOME/.arql.d/init.yml . Configuration files are usually the same as Rails database configuration files, but there are some additional configuration options, such as ssh options, etc. References Configuration Files section.

Specify a Ruby source file and execute the code for Arql after defining the ActiveRecord model class, which defaults to $HOME/.arql.rb or $HOME/.arql.d/init.rb . In this file, you can add method and association definitions to the ActiveRecord model class.

Specify one or more environment names in the configuration file, separated by commas/pluses/colons.

The model classes that Arql generates for each environment will be placed in the namespace specified by the namespace configuration for that environment. For example:

development:
  adapter: mysql2
  host: localhost
  username: root
  database: myapp_development
  pool: 5
  namespace: Dev

Suppose there is a table in the myapp_development database named users , posts etc., then the model class generated in the development environment will be:

• Dev::User
• Dev::Post

If no namespace configuration is specified, the default namespace is in the form of CamelCase for the environment name. For example Development , here , then the resulting model class would be:

• Development::User
• Development::Post

Arql Object.const_missing also defines an “alias” under the top-level namespace for those model classes whose class names and existing constants do not have the same name, for example, if the class name is not the same as the existing constant name, it can be directly used User . Post

If there are tables with the same names in more than one environment, an alias will be defined for the model classes of the tables in the previous environments in the order of the specified environments

Specify a Ruby snippet, if specified, the Pry REPL will not be launched.

arql does not display SQL logs by default and is turned on with this option.

You can also use this option to have arql write SQL logs to a file.

-w Similar to , but with append writes, existing files are not truncated.

The options described in this section are typically configured in a configuration file, and these options are simply shortcuts to the configuration items in the configuration file so that certain configuration items can be modified directly in the CLI.

Specify the database adapter, available values:

• mysql2
• postgresql
• sqlite3
• sqlserver
• oracle_enhanced

Specify the database host

Specify the database port

Specify the database name

Specify the database user

Specify the database password

Specify the database encoding, default is utf8

Specify the database connection pool size, default is 5

Specify the SSH host, when the SSH-related options are specified, arql will establish an SSH tunnel and connect to the

Specify the SSH port

Specify the SSH user

Specify the SSH password

Specify the local SSH proxy port

The path to the configuration file defaults to or $HOME/.arql.yml $HOME/.arql.d/init.yml . The configuration file is usually the same as the Rails database configuration file, but there are some additional configuration options:

1. created_at : An array of custom column names containing the ActiveRecord created_at field, with the default value of , if specified created_at , the value of the column will be populated with the current timestamp when created
2. updated_at : An array of custom column names containing the ActiveRecord updated_at field, with the default value of , if specified updated_at , the value of the column will be populated with the current timestamp when updated
3. ssh.host : ssh host, you can use the hostname in the ssh_config file, or it can be a direct IP address or hostname
4. ssh.port : ssh port, default is 22
5. ssh.user : ssh username
6. ssh.password : SSH password
7. ssh.local_port : ssh local port
8. singularized_table_names : Whether to use the singular table name, the default is false , if it is false , the students table will be defined as a Student model, if it is true , the students table will be defined as a Students model
9. table_name_prefixes : An array of table name prefixes, which default is an empty array, if specified, these prefixes will be ignored when generating the model, for example ["t_"] , if , the t_students table will be defined as a Student model
10. namespace : The model namespace, which defaults to the CamelCase form of the environment name, and the generated model will be placed under the specified namespace
11. model_names : The value of this configuration item is a hash(map), the key is the table name, and the value is the name of the model to be generated for the table. Arql uses ActiveRecord’s naming convention to generate model names by default, and if this configuration item is specified, the table specified by the configuration item will use the model name specified by the modified configuration itemValue can be an array of strings in addition to a string representing the model name, with the first element of the array representing the model name and the second element representing the constant alias (Arql) created for the model By default, aliases are also automatically created for the generated model class according to certain rules, and if an alias is specified here, the user-provided value will be used as the alias)

model_names Examples of configuration items:

development:
  host: localhost
  database: test
  username: root
  model_names:
    students: Seito
    teachers: ["LaoShi", "LS"]

In the above configuration file, a model named for the students table will be generated, a model named LaoShi for the teachers table will be generated for the table, and a constant alias named LS will be created for the LaoShi model. An alias is also generated for the students table: S

default: &default
  adapter: mysql2
  encoding: utf8
  created_at: ["gmt_created"]
  updated_at: ["gmt_modified"]
  singularized_table_names: true

local:
  <<: *default
  username: root
  database: blog
  password:
  table_name_prefixes: ["t_"]
  socket: /tmp/mysql.sock
  namespace: B

dev:
  <<: *default
  host: devdb.mycompany.com
  port: 3306
  username: root
  password: 123456
  database: blog
  table_name_prefixes: ["t_"]
  namespace: B
  ssh:
    host: dev.mycompany.com
    port: 22
    user: deploy
    password: 12345678
    local_port: 3307

In the example default , a generic configuration item is defined, as well as two specific database environments local and dev . local dev <<: *default and inherit default the configuration items of .

arql -e dev When the command is executed, arql uses the dev configuration in the configuration file; arql -e local When the command is executed, arql uses the local configuration in the configuration file.

dev The environment uses an SSH tunnel, and when you connect to a devdb.mycompany.com database, you will first establish an SSH tunnel to and then connect to dev.mycompany.com the database through the SSH tunnel.

If neither the [ruby file] Nor the Specify -E option is specified, and the STDIN is a tty , arql launches a Pry REPL. For example, execute:

arql -e dev

Arql provides a few Pry commands:

info The command prints the current database connection information and SSH proxy information, for example:

my_env Database Connection Information:
    Host:
    Port:
    Username:  root
    Password:
    Database:  test
    Adapter:   mysql2
    Encoding:  utf8
    Pool Size: 5

info By default, the connection information for all specified environments is displayed, if you only want to display the connection information of the current environment, the info command accepts a regular expression argument and only displays the matching environment information, for example:

info .*dev

m (or l ) command to print all table names and corresponding model class names and abbreviated class names, for example:

+--------------------+------------------+------+---------+
| Table Name         | Model Class      | Abbr | Comment |
+--------------------+------------------+------+---------+
| post               | Post             | P    | 帖子    |
| org                | Org              | O    | 组织    |
| user_org           | UserOrg          | UO   |         |
| student            | Student          | S    | 学生    |
| course             | Course           | C    |         |
| score              | Score            | S2   |         |
| users              | Users            | U    |         |
| posts              | Posts            | P2   |         |
| authors            | Authors          | A    |         |
+--------------------+------------------+------+---------+

Thereinto:

• Table Name : Table name
• Model Class : Model class name
• Abbr : Abbreviated class name
• Comment :Exegesis.

m / l Command with three optional options:

• -e , --env : Specify the environment, regular expression, only display the table name in the matching environment, and display all environments by default
• -f , --format : Output Format:
  • terminal : Default table format
  • md : markdown table format
  • org : org mode table format
  • sql : 输出 create table SQL
• -c , --column : Regular expression, which lists fields, not tables, and filters by field name or field comment

m The / l command can also accept an optional regular expression argument that displays only information about matching tables (by table name or table comment), for example:

l  # Print all table information
l ^post  # Only display information about tables whose names start with post
l -e dev -f md  # Display table information in the dev environment and output in markdown format
l -c no|num  # Display only field information containing no or num in field name or field comment

This pair of commands toggles the display of SQL logs in the Pry REPL.

By default, SQL logs are not displayed:

ARQL@demo247(main) [2] ❯ Student.count
=> 0

When you open the SQL log, you will see the SQL statement that was executed each time:

ARQL@demo247(main) [3] ❯ show-sql
ARQL@demo247(main) [4] ❯ Student.count
D, [2024-04-07T13:31:32.053903 #20440] DEBUG -- :   Student Count (29.8ms)  SELECT COUNT(*) FROM `student`
=> 0

reconnect The command is used to reconnect the current database connection. When the connection is lost due to network reasons, you can use the command to reconnect. reconnect, the objects in the current Pry session are not lost. reconnect First, it will determine whether the current connection is still valid, if it is valid, it will not be reconnected, if reconnect the validity of the connection is wrong, you can use reconnect! the command to force a reconnection.

redefine The command is used to redefine the ActiveRecord model class, which regenerates the model class based on the information from the database table. init.rb If you want a new relationship definition to take effect in the current Pry session, you can use redefine the command.

sandbox-enter command to turn on sandbox mode. In sandbox mode, all database operations are executed in a transaction, which is not automatically committed, and is automatically rolled back when exiting sandbox mode.

1. Turn on sandbox mode:

   ARQL@demo247(main) [6] ❯ sandbox-enter
   ARQL@demo247 [sandbox] (main) [7] ❯
       

2. To exit sandbox mode:

   ARQL@demo247 [sandbox] (main) [7] ❯ sandbox-quit
       

If a Ruby file is specified as a command-line argument, or if the option is used -E , or if STDIN is not a tty , then Arql will not start Pry, but will simply execute the specified file or code snippet (or read code from standard input). The model class definition is loaded before the code snippet is executed. You can think of this usage as something like a runner subcommand rails of .

The -E option allows you to execute the code snippet directly without starting Pry:

$ arql -e dev -E 'puts Person.count'

By specifying a Ruby file as a command-line argument, you can execute code directly from a Ruby file:

test.rb:

puts Person.count

$ arql -e dev test.rb

Reading code from standard input, the code snippet can be executed directly:

$ echo 'puts Person.count' | arql -e dev

q Used to execute SQL queries

ARQL ❯ rs = Blog::q 'select count(0) from person;'
=> #<ActiveRecord::Result:0x00007fd1f8026ad0 @column_types={}, @columns=["count(0)"], @hash_rows=nil, @rows=[[11]]>
ARQL ❯ rs.rows
=> [[11]]

models Returns all model classes in that namespace

ARQL ❯ Blog::models
=> [Blog::Person(id: integer, name: string, age: integer, created_at: datetime, updated_at: datetime), Blog::Post(id: integer, title: string, content: text, created_at: datetime, updated_at: datetime)]

tables Returns all table names in the namespace

ARQL ❯ Blog::tables
=> ["people", "posts"]

model_names Returns the names of all model classes in that namespace

ARQL ❯ Blog::model_names
=> ["Demo::Person", "Demo::Post"]

create_table Used to create tables in the environment corresponding to the namespace

ARQL ❯ Blog::create_table :people do |t|
ARQL ❯   t.string :name
ARQL ❯   t.integer :age
ARQL ❯   t.timestamps
ARQL ❯ end

dump Export mysqldump the database corresponding to the namespace to the specified file with

ARQL ❯ Blog::dump('~/data/blog.sql')

Pry has built-in show-source (alias $ ) and show-doc (alias ? ) commands to view the source code and documentation of the method. You can show-doc view the documentation for the method through . For example:

ARQL ❯ ? Student.add_column

You can call to_create_sql the method on any ActiveRecord model class to get the SQL statement that creates the table corresponding to that model class.

ARQL@demo247(main) [16] ❯ puts Blog::Post.to_create_sql
D, [2024-04-07T14:15:11.106693 #20440] DEBUG -- :   SQL (24.9ms)  show create table post
CREATE TABLE `post` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(256) DEFAULT NULL,
  `gender` varchar(256) DEFAULT NULL,
  `phone` varchar(256) DEFAULT NULL,
  `id_no` varchar(256) DEFAULT NULL,
  `note` varchar(256) DEFAULT NULL,
  `gmt_created` datetime NOT NULL COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  KEY `index_post_on_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=83 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

t The class method is used to print the table structure of a model class

Executing the Blog::Person.t command prints the person definition information for the table:

Table: person
+----|------------|------------------|-----------|-------|-----------|-------|---------|----------|---------+
| PK | Name       | SQL Type         | Ruby Type | Limit | Precision | Scale | Default | Nullable | Comment |
+----|------------|------------------|-----------|-------|-----------|-------|---------|----------|---------+
| Y  | id         | int(11) unsigned | integer   | 4     |           |       |         | false    |         |
|    | name       | varchar(64)      | string    | 64    |           |       |         | true     |         |
|    | age        | int(11)          | integer   | 4     |           |       |         | true     |         |
|    | gender     | int(4)           | integer   | 4     |           |       |         | true     |         |
|    | grade      | int(4)           | integer   | 4     |           |       |         | true     |         |
|    | blood_type | varchar(4)       | string    | 4     |           |       |         | true     |         |
+----|------------|------------------|-----------|-------|-----------|-------|---------|----------|---------+

t Accept an optional format named parameter with the following values:

• md
• org
• sql
• terminal (default)

例如：

ARQL ❯ Blog::Person.t :sql

输出：

CREATE TABLE `person` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(64) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` int(4) DEFAULT NULL,
  `grade` int(4) DEFAULT NULL,
  `blood_type` varchar(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='人员表';

v Class methods are used to integrate with Emacs’ org babel, and can be called v directly in the org file to obtain the table structure of the model class.

例如：

ARQL ❯ Blog::Post.v

输出：

ARQL@demo247(main) [10] ❯ Demo::Post.v
=> [["PK", "Name", "SQL Type", "Ruby Type", "Limit", "Precision", "Scale", "Default", "Nullable", "Comment"],
 nil,
 ["Y", "id", "int(10) unsigned", :integer, 4, "", "", "", false, "ID"],
 ["", "name", "varchar(256)", :string, 256, "", "", "", true, ""],
 ["", "gender", "varchar(256)", :string, 256, "", "", "", true, ""],
 ["", "phone", "varchar(256)", :string, 256, "", "", "", true, ""],
 ["", "id_no", "varchar(256)", :string, 256, "", "", "", true, ""],
 ["", "note", "varchar(256)", :string, 256, "", "", "", true, ""],
 ["", "gmt_created", "datetime", :datetime, "", 0, "", "", false, "创建时间"],
 ["", "gmt_modified", "datetime", :datetime, "", 0, "", "", false, "最后修改时间"],
 ["", "sasa", "varchar(255)", :string, 255, "", "", "", true, ""]]

Use the visidata display table structure

Returns table annotations for the model

例如：

ARQL ❯ Blog::Post.table_comment

输出：

"文章表"

Blog::Student.add_column :note, :text, comment: 'Remarks'

Blog::Student.change_column :note, :string, comment: 'Remarks'

Blog::Student.remove_column :note

Blog::Student.add_index :name
Blog::Student.add_index [:branch_id, :party_id], unique: true, name: 'by_branch_party'

Blog::Student.change_column_comment :note, 'Remarks'

Blog::Student.change_column_default :note, 'A note'

Blog::Student.rename_column :note, :remark

Blog::Student.rename_table :seitou

Blog::Student.change_table_comment from: '', to: 'students table'

Blog::Student.drop_table

Blog::Student.remove_index :age
Blog::Student.remove_index name: 'by_branch_party'

Blog::Student.table_comment

Blog::Student.indexes

t In addition to being called as a class method on an ActiveRecord model class, it can also be called as an instance method on an ActiveRecord model instance object.

ARQL ❯ Person.last.t
+----------------|-----------------|------------------|---------+
| Attribute Name | Attribute Value | SQL Type         | Comment |
+----------------|-----------------|------------------|---------+
| id             | 11              | int(11) unsigned |         |
| name           | Jackson         | varchar(64)      |         |
| age            | 30              | int(11)          |         |
| gender         | 2               | int(4)           |         |
| grade          | 2               | int(4)           |         |
| blood_type     | AB              | varchar(4)       |         |
+----------------|-----------------|------------------|---------+

t The method can accept the following two options:

• :compact option to specify whether to display compactly, the value can be true or false , if compact display is enabled, those NULL columns with all values will not be displayed, which is useful for viewing tables with sparse data, such as:

  Person.last.t(compact: true)
  Student.where(condition).t(compact: false)
      

• :format option, which is used to specify the output format, the value can be:
  • :terminal The default output format is suitable for viewing in the terminal
  • :org org-mode table format
  • :md Markdown table format

You can call to_insert_sql the / to_upsert_sql method on any instance of the ActiveRecord model to get the insert or update SQL statement for that object. These two methods can also be called on an array object that contains an ActiveRecord model instance object.

ARQL ❯ Person.all.to_a.to_insert_sql
=> "INSERT INTO `person` (`id`,`name`,`age`,`gender`,`grade`,`blood_type`) VALUES (1, 'Jack', 30, NULL, NULL, NULL), (2, 'Jack', 11, 1, NULL, NULL), (3, 'Jack', 12, 1, NULL, NULL), (4, 'Jack', 30, 1, NULL, NULL), (5, 'Jack', 12, 2, NULL, NULL), (6, 'Jack', 2, 2, 2, NULL), (7, 'Jack', 3, 2, 2, NULL), (8, 'Jack', 30, 2, 2, 'AB'), (9, 'Jack', 30, 2, 2, 'AB'), (10, 'Jack', 30, 2, 2, 'AB'), (11, 'Jackson', 30, 2, 2, 'AB') ON DUPLICATE KEY UPDATE `id`=`id`;"

v method is used to integrate with Emacs org babel.

Calling v the method on any ActiveRecord model instance object prints an array of the first element of the array ['Attribute Name', 'Attribute Value', 'SQL Type', 'Comment'] , the second element nil , and the remaining elements of the object’s property name and value. In Emacs org-mode, if :result the type is value (the default), this return value will be rendered as a nice table.

ARQL ❯ Person.last.v
=> [["Attribute Name", "Attribute Value", "SQL Type", "Comment"],
 nil,
 ["id", 11, "int(11) unsigned", ""],
 ["name", "Jackson", "varchar(64)", ""],
 ["age", 30, "int(11)", ""],
 ["gender", 2, "int(4)", ""],
 ["grade", 2, "int(4)", ""],
 ["blood_type", "AB", "varchar(4)", ""]]

ARQL ❯ Person.all.to_a.v
=> [["id", "name", "age", "gender", "grade", "blood_type"],
 nil,
 [1, "Jack", 30, nil, nil, nil],
 [2, "Jack", 11, 1, nil, nil],
 [3, "Jack", 12, 1, nil, nil],
 [4, "Jack", 30, 1, nil, nil],
 [5, "Jack", 12, 2, nil, nil],
 [6, "Jack", 2, 2, 2, nil],
 [7, "Jack", 3, 2, 2, nil],
 [8, "Jack", 30, 2, 2, "AB"],
 [9, "Jack", 30, 2, 2, "AB"],
 [10, "Jack", 30, 2, 2, "AB"],
 [11, "Jackson", 30, 2, 2, "AB"]]

ARQL ❯ arr = [{name: 'Jack', age: 10}, {name: 'Lucy', age: 20}]
=> [{:name=>"Jack", :age=>10}, {:name=>"Lucy", :age=>20}]
ARQL ❯ arr.v
=> [[:name, :age], nil, ["Jack", 10], ["Lucy", 20]]

To export the instance object as INSERT a SQL statement, see the “dump data” section below

To export the instance object as an Excel or CSV file, see the “Reading and Writing Excel and CSV Files” section below

ActiveRecord::Relation / ActiveRecord::Result / Ransack::Search Logically they can all be thought of as arrays, so these methods can be called on these objects:

t Methods can also be called on an array that contains an ActiveRecord instance, or on a ActiveRecord::Relation / ActiveRecord::Result / Ransack::Search object.

ARQL ❯ Person.last(2).t
 +----+----------+--------+----------------------------------+-------+------+---------------------------+---------------------------+
 | id | name     | gender | id_no                            | phone | note | gmt_created               | gmt_modified              |
 +----+----------+--------+----------------------------------+-------+------+---------------------------+---------------------------+
 | 90 | Zhangsan | M      | f09288fb381cc47dd2e56389cf15f0bf |       |      | 2021-04-26 15:32:05 +0800 | 2021-04-26 15:32:05 +0800 |
 | 91 | Lisi     | F      | fb6fea4b23b1d3c54739774946246e4c |       |      | 2021-04-26 15:32:05 +0800 | 2021-04-26 15:32:05 +0800 |
 +----+----------+--------+----------------------------------+-------+------+---------------------------+---------------------------+

When used as an array and “array-like” object instance method, t the method can accept multiple parameters for filtering attributes, which can be:

• string or Symbol, which literally matches the property
• Regular expressions to make regular matches to attributes

For example, only name show , gender and all properties whose names contain the time word :

ARQL ❯ Person.last(2).t('name', :gender, /time/i)

As an example of an array and an Array-like object, t the following three options can also be accepted:

• :except option, which allows you to specify a property name that is not displayed, and the value can be a string or a regular expression, for example:

  Person.last(10).t(except: 'id')
  Student.where(condition).t(except: /id|name/)
      

• :compact option to specify whether to display compactly, the value can be true or false , if compact display is enabled, those NULL columns with all values will not be displayed, which is useful for viewing tables with sparse data, such as:

  Person.last(10).t(compact: true)
  Student.where(condition).t(compact: false)
      

• :format option, which is used to specify the output format, the value can be:
  • :terminal The default output format is suitable for viewing in the terminal
  • :org org-mode table format
  • :md Markdown table format

v method is used to integrate with Emacs org babel.

ARQL ❯ Person.last.v
=> [["Attribute Name", "Attribute Value", "SQL Type", "Comment"],
 nil,
 ["id", 11, "int(11) unsigned", ""],
 ["name", "Jackson", "varchar(64)", ""],
 ["age", 30, "int(11)", ""],
 ["gender", 2, "int(4)", ""],
 ["grade", 2, "int(4)", ""],
 ["blood_type", "AB", "varchar(4)", ""]]

Use to visidata display “array” data

write_csv and write_excel Used to export Array data to CSV or Excel files, see the “Reading and Writing Excel and CSV Files” section below

dump The method is used to export the ActiveRecord::Relation / ActiveRecord::Result / Ransack::Search object as an INSERT SQL statement, see the “dump data” section below

The following methods corresponding to a DDL operation have a limitation when used: if multiple environments are connected, the environment name must be specified via :env the option when calling these methods. For example:

create_table :users, env: 'development', comment: 'the user table' do |t|
  t.string :name, comment: 'User Name'
  t.integer :age, comment: 'User Age'
end

create_table :post, id: false, primary_key: :id do |t|
  t.column :id, :bigint, precison: 19, comment: 'ID'
  t.column :name, :string, comment: 'Post Name'
  t.column :gmt_created, :datetime, comment: 'Created Time'
  t.column :gmt_modified, :datetime, comment: 'Modified Time'
end

create_join_table :products, :categories do |t|
  t.index :product_id
  t.index :category_id
end

drop_table :post

drop_join_table :products, :categories

rename_table :post, :posts

Returns the model classes that will be under all environment namespaces

Returns table names for all environments

Returns the model class names for all environments

If you specify only one environment, you can use q methods to execute native SQL queries without specifying a namespace module q in Blog::q front of

Calling j the method on any object will result in a JSON-formatted string, and the calling jj method will result in a formatted JSON string.

Use jp the method to print the JSON, and use the method to jjp print the formatted JSON.

1. Srting#p p The method is defined as follows:

   class String
     def p
       puts self
     end
   end
       

   ​"hello".p Equivalent to puts "hello"​ .

2. String#parse For a string representing a file path, you can call parse the method to parse Excel, CSV, and JSON files by the suffix in the file path.

   excel = 'path/to/excel.xlsx'.parse
   csv = 'path/to/csv.csv'.parse
   json = 'path/to/json.json'.parse
       

Arql provides a ID class that generates snowflake algorithm IDs and UUIDs.

id = ID.long # Generate a snowflake algorithm ID
 id = ID.uuid # Generate a UUID

Arql integrates caxlsx with roo the and two Excel libraries, providing a way to parse and generate Excel files. Arql also provides methods for reading and writing CSV files.

Arql adds parse_excel methods to the Kernel module that can be used to parse Excel files. For example:

ARQL ❯ parse_excel 'path/to/excel.xlsx'

You can use in the file path to ~/ represent the user’s home directory, and Arql will be automatically expanded.

You can also call parse_excel the Method on an object that represents the path to the String file:

ARQL ❯ 'path/to/excel.xlsx'.parse_excel

parse_excel The method returns an Hash object, Key is the name of the Sheet, Value is the data for the Sheet, and Value is a two-dimensional array. For example:

{
  'Sheet1' => [
    ['A1', 'B1', 'C1'],
    ['A2', 'B2', 'C2'],
    ['A3', 'B3', 'C3']
  ],
  'Sheet2' => [
    ['A1', 'B1', 'C1'],
    ['A2', 'B2', 'C2'],
    ['A3', 'B3', 'C3']
  ]
}

Arql adds a write_excel method for Hash the / Array / ActiveRecord::Relation ActiveRecord::Base / object, which can be used to generate an Excel file:

ARQL ❯ obj.write_excel 'path/to/excel.xlsx'

Hash#write_excel The key of the hash object is the name of the sheet, the value is the data of the sheet, and the type of value can be:

• An array, the elements of which can be:
  • An array representing a row of data
  • A hash object that represents a row of data, with Key being the column name and Value being the column value
  • An ActiveRecord::Base object that represents a row of data
• A hash object that contains two key-value pairs:
  • :fields , an array representing the column name
  • :data , a two-dimensional array that represents data

ARQL ❯ obj.write_excel 'path/to/excel.xlsx', :name, :age, :gender, sheet_name: 'Order Data'

Thereinto:

• :name, :age, :gender These parameters are column names, and if not specified, the column names will be determined based on the first element of the array:
  • If the element is ActiveRecord::Base an object, all of the object’s property names (i.e., a list of database fields) are used as the column names
  • If the element is Hash an object, the All Keys are used Hash as the column name
• sheet_name Specify a sheet name, or if you don’t Sheet1 , the default sheet name is used

Array Each element of the object represents a row of data, requiring Array#write_excel each element of the Array object to:

• An ActiveRecord::Base object
• An Hash object that represents a row of data, with Key being the column name and Value being the column value
• An array representing a row of data

ARQL ❯ Student.find(123).write_excel 'path/to/excel.xlsx', sheet_name: 'Student Data'

ActiveRecord::Base The write_excel object is actually the method that wraps the ActiveRecord::Base object into an object with Array only one element, and then calls Array the write_excel method.

ARQL ❯ Student.where(gender: 'M').write_excel 'path/to/excel.xlsx', sheet_name: 'Male Students'

ActiveRecord::Relation The write_excel object is actually the write_excel method Array that converts the ActiveRecord::Relation object into an Array object and then calls it.

Arql provides parse_csv methods that can be used to parse CSV files:

ARQL ❯ parse_csv 'path/to/csv.csv'

parse_csv The method returns a CSV object from a standard library.

parse_csv There can be the following optional parameters:

• encoding , specifies the encoding of the CSV file, default is UTF-16 (with BOM)
• headers , specifies whether to include a table header, which is the default value false
• col_sep to specify the column separator, which defaults to \t
• row_sep , specifies the row separator, which is \r\n the default

(The above default values are actually the default configuration used by Microsoft Office Excel when saving CSV files)

You can also call parse_csv the Method on an object that represents the path to the String file:

ARQL ❯ 'path/to/csv.csv'.parse_csv

Arql adds a method for Array the / ActiveRecord::Relation / ActiveRecord::Base object, which can be used to generate a CSV write_csv file:

ARQL ❯ obj.write_csv 'path/to/csv.csv', :name, :age, :gender, sheet_name: 'Order Data'

The usage is similar to the Array object’s write_excel method.

ARQL ❯ Student.find(123).write_csv 'path/to/csv.csv', sheet_name: 'Student Data'

The usage is similar to the ActiveRecord::Base object’s write_excel method.

ARQL ❯ Student.where(gender: 'M').write_csv 'path/to/csv.csv', sheet_name: 'Make Students'

The usage is similar to the ActiveRecord::Relation object’s write_excel method.

Note: Only MySQL databases are supported

Arql adds dump methods for objects such as Array / ActiveRecord::Base / ActiveRecord::Relation that can be used to export data to a SQL file:

ARQL ❯ obj.dump 'path/to/dump.sql', batch_size: 5000

Array Each element of an object must be an ActiveRecord::Base object

batch_size The parameter specifies the data queried out for each batch, and the default value is 500

ARQL ❯ Student.find(123).dump 'path/to/dump.sql', batch_size: 5000

ActiveRecord::Base An object’s dump method is actually a dump method that wraps the ActiveRecord::Base object into an Array object with only one element, and then calls Array the method.

ARQL ❯ Student.where(gender: 'M').dump 'path/to/dump.sql', batch_size: 5000

ActiveRecord::Relation The dump object is actually the dump method Array that converts the ActiveRecord::Relation object into an Array object and then calls it.

ARQL ❯ Student.dump 'path/to/dump.sql', no_create_table: false

This method will export all the data in the Student table to a SQL file via mysqldump the command.

no_create_table parameter specifies whether to include a statement to create a table in the SQL file, and the default value is false .

Arql integrates the youplot library of Ruby and adds some methods to Array that can be used to draw charts:

• barplot
• countplot
• histo
• lineplot
• lineplots
• scatter
• density
• boxplot

  Example:

  Count plot:

ARQL@demo247(main) [44] ❯ Student.pluck(:gender)
=> ["M", "M", "M", "M", "M", "M", "M", "F", "M", "F", "M", "M", "M", "M", "M"]
ARQL@demo247(main) [45] ❯ Student.pluck(:gender).countplot
     ┌                                        ┐
   M ┤■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■ 13.0
   F ┤■■■■■ 2.0
     └                                        ┘

Histo plot:

ARQL@jicai.dev(main) [18] ❯ Order.last(20).pluck(:order_sum)
=> [0.21876e5, 0.336571e5, 0.1934e5, 0.966239e4, 0.38748e3, 0.31092e4, 0.483e5, 0.445121e5, 0.1305e4, 0.2296e6, 0.943e5, 0.352e4, 0.3756e5, 0.323781e5, 0.7937622e5, 0.982e4, 0.338393e5, 0.316597e5, 0.213678e5, 0.336845e5]
ARQL@jicai.dev(main) [19] ❯ Order.last(20).pluck(:order_sum).histo
                        ┌                                        ┐
   [     0.0,  50000.0) ┤▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇▇ 17
   [ 50000.0, 100000.0) ┤▇▇▇▇ 2
   [100000.0, 150000.0) ┤ 0
   [150000.0, 200000.0) ┤ 0
   [200000.0, 250000.0) ┤▇▇ 1
                        └                                        ┘
                                        Frequency

Arql integrates Ransack:

Student.ransack(name_cont: 'Tom').result # query students whose name contains 'Tom'
Student.ransack(name_start: 'Tom').result # query students whose name starts with 'Tom'

Here is an ob-arql, which is used to integrate Emacs org babel.

You can use Arql to view SQLite3 database files, for example:

arql -d db/development.sqlite3

After checking out the code, run bin/setup to install dependencies. You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and tags, and push the .gem file to rubygems.org.

Bug reports and pull requests are welcome on GitHub at https://github.com/lululau/arql. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the Code of Conduct.

The gem is available as open source under the terms of the MIT License.

Everyone interacting in the Arql project’s codebases, issue trackers, chat rooms and mailing lists is expected to follow the Code of Conduct.