[sqlite] Katai based SQLite file format readers

[Alek Paunov]

SQLite file format [1] as executable description in Katai Struct
YAML [2] (contribution of Mr. Mikhail Yakshin [3]).

Katai seems as a very good library in the hot field of the declarative
binary formats manipulation.

King Regards,
Alek

[1] https://sqlite.org/fileformat.html
https://sqlite.org/fileformat2.html#record_format
[2] http://formats.kaitai.io/sqlite3/index.html
[3] 
https://github.com/kaitai-io/kaitai_struct_formats/commits/master/database/sqlite3.ksy

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Excel Pivot Table

[Alek Paunov]

Forgot to mention the most important feature of spreadsheet datasources:

Once you linked your e.g. pivot to a datasource, you always can refresh
it (usually Ritgh Click/Refresh), after a DB data change.

Kind Regards,
Alek
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Excel Pivot Table

[Alek Paunov]

Hi Dennis,

On 2019-10-17 02:11, Harris, Dennis wrote:

I sure this has been asked a 100 times but what is the best practice to get 
data from SQLITE to Excel?  I would like to have a pivot table that updates 
upon open.



Excel and it's leading open source alternative - LibreOffice Calc, both
have build-in DB/XML datasource facilities [*]. In LibreOffice user
interface should be sufficient. For Excel 2003 (the only version I have)
I am using VBA Macro, pasted bellow.

Testing:

1.Install ODBC driver for SQLite
  This is the leading OSS driver, developed by Mr.Cristian Werner:
  http://www.ch-werner.de/sqliteodbc/

2.Save module text bellow as vb.base somewhere, then import it into your
  workbook (Alt-F11, File/Import file) [**]

3.Name a new sheet in your workbook "sqlite". Paste the following 4
  lines in column A starting from A1

```
c:\path\to\your\database.sqlite

tables_and_views select name, 'select * from ' || name sql, type from
sqlite_master where type in ('table', 'view') order by name
```

4.Select Cell A3 (containing tables_and_views), press Ctrl+Shift+d.
  Expected result is a table (name, sql) placed from A6.

5.In any cell like A6 - which contains name (suitable for a Excel
  identifier - sheet-name, QueryTable name, etc) and sql in the adjacent
  cell you can press Ctrl-d for table and Ctrl-t from pivot.

Step 4. is optional (just for illustration of what is expected on step
5.). From step 3. only the path placed in A1 is mandatory.

Hope this mess still work in your Excel version :-)

Kind Regards,
Alek

[*] IMHO, spreadsheet applications should be used primarily for
browsing/pivoting of data based on DB/XML datasources, not as
development place, where the user tries with a bunch of fragile formulas
and macros to achieve the same result as of few lines SQL.

[**] If you want these macros to be available in all workbooks, import
them in a hidden workbook placed in the path, specified at
"Tools/Options/General/At startup" (Usually persnal.xls) or something
similar in your version of Excel.


db.bas:
---
Attribute VB_Name = "db"
Option Explicit
Private Function sheet_db_name(sheet As Excel.Worksheet)
Select Case ActiveSheet.name
Case "sqlite"
sheet_db_name = sheet.range("a1")
Case "mysql-sample"
sheet_db_name = sheet.range("a1")
Case Else
sheet_db_name = ""
End Select
End Function

Private Function connection_string(connection_key As String, Optional 
db_name As String) As String

Select Case connection_key
Case "sqlite"
Let connection_string = _
"ODBC;DRIVER={SQLite3 ODBC Driver};DATABASE=" & db_name
Case "dsn-sample"
Let connection_string = _
"ODBC;DSN=sample;Uid=aUsername;Pwd=aPassword"
Case "mysql-sample"
Let connection_string = _
"ODBC;DRIVER={MySQL ODBC 5.1 
Driver};SERVER=127.0.0.1;Port=3306;" _

& "DATABASE=" & db_name & ";USER=aPassword;OPTION=3;"
Case Else
Let connection_string = _
"OLEDB;Provider=SQLOLEDB; Server=127.0.0.1,1433; " & _
"User ID=aUsername; Password=aPassword; Initial 
Catalog=" & connection_key

End Select
End Function

Private Function sheet_get(name As String) As Excel.Worksheet
Dim sel As Object
Dim sheet As Excel.Worksheet
For Each sel In ActiveWorkbook.Sheets
If sel.name = name Then
Set sheet_get = sel
Exit Function
End If
Next
End Function

Private Sub db_fetch( _
sql As String, connection_key As String, _
range_at As Excel.range, fetch_name As String, _
Optional db_name As String = "", _
Optional as_pivot As Boolean = False _
)
Dim sheet As Excel.Worksheet
Dim db_connection_string As String

Let db_connection_string = connection_string(connection_key, db_name)
Set sheet = range_at.Worksheet

If Not Err Then
If as_pivot Then
Dim cache As Excel.PivotCache
Set cache = ActiveWorkbook.PivotCaches.Add(xlExternal)
With cache
.Connection = db_connection_string
.CommandType = xlCmdSql
.CommandText = sql
End With

Dim pivot As PivotTable
Set pivot = sheet.PivotTables.Add(cache, range_at, fetch_name)
Let pivot.DisplayImmediateItems = True
Else
With sheet.QueryTables.Add(db_connection_string, range_at, sql)
.name = fetch_name
.FieldNames = True
.RowNumbers = False
.MaintainConnection = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True

[sqlite] [OT] DVCS/Data (was: Re: Network file system that support sqlite3 well)

[Alek Paunov]

Off topic

And a something relatively new from the future (I hope)
filed of DVCS/Data (like SQLite+Fossil in one).

Dolt:
https://github.com/liquidata-inc/dolt

Underlying versioned DB:
https://github.com/attic-labs/noms

Data commit sample:
https://www.dolthub.com/repositories/oscarbatori/mta-data/commits/gna1lpej84qgo1esn1o4iat9742lkiua

Kind Regards,
Alek
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Network file system that support sqlite3 well

[Alek Paunov]

On 2019-10-16 01:47, Peng Yu wrote:


Is there a solution that are known to fill in this niche? Thanks.



Would be clusteded SQLite (distributed SQLite instead of central shared
DB) be a good option for your project? - Bellow, I am pasting my
bookmarks for few well established projects developing that approach.

Kind Regards,
Alek

- notes:
  - label: SQLite notes
  - theme:
- label: SQLite Clustering
  xmlns:n: decl:sqlite-notes-1

- project:
  - gh: https://github.com/canonical/dqlite
  - bookmark:
- label: WAL replication patch on top of the original SQLite tree
  url: 
https://sourcegraph.com/github.com/canonical/sqlite/-/compare/version-3.29.0...version-3.29.0%2Breplication3


  - n:replication:
- style-code: single-master/bininary-log-shipping
- bookmark:
  - url: https://en.wikipedia.org/wiki/Log_shipping

  - n:interface:
- style-code: exising-c-api/patched-lib/libsqlite3
- note:
  - >-
Supports existing SQLite Linux application, by switching to 
patched libsqlite3

- note:
  - >-
Out of the box build for Windows is not ready yet.

  - n:interface:
- style-code: custom-protocol/binary-wire-protocol
  url: 
https://github.com/canonical/dqlite/blob/master/doc/protocol.md


- project:
  - name: BedrockDB
gh: https://github.com/Expensify/Bedrock
license-code: MIT

  - n:replication:
- style-code: single-master/distribute-update-sql-statements
- documentation:
  - url: https://bedrockdb.com/blockchain.html
- documentation:
  - url: https://bedrockdb.com/synchronization.html

  - n:interface:
- style-code: exising-protocol/mysql-wire-protocol
  url: https://bedrockdb.com/#how-to-use-it
- note:
  - >-
Supports any MySQL client (But of course accpets only 
SQLite SQL dialect)


  - n:interface:
- style-code: exising-protocol/mysql-wire-protocol
- note:
  - >-
Trivial socket protocol, json results

  - n:interface:
- style-code: cli-tool

- project:
  - name: rqlite
gh: https://github.com/rqlite/rqlite
license-code: MIT

  - replication:
- style-code: single-master/distribute-update-sql-statements

  - bookmark:
- tag: news-articles
  url: http://www.philipotoole.com/tag/rqlite/
- note:
  - >-
News about rqlite, gorqlite

  - n:interface:
- style-code: rest-api
  url: https://github.com/rqlite/rqlite/blob/master/DOC/DATA_API.md
- note:
  - >-
Interface style: Serving HTTP API

  - n:interface:
