Loading ...
Sorry, an error occurred while loading the content.

vim patch to add "sqlite" functions (built-in)

Expand Messages
  • Ron Aaron
    Here is a patch which adds an internal interface to SQLite. I ve used it heavily for my session code, for six months or more: ... *************** ... #endif +
    Message 1 of 6 , Nov 30, 2008
    • 0 Attachment
      Here is a patch which adds an internal interface to SQLite. I've used
      it heavily for my session code, for six months or more:

      *** src/eval.c.orig Mon Dec 1 08:39:38 2008
      --- src/eval.c Sun Nov 30 08:08:38 2008
      ***************
      *** 31,34 ****
      --- 31,35 ----
      #endif

      + #define FEAT_SQLITE 1
      #define DICT_MAXNEST 100 /* maximum nesting of lists and dicts */

      ***************
      *** 675,678 ****
      --- 676,684 ----
      static void f_spellsuggest __ARGS((typval_T *argvars, typval_T
      *rettv));
      static void f_split __ARGS((typval_T *argvars, typval_T *rettv));
      + #ifdef FEAT_SQLITE
      + static void f_sqlclose __ARGS((typval_T *argvars, typval_T *rettv));
      + static void f_sqlopen __ARGS((typval_T *argvars, typval_T *rettv));
      + static void f_sqlexec __ARGS((typval_T *argvars, typval_T *rettv));
      + #endif
      #ifdef FEAT_FLOAT
      static void f_sqrt __ARGS((typval_T *argvars, typval_T *rettv));
      ***************
      *** 7675,7678 ****
      --- 7681,7689 ----
      {"spellsuggest", 1, 3, f_spellsuggest},
      {"split", 1, 3, f_split},
      + #ifdef FEAT_SQLITE
      + {"sql_close", 1, 1, f_sqlclose},
      + {"sql_exec", 2, 3, f_sqlexec},
      + {"sql_open", 1, 1, f_sqlopen},
      + #endif
      #ifdef FEAT_FLOAT
      {"sqrt", 1, 1, f_sqrt},
      ***************
      *** 16207,16210 ****
      --- 16218,16312 ----
      }

      + #ifdef FEAT_SQLITE
      + // NOTE: these do not exactly match the sqlite3.h definitions but
      they will work
      + extern int sqlite3_close(int handle);
      + extern int sqlite3_open(char *fname, int *handle);
      + int sqlite3_exec(
      + int handle, /* An open database */
      + const char *sql, /* SQL to be evaluted
      */
      + int (*callback)(void*,int,char**,char**), /* Callback function */
      + void *, /* 1st argument to
      callback */
      + char **errmsg /* Error msg written
      here */
      + );
      +
      + f_sqlclose(argvars, rettv)
      + typval_T *argvars;
      + typval_T *rettv;
      + {
      + int handle = get_tv_number(&argvars[0]);
      + int retval = sqlite3_close(handle);
      + rettv->vval.v_number = retval;
      + }
      + f_sqlopen(argvars, rettv)
      + typval_T *argvars;
      + typval_T *rettv;
      + {
      + char_u *fname = get_tv_string(&argvars[0]);
      + int handle = 0;
      + int retval = sqlite3_open(fname, &handle);
      + rettv->vval.v_number = handle;
      + }
      +
      + static char *sep;
      + static int sqlite_callback ( list_T *list, int nCols, char **sCols,
      char **colNames)
      + {
      + int x;
      + char *temp;
      + char *temp2;
      + int len = 0;
      + int seplen = STRLEN(sep);
      + if (nCols > 1)
      + {
      + // jam the data together with a "|" separator:
      + // first count string length:
      + for (x=0; x<nCols; x++)
      + {
      + len += STRLEN(sCols[x]) + seplen ;
      + }
      +
      + // allocate the space:
      + temp = (char *) alloc(len+1);
      +
      + // now copy over the data:
      + temp2 = temp;
      +
      + for (x=0; x<nCols-1; x++)
      + {
      + STRCPY(temp2, sCols[x]);
      + STRCAT(temp2, sep);
      + temp2 += seplen + STRLEN(sCols[x]);
      + }
      + STRCPY(temp2, sCols[x]);
      + }
      + else
      + {
      + // just copy the data over:
      + temp = (char *) alloc(1 + STRLEN(sCols[0]));
      + STRCPY(temp, sCols[0]);
      + }
      +
      + list_append_string(list, temp, STRLEN(temp));
      + free(temp);
      + return 0;
      + }
      +
      + f_sqlexec(argvars, rettv)
      + typval_T *argvars;
      + typval_T *rettv;
      + {
      + int handle = get_tv_number(&argvars[0]);
      + char_u *sql = get_tv_string(&argvars[1]);
      + rettv->vval.v_list = NULL;
      + rettv->v_type = VAR_LIST;
      + sep = "|";
      + if (argvars[2].v_type != VAR_UNKNOWN)
      + {
      + sep = get_tv_string(&argvars[2]);
      + }
      + if (rettv_list_alloc(rettv) == OK)
      + sqlite3_exec(handle, sql, sqlite_callback, rettv->vval.v_list,
      NULL);
      + }
      + #endif
      +
      #ifdef FEAT_FLOAT
      /*
      *** src/version.c.orig Mon Dec 1 08:10:23 2008
      --- src/version.c Mon Dec 1 08:38:39 2008
      ***************
      *** 811,814 ****
      --- 811,816 ----
      { /* Add your patch description below this line */
      /**/
      + "SQLite interface",
      + /**/
      NULL
      };
      *** runtime/doc/eval.txt.orig Sun Nov 30 08:08:33 2008
      --- runtime/doc/eval.txt Mon Dec 1 09:05:28 2008
      ***************
      *** 1886,1889 ****
      --- 1886,1892 ----
      split( {expr} [, {pat} [, {keepempty}]])
      List make |List| from {pat} separated {expr}
      + sql_close( {handle} ) Number Close a SQLite database opened using
      [sql_open]
      + sql_exec( {handle}, {sql} [, {separator}] ) List Execute the SQL on
      the open database
      + sql_open( {filename} ) Number Open a SQLite database. Returns a
      {handle}
      sqrt( {expr} Float squar root of {expr}
      str2float( {expr}) Float convert String to Float
      ***************
      *** 5139,5142 ****
      --- 5142,5161 ----


      + sql_close( {handle} ) *sql_close()*
      + Closes the open SQLite database, whose {handle} was returned
      + by the [sql_open] function. Returns the SQLite result.
      +
      + sql_exec( {handle}, {sql} [, {separator}] ) *sql_exec()*
      + Execute the SQL (a string of valid SQL commands) on the opened
      + {handle}, which must have been returned from [sql_open]. The
      + return result is a List consisting of one String per row of
      + results. Columns within each row are separated by the "|"
      + character by default. Optionally, one may pass the
      + {separator} string to use a different separator.
      +
      + sql_open( {filename} ) *sql_open()*
      + Open the SQLite database in {filename}. The return result is
      + a {handle} which is passed to [sql_exec] and [sql_close]
      +
      sqrt({expr}) *sqrt()*
      Return the non-negative square root of Float {expr} as a

      --~--~---------~--~----~------------~-------~--~----~
      You received this message from the "vim_dev" maillist.
      For more information, visit http://www.vim.org/maillist.php
      -~----------~----~----~----~------~----~------~--~---
    • Tony Mechelynck
      ... I m not convinced of its usefulness -- this, however, is for its users to judge. One thing, however: it would be better to publish the help as a separate
      Message 2 of 6 , Nov 30, 2008
      • 0 Attachment
        On 01/12/08 08:05, Ron Aaron wrote:
        > Here is a patch which adds an internal interface to SQLite. I've used
        > it heavily for my session code, for six months or more:

        I'm not convinced of its usefulness -- this, however, is for its users
        to judge. One thing, however: it would be better to publish the help as
        a separate file (to be dropped in $VIM/vimfiles/doc/) rather than as a
        patch to $VIMRUNTIME/doc/eval.txt because the latter changes will get
        lost as long (as your patch is not "taken in" by Bram) as soon as the
        user synchronizes his runtime files (surely if he uses rsync, as I do
        now, or FTP, as I did when I was on Windows; I'm not sure about CVS,
        A-A-P or SVN).

        Best regards,
        Tony.
        --
        Then a man said: Speak to us of Expectations.

        He then said: If a man does not see or hear the waters of the Jordan,
        then he should not taste the pomegranate or ply his wares in an open
        market.

        If a man would not labour in the salt and rock quarries then he should
        not accept of the Earth that which he refuses to give of himself.

        Such a man would expect a pear of a peach tree.
        Such a man would expect a stone to lay an egg.
        Such a man would expect Sears to assemble a lawnmower.
        -- Kehlog Albran, "The Profit"

        --~--~---------~--~----~------------~-------~--~----~
        You received this message from the "vim_dev" maillist.
        For more information, visit http://www.vim.org/maillist.php
        -~----------~----~----~----~------~----~------~--~---
      • Ron Aaron
        Fair enough. Here is the modified patch, and a separate sqlite.txt help file: ... *************** ... #endif + #define FEAT_SQLITE 1 #define DICT_MAXNEST
        Message 3 of 6 , Dec 1, 2008
        • 0 Attachment
          Fair enough. Here is the modified patch, and a separate 'sqlite.txt'
          help file:

          *** src/eval.c.orig Mon Dec 1 08:39:38 2008
          --- src/eval.c Sun Nov 30 08:08:38 2008
          ***************
          *** 31,34 ****
          --- 31,35 ----
          #endif

          + #define FEAT_SQLITE 1
          #define DICT_MAXNEST 100 /* maximum nesting of lists and dicts */

          ***************
          *** 675,678 ****
          --- 676,684 ----
          static void f_spellsuggest __ARGS((typval_T *argvars, typval_T
          *rettv));
          static void f_split __ARGS((typval_T *argvars, typval_T *rettv));
          + #ifdef FEAT_SQLITE
          + static void f_sqlclose __ARGS((typval_T *argvars, typval_T *rettv));
          + static void f_sqlopen __ARGS((typval_T *argvars, typval_T *rettv));
          + static void f_sqlexec __ARGS((typval_T *argvars, typval_T *rettv));
          + #endif
          #ifdef FEAT_FLOAT
          static void f_sqrt __ARGS((typval_T *argvars, typval_T *rettv));
          ***************
          *** 7675,7678 ****
          --- 7681,7689 ----
          {"spellsuggest", 1, 3, f_spellsuggest},
          {"split", 1, 3, f_split},
          + #ifdef FEAT_SQLITE
          + {"sql_close", 1, 1, f_sqlclose},
          + {"sql_exec", 2, 3, f_sqlexec},
          + {"sql_open", 1, 1, f_sqlopen},
          + #endif
          #ifdef FEAT_FLOAT
          {"sqrt", 1, 1, f_sqrt},
          ***************
          *** 16207,16210 ****
          --- 16218,16312 ----
          }

          + #ifdef FEAT_SQLITE
          + // NOTE: these do not exactly match the sqlite3.h definitions but
          they will work
          + extern int sqlite3_close(int handle);
          + extern int sqlite3_open(char *fname, int *handle);
          + int sqlite3_exec(
          + int handle, /* An open database */
          + const char *sql, /* SQL to be evaluted
          */
          + int (*callback)(void*,int,char**,char**), /* Callback function */
          + void *, /* 1st argument to
          callback */
          + char **errmsg /* Error msg written
          here */
          + );
          +
          + f_sqlclose(argvars, rettv)
          + typval_T *argvars;
          + typval_T *rettv;
          + {
          + int handle = get_tv_number(&argvars[0]);
          + int retval = sqlite3_close(handle);
          + rettv->vval.v_number = retval;
          + }
          + f_sqlopen(argvars, rettv)
          + typval_T *argvars;
          + typval_T *rettv;
          + {
          + char_u *fname = get_tv_string(&argvars[0]);
          + int handle = 0;
          + int retval = sqlite3_open(fname, &handle);
          + rettv->vval.v_number = handle;
          + }
          +
          + static char *sep;
          + static int sqlite_callback ( list_T *list, int nCols, char **sCols,
          char **colNames)
          + {
          + int x;
          + char *temp;
          + char *temp2;
          + int len = 0;
          + int seplen = STRLEN(sep);
          + if (nCols > 1)
          + {
          + // jam the data together with a "|" separator:
          + // first count string length:
          + for (x=0; x<nCols; x++)
          + {
          + len += STRLEN(sCols[x]) + seplen ;
          + }
          +
          + // allocate the space:
          + temp = (char *) alloc(len+1);
          +
          + // now copy over the data:
          + temp2 = temp;
          +
          + for (x=0; x<nCols-1; x++)
          + {
          + STRCPY(temp2, sCols[x]);
          + STRCAT(temp2, sep);
          + temp2 += seplen + STRLEN(sCols[x]);
          + }
          + STRCPY(temp2, sCols[x]);
          + }
          + else
          + {
          + // just copy the data over:
          + temp = (char *) alloc(1 + STRLEN(sCols[0]));
          + STRCPY(temp, sCols[0]);
          + }
          +
          + list_append_string(list, temp, STRLEN(temp));
          + free(temp);
          + return 0;
          + }
          +
          + f_sqlexec(argvars, rettv)
          + typval_T *argvars;
          + typval_T *rettv;
          + {
          + int handle = get_tv_number(&argvars[0]);
          + char_u *sql = get_tv_string(&argvars[1]);
          + rettv->vval.v_list = NULL;
          + rettv->v_type = VAR_LIST;
          + sep = "|";
          + if (argvars[2].v_type != VAR_UNKNOWN)
          + {
          + sep = get_tv_string(&argvars[2]);
          + }
          + if (rettv_list_alloc(rettv) == OK)
          + sqlite3_exec(handle, sql, sqlite_callback, rettv->vval.v_list,
          NULL);
          + }
          + #endif
          +
          #ifdef FEAT_FLOAT
          /*
          *** src/version.c.orig Mon Dec 1 08:10:23 2008
          --- src/version.c Mon Dec 1 08:38:39 2008
          ***************
          *** 811,814 ****
          --- 811,816 ----
          { /* Add your patch description below this line */
          /**/
          + "SQLite interface",
          + /**/
          NULL
          };



          the help file:

          *sqlite.txt* For the 'sqlite_interface' patch to Vim version 7.2.
          Last change: 2008 Nov 02

          This patch is maintained by Ron Aaron (ron@...)

          After applying the sqlite_interface patch, the following three
          functions will be
          available:

          sql_close( {handle} ) *sql_close()*
          Closes the open SQLite database, whose {handle} was returned
          by the [sql_open] function. Returns the SQLite result.

          sql_exec( {handle}, {sql} [, {separator}] ) *sql_exec()*
          Execute the SQL (a string of valid SQL commands) on the opened
          {handle}, which must have been returned from [sql_open]. The
          return result is a List consisting of one String per row of
          results. Columns within each row are separated by the "|"
          character by default. Optionally, one may pass the
          {separator} string to use a different separator.

          sql_open( {filename} ) *sql_open()*
          Open the SQLite database in {filename}. The return result is
          a {handle} which is passed to [sql_exec] and [sql_close]


          vim:tw=78:ts=8:ft=help:norl:

          --~--~---------~--~----~------------~-------~--~----~
          You received this message from the "vim_dev" maillist.
          For more information, visit http://www.vim.org/maillist.php
          -~----------~----~----~----~------~----~------~--~---
        • David Fishburn
          ... Ron, out of interest sake, what is this providing that you cannot already have by shelling out to SQLite? For example, the dbext plugin have gear in place
          Message 4 of 6 , Dec 1, 2008
          • 0 Attachment
            On 12/1/08, Ron Aaron <rambamist@...> wrote:
            > After applying the sqlite_interface patch, the following three
            > functions will be
            > available:
            >
            > sql_close( {handle} ) *sql_close()*
            > Closes the open SQLite database, whose {handle} was returned
            > by the [sql_open] function. Returns the SQLite result.
            >
            > sql_exec( {handle}, {sql} [, {separator}] ) *sql_exec()*
            > Execute the SQL (a string of valid SQL commands) on the opened
            > {handle}, which must have been returned from [sql_open]. The
            > return result is a List consisting of one String per row of
            > results. Columns within each row are separated by the "|"
            > character by default. Optionally, one may pass the
            > {separator} string to use a different separator.
            >
            > sql_open( {filename} ) *sql_open()*
            > Open the SQLite database in {filename}. The return result is
            > a {handle} which is passed to [sql_exec] and [sql_close]

            Ron, out of interest sake, what is this providing that you cannot
            already have by shelling out to SQLite?

            For example, the dbext plugin have gear in place to interact with
            SQLite 2 ways. You can access either through shelling out to command
            line tools or through DBI (if it has a DBI driver). That way you can
            maintain SQL transactions.

            Thanks,
            Dave

            --~--~---------~--~----~------------~-------~--~----~
            You received this message from the "vim_dev" maillist.
            For more information, visit http://www.vim.org/maillist.php
            -~----------~----~----~----~------~----~------~--~---
          • Ron Aaron
            I was using the command-line and shelling . However, this is much faster. ... --~--~---------~--~----~------------~-------~--~----~ You received this message
            Message 5 of 6 , Dec 1, 2008
            • 0 Attachment
              I was using the command-line and shelling . However, this is much
              faster.

              On Dec 1, 8:43 pm, "David Fishburn" <dfishburn....@...> wrote:
              > On 12/1/08, Ron Aaron <rambam...@...> wrote:
              >
              >
              >
              > > After applying the sqlite_interface patch, the following three
              > > functions will be
              > > available:
              >
              > > sql_close( {handle} )                                      *sql_close()*
              > >            Closes the open SQLite database, whose {handle} was returned
              > >            by the [sql_open] function.  Returns the SQLite result.
              >
              > > sql_exec( {handle}, {sql} [, {separator}] )                *sql_exec()*
              > >            Execute the SQL (a string of valid SQL commands) on the opened
              > >            {handle}, which must have been returned from [sql_open].   The
              > >            return result is a List consisting of one String per row of
              > >            results.  Columns within each row are separated by the "|"
              > >            character by default.  Optionally, one may pass the
              > >            {separator} string to use a different separator.
              >
              > > sql_open( {filename} )                                     *sql_open()*
              > >            Open the SQLite database in {filename}.  The return result is
              > >            a {handle} which is passed to [sql_exec] and [sql_close]
              >
              > Ron, out of interest sake, what is this providing that you cannot
              > already have by shelling out to SQLite?
              >
              > For example, the dbext plugin have gear in place to interact with
              > SQLite 2 ways.  You can access either through shelling out to command
              > line tools or through DBI (if it has a DBI driver).  That way you can
              > maintain SQL transactions.
              >
              > Thanks,
              > Dave
              --~--~---------~--~----~------------~-------~--~----~
              You received this message from the "vim_dev" maillist.
              For more information, visit http://www.vim.org/maillist.php
              -~----------~----~----~----~------~----~------~--~---
            • Antony Scriven
              Hi ... Wouldn t returning a list of dictionaries be more useful? --Antony --~--~---------~--~----~------------~-------~--~----~ You received this message from
              Message 6 of 6 , Dec 5, 2008
              • 0 Attachment
                Hi

                2008/12/1 Ron Aaron <rambamist@...>:

                > [...]
                > sql_exec( {handle}, {sql} [, {separator}] ) *sql_exec()*
                > Execute the SQL (a string of valid SQL commands) on the
                > opened {handle}, which must have been returned from
                > [sql_open]. The return result is a List consisting of
                > one String per row of results. Columns within each row
                > are separated by the "|" character by default.
                > Optionally, one may pass the {separator} string to use
                > a different separator.
                > [...]

                Wouldn't returning a list of dictionaries be more useful? --Antony

                --~--~---------~--~----~------------~-------~--~----~
                You received this message from the "vim_dev" maillist.
                For more information, visit http://www.vim.org/maillist.php
                -~----------~----~----~----~------~----~------~--~---
              Your message has been successfully submitted and would be delivered to recipients shortly.