[sqlite] FTS5 Prefix Indexes
Hello !
Is it possible to expose commands to create/remove prefix indexes ?
Something like (or any other friendly way to do it without reindex the whole
database): INSERT INTO ft(ft) VALUES('remove prefix 2 3'); INSERT INTO
ft(ft) VALUES('remove prefix 2'); INSERT INTO ft(ft) VALUES('create prefix
2');
Cheers !
?
[sqlite] calling Skip() on IQueryable with LINQ is generating incorrect sql
Tim Stowell wrote: > > var queryable = table.Skip(20); // Incorrect SQL generated > Are you using the latest System.Data.SQLite? Do you have an example of the generated SQL? -- Joe Mistachkin
[sqlite] calling Skip() on IQueryable with LINQ is generating incorrect sql
I?m using SQLite with .NET and LINQ. When I call Skip on an IQueryable, the
generated SQL uses ?TOP? instead of ?Limit? which is causing an error due to
SQLite not supporting the Top keyword. I have referenced both
System.Data.SQLite, System.Data.SQLite.Linq, and System.Data.Linq in the Visual
Studio Project. Below is some sample code:
var cachedFilePath = String.Concat(PathInfo.getDataPath() + "reports\\",
reportGUID, ".", "sqlite");
var dbConnection = new SQLiteConnection("Data Source=somePath; ");
dbConnection.Open();
var context = new DataContext(dbConnection);
var table = context.GetTable();
var queryable = table.Skip(20); // Incorrect SQL generated
Does anyone have any ideas how to get LINQ to generate the correct ?Limit?
clause?
Confidentiality Notice: This e-mail message, including any attachments, is for
the sole use of NAVEX Global? Inc. and the intended recipients and may contain
confidential and privileged information. Any unauthorized review, use,
disclosure, or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply e-mail and destroy all copies of
the original message.
[sqlite] Help needed for COPY Command.
Hi All, Thank you so much for your answers. The Copy command that i have mentioned was found in below link: https://www.sqlite.org/tclsqlite.html#copy But just realized that it is in tcl interface. I overlooked it earlier :( I have tried .import and its working for command line interface. But i need to do a bulk import (copy data from file into table) from c interface. How can i do that? please help. Thanks & Best Regards, - Mahi On Sat, Mar 26, 2016 at 3:52 AM, Domingo Alvarez Duarte < sqlite-mail at dev.dadbiz.es> wrote: > Hello ! > > Have you ever heard about user friendly "alias/shortcut" ? > > Cheers ! > > Fri Mar 25 2016 10:48:52 PM CET from "James K. Lowden" > > Subject: Re: [sqlite] Help needed for COPY > >Command. > > > > On Fri, 25 Mar 2016 17:18:16 +0100 > > "Domingo Alvarez Duarte" wrote: > > > > > >>Why not have direct command ".export table_name" and internally it > >> does all commands you mention in one go, simple and intuitively. > >> > > > Importing has unique requirements. > > > > .import filename tablename > > > > is quite limited, but necessary: SQL has no "read from file" feature. > > > > You could have > > > > .export filename tablename > > > > but SQL already has a "read from table" feature, and shell already has > > a "write to file" feature. So an export command would be a redundant > > subset of what's already available. > > > > --jkl > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] sqlite in vba7.1 and windows 8.1
Am 19.02.2016 um 14:31 schrieb Montes C?mara, Victor: > I've been using sqlite in vba6 for some years > using RichClient3 framework of Olaf Schmidt (www.datenhaus.de) The new site (which offers a new version of the RichClient, as well as new versions of __stdcall-compiled SQLite... as e.g. version 3.11 currently) is now hosted here: http://vbRichClient.com/#/en/Downloads.htm > Now I'm migrating my application to 64 bits using vba7 > in Windows 8.1. > The problem I'm facing is with the LoadLibrary function > to load dynamically the DLL DirectCOM.dll DirectCOM.dll (as well as vb_cairo_sqlite.dll - and dito the COM-wrapper vbRichClient5.dll) - are all 32Bit-PE-Binaries. And 32Bit-Dlls cannot be loaded into a 64Bit-Process (in your case the 64Bit-version of your Excel-installation). This has (BTW) nothing to do with the 64Bitness of the OS, which can run both kind of *Processes* (32Bit and 64Bit) - it's simply the restriction that 32Bit-Dlls are loadable only by 32Bit-Processes - and the same thing holds true for the pairing of 64Bit-Dlls and 64Bit-Processes. That's also the reason, why basically two (nearly identical) sets of system-dlls exist - one in \System32 (for the 64- Bit-Dlls) - and the other set in \SysWow64 (32Bit-Dlls). As for "using 32Bit-Dlls in a 64Bit-Excel-Process" - that's (indirectly) possible, when you will use a kind of "64Bit-to- 32Bit-Broker-mechanism" (by creating a 32Bit-Process, which in turn then loads and instantiates the 32Bit-COM-Dll(s) - and then allows cross-process-communication between 64Bit- Excel and the 32Bit-Process (named e.g. MyAXHost32Bit.exe). This is possible over COM (not only across processes, but also across machines) by (auto-created) 64Bit-COM-Proxy- instances in the 64bit-Process - which will then "marshal" all the Method-Calls between the 64Bit-Proxy-Class and the real Class-instance (the 32Bit-one, which is hosted by the Broker-Process (your self-compiled AXHost32Bit.exe). VB6 allows the creation of such a 32Bit ActiveX-Executable quite easily - and it can be implemented even generically, by just this little code in e.g. a Public cConstruct-Class: Public Function GetInstanceByProgID(ProgID As String) As Object Set GetInstanceByProgID = CreateObject(ProgID) End Function As said, that's all the code in the little AXHost32Bit.exe that is necessary. With that you can create and access any 32Bit-COM-Dll(Class) which is registered on the System from within a 64Bit(-VBA)-Process. You can even use Early-Binding within 64Bit-VBA7 by selecting the (32Bit-)lib over the VBA7-"References"-dialogue (e.g. the one for the newer COM-SQLite-wrapper 'vbRichClient5') This will *not* load the 32Bit-COM-Dll itself - but restricts itself to a lookup in the Resource-Section of said Dll-File, to read only the TypeLib-Information (Classes and their Method-Defs). With these Typelib-Infos Excel-64Bit is then capable to (auto-)instantiate the appropriate, interface-compatible (64Bit-)Proxy-Class within Excel (which then does the auto- marshalling of method-calls to the real Class-instance which runs within AXHost32Bit.exe. Sorry for the somewhat longer post, which is only indirectly related to SQLite(-usage). To get more details you might want to ask further questions in a VB6-NewsGroup or on vbForums.com... Olaf
[sqlite] Help needed for COPY Command.
On 28 Mar 2016, at 6:49am, Mahi Gurram wrote: > I have tried .import and its working for command line interface. But i need > to do a bulk import (copy data from file into table) from c interface. Sorry, but SQLite has no facilities for handling text files. It reads and writes to its own database files only. You will have to write your own routines to read text from your text files. You might instead like to use system calls to script the command line shell: https://www.sqlite.org/cli.html Simon.
[sqlite] Help needed for COPY Command.
You need to handle the load yourself. Since you are talking bulk load, a scripting language is probably out of the question because they are too slow. I used C# with the System.Data.Sqlite binary. The db handling you need to worry about is the following: connection to db prepare an insert statement read your input while not eof populate prepared insert arguments execute the insert read your input end close connection It's about that simple. Depending on how many millions your are loading, you might want to setup a transaction cycle and commit every so often. I think I committed every 1mm records. It is a very fast load. With what I was doing at the time, I started using Ruby because it was quick to implement (less than 5 minutes). But the execution time of the load was going to be 24hrs or in that range. Using a compiled language (C#) the load happened in about 15 minutes (~750m a minute). So the time it took to write in C# was worth it. If this is one time load, I have used small sqlite managers to do the bulk load (Sqlite Expert to be exact.) The bulk load is very fast. But you must go through the interactive wizard every time to do a load. You cannot save the setup and repeat the action from the command line, unfortunately. dvn On Mon, Mar 28, 2016 at 2:45 AM, Simon Slavin wrote: > > On 28 Mar 2016, at 6:49am, Mahi Gurram wrote: > > > I have tried .import and its working for command line interface. But i > need > > to do a bulk import (copy data from file into table) from c interface. > > Sorry, but SQLite has no facilities for handling text files. It reads and > writes to its own database files only. You will have to write your own > routines to read text from your text files. > > You might instead like to use system calls to script the command line > shell: > > https://www.sqlite.org/cli.html > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Using a "personal" partial index
On 28 Mar 2016, at 1:27am, David Rayna wrote: > I got excited when I saw the "indexed by" clause. > A technique I used years ago with FoxPro & etc was to allow each user to > create his own personal index of the same data. > FoxPro allowed storing index files separately and on each user's own local C: > drive. > The index was on a complex computed "quality" value based on multiple table > fields allowing each user to create his own ranking of the data based on his > own input terms. > > In sqlite, would I give each user their own name for the index which he could > create, drop and redefine as desired? > How complex can the index value computation be? Would I need to implement it > in C and access via sqlite source code? > Can select ... indexed by work as a pre-filtering and sorting of the data > for each user's own view which could be filtered more if needed? There are numerous differences between FoxPro and SQL, and although I can see why you would want these things in FoxPro your approach isn't useful in SQLite. SQLite doesn't have a user model. If you have OS access to the database file, you can read the database. If you have OS write privs to the database file, you can make changes to the databases. In SQL you can make many indexes (including partial indexes) to a table. You could come up with a naming convention for these indexes that includes a pretend 'user name' in the index name, but any 'user' could have thousands of indexes. In SQLite an index is a series of column names, with a direction (ascending or descending) and a collation method for each column. A collation method is usually something like NOCASE (indicating that upper and lower case don't matter when constructing the index) but you can write custom collation methods in C, and sometimes in whatever your programming language is (depending on how its interface to SQLite works). Simon.
[sqlite] Searching a table of patterns
On 28 Mar 2016, at 1:35am, David Rayna wrote: > One thing I have occasionally desired is be able to search a table of > patterns given a string that might match some rows. > This is the reverse of searching a table of strings to find ones that match a > pattern. One usually sees SELECT id FROM myTable WHERE columnName LIKE '%abc%' but SQLite also handles SELECT id FROM myTable WHERE 'abc' LIKE columnName And put strings like '%abc%' in the column of the table. Simon.