- style-code: cli-tool
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe sqlite over remote filesystem?

[Alek Paunov]

On 2018-08-14 16:07, Wout Mertens wrote:


Is there a way to use safely sqlite in this situation, perhaps by using
extra lock files or some other additional mechanism?

One solution I can think of involves sending all writes through a single
master, via files describing changes and lots of polling, but that seems
really outlandish.



From the mail archives I see you have asked few questions about Bedrock 
SQLite cluster solution last year.


I am curious, Did you considered adapting writing in your use-case to 
Bedrock? AFAIK, you can read from Bedrock instance DBs safely without 
further adaptation.


Kind Regards,
Alek

[1] https://github.com/Expensify/Bedrock
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stored Procedures

[Alek Paunov]

On 2018-05-09 03:56, Richard Hipp wrote:

...



The other benefit of stored procedures is that it provides a way to
code up a common operation once (correctly!) and store it in the
database, rather than having multiple clients all have to work out the
operating themselves (some of them perhaps incorrectly).  The usual
way of handling that in SQLite is to store a script in a text column
someplace, then execute them as needed.  SQLite began life as a TCL
extension, and so naturally TCL scripts work very well for this kind
of thing.



To amplify the remark above, for me, the Stored procedures are mostly 
method to add methods :-) to the "cold" data objects encoded in tables, 
just like the OO style SQLite C API defines interfaces and behavior on 
top of "cold" C structures.


That way, schemes and non-trivial calculations on top of them can be 
encapsulated and reused as modules (or packages as named in some design 
tools).


Practical example of the benefit is that e.g. same complex turnover 
report which implementation includes bunch of intermediate calculations, 
could be used with same SQL call from Python desktop application, 
directly as Excel Data Source or on partial replica through SQL.js [1] 
in the browser, without coding the same thing 3 times: on Python, VBA 
and JS.


Having said that, Let's take the survey: How many list readers really 
missed the Stored procedures feature?


If there are enough interest, I would be happy to discuss an (possibly 
naive) idea (*) for implementation (of simple e.g. Sybase 12.5 level 
procedures) mostly in pure SQL with just tiny C runner as UDF TValued 
Function [2].


Kind Regards,
Alek

(*) On the sqlite mailing list, we see permanent conflict of interests: 
On the one side are 99% of the users, for which sqlite is just better 
(than e.g. compressed json) storage option (usually for small as size or 
simple as structure data).


On the other, are the advanced users - these with complex applications 
who [especially after the great advancements from the last years (CTEs, 
JSON and so on)] try to encapsulate as much as possible business logic 
into the DB.


My humble opinion is that for us, the minority, the best move is to look 
for approaches and collaboration on "incubating" valuable advanced 
features initially outside of the SQLite core, then eventually apply 
result solutions in our own projects and finally propose for inclusion 
in the core library only small key components (which lead to 
inefficiencies or maintenance burden when living outside).


For the Stored procedures, borrowing from the SQLite design, I think 
that it is possible the non SQL, procedural part of the code to be 
translated to very high level VM with just few instructions, which uses 
attached :memory: DB for stack/registers/variables and (tree) table for 
the code. [This can be seen as step further above the approach cited by 
Dr. Hipp SQLs kept in table and used by executed by TCL script.]


That way, at some stage, SQLite could even have several procedural 
dialect implementations - TSQL, MySQL, etc. :-)


[1] https://github.com/kripken/sql.js/
[2] https://www.sqlite.org/vtab.html#tabfunc2
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disassemble extension (Was: kooky thought: a vm-only build (for embedded). feasible?)

[Alek Paunov]

On 2018-04-15 21:54, dave wrote:
  
Anway, has this been discussed before?  Or is it a fool's errand?




Same kind of fool here :-)

After Mr.Hipp response, which suggest that hwaci.com does not sell SSE 
anymore, the community could try to reproduce the same idea against 
current VDBE instruction set (ideally with development communication 
hosted here, so we can receive advice from the SQLite core team).


Does anybody have an idea for table set (schema) for the 
assemble/disassemble functions in question to target?


Kind Regards,
Alek

Beyond concrete project uses and the fun of playing with VDBE code with 
SQL :-), I think that disassemble extension will be appropriate for 
university curses too.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Alek Paunov]

On 2017-08-02 20:24, Nico Williams wrote:

On Wed, Aug 02, 2017 at 07:48:52PM +0300, Alek Paunov wrote:

On 2017-08-02 18:23, Sylvain Pointeau wrote:
...


CREATE SEQUENCE IF NOT EXISTS SEQ_1 START WITH 12123;

insert into MYTABLE(SPECIFIED_NUMBER, OTHERINFO) values (seq_1.nextval,
'other info')



BTW, your request is somewhat related with the brand new union-vtab SQLite
extension [1] (for optimized union view of identical tables) - if we have
the basic PostreSQL nextval in SQLite, the following pattern would become
possible:


I find the union vtab thing mostly not useful because it requires
constraining the tables to be union'ed to have distinct ranges of
rowids.  This is of too narrow utility.


Indeed - My assertion was simply wrong because of the ranges 
requirement. Query in the VTable DDL also was totally not what the 
implementation expects - sorry for the noise :-(.




PostgreSQL-style sequence support would be much more general.

If the union vtab thing is aiming to make it easier to implement
something like table inheritance, I'll warn you right off that
PostgreSQL's INHERIT is utterly useless -- do not copy it.

I've implemented "inheritance" with triggers to map DMLs on "derived"
tables onto "base" tables.  That works and is much more general.  If you
need a rowid, however, the triggers have to do more work, first acquring
the rowid from the base table, then setting it on the derived table
rows, and this can get tricky.


Yes. I meant exactly that usecase - where many logically "subclass" 
tables share common "address" (rowid) space, so that, any object (or the 
rest of the DB) could "reference" objects in the whole hierarchy (like 
unified value references in script languages data-models).


Of course, even without sequence build-in, both your suggestions: main 
"object" table with triggers on every subclass table or simple UDF 
next_serial are enough to build such representation.


King Regards,
Alek
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Alek Paunov]

On 2017-08-02 18:23, Sylvain Pointeau wrote:
...


CREATE SEQUENCE IF NOT EXISTS SEQ_1 START WITH 12123;

insert into MYTABLE(SPECIFIED_NUMBER, OTHERINFO) values (seq_1.nextval,
'other info')



BTW, your request is somewhat related with the brand new union-vtab 
SQLite extension [1] (for optimized union view of identical tables) - if 
we have the basic PostreSQL nextval in SQLite, the following pattern 
would become possible:


create sequence doc_id start with 40;
create table doc(doc_id integer primary key default nextval('doc_id'), doc);
create table inbox_doc(doc_id integer primary key default 
nextval('doc_id'), doc);


create virtual table temp.doc using unionvtab(
'select doc_id, doc from doc union all select doc_id, doc from 
inbox_doc'
);
select doc from temp.doc where doc_id = 42;

So, maybe nextval is already on the roadmap ;-)

Kind Regards,
Alek

[1] https://sqlite.org/unionvtab.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqldiff nowadays

[Alek Paunov]

Hi MM,

