[sqlite] Help SQlite
> Hello my friends, i need your help, i have problems with the use special > character for example "?" , work with vb net. The problem consist when save > this character in the data base sqlite, this chance in another character. I > hope your help. Thank you. Bach. Jonathan Mej?a Acosta
[sqlite] Ideas on how to use backup API for remote, incremental backup?
On Tue, Oct 6, 2015 at 8:36 PM, Simon Slavin wrote: > Or copy an existing /open/ database file to the new server using the > SQLite Backup API, [requires other connections to stop modifying the > database for long enough for the copy to be made] > Well the backup API works with WAL mode [1] so it can still be modified while being backed up (though I imagine the WAL file will get huge during the backup, but that's fine). Regardless, all those solutions require me to wait for the entire backup to complete before sending the file to the remote host -- my goal is to send it one page at a time (eg, send the pages as the backup API processes them) so as to avoid the large delay and disk space of the backup itself. -david [1] http://sqlite.1065341.n5.nabble.com/WAL-mode-and-backup-API-td40107.html
[sqlite] Multiple connections to in-memory DB and virtual tables
On Tue, 6 Oct 2015 17:39:20 +
Hick Gunter wrote:
> AFAICT the FROM clause is superflous, as the function has no
> (supported) way of detecting which table(s) the FROM clause contains.
> What is your "reindex_virtual_table()" function (I assume it is a
> user implemented function) supposed to do?
I wrote it thinking it's selfexplaining, but now I think I wrote it too fast.
reindex_virtual_table() is a function defined inside Virtual Table code that
forces an internal reindex of its data.
In FTS3/4, for example, you can do an "INSERT INTO
fts_virtual_table(fts_virtual_table) VALUES('rebuild');" to reindex the FTS
virtual table.
And I say I wrote it too fast because a virtual table don't need to have an
internal index, depends on what is it for and its implementation.
> -Urspr?ngliche Nachricht-
> Von: Eduardo Morras [mailto:emorrasg at yahoo.es]
> Gesendet: Dienstag, 06. Oktober 2015 19:08
> An: sqlite-users at mailinglists.sqlite.org
> Betreff: Re: [sqlite] Multiple connections to in-memory DB and
> virtual tables
>
> On Tue, 6 Oct 2015 15:39:08 +0100
> Simon Slavin wrote:
>
> > There are also things Virtual Tables can't do. For instance you
> > cannot index a Virtual Table using SQL commands.
>
> Does "SELECT reindex_virtual_table() FROM virtual_table" count as SQL
> command only?
>
>
> --- ---
> Eduardo Morras
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the
> use of the intended recipient(s) only and may contain information
> that is confidential, privileged or legally protected. Any
> unauthorized use or dissemination of this communication is strictly
> prohibited. If you have received this communication in error, please
> immediately notify the sender by return e-mail message and delete all
> copies of the original communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--- ---
Eduardo Morras
[sqlite] Ideas on how to use backup API for remote, incremental backup?
On Tue, Oct 6, 2015 at 2:57 PM, Clemens Ladisch wrote: > It backs up to any disk that you can access. > Do you have a network file system? > Well yes, but I'd like to handle it at the application layer. Basically, we operate a custom replication layer atop sqlite. It replicates individual transactions great with 2-phase commit, but right now you need to manually "bootstrap" a new server by copying the database from a different server. I'd like to auto-bootstrap a new node by just starting it, it'd connect to a peer, and then download the entire database. > > how to use this API to do an incremental backup > > This API is not incremental; it always copies the entire database. > Agreed it will copy the entire database, but incrementally -- one bit at a time. Each call to sqlite3_backup_step() backs up a bit more to the target file on disk. My goal is to instead have sqlite3_backup_step() copy some pages to some block of RAM, so I can send that data over the wire and write it to the remote disk. -david
[sqlite] Multiple connections to in-memory DB and virtual tables
On Tue, 6 Oct 2015 15:39:08 +0100 Simon Slavin wrote: > There are also things Virtual Tables can't do. For instance you > cannot index a Virtual Table using SQL commands. Does "SELECT reindex_virtual_table() FROM virtual_table" count as SQL command only? --- --- Eduardo Morras
[sqlite] obtain a copy of a table
On 2015-10-06 04:52 PM, H?ctor Fiandor wrote:
> Dear fellows:
>
>
>
> I handle my applictions with Lazarus and use BD as sqlite. Sometimes I have
> to obtain a copy of an sqlite table and now I use the method of read and
> write, but I want to know if exists and SQL command that do the same with
> less efforts.
Hi H?ctor,
Copying a table via SQL is rather easy with respect to the data... but
tables may have Indices, Constraints and Triggers that were added after
creation or depend on other tables. Tables might also have an
Auto-Increment value set on a primary key and kept in another table
(sqlite_sequence), etc.
If your table has no Auto-increment explicitly defined, and no external
or table-dependent constraints, then the following SQL will happily
produce a copy of the basic table layout and its contents:
CREATE TABLE newTableName AS SELECT * FROM oldTableName;
If you fancy keeping the original Schema, then an extra step is needed,
which entail reading the old SQL, replacing the table name and then
applying it and copying data, which in some pseudo-code might look like
this:
Open DB;
$SQL = ReadFromSQLite(" SELECT sql FROM sqlite_master WHERE type='table'
AND name='oldTableName'; ");
replace($SQL, "CREATE TABLE oldTableName", "CREATE TABLE newTableName");
// Beware the old name might be enclosed in quotes or such.
if sqlite-execute($SQL) then
sqlite_execute(" INSERT INTO newTableName SELECT * FROM
oldTableName; ");
Note that it is better to determine the fields returned by a "SELECT *"
statement and using that string rather than the "*" because technically,
there is no guarantee that the order of fields returned by * will be the
same as it was defined in the table. (It mostly /is/ courtesy of
convention, but it is not guaranteed behaviour).
You can expand the above code to check and add external constraints,
indices and triggers. Use pragma foreign_keys = OFF; at the start where
you are duplicating tables with foreign key constraints.
If you are copying a table to a different DB, then the name need not be
replaced (obviously).
The SQLite CLI can dump an entire database to SQL if you fancy copying
the DB in total.
Hope some of this helped,
Cheers!
Ryan
[sqlite] Multiple connections to in-memory DB and virtual tables
AFAICT the FROM clause is superflous, as the function has no (supported) way of detecting which table(s) the FROM clause contains. What is your "reindex_virtual_table()" function (I assume it is a user implemented function) supposed to do? -Urspr?ngliche Nachricht- Von: Eduardo Morras [mailto:emorrasg at yahoo.es] Gesendet: Dienstag, 06. Oktober 2015 19:08 An: sqlite-users at mailinglists.sqlite.org Betreff: Re: [sqlite] Multiple connections to in-memory DB and virtual tables On Tue, 6 Oct 2015 15:39:08 +0100 Simon Slavin wrote: > There are also things Virtual Tables can't do. For instance you > cannot index a Virtual Table using SQL commands. Does "SELECT reindex_virtual_table() FROM virtual_table" count as SQL command only? --- --- Eduardo Morras ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] sqlite-users Digest, Vol 94, Issue 4
On 10/06/2015 02:45 AM, Andrew Cunningham wrote:
>> Ok. My first guess is that you are missing the "suggested index" on the
>> child table. Without this, if there is a foreign key violation in the
>> db, each insert on the parent table will cause a linear scan of the
>> entire child table. With the index, it's just a simple seek.
>>
>> https://www.sqlite.org/foreignkeys.html#fk_indexes
>>
>> If you're unsure, open your database using the command line tool and
>> dump the schema using the ".schema" command. Post that here and someone
>> should be able to tell you if you're missing an index or not.
>>
>
> OK.
>
> D:\ODB\odb-examples-2.4.0\relationship>D:\ODB\sqlite3.exe test.db
> SQLite version 3.8.11.1 2015-07-29 20:00:57
> Enter ".help" for usage hints.
> sqlite> .schema
> CREATE TABLE "relation_employer" (
>"name" TEXT NOT NULL PRIMARY KEY);
> CREATE TABLE "relation_project" (
>"name" TEXT NOT NULL PRIMARY KEY);
> CREATE TABLE "relation_employee" (
>"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
>"first" TEXT NOT NULL,
>"last" TEXT NOT NULL,
>"employer" TEXT NOT NULL,
>CONSTRAINT "employer_fk"
> FOREIGN KEY ("employer")
> REFERENCES "relation_employer" ("name")
> DEFERRABLE INITIALLY DEFERRED);
> CREATE TABLE "relation_employee_projects" (
>"object_id" INTEGER NOT NULL,
>"value" TEXT NOT NULL,
>CONSTRAINT "object_id_fk"
> FOREIGN KEY ("object_id")
> REFERENCES "relation_employee" ("id")
> ON DELETE CASCADE,
>CONSTRAINT "value_fk"
> FOREIGN KEY ("value")
> REFERENCES "relation_project" ("name")
> DEFERRABLE INITIALLY DEFERRED);
> CREATE INDEX "relation_employee_projects_object_id_i"
>ON "relation_employee_projects" ("object_id");
I think you need indexes on the child key columns that don't already
have them. i.e.
CREATE INDEX relation_employee_projects_value_i ON
relation_employee_projects_value(value);
CREATE INDEX relation_employee_employer_i ON relation_employee(employer);
Dan.
[sqlite] Multiple connections to in-memory DB and virtual tables
On Tue, Oct 6, 2015 at 4:39 PM, Simon Slavin wrote: > On 6 Oct 2015, at 2:51pm, Dominique Devienne wrote: > > Is this use case supported by SQLite? > > Whether this works correctly depends on how your Virtual Module is > written. It should be possible to implement this correctly, but I can > imagine a virtual system written quickly which does not take the care to > get this right. > It was not written "quickly" but it was definitely not written with that use-case in mind. And writing a virtual module correctly is not easy. > There are also things Virtual Tables can't do. For instance you cannot > index a Virtual Table using SQL commands. > That's a bit beside the point. Our vtable already provide indexing support. Just not dynamically added indexing via SQL commands. >From Gunter's answer, it seems at least that this use case is possible, even though his is multi-process and DB-file based, I think I see the parallel he's trying to make to our multi-threaded (1 connection / thread) and in-memory DB use case. I just wish I could get a bit more hand-holding on how to proceed, if possible :). As it is not quite crystal clear to me yet. Especially since that vmodule code was written a few years back, so I don't recall the details. Thanks, --DD
[sqlite] Ideas on how to use backup API for remote, incremental backup?
Well, there's the obvious Elephant in the room - SQL. You could just reduce any DB to SQL statements and pass those along at whichever pace/destination/byte-mode you fancy. The target system will have zero trouble turning it into a DB, thanks to the SQLite engine already able to parse SQL. This can become really efficient later when you only move updated bits - plus, if bandwidth is an issue, SQL text compresses well. Cheers, Ryan On 2015-10-06 03:44 PM, David Barrett wrote: > On Tue, Oct 6, 2015 at 8:36 PM, Simon Slavin wrote: > >> Or copy an existing /open/ database file to the new server using the >> SQLite Backup API, [requires other connections to stop modifying the >> database for long enough for the copy to be made] >> > Well the backup API works with WAL mode [1] so it can still be modified > while being backed up (though I imagine the WAL file will get huge during > the backup, but that's fine). > > Regardless, all those solutions require me to wait for the entire backup to > complete before sending the file to the remote host -- my goal is to send > it one page at a time (eg, send the pages as the backup API processes them) > so as to avoid the large delay and disk space of the backup itself. > > -david > > [1] http://sqlite.1065341.n5.nabble.com/WAL-mode-and-backup-API-td40107.html > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple connections to in-memory DB and virtual tables
We use (for years now) an in-memory DB with both tables and virtual tables in our app. We recently started experimenting with multiple connections to the same in-memory DB, thanks to URI filenames and shared cache, to perform queries in parallel. This seems to work well with regular tables, and we get close to a 3x speedup using 4-5 threads on some use cases (running many queries). But now we're trying to configure virtual tables and multiple connections to that unique in-memory DB, and we're having some trouble. We're not sure yet it's not related to our code, which so far assumed a single connection was in use accessing the vtables, but I'd like to get a reality check for that use case from experts. On the first connection, we declare the virtual module and its virtual tables. This also adds the vtables to sqlite_master. But then on additional connections to that same in-memory DB, we also need to declare a vmodule and vtables, right? Yet it's already in sqlite_master, and it fails on the "create virtual table" statement. Is this use case supported by SQLite? Has any one tried this successfully? Any chance this might work in the near future? Thanks for any input on this. --DD PS: assume read-only use cases for now, and no concurrent writes during these // queries.
[sqlite] Multiple connections to in-memory DB and virtual tables
On 6 Oct 2015, at 2:51pm, Dominique Devienne wrote: > On the first connection, we declare the virtual module and its virtual > tables. This also adds the vtables to sqlite_master. But then on additional > connections to that same in-memory DB, we also need to declare a vmodule > and vtables, right? Yet it's already in sqlite_master, and it fails on the > "create virtual table" statement. > > Is this use case supported by SQLite? > Has any one tried this successfully? > Any chance this might work in the near future? Whether this works correctly depends on how your Virtual Module is written. It should be possible to implement this correctly, but I can imagine a virtual system written quickly which does not take the care to get this right. There are also things Virtual Tables can't do. For instance you cannot index a Virtual Table using SQL commands. Simon.
[sqlite] Ideas on how to use backup API for remote, incremental backup?
On 6 Oct 2015, at 2:44pm, David Barrett wrote: > Regardless, all those solutions require me to wait for the entire backup to > complete before sending the file to the remote host -- my goal is to send > it one page at a time (eg, send the pages as the backup API processes them) > so as to avoid the large delay and disk space of the backup itself. In that case it's probably best, as was suggested by Clemens, to use a network filing system to allow a computer to access both the source file storage and the destination file storage. By opening a file on the remote filespace using SQLite you can use the Backup API to write directly to the destination filespace. Simon.
[sqlite] Multiple connections to in-memory DB and virtual tables
1) Each connection needs to load the image(s) that contain(s) the virtual module code (unless you have already linked it into a single image). 2) Each connection needs to declare the virtual module(s) by calling sqlite3_create_module(_v2). 3) Each connection needs to declare the tables using "CREATE VIRTUAL TABLE IF NOT EXISTS USING [()]" 4) Each xCreate/xConnect method needs to call sqlite3_declare_vtab() to tell SQLite about the table's fields 5) Each xCrteae/xConnect method also needs to allocate and return a sqlite_vtab structure on success, or an error status. Ad3) using IF NOT EXISTS prevents problems with failing re-creation of tables -Urspr?ngliche Nachricht- Von: Dominique Devienne [mailto:ddevienne at gmail.com] Gesendet: Dienstag, 06. Oktober 2015 16:53 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Multiple connections to in-memory DB and virtual tables On Tue, Oct 6, 2015 at 4:39 PM, Simon Slavin wrote: > On 6 Oct 2015, at 2:51pm, Dominique Devienne wrote: > > Is this use case supported by SQLite? > > Whether this works correctly depends on how your Virtual Module is > written. It should be possible to implement this correctly, but I can > imagine a virtual system written quickly which does not take the care > to get this right. > It was not written "quickly" but it was definitely not written with that use-case in mind. And writing a virtual module correctly is not easy. > There are also things Virtual Tables can't do. For instance you > cannot index a Virtual Table using SQL commands. > That's a bit beside the point. Our vtable already provide indexing support. Just not dynamically added indexing via SQL commands.
[sqlite] Problem sqlite
I suspect you are having a chracter encoding problem. SQLite supports UTF encoding. -Urspr?ngliche Nachricht- Von: Jonathan [mailto:jonathanmejiaa at hotmail.com] Gesendet: Dienstag, 06. Oktober 2015 15:27 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] Problem sqlite > Hello, i need your help, i have problems with the use special > character for example "?" , work with vb net. The problem consist when > save this character in the data base sqlite, this chance in another > character. I hope your help. Thank you. Bach. Jonathan Mej?a Acosta Tel. 7104-0440 Costa Rica Correo Enviado desde mi iPhone ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Ideas on how to use backup API for remote, incremental backup?
On 6 Oct 2015, at 1:52pm, David Barrett wrote: > Well yes, but I'd like to handle it at the application layer. Basically, > we operate a custom replication layer atop sqlite. It replicates > individual transactions great with 2-phase commit, but right now you need > to manually "bootstrap" a new server by copying the database from a > different server. I'd like to auto-bootstrap a new node by just starting > it, it'd connect to a peer, and then download the entire database. Copy an existing /closed/ database file to the new server using file commands, [other connections trying to make modifications will, I think, get error messages] Or copy an existing /open/ database file to the new server using the SQLite Backup API, [requires other connections to stop modifying the database for long enough for the copy to be made] Or copy an existing /open/ database file to the new server by creating the new database using sqlite_open(), then ATTACHing the source database and using INSERT (SELECT ...) [locks other connections trying to use the source database out while copying is done] Simon.
[sqlite] Multiple connections to in-memory DB and virtual tables
We are using multiple processes accessing the same on-disk db with almost exclusively virtual tables. Once the tables have been declared (CREATE VIRTUAL TABLE...), all other connections need only to load the modules. The xConnect method gets called on first access. -Urspr?ngliche Nachricht- Von: Dominique Devienne [mailto:ddevienne at gmail.com] Gesendet: Dienstag, 06. Oktober 2015 15:51 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] Multiple connections to in-memory DB and virtual tables We use (for years now) an in-memory DB with both tables and virtual tables in our app. We recently started experimenting with multiple connections to the same in-memory DB, thanks to URI filenames and shared cache, to perform queries in parallel. This seems to work well with regular tables, and we get close to a 3x speedup using 4-5 threads on some use cases (running many queries). But now we're trying to configure virtual tables and multiple connections to that unique in-memory DB, and we're having some trouble. We're not sure yet it's not related to our code, which so far assumed a single connection was in use accessing the vtables, but I'd like to get a reality check for that use case from experts. On the first connection, we declare the virtual module and its virtual tables. This also adds the vtables to sqlite_master. But then on additional connections to that same in-memory DB, we also need to declare a vmodule and vtables, right? Yet it's already in sqlite_master, and it fails on the "create virtual table" statement. Is this use case supported by SQLite? Has any one tried this successfully? Any chance this might work in the near future? Thanks for any input on this. --DD PS: assume read-only use cases for now, and no concurrent writes during these // queries. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] What's the status of SQLite4? Where can I find its source code?
@Scott Robison +1 mate; I thought the same thing as I was reading this. Maybe a different concept, something like SQLiteNG (SQLite Next Generation)? It's just a thought, that's all. Cheers. On 10/05/2015 05:47 PM, Scott Robison wrote: > It seems to me the reason these questions keep coming up because people are > encouraged to use the latest and greatest version of SQLite 3 at all times, > and SQLite 4 sounds like something even later and greater. I wonder if a > name like SQLite X might reduce the number of queries. Well, maybe not > SQLite X, but something different that doesn't imply what 4 implies. > > On Mon, Oct 5, 2015 at 4:30 AM, Richard Hipp wrote: > >> Jerry & Scott: What are you hoping to achieve with SQLite4 that you >> cannot do with SQLite3? >> >> On 10/5/15, Stephen Chrzanowski wrote: >>> There was a thread sometime last year about this. Basically, SQLite4 >> isn't >>> planned for a release at this time, and from what I understood then, >>> chances are there isn't going to be a thought towards release for at few >>> more years. It basically is a playground to test theories, it is buggy, >>> and such. It shouldn't be considered for use in a production >> environment. >>> On Mon, Oct 5, 2015 at 1:18 AM, Scott Doctor >> wrote: Is there a release date set for sqlite4? Scott Doctor scott at scottdoctor.com -- On 10/4/2015 9:14 PM, Stephen Chrzanowski wrote: > https://sqlite.org/src4/tree?ci=trunk > > On Mon, Oct 5, 2015 at 12:02 AM, Jerry wrote: > > Seems I could not find its source code ... >> https://sqlite.org/src4/doc/trunk/www/index.wiki >> >> Thanks. >> ___ >> 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-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 >>> >> >> -- >> D. Richard Hipp >> drh at sqlite.org >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > >
[sqlite] Multiple connections to in-memory DB and virtual tables
Your thread made me ponder what might be up, so I wrote a test using fts3: http://pastebin.com/AKP2yHuM and AFAICT, it works alright. I haven't specifically verified each of the flags to sqlite3_open_v2(), I just spammed what looked relevant in there. Hmm, should have commented the #if's before posting. The ":memory:" case is to verify that it doesn't work right with plain in-memory database. The real-file case doesn't work if run twice, to verify that I'm not accidentally working right with a shared in-memory database. The third version should be a real shared in-memory database, as best I can tell. The other #if verifies that it works the same for regular table or virtual table. If I were debugging this, the first place I'd look is my virtual table's xConnect/xCreate implementation. -scott On Tue, Oct 6, 2015 at 7:53 AM, Dominique Devienne wrote: > On Tue, Oct 6, 2015 at 4:39 PM, Simon Slavin wrote: > > > On 6 Oct 2015, at 2:51pm, Dominique Devienne > wrote: > > > Is this use case supported by SQLite? > > > > Whether this works correctly depends on how your Virtual Module is > > written. It should be possible to implement this correctly, but I can > > imagine a virtual system written quickly which does not take the care to > > get this right. > > > > It was not written "quickly" but it was definitely not written with that > use-case in mind. And writing a virtual module correctly is not easy. > > > > There are also things Virtual Tables can't do. For instance you cannot > > index a Virtual Table using SQL commands. > > > > That's a bit beside the point. Our vtable already provide indexing support. > Just not dynamically added indexing via SQL commands. > > From Gunter's answer, it seems at least that this use case is possible, > even though his is multi-process and DB-file based, I think I see the > parallel he's trying to make to our multi-threaded (1 connection / thread) > and in-memory DB use case. > > I just wish I could get a bit more hand-holding on how to proceed, if > possible :). As it is not quite crystal clear to me yet. Especially since > that vmodule code was written a few years back, so I don't recall the > details. Thanks, --DD > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Ideas on how to use backup API for remote, incremental backup?
On 10/6/15, David Barrett wrote: > sqlite has a cool "online backup" API: https://www.sqlite.org/backup.html > However, it only backs up to a local disk. I'm wondering if anybody can > think on how to use this API to do an incremental backup over a network > connection to a remote host? The networking part is easy. But I can't > figure out how to programmatically get the binary of the database file into > memory such that I can send over the network (other than waiting for the > whole thing to backup to disk and then just doing a remote disk copy, > obviously, but my file is so large I don't want to wait that long nor do I > have enough disk to do that). I'm not really familiar with the VFS layer > -- would there be some way to do a custom VFS that the online backup API > writes to, and then I just copy the blocks over from that? Thanks for any > creative ideas! > Suppose you have a really massive database that is being continually updated, and you want to have a live backup to another host over a network. The SQLite backup API will work for this, we believe, as long as the database is only being written from a single database connection. The backup API transfers information from a "source" database connection over to a "destination" database connection. There are several considerations here: (1) For the destination database connection, use a custom VFS that writes content over the network rather than writing content to disk. This custom VFS does not need to be general-purpose. It can be highly specialized so that it only handles the specific VFS methods that the backup interface will generate. (2) For the source database connection of the backup, use the same database connection that is used for writing to the database. That means that when changes are made to the source database, the backup mechanism can go back and resend only those pages that actually changed. If the database is modified by any database connection other than the one used for backup, then the backup has no way of knowing which pages changed, and so it has to start over again at the beginning and rewrite every page. (3) The thread that is running the backup should monitor how fast new content is being shipped over the network and should throttle calls to sqlite3_backup_step() to prevent too much content from being queued up in cache. -- D. Richard Hipp drh at sqlite.org
[sqlite] obtain a copy of a table
Dear fellows: I handle my applictions with Lazarus and use BD as sqlite. Sometimes I have to obtain a copy of an sqlite table and now I use the method of read and write, but I want to know if exists and SQL command that do the same with less efforts. Thanks for your suggestions Ing. H?ctor Fiandor hfiandor at ceniai.inf.cu
[sqlite] Ideas on how to use backup API for remote, incremental backup?
David Barrett wrote: > sqlite has a cool "online backup" API: https://www.sqlite.org/backup.html > However, it only backs up to a local disk. It backs up to any disk that you can access. Do you have a network file system? > how to use this API to do an incremental backup This API is not incremental; it always copies the entire database. Regards, Clemens
[sqlite] Incorrect limit behavior in conjunction with offset, union all, and order by.
On 10/5/15, Matt DeLand wrote: > The following SQL outputs more rows than expected. The behavior seems to > require the use of UNION ALL, ORDER BY (in the column being selected), and > an OFFSET. Thanks for providing a test case. See https://www.sqlite.org/src/info/b65cb2c8d91f6685 for the ticket. > > Swapping the two clauses of the union also produces the wrong result, > though it's definitely necessary to order by x. > > The answer is also correct if you build an index on the queried column. > > Thanks for your help (and great software)! > Matt > > > > ``` > drop table if exists test; > create table test ( x ); > insert into test values(1); > insert into test values(2); > > -- expected output: a single row > -- 1|1 > -- > -- actual output: > -- 1|1 > -- 2|2 > > SELECT * FROM ( > VALUES(0,0) > ) > UNION ALL > SELECT * FROM ( > SELECT rowid, x FROM test ORDER BY x > ) > LIMIT 1 > OFFSET 1 > ; > > > -- test again after building index > > create index test_x on test(x); > > -- rerun query > > SELECT * FROM ( > VALUES(0,0) > ) > UNION ALL > SELECT * FROM ( > SELECT rowid, x FROM test ORDER BY x > ) > LIMIT 1 > OFFSET 1 > ; > > -- output as expected! > > ``` > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] Ideas on how to use backup API for remote, incremental backup?
You could mount a directory as a ramdisk. On Tue, Oct 6, 2015 at 7:52 AM, David Barrett wrote: > On Tue, Oct 6, 2015 at 2:57 PM, Clemens Ladisch > wrote: > > > It backs up to any disk that you can access. > > Do you have a network file system? > > > > Well yes, but I'd like to handle it at the application layer. Basically, > we operate a custom replication layer atop sqlite. It replicates > individual transactions great with 2-phase commit, but right now you need > to manually "bootstrap" a new server by copying the database from a > different server. I'd like to auto-bootstrap a new node by just starting > it, it'd connect to a peer, and then download the entire database. > > > > > how to use this API to do an incremental backup > > > > This API is not incremental; it always copies the entire database. > > > > Agreed it will copy the entire database, but incrementally -- one bit at a > time. Each call to sqlite3_backup_step() backs up a bit more to the target > file on disk. My goal is to instead have sqlite3_backup_step() copy some > pages to some block of RAM, so I can send that data over the wire and write > it to the remote disk. > > -david > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Problem sqlite
> Hello, i need your help, i have problems with the use special > character for example "?" , work with vb net. The problem consist when save > this character in the data base sqlite, this chance in another character. I > hope your help. Thank you. Bach. Jonathan Mej?a Acosta Tel. 7104-0440 Costa Rica Correo Enviado desde mi iPhone
[sqlite] Sqlite connection/ Sqlite3_open
Hi every one, I'm a beginner with sqlite and I'm a C# programmer. Ive seen the documentation on the c/c++ API but i've no Idea of how to implement that. I've downloaded the system.data.sqlite dlls which is working: I can open a connection, list the tables in an sql3 db... But as I tried more complicated queries they don't work which is normal because i dont know the sql syntax. I read that the sqlite3_prepare command transforms sql statements in sqlite "statements". As I know transact sql syntax, I would like to use this knowledge for managing sqlite db. AS anyone ever faces the same problem or can someone help ? Thanks in advance.
[sqlite] Ideas on how to use backup API for remote, incremental backup?
sqlite has a cool "online backup" API: https://www.sqlite.org/backup.html However, it only backs up to a local disk. I'm wondering if anybody can think on how to use this API to do an incremental backup over a network connection to a remote host? The networking part is easy. But I can't figure out how to programmatically get the binary of the database file into memory such that I can send over the network (other than waiting for the whole thing to backup to disk and then just doing a remote disk copy, obviously, but my file is so large I don't want to wait that long nor do I have enough disk to do that). I'm not really familiar with the VFS layer -- would there be some way to do a custom VFS that the online backup API writes to, and then I just copy the blocks over from that? Thanks for any creative ideas! -david
