[sqlite] sqlite3 (or sqlite4) performance on NFS

Hi, I know that sqlite3 is not recommended to be used on NFS. But I only use sqlite3 for read only (the only time that I write is to load the initial data to the data base). With this restriction, NFS should be fine as the storage since no file locking is needed. I remember that sqlite3 on NFS

[sqlite] How to disambiguate duplicated column names when joining two tables?

Hi, The following example shows that there will be duplicated column names in the joined table. Is there a syntax to disambigurate the duplicated column names? ~$ cat main.sql #!/usr/bin/env bash rm -f main.db sqlite3 main.db <

[sqlite] How accept sqlite3 commands from stdin

I got the following error when I try to read the commands from the command line. Would you please let me know how to let sqlite3 read from stdin? $ cat main.txt create table tbl1(one varchar(10), two smallint); $ echo main.txt |sqlite3 main.db Incomplete SQL: main.txt -- Regards, Peng

[sqlite] sqlite documents in pdf format

I only find html format document but not pdf format. In case I miss anything, could you please let me know if there are pdf format of the complete document of sqlite3? http://www.sqlite.org/docs.html -- Regards, Peng ___ sqlite-users mailing list

[sqlite] What is the sqlite3 script suffix?

I'm trying to following the convention. If I'm going to save sql commands in a script and run them as needed, could you let me know what suffix people in general use? I'm thinking of the suffix '.sql', but I'm not sure what the most common one in sqlite community. -- Regards, Peng

Re: [sqlite] How accept sqlite3 commands from stdin

On Tue, May 25, 2010 at 3:25 PM, Black, Michael (IS) wrote: > sqlite3 main.db < main.txt > > or > cat main.txt | sqlite3 main.db > > or > > echo "create table tbl1(one varchar(10), two smallint);" | sqlite3 main.db Is there a way to use Shebang for sqlite3 script?

Re: [sqlite] sqlite documents in pdf format

Hi Artur, I could use acrobat to convert the whole website to pdf. Do you know if there is any particular advantage to use PDF Creator, besides the latter is free? Although I could use a software to convert html to pdf, I'd prefer to have a document in pdf directly. As the pdf file converted

Re: [sqlite] How accept sqlite3 commands from stdin

On Thu, Jun 24, 2010 at 5:05 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 24 Jun 2010, at 4:50am, Peng Yu wrote: > >> Is there a way to use Shebang for sqlite3 script? >> >> http://en.wikipedia.org/wiki/Shebang_%28Unix%29 > > SQLite comes

[sqlite] vim highlight for sqlite script

Hi, The default vim syntax highlight doesn't highlight command like .help that is specific to sqlite3. Does anybody use a vim script that can hightlight those sqlite specific commands? -- Regards, Peng ___ sqlite-users mailing list

Re: [sqlite] How accept sqlite3 commands from stdin

On Thu, Jun 24, 2010 at 9:19 AM, Eric Smith <eas@gmail.com> wrote: > Peng Yu wrote: > >> On Thu, Jun 24, 2010 at 5:05 AM, Simon Slavin <slav...@bigfraud.org> wrote: >> > >> > On 24 Jun 2010, at 4:50am, Peng Yu wrote: >> > >> >> Is

[sqlite] Why null is not printed?

According to The Definitive Guide to SQLite (page 120), if I type "select null=null;" in a sqlite3 session, I should get 'NULL' printed. http://apress.com/book/view/9781590596739 But I tried it in sqlite3, only an empty line is printed. Is it the cases that sqlite3 change the way to show null

Re: [sqlite] sqlite documents in pdf format

On Thu, Jun 24, 2010 at 2:32 PM, Roger Binns <rog...@rogerbinns.com> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 06/24/2010 06:08 AM, Peng Yu wrote: >> It is very common that software provide >> document in many different format. For example, py

[sqlite] set width from command line

I don't see an option (in man) to set width for column mode (equivalent to .width command in an sqlite3 session) from sqlite3 command line. I'm wondering if there is such an option or it is not available from the command line. -- Regards, Peng ___

[sqlite] count distinct on multiple fields

I use || to count distinct on multiple fields. It works for this particular example. But I'm not sure if it is robust. Would you please let me know if there is any better way? #!/usr/bin/env bash rm -f main.db sqlite3 main.db

[sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?

Hi, SELECT DISTINCT type_id FROM foods; If I use 'distinct', any entry that shows up greater or equal to one time will only appear once. But I want to select an entry that appears <=n times and only show n times if it appears more than n times. I think that "group by" might help. But I'm not

Re: [sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?

On Fri, Jul 2, 2010 at 11:19 AM, P Kishor <punk.k...@gmail.com> wrote: > On Fri, Jul 2, 2010 at 11:19 AM, P Kishor <punk.k...@gmail.com> wrote: >> On Fri, Jul 2, 2010 at 11:15 AM, Peng Yu <pengyu...@gmail.com> wrote: >>> Hi, >>> >>> SELECT D

[sqlite] loading data from file with the file name as the extra field

Hi, Suppose that I have a number of files, each file has some numbers in it (by line). I want to load the content of each file and the associated filename into the following table. create table test (id integer primary key, filename text, number integer); For example, if file 'a' has number

[sqlite] How to supply no values when inserting a record?

Hi, create table test (value text default 'unknown', value2 text default 'unknown'); insert into test (value) values('xxx'); The above code works correctly. But if there is only one column with a default value, I don't see how to insert a record with the default value. I tried the following two

Re: [sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?

On Sat, Jul 3, 2010 at 8:14 AM, Tim Romano wrote: >  I'm not quite sure what you meant by "only show n times if it appears more > than n times'.   Is the pseudo-code below analogous to what you are trying > to do? > > > for each surname in >  (select surname from

[sqlite] What is "collate nocase" for when used with create index?

Hi, I see some examples on using "collate nocase" with "create index". I'm not sure what it means, as the following select statement still only return the line with 'aaa' but not the line with 'AAA'. Does the corresponding column in the table has to be "collate nocase" as well? create table test

Re: [sqlite] What is "collate nocase" for when used with create index?

to optimize its execution then you should use "collate nocase" in the > index. In this case, I don't need to specify the column to be collate nocase when I create the table? > Pavel > > On Sun, Jul 4, 2010 at 12:47 PM, Peng Yu <pengyu...@gmail.com> wrote: >> Hi, &g

[sqlite] How to concat column

Hi, I only find row-wise concatenation by not column-wise. For example, I have table x1 y1 x1 y2 x2 y3 x4 y4 I want to have the second column concatenated based on the value in the first column to get the new table x1 y1y2 x2 y3y4 Moreover, I want to have a spectator (e.g., ',') in the second

[sqlite] "use database" in sqlite3?

Hi, I can attach a database to the current session. But I have to explicitly specify the table name to refer to any tables in it (such 'create_index' in 'create_index.sqlite_master'). Is there a command similar to 'use database' (mysql) in sqlite3 so that I can make a particular database as

[sqlite] How to show all the commands that are executed?

Hi, It seems that .echo on shows certain commands but not all. For example, 'create table' command is not shown. Is there a command to show all the commands that are executed? $ cat ./main.sql #!/usr/bin/env bash rm -f main.db sqlite3 main.db

[sqlite] Import all tables from one db file to another

I could create tables in database B to select all the data from database A and insert them in B. I don't find any simpler way to copy all tables from A to B. But I may miss something. I'm wondering if there is a very simple single command that can just copy all the tables from database A to

Re: [sqlite] Import all tables from one db file to another

On Tue, Jul 6, 2010 at 4:45 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 6 Jul 2010, at 10:42pm, Peng Yu wrote: > >> I could create tables in database B to select all the data from >> database A and insert them in B. I don't find any simpler way to copy >&

Re: [sqlite] How to concat column

On Sun, Jul 4, 2010 at 7:15 PM, P Kishor <punk.k...@gmail.com> wrote: > On Sun, Jul 4, 2010 at 6:15 PM, Peng Yu <pengyu...@gmail.com> wrote: >> Hi, >> >> I only find row-wise concatenation by not column-wise. >> >> For example, I have table >&g

[sqlite] Case insensitive join available?

Hi, I'm wondering if there is a syntax to do case insensitive join. Of course, I could generate two new tables, with the both joining columns converted to lower case (or upper case). But I'd like a simpler solution. #!/usr/bin/env bash rm -f main.db sqlite3 main.db

[sqlite] create one index on multiple columns or create multiple indexes, each of which is on one column?

This question may be trivial. But I'm wondering, whether I should create one index on multiple columns or create multiple indexes, each of which is on a single column. The condition of the 'select' statement can be a logic operation on any of the column of the table and their combinations. I

[sqlite] Parallel sqlite3

Hi, I import a tab-sep file and create index on it. It seems that sqlite3 only use 1 CPU. Is there a way to make it use more than one CPUs? I searched 'parallel' on sqlite.org. But I don't find anything. -- Regards, Peng ___ sqlite-users mailing list

Re: [sqlite] create one index on multiple columns or create multiple indexes, each of which is on one column?

On Sun, Jul 18, 2010 at 12:08 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 18 Jul 2010, at 5:46am, Peng Yu wrote: > >> This question may be trivial. But I'm wondering, whether I should >> create one index on multiple columns or create multiple indexes, each

[sqlite] count distinct group by

Suppose that I have a table of 4 columns. S R1 R2 T s1r1 r2 t1 s1r1 r2 t2 s2r3 r4 t5 s2r5 r4 t6 s3r6 r7 t7 s3r6

[sqlite] Is there a way to inner join on named intervals?

Hi, Suppose that I have a table "A", each row represents a interval. For example, the first row represents an interval [1,10) with a name "a". The first and second rows are considered overlapping because the interval [1,10) and interval [5,15) intersect and both rows have the same name "a". name

[sqlite] Is there a table that show all the available function from sqlite3?

Hi, http://www.sqlite.org/docs.html I don't see a table that shows all the available functions in sqlite3. Would you please let me know if there is such a table? -- Regards, Peng ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Is there a way to inner join on named intervals?

15 tag2a 3 7 attr1 a 5 15 tag2a 8 12 attr2 a 21 30 tag3a 25 35 attr4 > On Fri, Aug 13, 2010 at 11:07 AM, Peng Yu <pengyu...@gmail.com> wrote: >&g

[sqlite] How to import data from stdin?

Hi, I have the following data file and sql script. But I get the following error. I think that I should be able to import the data from from /dev/stdin. I replace /dev/stdin with an ordinary file and it works. Could anybody let me know what is the correct way to do so? Thank you very much!

Re: [sqlite] How to import data from stdin?

On Sun, Aug 15, 2010 at 5:58 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 15 Aug 2010, at 11:31pm, Peng Yu wrote: > >> $ cat file.txt >> 1     eenie >> 2     meenie >> 3     miny >> 4     mo >> $cat main.sql >> #!/usr/bin/e

Re: [sqlite] How to import data from stdin?

On Sun, Aug 15, 2010 at 5:58 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 15 Aug 2010, at 11:31pm, Peng Yu wrote: > >> $ cat file.txt >> 1     eenie >> 2     meenie >> 3     miny >> 4     mo >> $cat main.sql >> #!/usr/bin/e

[sqlite] .mode works for .import as well?

Hi, According to the man page, .mode works only for output mode. .mode MODE ?TABLE? Set output mode where MODE is one of: csv Comma-separated values .. However, it seems that it works for .import as well (see my

Re: [sqlite] .mode works for .import as well?

On Mon, Aug 16, 2010 at 11:53 AM, Peng Yu <pengyu...@gmail.com> wrote: > Hi, > > According to the man page, .mode works only for output mode. > >       .mode MODE ?TABLE?     Set output mode where MODE is one of: >                                csv    

[sqlite] How to execute '.read ...' along with some other commands from command-line?

Hi, I'm trying to use '.read ...' along with other commands from command-line. But it was not successful. Could anybody let me know what is the correct way to do so? $ cat main.sql select 4; $ cat main.sh #!/usr/bin/env bash sqlite3 main.db3 'select 3; select 4;' sqlite3 main.db3 '.read

[sqlite] What is the most efficient way to get the close by numbers?

Hi, I have the following code to search for neighboring positions (distance <=10). But it is slow for large data set. I'm wondering what is the most efficient query for such a search. Note that I don't create an index, as I'm not sure what index to create on table A. $ cat main.sql

Re: [sqlite] What is the most efficient way to get the close by numbers?

e it. > > A1.name=A2.name and A2.position between( A1.position - 10, A1.position + 10 ) > > > On 8/20/2010 3:54 PM, Peng Yu wrote: >> Hi, >> >> I have the following code to search for neighboring positions >> (distance<=10). But it is slow for large data

Re: [sqlite] What is the most efficient way to get the close by numbers?

On Sat, Aug 21, 2010 at 10:45 PM, ve3meo wrote: > "Eric Smith" ... >> I haven't used it myself, but I'm pretty sure this is what the R*tree >> module was designed for: > > I have not used it either but was intrigued by your suggestion. Looking into > it, my sense was

Re: [sqlite] What is the most efficient way to gettheclosebynumbers?

On Sat, Aug 21, 2010 at 7:53 AM, ve3meo wrote: > > "Simon Slavin" ... >> >> By the way, you might find that swapping the last two lines makes it >> faster: >> >> >>> select * from A as A1, A as A2 where A1.name=A2.name and >>> A2.position between A1.position - 10 and

Re: [sqlite] sqlite documents in pdf format

Hi, Just for the benefit of everybody using sqlite3. I made a pdf file for the document on the website (using htmldoc, none relevant pages removed). http://rapidshare.com/files/414401016/sqlite3_doc.pdf BTW, the document on sqlite website seems a little disorganized. From the docs.html file, I

Re: [sqlite] What is the most efficient way to gettheclosebynumbers?

On Sat, Aug 21, 2010 at 4:25 PM, ve3meo wrote: > "Simon Slavin" ... >> >>> Here's how I have interpreted Simon's suggested chunky query: >>> >>> select * from A as A1, A as A2 where A1.name=A2.name and >>> A1.position != A2.position and >>> A2.chunk between A1.chunk -

[sqlite] Better way to export sqlite3 data than pipe?

Hi, Since I don't find a command that can directly export the data into a file, I use pipe to export data from sqlite3 to a tsv file. Is there a better way to do so? $ cat main.sh #!/usr/bin/env bash rm -rf main.db3 sqlite3 main.db3 '.read main.sql' > main.txt $ cat main.sql create table A

[sqlite] How to get the original rows after 'group by'?

Hi, In the following sql query, I want to get all the lines which satisfies that the first column appears less or equal to 2 times. Is there a way to do it other than using inner join of the original table and the query in main.sql? $ cat main.sql #!/usr/bin/env bash #sqlite3 foods.db

Re: [sqlite] How to get the original rows after 'group by'?

)? On Thu, Aug 26, 2010 at 3:59 PM, David Bicking <dbic...@yahoo.com> wrote: > you can use: > > select col1, col2 from test where col1 in (select col1 from test group by > col1 having count(*)<=2); > > David > > --- On Thu, 8/26/10, Peng Yu <pengyu...@gmail.co

Re: [sqlite] How to dump all the tables into separate files?

Could you provide the working code for bash (on Mac OS X or linux)? Thanks. On Fri, Dec 8, 2017 at 7:52 AM, Luuk <luu...@gmail.com> wrote: > On 08-12-17 14:13, Simon Slavin wrote: >> >> >> On 8 Dec 2017, at 7:02am, Peng Yu <pengyu...@gmail.com> wrote: >&

[sqlite] How to dump all the tables into separate files?

I'd like to dump all the tables to separate files, one table one file. Each file should be in TSV format. Is there a convenient way to do so in sqlite3? -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] How to import TSV table with double quote in it without having to escape double quote

Hi, I got the following error. I don't want to escape the quote charaters in the input. Is there still a way to import quote characters into a sqlite3 table? $ cat my.sql3 create table mytab (var text); .separator "\t" .import /dev/stdin mytab $ cat main.sh #!/usr/bin/env bash # vim: set

[sqlite] Is there a way to select using cid?

There are cid's for each table. Is there a way to use "select" with cid's instead of their names? Thanks. $ sqlite3 dbfile

[sqlite] How many concurrent sqlite3 connections are allowed?

Hi, I have many concurrent sqlite3 connections from many python scripts. I am not sure this causes some deadlock condition, but my scripts do not show any progress after some initial progress. Is it that somehow many open connections can cause problems? Thanks. -- Regards, Peng

[sqlite] What is the most commonly used file extension for sqlite3 database files?

Hi, I see a variety of extensions for sqlite3 database files, such as .db, .db3, .sqlite3 and .sq3. Is there a most commonly used/accepted convention for the extension? Thanks. -- Regards, Peng ___ sqlite-users mailing list

[sqlite] Row locking sqlite3

Hi, I see that sqlite3 still does not support row locking. This package tries to resolve this problem. But it does not have a standard build process for Linux. https://github.com/sqlumdash/sqlumdash/ Are there other packages similar to sqlite3 but support row locking? Thanks. -- Regards, Peng

[sqlite] compressed sqlite3 database file?

I have some TSV table in .gz format of only 278MB. But the corresponding sqlite3 database exceeds 1.58GB (without any index). Is there a way to make the database file of a size comparable (at least not over 5 times) to the original TSV table in the .gz file? Thanks. -- Regards, Peng

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

> > Your comparison is unfair. A .gz file is COMPRESSED by gzip and not in any > > way human readable without uncompressing it first. But to store the file (and occasionally search the data), I would prefer 278MB instead of 1.5GB. With a .gz file, at least I can zcat and zgrep. > How big is the

Re: [sqlite] How lock is implemented upon write?

So the fundamental C API that unix_os.c is based on is the only following but nothing else? - fcntl: http://pubs.opengroup.org/onlinepubs/9699919799/functions/fcntl.html - flock: https://www.freebsd.org/cgi/man.cgi?query=flock=2 On 7/3/19, Jens Alfke wrote: > >> On Jul 3, 2019, at 6:20

Re: [sqlite] How lock is implemented upon write?

> Here's my guess. OP is trying to implement locking in Python. OP sees that > SQLite does locking and wants to copy code. > > Obviously, that's beyond the range of this mailing list, but just to be > helpful, here's some stackoverflow: > >

Re: [sqlite] How lock is implemented upon write?

As I said in my original message "I am trying to understand how lock is implemented in sqlite3". On 7/2/19, Keith Medcalf wrote: > > On Tuesday, 2 July, 2019 13:26, Peng Yu : > >>I not sure how to use os_unix.c. Are there any easy to follow >>examples in pytho

Re: [sqlite] How lock is implemented upon write?

I not sure how to use os_unix.c. Are there any easy to follow examples in python? On Mon, Jul 1, 2019 at 9:08 PM Simon Slavin wrote: > You might want to take a look at the standard VFSen: > > > > At a low level, SQLite depends on the

Re: [sqlite] How lock is implemented upon write?

on them, it is hard for me to make my own toying implementation on the locking mechanism to understand the nuts and bolts. On 7/3/19, Keith Medcalf wrote: > > On Wednesday, 3 July, 2019 11:59, Peng Yu wrote: > >>As I said in my original message "I am trying to understand how l

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

I don't know specifically what you refer to as data normalization. My guess is something like this. But it is irrelevant to my case. https://www.studytonight.com/dbms/database-normalization.php For my specific TSV file, it has about 50 million rows and just two columns. The first column is of

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

On 4/10/19, Keith Medcalf wrote: > > The first column is of strings ... > > Do you mean a single string as in "KerfufledAllaHasbalah" > Or a "bunch of strings with some implied delimiter" such as > "Kerfufled/Alla/Hasballah" where "/" is the separator between strings? > > If the latter, the data

Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

n the much larger disk space required, for an occasional search of the data, it seems that it makes no sense to use sqlite3 if disk space is a major concern. On 4/10/19, Peng Yu wrote: >> > Your comparison is unfair. A .gz file is COMPRESSED by gzip and not in >> > any way human re

[sqlite] Does sqlite3 support alter table add primary key?

Hi, I got the following error. Does sqlite3 support alter table add primary key? sqlite> alter table mytab add primary key (h1); Error: near "primary": syntax error https://www.tutorialspoint.com/sqlite/sqlite_primary_key.htm -- Regards, Peng ___

Re: [sqlite] compressed sqlite3 database file?

> > Wout. > > On Wed., Apr. 10, 2019, 5:04 p.m. Warren Young wrote: > > > On Apr 9, 2019, at 11:39 PM, Peng Yu wrote: > > > > > > Is there a way to make the database file of a size comparable (at least > > > not over 5 times) to the original TSV ta

Re: [sqlite] compressed sqlite3 database file?

What do you recommend for Mac? Thanks. On 4/10/19, Simon Slavin wrote: > On 10 Apr 2019, at 7:08pm, Peng Yu wrote: > >> https://softwarerecs.stackexchange.com/questions/45010/transparent-file-compression-apps-for-macos >> >> I work on Mac. Would this be worthwhile to try

[sqlite] How lock is implemented upon write?

Hi, I am trying to understand how lock is implemented in sqlite3. But the following manual page is too much such that I can figure out the main low level implementation techniques. https://www.sqlite.org/draft/lockingv3.html Could anybody provide some equivalent python code (other high level

[sqlite] sqlar: provide file content from stdin

Hi, https://www.sqlite.org/sqlar.html#managing_an_sqlite_archive_from_the_command_line To add something to sqlite3, I must provide an actual file. sqlite3 alltxt.sqlar -Ac *.txt What if I just want to specify the `name` field in the resulted .sqlar file in the command line, but take the file

[sqlite] database like file archive

Hi, I haven't found an archive format that allows in-place delete (I know that .zip, .7z and .tar don't). This means that whenever delete is needed, the original archive must be copied first. This can be problematic when the archive is large and the file to delete is small. Something along the

Re: [sqlite] strip off file metadata in sqlar

I just need the return status of sqldiff (as `cmp -s`). Is there an option to suppress all the screen output? Thanks. On 8/27/19, Warren Young wrote: > On Aug 27, 2019, at 2:40 PM, Peng Yu wrote: >> >> Where is the binary or source code of sqldiff? > > The source is part o

Re: [sqlite] strip off file metadata in sqlar

Where is the binary or source code of sqldiff? I don't find sqldiff in the package sqlite installed in homebrew. I also don't find sqldiff after compiling sqlite-autoconf-329.tar.gz. > https://www.sqlite.org/sqldiff.html -- Regards, Peng ___

[sqlite] What concurrency level is of sqlite?

Hi, In other words, if two processes write to the same sqlite file but to different tables, will one wait for the other? What if to the same table but different rows? Thanks. -- Regards, Peng ___ sqlite-users mailing list

[sqlite] How to create a new table or overwrite an existing table in one command?

Hi, If I try to create a table that already exists, sqlite will give me an error. Is there way to issue one command to create a table, but if there is already a table with the same name, drop it then create the new table? Thanks. -- Regards, Peng ___

[sqlite] What is the zlib compression level used by sqlar?

It is not clear what level of zlib compression is used in sqlar. Does anybody know? https://sqlite.org/sqlar/doc/trunk/README.md Is the following python code guarantee to generate a file that conform to the sqlar format standard? (Note that I deliberately set the timestamp and permission to zero

Re: [sqlite] database like file archive

> The standard "sqlite3" command-line tool will read and write SQLite > archive files. See the documentation at > https://www.sqlite.org/sqlar.html#managing_an_sqlite_archive_from_the_command_line OK. So there is basically no need to install the sqlar command since all features from the sqlar

[sqlite] strip off file metadata in sqlar

The .sqlar files contain file metadata. I'd like two .sqlar files to be exactly the same (`cmp` should return 0) when they store the same content. Is it possible to strip off all metadata of stored files? Thanks. $ sqlite3 my.sqlar -Atv -rwxr-xr-x181 2019-08-27 15:18:27 main.sh --

Re: [sqlite] database like file archive

> There is the sqlar archive format, which you can test using the official > sqlite3 command line shell. There is also a library for it as part of > the Sqlite3 repository. > > https://www.sqlite.org/sqlar.html > https://sqlite.org/sqlar/doc/trunk/README.md This is good to know. How to install

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

For now, I just delete the db file if it exists already. So that I don’t need to call DROP TABLE IF EXISTS mytab. I guess directly deleting the db file can be faster than the latter. Is it so? On Tue, Sep 3, 2019 at 3:06 PM Simon Slavin wrote: > On 3 Sep 2019, at 8:57pm, Peng Yu wr

[sqlite] lockfile but using db table row instead of file

The command line program `lockfile` locks based on files. I wants to do the same thing but based on a row in a table. For example (this is just one example feature, but all other features should also be preserved), if a given row is there, the process will hang unless some other process delete the

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

but > still took 40-60 mins from memory. > > Rob > > On 4 Sep 2019, at 11:18, Peng Yu wrote: > > > For now, I just delete the db file if it exists already. So that I > > don’t > > need to call DROP TABLE IF EXISTS mytab. I guess directly deleting the > > db &g

Re: [sqlite] lockfile but using db table row instead of file

On 9/4/19, Keith Medcalf wrote: > > On Wednesday, 4 September, 2019 04:40, Peng Yu wrote: > >>The command line program `lockfile` locks based on files. I wants to do the >>same thing but based on a row in a table. For example (this is just one >>example feature, bu

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

> If you delete the database file then make sure you also delete any other > files that might have been associated with it, such as left over journals > and so forth. I never see those extra files in practice. Are they guaranteed to be deleted automatically once an SQLite session is finished? --

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

> Nope. If there was a problem in closing down they can hang around (which is > their whole point for recovery). Also if a journal mode of "persit" was > used. But mostly from incorrect closure. > > So check for any -journal, -wal, or -shm files of the same name if you want > to obliterate a

[sqlite] Merge two sqlar files

Hi, UNION is for tables in the same db file. Is there an easy command to combine two sqlar files? Thanks. https://www.sqlitetutorial.net/sqlite-union/ -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] How to install REGEXP support?

Hi, I'd like to have regexp support in sqlite3. https://www.sqlite.org/lang_expr.html#regexp But it is not clear how to install it for sqlite3 installed by homebrew. Does anybody how to install it? Thanks. https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query --

Re: [sqlite] How to install REGEXP support?

Do you know what the commands are to just compile for the regex support? > SQLite doesn't come with a regexp implementation; it has to be added by an > extension. > I searched the website; there's a simple implementation here: > >

Re: [sqlite] Merge two sqlar files

OK. I made a mistake. UNION of multiple tables should work. On Fri, Sep 6, 2019 at 7:28 PM Peng Yu wrote: > > Hi, I think that there is no way to UNION 3 tables in one command is > it. What is the best way to merge 3 and more sqlar files? Thanks. > > On Fri, Sep 6, 2019 at 6:30 P

Re: [sqlite] Merge two sqlar files

but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >-Original Message- > >From: sqlite-users On Behalf > >Of Peng Yu > >Sent: Friday, 6 September, 2019 17:04 > >To: SQLite mailing list > >Subject: [sqlite] Merge two sqla

Re: [sqlite] Merge two sqlar files

OK. It works. But it seems that `.ar` is not necessary. import sqlite3 infile1, infile2, outfile = sys.argv[1:] conn=sqlite3.connect(outfile) c=conn.cursor() c.execute('ATTACH DATABASE ? AS d1', [infile1]) c.execute('ATTACH DATABASE ? AS d2', [infile2]) c.execute('CREATE TABLE sqlar AS SELECT *

[sqlite] How to do setdiff but update the original table by the result?

Hi, I'd like to perform setdiff. I think this should be relevant. But it does not update the original table. I want to update the original table by the result. Is there a command to do so? Thanks. https://www.sqlitetutorial.net/sqlite-except/ -- Regards, Peng

Re: [sqlite] How to do setdiff but update the original table by the result?

> I want to find all the rows in table1 that are not in table2 and then delete > those rows from table1. It is the above. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] How to install REGEXP support?

But I don't want to always specify a full path. I am asking where is the standard place to put the library file so that I don't have to always specify the whole path. On 9/17/19, Jens Alfke wrote: > >> On Sep 17, 2019, at 8:13 AM, Peng Yu wrote: >> >> I can compile it. Wher

Re: [sqlite] How to do setdiff but update the original table by the result?

Not relevant. See a previous follow up reply of mine. On Wed, Sep 18, 2019 at 11:21 AM Simon Slavin wrote: > > On 18 Sep 2019, at 3:37pm, Peng Yu wrote: > > > I'd like to perform setdiff. I think this should be relevant. But it > > does not update the original ta

[sqlite] The best way to check if a file is a sqlite3 DB file?

Hi, I use `file` to check if a file is a sqlite3 DB file. But I have to parse the result. Is there a better way to just return an exit status of 0 for a sqlite3 DB file and 1 otherwise? Thanks. $ file /tmp/tmp.erZ5aS6PUX.sqa /tmp/tmp.erZ5aS6PUX.sqa: SQLite 3.x database, last written using SQLite

Re: [sqlite] How to install REGEXP support?

/sqlite3ext.h ./lib ./lib/libsqlite3.0.dylib ./lib/pkgconfig ./lib/pkgconfig/sqlite3.pc ./lib/libsqlite3.dylib ./lib/libsqlite3.a ./README.txt ./share ./share/man ./share/man/man1 ./share/man/man1/sqlite3.1 On Mon, Sep 16, 2019 at 7:58 PM Warren Young wrote: > > On Sep 16, 2019, at 6:24 PM, P

[sqlite] Is pcre available on homebrew?

I'd like to use regex. https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query But I don't find pcre. https://formulae.brew.sh/formula/sqlite Does anybody know how to make regex available for slqite3 installed by homebrew? Thanks. -- Regards, Peng

  1   2   >