Sorry for the late replay - I usually manage to check the list only once 
a day :-(.

On 2016-03-22 16:05, MM wrote:
...

>> If, by chance, you are on something Fedora based, I could give you some
>> hints how to help our lead maintainer - Jan Stanek with the package
>> enhancement myself.

...

>
> Indeed, I am using fedora 23. I have the following rpms installed (though
> we are getting a bit out of scope for this list I suppose):

Great - Let's try to sort the issue out then!

Definitely it is not out of scope - At least with the goal of offloading 
the core sqlite team in mind, we should explain how the Linux 
distributions works, so future _packaging_ issues to be addressed to 
appropriate bug trackers instead of bothering upstream directly (i.e. 
this list).

>
> sqlite-libs-3.11.0-3.fc23.x86_64
> sqlite-3.11.0-3.fc23.x86_64
> sqlite-analyzer-3.11.0-3.fc23.x86_64
> sqlite-doc-3.11.0-3.fc23.noarch
> sqlite-devel-3.11.0-3.fc23.x86_64
>
> none of them has sqldiff.
>

I am aware of that - I have tried to make a remark above that we (the 
interested sqlite/fedora users) should try to assist our package 
maintainer (Jan Stanek) with the inclusion of the tool.

As first step, I prepared a test package set with added sqldiff - To 
test you could try:

dnf -y copr enable decalek/sqlite.tools
dnf -y install sqlite-tools

If it works for you, I will try to enumerate the tasks need to be done, 
so the tool to be included in the main Fedora package repositories.

Regards,
Alek

[sqlite] sqldiff nowadays

[Alek Paunov]

On 2016-03-22 13:49, Richard Hipp wrote:
> On 3/22/16, MM  wrote:
>> Hello,
>> I can see sqldiff appearing here:
>>
>> https://www.sqlite.org/sqldiff.html
>>
>> and in the downloads page as part of a linux 32bit binary package.
>> Alas I don't see any 64bit package.
>
> The 32bit binaries will run fine on 64bit machines.
>
>>
>> Given a distro-installed 64bit sqlite binary and libs, which part of the
>> sources would 1 need to download only sqldiff and build only that, in 64bit
>> as well.
>>
>
> Download the canonical source code distro and type:
>
>   ./configure; make sqldiff
>

In addition to the universal advice above, if you are not only 
interested in getting the tool working once, but also in _future_ 
sustainable support and consistency delivered for you by your OS 
distributor, please tell us which Linux distribution you are using.

Then other (more experienced) same distro users on the list eventually 
will be able to point you to the appropriate procedure and contacts, so 
the system sqlite package to be extended with sqldiff for all distro users.

If, by chance, you are on something Fedora based, I could give you some 
hints how to help our lead maintainer - Jan Stanek with the package 
enhancement myself.

Regards,
Alek

[sqlite] Schema-less JSON SQLite DB?

[Alek Paunov]

Hi Hayden,

On 14.07.2015 03:43, Hayden Livingston wrote:
>   Is there a concept of a schema-less JSON SQLite DB?
>
>   My reason is simple: versioning. We have lot of business metrics that
>   get updated let's say once a month, and we need to be agile to get
>   them. Right now, we just put the version in the SQLite file, and then
>   make sure no queries cross the boundaries.
>
>   Secondly, we have requirements for slightly hierarchal data, i.e.
>   mostly row form, but then some guy wants to put an object.
>
>   What's the SQLite community heading towards if at all?

Given the number of the threads in the list, it seems a
lot of people want hierarchical data in SQLite :-)

>
>   Do others have experiences and requirements similar to this?

My experience:

Few years ago, during small project about translating set of
procedures from one to another SQL dialect, we initially
tried to use XML database for the task, but finally become
to solution to manipulate the parsed procedures in SQLite
using a schema with following simplified core:

doc(d, name)
   node(dn, d, x, y, type, value)
 attribute(dn, type, value)
 reference(dn, type, ref_dn)

[Where (x, y) are equal to (row, column) when one dump the
tree to text file with indent = 1]

This schema is a variation of the BaseX encoding schema:

http://docs.basex.org/wiki/Node_Storage

where:
   - we used y (the depth of the node) instead of DIS
 (distance to the parent)
   - we omitted SIZ (size of the subtree) - it is easily
 calculable (next(on same y).x - x).
   - we used single integer .type as replacement of NS, KIND, Tag
 name + expected scalar type
   - we split the scalar attributes (XML attributes) in
 separate table - attribute and moved the first (only
 in our case) text() value to node.value.
   - we added reference (links between nodes)

Actually there were other modifications like e.g. 
node_range_offset(dn_from, dn_to, offset) table, which we
applied for faking insertions and deletions of whole subtrees
(logical node.x was function of that table and stored
node.x).

Sometime next year I am planning to revive that experiment,
this time for storing/generating configuration data (for
Linux configuration parsed with the augeas tool). Now with the
presence of the powerful CTE feature in SQLite the things
may be a way more easier.

Meantime (given that you have shared with the list that your
project is a business oriented one), I would like to propose
to you, to initiate a sponsorship and a team including
interested list members for digging the subject further.

The team could consolidate the different approach ideas,
existing open source components and discuss what will be
most appropriate and light as implementation resources for
use cases like yours + eventually report to the the SQLite
core team which additional APIs would make the implementation
more efficient.

Kind regards,
Alek

[sqlite] Where sqlite vtables are used? (was: Search for text in all tables)

[Alek Paunov]

On 05.12.2014 12:32, Dominique Devienne wrote:

On Fri, Dec 5, 2014 at 10:24 AM, Max Vlasov  wrote:


Not particularity the answer to your question, but rather a method you
or others might use.
I once implemented a virtual table "allvalues" that outputs all
database values with (hope self-explaining) fields

   TableName, TableRowId, FieldName, Value

that allows also exploring unknown complex databases.



Thanks for sharing Max! That's a great idea. I'd never would have thought
of that.

I agree with you, Sqlite's virtual tables are great. --DD


Where have you seen/developed interesting vtable implementations?

Please respond with a short description and/or link to sqlite vtable 
source locations you know :-)


Kind regards,
Alek

P.S. Few weeks ago, I tried to materialize few internet searches under 
sqlite_addon tag in openhub.net (formerly ohloh.net) DB, but the list is 
too short yet:


https://www.openhub.net/tags/sqlite_addon
https://www.openhub.net/tags/sqlite_vtable

Better ideas about where to collect/categorize available extensions?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[Alek Paunov]

On 25.08.2014 20:47, Richard Hipp wrote:

On Mon, Aug 25, 2014 at 1:21 PM, forkandwait  wrote:


You used the word "immense" which I like - it is an apt description of

the

knowledge and effort needed to add windowing functions to SQLite (and
probably any other database engine for that matter).


Hehe.  I would be interested in any of your specific thoughts on the
immensity of it.  I can imagine that most of the work would be in the
parser, but things always simpler to non-experts ;)



Parsing is the easy part.  The tricky part is the code generator - the
piece that takes the abstract syntax tree that the parser generates and
turns it into bytecode that renders the desired output, taking care to
correctly handle the myriad corner cases.   Then comes the tedious part of
writing 100% MC/DC test cases.



This is not a fresh idea, I am dropping it again, because I continue to 
think that something in that direction could be useful - mostly for 
studying SQLite in a university environment, but also for on-demand 
research like the OPs feature request, where nor compilation time, nor 
the full soundness of the generated code are critical:


- Single new supported SQLite feature bundle:
  - "Standard" database schema for representing "disassembled"
VDBE programs
  - SQLite extension consisting of:
- function "disassemble" for dumping prepared statement to the
  above schema
- function "assemble" for loading and linking VDBE program from
  given rowid of the schema for execution as prepared statement.

- Community project sqlite-asm-tools (possibly coordinated trough 
dedicated list @sqlite.org), aimed to help further development with more 
high level tools over that VDBE schema like: code templates application, 
code pattern marchers, manipulation methods, visualizations, etc.


It seems to me that the above basis will be enough for student projects 
like MERGE implementation or Stored procedures or even new languages 
experiments, just like the myriad of academic experiments on top of JVM, 
LLVM and other backends, some of them far away of the popularity of SQLite.


Kind regards,
Alek

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with recursive CTE

[Alek Paunov]

On 25.08.2014 15:42, Frank Millman wrote:


I have upgraded to version 3.8.6, and I can confirm that it now works.
Thanks very much, Richard and Keith

Now I have to figure out how to get Python to use the upgraded version,
but that is one for the python mailing list.



You may consider upgrade to the current Fedora release - F20, which 
comes with sqlite-3.8.6 [1]. F18 is already out of support anyway.


[As you know, in Fedora every binding points to the system sqlite, 
including both the standard Python library module (sqlite3) and the well 
known specialized/full wrapper python-apsw]


Kind regards,
Alek

[1] https://apps.fedoraproject.org/packages/sqlite

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual table API performance

[Alek Paunov]

On 05.03.2014 11:02, RSmith wrote:


On 2014/03/05 10:41, Dominique Devienne wrote:

On Tue, Mar 4, 2014 at 9:41 PM, Elefterios Stamatogiannakis

One thing that IMHO long term might improve the situation would be if
SQLite's own "native" tables would use the same Virtual Table API,//...

...//Of course, the above is a "naive" abstract reflection which ignores
the realities of VDBE, so it may sound rubbish to actual SQLite
developers. Apologies for that. --DD


I don't think it is rubbish at all, but maybe idealistic.  The biggest
problem I can see from making API's pov is that you can at any time
alter, update, change the way SQLIte (or any other API) works with the
base check that the input values produce the same (or maybe
more-correct) results.  Once you let the VT use the same API, any change
is a potential change to how other people's programmed interfaces need
to talk to - or get data from - the SQLite engine. This cannot simply
change on a whim, so the levels of separation remain needed.

