Re: [sqlite] A memvfs for loading/saving database from buffer

[stephen liu]

I try to implement One SQLite Database Per User.

http://stackoverflow.com/questions/128919/extreme-sharding-one-sqlite-database-per-user
Extreme Sharding: One SQLite Database Per User

2009/4/29 stephen liu 

>
> I want to save multipile sqlite database in one file.
> The backup api is not fix for this requirement.
>
> I want to save sqlite database as a entry of the dbm.
> For example, the user's addressbook save in a sqlite database,
> and the buffer of the database may be store in a dbm.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A memvfs for loading/saving database from buffer

[stephen liu]

I want to save multipile sqlite database in one file.
The backup api is not fix for this requirement.

I want to save sqlite database as a entry of the dbm.
For example, the user's addressbook save in a sqlite database,
and the buffer of the database may be store in a dbm.

2009/4/29 Roger Binns 

> stephen liu wrote:
> > The attachment is a memvfs implementation for sqlite.
>
> The mailing list strips out attachments.
>
> > With the memvfs, we can loading/saving sqlite database from buffer.
>
> Even simpler, you can use sqlite3_backup API to copy from a disk to
> :memory: and the reverse direction when you are done.
>
> Roger
> ___
> 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] A memvfs for loading/saving database from buffer

[stephen liu]

The mailing list strips out attachments.

Please download from:
http://spserver.googlecode.com/files/spmemvfs.tar.gz

2009/4/29 Virgilio Alexandre Fornazin 

> Where we can get the code ?
>  
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A memvfs for loading/saving database from buffer

[Virgilio Alexandre Fornazin]

Where we can get the code ?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of stephen liu
Sent: terça-feira, 28 de abril de 2009 22:24
To: sqlite-users@sqlite.org
Subject: [sqlite] A memvfs for loading/saving database from buffer

Hi,

The attachment is a memvfs implementation for sqlite.

With the memvfs, we can loading/saving sqlite database from buffer.

There also includes a demo to show how to use it.

Cheers,

Stephen Liu

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

Re: [sqlite] A memvfs for loading/saving database from buffer

[Roger Binns]

stephen liu wrote:
> The attachment is a memvfs implementation for sqlite.

The mailing list strips out attachments.

> With the memvfs, we can loading/saving sqlite database from buffer.

Even simpler, you can use sqlite3_backup API to copy from a disk to
:memory: and the reverse direction when you are done.

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

[sqlite] A memvfs for loading/saving database from buffer

[stephen liu]

Hi,

The attachment is a memvfs implementation for sqlite.

With the memvfs, we can loading/saving sqlite database from buffer.

There also includes a demo to show how to use it.

Cheers,

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

[sqlite] Merging two databases

[Kristoffer Danielsson]

Consider this simple database:

 

CREATE TABLE T1

(TestID INTEGER PRIMARY KEY, X INTEGER NOT NULL, Y NOT NULL, Z NOT NULL, 
UNIQUE(X, Y));

 

CREATE TABLE T2

(TestID INTEGER NOT NULL, X2 INTEGER NOT NULL, Y2 NOT NULL, Z2 NOT NULL);

 

What would be the most efficient way of copying all T1 entries + their 
corresponding 0..N T2 entries from one database to another (both with the same 
schema)? Note that TestID=1 in Database1 could be a totally different item than 
TestID=1 in Database2! Also pay attention to the UNIQUE-clause. It implies 
"semi-unique coordinates" in this case, which makes the merge a bit harder 
(perhaps an "INSERT OR IGNORE" is sufficient here?)!

 

I tried "INSERT INTO T1 from (SELECT * FROM Database2.T1)" but soon realized 
SQLite does not handle this properly. E.g. SQL Server takes care of the primary 
key in this case. SQLite doesn't!

Also, how would I copy all T2-items properly? Using triggers?

 

Thanks for your help!

/Chris

_
Vem är du? Gör personlighetstestet på MSN Dejting!
http://dejting.se.msn.com/channel/index.aspx?trackingid=1002952
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[Matthew L. Creech]

