* Arql

  [[./README-zh_CN.org][中文]]

  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.

** Dependencies

   - 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=

** Installation
   
   Execute:

   #+begin_example
   $ gem install arql
   #+end_example

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

   #+begin_example
   $ sudo gem install arql
   #+end_example

** How to Use
*** Command Line Options
    
    #+begin_example
    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
    #+end_example

**** =-c, --config=CONFIG_FILE=

     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.

**** =-i, --initializer=INITIALIZER=

     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.
     
**** =-e, --env=ENVIRON=

     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:

     #+BEGIN_SRC yaml
         development:
           adapter: mysql2
           host: localhost
           username: root
           database: myapp_development
           pool: 5
           namespace: Dev
     #+END_SRC

     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

**** =-E, --eval=CODE=

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

**** =-S, --show-sql=

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

**** =-w, --write-sql=OUTPUT=

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

**** =-A, --append-sql-OUTOUT=

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

**** Database options

     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.

***** -a, --db-adapter=DB_ADAPTER

      Specify the database adapter, available values:

      -  =mysql2=
      -  =postgresql=
      -  =sqlite3=
      -  =sqlserver=
      -  =oracle_enhanced=

***** -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, when the SSH-related options are specified, arql will establish an SSH tunnel and connect to
      the

***** -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

*** Configuration Files

    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:

    #+BEGIN_SRC yaml
        development:
          host: localhost
          database: test
          username: root
          model_names:
            students: Seito
            teachers: ["LaoShi", "LS"]
    #+END_SRC

    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=
    
**** Example configuration file

     #+begin_example
     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
     #+end_example

     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.

*** Use as a REPL

    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:

    #+BEGIN_EXAMPLE
      arql -e dev
    #+END_EXAMPLE


    Arql provides a few Pry commands:


**** =info=

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

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

     =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:
     
     #+BEGIN_EXAMPLE
      info .*dev
     #+END_EXAMPLE

**** =m= or =l=

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

     #+begin_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    |         |
    +--------------------+------------------+------+---------+
     #+end_example

     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:

     #+BEGIN_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
     #+END_EXAMPLE

**** =show-sql= / =hide-sql=

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

     By default, SQL logs are not displayed:

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

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

     #+begin_example
     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
     #+end_example

**** =reconnect=

     =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=

     =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= 和 =sandbox-quit=

     =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:

        #+begin_example
        ARQL@demo247(main) [6] ❯ sandbox-enter
        ARQL@demo247 [sandbox] (main) [7] ❯
        #+end_example

     2. To exit sandbox mode:

        #+begin_example
        ARQL@demo247 [sandbox] (main) [7] ❯ sandbox-quit
        #+end_example

*** Use as a Code Interpreter

    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 .

**** Use =-E= the option

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

     #+begin_example
     $ arql -e dev -E 'puts Person.count'
     #+end_example

**** Specify a Ruby file as a command-line argument

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

     =test.rb=:

     #+BEGIN_SRC ruby
       puts Person.count
     #+END_SRC

     #+begin_example
     $ arql -e dev test.rb
     #+end_example

**** Read the code from the standard input

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

     #+begin_example
     $ echo 'puts Person.count' | arql -e dev
     #+end_example

** Additional Extension Methods
*** Module Methods for Namespace Modules
**** =q=

     =q= Used to execute SQL queries

     #+begin_example
     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]]
     #+end_example

**** =models=

     =models= Returns all model classes in that namespace

     #+begin_example
     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)]
     #+end_example

**** =tables=
     =tables= Returns all table names in the namespace

     #+begin_example
     ARQL ❯ Blog::tables
     => ["people", "posts"]
     #+end_example

**** =model_names=

     =model_names= Returns the names of all model classes in that namespace

     #+begin_example
     ARQL ❯ Blog::model_names
     => ["Demo::Person", "Demo::Post"]
     #+end_example

**** =create_table=

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

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

**** =dump=

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

     #+begin_example
     ARQL ❯ Blog::dump('~/data/blog.sql')
     #+end_example
*** Class Methods for Models
    
    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:

    #+begin_example
    ARQL ❯ ? Student.add_column
    #+end_example

**** =to_create_sql=

     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.

     #+begin_example
     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
     #+end_example

**** =t=


     =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:

     #+begin_example
     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     |         |
     +----|------------|------------------|-----------|-------|-----------|-------|---------|----------|---------+
     #+end_example


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

     -  =md=
     -  =org=
     -  =sql=
     -  =terminal= (default)

     例如：

     #+begin_example
     ARQL ❯ Blog::Person.t :sql
     #+end_example

     输出：

     #+begin_example
     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='人员表';
     #+end_example