That said, I'm all for making a more efficient VT API, but it would
probably need to be "new" functionality since I cannot see how the
existing interface could implement any of the mentioned enhancements
without breaking existing behaviour. The OP's xNextRow suggestion seems
a good idea, but opens up a whole can of what-ifs which other posters
have alluded to, but something to that effect might be worthwhile if the
efficiency bonus is significant.



The whole thread so far is based on the OP observations in mixed C/PyPy 
and apsw/CPython environments (as being said already, we suffering the 
noise in both cases).


To be helpful to the SQLite team, before proposing any changes, please 
let someone show some well designed, pure C vtable implementation 
demonstrating the possible vtable interface inefficiency.


Let's remember that all xNextRow, xNextPage optimizations are 
applicable only for "select *" cases, not in the general "select f(x), 
y" case.


Kind regards,
Alek

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual table API performance

[Alek Paunov]

On 04.03.2014 20:25, Eleytherios Stamatogiannakis wrote:

On 04/03/14 20:11, Alek Paunov wrote:

On 04.03.2014 15:05, Eleytherios Stamatogiannakis wrote:

On 03/03/14 03:01, Alek Paunov wrote:

It seems that the easiest optimization for this (very often) VT use
case
(bulk streaming) is SQLite add-on in _C_ to be written, implementing
vtable interface specialization containing xNextPage "buffering" let's
say 4K rows or even better 16KB data (in addition to your initial
proposal of xNextRow).

The technical question is: how the rows to be encoded? You said
initially that you use some compressed format. But for such extension,
to gain more traction in the future, it would be better probably a more
standard format to be chosen.

a) Rows represented in native SQLite3 format [3]
b) ... native SQLite4 format
c) Some wide used encoding near to SQLite types [4]
d) ...

[3] http://www.sqlite.org/fileformat.html#record_format
[4] https://github.com/msgpack/msgpack/blob/master/spec.md#formats

IMHO, It would be very nice if some common conventions for binary
record
streaming could be discussed and adopted across the SQLite binding and
add-on developers. The possible applications are not limited only to
vtables ;-).


SQLite doesn't need any special format for the records to be passed over
to it. It already has the "bind" API which would be very suitable for
the xNextRow function too.



It seems that I do not know the sqlite3_ API very well.

http://www.sqlite.org/c3ref/funclist.html

Would you like to point me to the "bind" API page?


In the link that you posted above, look for all the sqlite3_bind_x
functions. In SQLite the bind API is used to pass parameters to prepared
statements. The way the the bind API works is that you have a statement
parameter "row", and you fill it by saying:

Set column 1 of statement parameter "row" to an int with value 10
Set column 2 of statement parameter "row" to an float with value 3.5
...

So instead of SQLite calling back for each column, in the bind API
"way", the program calls SQLite to fill a row's values.



Ah, OK. Let see if I finally understood your idea:

- exec "insert into t select * from vt"
- VDBE calls xNextRow
- in xNextRow implementation, the Python code calls something like 
bind_xxx for each column with the scalar addresses (allocated by you)

- xNextRow returns, VDBE inserts the row, you clean on next step

Questions:

What stops you to make this wrapper right now (e.g. as apsw patch or 
standalone sqlite add-on loaded by PyPy FFI)?


How you expect this model (managing one per cell count of scalar 
allocations during the query) to perform in comparison with passing 
encoded row pages (memory chinks) between sqlite and the script engine 
especially when it is not PyPy or LuaJIT?


Regards,
Alek

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual table API performance

[Alek Paunov]

On 04.03.2014 15:05, Eleytherios Stamatogiannakis wrote:

On 03/03/14 03:01, Alek Paunov wrote:

It seems that the easiest optimization for this (very often) VT use case
(bulk streaming) is SQLite add-on in _C_ to be written, implementing
vtable interface specialization containing xNextPage "buffering" let's
say 4K rows or even better 16KB data (in addition to your initial
proposal of xNextRow).

The technical question is: how the rows to be encoded? You said
initially that you use some compressed format. But for such extension,
to gain more traction in the future, it would be better probably a more
standard format to be chosen.

a) Rows represented in native SQLite3 format [3]
b) ... native SQLite4 format
c) Some wide used encoding near to SQLite types [4]
d) ...

[3] http://www.sqlite.org/fileformat.html#record_format
[4] https://github.com/msgpack/msgpack/blob/master/spec.md#formats

IMHO, It would be very nice if some common conventions for binary record
streaming could be discussed and adopted across the SQLite binding and
add-on developers. The possible applications are not limited only to
vtables ;-).


SQLite doesn't need any special format for the records to be passed over
to it. It already has the "bind" API which would be very suitable for
the xNextRow function too.



It seems that I do not know the sqlite3_ API very well.

http://www.sqlite.org/c3ref/funclist.html

Would you like to point me to the "bind" API page?


For a paging API (which IMHO is too complex ATM), the bind API could be
extended with a row number parameter.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual table API performance

[Alek Paunov]

On 02.03.2014 21:38, Elefterios Stamatogiannakis wrote:

Under this view, the efficiency of the virtual table api is very
important. Above query only uses 2 VTs in it, but we have other queries
that use a lot more VTs than that.


Max tests in C shows 2x CPU work, but he explains that the test is not 
very sound, so let's say somewhere between 1x-2x. Your tests - 3x time.


As you have already identified, the real reason probably is the million 
scale callback quantity across the VM barrier - I do not follow PyPy, 
but see these notes [1] by Mike Pall - the LuaJIT author (LuaJIT is the 
leading project in the trace compilers filed):


[1] http://luajit.org/ext_ffi_semantics.html#callback_performance

Also from one of the dozens of threads touching the subject:

[2] http://www.freelists.org/post/luajit/Yielding-across-C-boundaries,3

```
Entering the VM needs a lot of state setup and leaving it isn't
free either. Constantly entering and leaving the VM via a callback
from C *to* Lua has a high overhead. For short callbacks, the
switching overhead between C and Lua may completely dominate the
total CPU time.

Calling an iterator written in C via the FFI *from* a Lua program
is much cheaper -- this compiles down to a simple call instruction.
```

Unfortunately, for your "insert into t select * from vt" case an the 
callback/iterator transformation is not possible (we do not have 
repetitive _step call to invert the control somehow). What to do?


It seems that the easiest optimization for this (very often) VT use case 
(bulk streaming) is SQLite add-on in _C_ to be written, implementing 
vtable interface specialization containing xNextPage "buffering" let's 
say 4K rows or even better 16KB data (in addition to your initial 
proposal of xNextRow).


The technical question is: how the rows to be encoded? You said 
initially that you use some compressed format. But for such extension, 
to gain more traction in the future, it would be better probably a more 
standard format to be chosen.


a) Rows represented in native SQLite3 format [3]
b) ... native SQLite4 format
c) Some wide used encoding near to SQLite types [4]
d) ...

[3] http://www.sqlite.org/fileformat.html#record_format
[4] https://github.com/msgpack/msgpack/blob/master/spec.md#formats

IMHO, It would be very nice if some common conventions for binary record 
streaming could be discussed and adopted across the SQLite binding and 
add-on developers. The possible applications are not limited only to 
vtables ;-).


Kind regards,
Alek

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Still trying to track down loadable extensions

[Alek Paunov]

On 21.02.2014 02:05, Nico Williams wrote:

https://github.com/slightfoot/sqlite3-extensions
https://github.com/salviati/sqlite3-lz4
https://github.com/ralight/sqlite3-pcre
http://sqlite.mobigroup.ru/wiki?name=extensions
http://sqlite.mobigroup.ru/wiki?name=utils
https://github.com/djodjo/sqlite3ext_parse_json
https://github.com/fnoyanisi/sqlite3_capi_extensions
https://github.com/mrwilson/squib
https://github.com/evsukov89/SQLiteFuzzySearch
https://sites.google.com/site/lserinol/sqlitecompress
ftp://ftp.freebsd.org/pub/FreeBSD/ports/local-distfiles/glarkin/extension-functions-1.0.c
http://sqlcipher.net/design/
http://sourceforge.net/projects/sqlite-undo/
http://schplurtz.free.fr/wiki/schplurtziel/sqlite3-ipv4-ext
https://bitbucket.org/luciad/libgpkg
https://www.linux.com/news/software/developer/8010-libferris-and-sqlite-a-powerful-combination-part-2


Just in case if Alessandro Furieri do not follow the list closely, 
several of the spatialite project provided addons:


Spatial functionality (+extras: math functions, libxml2/VirtualXPath 
table, MS XLS Virtual table):