On Tue, Apr 28, 2009 at 3:25 AM, liubin liu <7101...@sina.com> wrote:
>
> Thanks!
>
> It sounds pretty good. But I don't understand it exactly. Could you write
> down the sample codes?
>

Please see the documentation here:

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

You'll have something along the lines of (just a sketch, obviously):

typedef struct {
sqlite3 *db;
sqlite3_stmt *stmt;
} my_handle_t;

my_handle_t my_init() {
sqlite3_open();
sqlite3_prepare();
return handle;
}

void my_exec(my_handle_t handle, int id) {
sqlite3_bind_int();
/* Put in a loop or whatever: */
sqlite3_step();
/* After you've gotten all the result rows: */
sqlite3_reset();
}


Your caller would then call my_init() once to get a handle, then call
my_exec() a bunch of times using that handle.  The way your example is
doing it, _every_ time through the loop it does an exec(), which
re-compiles the same SQL code (which is not a fast operation).

There are plenty of other examples floating around on this mailing
list, I'm sure - just do some digging.

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

Re: [sqlite] Syntax for column names?

[D. Richard Hipp]

On Apr 28, 2009, at 11:38 AM, Igor Tandetnik wrote:

> Jean-Denis Muys  wrote:
>> My create table statement (program-generated from a text file) below
>> yields a syntax error.
>
> Column names should be valid identifiers (a sequence of digits,  
> letters
> and underscores that doesn't begin with a digit), or else enclosed in
> double quotes, as in
>
> create table document ("TC1.DOCUMENT.SORT" TEXT, ...)
>
> The same is true for table names, index names and so on.


You should use double-quotes (as Igor recommends) because that is the  
SQL standard.  However, please also note that for compatibility with  
MySQL and with MS-SQL Server, SQLite also supports quoting identifiers  
using grave accents and square brackets.  All of the following work  
the same:

  "tc1.doc.sort"
  `tc1.doc.sort`
  [tc1.doc.sort]

Let me reemphasize that the first example (double-quotes) is preferred.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Syntax for column names?

[Igor Tandetnik]

Jean-Denis Muys  wrote:
> My create table statement (program-generated from a text file) below
> yields a syntax error.

Column names should be valid identifiers (a sequence of digits, letters 
and underscores that doesn't begin with a digit), or else enclosed in 
double quotes, as in

create table document ("TC1.DOCUMENT.SORT" TEXT, ...)

The same is true for table names, index names and so on.

Igor Tandetnik



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

Re: [sqlite] How do you guys use sqlite from C++?

[Harald Nehring]

Just stumbled over SOCI (http://soci.sourceforge.net/ 
[http://soci.sourceforge.net/]) in my search for an easy to use C++ interface 
to relational DBs. Scales from simple scalar queries to OR mapping and 
STL/Boost integration. Supports SQLite as backend.

Haven't tested it yet, but the concept sounds clean and promising.

MfG H. Nehring



Pt! Schon vom neuen WEB.DE MultiMessenger gehört? 
Der kann`s mit allen: *http://www.produkte.web.de/messenger/?did=3123* 
[http://www.produkte.web.de/messenger/?did=3123] 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Syntax for column names?

[P Kishor]

On Tue, Apr 28, 2009 at 10:03 AM, Jean-Denis Muys  wrote:
> Hello,
>
> My create table statement (program-generated from a text file) below yields
> a syntax error.
>
> I went to SQL syntax diagrams, and as far as I could see, the "table-name"
> box is not detailed in its own diagram.
>
> I suspect SQLite doesn't like column names with two periods. It this it?
>
> Here is my statement:
>
> create table document (TC1.DOCUMENT.SORT TEXT,TC1.DOCUMENT.CLE_CATAL
> TEXT,TC1.DOCUMENT.DORIS_SUBKEY TEXT,TC1.DOCUMENT.DATE_CREAT
> TEXT,TC1.DOCUMENT.LIEN_CATAL TEXT,TC1.DOCUMENT.FICHIER
> TEXT,TC1.DOCUMENT.DATE_MAJ TEXT,TC1.DOCUMENT.DORIS_STAMP
> TEXT,TC1.DOCUMENT.DIRDOC TEXT,TC1.DOCUMENT.UTIL_CREAT
> TEXT,TC1.DOCUMENT.DRAWER TEXT,TC1.DOCUMENT.DORIS_KEY TEXT PRIMARY
> KEY,TC1.DOCUMENT.FOLDER TEXT,TC1.DOCUMENT.CLE_NUMPER TEXT,TC1.DOCUMENT.TYPE
> TEXT,TC1.DOCUMENT.DISPLAY TEXT,TC1.DOCUMENT.CATTABKEY TEXT);
>

enclose column names with double quotes.

[08:23 PM] ~/Sites/pkmap$sqlite3
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE foo (foo.bar.baz TEXT);
SQL error: near ".": syntax error
sqlite> CREATE TABLE foo ("foo.bar.baz" TEXT);
sqlite> INSERT INTO foo ("foo.bar.baz") VALUES ('some stuff');
sqlite> .h on
sqlite> .m col
sqlite> SELECT * FROM foo;
foo.bar.baz
---
some stuff
sqlite>

> Thanks,
>
> Jean-Denis
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org/
Carbon Model http://carbonmodel.org/
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/
Science Commons Fellow, Geospatial Data http://sciencecommons.org
Nelson Institute, UW-Madison http://www.nelson.wisc.edu/
---
collaborate, communicate, compete
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Syntax for column names?

[Jean-Denis Muys]

Hello,

My create table statement (program-generated from a text file) below yields
a syntax error.

I went to SQL syntax diagrams, and as far as I could see, the "table-name"
box is not detailed in its own diagram.

I suspect SQLite doesn't like column names with two periods. It this it?

Here is my statement:

create table document (TC1.DOCUMENT.SORT TEXT,TC1.DOCUMENT.CLE_CATAL
TEXT,TC1.DOCUMENT.DORIS_SUBKEY TEXT,TC1.DOCUMENT.DATE_CREAT
TEXT,TC1.DOCUMENT.LIEN_CATAL TEXT,TC1.DOCUMENT.FICHIER
TEXT,TC1.DOCUMENT.DATE_MAJ TEXT,TC1.DOCUMENT.DORIS_STAMP
TEXT,TC1.DOCUMENT.DIRDOC TEXT,TC1.DOCUMENT.UTIL_CREAT
TEXT,TC1.DOCUMENT.DRAWER TEXT,TC1.DOCUMENT.DORIS_KEY TEXT PRIMARY
KEY,TC1.DOCUMENT.FOLDER TEXT,TC1.DOCUMENT.CLE_NUMPER TEXT,TC1.DOCUMENT.TYPE
TEXT,TC1.DOCUMENT.DISPLAY TEXT,TC1.DOCUMENT.CATTABKEY TEXT);

Thanks,

Jean-Denis


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

[sqlite] passive DNS monitoring

[Steve Friedman]

This article http://isc.sans.org/diary.html?storyid=6271
mentions a presentation on passive DNS monitoring that will be given at
http://www.sans.org/sansfire09/night.php in Baltimore.

What do you think?

Steve


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

Re: [sqlite] Using SQLite3 on On-Time RTOS ...

[Sylvain Pointeau]

.. or you can pay for a support ?

On Mon, Apr 27, 2009 at 3:31 PM, Virgilio Alexandre Fornazin <
virgilioforna...@gmail.com> wrote:

> Maybe RTOS kernel does not implement Wide-Char functions...
>
> Windows VFS must guard them with a SQLITE_WINDOWS_NO_UNICODE
> macro or something like that at compile time, but you should
> do it yourself... then you can contribute it back to SQLite.
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kent Dahl
> Sent: segunda-feira, 27 de abril de 2009 09:58
> To: General Discussion of SQLite Database
> Subject: [sqlite] Using SQLite3 on On-Time RTOS ...
>
> Hi.
>
> I wondered whether anyone is using SQLite3 on the On-Time RTOS platform,
> and if so, if there are any patches or tips to help others along?
>
> We've been giving the SQLite 3.6.10 amalgamation source code for Windows
> a try against RTOS 5.14 and it compiled out of the box. However, we ran
> into some linker and run-time errors. After a fair amount of
> experimenting, we got it up and running, but only towards an in-memory
> database. We still have problems opening existing or creating database
> files.
>
> I'll outline what we ran into and what we've tried so far.
>
> The linker errors were primarily towards wide char APIs that didn't
> exist on RTOS. Most of these could easily be ifdef'd away. There was
> alternate Win95/98/ME code we could trigger if we changed "isNT" to 0,
> which helped a bit. Some, like AreFileApisANSI we just defined to 1.
>
> We then ran into a crasher caused by convertUtf8Filename returning NULL,
> so we changed that to use a fallback. Just strdup-ing the input string
> if the conversion failed. Debugging this took a little while, because
> using the amalgamation source meant that the RTOS compiler crossed a 64k
> symbols limit, meaning breakpoints and backtraces broke badly.
>
> Finally we had something that linked and ran, but only towards in-memory
> database. When we tried to open an existing database (or create a new
> one) using the sqlite3_open_v2 API, it kept returning SQLITE_NOMEM(7).
> When I tried debugging this, I got as far as the sqlite3BtreeFactory
> call, but because of the breakpointing problems I didn't get much
> further.
>
> So, my questions then are:
> * Is anyone using or have used SQLite3 on RTOS?
> * Are the older Win95/98/ME code paths still actively used, tested and
> found to be working? (Or should I expect a few inches of legacy dust and
> bugs in them?)
> * Is there a good way to get more debug information? (SQLITE_DEBUG seems
> more targetted at debugging SQL statements.)
> * Am I barking up any of the wrong trees?
>
> Hope someone out there has some ideas or pointers to help me get
> motivated enough to give another stab at it. :)
>
>
> = Additional information =
>
> === Linker errors ===
>
> Example linker error:
> "Error: DLL dependency in CDP.EXE: KERNEL32.dll.DeleteFileW"
>
> These APIs also gave linker errors:
> - LockFileEx
> - GetTempPathW
> - GetFullPathNameW
> - GetDiskFreeSpaceW
> - AreFileApisANSI
>
> === References ===
>
> * On-Time RTOS - http://www.on-time.com/
>
>
> --
> Mvh/Regards,
>
> Kent Dahl
> Software Developer
>
> Industrial Control Design AS
>
>
>
> Phone: +47 93 07 32 30
>
> Breivika Industriveg 63
> N-6018 Ålesund
> Norway
>
> k...@icd.no
>
> www.icd.no
>
>
> The information transmitted is intended only for the person or entity to
> which it is addressed and may contain confidential and/or privileged
> material. Any review, retransmission, dissemination or other use of, or
> taking of any action in reliance upon this information by persons or
> entities other than the intended recipient is prohibited. If you
> received this in error, please contact the System Manager i...@icd.no and
> delete the material from any computer.
> ___
> 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] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[liubin liu]

Thanks!

It sounds pretty good. But I don't understand it exactly. Could you write
down the sample codes?



Matthew L. Creech wrote:
> 
> On Mon, Apr 27, 2009 at 10:25 PM, liubin liu <7101...@sina.com> wrote:
>>
>> thanks
>>
>> I'm not sure of the real reason.
>> It's true that the speed of inserting with transaction is very fast. But
>> my
>> project is using SQLite mainly in selecting something. I don't know how
>> to
>> use transaction in the situation. May some friends give me some codes on
>> that?
>>
> As someone else mentioned, you probably don't want to open/close the
> database every time you do a single SELECT.  In the example you're
> doing 100,000 iterations of open/exec/close, which will give pretty
> terrible performance.
> 
> Instead, try creating an initialization call, which opens the
> database, prepare()s your SELECT stmt (with a '?' placeholder for the
> ID in this example), and returns a handle to the caller.  The caller
> can then loop 100,000 times calling getdata() with this handle.
> getdata() can just do sqlite3_bind_XXX() followed by sqlite3_step()
> (and sqlite3_reset()), which will be _much_ faster than
> sqlite3_exec().
> 
> -- 
> Matthew L. Creech
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/speed-test%2C-Sqlite3-vs-BerkeleyDB%2C-I%27m-confused-tp23209208p23271593.html
Sent from the SQLite mailing list archive at Nabble.com.

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