**** =v=


     =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.

     例如：

     #+begin_example
     ARQL ❯ Blog::Post.v
     #+end_example

     输出：

     #+begin_example
     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, ""]]
     #+end_example

**** =vd=


     Use the =visidata= display table structure

**** =table_comment=

     Returns table annotations for the model

     例如：

     #+begin_example
     ARQL ❯ Blog::Post.table_comment
     #+end_example

     输出：

     #+begin_example
     "文章表"
     #+end_example

**** Add a field =add_column=

     #+begin_example
     Blog::Student.add_column :note, :text, comment: 'Remarks'
     #+end_example

**** Modify Fields =change_column=

     #+begin_example
     Blog::Student.change_column :note, :string, comment: 'Remarks'
     #+end_example

**** Delete the field =remove_column=

     #+begin_example
     Blog::Student.remove_column :note
     #+end_example

**** Add an index =add_index=

     #+begin_example
     Blog::Student.add_index :name
     Blog::Student.add_index [:branch_id, :party_id], unique: true, name: 'by_branch_party'
     #+end_example

**** Modify field comments =change_column_comment=

     #+begin_example
     Blog::Student.change_column_comment :note, 'Remarks'
     #+end_example

**** Modify the field defaults =change_column_default=

     #+begin_example
     Blog::Student.change_column_default :note, 'A note'
     #+end_example

**** Modify the field name =rename_column=

     #+begin_example
     Blog::Student.rename_column :note, :remark
     #+end_example

**** Modify the table name =rename_table=

     #+begin_example
     Blog::Student.rename_table :seitou
     #+end_example

**** Modify table comments =change_table_comment=

     #+begin_example
     Blog::Student.change_table_comment from: '', to: 'students table'
     #+end_example

**** Delete the table =drop_table=

     #+begin_example
     Blog::Student.drop_table
     #+end_example

**** Delete the index =remove_index=

     #+begin_example
     Blog::Student.remove_index :age
     Blog::Student.remove_index name: 'by_branch_party'
     #+end_example

**** Query Table Comments =table_comment=

     #+begin_example
     Blog::Student.table_comment
     #+end_example

**** Lists the indexes =indexes= of the table

     #+begin_example
     Blog::Student.indexes
     #+end_example
*** Instance Methods for Models
**** =t=

     =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.

     #+begin_example
     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)       |         |
     +----------------|-----------------|------------------|---------+
     #+end_example

     =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:

       #+begin_example
       Person.last.t(compact: true)
       Student.where(condition).t(compact: false)
       #+end_example

     - =: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