https://www.gaia-gis.it/fossil/libspatialite/index
http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.1.0.html
https://www.gaia-gis.it/fossil/freexl/index

PostgreSQL interface:
https://www.gaia-gis.it/fossil/virtualpg/index

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Still trying to track down loadable extensions

[Alek Paunov]

On 21.02.2014 01:19, Peter Haworth wrote:

I'm still hunting for loadable extensions.  The SQLite web site makes
reference to extensions being part of the source code in the contrib folder
but when I browser around there, I can only see 2 files dlmalloc and sqlcon.



I see that you are supporting non-FOSS software based on SQLite. What is 
your goal - to include as much as possible extensions in your software 
or to start cataloging initiative in the service of community?


If the latter, you can contact James K. Lowden, who was about to 
sort-out something in this direction an year ago [1].


Alek

[1] https://groups.google.com/forum/#!topic/sqlite-dev/icj60Bc5Lt0

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive query?

[Alek Paunov]

On 10.01.2014 17:34, Richard Hipp wrote:

On Wed, Jan 8, 2014 at 2:35 PM, Richard Hipp  wrote:



FYI:  The sponsor is now indicating that they want to go with WITH
RECURSIVE.  So the CONNECT BY branch has been closed and we are starting to
work on a WITH RECURSIVE implementation.


Thank you, SQLite team! Big thanks to the sponsor too!

Kind Regards,
Alek



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RFE: Rename Column

[Alek Paunov]

On 06.01.2014 00:58, Simon Slavin wrote:


On 5 Jan 2014, at 6:41pm, Petite Abeille  wrote:

On Jan 5, 2014, at 6:56 PM, Igor Tandetnik  wrote:

On 1/4/2014 7:15 PM, Elrond wrote:

Short: Could you implement alter table rename column?


The problem would be, what to do with all the indexes, triggers, views and 
foreign keys that reference that column?


Sure, but that’s an unrelated set of problems, isn’t it? As it stands, one 
cannot even rename a column.


You're both right.  Igor's statement pretty-much /is/ the reason one cannot 
rename a column.  One would need to write a parser and changer for SQL 
statements that could identify and change column names in many statements with 
all sorts of weird possibilities for formatting.

Two alternatives: (a) actually write the parser-and-changer that processes SQL 
commands, or (b) wait until the major file format changes in SQLite4, then 
change the way SQL stores the CREATE commands needed to construct a database so 
it stores a structured version of the commands instead of the raw text.


If someone have a little time to experiment with the (a) road, it is not 
so complicated as it might look at the first glance: For example sqld3 
[1] is a PEG parser which, as author claims, is derived from the 
SQLite's railroad syntax diagrams, which in order are derived (I 
believe) from the SQLite's sources (i.e. there are chances, that the 
grammar is sound).


PEG [2], is the simplest possible kind of grammar machinery, (probably 
easiest for understanding for non computer language experts) - no 
scanner/parser split, no ambiguity, as people often said - something 
like RegExps on steroids :-).


The above project is in Ruby (is there someone who reads Ruby to give 
some test results?) and it is 3 years old, but this is not so important 
- I think Richard and the team are able to point out even more clever 
path for pure grammar extraction in sync with the latest SQLite sources.


Once a Language grammar is available for given PEG implementation it is 
usually easy to translate it for another - because the PEG rules (for 
the syntax rules :-) ) are basically the same everywhere.


There are hundreds of PEG implementations already - at least several per 
language. My personal favorite is one of the smallest, pure C libs - 
LPeg [3], which just like the SQLite itself compiles the grammar to the 
VM code. LPegLJ [4] port of [3] even does not need a C compiler (the 
source code - it is JIT-ed on demand)


Parsing is the first step. I think, it would be funny if the  dogfooding 
principle for the second - transformation step is tried. i.e. when the 
parse trees of SQlite SQL are stored back in (e.g. in memory) SQlite and 
transformed there ;-).


BTW, transformation relaying on data stores are applied in one of the 
modern products in that field - Rascal [5] (IMP PDB).


Another note: This topic seems somewhat related to the recent 
discussions (e.g. CTEs) about SQLite RFEs which are statically 
implementable (by rewriting, without changes to the SQLite engine)


Kind Regards,
Alek

[1] https://github.com/steveyen/sqld3
[2] http://en.wikipedia.org/wiki/Parsing_expression_grammar
[3] http://www.inf.puc-rio.br/~roberto/lpeg/
[4] https://github.com/sacek/LPegLJ
[5] http://www.rascal-mpl.org/

P.S. @Simon, and others closely following: Please point me to the docs 
for the new style (structured) SQL objects representation in the SQlite4 
- only tables? or scripts too?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

[Alek Paunov]

On 23.11.2013 13:18, Richard Hipp wrote:

SQLite must know that the function always gives the same output given the
same inputs.  No every function works that way.  Counterexamples include
random() and last_insert_rowid().  But most built-in functions are
factorable in the same way that datetime() is.


BTW, I see the term "deterministic" in the SQL99 BNFs:

http://savage.net.au/SQL/sql-99.bnf.html#deterministic%20characteristic
http://savage.net.au/SQL/sql-2003-2.bnf.html#deterministic%20characteristic

aslo found in MySQL:

http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html

but different in PostgreSQL ("immutable", "stable", etc):

http://www.postgresql.org/docs/9.3/static/sql-createfunction.html

I think "deterministic" is used also in the Prolog, whit the same meaning.

Kind Regards,
Alek

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL 2003 in sqlite

[Alek Paunov]

On 12.11.2013 10:45, Sylvain Pointeau wrote:

The merge statement is really missing in sqlite...


Definitely it is missing ... for maybe 0.05% of the (advanced) SQLite 
users :-). Much large group missing UPDATE and DELETE statements over 
joins at first place.



Is there any plan to integrate this SQL 2003 syntax in sqlite?


Your question is already 36+ hours old. Because the SQLite[*] core team 
(consisting of *3 developers* including the leading architect) is 
usually very responsive when the subject is considered important (we 
often have seen bugfixes and improvements done literally over the 
night), the answer is obviously "No" - at least from the core team side.


But ... SQLite is very simple and smart architecture. Almost every part 
is plugable and the interfaces between the moving parts are rigorously 
documented.


http://www.sqlite.org/arch.html

For the MERGE RFE implementation you need just an extension of the SQL 
frontend (first tier of the architecture) which translates SQL to the 
simple and well evolved bytecode.


http://www.sqlite.org/opcode.html
sqlite3 :memory: 'explain select name from sqlite_master'

Actually SQLite is close to MERGE support in the sense that hypothetical 
MERGE VDBE bytecode is relatively simple function of the bytecodes of 
the three "elementary" statements which MERGE combines (insert, update, 
delete).


Naturally, SQLite already generates MERGE "sub" statements bytecode for 
every version of the engine. I.e. you have valid input to the bytecode 
morphing transformation at hand.


So, if you are really like MERGE, and you are hacker with few dozens of 
free hours - give it a go, many people here will (at least) follow with 
interest your experiment.


If you are not - help the listening hackers (they are many here, but 
believe me - 2 .. 5 max of them are regular MERGE users :-) ) to 
understand the benefits of your RFE.


Cheers,
Alek

[*] The most used DB in the world

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Which constraint is being violating??

[Alek Paunov]

On 04.11.2013 11:46, Rafa de Miguel wrote:

Yes, I knew that but that info it doesn't really help me too much


FWIW: You probably are aware of this too, but just in case: On the 
browsers, especially these without build-in WebSQL (sqlite) support, the 
developer can fallback to a C to LLVM IR to JS (asm.js which is 
efficiently JIT-ted on FF) build/port as provided by:


https://github.com/kripken/sql.js

(It is possible to build with sqlite version different than currently 
bundled amalgamation version - 3.7.17)


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request: Binding Arrays

[Alek Paunov]

On 01.11.2013 22:04, Alek Paunov wrote:

After reading the whole tread I suspect that you have already considered
the whole thing about the :memory: DB bridging the GUI with the real DB
but I am curious why?


Sorry - unfinished sentence: ... why you have rejected this approach?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request: Binding Arrays

[Alek Paunov]

Hi Dominique,

On 16.10.2013 11:40, Dominique Devienne wrote:

We have an SQLite virtual-table heavy application with a lot of the GUI
driven by SQL queries, and often times we have queries of the form



...



create table t (name text, type text, primary key (name, type));
select * from t where type in (%1);



...



