RE: [sqlite] Data encryption
Andreas:
Some time ago, in response to a similar question, Günter Greschenz sent to
this forum a email:
hi,
i've written some sqlite-functions to crypt (blowfish) or compress (bzip)
data in sqlite:
e.g.
insert into blubs values (crypt('data','pwd'))
or
select from xyz where decompress(data) = 'blablabla'
or
select from xyz where data = compress('blablabla')
but you have to wait until next weekend, because i'v traveling for my
company at the moment and >return on friday (i hope :-)
Some day later:
after a long time being on a business trip, i finally came home and have
now the chance to upload >the sources to my webserver:
http://greschenz.dyndns.org/sqlite.html
these sources have never been in a productive system, i just implemented
it for fun...
what i want to say: i never tested it really good !
To me it was a wonderful source of information and ideas, but this morning
the URL was unable. May be if you recite certain magic spell, Günter can
appear again...
Cheers
A.J.Millan
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Data encryption
On 11/12/07, Andreas Volz <[EMAIL PROTECTED]> wrote: > I think about to encrypt the data in my DB. Does sqlite offer a data > encryption on a lower level? Or should I encrypt my data before putting > it into the table on a higher level in my application without involving > sqlite? Dr. Hipp sells encryption support for sqlite: http://www.hwaci.com/sw/sqlite/prosupport.html Several others also maintain their own versions of sqlite with encryption support; I believe the .NET wrapper from phxsoftware uses the Windows crypto libraries, for example. The goal of all of them is to encrypt the entire database file, except for a small part of the beginning of the file that contains physical layout info. Encrypting specific data in your application may still be useful in some circumstances. When sqlite is encrypting the entire database, that means any application that uses the database needs to use the specific version of the sqlite library with the encryption support. If you have a situation where it's ok if other applications access most of the data, that's probably a bad thing, since you only need specific parts protected. The same holds true for pretty much any scenario where changing the sqlite library is inconvenient. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] BLOB data performance?
Am Mon, 12 Nov 2007 23:41:59 + schrieb [EMAIL PROTECTED]: > Andreas Volz <[EMAIL PROTECTED]> wrote: > > Hello, > > > > I'll tell you my current situation. I implemented a web cache > > function for images and other data in my application. In the past I > > saved the data on the hard disk with a special name and had a text > > file with the relation "cache file name <-> url". But I didn't like > > it. Now I like to evaluate sqlite as solution. > > > > So my question is about the binary data. Is it better to insert the > > images and other media data (e.g. videos with < 10 MB of size) into > > the DB or only a "pointer" to a file laying around on my hard disk? > > I would estimate a maximum DB size of several hundred MB. > > > > How good/bad is reading/writing this data into a BLOB compared to > > write it as file beside the DB and write only a small name into the > > DB? Where is the difference between both ways regarding memory and > > CPU usage? > > > > BTW: My current use case writes data slow, but reads data fast. > > Reading BLOB's must be as fast as reading on the hard disk. > > > > In my studies, BLOB I/O is faster than disk I/O for BLOBs of about > 50KiB or less on Linux. Disk I/O is faster for larger BLOBs. I > have received reports that the transition threshold is about 14KiB > on win32. In my experiements, BLOB I/O is about 10% slower than > direct disk I/O for multi-megabyte blobs. Less than 50 kiB would be nice at least for images and HTML files. I'll give it a try and do my own experiments. Thanks for your experience so far. regards Andreas - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Data encryption
Hello, I think about to encrypt the data in my DB. Does sqlite offer a data encryption on a lower level? Or should I encrypt my data before putting it into the table on a higher level in my application without involving sqlite? regards Andreas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: a TRIGGER to create a VIEW ? (newbie)
On 11/12/07, Andreas <[EMAIL PROTECTED]> wrote: > Am 12.11.2007 um 14:40 schrieb Igor Tandetnik: > > I wonder - do you have any plans to eventually drop some of these > > views? Or are you just going to keep creating them, exploding the > > size of the database schema? In fact, why on earth do you need so > > many views in the first place? > hmm, honestly i planned a ON DELETE-Trigger as soon as the ON INSERT- > Trigger works ;-) to sum it up : i expect to gather 25-30 rows in the > config-table and ~200-250 corresponding rows in the files table. That > makes at worst 7500 rows in the files-table. That said, i see myself > far away from performance-issues. My question belongs to the area > 'research-while-developing' or maybe howto place as much logic into > the DB, not polluting my apps-source lines with SELECT-staements. I'm > used to do things dynamically and usually use OODB's with python. For > this tiny project it's JS and sqlite and things work different here - > but it's gone work. In general, DDL should not be a common operation in an SQL database. The SQL schema should be fixed, using a relational model that fits what you need to do with your data, and queries do the actual data manipulation and retrieval. In sqlite terms, those VIEWs will actually come at a performance cost: every time the database is opened, sqlite must parse the schema to determine the layout of the database. There is no performance to be gained from using them, and the example above does not appear to simplify application queries at all (which is the entire point of using VIEWs). You've simply exchanged a column identifier for a table identifier. As you said, you're not yet near performance issues, and there's obviously room to do whatever you want, the above is just general guideline. I'd call creating such VIEWs as the above to be bad design, unless there's a detail you left out that makes them more appropriate. You mentioned using OODBs, so I think I understand where you're coming from, although I've never used anymyself. You might try to find what some common ORMs (Object-Relational Mappers) do. They're essentially middleware that converts between an object and SQL data. The "column=foo" bit is the type of thing they do behind the scenes. Your project sounds small enough that I would probably just bite the bullet and use SQL myself, rather than trying to use some abstraction layer. It'll take some mental adjustment, but it's probably worth it just to learn the SQL way of doing things. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: a TRIGGER to create a VIEW ? (newbie)
Am 12.11.2007 um 14:40 schrieb Igor Tandetnik: There is no syntax in SQLite to create a view (or anything else) with name generated at runtime from field values. A view name is a plain identifier hardcoded into the statement - it is not evaluated as an expression. great, thx for this quick clarification. What you can try is call a custom function from the trigger (see sqlite3_create_function), which function can then create a view. The function can use string manipulation to generate an appropriate SQL statement from the values of its parameters. ok, maybe later - something tells me maybe simple SELECTS within my app could do it, too. I wonder - do you have any plans to eventually drop some of these views? Or are you just going to keep creating them, exploding the size of the database schema? In fact, why on earth do you need so many views in the first place? hmm, honestly i planned a ON DELETE-Trigger as soon as the ON INSERT- Trigger works ;-) to sum it up : i expect to gather 25-30 rows in the config-table and ~200-250 corresponding rows in the files table. That makes at worst 7500 rows in the files-table. That said, i see myself far away from performance-issues. My question belongs to the area 'research-while-developing' or maybe howto place as much logic into the DB, not polluting my apps-source lines with SELECT-staements. I'm used to do things dynamically and usually use OODB's with python. For this tiny project it's JS and sqlite and things work different here - but it's gone work. @Joe : welcome to hackland ;) i've seen that approach in threads about 'cascading | recursive'-triggers on this list and assumed a solution to my tiny prob. thx for the example, i'll keep it till the next rainy sunday-afternoon comes - i'm sure i'll find a way to make the main-schema explode somehow ;-) greets, andreas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] BLOB data performance?
Andreas Volz <[EMAIL PROTECTED]> wrote: > Hello, > > I'll tell you my current situation. I implemented a web cache function > for images and other data in my application. In the past I saved the > data on the hard disk with a special name and had a text file with the > relation "cache file name <-> url". But I didn't like it. Now I like to > evaluate sqlite as solution. > > So my question is about the binary data. Is it better to insert the > images and other media data (e.g. videos with < 10 MB of size) into the > DB or only a "pointer" to a file laying around on my hard disk? I would > estimate a maximum DB size of several hundred MB. > > How good/bad is reading/writing this data into a BLOB compared to write > it as file beside the DB and write only a small name into the DB? Where > is the difference between both ways regarding memory and CPU usage? > > BTW: My current use case writes data slow, but reads data fast. Reading > BLOB's must be as fast as reading on the hard disk. > In my studies, BLOB I/O is faster than disk I/O for BLOBs of about 50KiB or less on Linux. Disk I/O is faster for larger BLOBs. I have received reports that the transition threshold is about 14KiB on win32. In my experiements, BLOB I/O is about 10% slower than direct disk I/O for multi-megabyte blobs. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] One line batch file?
Owen Watson wrote:
I'd like to have a one line batch file:
sqlite3 test 'insert into testable values ('value1','value2')'
but the few variants of this I've tried don't work.
I've seen and understood the batch file that calls another text file
approach but I was wondering if I could avoid this overhead for a
one-liner.
Owen,
Try this instead:
sqlite3 test "insert into testable values ('value1','value2')"
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Problem creating extension for use with load_extension
Nuno Lucas wrote > Seems like you didn't enable the extension loading mechanism. It > defaults to disabled for security reasons. > > Check the wiki page about the SQLITE_OMIT_LOAD_EXTENSION define: > * http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions I have been using the precompiled sqlite3.exe for Windows which I believe includes extension loading (.help shows the .load function, and the source code seems to indicate that this is only visible if extension loading is turned on). Please correct me if I am wrong. Still haven't been able to get this to work - does anyone have a working extension in Windows form that I could try just to make sure I am using this properly? Thanks, Bob Dankert - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to get 3.4.2 code
Ah, that makes sense. Thanks! > > Mark, > > I was talking about using the sqlite download page at > http://www.sqlite.org/download.html. > > If you right click on a link (like the one for > sqlite3-3_5_2.zip under > Precompiled Binaries For Windows) and select "copy the link location" > (at least that's the command using Firefox), you will have a > link to the > specified file on the clipboard. > > If you then paste that file name into your browser's address bar you > will have a URL like this http://www.sqlite.org/sqlite-3_5_2.zip that > you can edit. If you now change the 5 in that URL to a 4 and > then press > enter, you will start a download of sqlite-3_4_2.zip. > > If you want you can simply type in the URL to start the download. > > All the old versions are still on the server, they just don't have > clickable links on the download page, so you have to enter > the filename > with the desired version manually (or by editing a very similar URL > using copy and paste to minimize typing errors). - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: a TRIGGER to create a VIEW ? (newbie)
Andreas wrote:
I'm trying to let a Trigger create a View. The first Trigger
('resetSyncMarker') cares for unique-keying in the config-table. The
second Trigger('makeView') shall create a View on insert into the
config-table. The View's name shall be the content of the inserted
shortName-field.
There is no syntax in SQLite to create a view (or anything else) with
name generated at runtime from field values. A view name is a plain
identifier hardcoded into the statement - it is not evaluated as an
expression.
What you can try is call a custom function from the trigger (see
sqlite3_create_function), which function can then create a view. The
function can use string manipulation to generate an appropriate SQL
statement from the values of its parameters.
I wonder - do you have any plans to eventually drop some of these views?
Or are you just going to keep creating them, exploding the size of the
database schema? In fact, why on earth do you need so many views in the
first place?
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] a TRIGGER to create a VIEW ? (newbie)
Hi sqlite-users,
i'm working on a web-based workflow involving POW and Server-Side-JS
and sqlite for sure.
I'm trying to let a Trigger create a View. The first Trigger
('resetSyncMarker') cares for unique-keying in the config-table. The
second Trigger('makeView') shall create a View on insert into the
config-table. The View's name shall be the content of the inserted
shortName-field. The View shall select all rows in the files-table
WHERE mand-field=shortName-field.
BEGIN TRANSACTION;
CREATE TABLE config (
client VARCHAR,
isSynced INTEGER DEFAULT 0,
shortName CHAR(4),
remoteIndex VARCHAR,
localIndex VARCHAR,
lastCheck DATE DEFAULT CURRENT_DATE,
lastCheckSTR DATE DEFAULT CURRENT_DATE,
PRIMARY KEY (shortName) ON CONFLICT REPLACE
);
CREATE TABLE files (
mand CHAR(4),
name VARCHAR,
mailDate DATE DEFAULT CURRENT_DATE,
liveDate DATE DEFAULT 0,
pubDate DATE DEFAULT 0,
title VARCHAR,
PRIMARY KEY (mand, name) on CONFLICT REPLACE
);
CREATE TRIGGER resetSyncMarker INSERT ON files
BEGIN
UPDATE config SET isSynced = 0 WHERE shortName = new.mand;
END;
# begin of problem
CREATE TRIGGER makeView INSERT ON config
BEGIN
CREATE VIEW new.shortName AS SELECT * FROM files WHERE
mand=new.shortName;
END;
# eo-problem
COMMIT;
That gives me syntax-error's for the CREATE-statement inside the
CREATE TRIGGER-statement. I've tried variations, taken from the
mailing list:
CREATE VIEW new."shortName" AS SELECT * FROM files WHERE
mand=new."shortName";
CREATE VIEW quote(new.shortName) AS SELECT * FROM files WHERE
mand=quote(new.shortName);
Is this doable at all and maybe quoting/escaping is the problem ? Or
is it a goofy concept ?
thanks for the support, greets
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
