[sqlite] Where sqlite vtables are used? (was: Search for text in all tables)
On 05.12.2014 12:32, Dominique Devienne wrote: On Fri, Dec 5, 2014 at 10:24 AM, Max Vlasov max.vla...@gmail.com 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?
On 25.08.2014 20:47, Richard Hipp wrote: On Mon, Aug 25, 2014 at 1:21 PM, forkandwait webb.spra...@gmail.com 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
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
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
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
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
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
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] Still trying to track down loadable extensions
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] Recursive query?
On 10.01.2014 17:34, Richard Hipp wrote: On Wed, Jan 8, 2014 at 2:35 PM, Richard Hipp d...@sqlite.org 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
On 06.01.2014 00:58, Simon Slavin wrote: On 5 Jan 2014, at 6:41pm, Petite Abeille petite.abei...@gmail.com wrote: On Jan 5, 2014, at 6:56 PM, Igor Tandetnik i...@tandetnik.org 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.
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
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??
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
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] Feature Request: Binding Arrays
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
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] sqlite versus MySQL automatic defragmentation on SSDs?
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] SQLite, HTML5 and Javascript
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] Unofficial poll
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] Unofficial poll
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] INSERT OR REPLACE
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
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
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] GUI for SQLite
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] INSERT OR REPLACE
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] INSERT OR REPLACE
On 09.03.2012 02:22, Nico Williams wrote: On Thu, Mar 8, 2012 at 5:57 PM, Alek Paunova...@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] Some pieces of the puzzle are coming together
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
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 Ddurga.d...@gmail.com 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)michael.bla...@ngc.com
wrote:
I don't know if FTS or a normal table will matter here but just normalize
the whole thing.
CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);
Your level can be CO, ST, CI, VI. Or 1,2,3,4. 1,2,3,4 would be a touch
faster.
INSERT INTO virfts4 VALUES(1,'CO','country1');
INSERT INTO virfts4 VALUES(1,'ST','state1');
INSERT INTO virfts4 VALUES(1,'CI','city1');
INSERT INTO virfts4 VALUES(1,'VI','village1');
SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';
You can store as many levels as you want.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of Durga D [durga.d...@gmail.com]
Sent: Tuesday, December 27, 2011 4:27 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] search
Thank you. I agree. It's correct.
I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?
if yes, how?
in case, if I have 250 levels like this ex: file system. how to do this.
any idea?
Thanks in advance.
On Tue, Dec 27, 2011 at 3:38 PM, Kitkit.sa...@gmail.com wrote:
2011/12/27 Durga Ddurga.d...@gmail.com:
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
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
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
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
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 Slavinslav...@bigfraud.org To: General Discussion of SQLite Databasesqlite-users@sqlite.org 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
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)
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
.FillAdjacentFormulas = False