If I somehow missed a better work-around to this lack of array-binding, I'm
also interested of course, but obviously I'd prefer real array binding.



I am thinking about a sort of workaround:

attach ':memory:' as gui

create table gui.node(node int primary key, parent int, value);
/* Model for data binding elements with single value - one tree per 
widget */


create table gui.node_value(node int, typecode int, value);
/* Model for widget elements with multiple values */

create table gui.widget_binding(widget primary key, node int);
/* Current widget binding */

Or more direct alternative:

create table gui.t_based_combo(widget int primary key, label, type text);

Let see the later (for the sake of simplicity)

Variant 1: Ideally you are able to rebind your widgetkit to the inmemory 
gui DB (replacing your current memory containers). Then we have:


- Populating the widget data: insert into gui.t_based_combo select 
$widget, 
- Destroying the widget: delete from gui.t_based_combo where widget = 
$widget
- Using widget: select * from t where type in (select type from 
gui.t_based_combo where widget = $widget)


Variant 2: You are not able (to rebind): Basically the same as Variant 
1, but you have to inject triggers in your memory containers to keep 
them in sync with the gui DB. In this case probably the more general 
model scheme (the first one - "node" tree) will be appropriate, because 
you will likely implement the triggers in some base widget class.


After reading the whole tread I suspect that you have already considered 
the whole thing about the :memory: DB bridging the GUI with the real DB 
but I am curious why?


Kind Regards,
Alek


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request: Binding Arrays

[Alek Paunov]

On 31.10.2013 18:37, Nico Williams wrote:

On Wed, Oct 16, 2013 at 07:28:04AM -0400, Richard Hipp wrote:

Please see http://www.sqlite.org/src/artifact/2ece66438?ln=13-75


I've been thinking for a while -ever since I happened upon jq(1)- that a
marriage of jq and SQLite3 would be wonderful.

jq is a JSON query language.  It's a functional language.

In my mind this would consist of:

  - a jq function for sqlite3
  - a jq array grouping aggregate function for sqlite3
  - a jq virtual table for sqlite3 (for, e.g., disaggregating values)
  - a jq binding for sqlite3 (so SQLite3 can be invoked from jq)

The IN array binding could then be handled like this:

sqlite3> SELECT * FROM foo WHERE jq('contains($arg1), :in_list, column1);

The value bound to :in_list would be a JSON array or object (faster for
larger sets) of values.


I am sure, there are many SQLite users waiting with hope :-) for an 
extension handling semi-structured data.


BTW, I think some functionality are already online trough 
libspatialite's VirtualXPath virtual table [1].


Might be some code reuse could be possible for the JSON case.

Are there enough interest for something like informal SIG about 
Tree/Graph data processing in SQLite?


Kind Regards,
Alek

[1] 
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=VirtualXPath-intro


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite, HTML5 and Javascript

[Alek Paunov]

On 29.10.2012 20:44, Rose, John B wrote:

1) We are looking for simple examples, with source code,  of a UI using HTML5 
to query and add data to an SQLite database
2) We are also looking for examples using Javascript to query an existing 
SQLite database. And maybe add data to an existing database. We do not care of 
the database is on a server or local. We just want to put together a simple 
mechanism using javascript as a query interface to an SQLite database.

We have read a bit about WebSQL, Web Storage and IndexedDB. We are a bit 
confused.

Are one of those a requirement to interact with SQLite via Javascript? After a 
reasonable bit of googling we have not found a simple javascript/SQLite example.



I am assuming that you are talking about client side SQLite DBs.

Unfortunately, the sad reality is that Web SQL Database [1] (i.e. SQLite 
presence in any modern browser) has been abandoned as a standard in 
favor of IndexedDB.


The main reasons (cited in various official and semi-official sources by 
the Mozilla guys) are:


 * Internet badly needs browser convergence over HTML5, but Microsoft
   will never include exactly SQLite in IE for Windows, (the proposed
   standard [1] roughly says "WebSQL in terms of query language and
   behavior is ... SQLite 3.6.19")

 * SQL is not the perfect language for the average JS developer.

Mozilla (Firefox) continues to use (quite inefficiently) SQLite as 
IndexedDB backend, Chrome switched to LevelDB recently.


Meantime, you have the following options:

 * Stick with IndexedDB, which do not support any query language - you
   will need to render your queries to low level API calls manually,
   like in the pre-SQL dark ages :-) (but IndexedDB is already supported
   in all recent versions, natively [2] or by shim [3] on top of
   SQLite/WebSQL [4]).

 * Test for a project (and contribute to - e.g. filling bugs) Emscripten
   SQLite [5] (C SQLite code compiled as JS using the new HTML5 typed
   arrays as memory representation).

Sorry,
Alek

P.S. I am keeping the hope, that it is still possible to bring back 
SQLite in the standard JS APIs, will be glad to discuss how we could try 
to achieve the goal if anyone is interested.


[1] http://www.w3.org/TR/webdatabase/
[2] http://caniuse.com/#search=IndexedDB
[3] http://nparashuram.com/IndexedDBShim/
[4] http://caniuse.com/#search=WebSQL
[5] http://syntensity.com/static/sql.html

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

[Alek Paunov]

Hi David,

On 29.10.2012 11:58, David Barrett wrote:

Because in practice, as someone actually doing it (as opposed to theorizing
about it), it works great.  The MySQL portions of our service are always in
a semi-constant state of emergency, while our sqlite portions just hum
along   And given that we're switching to SSDs, I expect they will hum even
better.  What problems would you expect me to be seeing that I can happily
report I'm not, or what problems have I not yet encountered but will -- at
100GB, or 1TB?


In your previous thread (2012-02), you have mentioned that you are about 
to open-source your replication method based on SQL statement 
distribution. Probably your work would be of interest for a huge number 
of sites managing data volumes around or bellow your current level, even 
if you switch to PostgreSQL at this point.


IMHO, there might be a future for your replication model, because I 
think that SQLite, can more easily (relative to other proven DB 
technologies e.g. PostgreSQL) be turned to DB engine for more query 
languages than SQL (thanks to his clever VM design).


Furthermore, AFAIK, PostgreSQL replicates at WAL distribution level, 
most NoSQL databases at keys distribution level, whereas your method 
seems more efficient as bandwidth.


Kind Regards,
Alek

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unofficial poll

[Alek Paunov]

On 23.09.2012 23:59, Simon Slavin wrote:

On 23 Sep 2012, at 9:55pm, Alek Paunov <a...@declera.com> wrote:

This feature is very useful for storing hierarchical data - XML, JSON, ASTs, 
objects in the script engines (e.g. Lua, Python, ...), etc.


Really ?  I don't know about ASTs, but aren't XML and JSON encodings just 
strings ?  You could encode anything in JSON and keep it in a TEXT column.

In some cases they arrive as strings, in other not - i.e. when they are 
generated or parsed as object structures in the host application 
already. In both cases you usually want to do something meaningful with 
the data afterwards - i.e. need to perform queries (for example to 
select just given class of subnodes and attributes) - so it is not 
feasible to store them as BLOBs, especially if the volume is significant.


Instead you need to "shred" them as sqlite rows (following your favorite 
tree encoding scheme), containing "value" attribute (column) for the 
scalars (the leafs of the hierarchy). Here comes the convenience of the 
sqlite feature in the question - the "value" attribute can contain 
values of any of the basic scalar types in single table.


Contrary, in other (relational) databases you need several tables in the 
form fact_int(..., value integer), fact_float(..., value float), etc - 
which additionally complicates the query code.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unofficial poll

[Alek Paunov]

On 23.09.2012 13:37, Baruch Burstein wrote:

Has anyone ever actually taken advantage of this feature? In what case?

Yes, This feature is very useful for storing hierarchical data - XML, 
JSON, ASTs, objects in the script engines (e.g. Lua, Python, ...), etc.


IMHO, If the understanding of the unique sqlite mechanism of operation 
(as SQL to VM-bytecode compiler) was a little bit more widespread, we 
would have already at least one community compiler for these new 
UnQL-like JSON query languages, targeting VDBE as backend (probably 
written in JS by these enthusiastic node.js generation guys) :-)


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR REPLACE

[Alek Paunov]

On 12.03.2012 16:02, Christian Smith wrote:

I've had a similar problem in the past, and solved it by using a pre-insert
trigger to do the desired update.

Thus, the insert/update from above becomes just an insert (or ignore),
with an implied update in the trigger, which appears to be sematically
closer to what people want in the above case (though not in the original
subject matter.)


Thank you Christian!