**** =to_insert_sql= / =to_upsert_sql=


     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.

     #+begin_example
     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`;"
     #+end_example

**** =v=


     =v= method is used to integrate with Emacs org babel.

***** =v= as an instance method for a model class


      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.

      #+begin_example
      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)", ""]]
      #+end_example

***** An array that contains only model instances

      #+begin_example
      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"]]
      #+end_example

***** An array containing only homogeneous hash objects

      #+begin_example
      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]]
      #+end_example

**** =dump=


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

**** =write_excel= / =write_csv=


     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= / =Array=

    =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=
     =t= Methods can also be called on an array that contains an ActiveRecord instance, or on a =ActiveRecord::Relation= /
     =ActiveRecord::Result= / =Ransack::Search= object.

     #+begin_example
     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 |
      +----+----------+--------+----------------------------------+-------+------+---------------------------+---------------------------+
     #+end_example


     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 :

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


     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:

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

     - =: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:

       #+begin_example
       Person.last(10).t(compact: true)
       Student.where(condition).t(compact: false)
       #+end_example

     - =: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=


     =v= method is used to integrate with Emacs org babel.

     #+begin_example
     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)", ""]]
     #+end_example

**** =vd=


     Use to =visidata= display "array" data

**** =write_csv= / =write_excel=


     =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=


     =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
*** Extension =Kernel= Methods
    
    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:

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

**** Create a table =create_table=

     #+begin_example
     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
     #+end_example

**** Create an intermediate table =create_join_table= for many-to-many relationships

     #+begin_example
     create_join_table :products, :categories do |t|
       t.index :product_id
       t.index :category_id
     end
     #+end_example

**** Delete the table =drop_table=

     #+begin_example
     drop_table :post
     #+end_example

**** Delete intermediate tables =drop_join_table= for many-to-many relationships

     #+begin_example
     drop_join_table :products, :categories
     #+end_example

**** Modify the table name =rename_table=

     #+begin_example
     rename_table :post, :posts
     #+end_example

**** =models=

     Returns the model classes that will be under all environment namespaces

**** =table_names=

     Returns table names for all environments

**** =model_names=

     Returns the model class names for all environments

**** =q=


     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
*** Other Extension Methods

**** JSON conversion and formatting

     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.

**** =String=

     1. =Srting#p=
        =p= The method is defined as follows:
        #+begin_example
        class String
          def p
            puts self
          end
        end
        #+end_example
        =​"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.
        #+begin_example
          excel = 'path/to/excel.xlsx'.parse
          csv = 'path/to/csv.csv'.parse
          json = 'path/to/json.json'.parse
        #+end_example

**** =ID=


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

     #+begin_example
     id = ID.long # Generate a snowflake algorithm ID
      id = ID.uuid # Generate a UUID
     #+end_example
*** Read and write Excel and CSV files

    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.

**** Parse Excel

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

     #+BEGIN_EXAMPLE
       ARQL ❯ parse_excel 'path/to/excel.xlsx'
     #+END_EXAMPLE

     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:

     #+BEGIN_EXAMPLE
       ARQL ❯ 'path/to/excel.xlsx'.parse_excel
     #+END_EXAMPLE

     =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:

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

**** Generate Excel

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

***** Generate Excel from Hash objects

      #+BEGIN_EXAMPLE
        ARQL ❯ obj.write_excel 'path/to/excel.xlsx'
      #+END_EXAMPLE

      =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

*****  Generate Excel from Array objects

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

      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

*****  Generate Excel from =ActiveRecord::Base= objects

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

      =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.

*****  Generate Excel from =ActiveRecord::Relation= objects

      #+BEGIN_EXAMPLE
        ARQL ❯ Student.where(gender: 'M').write_excel 'path/to/excel.xlsx', sheet_name: 'Male Students'
      #+END_EXAMPLE
      =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.

****  Parse CSV

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

     #+begin_example
     ARQL ❯ parse_csv 'path/to/csv.csv'
     #+end_example

     =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:

     #+BEGIN_EXAMPLE
       ARQL ❯ 'path/to/csv.csv'.parse_csv
     #+END_EXAMPLE

****  Generate a CSV

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

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

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

***** Generate a CSV from an =ActiveRecord::Base= object

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

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

*****  Generate a CSV from an =ActiveRecord::Relation= object

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

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

*** dump data


    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:

**** Export data from an Array object

     #+begin_example
     ARQL ❯ obj.dump 'path/to/dump.sql', batch_size: 5000
     #+end_example


     =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

**** Export data from the ActiveRecord::Base object

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


     =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.

**** Export data from the ActiveRecord::Relation object

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


     =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.

**** Call the dump class method of ActiveRecord::Base

     #+begin_example
     ARQL ❯ Student.dump 'path/to/dump.sql', no_create_table: false
     #+end_example


     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=  .

*** Plot

    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:

    #+BEGIN_EXAMPLE
      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
           └                                        ┘
    #+END_EXAMPLE

    Histo plot:

    #+BEGIN_EXAMPLE
      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

    #+END_EXAMPLE

*** Ransack

    Arql integrates Ransack:

    #+BEGIN_EXAMPLE
      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'
    #+END_EXAMPLE

*** Emacs Org Babel Integration

    Here is an [[https://github.com/lululau/spacemacs-layers/blob/master/ob-arql/local/ob-arql/ob-arql.el][ob-arql]], which is used to integrate Emacs org babel.

** Guides and Tips
*** [[./define-associations.org][Define Associations in Initializer File]]
*** [[./initializer-structure.org][Put Initialization Code for Different Environments in Different Files]]
*** [[./helper-for-datetime-range-query.org][Define Convenient Methods for Quickly Querying by Time]]
*** [[./auto-set-id-before-save.org][Automatically Set ID for New Objects Before Saving]]
*** [[./custom-configurations.org][Custom Configuration Items in the Configuration File]]
*** [[./sql-log.org][Automatically Record SQL Logs and REPL Input History]]
*** [[./fuzzy-field-query.org][Fuzzy Field Name Query]]
*** [[./oss-files.org][OSS Data Download and View]]
*** Use Arql to query SQLite3 database file

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

    #+BEGIN_EXAMPLE
      arql -d db/development.sqlite3
    #+END_EXAMPLE

** Development

   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 [[https://rubygems.org][rubygems.org]].


** Contributing

   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
   [[https://github.com/lululau/arql/blob/master/CODE_OF_CONDUCT.md][Code of Conduct]].


** License

   The gem is available as open source under the terms of the [[https://opensource.org/licenses/MIT][MIT License]].

** Code of Conduct


   Everyone interacting in the Arql project’s codebases, issue trackers, chat rooms and mailing lists is expected to follow
   the [[https://github.com/lululau/arql/blob/master/CODE_OF_CONDUCT.md][Code of Conduct]].