Your insightful advice led me to the following (slightly more natural) 
variation (with the OP's sample):


create view t1_inc as
select
t1.rowid, t1.a, t2.b,
t1.a + t2.b a_next, t1.b + t2.b b_next
from t2
inner join t1 on t1.id = t2.id
;
create trigger t1_inc_apply instead of update on t1_inc begin
update t1
set a = NEW.a_next, b = NEW.b_next
where rowid = NEW.rowid
;
end
;
update t1_inc set a = a_next, b = b_next
;

The only visible downside is, that in both variants (your original/the 
above), generated VDBE code contains OpenEphemeral and (AFAICT) 
temporary record for every row in the join, but maybe someone knows 
variation of the trigger based approach which avoids this ... ?


Kind regards,
Alek
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cyrillic support

[Alek Paunov]

On 10.03.2012 19:07, Mite wrote:

I am using the newest version of SQLite. How do I enable support for
Cyrillic letters? Whenever I enter something in the DB with Cyrillic
letters it gets saved like this ??


I don't think so. Please try the attached shell script.

Also, you can check this out if add a bookmark for any page with 
Cyrillic title in your Firefox, then look at the db: places.sqlite in 
your Firefox profile, table: moz_bookmarks, column: title (last row).


Alek
#/bin/sh

n0=cyrtest-0.txt
n1=cyrtest-1.txt

echo "Текст на кирилица" > $n0

(

echo "create table cyrtest(c text);"
echo "insert into cyrtest(c) values ('$(cat $n0)');"
echo "select c from cyrtest;"

) | sqlite3 cyrtest.sqlite > $n1


diff $n0 $n1
if [ $? -eq 0 ]; then
echo "$n0 and $n1 are identical"
fi
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR REPLACE

[Alek Paunov]

On 09.03.2012 17:39, Nico Williams wrote:

Perhaps a Google summer of code project or something?


Perhaps.


Unfortunately not :-(. Google people have made a mistake in the past - 
looking for formal criteria to guarantee the openness of the initiative 
they included the rule that mentoring organizations are required to 
release code under the OSI [1] approved license:


"As long as your project can provide mentors and is releasing code under 
an Open Source Initiative approved license" [2]


which in principle is OK, but leaves the projects releasing code as 
Public Domain out of account :-(. (confirmed by Cat Allman, Open Source 
Programs Office, Google, 2012-02-13).


[1] http://www.opensource.org/licenses/alphabetical
[2] http://www.google-melange.com/document/show/gsoc_program/google
/gsoc2012/faqs#mentoring_org_type
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR REPLACE

[Alek Paunov]

On 09.03.2012 02:22, Nico Williams wrote:

On Thu, Mar 8, 2012 at 5:57 PM, Alek Paunov<a...@declera.com>  wrote:

Let suppose hypothetical function:

asm(sql): returns VDBE assembler code for the sql parameter (like 'explain'
but with full instruction attributes)

I am curious, Is it evaluable (to proper VDBE code):

asm(update with complex join)
as Transformation(
asm(select with the same join),
asm(same update without join)
)

?

What the "internals" gurus think? :-)


It most definitely is.  (I'm not a SQLite3 internals guru, but I've
played enough with the internals to believe that I can make that
assertion with high confidence.  I could be wrong however.  Caveat
emptor.)


So maybe it's worth to give it a try ... Is there someone else, which is 
interested to work on VDBE dump/load to assembler representation? Once 
these base tools are available, maybe other people would reuse bytecode 
instrumentation for other purposes also ;-)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR REPLACE

[Alek Paunov]

Hi List,

On 09.03.2012 01:15, Nico Williams wrote:

SQL was not, originally, a Turing complete language (unless one were
to consider cross self joins of large tables for driving iteration as
good enough), but nowadays it pretty much is, therefore it  is a
programming language.  The language supported by SQLite3 is Turing
complete, FYI, since you have conditionals (WHERE, WHEN, CASE, ...)
and iteration (e.g., via recursive triggers).  And the SQLite3 VM most
assuredly is Turing complete.


Let suppose hypothetical function:

asm(sql): returns VDBE assembler code for the sql parameter (like 
'explain' but with full instruction attributes)


I am curious, Is it evaluable (to proper VDBE code):

asm(update with complex join)
as Transformation(
asm(select with the same join),
asm(same update without join)
)

?

What the "internals" gurus think? :-)

Thank you,
Alek

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GUI for SQLite

[Alek Paunov]

On 08.03.2012 15:13, gregorinator wrote:

I've been happy with SQLite Studio:

http://sqlitestudio.one.pl/


Just tried Sqlite Studio following your advice - Great tool:

 * open source
 * implemented in scripting language (Tcl/Tk)
 * available as single executable
 * SQL editor with highlighting and autocompletion
 * browsing big tables
 * convenient editing of the data directly in the result grid
 * user defined functions in tcl and sql, plugins
 * many, many other features ...

I was Sqlite Manager user past years (Firefox plugin) but now I think I 
have a better tool :-)


Thank you Pawel!
Alek

P.S. If anyone have troubles to use the app on Fedora/CentOS, feel free 
to drop me a line.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Some pieces of the puzzle are coming together

[Alek Paunov]

On 21.01.2012 16:00, John Elrick wrote:

manifestation.  As frustrating as it has been to narrow down the cause, I


Frustrating ... ?

John Elrick, 2012-01-13:
"""
I created a logging system which took a specific set of data and converted
all of the automatically run queries to an SQL script which I could use in
a test application.  When testing this particular script using a test
program which uses our Delphi wrappers the following times are observed:

Test Application Run Batch Script

3.6.17: 14 seconds
3.7.9: 10 seconds

This clearly demonstrates that the newer version of Sqlite is, all things
being equal, superior in performance to the older.  However, tests inside
our Delphi application demonstrate that reaching the exact same point of
the database result in the following times:

Live Application

3.6.17: 16 seconds
3.7.9: 58 seconds
"""
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search

[Alek Paunov]

BTW, in my eyes, sqlite has the full potential to become most used DB 
engine for hierarchical data too (besides relational), once someone 
clever hacker manage to port something like Pathfinder [1,2,3] (which 
is, let say, optimizing XQuery/SQL compiler) to generate VDBE bytecode 
(like the build-in frontend for SQL)


[1] http://dev.monetdb.org/hg/MonetDB/file/f7d6c302cc9c/pathfinder
[2] http://hackage.haskell.org/package/Pathfinder
[3] 
http://www.lug-erding.de/vortrag/Purely%20Relational%20XQuery%20LUG%20Erding.pdf


On 30.12.2011 16:35, Aris Setyawan wrote:

Hi Durga,

Another alternative, you can use an xml database. It will fix your
problem easily using xquery, like this:
 doc('region')//country/title/text() ->  it will show all region you have
 doc('region')//village/title/text() ->  it will show all village you have

You also can use selection too (where condition).
http://en.wikibooks.org/wiki/XQuery/XPath_examples
http://sedna.org

SQLite with fts is my favorite, but for tree like data structure I
will use xml database.

-aris

On 12/28/11, Durga D  wrote:

Dear Michael.Black.

 It's correct.

I need to design database to store file paths and their info like
size. I have an idea
   item(file or folder), level0(imm. parent), level1(grand parent)
to level160(ancestor), type(file type or folder type).

  primary key: (item, level0 to level160)

  Is it correct approach? This is from server side. Need to store
millions of records.

 Need optimum relationship between folders and files uniquely.

 for ex: c:/mydocs/home/a.doc
 c:/mydocs/office/agreement.doc

   insertion of filepaths,deltion of file paths are enough. should be able
to search by folder wise also.

any ideas?

Thanks in advance.

On Tue, Dec 27, 2011 at 7:54 PM, Black, Michael (IS)  wrote:


2011/12/27 Durga D:

select * from virfts4 where residence match '/*'; -- dint work
how to get counties names from this db by using query?


Normalize database to 1NF, e.g.
CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
arrivtime, duration, imagelocation);
INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
0730, 1500,'C');

then use select:
SELECT DISTINCT country FROM virfts4;
--
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How about a proper forum rather than an e-mail list

[Alek Paunov]

On 15.11.2011 22:35, Nico Williams wrote:

my answer: a plethora of interfaces to the same data (posts/threads).


+1


I'd like to see:

  - RSS/Atom feeds
  - web UIs ("web forum" UIs)
  - stable HTTP APIs
  - mobile apps specifically for fora (probably based on HTTP APIs)
  - e-mail interface (mailing list)
  - archives that can be downloaded, as well as searched online
  - maybe even Usenet bridging



Me too. As simple first step - let's load the mail archives to 
downloadable sqlite DB.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] status of unqlspec / sqlite

[Alek Paunov]

Hi,

Recent comment on the topic from unql mailing list:

https://groups.google.com/forum/#!msg/unql/dVc_cM1ZGw8/3QHE1_MIqRQJ

On 04.11.2011 10:50, sqlite-us...@h-rd.org wrote:

Hi,

some time ago Richard was involved in http://www.unqlspec.org/ . Is that
still going on? I am quite interested in a backend for sqlite.



Sqlite backend for UNQL frontend or new backend for VDBE in sqlite ?


thanks,

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite on Windows 64bit

[Alek Paunov]

I have 64-bit binary package for my OS (kindly maintained by guy named 
Panu Matilainen, outside of the sqltie.org core team).


I have installed this package after the command: yum install sqlite This 
currently installs 3.7.5 for me.


If I want to test/use different version I type: fossill clone ..., 
fossil open version-3.7.8, ./configure, make, and I have 3.7.8 .so and 
shell.


You are developer, what stops you to do something similar for your 
favorite OS?


Then you can put this 64-bit build somewhere and announce this in favor 
to other users of the same OS in the list. Or you can ask your OS vendor 
(you are paying them If I remember well) to start maintain MSI for sqlite3.


Why you are thinking that someone other is responsible for the lack of 
binary packages for your OS variant?


On 29.10.2011 15:03, Arbol One wrote:

Yes, compiling to 32-bit is nice. It is like dancing at the tunes of "Earth
Wind and Fire", h, those were the days. Like the old good songs, the
32bit apps are a good memory, many people like it, but they are a thing of
the past. We are developers and anyone of us who stays behind, is left
behind. Not having the option of SQLite-64 is a major drawback and will
leave us behind. So, come on folks, pull up your socks and let's get the
SQLite apps into the 21st century, shall we?



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How about a proper forum rather than an e-mail

[Alek Paunov]

This was said above in the thread - try this google query:

site:mail-archive.com inurl:sqlite-users "How about a"

BTW, Some day I would be happy to use FTS powered search across the mail 
archives, maybe with additional feature (authorized with list-member 
credentials) for tagging and assigning additional related bookmarks (to 
the lines in source revisions, documentation and relevant blog articles) 
to some messages.


On 27.10.2011 20:35, Pete wrote:

The one attraction of a forum to me is that it's searchable so I'd be able
to check for any discussions before posting to the mailing list. Is there an
archive for the mailing list somewhere which could serve the same prupose?

Pete



--


Message: 27
Date: Thu, 27 Oct 2011 16:45:12 +0100
From: Simon Slavin
To: General Discussion of SQLite Database
Subject: Re: [sqlite] How about a proper forum rather than an e-mail
list
Message-ID:<41d980cd-ae28-46a3-85d0-f2789b9fb...@bigfraud.org>
Content-Type: text/plain; charset=us-ascii


On 27 Oct 2011, at 4:41pm, Yves Goergen wrote:


On 23.10.2011 16:05 CE(S)T, Simon Slavin wrote:

Part of the attraction of this list is that I don't have to think
"Oh, I want to read a lot of SQLite-related stuff now !".


What do you mean? I don't get it.


If I had to go to a separate forum for my SQLite thoughts, I wouldn't
bother to go very often.  Because most of the time there's nothing there
that interests me.  An advantage of a mailing list is that the SQLite
messages roll in gradually, mixed with other stuff that requires less
concentration to understand.  I'm not put off by the idea that I'll now have
to wade through 20 posts I'm not interested in.

Simon.

--


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API docs

[Alek Paunov]

I do not know the answer, but I am thinking for an attempt to extract 
them as clang+lpeg exercise. Why you are asking ... ?


On 24.10.2011 16:05, Baruch Burstein wrote:

How are the C API documents auto-generated? Which tool is used?
I see that they are all in the comments in the code, but couldn't find a
tool in the source that is used to extract them and make the links.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

[Alek Paunov]

Hi Frank,

You can take a look at my start-up file for excel 2000/2003:

http://source.declera.com/excel/personal.xls
(I am also attaching contained VBA module db.bas)

With started personal.xls [1], one can open empty sheet named "sqlite", 
enter the path to sqlite database file in cell A1, and then use the 
following (defined in personal.xls) shortcuts:


* Ctrl-D: pressed (for example) in A6: Executes SQL in B6 storing 
results in new worksheet named as A6


* Ctrl-Shift-D: in A3, same as above, but looks for SQL in A4 and stores 
the result starting from A5


* Ctrl-T: like Ctrl-D but opens Pivot table instead of Query Table

You can see these in following example:
http://source.declera.com/excel/packages.xls

The example workbook uses this database (part of the Fedora packaging 
system yum):

http://source.declera.com/excel/packages.zip

As you will see (in the VBA code), this VBA glue lies on the ODBC driver 
for sqlite (Thank you Mr.Werner!), but can be used with OLEDB sources too.


Cheers,
Alek

[1] Excel personal.xls from the location specified in:
Tools/Options/General/At startup, open all files in


On 13.10.2011 22:10, Frank Missel wrote:

Hi Bart,


boun...@sqlite.org] On Behalf Of Bart Smissaert
No, the wrapper is not used that way and I don't think it can be used that
way.
The SQLite database is dealt with in VBA or VB6 code via this wrapper.
I suppose you could compare it to using ADO with a DSN-less connection.

RBS


Okay, that's what I thought.
I did try the wrapper a couple of years ago and found it very well designed
and performing; I can also recommend it for VB 6, VBA or VBScript.
I now use the C API directly -- sort of my own wrapper for some special
purposes.

So when referencing an SQLite database from Excel you also use the ODBC
driver I guess.
This brings me to my main remaining issue which is to avoid having to create
an individual data source for each SQLite database.

If anyone have any solution for this or any other, easier alternative way of
accessing an SQLite database as a data source programmatically through the
Excel COM object model (in order to e.g. create a Pivottable), I would be
very eager to hear about it :-).


/Frank

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



Attribute VB_Name = "db"
Option Explicit

Private Function sheet_get(name As String) As Excel.Worksheet
Dim sel As Object
Dim sheet As Excel.Worksheet
For Each sel In ActiveWorkbook.sheets
If sel.name = name Then
Set sheet_get = sel
Exit Function
End If
Next
End Function

Private Function querytable_get(sheet As Excel.Worksheet, name As String) As 
Excel.QueryTable
Dim query As Excel.QueryTable
For Each query In sheet.QueryTables
If query.name = name Then
Set querytable_get = query
Exit Function
End If
Next
End Function

Private Sub db_fetch_sql( _
sql As String, db_code As String, _
range_at As Excel.range, fetch_name As String, _
Optional db_code2 As String = "", _
Optional as_pivot As Boolean = False _
)
Dim sheet As Excel.Worksheet
Dim db_connection_string As String

Select Case db_code
Case "sqlite"
Let db_connection_string = _
"ODBC;DRIVER={SQLite3 ODBC Driver};DATABASE=" & db_code2
Case "inv"
Let db_connection_string = _
"OLEDB;Provider=SQLOLEDB; Data Source=172.16.1.5,1433; " & _
"User ID=public_user; Password=public_passwrod; Initial 
Catalog=inv"
Case "sonita"
Let db_connection_string = _
"ODBC;DRIVER={Adaptive Server Anywhere 9.0};" & _
"ServerName=sio;Links=tcpip(Host=172.16.2.24;Port=2638);" & _
"DatabaseName=sonita;Uid=inv_app;Pwd=public_passwrod"
Case Else
Let db_connection_string = _
"OLEDB;Provider=SQLOLEDB; Server=172.16.1.5,1433; " & _
"User ID=public_user; Password=public_passwrod; Initial 
Catalog=" & db_code
End Select
Set sheet = range_at.Worksheet

If Not Err Then
If as_pivot Then
Dim cache As Excel.PivotCache
Set cache = ActiveWorkbook.PivotCaches.Add(xlExternal)
With cache
.Connection = db_connection_string
.CommandType = xlCmdSql
.CommandText = sql
End With

Dim pivot As PivotTable
Set pivot = sheet.PivotTables.Add(cache, range_at, fetch_name)
Let pivot.DisplayImmediateItems = True
Else
With sheet.QueryTables.Add(db_connection_string, range_at, sql)
.name = fetch_name
.FieldNames = True
.RowNumbers = False
.MaintainConnection = False